python画漏斗图

只需要输入当日时间，就可以得到当日、上周同日、30日日均的漏斗图，图片如下

from pyechartsimport optionsas opts

from pyecharts.chartsimport Funnel

from pyecharts.fakerimport Faker

import numpyas np

import datetime

import pandasas pd

import webbrowser

import pymysql

##输入当日时间

time ='2021/08/02'

time_lastweek = (datetime.datetime.strptime(time,"%Y/%m/%d")-datetime.timedelta(days=7)).strftime("%Y/%m/%d")

db = pymysql.connect(host="1ghfoc48gd0-7csowqjfug5w.cn-shenzhen.datalakeanalytics.aliyuncs.com",

user="dladev_s1708083130941856",

password="xlKs96AKfV1s57WvJ1", port=10000, database='ods', charset='utf8')

date_group = time.split('/')#拆分字符，用于查询

date_group_lastweek=time_lastweek.split('/')

data =list()

#title = ['商品详情页uv', '填写详情页uv', '支付选择页uv', '提交资料页uv', '免密签约页uv']

##当日和上周同日

sql_lst = ["select COUNT(1) from (select DISTINCT(uid) from event_log where page in ('car-pages/car-etc/index','car-pages-new-process/car-etc/index') and year='%s' and month='%s' and day='%s')" % (date_group[0],date_group[1],date_group[2]),

"select COUNT(1) from (select DISTINCT(uid) from event_log where page in ('car-pages/car-info/basic/basic','car-pages-new-process/car-info/basic/basic') and year='%s' and month='%s' and day='%s')" % (date_group[0],date_group[1],date_group[2]),

"select COUNT(1) from (select DISTINCT(uid) from event_log where page in ('car-pages/car-info/pay/pay','car-pages-new-process/car-info/pay/pay') and year='%s' and month='%s' and day='%s')" % (date_group[0],date_group[1],date_group[2]),

"select COUNT(1) from (select DISTINCT(uid) from event_log where page in ('car-pages/car-info/certificate/certificate','car-pages-new-process/car-info/certificate/certificate') and year='%s' and month='%s' and day='%s')" % (date_group[0],date_group[1],date_group[2]),

"select COUNT(1) from (select DISTINCT(uid) from event_log where page in ('car-pages/car-info/cash/cash','car-pages-new-process/car-info/cash/cash') and year='%s' and month='%s' and day='%s')" % (date_group[0],date_group[1],date_group[2]),

"select COUNT(1) from (select DISTINCT(uid) from event_log where page in ('car-pages/car-etc/index','car-pages-new-process/car-etc/index') and year='%s' and month='%s' and day='%s')" % (date_group_lastweek[0], date_group_lastweek[1], date_group_lastweek[2]),

cursor = db.cursor()

for sqlin sql_lst:

cursor.execute(sql)

columns_names = ['num']

df = pd.DataFrame(cursor.fetchall(), columns=columns_names)

data.append(int(df['num'][0]))

#title = ['落地页：', '填写资料页：', '选择支付页：', '提交资料页：', '签约页：']

#30日均

time_lastmonth = (datetime.datetime.strptime(time, "%Y/%m/%d") - datetime.timedelta(days=29)).strftime("%Y/%m/%d")

sql_lst1 = ["select COUNT(1) from (select DISTINCT(uid) from event_log where page in ('car-pages/car-etc/index','car-pages-new-process/car-etc/index') and _hoodie_partition_path>='"+time_lastmonth+"' and _hoodie_partition_path<='"+time+"')",

"select COUNT(1) from (select DISTINCT(uid) from event_log where page in ('car-pages/car-info/basic/basic','car-pages-new-process/car-info/basic/basic') and _hoodie_partition_path>='"+time_lastmonth+"' and _hoodie_partition_path<='"+time+"')",

"select COUNT(1) from (select DISTINCT(uid) from event_log where page in ('car-pages/car-info/pay/pay','car-pages-new-process/car-info/pay/pay') and _hoodie_partition_path>='"+time_lastmonth+"' and _hoodie_partition_path<='"+time+"')",

"select COUNT(1) from (select DISTINCT(uid) from event_log where page in ('car-pages/car-info/certificate/certificate','car-pages-new-process/car-info/certificate/certificate') and _hoodie_partition_path>='"+time_lastmonth+"' and _hoodie_partition_path<='"+time+"')",

"select COUNT(1) from (select DISTINCT(uid) from event_log where page in ('car-pages/car-info/cash/cash','car-pages-new-process/car-info/cash/cash') and _hoodie_partition_path>='"+time_lastmonth+"' and _hoodie_partition_path<='"+time+"')"]

cursor = db.cursor()

for sqlin sql_lst1:

cursor.execute(sql)

columns_names = ['num']

df = pd.DataFrame(cursor.fetchall(), columns=columns_names)

data.append(round((df['num'][0])/30))

title = [' 落地页：', ' 填写资料页：', '选择支付页：', '提交资料页：', '签约页：']

print(data)

###画漏斗图

save_path ='C:/Users/EDZ/Desktop/工作/2021.08.06/' + time.replace('/', '') +'Funnel.html'

rate1=format((data[0]-data[5])/data[5], '.2%')

rate2=format((data[0]-data[10])/data[10], '.2%')

Title = [ title+'\n 当日：%d 相对转化率:%.2f%% 转化率:(落地页):%.2f%% \n 上周同日：%d 相对转化率:%.2f%% 转化率:(落地页):%.2f%% \n 30日均：%d 相对转化率:%.2f%% 转化率:(落地页):%.2f%% '% (data[idx], (data[idx]/data[idx-1])*100, (data[idx]/data[0])*100,data[idx+5], (data[idx+5]/data[idx-1+5])*100, (data[idx+5]/data[0+5])*100,data[idx+10], (data[idx+10]/data[idx-1+10])*100, (data[idx+10]/data[0+10])*100)if idx >0 else title+'\n 当日： ' +str(data[0]) +'\n 上周同日： ' +str(data[5]) +' 同比变化率：'+str(rate1)+'\n 30日均： ' +str(data[10]) +' 同比变化率：'+str(rate2)for title, idxin zip(title, range(len(data)))]

c = (

Funnel(init_opts=opts.InitOpts(width="1400px"))

.add('Test', [list(z)for zin zip(Title, data)], sort_='none')

.set_global_opts(title_opts=opts.TitleOpts(title=time+' 新用户访问漏斗图'),

legend_opts=opts.LegendOpts(is_show=False))

.render(save_path)

)

webbrowser.open(save_path)

python画漏斗图

推荐阅读更多精彩内容