Python pandas：如何按Year与Status聚合生成指定多列统计结果（解决value_counts输出不符合预期问题）

Your current code creates a redundant multi-index because you’re grouping by Status and then calling value_counts() on the same column—this repeats the Status level unnecessarily. Instead, we need to count occurrences per year and status, then reshape the data into the wide format you want.

Here’s a step-by-step solution:

Step 1: Count occurrences per Year and Status #

First, use groupby with size() to get the count of each status for every year. This gives a Series with a multi-index (Year, Status):

import pandas as pd
data = pd.DataFrame({"Year": [1982, 1983, 1982, 1983, 1984, 1982], "Status": ["Yes", "No", "Yes", "Yes", "No", "No"]})

# Count occurrences per Year and Status
counts = data.groupby(['Year', 'Status']).size()

Step 2: Reshape to wide format #

Use unstack() to pivot the Status index level into columns. We’ll fill missing values (like 1984’s "Yes" count) with 0 to ensure every year has both status counts:

wide_counts = counts.unstack(fill_value=0)

Step 3: Clean up and format the DataFrame #

Reset the index to make Year a column, rename columns to match your desired output, and add static status label columns:

# Reset index and rename count columns
evo = wide_counts.reset_index().rename(columns={'Yes': 'Count_yes', 'No': 'Count_no'})

# Add static status label columns
evo['Status_yes'] = 'yes'
evo['Status_no'] = 'no'

# Reorder columns to match your expected output
evo = evo[['Year', 'Status_yes', 'Count_yes', 'Status_no', 'Count_no']]

Final Output #

Running this code will give you exactly the DataFrame you want:

Year Status_yes  Count_yes Status_no  Count_no
0  1982        yes          2        no         1
1  1983        yes          1        no         1
2  1984        yes          0        no         1

Alternative: Using pivot_table #

You can also achieve this in a more concise way with pivot_table, which is built for this kind of reshaping:

evo = pd.pivot_table(
    data,
    index='Year',
    columns='Status',
    aggfunc='size',
    fill_value=0
).reset_index().rename(columns={'Yes': 'Count_yes', 'No': 'Count_no'})

evo['Status_yes'] = 'yes'
evo['Status_no'] = 'no'
evo = evo[['Year', 'Status_yes', 'Count_yes', 'Status_no', 'Count_no']]

This DataFrame is now ready for your plotting needs!

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