如何在R中通过年度企业客户组合识别新增客户？

核心逻辑 #

新增客户指企业在初始运营年份之后首次合作的客户，具体判断规则：

1. 先确定每个企业有客户记录的最早年份（初始年份），该年份的客户不算新增
2. 对每个企业-客户组合，找到首次合作年份，若该年份晚于企业初始年份，则视为该年度的新增客户
3. 仅保留每个新增客户首次出现的记录

方法一：使用dplyr（适合中等数据量，代码易读） #

步骤1：构造示例数据

df <- data.frame(
  client.id = c(1,1,1,2,1,2,3,4,5,5,6,5,6,7),
  client.name = c("A","A","A","B","A","B","C","D","E","E","F","E","F","G"),
  year = c(2013,2014,2015,2015,2016,2016,2016,2013,2013,2014,2014,2015,2015,2015),
  firm.id = c(1,1,1,1,1,1,1,2,2,2,2,2,2,2),
  firm.name = c("AA","AA","AA","AA","AA","AA","AA","BB","BB","BB","BB","BB","BB","BB")
)

步骤2：计算并筛选新增客户

library(dplyr)

new_clients <- df %>%
  # 计算每个企业的初始年份
  group_by(firm.id, firm.name) %>%
  mutate(firm_min_year = min(year)) %>%
  ungroup() %>%
  # 计算每个企业-客户组合的首次合作年份
  group_by(firm.id, client.id, client.name) %>%
  mutate(first_year = min(year)) %>%
  # 筛选新增客户：首次合作晚于企业初始年份，且仅保留首次记录
  filter(first_year > firm_min_year, year == first_year) %>%
  # 去重（避免同年度同客户多条重复记录）
  distinct(firm.id, client.id, year, .keep_all = TRUE) %>%
  # 移除辅助计算列
  select(-firm_min_year, -first_year) %>%
  # 按企业、年份排序
  arrange(firm.id, year)

# 查看结果
print(new_clients)

方法二：使用data.table（适合大数据量，运行高效） #

library(data.table)

setDT(df)

# 计算每个企业的初始年份
df[, firm_min_year := min(year), by = .(firm.id, firm.name)]
# 计算每个企业-客户组合的首次合作年份
df[, first_year := min(year), by = .(firm.id, client.id)]
# 筛选新增客户并去重
new_clients <- df[first_year > firm_min_year & year == first_year, 
                  .(client.id, client.name, year, firm.id, firm.name)]
new_clients <- unique(new_clients, by = c("firm.id", "client.id", "year"))
# 按企业、年份排序
setorder(new_clients, firm.id, year)

# 查看结果
print(new_clients)

最终输出结果 #

两种方法都会得到与你期望一致的结果：

client.id client.name year firm.id firm.name
1         2           B 2015       1        AA
2         3           C 2016       1        AA
3         6           F 2014       2        BB
4         7           G 2015       2        BB

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