Python数据处理(第二集)

# coding: utf-8


# In[1]:






import time
import random
import os
import re
import xlwt
import requests
import numpy as np
import xlsxwriter
import pandas as pd
from matplotlib import pyplot as plt
import sys
import math
reload(sys)
sys.setdefaultencoding('utf8')




# In[2]:


os.chdir(u'********')
print os.getcwd()




# In[3]:


os.getcwd()




# In[4]:


guangdabase = pd.read_csv('guangdabase.csv',header=None)
print guangdabase.head()




# In[5]:


guangdadzd = pd.read_csv('guangdadzd.csv',header=None)
print guangdadzd.head()




# In[75]:


guangdacardinfo = pd.read_csv('guangdaCardInfo.csv',header=None,sep = ',',error_bad_lines=False)
print guangdacardinfo.head()




# In[5]:


def xybasenames(data1):
    data1.colnames=['id', 'UpdateFlag', 'branch', 'ajbh', 'kehu', 'ajlx', 'shfzh', 'shfzh18', 'shebaoID', 'xm', 'pinyin', 'sex',                     'zhiwu', 'zjqkje', 'zjshje', 'zjzxqke', 'zjzxqkerq', 'zjyhlx', 'jdsj', 'dqsj', 'zu', 'ywy', 'states', 'period',                    'yjbl', 'fenpeisj', 'urgent', 'lasttime', 'closetime', 'czy', 'addtime', 'pici', 'inpici', 'shengfen', 'chengshi',                    'remark1', 'remark2', 'remark3', 'lastJzSj', 'kongguan', 'PromisedDate', 'PromisedJe', 'nextStep', 'hint',                     'dingyueTime', 'fabuTime', 'gaNum', 'Ajsx', 'ajInfo', 'kehuAjBh', 'ajStop', 'ajLock', 'yxAj', 'isShare', 'zxxddm',                    'picipizhu']
    return data1




# In[7]:


def xydzdnames(data2):
    data2.colnames=['shfzh18','kehu','ajlx','inpici','account','cardno','hkrq','dzrq','hkze','hkmx','rate','huobi',                    'zhrmb','qkbj','yhlx','zxqkje','hkbz','ywy','czy','Lasttime','flag','IsAggregatedCard','ajbh',                    'fabuTime','kehuliushuiNum']
    return data2




# In[8]:


def xyjzhnames(data3):
    data3.colnames=['id','updateFlag','shfzh18','shfzh','ajid','ajbh','rq','cuoshi','je','xgrq','czy','UniqId',                   'Phone','yxPhone','jzhleix','TpPath','TpFile','TpTime','addlx','phoneLx','duixiang','result',                   'PromisedDate','PromisedJe','jzhuniqId','NotInsert','luyinId','daima','imagename','recordname',                   'Longitude','Latitude','signName','jihua','jihuaRq','xddm']
    return data3






# In[9]:


def xycardinfonames(data4):
    data4.colnames=['id','UpdateFlag','ajbh','shfzh18','kehu','ajlx','inpici','account','cardno','khrqback',                    'khrq','srcHuobi','huobi','qkje','zhrmb','rate','zjjkj','zjjkrq','zxqke','zxqkerq','shje',                    'yhlx','zdr','klms','xsqd','tgno','gqsd','zfk','addtime','lasttime','remark1','remark2',                    'remark3','czy','LastShuaKaRq','LastTiXianRq','pici','khrq2','zjjkrq2','IsAggregatedCard',                    'hkzhdzh','fabuTime','kehuAjBh','edu','rcrq','qkbj','qkzhnj','nianFei','chaoxianFei','qitaFei','fyze','maxYqsd']
    return data4




# In[6]:


guangdabase =xybasenames(guangdabase[:])




# In[7]:


guangdabase.columns=guangdabase.colnames
guangdabase




# In[12]:


guangdadzd = xydzdnames(guangdadzd[:])




# In[13]:


guangdadzd.columns = guangdadzd.colnames
guangdadzd




# In[14]:


guangdacardinfo = xycardinfonames(guangdacardinfo[:])
guangdacardinfo.columns = guangdacardinfo.colnames
guangdacardinfo




# In[ ]:


#guangdajzh = xydzdnames(guangdajzh[:])




# In[ ]:


#guangdajzh.columns = guangdajzh.colnames
#guangdajzh




# In[8]:


guangda1 = guangdabase[["ajbh","shfzh18","ywy","zjqkje","zjshje"]]




# In[9]:


print guangda1.head()




# In[10]:


guangda1["hkzhb"] = guangda1["zjshje"]/guangda1["zjqkje"]




# In[11]:


guangda1.describe()




# In[83]:


def shfzhage(age1):
    num1 = age1.shape[0]
    for i in range(num1):
        if len(age1[i])==18:
            age1[i] = 2017-int(age1[i][6:10])
        else:
            age1[i] = 0
    return age1




# In[ ]:


def shfzhsex(sex1):
    num2 = sex1.shape[0]
    for i in range(num2):
        if len(sex1[i])==18:
            if (int(sex1[i][16]))%%2==0:
                sex1[i]='女'
            else:
                sex1[i]='男'
        else:
            sex1[i] = 0
    return sex1




# In[ ]:


def shfzhaddress(address1):
    num3 = address1.shape[0]
    for i in range(num3):
        address1[i]=address1[i][0:6]
    return address1




# In[12]:


guangda1["bornyear"]=guangda1["shfzh18"].str.slice(6,10)




# In[13]:


guangda1["sex"]=guangda1["shfzh18"].str.get(16)




# In[14]:


guangda1["address"]=guangda1["shfzh18"].str.slice(0,6)




# In[15]:


guangda1["shfzhnum"] = guangda1["shfzh18"].str.len()




# In[16]:


guangda2 = guangda1[["ajbh","shfzh18","bornyear","shfzhnum","sex","address","zjqkje","zjshje","ywy"]]




# In[17]:


guangda1.shape




# In[18]:


guangda2.shape




# In[19]:


guangda2 = guangda2[guangda2["shfzhnum"]==18]




# In[20]:


guangda2["yearlen"] = guangda2["bornyear"].str.len()




# In[21]:


guangda2 = guangda2[guangda2["yearlen"]==4]




# In[22]:


guangda2 = guangda2[guangda2["bornyear"]!='\xe7\xac\xac2']




# In[23]:


guangda2["bornyear"] = guangda2["bornyear"].astype(int)




# In[24]:


guangda2["age"] = 2017-guangda2["bornyear"]




# In[25]:


guangda2["sex"] = guangda2["sex"].astype(int)




# In[26]:


print guangda2




# In[29]:


guangda2["sex"][guangda2["sex"]=='女']=0




# In[30]:


guangda2["sex"][guangda2["sex"]!=0]=1




# In[31]:


guangda2["address"] = guangda2["address"].astype(int)




# In[32]:


guangda2["hkzhb"] = guangda2["zjshje"]/guangda2["zjqkje"]




# In[33]:


guangda2["ywy"] = guangda2["ywy"].str.upper()




# In[34]:


print guangda2.head()




# In[35]:


guangda3 = guangda2.dropna() 




# In[36]:


print guangda3.shape




# In[37]:


print guangda2.shape




# In[38]:


guangda = guangda3[["ajbh","shfzh18","age","sex","address","zjqkje","zjshje","hkzhb","ywy"]]




# In[39]:


print guangda.head()




# In[40]:


def maxminscale(normal):
    max1=np.max(normal)
    min1=np.min(normal)
    normal=(normal-min1)/(max1-min1)
    return normal




# In[41]:


def datascale(scaledata):
    mean1=np,mean(scaledata)
    std1=np.std(scaledata)
    scaledata=(scaledata-mean1)/std1
    return scaledata
    




# In[42]:


print guangda.describe()




# In[43]:


guangda = guangda[guangda.zjqkje>0]




# In[44]:


guangda["age"] = maxminscale(guangda["age"])




# In[45]:


guangda["address"] = maxminscale(guangda["address"])




# In[46]:


guangda["zjqkje"] = maxminscale(guangda["zjqkje"])




# In[47]:


guangda["zjshje"] = maxminscale(guangda["zjshje"])




# In[48]:


guangda["hkzhb"] = maxminscale(guangda["hkzhb"])




# In[49]:


guangda.head()




# In[296]:


#测试读写成excel




# In[300]:


"""
testt = pd.read_csv('dzd.csv',encoding='UTF-8')




import datetime
starttime = datetime.datetime.now()




writer = pd.ExcelWriter('output1.xlsx',options={'encoding':'utf-8'})
testt.to_excel(writer,'Sheet1',encoding='UTF-8')
writer.save()




endtime = datetime.datetime.now()
(endtime - starttime).seconds
"""




# In[140]:


def distEd(vecA,vecB):
    return np.sqrt((np.square(vecA-vecB)).sum())




# In[320]:


testclust = guangda[["age","sex","address","zjqkje","zjshje","hkzhb"]]




# In[321]:


testclust["sex"] = testclust["sex"].astype(int)




# In[322]:


testclust.describe()




# In[323]:


test1 = np.empty_like(testclust)




# In[324]:


test1 = testclust




# In[71]:


from sklearn.cluster import KMeans




# In[325]:


a = np.array(range(10))
for j in range(2,12):
    kmeanss = KMeans(n_clusters=j, init='k-means++', n_init=10, max_iter=300,algorithm='auto').fit(test1)
    a[j-2] = kmeanss.inertia_
a




# In[326]:


x = np.array(range(1,11))
x
y=a
y
plt.xlabel("Cluster_k")
plt.ylabel("MSE")
plt.plot(x, y)
plt.show()




# In[327]:


kmeans = KMeans(n_clusters=5, init='k-means++', n_init=10, max_iter=300,algorithm='auto').fit(test1)




# In[328]:


test1["label"] = kmeans.labels_




# In[329]:


kcenters = kmeans.cluster_centers_
kcenters




# In[330]:


kmeans.inertia_




# In[383]:


test1 = test1.sort('label')
test1




# In[ ]:


#求取均方误差




# In[376]:


def centerDistance(labeldata,centerdata):
    
    ldata0 = labeldata[labeldata.label==0]
    ldata0 = ldata0.iloc[:,0:6]
    ldata00 = np.array(ldata0)
    
    ldata1 = labeldata[labeldata.label==1]
    ldata1 = ldata1.iloc[:,0:6]
    ldata11 = np.array(ldata1)
    
    ldata2 = labeldata[labeldata.label==2]
    ldata2 = ldata2.iloc[:,0:6]
    ldata22 = np.array(ldata2)
    
    ldata3 = labeldata[labeldata.label==3]
    ldata3 = ldata3.iloc[:,0:6]
    ldata33 = np.array(ldata3)
    
    ldata4 = labeldata[labeldata.label==4]
    ldata4 = ldata4.iloc[:,0:6]
    ldata44 = np.array(ldata4)
    
    test0 = ldata00-kcenters[0]
    test0 = test0*test0
    test0 = test0.sum(axis=1)
    test0 = np.sqrt(test0)
    
    test1 = ldata11-kcenters[1]
    test1 = test1*test1
    test1 = test1.sum(axis=1)
    test1 = np.sqrt(test1)
    
    test2 = ldata22-kcenters[2]
    test2 = test2*test2
    test2 = test2.sum(axis=1)
    test2 = np.sqrt(test2)
    
    test3 = ldata33-kcenters[3]
    test3 = test3*test3
    test3 = test3.sum(axis=1)
    test3 = np.sqrt(test3)
    
    test4 = ldata44-kcenters[4]
    test4 = test4*test4
    test4 = test4.sum(axis=1)
    test4 = np.sqrt(test4)
        
        
    test = np.concatenate((test0,test1,test2,test3,test4))
    
    return test




# In[384]:


ttttt = centerDistance(test1,kcenters)




# In[398]:


test1.describe()




# In[409]:


test1




# In[386]:


test1["ddd"] = ttttt




# In[403]:


test1["label"] = test1["label"].astype("int")




# In[422]:


test2 = test1.sort(['label','ddd'])




# In[423]:


test3 = pd.DataFrame(test2)




# In[428]:


test3 = test3.sort()
test3




# In[429]:


test3["ywy"] = guangda["ywy"]




# In[430]:


test3




# In[432]:


test3 = test3.sort(["label","ddd"])
test3