In R, how can I sum a a column of data between a date and a date in the past and within another variable, similar to the sumifs function in excel

Question

I am using the nycflightdata13 library as the example.

I want to calculate the total flying time of any given aircraft in the 10 days previous to the date of the flight described in the row

row tailnum flyingtime  dep_date
1   N14228  227 1/1/2013
2   N24211  227 1/1/2013
3   N619AA  160 1/1/2013
4   N24211  252 1/2/2013
5   N14228  150 1/8/2013
6   N24211  308 1/8/2013
7   N14228  39  1/9/2013
8   N14228  149 1/9/2013
9   N24211  319 1/9/2013
10  N24211  344 1/10/2013
11  N24211  360 1/11/2013
12  N14228  43  1/13/2013
13  N24211  145 1/13/2013
14  N24211  33  1/16/2013

In excel i would use something with the structure of =sumifs(flyingtime,dep_date,"<"&row.dep_date,dep_date,">="&row.dep_date-10,tailnum,row.tailnum) and in SQL i would use some sort of partitioned sub query

to get the result of

tailnum flyingtime  dep_date    flying_10
N14228  227 1/1/2013    0.00
N24211  227 1/1/2013    0.00
N619AA  160 1/1/2013    0.00
N24211  252 1/2/2013    227.00
N14228  150 1/8/2013    227.00
N24211  308 1/8/2013    479.00
N14228  39  1/9/2013    377.00
N14228  149 1/9/2013    377.00
N24211  319 1/9/2013    787.00
N24211  344 1/10/2013   1106.00
N24211  360 1/11/2013   1450.00
N14228  43  1/13/2013   338.00
N24211  145 1/13/2013   1331.00
N24211  33  1/16/2013   1476.00

But I can't quite land on the right syntax to get this right in R

I have searched a lot and none of the solutions I have found match this issue and my R skills are not quite there to apply them to this. I am familiar with dplyr and data.table

Thanks

score 1 · Answer 1 · answered Sep 17 '20 at 11:22

1

In true SO form, i have found the answer within 1 hour of posting. here Excel SUMIFS equivalent in R thanks to @akrun

fh <- as.data.table(fh)
fh$sumifs <- sapply(seq_len(nrow(fh)), function(i) with(fh, sum(air_time[tailnum == tailnum[i] & time_hour < time_hour[i] & time_hour >= time_hour[i]-10])))

answered Sep 17 '20 at 11:22

Jay Ramsay

25
5

This would be awfully slow and in inefficient for a big data set – David Arenburg Sep 17 '20 at 13:05

score -1 · Answer 2 · answered Sep 17 '20 at 10:25

-1

In dplyr you can sum with conditions as seen in this answer.

For example:

library(dplyr)

df %>%
mutate(sum10 = sum(B[A<=10]))

answered Sep 17 '20 at 10:25

Fnguyen

1,159
10
23

I can see how that works when the comparator is a constant i.e. 10, but how can I apply it to each row when the value of dep_date is different? – Jay Ramsay Sep 17 '20 at 11:05

In R, how can I sum a a column of data between a date and a date in the past and within another variable, similar to the sumifs function in excel

2 Answers2