4电商打折套路

本篇主要讲电商打折套路，与之前的几篇相较，改进之处在于使用了Bokeh Plot画图。
链接：https://pan.baidu.com/s/1UrbrH0jtNfXOE3X6ZuEpsQ
提取码：2222

import plotly as py
py.offline.init_notebook_mode()
pyplot = py.offline.iplot

import plotly.graph_objs as go
from plotly.graph_objs import Scatter

from scipy import stats

import pandas as pd

import numpy as np

import seaborn as sns
sns.set(style='darkgrid')


import matplotlib.pyplot as plt
plt.rcParams['font.sans-serif'] = ['SimHei']
plt.rcParams['axes.unicode_minus'] = False


from IPython.core.interactiveshell import InteractiveShell
InteractiveShell.ast_node_interactivity = "all"


from bokeh.palettes import brewer
from bokeh.models import HoverTool,ColumnDataSource
from bokeh.core.properties import value
from bokeh.plotting import figure,show,output_file


import os
os.chdir(r'E:\048-数据分析师python') 
df = pd.read_csv('双十一淘宝美妆数据.csv',encoding= 'gbk')
df


df.fillna(0,inplace = True)
df['update_time'] = pd.to_datetime(df['update_time'],errors='coerce')
df.index = df['update_time']
df['date'] = df.index.day
df

# df.dropna(how='any',inplace= True)


data1 = df[['id','title','店名','date']]
data1
#统计不同商品销售的开始日期，结束日期
d1 = data1[['id','date']].groupby(by = 'id').agg(['min','max'])['date']
d1


#双十一当天售卖的商品ID
id_11 = data1[data1['date'] == 11]['id']
id_11
d2 = pd.DataFrame({'id':id_11,'双十一当天是否售卖':True})
d2


#合并d1和d2
id_data = pd.merge(d1,d2,left_index = True,right_on = 'id',how = 'left')
id_data = id_data.fillna(False)
id_data


m = len(d1)
m_11 = len(id_11)
m_pre = m_11/m
m_pre
print('双十一当天参与活动的商品数为%i个，占比为%.2f%%' % (m_11,m_pre*100))


id_data['type'] = '待分类'
id_data['type'][(id_data['min']<11)&(id_data['max']>11)] = 'A'
id_data['type'][(id_data['min']<11)&(id_data['max']==11)] = 'B'
id_data['type'][(id_data['min']==11)&(id_data['max']>11)] = 'C'
id_data['type'][(id_data['min']==11)&(id_data['max']==11)] = 'D'
id_data['type'][id_data['双十一当天是否售卖']==False] = 'E'
id_data['type'][id_data['max']<11] = 'F'
id_data['type'][id_data['min']>11] = 'G'
#销售节奏分类
result1 = id_data['type'].value_counts()
result1 = result1.loc[['A','B','C','D','E','F','G']]


#bokeh画图
from bokeh.palettes import brewer
colori = brewer['YlGn'][7]
plt.axis('equal')
plt.pie(result1,labels = result1.index,autopct = '%.2f%%',colors = colori,startangle = 90,radius = 1.5,counterclock = False)

#go画图
trace_basic = [go.Pie(labels = result1.index,values = result1.values,hole=0.2,textfont = dict(size=12,color='white'))]
layout = go.Layout(title = '销售节奏分类比例')
figure_basic = go.Figure(data = trace_basic,layout = layout,)
pyplot(figure_basic)


#未参与双十一当天活动的商品去向如何
id_not11 = id_data[id_data['双十一当天是否售卖']==False]
df_not11 = id_not11[['id','type']]
data_not11 = pd.merge(df_not11,df,on = 'id',how = 'left')
#找到双十一当天未参与活动的商品的原始数据
id_con1 = id_data['id'][id_data['type']=='F'].values
id_con1
data_con2 = data_not11[['id','title','date']].groupby(by = ['id','title']).count()
data_con2
title_count = data_con2.reset_index()['id'].value_counts()
title_count
id_con2 = title_count[title_count>1].index
id_con2
data_con3 = data_not11[data_not11['title'].str.contains('预售')]
id_con3 = data_con3['id'].value_counts().index
id_con3
print('未参与双十一当天活动的商品中中，有%i个为暂时下架商品，%i个为重新上架商品，%i个为预售商品' % (len(id_con1),len(id_con2),len(id_con3)))


#真正参与双十一那天活动的商品及品牌情况
#真正参与活动的商品 = 双十一当天在售的商品 + 预售商品
#真正参与双十一那天活动的商品
data_11sale = id_11
id_11sale_final = np.hstack((data_11sale,id_con3))
result2_i = pd.DataFrame({'id':id_11sale_final})
#不同品牌参与双十一当天活动的商品数量
x1 = pd.DataFrame({'id':id_11})
x1_df = pd.merge(x1,df,on = 'id',how = 'left')
brand_11sale = x1_df.groupby('店名')['id'].count()
#不同品牌的预售商品数量
x2 = pd.DataFrame({'id':id_con3})
x2_df = pd.merge(x2,df,on = 'id',how = 'left')
brand_ys = x1_df.groupby('店名')['id'].count()#
#计算结果
result2_data = pd.DataFrame({'当天参与活动商品数量':brand_11sale,'预售商品数量':brand_ys})
result2_data['总量'] = result2_data['当天参与活动商品数量'] + result2_data['预售商品数量']
result2_data.sort_values(by = '总量',inplace = True,ascending = False)

from bokeh.models import HoverTool,ColumnDataSource
from bokeh.core.properties import value
from bokeh.plotting import figure,show,output_file

# 注意：可以改列名，也可以不改，要改的话，请把改列名的代码放在.tolist()以前
result2_data.index.name='brand'
result2_data.columns = ['sale_on_11','presell','sum']

lst_brand = result2_data.index.tolist()
lst_type = result2_data.columns.tolist()[:2]
colors = ['red','green']
source1 = ColumnDataSource(result2_data)
hover1 = HoverTool(tooltips = [('品牌','@brand'),('双十一当天参与活动的商品数量','@sale_on_11'),('预售商品数量','@presell'),('真正参与双十一活动的商品总数','@sum')])
#不改列名也可以直接写
# hover = HoverTool(tooltips = [('品牌','@店名'),('双十一当天参与活动的商品数量','@当天参与活动商品数量'),('预售商品数量','@预售商品数量'),('真正参与双十一活动的商品总数','@总量')])
output_file('真正参与双十一那天活动的商品及品牌情况.html')

p1 = figure(x_range = lst_brand,plot_width = 900,plot_height = 350,title = '各个品牌参与双十一活动的情况',tools = [hover1,'reset,xwheel_zoom,pan,crosshair'])
p1.vbar_stack(lst_type,x = 'brand',source = source1,width = 0.9,color = colors,alpha = 0.7,legend = [value(x) for x in lst_type],muted_color = 'black',muted_alpha = 0.2)

# 设置其他参数
p1.xgrid.grid_line_color = None
p1.axis.minor_tick_line_color = None
p1.outline_line_color = None
p1.legend.location = "top_right"
p1.legend.orientation = "horizontal"
p1.legend.click_policy="mute"

show(p1)

0.6668573062625107
双十一当天参与活动的商品数为2332个，占比为66.69%

未参与双十一当天活动的商品中中，有840个为暂时下架商品，110个为重新上架商品，453个为预售商品

#筛选数据
data2 = df[['id','title','店名','date','price']]
data2['period'] = pd.cut(data2['date'],[4,10,11,14],labels = ['双十一前','双十一当天','双十一后'])
price = data2[['id','price','period']].groupby(['id','price']).min()
price.reset_index(inplace = True)
price
#针对每个商品，评估其打折与不打折的情况
#查看数据是否有波动
id_count = price['id'].value_counts()
#筛选出不打折与打折
#第一类：不打折
id_type1 = id_count[id_count == 1].index
id_type1
#第二类：打折
id_type2 = id_count[id_count != 1].index
id_type2

#针对在打折的商品，其折扣率的情况
result3_data1 = data2[['id','price','period','店名']].groupby(['id','period']).min()
result3_data1.reset_index(inplace = True)
result3_data1

result3_before11 = result3_data1[result3_data1['period']=='双十一前']
result3_at11 = result3_data1[result3_data1['period']=='双十一当天']
result3_data2 = pd.merge(result3_before11,result3_at11,on = 'id')

#合并数据，一定要用英文重命名表示折扣率，否则无法显示数字
result3_data2['zkl'] = result3_data2['price_y']/result3_data2['price_x']
result3_data2

#折扣区间占比
bokeh_data1 = result3_data2[['id','zkl']].dropna()
bokeh_data1['zkl_range'] = pd.cut(bokeh_data1['zkl'],bins = np.linspace(0,1,21))
bokeh_data1

bokeh_data2 = bokeh_data1.groupby('zkl_range').count().iloc[:-1]

bokeh_data2['zkl_pre'] = bokeh_data2['zkl']/bokeh_data2['zkl'].sum()
bokeh_data2
#要先把'id'一列去掉
bokeh_data2 = bokeh_data2.reset_index().drop('id', axis = 1)
bokeh_data2.info()
#要把'折扣率范围'转换为字符串格式
bokeh_data2['zkl_range'] = bokeh_data2['zkl_range'].astype(str)

output_file('商品折扣率统计.html')
source2 = ColumnDataSource(bokeh_data2)

lst_discount = bokeh_data2['zkl_range'].tolist()

hover2 = HoverTool(tooltips = [('折扣率','@zkl_pre')])

p2 = figure(x_range = lst_discount,plot_width = 900,plot_height = 350,title = '商品折扣率统计',tools = [hover2,'reset,xwheel_zoom,pan,crosshair'])
p2.line(x = 'zkl_range',y = 'zkl_pre',source = source2,line_color = 'black',line_dash = [10,4])
p2.circle(x = 'zkl_range',y = 'zkl_pre',source = source2,size = 8,color = 'red',alpha = 0.8)
show(p2)

#按照品牌分析不同品牌的打折力度
from bokeh.transform import jitter
brands = lst_brand.copy()
bokeh_data3 = result3_data2[['id','zkl','店名_x']].dropna()
#把店名_x设为索引
bokeh_data3 = bokeh_data3.set_index("店名_x")
#把店名_x重命名为brand
bokeh_data3.index.name='brand'

bokeh_data3[bokeh_data3['zkl']<0.96]
source3 = ColumnDataSource(bokeh_data3)

output_file('不同品牌的打折力度.html')

hover3 = HoverTool(tooltips = [('折扣率', '@zkl'),('品牌','@brand')])
p3 = figure(y_range = brands,plot_width = 900,plot_height = 700,title = '不同品牌的折扣情况',tools = [hover3,'reset,xwheel_zoom,pan,crosshair'])
p3.circle(x = 'zkl',y = jitter('brand',width = 0.7,range = p3.y_range),source = source3,alpha = 0.3)

show(p3)

#商家营销套路
#计算不同品牌的折扣率
data_zk = result3_data2[result3_data2['zkl']<0.96]
result4_zkld = data_zk.groupby('店名_x')['zkl'].mean()
#打折的商品数量
n_dz = data_zk['店名_x'].value_counts()
#不打折的商品数量
n_bdz = result3_data2['店名_x'].value_counts()
#打折商品比例
result4_dzzpb1 = pd.DataFrame({'打折商品数':n_dz,'不打折商品数':n_bdz})
#计算不同品牌参与打折商品的比例
result4_dzzpb1['参与打折商品比例'] = result4_dzzpb1['打折商品数']/(result4_dzzpb1['打折商品数'] + result4_dzzpb1['不打折商品数'])
result4_dzzpb1.dropna(inplace=True)
#筛选出不同品牌参加双十一活动的商品总数
result4_sum = result2_data.copy()
#合并数据
result4_data = pd.merge(pd.DataFrame(result4_zkld),result4_dzzpb1,left_index = True,right_index = True,how = 'inner')
result4_data = pd.merge(result4_data,result4_sum,left_index = True,right_index = True,how = 'inner')
result4_data

from bokeh.models.annotations import Span
from bokeh.models.annotations import Label
from bokeh.models.annotations import BoxAnnotation

bokeh_data4 = result4_data[['zkl','sum','参与打折商品比例']]
bokeh_data4.columns = ['zkl','amount','pre']
bokeh_data4['size'] = bokeh_data4['amount']*0.03
source4 = ColumnDataSource(bokeh_data4)

x_mean = bokeh_data4['pre'].mean()
y_mean = bokeh_data4['zkl'].mean()
hover4 = HoverTool(tooltips = [('品牌','@index'),('折扣率', '@zkl'),('商品总数', '@amount'),('参与打折商品比例', '@pre'),])
output_file('商家营销套路.html')
p4 = figure(plot_width = 600,plot_height = 600,title = '各个品牌打折套路解析',x_axis_label = '参与打折商品比例',y_axis_label = '折扣率',tools = [hover4,'box_select,pan,reset,wheel_zoom,crosshair'])
p4.circle_x(x = 'pre',y = 'zkl',source = source4,size = 'size',fill_color = 'red',line_color = 'black',fill_alpha = 0.6,line_dash = [8,4])
#辅助线
x = Span(location = x_mean,dimension = 'height',line_color = 'green',line_dash = [6,4],line_alpha = 0.5)
y = Span(location = y_mean,dimension = 'width',line_color = 'green',line_dash = [6,4],line_alpha = 0.5)
p4.add_layout(x)
p4.add_layout(y)

bg1 = BoxAnnotation(bottom = y_mean,right = x_mean,fill_alpha = 0.1,fill_color = 'olive')
label1 = Label(x = 0.1,y = 0.55,text = '少量大打折')
p4.add_layout(bg1)
p4.add_layout(label1)

bg2 = BoxAnnotation(bottom = y_mean,left = x_mean,fill_alpha = 0.1,fill_color = 'firebrick')
label2 = Label(x = 0.31,y = 0.55,text = '大量大打折')
p4.add_layout(bg2)
p4.add_layout(label2)

bg3 = BoxAnnotation(top = y_mean,right = x_mean,fill_alpha = 0.1,fill_color = 'firebrick')
label3 = Label(x = 0.1,y = 0.8,text = '少量少打折')
p4.add_layout(bg3)
p4.add_layout(label3)

bg4 = BoxAnnotation(top = y_mean,left = x_mean,fill_alpha = 0.1,fill_color = 'olive')
label4 = Label(x = 0.31,y = 0.8,text = '大量少打折')
p4.add_layout(bg4)
p4.add_layout(label4)

show(p4)