基于多数据集条件匹配与日期范围为数据集添加列的方案

Got it, let's tackle this problem step by step. You want to add columns from a third dataset to your first one, using a middle dataset for ID matching and filtering records based on date ranges. Below is a practical, efficient solution using Python's pandas library—perfect for both small and large datasets.

Step 1: Define Sample Data #

First, let's set up dummy datasets that mirror your use case. We'll have:

• Target Dataset (df1): The dataset you want to enrich
• Matching Dataset (df2): Links IDs between df1 and the third dataset
• Source Dataset (df3): Contains the date ranges and values you want to pull into df1

import pandas as pd

# Target Dataset (first dataset)
df1 = pd.DataFrame({
    'id': ['A1', 'A2', 'A3', 'A4'],
    'event_date': pd.to_datetime(['2023-01-15', '2023-03-20', '2023-05-10', '2023-06-01'])
})

# Middle Matching Dataset
df2 = pd.DataFrame({
    'id': ['A1', 'A2', 'A3', 'A4'],
    'source_id': ['S1', 'S2', 'S1', 'S3']
})

# Source Dataset (third dataset with date ranges)
df3 = pd.DataFrame({
    'source_id': ['S1', 'S1', 'S2', 'S3'],
    'start_date': pd.to_datetime(['2023-01-01', '2023-04-01', '2023-03-01', '2023-05-01']),
    'end_date': pd.to_datetime(['2023-03-31', '2023-06-30', '2023-04-30', '2023-06-30']),
    'value_to_add': [100, 200, 150, 300]
})

Step 2: Link Target and Matching Datasets #

First, merge df1 with df2 to get the source_id needed to connect to df3:

# Merge target dataset with matching dataset to get source_id
df1_with_source = df1.merge(df2, on='id', how='left')

Step 3: Match with Date Range Filter #

For large datasets, merge_asof is the most efficient method—it avoids expensive cross-joins. It requires sorting the date columns first:

# Sort source dataset by source_id and start_date (required for merge_asof)
df3_sorted = df3.sort_values(by=['source_id', 'start_date'])

# Use merge_asof to match records where event_date >= start_date (backward direction picks the latest valid start_date)
merged = pd.merge_asof(
    df1_with_source.sort_values('event_date'),
    df3_sorted,
    left_on='event_date',
    right_on='start_date',
    by='source_id',
    direction='backward'
)

# Filter out records where event_date exceeds the end_date
merged = merged[merged['event_date'] <= merged['end_date']]

# Final cleanup: Keep original df1 columns plus the added value
final_result = merged[['id', 'event_date', 'value_to_add']].merge(df1, on=['id', 'event_date'], how='right')

Alternative: For Small Datasets (More Intuitive) #

If your dataset is small, you can use apply to manually check each row's matching conditions:

def fetch_matching_value(row):
    # Get source_id from the middle dataset
    source_id = df2.loc[df2['id'] == row['id'], 'source_id'].iloc[0]
    # Find all source records that match the source_id and date range
    matches = df3[(df3['source_id'] == source_id) & 
                  (df3['start_date'] <= row['event_date']) & 
                  (df3['end_date'] >= row['event_date'])]
    # Return the value if a match exists, else None
    return matches['value_to_add'].iloc[0] if not matches.empty else None

# Apply the function to df1
df1['value_to_add'] = df1.apply(fetch_matching_value, axis=1)

Key Notes #

• Efficiency: merge_asof is ~10-100x faster than apply for large datasets (10k+ rows)
• Edge Cases: The code handles missing matches by returning None—adjust this to your needs (e.g., fill with 0)
• Multiple Matches: If multiple source records fit the date range, merge_asof picks the one with the latest start_date. For all matches, use a cross-join followed by filtering.

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