在R数据框中计算访客时长：统计机构停留、房间耗时及等待时间

我在R中有一份机构访客数据框，记录了访客（id）进入机构（create事件）、被呼叫进入房间（call事件）、离开房间（discharge事件），还有中间的work事件。需要计算三列指标：

• diff_since_create：每个访客每行记录距离其create事件的分钟数；
• time_in_room：每个访客每行记录距离最近一次call事件的分钟数，代表房间内停留时间（首次call从0开始，到discharge结束）；
• time_waiting：每个访客每行记录距离最近一次discharge事件的分钟数，代表房间外的等待时间。

注：call与discharge、discharge与call之间可能存在多行数据，示例数据已包含目标结果列（实际数据无这些列）。

示例数据 #

structure(list(id = c(1L, 1L, 1L, 1L, 1L, 1L, 1L, 1L, 1L, 2L, 
2L, 2L, 2L, 2L, 2L, 2L, 2L, 2L), time = structure(c(1713164400, 
1713164700, 1713165000, 1713165600, 1713165720, 1713165780, 1713166200, 
1713166500, 1713167100, 1713164400, 1713164700, 1713165000, 1713165600, 
1713166020, 1713166200, 1713166500, 1713166620, 1713167100), class = c("POSIXct", 
"POSIXt"), tzone = ""), action = c("create", "call", "discharge", 
"call", "work", "work", "discharge", "call", "discharge", "create", 
"call", "discharge", "call", "work", "discharge", "call", "work", 
"discharge"), room = c("", "room 1", "room 1", "room 2", "room 2", 
"room 2", "room 2", "room 3", "room 3", "", "room 1", "room 1", 
"room 2", "room 2", "room 2", "room 3", "room 3", "room 3"), 
    diff_since_create = c(0L, 5L, 10L, 20L, 22L, 23L, 30L, 35L, 
    45L, 0L, 5L, 10L, 20L, 20L, 30L, 35L, 37L, 45L), time_in_room = c("na", 
    "0", "5", "0", "2", "3", "10", "0", "10", "na", "0", "5", 
    "0", "7", "10", "0", "2", "10"), time_waiting = c(0L, 5L, 
    0L, 10L, 0L, 0L, 0L, 5L, 0L, 0L, 5L, 0L, 10L, 0L, 0L, 5L, 
    0L, 0L)), row.names = c(NA, -18L), class = "data.frame")

解决方案 #

我们使用dplyr进行分组处理，结合zoo包的na.locf函数填充最近事件的时间，实现三列指标的计算。

1. 安装并加载所需包 #

# 安装依赖包（若未安装）
install.packages(c("dplyr", "zoo"))
# 加载包
library(dplyr)
library(zoo)

2. 计算指标列 #

# 读取数据（实际使用时替换为你的数据框名称）
df <- structure(list(id = c(1L, 1L, 1L, 1L, 1L, 1L, 1L, 1L, 1L, 2L, 
2L, 2L, 2L, 2L, 2L, 2L, 2L, 2L), time = structure(c(1713164400, 
1713164700, 1713165000, 1713165600, 1713165720, 1713165780, 1713166200, 
1713166500, 1713167100, 1713164400, 1713164700, 1713165000, 1713165600, 
1713166020, 1713166200, 1713166500, 1713166620, 1713167100), class = c("POSIXct", 
"POSIXt"), tzone = ""), action = c("create", "call", "discharge", 
"call", "work", "work", "discharge", "call", "discharge", "create", 
"call", "discharge", "call", "work", "discharge", "call", "work", 
"discharge"), room = c("", "room 1", "room 1", "room 2", "room 2", 
"room 2", "room 2", "room 3", "room 3", "", "room 1", "room 1", 
"room 2", "room 2", "room 2", "room 3", "room 3", "room 3"), 
    diff_since_create = c(0L, 5L, 10L, 20L, 22L, 23L, 30L, 35L, 
    45L, 0L, 5L, 10L, 20L, 20L, 30L, 35L, 37L, 45L), time_in_room = c("na", 
    "0", "5", "0", "2", "3", "10", "0", "10", "na", "0", "5", 
    "0", "7", "10", "0", "2", "10"), time_waiting = c(0L, 5L, 
    0L, 10L, 0L, 0L, 0L, 5L, 0L, 0L, 5L, 0L, 10L, 0L, 0L, 5L, 
    0L, 0L)), row.names = c(NA, -18L), class = "data.frame")

# 分组计算三列指标
result_df <- df %>%
  group_by(id) %>%
  # 计算diff_since_create：距离create事件的分钟数
  mutate(
    create_time = first(time[action == "create"]),
    diff_since_create = as.integer(difftime(time, create_time, units = "mins"))
  ) %>%
  # 计算time_in_room：距离最近call事件的分钟数，create事件标记为"na"
  mutate(
    last_call_time = na.locf(ifelse(action == "call", time, NA), na.rm = FALSE),
    time_in_room = ifelse(action == "create", 
                          "na", 
                          as.integer(difftime(time, last_call_time, units = "mins"))),
    last_call_time = NULL
  ) %>%
  # 计算time_waiting：距离最近discharge事件的分钟数，房间内事件标记为0
  mutate(
    last_discharge_time = na.locf(ifelse(action == "discharge", time, NA), na.rm = FALSE),
    last_discharge_time = ifelse(is.na(last_discharge_time), create_time, last_discharge_time),
    time_waiting = ifelse(action %in% c("call", "work", "discharge"),
                          0,
                          as.integer(difftime(time, last_discharge_time, units = "mins"))),
    last_discharge_time = NULL,
    create_time = NULL
  ) %>%
  ungroup()

# 查看结果
print(result_df)

指标计算说明 #

• diff_since_create：分组后提取每个访客的create时间，用difftime计算当前行时间与create时间的分钟差，转换为整数。
• time_in_room：将call事件的时间保留，其余设为NA，通过na.locf向前填充最近的call时间；create事件标记为"na"，其余行计算与最近call时间的分钟差。
• time_waiting：将discharge事件的时间保留，其余设为NA，通过na.locf向前填充最近的discharge时间；初始无discharge的行用create时间替代；房间内事件（call/work/discharge）标记为0，其余行计算与最近discharge时间的分钟差。

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