Pandas中两个DataFrame多条件匹配并生成多列匹配结果的实现方法

Hey there! Since you're new to Python and pandas, let's walk through this problem step by step to get the exact result you want.

Step 1: Convert Date Columns to Datetime Type #

First, we need to make sure our date columns are in a format pandas can compare. Right now they're strings, so we'll convert them to datetime objects:

import pandas as pd

# Your sample data (replace with your actual df1/df2 if needed)
df1 = pd.DataFrame({
    'fruit': ['apple', 'pear', 'banana', 'peach'],
    'id': [2, 1, 3, 4],
    'date': ['01/10/20', '15/09/20', '01/06/20', '10/04/20']
})

df2 = pd.DataFrame({
    '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']
})

# Convert date strings to datetime objects (format: day/month/year)
df1['date'] = pd.to_datetime(df1['date'], format='%d/%m/%y')
df2['ndate'] = pd.to_datetime(df2['ndate'], format='%d/%m/%y')

Step 2: Filter Matching Rows #

Next, we'll merge the two DataFrames on id (from df1) and uid (from df2), then keep only the rows where ndate is earlier than date:

# Merge df1 and df2, then filter for the date condition
merged = pd.merge(df1, df2, left_on='id', right_on='uid')
filtered_matches = merged[merged['ndate'] < merged['date']]

Step 3: Group Names by Fruit ID #

Now we'll group the filtered results by id and collect all matching names into a list for each fruit:

# Group names by id and aggregate into lists
name_groups = filtered_matches.groupby('id')['name'].agg(list).reset_index()
name_groups.columns = ['id', 'matches']

Step 4: Merge Back to df1 and Split into Columns #

Finally, we'll merge this grouped data back to df1, then split the list of matches into separate columns (match1, match2, etc.):

# Merge the grouped matches with the original df1
df1_with_matches = pd.merge(df1, name_groups, on='id', how='left')

# Split the matches list into individual columns
match_columns = df1_with_matches['matches'].apply(pd.Series)
match_columns.columns = [f'match{i+1}' for i in match_columns.columns]

# Combine the original df1 with the new match columns
final_df = pd.concat([df1_with_matches.drop('matches', axis=1), match_columns], axis=1)

# Optional: Convert dates back to the original string format
final_df['date'] = final_df['date'].dt.strftime('%d/%m/%y')

print(final_df)

Final Result #

Running this code will produce exactly the DataFrame you described:

fruit  id      date  match1  match2  match3
0   apple   2  01/10/20   david  hannah  janine
1    pear   1  15/09/20     NaN     NaN     NaN
2  banana   3  01/06/20    iain     NaN     NaN
3   peach   4  10/04/20   frida    adam     NaN

Quick notes:

• Rows with no matches (like pear) will show NaN in the match columns, which is clean and easy to handle.
• If a fruit has more than 3 matches, the code will automatically create match4, match5, etc.
• You can skip the final date conversion step if you want to keep datetime objects for further analysis.

内容的提问来源于stack exchange，提问作者David0