前言
2019年11月自行車業(yè)務(wù)分析報告
目錄：
- 一、自行車整體銷售表現(xiàn)
- 二、2019年11月自行車地域銷售表現(xiàn)
- 三、2019年11月自行車產(chǎn)品銷售表現(xiàn)
- 四、用戶行為分析
- 五、2019年11月熱品銷售分析
計算結(jié)果存入數(shù)據(jù)庫_對應表名：
- 自行車整體銷售表現(xiàn):pt_overall_sale_performance_1
- 2019年11月自行車地域銷售表現(xiàn):pt_bicy_november_territory_2、pt_bicy_november_october_city_3
- 2019年11月自行車產(chǎn)品銷售表現(xiàn)：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
一、自行車整體銷售表現(xiàn)
1.1、從數(shù)據(jù)庫讀取源數(shù)據(jù)：dw_customer_order
1.1.0 把數(shù)據(jù)從遠程的數(shù)據(jù)庫同步到本地
在navicat中可以把數(shù)據(jù)同步到本地的數(shù)據(jù)庫，而且表結(jié)構(gòu)都會自動的幫我們同步過來。
導過來之后，從本地sql導入的時間從5分鐘縮短到30秒，大大的提高了效率。
1.1.1 連接數(shù)據(jù)庫
#讀取源數(shù)據(jù)。不同城市，每天產(chǎn)品銷售信息
#創(chuàng)建數(shù)據(jù)庫引擎
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)
#查看源數(shù)據(jù)前5行，觀察數(shù)據(jù)，判斷數(shù)據(jù)是否正常識別
gather_customer_order.head()
1.1.2查看源數(shù)據(jù)類型：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、篩選產(chǎn)品類型cplb_zw中的自行車作為新的gather_customer_order，目前存在如圖情況，你需要剔除掉其余2個
#篩選產(chǎn)品類別為自行車的數(shù)據(jù)
# print(gather_customer_order.cplb_zw.unique())
gather_customer_order = gather_customer_order.loc[ gather_customer_order.cplb_zw=='自行車']
gather_customer_order.head()
1.1.5、總結(jié)知識點：
1.python連接mysql的幾種方式
create_engine建立連接
pd.read_sql_query在連接上執(zhí)行sql語句
2.查看數(shù)據(jù)類型的幾種方式
type, info
3.時間轉(zhuǎn)字符串類型
時間轉(zhuǎn)為字符串：strftime("%Y-%m-%d %H%M%s")
數(shù)字轉(zhuǎn)為時間：pd.to_datetime(x, format="%Y%m%d")
日期只保留月份：d.astype('datetime64[M]')
查看時間差距幾天 (d1-d2)/np.deltatime64(1, 'D')
4.可能DataFrame中存在科學計數(shù)法，請自行探索如何解決
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、自行車整體銷售量表現(xiàn)
1.2.1、聚合每月訂單數(shù)量和銷售金額，具體groupby創(chuàng)建一個新的對象，需要將order_num、sum_amount求和，對日期降序排序，記得重置索引
#每月訂單數(shù)量和銷售金額，用groupby創(chuàng)建一個新的對象，需要將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()
#按日期降序排序，方便計算環(huán)比
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()
1.2.2、新增一列order_num_diff，此為每月自行車銷售訂單量環(huán)比，本月與上月對比，例如本期2019-02月銷售額與上一期2019-01月銷售額做對比
步驟：
1.利用diff()函數(shù)
2.使用列表形式方便加工
3.加工形成同樣長度的列表，轉(zhuǎn)為Series或DataFrame與本身數(shù)據(jù)合并
#求每月自行車銷售訂單量環(huán)比，觀察最近一年數(shù)據(jù)變化趨勢
#環(huán)比是本月與上月的對比，例如本期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]
#將環(huán)比轉(zhuǎn)化為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
1.2.3、新增一列sum_amount_diff，此為每月自行車銷售金額環(huán)比，原理一樣，但是所需字段不同，最后形成按照日期升序排列
#求每月自行車銷售金額環(huán)比
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
#將環(huán)比轉(zhuǎn)化為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()
#銷量環(huán)比字段名order_diff，銷售金額環(huán)比字段名amount_diff
#按照日期排序，升序
overall_sales_performance_ret2.sort_values('create_year_month', ascending=True, inplace=True)
overall_sales_performance_ret2.head()
#查看每月自行車訂單量、銷售金額、環(huán)比、前5行
overall_sales_performance_ret2.head(5)
字段注釋：
create_year_month：時間，order_num：本月累計銷售數(shù)量，sum_amount：本月累計銷售金額，order_diff：本月銷售數(shù)量環(huán)比，
sum_amount_diff：本月銷售金額環(huán)比，dw_customer_order:用戶訂單表
1.2.4、將最終的overall_sales_performance的DataFrame存入Mysql的pt_overall_sale_performance_1當中，請使用追加存儲。
#將數(shù)據(jù)存入數(shù)據(jù)庫
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、總結(jié)知識點：
- 1、計算上下兩行之間相除或相乘等一系列方法的函數(shù)使用方法羅列
s.mul(s.shift()) s.div(s.shift())
- 2、總結(jié)DataFrame與Series類型區(qū)別
Series:鍵值對，key就是索引,key可以是一個數(shù)值
DataFrame：value應該是一個list，series，ndarray
- 3、總結(jié)DataFrame專用的排序函數(shù)和列表的排序函數(shù)
sort_index(), sort_values()
list1.sort(reverse=True)
- 4、總結(jié)存儲到mysql的幾種形式（覆蓋、追加等）
{'fail', 'replace', 'append'}
二、2019年11月自行車地域銷售表現(xiàn)
2.1、源數(shù)據(jù)dw_customer_order，數(shù)據(jù)清洗篩選10月11月數(shù)據(jù)
#gather_customer_order在分析自行車整體表現(xiàn)時已從數(shù)據(jù)庫導入表（dw_customer_order），并篩選僅自行車數(shù)據(jù)，這里不再導入
gather_customer_order.head()
2.1.1 篩選10、11月的自行車數(shù)據(jù)，賦值變量為gather_customer_order_10_11
#篩選10月11月自行車數(shù)據(jù)
#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()
2.1.2 查看10月、11月的自行車訂單數(shù)量
#10月11月自行車訂單數(shù)據(jù)共6266條
len(gather_customer_order_10_11)
6266
2.2、2019年10月和11月自行車區(qū)域銷售量表現(xiàn)
2.2.1 請按照'chinese_territory','create_year_month'，區(qū)域、月份分組，訂單量求和、銷售金額求和，賦予變量gather_customer_order_10_11_group，記得重置索引
#按照區(qū)域、月分組,訂單量求和，銷售金額求和
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)
2.2.2 求不同區(qū)域10月11月的環(huán)比
步驟
- 1、獲得去重區(qū)域的列表region_list
- 2、利用for循環(huán)區(qū)域列表，結(jié)合loc定位符合區(qū)域
- 3、利用pct_change()函數(shù)實現(xiàn)環(huán)比效果
- 4、形成新的Series
- 5、利用Series帶的索引，合并到gather_customer_order_10_11_group中
#將區(qū)域存為列表
region_list=gather_customer_order_10_11_group.chinese_territory.unique()
region_list
array(['東北', '華東', '華中', '華北', '華南', '臺港澳', '西北', '西南'], dtype=object)
#pct_change()當前元素與先前元素的相差百分比,求不同區(qū)域10月11月環(huán)比
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月各個區(qū)域自行車銷售數(shù)量、銷售金額環(huán)比
gather_customer_order_10_11_group.head()
字段注釋：
chinese_territory：區(qū)域，create_year_month：時間，order_num：區(qū)域銷售數(shù)量，sum_amount：區(qū)域銷售金額，order_diff：本月銷售數(shù)量環(huán)比，
amount_diff：本月銷售金額環(huán)比
2.2.3、將最終的gather_customer_order_10_11_group的DataFrame存入Mysql的pt_bicy_november_territory_2當中，請使用追加存儲。
#將數(shù)據(jù)存入數(shù)據(jù)庫
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、總結(jié)知識點：
- 1、總結(jié)DataFrame的去重方法，多種
groupby, unique,
- 2、總結(jié)Series轉(zhuǎn)化為列表的方式
list(s) s.tolist()
- 3、總結(jié)Series與列表的區(qū)別
Series是帶索引的，不可變變量
List是不帶索引的，在內(nèi)存中是可變變量。
-
4、總結(jié)append\extend等列表追加元素的區(qū)別及適用范圍
image-20200630203606346
2.3、2019年11月自行車銷售量TOP10城市環(huán)比
2.3.1、篩選11月自行車交易數(shù)據(jù) 賦予變量為gather_customer_order_11
#篩選11月自行車交易數(shù)據(jù)
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城市分組，求和銷售數(shù)量order_num，最終查看11月自行車銷售數(shù)量前十城市，賦予變量gather_customer_order_city_head
gather_customer_order_city_11= gather_customer_order_11.groupby('chinese_city').agg({'order_num':'sum'}).reset_index()
#11月自行車銷售數(shù)量前十城市
gather_customer_order_city_head = gather_customer_order_city_11.sort_values('order_num', ascending=False).head(10)
#查看11月自行車銷售數(shù)量前十城市
gather_customer_order_city_head
2.3.3、根據(jù)gather_customer_order_city_head的前十城市，查看10月11月自行車銷售數(shù)據(jù)gather_customer_order_10_11，賦予變量gather_customer_order_10_11_head
#篩選銷售前十城市，10月11月自行車銷售數(shù)據(jù)
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)
2.3.4、根據(jù)gather_customer_order_10_11_head，分組計算前十城市，自行車銷售數(shù)量銷售金額，賦予變量gather_customer_order_city_10_11
#分組計算前十城市，自行車銷售數(shù)量銷售金額
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
2.3.5、根據(jù)gather_customer_order_city_10_11，計算前10的銷售金額及銷售量環(huán)比，最終形成結(jié)果gather_customer_order_city_10_11如圖,方法參照作業(yè)2.2.2
#計算前十城市環(huán)比
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銷售數(shù)量環(huán)比，amount_diff銷售金額環(huán)比
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)
字段注釋
chinese_city：城市，create_year_month：時間，order_num：本月銷售數(shù)量，sum_amount：本月銷售金額，order_diff：本月銷售數(shù)量環(huán)比，
amount_diff：本月銷售金額環(huán)比
2.3.6、將最終的gather_customer_order_city_10_11的DataFrame存入Mysql的pt_bicy_november_october_city_3當中，請使用追加存儲。
#11月銷售數(shù)量前十城市。銷售數(shù)量、銷售金額環(huán)比
#print(gather_customer_order_city_10_11.head())
#存入數(shù)據(jù)庫, 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、總結(jié)知識點
- 1、函數(shù)agg的用法及適用范圍
groupby分類匯總的時候
- 2、DataFrame中的loc,iloc,at,iat,ix的用法和區(qū)別
loc,at 使用索引查找
iloc,iat 使用數(shù)字，位置查找
loc,iloc除了跟單一的值，還可以跟 切片，list，true/false的series
at,iat 只能跟單一的數(shù)值
ix 是比前面的幾個更強大，但是也容易混淆
- 3、篩選函數(shù)的總結(jié)，如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、個人總結(jié)第二部分目的及需求
- 對于區(qū)域的表現(xiàn)我們是先unique得到區(qū)域的集合，然后再集合上面迭代篩選這個區(qū)域內(nèi)的數(shù)據(jù)，在這個數(shù)據(jù)里面計算環(huán)比
- 對于TOPS10的問題，其實和區(qū)域類似，唯一的不同在于先要篩選前十名的城市，其中用到了排序以及篩選然后再unique得到區(qū)域的集合。然后就可以篩選數(shù)據(jù)并且分類匯總
-
5、容易出錯的地方
排序之后需要reset_index否則后面concat的時候順序又會搞錯。
