Select the first row from each group after groupby (Multiindex)

user_15410844 0
Viewed 265

I am doing data analysis, and conducted the groupby to get the 'count' and 'sum' by 'year' and 'product' (already sort by count in each year)

The df is like:

count sum
year product
2015 product A 9 23
product 5 46
2016 product A 7 17
product B 7 42
product C 3 78
2017 product B 9 32
product C 8 24
product A 1 72
2018 product A 3 33
product B 1 82

The 'year' and 'product' are the indexes, and 'count' and 'sum' are the agg columns.

I would like to retrieve the Max count (the first row) from each year.

The result would be like

count sum
year product
2015 product A 9 23
2016 product A 7 17
2017 product B 9 32
2018 product A 3 33

How can I deal with it?

Thanks

multi-index pandas group-by
1 Answers

To select the first row from the each year, you can do:

print(
    df.reset_index(level="product")
    .groupby(level="year")
    .first()
    .set_index(["product"], append=True)
)

Prints:

                count  sum
year product              
2015 product A      9   23
2016 product A      7   17
2017 product B      9   32
2018 product A      3   33
user_100359850
Related