R - Print table with columns sums below
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.
I took the answer of https://stackoverflow.com/a/36069586/8076560 as an inspiration to create the following in R with RStudio. The table can then be exported (knit) to pdf/html as needed.
And now to the code...
```{r name-of-chunk, echo=FALSE}
# Load packages
library(dplyr)
library(kableExtra)
# Make lists
types <- c("Heating", "Water Heating", "Electricity")
heat_per_person <- c (9.59, 1.32, 1.95)
heat_per_area <- c(95.26, 13.55, 20.03)
# Make dataframe
results_df <- data.frame(types , heat_per_person, heat_per_area, stringsAsFactors = FALSE)
# Sum the last row of each column if numeric
func <- function(z) if (is.numeric(z)) sum(z) else ''
sumrow <- as.data.frame(lapply(results_df, func))
# Give name to the first element of the new data frame created above
sumrow[1] <- "Total"
# Add the original and new data frames together
summed_results_df <- rbind(results_df, sumrow)
# Name the columns
colnames <- data.frame("Service", "Amount per Person","Amount per Area", stringsAsFactors = FALSE)
colnames(summed_results_df) <- colnames
# Make Table
kable(summed_results_df, caption = "Normalized Annual Energy Demand", booktabs = TRUE) %>%
kable_styling(bootstrap_options = c("striped", "hover", "condensed")) %>%
row_spec(dim(summed_results_df)[1], bold = T) %>% # format last row
column_spec(1, italic = T) # format first column
```
You can refer to the table in the Rmarkdown/ Rmd text with the name of the code chunk as shown here: \@ref(tab:name-of-chunk)