How to join two dataframes with different column sets in pandas

I have two pandas dataframes: df1 and df2.

data_df1 = {
    'seller': ['John', 'Alice', 'Bob', 'Alice'],
    'date': ['2023-07-25', '2023-07-26', '2023-07-27', '2023-07-28']
}

df1 = pd.DataFrame(data_df1)

data_df2 = {
    'seller': ['Alice Perry', 'John Russell', 'Bob Foster', 'Alice Mcgee'],
    'id': [101, 102, 103, 104],
    'date': ['2023-07-26', '2023-07-25', '2023-07-27', '2023-07-26']
}

df2 = pd.DataFrame(data_df2)

I want to perform a join between these dataframes based on the 'seller' and 'date' columns. It is worth mentioning that the seller column is not the same in both dataframes, one has only the name (df1) and the other has the full name (df2) and the date is not the same, so the merge must be by proximity, not exact dates.

How can I perform the join and get the desired result? Should I convert the 'date' columns to a common format before joining, and if so, how can I do that?

I appreciate any insights or examples on how to accomplish this join correctly. Thank you!

I already managed to make the conversion of dates for the join to work, but I don't know how to do this with the names

Standardize the date columns:

df1['date'] = pd.to_datetime(df1['date']).dt.date
df2['date'] = pd.to_datetime(df2['date']).dt.date

Extract only the first name for df2:

df2['first_name'] = df2['seller'].str.split(' ').str[0]

Merge using .merge():

result_df = df1.merge(df2, how='inner', left_on=['seller', 'date'], right_on=['first_name', 'date'])

Output:

> seller_x date seller_y id first_name
> 0 John 2023-07-25 John Russell 102 John
> 1 Alice 2023-07-26 Alice Perry 101 Alice
> 2 Alice 2023-07-26 Alice Mcgee 104 Alice
> 3 Bob 2023-07-27 Bob Foster 103 Bob

You can use:

df1['date'] = pd.to_datetime(df1['date'])
df2['date'] = pd.to_datetime(df2['date'])

df1.merge(df2, left_on=['seller', 'date'],
          right_on=[df2['seller'].str.extract(r'(\w+)', expand=False), df2['date'].dt.normalize()],
          suffixes=(None, '_'))

Output:

 seller       date       seller_   id               date_
0   John 2023-07-25  John Russell  102 2023-07-25 11:45:00
1  Alice 2023-07-26   Alice Perry  101 2023-07-26 12:30:00
2  Alice 2023-07-26   Alice Mcgee  104 2023-07-26 16:20:00
3    Bob 2023-07-27    Bob Foster  103 2023-07-27 08:15:00

If you only want to keep one of the keys:

out = df1.merge(df2, left_on=['seller', 'date'],
                right_on=[df2['seller'].str.extract(r'(\w+)', expand=False),
                          df2['date'].dt.normalize()],
               suffixes=(None, '_')
               ).drop(columns=['seller_', 'date_'])

Output:

  seller       date   id
0   John 2023-07-25  102
1  Alice 2023-07-26  101
2  Alice 2023-07-26  104
3    Bob 2023-07-27  103

Good Day,

There are few things to consider in your example.

1. Joins on the dates needs to be the same data type, so make them both dates instead of datetimes. df["date"] = df["date"].astype(date)
2. Join in the seller might be a bit more complicated. In df1 you have only the first name, but in df2 you have the full name. You can separate them into seller_first and seller_last using something like: df2["seller_first"] = df2["seller"].partition(" ")[0], but the real problem is in your data. You have two entries in df1 with Alice and two entries in df2 that begin with Alice. Your join will not know how to resolve this correctly since the Alice in df2 are different people.