transform data frame from wide to long (several variables) using separator [duplicate]

Question

I have a df like this one:

FID <- c(1,2,3,4)
d <- c(1,1,2,2)
IID_1 <- c("1A","2A","NA","4A")
IID_2 <- c("1B","2B","3B","4B")
PS_1 <- c(0.5,0.8, NA, 0.3)
PS_2 <- c(0.3,0.4, 0.1, 0.4)
EA_1 <- c(0.2,0.8, NA, 0.3)
EA_2 <- c(0.5,0.5, 0.1, 0.2)

df <- data.frame (FID,d,IID_1,IID_2, PS_1, PS_2, EA_1, EA_2)

and I need to convert it to the long format, as this one:

FID <- c(1,1,2,2,3,3,4,4)
d <- c(1,1,1,1,2,2,2,2)
IID <- c("1A","1B", "2A", "2B","NA","3B", "4A", "4B")

PS <- c(0.5,0.3, 0.8,0.4, NA,0.1, 0.3, 0.4)

EA <- c(0.2,0.5,0.8,0.5 ,NA,0.1, 0.3,0.2)


df2 <- data.frame (FID,d,IID, PS, EA)

I am stuck since I have tried with pivot_longer but I cannot make it work. I do not know if it is because of the separator.

Thank you so much for your help

Answer 1

We could use pivot_longer with the names_sep argument. The trick is to use names_to with c(".value", "num")

.value in names_to forces R to use the values from the original column as the name of the new column. Here we use .value in first place -> so the new variable will be called PS, EA and IID, the part after _ will called num. In our code we remove num:

library(dplyr)
library(tidyr)

df %>%
  pivot_longer(cols = -c(FID, d), 
               names_to = c(".value", "num"),
               names_sep = "_") %>%
  select(-num)

    FID     d IID      PS    EA
  <dbl> <dbl> <chr> <dbl> <dbl>
1     1     1 1A      0.5   0.2
2     1     1 1B      0.3   0.5
3     2     1 2A      0.8   0.8
4     2     1 2B      0.4   0.5
5     3     2 NA     NA    NA  
6     3     2 3B      0.1   0.1
7     4     2 4A      0.3   0.3
8     4     2 4B      0.4   0.2