How to get a count of different values in one column in pandas-CodePudding

I have following dataframe in pandas

account_no     store_no    chain
12             1           Yes
12             2           Yes
13             10          No
13             21          No
13             32          No
14             11          No
15             12          Yes

I want to count unique values of Yes & No in chain column w.r.t unique account_no. account_no can have duplicates since it has one to many relationship with store_no. Following is my expected output

Yes : 2
No :  2

I am using following command in pandas, but it gives me overall unique value count

df.groupby(['account_no', 'chain']).ngroups

CodePudding user response：

Use DataFrame.drop_duplicates by columns account_no and chain and then count chain by Series.value_counts:

s = df.drop_duplicates(subset=['account_no','chain'])['chain'].value_counts()
print (s)
No     2
Yes    2
Name: chain, dtype: int64

CodePudding user response：

df.loc[df.store_no == 1, 'chain'].value_counts()