使用dplyr将分组数据与每组参考案例（党团领袖）对比

原始数据 #

df <- data.frame(
  PersonId = c(89156, 105340, 72029, 89535, 110339, 25446, 89156, 105340, 72029, 89535, 110339, 25446),
  VoteId = c(1,1,1,1,1,1,2,2,2,2,2,2),
  CaucusShortName = c("Conservative", "Conservative", "Conservative", "Liberal", "Liberal", "Independent", "Conservative", "Conservative", "Conservative", "Liberal", "Liberal", "Independent"),
  IsLeader = c(0,0,1,0,1,0,0,0,1,0,1,0),
  VoteValueName = c("Nay", "Yea", "Nay", "Yea", "Yea", "Nay", "Did not vote", "Nay", "Nay", "Yea", "Yea", "Yea")
)

需求说明 #

• 按VoteId和CaucusShortName分组
• 对每组内成员，将其VoteValueName与该组对应投票的党团领袖（IsLeader=1）的投票值对比，生成LeaderAgree列：一致为1，不一致为0；组内无领袖则填NA
• 每个党团单次投票最多1位领袖，领袖可能随投票变更
• 可先生成LeaderVote列（显示组内领袖的投票值，无领袖则为NA），再推导LeaderAgree

期望结果 #

带LeaderAgree列的最终结果 #

PersonId VoteId CaucusShortName IsLeader VoteValueName LeaderAgree
1      89156      1    Conservative        0           Nay           1
2     105340      1    Conservative        0           Yea           0
3      72029      1    Conservative        1           Nay           1
4      89535      1         Liberal        0           Yea           1
5     110339      1         Liberal        1           Yea           1
6      25446      1     Independent        0           Nay          NA
7      89156      2    Conservative        0  Did not vote           0
8     105340      2    Conservative        0           Nay           1
9      72029      2    Conservative        1           Nay           1
10     89535      2         Liberal        0           Yea           1
11    110339      2         Liberal        1           Yea           1
12     25446      2     Independent        0           Yea          NA

带LeaderVote列的中间结果 #

PersonId VoteId CaucusShortName IsLeader VoteValueName LeaderVote
1      89156      1    Conservative        0           Nay        Nay
2     105340      1    Conservative        0           Yea        Nay
3      72029      1    Conservative        1           Nay        Nay
4      89535      1         Liberal        0           Yea        Yea
5     110339      1         Liberal        1           Yea        Yea
6      25446      1     Independent        0           Nay         NA
7      89156      2    Conservative        0  Did not vote        Nay
8     105340      2    Conservative        0           Nay        Nay
9      72029      2    Conservative        1           Nay        Nay
10     89535      2         Liberal        0           Yea        Yea
11    110339      2         Liberal        1           Yea        Yea
12     25446      2     Independent        0           Yea         NA

解决方案 #

使用dplyr包实现，先分组提取领袖投票值，再对比生成结果：

library(dplyr)

df_result <- df %>%
  # 按单次投票+党团分组，适配领袖随投票变更的情况
  group_by(VoteId, CaucusShortName) %>%
  # 生成LeaderVote列：存在领袖则提取其投票值，否则为NA
  mutate(LeaderVote = ifelse(any(IsLeader == 1), VoteValueName[IsLeader == 1], NA_character_)) %>%
  # 生成LeaderAgree列：无领袖填NA，投票一致为1，不一致为0
  mutate(LeaderAgree = case_when(
    is.na(LeaderVote) ~ NA_integer_,
    VoteValueName == LeaderVote ~ 1L,
    TRUE ~ 0L
  )) %>%
  ungroup()

# 查看结果
print(df_result)

代码说明 #

• group_by(VoteId, CaucusShortName)：确保分组维度精准覆盖单次投票下的党团，处理领袖随投票变更的场景
• LeaderVote生成逻辑：通过any(IsLeader ==1)判断组内是否有领袖，有则提取对应投票值，无则设为NA
• LeaderAgree生成逻辑：用case_when分情况处理，保证NA值传递正确，对比结果符合需求

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