CODEWITHSUNDEEP
pythonpandasgroup-by
Gwen Yang
Gwen Yang

Reputation: 146

count unique values in groups pandas

I have a dataframe like this:

data = {'id': [1,1,1,2,2,3],
        'value': ['a','a','a','b','b','c'],
        'obj_id': [1,2,3,3,3,4]
}
df = pd.DataFrame (data, columns = ['id','value','obj_id'])

I would like to get the unique counts of obj_id groupby id and value:

1 a 3
2 b 1
3 c 1

But when I do:

result=df.groupby(['id','value'])['obj_id'].nunique().reset_index(name='obj_counts')

the result I got was:

1 a 2
1 a 1
2 b 1
3 c 1

so the first two rows with same id and value don't group together.

How can I fix this? Many thanks!

Upvotes: 0

Views: 45

Answers (1)

jezrael
jezrael

Reputation: 862641

For me your solution working nice with sample data.

Like mentioned @YOBEN_S in comments is possible problem traling whitespeces, then solution is add Series.str.strip:

data = {'id': [1,1,1,2,2,3],
        'value': ['a ','a','a','b','b','c'],
        'obj_id': [1,2,3,3,3,4]
}
df = pd.DataFrame (data, columns = ['id','value','obj_id'])

df['value'] = df['value'].str.strip()
df = df.groupby(['id','value'])['obj_id'].nunique().reset_index(name='obj_counts')
print (df)
   id value  obj_counts
0   1     a           3
1   2     b           1
2   3     c           1

Upvotes: 1

Related Questions