如果连续行之间的差异满足条件，则计算列的总和

这是帖子中的一个持续问题如果第二行满足条件，则从每组中删除第一行

下面是一个示例数据集：

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     |

我需要根据每个买家和 ID 保留这些记录，如果两个连续行之间的差异 5000。因此，例如，ID 为“4”的买家“Sandy”在“6/15/2018”和“6/20/2018”在 5 天内有两笔交易 1849 和 4193，并且由于这些交易的总和两个金额>5000，输出就会有这些记录。然而，对于同一买家“Sandy”（ID 为“4”），在“8/17/2018”、“8/20/2018”和“8/23/2018”之间的间隙内又有 4256、65 和 100 笔交易每 3 天，但输出不会有这些记录，因为该金额的总和

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

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

将日期从字符更改为日期并将金额从字符更改为数字：

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

现在，我按 id 对数据集进行分组，用日期对其进行排列，并在每个 id 内创建一个排名（例如，Sandy 在她购物的 5 个不同天中将获得从 1 到 5 的排名），然后我定义一个名为 ConsecutiveSum 的新变量，它将每行的值添加到其前一行的值（滞后给出前一行）。如果前一行的值不存在，则 ifelse 语句会强制连续 sum 输出 0。下一步只是执行您的条件：

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