2

I have the following data example:

trap_data <- structure(list(site = c(1, 2, 3, 3), trap_date = structure(c(18809, 
18809, 18307, 18322), class = "Date")), class = "data.frame", row.names = c(NA, 
-4L))

climate <- structure(list(site = c(1, 1, 1, 1, 2, 2, 2, 2, 3, 3, 3, 3), 
    V1 = c(0.1, 0.2, 0.1, 0.1, 0.5, 0.2, 0.3, 0.1, 0.2, 0.1, 
    0.1, 0.5), V2 = c(1, 1, 3, 1, 2, 3, 3, 1, 1, 3, 1, 2), date = structure(c(18779, 
    18790, 18789, 18792, 18791, 18790, 18789, 18792, 18305, 18306, 
    18307, 18308), class = "Date")), class = "data.frame", row.names = c(NA, 
-12L))

I would add columns in trap_data that summarize N, mean and sum, of V1 and V2 of climate data, conditioned by site and date. However, I would these new variables (N, mean and sum) are for the N days (these time lengths are variable. for example 20 current days) before the specified date in trap_data.

Thanks,

Wilson Souza
  • 830
  • 4
  • 12

1 Answers1

4

We may create a new column in the 'trap_data' - 20 days prior date and then do a join

library(data.table)
library(lubridate)
setDT(trap_data)[, prev_date := trap_date - days(20)]
setDT(climate)[trap_data, .(N = .N,
     MeanV1 = mean(V1, na.rm = TRUE),
     SumV1 = sum(V1, na.rm = TRUE),
     MeanV2 = mean(V2, na.rm = TRUE),
  SumV2 = sum(V2, na.rm = TRUE)),
   on = .(site, date >= prev_date, date <= trap_date), by = .EACHI]

-output

    site       date       date     N    MeanV1 SumV1   MeanV2 SumV2
   <num>     <Date>     <Date> <int>     <num> <num>    <num> <num>
1:     1 2021-06-11 2021-07-01     3 0.1333333   0.4 1.666667     5
2:     2 2021-06-11 2021-07-01     4 0.2750000   1.1 2.250000     9
3:     3 2020-01-26 2020-02-15     3 0.1333333   0.4 1.666667     5
4:     3 2020-02-10 2020-03-01     4 0.2250000   0.9 1.750000     7
akrun
  • 874,273
  • 37
  • 540
  • 662
  • 1
    Nice but extra dependency just fot a single call days(20) is not best way to go – jangorecki Jun 27 '22 at 06:13
  • 1
    @jangorecki I think `trap_date - 20` should also work – akrun Jun 27 '22 at 16:06
  • I think that this code could be more applicable for other purposes. If for example, for V1 was 20 days before, but V2 was 5 days before. How can I adjust the code for this possibility? – Wilson Souza Jun 27 '22 at 16:50
  • @WilsonSouza Create two columns, `setDT(trap_data)[, c("prev_dateV1", "prev_dateV2") :=.( trap_date - 20, trap_date - 5)]` and then do the join separately where `date >= prev_dateV1` in place of `date >= prev_date` and then assing then create the `MeanV1`, SumV1` and similarly for the other and do a join by 'site' on each of those summarised outputs – akrun Jun 27 '22 at 16:53