在Pandas中基于多级索引日期生成年度-月度观测频率表

Nice work figuring out the reset-index approach—let’s make it even cleaner by working directly with your existing multi-index, no need to modify the original DataFrame structure.

Why Your Earlier Attempt Failed #

The error ValueError: Grouper and axis must be same length happens because df.index.levels[0] returns the unique dates in your index, not every date entry in the DataFrame. That means its length doesn’t match the number of rows, so pandas can’t align it for grouping.

Method 1: Use get_level_values with dt Accessors #

This is the most straightforward approach—you can directly extract year and month from the date index level without resetting it:

# Extract year and month from the date index level
freq_table = df.groupby([
    df.index.get_level_values('date').dt.year,
    df.index.get_level_values('date').dt.month
])['value'].count()

# Reshape to get year as rows, month as columns
freq_table = freq_table.unstack(level=1).rename_axis(index='year', columns='month').T

Method 2: Use pd.Grouper for Granular Control #

If you prefer using pd.Grouper (like your initial attempt), you can combine it with lambda functions to extract year/month from the grouped dates:

freq_table = df.groupby([
    pd.Grouper(level='date', freq='Y').apply(lambda x: x.year),
    pd.Grouper(level='date', freq='M').apply(lambda x: x.month)
])['value'].count().unstack().T

What This Produces #

Both methods will give you the exact table you want, matching the output from your reset-index approach:

month  1  2  3  4  5  6  7  8  9  10  11  12
year                                        
2016   3  3  3  3  3  3  3  3  3   3   3   3
2017   3  3  3  3  3  3  3  3  3   3   3   3

Bonus: Keep the Original DataFrame Intact #

Unlike the reset-index method, these approaches don’t modify your original DataFrame (unless you explicitly assign changes back), which is especially useful for large datasets where resetting indexes can be inefficient.

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