Add a count unique combinations across rows in pandas

user_181971 0
Viewed 136

I am trying to find the unique combinations of the rows and introduce a count column as an int. The idea is to find the total number of unique combinations for the data below (ideally in a new dataframe)

id cat_1 cat_2 cat_3 cat_4
001 Chips Null Null Null
789 Chips Avocado Null Null
002 Chips Pasta Null Null
323 Chips Pasta Null Null
123 Chips Pasta Cheese Null
456 Chips Sauce Cheese Null
101 Pasta Null Null Null
231 Pasta Null Null Null
321 Pasta Bread Null Null
212 Pasta Bread Null Null
632 Pasta Cheese Null Null

I'm imagining the data to look something like this:

id cat_1 cat_2 cat_3 cat_4 count
0 Chips Null Null Null 1
1 Chips Pasta Null Null 2
2 Chips Pasta Cheese Null 1
4 Chips Sauce Cheese Null 1
5 Chips Avocado Null Null 1
6 Pasta Null Null Null 2
7 Pasta Bread Null Null 2
8 Pasta Cheese Null Null 1

I thought I could use something like below however my data actually has up to seven cat_7 and wasn't sure if this was the right way

df1.groupby(['cat_1','cat_2', 'cat_3', 'cat_4']).size().reset_index().rename(columns={0:'count'})

How can I get it into the format?

pandas combinations dataframe
2 Answers

I think yes, you can use:

cols = df.columns.difference(['id']).tolist()
#should working like
#cols = ['cat_1','cat_2', 'cat_3', 'cat_4', 'cat_5', 'cat_6', 'cat_7']
df = df.groupby(cols, sort=False).size().reset_index(name='count')
print (df)
   cat_1    cat_2   cat_3 cat_4  count
0  Chips     Null    Null  Null      1
1  Chips  Avocado    Null  Null      1
2  Chips    Pasta    Null  Null      2
3  Chips    Pasta  Cheese  Null      1
4  Chips    Sauce  Cheese  Null      1
5  Pasta     Null    Null  Null      2
6  Pasta    Bread    Null  Null      2
7  Pasta   Cheese    Null  Null      1
user_29010020

DataFrame.value_counts

df.drop('id', axis=1).value_counts(dropna=False).reset_index(name='count')

   cat_1    cat_2   cat_3 cat_4  count
0  Chips    Pasta    Null  Null      2
1  Pasta    Bread    Null  Null      2
2  Pasta     Null    Null  Null      2
3  Chips  Avocado    Null  Null      1
4  Chips     Null    Null  Null      1
5  Chips    Pasta  Cheese  Null      1
6  Chips    Sauce  Cheese  Null      1
7  Pasta   Cheese    Null  Null      1
user_128331660
Related