Pivot manipulations with mean scores?

[Slavek]

Hi,
I have this nultiresponse df. TMC is one question with 4 answer options:

source <- data.frame(
   stringsAsFactors = FALSE,
                              URN = c("21GB01293040","21GB01240221",
                                      "21GB03294610","21GB01309069","21GB03078286",
                                      "21GB01086060","21GB01169525","21GB01209144",
                                      "21GB01204925","21GB90063865",
                                      "21GB01068838","21GB01176411","21GB01215531"),
  TMC.Communication = c(0, 0, 0, 0, 0, 1, 1, 1, 0, 1, 0, 0, 1),
      TMC.Paperwork = c(0, 1, 0, 0, 0, 1, 0, 0, 1, 1, 0, 0, 0),
       TMC.Electric = c(0, 0, 0, 0, 1, 0, 0, 0, 1, 1, 0, 0, 0),
          TMC.Other = c(1, 0, 1, 1, 0, 0, 0, 0, 0, 0, 1, 1, 0),
                              Qtr = c(2021.5,2021.5,2021.5,2021.5,2021.25,
                                      2021.25,2021.5,2021.5,2021.5,2021.5,
                                      2021.25,2021.25,2021.5),
                QA1 = c(9, 1, 8, 4, 8, 6, 7, 6, 2, 3, 6, 10, 8),
               QA31 = c(7, 1, 8, 3, 8, 8, 8, 6, 2, 2, 7, 8, 8)
              )

I can calculate counts and proportions for this question (overall and by Qtr):

library(dplyr)
library(tidyr)
idea <- source %>% 
  select(starts_with("TMC.")) %>% 
  summarise_all(list(Count = sum, Proportion = mean)) %>% 
  pivot_longer(everything(), names_to = c("Category", "summary"), names_sep = "_", "value") %>% 
  pivot_wider(names_from = summary, values_from = value)

idea2 <- source %>% 
  select(Qtr, starts_with("TMC.")) %>% 
  group_by(Qtr) %>% 
  summarise_all(list(Count = sum, Proportion = mean)) %>% 
  pivot_longer(-Qtr, names_to = c("Category", "summary"), names_sep = "_", "value") %>% 
  pivot_wider(names_from = summary, values_from = value)

Now I would like to show Mean scores of questions starting from QA instead of proportions. Is it possible?

[Slavek]

Perhaps I have to export the df and use other tools if R is not capable to do that?

[nirgrahamuk]

I think you may not have received many offers of help because your ask is somewhat opaque. Can you shed more light on what your goal is?

[Slavek]

Ah, I see.
I want to see mean scores (questions starting from QA) for each row generated in my idea and idea2.
So ideally, the layout should be the same like in the idea and idea2 but "Proportion" should be replaced by two columns (QA1 Mean and QA31 Mean). In other words, I need mean scores of these two questions for Communication, Paperwork, Electric and Other...

[nirgrahamuk]

library(tidyverse)
library(slider)

doit2 <- function(x,g="Qtr"){
select(source,
       {{x}},{{g}},QA1,QA31) %>% filter(!!sym(x)!=0) %>% 
    group_by_at(.vars = c(x,g)) %>% 
    summarise(across(.fns=mean)) %>% ungroup() %>% 
    mutate(Category=x) %>% select(-x)
}
doit1 <- function(x){
  doit2(x,g=NULL)
}

  
( sol1 <-  idea %>% left_join(slide_dfr(idea$Category,doit1)))
( sol2 <- idea2 %>% left_join(distinct(slide_dfr(idea2$Category,doit2) )))

[Slavek]

Perfect! I know the issue is resolved but I'm wondering if there is a way of finding mean scores for questions starting from QA. Without this option, we have to list them all...