How to count unique combinations of rows in dataframe group by?

Question

I would like to use pandas groupby to count the occurrences of a combination of animals on each farm (denoted by the farm_id). I am trying to count the number of farms with each type of animal combination.

The desired output would be something like this:

Out[6]: 
                 combo  count
0                  cow      1
1       [cow, chicken]      1
2  [cow, pig, chicken]      2

For the following dataframe:

df = pd.DataFrame([['cow',0],['chicken',0],
                   ['cow',1],
                   ['chicken',3],['pig',3],['cow',3],
                   ['pig',4],['cow',4],['chicken',4]]
                   ,columns=['animals','farm_id'])

df
Out[4]: 
   animals  farm_id
0      cow        0
1  chicken        0
2      cow        1
3  chicken        3
4      pig        3
5      cow        3
6      pig        4
7      cow        4
8  chicken        4

Notice the order the animals appear does not matter.

I have tried this:

df.groupby('farm_id').agg({'animals':'unique'})
Out[7]: 
                     animals
farm_id                     
0             [cow, chicken]
1                      [cow]
3        [chicken, pig, cow]
4        [pig, cow, chicken]

Which gives me the combinations, but (1) the ordering is taken into account and (2) I'm not sure how to generate the count as a separate column.

Nev1111 · Accepted Answer · 2020-06-20 15:10:43Z

1

Try:

import pandas as pd
from collections import Counter

df_1=df.groupby('farm_id')['animals'].unique().apply(list).apply(lambda x: sorted(x)).reset_index()

Count the nummber of occurences

dict=Counter([tuple(i) for i in df_1['animals']])

counter_df=pd.DataFrame.from_dict(dict, orient='index').reset_index()
counter_df.columns=['combo','count']

edited Jun 20, 2020 at 15:10

answered Jun 20, 2020 at 14:27

Nev1111

1,0499 silver badges13 bronze badges

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

3 Comments

Benjamin Latimer Over a year ago

Nice! Is there a way to convert the dictionary to a column? It would be more convenient if the count was a column in the dataframe.

Scott Skiles Over a year ago

@BenjaminLatimer - Check my update - I was able to riff on Nev's answer and mine to get the count without the use of Counter. Cheers

Nev1111 Over a year ago

@BenjaminLatimer - See revised answer.

Scott Skiles · Accepted Answer · 2020-06-20 20:36:12Z

0

df = df.groupby('farm_id')['animals'].unique().apply(lambda x: tuple(sorted(x))).reset_index().rename(columns={'farm_id':'count'})
print(df.groupby('animals').count())

The key to this solution is making the list of animals hashable by using a tuple and then sorting that tuple so that we can count the number of combo occurrences.

edited Jun 20, 2020 at 20:36

answered Jun 20, 2020 at 14:33

Scott Skiles

3,8867 gold badges45 silver badges74 bronze badges

Comments

Christian Eslabon · Accepted Answer · 2020-06-20 14:42:56Z

0

import pandas as pd
df = pd.DataFrame([['cow',0],['chicken',0],
               ['cow',1],
               ['chicken',3],['pig',3],['cow',3],
               ['pig',4],['cow',4],['chicken',4]]
               ,columns=['animals','farm_id'])
df  = df.sort_values(['animals','farm_id'])
df = df.groupby('farm_id').agg({'animals':'unique'})
df['animals'] = df['animals'].astype(str)
df2 = pd.DataFrame(df.animals.value_counts())
df = pd.merge(df, df2, left_on = 'animals', right_index = True,how = 'left')
df.columns = ['animal_combination','count']
df

answered Jun 20, 2020 at 14:42

Christian Eslabon

1854 silver badges10 bronze badges

Collectives™ on Stack Overflow

How to count unique combinations of rows in dataframe group by?

3 Answers 3

3 Comments

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

3 Comments

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related