R convert df from wide to long by splitting column names

Question

I am trying to convert the below df_original data.frame to the form in df_goal. The columns from the original data.frame shall be split, with the latter part acting as a key, while the first part shall be kept as a variable name. Preferably I would like to use a tidyverse-solution, but am open to every aproach. Thank you very much!

df_original <- 
  data.frame(id = c(1,2,3),
           variable1_partyx = c(4,5,6),
           variable1_partyy = c(14,15,16),
           variable2_partyx = c(24,25,26),
           variable2_partyy = c(34,35,36))

df_goal <- 
  data.frame(id = c(1,1,2,2,3,3),
             key = c("partyx","partyy","partyx","partyy","partyx","partyy"),
             variable1 = c(4,14,5,15,6,16),
             variable2 = c(24,34,25,35,26,36))

E.g. `df_original %>% gather(key, value, 2:5) %>% separate(key, into = c("variable", "key")) %>% spread(variable, value)` does the job — Maurits Evers, Mar 19 '18 at 09:36

score 2 · Accepted Answer · answered Mar 19 '18 at 09:36

2

df_original %>%
  tidyr::gather(key, value, -id) %>%
  tidyr::separate(key, into = c("var", "key"), sep = "_") %>%
  tidyr::spread(var, value)

answered Mar 19 '18 at 09:36

Julien Navarre

7,653
3
42
69

Thank you, works perfectly. – Marcel Schliebs Mar 19 '18 at 10:08

R convert df from wide to long by splitting column names

1 Answers1