Reshaping a pandas correlation matrix
Use the code below to (a) reshape the correlation matrix, (b) remove duplicate rows (e.g., {aaa, bbb} and {bbb, aaa}), and (c) remove rows that contain the same variable in the first two columns (e.g., {aaa, aaa}):
# calculate the correlation matrix and reshape
df_corr = df.corr().stack().reset_index()
# rename the columns
df_corr.columns = ['FEATURE_1', 'FEATURE_2', 'CORRELATION']
# create a mask to identify rows with duplicate features as mentioned above
mask_dups = (df_corr[['FEATURE_1', 'FEATURE_2']].apply(frozenset, axis=1).duplicated()) | (df_corr['FEATURE_1']==df_corr['FEATURE_2'])
# apply the mask to clean the correlation dataframe
df_corr = df_corr[~mask_dups]
This will generate an output like this:
FEATURE_1 FEATURE_2 CORRELATION
0 aaa bbb 0.346099
1 aaa ccc 0.131874
2 aaa ddd -0.150910
3 aaa eee 0.177589
4 bbb ccc 0.177308
5 bbb ddd -0.384893
6 bbb eee 0.301150
7 ccc ddd -0.176995
8 ccc eee 0.258812
9 ddd eee -0.310137
You need add reset_index:
#reset columns and index names
df = df.rename_axis(None).rename_axis(None, axis=1)
#if pandas version below 0.18.0
#df.columns.name = None
#df.index.name = None
print (df)
aaa bbb ccc ddd eee
aaa 1.000000 0.346099 0.131874 -0.150910 0.177589
bbb 0.346099 1.000000 0.177308 -0.384893 0.301150
ccc 0.131874 0.177308 1.000000 -0.176995 0.258812
ddd -0.150910 -0.384893 -0.176995 1.000000 -0.310137
eee 0.177589 0.301150 0.258812 -0.310137 1.000000
df1 = df.stack().reset_index()
#set column names
df1.columns = ['a','b','c']
print (df1)
a b c
0 aaa aaa 1.000000
1 aaa bbb 0.346099
2 aaa ccc 0.131874
3 aaa ddd -0.150910
4 aaa eee 0.177589
5 bbb aaa 0.346099
6 bbb bbb 1.000000
7 bbb ccc 0.177308
8 bbb ddd -0.384893
9 bbb eee 0.301150
10 ccc aaa 0.131874
11 ccc bbb 0.177308
12 ccc ccc 1.000000
13 ccc ddd -0.176995
14 ccc eee 0.258812
15 ddd aaa -0.150910
16 ddd bbb -0.384893
17 ddd ccc -0.176995
18 ddd ddd 1.000000
19 ddd eee -0.310137
20 eee aaa 0.177589
21 eee bbb 0.301150
22 eee ccc 0.258812
23 eee ddd -0.310137
24 eee eee 1.000000