Matching multiple conditions and mapping multiple results, between two dataframes

[klllmmm]

Hi deveyge33

Good day!. Hope the below answer helps.

import pandas as pd
import datetime as dt

# Load the datasets & convert text into datatime format
df1 = pd.DataFrame(data={"fruit":['apple','pear','banana','peach'],
                          'id':[2,1,3,4],
                          'date':['01/10/20','15/09/20','01/06/20','10/04/20']})

df1['date'] = df1['date'].apply(lambda x: 
                                    dt.datetime.strptime(x,'%d/%m/%y'))


df2 = pd.DataFrame(data={"name":['paul','tracy','iain','frida','david','peter','adam','eve','hannah','janine','charlotte'],
                          'uid':[2,1,3,4,2,3,4,1,2,2,5],
                          'ndate':['02/11/20','15/12/20','01/05/20','23/02/20','06/06/20','19/11/20','07/03/20','30/11/20','25/09/20','13/08/20','10/04/20']})

df2['ndate'] = df2['ndate'].apply(lambda x: 
                                    dt.datetime.strptime(x,'%d/%m/%y'))

then,

# Mearge the two dataframes using "ID" and "UID" columns
df_temp = pd.merge(df1,df2,left_on=['id'], right_on=['uid'],how='left',indicator='True')

# Locate only matches with date condition
df_temp1 = df_temp.loc[ (df_temp['ndate']<=df_temp['date'])].copy()

# Add a sequance for multiple matches
df_temp1['Sequance'] = df_temp1.groupby(['fruit',"uid"]).cumcount()+1

# Delete the "ndate" columns
del df_temp1['ndate']

# Unstack the name column
df_temp2 = df_temp1.set_index(['fruit', 'id', 'date','Sequance'])['name'].unstack().reset_index()

# Merge again to get df1 items that doesn't have matching items in df2
df_result = pd.merge(df1,df_temp2,left_on=['id','date','fruit'], right_on=['id','date','fruit'],how='left')

print(df_result)
del df_temp1, df_temp2

Output:
print(df_result)
    fruit  id       date      1       2       3
0   apple   2 2020-10-01  david  hannah  janine
1    pear   1 2020-09-15    NaN     NaN     NaN
2  banana   3 2020-06-01   iain     NaN     NaN
3   peach   4 2020-04-10  frida    adam     NaN