Extract upper and lower triangular with different value part of a NumPy matrix to a 2 column pandas

user_11118581 0
Viewed 454

I have a test_matrix:

    A  B  C
A  nan 10 20
B  30 nan 40
C  50 60 nan

My DataFrame should be:

cus1    cus2    lower    upper
 A       B       30       10
 A       C       50       20
 B       C       60       40

I can extract DataFrame above with 2 parts (I extract the upper is first and the lower triangular is second):

lower_triangular = test_matrix[np.tril_indices(test_matrix.shape[0], -1)]
upper_triangular = test_matrix[np.triu_indices(test_matrix.shape[0], 1)]

But when I create a DataFrame, I have a bunch of code is very complex to extract right DataFrame above.

Can I extract it one times?

UPDATE SOLUTION

Mr/Ms Pygirl give a good solution, but when your matrix has value 0:

    A   B   C
A  nan 10   0
B  30  nan  40
C  0   60  nan

Pygirl-solution will give a result:

cus1    cus2    lower    upper
A       B       30       10
B       C       60       40

If you want to get a value 0 (index: AC and CA), you should use:

df2=df.where(np.triu(np.ones(df.shape)).astype(np.bool)).stack().rename_axis(('cus1', 'cus2')).reset_index(name='upper')

y=df.where(np.tril(np.ones(df.shape)).astype(np.bool)).stack().values

The result:

cus1    cus2    lower    upper
A       B       30       10
A       C       0        0
B       C       60       40

PROBLEM 2 (AFTER USE PYGIRL-SOLUTION)

I have a test_matrix with 4x4 dimension:

    A    B    C    D
A  nan   10   20   30 
B  40    nan  50   60
C  70    80   nan  90
D  100   110  120  nan

My DataFrame should be:

cus1    cus2    lower    upper
 A       B       40       10
 A       C       70       20
 A       D       100      30
 B       C       80       50
 B       D       110      60
 D       C       120      90

But I get a wrong result (lost DC and wrong AD,BC):

cus1    cus2    lower    upper
 A       B       40       10
 A       C       70       20
 A       D       *80*     *30*
 B       C       *100*    *50*
 B       D       110      60
pandas dataframe numpy python
2 Answers

try:

li = ['A', 'B' , 'C']
df = pd.DataFrame(test_matrix, index=li, columns=li)
    
df2=df.where(np.triu(df).astype(np.bool)).stack().rename_axis(('cus1', 'cus2')).reset_index(name='upper')
y=df.where(np.tril(df).astype(np.bool)).stack().values#.reset_index(name='upper')
df2['lower'] = y

df2:

    cus1    cus2    upper   lower
0   A       B       10.0    30.0
1   A       C       20.0    50.0
2   B       C       40.0    60.0

EDIT:

df = pd.DataFrame(test_matrix, index=li, columns=li)
    
df2=df.where(np.triu(df).astype(np.bool)).stack().rename_axis(('cus1', 'cus2')).reset_index(name='upper')
df1=df.where(np.tril(df).astype(np.bool)).stack().rename_axis(('cus2', 'cus1')).reset_index(name='lower')
df3 = pd.merge(df1,df2,on=['cus2', 'cus1'])

df3:

  cus2 cus1 lower   upper
0   B   A   40.0    10.0
1   C   A   70.0    20.0
2   C   B   80.0    50.0
3   D   A   100.0   30.0
4   D   B   110.0   60.0
5   D   C   120.0   90.0
user_66603730

Code Setup:

import pandas as pd
import numpy as np

test_matrix = np.array([[np.nan,10,20],[30,np.nan,40],[50,60,np.nan]])

lower_triangular = test_matrix[np.tril_indices(test_matrix.shape[0], -1)]

lower_triangular = list(np.tril(test_matrix).flat)
upper_triangular = list(np.triu(test_matrix).flat)

lower_triangular = [x for x in lower_triangular if x>0]
upper_triangular = [x for x in upper_triangular if x>0]


cus1 = np.tril_indices(test_matrix.shape[0], -1)[0]
cus2 = np.tril_indices(test_matrix.shape[0], -1)[1]

q = pd.DataFrame(
    {'cus1': cus1,
     'cus2': cus2,
     'lower': lower_triangular,
     'upper': upper_triangular
    })

Output:

   cus1  cus2  lower  upper
0     1     0   30.0   10.0
1     2     0   50.0   20.0
2     2     1   60.0   40.0
user_148844540
Related