Getting the minimum and maximum after using group by

Question

My data frame contains columns named: City, Product line, Quantity and several others which are not important in my problem.

I would like to find which Products lines have the minimal and maximal average quantity in each city.

I used the function groupby. That's my code:

import pandas as pd
dataset = pd.read_csv('supermarket_sales.csv')
stats_product_line_by_cities = dataset.groupby(['City', 'Product line'])['Quantity'].mean()

The output looks like this

City       Product line          
Mandalay   Electronic accessories    5.745455
           Fashion accessories       4.790323
           Food and beverages        5.400000
           Health and beauty         6.037736
           Home and lifestyle        5.900000
           Sports and travel         5.193548
Naypyitaw  Electronic accessories    6.054545
           Fashion accessories       5.261538
           Food and beverages        5.590909
           Health and beauty         5.326923
           Home and lifestyle        5.444444
           Sports and travel         5.888889
Yangon     Electronic accessories    5.366667
           Fashion accessories       5.156863
           Food and beverages        5.396552
           Health and beauty         5.468085
           Home and lifestyle        5.707692
           Sports and travel         5.644068
Name: Quantity, dtype: float64

That's kind of a fortunate output. Now I would like to pull only those lines with minimum and maximum for each city. How can I do that?

Does this answer your question? [get first and last values in a groupby](https://stackoverflow.com/questions/38797271/get-first-and-last-values-in-a-groupby) — AMC, Mar 13 '20 at 00:37

score 1 · Accepted Answer · answered Mar 12 '20 at 22:19

1

Let us do sort_values, the groupby head + tail

g=stats_product_line_by_cities.sort_values().groupby(level=0)
out=pd.concat([g.head(1),g.tail(1)])

answered Mar 12 '20 at 22:19

BENY

317,841
20
164
234

Thank you. It worked. Can you explain a bit how *groupby(level=0)* works? I tried to find it in the documentation however with no results. – Hendrra Mar 13 '20 at 11:30

Getting the minimum and maximum after using group by

1 Answers1