We have a Tableau license here at work, but I’m always trying to replicate Tableau’s output into R. Recently, I was thinking about how to place summary “Totals”" in a bottom row of an R table, and I realized that it was something that was not discussed much in lectures I attend. Most likely, it is considered too easy to discuss, but it is incredibly important when presenting certain types of data. For example, we summarize sales utilization data by year at work, and it is only natural to place a summary total on this table.
I often find myself searching past code, trying to get the syntax correct, but I thought it would make more sense to summarize it here, so I can find it easily.
First, we can simulate sales data for four data periods, as shown below:
library(tidyverse)
#create the sales data frame with sample data
sales <- as.data.frame(matrix(runif(16, min = 0, max = 100), nrow = 4))
sales <- round(sales, 2)
sales## V1 V2 V3 V4
## 1 31.95 99.11 62.59 85.97
## 2 79.91 33.75 54.44 59.27
## 3 58.75 54.31 82.62 19.31
## 4 81.74 39.55 41.34 4.37To sum the sales data for each period, we would perform the following:
#find column totals
sales_totals <- sales %>% select(V1:V4) %>% summarise_all(funs(sum))
#bind totals to sales table
sales <- bind_rows(sales, sales_totals)
# create a function to format currency
currency <- function(x) {
new <- paste("$", formatC(round(x, 2), format = 'f', digits = 2),sep = "")
print(new)
}
#apply currency function to rows and columns
sales <- apply(sales, 1:2, currency)
#set Col names and Row names
col_names <- paste("Period", 1:4, sep = "_")
row_names <- c(paste("Region", 1:4, sep = " "), "Total")
colnames(sales) <- col_names
sales <- bind_cols(data.frame(row_names),data.frame(sales)) %>% rename(Region = row_names)Finally, after proper formatting, we have our table:
sales## Region Period_1 Period_2 Period_3 Period_4
## 1 Region 1 $31.95 $99.11 $62.59 $85.97
## 2 Region 2 $79.91 $33.75 $54.44 $59.27
## 3 Region 3 $58.75 $54.31 $82.62 $19.31
## 4 Region 4 $81.74 $39.55 $41.34 $4.37
## 5 Total $252.35 $226.72 $240.99 $168.92