数据清洗和基本异常值分析---铁塔电费分析报告

import pandas as pd
import os
import matplotlib.pyplot as plt 
import seaborn as sns
import time
import datetime
import dateutil
import numpy as np
import seaborn as sns
sns.set_style("darkgrid",{"font.sans-serif":['KaiTi', 'Arial']})
from pylab import mpl
mpl.rcParams['font.sans-serif'] = ['SimHei']
mpl.rcParams['axes.unicode_minus'] = False 
'''
指定工作盘
'''
os.chdir('k:\\')


'''
遍历文件夹查看数据
'''

def f1_1():
    path = 'K:\\项目\\铁塔电费\\aaa\\'
    folder = os.walk(path)  
    files = list(folder)[0][2]
    n = 1
    for i in files:
        file = path + i
        data = pd.read_excel(file,encoding='utf-8',index=False)
        data_counts = len(data)   # 计算数据量
        columns = data.columns.tolist()  # 输出数据columns
        nan_counts = len(data[data.isnull().values == True])   # 计算缺失值数量
        print('第%i个数据数据量为：%i' % (n, data_counts))
        print(i)
        print('第%i个数据数据字段为：' % n,columns)
        print('第%i个数据缺失值数量为：%i' % (n, nan_counts))
        print('------')
        n += 1
        
f1_1() # 运函数
print('finished!')




'''
构建列表,写入数据入手时间并存入bigdata
'''
lst=[]
def f1_2():
    path ='K:\\项目\\铁塔电费\\aaa\\'
    folder = os.walk(path)   # 遍历文件夹
    files = list(folder)[0][2]
    n = 1
    for i in files:
        file = path + i
        data = pd.read_excel(file,index_col = 0)
        data['数据时间']=i
        lst.append(data)
        #print('第%i个数据列成的列表为：%i' % (n,lst))
        print('------')
        n += 1
    return lst     
f1_2() # 运行函数
print('finished!')
data=pd.concat(lst)    
#处理数据入手时间   
data['数据时间']=data['数据时间'].str.split('.').str[0]





'''
提取最后一次付费时间和首次付费时间
'''
#data['购电截止日期']=pd.to_datetime(data['购电截止日期'])
#data['购电起始日期']=pd.to_datetime(data['购电起始日期'])
def f1_3(data,col):
    col_name1=col+'_max'
    col_name2=col+'_min'
    lst_max=[]
    lst_min=[]
    for i in data[col]:
        #print(i)
        lst_max.append(data[data['站点名称']==i]['购电截止日期'].max())
        lst_min.append(data[data['站点名称']==i]['购电起始日期'].min())
    data[col_name1]=lst_max
    data[col_name2]=lst_min
    return data
 
f1_3(data,'站点名称')
data.rename(columns={'站点名称_max':'最后一次付费时间'},inplace=True)    
data.rename(columns={'站点名称_min':'首次付费时间'},inplace=True)




'''
提取首次和最后一次的时间差
'''

#计算两个日期相差天数，自定义函数名，和两个日期的变量名。
def Caltime(date1,date2):
    #%Y-%m-%d为日期格式，其中的-可以用其他代替或者不写，但是要统一，同理后面的时分秒也一样；可以只计算日期，不计算时间。
    #date1=time.strptime(date1,"%Y-%m-%d %H:%M:%S") 
    #date2=time.strptime(date2,"%Y-%m-%d %H:%M:%S")
    date1=time.strptime(date1,"%Y-%m-%d")
    date2=time.strptime(date2,"%Y-%m-%d")
    #根据上面需要计算日期还是日期时间，来确定需要几个数组段。下标0表示年，小标1表示月，依次类推...
    #date1=datetime.datetime(date1[0],date1[1],date1[2],date1[3],date1[4],date1[5])
    #date2=datetime.datetime(date2[0],date2[1],date2[2],date2[3],date2[4],date2[5])
    date1=datetime.datetime(date1[0],date1[1],date1[2])
    date2=datetime.datetime(date2[0],date2[1],date2[2])
    #返回两个变量相差的值，就是相差天数
    return date2-date1



data['最后一次付费时间']=data['最后一次付费时间'].astype(str)
data['首次付费时间']=data['首次付费时间'].astype(str)

def f1_4(data,col1,col2):
    col_name=col1+'_diff'
    lst=[]
    for i in range(len(data)):
        m=data[col1].iloc[i]
        n=data[col2].iloc[i]
        lst.append(Caltime(n,m))
    data[col_name]=lst
    return data
   

f1_4(data,'最后一次付费时间','首次付费时间')#提取最后一次付费时间和首次付费时间差
data.rename(columns={'最后一次付费时间_diff':'最后一次付费时间和首次付费时间差'},inplace=True)





col_name=data.columns.tolist()
col_name.insert(col_name.index('购电截止读数')+1,'用电度数')
data=data.reindex(columns=col_name)
data['用电度数']=data['购电截止读数']-data['购电起始读数']


data_gc1=data.groupby('站点名称')['站点编码','供电方式'].count()
data_gc2=pd.merge(data,data_gc1,left_on='站点名称',right_index=True,how='left')
data_gc2.rename(columns={'站点编码_y':'缴费次数'},inplace=True)
data=data_gc2[['站点编码_x',
 '站点名称',
 '供电方式_x',
 '购电起始日期',
 '购电截止日期',
 '购电起始读数',
 '购电截止读数',
 '用电度数',
 '电表倍率',
 '线损金额',
 '电费单价','支付电量',
 '支付电费金额',
 '移动电费分割比例',
 '联通电费分割比例',
 '电信电费分割比例',
 '移动分割金额',
 '联通分割金额',
 '电信分割金额',
 '发票类型',
 '站点类型',
 '联通电费（加税后）',
 '数据时间',
 '最后一次付费时间',
 '首次付费时间',
 '最后一次付费时间和首次付费时间差','缴费次数']]
data.columns=['站点编码',
 '站点名称',
 '供电方式',
 '购电起始日期',
 '购电截止日期',
 '购电起始读数',
 '购电截止读数',
 '用电度数',
 '电表倍率',
 '线损金额',
 '电费单价','支付电量',
 '支付电费金额',
 '移动电费分割比例',
 '联通电费分割比例',
 '电信电费分割比例',
 '移动分割金额',
 '联通分割金额',
 '电信分割金额',
 '发票类型',
 '站点类型',
 '联通电费（加税后）',
 '数据时间',
 '最后一次付费时间',
 '首次付费时间',
 '最后一次付费时间和首次付费时间差','缴费次数']


    
'''
获取平均每日用电度数
'''   
#转换格式
data['购电起始日期']=data['购电起始日期'].astype(str)
data['购电截止日期']=data['购电截止日期'].astype(str)

def f1_4(data,col1,col2):
    col_name=col1+'_diff'
    lst=[]
    for i in range(len(data)):
        m=data[col1].iloc[i]
        n=data[col2].iloc[i]
        lst.append(Caltime(n,m))
    data[col_name]=lst
    return data
f1_4(data,'购电截止日期','购电起始日期')#提取缴费的时间差 


#一列timedelta数据转换为int或float
data['购电截止日期_diff']=(data['购电截止日期_diff'] / np.timedelta64(1, 'D')).astype(float)
data['平均每日用电度数']=data['用电度数']/data['购电截止日期_diff']

# 绘制箱型图
color = dict(boxes='DarkGreen', whiskers='DarkOrange', medians='DarkBlue', caps='Gray')
data['平均每日用电度数'].plot.box(vert=False, grid = True,color = color) 

data['站点编码']=data['站点编码'].astype(str)+"\t"

data.columns.tolist()

writer = pd.ExcelWriter('K:\\项目\\铁塔电费\\数据清洗step1.xlsx')
data.to_excel(writer,'data_c1',index=False)
writer.save()

import pandas as pd
import numpy as np
import os
import matplotlib.pyplot as plt
import time
import datetime
import seaborn as sns
os.chdir('G:\\')
sns.set_style("darkgrid",{"font.sans-serif":['KaiTi', 'Arial']})
from pylab import mpl
mpl.rcParams['font.sans-serif'] = ['Verdana'] # 指定默认字体
mpl.rcParams['axes.unicode_minus'] = False # 解决保存图像是负号'-'显示为方块的问题


import warnings
warnings.filterwarnings('ignore')

'''
导入数据
'''
data_zong=pd.read_excel('G:\\铁塔电费\\数据清洗step1.xlsx',sheet='data_c1')

'''
制作数据
'''
data=data_zong.copy()



'''
查看站点用电量异常
'''


def f1_5(data,col):
    col_name1=col+"_"+'norm1'
    col_name2=col+'_'+'norm2'
    m=[]
    n=[]
    j=[]
    for i in data[col]:
        m=data[data[col]==i]['平均每日用电度数'].describe()
        n.append(m[6])
        j.append(m[4])
    data[col_name1]=n
    data[col_name2]=j
    print('finish')
    return data
    
f1_5(data,'站点名称') 

data.rename(columns={'站点名称_norm1':'站点用电量上四分位点'},inplace=True)
data.rename(columns={'站点名称_norm2':'站点用电量下四分位点'},inplace=True)
data['站点用电量极度异常点门限']=data['站点用电量上四分位点']+3*(data['站点用电量上四分位点']-data['站点用电量下四分位点'])

data=data[data['站点用电量极度异常点门限']<data['平均每日用电度数']]
data_dfyc=data.copy()



#删除购电截止日期是2018-04-30的异常值，该极度异常值是由于人工合并账目导致 
data_dfycfinal=data_dfyc[data_dfyc['购电截止日期']!='2018-04-30']

data_dfycfinal['站点编码']=data_dfycfinal['站点编码'].astype(str)+"\t"

'''
保存处理极度异常值和过人为异常值的数据
'''
writer = pd.ExcelWriter('G:\\铁塔电费\\极度用电度数异常点_step2a.xlsx')
data_dfyc.to_excel(writer,'data_dfyc',index=False)
data_dfycfinal.to_excel(writer,'data_dfycfinal',index=False)
writer.save()