如何计算门店年度销售额差异并识别新增与关闭门店？

首先，先回顾下你已经整理好的基础数据：

1. 2016-2018年各门店总销售额原始统计 #

Store_Key Year count Total_Sales 
0 5.0 2016 28 6150.0 
1 5.0 2017 39 8350.0 
2 5.0 2018 27 5150.0 
3 7.0 2016 3664 105370.0 
4 7.0 2017 3736 116334.0 
5 7.0 2018 3863 99375.0 
6 10.0 2016 3930 79904.0 
7 10.0 2017 3981 91227.0 
8 10.0 2018 4432 97226.0 
9 11.0 2016 4084 91156.0 
10 11.0 2017 4220 99565.0 
11 11.0 2018 4735 113584.0 
12 16.0 2016 4257 135655.0 
13 16.0 2017 4422 144725.0 
14 16.0 2018 4630 133820.0 

2. 透视后的年度销售额数据 #

Store_Key 2016 2017 2018 
5.0 6150.0 8350.0 5150.0 
7.0 105370.0 116334.0 99375.0 
10.0 79904.0 91227.0 97226.0 
11.0 91156.0 99565.0 113584.0 
16.0 135655.0 144725.0 133820.0 
18.0 237809.0 245645.0 88167.0 
20.0 110225.0 131999.0 83302.0 
24.0 94087.0 101062.0 108888.0 

3. 销售额差异计算与门店状态识别的优化方案 #

针对你提到的门店每年有大量新增和关闭的情况，除了识别NULL值，我推荐以下更系统的方案，既能计算销售额差异，又能清晰标记门店状态：

3.1 先构建全量门店-年度维度并标记活跃状态 #

与其依赖透视表的NULL值，不如从原始数据出发，先生成所有门店与所有年份的全量组合，再标记每个门店在对应年份的活跃状态。用Python pandas实现的话，代码如下：

import pandas as pd

# 假设原始数据存储在df变量中
df['is_active'] = 1  # 标记该年度门店处于活跃状态

# 生成所有门店-年度的全量组合
all_store_year_pairs = pd.MultiIndex.from_product(
    [df['Store_Key'].unique(), df['Year'].unique()],
    names=['Store_Key', 'Year']
).to_frame(index=False)

# 左连接原始数据，填充缺失值得到完整数据集
full_dataset = all_store_year_pairs.merge(df, on=['Store_Key', 'Year'], how='left')
full_dataset['is_active'] = full_dataset['is_active'].fillna(0)

这样full_dataset里就包含了所有门店在每一年的状态：is_active=1表示当年门店正常营业，0表示当年门店未开业或已关闭。

3.2 计算销售额差异并标记门店状态 #

接下来按门店分组，用shift函数获取上一年销售额，同时通过状态字段标记门店类型：

# 按门店编号和年份排序，确保时间顺序正确
full_dataset = full_dataset.sort_values(['Store_Key', 'Year'])

# 获取上一年度销售额，计算年度差异
full_dataset['prev_year_sales'] = full_dataset.groupby('Store_Key')['Total_Sales'].shift(1)
full_dataset['sales_yearly_diff'] = full_dataset['Total_Sales'] - full_dataset['prev_year_sales']

# 定义函数标记门店状态
def label_store_status(row):
    if row['is_active'] == 1 and pd.isna(row['prev_year_sales']):
        return '新增门店'
    elif row['is_active'] == 0 and pd.isna(row['Total_Sales']):
        return '关闭门店'
    elif row['is_active'] == 1 and not pd.isna(row['prev_year_sales']):
        return '持续活跃'
    else:
        return '状态异常'

full_dataset['store_status'] = full_dataset.apply(label_store_status, axis=1)

这种方式的优势在于：

• 不依赖透视表结构，灵活适配门店数量的动态变化
• 销售额差异仅针对持续活跃的门店，结果更具分析价值
• 清晰区分新增、关闭、持续活跃三类门店，避免NULL值带来的歧义

3.3 可视化与分析展示优化 #

你可以基于处理后的数据集做以下展示：

• 单独统计各年度新增/关闭门店的数量及销售额贡献
• 对持续活跃门店，用柱状图或箱线图展示销售额差异的分布情况
• 用分组表格呈现不同状态门店的年度销售额数据，让结果更直观

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