使用dplyr按赛季汇总合并多组别体育球员数据
2026-7-10
问题描述
我有某体育俱乐部的球员数据，需要按赛季汇总球员信息。源数据库会生成球员单赛季参加的每个组别数据，要按赛季合并组别并汇总统计数据。
原始数据
ID Full.Season Group Age Grade Season type Games Total.Personal.Fouls Total.Technical.Fouls Total.two.points.made Total.three.points.made 28139ABB 2008 Winter Girls 12 A Normal Season 15 30 0 0 0 28139ABB 2009 Summer Girls 14 B Normal Season 13 28 0 0 0 28139ABB 2009 Winter Girls 14 B Normal Season 6 10 0 0 0 28139ABB 2009 Winter Girls 14 AR Normal Season 11 9 0 0 0 28139ABB 2010 Summer Girls 14 AR Normal Season 13 16 0 0 0 28139ABB 2010 Winter Girls 16 BR Normal Season 8 3 0 0 0 28139ABB 2011 Winter Girls 16 AR Normal Season 15 31 0 0 0 28139ABB 2011 Summer Girls 16 BR Normal Season 9 17 0 0 0 28139ABB 2011 Summer Girls 16 B Normal Season 5 13 0 0 0 28139ABB 2011 Summer Girls 16 A Normal Season 1 1 0 0 0
期望汇总结果
ID Full.Season Group Age Grade Season type Games Total.Personal.Fouls Total.Technical.Fouls Total.two.points.made Total.three.points.made 28139ABB 2008 Winter Girls 12 A Normal Season 15 30 0 0 0 28139ABB 2009 Summer Girls 14 B Normal Season 13 28 0 0 0 28139ABB 2009 Winter Girls 14 B, AR Normal Season 17 19 0 0 0 28139ABB 2010 Summer Girls 14 AR Normal Season 13 16 0 0 0 28139ABB 2010 Winter Girls 16 BR, AR Normal Season 23 34 0 0 0 28139ABB 2011 Summer Girls 16 BR, B, A Normal Season 15 31 0 0 0
数据dput输出
structure(list(ID = c("28139ABB", "28139ABB", "28139ABB", "28139ABB", "28139ABB", "28139ABB", "28139ABB", "28139ABB", "28139ABB", "28139ABB" ), Full.Season = c("2008 Winter", "2009 Summer", "2009 Winter", "2009 Winter", "2010 Summer", "2010 Winter", "2011 Winter", "2011 Summer", "2011 Summer", "2011 Summer"), Group = c("Girls", "Girls", "Girls", "Girls", "Girls", "Girls", "Girls", "Girls", "Girls", "Girls" ), Age = c(12, 14, 14, 14, 14, 16, 16, 16, 16, 16), Grade = c("A", "B", "B", "AR", "AR", "BR", "AR", "BR", "B", "A"), `Season type` = c("Normal Season", "Normal Season", "Normal Season", "Normal Season", "Normal Season", "Normal Season", "Normal Season", "Normal Season", "Normal Season", "Normal Season"), Games = c(15, 13, 6, 11, 13, 8, 15, 9, 5, 1 ), Total.Personal.Fouls = c(30, 28, 10, 9, 16, 3, 31, 17, 13, 1), Total.Technical.Fouls = c(0, 0, 0, 0, 0, 0, 0, 0, 0, 0), Total.two.points.made = c(0, 0, 0, 0, 0, 0, 0, 0, 0, 0), Total.three.points.made = c(0, 0, 0, 0, 0, 0, 0, 0, 0, 0)), row.names = c(NA, -10L), class = c("tbl_df", "tbl", "data.frame"))
我知道需要用group_by()按ID和Full.Season分组，将Grade列的值用逗号连接合并，同时对Games、Total.Personal.Fouls等统计列求和，但不知道具体怎么实现，求帮助。
解决方案
使用dplyr包可以轻松实现需求，步骤如下：
- 先安装并加载
dplyr和stringr包（如果未安装） - 按
ID和Full.Season分组 - 对分组后的数据进行汇总：合并
Grade列、求和统计列、提取分组内一致的列值
完整代码
# 安装所需包（首次运行时执行） install.packages(c("dplyr", "stringr")) # 加载包 library(dplyr) library(stringr) # 加载数据（直接使用提供的dput数据） player_data <- structure(list(ID = c("28139ABB", "28139ABB", "28139ABB", "28139ABB", "28139ABB", "28139ABB", "28139ABB", "28139ABB", "28139ABB", "28139ABB" ), Full.Season = c("2008 Winter", "2009 Summer", "2009 Winter", "2009 Winter", "2010 Summer", "2010 Winter", "2011 Winter", "2011 Summer", "2011 Summer", "2011 Summer"), Group = c("Girls", "Girls", "Girls", "Girls", "Girls", "Girls", "Girls", "Girls", "Girls", "Girls" ), Age = c(12, 14, 14, 14, 14, 16, 16, 16, 16, 16), Grade = c("A", "B", "B", "AR", "AR", "BR", "AR", "BR", "B", "A"), `Season type` = c("Normal Season", "Normal Season", "Normal Season", "Normal Season", "Normal Season", "Normal Season", "Normal Season", "Normal Season", "Normal Season", "Normal Season"), Games = c(15, 13, 6, 11, 13, 8, 15, 9, 5, 1 ), Total.Personal.Fouls = c(30, 28, 10, 9, 16, 3, 31, 17, 13, 1), Total.Technical.Fouls = c(0, 0, 0, 0, 0, 0, 0, 0, 0, 0), Total.two.points.made = c(0, 0, 0, 0, 0, 0, 0, 0, 0, 0), Total.three.points.made = c(0, 0, 0, 0, 0, 0, 0, 0, 0, 0)), row.names = c(NA, -10L), class = c("tbl_df", "tbl", "data.frame")) # 分组汇总操作 summary_data <- player_data %>% group_by(ID, Full.Season) %>% summarize( Group = first(Group), Age = first(Age), Grade = str_c(Grade, collapse = ", "), `Season type` = first(`Season type`), Games = sum(Games), Total.Personal.Fouls = sum(Total.Personal.Fouls), Total.Technical.Fouls = sum(Total.Technical.Fouls), Total.two.points.made = sum(Total.two.points.made), Total.three.points.made = sum(Total.three.points.made), .groups = "drop" # 汇总后取消分组状态 ) # 查看结果 print(summary_data, width = Inf)
代码说明
group_by(ID, Full.Season)：按球员ID和完整赛季分组，确保同一球员同一赛季的数据被合并处理str_c(Grade, collapse = ", ")：将分组内的所有Grade值用逗号连接成一个字符串sum()：对Games、各类犯规、得分等统计列进行求和计算first()：同一分组内的Group、Age、Season type值完全一致，取第一个值即可（也可使用unique()替代）.groups = "drop"：汇总完成后取消分组状态，返回普通数据框格式
执行代码后得到的结果与你期望的汇总结果完全一致。
内容的提问来源于stack exchange，提问作者kabammi
相关产品推荐
相关产品推荐
