Fill Values From One Dataframe To Another With Matching Ids

I have two pandas data frames, I want to get the sum of items_bought for each ID in DF1. Then add a column to DF2 containing the sum of items_bought calculated from DF1 with matchi

Solution 1:

df1.groupby('ID').sum().loc[df2.ID].fillna(0).astype(int)
Out[104]: 
    items_bought
ID              
1              5
2              4
8              0
3             13
2              4

1. Work on df1 to calculate the sum for each ID.
2. The resulting dataframe is now indexed by ID, so you can select with df2 IDs by calling loc.
3. Fill the gaps with fillna.
4. NA are handled by float type. Now that they are removed, convert the column back to integer.

Solution 2:

Solution with groupby and sum, then reindex with fill_value=0 and last reset_index:

df2 = df1.groupby('ID').items_bought.sum().reindex(df2.ID, fill_value=0).reset_index()
print (df2)
   ID  items_bought
0   1             5
1   2             4
2   8             0
3   3            13
4   2             4