(Adventure项目)自行车业务数据分析报告(一)
项目背景
- Adventure Works Cycles是Adventure Works样本数据库所虚构的公司,这是一家大型跨国制造公司。该公司生产和销售自行车到北美,欧洲和亚洲的商业市场。虽然其基地业务位于华盛顿州博塞尔,拥有290名员工,但几个区域销售团队遍布整个市场。
2019年11月自行车业务分析报告
目录:
- 一、自行车整体销售表现
- 二、2019年11月自行车地域销售表现
- 三、2019年11月自行车产品销售表现
- 四、用户行为分析
- 五、2019年11月热品销售分析
本文主要介绍第一部分:自行车整体销售表现,其他章节可访问本专栏Adventure自行车项目,建议订阅收藏
- 自行车整体销售表现:pt_overall_sale_performance_1
- 2019年11月自行车地域销售表现:pt_bicy_november_territory_2、pt_bicy_november_october_city_3
- 2019年11月自行车产品销售表现:pt_bicycle_product_sales_month_4、pt_bicycle_product_sales_order_month_4、pt_bicycle_product_sales_order_month_11
- 用户行为分析:pt_user_behavior_november
- 2019年11月热品销售分析:pt_hot_products_november
#导入模块
import pandas as pd
import numpy as np
import pymysql
pymysql.install_as_MySQLdb()
import sqlalchemy
一、自行车整体销售表现
1.1、从数据库读取源数据:dw_customer_order
#读取源数据。不同城市,每天产品销售信息
#创建数据库引擎
engine = sqlalchemy.create_engine('mysql+pymysql://ID:*****@localhost:3306/database?charset=gbk')
sql_query='select * from dw_customer_order'
gather_customer_order=pd.read_sql_query(sql=sql_query,con=engine)
#查看源数据前5行,观察数据,判断数据是否正常识别
gather_customer_order.head()
create_date | product_name | cpzl_zw | cplb_zw | order_num | customer_num | sum_amount | is_current_year | is_last_year | is_yesterday | is_today | is_current_month | is_current_quarter | chinese_province | chinese_city | chinese_territory | |
---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|
0 | 2019-01-02 | AWC Logo Cap | 帽子 | 服装 | 1 | 1 | 8.99 | 0 | 0 | 0 | 0 | 0 | 0 | 直辖市 | 潍坊市 | 华东 |
1 | 2019-01-02 | AWC Logo Cap | 帽子 | 服装 | 1 | 1 | 8.99 | 0 | 0 | 0 | 0 | 0 | 0 | 福建省 | 三明市 | 华东 |
2 | 2019-01-02 | AWC Logo Cap | 帽子 | 服装 | 1 | 1 | 8.99 | 0 | 0 | 0 | 0 | 0 | 0 | 福建省 | 福州市 | 华东 |
3 | 2019-01-02 | AWC Logo Cap | 帽子 | 服装 | 1 | 1 | 8.99 | 0 | 0 | 0 | 0 | 0 | 0 | 辽宁省 | 青岛市 | 华东 |
4 | 2019-01-02 | AWC Logo Cap | 帽子 | 服装 | 1 | 1 | 8.99 | 0 | 0 | 0 | 0 | 0 | 0 | 湖南省 | 长沙市 | 华中 |
1.1.2 查看源数据类型:dw_customer_order
gather_customer_order.info()
<class 'pandas.core.frame.DataFrame'>
RangeIndex: 518361 entries, 0 to 518360
Data columns (total 16 columns):
create_date 518361 non-null object
product_name 518361 non-null object
cpzl_zw 518361 non-null object
cplb_zw 518361 non-null object
order_num 518361 non-null int64
customer_num 518361 non-null int64
sum_amount 518361 non-null float64
is_current_year 518361 non-null object
is_last_year 518361 non-null object
is_yesterday 518361 non-null object
is_today 518361 non-null object
is_current_month 518361 non-null object
is_current_quarter 518361 non-null object
chinese_province 518361 non-null object
chinese_city 518361 non-null object
chinese_territory 518361 non-null object
dtypes: float64(1), int64(2), object(13)
memory usage: 63.3+ MB
1.1.3 利用create_date字段增加create_year_month月份字段
#增加create_year_month月份字段。按月维度分析时使用
gather_customer_order['create_year_month']=gather_customer_order.create_date.apply(lambda x:x.strftime('%Y-%m'))
gather_customer_order['create_year_month']
0 2019-01
1 2019-01
2 2019-01
3 2019-01
4 2019-01
...
518356 2021-02
518357 2021-02
518358 2021-02
518359 2021-02
518360 2021-02
Name: create_year_month, Length: 518361, dtype: object
1.1.4 筛选产品类型cplb_zw中的自行车作为新gather_customer_order
#筛选产品类别为自行车的数据
gather_customer_order = gather_customer_order.loc[gather_customer_order['cplb_zw']=='自行车']
gather_customer_order.head()
create_date | product_name | cpzl_zw | cplb_zw | order_num | customer_num | sum_amount | is_current_year | is_last_year | is_yesterday | is_today | is_current_month | is_current_quarter | chinese_province | chinese_city | chinese_territory | create_year_month | |
---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|
152 | 2019-01-02 | Mountain-100 Silver | 山地自行车 | 自行车 | 1 | 1 | 3399.9900 | 0 | 0 | 0 | 0 | 0 | 0 | 江苏省 | 盐城市 | 华东 | 2019-01 |
153 | 2019-01-02 | Mountain-200 Black | 山地自行车 | 自行车 | 1 | 1 | 2294.9900 | 0 | 0 | 0 | 0 | 0 | 0 | 海南省 | 焦作市 | 华南 | 2019-01 |
154 | 2019-01-02 | Mountain-200 Black | 山地自行车 | 自行车 | 1 | 1 | 2294.9900 | 0 | 0 | 0 | 0 | 0 | 0 | 陕西省 | 阜阳市 | 西北 | 2019-01 |
155 | 2019-01-02 | Mountain-200 Black | 山地自行车 | 自行车 | 1 | 1 | 2294.9900 | 0 | 0 | 0 | 0 | 0 | 0 | 贵州省 | 贵阳市 | 西南 | 2019-01 |
156 | 2019-01-02 | Mountain-200 Black | 山地自行车 | 自行车 | 1 | 1 | 2049.0982 | 0 | 0 | 0 | 0 | 0 | 0 | 贵州省 | 铜仁市 | 西南 | 2019-01 |
1.2、自行车整体销售量表现
1.2.1 聚合每月订单数量和销售金额,具体groupby创建一个新的对象,需要将order_num、sum_amount求和,对日期降序排序,记得重置索引
#每月订单数量和销售金额,用groupby创建一个新的对象,需要将order_num、sum_amount求和
overall_sales_performance = gather_customer_order.groupby('create_year_month').agg({'order_num':sum,
'sum_amount':sum})\
.sort_values('create_year_month',ascending=False).reset_index()
#取消科学计数法
pd.set_option('display.float_format', lambda x: '%f' % x)
#按日期降序排序,方便计算环比
overall_sales_performance.head()
create_year_month | order_num | sum_amount | |
---|---|---|---|
0 | 2021-02 | 6061 | 11348244.679600 |
1 | 2021-01 | 7925 | 14857125.340001 |
2 | 2020-12 | 7807 | 14743275.933900 |
3 | 2020-11 | 6322 | 11969145.536300 |
4 | 2020-10 | 13632 | 26025173.289898 |
1.2.2 新增一列order_num_diff,此为每月自行车销售订单量环比,本月与上月对比,例如本期2019-02月销售额与上一期2019-01月销售额做对比
你需要一步步完成。
提示:
1.利用diff()函数
2.使用列表形式方便加工
3.加工形成同样长度的列表,转为Series或DataFrame与本身数据合并
overall_sales_performance.order_num-overall_sales_performance.order_num.shift()
0 nan
1 1864.000000
2 -118.000000
3 -1485.000000
4 7310.000000
5 -491.000000
6 -5451.000000
7 13155.000000
8 -12732.000000
9 -2599.000000
10 -2222.000000
11 -228.000000
12 -139.000000
13 232.000000
14 -123.000000
15 404.000000
16 -426.000000
17 20.000000
18 204.000000
19 -167.000000
20 -73.000000
21 90.000000
22 -106.000000
23 115.000000
24 -276.000000
25 85.000000
Name: order_num, dtype: float64
overall_sales_performance.order_num.diff()
0 nan
1 1864.000000
2 -118.000000
3 -1485.000000
4 7310.000000
5 -491.000000
6 -5451.000000
7 13155.000000
8 -12732.000000
9 -2599.000000
10 -2222.000000
11 -228.000000
12 -139.000000
13 232.000000
14 -123.000000
15 404.000000
16 -426.000000
17 20.000000
18 204.000000
19 -167.000000
20 -73.000000
21 90.000000
22 -106.000000
23 115.000000
24 -276.000000
25 85.000000
Name: order_num, dtype: float64
#求每月自行车销售订单量环比,观察最近一年数据变化趋势
#环比是本月与上月的对比,例如本期2019-02月销售额与上一期2019-01月销售额做对比
order_num_diff = list(-(overall_sales_performance.order_num.diff())/overall_sales_performance.order_num)
order_num_diff.pop(0) #删除列表中第一个元素
order_num_diff.append(0) #将0新增到列表末尾
order_num_diff
[-0.235205047318612,
0.015114640707057769,
0.23489402087946853,
-0.5362382629107981,
0.03736397534434213,
0.7088426527958388,
-0.6310865915087551,
1.5693331689880439,
0.47134566557852736,
0.6749696233292831,
0.07441253263707572,
0.04752136752136752,
-0.07348748812163447,
0.04054054054054054,
-0.11751018033740547,
0.14143426294820718,
-0.006596306068601583,
-0.0630407911001236,
0.054415118931247966,
0.024365821094793058,
-0.02916396629941672,
0.03557046979865772,
-0.03715670436187399,
0.09790705924086555,
-0.029269972451790634,
0]
#将环比转化为DataFrame
overall_sales_performance = pd.concat([overall_sales_performance,pd.DataFrame({'order_num_diff':order_num_diff})],axis=1)
overall_sales_performance
create_year_month | order_num | sum_amount | order_num_diff | |
---|---|---|---|---|
0 | 2021-02 | 6061 | 11348244.679600 | -0.235205 |
1 | 2021-01 | 7925 | 14857125.340001 | 0.015115 |
2 | 2020-12 | 7807 | 14743275.933900 | 0.234894 |
3 | 2020-11 | 6322 | 11969145.536300 | -0.536238 |
4 | 2020-10 | 13632 | 26025173.289898 | 0.037364 |
5 | 2020-09 | 13141 | 24648450.671199 | 0.708843 |
6 | 2020-08 | 7690 | 14111182.146000 | -0.631087 |
7 | 2020-07 | 20845 | 38859851.609801 | 1.569333 |
8 | 2020-06 | 8113 | 15175107.905901 | 0.471346 |
9 | 2020-05 | 5514 | 10354835.838800 | 0.674970 |
10 | 2020-04 | 3292 | 6202452.052800 | 0.074413 |
11 | 2020-03 | 3064 | 5702518.206800 | 0.047521 |
12 | 2020-02 | 2925 | 5301279.160000 | -0.073487 |
13 | 2020-01 | 3157 | 5965646.807600 | 0.040541 |
14 | 2019-12 | 3034 | 5735361.733800 | -0.117510 |
15 | 2019-11 | 3438 | 6568254.970400 | 0.141434 |
16 | 2019-10 | 3012 | 5573722.429800 | -0.006596 |
17 | 2019-09 | 3032 | 5688701.623900 | -0.063041 |
18 | 2019-08 | 3236 | 6013788.693600 | 0.054415 |
19 | 2019-07 | 3069 | 5750549.244500 | 0.024366 |
20 | 2019-06 | 2996 | 5621673.571500 | -0.029164 |
21 | 2019-05 | 3086 | 5734630.462800 | 0.035570 |
22 | 2019-04 | 2980 | 5489733.524800 | -0.037157 |
23 | 2019-03 | 3095 | 5776688.528200 | 0.097907 |
24 | 2019-02 | 2819 | 5315683.034400 | -0.029270 |
25 | 2019-01 | 2904 | 5441653.458500 | 0.000000 |
1.2.3 新增一列sum_amount_diff,此为每月自行车销售金额环比,原理一样,但是所需字段不同,最后形成按照日期升序排列
#求每月自行车销售金额环比
sum_amount_diff = list(-(overall_sales_performance.sum_amount.diff())/overall_sales_performance.sum_amount)
sum_amount_diff.pop(0) #删除列表中第一个元素
sum_amount_diff.append(0) #将0新增到列表末尾
sum_amount_diff
#将环比转化为DataFrame
overall_sales_performance = pd.concat([overall_sales_performance,pd.DataFrame({'sum_amount_diff':sum_amount_diff})],axis=1)
#销量环比字段名order_diff,销售金额环比字段名amount_diff
#按照日期排序,升序
overall_sales_performance = overall_sales_performance.rename(columns={'sum_amount_diff':'amount_diff'})\
.sort_values('create_year_month')
#查看每月自行车订单量、销售金额、环比、前5行
overall_sales_performance.head(5)
create_year_month | order_num | sum_amount | order_num_diff | amount_diff | |
---|---|---|---|---|---|
25 | 2019-01 | 2904 | 5441653.458500 | 0.000000 | 0.000000 |
24 | 2019-02 | 2819 | 5315683.034400 | -0.029270 | -0.023149 |
23 | 2019-03 | 3095 | 5776688.528200 | 0.097907 | 0.086726 |
22 | 2019-04 | 2980 | 5489733.524800 | -0.037157 | -0.049675 |
21 | 2019-05 | 3086 | 5734630.462800 | 0.035570 | 0.044610 |
字段注释:
create_year_month:时间,order_num:本月累计销售数量,sum_amount:本月累计销售金额,order_diff:本月销售数量环比,
sum_amount_diff:本月销售金额环比,dw_customer_order:用户订单表
1.2.4 将最终的overall_sales_performance的DataFrame存入Mysql的pt_overall_sale_performance_1当中,请使用追加存储。
#将数据存入数据库
engine = sqlalchemy.create_engine('mysql://id:password@xxx.xx.xx.xxx;3306/db?charset=gbk')
overall_sales_performance.to_sql('name',con=engine,if_exists='replace',index=False)