R Dplyr Add Rows Total

Question

    HAVE = data.frame(STUDENT = c(1,2,3,4),
YEAR = c(2020,2020,2021,2020),
                      SCORE1 = c(5,9,8,0),
                      TEST = c(7,11,3,9))
    
    WANT = data.frame(STUDENT = c(1,2,3,4, 'ALL'),
YEAR = c(2020, 2020, 2021, 2020,NA),
                      SCORE1 = c(5,9,8,0,22),
                       TEST = c(7,11,3,9,30))

I have 'HAVE' and wish to create 'WANT' which does this: Add row to HAVE where STUDENT = 'ALL' and SCORE1 = sum up all values of SCORE and TEST = sum up all values of TEST.

I try this with no success:

WANT = rowbind(HAVE, data.frame(STUDENT = 'ALL', SCORE1 = sum(HAVE$SCORE1), TEST = sum(HAVE$TEST))

But is there a efficient DPLYR solution?

score 5 · Answer 1 · answered Dec 10 '22 at 17:11

5

Or in base R with addmargins

addmargins(as.matrix(HAVE[-(1:2)]), 1)

answered Dec 10 '22 at 17:11

akrun

874,273
37
540
662

score 4 · Accepted Answer · answered Dec 10 '22 at 15:55

4

With dplyr:

library(dplyr)

HAVE %>% 
  mutate(STUDENT = as.character(STUDENT)) %>% 
  bind_rows(summarise(.,
                      across(where(is.numeric), sum),
                      across(where(is.character), ~"All")))

  STUDENT SCORE1 TEST
1       1      5    7
2       2      9   11
3       3      8    3
4       4      0    9
5     All     22   30

OR Maybe with janitor package:

libray(dplyr)
library(janitor)
HAVE %>% 
  adorn_totals("row")

 STUDENT SCORE1 TEST
       1      5    7
       2      9   11
       3      8    3
       4      0    9
   Total     22   30

answered Dec 10 '22 at 15:55

TarJae

72,363
6
19
66

this is so perfect! Let us say there are some other COLUMNS and you Wish for 'Total' for those to be empty, can you specify to leave blank? Like If there is column YEAR, we don't want to sum up YEAR...we just wish it to be NA – bvowe Dec 10 '22 at 16:00
1

i update question for example – bvowe Dec 10 '22 at 16:03
1

In my first solution dplyr only just add this line `across(c(YEAR), ~ NA_real_))) %>% ...` Here you specify all columns with `c()` that you want to keep NA. Hope it is clear?! – TarJae Dec 10 '22 at 16:13

chris jude · Answer 3 · 2022-12-10T16:05:11.803

Hope this works! Use rbind or bind_rows.

HAVE = data.frame(STUDENT = c(1,2,3,4),
                  SCORE1 = c(5,9,8,0),
                  TEST = c(7,11,3,9)) %>% 
  mutate(STUDENT = as.character(STUDENT))

WANT = bind_rows(HAVE, data.frame(STUDENT = 'ALL', SCORE1 = sum(HAVE$SCORE1),
                                  TEST = sum(HAVE$TEST)))


or

WANT <- rbind(HAVE, data.frame(STUDENT = 'ALL', SCORE1 = sum(HAVE$SCORE1), TEST = sum(HAVE$TEST)))

R Dplyr Add Rows Total

3 Answers3