自行车业务分析 1.2

1.2、自行车整体销售量表现

作业 1.2.1、聚合每月订单数量和销售金额，具体groupby创建一个新的对象，需要将order_num、sum_amount求和，对日期降序排序，记得重置索引

#每月订单数量和销售金额，用groupby创建一个新的对象，需要将order_num、sum_amount求和

# 本题问题在于日期格式为str，无法用sort_values排序

# 方法1 索引取巧 直接对索引进行排序，前提是之前按日期排序好

overall_sales_performance = gather_customer_order.groupby('create_year_month').agg({'order_num':'sum','sum_amount':'sum'}).reset_index().sort_index(ascending=False).reset_index().drop('index',axis=1)

# 方法2 转换日期 麻烦

overall_sales_performance = overall_sales_performance = gather_customer_order.groupby('create_year_month').agg({'order_num':'sum','sum_amount':'sum'}).reset_index()

overall_sales_performance['create_date'] = overall_sales_performance.create_year_month.apply(lambda x:pd.datetime.strptime(x,'%Y-%m'))

overall_sales_performance = overall_sales_performance.sort_values(by='create_date',axis=0,ascending=False).reset_index().drop(['create_date','index'],axis=1)

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

# 规避科学计数法！

overall_sales_performance.head()

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

[1,2,3] 对象Series

shift():[NaN,1,2] 向下移动一位

diff():[NaN,1,1] 原来的数减去shift()

#求每月自行车销售订单量环比，观察最近一年数据变化趋势

#环比是本月与上月的对比，例如本期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

#将环比转化为DataFrame

overall_sales_performance1 = pd.concat([overall_sales_performance,pd.DataFrame(order_num_diff)],axis=1).rename(columns={0:'order_diff'})

作业 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_performance2 = pd.concat([overall_sales_performance1,pd.DataFrame(sum_amount_diff)],axis=1).rename(columns={0:'sum_amount_diff'})

#销量环比字段名order_diff，销售金额环比字段名amount_diff

#按照日期排序，升序

overall_sales_performance = overall_sales_performance2.sort_index(ascending=False).reset_index().drop('index',axis=1)

作业 1.2.4、将最终的overall_sales_performance的DataFrame存入Mysql的pt_overall_sale_performance_1当中，请使用追加存储。

#将数据存入数据库

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

#将数据存入数据库

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

overall_sales_performance.to_sql('pt_overall_sale_performance_1',con=engine,if_exists='replace')