r - 基于分组一列或多列计算日期之间的差异
问题描述
我的数据集示例如下:
| id | Date | Buyer |
|:--:|-----------:|----------|
| 9 | 11/29/2018 | Jenny |
| 9 | 11/29/2018 | Jenny |
| 9 | 11/29/2018 | Jenny |
| 4 | 5/30/2018 | Chang |
| 4 | 7/4/2018 | Chang |
| 4 | 8/17/2018 | Chang |
| 5 | 5/25/2018 | Chunfei |
| 5 | 2/13/2019 | Chunfei |
| 5 | 2/16/2019 | Chunfei |
| 5 | 2/16/2019 | Chunfei |
| 5 | 2/23/2019 | Chunfei |
| 5 | 2/25/2019 | Chunfei |
| 8 | 2/28/2019 | Chunfei |
| 8 | 2/28/2019 | Chunfei |
我对这个数据集有两组问题:
- 我需要计算日期之间的差异,但这个差异将基于分组“买家”和“id”计算,这意味着买家“珍妮”和标识“9”的日期差异将是一组,买家“张” ' ID 为“4”的将是另一个组,ID 为“5”的买家“Chunfei”将是另一个组,ID 为“8”的“Chunfei”将是另一个组。因此,输出将是:
| id | Date | Buyer_id | Diff |
|:--:|-----------:|----------|------|
| 9 | 11/29/2018 | Jenny | NA |
| 9 | 11/29/2018 | Jenny | 0 |
| 9 | 11/29/2018 | Jenny | 0 |
| 4 | 5/30/2018 | Chang | NA |
| 4 | 7/4/2018 | Chang | 35 |
| 4 | 8/17/2018 | Chang | 44 |
| 5 | 5/25/2018 | Chunfei | NA |
| 5 | 2/13/2019 | Chunfei | 264 |
| 5 | 2/16/2019 | Chunfei | 3 |
| 5 | 2/16/2019 | Chunfei | 0 |
| 5 | 2/23/2019 | Chunfei | 7 |
| 5 | 2/25/2019 | Chunfei | 2 |
| 8 | 2/28/2019 | Chunfei | NA |
| 8 | 2/28/2019 | Chunfei | 0 |
问题是我不明白为什么 group_by 不起作用。以下代码减去连续的行,而不是将它们分组为相同的买家和 id,然后减去。
df=data.frame(id=c("9","9","9","4","4","4","5","5","5","5","5","5","8","8"),
Date=c("11/29/2018","11/29/2018","11/29/2018","5/30/2018","7/4/2018",
"8/17/2018","5/25/2018","2/13/2019","2/16/2019","2/16/2019","2/23/2019",
"2/25/2019","2/28/2019","2/28/2019"),Buyer=c("Jenny","Jenny","Jenny",
"Chang","Chang","Chang","Chunfei","Chunfei","Chunfei","Chunfei","Chunfei",
"Chunfei","Chunfei","Chunfei"))
df$id=as.numeric(as.character(df$id))
df$Date=as.Date(df$Date, "%m/%d/%Y")
df$Buyer=as.character(df$Buyer)
df1=df %>% group_by(Buyer,id) %>%
mutate(diff=as.numeric(difftime(Date,lag(Date),units='days')))
- 计算日期差后,我需要过滤那些日期差为 5 天的记录。在上面的示例中,“5/25/2018”、“2/13/2019”、“2/16/2019”、“2/16/2019”、“2/23/2019”、 2/25/2019" 将是 NA,264,3,0,7,2。但是,如果我为 n<6 提供过滤器,我会错过日期“2/13/2019”和“2/23/2019”。这些日期对于保留在最终输出中很重要,因为即使日期“2/13/2019”和“5/25/2018”之间的差异是 264,“2/16/2019”和“2 /13/2019" 是 3。同样,即使 "2/16/2019" 和 "2/23/2019" 之间的差异是 7,但 "2/23/2019" 和 "2/25/2019" 之间的差异" 是 2。所以,我需要保留这些日期。如何做到这一点?
我们可以在最终输出中屏蔽“diff”列,它应该如下所示:
| id | Date | Buyer_id |
|----|:----------:|---------:|
| 9 | 11/29/2018 | Jenny |
| 9 | 11/29/2018 | Jenny |
| 9 | 11/29/2018 | Jenny |
| 5 | 2/13/2019 | Chunfei |
| 5 | 2/16/2019 | Chunfei |
| 5 | 2/16/2019 | Chunfei |
| 5 | 2/23/2019 | Chunfei |
| 5 | 2/25/2019 | Chunfei |
| 8 | 2/28/2019 | Chunfei |
| 8 | 2/28/2019 | Chunfei |
解决方案
我们可以diff
用来减去Date
和选择至少有一个小于等于 5 天的值的组。
library(dplyr)
df %>%
group_by(id, Buyer) %>%
filter(any(diff(Date) <= 5))
# id Date Buyer
# <dbl> <date> <chr>
# 1 9 2018-11-29 Jenny
# 2 9 2018-11-29 Jenny
# 3 9 2018-11-29 Jenny
# 4 5 2018-05-25 Chunfei
# 5 5 2019-02-13 Chunfei
# 6 5 2019-02-16 Chunfei
# 7 5 2019-02-16 Chunfei
# 8 5 2019-02-23 Chunfei
# 9 5 2019-02-25 Chunfei
#10 8 2019-02-28 Chunfei
#11 8 2019-02-28 Chunfei
重新阅读问题后,我认为您可能不会查看filter
整个组,而只会查看那些相差 5 天的行。我们可以得到diff
值小于 5 的索引,并选择它的前一个索引。
df %>%
group_by(id, Buyer) %>%
mutate(diff = c(NA, diff(Date))) %>%
slice({i1 <- which(diff <= 5); unique(c(i1, i1-1))}) %>%
select(-diff)
# id Date Buyer
# <dbl> <date> <chr>
# 1 5 2019-02-16 Chunfei
# 2 5 2019-02-16 Chunfei
# 3 5 2019-02-25 Chunfei
# 4 5 2019-02-13 Chunfei
# 5 5 2019-02-23 Chunfei
# 6 8 2019-02-28 Chunfei
# 7 8 2019-02-28 Chunfei
# 8 9 2018-11-29 Jenny
# 9 9 2018-11-29 Jenny
#10 9 2018-11-29 Jenny
数据
df <- structure(list(id = c(9, 9, 9, 4, 4, 4, 5, 5, 5, 5, 5, 5, 8,
8), Date = structure(c(17864, 17864, 17864, 17681, 17716, 17760,
17676, 17940, 17943, 17943, 17950, 17952, 17955, 17955), class = "Date"),
Buyer = c("Jenny", "Jenny", "Jenny", "Chang", "Chang", "Chang",
"Chunfei", "Chunfei", "Chunfei", "Chunfei", "Chunfei", "Chunfei",
"Chunfei", "Chunfei")), row.names = c(NA, -14L), class = "data.frame")
推荐阅读
- excel - 如何在具有特定标题的每列之后插入 3 列?
- java - 将 Collectors maxBy 与 groupingBy 一起使用时展开可选
- ios - 如何将字典从一个视图控制器类传递到另一个?SWIFT
- angular - Angular 单元测试模拟服务
- html - 如何将单独类型的文本定位到我制作的另一个文本的同一行中?
- excel - Excel-Pivot 扩展字段
- python - 使用 python 与 JSON 交互的更快方法
- python - 如何返回多个文件的第一行?
- validation - Cakephp 3.4 - 如何验证关联的关系数据?
- python - django huey 在过滤时总是返回空查询集