Create sequence with data.table

Question

I have a data.table of the format

id | pet   | name  
2011-01-01 | "dog" | "a"  
2011-01-02 | "dog" | "b"  
2011-01-03 | "cat" | "c"  
2011-01-04 | "dog" | "a"  
2011-01-05 | "dog" | "some"   
2011-01-06 | "cat" | "thing"

I want to perform an aggregate that concatenates all the dog names that appear before the cat occurs e.g.,

id | pet   | name   | prior  
2011-01-01 | "dog" | "a"     |  
2011-01-02 | "dog" | "b"     |  
2011-01-03 | "cat" | "c"     |  "a b"  
2011-01-04 | "dog" | "a"     |  
2011-01-05 | "dog" | "some"  |  
2011-01-06 | "cat" | "thing" | "a some"

Please don't "extend the problem" here. If you have a new problem, you should probably post it as a new question, preferably with a small easily reproducible example http://stackoverflow.com/a/28481250/1191259 — Frank, Jun 11 '15 at 15:09

score 3 · Accepted Answer · edited Jun 20 '20 at 09:12

3

Try

 library(data.table)#v1.9.5+
 setDT(df1)[, prior:= paste(name[1:(.N-1)], collapse=' ') ,
    .(group=cumsum(c(0,diff(pet=='cat'))<0))][pet!='cat',  prior:= '']
 #            id pet  name  prior
 #1: 2011-01-01  dog     a       
 #2: 2011-01-02  dog     b       
 #3: 2011-01-03  cat     c    a b
 #4: 2011-01-04  dog     a       
 #5: 2011-01-05  dog  some       
 #6: 2011-01-06  cat thing a some

Or a possible solution with shift (introduced in the devel version ie. v1.9.5), inspired from @David Arenburg's post. Instructions to install the devel version are here.

 setDT(df1)[, prior := paste(name[-.N], collapse= ' '), 
    .(group=cumsum(shift(pet, fill='cat')=='cat'))][pet!='cat', prior := '']

data

df1 <- structure(list(id = c("2011-01-01 ", "2011-01-02 ", "2011-01-03 ", 
 "2011-01-04 ", "2011-01-05 ", "2011-01-06 "), pet = c("dog", 
"dog", "cat", "dog", "dog", "cat"), name = c("a", "b", "c", "a", 
"some", "thing")), .Names = c("id", "pet", "name"), row.names = c(NA, 
-6L), class = "data.frame")

edited Jun 20 '20 at 09:12

Community

1
1

answered Jun 10 '15 at 22:44

akrun

874,273
37
540
662

Works! Thanks a lot. I will do some benchmarking with my problem to compare the solutions. – Stereo Jun 10 '15 at 23:35
1

@Anton, just of curiosity, my answer didn't work too? Or you just can't be bothered to provide some feedback for the free help you've got here? – David Arenburg Jun 11 '15 at 06:38
@DavidArenburg I think the OP intend to compare the solution in both posts (though not sure) – akrun Jun 11 '15 at 06:42
1

If your second solution wins, I'm taking credit for 7.5 points :) – David Arenburg Jun 11 '15 at 06:43
@davidArenburg I will test your solution too. – Stereo Jun 11 '15 at 13:04

score 1 · Answer 2 · answered Jun 10 '15 at 22:52

Here's another option

indx <- setDT(DT)[, list(.I[.N], paste(name[-.N], collapse = ' ')), 
                    by = list(c(0L, cumsum(pet == "cat")[-nrow(DT)]))]
DT[indx$V1, prior := indx$V2]
DT
#            id pet  name  prior
# 1: 2011-01-01 dog     a     NA
# 2: 2011-01-02 dog     b     NA
# 3: 2011-01-03 cat     c    a b
# 4: 2011-01-04 dog     a     NA
# 5: 2011-01-05 dog  some     NA
# 6: 2011-01-06 cat thing a some

Stereo · Answer 3 · 2015-06-11T14:45:28.840

I ran each solution on my data set and compared the run times with rbenchmark.

I cannot share the data set but here some basic info:

dim(event_source_causal_parts)
[1] 311127      4

The code for the comparison,

require(rbenchmark)
benchmark({
  event_source_causal_parts <- augmented_data_no_software[, list(PROD_ID, Source, Event_Date, Causal_Part_Number)] 
  setDT(event_source_causal_parts)[, prior := paste(Causal_Part_Number[-.N], collapse = ' '), .(group=cumsum(c(0,diff(Source == "Warranty")) < 0))][Source != 'Warranty', prior := '']
 })

benchmark({
  event_source_causal_parts <- augmented_data_no_software[, list(PROD_ID, Source, Event_Date, Causal_Part_Number)] 
  setDT(event_source_causal_parts)[, prior := paste(Causal_Part_Number[-.N], collapse = ' '), .(group=cumsum(shift(Source, fill="Warranty") == "Warranty"))][Source != 'Warranty', prior := ''] 
  })


benchmark({
  event_source_causal_parts <- augmented_data_no_software[, list(PROD_ID, Source, Event_Date, Causal_Part_Number)] 
  indx <- setDT(event_source_causal_parts)[, list(.I[.N], paste(Causal_Part_Number[-.N], collapse = " ")),
                                       by = list(c(0L, cumsum(Source == "Warranty")[-nrow(event_source_causal_parts)]))]
})

The outcome are as follows,

  replications elapsed relative user.self sys.self user.child sys.child
1          100   12.91        1     12.76     0.05         NA        NA

  replications elapsed relative user.self sys.self user.child sys.child
1          100    12.7        1     12.66     0.05         NA        NA

  replications elapsed relative user.self sys.self user.child sys.child
1          100   61.97        1     61.65        0         NA        NA

my environment,

R version 3.1.2 (2014-10-31)
Platform: x86_64-w64-mingw32/x64 (64-bit)

locale:
[1] LC_COLLATE=English_United States.1252  LC_CTYPE=English_United States.1252   
[3] LC_MONETARY=English_United States.1252 LC_NUMERIC=C                          
[5] LC_TIME=English_United States.1252    

attached base packages:
[1] stats     graphics  grDevices utils     datasets  methods   base     

other attached packages:
[1] rbenchmark_1.0.0 stringr_0.6.2    data.table_1.9.5 vimcom_1.2-6    

loaded via a namespace (and not attached):
[1] chron_2.3-45    grid_3.1.2      lattice_0.20-30 tools_3.1.2     zoo_1.7-11

R used the Intel MKL math libraries.

Based on these results I think that @akrun 's second solution is the fastest.

I ran the test again but now I recompiled data.table with -O3 and updated R to 3.2.0. The results are very different:

  replications elapsed relative user.self sys.self user.child sys.child
1          100   21.22        1     20.73     0.48         NA        NA

  replications elapsed relative user.self sys.self user.child sys.child
1          100   11.31        1     10.39     0.92         NA        NA

  replications elapsed relative user.self sys.self user.child sys.child
1          100   35.77        1     35.53     0.25         NA        NA

So the best solution is even faster under new R with O3 but the second best solution is much slower.

Create sequence with data.table

3 Answers3

data