数据分析实战——利润下滑诊断

背景

某个食品商家2018年到8月为止的利润同比下滑了许多，请你利用所学知识找出利润下滑的原因。

加载商品销售明细表（事实表）

import numpy as np
import pandas as pd
import matplotlib.pyplot as plt
plt.rcParams['font.sans-serif'].insert(0, 'SimHei')
plt.rcParams['axes.unicode_minus'] = False

%config InlineBackend.figure_format = 'svg'

# 加载事实表
sales_df = pd.read_excel('profits/商品销售明细表.xlsx')
#查看该表的基本信息
sales_df.info()

<class 'pandas.core.frame.DataFrame'>
RangeIndex: 40514 entries, 0 to 40513
Data columns (total 7 columns):
 #   Column   Non-Null Count  Dtype  
---  ------   --------------  -----  
 0   日期(年月日)  40514 non-null  object 
 1   门店编码     40514 non-null  object 
 2   单据编码     40514 non-null  object 
 3   商品编码     40514 non-null  object 
 4   成本额      40514 non-null  float64
 5   数量       40514 non-null  int64  
 6   销售额      40514 non-null  float64
dtypes: float64(2), int64(1), object(4)
memory usage: 2.2+ MB

从该结果可以看出商品销售明细表中共有40514行数据，7个字段。其中日期(年月日) 、  门店编码 、  单据编码 、  商品编码4个字段是object类型，成本额和销售字段为float类型，数量字段为int类型。大家从中发现什么问题了吗？

没错日期（年月日）字段应该为datetime类型，而在这里却是object类型，那么我们接下来要将日期（年月日）字段类型转换成datetime类型。

数据预处理

# 数据预处理
#新增一列datetime类型的‘日期列’
sales_df['日期'] = pd.to_datetime(sales_df['日期(年月日)'])
#删除原有的日期（年月日）列
sales_df.drop(columns='日期(年月日)', inplace=True)
sales_df

 

既然我们要找出利润下滑的原因，那么应该先算出毛利润（为什么是毛利润呢，在这里我不过多赘述，感兴趣的自行查找）。

毛利润 = 销售额 - 成本额

这里我添加了一列‘月份’列方便后续处理，使用的是pandas库高级接口dt使用方法，我的文章中有相关使用说明，感兴趣的可以去看看。

# 添加“毛利润”和“月份”列
sales_df['毛利润'] = sales_df.销售额 - sales_df.成本额
sales_df['月份'] = sales_df.日期.dt.month
sales_df

接下来我们看看是那个月的毛利润下滑的严重，将表中毛利润和销售额按月份统计出来，我这里用到的是透视表pivot_table，将月份作为索引对销售额和毛利润进行求和。

# 统计每月的销售额和毛利润
temp_df = sales_df.pivot_table(
    index='月份',
    values=['销售额', '毛利润'],
    aggfunc='sum'
)
temp_df

 现在我们把每个月的毛利润和销售额求出来了，但是这样不方便我们观察，所以我们画一个折线图就能很好的看出到底是哪个月的问题了。

# 绘制折线图
temp_df.plot(kind='line', figsize=(8, 4))
plt.ylim(0, 14000000)
plt.show()

 现在我们可以看到，销售额和毛利润整体呈一个上升趋势，但是在7月份的时候销售额明明上升的很快，但是毛利润却是在下降，这到底是怎么回事呢？我们这里推断应该是成本控制处了问题，但是仅仅靠现有的数据还不足以找出具体原因，所有我们需要将商品销售明细表、门店信息维度表和商品信息温度表连接起来。

加载商品维度表

# 加载商品维度表
products_df = pd.read_excel('profits/商品信息维度表.xlsx')
products_df

 加载门店维度表

# 加载门店维度表
stores_df = pd.read_excel('profits/门店信息维度表.xlsx')
stores_df

连接事实表和维度表

# 将事实表和维度表连接起来（通过不同的维度去拆解数据）
df = pd.merge(pd.merge(sales_df, products_df, how='inner', on='商品编码'), stores_df, how='inner', on='门店编码')
df

数据分析

上面我们已经看到了利润下滑，那么现在我们要到哪个省、哪个城市、哪个门店、哪个商品类别、哪个商品的成本控制出现了问题。

按省份将销售额和成本额用聚合函数agg()进行求和，并用nlargest()函数选出成本额最大的10个省份。

# 找到哪个省的成本控制出现了问题
temp_df = df.groupby('省份')[['销售额', '成本额']].agg({
    '销售额': 'sum',
    '成本额': 'sum',
}).nlargest(10, '成本额')
temp_df

 

随后求出各个省份的毛利润率

毛利润率 = （销售额 - 成本额）/ 销售额

((temp_df.销售额 - temp_df.成本额) / temp_df.销售额).sort_values()

 

现在我们可以看到湖南省的毛利润率低于其它省份，所以我们大胆猜测是湖南省的成本控制出了问题，接下来我们便向湖南省下手看看是哪个城市的成本控制出现了问题，还是同样的套路……

我们利用query()函数筛选出湖南省，然后按湖南省的城市对销售额和成本额进行求和，对成本额进行升序处理。

# 找到哪个城市的成本控制出现了问题
temp_df = df.query('省份 == "湖南省"').groupby('城市')[['销售额', '成本额']].sum().sort_values('成本额', ascending=False)
temp_df

 

 求出各个城市的毛利润率

((temp_df.销售额 - temp_df.成本额) / temp_df.销售额).sort_values()

 

现在我们看到长沙市的毛利润率远低于其它城市，那么它的成本控制一定是有问题的，接下来我们继续深究是哪个门店成本控制出了问题。

# 找到哪个门店的成本控制出现了问题
temp_df = df.query(
    '省份 == "湖南省" and 城市 == "长沙市"'
).groupby('门店名称')[['销售额', '成本额']].sum().sort_values('成本额', ascending=False)
temp_df

 

接下来便是重复操作，这里我就不再文字赘述了。

((temp_df.销售额 - temp_df.成本额) / temp_df.销售额).sort_values()

 

# 找到哪个商品品类的成本控制出现了问题
temp_df = df.query(
    '门店名称 == "长沙梅溪湖店"'
).groupby('商品类别')[['销售额', '成本额']].sum().sort_values('成本额', ascending=False)
temp_df

 

((temp_df.销售额 - temp_df.成本额) / temp_df.销售额).sort_values()

 

# 找到哪个商品的成本控制出现了问题
temp_df = df.query(
    '门店名称 == "长沙梅溪湖店" and 商品类别 == "零食"'
).groupby('商品名称')[['销售额', '成本额']].sum().sort_values('成本额', ascending=False)
temp_df

 

((temp_df.销售额 - temp_df.成本额) / temp_df.销售额).sort_values()

 

至此我们得到是湖南省长沙市长沙梅溪湖店中零食品类中的德芙巧克力的成本控制出现了问题，现在我们找出了利润下滑的原因，接下来解决问题就需要去对应门店去了解详情了。