import pandas as pd
custom_info=pd.read_excel('data/会员信息查询.xlsx')
custom_info.info()

<class 'pandas.core.frame.DataFrame'>
RangeIndex: 952714 entries, 0 to 952713
Data columns (total 12 columns):
会员卡号      952714 non-null object
会员等级      952714 non-null object
会员来源      952714 non-null object
注册时间      952714 non-null datetime64[ns]
所属店铺编码    952714 non-null object
门店店员编码    253828 non-null object
省份        264801 non-null object
城市        264758 non-null object
性别        952714 non-null object
生日        785590 non-null object
年齡        952705 non-null float64
生命级别      952714 non-null object
dtypes: datetime64[ns](1), float64(1), object(10)
memory usage: 87.2+ MB

#会员信息查询
custom_info.head()

# 给 会员信息表 添加年月列
from datetime import datetime
custom_info.loc[:,'注册年月'] = custom_info['注册时间'].apply(lambda x : x.strftime('%Y-%m'))
custom_info[['会员卡号','会员等级','会员来源','注册时间','注册年月']].head()

month_count = custom_info.groupby('注册年月')[['会员卡号']].count()
month_count.columns = ['月增量']
month_count.head()

custom_info.pivot_table(index = '注册年月',values = '会员卡号',aggfunc = 'count')

#通过cumsum 对月增量做累积求和
month_count.loc[:,'存量'] = month_count['月增量'].cumsum()
month_count

#Pandas版本>1.1
import matplotlib.pyplot as plt
month_count['月增量'].plot(figsize = (20,8),color='red',secondary_y = True)
month_count['存量'].plot.bar(figsize = (20,8),color='gray',xlabel = '年月',legend = True,ylabel = '存量')
plt.title("会员存量增量分析",fontsize=20)

month_degree_count =custom_info.groupby(['注册年月','会员等级'])[['会员卡号']].count()
month_degree_count

#使用reset_index()
month_degree_count.reset_index()

#使用unstack()
month_degree_count.unstack()

member_rating = custom_info.pivot_table(index = '注册年月',columns='会员等级',values='会员卡号',aggfunc = 'count')
member_rating

#去掉首月数据
member_rating=member_rating[1:]

member_rating = member_rating[1:] # 去掉第一个月的异常数据
## 画图显示增量等级
fig, ax1 = plt.subplots(figsize=(20, 8), dpi=100)
ax2 = ax1.twinx()
member_rating[['白银会员', '黄金会员']].plot.bar(ax=ax1, rot=0, grid=True, xlabel='年月',ylabel = '白银黄金')
ax1.legend(loc='upper left')
member_rating[['钻石会员', '铂金会员']].plot(ax=ax2, color=['red', 'gray'], ylabel='铂金钻石')
ax2.legend(loc='upper right') # loc参数 可以设置不同位置ax2.legend(loc='upper/bottom left/center/right')
plt.title('会员增量等级分布', fontsize= 20)

#按行求和
member_rating.loc[:,'总计'] = member_rating.sum(axis = 'columns')
#计算白银和黄金会员等级占比 铂金钻石会员数量太少暂不计算
member_rating.loc[:,'白银会员占比'] = member_rating['白银会员'].div(member_rating['总计'])
member_rating.loc[:,'黄金会员占比'] = member_rating['黄金会员'].div(member_rating['总计'])
member_rating

member_rating[['白银会员占比','黄金会员占比']].plot(color=['r','g'],ylabel='占比',figsize=(16,8),grid=True)
plt.title("会员等级占比分析",fontsize=20)

#会员按等级分组groupby实现
ratio = custom_info.groupby('会员等级')[['会员卡号']].count()
#另一种写法
custom_info.groupby('会员等级').agg({'会员卡号':'count'})
#会员按等级分组透视表实现
ratio = custom_info.pivot_table(index = '会员等级',values = '会员卡号',aggfunc = 'count')

# 计算占比
ratio.columns=['会员数']
ratio.loc[:,'占比'] = ratio['会员数'].div(ratio['会员数'].sum())
ratio

# autopct 显示数据标签，并指定保留小数位数
ratio.loc[['白银会员','钻石会员','黄金会员','铂金会员'],'占比'].plot.pie(figsize=(16,8),autopct='%.1f%%',fontsize=16)

#按会员来源进行分组 使用groupby实现
from_data = custom_info.groupby(['注册年月','会员来源'])[['会员卡号']].count()
from_data = from_data.unstack()
from_data.columns = ['电商入口', '线下扫码']
from_data = from_data[1:]
from_data

# 透视表实现
custom_info.pivot_table(index = ['注册年月'],columns='会员来源',values ='会员卡号',aggfunc = 'count')

from_data.plot(figsize=(20,8),fontsize=16,grid=True)
plt.title("电商与线下会员增量分析",fontsize=18)

#查看门店信息表
store_info = pd.read_excel('data/门店信息表.XLSX')
store_info

store_info[['店铺代码','地区编码']].head()

custom_info1 = pd.merge(custom_info,store_info[['店铺代码','地区编码']],left_on='所属店铺编码',right_on='店铺代码')

#统计不同地区的会员数量 注意只统计线下，不统计电商渠道 GBL6D01为电商
district = custom_info1[custom_info1['地区编码']!='GBL6D01'].groupby('地区编码')[['会员卡号']].count()
#修改列名
district.columns = ['会员数量']
district

district['店铺数'] = custom_info1[['地区编码','所属店铺编码']].drop_duplicates().groupby('地区编码')['所属店铺编码'].count()
district

district.loc[:,'每店平均会员数']=round(district['会员数量'].div(district['店铺数']))
#计算总体平均数
district.loc[:,'总平均会员数']=district['会员数量'].sum()/district['店铺数'].sum()
#排序
district=district.sort_values(by='每店平均会员数',ascending=False)
district.head()

district['每店平均会员数'].plot.bar(figsize=(20,8),color='r',legend = True,grid=True)
district['总平均会员数'].plot(figsize=(20,8),color='g',legend = True,grid=True)
plt.title("地区店均会员分析",fontsize=18)

custom_consume=pd.read_excel('data/会员消费报表.xlsx')
all_orders=pd.read_excel('data/全国销售订单数量表.xlsx')
custom_consume.head()

#添加年月  这里年月要转换成整数,因为等会后面要链接的字段是整数
custom_consume.loc[:,'年月']=pd.to_datetime(custom_consume['订单日期']).apply(lambda x:datetime.strftime(x,'%Y%m')).astype(np.int)
custom_consume.head()

custom_consume=pd.merge(custom_consume,store_info[['店铺代码','地区编码']],on='店铺代码')
custom_consume.head()

# margins参数 每行每列求和
member_orders=custom_consume[custom_consume['地区编码']!='GBL6D01'].pivot_table(values = '消费数量',index='地区编码',columns='年月',aggfunc=sum,margins=True)
member_orders

country_sales=all_orders.pivot_table(values = '全部订单数',index='地区代码',columns='年月',aggfunc=sum,margins=True)
country_sales

result=member_orders/country_sales
result.applymap(lambda x: format(x,".2%"))

order_data=custom_consume.query(" 订单类型=='下单' & 地区编码!='GBL6D01'")
#去重  统计订单量需要去重  后面统计消费数量和消费金额不需要去重
order_count=order_data[['年月','地区编码','订单号']].drop_duplicates()
order_count=order_count.pivot_table(index = '地区编码',columns='年月',values='订单号',aggfunc='count')

consume_count=order_data.pivot_table(values = '消费数量',index='地区编码',columns='年月',aggfunc=sum)
consume_count.head()

result=consume_count/order_count
#小数二位显示
result=result.applymap(lambda x:format(x,'.2f'))
result

order_data=custom_consume.query("订单类型=='下单'")
#因为需要用到地区编号和年月  所以选择 订单日期  卡号   年月   地区编码  四个字段一起去重
order_data=order_data[['订单日期','卡号','年月','地区编码']].drop_duplicates()
consume_count = order_data.pivot_table(index =['地区编码','卡号'],values='订单日期',aggfunc='count').reset_index()
consume_count.rename(columns={'订单日期':'消费次数'},inplace=True)
consume_count

consume_count['是否复购']=consume_count['消费次数']>1
consume_count

depart_data=consume_count.pivot_table(index = ['地区编码'],values=['消费次数','是否复购'],aggfunc={'消费次数':'count','是否复购':'sum'})
depart_data.columns=['复购人数','购买人数']
depart_data

depart_data.loc[:,'复购率']=depart_data['复购人数']/depart_data['购买人数']
depart_data

def stats_reorder(start,end,col):
    """
    统计指定起始年月的复购率
    """
    #只要下单的数据  退单不统计
    order_data=custom_consume.query("订单类型=='下单'")
    #筛选日期
    order_data= order_data[(order_data['年月']<=end) & (order_data['年月']>=start)]
    #因为需要用到地区编号和年月  所以选择 订单日期  卡号   年月   地区编码  四个字段一起去重
    order_data=order_data[['订单日期','卡号','年月','地区编码']].drop_duplicates()
    #按照地区编码和卡号进行分组  统计订单日期数量  就是每个地区每个会员的购买次数
    consume_count = order_data.pivot_table(index =['地区编码','卡号'],values='订单日期',aggfunc='count').reset_index()
    #重命名列
    consume_count.rename(columns={'订单日期':'消费次数'},inplace=True)
    #判断是否复购
    consume_count['是否复购']=consume_count['消费次数']>1
    #统计每个地区的购买人数和复购人数
    depart_data=consume_count.pivot_table(index = ['地区编码'],values=['消费次数','是否复购'],aggfunc={'消费次数':'count','是否复购':'sum'})
    
    #重命名列
    depart_data.columns=['复购人数','购买人数']
    #计算复购率
    depart_data[col+'复购率']=depart_data['复购人数']/depart_data['购买人数']
   
    return depart_data

reorder_2018=stats_reorder(201801,201812,'2018.01-2018.12')
reorder_2018

reorder_2019=stats_reorder(201802,201901,'2018.02-2019.01')
reorder_2019

#合并数据
result=pd.concat([reorder_2018['2018.01-2018.12复购率'],reorder_2019['2018.02-2019.01复购率']],axis = 1)
#计算环比
result['环比']=(result['2018.02-2019.01复购率']-result['2018.01-2018.12复购率'])
#百分数显示
result=result.applymap(lambda x:format(x,'.2%'))
result