Pandas: Finding max min rows for every column in the dataframe

user_3679377 0
Viewed 56

I am trying to find the max min rows of every column in a dataframe. I don't even know where to start. I think df.groupby with agg wont work because I need the whole row.

Here's a sample data

import pandas as pd
df = pd.DataFrame(
{'A': array([4, 9, 2, 3, 3, 5, 7, 0, 4, 6]),
 'B': array([4, 2, 4, 8, 4, 3, 1, 6, 9, 2]),
 'C': array([8, 1, 8, 1, 2, 2, 7, 5, 9, 8]),
 'D': array([9, 4, 2, 8, 0, 3, 6, 9, 3, 8])}
)

Expected outout

        A B C D
1 A max 9 2 1 4
7 A min 0 6 5 9
8 B max 4 9 9 3
6 B min 7 1 7 6
8 C max 4 9 9 3
1 C min 9 2 1 4
0 D max 4 4 8 9
4 D min 3 4 2 0

if there are multiple rows with same min/max value, it's okey if it returns any of those.

PS: I'd like it to retain the original index.

pandas dataframe python
1 Answers

Let's try agg on idxmin, idxmax, then merge:

out=(df.agg(['idxmin','idxmax']).unstack().reset_index(name='idx')
       .merge(df, left_on='idx', right_index=True, how='left')
    )

Output (idx is the original index):

  level_0 level_1  idx  A  B  C  D
0       A  idxmin    7  0  6  5  9
1       A  idxmax    1  9  2  1  4
2       B  idxmin    6  7  1  7  6
3       B  idxmax    8  4  9  9  3
4       C  idxmin    1  9  2  1  4
5       C  idxmax    8  4  9  9  3
6       D  idxmin    4  3  4  2  0
7       D  idxmax    0  4  4  8  9
user_42384080
Related