如何合并数据集统计各分组女性战胜男性的对战胜场数

统计规则 #

• 关联两类基础数据：各分组的对战胜负记录数据集、对应分组的个体性别信息数据集
• 统计维度：按分组独立计算，输出每位女性个体战胜男性个体的累计胜场数
• 输出字段要求：女性个体ID、战胜男性的总胜场数、所属分组

示例参考（C.1分组） #

对战胜负记录样例（共591条，截取前10条） #

Winner  Loser
1   George   Paul
2   George   Paul
3   George   Paul
4    Horst   Paul
5      Tom Louise
6   George  Horst
7   George Louise
8      Tom  Nobel
9   George  Adele
10   Rufus   Paul

个体性别数据集（C1dat）样例 #

Individual  Gender
Adele        F
George       M
Horst        M
Laggie       M
Louise       F
Max          M
Nobel        F
Paul         M
Rufus        M
Tom          M

期望输出格式 #

Individual female      Number of wins against males          Group
Adele                                 0                         C.1
Louise                                0                         C.1
Nobel                                 0                         C.1

实现步骤 #

1. 批量导入所有分组的两类数据集，给每组数据添加Group字段标记所属分组（比如C.1组数据统一标记Group值为C.1）
2. 给对战记录匹配性别信息：将胜负表的Winner字段与性别表的个体ID字段匹配，得到胜者性别；将Loser字段与性别表的个体ID字段匹配，得到败者性别
3. 筛选有效记录：仅保留*胜者性别为女性（F）、败者性别为男性（M）*的对战记录
4. 分组计数：按「所属分组+女性胜者ID」维度聚合，计算每个个体的胜场总数
5. 结果补全：把分组内所有女性个体都纳入结果表，没有胜场的个体胜场值填0，调整字段顺序后输出即可

示例实现代码（R语言） #

library(dplyr)
library(tidyr)

# 读取C.1分组数据
# 对战记录
c1_battle <- data.frame(
  Winner = c("George","George","George","Horst","Tom","George","George","Tom","George","Rufus"),
  Loser = c("Paul","Paul","Paul","Paul","Louise","Horst","Louise","Nobel","Adele","Paul")
)
# 性别信息
c1_gender <- data.frame(
  Individual = c("Adele","George","Horst","Laggie","Louise","Max","Nobel","Paul","Rufus","Tom"),
  Gender = c("F","M","M","M","F","M","F","M","M","M")
)

# 统计计算
c1_result <- c1_battle %>%
  # 关联胜者性别
  left_join(c1_gender, by = c("Winner" = "Individual")) %>%
  rename(winner_gender = Gender) %>%
  # 关联败者性别
  left_join(c1_gender, by = c("Loser" = "Individual")) %>%
  rename(loser_gender = Gender) %>%
  # 筛选女胜男的记录
  filter(winner_gender == "F", loser_gender == "M") %>%
  # 统计胜场
  count(Winner, name = "Number of wins against males") %>%
  # 调整字段名
  rename(`Individual female` = Winner) %>%
  # 补全所有女性个体（胜场为0的也展示）
  full_join(
    c1_gender %>% filter(Gender == "F") %>% select(Individual),
    by = c("Individual female" = "Individual")
  ) %>%
  mutate(
    `Number of wins against males` = replace_na(`Number of wins against males`, 0),
    Group = "C.1"
  )

# 打印输出结果
print(c1_result)

多分组批量统计时，把上述逻辑封装成函数，遍历所有分组的文件路径批量计算，最后把所有分组的结果行合并即可。

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