【Python】红旗超市线下缴电费用户数据分析及可视化（pandas+pyecharts）

import pandas as pddata = pd.read_excel(r"./成都红旗成都收费员收费记录/红旗连锁成都收费员-2022年1月交费数据.xlsx")result=data["台区名称"].unique().tolist()print(len(result))

import pandas as pdtb=pd.DataFrame(pd.read_excel(r"红旗连锁成都收费员-2022年1月交费数据.xlsx"))

gp=tb.groupby(["区县","台区名称"])
d0=gp.sum().loc[:,["收费金额"]]
d1=gp.size()
d=pd.concat([d0,d1],axis=1)
d.columns=["总金额","缴费次数"]

d_1=d.sort_values(by="总金额",ascending=False)
d_1.to_excel("统计信息_金额.xlsx")
d_2=d.sort_values(by="缴费次数",ascending=False)
d_2.to_excel("统计信息_缴费次数.xlsx")

from urllib.request import quoteimport requestsimport pandas as pdimport numpy as npimport jsonpd.set_option('display.max_columns',None)

def getlnglat(address):    url = 'http://api.map.baidu.com/geocoding/v3/'    output = 'json'    ak = '在百度地图开放平台申请的ak'    address = quote(address) # 由于本文地址变量为中文，为防止乱码，先用quote进行编码    uri = url + '?' + 'address=' + address  + '&output=' + output + '&ak=' + ak  +'&callback=showLocation%20'+'//GET%E8%AF%B7%E6%B1%82'    res=requests.get(uri).text    temp = json.loads(res)  # 将字符串转化为json    lat = temp['result']['location']['lat']    lng = temp['result']['location']['lng']    return lng,lat   # 纬度 latitude,经度 longitude

def renew_quxian(x):    if "供电中心" in x:        return x.split("供")[0]    if "供电公司" in x:        temp = x.split("网")[1]        if "成都市" in temp:           temp1 = temp.split("供")[0]           return temp1.split("市")[1]        else:            return temp.split("供")[0]

data["中文地址"]="成都市"+data["区县"].apply(renew_quxian)+data["台区名称"]

data_unique = data.iloc[data.groupby(['台区名称']).apply(   lambda x: x['用户编号'].idxmax())]

data_unique["经纬度"]=data_unique["中文地址"].apply(getlnglat)r=data_unique.loc[:,["台区名称","经纬度"]]

for i in r.iterrows():    k=i[1]['台区名称']    v=i[1]['经纬度']    mp[k]=v
f=open(r"city_coordinates.json", 'r',encoding='utf-8')fs=str(f.read())f.close()
old=json.loads(fs)  #json转objold.update(mp)      #把从excel里读到的更新进去f=open(r"city_coordinates.json",'w',encoding='utf-8')json.dump(old,f,ensure_ascii=False)f.close()

{"四川大学台区": [104.08399190311378, 30.63014203788181], "电子科大清水河台区": [103.93740389474014, 30.756035188715344], "西南交通大学台区": [103.99321381946406, 30.77039889704779], "四川师范大学台区": [104.12869113613753, 30.619258186581966], "西华大学台区": [104.07428849682555, 30.640677198288525], "四川电力职业技术学院台区": [103.84530242741315, 30.726613592027057], "西南民族大学公变": [104.02072415621548, 30.626048550861352], "四川工商职业技术学院": [103.70236701907363, 30.918967871107483]}

def getlistnum(li):    set1 = set(li)    dict1 = {}    for item in set1:        dict1.update({item:li.count(item)})    return dict1
total = {}total = getlistnum(data["台区名称"].tolist())

def fun(li):    zhi=[]    for i in li:        scale = (i - min(li)) / (max(li) - min(li))        zhi.append(50 + 50 * scale)    return zhi

from pyecharts.charts import BMapfrom pyecharts import options as opts
def heatmap_heatmap() -> BMap:    c = (        BMap()            .add_schema(baidu_ak="在百度地图开放平台申请的ak", center=[104.07231,30.663467])            .add(            "",            [list(z) for z in zip(total.keys(), fun(total.values()))],            type_="heatmap",            label_opts=opts.LabelOpts(formatter="{b}"),        )            .set_global_opts(            title_opts=opts.TitleOpts(title="",title_textstyle_opts = (opts.TextStyleOpts(color='red', font_size=50))),            legend_opts=opts.LegendOpts(                pos_left='80%',                pos_top='20',                textstyle_opts=opts.TextStyleOpts(color='black', font_size=30),            ),            tooltip_opts=opts.TooltipOpts(                textstyle_opts=opts.TextStyleOpts(font_size=30),                # background_color='green',                border_color='black',                border_width=3),            visualmap_opts=opts.VisualMapOpts()        )            .render("红旗地图.html")    )    return cheatmap_heatmap()