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

You mean the result you get if you change `rows = c("TOC", "Status")` to `rows = "Status"`? — Allan Cameron, Aug 06 '22 at 13:14
Could you please show the expected ouptut or explain further? — TarJae, Aug 06 '22 at 13:19

score 3 · Accepted Answer · answered Aug 06 '22 at 13:47

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

Thanks for very useful answer. Would appreciate it if you guide how to remove last row rather than giving the number of row. — MYaseen208, Aug 06 '22 at 14:48
You would need to do something like `with(bhmtrains, (length(unique(TOC)) + 1) * (length(unique(Status)) + 1) + 1)` to get the grand total row for removal. — Allan Cameron, Aug 06 '22 at 14:52
Adding `totals = list("Status" = "Total")` argument to `qpvt` would do the trick. — MYaseen208, Aug 07 '22 at 04:27

pivottabler: Producing grouped summary statistics for total

1 Answers1