Intro

There are two functions that will add rows to a gt table: summary_rows() and grand_summary_rows(). These are useful for adding groupwise and grand summary rows.

Functions in this module:

summary_rows(): Add groupwise summary rows using aggregation functions

summary_rows(
  data,
  groups = NULL,
  columns = everything(),
  fns,
  missing_text = "---",
  formatter = fmt_number,
  ...
)

Add summary rows to one or more row groups by using the table data and any suitable aggregation functions.

EXAMPLES

Use exibble to create a gt table with a stub and row groups. Get the sum of the num column in both groups.

exibble %>%
  gt(rowname_col = "row", groupname_col = "group") %>%
  summary_rows(
    groups = c("grp_a", "grp_b"), # <- `TRUE` also works to target all groups
    columns = num,
    fns = list(sum = ~ sum(., na.rm = TRUE))
  )
num char fctr date time datetime currency
grp_a
row_1 1.111e-01 apricot one 2015-01-15 13:35 2018-01-01 02:22 49.950
row_2 2.222e+00 banana two 2015-02-15 14:40 2018-02-02 14:33 17.950
row_3 3.333e+01 coconut three 2015-03-15 15:45 2018-03-03 03:44 1.390
row_4 4.444e+02 durian four 2015-04-15 16:50 2018-04-04 15:55 65100.000
sum 480.06 — — — — — —
grp_b
row_5 5.550e+03 NA five 2015-05-15 17:55 2018-05-05 04:00 1325.810
row_6 NA fig six 2015-06-15 NA 2018-06-06 16:11 13.255
row_7 7.770e+05 grapefruit seven NA 19:10 2018-07-07 05:22 NA
row_8 8.880e+06 honeydew eight 2015-08-15 20:20 NA 0.440
sum 9,662,550.00 — — — — — —

Extend the above the example by formatting num values and their summary values (adding minimum and maximum summary rows as well).

exibble %>%
  gt(rowname_col = "row", groupname_col = "group") %>%
  fmt_number(
    columns = num,
    decimals = 1
  ) %>%
  summary_rows(
    groups = TRUE,
    columns = num,
    fns = list(
      sum = ~ sum(., na.rm = TRUE),
      min = ~ min(., na.rm = TRUE),
      max = ~ max(., na.rm = TRUE)
    ),
    formatter = fmt_number,
    decimals = 1
  )
num char fctr date time datetime currency
grp_a
row_1 0.1 apricot one 2015-01-15 13:35 2018-01-01 02:22 49.950
row_2 2.2 banana two 2015-02-15 14:40 2018-02-02 14:33 17.950
row_3 33.3 coconut three 2015-03-15 15:45 2018-03-03 03:44 1.390
row_4 444.4 durian four 2015-04-15 16:50 2018-04-04 15:55 65100.000
sum 480.1 — — — — — —
min 0.1 — — — — — —
max 444.4 — — — — — —
grp_b
row_5 5,550.0 NA five 2015-05-15 17:55 2018-05-05 04:00 1325.810
row_6 NA fig six 2015-06-15 NA 2018-06-06 16:11 13.255
row_7 777,000.0 grapefruit seven NA 19:10 2018-07-07 05:22 NA
row_8 8,880,000.0 honeydew eight 2015-08-15 20:20 NA 0.440
sum 9,662,550.0 — — — — — —
min 5,550.0 — — — — — —
max 8,880,000.0 — — — — — —

Add group summary values for the currency column. Use the fmt_currency() formatting function and re-use the summary labels (sum, min, and max).

exibble %>%
  gt(rowname_col = "row", groupname_col = "group") %>%
  fmt_number(
    columns = num,
    decimals = 1
  ) %>%
  summary_rows(
    groups = TRUE,
    columns = num,
    fns = list(
      sum = ~ sum(., na.rm = TRUE),
      min = ~ min(., na.rm = TRUE),
      max = ~ max(., na.rm = TRUE)
    ),
    formatter = fmt_number,
    decimals = 1
  ) %>%
  fmt_currency(
    columns = currency,
    currency = "USD"
  ) %>%
  summary_rows(
    groups = TRUE,
    columns = currency,
    fns = list(
      sum = ~ sum(., na.rm = TRUE),
      min = ~ min(., na.rm = TRUE),
      max = ~ max(., na.rm = TRUE)
    ),
    formatter = fmt_currency,
    currency = "USD",
    missing_text = "" # <- this `missing_text` value replaces the dashes with nothing
  )
num char fctr date time datetime currency
grp_a
row_1 0.1 apricot one 2015-01-15 13:35 2018-01-01 02:22 $49.95
row_2 2.2 banana two 2015-02-15 14:40 2018-02-02 14:33 $17.95
row_3 33.3 coconut three 2015-03-15 15:45 2018-03-03 03:44 $1.39
row_4 444.4 durian four 2015-04-15 16:50 2018-04-04 15:55 $65,100.00
sum 480.1 $65,169.29
min 0.1 $1.39
max 444.4 $65,100.00
grp_b
row_5 5,550.0 NA five 2015-05-15 17:55 2018-05-05 04:00 $1,325.81
row_6 NA fig six 2015-06-15 NA 2018-06-06 16:11 $13.26
row_7 777,000.0 grapefruit seven NA 19:10 2018-07-07 05:22 NA
row_8 8,880,000.0 honeydew eight 2015-08-15 20:20 NA $0.44
sum 9,662,550.0 $1,339.50
min 5,550.0 $0.44
max 8,880,000.0 $1,325.81

Use sp500 to create a gt table with row groups. Create summary rows (min, max, avg) by row group, where each each row group is a week number.

sp500 %>%
  dplyr::filter(date >= "2015-01-05" & date <="2015-01-16") %>%
  dplyr::arrange(date) %>%
  dplyr::mutate(week = paste0("W", strftime(date, format = "%V"))) %>%
  dplyr::select(-adj_close, -volume) %>%
  gt(
    rowname_col = "date",
    groupname_col = "week"
  ) %>%
  fmt_currency(columns = everything()) %>%
  summary_rows(
    groups = TRUE,
    columns = c(open, high, low, close),
    fns = list(
      min = ~min(.),
      max = ~max(.),
      avg = ~mean(.)
    ),
    formatter = fmt_currency
  )
open high low close
W02
2015-01-05 $2,054.44 $2,054.44 $2,017.34 $2,020.58
2015-01-06 $2,022.15 $2,030.25 $1,992.44 $2,002.61
2015-01-07 $2,005.55 $2,029.61 $2,005.55 $2,025.90
2015-01-08 $2,030.61 $2,064.08 $2,030.61 $2,062.14
2015-01-09 $2,063.45 $2,064.43 $2,038.33 $2,044.81
min $2,005.55 $2,029.61 $1,992.44 $2,002.61
max $2,063.45 $2,064.43 $2,038.33 $2,062.14
avg $2,035.24 $2,048.56 $2,016.85 $2,031.21
W03
2015-01-12 $2,046.13 $2,049.30 $2,022.58 $2,028.26
2015-01-13 $2,031.58 $2,056.93 $2,008.25 $2,023.03
2015-01-14 $2,018.40 $2,018.40 $1,988.44 $2,011.27
2015-01-15 $2,013.75 $2,021.35 $1,991.47 $1,992.67
2015-01-16 $1,992.25 $2,020.46 $1,988.12 $2,019.42
min $1,992.25 $2,018.40 $1,988.12 $1,992.67
max $2,046.13 $2,056.93 $2,022.58 $2,028.26
avg $2,020.42 $2,033.29 $1,999.77 $2,014.93

grand_summary_rows(): Add grand summary rows using aggregation functions

grand_summary_rows(
  data,
  columns = everything(),
  fns,
  missing_text = "---",
  formatter = fmt_number,
  ...
)

Add grand summary rows to the gt table by using applying aggregation functions to the table data. The summary rows incorporate all of the available data, regardless of whether some of the data are part of row groups.

EXAMPLE

Use sp500 to create a gt table with row groups. Create grand summary rows (min, max, avg) for the table.

sp500 %>%
  dplyr::filter(date >= "2015-01-05" & date <="2015-01-16") %>%
  dplyr::arrange(date) %>%
  dplyr::mutate(week = paste0("W", strftime(date, format = "%V"))) %>%
  dplyr::select(-adj_close, -volume) %>%
  gt(
    rowname_col = "date",
    groupname_col = "week"
  ) %>%
  fmt_currency(columns = everything()) %>%
  grand_summary_rows(
    columns = c(open, high, low, close),
    fns = list(
      min = ~min(.),
      max = ~max(.),
      avg = ~mean(.)),
    formatter = fmt_currency
  )
open high low close
W02
2015-01-05 $2,054.44 $2,054.44 $2,017.34 $2,020.58
2015-01-06 $2,022.15 $2,030.25 $1,992.44 $2,002.61
2015-01-07 $2,005.55 $2,029.61 $2,005.55 $2,025.90
2015-01-08 $2,030.61 $2,064.08 $2,030.61 $2,062.14
2015-01-09 $2,063.45 $2,064.43 $2,038.33 $2,044.81
W03
2015-01-12 $2,046.13 $2,049.30 $2,022.58 $2,028.26
2015-01-13 $2,031.58 $2,056.93 $2,008.25 $2,023.03
2015-01-14 $2,018.40 $2,018.40 $1,988.44 $2,011.27
2015-01-15 $2,013.75 $2,021.35 $1,991.47 $1,992.67
2015-01-16 $1,992.25 $2,020.46 $1,988.12 $2,019.42
min $1,992.25 $2,018.40 $1,988.12 $1,992.67
max $2,063.45 $2,064.43 $2,038.33 $2,062.14
avg $2,027.83 $2,040.92 $2,008.31 $2,023.07

SUMMARY

  1. The summary_rows() and grand_summary_rows() functions allow for calculation and insertion of summary rows, either per group or for all rows in the table.
  2. You might call these functions several times to build up summary rows; use the same labels to line up entries across columns.
  3. The functions are not very easy to use, don’t feel bad using ?summary_rows or ?grand_summary_rows and working from an example.