# Efficiecy of Extracting Rows from A Data Frame in R

[This article was first published on

Want to share your content on R-bloggers? click here if you have a blog, or here if you don't.

**Yet Another Blog in Statistical Computing » S+/R**, and kindly contributed to R-bloggers]. (You can report issue about the content on this page here)Want to share your content on R-bloggers? click here if you have a blog, or here if you don't.

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 about learning R and many other topics. Click here if you're looking to post or find an R/data-science job.

Want to share your content on R-bloggers? click here if you have a blog, or here if you don't.