def get_lm_pricing(workbook, sheet):
import openpyxl as xl
Workbook = xl.load_workbook(workbook)
Sheet = Workbook[sheet]
Pricing = {}
# Get State in DIct
nb_state = instance(Sheet, starting_row=3)
for r in range(nb_state):
Pricing[cell(Sheet, 3 + r, 2)] = {}
# Get carriers
c = 0
Carriers = []
while cell(Sheet, 1, 4 + 3 * c) is not None:
Carriers.append(cell(Sheet, 1, 4 + 3 * c))
c += 1
# Create Dictionnaries
for r in range(len(Pricing)):
for c in range(len(Carriers)):
# Append only if there is pricing info
if cell(Sheet, r + 3, 3 * c + 3) is not None:
Pricing[cell(Sheet, r + 3, 2)][Carriers[c]] = {
'Flat': cell(Sheet, r + 3, 3 + 3 * c),
'Break': cell(Sheet, r + 3, 4 + 3 * c),
'Extra': cell(Sheet, r + 3, 5 + 3 * c)
}
return Pricing
def neig_states(state_code, Sheet):
a = instance(Sheet)
List = [state_code]
for r in range(a):
if cell(Sheet, r + 2, 1) == state_code:
List.append(cell(Sheet, r + 2, 2))
if cell(Sheet, r + 2, 2) == state_code and cell(Sheet, r + 2,
3) == "1st":
List.append(cell(Sheet, r + 2, 1))
return List
def compute_distance(Workbook, Sheet, column_origin, column_destination,
column_distance):
import openpyxl as xl
from geopy.distance import vincenty
from Procedures import instance, cell, geocode2
print('Open File')
wb = xl.load_workbook(Workbook)
wsdist = wb[Sheet]
print('Open Database')
wdata = xl.load_workbook('Excel Files/Zip_latlong.xlsx')
wslatlong = wdata['Zip']
linelatlong = instance(wslatlong)
# Collect Data
Zip_lat_long = {}
for r in range(linelatlong):
Zip_lat_long[str(cell(wslatlong, r + 2,
1))] = (cell(wslatlong, r + 2,
2), cell(wslatlong, r + 2, 3))
print('Collecting Data ', r * 100 / linelatlong, '%')
# Compute distance
linedistance = instance(wsdist)
# a serve to know if zipcode not in database appears
a = 0
for r in range(linedistance):
try:
wsdist.cell(row=r + 2, column=column_distance).value = vincenty(
Zip_lat_long[str(cell(wsdist, r + 2, column_origin))],
Zip_lat_long[str(cell(wsdist, r + 2,
column_destination))]).miles
except KeyError:
Zip_lat_long[str(cell(
wsdist, r + 2,
column_origin))] = (geocode2(cell(wsdist, r + 2,
column_origin))[2][0],
geocode2(cell(wsdist, r + 2,
column_origin))[2][1])
Zip_lat_long[str(cell(
wsdist, r + 2,
column_destination))] = (geocode2(
cell(wsdist, r + 2, column_destination))[2][0],
geocode2(
cell(wsdist, r + 2,
column_destination))[2][1])
wsdist.cell(row=r + 2, column=column_distance).value = vincenty(
Zip_lat_long[str(cell(wsdist, r + 2, column_origin))],
Zip_lat_long[str(cell(wsdist, r + 2,
column_destination))]).miles
a += 1
print('Compute Distances ', (r + 1) / linedistance * 100, '%')
print('Saving File')
wb.save(Workbook)
# Update database
if a != 0:
print("Update Database")
ZipList = Zip_lat_long.keys()
c = 0
for r in ZipList:
wslatlong.cell(row=c + 2, column=1).value = r
wslatlong.cell(row=c + 2, column=2).value = Zip_lat_long[r][0]
wslatlong.cell(row=c + 2, column=3).value = Zip_lat_long[r][1]
c += 1
print('Updating Database ', c * 100 / len(ZipList), '%')
wdata.save('Excel Files/Zip_latlong.xlsx')
print('Database updated')
# -*- coding: utf-8 -*-
"""
Created on Sat Aug 5 15:30:32 2017
@author: Bastide
"""
import openpyxl as xl
from Procedures import cell, instance, geocode2
wb = xl.load_workbook("Excel Files/Zip_latlong.xlsx")
ws = wb["Zip"]
line = instance(ws)
Latlong = {
cell(ws, r + 2, 1): {
"lat": cell(ws, r + 2, 4),
"long": cell(ws, r + 2, 5)
}
for r in range(line)
}
wb = xl.load_workbook("File_Modified.xlsx")
ws = wb["Zip_lat_long"]
line = instance(ws)
for r in range(line):
try:
ws.cell(row=r + 2, column=2).value = Latlong[str(cell(ws, r + 2,
1))]["lat"]
ws.cell(row=r + 2, column=3).value = Latlong[str(cell(ws, r + 2,
1))]["long"]
# Sheet with DA info
wda = wb["DA_List"]
# Sheet with Zip info
wzip = wb["Zip_Allocation_and_Pricing"]
# Sheet with Distance
wdistance = wb["Distances"]
end_time = time.clock()
print(end_time - start_time)
#Get info from DA { Zip:(Zip, City, State, [Carrier])}
start_time = time.clock()
print("Importing info")
numberDa = instance(wda)
Da = {}
for r in range(numberDa):
try:
Da[str(cell(wda, 2 + r, 2))]['Carrier'].append(cell(wda, r + 2, 4))
except KeyError:
Da[str(cell(wda, 2 + r, 2))] = {
'Zip': str(cell(wda, 2 + r, 2)),
'City': cell(wda, 2 + r, 1),
'State': cell(wda, 2 + r, 3),
'Carrier': [cell(wda, 2 + r, 4)]
}
#Get info from Zipcode (Zip, Volume , City, State)
numberZip = instance(wzip)
Zip = {}
import openpyxl as xl
from Procedures import neig_states, cell, instance, compute_distance
# Open Worksheet
wb = xl.load_workbook('Excel Files\Optimized.xlsx')
# open Sheets and create Distances sheet
w_neig = wb['List_of_Neighboring_States']
w_da = wb['DA_List']
w_zip = wb['Zip_Allocation_and_Pricing']
w_dis = wb['Distances']
wb.remove_sheet(w_dis)
wb.create_sheet('Distances')
w_dis = wb['Distances']
w_opti = wb['Optimization_Results']
#Create list of useful DA zip code based on optimization 1 result
useful_DA_nb = instance(w_opti, 2, 3)
useful_DA = []
for r in range(useful_DA_nb):
useful_DA = list(set().union([str(cell(w_opti, r + 2, 3))], useful_DA))
w_dis.cell(row=1, column=1).value = 'DA'
w_dis.cell(row=1, column=2).value = 'ZipCode'
w_dis.cell(row=1, column=3).value = 'Distance DA-Zip'
# Get number of DA and of Zip
n_da = instance(w_da)
n_zip = instance(w_zip)
# Create dictionnaries for DA and Zip(with volume in tuple)
DA_dict = {}
Zip_dict = {}
"""
# This is the optimization file
import time
import openpyxl as xl
from Procedures import cell, instance, get_lm_pricing
import pulp
# Open Excel worksheet
cost_da = 100*365/12
print("Open Worksheet")
start_time = time.clock()
wb = xl.load_workbook("Excel Files\File_modified_2.xlsx")
w_opti = wb['Optimization_Results']
#Create list of useful DA zip code
useful_DA_nb = instance(w_opti,2,3)
useful_DA = []
for r in range(useful_DA_nb):
useful_DA = list(set().union([str(cell(w_opti,r+2,3))],useful_DA))
# Sheet with DA info
wda = wb["DA_List"]
# Sheet with Zip info
wzip = wb["Zip_Allocation_and_Pricing"]
# Sheet with Distance_Matrix
wdistance = wb["Distances"]
end_time = time.clock()
print(end_time-start_time)
# -*- coding: utf-8 -*-
"""
Created on Sat Aug 12 13:31:18 2017
@author: Bastide
"""
import openpyxl as xl
from Procedures import cell, instance
from uszipcode import ZipcodeSearchEngine
search = ZipcodeSearchEngine()
wb = xl.load_workbook('Excel Files\Standard_File.xlsx')
ws = wb['Zip_Allocation_and_Pricing']
l = instance(ws)
for r in range(l):
if cell(ws, r + 2, 2) is None:
ws.cell(row=r + 2,
column=2).value = search.by_zipcode(cell(ws, r + 2,
1))['State']
wb.save('Excel Files\Standard_File.xlsx')
import openpyxl as xl
from Procedures import neig_states, cell, instance, compute_distance
# Open Worksheet
wb = xl.load_workbook('Excel Files\Standard_File.xlsx')
# open Sheets and create Distances sheet
w_neig = wb['List_of_Neighboring_States']
w_da = wb['DA_List']
w_zip = wb['Zip_Allocation_and_Pricing']
wb.create_sheet('Distances')
w_dis = wb['Distances']
w_dis.cell(row=1, column=1).value = 'DA'
w_dis.cell(row=1, column=2).value = 'ZipCode'
w_dis.cell(row=1, column=3).value = 'Distance DA-Zip'
# Get number of DA and of Zip
n_da = instance(w_da)
n_zip = instance(w_zip)
# Create dictionnaries for DA and Zip(with volume in tuple)
DA_dict = {}
Zip_dict = {}
for r in range(n_da):
try:
# Remove duplicates, DA in same pc but different carriers
DA_dict[cell(w_da, r + 2,
3)] = list(set().union(DA_dict[cell(w_da, r + 2, 3)],
[int(cell(w_da, r + 2, 2))]))
except KeyError:
DA_dict[cell(w_da, r + 2, 3)] = []
DA_dict[cell(w_da, r + 2, 3)].append(int(cell(w_da, r + 2, 2)))