自行车业务分析 3.3

3.3、公路/山地/旅游自行车细分市场表现

#查看自行车有那些产品子类

gather_customer_order['cpzl_zw'].unique()

gather_customer_order['cpzl_zw'].drop_duplicates(keep='first')

公路自行车细分市场销量表现

gather_customer_order_road = gather_customer_order[gather_customer_order['cpzl_zw'] == '公路自行车']

作业 3.3.2、求公路自行车不同型号'product_name'字段的产品销售数量，赋值变量为gather_customer_order_road_month

#求公路自行车不同型号产品销售数量

gather_customer_order_road_month = gather_customer_order_road.groupby(by = ['create_year_month','product_name']).agg({'order_num':'sum'}).reset_index()

gather_customer_order_road_month['cpzl_zw'] = '公路自行车'

#每个月公路自行车累计销售数量

gather_customer_order_road_month_sum = gather_customer_order_road.groupby('create_year_month').agg({'order_num':'sum'}).reset_index()

作业 3.3.3、在gather_customer_order_road_month基础上，合并公路自行车每月累计销售数量gather_customer_order_road_month_sum，主键为'create_year_month'

gather_customer_order_road_month = pd.merge(gather_customer_order_road_month,gather_customer_order_road_month_sum,on='create_year_month',how='left')

gather_customer_order_road_month['order_num_prop']=gather_customer_order_road_month['order_num_x']/gather_customer_order_road_month['order_num_y']

gather_customer_order_road_month

山地、公路和旅游自行车全部进行以上操作

#将山地自行车、旅游自行车、公路自行车每月销量信息合并

gather_customer_order_month = pd.concat([gather_customer_order_road_month,gather_customer_order_Mountain_month,gather_customer_order_tour_month])

作业 3.3.5、新增一列'order_num_proportio'，为销售量占每月自行车总销售量比率

#各类自行车，销售量占每月自行车总销售量比率

gather_customer_order_month['order_num_proportio'] = gather_customer_order_month.order_num_x/gather_customer_order_month.order_num_y

#order_month_product当月产品累计销量

#sum_order_month当月自行车总销量

gather_customer_order_month.rename(columns={'order_num_x':'order_month_product','order_num_y':'sum_order_month'})

gather_customer_order_month

计算2019年11月自行车环比

#计算11月环比，先筛选10月11月数据

gather_customer_order_month_10_11 = gather_customer_order_month[gather_customer_order_month.create_year_month.isin(['2019-10','2019-11'])]

#排序。将10月11月自行车销售信息排序

gather_customer_order_month_10_11 = gather_customer_order_month_10_11.sort_values(by = ['product_name','create_year_month'])

gather_customer_order_month_10_11.head(3)

product_name = list(gather_customer_order_month_10_11.product_name.drop_duplicates())

product_name

作业 3.3.8、计算自行车销售数量环比

#计算自行车销售数量环比

order_top_x = list([])

for i in product_name:

a=gather_customer_order_month_10_11[gather_customer_order_month_10_11['product_name']==i]['order_num_proportio'].pct_change().fillna(0)

order_top_x.extend(a)

gather_customer_order_month_10_11['order_num_diff'] = order_top_x

#筛选出11月自行车数据

gather_customer_order_month_11 = gather_customer_order_month_10_11[gather_customer_order_month_10_11['create_year_month'] == '2019-11']

gather_customer_order_month_11.head(3)

计算2019年1月至11月产品累计销量

作业 3.3.8、筛选2019年1月至11月自行车数据，赋予变量为gather_customer_order_month_1_11

gather_customer_order_month_1_11 = gather_customer_order_month[(gather_customer_order_month['create_year_month'].apply(lambda x:x[:4])=='2019') & (gather_customer_order_month['create_year_month']!='2019-12')]

#计算2019年1月至11月自行车累计销量

gather_customer_order_month_1_11_sum = gather_customer_order_month_1_11.groupby(by = 'product_name').order_month_product.sum().reset_index()

#重命名sum_order_1_11：1-11月产品累计销量

gather_customer_order_month_1_11_sum = gather_customer_order_month_1_11_sum.rename(columns = {'order_month_product':'sum_order_1_11'})

gather_customer_order_month_1_11_sum.head()

2019年11月自行车产品销量、环比、累计销量

#按相同字段product_name产品名，合并两张表

gather_customer_order_month_11 = pd.merge(gather_customer_order_month_11,gather_customer_order_month_1_11_sum,on='product_name',how='left')

gather_customer_order_month_11.head()