根据R中的每一组计算后续行的日期差

Question

我有一个示例表，它看起来有点像这样：

| Date       | Vendor_Id | Requisitioner | Amount |
|------------|:---------:|--------------:|--------|
| 1/17/2019  |     98    |          John | 2405   |
| 4/30/2019  |    1320   |          Dave | 1420   |
| 11/29/2018 |    3887   |       Michele | 596    |
| 11/29/2018 | 3887      | Michele       | 960    |
| 11/29/2018 | 3887      | Michele       | 1158   |
| 9/21/2018  | 4919      | James         | 857    |
| 10/25/2018 | 4919      | Paul          | 1162   |
| 10/26/2018 | 4919      | Echo          | 726    |
| 10/26/2018 | 4919      | Echo          | 726    |
| 10/29/2018 | 4919      | Andrew        | 532    |
| 10/29/2018 | 4919      | Andrew        | 532    |
| 11/12/2018 | 4919      | Carlos        | 954    |
| 5/21/2018  | 2111      | June          | 3580   |
| 5/23/2018  | 7420      | Justin        | 224    |
| 5/24/2018  | 1187      | Sylvia        | 3442   |
| 5/25/2018  | 1187      | Sylvia        | 4167   |
| 5/30/2018  | 3456      | Ama           | 4580   |

根据每个请求者和供应商id，我需要找出日期的差异，这样应该是这样的：

| Date       | Vendor_Id | Requisitioner | Amount | Date_Diff |
|------------|:---------:|--------------:|--------|-----------|
| 1/17/2019  |     98    |          John | 2405   | NA        |
| 4/30/2019  |    1320   |          Dave | 1420   | 103       |
| 11/29/2018 |    3887   |       Michele | 596    | NA        |
| 11/29/2018 | 3887      | Michele       | 960    | 0         |
| 11/29/2018 | 3887      | Michele       | 1158   | 0         |
| 9/21/2018  | 4919      | James         | 857    | NA        |
| 10/25/2018 | 4919      | Paul          | 1162   | NA        |
| 10/26/2018 | 4919      | Paul          | 726    | 1         |
| 10/26/2018 | 4919      | Paul          | 726    | 0         |
| 10/29/2018 | 4919      | Paul          | 532    | 3         |
| 10/29/2018 | 4919      | Paul          | 532    | 0         |
| 11/12/2018 | 4917      | Carlos        | 954    | NA        |
| 5/21/2018  | 2111      | Justin        | 3580   | NA        |
| 5/23/2018  | 7420      | Justin        | 224    | 2         |
| 5/24/2018  | 1187      | Sylvia        | 3442   | NA        |
| 5/25/2018  | 1187      | Sylvia        | 4167   | 1         |
| 5/30/2018  | 3456      | Ama           | 4580   | NA        |

现在，如果日期的差异是每个请求者和供应商id中的<=3天，并且金额的总和>5000，我需要创建一个子集。最终的输出应该是这样的：

| Date      | Vendor_Id | Requisitioner | Amount | Date_Diff |
|-----------|:---------:|--------------:|--------|-----------|
| 5/24/2018 |    1187   |        Sylvia | 3442   | NA        |
| 5/25/2018 |    1187   |        Sylvia | 4167   | 1         |

最初，当我尝试处理日期差异时，我使用了以下代码：

df=df %>% mutate(diffdate= difftime(Date,lag(Date,1))) 

然而，区别没有意义，因为它们是巨大的数字，如86400和一些巨大的随机数字。当'Date‘字段的数据类型最初是Posixct时，我尝试了上面的代码。后来当我把它改成' date‘数据类型时，日期差异仍然是巨大的随机数。此外，是否可以根据上面第二个表中提到的申购人和供应商id对日期差异进行分组？

编辑:我现在遇到了一个新的挑战。在问题集中，我需要过滤掉日期差异小于3天的值。让我们假设具有日期差异的表如下所示：

| MasterCalendarDate | Vendor_Id | Requisitioner | Amount | diffdate |
|--------------------|:---------:|--------------:|--------|----------|
| 1/17/2019          |     98    |          John | 2405   | #N/A     |
| 4/30/2019          |    1320   |          Dave | 1420   | 103      |
| 11/29/2018         | 3887      | Michele       | 596    | #N/A     |
| 11/29/2018         | 3887      | Michele       | 960    | 0        |
| 11/29/2018         | 3887      | Michele       | 1158   | 0        |
| 9/21/2018          | 4919      | Paul          | 857    | #N/A     |
| 10/25/2018         | 4919      | Paul          | 1162   | 34       |
| 10/26/2018         | 4919      | Paul          | 726    | 1        |
| 10/26/2018         | 4919      | Paul          | 726    | 0        |

当我们查看申请者'Paul‘时，2018年9月21日和2018年10月25日之间的日期差异是34，而2018年10月25日和2018年10月26日之间的日期差异是1天。然而，当我过滤日期差异<=3 days的数据时，由于34天的差异，我错过了10/25/2018。我有很多这样的事情发生。我怎么才能修复它？

Answer 1

我认为您需要使用as.Date()转换日期变量，然后您可以使用difftime()计算滞后时间差。

# create toy data frame
df <- data.frame(date=as.Date(paste(sample(2018:2019,100,T),
                            sample(1:12,100,T),
                            sample(1:28,100,T),sep = '-')),
                 req=sample(letters[1:10],100,T),
                 amount=sample(100:10000,100,T))

# compute lagged time difference in days -- diff output is numeric
df %>% arrange(req,date) %>% group_by(req) %>% 
  mutate(diff=as.numeric(difftime(date,lag(date),units='days')))

# as above plus filtering based on time difference and amount
df %>% arrange(req,date) %>% group_by(req) %>% 
  mutate(diff=as.numeric(difftime(date,lag(date),units='days'))) %>% 
  filter(diff<10 | is.na(diff), amount>5000)

# A tibble: 8 x 4
# Groups:   req [7]
  date       req   amount  diff
  <date>     <fct>  <int> <dbl>
1 2018-05-13 a       9062    NA
2 2019-05-07 b       9946     2
3 2018-02-03 e       5697    NA
4 2018-03-12 g       7093    NA
5 2019-05-16 g       5631     3
6 2018-03-06 h       7114     6
7 2018-08-12 i       5151     6
8 2018-04-03 j       7738     8