How to count unique combinations of variable in a Pandas Dataframe

Question

I'm using pandas to count unique combinations of sets of variables in a dataframe. I'm currently using the .groupby() function, but I think I'm missing part of it's functionality.

Example code:

import pandas
df = pd.DataFrame([['A','C','G'],
                   ['A','C','H'],
                   ['A','D','G'],
                   ['A','D','H'],
                   ['B','E','I'],
                   ['B','F','I']], columns=['a','b','c'])
df

   a  b  c
0  A  C  G
1  A  C  H
2  A  D  G
3  A  D  H
4  B  E  I
5  B  F  I

Say I want to know, for every unique value a, how many different b's does it have? In this example, the desired output is A: 2, B:2 because A has two unique b values and B has two unique b values.

If I were counting the unique c's per a, I would expect A: 2, B: 1.

My current code is:

df.groupby(['a','b'],as_index=False).count().groupby(['a'], as_index=False).count()[['a','b']]

   a  b
0  A  2
1  B  2

df.groupby(['a','c'], as_index=False).count().groupby(['a'],as_index=False).count()[['a','c']]

   a  c
0  A  2
1  B  1

This gives me the correct result, but I think there should be a way to avoid two sets of groupby() and count(), no?

user2285236 · Accepted Answer · 2016-06-28 21:18:47Z

7

How about nunique?

df.groupby('a')['b'].nunique()
Out[36]: 
a
A    2
B    2
Name: b, dtype: int64

answered Jun 28, 2016 at 21:18

user2285236

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

1 Comment

Dan Burkhardt Over a year ago

Great suggestion, this is exactly what I was looking for!

Collectives™ on Stack Overflow

How to count unique combinations of variable in a Pandas Dataframe

1 Answer 1

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related