fj911

# -*- coding: utf-8 -*-
"""
Created on Thu Aug 24 15:14:07 2017

@author: Administrator
"""

import pymongo
from pymongo import MongoClient
import numpy as np
import pandas as  pd
from pandas import DataFrame,Series
from numpy import row_stack,column_stack
from dateutil.parser import parse
from matplotlib.pylab import date2num
import random
from collections import Counter



cy_rg=["上海","杨浦"]
dirtic_list=["大运盛城","凤城三村","凤城六村",
             "凤城花园","东方名园"]

#导入经度和纬度,把经纬度转化成DataFrame

client1 = MongoClient('192.168.0.136',27017)
db1 = client1.fangjia
seaweed1 = db1.seaweed

#print(seaweed.find_one({"city":"上海","region":"浦东","name":"康桥半岛二期"},{"lat2":1,"lng2":1}))
'''
cy_rg=["上海","杨浦"]
dirtic_list=["凤城四村","凤城三村","大运盛城",
             "新时代富嘉花园","新时代花园"]
'''


query1 = {"status":0,"cat":"district","city":cy_rg[0],"region":cy_rg[1], "name":{"$in":dirtic_list}}
fields1 = {"lat2":1,"lng2":1, "city":1,"region":1,"cat":1,"name":1}

lct= list()
for s in seaweed1.find(query1, fields1):
    lct.append(s)

lf=DataFrame(lct)

le=lf    

le.index=le['name'] 

lr=le[['lng2','lat2']]


#从公司的数据库中导入数据
client = MongoClient('192.168.10.88',27777)
db = client.fangjia
seawater = db.seawater
seawater.find_one()

# 索引数据库里的数据
query = {"city":cy_rg[0],"cat":"sell","region":cy_rg[1],
         "district_name":{"$in":dirtic_list},


         "p_date":{"$gt":20170815}}


lt= seawater.count(query)
print(lt)
pos = list()
#数据转化为数组，数组的元素为字典
for s in seawater.find(query).limit(lt-1):
    pos.append(s)

#将数据转化为  DataFrame
data=DataFrame(pos)

'''
p1=pd.DataFrame(Series(pos[1]))  

for i in range(1,42):
    s=pos[i]
    p2=pd.DataFrame(Series(s))   
    p1 = pd.concat([p1,p2],axis=1)

'''



data.to_excel('data.xls')

#需要提取的特征
choose_class=['total_price','area','height','room','build_date',
             'direction','hall','toilet','fitment','district_name','p_date']

dc=data[choose_class]

#增加两列，分别是经度和纬度
dc['lng2']=0
dc['lat2']=1


for i in range(dc.shape[0]):

    for j in range(lr.shape[0]):

        if dc['district_name'][i]==lr.index[j]:

            dc['lng2'][i]=lr['lng2'][j]
            dc['lat2'][i]=lr['lat2'][j]

#将'total_price' 转化为均价，并把均价赋值给'total_price'
mean_price=dc['total_price']/dc['area']

dc['total_price']=mean_price #将'total_price' 转化为均价

'''
#这段代码用于把时间转化成一个连续的数，至于是否有效有待观察
####################
h=dc['p_date']
for i in range(1,len(h)):   
    a=int(h[i])    
    b=str(a)    
    c=parse(b)        
    e = date2num(c)    
    h[i]=e 

dc['p_date']=h
###################
''' 
dc.to_excel('dc.xls')



def turn_str_to_number(dc):

    h=dc['p_date']
    for i in range(1,len(h)):   
        a=int(h[i])    
        b=str(a)    
        c=parse(b)        
        e = date2num(c)    
        h[i]=e 
        dc['p_date']=h
    return dc  

dc=turn_str_to_number(dc)



'''
for i in dc['direction'].index:

    if ('南' in str(dc['direction'][i])) :
        dc['direction'][i]=0
    elif('透' in str(dc['direction'][i])):

        dc['direction'][i]=1
    else:

        dc['direction'][i]=2

'''

def op_direc(dc):
    for i in dc['direction'].index:

        if ('南' in str(dc['direction'][i])) :
           dc['direction'][i]=0
        elif('透' in str(dc['direction'][i])):

           dc['direction'][i]=1
        else:

           dc['direction'][i]=2  

    return dc  

dc= op_direc(dc)


def op_fitmen(dc):
    for i in dc['fitment'].index:
        if ('豪' or '精') in str(dc['fitment'][i]) :
           dc['fitment'][i]=0

        else :
           dc['fitment'][i]=1

    return dc


dc = op_fitmen(dc)


'''       
for i in dc['fitment'].index:
    if ('豪' or '精') in str(dc['fitment'][i]) :
        dc['fitment'][i]=0

    else :
        dc['fitment'][i]=1

'''



'''

dc=dc.fillna({'height':dc['height'].mean(),
              'room':dc['room'].mean(),
              'toilet':dc['toilet'].mean(),
              'hall':dc['hall'].mean(),
              })

'''

dc=dc.fillna(method='ffill')

#提取字符串中的数字日期，如'1988年' 提取为1988


import re
def numbertake(stnum):

    lg = str(stnum)
    lgttcc = re.sub("\D", "", lg)
    return int(lgttcc)

al=list(dc['build_date'].values)
dc=dc.fillna(Counter(al).most_common(1)[0][0])

for i in range(dc.shape[0]):
    dc['build_date'][i:i+1]=numbertake(dc['build_date'][i:i+1].values[0])




ds=dc.drop('district_name',axis=1)

'''
def take_number(dc):
    import re
    def numbertake(stnum):

        lg = str(stnum)
        lgttcc = re.sub("\D", "", lg)
        return int(lgttcc)

    al=list(dc['build_date'].values)
    dc=dc.fillna(Counter(al).most_common(1)[0][0])
    for i in range(dc.shape[0]):
        dc['build_date'][i:i+1]=numbertake(dc['build_date'][i:i+1].values[0])
    return dc

dc=take_number(dc)
'''
ds = ds.drop([0],axis=0)




def isolate_point(dc):
    lll=list()
    for j in dirtic_list:
        fg=dc[dc['district_name']==j]
        hh=fg['total_price'].values
        hmean=hh.mean()
        hstd=hh.std()
        lg=list(fg['total_price'].index)


        for i in lg:
            if (fg['total_price'][i]<(hmean-1.645*hstd))or(fg['total_price'][i]>(hmean+1.645*hstd)):

               lll.append(i)
        return lll

lll=isolate_point(dc)

'''
#####################################################################
#取出离群点的索引
lll=list()
for j in dirtic_list:
    fg=dc[dc['district_name']==j]
    hh=fg['total_price'].values
    hmean=hh.mean()
    hstd=hh.std()
    lg=list(fg['total_price'].index)


    for i in lg:
        if (fg['total_price'][i]<(hmean-1.645*hstd))or(fg['total_price'][i]>(hmean+1.645*hstd)):

            lll.append(i)



######################################################################
'''

data_all = ds.drop(lll,axis=0)

#data_all = ds.drop([0],axis=0)

data_all.to_excel('data_all.xls')

#sample_number=data_all.shape[0]



#kk=int(0.08 *sample_number)


def divdata(dvt):


    listall=list(dvt.index)

    kk=int(0.08 *len(listall))

    list_index=[random.randint(1,len(listall)) for _ in range(kk)]

    test_label=[listall[x] for x in list_index]


    return test_label



#test_label=[random.randint(1,sample_number) for _ in range(kk)]

test_label=divdata(data_all)


data_train= data_all.drop(test_label,axis=0)
#data_train.to_excel('data_train.xls')

data_max = data_train.max()
data_min = data_train.min()

def normolize_data(data_train):
    data_train1 = (data_train-data_min)/(data_max-data_min+0.2)
    return data_train1

'''
data_max = data_train.max()
data_min = data_train.min()

data_train1 = (data_train-data_min)/(data_max-data_min+0.2) #数据标准化

#knife=int(0.95*(data_train.shape[0]))#用于切割数据80%用于训练，20%用于计算
'''

data_train1=normolize_data(data_train)

x_train = data_train1.iloc[:,1:12].as_matrix() #训练样本标签列
y_train = data_train1.iloc[:,0:1].as_matrix() #训练样本特征



from keras.models import Sequential
from keras.layers.core import Dense, Dropout, Activation

model = Sequential() #建立模型
model.add(Dense(input_dim = 11, output_dim = 48)) #添加输入层、隐藏层的连接
model.add(Activation('relu')) #以Relu函数为激活函数

model.add(Dense(input_dim = 48, output_dim = 100)) #添加隐藏层、隐藏层的连接
model.add(Activation('relu')) #以Relu函数为激活函数


model.add(Dense(input_dim = 100, output_dim = 50)) #添加隐藏层、隐藏层的连接
model.add(Activation('relu')) #以Relu函数为激活函数

model.add(Dense(input_dim = 50, output_dim = 36)) #添加隐藏层、隐藏层的连接
model.add(Activation('relu')) #以Relu函数为激活函数

model.add(Dense(input_dim = 36, output_dim = 12)) #添加隐藏层、隐藏层的连接
model.add(Activation('relu')) #以Relu函数为激活函数
model.add(Dense(input_dim = 12, output_dim = 12)) #添加隐藏层、隐藏层的连接
model.add(Activation('relu')) #以Relu函数为激活函数


model.add(Dense(input_dim = 12, output_dim = 1)) #添加隐藏层、输出层的连接
model.add(Activation('sigmoid')) #以sigmoid函数为激活函数
#编译模型，损失函数为binary_crossentropy，用adam法求解
model.compile(loss='mean_squared_error', optimizer='adam')
model.fit(x_train, y_train, nb_epoch = 10, batch_size = 2) #训练模型

model.save_weights('net.model') #保存模型参数


test=data_all.ix[test_label,:]

test.to_excel('test.xls')

#test_max = test.max()
#test_min = test.min()
# = (test-data_min)/(data_max-data_min+0.2) 

data_test=normolize_data(test)

x_test = data_test.iloc[:,1:12].as_matrix()
y_test = data_test.iloc[:,0:1].as_matrix()


r = (model.predict(x_test))
rt=r*(data_max.values-data_min.values+0.2)+data_min.values
#print(rt.round(2))

#################################
p=rt[:,0:1].flatten()





#如果小区有房价等数据，则进行如下后处理，即控制房价相对于小区均价的波动幅度
'''
jk=dc.drop([0],axis=0)

cx=list(test.index)
p_dmean_ratio=list()
#p_dmean_ratio=list(range(len(cx)))
for j in range(len(cx)):

    pk=jk[jk['district_name']==jk['district_name'][cx[j]]]

    dmean=pk['total_price'].values.mean()

    pmn=p[j]/dmean

    p_dmean_ratio.append(pmn)

    if (pmn>1.19) or(pmn<0.81):
        p[j]=dmean
'''        

def wave_move(dc,p,test):
    jk=dc.drop([0],axis=0)

    cx=list(test.index)
    p_dmean_ratio=list()
#p_dmean_ratio=list(range(len(cx)))
    for j in range(len(cx)):

        pk=jk[jk['district_name']==jk['district_name'][cx[j]]]

        dmean=pk['total_price'].values.mean()

        pmn=p[j]/dmean

        p_dmean_ratio.append(pmn)

        if (pmn>1.19) or(pmn<0.81):
            p[j]=dmean
    return p



#如果没有小区房源信息，则进行如下算法，对小区进行多维聚类。

'''

#计算任意两条房源的欧氏距离

ppd=data_all.values

p0=ppd[0]
a=list()
for i in range(1,ppd.shape[0]):
    for j in range(i+1,ppd.shape[0]):
        X=np.vstack([ppd[i],ppd[j]])
        d2=pdist(X,'minkowski',p=2)
        a.append(list(d2))
print(max(a))

'''        
from scipy.spatial.distance import pdist
################################
def distence(data_all):
    ppd=data_all.values

    p0=ppd[0]
    a=list()
    for i in range(1,ppd.shape[0]):
        for j in range(i+1,ppd.shape[0]):
            X=np.vstack([ppd[i],ppd[j]])
            d2=pdist(X,'minkowski',p=2)
            a.append(list(d2))
    return a

dist=distence(data_train1)



p=wave_move(dc,p,test)

predict=np.array([p]).T
'''
realvalue= test.iloc[:,0:1].as_matrix()

error=abs((predict-realvalue)/realvalue)*100


pro=(np.array([p_dmean_ratio]).T-1)*100


gek=column_stack((predict,realvalue,error,pro))

#geek=DataFrame(gek,columns=['predict','realvalue','error'])
geek=DataFrame(gek,columns=['predict','realvalue','error','p_dmean_ratio'],
               index=test.index)



test_and_geek=pd.concat([test,geek],axis=1)

output_label=['total_price', 'area', 'height', 'room', 'direction', 'hall',
              'toilet','fitment', 'p_date',  'predict', 'realvalue', 'error','p_dmean_ratio']


tg=test_and_geek[output_label]

output_label1=['mean_price', 'area', 'height', 'room', 'direction', 'hall',
              'toilet','fitment', 'p_date',  'predict', 'realvalue', 'error','p_dmean_ratio']

tg.columns=output_label1

tg.to_excel('tg.xls')

print(tg)

print('平均计算误差：','%.2f'%error.mean(),'%')
'''

# -*- coding: utf-8 -*-
"""
Created on Mon Sep 11 16:22:27 2017

@author: Administrator
"""

import numpy as np
import pandas as pd 

dirtic_list=["大运盛城","凤城三村","凤城六村",
             "凤城花园","东方名园"]

lgttcc=pd.read_excel('dc1.xls')

'''
inner=lgttcc.loc[[0],:]
outer=lgttcc.loc[[0],:]

for i in range(lgttcc.shape[0]):
    if lgttcc['district_name'][i] in dirtic_list:
        inner=pd.concat([inner,lgttcc['district_name'][i]])

    else:
        outer=pd.concat([outer,lgttcc['district_name'][i]])

'''






lg1= op_direc(lgttcc)
lg2 = op_fitmen(lg1)

lg3=lg2.fillna(method='ffill')

dc=lg3
import re
def numbertake(stnum):

    lg = str(stnum)
    lgttcc = re.sub("\D", "", lg)
    return int(lgttcc)

al=list(dc['build_date'].values)
dc=dc.fillna(Counter(al).most_common(1)[0][0])

for i in range(dc.shape[0]):
    dc['build_date'][i:i+1]=numbertake(dc['build_date'][i:i+1].values[0])


dc1=turn_str_to_number(dc)

inner=dc1.loc[[0],:]
outer=dc1.loc[[0],:]

for i in range(dc1.shape[0]):
    if dc1['district_name'][i] in dirtic_list:
        inner=pd.concat([inner,dc1.loc[[i],:]])

    else:
        outer=pd.concat([outer,dc1.loc[[i],:]])


#dc1=dc.drop('district_name',axis=1)

inner = inner.drop([0],axis=0)
outer= outer.drop([0],axis=0)

outer=outer.drop('district_name',axis=1)

outer_normal=normolize_data(outer)

dist_np=np.array(dist)

dist_mean=dist_np.mean()
dist_std=dist_np.std()

data_train1

b=list()

for i in range(outer_normal.shape[0]):
    for j in range(data_train1.shape[0]):
        X=np.vstack([outer_normal.values[i],data_train1.values[j]])
        d2=pdist(X,'minkowski',p=2)

        if (d2<0.5):
        #if (d2<dist_mean+0.1*dist_std) and (d2>dist_mean-0.1*dist_std):

           b.append(data_train1.values[j][0])