Spread unique values (in multiple columns) to different columns and paste aggregated values

Question

I have a dataframe as below:

structure(list(Value = c(1, 2, 3, 4), col1 = structure(c(1L, 
1L, 2L, 2L), .Label = c("A1", "A2"), class = "factor"), col2 = structure(c(1L, 
2L, 2L, 1L), .Label = c("B1", "B2"), class = "factor"), col3 = structure(1:4, .Label = c("C1", 
"C2", "C3", "C4"), class = "factor")), class = "data.frame", row.names = c(NA, 
-4L))

I want to spread unique values in each column to different columns using data.table and paste the summed value (from column 'Value') under each column For ex: Column col1 has 2 unique values A1 and A2. Sum of A1 is 3 and A2 is 7 Similarly, Column col2 has 2 unique values B1 and B2. Sum of B1 is 5 and B2 is 5

This operation will be performed for each of columns col1, col2 and col3.

The expected output is as below

structure(list(A1 = 3, A2 = 7, B1 = 5, B2 = 5, C1 = 1, C2 = 2, 
    C3 = 3, C4 = 4), class = "data.frame", row.names = c(NA, 
-1L))

How can I achieve this in R ?

Answer 1

data.table version of @Sotos' answer would be :

library(data.table)

dcast(melt(setDT(df), 'Value')[, .(Total = sum(Value)), value],
           rowid(value)~value, value.var = 'Total')

#   value A1 A2 B1 B2 C1 C2 C3 C4
#1:     1  3  7  5  5  1  2  3  4

Probably, you don't need the value column so you can drop it by adding [, value := NULL][]

Answer 2

I am not very acclimated with data.tablebut a tidyverse solution can be,

library(dplyr)
library(tidyr)

df %>% 
 pivot_longer(starts_with('col')) %>% 
 group_by(value) %>% 
 summarise(res = sum(Value)) %>% 
 pivot_wider(names_from = value, values_from = res)

which gives,

# A tibble: 1 x 8
     A1    A2    B1    B2    C1    C2    C3    C4
  <dbl> <dbl> <dbl> <dbl> <dbl> <dbl> <dbl> <dbl>
1     3     7     5     5     1     2     3     4

Answer 3

Base R version (another data.table wannabe):

t(unstack(
    with(reshape(df, direction="long", 
             varying=grep("^col", names(df), value=TRUE), sep=""),
     aggregate(formula=Value~col, FUN=sum)), 
  form=Value~col))

---

    A1 A2 B1 B2 C1 C2 C3 C4
res  3  7  5  5  1  2  3  4

Answer 4

Here is another base R solution

dfout <- t(do.call(rbind,
                   lapply(seq_along(df)[-1], 
                          function(k) unstack(rev(aggregate(Value~.,df[c(1,k)],sum))))))

such that

> dfout
    A1 A2 B1 B2 C1 C2 C3 C4
res  3  7  5  5  1  2  3  4

DATA

df <- structure(list(Value = c(1, 2, 3, 4), col1 = structure(c(1L, 
1L, 2L, 2L), .Label = c("A1", "A2"), class = "factor"), col2 = structure(c(1L, 
2L, 2L, 1L), .Label = c("B1", "B2"), class = "factor"), col3 = structure(1:4, .Label = c("C1", 
"C2", "C3", "C4"), class = "factor")), class = "data.frame", row.names = c(NA, 
-4L))

Answer 5

Here is another option:

library(data.table)
x <- rbindlist(lapply(paste0("col", 1:3), function(b) df[, sum(Value), b]), 
    use.names=FALSE)

setDT(setNames(as.list(x$V1), x$col1))[]

data:

df <- structure(list(Value = c(1, 2, 3, 4), col1 = structure(c(1L, 
1L, 2L, 2L), .Label = c("A1", "A2"), class = "factor"), col2 = structure(c(1L, 
2L, 2L, 1L), .Label = c("B1", "B2"), class = "factor"), col3 = structure(1:4, .Label = c("C1", 
"C2", "C3", "C4"), class = "factor")), class = "data.frame", row.names = c(NA, 
-4L))

Answer 6

You could also solve it as follows:

library(data.table)
melt(setDT(df), "Value")[, .(TOT = sum(Value)), value][, setNames(as.list(TOT), value)]

#       A1    A2    B1    B2    C1    C2    C3    C4
# 1:     3     7     5     5     1     2     3     4