Pandas get all rows of min and max values after groupby

Question

I have a dataframe like this:

df = pd.DataFrame({'A' : list('ababababba'),
                   'B' : [1, 1, 1, 2, 2, 1,1,2,1,1],
                   'C' : [2.0, 5., 8., 1., 2., 9.,2.0,4.0,5.0,3.0],
                   'D' : [10,20,30,10,20,30,20,40,50,10]})

Required:

   A  B    C   D
0  a  1  2.0  10 # a1 min keep
1  b  1  5.0  20 # b1 min
2  a  1  8.0  30 # a1 max keep
3  b  2  1.0  10 
4  a  2  2.0  20
                  # b1 removed
                  # a1 remove
7  b  2  4.0  40
8  b  1  5.0  50 # b1 max keep
9  a  1  3.0  10 # a1 min keep

Related links: Min and max row from pandas groupby

Max and min from two series in pandas groupby

Max and Min date in pandas groupby

pandas groupby and then select a row by value of column (min,max, for example)

Quang Hoang · Accepted Answer · 2019-04-21T19:28:37.480

4

Do you want this:

df.groupby(['A','B']).D.agg([min,max])

Output:

+---+---+-----+-----+
|   |   | min | max |
+---+---+-----+-----+
| A | B |     |     |
+---+---+-----+-----+
| a | 1 |  10 |  30 |
|   | 2 |  20 |  20 |
| b | 1 |  20 |  50 |
|   | 2 |  10 |  40 |
+---+---+-----+-----+

Edit: If you want all rows with either min or max, then consider transform

groups = df.groupby(['A','B']).D
min_val = groups.transform(min)
max_val = groups.transform(max)

df[(df.D==min_val) | (df.D==max_val)]

Output:

+---+---+---+-----+----+
|   | A | B |  C  | D  |
+---+---+---+-----+----+
| 0 | a | 1 | 2.0 | 10 |
| 1 | b | 1 | 5.0 | 20 |
| 2 | a | 1 | 8.0 | 30 |
| 3 | b | 2 | 1.0 | 10 |
| 4 | a | 2 | 2.0 | 20 |
| 7 | b | 2 | 4.0 | 40 |
| 8 | b | 1 | 5.0 | 50 |
+---+---+---+-----+----+

edited Apr 21 '19 at 19:28

answered Apr 21 '19 at 19:20

Quang Hoang

146,074
10
56
74

I want all rows with min and max and also C. – BhishanPoudel Apr 21 '19 at 19:24
Yup, it works with lots of code, but great. One aside, how do you get the output in nice tabular form? That looks beautiful, my jupyternotebk simply gives a table. – BhishanPoudel Apr 21 '19 at 19:31
@astro123 I use this [website](https://senseful.github.io/text-table/) to format the table. – Quang Hoang Apr 21 '19 at 20:55
Thanks, I was thinking you have some kind of python module, or jupyter-setting or Alfred Workflow like that. – BhishanPoudel Apr 21 '19 at 20:57
1

NVM. I got it. `from tabulate import tabulate print(tabulate(df, headers=df.columns, tablefmt='psql'))` – BhishanPoudel Apr 21 '19 at 21:05

Pandas get all rows of min and max values after groupby

1 Answers1