In the dataframe below, I want to stack the three columns with different names (s_psy01, l_psy01, r_psy01) and name the resulting new column as psy01. The same goes for the other two sets of columns. How can I do this using preferably tidyverse or through alternative methods.
nr=c(1:6)
s_psy01=c(NA,NA,NA,NA,4,5)
l_psy01=c(NA,NA,2,3,NA,NA)
r_psy01=c(2,5,NA,NA,NA,NA)
s_psy02=c(NA,NA,NA,NA,2,3)
l_psy02=c(NA,NA,5,2,NA,NA)
r_psy02=c(3,5,NA,NA,NA,NA)
s_psy03=c(NA,NA,NA,NA,2,4)
l_psy03=c(NA,NA,2,5,NA,NA)
r_psy03=c(1,3,NA,NA,NA,NA)
df <- data.frame(nr,s_psy01, l_psy01, r_psy01,
s_psy02, l_psy02, r_psy02,
s_psy03, l_psy03, r_psy03)
One approach with pivot_longer - capture the non-digits (\\D+) and the digits (\\d+) till the end ($) of the string from the _ as a single group in names_pattern for the corresponding .value specified in names_to to concatenate the column values that match the substring suffix into a single column in the long format
library(tidyr)
pivot_longer(df,
cols =-nr, names_to = c("prefix", ".value"),
names_pattern = "(.*)_(\\D+\\d+)$", values_drop_na = TRUE)
-output
# A tibble: 6 × 5
nr prefix psy01 psy02 psy03
<int> <chr> <dbl> <dbl> <dbl>
1 1 r 2 3 1
2 2 r 5 5 3
3 3 l 2 5 2
4 4 l 3 2 5
5 5 s 4 2 2
6 6 s 5 3 4