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

The mean value per `group`? What about the `indication` columns? — Martin Gal, Aug 08 '21 at 09:29
Or asked in another way: How do you want your expected output to look like based on the example data shown in your question? — Martin Gal, Aug 08 '21 at 09:58
yes, mean value per group. the indication rows in pivottable remain. just added a desirable output - hope now it is clear — nomi, Aug 08 '21 at 10:19

score 2 · Answer 1 · answered Aug 08 '21 at 10:33

You could do this with dplyr:

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

library(dplyr)

df %>%
  group_by(group) %>%
  summarise(across(starts_with("indic"), sum), 
            mean_grade = mean(grade)) %>%
  bind_rows(df %>% 
            summarise(group = "total", 
                      across(starts_with("indic"), sum), 
                      mean_grade = mean(grade)))

returns

# A tibble: 4 x 4
  group indication indication2 mean_grade
  <chr>      <dbl>       <dbl>      <dbl>
1 blue           1           1       58.7
2 green          2           3       78.3
3 red            2           0       68.5
4 total          5           4       68.5

very useful, but I thought maybe there is a way to do that with "pivotabler" package. — nomi, Aug 09 '21 at 10:57

score 1 · Accepted Answer · answered Aug 09 '21 at 12:14

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.

adding Calculation row to PivotTable in r

2 Answers2