前言

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

目录：

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

计算结果存入数据库_对应表名：

• 自行车整体销售表现: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()
from sqlalchemy import create_engine

一、自行车整体销售表现

1.1、从数据库读取源数据：dw_customer_order

1.1.0 把数据从远程的数据库同步到本地
在navicat中可以把数据同步到本地的数据库，而且表结构都会自动的帮我们同步过来。

image.png

导过来之后，从本地sql导入的时间从5分钟缩短到30秒，大大的提高了效率。
1.1.1 连接数据库

#读取源数据。不同城市，每天产品销售信息
#创建数据库引擎
engine = create_engine(r'mysql://root@localhost:3306/adventure_local?charset=gbk',echo=False)
gather_customer_order=pd.read_sql_query("select * from dw_customer_order", engine)

#查看源数据前5行，观察数据，判断数据是否正常识别
gather_customer_order.head()

image-20200630194940701

1.1.2查看源数据类型：dw_customer_order

gather_customer_order.info()

<class 'pandas.core.frame.DataFrame'>
RangeIndex: 302726 entries, 0 to 302725
Data columns (total 16 columns):
create_date           302726 non-null object
product_name          302726 non-null object
cpzl_zw               302726 non-null object
cplb_zw               302726 non-null object
order_num             302726 non-null int64
customer_num          302726 non-null int64
sum_amount            302726 non-null float64
is_current_year       302726 non-null object
is_last_year          302726 non-null object
is_yesterday          302726 non-null object
is_today              302726 non-null object
is_current_month      302726 non-null object
is_current_quarter    302726 non-null object
chinese_province      302726 non-null object
chinese_city          302726 non-null object
chinese_territory     302726 non-null object
dtypes: float64(1), int64(2), object(13)
memory usage: 37.0+ MB

1.1.3利用create_date字段增加create_year_month月份字段

#增加create_year_month月份字段。按月维度分析时使用  strftime("%Y-%m")
gather_customer_order['create_year_month']=gather_customer_order.create_date.apply(lambda x:x.strftime("%Y-%m"))

gather_customer_order['create_year_month'].loc[ 163496:163500]

163496    2020-03
163497    2020-03
163498    2020-03
163499    2020-03
163500    2020-03
Name: create_year_month, dtype: object

1.1.4、筛选产品类型cplb_zw中的自行车作为新的gather_customer_order，目前存在如图情况，你需要剔除掉其余2个

#筛选产品类别为自行车的数据
# print(gather_customer_order.cplb_zw.unique())

gather_customer_order = gather_customer_order.loc[ gather_customer_order.cplb_zw=='自行车']
gather_customer_order.head()

image-20200630200125505

1.1.5、总结知识点：

1.python连接mysql的几种方式

create_engine建立连接
pd.read_sql_query在连接上执行sql语句

2.查看数据类型的几种方式

type, info

3.时间转字符串类型

时间转为字符串：strftime("%Y-%m-%d %H%M%s")

数字转为时间：pd.to_datetime(x, format="%Y%m%d")

日期只保留月份：d.astype('datetime64[M]')

查看时间差距几天 (d1-d2)/np.deltatime64(1, 'D')

4.可能DataFrame中存在科学计数法，请自行探索如何解决

sql中是CAST('12.5' AS decimal(9,2)) CAST(12.5 AS int)

pandas中是pd.set_option('display.float_format',lambda x:'%.3f' % x)

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'}).reset_index()
overall_sales_performance.head()

image-20200630201311522

#按日期降序排序，方便计算环比
overall_sales_performance.sort_values('create_year_month', ascending=False, inplace=True)
overall_sales_performance.reset_index(inplace=True)
del overall_sales_performance['index']
overall_sales_performance.head()

image-20200630201335285

1.2.2、新增一列order_num_diff，此为每月自行车销售订单量环比，本月与上月对比，例如本期2019-02月销售额与上一期2019-01月销售额做对比

步骤：

1.利用diff()函数

2.使用列表形式方便加工

3.加工形成同样长度的列表，转为Series或DataFrame与本身数据合并

#求每月自行车销售订单量环比，观察最近一年数据变化趋势
#环比是本月与上月的对比，例如本期2019-02月销售额与上一期2019-01月销售额做对比
order_num_diff = list((overall_sales_performance.order_num.diff() * -1 / overall_sales_performance.order_num))    #s.diff() = s - s.shift()
order_num_diff.pop(0) #删除列表中第一个元素
order_num_diff.append(0) #将0新增到列表末尾
order_num_diff

[4.2236591053635788,
 1.7730746619635509,
 -0.76109550561797756,
 0.85271922976841008,
 0.21460176991150443,
 0.032637075718015669,
 -0.075995174909529548,
 0.0710594315245478,
 0.060637204522096609,
 -0.052580331061343723,
 -0.01027947317699968,
 0.029771749917300694,
 -0.02263174911089557,
 0.038267875125881166,
 -0.063796354494028915,
 0.12358757062146893,
 -0.067193675889328064,
 0]

#将环比转化为DataFrame:
overall_sales_performance_ret = pd.concat([overall_sales_performance, pd.DataFrame({'order_num_diff_222':order_num_diff})], axis=1)

overall_sales_performance_ret

image-20200630201432115

1.2.3、新增一列sum_amount_diff，此为每月自行车销售金额环比，原理一样，但是所需字段不同，最后形成按照日期升序排列

#求每月自行车销售金额环比
sum_amount_diff = list(overall_sales_performance.sum_amount.diff() * -1/ overall_sales_performance.sum_amount)
sum_amount_diff.pop(0) #删除列表中第一个元素
sum_amount_diff.append(0) #将0新增到列表末尾
sum_amount_diff
#将环比转化为DataFrame
overall_sales_performance_ret2 = pd.concat( [overall_sales_performance_ret, 
                                       pd.DataFrame({'sum_amount_dif':sum_amount_diff})], axis=1)

overall_sales_performance_ret2.head()

image-20200630201719444

#销量环比字段名order_diff，销售金额环比字段名amount_diff
#按照日期排序，升序
overall_sales_performance_ret2.sort_values('create_year_month', ascending=True, inplace=True)
overall_sales_performance_ret2.head()

image-20200630201741535

#查看每月自行车订单量、销售金额、环比、前5行
overall_sales_performance_ret2.head(5)

image-20200630202152956

字段注释：

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_write = create_engine(r'mysql://root@localhost:3306/adventure_write?charset=gbk',echo=False)
overall_sales_performance_ret2.to_sql('pt_overall_sale_performance_1', con=engine_write, if_exists='append')

s = pd.Series(np.arange(1, 5))
s.diff()
# s - s.shift()

0    NaN
1    1.0
2    1.0
3    1.0
dtype: float64

1.2.5、总结知识点：

• 1、计算上下两行之间相除或相乘等一系列方法的函数使用方法罗列

s.mul(s.shift()) s.div(s.shift())

• 2、总结DataFrame与Series类型区别

Series:键值对，key就是索引,key可以是一个数值
DataFrame：value应该是一个list，series，ndarray

• 3、总结DataFrame专用的排序函数和列表的排序函数

sort_index(), sort_values()

list1.sort(reverse=True)

• 4、总结存储到mysql的几种形式（覆盖、追加等）

{'fail', 'replace', 'append'}

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

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

#gather_customer_order在分析自行车整体表现时已从数据库导入表（dw_customer_order），并筛选仅自行车数据，这里不再导入
gather_customer_order.head()

image-20200630202446479

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

#筛选10月11月自行车数据
#gather_customer_order.head().create_date.apply(lambda x:x.month==10) 

gather_customer_order_10_11 = gather_customer_order.loc[ gather_customer_order.create_date.apply(lambda x:(x.month==10 or x.month==11))]
gather_customer_order_10_11.tail()

image-20200630202539985

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

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

6266

2.2、2019年10月和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( by=['chinese_territory', 
                                                                            'create_year_month']).agg({'order_num':'sum',
                                                                                                    'sum_amount':'sum'}).reset_index()

gather_customer_order_10_11_group.head(6)

image-20200630202745724

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=gather_customer_order_10_11_group.chinese_territory.unique()

region_list

array(['东北', '华东', '华中', '华北', '华南', '台港澳', '西北', '西南'], dtype=object)

#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']
    a = a.diff()/a.shift()
    a.fillna(0, inplace=True)
    b=gather_customer_order_10_11_group.loc[gather_customer_order_10_11_group['chinese_territory']==i]['sum_amount']
    b = b.diff()/b.shift()
    b.fillna(0, inplace=True)
    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()

image-20200630203303370

字段注释：

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当中，请使用追加存储。

#将数据存入数据库
gather_customer_order_10_11_group.to_sql('pt_bicy_november_territory_2', con=engine_write, if_exists='append')

l = [1, 2, 4]
dict = {'a':l}
print(dict)
l.append(5)
print(dict)

{'a': [1, 2, 4]}
{'a': [1, 2, 4, 5]}

2.2.4、总结知识点：

• 1、总结DataFrame的去重方法，多种

groupby, unique,

• 2、总结Series转化为列表的方式

list(s) s.tolist()

• 3、总结Series与列表的区别

Series是带索引的，不可变变量
List是不带索引的，在内存中是可变变量。

• 4、总结append\extend等列表追加元素的区别及适用范围

  image-20200630203606346

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

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

#筛选11月自行车交易数据
gather_customer_order_11 = gather_customer_order.loc[ gather_customer_order.create_date.apply(lambda x:x.month==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').agg({'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

image-20200630203654708

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

#筛选销售前十城市，10月11月自行车销售数据
interested_cities = gather_customer_order_city_head.chinese_city.unique()

gather_customer_order_10_11_head = gather_customer_order_10_11.loc[ gather_customer_order_10_11.chinese_city.apply(lambda x:
                                                                                                     x in interested_cities)]

gather_customer_order_10_11_head.head(10)

image-20200630203728947

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(by=
                                                                            ['chinese_city', 'create_year_month']).agg({'order_num':'sum', 
                                                                                                                    'sum_amount':'sum'}).reset_index()

gather_customer_order_city_10_11

image-20200630203806774

2.3.5、根据gather_customer_order_city_10_11，计算前10的销售金额及销售量环比，最终形成结果gather_customer_order_city_10_11如图,方法参照作业2.2.2

#计算前十城市环比
city_top_list = gather_customer_order_city_10_11.chinese_city.unique()
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[ gather_customer_order_city_10_11.chinese_city==i].order_num
    a = a.diff() / a.shift()
    a.fillna(0, inplace=True)
    b = gather_customer_order_city_10_11[ gather_customer_order_city_10_11.chinese_city==i].sum_amount
    b= b.diff() / b.shift()    
    b.fillna(0, inplace=True)
    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(5)

image-20200630203834334

字段注释

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当中，请使用追加存储。

#11月销售数量前十城市。销售数量、销售金额环比
#print(gather_customer_order_city_10_11.head())

#存入数据库, ife
engine2 = create_engine(r'mysql://root@localhost/adventure_write?charset=gbk')
gather_customer_order_city_10_11.to_sql('pt_bicy_november_october_city_3', con=engine2, if_exists='append')

2.3.7、总结知识点

• 1、函数agg的用法及适用范围

groupby分类汇总的时候

• 2、DataFrame中的loc,iloc,at,iat,ix的用法和区别

loc,at 使用索引查找
iloc,iat 使用数字，位置查找
loc,iloc除了跟单一的值，还可以跟 切片，list，true/false的series
at,iat 只能跟单一的数值
ix 是比前面的几个更强大，但是也容易混淆

• 3、筛选函数的总结，如between \ & \ ~ \ isin \ ==

df.loc[df.s.isin(['lama'])] # isin
h1 = df1[(df1['nat_actn_2_3'].isin['100','102']) | ((df1['nat_acctn_2_3'] > 500) & (df1['nat_actn_2_3'] < 599))] #& |
df.loc[~df.s.isin(['lama'])] #~
df.loc[df.s.between(1, 4) ]

• 4、个人总结第二部分目的及需求

  1. 对于区域的表现我们是先unique得到区域的集合，然后再集合上面迭代筛选这个区域内的数据，在这个数据里面计算环比
  2. 对于TOPS10的问题，其实和区域类似，唯一的不同在于先要筛选前十名的城市，其中用到了排序以及筛选然后再unique得到区域的集合。然后就可以筛选数据并且分类汇总

• 5、容易出错的地方

  排序之后需要reset_index否则后面concat的时候顺序又会搞错。