R help - Nov 2011 - Data Frame Search Slow

Hey All,

So - I promise to write a blog post on this topic and post it somewhere on
the internet once I get to the bottom of this.  Basically, the set-up to the
problem is like this:

1.  I have a data frame with dim (2547290, 4)
2.  I need to make SQL like lookups on the dataframe.  I have been using the
following sort of syntax:

a.dataframe[a.dataframe[[column_index]] %in% some_value, ]

3.  This process takes quite a lot of time (~2 seconds) on m1.small
instances AMIs (AWS)

So, I hope I can get that look-up/search logic quite a lot faster.  I have
heard that using matrices is the way to do it but I haven't found any
resources on performing that sort of operation specifically that have
yielded better results.  

Thought, feelings and advice are more than welcome.

Best,
TMD

--
View this message in context:
http://r.789695.n4.nabble.com/Data-Frame-Search-Slow-tp4096906p4096906.html
Sent from the R help mailing list archive at Nabble.com.

take a look at using the 'data.table' package.  Here are some times to
do the lookup using dataframes, matrices and data.tables:  data.tables
give the answer is less than 0.1 seconds.
> str(x.df)
'data.frame':   2500000 obs. of  4 variables:
 $ x  : Factor w/ 455063 levels "AAAA","AAAB",..: 200683
388992 241029
305994 209907 112469 105656 233058 247529 416273 ...
 $ x.1: Factor w/ 455063 levels "AAAA","AAAB",..: 200683
388992 241029
305994 209907 112469 105656 233058 247529 416273 ...
 $ x.2: Factor w/ 455063 levels "AAAA","AAAB",..: 200683
388992 241029
305994 209907 112469 105656 233058 247529 416273 ...
 $ x.3: Factor w/ 455063 levels "AAAA","AAAB",..: 200683
388992 241029
305994 209907 112469 105656 233058 247529 416273 ...
> system.time(a <- x.df[[1]] %in% "AAAA")
   user  system elapsed
   0.33    0.00    0.39
> x.m <- as.matrix(x.df)
> str(x.m)
 chr [1:2500000, 1:4] "LMDC" "WFXC" "NUBQ"
"RMOK" "LZVR" "GLCE" "GAZE"
"NIFT" ...
 - attr(*, "dimnames")=List of 2
  ..$ : NULL
  ..$ : chr [1:4] "x" "x.1" "x.2"
"x.3"
> system.time(a <- x.m[,1] %in% "AAAA")
   user  system elapsed
   0.50    0.00    0.51
> require(data.table)
> x.df <- data.table(x.df)
> setkey(x.df, x)
> system.time(a <- x.df["AAAA"])
   user  system elapsed
   0.05    0.03    0.13
> str(a)
Classes ?data.table? and 'data.frame':  7 obs. of  4 variables:
 $ x  : Factor w/ 1 level "AAAA": 1 1 1 1 1 1 1
 $ x.1: Factor w/ 455063 levels "AAAA","AAAB",..: 1 1 1 1 1
1 1
 $ x.2: Factor w/ 455063 levels "AAAA","AAAB",..: 1 1 1 1 1
1 1
 $ x.3: Factor w/ 455063 levels "AAAA","AAAB",..: 1 1 1 1 1
1 1
 - attr(*, "sorted")= chr "x"
> system.time(x.df["ABCD"])
   user  system elapsed
   0.08    0.02    0.16
>
On Tue, Nov 22, 2011 at 2:01 PM, TimothyDalbey <tmdalbey at gmail.com>
wrote:
> Hey All,
>
> So - I promise to write a blog post on this topic and post it somewhere on
> the internet once I get to the bottom of this. ?Basically, the set-up to
the
> problem is like this:
>
> 1. ?I have a data frame with dim (2547290, 4)
> 2. ?I need to make SQL like lookups on the dataframe. ?I have been using
the
> following sort of syntax:
>
> a.dataframe[a.dataframe[[column_index]] %in% some_value, ]
>
> 3. ?This process takes quite a lot of time (~2 seconds) on m1.small
> instances AMIs (AWS)
>
> So, I hope I can get that look-up/search logic quite a lot faster. ?I have
> heard that using matrices is the way to do it but I haven't found any
> resources on performing that sort of operation specifically that have
> yielded better results.
>
> Thought, feelings and advice are more than welcome.
>
> Best,
> TMD
>
> --
> View this message in context:
http://r.789695.n4.nabble.com/Data-Frame-Search-Slow-tp4096906p4096906.html
> Sent from the R help mailing list archive at Nabble.com.
>
> ______________________________________________
> R-help at r-project.org mailing list
> https://stat.ethz.ch/mailman/listinfo/r-help
> PLEASE do read the posting guide
http://www.R-project.org/posting-guide.html
> and provide commented, minimal, self-contained, reproducible code.
>


-- 
Jim Holtman
Data Munger Guru

What is the problem that you are trying to solve?
Tell me what you want to do, not how you want to do it.

Update from email outside of this thread:

Justin Haynes writes:

> matrices will help, but two quick solutions:
> 
> if you are looking for single items to go in the some_value space, use
=> instead of %in% and you'll notice speedups.  The second more involved
> option is to take a look at the package data.table.
> 
> it provides a wrapper for data.frame that has some impressive
> optimizations as well as allowing for SQL like indexing and syntax.
> 
> 
> hope that helps!
> 
Justin, thanks.  Here's the before and after clock:

Before: 

user  system elapsed 
  0.976   0.164   2.607 

After:

user  system elapsed 
  0.624   0.156   1.810 

You are correct.  I still need to get that elapsed down even more. 
Data.table sounds like a good option.  I'll look into it more.  Thank you.

--
View this message in context:
http://r.789695.n4.nabble.com/Data-Frame-Search-Slow-tp4096906p4097261.html
Sent from the R help mailing list archive at Nabble.com.

Wow, these specs are fantastic:


> user  system elapsed
>    0.33    0.00    0.39 
> 
I wonder how much of that is because of the capacity of the box that you are
running R on.  Can you post pertinent specs?  This suggest to me that
hardware upgrades (RAM specifically) may also be in order.

Investigating data.table now.

Thanks!

--
View this message in context:
http://r.789695.n4.nabble.com/Data-Frame-Search-Slow-tp4096906p4097278.html
Sent from the R help mailing list archive at Nabble.com.

So, here is the result time from using the datatable package:


> user  system elapsed 
>   0.800   0.012   1.847
> 
Here are the methods that I am using:

ush <- data.table(read.csv(...))
setkey(ush, product_id)
s1 <- subset(ush, product_id == product.id)

Seems like a minor improvement but not enough to get those subsets in less
than ~2 seconds.  Am I doing something wrong I wonder...


--
View this message in context:
http://r.789695.n4.nabble.com/Data-Frame-Search-Slow-tp4096906p4097441.html
Sent from the R help mailing list archive at Nabble.com.