如何通过Pivoting和Multi-Indexing转换Pandas DataFrame为指定格式？

Got it, let's fix this. The key here is to use a multi-level column index when pivoting, which aligns exactly with the structure you want. Here's how to do it step by step:

Step 1: Clean Up the Original Data #

First, let's assume your original DataFrame is named df. We'll drop the extra Index column since it's just the default row number (skip this if that column isn't present in your actual data):

import pandas as pd

# Drop the unnecessary Index column if present
df = df.drop('Index', axis=1)

Step 2: Pivot to the Desired Format #

Use pivot_table to create the wide format with nested Year → Week column headers:

# Create the pivoted DataFrame
result = df.pivot_table(
    index='Code',
    columns=['Year', 'Week'],  # This sets up the two-level column structure
    values='Count',
    fill_value=0  # Ensures any missing weeks get filled with 0 (safe guard)
)

# Optional: Sort columns to ensure years and weeks are in sequential order
result = result.sort_index(axis=1)

Why This Works: #

• The columns=['Year', 'Week'] argument tells Pandas to group columns first by Year, then by Week under each Year—exactly matching your desired output layout.
• fill_value=0 prevents NaN values in case of accidental missing week entries (even though you noted each year has 52 rows, this is a safe backup).
• Sorting the columns (sort_index(axis=1)) guarantees years are ordered correctly and weeks run from 1 to 52 under each year.

Example Output: #

When you print result, you'll get the exact format you requested:

Year   2005                          2006                        
Week     1  2  3  ...  50  51  52     1  2  3  ...  50  51  52
Code                                                            
AE       0  0  2  ...   0   0   1     3  0  1  ...   0   0   1
AU       0  0  2  ...   0   0   1     3  0  1  ...   0   0   1
...     ... ... ... ... ... ... ...   ... ... ... ... ... ... ...
ZC       ... ... ... ... ... ... ...   ... ... ... ... ... ... ...

This should resolve your issue—you likely missed specifying the multi-column grouping in your earlier pivot attempts!

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