如何基于ID与30天固定时间窗口高效分组数据（避免使用for循环）

Got it, let's solve this problem properly—no slow loops needed, which is critical for your 1.5M row dataset. The key issue with your initial pd.Grouper approach is that it uses a global time anchor instead of each ID's own first record as the starting point. Here's a fully vectorized, high-performance solution:

Core Idea #

For each ID, calculate the number of days between each record's timestamp and the ID's earliest timestamp. Then, divide that day count by 30 (using integer division) to group records into consecutive 30-day windows starting from the ID's first entry.

Step-by-Step Implementation #

First, make sure your Time column is properly parsed as datetime (skip this if it's already done):

import pandas as pd

# Sample input data
data = [
    (12345, "2021-01-01 14:00:00"),
    (12345, "2021-01-15 14:00:00"),
    (12345, "2021-01-29 14:00:00"),
    (12345, "2021-02-15 14:00:00"),
    (12345, "2021-02-16 14:00:00"),
    (12345, "2021-03-15 14:00:00"),
    (12345, "2021-04-24 14:00:00"),
    (12344, "2021-01-24 14:00:00"),
    (12344, "2021-01-25 14:00:00"),
    (12344, "2021-04-24 14:00:00"),
]

df = pd.DataFrame(data, columns=["ID", "Time"])
df["Time"] = pd.to_datetime(df["Time"])

Now, compute the per-ID windows:

# 1. Get the earliest timestamp for each ID (broadcast to all rows of the ID)
df["id_start_time"] = df.groupby("ID")["Time"].transform("min")

# 2. Calculate days between current record and ID's start time
df["days_since_start"] = (df["Time"] - df["id_start_time"]).dt.days

# 3. Assign group number: integer division by 30 gives 0-based groups, +1 makes it 1-based
df["Group"] = (df["days_since_start"] // 30) + 1

# Clean up intermediate columns (optional)
df = df.drop(columns=["id_start_time", "days_since_start"])

Result #

Running this code will produce exactly the grouping logic you need (with per-ID group numbering, which aligns with your note that global uniqueness isn't required):

Why This Works (And Is Fast) #

• Vectorized Operations: All calculations use Pandas' built-in groupby/transform and datetime arithmetic, which are optimized in C (no Python-level loops).
• Per-ID Anchoring: The transform("min") ensures each ID uses its own first record as the window start, not a global date.
• Scalability: This will handle your 1.5M rows with ease—far faster than any loop-based approach.

If you need global group numbers (like your example's 5 and 6 for ID 12344), you can add a final step to map per-ID groups to unique global IDs:

# Generate global unique group IDs
df["Global_Group"] = df.groupby(["ID", "Group"]).ngroup() + 1

This would assign global numbers matching your sample output, but again, your note says this isn't required—use it only if you need it.

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