Returning a dataframe that lists column pairs using comprehension

Question

I begin with a Correlation matrix DataFrame similar to this:

    x      y    z
x   1.0   0.4   0.25
y   0.2   1.0  -0.44
z  -0.3  -0.3  1.0

I want to know how to create a new DataFrame that will store all column pairs and their corresponding correlation value such as: ['Column name1', 'Column name2', 'Correlation value'].

An example row: [x, y, 0.2]

Is there a way to do this with comprehension, while also ensuring that no pairs are included twice, and that correlation pairs equal to 1 are excluded?

Something like [[x, y, df.loc[x,y]] for x in df.columns for y in df.columns

If you need all possible combinations, you may need something like [itertools.product](https://docs.python.org/2/library/itertools.html#itertools.product) to help with the efficiency — G. Anderson, Feb 22 '19 at 23:08

score 2 · Answer 1 · answered Feb 23 '19 at 00:30

Use pd.DataFrame.unstack and reset_index:

print(df)

     x    y     z
x  1.0  0.4  0.25
y  0.2  1.0 -0.44
z -0.3 -0.3  1.00

df.unstack().reset_index()

  level_0 level_1     0
0       x       x  1.00
1       x       y  0.20
2       x       z -0.30
3       y       x  0.40
4       y       y  1.00
5       y       z -0.30
6       z       x  0.25
7       z       y -0.44
8       z       z  1.00

Dyno Fu · Answer 2 · 2019-02-22T23:20:35.533

Adopted from Correlation matrix using pandas

import numpy as np
import pandas as pd

rs = np.random.RandomState(0)
df = pd.DataFrame(rs.rand(10, 10))
# df

corr = df.corr()
# corr

result = [(idx, col, corr[col].loc[idx])
           for idx in corr.index
           for col in corr.columns]
print(result)

for your example,

from io import StringIO
from pprint import pprint

import pandas as pd

csv = """
    x      y    z
x   1.0   0.4   0.25
y   0.2   1.0  -0.44
z  -0.3  -0.3  1.0
"""

df = pd.read_csv(StringIO(csv), sep=r"\s+")
print(df)
result = [(idx, col, df[col].loc[idx]) for idx in df.index for col in df.columns]
pprint(result)

output

     x    y     z
x  1.0  0.4  0.25
y  0.2  1.0 -0.44
z -0.3 -0.3  1.00
[('x', 'x', 1.0),
 ('x', 'y', 0.4),
 ('x', 'z', 0.25),
 ('y', 'x', 0.2),
 ('y', 'y', 1.0),
 ('y', 'z', -0.44),
 ('z', 'x', -0.3),
 ('z', 'y', -0.3),
 ('z', 'z', 1.0)]

Returning a dataframe that lists column pairs using comprehension

2 Answers2