Dataframe transformation in Pandas

Question

I am trying to get frequency counts of certain columns in a dataframe in python. I have a dataframe that looks like this

Animal	Body Part 1	Body Part 2	Body Part 3
Monkey	Tail	Head	Legs
Elephant	Head	Tail	Trunk
Monkey	Ears	Head	Legs
Elephant	Eyes	Tail	Legs

The output I am looking for is to get the count of each body part for the corresponding animals (shown below).The values of the different body parts become the rows and the unique animals become the columns, with each cell denoting the count of occurrence of the body part in that animal. It is a form of a pivot table but not sure what is the right method to apply here in python.


      | Monkey| Elephant
-------------------------
Tail  | 1     | 2
Head  | 2     | 1
Legs  | 2     | 1
Ears  | 1     | 0
Trunk | 0     | 1

Quang Hoang · Accepted Answer · 2021-02-11 05:15:43Z

3

One way is to melt the data, then groupby().value_counts()

(df.melt('Animal')
   .groupby('Animal')
   ['value'].value_counts()
   .unstack('Animal', fill_value=0)
)

Output:

Animal  Elephant   Monkey 
value                     
Ears            0        1
Eyes            1        0
Head            1        2
Legs            1        2
Tail            2        1
Trunk           1        0

Option 2: Similar to option 1 with set_index().stack() instead of melt:

(df.set_index('Animal')
   .stack().groupby(level=0)
   .value_counts()
   .unstack(level=0, fill_value=0)
)

Option 3: similar to option 1 but with pd.crosstab:

tmp = df.melt('Animal')
out = pd.crosstab(tmp['value'], tmp['Animal'])

Option 4: apply Series.value_counts on the rows:

(df.set_index('Animal')
   .apply(pd.Series.value_counts, axis=1)
   .sum(level=0).T
)

edited Feb 11, 2021 at 5:15

answered Feb 11, 2021 at 5:06

Quang Hoang

151k11 gold badges64 silver badges86 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

Dataframe transformation in Pandas

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related