Get col names and index based on dataframe value

Question

I have dataframe df_corr like this

  A2M.AX    ABC.AX    AGL.AX    AHY.AX    ALL.AX    AMC.AX    AMP.AX  
A2M.AX  1.000000 -0.505433  0.687367  0.223044 -0.664764 -0.199477   
ABC.AX -0.505433  1.000000 -0.801770 -0.606418  0.860923  0.332359   
AGL.AX  0.687367 -0.801770  1.000000  0.394378 -0.917379 -0.193461  
AHY.AX  0.223044 -0.606418  0.394378  1.000000 -0.483766 -0.063892  
ALL.AX -0.664764  0.860923 -0.917379 -0.483766  1.000000  0.177633

i want to get the index and column names based on value This is my attempt:

df_corr[(df_corr>0.7)&(df_corr<1)]


 A2M.AX    ABC.AX    AGL.AX    AHY.AX    ALL.AX  AMC.AX    AMP.AX  

ABC.AX       NaN       NaN       NaN       NaN  0.860923     NaN  
AGL.AX       NaN       NaN       NaN       NaN       NaN     NaN        
AHY.AX       NaN       NaN       NaN       NaN       NaN     NaN       
ALL.AX       NaN  0.860923       NaN       NaN       NaN     NaN

Expect result:

AGL.AX ALL.AX
AMC.AX ABC.AX

Is your output supposed to be an nx2 dataframe, a MultiIndex, a list-of-lists, or what? — smci
– smci, Commented Dec 11, 2018 at 23:40

smci · Accepted Answer · 2018-12-11 23:39:08Z

3

Use stack to reshape/pivot the dataframe, and transform the index to a multiindex:

df_corr[(df_corr>0.7)&(df_corr<1)].stack()
Out[79]: 
A2M.AX        
ABC.AX  AMC.AX    0.860923
ALL.AX  AGL.AX    0.860923
dtype: float64

df_corr[(df_corr>0.7)&(df_corr<1)].stack().index.values
Out[80]: array([('ABC.AX', 'AMC.AX'), ('ALL.AX', 'AGL.AX')], dtype=object)

edited Dec 11, 2018 at 23:39

smci

34.2k21 gold badges118 silver badges152 bronze badges

answered Dec 11, 2018 at 23:10

BENY

324k22 gold badges176 silver badges250 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

smci Over a year ago

Please explain this better; stack() reshapes/pivots the dataframe, and transforms the index to a multiindex e.g. ('ABC.AX', 'AMC.AX')

jpp · Accepted Answer · 2018-12-11 23:28:23Z

1

Here's one way using NumPy indexing, which avoids having to subset your dataframe.

import numpy as np

condition = df.gt(0.7) & df.lt(1)

x, y = map(list, zip(*np.where(condition.values)))

res = list(zip(df.index[x], df.columns[y]))

[('ABC.AX', 'AMC.AX'), ('ALL.AX', 'AGL.AX')]

edited Dec 11, 2018 at 23:28

answered Dec 11, 2018 at 23:16

jpp

166k37 gold badges301 silver badges363 bronze badges

Collectives™ on Stack Overflow

Get col names and index based on dataframe value

2 Answers 2

1 Comment

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

1 Comment

Comments

Your Answer

Sign up or log in

Post as a guest

Related