如何在Excel中创建X轴为年龄分组的堆积柱形图？

Hey there! I see you're trying to build a stacked bar chart where the X-axis is age groups, Y-axis is the count of records, and each bar splits into two parts: income >$50K and <=$50K. Let's break this down step by step using Python (with pandas and matplotlib—super straightforward tools for this). If you're using another tool like Excel or Tableau, I can tweak the steps too, but let's start with code since it's easy to replicate.

Step 1: Load & Prepare Your Data #

First, let's get your sample data into a structured pandas DataFrame. Here's how to do that:

import pandas as pd

# Your sample data
data = {
    'Age': [39,50,38,53,28,37,49,52,31,42,37,30,23,32,40,34,25,32],
    'Income': ['<=50K','<=50K','<=50K','<=50K','<=50K','<=50K','<=50K','>50K','>50K','>50K','>50K','>50K','<=50K','<=50K','>50K','<=50K','<=50K','<=50K']
}

df = pd.DataFrame(data)

Step 2: Create Age Groups #

This is probably where you hit a snag! Let's define clear age bins (like 20-29, 30-39, etc.) using pandas' cut() function. You can adjust the bins if you want different groupings:

# Define age bins and labels
age_bins = [20, 29, 39, 49, 59]
age_labels = ['20-29', '30-39', '40-49', '50-59']

# Add a new column for age groups
df['Age Group'] = pd.cut(df['Age'], bins=age_bins, labels=age_labels, right=False)

The right=False makes sure bins include the lower bound (e.g., 20-29 includes 20 but not 30—adjust this if you prefer the opposite logic).

Step 3: Count Records per Age Group & Income Bracket #

Now we need to count how many people fall into each (age group, income) combination. pd.crosstab() gives us a clean table ready for plotting:

# Create a cross-tabulation of age groups vs income
count_table = pd.crosstab(df['Age Group'], df['Income'])

# Reorder columns to match your desired display order (optional but helpful)
count_table = count_table[['<=50K', '>50K']]

Step 4: Plot the Stacked Bar Chart #

Finally, use matplotlib to draw the stacked bar chart. The bottom parameter is key to stacking one bar on top of the other:

import matplotlib.pyplot as plt

# Set up the plot
fig, ax = plt.subplots(figsize=(8, 5))

# Plot the first bar segment (income <=50K)
count_table['<=50K'].plot(kind='bar', ax=ax, color='#1f77b4', label='<=50K')

# Plot the second segment (income >50K) on top of the first
count_table['>50K'].plot(kind='bar', ax=ax, color='#ff7f0e', label='>50K', bottom=count_table['<=50K'])

# Add labels and title for clarity
ax.set_xlabel('Age Group')
ax.set_ylabel('Number of Records')
ax.set_title('Income Distribution by Age Group')
ax.legend()

# Rotate x-axis labels to avoid overlap
plt.xticks(rotation=0)

# Adjust layout and show the plot
plt.tight_layout()
plt.show()

If You're Using Excel Instead: #

• Paste your data into Excel (two columns: Age, Income)
• Add a new column for Age Groups: Use the IF() function to assign groups (e.g., =IF(A2<30,"20-29",IF(A2<40,"30-39",IF(A2<50,"40-49","50-59"))))
• Go to Insert > PivotTable, drag Age Group to Rows, Income to Columns, and Age to Values (set to Count)
• Select the pivot table, then go to Insert > Bar Chart > Stacked Bar Chart

That should get you exactly the chart you need! Let me know if you run into any hiccups with specific steps.

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