Python Pandas calculate value_counts of two columns and use groupby-CodePudding

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())

CodePudding user response：

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