如何按组计算事件之间的天数

Question

我在为如何计算两次发生之间的天数而挣扎，因为我需要计算设备维修之间所需的天数。

我有一个数据，有很多设备和日期表示维修，然后我需要计算每台设备的维修之间的天数。我将展示一个玩具例子：

test = data.frame(car = c("A", "A", "B", "B", "B", "C", "C", "D",  "D", "D", "E"), 
                   maintenance_date= c("20-09-2020", "25-09-2020", "14-05-2020", "20-05-2020", "20-05-2021", "11-01-2021", "13-01-2021", "13-01-2021", "15-01-2021", "15-01-2021", "13-01-2021"))
#test
#   car      maintenance_date
#1    A            20-09-2020
#2    A            25-09-2020
#3    B            14-05-2020
#4    B            20-05-2020
#5    B            20-05-2021
#6    C            11-01-2021
#7    C            13-01-2021
#8    D            13-01-2021
#9    D            15-01-2021
#10   D            15-01-2021
#11   E            13-01-2021

#for result, I'd like something like:
result
#   car     maintenance_date
#1    A         5
#2    B         6
#3    B       365
#4    C         2
#5    D         2
#6    D         0

我想使用类似于test %>% arrange(maintenance_date) %>% group_by(car) %>% ...的东西。

我该怎么做呢？

Answer 1

在执行Date之前，我们需要转换为arrange类，然后执行group_by 'car‘，然后得到difference

library(dplyr)
library(lubridate)
test %>% 
   mutate(maintenance_date = dmy(maintenance_date)) %>% 
   arrange(maintenance_date) %>% 
   group_by(car) %>%
   summarise(maintenance_date = diff(maintenance_date), .groups = 'drop')

-output

# A tibble: 6 × 2
  car   maintenance_date
  <chr> <drtn>          
1 A       5 days        
2 B       6 days        
3 B     365 days        
4 C       2 days        
5 D       2 days        
6 D       0 days        

Answer 2

data.table

library(data.table)
setDT(test)
test[, maintenance_date := as.Date(maintenance_date, format="%d-%m-%Y")
  ][, .(ndays = diff(maintenance_date)), by = car]
#       car      ndays
#    <char> <difftime>
# 1:      A     5 days
# 2:      B     6 days
# 3:      B   365 days
# 4:      C     2 days
# 5:      D     2 days
# 6:      D     0 days

Answer 3

另一个解决方案，tidyverse-based，可以是：

library(tidyverse)
library(lubridate)

test = data.frame(car = c("A", "A", "B", "B", "B", "C", "C", "D",  "D", "D", "E"), maintenance_date= c("20-09-2020", "25-09-2020", "14-05-2020", "20-05-2020", "20-05-2021", "11-01-2021", "13-01-2021", "13-01-2021", "15-01-2021", "15-01-2021", "13-01-2021"))

test  %>% 
  group_by(car) %>% 
  mutate(maintenance_date = c(-1,diff(dmy(maintenance_date)))) %>% 
  filter(maintenance_date >= 0) %>% ungroup

#> # A tibble: 6 × 2
#> # Groups:   car [4]
#>   car   maintenance_date
#>   <chr>            <dbl>
#> 1 A                    5
#> 2 B                    6
#> 3 B                  365
#> 4 C                    2
#> 5 D                    2
#> 6 D                    0