Pandas: Count selected combinations of two columns and add to the same data file

Question

Need help adding a unique combination of two columns to the same data file in pandas. I need a 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 result 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 nos should have a value of 1. Id = 3 has only 4 acct_nos-3a to 3d, so nos should have a value of 4.

Not sure how to do this in Python pandas. SQL queries that I can figure out.

thank

Answer 1

groupby.transform nunique() id:

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

Answer 2

1

df.assign(nos=df.id.map(df.drop_duplicates(['id', 'acct_nos']).id.value_counts()))

2
Counter

from collections import Counter

tups = pd.unique(
    zip(df.id.values.tolist(), df.acct_nos.values.tolist())
).tolist()
df.assign(nos=df.id.map(Counter([tup[0] for tup in tups])))

   id acct_nos   name  nos
0   1       1a    one    1
1   1       1a    two    1
2   2       2b  three    1
3   3       3a   four    4
4   3       3b   five    4
5   3       3c    six    4
6   3       3d  seven    4