基于5天回溯滚动周期检测Pandas中指定列值的重现情况

Got it, let's work through this problem together. Your goal is to check if the current row's ColM value has shown up in the previous 5-day window, and you need to do this grouped by ColN. Here's a step-by-step implementation:

Step 1: Prepare and clean the data #

First, we need to convert the ColN_dt column from string to datetime format—this is crucial for calculating date differences correctly. We'll also sort each group by date to ensure our window checks work as expected.

import pandas as pd
import numpy as np

# Sample data
df = pd.DataFrame()
df['ColN'] = ['AAA', 'AAA', 'AAA', 'ABC', 'ABC', 'ABC', 'ABC', 'ABC']
df['ColM'] = ['XYZ', 'WUV', 'WUV', 'XYZ', 'WUV', 'WUV', 'OPQ', 'XYZ']
df['ColN_dt'] = ['03-12-2018', '03-13-2018', '03-16-2018', '03-18-2018', '03-22-2018', '03-23-2018', '03-26-2018', '03-30-2018']

# Convert date column to datetime
df['ColN_dt'] = pd.to_datetime(df['ColN_dt'], format='%m-%d-%Y')

# Sort each group by date to maintain chronological order
df = df.sort_values(['ColN', 'ColN_dt']).reset_index(drop=True)

Step 2: Define the check function for each group #

We'll create a helper function that, for each row in a group, checks if ColM exists in the rows where the date is within the past 5 days (but not including the current row itself, since we're looking for prior occurrences).

def check_past_5days(group):
    # Initialize a new column to store results
    group['ColM_in_past5d'] = False
    
    for idx, row in group.iterrows():
        # Calculate date range: current date minus 5 days to current date (exclusive)
        date_window = (group['ColN_dt'] >= row['ColN_dt'] - pd.Timedelta(days=5)) & (group['ColN_dt'] < row['ColN_dt'])
        # Check if ColM appears in this window
        if row['ColM'] in group.loc[date_window, 'ColM'].values:
            group.at[idx, 'ColM_in_past5d'] = True
    
    return group

# Apply the function to each group
result_df = df.groupby('ColN').apply(check_past_5days).reset_index(drop=True)

Step 3: View the result #

Let's print the output to verify our logic works:

print(result_df)

Output: #

ColN ColM    ColN_dt  ColM_in_past5d
0   AAA  XYZ 2018-03-12           False
1   AAA  WUV 2018-03-13           False
2   AAA  WUV 2018-03-16            True  # WUV appeared on 2018-03-13 (within 5 days)
3   ABC  XYZ 2018-03-18           False
4   ABC  WUV 2018-03-22           False
5   ABC  WUV 2018-03-23            True  # WUV appeared on 2018-03-22 (within 5 days)
6   ABC  OPQ 2018-03-26           False
7   ABC  XYZ 2018-03-30           False  # XYZ last appeared on 2018-03-18 (more than 5 days prior)

Key Notes: #

• We use pd.Timedelta(days=5) to handle date arithmetic accurately, avoiding manual string comparisons.
• The condition group['ColN_dt'] < row['ColN_dt'] ensures we don't count the current row's value as a "prior occurrence".
• Sorting groups by date first is non-negotiable—otherwise, the window check might accidentally include future dates.

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