R Transformer Data By Col Names

Question

I have these data above and wish to sum score by common titles (Zebra fish, Car-Po Wax) to get this data below

I know I am supposed to insert code instead but I do not know how to make a variable name in R with a space, I tried but it gives "Zebra..fish"

Answer 1

Data

quux <- data.frame(Score=1:3, "Zebra (text)"=c(5,1,1), "Zebra (feow)"=c(4,1,4), "Car-Po (wordz)"=c(2,2,5), "Car-Po (bat)"=c(2,4,3), check.names=FALSE)

dplyr

library(dplyr)
library(tidyr)
quux |>
  pivot_longer(-Score) |>
  mutate(name = trimws(sub("\\(.*", "", name))) |>
  summarize(value = sum(value), .by = c(Score, name)) |>
  pivot_wider(id_cols = Score)
# # A tibble: 3 × 3
#   Score Zebra `Car-Po`
#   <int> <dbl>    <dbl>
# 1     1     9        4
# 2     2     2        6
# 3     3     5        8

data.table

DT <- as.data.table(quux)
library(data.table)
melt(DT, id.vars = "Score"
  )[, variable := trimws(sub("\\(.*", "", variable))
  ][, .(value = sum(value)), by = c("Score", "variable")] |>
  dcast(Score ~ variable, value.var = "value")
# Key: <Score>
#    Score Car-Po Zebra
#    <int>  <num> <num>
# 1:     1      4     9
# 2:     2      6     2
# 3:     3      8     5

base R

nms <- colnames(quux)
split(nms, trimws(sub("\\(.*", "", nms))) |>
  lapply(function(nm) rowSums(quux[,nm,drop=FALSE])) |>
  data.frame(check.names = FALSE)
#   Car-Po Score Zebra
# 1      4     1     9
# 2      6     2     2
# 3      8     3     5