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')
网友评论