如何在Pandas中为DataFrame补充指定日期范围的缺失周数据且保留原值

Hey there! I totally get the frustration of trying to fill in missing weekly gaps only to have your existing data get wiped out. Let’s fix this issue with your pandas DataFrame step by step.

The Root of the Problem #

When you tried using reindex directly, you likely weren’t targeting the date column as your index. By default, reindex works with the DataFrame’s row indices—not your date values—so it was replacing existing rows instead of filling in gaps. Here’s how to fix it properly.

Solution 1: Correctly Use reindex #

First, we’ll create a complete range of weekly dates matching your W-SUN frequency, then set your date column as the index before reindexing. This ensures existing dates retain their values, while missing dates get filled with 0.

import pandas as pd
from datetime import timedelta

# Your original weekly data generation code
df['date'] = pd.to_datetime(df['date']) - timedelta(days=6)
data_type = '<your_column_name>'  # Replace with your actual column name
weekly_data: pd.DataFrame = (
    df
    .groupby([pd.Grouper(key='date', freq='W-SUN')])[data_type]
    .sum()
    .reset_index()
)

# Define your target date range
start = '2020-08-30'
end = '2020-10-30'

# 1. Create a full range of weekly dates (ending on Sundays)
full_weekly_dates = pd.date_range(start=start, end=end, freq='W-SUN')

# 2. Set 'date' as the index, reindex to fill gaps, then reset index back
weekly_data_complete = (
    weekly_data
    .set_index('date')
    .reindex(full_weekly_dates, fill_value=0)
    .reset_index()
    .rename(columns={'index': 'date'})  # Rename the index column back to 'date'
)

Solution 2: Use merge (More Intuitive for Beginners) #

If you prefer a more explicit approach, merging your existing weekly data with a full date DataFrame works great. This avoids index confusion entirely:

# Step 1: Create a DataFrame with all required weekly dates
full_dates_df = pd.DataFrame({'date': full_weekly_dates})

# Step 2: Left merge to retain all dates, fill missing values with 0
weekly_data_complete = pd.merge(
    full_dates_df, 
    weekly_data, 
    on='date', 
    how='left'
).fillna(0)

Key Notes #

• Match the Frequency: Make sure freq='W-SUN' is consistent across your date range creation and original grouping—this ensures the weekly dates align perfectly.
• Verify the Output: Both methods will keep your existing values for 2020-10-11 and 2020-10-18, while setting all missing weeks in your target range to 0.0.

Either approach will solve your problem—pick whichever feels more intuitive for your workflow!

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