按ID计算近10天Values总和的数据集处理需求

Hey there! Let's walk through how to add a column that sums the Values for each ID over the most recent 10 days. First, we'll start with a reproducible dataset (since you mentioned the data can be recreated with specified code), then implement the rolling sum logic.

Step 1: Create a Reproducible Dataset #

Let's generate a sample dataset with ID, dates, and Values using pandas:

import pandas as pd
import numpy as np

# Set random seed for reproducibility
np.random.seed(42)

# Generate sample data
ids = ['A', 'B', 'C']
dates = pd.date_range(start='2024-01-01', end='2024-01-20')
data = []
for id in ids:
    for date in dates:
        data.append({'ID': id, 'dates': date, 'Values': np.random.randint(1, 10)})

df = pd.DataFrame(data)
print(df.head())

Step 2: Calculate Rolling 10-Day Sum per ID #

To get the sum of Values for each ID over the most recent 10 days (including the current date), follow these steps:

1. Ensure the dates column is in datetime format (it already is in our sample, but this is critical for real-world data):

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

2. Group the data by ID, sort each group chronologically, then compute the rolling 10-day sum:

   # Group by ID, sort within each group, then calculate rolling sum
   df['rolling_10d_sum'] = df.groupby('ID').apply(
       lambda group: group.sort_values('dates')['Values'].rolling('10D', on='dates').sum()
   ).reset_index(level=0, drop=True)
   

Key Details: #

• rolling('10D') uses a time-based window (10 days) instead of a fixed number of rows, which correctly handles gaps in dates for any ID.
• on='dates' tells pandas to use the date column to define window boundaries, ensuring the sum only includes entries within the 10-day window relative to each row's date.
• Sorting each group by dates first guarantees the rolling calculation proceeds in the correct chronological order.

Step 3: Verify the Result #

Check a snippet of the output to confirm the logic works:

print(df[df['ID'] == 'A'].tail(10))

You'll see rolling_10d_sum accumulates the sum of Values over the previous 9 days plus the current day (a full 10-day window).

Edge Cases to Keep in Mind #

• If an ID has multiple entries on the same date, all those entries will be included in the rolling sum for that date's window.
• For the first few entries of an ID (where fewer than 10 days of data exist), the sum will only include the available days up to that point.

内容的提问来源于stack exchange，提问作者Luis Otavio Fernandes