Ramon J Romero y Vigil Ramon J Romero y Vigil - 1 year ago 130
Python Question

Melt the Upper Triangular Matrix of a Pandas Dataframe

Given a square pandas DataFrame of the following form:

a b c
a 1 .5 .3
b .5 1 .4
c .3 .4 1

How can I
only the upper triangle to get

Row Column Value
a a 1
a b .5
a c .3
b b 1
b c .4
c c 1

#Note the combination a,b is only listed once. There is no b,a listing

I'm more interested in an idiomatic pandas solution, a custom indexer would be easy enough to write by hand... Thank you in advance for your consideration and response.

Answer Source

First I convert lower values of df to NaN by where and numpy.triu and then stack, reset_index and set column names:

import numpy as np

print df
     a    b    c
a  1.0  0.5  0.3
b  0.5  1.0  0.4
c  0.3  0.4  1.0

print np.triu(np.ones(df.shape)).astype(np.bool)
[[ True  True  True]
 [False  True  True]
 [False False  True]]

df = df.where(np.triu(np.ones(df.shape)).astype(np.bool))
print df
    a    b    c
a   1  0.5  0.3
b NaN  1.0  0.4
c NaN  NaN  1.0

df = df.stack().reset_index()
df.columns = ['Row','Column','Value']
print df

  Row Column  Value
0   a      a    1.0
1   a      b    0.5
2   a      c    0.3
3   b      b    1.0
4   b      c    0.4
5   c      c    1.0
Recommended from our users: Dynamic Network Monitoring from WhatsUp Gold from IPSwitch. Free Download