Comma separated numerical values as strings.

[Rtip] In my continuous series of 'Rtips', i came across this annoying set of numerical values that are expressed as strings with commas. for example "20,000,123" that should read as 20000123 . This is common when working with monetary sums or totals. So how would you convert that to readable numbers or integers. I found out that gsub function really good at refomatting this character vector. 

       

> budgets_amounts <- c("20,929,200","782,000,000","100,000,000","14,111,122") 
> as.numeric(gsub(",","", budgets_amount))
[1]  20929200 782000000   100000000 14111122
       
 

Factor to Integer/numeric [R Tip]

This always annoys me and i have to look it up all the time, when i try to convert a factor to a numeric, the values change to rank values. #damn

So to transform a factor f to it's approximately original numeric values and no "bull shit" ranks. This command below will save you. 

       
as.numeric(levels(f))
Also mentioned that this is recommended and slightly more efficient than
as.numeric(as.character(f))
       
 

Now backed up, bring on any other disturbing factors. 

Install rgdal issues.

install.packages("rgdal") - this command throws some annoying errors. 

I have tried to install rgdal on my Ubuntu 12.04 instance, but it is not as straight as installing "sp" or any other packages. It demands the pre-installed GDAL and proj.4.

      
$ sudo apt-get install libgdal1-dev libproj-dev
$ sudo R
> install.packages("rgdal")

Predict traffic from count of cars.

A friend recently shared a data set that got a count of cars coming in on specific roads at specific times. I wanted to predict the count of cars on future dates basing on the small data set that was shared. Apparently using times series we can forecast this. 

Feel free to follow along the steps to achieve this with basic packages xts() and forecast. This is my initial stab at this but i'm sure there are better models to use around this. I'll follow up with better models soon. 

       
# Simple script to use
# Default ts() package to forecast traffic count. 
# Dataset: traffic_data.csv

# Author: 'Richard Ngamta', 'ngamita@gmail.com'

# check for directory, else create one. 
if(!file('traffic')){
  #print('hello')
  dir.create('traffic')
}

# Load the forecasting and time series packages. 
require(forecast)
require(xts)

# Set wd to 'traffic'
setwd('traffic/')

# Download the traffic_data.csv from dropbox.
fileUrl <- 'https://www.dropbox.com/s/cbufz4f0rd11tl1/traffic_data.csv'
download.file(fileUrl, destfile='traffic_data.csv', method='wget') # Use method='curl' non *nix 

# Load data into R memory/data frame. df == data frame, csv and got a header. 
traffic_df <- read.csv('traffic_data.csv', sep=',', header=TRUE)

# Check if loaded fine. 
head(traffic_df)

# Format the date, to R readable date and
# not Char strings.
traffic_df$datetime = as.Date(traffic_df$datetime,format="%Y-%m-%d")

# Convert to xts
traffic_df_xts = xts(x=traffic_df$count, order.by=traffic_df$datetime)

# We need to get a start date from data, i got (68)
# How did i do that. Check next
# > head(traffic_df)
# > as.POSIXlt(i = "2014-03-10", origin="2014-03-10")$yday
##    [1] 68
# Add one since that starts at "0" and convert to normal ts()
traffic_df_ts = ts(traffic_df_xts, freq=365, start=c(2014, 68))
png('Traffic_forecast.png')
plot(forecast(ets(traffic_df_ts), 1), main="Traffic Forecast")
dev.off()


# Ignore the frequenxy warnings.
# Check the downloaded plot in same folder/dir. 
# Note from graphs that 2014.4 means "day number 365 * 0.4" (day 146 in the year).
# So actual date is run > as.Date(146, origin="2014-03-10")
# Answer: "2014-08-03"
# This is just the default method, using othee methods lile ARIMA, works better. 

       
 

R joins - revisited (OpenDataKit merge problem)

I've been hacking around #Opendatakit and after pulling data with it's ODKBriefcase tool, you find yourself with a number of .csv files. The first file is the main flat file while the rest are due to the loops that users were adding to the forms. I won't go further into ODK tool but what i know is that the R merge() function allows us to take two data sets and combine them into one, based on a common
variable. To test this, import the following data by running this command:

       

            # Set working directory
# Read data from the web, but download the files first in .csv
download.file('https://www.dropbox.com/s/w12qrdkbp6gpsg2/survey_jan.csv', destfile='survery.csv', method='wget')
download.file('https://www.dropbox.com/s/w12qrdkbp6gpsg2/survey_jan_member.csv', destfile='survery_jan_member.csv', method='wget')

#Load the data in R.  
data <- read.csv('survey_jan.csv', sep=',', header=TRUE)
member <- read.csv('survey_jan_member.csv', sep=',', header=TRUE) 

And to check that it has imported correctly, which is always a good idea, run:

       

# Check the loaded data.
head(data)
head(member)
     
 

We are now tackling a JOIN problem and i always find my self falling back to this JOIN explained page on SO. HERE
For me this is the best part, we now have two data sets; data, which contains a list of survey entries called data, and members, which contains a list including those people as well as additional people who are members of the specific households.
The next step is to combine the two. What we are going to do is select the unique KEYS in the "member" data frame who also appear in the main "data" data frame, and copy their details into a new data frame, along with all the information.

We will refer to the two data frames as x and y. The x data frame is data; and the y is member. In x, the
column containing the list of id's  is called “KEY”, and in y, it is called “PARENT_KEY”. The parameters of the merge function first accept the two table names, and then the lookup columns as by.x or by.y. You should also include all.x=TRUE as a final parameter. This tells the function to keep all the records in x, but only those in y that match.

       

main_survey <- merge(data, member, by.x = "KEY", by.y="PARENT_KEY", all.x = TRUE) 
 

To see what this command has done, type main_survey to show the content of the new data
frame. This should look like:

> head(main_survey)
                                        KEY X.x           SubmissionDate                    sstart
1 uuid:68ec3f5d-078d-4ce9-a197-c3377eee720b   1 Apr 19, 2014 12:07:09 PM Apr 19, 2014 12:05:12 PM
2 uuid:68ec3f5d-078d-4ce9-a197-c3377eee720b   1 Apr 19, 2014 12:07:09 PM Apr 19, 2014 12:05:12 PM
3 uuid:68ec3f5d-078d-4ce9-a197-c3377eee720b   1 Apr 19, 2014 12:07:09 PM Apr 19, 2014 12:05:12 PM
4 uuid:68ec3f5d-078d-4ce9-a197-c3377eee720b   1 Apr 19, 2014 12:07:09 PM Apr 19, 2014 12:05:12 PM
5 uuid:a513043a-0450-47fa-8495-4c2611ece384   2 Apr 19, 2014 12:04:22 PM Apr 19, 2014 12:02:05 PM
6 uuid:a513043a-0450-47fa-8495-4c2611ece384   2 Apr 19, 2014 12:04:22 PM Apr 19, 2014 12:02:05 PM
                       end        today respondent.r_name respondent.position
1 Apr 19, 2014 12:07:03 PM Apr 19, 2014      Ngamita mary           Head food
2 Apr 19, 2014 12:07:03 PM Apr 19, 2014      Ngamita mary           Head food
3 Apr 19, 2014 12:07:03 PM Apr 19, 2014      Ngamita mary           Head food
4 Apr 19, 2014 12:07:03 PM Apr 19, 2014      Ngamita mary           Head food
5 Apr 19, 2014 12:04:18 PM Apr 19, 2014       Jona okello             Prefect
6 Apr 19, 2014 12:04:18 PM Apr 19, 2014       Jona okello             Prefect

Finally, this is a very important note and don't forget that if the by column names were named the same in both x and y (e.g. both called "KEY”), we could specify this

more simply with by="column name" rather than by.x and by.y; and finally, a critical issue when making any join is assuring that the “by” columns are in the same format.

I hope this helps someone out there working with normal joins and also ODK data. 

How to Count, Sum and aggregate in R.

I recently had a chat with a friend who works for an SMS aggregator company and he told me about the challenges he has with analyzing some SMS data they log and that it takes him weeks to put together with Excel. I showed him how to do this in minutes with R and start taking coffee at work :).

The Problem: He frequently wants to to count(SUM) and aggregate things in data frames. For example, he wants to know how much revenue the different musicians made from the ringtones and under what categories etc 

A snap shot of the raw data frame is below. You might want to download the zipped file here and load into your R session with below.

> sms <- read.csv(“sms.csv”, sep=”,”, header=T )  

> dim(sms)

[1] 624  10

You must have 10 columns and 624 rows to make sure you loaded the right data and well :).

customer_name report_date       content_name cms_provider                artist mrp
1      AIRTELUG  11/22/2012              AYAKA   AA_SMSDONE               NANJEGO  30
2      AIRTELUG  11/26/2012       CHOICE YANGE   AA_SMSDONE        GOEFREY LUTAYA  30
3      AIRTELUG   11/5/2012      OJANGA NOSABA   AA_SMSDONE BUGEMBE AND BOBI WINE  30
4      AIRTELUG  11/14/2012         ENSI EKUBA   AA_SMSDONE           CHRIS EVANS  30
5      AIRTELUG  11/15/2012         MR KATAALA   AA_SMSDONE             BOBI WINE  30
6      AIRTELUG   11/3/2012 ABAKUBI BA PULAANI   AA_SMSDONE       GEOFREY LUTAAYA  30
 cms_clip_promo_id    trans_desc revenue count
1           2579072 Song Download     120     4
2           2579000  Song Renewal   10140   338
3           2579196  Song Renewal     960    32
4           2517666  Song Renewal     120     4
5           2517527  Song Renewal     330    11
6           2517665  Song Renewal     360    12

How many times does each artist name occur in our data? There is a guy called Hadley Wickham who came up with an excellent plyr package and that’s what we shall use through out this blog post.

> library(plyr)

> ?count

> count(sms, "artist")

                              artist freq
1                  2 STARS├â┬§├é┬┐├é┬╜    3
2                                 AK47    4
3              AKAYO CHOIR├â┬§├é┬┐├é┬╜    2
4         ALICIOS FT JULIANA KANYOMOZI    1
5                AMBASSADORS OF CHRIST    2
6                       ANGELLA KALULE    1

How many times did each artist appear on specific promo clip? In case you want to tally things up by more than one column use the c function to combine things into a vector:

> count(sms, c("artist", "cms_clip_promo_id")) 
                              artist cms_clip_promo_id freq
1                  2 STARS├â┬§├é┬┐├é┬╜           2517483    2
2                  2 STARS├â┬§├é┬┐├é┬╜           2517589    1
3                                 AK47           2579164    2
4                                 AK47           2579166    2
5              AKAYO CHOIR├â┬§├é┬┐├é┬╜           2517454    1
6              AKAYO CHOIR├â┬§├é┬┐├é┬╜           2517590    1
7         ALICIOS FT JULIANA KANYOMOZI           2599168    1
8                AMBASSADORS OF CHRIST           2517492    2
9                       ANGELLA KALULE           2517507    1
10                        BAINE VIOLLA           2585894    1

R just makes life easy. All i did was just tell count which data frame i was using, then which columns i want to tally by, and it does the counting very quickly and efficiently even on millions of rows.

How much did each artist song gain in revenue total? How much revenue did I it gain in relation to promo clip ? Now i came across the awesome part of aggregate that will do the job for this kind of figuring.

> aggregate(revenue ~ artist  + cms_clip_promo_id, data = sms, sum)
                              artist cms_clip_promo_id revenue
1        BOBI WINE PREV PHILLY LUTAAYA           2517453      90
2              AKAYO CHOIR├â┬§├é┬┐├é┬╜           2517454     990
3                       JULIUS MUHOOZI           2517455     750
4             FRED SEBATTA├â┬§├é┬┐├é┬╜           2517456    1020
5                       RONALD MAYINJA           2517457    1560
6                            BOBI WINE           2517458     150
7                           LADY DIANA           2517460      30
8                      SOPHIE NANTONGO           2517462      30
9                       WILSON BUGEMBE           2517464    2610

To a lay man this command can be interpreted as "I want to apply the sum function to the revenue column while aggregating rows based on unique values in the artist and cmc_clip_promo_id columns." #damn this is so easy and cool right?

How much did each artist gain in revenue  total? Forget about aggregating by clip_promo_id, and just aggregate by artist name:

> aggregate(revenue ~ artist, data = sms, sum)

                              artist revenue
1                  2 STARS├â┬§├é┬┐├é┬╜     720
2                                 AK47   16320
3              AKAYO CHOIR├â┬§├é┬┐├é┬╜    1080
4         ALICIOS FT JULIANA KANYOMOZI     810
5                AMBASSADORS OF CHRIST    1170
6                       ANGELLA KALULE      30
7                         BAINE VIOLLA      30
8                   BEBE COOL FT ALPHA     120
9                            BOBI WINE    3360
10       BOBI WINE PREV PHILLY LUTAAYA      90

What was the mean revenue that the artists gained ? Change sum to mean in the formula:

> aggregate(revenue ~ artist, data = sms, mean)
                              artist     revenue
1                  2 STARS├â┬§├é┬┐├é┬╜   240.00000
2                                 AK47  4080.00000
3              AKAYO CHOIR├â┬§├é┬┐├é┬╜   540.00000
4         ALICIOS FT JULIANA KANYOMOZI   810.00000
5                AMBASSADORS OF CHRIST   585.00000
6                       ANGELLA KALULE    30.00000
7                         BAINE VIOLLA    30.00000
8                   BEBE COOL FT ALPHA    40.00000
9                            BOBI WINE   240.00000
10       BOBI WINE PREV PHILLY LUTAAYA    90.00000
11                           BODDO S S    30.00000

Install package Rgraphviz errors

I recently wanted to use the R package "Rgraphviz" while working on a Text mining project but when i wanted to install it, i continuously got the error below

"Warning in install.packages :
package ‘Rgraphviz’ is not available (for R version 3.0.2)"

hmmm annoying right? I got the fix :) 
Go ahead and type setRepositories() and it will show you a list of available repositories.
setRepositories()

A pop up with the other list of repositories will appear and make sure to select the "BioC Software", press return and when you click on 'install packages' and boom the package will load ok. 

R then Inkscape.

Just wanted to show case some of what i did with data and sketches from R and then making it beautiful in Inkscape. As the saying goes "Don't make anything unless its both necessary and useful: but if it is both, do not hesitate to make it beautiful "

Rows matching value.

Quick tip to select only rows that meet certain criteria in R.
df[df$age >= 50, ]

OR

#script or loop to detect all accounts with age >=50 
count = 0
for (i in 1:length(trend$x)){
  if (trend$x[i] >= 50 ){
    count = count +1
  }

}

Join data frames in R (inner, outer, left, right)

I recently had 2 data frames that i wanted to join into 1 data frame based on the exact dates matched from the two frames.

df1 <- data.frame( Registrations.x = c(7,15,13,20))

df1$Dates <- c("2013-01-01", "2013-01-02", "2013-01-03", "2013-01-06")

df2 <- data.frame(Registrations.y = c(21,36,23,16,28,22))

df2$Dates <- c("2013-01-01", "2013-01-02", "2013-01-03", "2013-01-04", "2013-01-05", "2013-01-06")

Let me show you how to go about with running sql like JOINS using the MERGE function and its optional parametersin R to achieve the following -

1. An Full join of df1 and df2
2. An inner join of df1 and df2
3. An outer join of df1 and df2
4. A left outer join of df1 and df2
5. A right outer join of df1 and df2

I

This is how i’ve used the merge function and its optional parameters:

Inner join: merge(df1, df2, by =”Dates”) NB: May leave out the “by”

Outer join: merge(x = df1, y = df2, by = "Dates", all = TRUE)

Left outer: merge(x = df1, y = df2, by = "Dates", all.x=TRUE)

Right outer: merge(x = df1, y = df2, by = "Dates", all.y=TRUE)

Cross join: merge(x = df1, y = df2, by = NULL)

It’s advisable that you explicitly state the identifiers on which you want to merge with the “by” parameter;

Java Heap size - R

I recently came across this error "java.lang.OutOfMemoryError: Java heap space" when accessing large data sets from Mongodb with the RMongo package. 

Try to increase java Heap size(sufficient), by using:

options(java.parameters = "-Xmx3g")Make sure that you are setting the Java parameters before any JVM is initialized, i.e. before packages are loaded.

That means on the R shell run

First: > options(java.parameters = "-Xmx3g")

Second: >  RMongo 

R snippets of Week

This week i came across some interesting snippets to get me around and i'll back them up here.

Subset by condition:

resultDF <- result[ result$actingUserProfileId %in% c(1, 2, 3, 4, 5), ]

Subset by dates:

subset(resultsDF, as.Date(Date) >= '20013-06-01' & as.Date(Date) <= '2013-06-30')

Subset Regex:

weekly_ke <- weekly_regs[grep("+254", weekly_regs$dialCode),] 

Convert to R dates:

posDate <- as.POSIXct (d, tz = 'EAT', format = "% a% b% d% H:% M:%S +0000 %Y")

Bugged!! but working. Timestamp to R dates e.g How would you convert this to readable R dates "Mon Jan 20 10:12:59 EAT 2013" 

as.Date(createdAt,format="%a %b %d %H:%M:%S")

Plotting two or more lines using ggplot2

How would you go about with plotting 2 or more line graphs in the same graph with ggplot. There are simpler ways with plot() but I wanted to use ggplot2 because it's way cooler and i use melted data. But what about if your data is not melted, like my data below ? From the previous blog i’m using the same data frame so just follow along.

Dates Registrations.x Registrations.y
1   2013-01-01               7              21
2   2013-01-02              15              36
3   2013-01-03              13              23
4   2013-01-04              20              16
5   2013-01-05              14              28
6   2013-01-06              17              22

df <- data.frame(
 Registrations.x = c(7,15,13,20,14,17),
 Registrations.y = c(21,36,23,16,28,22))

also add:-
> df$Dates <- c("2013-01-01", "2013-01-02", "2013-01-03", "2013-01-04", "2013-01-05", "2013-01-06")

Now go ahead and run the following packages to make sure they are running

>require("reshape")
>require("ggplot2")

Conver to long format by melting the data frame.

>test_df <- melt(df, id="Dates")  

>ggplot(data=test_df, aes(x=Dates, y=value, colour=variable)) + geom_line()

I hit errors with “geom_path: Each group consist of only one observation. Do you need to adjust the group aesthetic?” - i then dug a little deeper online and found out the solution is to add a group. The error message indicates that you need to specifie a group. Personally  message indicates almost nothing, as always in R :(. So i edited my plot with code below.

>ggplot(data=test_df, aes(x=Dates, y=value, colour=variable, group=variable)) + geom_line()

Bang, it then works like a charm?

rseek.org

The R Search engine!

Stacked graphs “unmelted” data with ggplot2

I recently had a problem that took lots of my time trying to figure out the solutions. I had wanted to make a stacked bar chart in ggplot2. I know how to make one with barplot(), but I wanted to use ggplot2 because it's very easy to make the bars and use melted data. But what about if your data is not melted, like my data below ?

Dates Registrations.x Registrations.y
1   2013-01-01               7              21
2   2013-01-02              15              36
3   2013-01-03              13              23
4   2013-01-04              20              16
5   2013-01-05              14              28
6   2013-01-06              17              22

df <- data.frame(
 Registrations.x = c(7,15,13,20,14,17),
 Registrations.y = c(21,36,23,16,28,22))

also add:-
> df$Dates <- c("2013-01-01", "2013-01-02", "2013-01-03", "2013-01-04", "2013-01-05", "2013-01-06")

What I want is a plot with categories where the Dates are on the X axis, and for each of those, the values for different registration stats  stacked on top of each other on the Y axis. Most graphs and examples with R that I have seen plot only one variable on the Y axis, so this how to go about with a different way using ggplot.

First, we need to do some data manipulation by adding a different category as a variable and melting the data to long format. WTF is melting data?

row.names(df) <- df$Dates
mdf <- melt(df, id.vars = "Dates")

fyi - incase you get errors like “Error: could not find function "melt"” make sure that you got the “reshape” package installed and to do that run the command below.

>install.packages("reshape")

>library(reshape)

for more details you find out more with ?melt() on R prompt.

Now we can plot the stacked bar, using the variable named “Dates” to determine the fill colour of each bar.

g <- ggplot(mdf, aes(Dates, y=value, fill = variable)) + geom_bar(stat="identity")

RMySQL on Ubuntu 12.04

At our little start-up we run our system on MySQL and i constantly want to play around with data to have an overview of how users are registering, searching etc As you might have noticed from my previous blogs, i'm sticking to R for all my analyses so even MySQL will be accessed from R so reason why i need to install RMySQL

I kicked off my R tool by typing R at the prompt and running.

> install.packages(“RMySQL”)

This loads and throws me these errors

“ERROR: configuration failed for package ‘RMySQL’ ...”

I looked up online and there seems to be a dependency for just a linux package "libmysqlclient-dev" which contains MySQL so i went ahead an installed that too.

$sudo apt-get install libmysqlclient-dev

$sudo apt-get install r-cran-rmysql

then finally go back to the R shell

> install.packages(“RMySQL”)

and woohooo that runs smoothly on my Ubuntu 12.04 Linux Machine.

How to query MongoDB from R?

At my little start-up we use Mongodb for stats and this is awesome for me. I’m quite new to Mongo though when i worked at Google Inc. i used something called Big table that kind of works the same. As you might have noticed i’m highly using R for all my analyses and that’s how i want to keep it.  

I recently came across RMongo, a database access layer to MongoDB in R as an R package. I’m assuming that you already have R and Mongo installed otherwise you might want to go through my previous tutorials on installations of R or Mongo.

To install RMongo:

>install.packages(“RMongo”)

If that does not work, try downloading it from http://cran.r-project.org/web/packages/RMongo/index.html and run:

install.packages("~/Downloads/RMongo_XX.XX.XX.tar.gz", repos=NULL, type="source")

The Data Querying?

Now from within R, this is how to connect to a local MongoDB. For those with remote databases please know that the mongoDbConnect function takes some additional arguments . 

Run the command below to know these arguments and what they mean.

> library("RMongo")

>?mongoDbConnect()

and you will be presented with a number of various arguments to use.

If you are new to Mongo you might want to go through this wonderful tutorial by nettuts here title “Getting started with mongodb”. This tutorial is awesome though got some few typos!

I did put my database into a test db, so this is how i connect to the db called "test"
> the_mongo  <- mongoDbConnect("test")

Also run

>?dbGetQuery()

> the_result <- dbGetQuery(the_mongo, "nettuts", "", 0, 10)

> the_result

It’s also possible to use more complex queries to extract data.

The main query function takes five arguments as shown by the:

>?dbGetQuery()

• database connection
• collection name
• query
• skip - how many objects to skip
• limit - total number of objects to return

For example, extract all fields from the “nettuts” collection where age is greater than or equal to 47:

> some_result <- dbGetQuery(the_mongo, "nettuts", "{'age' : { '$gte' : 47}}")