0

The goal is to identify the count of colors in each groupby().

If you see the outcome below shows the first group color blue appears 3 times.

The second group yellow appears 2 times and third group blue appears 5 times

So far this is what I got.

df.groupby(['X','Y','Color'})..Color.value_counts()

but this produces only the count of 1 since color for each row appears once.

The final output should be like this:

Thanks in advance for any assistance.

accdias
  • 5,160
  • 3
  • 19
  • 31
Krish Kk
  • 21
  • 6
  • 1
    Please, avoid [posting images of text](https://unix.meta.stackexchange.com/questions/4086/psa-please-dont-post-images-of-text). It is a better practice to transcribe them instead. Also, it is recommended for you to read [How to make good reproducible pandas examples](https://stackoverflow.com/questions/20109391/how-to-make-good-reproducible-pandas-examples). – accdias Apr 28 '21 at 12:33
  • Do you want only the count of each color in the dataframe? – Rafael Dourado D Apr 28 '21 at 12:40
  • No. I want to count colors for each group. – Krish Kk Apr 28 '21 at 12:44
  • 1
    How about generating the latter and just merging it to existing df? (pd.merge(df, df_freq, how='left', on = 'x','y', 'color') – pinegulf Apr 28 '21 at 12:50
  • This is great idea as well but its a two step approach. – Krish Kk Apr 28 '21 at 14:34

1 Answers1

1

If you give size to the transform function, it will be in tabular form without aggregation.

df['Count'] = df.groupby(['X','Y']).Color.transform('size') 
df.set_index(['X','Y'], inplace=True)
df

        Color   Count
X   Y       
A   B   Blue    3
    B   Blue    3
    B   Blue    3
C   D   Yellow  2
    D   Yellow  2
E   F   Blue    5
    F   Blue    5
    F   Blue    5
    F   Blue    5
    F   Blue    5
r-beginners
  • 31,170
  • 3
  • 14
  • 32