如何用Pandas高效重组时间序列数据，生成年度月度发电量汇总表？

Nice one! This is a classic time series reshaping task, and Pandas has exactly the vectorized tools you need to avoid clunky loops. Let's break this down with your example scenario.

First, let's start with a sample version of your monthly aggregated Series to work with:

import pandas as pd

# Sample monthly yield data (2017 full year + 2018 Jan)
dates = pd.date_range(start="2017-01-31", end="2018-01-31", freq="M")
monthly_yield = pd.Series(
    [1200, 1100, 1300, 1400, 1600, 1500, 1450, 1350, 1250, 1150, 1050, 950, 1250],
    index=dates,
    name="Yield (kWh)"
)

Method 1: Using pivot (Intuitive & Readable) #

This approach is great if you prefer explicit steps to split out year/month values:

1. Convert the Series to a DataFrame and extract year/month from the DateTime index:

   # Reset index to turn dates into a column, then extract year and month
   df = monthly_yield.reset_index().rename(columns={"index": "date"})
   df["year"] = df["date"].dt.year
   df["month"] = df["date"].dt.month
   

2. Pivot the DataFrame to get months as rows, years as columns, fill missing values with 0:

   pivoted = df.pivot(index="month", columns="year", values="Yield (kWh)").fillna(0)
   

Method 2: Using unstack (Concise & Efficient) #

If you want a more streamlined approach, leverage Pandas multi-indexes and unstack:

1. Convert the DateTime index into a multi-index with year and month levels:

   # Create a multi-index from the original DateTime index's year and month
   monthly_yield.index = pd.MultiIndex.from_arrays(
       [monthly_yield.index.year, monthly_yield.index.month],
       names=["year", "month"]
   )
   

2. Unstack the year level into columns, then ensure we have all 12 months (even if missing data):

   # Unstack year to columns, fill missing values with 0, and reindex to include 1-12
   reshaped = monthly_yield.unstack(level="year").fillna(0).reindex(range(1, 13))
   

Result #

Either method will give you the exact DataFrame you need:

year    2017   2018
month              
1      1200.0  1250.0
2      1100.0     0.0
3      1300.0     0.0
4      1400.0     0.0
5      1600.0     0.0
6      1500.0     0.0
7      1450.0     0.0
8      1350.0     0.0
9      1250.0     0.0
10     1150.0     0.0
11     1050.0     0.0
12      950.0     0.0

Both approaches use Pandas' optimized vectorized operations—no slow loops involved! They’re perfect even for large time series datasets with years of 10-minute readings.

内容的提问来源于stack exchange，提问作者Bart Van Loon