Python报表自动化
本小节将详细介绍如何用Python批量处理多张Excel表，从而帮助我们大幅提升做报表的效率，并基于相关数据进行案例分析。
- Python报表自动化的难点在于如何批量处理数据，而批量处理数据的核心在于“单表突破，批量循环”。先按照需求逻辑处理好一张表，再把单张表的处理逻辑用遍历的方式应用到所有的表上，最终整合成我们需要的数据报表，并将代码固化以实现自动化。
- 本章的两个案例虽然都涉及Python批量数据处理，但是各自有很强的代表性。
- 第一个案例，需求明确，需要数据分析师做的是取数和处理的工作，这样的需求，难点往往在于取数和清洗的复杂性。
- 第二个案例，需求比较模糊，需要数据分析师结合实际数据定逻辑，给建议。
报表案例1
1.1 案例背景
筛选出2023年销售额总额TOP5的品牌以及对应的销售额
- 专注于户外运动的巨头公司。公司旗下有20个品牌，每个品牌都涉及128个类目（行业）
- 做一张汇总报表，包含2023年销售总额排名前五的品牌以及对应的销售额。
业务部门的同事总共发来了128张表
-
每张表里是一个细分行业的数据
-
每张表都以月的维度，从2022年1月到2023年12月，时间跨度为2年，记录着每个品牌的日期、访客数、客单价、转化率、所属类目（细分行业）等数据
-
输入
-
日期 品牌 访客数 转化率 客单价 三级类目 详细类目 2023-12 品牌-14 9348 0.060898991 134 冰爪 专项户外运动装备&冰爪 2023-12 品牌-20 1187 0.047991053 1244 冰爪 专项户外运动装备&冰爪 2023-12 品牌-4 10433 0.049204619 97 冰爪 专项户外运动装备&冰爪 2023-12 品牌-13 4648 0.024491985 428 冰爪 专项户外运动装备&冰爪
-
-
输出
- | | 品牌 | 销售额 |
| —: | ------: | ------------: |
| 15 | 品牌-5 | 2814286376.98 |
| 8 | 品牌-17 | 2735897004.88 |
| 2 | 品牌-11 | 2617557159.86 |
| 4 | 品牌-13 | 2614862151.29 |
| 3 | 品牌-12 | 2590192538.85 |
- | | 品牌 | 销售额 |
1.2 单个表格处理
import pandas as pd
import os
导入并预览数据
name = '睡袋&睡袋.xlsx'
data_dir = '/root/data/'
df = pd.read_excel(data_dir+name,parse_dates=[0])
df.head()
Out[9]:
| 日期 | 品牌 | 访客数 | 转化率 | 客单价 | 三级类目 | 详细类目 | |
|---|---|---|---|---|---|---|---|
| 0 | 2023-12-01 | 品牌-14 | 1415240 | 0.038310 | 137 | 睡袋 | 睡袋&睡袋 |
| 1 | 2023-12-01 | 品牌-5 | 604621 | 0.051700 | 134 | 睡袋 | 睡袋&睡袋 |
| 2 | 2023-12-01 | 品牌-11 | 451442 | 0.059269 | 132 | 睡袋 | 睡袋&睡袋 |
| 3 | 2023-12-01 | 品牌-12 | 382854 | 0.054379 | 159 | 睡袋 | 睡袋&睡袋 |
| 4 | 2023-12-01 | 品牌-8 | 402202 | 0.085469 | 91 | 睡袋 | 睡袋&睡袋 |
In [7]:
df.info()
<class 'pandas.core.frame.DataFrame'>
RangeIndex: 480 entries, 0 to 479
Data columns (total 7 columns):
# Column Non-Null Count Dtype
--- ------ -------------- -----
0 日期 480 non-null datetime64[ns]
1 品牌 480 non-null object
2 访客数 480 non-null int64
3 转化率 480 non-null float64
4 客单价 480 non-null int64
5 三级类目 480 non-null object
6 详细类目 480 non-null object
dtypes: datetime64[ns](1), float64(1), int64(2), object(3)
memory usage: 26.4+ KB
In [11]:
df['日期'].unique()
Out[11]:
array(['2023-12-01T00:00:00.000000000', '2023-11-01T00:00:00.000000000',
'2023-10-01T00:00:00.000000000', '2023-09-01T00:00:00.000000000',
'2023-08-01T00:00:00.000000000', '2023-07-01T00:00:00.000000000',
'2023-06-01T00:00:00.000000000', '2023-05-01T00:00:00.000000000',
'2023-04-01T00:00:00.000000000', '2023-03-01T00:00:00.000000000',
'2023-02-01T00:00:00.000000000', '2023-01-01T00:00:00.000000000',
'2022-12-01T00:00:00.000000000', '2022-11-01T00:00:00.000000000',
'2022-10-01T00:00:00.000000000', '2022-09-01T00:00:00.000000000',
'2022-08-01T00:00:00.000000000', '2022-07-01T00:00:00.000000000',
'2022-06-01T00:00:00.000000000', '2022-05-01T00:00:00.000000000',
'2022-04-01T00:00:00.000000000', '2022-03-01T00:00:00.000000000',
'2022-02-01T00:00:00.000000000', '2022-01-01T00:00:00.000000000'],
dtype='datetime64[ns]')
- 添加一列年份， 用于区分23年和22年
df['年份'] = df['日期'].dt.year
In [13]:
df
Out[13]:
| 日期 | 品牌 | 访客数 | 转化率 | 客单价 | 三级类目 | 详细类目 | 年份 | |
|---|---|---|---|---|---|---|---|---|
| 0 | 2023-12-01 | 品牌-14 | 1415240 | 0.038310 | 137 | 睡袋 | 睡袋&睡袋 | 2023 |
| 1 | 2023-12-01 | 品牌-5 | 604621 | 0.051700 | 134 | 睡袋 | 睡袋&睡袋 | 2023 |
| 2 | 2023-12-01 | 品牌-11 | 451442 | 0.059269 | 132 | 睡袋 | 睡袋&睡袋 | 2023 |
| 3 | 2023-12-01 | 品牌-12 | 382854 | 0.054379 | 159 | 睡袋 | 睡袋&睡袋 | 2023 |
| 4 | 2023-12-01 | 品牌-8 | 402202 | 0.085469 | 91 | 睡袋 | 睡袋&睡袋 | 2023 |
| … | … | … | … | … | … | … | … | … |
| 475 | 2022-01-01 | 品牌-9 | 78719 | 0.014493 | 404 | 睡袋 | 睡袋&睡袋 | 2022 |
| 476 | 2022-01-01 | 品牌-12 | 115505 | 0.029425 | 131 | 睡袋 | 睡袋&睡袋 | 2022 |
| 477 | 2022-01-01 | 品牌-13 | 103721 | 0.040405 | 102 | 睡袋 | 睡袋&睡袋 | 2022 |
| 478 | 2022-01-01 | 品牌-15 | 104182 | 0.046553 | 87 | 睡袋 | 睡袋&睡袋 | 2022 |
| 479 | 2022-01-01 | 品牌-8 | 76958 | 0.040844 | 133 | 睡袋 | 睡袋&睡袋 | 2022 |
480 rows × 8 columns
- 筛选2023年的数据
df_23 = df[df['年份']==2023]
- 计算销售额
df_23['销售额'] = df_23['访客数'] * df_23['转化率'] * df_23['客单价']
df_23.head()
Out[16]:
| 日期 | 品牌 | 访客数 | 转化率 | 客单价 | 三级类目 | 详细类目 | 年份 | 销售额 | |
|---|---|---|---|---|---|---|---|---|---|
| 0 | 2023-12-01 | 品牌-14 | 1415240 | 0.038310 | 137 | 睡袋 | 睡袋&睡袋 | 2023 | 7.427752e+06 |
| 1 | 2023-12-01 | 品牌-5 | 604621 | 0.051700 | 134 | 睡袋 | 睡袋&睡袋 | 2023 | 4.188725e+06 |
| 2 | 2023-12-01 | 品牌-11 | 451442 | 0.059269 | 132 | 睡袋 | 睡袋&睡袋 | 2023 | 3.531869e+06 |
| 3 | 2023-12-01 | 品牌-12 | 382854 | 0.054379 | 159 | 睡袋 | 睡袋&睡袋 | 2023 | 3.310229e+06 |
| 4 | 2023-12-01 | 品牌-8 | 402202 | 0.085469 | 91 | 睡袋 | 睡袋&睡袋 | 2023 | 3.128186e+06 |
set_option（），让销售额正常显示，否则销售额会因为数值过大而显示科学记数法
pd.set_option('display.float_format', lambda x: '%.2f' % x)
df_sum = df_23.groupby('品牌')['销售额'].sum().reset_index()
df_sum.head()
Out[17]:
| 品牌 | 销售额 | |
|---|---|---|
| 0 | 品牌-1 | 30744687.44 |
| 1 | 品牌-10 | 23247058.49 |
| 2 | 品牌-11 | 25877219.51 |
| 3 | 品牌-12 | 26974520.31 |
| 4 | 品牌-13 | 29096557.91 |
In [18]:
df_sum['行业'] = name.replace('.xlsx','')
df_sum.head()
Out[18]:
| 品牌 | 销售额 | 行业 | |
|---|---|---|---|
| 0 | 品牌-1 | 30744687.44 | 睡袋&睡袋 |
| 1 | 品牌-10 | 23247058.49 | 睡袋&睡袋 |
| 2 | 品牌-11 | 25877219.51 | 睡袋&睡袋 |
| 3 | 品牌-12 | 26974520.31 | 睡袋&睡袋 |
| 4 | 品牌-13 | 29096557.91 | 睡袋&睡袋 |
1.3 批量处理
import time
#开始时间
start = time.time()
#存储汇总的结果
result = pd.DataFrame()
#循环遍历表格名称
for name in os.listdir(data_dir):
df = pd.read_excel(data_dir+name,parse_dates=[0])
df['年份'] = df['日期'].dt.year
#筛选日期为2023年的数据
df_23 = df[df['年份']==2023]
#计算销售额字段
df_23['销售额'] = df_23['访客数'] * df_23['转化率'] * df_23['客单价']
#按品牌对细分行业销售额进行汇总
df_sum = df_23.groupby('品牌')['销售额'].sum().reset_index()
df_sum['类目'] = name.replace('.xlsx','')
result = pd.concat([result,df_sum])
#对最终结果按销售额进行排序
final = result.groupby('品牌')['销售额'].sum().reset_index().sort_values('销售额',ascending = False)
#结束时间
end = time.time()
print('操作花费时间：{} s'.format(end-start))
用Python操作所花费时间：5.897027254104614 s
- 查看前5名
final.head()
Out[21]:
| 品牌 | 销售额 | |
|---|---|---|
| 15 | 品牌-5 | 2814286376.98 |
| 8 | 品牌-17 | 2735897004.88 |
| 2 | 品牌-11 | 2617557159.86 |
| 4 | 品牌-13 | 2614862151.29 |
| 3 | 品牌-12 | 2590192538.85 |
2 案例二: 批量处理与品牌投放分析
2.1 新的需求背景
目标：找到近一年（2023年）投放效果还不错的品牌，进行分析
2.2 数据预览
import pandas as pd
import os
dir_path = 'data/ad_data/'
data = pd.read_excel(dir_path+'2023-12.xlsx')
print(data.info())
data.head()
<class 'pandas.core.frame.DataFrame'>
RangeIndex: 50 entries, 0 to 49
Data columns (total 4 columns):
# Column Non-Null Count Dtype
--- ------ -------------- -----
0 品牌 50 non-null object
1 品牌搜索人数 50 non-null int64
2 点击人数 50 non-null int64
3 支付人数 50 non-null int64
dtypes: int64(3), object(1)
memory usage: 1.7+ KB
None
Out[14]:
| 品牌 | 品牌搜索人数 | 点击人数 | 支付人数 | |
|---|---|---|---|---|
| 0 | 七喜 | 6896 | 3841 | 140 |
| 1 | 万迅 | 6394 | 3014 | 83 |
| 2 | 东方 | 16453 | 11114 | 1445 |
| 3 | 九方 | 53371 | 36682 | 942 |
| 4 | 佳禾 | 52686 | 28308 | 818 |
2.3 分析思路
漏斗模型分析
漏斗分析模型是企业实现精细化运营的重要分析模型，其精细化程度影响着营销管理的成败。
-
什么是漏斗分析
- 漏斗分析是一套流程分析，它能够科学反映用户行为状态以及从起点到终点各阶段用户转化率情况的重要分析模型
- 漏斗分析模型广泛应用于流量监控、产品目标转化等日常数据运营工作中
- 直播用户从激活APP开始到刷礼物，一般的用户购物路径为
- 激活APP→注册账号→进入直播间→互动行为→刷礼物等五大阶段，漏斗能够展现出各个阶段的转化率
- 通过漏斗各环节相关数据的比较,能够直观地发现和说明问题所在，从而找到优化方向。
- 直播用户从激活APP开始到刷礼物，一般的用户购物路径为
-
漏斗分析模型的特点与价值：
-
对于业务流程相对规范、周期较长、环节较多的流程分析,能够直观地发现和说明问题所在
-
值得强调的是，漏斗分析模型并非只是简单的转化率的呈现，科学的漏斗分析模型能够实现以下价值：
-
企业可以监控用户在各个层级的转化情况，聚焦用户选购全流程中最有效转化路径；同时找到可优化的短板，提升用户体验；降低流失是运营人员的重要目标，通过不同层级的转情况，迅速定位流失环节，针对性持续分析找到可优化点，如此提升用户留存率。
-
多维度切分与呈现用户转化情况，成单瓶颈无处遁形。科学的漏斗分析能够展现转化率趋势的曲线，能帮助企业精细地捕捉用户行为变化。提升了转化分析的精度和效率,对选购流程的异常定位和策略调整效果验证有科学指导意义。
-
不同属性的用户群体漏斗比较，从差异角度窥视优化思路。漏斗对比分析是科学漏斗分析的重要一环。运营人员可以通过观察不同属性的用户群体（如新注册用户与老客户、不同渠道来源的客户）各环节转化率，各流程步骤转化率的差异对比，了解转化率最高的用户群体，分析漏斗合理性，并针对转化率异常环节进行调整。
-
-
结合当前案例， 在费用无差别的情况下：人群基数大（搜索人数），表示投放的心智效果不错，让更多用户被广告触达后，在平台主动搜相关的品牌
- 搜索-点击转化率高，代表搜索结果的精准度高，搜索后展示页面的吸引力大等
- 点击-支付转化率高，更可能受产品详情页面、活动力度等影响
2.4 数据处理
循环批量汇总
In [15]:
final = pd.DataFrame()
for name in os.listdir():
df = pd.read_excel(name)
#取文件名的年份作为日期列
df['日期'] = name[:4]
final = pd.concat([final,df])
#筛选出年份为2023年的数据，即场景下的近一年
final_last = final[final['日期']=='2023']
print('数据行数：{}'.format(len(final_last)))
final_last.head()
数据行数：600
Out[15]:
| 品牌 | 品牌搜索人数 | 点击人数 | 支付人数 | 日期 | |
|---|---|---|---|---|---|
| 0 | 七喜 | 96885 | 46896 | 4692 | 2023 |
| 1 | 万迅 | 30070 | 21386 | 4393 | 2023 |
| 2 | 东方 | 354060 | 72224 | 7544 | 2023 |
| 3 | 九方 | 244846 | 103363 | 17097 | 2023 |
| 4 | 佳禾 | 6547 | 3257 | 337 | 2023 |
品牌维度的汇总
In [16]:
gp = final_last.groupby('品牌')[['品牌搜索人数','点击人数','支付人数']].sum().reset_index()
gp = gp.sort_values('品牌搜索人数',ascending = False)
gp.head()
Out[16]:
| 品牌 | 品牌搜索人数 | 点击人数 | 支付人数 | |
|---|---|---|---|---|
| 12 | 双敏 | 1604198 | 571399 | 61244 |
| 3 | 九方 | 1552916 | 712139 | 101217 |
| 20 | 巨奥 | 1417267 | 512847 | 45790 |
| 15 | 商软 | 1002679 | 544392 | 72050 |
| 24 | 戴硕 | 920350 | 540284 | 30371 |
计算对应的搜索-点击转化率、点击-支付转化率
In [17]:
gp['搜索-点击转化率'] = gp['点击人数'] / gp['品牌搜索人数']
gp['点击-支付转化率'] = gp['支付人数'] / gp['点击人数']
gp.head()
Out[17]:
| 品牌 | 品牌搜索人数 | 点击人数 | 支付人数 | 搜索-点击转化率 | 点击-支付转化率 | |
|---|---|---|---|---|---|---|
| 12 | 双敏 | 1604198 | 571399 | 61244 | 0.36 | 0.11 |
| 3 | 九方 | 1552916 | 712139 | 101217 | 0.46 | 0.14 |
| 20 | 巨奥 | 1417267 | 512847 | 45790 | 0.36 | 0.09 |
| 15 | 商软 | 1002679 | 544392 | 72050 | 0.54 | 0.13 |
| 24 | 戴硕 | 920350 | 540284 | 30371 | 0.59 | 0.06 |
2.5 数据分析
绘图呈现
In [18]:
from matplotlib import pyplot as plt
plt.rcParams['font.sans-serif'] = ['SimHei']
# %config InlineBackend.figure_format = 'jpg' # 画图的图片格式 默认jpeg 可以改成SVG 画出来的图不能保存
#筛选出TOP15的品牌
draw_data = gp.iloc[:15,:]
#设置画布大小
plt.figure(figsize=(18,11))
#筛选对应的x，y值和标签名
x = draw_data['搜索-点击转化率'].to_list()
y = draw_data['点击-支付转化率'].to_list()
z = draw_data['品牌搜索人数']
text = draw_data['品牌'].to_list()
#绘制气泡图
plt.scatter(x, y, s = z / 400, c = x, cmap = "Reds", alpha = 0.7, edgecolors = "grey", linewidth = 1)
plt.xlabel("搜索-点击转化率")
plt.ylabel("点击-支付转化率")
plt.title("TOP15品牌搜索分布",size = 15)
plt.show()
Out[18]:
- 添加文字说明
from matplotlib import pyplot as plt
plt.rcParams['font.sans-serif'] = ['SimHei']
#筛选出TOP15的品牌
draw_data = gp.iloc[:15,:]
#设置画布大小
plt.figure(figsize=(18,11))
#筛选对应的x，y值和标签名
x = draw_data['搜索-点击转化率'].to_list()
y = draw_data['点击-支付转化率'].to_list()
z = draw_data['品牌搜索人数']
text = draw_data['品牌'].to_list()
#绘制气泡图
plt.scatter(x, y, s = z / 400, c = x, cmap = "Reds", alpha = 0.7, edgecolors = "grey", linewidth = 1)
#为每个值打上对应品牌名
for i,txt in enumerate(text):
plt.text(x=x[i], y=y[i], s=txt, size=18, horizontalalignment='center', verticalalignment='center')
plt.xlabel("搜索-点击转化率")
plt.ylabel("点击-支付转化率")
plt.title("TOP15品牌搜索分布",size = 15)
plt.show()
因为分析背景是无差别投放，所以搜索人数重要性非常高。为了使可视化结果简洁清晰，我们只筛选TOP15品牌来绘图，气泡大小代表着品牌搜索人数的量级。
根据气泡图，我们按照搜索-点击转化率和点击-支付转化率的高低划分了4个区间
- 右上角：高搜索-点击转化率，高点击-支付转化率
- 左上角：低搜索-点击转化率，高点击-支付转化率
- 左下角：低搜索-点击转化率，低点击-支付转化率
- 右下角的：高搜索-点击转化率，低点击-支付转化率
添加分割线
from matplotlib import pyplot as plt
plt.rcParams['font.sans-serif'] = ['SimHei']
#筛选出TOP15的品牌
draw_data = gp.iloc[:15,:]
#设置画布大小
plt.figure(figsize=(18,11))
#筛选对应的x，y值和标签名
x = draw_data['搜索-点击转化率'].to_list()
y = draw_data['点击-支付转化率'].to_list()
z = draw_data['品牌搜索人数']
text = draw_data['品牌'].to_list()
#绘制气泡图
plt.scatter(x, y, s = z / 400, c = x, cmap = "Reds", alpha = 0.7, edgecolors = "grey", linewidth = 1)
#为每个值打上对应品牌名
for i,txt in enumerate(text):
plt.text(x=x[i], y=y[i], s=txt, size=18, horizontalalignment='center', verticalalignment='center')
# 添加黑色虚线水平辅助线
plt.axhline(y=0.12 ,color='black', linestyle='--',alpha = 0.5)
# 添加黑色虚线垂直辅助线
plt.axvline(x=0.465,color='black', linestyle='--',alpha = 0.5)
plt.xlabel("搜索-点击转化率")
plt.ylabel("点击-支付转化率")
plt.title("TOP15品牌搜索分布",size = 15)
plt.show()
小结
在本章中，我们通过Python实现了对Excel数据的批量处理和报表自动化，有效地提高了数据分析的效率和准确性。通过两个具体的案例，我们展示了如何从多张Excel表中提取和汇总数据，进而分析出行业内品牌的销售表现和广告投放的效果。
-
案例1：销售额分析
我们处理了128张细分行业的表格，提取了2023年的数据，并计算出了各品牌的年销售额，最终确定了销售额排名前五的品牌。这个过程体现了从单个表格的处理到批量处理的流程，强调了自动化处理数据在实际业务中的应用。 -
案例2：广告投放分析
通过分析广告数据，我们使用了漏斗模型来观察品牌的市场表现，特别是在转化率方面的表现。分析了品牌搜索人数、点击人数和支付人数，从而评估了广告投放的效果，并通过数据可视化直观地展示了各品牌的表现。
(“点击-支付转化率”)
plt.title(“TOP15品牌搜索分布”,size = 15)
plt.show()
[外链图片转存中...(img-8dF1c5gV-1728282640962)]
[外链图片转存中...(img-S8ea7zf2-1728282640963)]
### 小结
在本章中，我们通过Python实现了对Excel数据的批量处理和报表自动化，有效地提高了数据分析的效率和准确性。通过两个具体的案例，我们展示了如何从多张Excel表中提取和汇总数据，进而分析出行业内品牌的销售表现和广告投放的效果。
1. **案例1：销售额分析**
我们处理了128张细分行业的表格，提取了2023年的数据，并计算出了各品牌的年销售额，最终确定了销售额排名前五的品牌。这个过程体现了从单个表格的处理到批量处理的流程，强调了自动化处理数据在实际业务中的应用。
2. **案例2：广告投放分析**
通过分析广告数据，我们使用了漏斗模型来观察品牌的市场表现，特别是在转化率方面的表现。分析了品牌搜索人数、点击人数和支付人数，从而评估了广告投放的效果，并通过数据可视化直观地展示了各品牌的表现。
扫一扫
1922
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
