Is there a way in R to total specific columns that aren't integers?-CodePudding

Really stuck with a problem in R and hoping to get some help on it.

Consider the following table:

Vars	Democrats(M.F)	Republicans(M.F)
votes(MI)	30 . 53	40 . 23
votes(TX)	64 . 42	45 . 25
votes(COL)	30 . 59	20 . 23
votes(NY)	64 . 40	18 . 34

I want an additional column that sums all the values above in the same M.F format given, so:

Vars	Democrats(M.F)	Republicans(M.F)
votes(MI)	30 . 53	40 . 23
votes(TX)	64 . 42	45 . 25
votes(COL)	30 . 59	20 . 23
votes(NY)	64 . 40	18 . 34
TOTAL	188 . 194	123 . 105

Does anyone know a simple way to do this? I've come up with solutions that require a complete overhaul of the table while ideally I don't want. Thanks in advance!

Sample Data

df <- structure(list(HOW = structure(c(2L, 4L, 3L, 1L), .Label = c("4", "1", "3", "2"), class = "factor"), Democrats = structure(c("1" = 2L, "2" = 4L, "3" = 3L, "4" = 1L), .Label = c("0 . 0", "1 . 2", "3 . 1", "4 . 6"), class = "factor"), Repubs = structure(c("1" = 2L, "2" = 3L, "3" = 4L, "4" = 1L), .Label = c("0 . 2", "1 . 1", "5 . 2", "5 . 7"), class = "factor")), class = "data.frame", row.names = c(NA, -4L))

CodePudding user response：

A base solution:

df[] <- lapply(df, as.character)
rbind(
  df,
  c(HOW = "Total", lapply(df[-1], \(x) paste(rowSums(sapply(strsplit(x, " . "), as.numeric)), collapse = " . ")))
)

#     HOW Democrats  Repubs
# 1     1     1 . 2   1 . 1
# 2     2     4 . 6   5 . 2
# 3     3     3 . 1   5 . 7
# 4     4     0 . 0   0 . 2
# 5 Total     8 . 9 11 . 12

CodePudding user response：

Here is a dplyr answer.

library(tidyverse)

df <- structure(list(HOW = structure(c(2L, 4L, 3L, 1L), .Label = c("4", "1", "3", "2"), class = "factor"), Democrats = structure(c("1" = 2L, "2" = 4L, "3" = 3L, "4" = 1L), .Label = c("0 . 0", "1 . 2", "3 . 1", "4 . 6"), class = "factor"), Repubs = structure(c("1" = 2L, "2" = 3L, "3" = 4L, "4" = 1L), .Label = c("0 . 2", "1 . 1", "5 . 2", "5 . 7"), class = "factor")), class = "data.frame", row.names = c(NA, -4L))

df2 <- df %>% 
    separate(Democrats, c("Democrats.M", "Democrats.F")) %>% 
    separate(Repubs, c("Repubs.M", "Repubs.F")) %>% 
    mutate(across(-HOW, as.integer))

df2 %>% 
    summarize(HOW = "Total", across(-HOW, sum)) %>% 
    bind_rows(df2, .) %>% 
    unite("Democrats", starts_with("Democrats."), sep = " . ") %>% 
    unite("Repubs", starts_with("Repubs."), sep = " . ")

But to be honest, I recommend keeping the data in separate columns and only combining at the very end for display.