sum up column and take it as colnames- dataframe in R

Question

d <- data.frame(var1 = c(0,0,1),var2 = c(45.245,818.15,151.12),var3 = c(45,159,0),group = c('a','a','b'))
d
  var1    var2 var3 group
1    0  45.245   45     a
2    0 818.150  159     a
3    1 151.120    0     b

I want d$group to be the colnames and the former colnames (var1,var2,var3) to be the rownames. The values should be summed up

It should look like this

       a            b
var1   0             1
var2   863.395      151.12
var3   204          0

I tried pivot_longer(d,cols = 'group',names_to = c('var1','var2','var3') but I reveived Errors.

in the pivot_longer, you need `cols = -group`. After this you will need a pivot_wider — Richard Telford, Oct 07 '20 at 13:51

AlexB · Answer 1 · 2020-10-07T14:08:11.683

1

A slightly different approach would be to use values_fn argument to sum the values.

library(tidyr)

d %>%
  pivot_longer(-group, names_to = 'var', values_to = 'value') %>%
  pivot_wider(values_from = value, names_from = group, values_fn = {sum})

# var       a     b
# <chr> <dbl> <dbl>
# 1 var1     0     1 
# 2 var2   863.  151.
# 3 var3   204     0

edited Oct 07 '20 at 14:08

answered Oct 07 '20 at 13:55

AlexB

3,061
2
17
19

score 0 · Accepted Answer · answered Oct 07 '20 at 13:52

Try this reshaping your data and aggregating by group. You first have to summarise the values per group and then combine pivot_longer() and pivot_wider() to obtain the desired result. Here the code using tidyverse functions (I have used your d data):

library(dplyr)
library(tidyr)
#Code
newd <- d %>% group_by(group) %>%
  summarise_all(.funs = sum) %>%
  pivot_longer(-group) %>%
  pivot_wider(names_from = group,values_from=value)

Output:

# A tibble: 3 x 3
  name      a     b
  <chr> <dbl> <dbl>
1 var1     0     1 
2 var2   863.  151.
3 var3   204     0

score 0 · Answer 3 · answered Oct 07 '20 at 13:58

Does this work:

> d1 <- as.data.frame(t(d %>% group_by(group) %>% summarise(var1 = sum(var1), var2 = sum(var2), var3 = sum(var3)))[-1,])
`summarise()` ungrouping output (override with `.groups` argument)
> names(d1) <- c('a','b')
> d1
           a       b
var1       0       1
var2 863.395 151.120
var3     204       0
>

score 0 · Answer 4 · answered Oct 07 '20 at 14:07

For completion here is data.table method similar to @AlexB's tidyr approach :

library(data.table)
dcast(melt(setDT(d), id.vars = 'group'), 
      variable~group, value.var = 'value', fun.aggregate = sum)

#   variable   a   b
#1:     var1   0   1
#2:     var2 863 151
#3:     var3 204   0

sum up column and take it as colnames- dataframe in R

4 Answers4