adding Calculation row to PivotTable in r

Question

here's my data

df=data.frame("indication"=c(1,1,0,0,1,0,1,1),"indication2"=c(1,0,1,0,1,0,0,1),"grade"=c(65,67,80,20,100,91,70,55),
       "group"=c("blue","red","green","blue","green","blue","red","green"))

  indication indication2 grade group
1          1           1    65  blue
2          1           0    67   red
3          0           1    80 green
4          0           0    20  blue
5          1           1   100 green
6          0           0    91  blue
7          1           0    70   red
8          1           1    55 green

what I'm trying to do with pivotTable is adding a third row that will calculate the mean of column GRADE.

my code:

pt$addData(df)
pt$addColumnDataGroups("group")
pt$addRowGroup(variableName="indication", values="1",caption = "indication")
pt$addRowGroup(variableName="indication2", values="1",caption = "indication2")
pt$defineCalculation(calculationName="Total", summariseExpression="n()")
cg1 <- pt$addRowGroup()
pt$defineCalculation(calculationGroupName="mean_grade", calculationName="meanOfGRADE", 
                     summariseExpression="mean(grade, na.rm=TRUE)")
cg1$addCalculationGroups("meanOfGRADE")
pt$addRowCalculationGroups(atLevel = 1)
pt$renderPivot()

unfortunately:

the desirable output: (the mean calculated by var "group")

Answer 1

Using pivottabler you could first bring your data into a long format:

library(tidyr)

data <- df %>% 
  pivot_longer(-c(group, grade))

and then using

library(pivottabler)

pt <- PivotTable$new()
pt$addData(data) 
pt$addRowDataGroups("group")
# pt$addColumnDataGroups("name")
pt$defineCalculation(calculationName = "Number of Indication 1", 
                     summariseExpression = "sum(value)",
                     filters = PivotFilters$new(pt, variableName="name", values="indication"))
pt$defineCalculation(calculationName = "Number of Indication 2", 
                     summariseExpression = "sum(value)",
                     filters = PivotFilters$new(pt, variableName="name", values="indication2"))
pt$defineCalculation(calculationName = "Mean Grade", summariseExpression = "mean(grade)")
pt$renderPivot()

create (can't upload an image file atm):

#>          Number of Indication 1  Number of Indication 2  Mean Grade
#>  blue    1                       1                       58.6666666666667
#>  green   2                       3                       78.3333333333333
#>  red     2                       0                       68.5
#>  Total   5                       4                       68.5

I don't know how to transpose this data. I used pivottabler for the first (and most likely the last) time. The pivottabler vignette (especially chapter 03. Calculations: Filtering data as part of a calculation") was a quite useful ressource.