How to sort a dataframe in R by mapping column names of one dataframe based on the order of row names of another dataframe?

1
votes

I was looking to sort a dataframe in R by mapping column names of one dataframe based on the order of row names of another dataframe. After this identify whether the rows and column names that have been sorted and mapped correctly for the purpose plotting a heatmap. Similarly I have many such dataframes, and performing the same manually using MS Excel is very tedious. Please assist me with this. Example of the two dataframes (df1 and df2) and output dataframe (df3) are given below:

dput(df1)
structure(list(Condition = c("Pos", "Pos", "Pos", "Pos", "Neg", 
"Neg", "Neg", "Neutral", "Neutral", "Neutral", "Neutral"), State = c("A", 
"A", "A", "A", "A", "A", "B", "B", "B", "B", "B")), class = "data.frame", row.names = c("GSM533312", 
"GSM533313", "GSM533321", "GSM533318", "GSM533315", "GSM533317", 
"GSM533314", "GSM533319", "GSM533320", "GSM533316", "GSM533322"
))



> dput(df2)
structure(list(GSM533312 = c(126.9026154, 417.8250699, 10, 10, 
10, 23.52525494, 10, 39.17102201, 10, 18.67273627), GSM533313 = c(69.54673614, 
462.6758022, 10, 10, 10, 26.73450534, 10, 27.07827269, 10, 16.24450811
), GSM533314 = c(60.80383818, 520.416152, 10, 10, 10, 24.43279709, 
10, 35.73886139, 10, 14.23653399), GSM533315 = c(55.13260829, 
295.2346963, 10, 10, 10, 14.06799091, 10, 20.01497351, 10, 22.59825164
), GSM533316 = c(45.89227444, 381.1542662, 10, 10, 10, 23.09791483, 
10, 29.2303841, 10, 19.17842478), GSM533317 = c(70.44756764, 
270.053056, 10, 10, 10, 11.72493597, 10, 29.75588034, 10, 20.24234006
), GSM533318 = c(137.5149798, 399.8257215, 10, 10, 10, 25.61676988, 
10, 17.51122374, 10, 26.61600151), GSM533319 = c(149.9062073, 
392.0936192, 10, 10, 10, 34.50483435, 10, 17.70500769, 10, 25.42173724
), GSM533320 = c(47.45800668, 308.1893821, 10, 10, 10, 19.99262754, 
10, 22.51401429, 10, 17.11589963), GSM533321 = c(58.67205043, 
334.0037624, 10, 10, 10, 10, 10, 17.07492728, 10, 16.99331094
), GSM533322 = c(47.9377578, 346.2849982, 10, 10, 10, 11.17797599, 
10, 19.35690144, 10, 18.52996342)), class = "data.frame", row.names = c("Gene_A", 
"Gene_B", "Gene_C", "Gene_D", "Gene_E", "Gene_F", "Gene_G", "Gene_H", 
"Gene_AA", "Gene_AB"))


> dput(df3)
structure(list(GSM533312 = c(126.9026154, 417.8250699, 10, 10, 
10, 23.52525494, 10, 39.17102201, 10, 18.67273627), GSM533313 = c(69.54673614, 
462.6758022, 10, 10, 10, 26.73450534, 10, 27.07827269, 10, 16.24450811
), GSM533321 = c(58.67205043, 334.0037624, 10, 10, 10, 10, 10, 
17.07492728, 10, 16.99331094), GSM533318 = c(137.5149798, 399.8257215, 
10, 10, 10, 25.61676988, 10, 17.51122374, 10, 26.61600151), GSM533315 = c(55.13260829, 
295.2346963, 10, 10, 10, 14.06799091, 10, 20.01497351, 10, 22.59825164
), GSM533317 = c(70.44756764, 270.053056, 10, 10, 10, 11.72493597, 
10, 29.75588034, 10, 20.24234006), GSM533314 = c(60.80383818, 
520.416152, 10, 10, 10, 24.43279709, 10, 35.73886139, 10, 14.23653399
), GSM533319 = c(149.9062073, 392.0936192, 10, 10, 10, 34.50483435, 
10, 17.70500769, 10, 25.42173724), GSM533320 = c(47.45800668, 
308.1893821, 10, 10, 10, 19.99262754, 10, 22.51401429, 10, 17.11589963
), GSM533316 = c(45.89227444, 381.1542662, 10, 10, 10, 23.09791483, 
10, 29.2303841, 10, 19.17842478), GSM533322 = c(47.9377578, 346.2849982, 
10, 10, 10, 11.17797599, 10, 19.35690144, 10, 18.52996342)), class = "data.frame", row.names = c("Gene_A", 
"Gene_B", "Gene_C", "Gene_D", "Gene_E", "Gene_F", "Gene_G", "Gene_H", 
"Gene_AA", "Gene_AB"))

Identify equivalence of the names:

colnames(df3)==rownames(df1)

Thank you,

Toufiq

1
rdataframemappingequivalence

1 Answers

2
votes

We can use the row.names as column names

dfn <- df2[row.names(df1)]
identical(df3, dfn)
#[1] TRUE

If there are many datasets, place it in a list and do this in one step and store it in a list

lst1 <- lapply(list(df2, df4, df5), function(dat) dat[row.names(df1)])