I have a wide data frame friend where I'd like to perform pivot_longer() over multiple sets of columns. A minimal example of the data frame is provided below:
id <- c(303, 303)
year <- c(2020, 2020)
city_a <- c("Madrid", "Madrid")
PA1 = c("AA", "AA")
a1_x <- c(475, 457)
a1_y = c(576, 576)
PA2 = c("BB", "BB")
a2_x = c(746, 756)
a2_y = c(465, 475)
PA3 = c("CC", "CC")
a3_x = c(546, 756)
a3_y = c(574, 867)
PA4 = c("DD", "DD")
a4_x = c(463, 875)
a4_y = c(565, 576)
PA5 = c("EE", "EE")
a5_x = c(564, 746)
a5_y= c(576, 576)
city_h = c("Chicago", "Chicago")
PH1 = c("FF", "FF")
h1_x = c(475, 475)
h1_y = c(576, 745)
PH2 = c("HH", "HH")
h2_x = c(746, 475)
h2_y = c(465, 465)
PH3 = c("JJ", "JJ")
h3_x = c(546, 475)
h3_y = c(574, 475)
PH4 = c("KK", "KK")
h4_x = c(463, 756)
h4_y = c(565, 586)
PH5 = c("MM", "MM")
h5_x = c(564, 456)
h5_y = c(576, 586)
vue_x = c(365, 465)
vue_y = c(846, 475)
vue_z = c(465, 845)
data <- data.frame(id, year, city_a, PA1, a1_x, a1_y, PA2, a2_x, a2_y, PA3, a3_x, a3_y, PA4, a4_x, a4_y, PA5, a5_x, a5_y, city_h, PH1, h1_x, h1_y, PH2, h2_x, h2_y, PH3, h3_x, h3_y, PH4, h4_x, h4_y, PH5, h5_x, h5_y, vue_x, vue_y, vue_z)
I'd like to modify the final data frame to have the following format:
id
year
City: gathers columns city_a and city_h
Person: gathers columns starting with PA or PH
x: gathers columns with pattern _x
y: gathers columns with pattern _y
z: shows data deom _z column
Hopefully the explanation makes sense.I've been trying examples modifying the solution here https://community.rstudio.com/t/pivot-longer-on-multiple-column-sets-pairs/43958/7 but haven't been successful.