Pandas: Count Distinct Combinations of two columns and add to Same Dataframe

Question

Need help in adding the unique combination of two columns to the same dataframe in pandas. I want that "nos" column.

Input:
id  acct_nos name
1   1a       one
1   1a       two
2   2b       three
3   3a       four
3   3b       five
3   3c       six
3   3d       seven

Here is the output I want:

Output:
id  acct_nos    nos name
1   1a          1   one 
1   1a          1   two
2   2b          1   three
3   3a          4   four
3   3b          4   five
3   3c          4   six
3   3d          4   seven

In the above example Id=1 has only 1 acct_nos-1a so the nos has to have a value 1. Id=3 has only 4 acct_nos-3a to 3d so the nos has to have a value 4.

Not sure how to put this in Python Pandas. SQL queries I can figure out.

Thanks

Answer 1

You can use groupby.transform with nunique() function to count the number of unique elements per id:

df['nos'] = df.groupby("id")['acct_nos'].transform("nunique")
df