pivottabler: Producing grouped summary statistics for total

Question

Looking for how to produce grouped summary statistics for Total (last row appended) using pivottabler.

library(pivottabler)
qpvt(
    dataFrame    = bhmtrains
  , rows         = c("TOC", "Status")
  , columns      = "TrainCategory"
  , calculations =  "n()"
  )
#>                             Express Passenger  Ordinary Passenger  Total  
#> Arriva Trains Wales  A                   3018                 815   3833  
#>                      C                     59                  15     74  
#>                      R                      2                          2  
#>                      Total               3079                 830   3909  
#> CrossCountry         A                  22270                  60  22330  
#>                      C                    569                   2    571  
#>                      R                     26                   1     27  
#>                      Total              22865                  63  22928  
#> London Midland       A                  14133               32851  46984  
#>                      C                    336                 914   1250  
#>                      R                     18                  27     45  
#>                      Total              14487               33792  48279  
#> Virgin Trains        A                   8359                       8359  
#>                      C                    226                        226  
#>                      R                      9                          9  
#>                      Total               8594                       8594  
#> Total                                   49025               34685  83710

Edited

Expected output

#>                             Express Passenger  Ordinary Passenger  Total  
#> Arriva Trains Wales  A                   3018                 815   3833  
#>                      C                     59                  15     74  
#>                      R                      2                          2  
#>                      Total               3079                 830   3909  
#> CrossCountry         A                  22270                  60  22330  
#>                      C                    569                   2    571  
#>                      R                     26                   1     27  
#>                      Total              22865                  63  22928  
#> London Midland       A                  14133               32851  46984  
#>                      C                    336                 914   1250  
#>                      R                     18                  27     45  
#>                      Total              14487               33792  48279  
#> Virgin Trains        A                   8359                       8359  
#>                      C                    226                        226  
#>                      R                      9                          9  
#>                      Total               8594                       8594  
#> Total                A                   47780               33726  81506
#>                      C                    1190                 931   2121
#>                      R                      55                  28     83
#>                      Total               49025               34685  83710

Answer 1

This is a bit hacky, but it does the job:

library(pivottabler)

pt <- qpvt(
    dataFrame    = rbind(bhmtrains, within(bhmtrains, TOC <- "Total"))
  , rows         = c("TOC", "Status")
  , columns      = "TrainCategory"
  , calculations =  "n()"
  )

pt$removeRow(21)

pt
#>                             Express Passenger  Ordinary Passenger  Total  
#> Arriva Trains Wales  A                   3018                 815   3833  
#>                      C                     59                  15     74  
#>                      R                      2                          2  
#>                      Total               3079                 830   3909  
#> CrossCountry         A                  22270                  60  22330  
#>                      C                    569                   2    571  
#>                      R                     26                   1     27  
#>                      Total              22865                  63  22928  
#> London Midland       A                  14133               32851  46984  
#>                      C                    336                 914   1250  
#>                      R                     18                  27     45  
#>                      Total              14487               33792  48279  
#> Virgin Trains        A                   8359                       8359  
#>                      C                    226                        226  
#>                      R                      9                          9  
#>                      Total               8594                       8594  
#> Total                A                  47780               33726  81506  
#>                      C                   1190                 931   2121  
#>                      R                     55                  28     83  
#>                      Total              49025               34685  83710

^{Created on 2022-08-06 by the reprex package (v2.0.1)}