Refer to other columns in reactable groupby aggregate expression

Question

I have a dataframe that I'd like to leverage reactable with expandable groupings. I know I can use the groupby argument and then specify the aggregation in the colDef for each metric however, in some cases the column I'm trying to aggregate is a function of other columns and applying something like "mean" or "sum" results in inaccurate values.

type = c("mammal", "mammal", "reptile", "reptile")
animal = c("monkey", "human", "frog", "toad")
measureCY = c(1, 4, 5, 6)
measurePY = c(3,4,5,7)
df = data.frame(type, animal, measureCY, measurePY)
df$YoY <- df$measureCY/df$measurePY

reactable(
df,
pagination=FALSE,
pagination=FALSE,
    sortable=FALSE,
    height = 600,
    defaultColDef = colDef(style = function(value) { list(height="30px",font_size="1px")},vAlign="center"),
    defaultPageSize = 25, # default 10 - just want to show entire table
groupBy=c("type", "animal"),
columns = list(
type = colDef(name = "Type"),
animal = colDef(name = "Animal"),
measureCY = colDef(name = "CY", aggregate="sum"),
measurePY = colDef(name = "PY", aggregate="sum"),
YoY = colDef(name = "YoY", aggregate = "sum")
),
showSortable = TRUE
  )

The above code is an example. This works for the two measures, however having a sum or mean for the YoY column results in inaccurate values. I somehow need to reference the CYPY values in the aggregate function of the YoY column, but I"m unsure of how to do that.

Answer 1

You could achieve your desired result using a custom JS aggregate function which allows to access the values in other columns using the rows argument (see the docs. The rows attribute is an array of row objects where each object contains the data for one row. Hence, we can loop over the rows to compute the aggregated sum of the values in the measureCY and measurePY columns to compute the correct aggregated value of YoY:

library(reactable)

reactable(
  df,
  pagination = FALSE,
  sortable = FALSE,
  height = 600,
  defaultColDef = colDef(style = function(value) {
    list(height = "30px", font_size = "1px")
  }, vAlign = "center"),
  defaultPageSize = 25, # default 10 - just want to show entire table
  groupBy = c("type", "animal"),
  columns = list(
    type = colDef(name = "Type"),
    animal = colDef(name = "Animal"),
    measureCY = colDef(name = "CY", aggregate = "sum"),
    measurePY = colDef(name = "PY", aggregate = "sum"),
    YoY = colDef(
      name = "YoY",
      aggregate =
        htmlwidgets::JS("
              function(values, rows) {
                // sum of CY
                let measureCY = rows
                    .map( (row) => row.measureCY)
                    .reduce( (a, b) => a + b );
                // sum of PY
                let measurePY = rows
                    .map( (row) => row.measurePY)
                    .reduce( (a, b) => a + b );

                return measureCY / measurePY;
              }
            "),
      format = reactable::colFormat(
        digits = 2
      )
    )
  ),
  showSortable = TRUE
)