基于客户ID与订单日期分组的字符串拼接及状态修改需求

需求说明 #

• 对数据框按customerid（客户ID）和orderdate（订单日期）分组：
  1. 每个分组内仅保留1条状态为"review"的记录，其余同分组的"review"状态改为"duplicate"
  2. 将分组内所有orderid（订单ID）拼接为字符串，分组内每条记录均显示该拼接结果

原始数据 #

customerid <- c("A1", "A1", "A2", "A2", "A3", "A3", "A3", "A4")
orderdate <- c("2018-09-14", "2018-09-14", "2018-09-15", "2018-09-15", "2020-08-21", "2020-08-21","2020-08-21", "2018-08-10")
orderid <- c("1", "2", "3", "4", "5", "6", "7", "8")
status <- c("review", "review", "review", "negative", "positive", "review", "review", "review")
df <- data.frame(customerid, orderdate, orderid, status)

实现代码 #

使用dplyr包完成分组操作，代码如下：

library(dplyr)

df_processed <- df %>%
  # 按客户ID和订单日期分组
  group_by(customerid, orderdate) %>%
  # 拼接分组内所有orderid为字符串
  mutate(orderid = paste(orderid, collapse = ",")) %>%
  # 处理status字段：仅保留第一条review，其余同分组review改为duplicate
  mutate(status = case_when(
    status != "review" ~ status,
    row_number() == which(status == "review")[1] ~ "review",
    TRUE ~ "duplicate"
  )) %>%
  # 取消分组
  ungroup()

# 查看处理结果
print(df_processed)

代码解释 #

1. group_by(customerid, orderdate)：指定分组依据，将同一客户同一日期的记录归为一组
2. mutate(orderid = paste(orderid, collapse = ","))：把当前分组内的所有orderid用逗号拼接成字符串，替换原字段值
3. case_when逻辑处理：
   • 非"review"状态的记录保持原状态不变
   • 定位分组内第一条status为"review"的记录，保留其状态
   • 其他status为"review"的记录统一改为"duplicate"
4. ungroup()：取消分组标记，将数据恢复为普通数据框格式

验证结果 #

处理后的数据框与期望结果完全一致：

customerid orderdate orderid     status
1         A1 2018-09-14    1,2     review
2         A1 2018-09-14    1,2 duplicate
3         A2 2018-09-15    3,4     review
4         A2 2018-09-15    3,4  negative
5         A3 2020-08-21  5,6,7  positive
6         A3 2020-08-21  5,6,7     review
7         A3 2020-08-21  5,6,7 duplicate
8         A4 2018-08-10      8     review

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