count nunique from another dataframe

user_14962606 0
Viewed 77

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
pandas counter dataframe for-loop python
3 Answers

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
user_128331660

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
user_58208140

try:

x = dfa.merge(dfb, on='Agreement', how='right')
y = x.groupby('Agreement').size().reset_index(name='Count')
dfa = dfa.merge(y, on='Agreement')

In one line:

dfa.join(dfb['Agreement'].groupby(dfb['Agreement']).count().rename('Count'), on='Agreement')

dfa:

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

EDIT:

try filtering them using this function.

def grpdates(grp):
    stRow = grp.iloc[0]
    stRow1 = dfa[dfa['Agreement']==stRow.Agreement].Date_1
    grp = grp[np.array(grp.Date_2)<=np.array(stRow1 + pd.DateOffset(days=30))]
    return grp  

dfb['Date_2'] = pd.to_datetime(dfb.Date_2, format='%m/%d/%Y')
dfa['Date_1'] = pd.to_datetime(dfa.Date_1, format='%m/%d/%Y')

df = dfb.groupby('Agreement', group_keys=False).apply(grpdates)
dfa['Count'] = dfa['Agreement'].map(df['Agreement'].value_counts())

dfa:

    Agreement   Date_1      Count
0   146108493   2019-01-31  3.0
1   142527722   2019-01-09  NaN
user_66603730
Related