Yet Another Blog in Statistical Computing

I can calculate the motion of heavenly bodies but not the madness of people. -Isaac Newton

Efficiecy of Extracting Rows from A Data Frame in R

In the example below, 552 rows are extracted from a data frame with 10 million rows using six different methods. Results show a significant disparity between the least and the most efficient methods in terms of CPU time. Similar to the finding in my previous post, the method with data.table package is the most efficient solution with 0.64s CPU time. Albeit user-friendly, the method with sqldf() is the least efficient solution with 82.27s CPU time.

> # SIMULATE A DATA.FRAME WITH 10,000,000 ROWS
> set.seed(2013)
> df <- data.frame(x1 = rpois(10000000, 1), x2 = rpois(10000000, 1), x3 = rpois(10000000, 1))
> 
> # METHOD 1: EXTRACT ROWS WITH LOGICAL SUBSCRIPTS
> system.time(set1 <- df[df$x1 == 4 & df$x2 > 4 & df$x3 < 4,])
   user  system elapsed 
  1.484   1.932   3.640 
> dim(set1)
[1] 552   3
> 
> # METHOD 2: EXTRACT ROWS WITH ROW INDEX
> system.time(set2 <- df[which(df$x1 == 4 & df$x2 > 4 & df$x3 < 4),])
   user  system elapsed 
  0.856   1.200   2.197 
> dim(set2)
[1] 552   3
> 
> # METHOD 3: EXTRACT ROWS WITH SUBSET() 
> system.time(set3 <- subset(df, x1 == 4 & x2 > 4 & x3 < 4))
   user  system elapsed 
  1.680   2.644   4.690 
> dim(set3)
[1] 552   3
> 
> # METHOD 4: EXTRACT ROWS WITH SQLDF() 
> require(sqldf)
> system.time(set4 <- sqldf("select * from df where x1 = 4 and x2 > 4 and x3 < 4", row.names = TRUE))
   user  system elapsed 
 82.269  13.733  98.943 
> dim(set4)
[1] 552   3
> 
> # METHOD 5: EXTRACT ROWS WITH SQL.SELECT()
> source("http://sqlselect.googlecode.com/svn/trunk/sql.select.R")
> system.time(set5 <- sql.select("select * from df where `x1 == 4 & x2 > 4 & x3 < 4`"))
   user  system elapsed 
  2.800   3.152   7.107 
> dim(set5)
[1] 552   3
> 
> # METHOD 6: EXTRACT ROWS WITH DATA.TABLE PACKAGE 
> require(data.table)
> dt <- data.table(df)
> system.time(set6 <- dt[dt$x1 == 4 & dt$x2 > 4 & dt$x3 < 4,])
   user  system elapsed 
  0.636   0.000   0.655 
> dim(set6)
[1] 552   3
Advertisements

Written by statcompute

January 1, 2013 at 9:23 pm

Posted in S+/R

Tagged with

%d bloggers like this: