项目描述
本文是针对连锁超市利润下滑进行的数据分析报告,利润下滑可能是利润持续下降、销售额减少、成本增加等问题。通过对数据的销售额 、成本额,利润额等数据分析和趋势的变化进行观察,找出导致利润下滑的主要原因。
数据获取
# 导入数据分析需要使用的库
import numpy as np
import pandas as pd
import matplotlib.pyplot as pltplt.rcParams['font.sans-serif'].insert(0, 'SimHei')
plt.rcParams['axes.unicode_minus'] = False
get_ipython().run_line_magic('config', "InlineBackend.figure_format = 'svg'")# 加载数据
detail_df = pd.read_excel('连锁超市的利润下滑分析/商品销售明细表.xlsx')
outlet_df = pd.read_excel('连锁超市的利润下滑分析/门店信息维度表.xlsx')
commod_df = pd.read_excel('连锁超市的利润下滑分析/商品信息维度表.xlsx')print(detail_df)
print(outlet_df)
print(commod_df)
日期(年月日) 门店编码 单据编码 \ 0 2018-01-01 D010112 SMDBJ18000010 1 2018-01-01 D010114 SMDBJ18000014 2 2018-01-01 D010114 SMDBJ18000014 3 2018-01-01 D010115 SMDBJ18000015 4 2018-01-01 D010115 SMDBJ18000015 ... ... ... ... 40509 2018-08-17 D010284 SMDCD18008257 40510 2018-08-17 D010284 SMDCD18008258 40511 2018-08-17 D010284 SMDCD18008259 40512 2018-08-17 D010284 SMDCD18008260 40513 2018-08-17 D010284 SMDCD18008261 商品编码 成本额 数量 销售额 0 BC639DE8-B503-437C-9B6B-F0B598052A65 641.03 30 1076.92 1 62CF9CFA-1E86-4960-B7CA-F077A8BDD5A6 998.56 120 2461.54 2 BE2DA7F0-1E24-4729-BED3-3CDC0A2E4918 1033.47 100 3119.66 3 62CF9CFA-1E86-4960-B7CA-F077A8BDD5A6 1664.27 200 4102.56 4 AE451BC0-8615-4F8C-8126-05E0C01DDF24 950.36 100 2948.72 ... ... ... ... ... 40509 0A88662D-AEF2-4756-BA9A-C784B777604E 18000.17 1800 15000.00 40510 0A88662D-AEF2-4756-BA9A-C784B777604E 18000.17 1800 15000.00 40511 0A88662D-AEF2-4756-BA9A-C784B777604E 18000.17 1800 15000.00 40512 0A88662D-AEF2-4756-BA9A-C784B777604E 18000.17 1800 15000.00 40513 0A88662D-AEF2-4756-BA9A-C784B777604E 18000.17 1800 15000.00 [40514 rows x 7 columns] 城市 门店编码 门店名称 省份 0 亳州市 D010270 亳州店 安徽省 1 宣城市 D010342 宣城店 安徽省 2 芜湖市 D010244 芜湖店 安徽省 3 铜陵市 D010349 铜陵店 安徽省 4 宿州市 D010252 宿州店 安徽省 .. ... ... ... ... 306 丽水市 D010256 丽水店 浙江省 307 金华市 D010261 金华店 浙江省 308 嘉兴市 D010242 嘉兴店 浙江省 309 湖州市 D010227 湖州店 浙江省 310 重庆市 D010175 重庆店 重庆市 [311 rows x 4 columns] 商品编码 商品类别 商品名称 0 47DFCF0A-87A5-42BD-BCAC-E8CD8A960110 饮料 纯悦550ml矿物质水 1 A3E1185B-5B4E-497E-B5B6-E667307780F4 饮料 罐装雪碧280ml装 2 8C56F4E3-B51B-44B7-8080-21E4507A57F0 调料 太太乐40g鸡精调味料 3 6B38CB04-702F-4F3B-9DF9-DD1CDC521C88 调料 多力葵花油500ml 4 AE451BC0-8615-4F8C-8126-05E0C01DDF24 零食 丽芝士散装系列 5 BE2DA7F0-1E24-4729-BED3-3CDC0A2E4918 零食 嘉士利115g威化饼 6 62CF9CFA-1E86-4960-B7CA-F077A8BDD5A6 零食 三全960g奶香馒头 7 CBB40A41-178E-44F7-BD5E-9ECD8B73978A 零食 格力高55g草莓味百奇 8 5B7ECC92-0FCC-4F40-98FF-82126D87E80C 零食 金冠森香颂夹心蛋糕系列 9 7D6DCE09-0F1F-4052-B28A-D7399DE9DF19 零食 盐津铺子散装系列 10 77DA67A0-ED8C-4FB3-8E04-E9EA7DD96BE8 零食 养乐多100ml*5乳酸菌 11 0A88662D-AEF2-4756-BA9A-C784B777604E 零食 德芙巧克力 12 67E818C0-3E49-4010-A453-20BF466D9DF0 零食 德芙巧克力180g 13 E5EA2E2A-DC19-4D47-BF0F-F29CC467A5C0 日用品 家之寓圆形24夹晒架 14 31995BCC-72AA-413B-9B2B-A57E78AC7715 日用品 雕牌1500g洗洁精 15 BC639DE8-B503-437C-9B6B-F0B598052A65 日用品 微爽日用245mm 16 E5BBD85B-D0BE-4C5F-A712-D0723940E80C 日用品 飘柔1000ml去屑洗发露 17 59F35931-24BA-46DC-9551-6744A16FC87B 生鲜 本地小白菜 18 12EF7049-C847-4A7F-A5B4-C0BEDDADAA81 生鲜 西红柿 19 5DDCE422-6782-43D2-9AC6-0E28ABA93636 生鲜 西域香妃蜜瓜 20 40106A05-FFFA-401A-A888-7738463D8E47 生鲜 冬笋 21 9685A8C3-76FA-4EB8-B041-C6549B1B0DC5 生鲜 香菇 22 88692839-DB0E-4BA4-83BC-1545A5664BEF 生鲜 青葱
数据清洗
# 查看商品销售数据缺失值情况
detail_df.isnull().sum()
销售日期 0 门店编码 0 单据编码 0 商品编码 0 成本额 0 数量 0 销售额 0 月份 0 利润额 0 dtype: int64
可见无缺失值。
#数据类型的转换(to_datetime转换成日期类型)
detail_df.rename(columns={'日期(年月日)': '销售日期'}, inplace=True)
detail_df['销售日期'] = pd.to_datetime(detail_df.销售日期)detail_df['销售日期'].dtypes
dtype('<M8[ns]'):M8表示时间类型
#添加月份和利润额两列以便分析
detail_df['月份'] = detail_df.销售日期.dt.month
detail_df['利润额'] = detail_df.销售额 - detail_df.成本额
detail_df.info()
<class 'pandas.core.frame.DataFrame'> RangeIndex: 40514 entries, 0 to 40513 Data columns (total 9 columns): # Column Non-Null Count Dtype --- ------ -------------- ----- 0 销售日期 40514 non-null datetime64[ns] 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 7 月份 40514 non-null int32 8 利润额 40514 non-null float64 dtypes: datetime64[ns](1), float64(3), int32(1), int64(1), object(3) memory usage: 2.6+ MB
完成基础数据清洗。
数据分析
1.进行月份对比:月环比
temp_df1 = detail_df.groupby('月份')[['销售额', '利润额']].sum()
temp_df1['销售月环比'] = temp_df1.销售额.pct_change()
temp_df1['利润月环比'] = temp_df1.利润额.pct_change()
temp_df1[['销售额', '销售月环比', '利润额', '利润月环比']].style.format(
formatter={
'销售月环比': '{:.2%}',
'利润月环比': '{:.2%}',
},
na_rep='--------'
)
月份 | 销售额 | 销售月环比 | 利润额 | 利润月环比 |
---|---|---|---|---|
1 | 6500712.810000 | -------- | 3934812.980000 | -------- |
2 | 8139554.050000 | 25.21% | 4276205.680000 | 8.68% |
3 | 9501298.680000 | 16.73% | 5641122.090000 | 31.92% |
4 | 10376156.370000 | 9.21% | 6286247.840000 | 11.44% |
5 | 10757308.210000 | 3.67% | 6464626.700000 | 2.84% |
6 | 11167837.630000 | 3.82% | 6636056.760000 | 2.65% |
7 | 10822060.200000 | -3.10% | 6571467.050000 | -0.97% |
8 | 12142253.530000 | 12.20% | 5794373.630000 | -11.83% |
# 数据可视化
sales_by_month = detail_df.groupby('月份')[['销售额']].sum()
profs_by_month = detail_df.groupby('月份')[['利润额']].sum()
ax = sales_by_month.plot(kind='line', figsize=(9, 5), marker='o', color='navy', linestyle='--')
profs_by_month.plot(ax=ax, kind='line', figsize=(9, 5), marker='*', color='darkgreen', linestyle='--')
plt.ylim(0, 14000000)
plt.legend(loc='lower right')
plt.show()
由月环比可得知,其他月份基本是上升,七月份下滑一点也可以理解,但是八月份销售额在上升利润率还下降11.83%,由此可见应该重点对八月份进行分析
2.对八月份数据进行分析
# 建立表的连接
merged_df = pd.merge(detail_df, outlet_df, how='left', on='门店编码')
merged_df = pd.merge(merged_df, commod_df, how='left', on='商品编码')
# 获取八月份的数据
august_df = merged_df.query('月份 == 8')
august_df
销售日期 | 门店编码 | 单据编码 | 商品编码 | 成本额 | 数量 | 销售额 | 月份 | 利润额 | 城市 | 门店名称 | 省份 | 商品类别 | 商品名称 | |
---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|
33937 | 2018-08-01 | D010101 | SM80118080035 | 47DFCF0A-87A5-42BD-BCAC-E8CD8A960110 | 582.85 | 50 | 1077.59 | 8 | 494.74 | 泉州市 | 泉州店 | 福建省 | 饮料 | 纯悦550ml矿物质水 |
33938 | 2018-08-01 | D010101 | SM80118080035 | 31995BCC-72AA-413B-9B2B-A57E78AC7715 | 914.52 | 40 | 2068.97 | 8 | 1154.45 | 泉州市 | 泉州店 | 福建省 | 日用品 | 雕牌1500g洗洁精 |
33939 | 2018-08-01 | D010147 | SMDBJ18003457 | AE451BC0-8615-4F8C-8126-05E0C01DDF24 | 508.00 | 50 | 1487.07 | 8 | 979.07 | 大庆市 | 大庆店 | 黑龙江省 | 零食 | 丽芝士散装系列 |
33940 | 2018-08-01 | D010126 | SMDBJ18003459 | 5DDCE422-6782-43D2-9AC6-0E28ABA93636 | 1143.36 | 20 | 3413.79 | 8 | 2270.43 | 佳木斯市 | 佳木斯店 | 黑龙江省 | 生鲜 | 西域香妃蜜瓜 |
33941 | 2018-08-01 | D010126 | SMDBJ18003459 | 31995BCC-72AA-413B-9B2B-A57E78AC7715 | 368.43 | 20 | 948.28 | 8 | 579.85 | 佳木斯市 | 佳木斯店 | 黑龙江省 | 日用品 | 雕牌1500g洗洁精 |
... | ... | ... | ... | ... | ... | ... | ... | ... | ... | ... | ... | ... | ... | ... |
40509 | 2018-08-17 | D010284 | SMDCD18008257 | 0A88662D-AEF2-4756-BA9A-C784B777604E | 18000.17 | 1800 | 15000.00 | 8 | -3000.17 | 贵阳市 | 花溪店 | 贵州省 | 零食 | 德芙巧克力 |
40510 | 2018-08-17 | D010284 | SMDCD18008258 | 0A88662D-AEF2-4756-BA9A-C784B777604E | 18000.17 | 1800 | 15000.00 | 8 | -3000.17 | 贵阳市 | 花溪店 | 贵州省 | 零食 | 德芙巧克力 |
40511 | 2018-08-17 | D010284 | SMDCD18008259 | 0A88662D-AEF2-4756-BA9A-C784B777604E | 18000.17 | 1800 | 15000.00 | 8 | -3000.17 | 贵阳市 | 花溪店 | 贵州省 | 零食 | 德芙巧克力 |
40512 | 2018-08-17 | D010284 | SMDCD18008260 | 0A88662D-AEF2-4756-BA9A-C784B777604E | 18000.17 | 1800 | 15000.00 | 8 | -3000.17 | 贵阳市 | 花溪店 | 贵州省 | 零食 | 德芙巧克力 |
40513 | 2018-08-17 | D010284 | SMDCD18008261 | 0A88662D-AEF2-4756-BA9A-C784B777604E | 18000.17 | 1800 | 15000.00 | 8 | -3000.17 | 贵阳市 | 花溪店 | 贵州省 | 零食 | 德芙巧克力 |
6577 rows × 14 columns
# 获取成本额前十的省份
temp_df2 = august_df.groupby('省份')[['销售额', '成本额']].sum()
temp_df2.nlargest(10, '成本额')
省份 销售额 成本额
贵州省 2378955.90 2186000.47
广东省 2036411.63 803233.76
山东省 950153.11 415478.31
湖南省 759459.40 314052.85
上海市 587152.72 278081.99
河北省 617282.82 251300.22
福建省 558771.72 224477.37
江苏省 429447.54 196850.44
辽宁省 361684.20 176793.64
河南省 405902.37 173314.22
# 数据可视化
temp_df2.nlargest(10, '成本额').plot(kind='bar', figsize=(9, 5))
plt.show()
由销售额跟成本额比对来看,其他省份的成本额差不多在销售额一半左右,然而贵州省的成本额差不多是销售额的90%,成本额大得离谱,咱们就有足够的理由怀疑贵州省的成本控制出现了问题。
3.对贵州省的数据进行分析
# 获取贵州省的数据
temp_df3 = august_df.query('省份 == "贵州省"').groupby('城市')[['销售额', '成本额']].sum()
temp_df3['利润率'] = (temp_df3.销售额 - temp_df3.成本额) / temp_df3.销售额
temp_df3.nsmallest(3, '利润率').style.format(formatter={'利润率': '{:.2%}'})
可见,是贵阳市的成本出现了问题
#对贵州省的贵阳市进行分析
temp_df4 = august_df.query('省份 == "贵州省" and 城市 == "贵阳市"').groupby('门店名称')[['销售额', '成本额']].sum()
temp_df4['利润率'] = (temp_df4.销售额 - temp_df4.成本额) / temp_df4.销售额
temp_df4.sort_values(by='利润率').style.format(formatter={'利润率': '{:.2%}'})
可见,是花溪店的成本出现了问题,
4.对花溪店进行数据分析
# 对花溪店的数据获取
august_df = august_df.query('省份 == "贵州省" and 城市 == "贵阳市" and 门店名称 == "花溪店"')
august_df
# 对成本额跟利润额进行对比
temp_df5 = august_df.groupby('商品类别')[['销售额', '成本额']].sum()
temp_df5['利润率'] = (temp_df5.销售额 - temp_df5.成本额) / temp_df5.销售额
temp_df5.sort_values(by='利润率').style.format(formatter={'利润率': '{:.2%}'})
可见,是零食的成本出现了问题
# 对零食的成本额跟销售额进行对比
temp_df6 = august_df.query('商品类别 == "零食"').groupby('商品名称')[['销售额', '成本额']].sum()
temp_df6['利润率'] = (temp_df6.销售额 - temp_df6.成本额) / temp_df6.销售额
temp_df6.sort_values(by='利润率').style.format(formatter={'利润率': '{:.2%}'})
可见是德芙巧克力成本控制出现了问题
项目结果
最终得到的结果是贵州省贵阳市花溪店的德芙巧克力成本控制出现了问题