Efficiecy of Extracting Rows from A Data Frame in R

January 1, 2013
By

(This article was first published on Yet Another Blog in Statistical Computing » S+/R, and kindly contributed to R-bloggers)

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

To leave a comment for the author, please follow the link and comment on their blog: Yet Another Blog in Statistical Computing » S+/R.

R-bloggers.com offers daily e-mail updates about R news and tutorials on topics such as: Data science, Big Data, R jobs, visualization (ggplot2, Boxplots, maps, animation), programming (RStudio, Sweave, LaTeX, SQL, Eclipse, git, hadoop, Web Scraping) statistics (regression, PCA, time series, trading) and more...



If you got this far, why not subscribe for updates from the site? Choose your flavor: e-mail, twitter, RSS, or facebook...

Comments are closed.

Sponsors

Mango solutions



RStudio homepage



Zero Inflated Models and Generalized Linear Mixed Models with R

Dommino data lab

Quantide: statistical consulting and training



http://www.eoda.de







ODSC

ODSC

CRC R books series





Six Sigma Online Training





Contact us if you wish to help support R-bloggers, and place your banner here.

Never miss an update!
Subscribe to R-bloggers to receive
e-mails with the latest R posts.
(You will not see this message again.)

Click here to close (This popup will not appear again)