Search code examples
rdt

How to create custom row aggregates in R's DT library


Goal

I would like to produce row aggregates for a table grouped by the rowGroup extension to the DT library. I am able to group the data frame with rowGroup but am unclear how to populate aggregated values in the summary rows.

What I've tried

Consulting examples in the DT documentation, here and here I believe the answer lies in using rowGroup.startRender. However, I am unclear how to implement this in the R DT library rowGroup extension.

Desired Output

I would like to get the number of samples per group as well as the average weight by group.

enter image description here

Code

DT::datatable(chickwts,
              rownames = TRUE,
              extensions="RowGroup",
              options = list(rowGroup = list(dataSrc=c(2),
                                             columnDefs = list(list(visible=FALSE, targets=c(2))))
              ))

Update

@stefan's answer works, but there is a limitation that aggregate values are only based on the records presently displayed rather than the total number of records in the group.

enter image description here

enter image description here

Is there perhaps a way to generate aggregates based on the structure of the underlying data rather than those records that are displayed at any given moment?


Solution

  • Adapting the example in the referenced links you can achieve your desired result by adding a custom JS render function to the startRender attribute of the rowGroup list:

    DT::datatable(chickwts,
      rownames = TRUE,
      extensions = "RowGroup",
      options = list(rowGroup = list(
        dataSrc = c(2),
        columnDefs = list(list(visible = FALSE, targets = c(2))),
        startRender = htmlwidgets::JS(
          "
          function (rows, group) {
          
                function addCell(tr, content, colSpan = 1) {
                    let td = document.createElement('th');
                 
                    td.colSpan = colSpan;
                    td.textContent = content;
                 
                    console.log(td);
          
                    tr.appendChild(td);
                }
    
                let count = rows.count();
          
                let mean =
                    rows
                      .data()
                      .pluck(1)
                      .reduce((a, b) => a + b) / rows.count();
          
                mean = DataTable.render
                  .number(null, null, 0)
                  .display(mean);
          
                let tr = document.createElement('tr');
                
                addCell(tr, group, 2);
                addCell(tr, 'n = ' + count + ', mean = ' + mean, 1);
                
                return tr;
            }
          ")
      ))
    )
    

    enter image description here

    UPDATE As an JS beginner I guess that there are more elegant approaches. But here is one approach which instead of using the data on the displayed rows filters the table data for the group and computes the count and the mean for the whole dataset:

    DT::datatable(chickwts,
      rownames = TRUE,
      extensions = "RowGroup",
      elementId = "example",
      options = list(rowGroup = list(
        dataSrc = c(2),
        columnDefs = list(list(visible = FALSE, targets = c(2))),
        startRender = htmlwidgets::JS(
          "
          function (rows, group) {
    
                function addCell(tr, content, colSpan = 1) {
                    let td = document.createElement('th');
    
                    td.colSpan = colSpan;
                    td.textContent = content;
    
                    console.log(td);
    
                    tr.appendChild(td);
                }
    
                var table_data = $('#example table').DataTable().data();
          
                let vals = table_data.filter(k => k[2] === group).map(k => k[1]);
          
                let count = vals.count();
    
                let mean =
                    vals
                      .reduce((a, b) => a + b) / vals.count();
    
                mean = DataTable.render
                  .number(null, null, 0)
                  .display(mean);
    
                let tr = document.createElement('tr');
    
                addCell(tr, group, 2);
                addCell(tr, 'n = ' + count + ', mean = ' + mean, 1);
    
                return tr;
            }
          "
        )
      ))
    )
    

    enter image description here