r transform dataset from wide to long for column names with patterns

Question

Let us assume a dataset like this below.

 ID   Gender  ColA_1  ColB_1 ColC_1__1 ColC_1__2 ColA_2  ColB_2 ColC_2__1 ColC_2__2 
 1    Male    No      Yes    Yes       No        No      Yes    No        No
 2    Female  Yes     No     Yes       No        No      Yes    Yes       No

What I like to do is transform this dataset like this below

 ID Index  Gender  ColA  ColB   ColC_1 ColC_2  
 1  1      Male    No    Yes    Yes       No        
 1  2      Male    No    Yes    No        No
 2  1    Female    Yes   No     Yes       No        
 2  2    Female    No    Yes    Yes       No  

Not sure how to do this, need help. Thanks in advance.

Answer 1

Here is an option with pivot_longer

library(dplyr)
library(tidyr)
library(stringr)
df1 %>% 
     rename_at(vars(starts_with('ColC')), ~ str_replace(., "_(\\d+)__(\\d+)",
              "\\2_\\1")) %>%
     pivot_longer(cols = 3:ncol(.),  names_to = c(".value", "Index"), 
                 names_sep="_")
# A tibble: 4 x 7
#     ID Gender Index ColA  ColB  ColC1 ColC2
#  <int> <chr>  <chr> <chr> <chr> <chr> <chr>
#1     1 Male   1     No    Yes   Yes   No   
#2     1 Male   2     No    Yes   No    No   
#3     2 Female 1     Yes   No    Yes   No   
#4     2 Female 2     No    Yes   Yes   No   

data

df1 <- structure(list(ID = 1:2, Gender = c("Male", "Female"), ColA_1 = c("No", 
"Yes"), ColB_1 = c("Yes", "No"), ColC_1__1 = c("Yes", "Yes"), 
    ColC_1__2 = c("No", "No"), ColA_2 = c("No", "No"), ColB_2 = c("Yes", 
    "Yes"), ColC_2__1 = c("No", "Yes"), ColC_2__2 = c("No", "No"
    )), class = "data.frame", row.names = c(NA, -2L))

Answer 2

You can solve your problem using data.table package:

library(data.table)
melt(data = setDT(df), 
     measure = patterns(ColA="ColA", ColB="ColB", ColC_1="ColC_.+1$", ColC_2="ColC_.+2$"), 
     variable.name = "index")