TransWikia.com

Pandas dataframe with multiple hierarchical indices

Data Science Asked on May 2, 2021

I have a data frame which looks like this

FRUIT       ID      COLOR       WEIGHT
Apple       142     Red         Heavy
Mango       231     Red         Light
Apple       764     Green       Light
Apple       543     Green       Heavy

And I want the following result:

FRUIT                 COUNT
Apple       COLOR
            Red         1
            Green       2

            WEIGHT
            Heavy       2
            Light       1

Mango       COLOR
            Red         1
            Green       0
            
            WEIGHT
            Heavy       0
            Light       1   

I tried different variations of set_index, groupby() and unstack() on the dataframe in combination with ['ID]'.count() and .size(), but my grouping works only on the first level (Fruit).
On the second level I am not able to get color and weight separately, I always end up with weight as another subindex of color so it looks like

FRUIT                 
Apple       COLOR    WEIGHT   COUNT
            Red      Heavy    1
                     Light    0

            Green    Heavy    1
                     Light    1 

How can I get my desired result?

One Answer

I got it working with below simple line of code

df.set_index('fruit').stack().groupby(level=[0,1]).value_counts().unstack(level=[1,2]).fillna(0)

Correct answer by Phil on May 2, 2021

Add your own answers!

Ask a Question

Get help from others!

© 2024 TransWikia.com. All rights reserved. Sites we Love: PCI Database, UKBizDB, Menu Kuliner, Sharing RPP