How can I correctly aggregate nested rows with reactable in R?

Question

I am working on a Shiny app where the reactable package has been very useful because of the nested structure of my data. It allows me to collapse and summarize rows within a higher-level category, and only expand to reveal the 'subrows' if/when desired.

For just 1 nested level (e.g., car models within makes), the aggregation functions (unique, count etc.) from reactable work just fine out of the box. However when adding extra levels of nested-ness, things break down and even aggregators like unique present repeated values (!). I suspect this is because the subcategories are not all pooled together in a flat structure with only 1 aggregation step performed on all of them, but rather unique values remain subcategory-specific and then just get concatenated together, leading to repetition. This issue affects other aggregators too, not just unique.

I'm adding a MWE below in R, as I've been unable to fix this. Since JS is also far from being my strong suit, I've been unable to insert any JS to fix this more flexibly, as is suggested here. How can I tweak the aggregators below to get correctly displayed output (i.e., no repetition)?

library(shiny)
library(shinydashboard)
library(reactable)
library(stringr)

if (interactive()) {
  
  ui <- shinyUI(basicPage(
    box(title = "mtcars data",
        width = 12,
        reactableOutput("car_tab")
    )
  ))
  
  server <- function(input, output, session) {
    output$car_tab <- renderReactable({
      
      df <- mtcars
      df$make <- str_extract(rownames(df), "[[:alpha:]]+")
      df$model <- rownames(df)
      rownames(df) <- NULL
      df <- df[ , c("make", "model", "mpg", "cyl", "disp", "hp", "drat", "wt", "qsec", "vs", "am", "gear", "carb")]
      
      reactable(df,
                groupBy = c("cyl", "am", "make"),
                columns = list(
                  cyl = colDef(name = "Number of cylinders"),
                  am = colDef(name = "Transmission",
                              aggregate = "frequency"),
                  make = colDef(name = "Make",
                                aggregate = "frequency"),
                  model  = colDef(name = "Model",
                                  aggregate = "unique"),
                  mpg = colDef(name = "Miles/gallon",
                               aggregate = "mean",
                               format = colFormat(digits = 2)),
                  disp = colDef(name = "Displacement"),
                  hp = colDef(name = "Horsepower"),
                  drat = colDef(name = "Rear axle ratio"),
                  wt = colDef(name = "Weight"),
                  qsec = colDef(name = "1/4 mile time"),
                  vs = colDef(name = "Engine",
                              aggregate = "count"),
                  gear = colDef(name = "Number of forward gears"),
                  carb = colDef(name = "Number of carburetors")
                )
      )
    })
  }
  
  shinyApp(ui = ui, server = server)
  
}

Everything looks fine to me. I am not sure what is repeating. Please note that for each possible value of `am`, it shows how many have 1 value in `make` and how many have 2 values in `make`, and so on. — YBS, Aug 27 '20 at 23:04
So, if you run the code above exactly as is, for example it will aggregate Transmission as 0, 0(2), 1(6), 1(2), instead of simply 0(3), 1(8). I'd like it to do the latter, but haven't managed to find a way to do it. — LexConstantine, Aug 28 '20 at 07:23

score 1 · Accepted Answer · answered Sep 14 '20 at 23:43

I think this can be solved by using aggregators as you are using them just now and then supplying a custom JavaScript renderer through aggregated parameter of colDef. The idea is that reactable will do aggregation through built in operators and will use the custom renderer to render output in aggregated cells.

The custom JavaScript renderer should take a string that looks like 0, 0(2), 1(6), 1(2) and do some string manipulation to produce an output like 0(3), 1(8).

If the JavaScript function is defined and saved in an R variable like this, it can be reused:

jsRenderer <- "
    function(cellInfo) {
        const handleBrackets = (item) => {
            const currentName = item.replace(/\\([0-9]+\\)/g, '')
            const currentCountsArr = item.match(/\\(([0-9]+)\\)/)
            let currentCount = 1
            if (currentCountsArr && currentCountsArr.length === 2) {
                currentCount = parseInt(currentCountsArr[1], 10)
            }
            return {
                currentName,
                currentCount
            }
        }

        const getCounts = (input) => {
            const trimmedInput = input.replace(/\\s+/g, '')
            const items = trimmedInput.split(',')
            const namesWithoutBrackets = trimmedInput.replace(/\\(([0-9]+)\\)/g, '').split(',')
            const itemObj = items.reduce((prev, current, index) => {
                const itemWithoutBrackets = handleBrackets(current)
                let {
                    currentName,
                    currentCount
                } = itemWithoutBrackets

                if (namesWithoutBrackets.indexOf(currentName) !== index) {
                    currentCount += prev[currentName]
                }

                return {
                    ...prev,
                    ...{
                        [currentName]: currentCount
                    }
                }

            }, {})

            const stringToSanitize = Object.entries(itemObj).reduce((prevString, currentKeyValue) => {
                return prevString.concat(`${currentKeyValue[0]}(${currentKeyValue[1]}), `)
            }, '')

            return stringToSanitize.slice(0, -2)
        }

        return (getCounts(cellInfo.value))
    }
"

You can then supply this rendered to colDef like this:

colDef(name = "Transmission", aggregate = "frequency", aggregated = jsRenderer)

In your MWE, the result is displayed like this:

Merc(2),
Toyota(2),
Datsun(1),
Fiat(2),
Honda(1),
Porsche(1),
Lotus(1),
Volvo(1)

score 0 · Answer 2 · answered Aug 28 '20 at 11:10

0

To get 0(3), 1(8), you need to

groupBy = c("cyl", "am")

answered Aug 28 '20 at 11:10

YBS

19,324
2
9
27

Thanks but unfortunately this won't work, we need to allow additional levels of nesting and a solution must be robust to that. So rather than reduce the nesting that needs to be there, the aggregator needs to cope with it somehow... And even in this solution, the same problem reoccurs under Make again, e.g., Toyota appears twice, instead of becoming Toyota (2) – LexConstantine Aug 28 '20 at 11:36

How can I correctly aggregate nested rows with reactable in R?

2 Answers2