Follow

Keep Up to Date with the Most Important News

By pressing the Subscribe button, you confirm that you have read and are agreeing to our Privacy Policy and Terms of Use
Contact

Extracting values of a pandas DataFrame using maximal indexes from another DataFrame

I have two pandas dataframes:

df1 = pd.DataFrame({
 'a': [1, 2, 3],
 'b': [2, 4, 2],
 'c': [3, 1, 5],
 'd': [-1, 0, 1]
}, index=('A', 'B', 'C'))
df2 = pd.DataFrame({
 'a': [0, 5, 10],
 'b': [9, 5, 1],
 'c': [3, 4, 2],
 'd': [12, 3, 0]
}, index=('A', 'B', 'C'))

>>> print(df1)
   a  b  c  d
A  1  2  3 -1
B  2  4  1  0
C  3  2  5  1
>>> print(df2)
    a  b  c   d
A   0  9  3  12
B   5  5  4   3
C  10  1  2   0

I would like to get the value from each column of df1 that corresponds to (i.e. is at the same coordinates as) the maximal value of that same column in df2. So in the above example it should return the values [3, 2, 1, -1]. I was able to get the correct indices using idxmax:

>>> print(df2.idxmax())
a    C
b    A
c    B
d    A
dtype: object

As you can see, these are indeed the indices corresponding to the column-wise maximums in df2. However trying to index into df1 using these indices does not return the desired result:

MEDevel.com: Open-source for Healthcare and Education

Collecting and validating open-source software for healthcare, education, enterprise, development, medical imaging, medical records, and digital pathology.

Visit Medevel

>>> print(df1.loc[df2.idxmax()])
   a  b  c  d
C  3  2  5  1
A  1  2  3 -1
B  2  4  1  0
A  1  2  3 -1

This indexing seems to only use the values of the Series returned by idxmax. How do I correctly index df1 using both parts (labels and values) of the returned Series? Or is there maybe a simpler solution to achieve what I want?

>Solution :

Try this:

s = df2.idxmax()
[df1.loc[row, col] for col, row in s.items()]
Add a comment

Leave a Reply

Keep Up to Date with the Most Important News

By pressing the Subscribe button, you confirm that you have read and are agreeing to our Privacy Policy and Terms of Use

Discover more from Dev solutions

Subscribe now to keep reading and get access to the full archive.

Continue reading