Home > Enterprise >  Pivot_wider across multiple columns
Pivot_wider across multiple columns

Time:05-09

I have this type of data for Subjects' ratings referred to as Annotation in a series of Trials:

df <- structure(list(Subject = c("A", "A", "A", "B", "B", "B"), 
                     Annotation = c("f", "n", "n", "f", "n", "f"), 
                     Trial = c(1L, 2L, 3L, 1L, 2L, 3L),
                     ID = c(1L, 2L, 3L, 1L, 2L, 3L),
                     Trial_time = c("00:00:00.001", 
                      "00:00:00.002", "00:00:00.003", "00:00:00.001", 
                      "00:00:00.002", "00:00:00.003")), row.names = c(NA, -6L), class = c("tbl_df", "tbl", "data.frame"))

I want to pivot_wider by Trial while keeping all associated columns for each Subject. All I can do is this, which loses the columns ID and Trial_time:

library(dplyr)
df %>%
  pivot_wider(Trial, 
              names_from = c(Subject), 
              names_glue = "{Subject}_{.value}",
              values_from = Annotation)
# A tibble: 3 × 3
  Trial A_Annotation B_Annotation
  <int> <chr>        <chr>       
1     1 f            f           
2     2 n            n           
3     3 n            f  

How can I pivot_wider so as to obtain this result:

  Trial A_Annotation B_Annotation  A_Trial  B_Trial  A_ID   B_ID    
1     1 f            f             1        1        1      1
2     2 n            n             2        2        2      2
3     3 n            f             3        3        3      3

CodePudding user response:

Add the other variables in the values_from parameter.

library(tidyr)
df %>% 
  pivot_wider(id_cols = Trial, names_from = Subject, names_glue = "{Subject}_{.value}", 
              values_from = c(Annotation, Trial_time, ID))

# A tibble: 3 × 7
  Trial A_Annotation B_Annotation A_Trial_time B_Trial_time  A_ID  B_ID
  <int> <chr>        <chr>        <chr>        <chr>        <int> <int>
1     1 f            f            00:00:00.001 00:00:00.001     1     1
2     2 n            n            00:00:00.002 00:00:00.002     2     2
3     3 n            f            00:00:00.003 00:00:00.003     3     3

data

df <- structure(list(Subject = c("A", "A", "A", "B", "B", "B"), 
                     Annotation = c("f", "n", "n", "f", "n", "f"), 
                     Trial = c(1L, 2L, 3L, 1L, 2L, 3L),
                     ID = c(1L, 2L, 3L, 1L, 2L, 3L),
                     Trial_time = c("00:00:00.001", 
                                    "00:00:00.002", "00:00:00.003", "00:00:00.001", 
                                    "00:00:00.002", "00:00:00.003")), row.names = c(NA, -6L), class = c("tbl_df", "tbl", "data.frame"))

CodePudding user response:

There might be another shortcut for this, but you can just copy the Trial column with mutate(), and then add Trial and ID to values_from

library(dplyr)
library(tidyr)

df <- structure(list(Subject = c("A", "A", "A", "B", "B", "B"), 
                     Annotation = c("f", "n", "n", "f", "n", "f"), 
                     Trial = c(1L, 2L, 3L, 1L, 2L, 3L),
                     ID = c(1L, 2L, 3L, 1L, 2L, 3L),
                     Trial_time = c("00:00:00.001", 
                      "00:00:00.002", "00:00:00.003", "00:00:00.001", 
                      "00:00:00.002", "00:00:00.003")), row.names = c(NA, -6L), class = c("tbl_df", "tbl", "data.frame"))

df %>%
  mutate(trial = Trial) %>%
  pivot_wider(Trial, 
              names_from = c(Subject), 
              names_glue = "{Subject}_{.value}",
              values_from = c(Annotation, trial, ID))
#> # A tibble: 3 × 7
#>   Trial A_Annotation B_Annotation A_trial B_trial  A_ID  B_ID
#>   <int> <chr>        <chr>          <int>   <int> <int> <int>
#> 1     1 f            f                  1       1     1     1
#> 2     2 n            n                  2       2     2     2
#> 3     3 n            f                  3       3     3     3
  • Related