Trying to transpose data in R

Question

I am looking to transpose data in R. I have the following dataframe.

Old Table

and I need to transpose this so the column rows would read as follows:

New Table

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?

What's your actual data structure? Can you `dput` a sample of the data? — A5C1D2H2I1M1N2O1R2T1, Dec 11 '20 at 03:10
Does this answer your question? [Reshaping data.frame from wide to long format](https://stackoverflow.com/questions/2185252/reshaping-data-frame-from-wide-to-long-format) — denis, Dec 11 '20 at 16:09

A5C1D2H2I1M1N2O1R2T1 · Answer 1 · 2020-12-11T03:42:35.227

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

Trying to transpose data in R

1 Answers1