Search code examples
rdplyrreactable

Reactable Aggregate Function for Multiple Groups

Using the R reactable package, I am trying to display the percentage of flagged readings using two groupBy variables. On the lower level of grouping, this is calculating the correct percentage. On the second (outer) level of grouping though, it is not displaying the correct percentage.

Here's the data:

dat <- structure(list(Date = structure(c(1592611200, 1592611200, 1592611200,
                                         1592611200, 1592697600, 1592697600,
                                         1592697600, 1592697600, 1592784000,
                                         1592784000, 1592784000, 1592784000,
                                         1592870400, 1592870400, 1592870400,
                                         1592870400, 1592956800, 1592956800,
                                         1592956800, 1592956800, 1593043200,
                                         1593043200, 1593043200, 1593043200,
                                         1593129600, 1593129600, 1593129600,
                                         1593129600, 1593216000, 1593216000,
                                         1593216000, 1593216000, 1593302400,
                                         1593302400, 1593302400, 1593302400,
                                         1593388800, 1593388800, 1593388800,
                                         1593388800), 
                                       tzone = "UTC", class = c("POSIXct", "POSIXt")), 
                      variable = c("Incoming Reading 1", "Outgoing Reading 1", "Incoming Reading 2", "Outgoing Reading 2", "Incoming Reading 1",
                                     "Outgoing Reading 1", "Incoming Reading 2", "Outgoing Reading 2", "Incoming Reading 1", "Outgoing Reading 1",
                                     "Incoming Reading 2", "Outgoing Reading 2", "Incoming Reading 1", "Outgoing Reading 1", "Incoming Reading 2",
                                     "Outgoing Reading 2", "Incoming Reading 1", "Outgoing Reading 1", "Incoming Reading 2", "Outgoing Reading 2",
                                     "Incoming Reading 1", "Outgoing Reading 1", "Incoming Reading 2", "Outgoing Reading 2", "Incoming Reading 1",
                                     "Outgoing Reading 1", "Incoming Reading 2", "Outgoing Reading 2", "Incoming Reading 1", "Outgoing Reading 1",
                                     "Incoming Reading 2", "Outgoing Reading 2", "Incoming Reading 1", "Outgoing Reading 1", "Incoming Reading 2",
                                     "Outgoing Reading 2", "Incoming Reading 1", "Outgoing Reading 1", "Incoming Reading 2", "Outgoing Reading 2"), 
                      reading = c(60, 55, 60, 72,
                                  61, 56, 60, 71,
                                  62, 55, 61, 72,
                                  61, 54, 60, 71,
                                  62, 53, 60, 72,
                                  61, 52, 59, 71,
                                  60, 51, 60, 72,
                                  62, 50, 60, 71,
                                  61, 55, 61, 72,
                                  62, 56, 60, 70),
                      in_spec = c (1, 1, 1, 1,
                                   1, 1, 1, 1,
                                   1, 1, 1, 1,
                                   1, 1, 1, 1,
                                   1, 0, 1, 1,
                                   1, 0, 0, 1,
                                   1, 0, 1, 1,
                                   1, 0, 1, 1,
                                   1, 1, 1, 1,
                                   1, 1, 1, 1),
                      category = c("reading_1", "reading_1", "reading_2", "reading_2",
                                   "reading_1", "reading_1", "reading_2", "reading_2",
                                   "reading_1", "reading_1", "reading_2", "reading_2",
                                   "reading_1", "reading_1", "reading_2", "reading_2",
                                   "reading_1", "reading_1", "reading_2", "reading_2",
                                   "reading_1", "reading_1", "reading_2", "reading_2",
                                   "reading_1", "reading_1", "reading_2", "reading_2",
                                   "reading_1", "reading_1", "reading_2", "reading_2",
                                   "reading_1", "reading_1", "reading_2", "reading_2",
                                   "reading_1", "reading_1", "reading_2", "reading_2")),
                 row.names = c(NA, -40L), class = c("tbl_df", "tbl", "data.frame"))

In this data, 1 in the in_spec column means the reading was within an acceptable range. If it's 0, it is out of the specified range. As data comes in, it gets flagged as in- or out-of-spec.

And here's the code I have so far:

library(reactable)

reactable(dat[, c("Date", "variable", "reading",
                                  "category", "in_spec")],
          columns = list(in_spec = colDef(name = "In Spec",
                           aggregate = JS("function(values, rows) {
                                            var totalReadings = 0;
                                            var inSpecReadings = 0;
                                            rows.forEach(function(row) {
                                              if(row['in_spec'] == 1) {
                                                inSpecReadings += 1;
                                              }
                                              totalReadings += 1;
                                            })
                                            return (inSpecReadings / totalReadings);
                                          }")
                           )
          ),
          groupBy = c("category", "variable"))

Here is the current output: Reactable Table

Within the reading_1 and reading_2 category, each of the variables are showing the correct percentage of in-spec readings. However, on the outer most level, each category is not calculating the percentage how I need it. Within each category, I want it to count the total number of in-spec readings and total number of readings. It should then divide the total in-spec by the total number of readings.

In this example, the first group (reading_1) has 16 in-spec readings and 20 total readings, so I want it to display 0.8. The second group (reading_2) has 19 in-spec readings and 20 total readings, so I want it to display 0.95.

I think writing a custom aggregate function is the correct way to go about this, but I am not sure. I am open to a dplyr solution outside of the reactable call, but I don't want to lose individual reading values, so a summarize probably wouldn't work.

Solution

  • An easy way to get what you are looking for is just changing the aggregate to "mean"

    reactable(dat[, c("Date", "variable", "reading",
                  "category", "in_spec")],
          columns = list(in_spec = colDef(name = "In Spec",
                                          aggregate = "mean")),
          groupBy = c("category", "variable"))

    If you want to do it in dplyr you would have to have two different group_by statements and two different variables.

    dat %>% 
  group_by(category, variable) %>% 
  mutate(pct_var_in = mean(in_spec)) %>% 
  group_by(category) %>% 
  mutate(pct_cat_in = mean(in_spec))