Here's a way you can do that:
The script is more wordy than it needs to be to make it easier to follow.
Since I qualified the tidyverse functions you may not need to load them, or you can replace the 4 library calls with a single library(tidyverse) to load all of them at once.
library(tidyr)
library(dplyr)
library(stringr)
library(purr)
dt <- data.frame(
Group = c(rep("ABC1", 10), rep("ABC2", 10)),
SubGroup = c(rep("XYZ1", 5), rep("XYZ2", 5), rep("XYZ1", 5), rep("XYZ2", 5)),
A1 = c(rep("LMN_PQR1", 5), rep("LMN_PQR2", 10), rep("LMN_PQR1", 5)),
A2 = c(rep("TUV_WXY2", 5), rep("TUV_WXY4", 5), rep("TUV_JKL3", 10)),
stringsAsFactors = FALSE
)
- Define a function that will take a column name from a data.frame, get the content of the first row, split by "_", take the first character vector result as new column name, and then proceed to separate the column (default is splitting by non_alpha character, as such it will split at the "_"). NA for the first column name will skip creating the column. [If you're in the same environment, you may be able to remove the second parameter, dframe and use dt directly. In that case also lose the corresponding argument to map_dfc.]
split_column <- function(col, dframe) {
row1 <- dframe[1, col]
new_col_name <- stringr::str_split_fixed(row1, "_", n = 2)[, 1]
new_col <- dframe %>%
dplyr::select(col) %>%
tidyr::separate(col, c(NA, new_col_name))
}
- Build a list of column names without the first 2:
cols <- colnames(dt)[-1:-2]
- Then run the split_column function over all remaining columns with map_dfc, which returns a data frame with the combined columns:
new_cols <- purrr::map_dfc(cols, split_column, dt)
- Add in the original first 2 columns to create the modified dt:
new_dt <- dplyr::bind_cols(dt[1:2], new_cols)
And here's the result:
Group SubGroup LMN TUV
1 ABC1 XYZ1 PQR1 WXY2
2 ABC1 XYZ1 PQR1 WXY2
3 ABC1 XYZ1 PQR1 WXY2
4 ABC1 XYZ1 PQR1 WXY2
5 ABC1 XYZ1 PQR1 WXY2
6 ABC1 XYZ2 PQR2 WXY4
7 ABC1 XYZ2 PQR2 WXY4
8 ABC1 XYZ2 PQR2 WXY4
9 ABC1 XYZ2 PQR2 WXY4
10 ABC1 XYZ2 PQR2 WXY4
11 ABC2 XYZ1 PQR2 JKL3
12 ABC2 XYZ1 PQR2 JKL3
13 ABC2 XYZ1 PQR2 JKL3
14 ABC2 XYZ1 PQR2 JKL3
15 ABC2 XYZ1 PQR2 JKL3
16 ABC2 XYZ2 PQR1 JKL3
17 ABC2 XYZ2 PQR1 JKL3
18 ABC2 XYZ2 PQR1 JKL3
19 ABC2 XYZ2 PQR1 JKL3
20 ABC2 XYZ2 PQR1 JKL3
Voilà