count nunique from another dataframe

Question

My goal is to add a column which counts the number of unique instances of a primary key in dfB using the keys in dfA. dfA has the primary key only show up once while dfB would have the primary key multiple times.

Example.

dfA

Agreement	Date_1
146108493	1/31/2019
142527722	1/9/2019

dfB

Agreement	Date_2
146108493	2/4/2019
146108493	2/15/2019
146108493	2/20/2019
142527722	2/28/2019
142527722	3/15/2019

Goal Outcome- adjusted dfA

Agreement	Date_1	Count
146108493	1/31/2019	3
142527722	1/9/2019	2

Answer 1

You can group dataframe dfB on Agreement and aggregate using count then map the values to dfA based on primary key Agreement:

dfA['Count'] = dfA['Agreement'].map(dfB.groupby('Agreement')['Date_2'].count())

Or use value_counts with map as suggested by @Pygirl in comments:

dfA['Count'] = dfA['Agreement'].map(dfB['Agreement'].value_counts())

---

   Agreement     Date_1  Count
0  146108493  1/31/2019      3
1  142527722   1/9/2019      2

Answer 2

Use df.merge:

In [1161]: x = dfA.merge(dfB, indicator='Count').query('Count == "both"').groupby('Agreement').size().reset_index(name='Count')

In [1165]: dfA = dfA.merge(x)

In [1166]: dfA
Out[1166]: 
   Agreement     Date_1  Count
0  146108493  1/31/2019      3
1  142527722   1/9/2019      2