Using Tidyverse to Output a Series of Summary Statistic Tables per User/Participant

Question

I am trying to produce a table of mean scores for each participant in my tibble. The number of observants is much larger than the data given below, but this tibble should be sufficient. I need to produce a table for each unique user_id. I would like the table to have 10 rows, 8 of which are the means of the indicators 1-8 per timepoint, and the other two are domain means per timepoint. The mean of domain 0 is the mean of indicators 1-4, and the mean of domain 1 is the mean of indicators 5-8. I would also like the outputted tables to have four columns, one per timepoint. Thus, each teacher_id's outputted table should be a 10 by 4. I have attempted this with tidyverse and would appreciate help. Also, some users (read several) will not have values at all timepoints.

structure(list(Group = c(1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 
1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 
1, 1, 1, 1, 1, 1, 1), user_id = c("Kim", "Kim", 
"Kim", "Kim", "Kim", "Kim", "Kim", 
"Kim", "Bob", "Bob", "Bob", "Bob", 
"Bob", "Bob", "Bob", "Bob", "Bob", 
"Bob", "Bob", "Bob", "Bob", "Bob", 
"Bob", "Bob", "George", "George", "George", "George", 
"George", "George", "George", "George", "George", "George", "George", 
"George", "George", "George", "George", "George"), indicator = c("1", 
"2", "3", "4", "5", "6", "7", "8", "1", "1", "2", "2", "3", "3", 
"4", "4", "5", "5", "6", "6", "7", "7", "8", "8", "1", "1", "2", 
"2", "3", "3", "4", "4", "5", "5", "6", "6", "7", "7", "8", "8"
), Timepoint = c(1, 1, 1, 1, 1, 1, 1, 1, 3, 4, 3, 4, 3, 
4, 3, 4, 3, 4, 3, 4, 3, 4, 3, 4, 3, 4, 3, 4, 3, 4, 3, 4, 3, 4, 
3, 4, 3, 4, 3, 4), score = c(3.5, 3.5, 2, 3, 3.5, 4, 
3, 4, 2, 3, 2.5, 3, 1.5, 1.5, 0.5, 3, 2, 4, 2.5, 4, 2.5, 3.5, 
3, 3.5, 3.5, 3, 2.5, 2.5, 2.5, 2, 2, 3, 3.5, 3.5, 3.5, 3.5, 3, 
3, 3, 2.5)), class = c("tbl_df", "tbl", "data.frame"), row.names = c(NA, 
-40L))

Attempted tidyverse code:

user_tables <- d %>% 
  group_by(user_id,indicator,Timepoint) %>% 
  summarise(Time1 = mean[which(indicator == 1 & Timepoint == 1)], mean[which(indicator == 2 & Timepoint == 1)], mean[which(indicator == 3 & Timepoint == 1)], mean[which(indicator == 4 & Timepoint == 1)], mean[which(indicator == 5 & Timepoint == 1)], mean[which(indicator == 6 & Timepoint == 1)], mean[which(indicator == 7 & Timepoint == 1)], mean[which(indicator == 8 & Timepoint == 1)],
            Time2 = mean[which(indicator == 1 & Timepoint == 2)], mean[which(indicator == 2 & Timepoint == 2)], mean[which(indicator == 3 & Timepoint == 2)], mean[which(indicator == 4 & Timepoint == 2)], mean[which(indicator == 5 & Timepoint == 2)], mean[which(indicator == 6 & Timepoint == 2)], mean[which(indicator == 7 & Timepoint == 2)], mean[which(indicator == 8 & Timepoint == 2)], 
            Time3 = mean[which(indicator == 1 & Timepoint == 3)], mean[which(indicator == 2 & Timepoint == 3)], mean[which(indicator == 3 & Timepoint == 3)], mean[which(indicator == 4 & Timepoint == 3)], mean[which(indicator == 5 & Timepoint == 3)], mean[which(indicator == 6 & Timepoint == 3)], mean[which(indicator == 7 & Timepoint == 3)], mean[which(indicator == 8 & Timepoint == 3)],
            Time4 = mean[which(indicator == 1 & Timepoint == 4)], mean[which(indicator == 2 & Timepoint == 4)], mean[which(indicator == 3 & Timepoint == 4)], mean[which(indicator == 4 & Timepoint == 4)], mean[which(indicator == 5 & Timepoint == 4)], mean[which(indicator == 6 & Timepoint == 4)], mean[which(indicator == 7 & Timepoint == 4)], mean[which(indicator == 8 & Timepoint == 4)]) %>% 
  split(., .$user_id)

Ultimately, I would like a table like this per user (where the NAs are the appropriate means) (Note: This one is for Bob - he didn't have scores for time 1 or time 2):

structure(c(NA, NA, NA, NA, NA, NA, NA, NA, NA, NA, NA, NA, NA, 
NA, NA, NA, NA, NA, NA, NA, 1.625, 2, 2.5, 1.5, 0.5, 2.5, 2, 
2.5, 2.5, 3, 2.625, 3, 3, 1.5, 3, 3.75, 4, 4, 3.5, 3.5), .Dim = c(10L, 
4L), .Dimnames = list(c("Domain  0", "Ind 1", "Ind 2", "Ind 3", 
"Ind 4", "Domain 1", "Ind  5", "Ind 6", "Ind 7", "Ind 8"), c("Time 1", 
"Time 2", "Time 3", "Time 4")))

Thank you!

user_8380272 · Answer

Since you are adding rows, you could do:

df %>%
  group_by(Group, user_id, Timepoint, domain = +(indicator>4), indicator) %>%
  summarise(sc=mean(score),.groups ='drop_last') %>%
  pivot_wider(c(Group, user_id, indicator, domain), Timepoint,'Time_', values_from = sc) %>%
  group_nest()%>%
  mutate(data = map(data, 
                ~rbind(c(NA,colMeans(select_if(.x,is.numeric), na.rm = TRUE)),.x)))%>%
  unnest(data)%>%
  mutate(indicator = ifelse(is.na(indicator), 
                            paste0('Domain ', domain), paste0('Ind ', indicator)),
         domain = NULL)

A tibble: 30 x 6
   Group user_id indicator Time_3 Time_4 Time_1
                 
 1     1 Bob     Domain 0    1.62   2.62    NaN
 2     1 Bob     Ind 1       2      3        NA
 3     1 Bob     Ind 2       2.5    3        NA
 4     1 Bob     Ind 3       1.5    1.5      NA
 5     1 Bob     Ind 4       0.5    3        NA
 6     1 Bob     Domain 1    2.5    3.75    NaN
 7     1 Bob     Ind 5       2      4        NA
 8     1 Bob     Ind 6       2.5    4        NA
 9     1 Bob     Ind 7       2.5    3.5      NA
10     1 Bob     Ind 8       3      3.5      NA
# ... with 20 more rows

user_4550695 · Answer

Same basic idea as in @Onyambu’s answer, but simplified a bit with new dplyr 1.0.0 features that allow summarise() to increase the row count:

library(tidyverse)

have %>% 
  mutate(domain = (as.numeric(indicator) - 1) %/% 4) %>% 
  group_by(user_id, Timepoint, domain, indicator) %>% 
  summarise(score = mean(score)) %>% 
  summarise(
    cur_data() %>% add_row(score = mean(score), .before = 1)
  ) %>% 
  arrange(Timepoint) %>% 
  pivot_wider(
    values_from = score,
    names_from = Timepoint,
    names_prefix = "Time "
  ) %>% 
  filter(user_id == "Bob")
#> `summarise()` regrouping output by 'user_id', 'Timepoint', 'domain' (override with `.groups` argument)
#> `summarise()` regrouping output by 'user_id', 'Timepoint', 'domain' (override with `.groups` argument)
#> # A tibble: 10 x 6
#> # Groups:   user_id, domain [2]
#>    user_id domain indicator `Time 1` `Time 3` `Time 4`
#>                         
#>  1 Bob          0             NA     1.62     2.62
#>  2 Bob          0 1               NA     2        3   
#>  3 Bob          0 2               NA     2.5      3   
#>  4 Bob          0 3               NA     1.5      1.5 
#>  5 Bob          0 4               NA     0.5      3   
#>  6 Bob          1             NA     2.5      3.75
#>  7 Bob          1 5               NA     2        4   
#>  8 Bob          1 6               NA     2.5      4   
#>  9 Bob          1 7               NA     2.5      3.5 
#> 10 Bob          1 8               NA     3        3.5

Data setup:

have <- structure(list(
  Group = c(1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1,
            1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1),
  user_id = c(
    "Kim", "Kim", "Kim", "Kim", "Kim", "Kim", "Kim", "Kim", "Bob", "Bob",
    "Bob", "Bob", "Bob", "Bob", "Bob", "Bob", "Bob", "Bob", "Bob", "Bob",
    "Bob", "Bob", "Bob", "Bob", "George", "George", "George", "George",
    "George", "George", "George", "George", "George", "George", "George",
    "George", "George", "George", "George", "George"
  ),
  indicator = c(
    "1", "2", "3", "4", "5", "6", "7", "8", "1", "1", "2", "2", "3", "3",
    "4", "4", "5", "5", "6", "6", "7", "7", "8", "8", "1", "1", "2",
    "2", "3", "3", "4", "4", "5", "5", "6", "6", "7", "7", "8", "8"
  ), Timepoint = c(
    1, 1, 1, 1, 1, 1, 1, 1, 3, 4, 3, 4, 3, 4, 3, 4, 3, 4, 3, 4,
    3, 4, 3, 4, 3, 4, 3, 4, 3, 4, 3, 4, 3, 4, 3, 4, 3, 4, 3, 4
  ), score = c(
    3.5, 3.5, 2, 3, 3.5, 4, 3, 4, 2, 3, 2.5, 3, 1.5, 1.5,
    0.5, 3, 2, 4, 2.5, 4, 2.5, 3.5, 3, 3.5, 3.5, 3, 2.5,
    2.5, 2.5, 2, 2, 3, 3.5, 3.5, 3.5, 3.5, 3, 3, 3, 2.5
)), class = c("tbl_df", "tbl", "data.frame"), row.names = c(NA, -40L))

want <- structure(
  c(NA, NA, NA, NA, NA, NA, NA, NA, NA, NA, NA, NA, NA, 
    NA, NA, NA, NA, NA, NA, NA, 1.625, 2, 2.5, 1.5, 0.5, 2.5,
    2, 2.5, 2.5, 3, 2.625, 3, 3, 1.5, 3, 3.75, 4, 4, 3.5, 3.5),
    .Dim = c(10L, 4L),
    .Dimnames = list(
      c("Domain  0", "Ind 1", "Ind 2", "Ind 3","Ind 4",
        "Domain 1", "Ind  5", "Ind 6", "Ind 7", "Ind 8"),
      c("Time 1", "Time 2", "Time 3", "Time 4")
    )
)

user_10556787 · Answer

Great question. How about a nest solution? Here, you create a function to summarise, nest the data by user_id, then apply the function to each participant.

library(tidyverse)

df <- structure(list(Group = c(1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 
                         1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 
                         1, 1, 1, 1, 1, 1, 1), user_id = c("Kim", "Kim", 
                                                           "Kim", "Kim", "Kim", "Kim", "Kim", 
                                                           "Kim", "Bob", "Bob", "Bob", "Bob", 
                                                           "Bob", "Bob", "Bob", "Bob", "Bob", 
                                                           "Bob", "Bob", "Bob", "Bob", "Bob", 
                                                           "Bob", "Bob", "George", "George", "George", "George", 
                                                           "George", "George", "George", "George", "George", "George", "George", 
                                                           "George", "George", "George", "George", "George"), indicator = c("1", 
                                                                                                                            "2", "3", "4", "5", "6", "7", "8", "1", "1", "2", "2", "3", "3", 
                                                                                                                            "4", "4", "5", "5", "6", "6", "7", "7", "8", "8", "1", "1", "2", 
                                                                                                                            "2", "3", "3", "4", "4", "5", "5", "6", "6", "7", "7", "8", "8"
                                                           ), Timepoint = c(1, 1, 1, 1, 1, 1, 1, 1, 3, 4, 3, 4, 3, 
                                                                            4, 3, 4, 3, 4, 3, 4, 3, 4, 3, 4, 3, 4, 3, 4, 3, 4, 3, 4, 3, 4, 
                                                                            3, 4, 3, 4, 3, 4), score = c(3.5, 3.5, 2, 3, 3.5, 4, 
                                                                                                         3, 4, 2, 3, 2.5, 3, 1.5, 1.5, 0.5, 3, 2, 4, 2.5, 4, 2.5, 3.5, 
                                                                                                         3, 3.5, 3.5, 3, 2.5, 2.5, 2.5, 2, 2, 3, 3.5, 3.5, 3.5, 3.5, 3, 
                                                                                                         3, 3, 2.5)), class = c("tbl_df", "tbl", "data.frame"), row.names = c(NA, 
                                                                                                                                                                              -40L))




output <- structure(c(NA, NA, NA, NA, NA, NA, NA, NA, NA, NA, NA, NA, NA, 
            NA, NA, NA, NA, NA, NA, NA, 1.625, 2, 2.5, 1.5, 0.5, 2.5, 2, 
            2.5, 2.5, 3, 2.625, 3, 3, 1.5, 3, 3.75, 4, 4, 3.5, 3.5), .Dim = c(10L, 
                                                                              4L), .Dimnames = list(c("Domain  0", "Ind 1", "Ind 2", "Ind 3", 
                                                                                                      "Ind 4", "Domain 1", "Ind  5", "Ind 6", "Ind 7", "Ind 8"), c("Time 1", 
                                                                                                                                                                   "Time 2", "Time 3", "Time 4")))



dfnest <- df %>% 
  group_nest(user_id)


my_summarise <- function(.data) {
  .data %>%
    group_by(indicator,Timepoint) %>%
    summarise(mean = mean(score, na.rm = TRUE)) %>% 
    pivot_wider(values_from = mean,names_from = Timepoint,names_prefix = 'Timepoint')
}


map(dfnest$data,my_summarise)
#> `summarise()` regrouping output by 'indicator' (override with `.groups` argument)
#> `summarise()` regrouping output by 'indicator' (override with `.groups` argument)
#> `summarise()` regrouping output by 'indicator' (override with `.groups` argument)
#> [[1]]
#> # A tibble: 8 x 3
#> # Groups:   indicator [8]
#>   indicator Timepoint3 Timepoint4
#>                   
#> 1 1                2          3  
#> 2 2                2.5        3  
#> 3 3                1.5        1.5
#> 4 4                0.5        3  
#> 5 5                2          4  
#> 6 6                2.5        4  
#> 7 7                2.5        3.5
#> 8 8                3          3.5
#> 
#> [[2]]
#> # A tibble: 8 x 3
#> # Groups:   indicator [8]
#>   indicator Timepoint3 Timepoint4
#>                   
#> 1 1                3.5        3  
#> 2 2                2.5        2.5
#> 3 3                2.5        2  
#> 4 4                2          3  
#> 5 5                3.5        3.5
#> 6 6                3.5        3.5
#> 7 7                3          3  
#> 8 8                3          2.5
#> 
#> [[3]]
#> # A tibble: 8 x 2
#> # Groups:   indicator [8]
#>   indicator Timepoint1
#>             
#> 1 1                3.5
#> 2 2                3.5
#> 3 3                2  
#> 4 4                3  
#> 5 5                3.5
#> 6 6                4  
#> 7 7                3  
#> 8 8                4

^{Created on 2020-11-12 by the reprex package (v0.3.0)}

Using Tidyverse to Output a Series of Summary Statistic Tables per User/Participant

3 Answers