（Adventure项目）自行车业务数据分析报告（二）

（Adventure项目）自行车业务数据分析报告（二）

项目背景

• Adventure Works Cycles是Adventure Works样本数据库所虚构的公司，这是一家大型跨国制造公司。该公司生产和销售自行车到北美，欧洲和亚洲的商业市场。虽然其基地业务位于华盛顿州博塞尔，拥有290名员工，但几个区域销售团队遍布整个市场。

2019年11月自行车业务分析报告

目录：

• 一、自行车整体销售表现
• 二、2019年11月自行车地域销售表现
• 三、2019年11月自行车产品销售表现
• 四、用户行为分析
• 五、2019年11月热品销售分析

本文主要介绍第二部分：2019年11月自行车地域销售表现，其他章节可访问本专栏Adventure自行车项目，建议订阅收藏

二、2019年11月自行车地域销售表现

2.1、源数据dw_customer_order，数据清洗筛选10月11月数据

#gather_customer_order在分析自行车整体表现时已从数据库导入表（dw_customer_order），并筛选仅自行车数据，这里不再导入
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.990000	0	0	0	0	0	0	江苏省	盐城市	华东	2019-01
153	2019-01-02	Mountain-200 Black	山地自行车	自行车	1	1	2294.990000	0	0	0	0	0	0	海南省	焦作市	华南	2019-01
154	2019-01-02	Mountain-200 Black	山地自行车	自行车	1	1	2294.990000	0	0	0	0	0	0	陕西省	阜阳市	西北	2019-01
155	2019-01-02	Mountain-200 Black	山地自行车	自行车	1	1	2294.990000	0	0	0	0	0	0	贵州省	贵阳市	西南	2019-01
156	2019-01-02	Mountain-200 Black	山地自行车	自行车	1	1	2049.098200	0	0	0	0	0	0	贵州省	铜仁市	西南	2019-01

2.1.1 筛选10、11月的自行车数据，赋值变量为gather_customer_order_10_11

#筛选10月11月自行车数据
gather_customer_order_10_11 = gather_customer_order[gather_customer_order['create_year_month'].isin(['2019-10','2019-11'])]

2.1.1 查看10月、11月的自行车订单数量

#10月11月自行车订单数据共6335条
len(gather_customer_order_10_11)

6335

2.2、2019年11月自行车区域销售量表现

2.2.1 请按照’chinese_territory’,‘create_year_month’，区域、月份分组，订单量求和、销售金额求和，赋予变量gather_customer_order_10_11_group，记得重置索引

#按照区域、月分组,订单量求和，销售金额求和
gather_customer_order_10_11_group= gather_customer_order_10_11.groupby(['chinese_territory','create_year_month'])\
.agg({'order_num':sum,'sum_amount':sum}).reset_index()

gather_customer_order_10_11_group.head()

	chinese_territory	create_year_month	order_num	sum_amount
0	东北	2019-10	239	433802.146000
1	东北	2019-11	255	476112.870700
2	华东	2019-10	787	1443753.658800
3	华东	2019-11	871	1651823.701100
4	华中	2019-10	470	868236.766500

2.2.2 求不同区域10月11月的环比

步骤

• 1、获得去重区域的列表region_list
• 2、利用for循环区域列表，结合loc定位符合区域
• 3、利用pct_change()函数实现环比效果
• 4、形成新的Series
• 5、利用Series带的索引，合并到gather_customer_order_10_11_group中

#将区域存为列表
region_list=list(gather_customer_order_10_11_group['chinese_territory'].unique())

region_list

['东北', '华东', '华中', '华北', '华南', '台港澳', '西北', '西南']

#pct_change()当前元素与先前元素的相差百分比,求不同区域10月11月环比
order_x = pd.Series([])
amount_x = pd.Series([])
for i in region_list:
    a=gather_customer_order_10_11_group.loc[gather_customer_order_10_11_group['chinese_territory']==i]['order_num'].pct_change().fillna(0)
    b=gather_customer_order_10_11_group.loc[gather_customer_order_10_11_group['chinese_territory']==i]['sum_amount'].pct_change().fillna(0)
    order_x=order_x.append(a)
    amount_x = amount_x.append(b)

gather_customer_order_10_11_group['order_diff']=order_x
gather_customer_order_10_11_group['amount_diff']=amount_x

#10月11月各个区域自行车销售数量、销售金额环比
gather_customer_order_10_11_group.head()

	chinese_territory	create_year_month	order_num	sum_amount	order_diff	amount_diff
0	东北	2019-10	239	433802.146000	0.000000	0.000000
1	东北	2019-11	255	476112.870700	0.066946	0.097535
2	华东	2019-10	787	1443753.658800	0.000000	0.000000
3	华东	2019-11	871	1651823.701100	0.106734	0.144117
4	华中	2019-10	470	868236.766500	0.000000	0.000000

字段注释：

chinese_territory：区域，create_year_month：时间，order_num：区域销售数量，sum_amount：区域销售金额，order_diff：本月销售数量环比，

amount_diff：本月销售金额环比

2.2.3 将最终的gather_customer_order_10_11_group的DataFrame存入Mysql的pt_bicy_november_territory_2当中，请使用追加存储。

#将数据存入数据库
engine = sqlalchemy.create_engine('mysql://ID:********@xxx.xx.xx.xxx:3306/db?charset=gbk')
gather_customer_order_10_11_group.to_sql('name',con=engine,if_exists='append',index=False)

2.3、2019年11月自行车销售量TOP10城市环比

2.3.1 筛选11月自行车交易数据 赋予变量为gather_customer_order_11

#筛选11月自行车交易数据
gather_customer_order_11 = gather_customer_order_10_11[gather_customer_order_10_11['create_year_month']=='2019-11']

2.3.2 将gather_customer_order_11按照chinese_city城市分组，求和销售数量order_num，最终查看11月自行车销售数量前十城市，赋予变量gather_customer_order_city_head

gather_customer_order_city_11= gather_customer_order_11.groupby('chinese_city')\
['order_num'].sum().reset_index()

#11月自行车销售数量前十城市
gather_customer_order_city_head = gather_customer_order_city_11.sort_values('order_num',ascending=False).head(10)

#查看11月自行车销售数量前十城市
gather_customer_order_city_head

	chinese_city	order_num
31	北京市	80
82	广州市	49
144	深圳市	45
3	上海市	45
207	郑州市	44
59	天津市	43
210	重庆市	41
121	武汉市	40
219	长沙市	37
189	西安市	36

2.3.3 根据gather_customer_order_city_head的前十城市，查看10月11月自行车销售数据gather_customer_order_10_11，赋予变量gather_customer_order_10_11_head

#筛选销售前十城市，10月11月自行车销售数据
gather_customer_order_10_11_head = gather_customer_order_10_11[gather_customer_order_10_11['chinese_city'].\
isin (list(gather_customer_order_city_head['chinese_city']))]

gather_customer_order_10_11_head.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
104581	2019-10-01	Mountain-100 Black	山地自行车	自行车	1	1	3374.990000	0	0	0	0	0	0	直辖市	重庆市	西南	2019-10
104585	2019-10-01	Mountain-200 Black	山地自行车	自行车	1	1	2049.098200	0	0	0	0	0	0	直辖市	上海市	华东	2019-10
104606	2019-10-01	Mountain-400-W Silver	山地自行车	自行车	2	2	1538.980000	0	0	0	0	0	0	广东省	广州市	华南	2019-10
104667	2019-10-01	Road-150 Red	公路自行车	自行车	1	1	3578.270000	0	0	0	0	0	0	湖南省	长沙市	华中	2019-10
104691	2019-10-01	Road-550-W Yellow	公路自行车	自行车	1	1	1120.490000	0	0	0	0	0	0	河北省	深圳市	华南	2019-10

2.3.4 根据gather_customer_order_10_11_head，分组计算前十城市，自行车销售数量销售金额，赋予变量gather_customer_order_city_10_11
[

#分组计算前十城市，自行车销售数量销售金额
gather_customer_order_city_10_11 = gather_customer_order_10_11_head.groupby(['chinese_city','create_year_month']).\
agg({'order_num':sum,'sum_amount':sum}).reset_index()

gather_customer_order_city_10_11.head()

	chinese_city	create_year_month	order_num	sum_amount
0	上海市	2019-10	39	75255.468500
1	上海市	2019-11	45	83769.892400
2	北京市	2019-10	53	101729.543300
3	北京市	2019-11	80	154997.506200
4	天津市	2019-10	29	55328.630600

2.3.5 根据gather_customer_order_city_10_11，计算前10的销售金额及销售量环比,方法参照2.2.2

#计算前十城市环比
city_top_list = list(gather_customer_order_city_head['chinese_city'])
order_top_x = pd.Series([])
amount_top_x = pd.Series([])
for i in city_top_list:
    #print(i)
    a=gather_customer_order_city_10_11.loc[gather_customer_order_city_10_11['chinese_city']==i]['order_num'].pct_change().fillna(0)
    b=gather_customer_order_city_10_11.loc[gather_customer_order_city_10_11['chinese_city']==i]['sum_amount'].pct_change().fillna(0)
    order_top_x=order_top_x.append(a)
    amount_top_x =amount_top_x.append(b)

#order_diff销售数量环比，amount_diff销售金额环比
gather_customer_order_city_10_11['order_diff']=order_top_x
gather_customer_order_city_10_11['amount_diff']=amount_top_x

gather_customer_order_city_10_11.head(6)

	chinese_city	create_year_month	order_num	sum_amount	order_diff	amount_diff
0	上海市	2019-10	39	75255.468500	0.000000	0.000000
1	上海市	2019-11	45	83769.892400	0.153846	0.113140
2	北京市	2019-10	53	101729.543300	0.000000	0.000000
3	北京市	2019-11	80	154997.506200	0.509434	0.523623
4	天津市	2019-10	29	55328.630600	0.000000	0.000000
5	天津市	2019-11	43	85048.381000	0.482759	0.537150

字段注释

chinese_city：城市，create_year_month：时间，order_num：本月销售数量，sum_amount：本月销售金额，order_diff：本月销售数量环比，

amount_diff：本月销售金额环比

2.3.6 将最终的gather_customer_order_city_10_11的DataFrame存入Mysql的pt_bicy_november_october_city_3当中，请使用追加存储。

#存入数据库
engine = sqlalchemy.create_engine('mysql://id:*******@xxx.xx.xxx.xx:3306/dab?charset=gbk')
gather_customer_order_city_10_11.to_sql('name',con=engine,if_exists='append',index=False)