CODEWITHSUNDEEP
rdata.table
digdeep
digdeep

Reputation: 624

R data.table subsetting on multiple conditions.

With the below data set, how do I write a data.table call that subsets this table and returns all customer ID's and associated orders for that customer IF that customer has ever purchased SKU 1?

Expected result should return a table that excludes cid 3 and 5 on that condition and every row for customers matching sku==1.

I am getting stuck as I don't know how to write a "contains" statement, == literal returns only sku's matching condition... I am sure there is a better way..

library("data.table")    
df<-data.frame(cid=c(1,1,1,1,1,2,2,2,2,2,3,4,5,5,6,6),
    order=c(1,1,1,2,3,4,4,4,5,5,6,7,8,8,9,9),
    sku=c(1,2,3,2,3,1,2,3,1,3,2,1,2,3,1,2))

    dt=as.data.table(df)

Upvotes: 6

Views: 14653

Answers (2)

Simon O&#39;Hanlon
Simon O&#39;Hanlon

Reputation: 60000

I would have thought that it was more (?!) data.table to use keys. I couldn't quite work out how to stick the whole lot on a single line, but I think that this would be a bit quicker on large data, because as I understand it (and I may very well be mistaken) this is the only solution presented thus far that avoids vector scanning (which is slow compared to binary search):

#  Set initial key
setkey(dt,sku)

#  Select only rows with 1 in the sku and return first example of each, setting key to customer id
dts <- dt[ J(1) , .SD[1] , keyby = cid ]

#  change key of dt to cid to match customer id
setkey(dt,cid)

#  join based on common key
dt[dts,.SD]
#    cid order sku
# 1:   1     1   1
# 2:   1     1   2
# 3:   1     2   2
# 4:   1     1   3
# 5:   1     3   3
# 6:   2     4   1
# 7:   2     5   1
# 8:   2     4   2
# 9:   2     4   3
#10:   2     5   3
#11:   4     7   1
#12:   6     9   1
#13:   6     9   2

An alternative that you can do on one line is to use a data.table merge like so...

setkey(dt,sku)
merge( dt[ J(1) , .SD[1] , keyby = cid ] , dt , by = "cid" )

Upvotes: 3

Peter Fine
Peter Fine

Reputation: 2923

This is similar to a previous answer, but here the subsetting works in a more data.table like manner.

First, lets take the cids that meet our condition:

matching_cids = dt[sku==1, cid]

the %in% operator allows us to filter to just those items that are contained in the list. so, using the above:

dt[cid %in% matching_cids]

or on one line:

> dt[cid %in% dt[sku==1, cid]]
     cid order sku
  1:   1     1   1
  2:   1     1   2
  3:   1     1   3
  4:   1     2   2
  5:   1     3   3
  6:   2     4   1
  7:   2     4   2
  8:   2     4   3
  9:   2     5   1
 10:   2     5   3
 11:   4     7   1
 12:   6     9   1
 13:   6     9   2

Upvotes: 9

Related Questions