实现居民客户用电习惯分析

分析居民的用电习惯代码：

# coding=utf-8
import pandas as pd
df = pd.read_excel('data.xlsx')
UserAll = df.groupby("用户编号").count().index
JEAll = df.groupby(by=["用户编号"])["缴费金额（元）"].sum().tolist()
CountAll = df.groupby(by=["用户编号"])["缴费日期"].count().tolist()
JeSum = 0;
JeA = 0;
CountSum = 0;
CountA = 0;
for i in JEAll:
    JeSum = JeSum+i
JeA = JeSum/100
for i in CountAll:
    CountSum = CountSum+i
CountA = CountSum/100
#用户的缴费和除以缴费次数

#print(JeA)
#print(CountA)
UserType = []
for i in range(100):
    if JEAll[i] >= JeA and CountAll[i] >= CountA:
        UserType.append("高价值型客户")
    elif JEAll[i] < JeA and CountAll[i] >= CountA:
        UserType.append("大众型客户")
    elif JEAll[i] >= JeA and CountAll[i] < CountA:
        UserType.append("潜力型客户")
    elif JEAll[i] < JeA and CountAll[i] < CountA:
        UserType.append("低价值型客户")
Types = list(UserType)
print(JeA)
print(CountA)
#print(df.loc[10]["用户编号"])
jguiyi=[]
cihuiyi=[]
zong=[]
pj=[]
for i in range(100):
    jguiyi.append(JEAll[i]/1500)
    cihuiyi.append(CountAll[i]/8)
    zong.append(jguiyi[i]*0.6+cihuiyi[i]*0.4)
    pj.append(JEAll[i]/CountAll[i])
j=list(jguiyi)
c=list(cihuiyi)
z=list(zong)
p=list(pj)
rows = []
for i in range(100):
    newdict = dict(用户编号=UserAll[i],缴费金额数=JEAll[i],缴费次数=CountAll[i],平均金额=p[i],金额归一化=j[i],次数归一化=c[i],总分=z[i])

    rows.append(newdict)
df1 = pd.DataFrame(rows)
df1.to_csv("归一.csv",encoding="utf-8_sig",index=False)

对缴费分析：

# coding=utf-8
import json
import pandas as pd
def getAndSet01():
    df_dict = {}
    # 读取Excel文件
    df = pd.read_excel('data.xlsx')

    # 替换Excel表格内的空单元格，否则在下一步处理中将会报错
    df.fillna("", inplace=True)
    df_list = []
    for i in df.index.values:
        # loc为按列名索引 iloc 为按位置索引，使用的是 [[行号], [列名]]
        df_line = df.loc[i, ['用户编号', '缴费日期', '缴费金额（元）']].to_dict()
        # 将每一行转换成字典后添加到列表
        df_list.append(df_line)
    df_dict['data'] = df_list
    data=pd.DataFrame(df_list)
    flo=data['缴费金额（元）'].sum()/100
    pin=len(data)/100
    tic=[{"平均缴费金额":flo,"平均缴费次数":pin}]
    set=pd.DataFrame(tic)
    set.to_csv("居民客户的用电缴费习惯分析 1.csv",encoding="utf-8_sig", index=False)
# def getAndSet02():

if __name__ =="__main__":
    getAndSet01()
    # getAndSet02()

效果：