I have a pandas dataframe like this:

    c1  c2  
0   A   red
1   B   blue
2   B   blue
3   C   red
4   C   red
5   C   blue
6   D   blue

All I want to do is find out how many red/blue values there are per all values in c1. Something like this:

    red  blue
A   1    0
B   0    2  
C   2    1
D   0    1

I tried using masks and groupby() but failed coming up with a solution. The main reason is that I am not allowed to use loops. Feels like there is an obvious solution but I'm not that good at using pandas :/ Any advice?

CodePudding user response：

Alternatively, groupby with the size of the groups...

df.groupby(['c1','c2']).size()

Output:

c1  c2  
A   red     1
B   blue    2
C   blue    1
    red     2
D   blue    1
dtype: int64

It's not exactly as you wanted, but gives you the important information. You said "Something like this"...

CodePudding user response：

Simple groupby with value_counts.

df.groupby('c1')['c2'].value_counts().unstack(fill_value=0)