I have two dataframes that I need to merge. The first table contains multiple rows of the same ID, but they each have their own time stamp. The second table also has those same ID's, but they each also have their own timestamps.

FIRST TABLE:

SECOND TABLE:

The goal is to perform a left join on the ID were the Num Sold before the date on the first table is summed and added as a column. So ID "1" for Date "2/1/2021" from the first table would take the sum of all the ID "1" Num Sold on the second table that happened on or before "2/1/2021". So the output for this would be 4. For ID "1" for Date "5/1/2021" on the first table would take the sum of all the ID "1" Num Sold on the second table that happened on or before "2/1/2021". The output of this would be 54.

The output dataframe would look like this.

CodePudding user response：

def get_sold(id,date):
    return df2[(df2['ID']==id) & (df2['Date'].le(date))]['Num Sold'].sum()

for i,v in df1.iterrows():
    df1.loc[i,'Total Sold'] = get_sold(v.ID,v.Date)