自行车业务分析 2.3

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

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

#筛选11月自行车交易数据

gather_customer_order_11 = gather_customer_order[gather_customer_order['create_year_month']=='2019-11']

gather_customer_order_11.head()

作业 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').apply(lambda x:x['order_num'].sum()).reset_index().rename(columns={0:'order_num'})

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

gather_customer_order_city_head = gather_customer_order_city_11.sort_values(by='order_num',ascending=False).head(10)

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

gather_customer_order_city_head

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

city_list=list(gather_customer_order_city_head['chinese_city'].unique())

#筛选销售前十城市，10月11月自行车销售数据

gather_customer_order_10_11_head = gather_customer_order_10_11[gather_customer_order_10_11['chinese_city'].agg(lambda x:x in city_list)]

gather_customer_order_10_11_head.head(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()

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

#计算前十城市环比

city_top_list = city_list

order_top_x = pd.Series([])

amount_top_x = pd.Series([])

for i in city_top_list:

a=gather_customer_order_city_10_11[gather_customer_order_city_10_11['chinese_city']==i]['order_num'].pct_change().fillna(0)    b=gather_customer_order_city_10_11[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(5)

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

#存入数据库

engine = create_engine("mysql://frogdata05:Frogdata!123@106.15.121.232:3306/datafrog05_adventure?charset=utf8")

datafrog05 = engine

gather_customer_order_10_11_group.to_sql('pt_bicy_november_october_city_3',con=datafrog05,if_exists='append')