R语言：基于多键及近似日期（误差≤1秒）合并Dataframe

Got it, let's work through how to match your library check-out and check-in records—since there's no unique transaction ID, we'll leverage your 3 ID columns plus the 1-second datetime tolerance to pair them up correctly. Here's a step-by-step solution using pandas:

1. First, Prep Your Datetime Columns #

First things first: make sure both datetime columns are properly parsed as datetime64 types. This is non-negotiable for accurate time-based matching:

import pandas as pd

# Convert datetime columns to proper datetime format
check_out_df['checkout_datetime'] = pd.to_datetime(check_out_df['checkout_datetime'])
check_in_df['checkin_datetime'] = pd.to_datetime(check_in_df['checkin_datetime'])

2. Choose Your Matching Key Approach #

You’ve got two solid options here, depending on your data structure and preference:

Option A: Combine ID Columns into a Single Key #

If your three ID columns (e.g., item_id, patron_id, branch_id) uniquely identify a potential check-out/check-in pair (ignoring time), concatenate them into a single match key to simplify the merge:

# Create a combined string key in both DataFrames
check_out_df['match_key'] = (
    check_out_df['item_id'].astype(str) + "_" +
    check_out_df['patron_id'].astype(str) + "_" +
    check_out_df['branch_id'].astype(str)
)
check_in_df['match_key'] = (
    check_in_df['item_id'].astype(str) + "_" +
    check_in_df['patron_id'].astype(str) + "_" +
    check_in_df['branch_id'].astype(str)
)

Option B: Use Multiple ID Columns Directly #

You can also skip creating a combined key and use the three ID columns directly as matching criteria—pandas supports merging on multiple columns natively:

# Define the list of ID columns to match on
match_columns = ['item_id', 'patron_id', 'branch_id']

3. Merge with Time Tolerance Using merge_asof #

The merge_asof function is perfect for this scenario—it lets you match rows based on datetime values with a specified tolerance, while also matching on your ID keys. Just remember: both DataFrames must be sorted by their datetime columns first (this is a requirement for merge_asof).

If You Went with Option A (Combined Key): #

# Sort both DataFrames by their datetime columns
check_out_sorted = check_out_df.sort_values('checkout_datetime')
check_in_sorted = check_in_df.sort_values('checkin_datetime')

# Merge with 1-second time tolerance
merged_df = pd.merge_asof(
    check_in_sorted,
    check_out_sorted,
    on='match_key',  # Use our combined ID key
    left_on='checkin_datetime',
    right_on='checkout_datetime',
    direction='backward',  # Match each check-in to the most recent prior check-out
    tolerance=pd.Timedelta(seconds=1)
)

If You Went with Option B (Multiple ID Columns): #

# Sort both DataFrames by their datetime columns
check_out_sorted = check_out_df.sort_values('checkout_datetime')
check_in_sorted = check_in_df.sort_values('checkin_datetime')

# Merge on multiple ID columns + time tolerance
merged_df = pd.merge_asof(
    check_in_sorted,
    check_out_sorted,
    by=match_columns,  # Match on all three ID columns
    left_on='checkin_datetime',
    right_on='checkout_datetime',
    direction='backward',
    tolerance=pd.Timedelta(seconds=1)
)

4. Clean Up and Validate the Results #

After merging, you’ll want to tidy up the output and verify matches:

# Drop rows where no matching check-out was found (optional, depending on your needs)
merged_df = merged_df.dropna(subset=['checkout_datetime'])

# Rename columns for clarity if needed
merged_df.rename(columns={
    'checkout_datetime': 'transaction_start',
    'checkin_datetime': 'transaction_end'
}, inplace=True)

# Spot-check a few rows to ensure matches make sense
print(merged_df[['transaction_start', 'transaction_end', *match_columns]].head())

Quick Tips to Avoid Headaches: #

• direction='backward': This ensures we pair each check-in with the most recent check-out that falls within the 1-second window. Since check-ins should always happen after check-outs, this is the logical choice.
• Tolerance Adjustment: If you find some valid matches are being excluded, double-check that your datetime columns are timezone-consistent (if applicable) and tweak the tolerance if needed.
• Duplicate Checks: If you have duplicate ID/datetime combinations, you may need to add extra logic (like grouping by ID columns and picking the first/last match) to avoid false duplicates.

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