如何基于或条件合并两个Pandas DataFrame？

Got it, let's break down how to solve this problem. First, let's restate our starting data to make sure we're on the same page:

import pandas as pd

# Our education mapping DataFrame
edu_data = [['school', 1, 2], ['college', 3, 4], ['grad-school', 5, 6]]
edu = pd.DataFrame(edu_data, columns=['Education', 'StudentID1', 'StudentID2'])

# The student DataFrame we want to enrich
data = [['tom', 3], ['nick', 5], ['juli', 6], ['jack', 10]]
df = pd.DataFrame(data, columns=['Name', 'StudentID'])

We need to match each StudentID in df with either StudentID1 or StudentID2 in edu, then add the corresponding Education value to df (filling with NaN when there's no match). Here are two solid approaches:

Approach 1: Reshape & Merge (Best for Scalability) #

This is the most efficient method, especially if you're working with larger datasets. We'll first "unpivot" the edu DataFrame to get all StudentIDs into a single column, then merge it with df.

1. Reshape edu to long format:

# Melt the edu DataFrame so all StudentIDs are in one column
edu_long = pd.melt(
    edu,
    id_vars=['Education'],  # Keep this column as is
    value_vars=['StudentID1', 'StudentID2'],  # The columns to unpivot
    value_name='StudentID'  # Name for the new combined ID column
).drop(columns='variable')  # We don't need the 'variable' column

2. Merge with df using a left join (to keep all rows from df):

result = pd.merge(df, edu_long, on='StudentID', how='left')

This gives exactly the output you want:

Name  StudentID    Education
0    tom          3      college
1   nick          5  grad-school
2   juli          6  grad-school
3   jack         10          NaN

Approach 2: Dictionary Lookup (Simple & Intuitive) #

If you prefer a more straightforward, readable approach for smaller datasets, you can build a lookup dictionary and use map to assign the Education values.

1. Build a dictionary mapping each StudentID to its Education:

id_edu_map = {}
for _, row in edu.iterrows():
    # Add both StudentIDs from the row to the dictionary
    id_edu_map[row['StudentID1']] = row['Education']
    id_edu_map[row['StudentID2']] = row['Education']

2. Map the values to df:

df['Education'] = df['StudentID'].map(id_edu_map)

This will also produce the desired result—map automatically fills in NaN for any StudentID that isn't in the dictionary.

Quick Notes #

• Approach 1 is better for larger datasets because pandas' built-in merge operations are optimized for performance.
• Approach 2 is great for small datasets or when you want to see exactly how the mapping is being built.

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