如果连续行之间的差满足条件，则计算列的总和 [英] Calculate sum of a column if the difference between consecutive rows meets a condition

问题描述

这是帖子以下是样本数据集:

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")))) 

如下所示:

| 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     |

如果两个连续行之间的差值<= 5，我需要保留这些记录，其中基于每个购买者和id，连续行之间的金额总和> 5000.因此，例如，标识为"4"的买方桑迪"在"5/15/2018"和"6/20/2018"之间有两笔交易，分别是1849年和4193，交易间隔为5天，因为这些交易的总和如果两个数量> 5000，则输出将具有这些记录.而同一个买家ID为'4'的'桑迪'在'8/17/2018'，'8/20/2018'和'8/23/2018'上还有另一笔分别为4256、65和100的交易最多3天，但输出不会包含这些记录，因为此记录的总和小于5000. 最终输出如下所示:

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   |

推荐答案

我会结合使用tidyverse中可用的技术:

I would use a combination of techniques available in tidyverse:

首先创建一个分组变量(new_id)，然后结合使用原始的id和new_id基于分组将它们加在一起.然后我们可以根据Amount> 5000之和的标准来filter.我们可以将其与filter然后join或semi_join进行过滤.

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是一个数据集，用于根据Dollars > 5000时的id和new_id和filter查找总的Amount.这使您符合条件的id和new_id

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)

这篇关于如果连续行之间的差满足条件，则计算列的总和的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！