Data frame aggregating across rows

Question

With a data frame df like below

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)

My data contains five political parties: IND, RP(K), DEM, REP, and BSP. I would like to create two new scoring columns:

• DRP: DEM score + RP(K) score
• RSP: REP score + BSP score

Additionally, I would like to include columns that group these scores at the District and County level.

How do I go about it with dplyr preferably. I'm thinking of the group function, however haven't been able to figure out the logic for that.

Answer 1

Using dplyr, if you want just two columns with sum on district and sum on county level for the parties:

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)) 

Or if you want an overall statistic for parties on district and on county level:

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

By using dplyr you could do something like this.

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"]))

Note: I think it is better if you keep everything in the same dataframe, but it is of course depending on the data size. Also for future analysis of the dataframe and for model/visualization purposes it might be better to go with mutate instead of summarise, although it would give a cleaner output.

Also, you could probably skip ungroup(), but I believe it is safer to have it included.