I am looking to transpose data in R. I have the following dataframe.
and I need to transpose this so the column rows would read as follows:
and so on so this table would have 90 rows (9 for each source destination)
What is the best and fastest way to do this?
It appears that you have a symmetric matrix. You can use dist2df from my SOfun package.
Here's some sample data:
df <- structure(list(City = structure(c(2L, 5L, 4L, 3L, 1L), .Label = c("CFMG", "CONT", "DILG", "DMNR", "INTG"), class = "factor"), CONT = c(0, 0.566595445231594, 0.576844266554399, 0.493804001521439, 0.431543849302017 ), INTG = c(0.566595445231594, 0, 0.0176923632394502, 0.0642444500652801, 0.0929571208871039), DMNR = c(0.576844266554399, 0.0176923632394502, 0, 0.0815745238751543, 0.093320922043577), DILG = c(0.493804001521439, 0.0642444500652801, 0.0815745238751543, 0, 0.0206006197275541 ), CFMG = c(0.431543849302017, 0.0929571208871039, 0.093320922043577, 0.0206006197275541, 0)), row.names = c(NA, 5L), class = "data.frame") df # City CONT INTG DMNR DILG CFMG # 1 CONT 0.0000000 0.56659545 0.57684427 0.49380400 0.43154385 # 2 INTG 0.5665954 0.00000000 0.01769236 0.06424445 0.09295712 # 3 DMNR 0.5768443 0.01769236 0.00000000 0.08157452 0.09332092 # 4 DILG 0.4938040 0.06424445 0.08157452 0.00000000 0.02060062 # 5 CFMG 0.4315438 0.09295712 0.09332092 0.02060062 0.00000000 The dist2df function expects a dist object, so use as.dist on the data.frame after dropping the first column:
library(SOfun) dist2df(as.dist(df[-1])) # row col value # 1 INTG CONT 0.56659545 # 2 DMNR CONT 0.57684427 # 3 DILG CONT 0.49380400 # 4 CFMG CONT 0.43154385 # 5 DMNR INTG 0.01769236 # 6 DILG INTG 0.06424445 # 7 CFMG INTG 0.09295712 # 8 DILG DMNR 0.08157452 # 9 CFMG DMNR 0.09332092 # 10 CFMG DILG 0.02060062 You can also use melt in the following way. This approach will retain the diagonal, while dist2df does not.
library(data.table) df1 <- df # making a copy since we're destructively editing the data df1[cbind(FALSE, upper.tri(df[-1]))] <- NA melt(as.data.table(df1), "City", na.rm = TRUE) # City variable value # 1: CONT CONT 0.00000000 # 2: INTG CONT 0.56659545 # 3: DMNR CONT 0.57684427 # 4: DILG CONT 0.49380400 # 5: CFMG CONT 0.43154385 # 6: INTG INTG 0.00000000 # 7: DMNR INTG 0.01769236 # 8: DILG INTG 0.06424445 # 9: CFMG INTG 0.09295712 # 10: DMNR DMNR 0.00000000 # 11: DILG DMNR 0.08157452 # 12: CFMG DMNR 0.09332092 # 13: DILG DILG 0.00000000 # 14: CFMG DILG 0.02060062 # 15: CFMG CFMG 0.00000000 
dputa sample of the data?