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?