R - Print table with columns sums below

Question

Example: I have a simple data frame

df <- data.frame(date=c("2016-01-01", "2016-01-02")
                 , sales_a = c(2,3)
                 , sales_b = c(1,1)
                 , diff=c(1,2))

I am currently printing this df using kable in an RMarkDown document. I would like to produce a row underneath with the sums of the columns (not date) with a line separating the df from the totals. Is this possible with any R package without writing latex code which I'm not too familiar with?

Thanks

Answer 1

I don't think you can draw a line between the last and second-to-last rows in markdown. But calculating and including a summary row is relatively straight-forward.

(BTW: I've included stringsAsFactors in the data.frame, for preference and ease of handling new strings.)

df <- data.frame(date=c("2016-01-01", "2016-01-02")
               , sales_a = c(2,3)
               , sales_b = c(1,1)
               , diff=c(1,2)
               , stringsAsFactors = FALSE)
func <- function(z) if (is.numeric(z)) sum(z) else ''
sumrow <- as.data.frame(lapply(df, func))
sumrow
#   date sales_a sales_b diff
# 1            5       2    3

It's relatively straight-forward to combine with the whole data.frame:

library(knitr)
kable(rbind(df, sumrow))
# |date       | sales_a| sales_b| diff|
# |:----------|-------:|-------:|----:|
# |2016-01-01 |       2|       1|    1|
# |2016-01-02 |       3|       1|    2|
# |           |       5|       2|    3|

If you want the ability to highlight/label the specific summary row:

kable(rbind(cbind(' '=' ', df),
            cbind(' '='Total', sumrow)))
# |      |date       | sales_a| sales_b| diff|
# |:-----|:----------|-------:|-------:|----:|
# |      |2016-01-01 |       2|       1|    1|
# |      |2016-01-02 |       3|       1|    2|
# |Total |           |       5|       2|    3|

The "Total" label can of course be put anywhere. You might try to add a row (of characters) manually, but I'm not certain it would look right.