Transforming A Correlation Matrix To A 3 Column Dataframe In Pandas?

I have a correlation matrix like so a b c a 1 0.5 0.3 b 0.5 1 0.7 c 0.3 0.7 1 And I want to transform this into a dataframe where the columns are like th

Solution 1:

Use stack with reset_index:

df1 = df.stack().reset_index()
df1.columns =['Letter1','Letter2','correlation']
print (df1)
  Letter1 Letter2  correlation
0       a       a          1.01       a       b          0.52       a       c0.33       b       a          0.54       b       b          1.05       b       c0.76c       a          0.37c       b          0.78cc1.0

And then insert columns by positions filled by factorizeed values:

df1.insert(0, 'Value1', pd.factorize(df1['Letter1'])[0] + 1)
df1.insert(2, 'Value2', pd.factorize(df1['Letter2'])[0] + 1)

print (df1)
   Value1Letter1Value2Letter2correlation01a1a1.011a2b0.521a3c0.332b1a0.542b2b1.052b3c0.763c1a0.373c2b0.783c3c1.0