Pandas dataframe with multiple hierarchical indices

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?

Topic dataframe pandas indexing

Category Data Science


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)

About

Geeks Mental is a community that publishes articles and tutorials about Web, Android, Data Science, new techniques and Linux security.