R reshape wide to long: multiple variables, observations with multiple indicies

Question

I have got some data containing observations with multiple idicies $y_{ibc}$ stored in a messy wide format. I have been fiddling around with tidyr and reshape2 but could not figure it out (reshaping really is my nemesis).

Here is an example:

df <- structure(list(id = c(1, 2, 3, 4, 5, 6, 7, 8, 9), a1b1c1 = c(5, 
2, 1, 4, 3, 1, 0, 1, 3), a2b1c1 = c(3, 4, 1, 1, 3, 2, 1, 4, 4
), a3b1c1 = c(4, 0, 0, 1, 1, 1, 0, 0, 1), a1b2c1 = c(1, 0, 4, 
2, 4, 1, 0, 4, 2), a2b2c1 = c(2, 0, 1, 0, 1, 0, 3, 2, 0), a3b2c1 = c(2, 
4, 3, 0, 2, 3, 3, 3, 4), yc1 = c(1, 2, 2, 1, 2, 2, 2, 1, 1), a1b1c2 = c(4, 
2, 3, 0, 4, 4, 2, 1, 4), a2b1c2 = c(3, 0, 3, 3, 4, 4, 3, 2, 2
), a3b1c2 = c(3, 1, 0, 1, 4, 0, 2, 2, 3), a1b2c2 = c(2, 2, 0, 
3, 2, 1, 4, 1, 0), a2b2c2 = c(3, 0, 2, 3, 4, 4, 4, 0, 4), a3b2c2 = c(0, 
0, 0, 2, 0, 0, 1, 4, 3), yc2 = c(2, 2, 2, 1, 2, 2, 2, 1, 1), X = c(5, 
6, 3, 7, 4, 3, 2, 3, 2)), row.names = c(NA, -9L), class = c("tbl_df", 
"tbl", "data.frame"))

This is what I want (excerpt):

     id b     c         y    a1    a2    a3     X

1     1 b1    c1        1     5     3     4     5
2     1 b2    c1        1     1     2     2     5
3     1 b1    c2        2     4     3     3     5
4     1 b2    c2        2     2     3     0     5

stlba stlba · Accepted Answer · 2020-11-18T18:17:09

Using tidyr & dplyr:

library(tidyverse)

df %>% 
  pivot_longer(cols = matches("a.b.c."), names_to = "name", values_to = "value") %>% 
  separate(name, into = c("a", "b", "c"), sep = c(2,4)) %>% 
  mutate(y = case_when(c == "c1" ~ yc1,
                       c == "c2" ~ yc2)) %>% 
  pivot_wider(names_from = a, values_from = value) %>% 
  select(id, b, c, y, a1, a2, a3, X)

First, convert all your a/b/c columns to a long format & separate the 3 values into separate columns. Then combine your y columns into one depending on the value of c using mutate andcase_when (you could also use if_else for two options but case_when is more expandable for more values). Then pivot your a columns back to wide format and use select to put them in the right order and get rid of the yc1 and yc2 columns.

R reshape wide to long: multiple variables, observations with multiple indicies

1 Answers