基于多条件与时间范围的R语言自定义分组金额聚合需求

核心需求 #

需要构建动态分组逻辑对数据集的amount列进行聚合，规则如下：

• 同一transaction_id的所有行必须归为同一组
• 若某seller_ID在其上次出现的24小时（含）内再次出现，对应的transaction_id需并入之前的分组；若间隔超过24小时，则独立成组

原始数据集 #

data <- tibble(
  date = as.POSIXct(c('2023-07-01 12:00:00', '2023-07-01 13:00:00', '2023-07-02 12:00:00',  
                      '2023-07-03 14:00:00', '2023-07-03 16:00:00', '2023-07-04 12:00:00', '2023-07-04 14:00:00', 
                      '2023-07-05 10:00:00', '2023-07-05 12:00:00')),
  transaction_id = c(1, 1, 2, 3, 3, 4, 4, 5, 5),
  seller_ID = c(201, 202, 201, 201, 204, 205, 206, 201, 207),
  amount = c(100, 150, 200, 300, 350, 400, 450, 500, 550),
)

预处理：添加时间差字段 #

已计算每个seller_ID与上次出现的时间间隔（小时）：

ordered_data <- data %>%
  group_by(seller_ID) %>%
  arrange(seller_ID, date) %>%
  mutate(time_diff_hours = as.numeric(difftime(date, lag(date, default = first(date)), units = "hours"))) %>%
  ungroup()

分组规则细节 #

1. 同一transaction_id的所有行强制归为一组；
2. 当seller_ID两次出现的时间间隔≤24小时，后出现的transaction_id需与之前关联的分组合并（例如transaction_id=2的seller_ID=201与transaction_id=1的seller_ID=201间隔24小时，需合并为同一组）；
3. 当seller_ID两次出现的时间间隔>24小时，后出现的transaction_id不与之前的分组合并（例如transaction_id=3和5中的seller_ID=201与之前的间隔均超24小时，各自独立分组）。

目标分组结果 #

期望生成的分组列ideal_groups如下：

ordered_data <- ordered_data  %>%
  mutate(ideal_groups = c(1,1,2,3,1,2,4,4,3))

实现方案 #

采用图论连通分量思路：将需要合并的transaction_id视为图中的节点，关联的transaction_id之间建立边，最终每个连通分量即为一个分组。

代码实现 #

library(dplyr)
library(igraph)

# 1. 构建关联边：同一transaction_id的自关联
trans_self_links <- ordered_data %>%
  distinct(transaction_id) %>%
  mutate(to_trans = transaction_id)

# 2. 构建关联边：seller_ID在24小时内出现的transaction_id对
trans_seller_links <- ordered_data %>%
  group_by(seller_ID) %>%
  arrange(date) %>%
  filter(time_diff_hours <= 24) %>%
  mutate(prev_trans = lag(transaction_id)) %>%
  filter(!is.na(prev_trans)) %>%
  ungroup() %>%
  select(from_trans = transaction_id, to_trans = prev_trans) %>%
  # 添加双向边，确保连通性
  bind_rows(rename(., from_trans = to_trans, to_trans = from_trans)) %>%
  distinct()

# 3. 合并所有边并构建图
all_links <- bind_rows(trans_self_links, trans_seller_links) %>%
  distinct()

graph <- graph_from_data_frame(all_links, directed = FALSE)
component_membership <- components(graph)$membership

# 4. 映射分组ID到原始数据
trans_group_map <- tibble(
  transaction_id = names(component_membership),
  group_id = component_membership
)

final_data <- ordered_data %>%
  left_join(trans_group_map, by = "transaction_id") %>%
  # 将分组ID调整为连续的整数
  mutate(group_id = dense_rank(group_id))

# 查看结果
print(final_data)

聚合计算 #

基于生成的group_id对amount列聚合：

aggregated_data <- final_data %>%
  group_by(group_id) %>%
  summarise(total_amount = sum(amount))

print(aggregated_data)

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