Pandas：按各单元格唯一值分组并拆分Status列为多列

Got it, let's tackle this problem step by step! You need to group your data by the unique combinations of all columns except Status and Count, then pivot the Status values into separate columns where each new column holds the sum of Count for that status in the group.

Step 1: Prepare Your Data #

First, let's get your sample data into a pandas DataFrame (I filled in the missing Count value for the last row since it was cut off):

import pandas as pd

# Your original data (cleaned up)
data = {
    "Department": ["Sales", "Sales", "Sales", "IT", "IT", "IT", "IT", "Marketing"],
    "Age": ["31-35", "26-30", "31-35", "21-25", "31-35", "26-30", "41-45", "36-40"],
    "Salary": ["46K-50K", "26K-30K", "31K-35K", "46K-50K", "66K-70K", "46K-50K", "66K-70K", "46K-50K"],
    "Status": ["Senior", "Junior", "Junior", "Junior", "Senior", "Junior", "Senior", "Senior"],
    "Count": [30, 40, 40, 20, 5, 3, 3, 10]
}

df = pd.DataFrame(data)

Step 2: Pivot the Data (Two Easy Methods) #

Method 1: Use pivot_table (Most Intuitive) #

This is the simplest way for most users—pivot_table handles grouping, aggregation, and pivoting all in one go:

# Pivot the Status column into separate columns with sum of Count
pivoted_df = df.pivot_table(
    index=["Department", "Age", "Salary"],  # Group by all non-Status/Count columns
    columns="Status",  # Split Status into individual columns
    values="Count",  # Column to aggregate
    aggfunc="sum",  # Aggregation type: sum
    fill_value=0  # Fill missing values with 0 (optional but clean)
).reset_index()  # Convert index columns back to regular columns

Method 2: Use groupby + unstack (For Advanced Users) #

If you prefer working with groupby operations, this achieves the same result:

# Group by all relevant columns, sum Count, then unstack Status into columns
grouped_df = df.groupby(["Department", "Age", "Salary", "Status"])["Count"].sum().unstack(fill_value=0).reset_index()

Step 3: View the Result #

Both methods will give you this cleaned output:

Department Age Salary Junior Senior
0 IT 21-25 46K-50K 20 0
1 IT 26-30 46K-50K 3 0
2 IT 31-35 66K-70K 0 5
3 IT 41-45 66K-70K 0 3
4 Marketing 36-40 46K-50K 0 10
5 Sales 26-30 26K-30K 40 0
6 Sales 31-35 31K-35K 40 0
7 Sales 31-35 46K-50K 0 30

Key Notes #

• If you have additional columns to group by, just add them to the index list in pivot_table or the groupby arguments.
• If Status has more unique values (like "Mid-level"), the code will automatically create new columns for each—no extra work needed!
• The fill_value=0 ensures that any group without a specific status gets a 0 instead of NaN, which is usually more useful for reporting.

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