content: 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)
  
  
  