Add row to a data frame with total sum for each column

Question

I have a data frame where I would like to add an additional row that totals up the values for each column. For example, Let's say I have this data:

x <- data.frame(Language=c("C++", "Java", "Python"), 
                Files=c(4009, 210, 35), 
                LOC=c(15328,876, 200), 
                stringsAsFactors=FALSE)

Data looks like this:

  Language Files   LOC
1      C++  4009 15328
2     Java   210   876
3   Python    35   200

My instinct is to do this:

y <- rbind(x, c("Total", colSums(x[,2:3])))

And this works, it computes the totals:

> y
  Language Files   LOC
1      C++  4009 15328
2     Java   210   876
3   Python    35   200
4    Total  4254 16404

The problem is that the Files and LOC columns have all been converted to strings:

> y$LOC
[1] "15328" "876"   "200"   "16404"

I understand that this is happening because I created a vector c("Total", colSums(x[,2:3]) with inputs that are both numbers and strings, and it's converting all the elements to a common type so that all of the vector elements are the same. Then the same thing happens to the Files and LOC columns.

What's a better way to do this?

score 114 · Accepted Answer · answered Apr 13 '18 at 14:12

114

See adorn_totals() from the janitor package:

library(janitor)
x %>%
  adorn_totals("row")

#>  Language Files   LOC
#>       C++  4009 15328
#>      Java   210   876
#>    Python    35   200
#>     Total  4254 16404

The numeric columns remain of class numeric.

Disclaimer: I created this package, including adorn_totals() which is made for precisely this task.

answered Apr 13 '18 at 14:12

Sam Firke

21,571
9
87
105

1

Note that one problem with this (the only one) is that it's now hard to sort by row totals, which I usually want to do. The 'Total' row ends up on top. – Barry DeCicco Dec 01 '21 at 21:56

Matifou · Answer 2 · 2020-11-27T20:06:00.253

71

A tidyverse way to do this would be to use bind_rows (or eventually add_row) and summarise to compute the sums. Here the issue is that we want sums for all but one, so a trick would be:

summarise_all(x, ~if(is.numeric(.)) sum(.) else "Total")

In one line:

x %>%
  bind_rows(summarise_all(., ~if(is.numeric(.)) sum(.) else "Total"))

Edit with dplyr >=1.0

One can also use across(), which is slightly more verbose in this case:

x %>%
  bind_rows(summarise(.,
                      across(where(is.numeric), sum),
                      across(where(is.character), ~"Total")))

edited Nov 27 '20 at 20:06

answered May 14 '18 at 02:02

Matifou

7,968
3
47
52

1

Thanks, you were right: My solution was not the required answer. Your answer is a correct one. I voted you up and deleted my entry. – petzi May 23 '18 at 13:27
Nice, I appreciate keeping it in the `tidyverse`, seems silly to load another package just for this. – Mako212 Aug 01 '18 at 19:13
Nice answer, how is about only sum for a certain no. of columns as other columsn might not need sum but Average instead. – user2493970 Feb 05 '20 at 07:09
1

I am afraid that if you want to use different functions on different columns, you would need to run manually `summarise(var1=mean(var1), var2= sum(var2), var = "Total")` – Matifou Feb 06 '20 at 06:05
Nice, solution. Wondering how to write right code with `across` instead of `summarise_all`? – Sasha Poda Nov 27 '20 at 09:11
Thank you @Matifou! I should guess using `across` two times. – Sasha Poda Nov 27 '20 at 20:37
Be aware with `across` you have to order your code as desired output columns. For this case the first should be `across(where(is.character), ~"Total")` otherwise "Total" column will be the last in output – Sasha Poda Nov 27 '20 at 21:01
correct, but using `bind_rows()` before, column "Total" will still be first! – Matifou Nov 28 '20 at 01:04

score 28 · Answer 3 · edited May 12 '20 at 08:41

28

Here's a way that gets you what you want, but there may very well be a more elegant solution.

rbind(x, data.frame(Language = "Total", t(colSums(x[, -1]))))

For the record, I prefer Chase's answer if you don't absolutely need the Language column.

edited May 12 '20 at 08:41

Jaap

81,064
34
182
193

answered Feb 09 '11 at 15:43

Joshua Ulrich

173,410
32
338
418

score 25 · Answer 4 · edited May 12 '20 at 08:43

25

Do you need the Language column in your data, or is it more appropriate to think of that column as the row.names? That would change your data.frame from 4 observations of 3 variables to 4 observations of 2 variables (Files & LOC).

x <- data.frame(Files = c(4009, 210, 35), LOC = c(15328,876, 200),
                row.names = c("C++", "Java", "Python"), stringsAsFactors = FALSE)    
x["Total" ,] <- colSums(x)


> x
       Files   LOC
C++     4009 15328
Java     210   876
Python    35   200
Total   4254 16404

edited May 12 '20 at 08:43

Jaap

81,064
34
182
193

answered Feb 09 '11 at 15:41

Chase

67,710
18
144
161

19

Personally, I don't recommend storing data in rownames - that's what variables are for! – hadley Feb 09 '11 at 17:19
2

In general, I agree. I also tend to follow @csgillespie's advice of not mixing raw data and summary statistics in the same object. As the OP pointed out however, it isn't really an issue in this instance since the question revolves around the presentation of data, not any further analysis. – Chase Feb 09 '11 at 19:01
7

What is the tidyverse equivalent? – thadk Aug 10 '17 at 09:49

score 11 · Answer 5 · edited Jul 30 '16 at 12:08

11

Try this

y[4,] = c("Total", colSums(y[,2:3]))

edited Jul 30 '16 at 12:08

Tunaki

132,869
46
340
423

answered Jul 30 '16 at 07:46

Prateek Joshi

152
1
6

score 10 · Answer 6 · answered Jun 08 '21 at 10:38

10

Extending the answer of Nicolas Ratto, if you were to have a lot more columns you could use

x %>% add_row(Language = "Total", summarise(., across(where(is.numeric), sum)))

answered Jun 08 '21 at 10:38

nstjhp

528
6
12

This solution is good but what we don't know at time of execution what's the name of the first column? – Angelo Aug 19 '21 at 18:06
@Angelo Not sure how robust this is, or if there is a far simpler way, but it seems to work for this example at least `x %>% add_row(!!rlang::as_name(names(.)[1]) := "Total", summarise(., across(where(is.numeric), sum)))` – nstjhp Jul 21 '22 at 12:29
In fact no need for `rlang::as_name()` i.e. `!!names(.)[1] := "Total"` works – nstjhp Jul 21 '22 at 12:43
awesome, thank you! I will definitely test it out! – Angelo Jul 21 '22 at 12:44

G. Grothendieck · Answer 7 · 2017-11-09T15:36:41.100

If (1) we don't need the "Language" heading on the first column then we can represent it using row names and if (2) it is ok to label the last row as "Sum" rather than "Total" then we can use addmargins like this:

rownames(x) <- x$Language
addmargins(as.table(as.matrix(x[-1])), 1)

giving:

       Files   LOC
C++     4009 15328
Java     210   876
Python    35   200
Sum     4254 16404

If we do want the first column labelled "Language" and the total row labelled "Total" then its a bit longer:

rownames(x) <- x$Language
Total <- sum
xa <- addmargins(as.table(as.matrix(x[-1])), 1, FUN = Total)
data.frame(Language = rownames(xa), as.matrix(xa[]), row.names = NULL)

giving:

  Language Files   LOC
1      C++  4009 15328
2     Java   210   876
3   Python    35   200
4    Total  4254 16404

score 5 · Answer 8 · answered Jul 24 '20 at 00:55

5

Try this

library(tibble)
x %>% add_row( Language="Total",Files = sum(.$Files),LOC = sum(.$LOC) )

answered Jul 24 '20 at 00:55

Nicolas Ratto

145
1
4

score 4 · Answer 9 · edited Dec 31 '20 at 16:30

4

df %>% bind_rows(purrr::map_dbl(.,sum))

edited Dec 31 '20 at 16:30

Dharman

30,962
25
85
135

answered Dec 31 '20 at 12:31

Manish

41
1

1

Good and elegant solution, but you have to drop the first column, and then pass it to map_dbl. A way to do that is to use the [] operator. x %>% bind_rows(x[,-1] %>% map_dbl(.,sum)) – BMLopes Jan 18 '21 at 20:59

score 1 · Answer 10 · answered Nov 09 '14 at 04:19

Since you mention this is a last step before exporting for presentation, you may have column names that will include spaces in them for clarity (i.e. "Grand Total"). If so, the following will insure that the created data.frame will rbind to the original dataset without an error caused by mismatched column names:

dfTotals <- data.frame(Language="Total",t(colSums(x[,-1]))))

colnames(dfTotals) <- names(x)  

rbind(x, dfTotals)

score 1 · Answer 11 · answered Feb 09 '11 at 16:33

Are you sure you really want to have the column totals in your data frame? To me, the data frame's interpretation now depends on the row. For example,

Rows 1-(n-1): how many files are associated with a particular language
Row n: how many files are associated with all languages

This gets more confusing if you start to subset your data. For example, suppose you want to know which languages have more than 100 Files:

> x = data.frame(Files=c(4009, 210, 35), 
                LOC=c(15328,876, 200), 
                row.names=c("C++", "Java", "Python"), 
                stringsAsFactors=FALSE)    
> x["Total" ,] = colSums(x)
> x[x$Files > 100,]
       Files   LOC
C++    4009 15328
Java    210   876
Total  4254 16404#But this refers to all languages!

The Total row is now wrong!

Personally I would work out the column sums and store them in a separate vector.

Typically I wouldn't do this for analysis, but this is for presentation. This is the last step before I generate a table in a LaTeX document with Sweave. — Lorin Hochstein, Feb 09 '11 at 17:34

score 0 · Answer 12 · answered Feb 09 '11 at 17:32

0

Your original instinct would work if you coerced your columns to numeric:

y$LOC <- as.numeric(y$LOC)
y$Files <- as.numeric(y$Files)

And then apply colSums() and rbind().

answered Feb 09 '11 at 17:32

Brandon Bertelsen

43,807
34
160
255

Add row to a data frame with total sum for each column

12 Answers12

Edit with dplyr >=1.0

Linked

Related