Calculate sum of a column if the difference between consecutive rows meets a condition

Question

This is a continued question from the post Remove the first row from each group if the second row meets a condition

Below is a sample dataset:

df <- data.frame(id=c("9","9","9","5","5","4","4","4","4","4","20","20"),
       Date=c("11/29/2018","11/29/2018","11/29/2018","2/13/2019","2/13/2019",
       "6/15/2018","6/20/2018","8/17/2018","8/20/2018","8/23/2018","12/25/2018","12/25/2018"), 
Buyer= c("John","John","John","Maria","Maria","Sandy","Sandy","Sandy","Sandy","Sandy","Paul","Paul"), 
Amount= c("959","1158","596","922","922","1849","4193","4256","65","100","313","99"), stringsAsFactors = F) %>% 
group_by(Buyer,id) %>% mutate(diffs = c(NA, diff(as.Date(Date, format = "%m/%d/%Y"))))

which would look like:

| id |    Date    | Buyer | diff | Amount |
|----|:----------:|------:|------|--------|
| 9  | 11/29/2018 |  John | NA   | 959    |
| 9  | 11/29/2018 |  John | 0    | 1158   |
| 9  | 11/29/2018 |  John | 0    | 596    |
| 5  | 2/13/2019  | Maria | 76   | 922    |
| 5  | 2/13/2019  | Maria | 0    | 922    |
| 4  | 6/15/2018  | Sandy | -243 | 1849   |
| 4  | 6/20/2018  | Sandy | 5    | 4193   |
| 4  | 8/17/2018  | Sandy | 58   | 4256   |
| 4  | 8/20/2018  | Sandy | 3    | 65     |
| 4  | 8/23/2018  | Sandy | 3    | 100    |
| 20 | 12/25/2018 | Paul  | 124  | 313    |
| 20 | 12/25/2018 | Paul  | 0    | 99     |

I need to retain those records where based on each buyer and id, the sum of amount between consecutive rows >5000 if the difference between two consecutive rows <=5. So, for example, Buyer 'Sandy' with id '4' has two transactions of 1849 and 4193 on '6/15/2018' and '6/20/2018' within a gap of 5 days, and since the sum of these two amounts>5000, the output would have these records. Whereas, for the same Buyer 'Sandy' with id '4' has another transactions of 4256, 65 and 100 on '8/17/2018', '8/20/2018' and '8/23/2018' within a gap of 3 days each, but the output will not have these records as the sum of this amount <5000. The final output would look like:

| id |    Date   | Buyer | diff | Amount |
|----|:---------:|------:|------|--------|
| 4  | 6/15/2018 | Sandy | -243 | 1849   |
| 4  | 6/20/2018 | Sandy | 5    | 4193   |

Your example is giving errors `Error in data.frame(id = c("9", "9", "9", "5", "5", "4", "4", "4", "4", : arguments imply differing number of rows: 11, 12` — akrun, Sep 13 '19 at 18:03
Can you check your input `diff` column. it is different from the one you showed — akrun, Sep 13 '19 at 18:07
@akrun If I understand your statement correct, the dataset in this post is little different than the previous one. So the values in column 'diff' are different. — hk2, Sep 13 '19 at 18:13
@akrun So the dataset given in this problem is what I need to work on now. — hk2, Sep 13 '19 at 18:15
@hk2 it is confusing to provide a data sample that doesn't line up with the requested output - I think this is because you probably didn't `group_by` when calculating diff in your sample but did in the reproduction code? — Calum You, Sep 13 '19 at 18:39

akash87 · Answer 1 · 2019-09-13T19:48:52.000

I would use a combination of techniques available in tidyverse:

First create a grouping variable (new_id) and use the original id and new_id in combination to add together based on a grouping. Then we can filter by the criteria of the sum of the Amount > 5000. We can take this and filter then join or semi_join to filter based on the criteria.

ids is a dataset that finds the total Amount based on id and new_id and filters for when Dollars > 5000. This gives you the id and new_id that meets your criteria

df <- data.frame(id=c("9","9","9","5","5","4","4","4","4","4","20","20"),
                 Date=c("11/29/2018","11/29/2018","11/29/2018","2/13/2019","2/13/2019",
                        "6/15/2018","6/20/2018","8/17/2018","8/20/2018","8/23/2018","12/25/2018","12/25/2018"), 
                 Buyer= c("John","John","John","Maria","Maria","Sandy","Sandy","Sandy","Sandy","Sandy","Paul","Paul"), 
                 Amount= c(959,1158,596,922,922,1849,4193,4256,65,100,313,99), stringsAsFactors = F) %>% 
  group_by(Buyer,id) %>% mutate(diffs = c(NA, diff(as.Date(Date, format = "%m/%d/%Y")))) 


library(tidyverse)

df1 <- df %>% mutate(Date      = as.Date(Date , format = "%m/%d/%Y"), 
                     tf1       = (id != lag(id, default = 0)),
                     tf2       = (is.na(diffs) | diffs > 5))

df1$new_id <- cumsum(df1$tf1 + df1$tf2 > 0)

>df1
       id    Date       Buyer Amount diffs days_post  tf1   tf2   new_id
       <chr> <date>     <chr>  <dbl> <dbl> <date>     <lgl> <lgl>  <int>
     1 9     2018-11-29 John     959    NA 2018-12-04 TRUE  TRUE       1
     2 9     2018-11-29 John    1158     0 2018-12-04 FALSE FALSE      1
     3 9     2018-11-29 John     596     0 2018-12-04 FALSE FALSE      1
     4 5     2019-02-13 Maria    922    NA 2019-02-18 TRUE  TRUE       2
     5 5     2019-02-13 Maria    922     0 2019-02-18 FALSE FALSE      2
     6 4     2018-06-15 Sandy   1849    NA 2018-06-20 TRUE  TRUE       3
     7 4     2018-06-20 Sandy   4193     5 2018-06-25 FALSE FALSE      3
     8 4     2018-08-17 Sandy   4256    58 2018-08-22 FALSE TRUE       4
     9 4     2018-08-20 Sandy     65     3 2018-08-25 FALSE FALSE      4
    10 4     2018-08-23 Sandy    100     3 2018-08-28 FALSE FALSE      4
    11 20    2018-12-25 Paul     313    NA 2018-12-30 TRUE  TRUE       5
    12 20    2018-12-25 Paul      99     0 2018-12-30 FALSE FALSE      5

ids <- df1 %>% 
       group_by(id, new_id) %>% 
       summarise(dollar = sum(Amount)) %>% 
       ungroup() %>% filter(dollar > 5000)
  id   new_id  dollar
 <chr>  <int>   <dbl>
1 4         3    6042
df1 %>% semi_join(ids)

I'm not clear with what "ids" does. Also, I get an error when I execute the last line of the code **Error: `by` required, because the data sources have no common variables Call `rlang::last_error()` to see a backtrace** — hk2, Sep 13 '19 at 19:03
Yes, I have. I'm not sure but is it because there are no common variable between ids and df1? — hk2, Sep 13 '19 at 19:20

Shirin Yavari · Answer 2 · 2019-09-13T19:06:43.950

df <- data.frame(id=c("9","9","9","5","5","4","4","4","4","4","20","20"),
                 Date=c("11/29/2018","11/29/2018","11/29/2018","2/13/2019","2/13/2019",
                        "6/15/2018","6/20/2018","8/17/2018","8/20/2018","8/23/2018","12/25/2018","12/25/2018"), 
                 Buyer= c("John","John","John","Maria","Maria","Sandy","Sandy","Sandy","Sandy","Sandy","Paul","Paul"), 
                 Amount= c("959","1158","596","922","922","1849","4193","4256","65","100","313","99"), stringsAsFactors = F) %>% 
  group_by(Buyer,id) %>% mutate(diffs = c(NA, diff(as.Date(Date, format = "%m/%d/%Y"))))

Changing Date from character to Date and Amount from character to numeric:

df$Date<-as.Date(df$Date, '%m/%d/%y')
df$Amount<-as.numeric(df$Amount)

Now here I group the dataset by id, arrange it with Date, and create a rank within each id (so for example Sandy is going to have rank from 1 through 5 for 5 different days in which she has shopped), then I define a new variable called ConsecutiveSum which adds the Value of each row to it's previous row's Value (lag gives you the previous row). The ifelse statement forces consecutive sum to output a 0 if the previous row's Value doesn't exists. The next step is just enforcing your conditions:

df %>%
  group_by(id) %>%
    arrange(Date) %>%
      mutate(rank=dense_rank(Date)) %>% 
        mutate(ConsecutiveSum = ifelse(is.na(lag(Amount)),0,Amount  + lag(Amount , default = 0)))%>%
         filter(diffs<=5 & ConsecutiveSum>=5000 | ConsecutiveSum==0 & lead(ConsecutiveSum)>=5000)


# id    Date      Buyer Amount diffs  rank ConsecutiveSum
#   <chr> <chr>     <chr>  <dbl> <dbl> <int>          <dbl>
# 1 4     6/15/2018 Sandy   1849    NA     1              0
# 2 4     6/20/2018 Sandy   4193     5     2           6042

My original dataset has id's which are existent for other buyers which means that two or more buyers will have same id's. So, if I'm grouping only on 'id', I'm missing out on records. — hk2, Sep 13 '19 at 19:42

Calculate sum of a column if the difference between consecutive rows meets a condition

2 Answers2

Linked