在R语言中按组计算日期与最近indicator为TRUE行的小时差

现有如下R数据框df：

group                date indicator
1      A 2022-11-01 01:00:00     FALSE
2      A 2022-11-01 03:00:00     FALSE
3      A 2022-11-01 04:00:00      TRUE
4      A 2022-11-01 05:00:00     FALSE
5      A 2022-11-01 06:00:00      TRUE
6      A 2022-11-01 07:00:00     FALSE
7      A 2022-11-01 10:00:00     FALSE
8      A 2022-11-01 12:00:00     FALSE
9      B 2022-11-01 01:00:00     FALSE
10     B 2022-11-01 02:00:00     FALSE
11     B 2022-11-01 03:00:00     FALSE
12     B 2022-11-01 06:00:00      TRUE
13     B 2022-11-01 07:00:00     FALSE
14     B 2022-11-01 08:00:00     FALSE
15     B 2022-11-01 11:00:00      TRUE
16     B 2022-11-01 13:00:00     FALSE

需要实现按组计算每行日期与最近的满足indicator == TRUE的行之间的小时差，且indicator为TRUE的行输出0，最终得到如下结果df_desired：

group                date indicator diff_hours
1      A 2022-11-01 01:00:00     FALSE          3
2      A 2022-11-01 03:00:00     FALSE          1
3      A 2022-11-01 04:00:00      TRUE          0
4      A 2022-11-01 05:00:00     FALSE          1
5      A 2022-11-01 06:00:00      TRUE          0
6      A 2022-11-01 07:00:00     FALSE          1
7      A 2022-11-01 10:00:00     FALSE          4
8      A 2022-11-01 12:00:00     FALSE          6
9      B 2022-11-01 01:00:00     FALSE          5
10     B 2022-11-01 02:00:00     FALSE          4
11     B 2022-11-01 03:00:00     FALSE          3
12     B 2022-11-01 06:00:00      TRUE          0
13     B 2022-11-01 07:00:00     FALSE          1
14     B 2022-11-01 08:00:00     FALSE          2
15     B 2022-11-01 11:00:00      TRUE          0
16     B 2022-11-01 13:00:00     FALSE          2

数据的dput代码 #

df <- structure(list(group = c("A", "A", "A", "A", "A", "A", "A", "A", 
"B", "B", "B", "B", "B", "B", "B", "B"), date = structure(c(1667260800, 
1667268000, 1667271600, 1667275200, 1667278800, 1667282400, 1667293200, 
1667300400, 1667260800, 1667264400, 1667268000, 1667278800, 1667282400, 
1667286000, 1667296800, 1667304000), class = c("POSIXct", "POSIXt"
), tzone = ""), indicator = c(FALSE, FALSE, TRUE, FALSE, TRUE, 
FALSE, FALSE, FALSE, FALSE, FALSE, FALSE, TRUE, FALSE, FALSE, 
TRUE, FALSE)), class = "data.frame", row.names = c(NA, -16L))

df_desired <- structure(list(group = c("A", "A", "A", "A", "A", "A", "A", "A", 
"B", "B", "B", "B", "B", "B", "B", "B"), date = structure(c(1667260800, 
1667268000, 1667271600, 1667275200, 1667278800, 1667282400, 1667293200, 
1667300400, 1667260800, 1667264400, 1667268000, 1667278800, 1667282400, 
1667286000, 1667296800, 1667304000), class = c("POSIXct", "POSIXt"
), tzone = ""), indicator = c(FALSE, FALSE, TRUE, FALSE, TRUE, 
FALSE, FALSE, FALSE, FALSE, FALSE, FALSE, TRUE, FALSE, FALSE, 
TRUE, FALSE), diff_hours = c(3, 1, 0, 1, 0, 1, 4, 6, 5, 4, 3, 
0, 1, 2, 0, 2)), class = "data.frame", row.names = c(NA, -16L
))

解决方案 #

方法1：使用dplyr + purrr

核心思路是按组提取所有indicator == TRUE的日期，对每行日期计算与这些日期的时间差绝对值，取最小值转换为小时：

library(dplyr)
library(purrr)

df_result <- df %>%
  group_by(group) %>%
  mutate(
    # 提取组内所有符合条件的日期
    true_dates = list(date[indicator]),
    # 计算当前日期与每个条件日期的小时差绝对值，取最小值
    diff_hours = map_dbl(date, ~ min(abs(difftime(.x, true_dates[[1]], units = "hours")))),
    # 符合条件的行强制设为0
    diff_hours = ifelse(indicator, 0, diff_hours)
  ) %>%
  select(-true_dates) %>%
  ungroup()

# 验证结果与目标一致
all.equal(df_result, df_desired)
# [1] TRUE

方法2：使用data.table（大数据集更高效）

通过交叉连接匹配组内所有条件日期，再计算最小小时差：

library(data.table)

setDT(df)
# 提取组内所有符合条件的日期
true_dates_df <- df[indicator == TRUE, .(true_date = date), by = group]

# 交叉连接后计算最小小时差
df_result <- df[true_dates_df, on = "group", allow.cartesian = TRUE] %>%
  .[, diff_hours := abs(difftime(date, true_date, units = "hours"))] %>%
  .[, .(diff_hours = min(diff_hours)), by = .(group, date, indicator)] %>%
  .[, diff_hours := ifelse(indicator, 0, diff_hours)] %>%
  setorder(group, date)

# 转回data.frame格式（可选）
setDF(df_result)

# 验证结果与目标一致
all.equal(df_result, df_desired)
# [1] TRUE

内容的提问来源于stack exchange，提问作者Quinten