R: Deriving a new data frame column based on containing string
I’ve been playing around with R data frames a bit more and one thing I wanted to do was derive a new column based on the text contained in the existing column.
I started with something like this:
1.> x = data.frame(name = c("Java Hackathon", "Intro to Graphs", "Hands on Cypher"))2.> x3.name4.1 Java Hackathon5.2 Intro to Graphs6.3 Hands on Cypher
And I wanted to derive a new column based on whether or not the session was a practical one. The grepl function seemed to be the best tool for the job:
1.> grepl("Hackathon|Hands on|Hands On", x$name)2.[1] TRUE FALSE TRUE
We can then add a column to our data frame with that output:
1.x$practical = grepl("Hackathon|Hands on|Hands On", x$name)
And we end up with the following:
1.> x2.name practical3.1 Java Hackathon TRUE4.2 Intro to Graphs FALSE5.3 Hands on Cypher TRUE
Not too tricky but it took me a bit too long to figure it out so I thought I’d save future Mark some time!
How to Import Some Parts of a Large Database
In the introduction of Computational Actuarial Science with R, there was a short paragraph on how could we import only some parts of a large database, by selecting specific variables. The trick was to use the following
01.> read.table.select.columns=function(datatablename,02.I,sep=";"){03.+ datanc=read.table(datatablename,header=TRUE,04.sep=sep,skip=0,nrows=1)05.+ mycols=rep("NULL",ncol(datanc))06.+ names(mycols)=names(datanc)07.+ mycols[I]=NA08.+ datat=read.table(datatablename,header=TRUE,09.sep=sep,colClasses=mycols)10.+ return(datat)}
For instance, if we use the same dataset as in the introduction, we can import only two variables of interest,
02.> dt1=read.table.select.columns(loc,c("Region",03."Wmax"),sep=",")04.> head(dt1,10)05.Region Wmax06.1 Basin 105.5634207.2 Basin 40.0000008.3 Basin 35.4182209.4 Basin 51.0674310.5 Florida 87.3432811.6 Basin 96.6413812.7 Gulf 35.4182213.8 US 35.4182214.9 US 87.3432815.10 US 106.3531816.> dim(dt1)17.[1] 2100 2
In other cases, it might be interesting to select some raws, or to avoid some of them (e.g. because of some typos in the original dataset). If we want to drop some specific raws, we can use
01.> read.table.drop.rows=function(datatablename,02.I,sep=";"){03.+ I=sort(I)04.+ if(min(I)>1) minI=105.+ if(min(I)==1) minI=NULL06.+ index1=c(minI,I[c(which(diff(I)>1),length(I))]+1)07.+ index2=c(I[c(minI,which(diff(I)>1)+1)],08.max(index1)-1)09.+ datat=read.table(datatablename,header=TRUE,10.sep,skip=0,nrows=1)11.+ datat=datat[-1,]12.+ for(i in 1:length(index1)){13.+ datat0=read.table(datatablename,header=FALSE,14.sep=sep,skip=index1[i],nrows=index2[i]-index1[i])15.+ names(datat0)=names(datat)16.+ datat=rbind(datat,datat0)17.+ }18.+ return(datat)}
On the same dataset, assume that we have some troubles reading some lines, or we know that values are not valid,
01.> dt2=read.table.drop.rows(loc,c(3,6:8),sep=",")02.> head(dt2[,1:5],10)03.Yr Region Wmax sst sun04.1 1899 Basin 105.56342 0.046596 8.405.2 1899 Basin 40.00000 0.046596 8.406.3 1899 Basin 51.06743 0.046596 8.407.4 1899 Florida 87.34328 0.046596 8.408.5 1899 US 87.34328 0.046596 8.409.6 1899 US 106.35318 0.046596 8.410.7 1899 US 51.06743 0.046596 8.411.8 1899 US 90.19791 0.046596 8.412.9 1899 Basin 56.48593 0.046596 8.413.10 1899 Basin 131.26902 0.046596 8.414.> dim(dt2)15.[1] 2096 11
Now, we can try to do both at the same time (more or less) : for some specific variables, we want to import a subpart of the database, but we also want to avoid some specific lines,
01.> read.table.select.columns.rows=function(02.datatablename,Ic,Ir,sep=";"){03.+ datanc=read.table(datatablename,header=TRUE,04.sep=sep,skip=0,nrows=1)05.+ mycols=rep("NULL",ncol(datanc))06.+ names(mycols)=names(datanc)07.+ mycols[Ic]=NA08.+ I=sort(Ir)09.+ if(min(I)>1) minI=110.+ if(min(I)==1) minI=NULL11.+ index1=c(minI,I[c(which(diff(I)>1),length(I))]+1)12.+ index2=c(I[c(minI,which(diff(I)>1)+1)],13.max(index1)-1)14.+ datat=read.table(datatablename,header=TRUE,15.sep=sep,skip=0,nrows=1,colClasses=mycols)16.+ datat=datat[-1,]17.+ for(i in 1:length(index1)){18.+ datat0=read.table(datatablename,header=FALSE,19.sep=sep,skip=index1[i],nrows=index2[i]-index1[i],colClasses=mycols)20.+ names(datat0)=names(datat)21.+ datat=rbind(datat,datat0)22.+ }23.+ return(datat)}24.> dt3=read.table.select.columns.rows(loc,25.c("Wmax","Region"),c(3,6:8),sep=",")26.> head(dt3)27.Region Wmax28.1 Basin 105.5634229.2 Basin 40.0000030.3 Basin 51.0674331.4 Florida 87.3432832.5 US 87.3432833.6 US 106.3531834.> dim(dt3)35.[1] 2096 2
One can observe that it is the same, here, as
01.> df=read.table(loc,header=TRUE,sep=",")02.> sdf=df[-c(3,6:8),c("Wmax","Region")]03.> head(sdf)04.Wmax Region05.1 105.56342 Basin06.2 40.00000 Basin07.4 51.06743 Basin08.5 87.34328 Florida09.9 87.34328 US10.10 106.35318 US11.> dim(sdf)12.[1] 2096 2
But if the dataset was much larger (with thousands of variables) with also some problems on specific lines, we now have a nice code to import our database.
R: Filtering data frames by column type ('x' must be numeric)
I’ve been working through the exercises from An Introduction to Statistical Learning and one of them required you to create a pair wise correlation matrix of variables in a data frame.
The exercise uses the ‘Carseats’ data set which can be imported like so:
01.> install.packages("ISLR")02.> library(ISLR)03.> head(Carseats)04.Sales CompPrice Income Advertising Population Price ShelveLoc Age Education Urban US05.1 9.50 138 73 11 276 120 Bad 42 17 Yes Yes06.2 11.22 111 48 16 260 83 Good 65 10 Yes Yes07.3 10.06 113 35 10 269 80 Medium 59 12 Yes Yes08.4 7.40 117 100 4 466 97 Medium 55 14 Yes Yes09.5 4.15 141 64 3 340 128 Bad 38 13 Yes No10.6 10.81 124 113 13 501 72 Bad 78 16 No Yes
filter the categorical variables from a data frame and
If we try to run the ‘cor‘ function on the data frame we’ll get the following error:
1.> cor(Carseats)2.Error in cor(Carseats) : 'x' must be numeric
As the error message suggests, we can’t pass non numeric variables to this function so we need to remove the categorical variables from our data frame.
But first we need to work out which columns those are:
1.> sapply(Carseats, class)2.Sales CompPrice Income Advertising Population Price ShelveLoc Age Education3."numeric" "numeric" "numeric" "numeric" "numeric" "numeric" "factor" "numeric" "numeric"4.Urban US5."factor" "factor"
We can see a few columns of type ‘factor’ and luckily for us there’s a function which will help us identify those more easily:
1.> sapply(Carseats, is.factor)2.Sales CompPrice Income Advertising Population Price ShelveLoc Age Education3.FALSE FALSE FALSE FALSE FALSE FALSE TRUE FALSE FALSE4.Urban US5.TRUE TRUE
Now we can remove those columns from our data frame and create the correlation matrix:
01.> cor(Carseats[sapply(Carseats, function(x) !is.factor(x))])02.Sales CompPrice Income Advertising Population Price Age Education03.Sales 1.00000000 0.06407873 0.151950979 0.269506781 0.050470984 -0.44495073 -0.231815440 -0.05195524204.CompPrice 0.06407873 1.00000000 -0.080653423 -0.024198788 -0.094706516 0.58484777 -0.100238817 0.02519705005.Income 0.15195098 -0.08065342 1.000000000 0.058994706 -0.007876994 -0.05669820 -0.004670094 -0.05685542206.Advertising 0.26950678 -0.02419879 0.058994706 1.000000000 0.265652145 0.04453687 -0.004557497 -0.03359430707.Population 0.05047098 -0.09470652 -0.007876994 0.265652145 1.000000000 -0.01214362 -0.042663355 -0.10637823108.Price -0.44495073 0.58484777 -0.056698202 0.044536874 -0.012143620 1.00000000 -0.102176839 0.01174659909.Age -0.23181544 -0.10023882 -0.004670094 -0.004557497 -0.042663355 -0.10217684 1.000000000 0.00648803210.Education -0.05195524 0.02519705 -0.056855422 -0.033594307 -0.106378231 0.01174660 0.006488032 1.000000000
Be Sociable, Share!
R: ggplot - Plotting multiple variables on a line chart
In my continued playing around with meetup data I wanted to plot the number of members who join the Neo4j group over time.
I started off with the variable ‘byWeek’ which shows how many members joined the group each week:
> head(byWeek)
Source: local data frame [6 x 2]
week n
1 2011-06-02 8
2 2011-06-09 4
3 2011-06-30 2
4 2011-07-14 1
5 2011-07-21 1
6 2011-08-18 1
I wanted to plot the actual count alongside a rolling average for which I created the following data frame:
library(zoo)
joinsByWeek = data.frame(actual = byWeek$n,
week = byWeek$week,
rolling = rollmean(byWeek$n, 4, fill = NA, align=c("right")))
> head(joinsByWeek, 10) actual week rolling 1 8 2011-06-02 NA 2 4 2011-06-09 NA 3 2 2011-06-30 NA 4 1 2011-07-14 3.75 5 1 2011-07-21 2.00 6 1 2011-08-18 1.25 7 1 2011-10-13 1.00 8 2 2011-11-24 1.25 9 1 2012-01-05 1.25 10 3 2012-01-12 1.75
The next step was to work out how to plot both ‘rolling’ and ‘actual’ on the same line chart. The easiest way is to make two calls to ‘geom_line’, like so:
ggplot(joinsByWeek, aes(x = week)) +
geom_line(aes(y = rolling), colour="blue") +
geom_line(aes(y = actual), colour = "grey") +
ylab(label="Number of new members") +
xlab("Week")
Alternatively we can make use of the ‘melt’ function from the reshape library…
library(reshape) meltedJoinsByWeek = melt(joinsByWeek, id = 'week')
> head(meltedJoinsByWeek, 20) week variable value 1 1 actual 8 2 2 actual 4 3 3 actual 2 4 4 actual 1 5 5 actual 1 6 6 actual 1 7 7 actual 1 8 8 actual 2 9 9 actual 1 10 10 actual 3 11 11 actual 1 12 12 actual 2 13 13 actual 4 14 14 actual 2 15 15 actual 3 16 16 actual 5 17 17 actual 1 18 18 actual 2 19 19 actual 1 20 20 actual 2
…which then means we can plot the chart with a single call to geom_line:
ggplot(meltedJoinsByWeek, aes(x = week, y = value, colour = variable)) +
geom_line() +
ylab(label="Number of new members") +
xlab("Week Number") +
scale_colour_manual(values=c("grey", "blue"))
I ennjoyed reading this
ReplyDelete