Pandas遍历DataFrame检测指定日期前后3天重复记录求助

Hey there! Let's break down what's going wrong with your current code and fix it with efficient, scalable approaches—critical since you're dealing with millions of rows (iterrows will be painfully slow for that volume).

First, Why Your Loop Isn't Working #

Your current code has two key issues:

1. Inside the loop, row is a single-row Series, not the full DataFrame. So row[(row['Day'] >= start) & (row['Day'] <= end)] is trying to filter a single row, which will never return meaningful results for duplication checks.
2. Even if you fixed that, iterrows() iterates over every row individually. For a million-row dataset, this could take hours to run—we need vectorized operations instead.

Solution 1: Grouped Time Difference Check (Fast & Simple) #

This approach leverages grouping by Source (A Number) and checks if any adjacent rows (after sorting) fall within the ±3-day window. It's efficient because it avoids full-dataframe filters per row.

import pandas as pd

# Ensure Day is datetime (you mentioned this is already done, but double-check)
df['Day'] = pd.to_datetime(df['Day'])

# Sort by Source and Day to group related records together
df_sorted = df.sort_values(['Source (A Number)', 'Day']).reset_index(drop=True)

# Calculate time differences with previous and next rows in the same Source group
df_sorted['days_since_prev'] = df_sorted.groupby('Source (A Number)')['Day'].diff().dt.days.abs()
df_sorted['days_until_next'] = df_sorted.groupby('Source (A Number)')['Day'].shift(-1).sub(df_sorted['Day']).dt.days.abs()

# Mark FCR as True if any adjacent record is within 3 days (exclude self)
df_sorted['FCR'] = (df_sorted['days_since_prev'] <= 3) | (df_sorted['days_until_next'] <= 3)

# Merge back to original dataframe to preserve row order
df = df.merge(df_sorted[['Row', 'FCR']], on='Row', how='left')
# Fill NaN (for sources with only one record) with False
df['FCR'] = df['FCR'].fillna(False)

Solution 2: Merge AsOf (Precise for Edge Cases) #

If you need to account for non-adjacent records in the ±3-day window (e.g., a source has 3 records spread across 5 days), use merge_asof—a Pandas tool built for time-series matching.

import pandas as pd

df['Day'] = pd.to_datetime(df['Day'])

# Create a copy of the dataframe for matching
df_match = df[['Row', 'Day', 'Source (A Number)']].rename(columns={'Row': 'matched_row'})

# Sort data (required for merge_asof)
df_sorted = df.sort_values('Day')
df_match_sorted = df_match.sort_values('Day')

# Match records from the past 3 days for the same Source
left_match = pd.merge_asof(
    df_sorted,
    df_match_sorted,
    on='Day',
    by='Source (A Number)',
    direction='backward',
    tolerance=pd.Timedelta(days=3)
)

# Match records from the next 3 days for the same Source
right_match = pd.merge_asof(
    df_sorted,
    df_match_sorted,
    on='Day',
    by='Source (A Number)',
    direction='forward',
    tolerance=pd.Timedelta(days=3)
)

# Check if any matched record exists (exclude self)
df_sorted['FCR'] = (left_match['matched_row'] != df_sorted['Row']) | (right_match['matched_row'] != df_sorted['Row'])
df_sorted['FCR'] = df_sorted['FCR'].fillna(False)

# Restore original row order
df = df_sorted.sort_values('Row').reset_index(drop=True)

Why These Work Better Than Loops #

Both methods use Pandas' optimized vectorized operations, which run in C under the hood. For a million-row dataset, they'll finish in minutes (or even seconds) instead of hours.

Quick Fix for Your Original Loop (If You Must Test Small Data) #

If you want to see the loop work for small test data (never use this for millions of rows), here's the corrected version:

for ind, row in df.iterrows():
    start = row['Day'] - pd.Timedelta(days=3)
    end = row['Day'] + pd.Timedelta(days=3)
    # Filter the FULL dataframe for matching Source and date window
    matching_records = df[(df['Source (A Number)'] == row['Source (A Number)']) & 
                          (df['Day'] >= start) & 
                          (df['Day'] <= end)]
    # FCR is True if there's more than one record (current row + at least one other)
    df.loc[ind, 'FCR'] = len(matching_records) > 1

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