Can you use pivot_wider to create multiple groups of alternating new columns?

0

Issue

My data currently looks like this, with the column "Number_Code based on each different Side_Effect:

Session_ID   Side_Effect     Number_Code
 1            anxious           1
 1            dizzy             2
 1            relaxed           3
 3            dizzy             2
 7            nauseous          4
 7            anxious           1

I know I can do:

mutate(rn = str_c('side_effect_', row_number())) %>% 
 pivot_wider(names_from = rn, values_from = Side_Effect)

In order to create new column names and put each side effect into a new column like this:

 session    Number_Code   side_effect1   side effect_2      side_effect_3    
      1     1                 anxious         NA                 NA
      1     2                 NA              dizzy              NA
      1     3                 NA              NA                 relaxed
      3     2                 dizzy           NA                 NA
      7     4                 nauseous        NA                 NA
      7     1                 NA              anxious            NA

But I need to widen the data based on both "Side_Effect" and "Number_Code", and have them in alternating columns like this:

 session     side_effect1   number_code1   side effect_2   number_code2   side_effect_3    number_code3
        1       anxious         1              dizzy             2            relaxed          3
        3       dizzy           2               NA               NA           NA              NA
        7       nauseous        4              anxious           1            NA              NA

I saw another post where they widened the data based on two variables, but all of the columns for the second one were after all of the columns of the first one. Is there a way to get them to alternate like this? Thank you!!

Solution

The pivot_wider can take multiple value_from columns, so after creating the sequence by group, use pivot_wider with values_from specifying the columns of interest

library(dplyr)
library(tidyr)
df1 %>% 
   group_by(Session_ID) %>%
   mutate(rn = row_number()) %>% 
   ungroup %>% 
   pivot_wider(names_from = rn, values_from = c(Side_Effect, Number_Code))
# A tibble: 3 x 7
#  Session_ID Side_Effect_1 Side_Effect_2 Side_Effect_3 Number_Code_1 Number_Code_2 Number_Code_3
#       <int> <chr>         <chr>         <chr>                 <int>         <int>         <int>
#1          1 anxious       dizzy         relaxed                   1             2             3
#2          3 dizzy         <NA>          <NA>                      2            NA            NA
#3          7 nauseous      anxious       <NA>                      4             1            NA

If we need to reorder the column order, then we can select based on the numeric part and order

df1 %>% 
    group_by(Session_ID) %>%
    mutate(rn = row_number()) %>% 
    ungroup %>% 
    pivot_wider(names_from = rn, values_from = c(Side_Effect, Number_Code)) %>%
    select(Session_ID, names(.)[-1][order(readr::parse_number(names(.)[-1]))] )
# A tibble: 3 x 7
#  Session_ID Side_Effect_1 Number_Code_1 Side_Effect_2 Number_Code_2 Side_Effect_3 Number_Code_3
#       <int> <chr>                 <int> <chr>                 <int> <chr>                 <int>
#1          1 anxious                   1 dizzy                     2 relaxed                   3
#2          3 dizzy                     2 <NA>                     NA <NA>                     NA
#3          7 nauseous                  4 anxious                   1 <NA>                     NA

data

df1 <- structure(list(Session_ID = c(1L, 1L, 1L, 3L, 7L, 7L), 
  Side_Effect = c("anxious", 
"dizzy", "relaxed", "dizzy", "nauseous", "anxious"), Number_Code = c(1L, 
2L, 3L, 2L, 4L, 1L)), class = "data.frame", row.names = c(NA, 
-6L))

Answered By – akrun

This Answer collected from stackoverflow, is licensed under cc by-sa 2.5 , cc by-sa 3.0 and cc by-sa 4.0

Leave A Reply

Your email address will not be published.

This website uses cookies to improve your experience. We'll assume you're ok with this, but you can opt-out if you wish. Accept Read More