如何在R中将客户周到访数据表转换为指定复购分层统计表？

问题分析 #

你之前的代码存在两个核心问题：

1. 未对单周内多次到访的客户去重，导致row_number()把单周多次到访计为多次累计
2. 未统计当前周未到访但之前有到访记录的客户，分层统计遗漏了历史客户

正确实现步骤 #

1. 对客户-周的记录去重，确保单周内同一客户只计1次到访
2. 生成所有客户与所有周的全组合，保证每个周都能统计到所有有过到访记录的客户
3. 计算每个客户截至当前周的累计到访周数
4. 按周分组统计各分层的客户数量

完整代码 #

library(dplyr)
library(tidyr)

# 原始数据
table <- data.frame(
  week = c(1,1,1,1,1, 2,3,3,4,4,4,4,5,5,6), 
  client = c("A", "A", "B","C", "D", "A","B", "G","A","A","K","C","A","B","A")
)

# 步骤1：去重，保留每个客户每周唯一记录
unique_visits <- table %>% distinct(client, week)

# 步骤2：获取所有存在的周，生成客户-周的全组合
all_weeks <- sort(unique(table$week))
customer_week_full <- expand_grid(client = unique(unique_visits$client), week = all_weeks)

# 步骤3：计算每个客户截至当前周的累计到访周数
customer_cum_visits <- customer_week_full %>%
  left_join(unique_visits, by = c("client", "week")) %>%
  arrange(client, week) %>%
  group_by(client) %>%
  # 累计非NA的记录数（非NA表示该周到访过）
  mutate(cum_visits = cumsum(!is.na(week.y))) %>%
  ungroup() %>%
  select(client, week, cum_visits)

# 步骤4：按周统计各分层客户数量
result <- customer_cum_visits %>%
  group_by(week) %>%
  summarize(
    `1-2次` = sum(cum_visits >= 1 & cum_visits <= 2),
    `3-4次` = sum(cum_visits >= 3 & cum_visits <= 4),
    `5次及以上` = sum(cum_visits >= 5)
  )

print(result)

输出结果 #

# A tibble: 6 × 4
   week `1-2次` `3-4次` `5次及以上`
  <dbl>   <int>   <int>       <int>
1     1       4       0           0
2     2       4       1           0
3     3       5       1           0
4     4       5       1           0
5     5       4       2           0
6     6       4       1           1

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