如何在Python中对DataFrame按周/年/国家分组并透视处理类别列

Got it, let's walk through how to build this pivot table exactly as you need it—grouping by week, country, year, summing values per category, auto-including new categories, and filling missing values with 0.

Step 1: Set Up Your DataFrame #

First, let's replicate your sample data (skip this if you already have your DataFrame loaded):

import pandas as pd

# Sample data matching your input
data = {
    'week': [1,1,1,1,1,1,1,1,1,1,2,2,2,2],
    'Country': ['Angola']*14,
    'Year': [2005]*5 + [2006]*5 + [2006]*4,
    'Value': [6,13,10,11,5,3,2,7,3,6,3,2,7,3],
    'category': ['A','A','B','C','D','A','B','C','D','E','A','B','C','D']
}
df = pd.DataFrame(data)

Step 2: Generate the Pivot Table #

We'll use pandas' pivot_table function—it's perfect for this scenario because it automatically handles all existing categories and fills gaps with 0. Then we'll tweak the column names to match your required format:

# Create the pivot table with sum aggregation
pivot_df = pd.pivot_table(
    df,
    index=['week', 'Country', 'Year'],  # Group by these columns
    columns='category',                 # Spread categories into columns
    values='Value',                     # Sum this column
    aggfunc='sum',                      # Aggregation method: sum
    fill_value=0                        # Fill missing category values with 0
).reset_index()  # Move index columns back to regular columns

# Rename columns to "Category_A", "Category_B" etc.
pivot_df.columns = [
    col if col in ['week', 'Country', 'Year'] 
    else f'Category_{col}' 
    for col in pivot_df.columns
]

Step 3: Check the Result #

If you print the resulting DataFrame, you'll get exactly what you asked for:

print(pivot_df)

Output:

week Country  Year  Category_A  Category_B  Category_C  Category_D  Category_E
0     1   Angola  2005          19          10          11           5           0
1     1   Angola  2006           3           2           7           3           6
2     2   Angola  2006           3           2           7           3           0

Key Notes for Future Use #

• Auto-include new categories: If your data gets updated with a new category (like "F"), pivot_table will automatically add a Category_F column to the result, filling 0 for any groups that don't have values for this new category.
• Flexibility: If you need a different aggregation method (like mean instead of sum), just change the aggfunc parameter (e.g., aggfunc='mean').

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