| Title: | Adds Subtotals to Data Reports |
|---|---|
| Description: | Adds subtotal rows / sections (a la the 'SAS' 'Proc Tabulate' All option) to a Group By output by running a series of Group By functions with partial sets of the same variables and combining the results with the original. Can be used to add comprehensive information to a data report or to quickly aggregate Group By outputs used to gain a greater understanding of data. |
| Authors: | Yoni Aboody [aut, cre, cph] |
| Maintainer: | Yoni Aboody <[email protected]> |
| License: | MIT + file LICENSE |
| Version: | 0.2.1 |
| Built: | 2026-10-21 11:22:08 UTC |
| Source: | https://github.com/yaboody/reportsubtotal |
Removes duplicate subtotal rows, which may be created by totalling a variable with only one level. In tables with many variables, some may have only one level within one section and many in other sections.
subtotal_dupe_removal( data, column, iterator = 2, skip = 0, remove = "Total", lead_name = "Lead_Column" )subtotal_dupe_removal( data, column, iterator = 2, skip = 0, remove = "Total", lead_name = "Lead_Column" )
data |
Data report or data frame to remove duplicate row labels from. |
column |
Column containing duplicate row labels. |
iterator |
Minimum number of rows meant to be between each section. Usually two. |
skip |
Number of rows to skip removing rows from. Usually zero. Can be used to avoid dealing with NA values or column labels. |
remove |
Label of subtotals to be removed. Usually "Total". |
lead_name |
Default name for lead column used to filter duplicates. |
Adds a leading version of the requested column, which places each row level in the same row as the row level two levels ahead. This is usually the value 2 rows ahead of the current row, although if not the iterator parameter can be used to account for this. Then, if both the original and leading column equal the value to be removed, then the row is considered a duplicate subtotal and is removed. Note: Columns named Lead will receive a temporary suffix.
The data report without duplicate subtotal rows.
library(dplyr) group_by(mtcars, cyl, vs) %>% summarise(Sum_Wt = sum(wt), .groups = "keep") %>% subtotal_row(mtcars, "wt") %>% subtotal_dupe_removal(2) group_by(mtcars, cyl, vs, am) %>% summarise(Mean_HP = mean(hp), .groups = "keep") %>% subtotal_row(mtcars, "hp", "mean") %>% subtotal_dupe_removal(3, skip = 1) group_by(mtcars, cyl, vs, am) %>% summarise(Mean_HP = mean(hp), .groups = "keep") %>% subtotal_row(mtcars, "hp", "mean") %>% subtotal_dupe_removal(3, skip = 1)library(dplyr) group_by(mtcars, cyl, vs) %>% summarise(Sum_Wt = sum(wt), .groups = "keep") %>% subtotal_row(mtcars, "wt") %>% subtotal_dupe_removal(2) group_by(mtcars, cyl, vs, am) %>% summarise(Mean_HP = mean(hp), .groups = "keep") %>% subtotal_row(mtcars, "hp", "mean") %>% subtotal_dupe_removal(3, skip = 1) group_by(mtcars, cyl, vs, am) %>% summarise(Mean_HP = mean(hp), .groups = "keep") %>% subtotal_row(mtcars, "hp", "mean") %>% subtotal_dupe_removal(3, skip = 1)
Adds subtotal rows to a data report. Designed to work with reports generated by group_by + summarise.
subtotal_row( report, frame, vars = "Population", aggregator = "sum", exclude = numeric(0), agg_parameter = list(), subtotal_label = "Total" )subtotal_row( report, frame, vars = "Population", aggregator = "sum", exclude = numeric(0), agg_parameter = list(), subtotal_label = "Total" )
report |
A data report. |
frame |
Data frame summarised by the data report. |
vars |
Names of column(s) in the data frame which were aggregated in the data report. |
aggregator |
Function to aggregate the data with. |
exclude |
Vector of column indices determining which variables don't require subtotal rows. |
agg_parameter |
A named list of optional parameter(s) for the aggregation function to use. |
subtotal_label |
Label to be used for subtotal rows. |
The dataset and report are factorized, and a series of reports are generated. These reports are structured similarly to the original report - using the same grouping variables, aggregating the given variables with the given aggregation function and optional parameters. For best results, choose the same variables and aggregator used to generate the original report. Each generated report has some grouping variables replaced by a chosen subtotal label, effectively concentrating all levels of those variables into one subtotal row for those variables. The subtotal reports are all combined with the original report, and the combined report is grouped and sorted, sorting the subtotal label to the top for all variables.
The grouped data report with subtotal rows included.
library(dplyr) group_by(iris, Species, Petal.Width) %>% summarise(Sum_P_Len = sum(Petal.Length), .groups = "keep") %>% subtotal_row(iris, vars = "Petal.Length") group_by(iris, Species, Petal.Width) %>% summarise(Mean_S_Width = mean(Sepal.Width), .groups = "keep") %>% subtotal_row(iris, vars = "Sepal.Width", aggregator = "mean") group_by(mtcars, cyl, gear, carb) %>% summarise(Med_Wt = median(wt), Med_Hp = median(hp), .groups = "keep") %>% subtotal_row(mtcars, vars = c("wt", "hp"), aggregator = "median") group_by(mtcars, cyl, gear, carb) %>% summarise(Med_Weight = median(wt), Med_Hrspw = median(hp), .groups = "keep") %>% subtotal_row(mtcars, vars = c("wt", "hp"), aggregator = "median", exclude = 1) group_by(mtcars, vs, am, drat, carb) %>% summarise(Min_Mpg = min(mpg), Min_Disp = min(disp), Min_Carb = min(carb), .groups = "keep") %>% subtotal_row(mtcars, vars = c("mpg", "disp", "carb"), aggregator = "min", exclude = c(2, 4), subtotal_label = "Min_Cars_Total", agg_parameter = list(na.rm = TRUE))library(dplyr) group_by(iris, Species, Petal.Width) %>% summarise(Sum_P_Len = sum(Petal.Length), .groups = "keep") %>% subtotal_row(iris, vars = "Petal.Length") group_by(iris, Species, Petal.Width) %>% summarise(Mean_S_Width = mean(Sepal.Width), .groups = "keep") %>% subtotal_row(iris, vars = "Sepal.Width", aggregator = "mean") group_by(mtcars, cyl, gear, carb) %>% summarise(Med_Wt = median(wt), Med_Hp = median(hp), .groups = "keep") %>% subtotal_row(mtcars, vars = c("wt", "hp"), aggregator = "median") group_by(mtcars, cyl, gear, carb) %>% summarise(Med_Weight = median(wt), Med_Hrspw = median(hp), .groups = "keep") %>% subtotal_row(mtcars, vars = c("wt", "hp"), aggregator = "median", exclude = 1) group_by(mtcars, vs, am, drat, carb) %>% summarise(Min_Mpg = min(mpg), Min_Disp = min(disp), Min_Carb = min(carb), .groups = "keep") %>% subtotal_row(mtcars, vars = c("mpg", "disp", "carb"), aggregator = "min", exclude = c(2, 4), subtotal_label = "Min_Cars_Total", agg_parameter = list(na.rm = TRUE))
Adds subtotal sections to a data report. Designed to work with reports generated by group_by + summarise.
subtotal_section( report, frame, vars = "Population", aggregator = "sum", exclude = numeric(0), agg_parameter = list(), subtotal_label = "Total" )subtotal_section( report, frame, vars = "Population", aggregator = "sum", exclude = numeric(0), agg_parameter = list(), subtotal_label = "Total" )
report |
A data report. |
frame |
Data frame summarised by the data report. |
vars |
Names of column(s) in the data frame which were aggregated in the data report. |
aggregator |
Function to aggregate the data with. |
exclude |
Vector of column indices determining which variables only require subtotal rows (as opposed to sections). |
agg_parameter |
A named list of optional parameter(s) for the aggregation function to use. |
subtotal_label |
Label to be used for subtotal rows. |
The dataset and report are factorized, and a series of reports are generated. These reports are structured similarly to the original report - using the same grouping variables, aggregating the given variables with the given aggregation function and optional parameters. For best results, choose the same variables and aggregator used to generate the original report. Each generated report has some grouping variables replaced by a chosen subtotal label, effectively concentrating all levels of those variables into one subtotal row for those variables - ultimately making up entire subtotal sections for some variables. The subtotal reports are all combined with the original report, and the combined report is grouped and sorted, sorting the subtotal label to the top for all variables.
The grouped data report with subtotal sections included.
library(dplyr) group_by(iris, Species) %>% summarise(Sum_P_Len = sum(Petal.Length), .groups = "keep") %>% subtotal_section(iris, vars = "Petal.Length") group_by(mtcars, cyl, gear) %>% summarise(Mean_Mpg = mean(mpg), .groups = "keep") %>% subtotal_section(mtcars, vars = "mpg", aggregator = "mean") group_by(mtcars, cyl, gear) %>% summarise(Mean_Mpg = mean(mpg), Mean_Wt = mean(wt), .groups = "keep") %>% subtotal_section(mtcars, vars = c("mpg", "wt"), aggregator = "mean", exclude = 1:2) group_by(iris, Species, Petal.Width) %>% summarise(Max_S_Width = max(Sepal.Width), Max_S_Length = max(Sepal.Length), .groups = "keep") %>% subtotal_section(iris, vars = c("Sepal.Width", "Sepal.Length"), aggregator = "max", agg_parameter = list(na.rm = TRUE)) group_by(mtcars, qsec, carb, hp, gear) %>% summarise(Min_Cyl = min(cyl), Min_Drat = min(drat), Min_Wt = min(wt), .groups = "keep") %>% subtotal_section(mtcars, vars = c("cyl", "drat", "wt"), aggregator = "min", agg_parameter = list(na.rm = TRUE), subtotal_label = "Cars_Total", exclude = c(1, 4))library(dplyr) group_by(iris, Species) %>% summarise(Sum_P_Len = sum(Petal.Length), .groups = "keep") %>% subtotal_section(iris, vars = "Petal.Length") group_by(mtcars, cyl, gear) %>% summarise(Mean_Mpg = mean(mpg), .groups = "keep") %>% subtotal_section(mtcars, vars = "mpg", aggregator = "mean") group_by(mtcars, cyl, gear) %>% summarise(Mean_Mpg = mean(mpg), Mean_Wt = mean(wt), .groups = "keep") %>% subtotal_section(mtcars, vars = c("mpg", "wt"), aggregator = "mean", exclude = 1:2) group_by(iris, Species, Petal.Width) %>% summarise(Max_S_Width = max(Sepal.Width), Max_S_Length = max(Sepal.Length), .groups = "keep") %>% subtotal_section(iris, vars = c("Sepal.Width", "Sepal.Length"), aggregator = "max", agg_parameter = list(na.rm = TRUE)) group_by(mtcars, qsec, carb, hp, gear) %>% summarise(Min_Cyl = min(cyl), Min_Drat = min(drat), Min_Wt = min(wt), .groups = "keep") %>% subtotal_section(mtcars, vars = c("cyl", "drat", "wt"), aggregator = "min", agg_parameter = list(na.rm = TRUE), subtotal_label = "Cars_Total", exclude = c(1, 4))