2
$\begingroup$

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?

$\endgroup$
2
$\begingroup$

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)
$\endgroup$

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.