基于GroupBy与值掩码的Pandas数据筛选：年度销量均值以上区域查询

Got it, let's work through this problem step by step to get the exact results you need! First, a quick heads-up on your current code: group-by should be groupby (no hyphen), and make sure your column names match your dataset exactly—you mentioned the sales field is number of item sold, so we’ll stick with that instead of the shorthand item sold.

Step 1: Calculate Regional Annual Sales Mean #

First, we’ll group the data by both year and region to get the average sales for each region in every year:

# Compute mean sales per region per year, then reset index to turn groups into columns
region_year_avg = history.groupby(['year', 'region'])['number of item sold'].mean().reset_index()
# Rename the mean column for clearer reference later
region_year_avg.rename(columns={'number of item sold': 'region_avg_sales'}, inplace=True)

Step 2: Calculate Annual Overall Sales Mean #

Next, we need the baseline: the average sales across all regions for each individual year:

# Compute overall mean sales for each year
year_overall_avg = history.groupby('year')['number of item sold'].mean().reset_index()
year_overall_avg.rename(columns={'number of item sold': 'year_overall_avg'}, inplace=True)

Step 3: Merge Data and Filter Results #

Now we’ll combine these two datasets, then filter to keep only regions where their annual mean sales exceed the year’s overall average:

# Merge the two dataframes on 'year' to align regional and annual averages
combined_data = region_year_avg.merge(year_overall_avg, on='year', how='left')

# Filter for regions where their average sales beat the annual overall average
filtered_regions = combined_data[combined_data['region_avg_sales'] > combined_data['year_overall_avg']]

Bonus: More Efficient Concise Approach #

If you prefer a tighter workflow, use transform to add the annual average directly to the original dataset, then deduplicate (since transform applies the value to every row):

# Add regional annual average and annual overall average as new columns
history['region_avg_sales'] = history.groupby(['year', 'region'])['number of item sold'].transform('mean')
history['year_overall_avg'] = history.groupby('year')['number of item sold'].transform('mean')

# Filter and deduplicate to get unique year-region pairs that meet the condition
filtered_regions = history[history['region_avg_sales'] > history['year_overall_avg']][['year', 'region', 'region_avg_sales']].drop_duplicates()

Either method will give you a clean list of regions per year where their average sales outperformed the annual overall average.

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