多个组的可 react 聚合函数

Question

使用 R reactable 包，我尝试使用两个 groupBy 变量显示标记读数的百分比。在较低级别的分组中，这是计算正确的百分比。但是在第二层(外层)分组上，它没有显示正确的百分比。

这是数据:

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_spec 列中的 1 表示读数在可接受的范围内。如果为 0，则超出指定范围。当数据传入时，它会被标记为符合或不符合规范。

这是我目前的代码:

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"))

这是当前的输出:

在 reading_1 和 reading_2 类别中，每个变量都显示了符合规范读数的正确百分比。然而，在最外层，每个类别都没有计算我需要的百分比。在每个类别中，我希望它计算符合规范的读数总数和读数总数。然后，它应该将符合规范的总数除以读数总数。

在此示例中，第一组 (reading_1) 有 16 个符合规范的读数和 20 个总读数，因此我希望它显示 0.8。第二组 (reading_2) 有 19 个合规读数和 20 个总读数，所以我希望它显示 0.95。

我认为编写自定义聚合函数是解决此问题的正确方法，但我不确定。我对 reactable 调用之外的 dplyr 解决方案持开放态度，但我不想失去个人阅读值(value)，因此总结可能行不通。

Answer 1

获得所需内容的一种简单方法是将 aggregate 更改为 "mean"

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

如果您想在 dplyr 中执行此操作，则必须有两个不同的 group_by 语句和两个不同的变量。

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