跨行聚合数据帧

Question

具有如下所示的数据帧df

text <- "
State,District,County,Num Voters,Total Votes in State,Votes for None,Candidate Name,Party,Votes Scored
CA,San Diego,Delmar,190962,48026634,2511,A1,IND,949
CA,San Diego,Delmar,190962,48026634,2511,A2,RP(K),44815
CA,San Diego,Delmar,190962,48026634,2511,A3,IND,1036
CA,San Diego,Delmar,190962,48026634,2511,A4,DEM,29235
CA,San Diego,Delmar,190962,48026634,2511,A5,IND,5064
CA,San Diego,Delmar,190962,48026634,2511,A6,IND,803
CA,San Diego,Delmar,190962,48026634,2511,A7,REP,22329
CA,San Diego,Delmar,190962,48026634,2511,A8,BSP,43553
CA,San Diego,La Jolla,190257,48026634,3629,A1,IND,972
CA,San Diego,La Jolla,190257,48026634,3629,A2,RP(K),66168
CA,San Diego,La Jolla,190257,48026634,3629,A3,IND,2763
CA,San Diego,La Jolla,190257,48026634,3629,A4,DEM,32792
CA,San Diego,La Jolla,190257,48026634,3629,A5,IND,8629
CA,San Diego,La Jolla,190257,48026634,3629,A6,IND,1191
CA,San Diego,La Jolla,190257,48026634,3629,A7,REP,28002
CA,San Diego,La Jolla,190257,48026634,3629,A8,BSP,2555
"
df <- read.table(textConnection(text), sep = ",", header = TRUE)

我的数据包含五个政党: IND、RP(K)、DEM、REP和BSP。我想创建两个新的评分栏：

• DRP: DEM评分+ RP(K)评分
• RSP: REP分数+ BSP分数

此外，我想包括在地区和县一级对这些分数进行分组的专栏。

我最好是用dplyr来做。我想到的是group函数，但是还没有弄清楚这方面的逻辑。

Answer 1

使用dplyr，如果您希望只有两个列，其中地区和县一级的sum代表各方：

df %>%
  mutate(Party2 = ifelse(Party == "DEM" | Party == "RP(K)", "DRP", 
                         ifelse(Party == "REP" | Party == "BSP", "RSP", paste(Party)))) %>%
  group_by(District, Party2) %>%
  mutate(Votes.Scored.District = sum(Votes.Scored)) %>%
  ungroup() %>%
  group_by(County, Party2) %>%
  mutate(Votes.Scored.County = sum(Votes.Scored)) 

或者，如果你想要一个地区和县级政党的总体统计数据：

df %>%
  mutate(Party2 = ifelse(Party == "DEM" | Party == "RP(K)", "DRP", 
                         ifelse(Party == "REP" | Party == "BSP", "RSP", paste(Party)))) %>%
  group_by(District, Party2) %>%
  mutate(Votes.Scored.District = sum(Votes.Scored)) %>%
  ungroup() %>%
  group_by(County, Party2) %>%
  mutate(Votes.Scored.County = sum(Votes.Scored)) %>%
  group_by(Party2) %>%
  summarise(Votes.Scored.District = min(Votes.Scored.District),
            Votes.Scored.County = min(Votes.Scored.County))

# A tibble: 3 x 3
  Party2 Votes.Scored.District Votes.Scored.County
  <chr>                  <dbl>               <dbl>
1 DRP                  173010.              74050.
2 IND                   21407.               7852.
3 RSP                   96439.              30557.

Answer 2

通过使用dplyr，您可以这样做。

tg <- df %>%
  group_by(County) %>%
  mutate(DRP_county = sum(Votes.Scored[Party == "RP(K)" | Party == "DEM"]),
         RSP_county = sum(Votes.Scored[Party == "REP" | Party == "BSP"])) %>%
  ungroup() %>% 
  group_by(District) %>%
  mutate(DRP_district = sum(Votes.Scored[Party == "RP(K)" | Party == "DEM"]),
         RSP_district = sum(Votes.Scored[Party == "REP" | Party == "BSP"]))

注意：--我认为最好将所有内容保持在相同的数据格式中，但这当然取决于数据大小。另外，对于数据的未来分析和模型/可视化的目的，最好是使用mutate而不是summarise，尽管它会提供更清晰的输出。

另外，您可能可以跳过ungroup()，但我认为包含它更安全。