Pandas反向遍历大型NetFlow数据集：基于历史行计算自定义属性

Hey there! I get that you know iterating through a Pandas DataFrame isn't the most efficient approach—there are way better vectorized methods out there—but you want to use iteration for clarity, especially with your large NetFlow dataset. Let's walk through how to build that custom attribute step by step.

First, let's align on exactly what you're aiming for: for each row in your NetFlow DataFrame, you want to:

• Grab the source ip from the current row
• Look back one hour from the current row's Timestamp to find all matching rows with the same source ip
• Extract the last two historical rows from that filtered subset
• Calculate a custom attribute based on properties from those two rows

Step 1: Prepare Your Data (Critical Pre-Requisite) #

Before jumping into iteration, you need to make sure your time data is properly formatted—this is essential for accurate window filtering:

import pandas as pd

# Convert Timestamp column to datetime format (if it's not already)
df['Timestamp'] = pd.to_datetime(df['Timestamp'])

# Sort the DataFrame chronologically to ensure our lookback works correctly
df = df.sort_values('Timestamp').reset_index(drop=True)

Step 2: Iterate Through Rows and Compute the Custom Attribute #

We'll use iterrows() here (even though it's slow for large datasets) since you requested it for learning purposes. We'll store our new attribute in a column called custom_attr:

# Initialize the new column with empty values
df['custom_attr'] = pd.NA

# Loop through each row in the DataFrame
for idx, current_row in df.iterrows():
    current_src = current_row['source ip']
    current_time = current_row['Timestamp']
    
    # Define the one-hour lookback window (end at the current row's time)
    lookback_start = current_time - pd.Timedelta(hours=1)
    
    # Filter to get all historical rows for this source IP in the window
    historical_matches = df[
        (df['source ip'] == current_src) &
        (df['Timestamp'] >= lookback_start) &
        (df['Timestamp'] < current_time)  # Exclude the current row itself
    ]
    
    # Grab the last two rows from the filtered results
    last_two_history = historical_matches.tail(2)
    
    # Calculate your custom attribute here (adjust this logic to match your needs)
    # Example: Average of the destination ports from the last two rows
    if len(last_two_history) == 2:
        df.loc[idx, 'custom_attr'] = (last_two_history['dest port'].iloc[0] + last_two_history['dest port'].iloc[1]) / 2
    elif len(last_two_history) == 1:
        # Handle cases where only one historical row exists
        df.loc[idx, 'custom_attr'] = last_two_history['dest port'].iloc[0]
    else:
        # No matching historical rows in the window, leave as NA or set a default
        pass

A Quick Reminder on Efficiency #

Just to echo what you already know: this iterative method will perform poorly on very large NetFlow datasets. For a production-ready solution, you'd want to use vectorized operations or groupby with rolling time windows (e.g., df.groupby('source ip').rolling('1h', on='Timestamp')). But since you're prioritizing understanding first, the iteration approach is great for learning the core logic.

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