背景:
接领导要求,要求产出几份关于外卖品类的分析报告,在需求数据和模板确定后,要按照这个模板生成多个品类的报告,每个品类相同的目标出一份报告,如下图所示:
1.读取原始数据
我的原始数据是从数据库里面汇总好了,再导出到excel里面,主要包括月份,近30天月售,门店数,品牌名称几个字段,
import numpy as np
import pandas as pd
import datetime,time
import os
import openpyxl as opxl
import xlrd
import xlwt
inputfile=r'D:\shiheng\市场数据\品类输出\原始数据.xlsx'
outputfile=r'D:\shiheng\市场数据\品类输出\卤味鸭脖报告.xlsx'#背景中展示的模板样子
df1=pd.read_excel(inputfile,sheet_name='卤味鸭脖')
print ('原始数据读取完毕')
2.品牌分析
2.1 开头插入
workbook=opxl.load_workbook(outputfile)
#sheet1=workbook.create_sheet(title='品牌分析') #因为模板已经有这个sheet,所以不需要创建
sheet1=workbook.get_sheet_by_name('品牌分析')
sheet1.cell(row=2,column=2).value='卤味鸭脖品牌分析' #往第二行第二列插入‘卤味鸭脖品牌分析’几个字
自定义一个插入函数,后面往excel中写入的时候可以用到
def insert_to_excel(sheet,data,rows,columns,r0,c0):
for i in range(rows):
for j in range(columns):
sheet.cell(row=i+r0,column=j+c0).value=data.iloc[i,j]
#print ('(%s,%s)插入成功'%i%j)
2.2 top20门店数
shops1=df1[df1['月份']==201911][['品牌名称','门店数']].groupby(['品牌名称'])['门店数'].sum().reset_index()
shops=shops1.sort_values('门店数',ascending=False).head(20)
#写入excel
rows1,columns1=shops.shape #查看行列数
insert_to_excel(sheet1,shops,rows1,columns1,r0=7,c0=2)
2.3 top20月售
sales1=df1[df1['月份']==201911][['品牌名称','近30天月售']].groupby(['品牌名称'])['近30天月售'].sum().reset_index()
sales=sales1.sort_values('近30天月售',ascending=False).head(20)
#写入excel
rows2,columns2=sales.shape
insert_to_excel(sheet1,sales,rows2,columns2,r0=7,c0=5)
2.4 top20开店数&关店数
#计算当月的门店数
brands_isopen1=df1[df1['月份']==201911][['品牌名称','门店数']]
brands_isopen1_gb=brands_isopen1.groupby(['品牌名称'])['门店数'].sum()
#计算上月的门店数
brands_isopen2=df1[df1['月份']==201910][['品牌名称','门店数']].rename(columns={'门店数':'上月门店数'})
brands_isopen2_gb=brands_isopen2.groupby(['品牌名称'])['上月门店数'].sum()
#合并并计算出新增门店数=当月-上月
brands_isopen=pd.merge(brands_isopen1_gb,brands_isopen2_gb,how='left',on='品牌名称').reset_index()
brands_isopen['新增门店数']=brands_isopen['门店数']-brands_isopen['上月门店数']
#根据上一步的计算结果筛选出新增数最多的top20品牌,注意这里是降序排列
open=brands_isopen[brands_isopen['新增门店数']>0][['品牌名称','新增门店数']].sort_values('新增门店数',ascending=False).rename(columns={'新增门店数':'新店数'}).head(20)
#同理,根据上一步结果筛选出新增门店《0(即闭店)最多的top20
close1=brands_isopen[brands_isopen['新增门店数']<0][['品牌名称','新增门店数']].sort_values('新增门店数').head(20)
close1['关店数']=close1['新增门店数'].apply(lambda x:abs(x))
close=close1[['品牌名称','关店数']]
#将上面的开店top20 和关店top20品牌写入excel
rows3,columns3=open.shape
rows4,columns4=close.shape
insert_to_excel(sheet1,open,rows3,columns3,r0=7,c0=8)
insert_to_excel(sheet1,close,rows4,columns4,r0=7,c0=11)
3.深度分析品牌表
3.1读取原始数据
该数据也为数据库计算好导出到excel的,也可以在python中连接数据库并存储数据。数据格式主要如下表所示,字段有品类,品牌名称,城市id,月售,城市名称字段。要利用该数据进行品牌城市分析。
df2=pd.read_excel(inputfile,sheet_name='月售top20bycity')
c1=df2[df2['品类']=='卤味鸭脖'].sort_values('门店数',ascending=False) #只读取卤味鸭脖品类的数据
3.2 品牌月售top20 分城市
gb2=c1.groupby(['brand_name'])['门店数','月售'].sum().reset_index()
brands_deep1=gb2.sort_values('月售',ascending=False).head(20)
brands_deep6=pd.merge(brands_deep1,brands_isopen[brands_isopen['新增门店数']>0][['品牌名称','新增门店数']],left_on='brand_name',right_on='品牌名称',how='left').drop(['品牌名称'],axis=1)
brands_deep7=pd.merge(brands_deep6,brands_isopen[brands_isopen['新增门店数']<0][['品牌名称','新增门店数']],left_on='brand_name',right_on='品牌名称',how='left').drop(['品牌名称'],axis=1)
brands_deep3=brands_deep7.rename(columns={'新增门店数_x':'11-12月新店','新增门店数_y':'11-12月关店'})
brands_deep3['11-12月关店']=brands_deep3['11-12月关店'].apply(lambda x:abs(x))
#要将城市改成如背景模板中的那样,“上海(356) 重庆(276) 深圳(258) 北京(251) 长沙(214) 天津(213)”,按照门店数降序排列城市,括号内为品牌数
def combine(t1,t2):
t=str(t1)+'('+str(t2)+')'
return t
c1['城市信息']=c1.apply(lambda x:combine(x.城市名称,x.门店数),axis=1)
brands_deep2=pd.merge(brands_deep3,c1[['brand_name','城市信息']],on='brand_name',how='left')
top_brands=brands_deep1['brand_name'].drop_duplicates()
result1=[]
for brand in list(top_brands):
dic1={}
dic1['brand_name']=brand
datasets=brands_deep2[brands_deep2['brand_name']==brand]
city_list=[]
for z in range(len(datasets)):
m1=datasets['城市信息'].iloc[z]
city_list.append(m1)
city_str=str(city_list)
dic1['city_info']=city_str.replace('[','').replace(']','').replace("'",'').replace(',','')
result1.append(dic1)
print ('深入分析品牌表完成')
brands_deep4=pd.DataFrame(result1)
brands_deep5=pd.merge(brands_deep3,brands_deep4,on='brand_name',how='left')
#写入数据
rows5,columns5=brands_deep5.shape
insert_to_excel(sheet1,brands_deep5,rows5,columns5,r0=32,c0=2)
3.3 深度分析城市表
#城市名称 门店数 前top20
gb3=c1.groupby(['城市名称'])['门店数','月售'].sum().reset_index()
city_deep1=gb3.sort_values('门店数',ascending=False).head(20)
city_deep2=pd.merge(city_deep1[['城市名称']],c1,on='城市名称',how='left')
city_deep2['品牌信息']=city_deep2.apply(lambda x:combine(x.brand_name,x.门店数),axis=1)
top_citys=city_deep1['城市名称'].drop_duplicates()
result2=[]
for city in list(top_citys):
dic2={}
dic2['city']=city
datasets=city_deep2[city_deep2['城市名称']==city]
brands_list=[]
for z in range(len(datasets)):
m1=datasets['品牌信息'].iloc[z]
brands_list.append(m1)
brands_str=str(brands_list)
dic2['brands_info']=brands_str.replace('[','').replace(']','').replace("'",'').replace(',','')
result2.append(dic2)
print ('深度分析城市表完成')
city_deep3=pd.DataFrame(result2).rename(columns={'city':'城市名称'})
city_deep4=pd.merge(city_deep1,city_deep3,on='城市名称',how='left')
#写入数据
rows6,columns6=city_deep4.shape
insert_to_excel(sheet1,city_deep4,rows6,columns6,r0=57,c0=2)
4.分城市的sheet表
#序号 店名 月售 地址 是否新店
df3=pd.read_excel(inputfile,sheet_name='营业详情bycity')
df4=df3[(df3['品类']=='卤味鸭脖')&(df3['月份']==201911)]
df5=pd.merge(city_deep1,df4,on='城市名称',how='left')
df6=df3[(df3['品类']=='卤味鸭脖')&(df3['月份']==201910)]
df7=pd.merge(df4[['stand_merchant_id']],df6[['stand_merchant_id','shop_name']],how='left',on='stand_merchant_id')
def is_null(x):
y=str(x)
if y=='nan':
return '是'
else:
return ''
#因为第一个《品牌分析》的模板的颜色已经定好,往里面写数据的时候就会按照已设定好的格式往里面写,但是后面分城市的sheet,因为每个品类每个城市的品牌数不一致,且每个品牌的门店数也不一致,因此无法定死单元格。只能循环计算每个品牌的门店数计算需要多少行,自动往下填充新的品牌需要的行数。
#并且,在上面的数据都已经成功填进excel对应的单元格内后,我们需要对单元格进行合并,颜色填充,加粗,斜体,颜色修改,字体等修改
from openpyxl.styles import PatternFill,Border,Protection,Font,Side,Alignment
border=Border(left=Side(border_style=None,color='FF000000'),right=Side(border_style=None,color='FF000000'),top=Side(border_style=None,color='FF000000'),bottom=Side(border_style=None,color='FF000000'))
from copy import copy
#把涉及到的几种背景颜色都列好
font1=Font(name='Calibri',size=24,color='FF4682B4',underline="single")
font2=Font(name='Calibri',size=20,color='FF2E8B57')
font3=Font(name='Calibri',size=14,color='FF2E8B57')
font4=Font(name='Calibri',size=16,color='FF00CD00')
font5=Font(name='Calibri',size=12,color='FF2E8B57')
fill1=PatternFill(fill_type='solid',fgColor='FF87CEFA')
align = Alignment(horizontal='center',vertical='center',wrap_text=True)
side1=Side(border_style='medium',color='FFDAA520')
border1 = Border(top=side1)
border2 = Border(bottom=side1)
df7['是否新店']=df7['shop_name'].apply(lambda x:is_null(x))
df8=pd.merge(df5,df7,on=['stand_merchant_id','shop_name'],how='left')[['城市名称','stand_merchant_id','brand_name','shop_name','近30天月售','shop_address','是否新店']].reset_index()
for city in list(top_citys):
df_city_i=df8[df8['城市名称']==city]
df_brands=df_city_i['brand_name'].drop_duplicates()
sheet_i=workbook.create_sheet(city)
#sheet_i=workbook.get_sheet_by_name(city)
shops_cnt=df_city_i['stand_merchant_id'].drop_duplicates()
newshops_cnt=df_city_i[df_city_i['是否新店']=='是']['stand_merchant_id'].drop_duplicates()
city_shops_cnt=city_deep1[city_deep1['城市名称']==city]['门店数']
sheet_i.cell(row=2,column=3).value='《《返回首页'
sheet_i.cell(row=2,column=3).font=font1 #返回首页的格式,选用font1
sheet_i.cell(row=3,column=3).value=city+'品牌分析(仅分析全国TOP20品牌)'
sheet_i.cell(row=3,column=3).font=font2 #城市的格式,选用font2
sheet_i.cell(row=5,column=3).value='品牌数'
sheet_i.cell(row=5,column=3).fill=fill1
sheet_i.cell(row=5,column=3).font=font3 #第5行第3列的格式
sheet_i.cell(row=5,column=4).fill=fill1 #第5行第4列的格式
sheet_i.cell(row=5,column=3).alignment=align
sheet_i.cell(row=5,column=5).value='11月门店数'
sheet_i.cell(row=5,column=5).font=font3
sheet_i.cell(row=5,column=5).fill=fill1
sheet_i.cell(row=5,column=6).fill=fill1
sheet_i.cell(row=5,column=5).alignment=align
sheet_i.cell(row=5,column=7).value='10-11月新店数'
sheet_i.cell(row=5,column=7).font=font3
sheet_i.cell(row=5,column=7).fill=fill1
sheet_i.cell(row=5,column=7).alignment=align
sheet_i.cell(row=6,column=3).value=len(df_brands)
sheet_i.cell(row=6,column=3).font=font4
sheet_i.cell(row=6,column=3).fill=fill1
sheet_i.cell(row=6,column=3).alignment=align
sheet_i.cell(row=6,column=4).fill=fill1
sheet_i.cell(row=6,column=5).value=len(shops_cnt)
sheet_i.cell(row=6,column=5).font=font4
sheet_i.cell(row=6,column=5).fill=fill1
sheet_i.cell(row=6,column=5).alignment=align
sheet_i.cell(row=6,column=6).fill=fill1
sheet_i.cell(row=6,column=7).value=len(newshops_cnt)
sheet_i.cell(row=6,column=7).font=font4
sheet_i.cell(row=6,column=7).fill=fill1
sheet_i.cell(row=6,column=7).alignment=align
##开始按照品牌门店数依次填充
s0=10 #默认初始行为第10行
for brand in list(df_brands):
print (brand)
df_brands_j=df_city_i[df_city_i['brand_name']==brand][['stand_merchant_id','shop_name','近30天月售','是否新店','shop_address']]
df_brands_i=df_brands_j[['shop_name','近30天月售','是否新店','shop_address']].sort_values('近30天月售',ascending=False).reset_index(drop=True).reset_index()
brand_shops_cnt=df_brands_j['stand_merchant_id'].drop_duplicates()
brand_newshops_cnt=df_brands_j[df_brands_j['是否新店']=='是']['stand_merchant_id'].drop_duplicates()
ri,ci=df_brands_i.shape
sheet_i.cell(row=s0,column=3).value='品牌:'
sheet_i.cell(row=s0,column=3).font=font4
sheet_i.cell(row=s0,column=4).value=str(brand)
sheet_i.cell(row=s0,column=4).font=font4
sheet_i.cell(row=s0+2,column=3).value='11月门店数'
sheet_i.cell(row=s0+2,column=3).font=font3
sheet_i.cell(row=s0+2,column=3).fill=fill1
sheet_i.cell(row=s0+2,column=3).alignment=align
sheet_i.cell(row=s0+2,column=4).fill=fill1
sheet_i.cell(row=s0+2,column=5).value='11月门店城市占比'
sheet_i.cell(row=s0+2,column=5).font=font3
sheet_i.cell(row=s0+2,column=5).fill=fill1
sheet_i.cell(row=s0+2,column=5).alignment=align
sheet_i.cell(row=s0+2,column=6).fill=fill1
sheet_i.cell(row=s0+2,column=7).value='10-11月新门店数'
sheet_i.cell(row=s0+2,column=7).font=font3
sheet_i.cell(row=s0+2,column=7).fill=fill1
sheet_i.cell(row=s0+2,column=7).alignment=align
sheet_i.cell(row=s0+3,column=3).value=len(brand_shops_cnt)
sheet_i.cell(row=s0+3,column=3).font=font5
sheet_i.cell(row=s0+3,column=3).fill=fill1
sheet_i.cell(row=s0+3,column=3).alignment=align
sheet_i.cell(row=s0+3,column=4).fill=fill1
sheet_i.cell(row=s0+3,column=5).value= "%.2f%%" % (100*len(brand_shops_cnt)/int(city_shops_cnt))
sheet_i.cell(row=s0+3,column=5).font=font5
sheet_i.cell(row=s0+3,column=5).fill=fill1
sheet_i.cell(row=s0+3,column=5).alignment=align
sheet_i.cell(row=s0+3,column=6).fill=fill1
sheet_i.cell(row=s0+3,column=7).value=len(brand_newshops_cnt)
sheet_i.cell(row=s0+3,column=7).font=font5
sheet_i.cell(row=s0+3,column=7).fill=fill1
sheet_i.cell(row=s0+3,column=7).alignment=align
sheet_i.cell(row=s0+5,column=3).value='点击+,查看11月营业门店详情'
sheet_i.cell(row=s0+5,column=3).font=font3
sheet_i.cell(row=s0+7,column=3).value='序号'
sheet_i.cell(row=s0+7,column=4).value='店名'
sheet_i.cell(row=s0+7,column=5).value='月售'
sheet_i.cell(row=s0+7,column=6).value='是否新店'
sheet_i.cell(row=s0+7,column=7).value='地址'
sheet_i.cell(row=s0+7,column=3).font=font5
sheet_i.cell(row=s0+7,column=4).font=font5
sheet_i.cell(row=s0+7,column=5).font=font5
sheet_i.cell(row=s0+7,column=6).font=font5
sheet_i.cell(row=s0+7,column=7).font=font5
sheet_i.cell(row=s0+7,column=3).fill=fill1
sheet_i.cell(row=s0+7,column=4).fill=fill1
sheet_i.cell(row=s0+7,column=5).fill=fill1
sheet_i.cell(row=s0+7,column=6).fill=fill1
sheet_i.cell(row=s0+7,column=7).fill=fill1
sheet_i.cell(row=s0+7,column=3).alignment=align
sheet_i.cell(row=s0+7,column=4).alignment=align
sheet_i.cell(row=s0+7,column=5).alignment=align
sheet_i.cell(row=s0+7,column=6).alignment=align
sheet_i.cell(row=s0+7,column=7).alignment=align
s1=s0+8
insert_to_excel(sheet_i,df_brands_i,ri,ci,s1,3)
si=len(df_brands_i)
print (si)
s0=s1+si+3
sheet_i.cell(row=s0,column=3).value='品牌:'
sheet_i.cell(row=s0,column=4).value=str(brand)
sheet_i.cell(row=s0+2,column=3).value='11月门店数'
sheet_i.cell(row=s0+2,column=5).value='11月门店城市占比'
sheet_i.cell(row=s0+2,column=7).value='新门店数'
sheet_i.cell(row=s0+3,column=3).value=len(brand_shops_cnt)
sheet_i.cell(row=s0+3,column=5).value="%.2f%%" % (100*len(brand_shops_cnt)/int(city_shops_cnt))
sheet_i.cell(row=s0+3,column=7).value=len(brand_newshops_cnt)
sheet_i.cell(row=s0+2,column=3).alignment=align
sheet_i.cell(row=s0+2,column=5).alignment=align
sheet_i.cell(row=s0+2,column=7).alignment=align
sheet_i.cell(row=s0+3,column=3).alignment=align
sheet_i.cell(row=s0+3,column=5).alignment=align
sheet_i.cell(row=s0+3,column=7).alignment=align
sheet_i.cell(row=s0+7,column=3).value='序号'
sheet_i.cell(row=s0+7,column=4).value='店名'
sheet_i.cell(row=s0+7,column=5).value='月售'
sheet_i.cell(row=s0+7,column=6).value='是否新店'
sheet_i.cell(row=s0+7,column=7).value='地址'
sheet_i.cell(row=s0+7,column=3).alignment=align
sheet_i.cell(row=s0+7,column=4).alignment=align
sheet_i.cell(row=s0+7,column=5).alignment=align
sheet_i.cell(row=s0+7,column=6).alignment=align
sheet_i.cell(row=s0+7,column=7).alignment=align
insert_to_excel(sheet_i,df_brands_i,ri,ci,s1,3)
print (s0,s1)
5.设置超链接(城市链接&返回首页链接2个)
在所有数据和格式都完成后,我们还差超链接设置,查了一些资料
for x in range(57,77): #深度分析城市表的城市列表所在单元格行号
link_from=workbook.get_sheet_by_name('品牌分析')['B'+str(x)]
city_name=link_from.value
link_to="#"+city_name+"!B1"
print (link_to)
link_from.hyperlink=link_to
link_from2=workbook.get_sheet_by_name(city_name).cell(row=2,column=3)
link_to2="#品牌分析!B2"
link_from2.hyperlink=link_to2
workbook.save(outputfile)
workbook.close()
print ('sucess')
以上,全部结束,其他品类报告只需要替换一下数据源的名称就可以。这是第一次写的比较完善的一份excel输出报告,有些地方可能写的很罗嗦,后面会继续改进。