How to filter and perform group operation for each row on already filtered data table

Viewed 95

I would like to filter data table and perform some calculation for each row of the filtered data table. I know I can do this in 2 steps: 1) filter data table and assign to new object, 2) calculate what i need on the already filtered table.

But is there a way to do this in one step? I.e. a way to use the number of rows of the filtered table in the by= parameter? My sample data:

test <- data.frame(min_date = c("2017-08-03", "2017-09-10", "2017-10-03"),
               max_date = c("2017-08-10", "2017-10-12", "2017-11-01"),
               group = c("g1", "g2", "g1"), loc = c("1", "2", "1"))

I want to filter only group g1 and for each record add new rows for each day between min_date and max_date.

Without filtering, I would do:

dt <- setDT(test)[ , list(group = group, loc = loc,
                                  min_date = min(as.Date(min_date)),
                                  max_date = max(as.Date(max_date)),
                                  loc = loc,
                                  date = seq(as.Date(min_date),
                                             as.Date(max_date),
                                             by = "day")),
              by = 1:nrow(test)]

With filtering, if I know number of rows after filtering:

dt <- setDT(test)[group == "g1", list(group = group, loc = loc,
                                  min_date = min(as.Date(min_date)),
                                  max_date = max(as.Date(max_date)),
                                  loc = loc,
                                  date = seq(as.Date(min_date),
                                             as.Date(max_date),
                                             by = "day")),
              by = 1:2]

The problem is, I can't use hardcoded number of rows and nrow(test) as well as .N return number of rows of the original dataset.

What would be the fastest way to do the filtering and then the group by operation? Is filter, assign to new object and perform group by the only (and best) way to do this?

Thank you!

1 Answers
Related