如何用R基于group_by列和时间戳计算平均与总响应时间

前置依赖 #

使用tidyverse系列工具做数据处理，lubridate处理时间格式，200万行数据推荐搭配data.table提升运行效率：

# 安装依赖（首次运行执行）
install.packages(c("tidyverse", "lubridate", "data.table"))
# 加载包
library(tidyverse)
library(lubridate)
library(data.table)

步骤1：数据读取与基础预处理 #

# 读入数据，这里假设是竖线分隔的文本格式，根据实际存储格式调整参数
# 用fread读取大文件速度远高于base R的read系列函数
dt <- fread("你的数据文件路径", sep = "|", strip.white = T, na.strings = "NA")

# 处理时间格式，转成可计算的POSIXct类型
dt[, created_at := parse_date_time(created_at, orders = "%a %b %d %H:%M:%S %z %Y")]

# 拆分逗号分隔的多值response_tweet_id，拆分为多行方便匹配
dt <- dt[, .(response_tweet_id = unlist(strsplit(as.character(response_tweet_id), ","))), 
         by = setdiff(names(dt), "response_tweet_id")]
# 转成数值型方便后续关联匹配
dt[, response_tweet_id := as.integer(response_tweet_id)]

步骤2：计算指标1：单条推文平均响应时间 #

指标定义：每条用户发给客服的消息，到对应客服回复的时间差的平均值，单位为分钟

# 拆分用户消息表和客服消息表
user_tweet <- dt[group == "Customer", .(user_id = id, 
                                        brand = str_extract(tweet, "@\\w+") %>% str_remove("@"), 
                                        user_tweet_id = tweet_id, 
                                        resp_support_tweet_id = response_tweet_id, 
                                        user_create_time = created_at)]
support_tweet <- dt[group == "Support", .(support_id = id, 
                                          support_tweet_id = tweet_id, 
                                          support_create_time = created_at)]

# 关联匹配用户消息和对应的客服回复，过滤无匹配的无效数据
resp_time_per_tweet <- user_tweet[support_tweet, on = c("resp_support_tweet_id" = "support_tweet_id"), nomatch = 0]
# 计算单条消息的响应时间，单位转成分钟
resp_time_per_tweet[, time_diff := difftime(support_create_time, user_create_time, units = "mins") %>% as.numeric()]

# 按品牌聚合求单条消息平均响应时间
avg_per_tweet <- resp_time_per_tweet[, .(Avg_response_time_per_tweet = round(mean(time_diff, na.rm = T), 2)), 
                                     by = .(id = support_id, group = "Support")]

步骤3：计算指标2：会话总平均响应时间 #

指标定义：同一用户与同一品牌的单轮会话从用户首次发消息到会话最后一条消息的时长平均值，单位为分钟

# 统一提取每条消息对应的服务品牌
dt[, brand := ifelse(group == "Support", id, str_extract(tweet, "@\\w+") %>% str_remove("@"))]
# 按用户+品牌划分会话，计算每个会话的起始、结束时间
# 如需增加会话切分规则（比如用户超过2小时未回复就算新会话），可在此处增加时间差判断逻辑
session_dt <- dt[, .(session_start = min(created_at), session_end = max(created_at)), 
                 by = .(user_id = id, brand)]
# 计算单个会话的总时长
session_dt[, session_total_time := difftime(session_end, session_start, units = "mins") %>% as.numeric()]

# 按品牌聚合求会话总时长平均值
avg_total_session <- session_dt[, .(Total_avg_response_time = round(mean(session_total_time, na.rm = T), 2)), 
                                by = .(id = brand, group = "Support")]

步骤4：合并两个指标输出结果 #

# 关联两个指标表得到最终结果
final_result <- avg_per_tweet[avg_total_session, on = c("id", "group")]
# 输出为竖线分隔的结果文件
fwrite(final_result, "结果输出路径.csv", sep = "|")
# 控制台打印查看样例
head(final_result)

注意事项 #

• 代码已自动过滤response_tweet_id为NA的无效匹配项
• 200万行数据运行建议预留至少8G内存，data.table的运算效率可以满足需求
• 如需调整精度，可修改round函数的保留小数位数参数

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