Pandas数据帧基于时间戳匹配条件添加结果列求助

Hey there! Let's work through this problem together. You need to add a res column where each row is 1 if there's at least one row after it with a timestamp within 20 minutes and A=1, otherwise 0. Here's how to do it properly:

Step 1: Prepare Your Data #

First, make sure your Timestamp column is actually a datetime type (not a string) — this is critical for time calculations. Then sort the DataFrame by timestamp to enable efficient window operations:

import pandas as pd

# Convert Timestamp column to datetime
df['Timestamp'] = pd.to_datetime(df['Timestamp'])

# Sort by timestamp (required for efficient rolling window logic)
df = df.sort_values('Timestamp').reset_index(drop=True)

Step 2: Efficient Method (Great for Large Datasets) #

For big datasets, using apply() (which loops row-by-row) is too slow. Instead, we'll reverse the DataFrame and use a time-based rolling window to check for future A=1 values:

# Reverse the DataFrame so "future" rows become "past" rows
reversed_df = df.iloc[::-1].set_index('Timestamp')

# Create a rolling window of 20 minutes, looking "backward" (which is forward in original data)
# `closed='left'` ensures we exclude the current row (we need timestamps > current row)
rolling_max = reversed_df['A'].rolling('20min', closed='left').max()

# Reverse the result back to match the original DataFrame, fill NaNs with 0, and convert to int
df['res'] = rolling_max.iloc[::-1].fillna(0).astype(int)

How This Works: #

• Reversing the DataFrame turns future rows into past rows, so we can use Pandas' built-in rolling window (which is optimized for performance).
• The rolling window checks all rows within the last 20 minutes of the reversed row (which is the next 20 minutes in the original data).
• Taking the max() of A tells us if there's any 1 in that window. If yes, max() returns 1; otherwise 0.
• We reverse the result back and fill NaNs (for the last row in original data, which has no future rows) with 0.

Step 3: Simpler (But Slower) Method (For Small Datasets) #

If your dataset is small (a few thousand rows max), you can use apply() for more readable code:

def check_future_ones(row):
    # Define the time window: after current timestamp, within 20 minutes
    time_window_end = row['Timestamp'] + pd.Timedelta(minutes=20)
    # Check if any row meets the conditions
    has_match = ((df['Timestamp'] > row['Timestamp']) & 
                 (df['Timestamp'] <= time_window_end) & 
                 (df['A'] == 1)).any()
    return 1 if has_match else 0

# Apply the function to each row
df['res'] = df.apply(check_future_ones, axis=1)

Example Test Case #

Let's test with sample data to verify:

# Sample data
data = {
    'Timestamp': ['2008-07-07 11:30:25', '2008-07-07 11:35:00', '2008-07-07 11:45:00', '2008-07-07 12:00:00'],
    'A': [0, 1, 0, 1]
}
df = pd.DataFrame(data)

After running the efficient method, the res column will be:

Which matches our expected logic:

• Row 1: Has a row with A=1 (11:35) within 20 minutes → res=1
• Row 2: No rows with A=1 in the next 20 minutes → res=0
• Row 3: Has a row with A=1 (12:00) within 20 minutes → res=1
• Row 4: No future rows → res=0

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