Is there a workaround to reshape data back to long format-CodePudding

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_"

)

CodePudding user response：

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