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?
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
Get help from others!
Recent Questions
Recent Answers
© 2024 TransWikia.com. All rights reserved. Sites we Love: PCI Database, UKBizDB, Menu Kuliner, Sharing RPP