arielle arielle - 1 month ago 7
R Question

R: Sorting columns based on partial match of column names with row names

I have a data frame that can be simplified to look like this (included the dput at the end):

T2_KL_21 A1_LC_11 W3_FA_22 RR_BI_12 PL_EW_12 RT_LC_22 YU_BI_21
FA 1 2 3 4 5 6 7
BI 1 2 3 4 5 6 7
KL 1 2 3 4 5 6 7
EW 1 2 3 4 5 6 7
LC 1 2 3 4 5 6 7


I would like to sort the columns so that they follow the order of the row names (based on partial match). It would then look like this:

W3_FA_22 RR_BI_12 YU_BI_21 T2_KL_21 PL_EW_12 A1_LC_11 RT_LC_22
FA 3 4 7 1 5 2 6
BI 3 4 7 1 5 2 6
KL 3 4 7 1 5 2 6
EW 3 4 7 1 5 2 6
LC 3 4 7 1 5 2 6


If more than one column name contains the string in the row names, they should be kept side by side, but the order does not matter.

I have already filtered the columns so that they all contain a match in the row names.

Here is the dput of the data frame:

structure(list(T2_KL_21 = c(1L, 1L, 1L, 1L, 1L), A1_LC_11 = c(2L,
2L, 2L, 2L, 2L), W3_FA_22 = c(3L, 3L, 3L, 3L, 3L), RR_BI_12 = c(4L,
4L, 4L, 4L, 4L), PL_EW_12 = c(5L, 5L, 5L, 5L, 5L), RT_LC_22 = c(6L,
6L, 6L, 6L, 6L), YU_BI_21 = c(7L, 7L, 7L, 7L, 7L)), .Names = c("T2_KL_21",
"A1_LC_11", "W3_FA_22", "RR_BI_12", "PL_EW_12", "RT_LC_22", "YU_BI_21"
), class = "data.frame", row.names = c("FA", "BI", "KL", "EW",
"LC"))


I have tried using pmatch, grep and match, with no success.

Any advice will be much appreciated! Thanks

Answer

We can loop through the rownames and grep to find the index of the column names that match, unlist and use that to arrange the columns

df1[unlist(lapply(gsub("\\d+", "", row.names(df1)), function(x) grep(x, names(df1))))]
#W3_FA_22 RR_BI_12 YU_BI_21 T2_KL_21 PL_EW_12 A1_LC_11 RT_LC_22
#FA        3        4        7        1        5        2        6
#BI        3        4        7        1        5        2        6
#KL        3        4        7        1        5        2        6
#EW        3        4        7        1        5        2        6
#LC        3        4        7        1        5        2        6