Is there a workaround to reshape data back to long format

user_13734451 0
Viewed 41

I would to reverse the reshaping from wide back to long format but am stack how I should proceed so that the values for a1_* be placed in a1. Is there a workaround this?

library(tidyverse)

df <- tibble(
  id = c("s001", "s002", "s002", "s003", "s003",
              "s004", "s005"),
  a1 = c("A", "B", "C", "A", "D", "A", "B"),
  a2 = c(23, 24, 45, 23, 56, 45, 34),
  a3 = c("Primary", "Secondary", "Tertiary", "Primary",
         "Tertiary", "Secondary", "Primary"))

#---- Reshape long to wide

df <- df %>%
  group_by(id) %>%
  mutate(index = row_number()) %>% ungroup()


df_wide <- df %>%
  pivot_wider(id_cols = id,
              names_from = index,
              values_from = starts_with("a"))

#---- Reverse back

df_long <- df_wide %>%
  pivot_longer(!id,
    names_to = "index",
    names_prefix = "a\\d_"

)
tidyr r
1 Answers

What you need to do is specify that you are using the specific values on the left hand of the separator to identify unique columns (i.e. a1, ...) and on the right index.

We can use the names_sep argument for this, and values_drop_na to remove rows that weren't in the original df.

df_wide %>%
  pivot_longer(!id,
               names_to = c(".value", "index"),
               names_sep = "_",
               values_drop_na = T)
#> # A tibble: 7 × 5
#>   id    index a1       a2 a3       
#>   <chr> <chr> <chr> <dbl> <chr>    
#> 1 s001  1     A        23 Primary  
#> 2 s002  1     B        24 Secondary
#> 3 s002  2     C        45 Tertiary 
#> 4 s003  1     A        23 Primary  
#> 5 s003  2     D        56 Tertiary 
#> 6 s004  1     A        45 Secondary
#> 7 s005  1     B        34 Primary
user_103573830
Related