CODEWITHSUNDEEP
rdata.table
badmax
badmax

Reputation: 645

How to join data.table by one column of multi-column key

Suppose I have a data.table with an id column, a date column and a value column. It is keyed by id and then date. I want to extract only certain dates. Is there a way to join on the second column of the key only, ignoring the first?

For example,

values <- data.table(id = rep(1:2, each = 10), dates = rep(1:10, 2), values = rep(c(1,NA,NA, NA), 5))
setkey(values, id, dates)

dates <- data.table(id = c(1,5,8))
setkey(dates, id)

# what i want
x <- values$dates %in% dates$id
values[x]

Thanks!

Upvotes: 2

Views: 292

Answers (1)

elanmi
elanmi

Reputation: 56

values <- data.table(id = rep(1:2, each = 10),
                     dates = rep(1:10, 2),
                     values = rep(c(1,NA,NA, NA), 5))
setkey(values, id, dates)

dates <- data.table(dates = c(1,5,8))
setkey(dates, dates)

merge(values, dates, by = "dates", nomatch = 0)

Column to join on should have the same name, so I changed 'id' in 'dates' to 'dates'.

Upvotes: 1

Related Questions