Pandas处理月度数据缺失值及月度与季度视图快速切换

Great call converting the Month column to datetime first—that's the perfect starting point for both tasks! Let's break down how to implement each requirement with clear, reproducible code.

1. Detect and Fill Missing Months #

Since you've already parsed Month as datetime, we can leverage pandas' date range tools to fill gaps and forward-fill missing values:

import pandas as pd

# Your original data (for reference)
data = {
    'Month': ['01-2021', '02-2021', '03-2021', '04-2021', '05-2021', '06-2021', '08-2021', '09-2021'],
    'Sum': [1, 4, 9, 11, 15, 16, 21, 25]
}
df = pd.DataFrame(data)

# Confirm datetime conversion (you already did this step!)
df['Month'] = pd.to_datetime(df['Month'], format='%m-%Y')

# Set datetime column as index for easy resampling/reindexing
df = df.set_index('Month')

# Generate a complete monthly sequence from the first to last date in your data
full_month_sequence = pd.date_range(start=df.index.min(), end=df.index.max(), freq='MS')

# Reindex to fill gaps, then use forward fill to populate missing Sum values
df_filled = df.reindex(full_month_sequence).ffill()

# Optional: Convert index back to your original 'MM-YYYY' string format
df_filled = df_filled.reset_index().rename(columns={'index': 'Month'})
df_filled['Month'] = df_filled['Month'].dt.strftime('%m-%Y')

This will add the missing 07-2021 row with a Sum value of 16 (carried over from June 2021), exactly as you requested.

2. Toggle Between Monthly and Quarterly Views #

We can build a simple function to convert between the filled monthly data and aggregated quarterly data. This keeps your workflow flexible:

# First, ensure we're working with the datetime-indexed version of df_filled
df_monthly = df_filled.copy()
df_monthly['Month'] = pd.to_datetime(df_monthly['Month'], format='%m-%Y')
df_monthly = df_monthly.set_index('Month')

# Function to convert monthly data to quarterly summaries
def get_quarterly_data(df_monthly):
    # Resample by quarter and sum values
    df_quarterly = df_monthly.resample('Q').sum()
    # Format index to 'Q1-2021' style
    df_quarterly.index = df_quarterly.index.to_period('Q').strftime('Q%q-%Y')
    # Reset index to match your desired output format
    return df_quarterly.reset_index().rename(columns={'index': 'Month'})

# Get quarterly data
df_quarterly = get_quarterly_data(df_monthly)

# To switch back to monthly, just use df_filled (or df_monthly reset to your string format)

The resample('Q') method automatically groups months into calendar quarters, and strftime('Q%q-%Y') formats the period to match your requested Q1-2021 style.

Quick Tip #

To make toggling even faster, you can wrap both views in a helper function that takes a view_type parameter (e.g., 'monthly' or 'quarterly') and returns the corresponding DataFrame.

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