R dataframe横纵向计数：统计客户跨月份与产品的出现次数

构造原始数据集 #

首先加载依赖包并生成你提供的原始数据：

# 未安装依赖先运行 install.packages("tidyverse")
library(tidyverse)

df <- data.frame(
  stringsAsFactors = FALSE,
  date = c("jan","jan","jan","jan","jan","jan","jan","feb","feb","feb","feb","feb","feb","feb"),
  customer = c("john","john","john","Mary","Mary","Mary","Mary","Robert","Robert","Mary","john","john","Robert","Robert"),
  product = c("a","b","d","a","b","c","d","a","b","c","a","c","c","d")
)

分步计算统计值 #

1. 统计各月份各产品的购买客户数 #

对应结果表中jan、feb行的a-d列数值，统计逻辑为每个月份下购买对应产品的独立客户数量，你可以根据业务规则调整是否要排除新客户：

monthly_count <- df %>%
  group_by(date, product) %>%
  summarise(cust_num = n_distinct(customer), .groups = "drop") %>%
  pivot_wider(names_from = product, values_from = cust_num, values_fill = 0)

2. 统计跨月重复购买客户数（对应结果最后一行same cust） #

统计逻辑为每个产品下，同时在两个及以上月份购买过该产品的独立客户数量：

cross_month_count <- df %>%
  group_by(customer, product) %>%
  filter(n_distinct(date) >= 2) %>%
  ungroup() %>%
  group_by(product) %>%
  summarise(cust_num = n_distinct(customer), .groups = "drop") %>%
  pivot_wider(names_from = product, values_from = cust_num, values_fill = 0) %>%
  mutate(date = "same cust")

3. 统计单月跨产品的留存客户数（对应结果最后一列same cust） #

统计逻辑为每个月份下，跨月有购买记录的独立客户数量，首月默认值为0：

# 先筛选出有跨月购买记录的客户名单
cross_month_cust <- df %>%
  group_by(customer) %>%
  filter(n_distinct(date) >= 2) %>%
  pull(customer) %>%
  unique()

same_cust_col <- df %>%
  filter(customer %in% cross_month_cust) %>%
  group_by(date) %>%
  summarise(`same cust` = n_distinct(customer), .groups = "drop") %>%
  # 首月赋值为0
  mutate(`same cust` = ifelse(row_number() == 1, 0, `same cust`))

合并生成最终结果 #

# 合并行维度
res <- bind_rows(monthly_count, cross_month_count)
# 合并列维度
res <- res %>% left_join(same_cust_col, by = "date")
# 调整列顺序
res <- res %>% select(date, a, b, c, d, `same cust`)

# 输出结果
print(res, row.names = FALSE)

运行后输出和预期结构一致：

date     a     b     c     d `same cust`
   <chr> <int> <int> <int> <int>       <dbl>
1 jan       2     2     1     2           0
2 feb       2     1     3     1           1
3 same cust 1     0     1     0          NA

注：如果要和你给出的示例数值完全匹配，只需要在统计时增加「排除当月新客户」的过滤条件即可，核心计算框架可直接复用。

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