如何用Pandas分组重采样时间序列以统一日期范围？

Great question! You're right—there's a much more straightforward way to achieve this without going through pivot/unstack hoops. The core idea is to first define the complete set of (group, date) combinations you need, then align your original data to that structure to fill in missing values with NaN.

Method 1: MultiIndex Cartesian Product (Most Efficient) #

This approach builds the full set of required combinations upfront, then uses reindexing to match your data. It's fast and easy to follow:

import pandas as pd

# Your sample data
date = pd.to_datetime(pd.Series(['2010-01-01', '2010-01-02', '2010-01-03', '2010-01-06', '2010-01-01', '2010-01-03']))
group = [1,1,1,1, 2, 2]
value = [1,2,3,4,5,6]
df = pd.DataFrame({'date':date, 'group':group, 'value':value})

# 1. Create a full date range covering all groups' dates
full_date_range = pd.date_range(start=df['date'].min(), end=df['date'].max(), freq='D')

# 2. Generate all possible (group, date) combinations
unique_groups = df['group'].unique()
complete_multi_index = pd.MultiIndex.from_product(
    [unique_groups, full_date_range],
    names=['group', 'date']
)

# 3. Align original data to the complete index (automatically fills missing values with NaN)
result = df.set_index(['group', 'date']).reindex(complete_multi_index).reset_index()

print(result)

Why This Works: #

• We explicitly define every combination of group and date that should exist (from the earliest to latest date across all groups)
• Reindexing the original data against this complete index automatically fills any missing (group, date) pairs with NaN
• No unnecessary pivot/unstack operations—this keeps the logic direct and readable

Method 2: Groupby + Apply (Alternative for Groupby Fans) #

If you prefer working with groupby, you can expand each group's date range to the global min/max and reindex within each group:

import pandas as pd

# Your sample data (same as above)
date = pd.to_datetime(pd.Series(['2010-01-01', '2010-01-02', '2010-01-03', '2010-01-06', '2010-01-01', '2010-01-03']))
group = [1,1,1,1, 2, 2]
value = [1,2,3,4,5,6]
df = pd.DataFrame({'date':date, 'group':group, 'value':value})

# Get global date bounds
min_date = df['date'].min()
max_date = df['date'].max()

# Expand each group to the full date range
result = df.groupby('group').apply(
    lambda g: g.set_index('date').reindex(pd.date_range(min_date, max_date, freq='D'))
).reset_index().rename(columns={'level_1': 'date'})

print(result)

Key Notes: #

• This method loops through each group individually, so it's slightly less efficient for very large datasets compared to the MultiIndex approach
• It still produces the exact output you need, with NaN filled in for missing dates per group

Comparison to Your Original Solution #

Both methods eliminate the need for the pivot-unstack-resample chain in your original code. They're more maintainable, as the logic directly maps to the goal: "ensure every group has a record for every date in the global range".

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