def findUniqueAddr(existFilepath, existSheetname):
excelResult = excel.excelRead(existFilepath, existSheetname)
temp_addr0 = ''
temp_addr1 = ''
temp_addr2 = ''
temp_addr3 = ''
insertList = []
for row in excelResult[1:]:
insertListTemp = []
addr0 = str(row[0].value)
addr1 = str(int(row[1].value))
addr2 = str(int(row[2].value))
addr3 = str(row[3].value)
if (addr0 + addr1 + addr2 + addr3) != (temp_addr0 + temp_addr1 +
temp_addr2 + temp_addr3):
insertListTemp.append(addr0)
insertListTemp.append(addr1)
insertListTemp.append(addr2)
insertListTemp.append(addr3)
insertList.append(insertListTemp)
temp_addr0 = addr0
temp_addr1 = addr1
temp_addr2 = addr2
temp_addr3 = addr3
return insertList
def findUniqueAddr(existFilepath, existSheetname):
excelResult = excel.excelRead(existFilepath, existSheetname)
temp_addr0 = ''
temp_addr1 = ''
temp_addr2 = ''
temp_addr3 = ''
insertList = []
for row in excelResult[1:]:
insertListTemp = []
addr0 = str(row[0].value)
addr1 = str(int(row[1].value))
addr2 = str(int(row[2].value))
addr3 = str(row[3].value)
if (addr0+addr1+addr2+addr3) != (temp_addr0+temp_addr1+temp_addr2+temp_addr3) :
insertListTemp.append(addr0)
insertListTemp.append(addr1)
insertListTemp.append(addr2)
insertListTemp.append(addr3)
insertList.append(insertListTemp)
temp_addr0 = addr0
temp_addr1 = addr1
temp_addr2 = addr2
temp_addr3 = addr3
return insertList
def findUniqueAddr2(existFilepath, existSheetname, inputList):
excelResult = excel.excelRead(existFilepath, existSheetname)
temp_addr0 = ''
temp_addr1 = ''
temp_addr2 = ''
temp_addr3 = ''
insertList = []
for inputRow in inputList:
temp_addr0 = inputRow[0]
temp_addr1 = str(inputRow[1])
temp_addr2 = str(inputRow[2])
temp_addr3 = inputRow[3]
insertListTemp = []
index = 0
for existingRow in excelResult:
index += 1
addr0 = str(existingRow[0].value)
addr1 = str(int(existingRow[1].value))
addr2 = str(int(existingRow[2].value))
addr3 = str(existingRow[3].value)
if (addr0 + addr1 + addr2 + addr3) == (temp_addr0 + temp_addr1 +
temp_addr2 + temp_addr3):
break
if index == len(excelResult):
insertListTemp.append(temp_addr0)
insertListTemp.append(temp_addr1)
insertListTemp.append(temp_addr2)
insertListTemp.append(temp_addr3)
insertList.append(insertListTemp)
return insertList
def findUniqueAddr2(existFilepath, existSheetname, inputList):
excelResult = excel.excelRead(existFilepath, existSheetname)
temp_addr0 = ''
temp_addr1 = ''
temp_addr2 = ''
temp_addr3 = ''
insertList = []
for inputRow in inputList:
temp_addr0 = inputRow[0]
temp_addr1 = str(inputRow[1])
temp_addr2 = str(inputRow[2])
temp_addr3 = inputRow[3]
insertListTemp = []
index = 0
for existingRow in excelResult:
index+=1
addr0 = str(existingRow[0].value)
addr1 = str(int(existingRow[1].value))
addr2 = str(int(existingRow[2].value))
addr3 = str(existingRow[3].value)
if (addr0+addr1+addr2+addr3) == (temp_addr0+temp_addr1+temp_addr2+temp_addr3) :
break
if index == len(excelResult):
insertListTemp.append(temp_addr0)
insertListTemp.append(temp_addr1)
insertListTemp.append(temp_addr2)
insertListTemp.append(temp_addr3)
insertList.append(insertListTemp)
return insertList
import math
if __name__ == '__main__':
## Read excel file##
# filename 1 - previous year including logged UnitPrice
# filename 2 - current year
filename1 = "SaleApartment201303"
filepath1 = filename1 + ".xlsx"
sheetname1 = filename1
filename2 = "SaleApartment201403"
filepath2 = filename2 + ".xlsx"
sheetname2 = filename2
preVar = excel.excelRead(filepath1, sheetname1)
curVar = excel.excelRead(filepath2, sheetname2)
# structure of the variable reading excel sheet
# 1st row - field name list - preVar[0] (list format)
# 2nd row ~ - values
## Setting neighborhood condition
bandwidth = 2000.0 # setting bandwidth (maximum distance)
mindist = -1.0 # setting minimum distance to avoid identical apartment complex itself
knn = 10 # the maximum number of neighbors
lagVar = [] # initialize lag variable list (final column)
for curValue in curVar[1:]: # 2nd row ~
curX = float(curValue[0].value) # X coordinate of current year data
# -*- coding: utf-8 -*- """ Created on Aug 23, 2015 @author: Administrator """ import FileIO.Excel as excel filepath = "Seoul_Building_all_point2.xlsx" sheetname = "Seoul_Building_all_point2" excelSeoul = excel.excelRead(filepath, sheetname) seoul11740 = [] seoul11710 = [] seoul11680 = [] seoul11650 = [] seoul11620 = [] seoul11590 = [] seoul11560 = [] seoul11545 = [] seoul11530 = [] seoul11500 = [] seoul11470 = [] seoul11440 = [] seoul11410 = [] seoul11380 = [] seoul11350 = [] seoul11320 = [] seoul11305 = []
'''
Created on Jun 1, 2015
@author: Bumsub
'''
import FileIO.Excel as excel
import Web.APIs.Geocoding as geocode
if __name__ == '__main__':
result = excel.excelRead("Texas.xlsx", "Sheet1")
#print(type(str(result[0][1].value)))
#print(str(result[0][1].value))
geocodingResult = geocode.geocodeList(result)
#print(geocodingResult)
excel.excelWriteOnExistingFile("Texas.xlsx", "Sheet1", "I", geocodingResult)
print("Success")
#
# print len(uniqueAddr2)
# if len(uniqueAddr2) != 0:
# excel.excelWriteOnExistingFile2(filenameDic, sheetnameDic, uniqueAddr2)
# print('saved successfully in Dictionary')
# ##Part 2 - #3 Geocoding - AddressMatching project
## Part 3
#4 - Matching excel file and BD_MGT_SN in Dictionary
filenameMat = '201503SaleApartment.xlsx'
sheetnameMat = 'Seoul'
matResult = excel.excelRead(filenameMat, sheetnameMat)
#4-1 - EMD code matching
filenameEMD = 'Seoul_EMD_code.xlsx'
sheetnameEMD = 'Seoul_EMD_code'
resultEMD = excel.excelRead(filenameEMD, sheetnameEMD)
emdMatList = []
for addrMat in matResult[1:]:
tempEMD = '-1'
addrStrip = str(addrMat[0].value).strip()
for rowEMD in resultEMD:
rowEMDStr = str(rowEMD[1].value)
'''
Created on 2016. 7. 17.
@author: Administrator
'''
import FileIO.Excel as excel
if __name__ == '__main__':
result = excel.excelRead("200603SaleApartment.xlsx", "Seoul", fromRow=4)
print len(result)
excel.excelWriteOnExistingFile("test.xlsx", "Sheet1", 'A', result, woFirst=0)
# print type(result[0][0])
# print type(result[1][0])
#-*- coding: utf-8 -*- ''' Created on Aug 23, 2015 @author: Administrator ''' import FileIO.Excel as excel filepath = 'Seoul_Building_all_point2.xlsx' sheetname = 'Seoul_Building_all_point2' excelSeoul = excel.excelRead(filepath, sheetname) seoul11740 = [] seoul11710 = [] seoul11680 = [] seoul11650 = [] seoul11620 = [] seoul11590 = [] seoul11560 = [] seoul11545 = [] seoul11530 = [] seoul11500 = [] seoul11470 = [] seoul11440 = [] seoul11410 = [] seoul11380 = [] seoul11350 = [] seoul11320 = [] seoul11305 = [] seoul11290 = []
#
# index1 += 1
#
# else:
# locationEQB = seoul.findEQBInfo(addr1, matchingResult[1])
# dicRow.append(locationEQB[0])
# dicRow.append(locationEQB[1])
#
# index2 += 1
#
# newDic.append(dicRow)
#
#
# print("index1= "+str(index1))
# print("index2= "+str(index2))
#
#
# newFilename = filename+'_result.xlsx'
# newSheetname = 'Sheet2'
# excel.excelWriteNewFile(newFilename, newSheetname, newDic)
## Part 2 - Geocoding with google
filenameRem = 'Dictionary_result.xlsx'
sheetnameRem = 'Sheet1'
remainDic = excel.excelRead(filenameRem, sheetnameRem)
geocodingResult = geocode.geocodeList(remainDic)
excel.excelWriteOnExistingFile(filenameRem, sheetnameRem, "H",
geocodingResult)
# addr2.append(tempEMD)
#
# print len(uniqueAddr2)
# if len(uniqueAddr2) != 0:
# excel.excelWriteOnExistingFile2(filenameDic, sheetnameDic, uniqueAddr2)
# print('saved successfully in Dictionary')
# ##Part 2 - #3 Geocoding - AddressMatching project
## Part 3
#4 - Matching excel file and BD_MGT_SN in Dictionary
filenameMat = '201503SaleApartment.xlsx'
sheetnameMat = 'Seoul'
matResult = excel.excelRead(filenameMat, sheetnameMat)
#4-1 - EMD code matching
filenameEMD = 'Seoul_EMD_code.xlsx'
sheetnameEMD = 'Seoul_EMD_code'
resultEMD = excel.excelRead(filenameEMD, sheetnameEMD)
emdMatList = []
for addrMat in matResult[1:]:
tempEMD = '-1'
addrStrip = str(addrMat[0].value).strip()
for rowEMD in resultEMD:
rowEMDStr = str(rowEMD[1].value)
import math
if __name__ == '__main__':
## Read excel file##
# filename 1 - previous year including logged UnitPrice
# filename 2 - current year
filename1 = "SaleApartment201303"
filepath1 = filename1+".xlsx"
sheetname1 = filename1
filename2 = "SaleApartment201403"
filepath2 = filename2+".xlsx"
sheetname2 = filename2
preVar = excel.excelRead(filepath1, sheetname1)
curVar = excel.excelRead(filepath2, sheetname2)
# structure of the variable reading excel sheet
# 1st row - field name list - preVar[0] (list format)
# 2nd row ~ - values
## Setting neighborhood condition
bandwidth = 2000.0 # setting bandwidth (maximum distance)
mindist = -1.0 # setting minimum distance to avoid identical apartment complex itself
knn = 10 # the maximum number of neighbors
lagVar = [] # initialize lag variable list (final column)
for curValue in curVar[1:]: # 2nd row ~
curX = float(curValue[0].value) # X coordinate of current year data
# dicRow.append(matchingResult[3])
#
# index1 += 1
#
# else:
# locationEQB = seoul.findEQBInfo(addr1, matchingResult[1])
# dicRow.append(locationEQB[0])
# dicRow.append(locationEQB[1])
#
# index2 += 1
#
# newDic.append(dicRow)
#
#
# print("index1= "+str(index1))
# print("index2= "+str(index2))
#
#
# newFilename = filename+'_result.xlsx'
# newSheetname = 'Sheet2'
# excel.excelWriteNewFile(newFilename, newSheetname, newDic)
## Part 2 - Geocoding with google
filenameRem = 'Dictionary_result.xlsx'
sheetnameRem = 'Sheet1'
remainDic = excel.excelRead(filenameRem, sheetnameRem)
geocodingResult = geocode.geocodeList(remainDic)
excel.excelWriteOnExistingFile(filenameRem, sheetnameRem, "H", geocodingResult)
