使用Pandas+Python按条件统计滑动窗口内的不同端口数

Hey there! I get why combining groupby and rolling might have tripped you up—Pandas 0.22 doesn't have the handy nunique() method directly on rolling objects like newer versions do. But don't worry, we can build a custom solution that fits your exact needs.

Let's Break Down the Problem #

You need, for each unique address, to calculate the number of distinct ports in a sliding window that includes the current row plus the previous 5 rows (so a window size of 6). For rows where there aren't 5 prior entries (like the first few rows of an address group), we'll just use all available rows up to that point.

Step-by-Step Solution #

1. First, Let's Create Sample Data (to test with)

import pandas as pd

# Sample data matching your use case
data = {
    'address': ['192.168.1.1']*7 + ['192.168.1.2']*2,
    'port': [80, 80, 443, 80, 22, 443, 22, 80, 443]
}
df = pd.DataFrame(data)

2. Define a Custom Function for Rolling Unique Count

Since Pandas 0.22 doesn't support rolling().nunique() directly, we'll use rolling().apply() to run a custom function that counts unique values in each window:

def count_unique_ports_in_window(group):
    # Window size = 6 (current row + 5 previous rows)
    window_size = 6
    # Use rolling with min_periods=1 to handle early rows with fewer than 5 prior entries
    return group.rolling(window=window_size, min_periods=1).apply(lambda window: window.nunique())

# Apply the function to each address group's port column
df['unique_ports'] = df.groupby('address')['port'].apply(count_unique_ports_in_window)

3. Check the Result

Running this on our sample data will give you:

Key Notes to Keep in Mind #

• Order Matters: Make sure your DataFrame is sorted correctly (e.g., by address and timestamp if you have time data) before applying this logic. If rows are out of order, the sliding window will calculate incorrect values. You can sort with df = df.sort_values(['address', 'your_timestamp_column']).
• Performance: For large datasets, rolling.apply() can be slower than newer Pandas methods, but it's the best option for version 0.22. If you hit performance issues, you could precompute unique values using numpy sliding windows, but that's more complex.
• Window Size: We used window=6 because you specified "current row + previous 5 rows". If you meant only the previous 5 (excluding current), adjust the window size to 5.

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