Avoid hard-coding with pivot longer to pivot multiple columns at once-CodePudding

I want to pivot_long() multiple columns of the dataset below avoiding hard-coding . I've seen some similar questions, but I still cannot do it.

Wide data:

> head(data)
      ID IND_TEST_SCORE ARG_G1_ABC NARR_G1_ABC ARG_G1_EF NARR_G1_EF ARG_G2_ABC NARR_G2_ABC
1 PART_1            100      68.53       71.32      4.94       3.42      64.90       64.25
2 PART_2             36      65.90          NA      6.55         NA      63.80       59.00
3 PART_3             32      69.78          NA      2.44         NA      71.73          NA
4 PART_4             96      68.29       67.83      3.00       3.17      67.67       67.88
5 PART_5             11        NaN         NaN       NaN        NaN         NA       67.08
6 PART_6             12      69.50       71.60      3.25       2.50         NA          NA
  ARG_G2_EF NARR_G2_EF
1      7.10       5.08
2      7.40       7.00
3      1.09         NA
4      3.67       1.76
5        NA       3.00
6        NA         NA

desired output:

ID     IND_TEST_SCORE       ABC    EF   GROUP  TYPE
1 PART_1            100                   G1   ARG
1 PART_1            100                   G1   NARR
1 PART_1            100                   G2   ARG
1 PART_1            100                   G2   NARR
2 PART_2             36                   G1   ARG
2 PART_2             36                   G1   NARR
2 PART_2             36                   G2   ARG
2 PART_2             36                   G2   NARR
so on...

Questions

How can I:

1) Create a new column called "GROUP" with 'G1' and G2' values
2) Create a new column called "TYPE" with 'ARG' and NARR' values
3) Create 2 new columns, one for "ABC" values and another for "EF" values

without hard-coding it? I'd like to work with patterns...Thanks in advance!

Obs1: my attempt so far:

# create a single "my_names" collumns and work on it:

dataLong <- data %>%  
  pivot_longer(cols = c(-ID, -IND_TEST_SCORE), 
               names_to = "my_names",
               values_to = "my_values") %>% 
  mutate(GROUP = case_when(my_names == "ARG_G1_ABC" ~ "G1",
                              my_names == "ARG_G1_ABC" ~ "G2",
                              my_names == "ARG_G1_EF" ~  "G1",
                              my_names == "ARG_G2_EF" ~  "G2",
                              my_names == "NARR_G1_ABC" ~ "G1",
                              my_names == "NARR_G1_ABC" ~ "G2",
                              my_names == "NARR_G1_EF" ~  "G1",
                              my_names == "NARR_G2_EF" ~  "G2")) %>% 
  mutate(TYPE = case_when(my_names == "ARG_G1_ABC" ~ "ARG",
                          my_names == "ARG_G2_ABC" ~ "ARG",
                          my_names == "ARG_G1_EF" ~  "ARG",
                          my_names == "ARG_G2_EF" ~  "ARG",
                          my_names == "NARR_G1_ABC" ~ "NARR",
                          my_names == "NARR_G2_ABC" ~ "NARR",
                          my_names == "NARR_G1_EF" ~  "NARR",
                          my_names == "NARR_G2_EF" ~  "NARR"))

Obs2: dataset:

> dput(data)
structure(list(ID = structure(c("PART_1", "PART_2", "PART_3", 
"PART_4", "PART_5", "PART_6", "PART_7", "PART_8", "PART_9", "PART_10", 
"PART_11", "PART_12", "PART_13", "PART_14", "PART_15", "PART_16", 
"PART_17", "PART_18", "PART_19", "PART_20", "PART_21", "PART_22", 
"PART_23", "PART_24", "PART_25", "PART_26", "PART_27", "PART_28", 
"PART_29", "PART_30", "PART_31", "PART_32", "PART_33", "PART_34", 
"PART_35", "PART_36", "PART_37", "PART_38", "PART_39", "PART_40", 
"PART_41", "PART_42", "PART_43", "PART_44", "PART_45", "PART_46", 
"PART_47", "PART_48", "PART_49", "PART_50", "PART_51", "PART_52", 
"PART_53", "PART_54", "PART_55", "PART_56", "PART_57", "PART_58", 
"PART_59", "PART_60", "PART_61", "PART_62", "PART_63", "PART_64", 
"PART_65", "PART_66", "PART_67", "PART_68", "PART_69", "PART_70", 
"PART_71"), class = c("glue", "character")), IND_TEST_SCORE = c(100, 
36, 32, 96, 11, 12, 32, 72, 100, 64, 2, 19, 99, 86, 60, 108, 
95, 35, 60, 9, 78, 61, 61, 67, 105, 99, 51, 21, 65, 30, 0.9, 
77, 54, 14, 103, 48, 0.7, 2, 39, 94, 80, 8, 30, 103, 113, 91, 
59, 56, 86, 99, 72, 34, 32, 6, 44, 99, 65, 98, 110, 102, 87, 
50, 89, 36, 93, 8, 11, 78, 48, 77, 4), ARG_G1_ABC = c(68.53, 
65.9, 69.78, 68.29, NaN, 69.5, 67.05, 73.74, 73.59, 72.57, 64.33, 
67.79, 72.94, 63.75, 71.56, 75.5, 68.16, NA, 65.64, 68.36, 69.75, 
72.73, 67.67, 66.19, 62.94, 72.48, 72.19, 62.44, 72.5, 71.06, 
70.4, 69.14, NA, 67.59, 69.1, 74.05, NA, 68.6, 68.27, 59.12, 
NA, NA, 63.7, 67.18, NA, 68.38, 63.44, 72.56, 66.06, 66.53, 73.19, 
NA, NA, NA, 73.44, 67.45, 72.91, 65.81, 73.96, 75, 75.89, 72, 
NA, 68.2, 67.29, 69.91, NaN, 69.67, 68.39, 69.2, 67.55), NARR_G1_ABC = c(71.32, 
NA, NA, 67.83, NaN, 71.6, 64.2, 71.68, 73.29, 70.53, 73.35, 59.31, 
71.08, 74.06, 68.7, 74, 69.08, NA, 68.52, 63.47, 68.33, NA, 65.64, 
62.11, 63.9, 70.41, 60.36, 65.88, 68.81, 69.62, 70.68, 67.5, 
NA, 68.45, 67.16, 74.39, 60.6, 65.89, 71.94, 68.75, NA, NA, 67, 
66.85, NA, NA, 62.56, 73.33, 69.81, 67.68, 73.06, 65.8, 63.85, 
NA, 67.64, 71.6, 68.47, 69.39, 71.16, 72.33, NA, 66.68, NA, 66.22, 
67, 61.27, NaN, 72.33, 68.29, 71.33, 65.57), ARG_G1_EF = c(4.94, 
6.55, 2.44, 3, NaN, 3.25, 4.71, 2.84, 1.07, 2, 5.33, 5.43, 1.72, 
10.55, 3, 1.17, 5.8, NA, 10.55, 4.21, 2.94, 3.55, 6.33, 8.25, 
5.88, 2, 3.44, 9.22, 1.69, 4.18, 2.5, 4.71, NA, 4.41, 5.9, 2.21, 
NA, 6.67, 3.33, 7, NA, NA, 8, 4.76, NA, 4.44, 2.68, 3.16, 4.94, 
5.42, 2.81, NA, NA, NA, 1.78, 6.09, 2.52, 6.56, 1.96, 1.12, 0.67, 
3.78, NA, 3.5, 3.65, 5.27, NaN, 4.33, 6.78, 3.6, 4.35), NARR_G1_EF = c(3.42, 
NA, NA, 3.17, NaN, 2.5, 3.29, 1.64, 1.07, 6, 1.41, 9.25, 3.25, 
2.69, 3.8, 1.32, 3.04, NA, 2.38, 5.18, 2.38, NA, 6.18, 6.11, 
6.4, 1.85, 7.45, 3.69, 1.89, 3.25, 1.6, 4.8, NA, 2.8, 4.32, 2.3, 
6.6, 7.42, 2.83, 4.75, NA, NA, 5, 4.75, NA, NA, 8, 1.71, 2.67, 
2.05, 1.47, 4.8, 7.96, NA, 4.43, 3.8, 4.47, 4.91, 1.68, 2.78, 
NA, 6.58, NA, 6.67, 6, 5.18, NaN, 1.67, 4.86, 2.08, 4.38), ARG_G2_ABC = c(64.9, 
63.8, 71.73, 67.67, NA, NA, 52.5, 72.35, 65.28, 57.22, NA, NaN, 
69, 66.67, NaN, 66.58, 69, 60.55, 56.29, 67.45, 68.4, 64.25, 
NaN, 50.86, 67.83, 65.96, 57, 53.07, 66.89, NaN, NA, 59, 61.5, 
NA, 65.9, 64.07, NA, NA, 57.91, 67.89, 68.75, 68.5, NaN, 63.24, 
66.19, 60.59, 59.24, 54.33, 64.39, 65.83, 65.71, 63, 63.78, 63.62, 
64, 65.08, NA, 67.61, 67.57, 72.71, 65.46, 61.71, NA, 57.62, 
NA, NA, NA, 64, 61.33, 62.64, NA), NARR_G2_ABC = c(64.25, 59, 
NA, 67.88, 67.08, NA, 60.75, 64.42, 71.17, 58.42, NA, 49.8, 63.36, 
65.2, NaN, 70.2, 62.85, NaN, 61.6, 53.92, 62.63, NA, NaN, 50.46, 
65.14, 60.58, 63.29, NA, 64.33, NaN, NA, 68.57, NA, NA, 66.3, 
NA, 57.29, NA, 53.5, 63.48, NA, 57.07, NaN, 61.82, NA, 68.61, 
57.1, 62.84, 63, 61.91, 58.38, NaN, 61.56, NA, NaN, 65.55, 63.8, 
65, 63.14, 67.31, 67.75, 57.62, 63.31, 54.83, 66.43, NA, NA, 
64.67, 57.92, 59, NA), ARG_G2_EF = c(7.1, 7.4, 1.09, 3.67, NA, 
NA, 12.75, 1.24, 3.28, 9.78, NA, NaN, 1.71, 1.93, NaN, 6.21, 
2.76, 7.91, 8.65, 3.55, 3.4, 5, NaN, 16.05, 3.39, 4.52, 13, 11.6, 
5.05, NaN, NA, 9.5, 9.67, NA, 7.03, 3.87, NA, NA, 8, 3.33, 2.19, 
3, NaN, 8.53, 3.37, 5.47, 7.35, 13.48, 5.33, 3.83, 3.65, 5.82, 
4, 6.17, 6, 6.42, NA, 3.83, 2.71, 2.19, 4.58, 5.18, NA, 9.75, 
NA, NA, NA, 5, 6.44, 5.36, NA), NARR_G2_EF = c(5.08, 7, NA, 1.76, 
3, NA, 8.88, 4.26, 2.92, 7.08, NA, 10.6, 5.5, 4.16, NaN, 2.87, 
4.7, NaN, 7, 9.5, 4.68, NA, NaN, 12.75, 4.77, 9.15, 5, NA, 5.44, 
NaN, NA, 4.57, NA, NA, 1.7, NA, 11.29, NA, 13.33, 5.95, NA, 10.79, 
NaN, 5.18, NA, 5.22, 7.1, 3.53, 5.75, 6.77, 6.31, NaN, 7.88, 
NA, NaN, 3, 4.88, 4.69, 6.19, 10.31, 3.62, 9.75, 5.46, 6.83, 
4.43, NA, NA, 3.67, 8.67, 8.53, NA)), row.names = c(NA, -71L), class = "data.frame")

CodePudding user response：

We may use pivot_longer - specify the columns with matches that match the column names substring _ABC or _EF at the end ($) of the string and split the column names at _ by specifying names_sep as _ as well as specify the corresponding column names in names_to (.value will return the value of the columns where as TYPE or GROUP gets the first and second substring from column names

library(tidyr)
pivot_longer(data, cols = matches('_(ABC|EF)$'), 
   names_to = c("TYPE", "GROUP", ".value"), 
     names_sep = "_", values_drop_na = TRUE)

-output

# A tibble: 217 × 6
   ID     IND_TEST_SCORE TYPE  GROUP   ABC    EF
   <glue>          <dbl> <chr> <chr> <dbl> <dbl>
 1 PART_1            100 ARG   G1     68.5  4.94
 2 PART_1            100 NARR  G1     71.3  3.42
 3 PART_1            100 ARG   G2     64.9  7.1 
 4 PART_1            100 NARR  G2     64.2  5.08
 5 PART_2             36 ARG   G1     65.9  6.55
 6 PART_2             36 ARG   G2     63.8  7.4 
 7 PART_2             36 NARR  G2     59    7   
 8 PART_3             32 ARG   G1     69.8  2.44
 9 PART_3             32 ARG   G2     71.7  1.09
10 PART_4             96 ARG   G1     68.3  3   
# … with 207 more rows