0

I wish to sum the rows (here in this case seats of parties in parliament) but only if the party voted yes (here indicated by 1 for yes and 0 for no).

Example data frame:

cabinet <- c("A", "B", "C")
seats1 <- c(20, 30, 40)
seats2 <- c(10, 15, 5)
seats3 <- c(10, 5, 10)
vote1 <- c(1, 1, 1)
vote2 <- c(1, 0, 1)
vote3 <- c(1, 0, 0)
df <- data.frame(cabinet, seats1, seats2, seats3, vote1, vote2, vote3)

So each row represents a vote and I want the total seats of all parties that voted for yes. I should also mention that sometimes some vote values are missing.

Thank you for your help!

Gregor Thomas
  • 136,190
  • 20
  • 167
  • 294
Denise Al
  • 1

1 Answers1

2

We may loop across the columns that starts with 'seats' multiply by the ones that starts with 'vote', and get the rowSums (0 * anyvalue -> 0)

library(dplyr)
df %>%
   mutate(total = rowSums(across(starts_with('seats')) * 
                           across(starts_with('vote'), ~ .x > 0), na.rm = TRUE))
  cabinet seats1 seats2 seats3 vote1 vote2 vote3 total
1       A     20     10     10     1     1     1    40
2       B     30     15      5     1     0     0    30
3       C     40      5     10     1     1     0    45
akrun
  • 874,273
  • 37
  • 540
  • 662
  • Thank you for your help! Sadly the calculated total is not correct when I use the code on my data. I am not sure what the problem could be. I also tried this solution: https://stackoverflow.com/questions/53569309/rowsums-set-of-columns-if-multiple-columns-conditions-are-met but here the total is also wrong (it is actually the same as when I use your solution). – Denise Al Oct 06 '22 at 15:37
  • @DeniseAl Do you have any other values in vote other than 1, 0 or NA – akrun Oct 06 '22 at 15:39
  • Oh, I just realized that sometimes vote can take the value 2 (for abstention).. Is there a workaround so that your solution still works? Thank you so much and sorry for not mentioning this before! – Denise Al Oct 06 '22 at 15:43
  • @DeniseAl just do `across(starts_with('vote'), ~ .x > 0)` (updated) – akrun Oct 06 '22 at 15:44
  • Sadly the calculated total is still wrong (it did not change from the solution before). Should I post a screenshot of my data? – Denise Al Oct 06 '22 at 15:57