Aggregating column totals in R

Question

Let's say I have a dataframe that looks like this:

variable1 <- c(1,1,1,0,1,0)
variable2 <- c(0,0,0,1,1,0)
variable3 <- c(1,0,1,0,1,1)

df <- data.frame(variable1, variable2, variable3)

What is the easiest way to get a dataframe output that looks like this:

   Variable     Total
   Variable1     4
   Variable2     2
   Variable3     3

colsums kind of gets me there, but the variable names aren't output as a legitimate column using this method.

Summarize and pivot. Or pivot and then summarize. – Reeza Jul 27 '21 at 16:02 — Reeza, Jul 27 '21 at 16:02

score 3 · Accepted Answer · answered Jul 27 '21 at 16:08

3

library(dplyr)
library(tidyr)
df %>% 
    pivot_longer(everything()) %>% 
    group_by(name) %>% 
    summarise(Total = sum(value))

# A tibble: 3 × 2
  name      Total
  <chr>     <dbl>
1 variable1     4
2 variable2     2
3 variable3     4

answered Jul 27 '21 at 16:08

user438383

5,716
8
28
43

this is the first answer I tried and it worked. Thanks! – DiamondJoe12 Jul 27 '21 at 19:27

score 2 · Answer 2 · answered Jul 27 '21 at 16:13

2

This could be another option:

df %>%
  tibble::rownames_to_column(var = "id") %>%
  janitor::adorn_totals()

    id variable1 variable2 variable3
     1         1         0         1
     2         1         0         0
     3         1         0         1
     4         0         1         0
     5         1         1         1
     6         0         0         1
 Total         4         2         4

answered Jul 27 '21 at 16:13

Anoushiravan R

21,622
3
18
41

1

Janitor comes to rescue many times in everyday use – AnilGoyal Jul 29 '21 at 08:17

score 2 · Answer 3 · answered Jul 27 '21 at 18:03

2

Using stack/colSums

stack(colSums(df))[2:1]
        ind values
1 variable1      4
2 variable2      2
3 variable3      4

answered Jul 27 '21 at 18:03

akrun

874,273
37
540
662

score 1 · Answer 4 · answered Jul 27 '21 at 16:14

1

You can try this.

variable1 <- c(1,1,1,0,1,0)
variable2 <- c(0,0,0,1,1,0)
variable3 <- c(1,0,1,0,1,1)

df <- data.frame(variable1, variable2, variable3)
> data.frame(Total= colSums(df))
          Total
variable1   4
variable2   2
variable3   4

answered Jul 27 '21 at 16:14

vivek

301
3
13

Sakshi Maurya · Answer 5 · 2021-07-30T10:53:55.390

1

## data frame
variable1 <- c(1,1,1,0,1,0)
variable2 <- c(0,0,0,1,1,0)
variable3 <- c(1,0,1,0,1,1)

df <- data.frame(variable1, variable2, variable3)
df

##using dplyr Library
library(dplyr)
new_df = df %>% summarise(across(variable1:variable3,sum)) # sum of ones in each column
t(new_df) # transpose new_df to get desired pattern

edited Jul 30 '21 at 10:53

answered Jul 27 '21 at 16:35

Sakshi Maurya

31
4

Welcome Sakshi to SO! typing `sum` for each variable again and again is not encouraged. Use `dplyr::across` in these cases. :) – AnilGoyal Jul 29 '21 at 15:30
1

@AnilGoyal Thanks for the information. I have made the edit. – Sakshi Maurya Jul 30 '21 at 10:54

score 1 · Answer 6 · answered Jul 29 '21 at 08:19

one more approach can be

library(tidyverse)

df %>%
  summarise(across(everything(), sum)) %>%
  pivot_longer(everything())

#> # A tibble: 3 x 2
#>   name      value
#>   <chr>     <dbl>
#> 1 variable1     4
#> 2 variable2     2
#> 3 variable3     4

^{Created on 2021-07-29 by the reprex package (v2.0.0)}

Aggregating column totals in R

6 Answers6