import pandas as pd
import glob
import time
import numpy as num

inter=sorted(glob.glob('*****.csv'))

w='*****.xlsx'
table1=pd.read_excel(w, '*****', index_col=None, na_values=['NA']).fillna(0)


w='*****.csv'
tab=pd.read_csv(w).fillna(0)

tab.is_copy = False
pd.options.mode.chained_assignment = None

t1=time.time()

for i in range(len(tab)):
	if tab["IBR"][i]=='9A' or tab["IBR"][i] == '9B' or tab["IBR"][i] == '09A' or tab["IBR"][i] == '09B':
		tab["IBR"][i]='9'
	if tab["IBR"][i]=='11A' or tab["IBR"][i] == '11B' or tab["IBR"][i]=='11C' or tab["IBR"][i] == '11D' or tab["IBR"][i]=='36B':
		tab["IBR"][i]='11'
	if tab["IBR"][i]=='36A' or tab["IBR"][i] == '36B':
		tab["IBR"][i]='36'
	if tab["IBR"][i]=='13A' or tab["IBR"][i] == '13B' or tab["IBR"][i] == '13C':
		tab["IBR"][i]='13'
	if tab["IBR"][i]=='23A' or tab["IBR"][i] == '23B' or tab["IBR"][i] == '23E' or tab["IBR"][i] == '23F' or tab["IBR"][i] == '23H':
		tab["IBR"][i]='23'
	if tab["IBR"][i]=='26A' or tab["IBR"][i] == '26B' or tab["IBR"][i] == '26C' or tab["IBR"][i] == '26D' or tab["IBR"][i] == '26E':
		tab["IBR"][i]='26'
	if tab["IBR"][i]=='35A' or tab["IBR"][i] == '35B':
		tab["IBR"][i]='35'
	if tab["IBR"][i]=='36A':
		tab["IBR"][i]='36'
	if tab["IBR"][i]=='39A' or tab["IBR"][i] == '39B' or tab["IBR"][i] == '39C' or tab["IBR"][i] == '39D':
		tab["IBR"][i]='39'
	if tab["IBR"][i]=='40A' or tab["IBR"][i] == '40B' or tab["IBR"][i] == '40C':
		tab["IBR"][i]='40'
	if tab["IBR"][i]=='64A' or tab["IBR"][i] == '64B':
		tab["IBR"][i]='64'
	if tab["IBR"][i]=='90A' or tab["IBR"][i] == '90B' or tab["IBR"][i] == '90C' or tab["IBR"][i] == '90H' or tab["IBR"][i] == '90F' or tab["IBR"][i] == '90G' or tab["IBR"][i]=='90J' or tab["IBR"][i]=='90Z':
		tab["IBR"][i]='90'


#convert to string for the join
for i in range(len(table1)):
	table1['IBR_code'][i]=str(table1['IBR_code'][i])

description=table1.set_index([ "IBR_code"])

t2=time.time()
print t2-t1

#index crime
tab["index"]=num.nan

for i in range(len(tab)): #convert to integer
	tab["index"][i]=tab.index[i]+1
	
#join
tab=tab.join(description, on=["IBR"], sort=True, rsuffix='_1', how='outer').fillna(0)
tab=tab[(tab["Reported_address"] != 0)].reset_index(drop=True).fillna(0)
tab["IBR_description"]=tab["crime_des12"]

t3=time.time()
print t3-t2

tab=tab[["Global_ID","Reported_address","Incident_date","Incident_time","Report_date","Report_time","Latitude","Longitude","IBR","IBR_description","Police_Department_Code","PD_description","State_Statute_Literal","State_Statute_Number","flag_geocode",'Fdir_n1','Edir_n1','strname_n1','strtype_n1','Enum_n1','Fdir_n2','Edir_n2','strname_n2','strtype_n2','Enum_n2','comname','mroad1','mratio1','wcorr1','wratio1','mroad2','mratio2','wcorr2','wratio2','match']]
tab=tab.replace("",num.nan)
tab=tab.replace("0",num.nan)
tab=tab.replace("00",num.nan)
tab=tab.replace(0,num.nan)
tab.to_csv('*****.csv',index=False)

for i in range(len(tab)):
	tab['Global_ID'][i]=str(tab['Global_ID'][i])
description=tab.set_index([ "Global_ID"])

name1=[i[i.find('inter'):i.rfind('C.csv')+1].replace('_matchgeo','') for i in inter]

for p, q in zip((inter), (name1)):
	table1=pd.read_csv(p)
	for i in range(len(table1)):
		tab['Global_ID'][i]=str(tab['Global_ID'][i])
	table1=table1.join(description, on=["Global_ID"], sort=True, rsuffix='_1', how='outer').fillna(0)
	table1=table1[(table1["Reported_address"] != 0)].reset_index(drop=True).fillna(0)
	table1["IBR_description"]=table1["IBR_description_1"]
	table1["IBR"]=table1["IBR_1"]
	table1=table1[["Global_ID","Reported_address","Incident_date","Incident_time","Report_date","Report_time","Latitude","Longitude","IBR","IBR_description","Police_Department_Code","PD_description","State_Statute_Literal","State_Statute_Number","flag_geocode",'Fdir_n1','Edir_n1','strname_n1','strtype_n1','Enum_n1','Fdir_n2','Edir_n2','strname_n2','strtype_n2','Enum_n2','comname','mroad1','mratio1','wcorr1','wratio1','mroad2','mratio2','wcorr2','wratio2','match']]
	table1.to_csv('*****.csv',index=False)


