R语言基于事件跨度计算人员本地/境外居住平均时间差实现需求

我们可以通过tidyverse+lubridate工具链完成需求，实现代码如下： #

步骤1：加载依赖包 #

library(dplyr)
library(lubridate)

步骤2：数据处理与指标计算 #

# 首先将日期列转为标准日期格式，生成连续居住事件的唯一ID
df_processed <- df %>%
  mutate(date = dmy(date)) %>% # 解析"日/月份缩写/年"格式的日期
  group_by(A) %>%
  # 规则：和上一行sequence取值不同时判定为新居住事件，累计生成事件ID
  mutate(event_id = cumsum(sequence != lag(sequence, default = first(sequence)))) %>%
  ungroup()

# 按人员、事件ID、居住类型分组，计算单段居住事件的时长与对应记录数
event_duration <- df_processed %>%
  group_by(A, event_id, sequence) %>%
  summarise(
    duration = as.numeric(max(date) - min(date)), # 单段事件时长（天）
    record_cnt = n(), # 单段事件对应的记录条数，用于计算平均值
    .groups = "drop"
  )

# 汇总计算最终指标并格式化输出
result <- event_duration %>%
  group_by(A, sequence) %>%
  summarise(
    total_spread = sum(duration),
    total_record = sum(record_cnt),
    avg_spread = total_spread / total_record,
    .groups = "drop"
  ) %>%
  # 长表转宽表匹配要求的输出结构
  tidyr::pivot_wider(
    id_cols = A,
    names_from = sequence,
    values_from = c(total_spread, avg_spread),
    values_fill = 0 # 无对应居住类型的指标填充为0
  ) %>%
  # 重命名列符合需求
  rename(
    avg_local_timespread = avg_spread_1,
    avg_international_timespread = avg_spread_0,
    total_local_timespread = total_spread_1,
    total_international_timespread = total_spread_0
  ) %>%
  # 调整列顺序
  select(A, avg_local_timespread, avg_international_timespread, total_local_timespread, total_international_timespread)

输出结果 #

执行print(result)即可得到符合预期的结果：

# A tibble: 2 × 5
  A     avg_local_timespread avg_international_timespread total_local_timespread total_international_timespread
  <chr>                <dbl>                        <dbl>                  <dbl>                          <dbl>
1 1244                     0                            0                      0                              0
2 1245                     6                            1                     30                              2

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