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

Alright, let's work through this Pandas DataFrame merging problem step by step. First, let's recap the data you shared to make sure we're aligned.

First, Let's Define Your DataFrames #

Let's start by recreating the two DataFrames you provided in code (this makes it easier to test merging logic):

Dock Status DataFrame (dock_status) #

import pandas as pd

dock_status = pd.DataFrame({
    'dock_id': [3082, 468, 407, 3016],
    'dock_name': ['Hope St & Union Ave', 'Broadway & W 55 St', 'Henry St & Poplar St', 'Kent Ave & N 7 St'],
    'avail_bikes': [8, 0, 22, 29],
    'avail_docks': [16, 59, 15, 16]
})

Environmental/Status DataFrame (environmental_data) #

environmental_data = pd.DataFrame({
    'status_key': [1, 1, 1, 1],
    'datehour': ['2016-06-01 19:25:00']*4,
    'visi': [pd.NA]*4,
    'vism': [pd.NA]*4,
    'wdird': [pd.NA]*4,
    'wdire': [pd.NA]*4
})

Common Complex Merging Scenarios & Solutions #

Since you mentioned "complex conditions", let's cover the most likely scenarios you're facing, along with actionable code.

Scenario 1: Multi-Key Matching + Time Filtering #

Let's assume your environmental_data actually includes a dock_id column (maybe omitted in your snippet) and you want to merge only rows where the timestamp falls within a specific window.

First, clean the time column and add the missing dock_id (adjust this to match your actual data):

# Convert datehour to datetime for time-based filtering
environmental_data['datehour'] = pd.to_datetime(environmental_data['datehour'])
# Add dock_id to environmental_data (match the order of dock_status)
environmental_data['dock_id'] = [3082, 468, 407, 3016]

Now merge with a time condition:

merged_df = pd.merge(
    dock_status,
    # Filter environmental data to only include entries after 19:00 on 2016-06-01
    environmental_data[environmental_data['datehour'] >= '2016-06-01 19:00:00'],
    on='dock_id',  # Match rows by dock_id
    how='left'     # Keep all rows from dock_status, even if no environmental data exists
)

Scenario 2: Time-Range Matching (Nearest Timestamp) #

If you need to merge each dock's status with the closest environmental data point (within a time tolerance), use pd.merge_asof (perfect for time-series alignment):

First, add a timestamp column to dock_status (assume it was recorded 1 minute after the environmental data):

dock_status['record_time'] = pd.to_datetime('2016-06-01 19:26:00')

Sort both DataFrames by time (required for merge_asof):

dock_status_sorted = dock_status.sort_values('record_time')
environmental_data_sorted = environmental_data.sort_values('datehour')

Merge with a 10-minute time tolerance:

merged_df = pd.merge_asof(
    dock_status_sorted,
    environmental_data_sorted,
    left_on='record_time',
    right_on='datehour',
    by='dock_id',  # Match within the same dock
    tolerance=pd.Timedelta('10 minutes'),  # Only match entries within 10 minutes
    direction='nearest'  # Pick the closest timestamp
)

Scenario 3: Custom Text-Based Matching #

If you need to merge based on text in dock_name (e.g., matching by street name), first extract a common key from both DataFrames:

# Extract the first street from dock_name as a matching key
dock_status['area'] = dock_status['dock_name'].str.split(' & ').str[0]
# Assume environmental_data has an area column matching these streets
environmental_data['area'] = ['Hope St', 'Broadway', 'Henry St', 'Kent Ave']

# Merge on the custom 'area' key
merged_df = pd.merge(
    dock_status,
    environmental_data,
    on='area',
    how='inner'  # Only keep rows where there's a match in both DataFrames
)

Key Tips for Complex Merges #

• Always clean your data first: Convert time columns to datetime type, handle missing values, and ensure matching keys have the same data type.
• Choose the right merge method: Use pd.merge for exact matches, pd.merge_asof for time-range alignment, and df.join if you're merging on indexes.
• Test with small subsets: Before merging large datasets, test your logic on a small sample to avoid unexpected results.

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