Category Archives: R

Easy way to perform a lookup in R

In order to make your data meaningful  you have to enrich your data from values supplied by lookup table.

eg. If you have got the POSTCODE in your data and you would like to replace the POSTCODE with the  SUBURB


Here is the easiest code which you can use to perform such lookups.

In this example  we will perform a lookup and then store the new value in a new column for the sake of comparison and testing.

Let us assume that you have a dataframe which has the names and the GenderCodes for each name
You would want to make your data useful by adding the actual gender names from a lookup table

# Create a dataframe containing your data
# You would want to replace the GenderCode in this table with the GenderNames from your lookup table
Name <- c("John", "David", "Angela", "Harry", "Christine")
GenderCode <- c(1,1,2,1,2)

MyData <- data.frame (Name,GenderCode)

# Created your lookup table
# Your lookup table has the codesand the actual value which you would want to use
GCode  <- c(1,2)
GenderName <- c("Male","Female")

MyLookupTable <- data.frame(GCode,GenderName)

# Perform alookup and add a new column in your datatable which will have the values pulled in from your lookup table
MyData$GenderName = MyLookupTable[match(MyData$GenderCode, MyLookupTable$GCode), "GenderName"] 



After you run the code you end with the following data.
Notice that a new column GenderName has been added and it has the actual gender names corresponding to the GenderCodes in your original data.

Using Lookup tables in R








How to count in R

Here is an easy way to count in R

In normal SQL you can easily do the following to get the count of a particular item

eg. Select count(*) from myTable group by FacilityName”
this statement in SQL will give you the count of the records belonging to each FacilityName.

Here is the R equivalent code

I prefer to use a package called plyr
Let us use the built in dataset in r called as mtcars


It will give you the count as shown below.
gear freq
1 3 15
2 4 12
3 5 5