Python Pandas calculate value_counts of two columns and use groupby

Question

I have a dataframe :

data = {'label': ['cat','dog','dog','cat','cat'],
      'breeds': [ 'bengal','shar pei','pug','maine coon','maine coon'],
      'nicknames':[['Loki','Loki' ],['Max'],['Toby','Zeus ','Toby'],['Marty'],['Erin ','Erin']],
       'eye color':[['blue','green'],['green'],['brown','brown','brown'],['blue'],['green','brown']]

Output:

label    breeds    nicknames            eye color
0   cat  bengal     [Loki,Loki]      [blue, green]
1   dog  shar pei   [Max]            [green]
2   dog  pug        [Toby,Zeus,Toby] [brown, brown, brown]
3   cat  maine coon [Marty]          [blue]
4   cat  maine coon [Erin,Erin]      [green, brown]

I want to apply the groupby :frame['label', 'breeds'], and calculate value_counts(unique value ) of nicknames and eye color,but output them in different columns: 'nickname_count','eye_count' This code outputs only in one column, how do I output separately?

 frame2=frame.groupby(['label','breeds'])['nicknames','eye color'].apply(lambda x: x.astype('str').value_counts().to_dict())

tlentali · Accepted Answer · 2021-10-27 21:47:55Z

1

First, we use a groupby with sum on the lists as sum concatenates the lists together :

>>> df_grouped = df.groupby(['label', 'breeds']).agg({'nicknames': sum, 'eye color': sum}).reset_index()
>>> df_grouped
    label   breeds      nicknames               eye color
0   cat     bengal      [Loki, Loki]            [blue, green]
1   cat     maine coon  [Marty, Erin , Erin]    [blue, green, brown]
2   dog     pug         [Toby, Zeus , Toby]     [brown, brown, brown]
3   dog     shar pei    [Max]                   [green]

Then, we can count the number of unique values in list by converting it to set, using len and save the output in two new columns to get the expected result :

>>> df_grouped['nickname_count'] = df_grouped['nicknames'].apply(lambda x: list(set(x))).str.len()
>>> df_grouped['eye_count'] = df_grouped['eye color'].apply(lambda x: list(set(x))).str.len()
>>> df_grouped
    label   breeds      nicknames               eye color               nickname_count  eye_count
0   cat     bengal      [Loki, Loki]            [blue, green]           1               2
1   cat     maine coon  [Marty, Erin , Erin]    [blue, green, brown]    3               3
2   dog     pug         [Toby, Zeus , Toby]     [brown, brown, brown]   2               1
3   dog     shar pei    [Max]                   [green]                 1               1

edited Oct 27, 2021 at 21:47

answered Oct 27, 2021 at 21:20

tlentali

3,4632 gold badges18 silver badges23 bronze badges

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

6 Comments

Rory Over a year ago

But I want to group by label and breeds and then count

tlentali Over a year ago

Indeed, I updated the answer with the group by and then count the number of elements in the list. Does it answer your question ?

Rory Over a year ago

Thanks for the answer, but that's not exactly what I want..I would like more like 'word':count, so that why I want to use value_counts

tlentali Over a year ago

Do you want to count the unique value ?

Rory Over a year ago

Yes ! Like eye_count :('blue':2)....and then groupby

|

Collectives™ on Stack Overflow

Python Pandas calculate value_counts of two columns and use groupby

1 Answer 1

6 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

6 Comments

Your Answer

Sign up or log in

Post as a guest

Related