-
Notifications
You must be signed in to change notification settings - Fork 0
/
export.py
214 lines (172 loc) · 8.19 KB
/
export.py
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
131
132
133
134
135
136
137
138
139
140
141
142
143
144
145
146
147
148
149
150
151
152
153
154
155
156
157
158
159
160
161
162
163
164
165
166
167
168
169
170
171
172
173
174
175
176
177
178
179
180
181
182
183
184
185
186
187
188
189
190
191
192
193
194
195
196
197
198
199
200
201
202
203
204
205
206
207
208
209
210
211
212
213
214
# -*- coding: utf-8 -*-
"""
Created on Tue Mar 30 20:53:53 2021
@author: Tarandeep
"""
import numpy as np
import pandas as pd
"""
This function will take SKU assignment and present it in a visual form that
will be exported to an Excel sheet for use by company
"""
def visualSKUOutput(SKUAssignment, aisleTuple):
# Aisle tuple - start aisles at 0 instead of 1
# https://stackoverflow.com/questions/17418108/elegant-way-to-perform-tuple-arithmetic
aisleTuple = tuple(np.subtract(aisleTuple, (1, 1, 1)))
# obtain list of columns in SKU Assignment
listColumns = SKUAssignment['Column'].unique().tolist()
listColumns.sort()
# create empty 2D array
array = [["" for i in range((np.int_(SKUAssignment['Column'].max()))*3)]
for j in range(aisleTuple[len(aisleTuple)-1])]
counter = 0
for colNo in range(0, np.int_(SKUAssignment['Column'].max())):
# if column has skus, do something
if colNo in listColumns:
# this will find the index of the column in this list, useful to determine if print...
# should be done on left hand side or right hand side
actualCol = listColumns.index(colNo)
# if column is even
if actualCol % 2 != 0:
for rowNo in range(aisleTuple[0], aisleTuple[len(aisleTuple)-1]):
if rowNo in aisleTuple and rowNo != 0:
array[rowNo][counter] = "X"
array[rowNo][counter+1] = "X"
array[rowNo][counter+2] = "X"
else:
# create dataframe that is only
df = SKUAssignment.loc[(SKUAssignment['Row'] == rowNo) & (
SKUAssignment['Column'] == colNo)]
if df.empty:
pass # keep cells empty
else:
array[rowNo][counter] = df.loc[df['A/B']
== 'A'].iloc[0]['SKU']
array[rowNo][counter+1] = df.loc[df['A/B']
== 'B'].iloc[0]['SKU']
# https://stackoverflow.com/questions/134934/display-number-with-leading-zeros
# +1 to start from 1 instead of 0
array[rowNo][counter+2] = "{:02d}".format(
actualCol+1) + "{:02d}".format(rowNo+1)
counter += 3
# if column is odd
else:
for rowNo in range(aisleTuple[0], aisleTuple[len(aisleTuple)-1]):
# update: rowNo!=0 added to omit aisle at top of excel sheet, as it overlapped SKUs in row 1
if rowNo in aisleTuple and rowNo != 0:
array[rowNo][counter] = "X"
array[rowNo][counter+1] = "X"
array[rowNo][counter+2] = "X"
else:
# create dataframe that is only
df = SKUAssignment.loc[(SKUAssignment['Row'] == rowNo) & (
SKUAssignment['Column'] == colNo)]
if df.empty:
pass # keep cells empty
else:
array[rowNo][counter+2] = df.loc[df['A/B']
== 'A'].iloc[0]['SKU']
array[rowNo][counter+1] = df.loc[df['A/B']
== 'B'].iloc[0]['SKU']
# https://stackoverflow.com/questions/134934/display-number-with-leading-zeros
array[rowNo][counter] = "{:02d}".format(
actualCol+1) + "{:02d}".format(rowNo+1) # +1 to start from 1 instead of 0
counter += 3
# if column is not in list, just fill entire column with X
else:
for rowNo in range(0, aisleTuple[len(aisleTuple)-1]):
array[rowNo][counter] = "X"
counter += 1
return array
"""
This will create a file to view for each heuristic
"""
def exportFiles(assignmentSKU, visualSKU: list, orderLines, orderDistance, exportPath: str, heuristicType: str):
path = exportPath + "/" + heuristicType + ".xlsx"
visual_sku_df = pd.DataFrame(visualSKU)
order_lines_df = pd.DataFrame(orderLines).T
order_distance_df = pd.DataFrame(orderDistance)
"""
SKU assignment should have the correct columns
"""
# #obtain list of columns in SKU Assignment
# listColumns = assignmentSKU['Column'].unique().tolist()
# listColumns.sort()
#
# #fix column numbers
# for colNo in range(0,np.int_(assignmentSKU['Column'].max())):
# if colNo in listColumns:
# actualCol = listColumns.index(colNo)
# assignmentSKU.loc[assignmentSKU['Column'] == colNo, 'Column'] = actualCol+1
# sort by column and reset index
assignmentSKU = assignmentSKU.sort_values(
['Column', 'Row'], ascending=True).reset_index(drop=True)
# https://xlsxwriter.readthedocs.io/example_pandas_multiple.html
# https://stackoverflow.com/questions/62618680/overwrite-an-excel-sheet-with-pandas-dataframe-without-affecting-other-sheets
"""
This will delete workbook to ensure file is completely overwritten
"""
with pd.ExcelWriter(path, engine='xlsxwriter') as writer:
# workBook = writer.book
# try:
# workBook.remove()
# print("Old workbook has been removed.")
# except:
# print("Workbook does not exist and cannot be deleted. New workbook will be created.")
# finally:
assignmentSKU.to_excel(writer, sheet_name='SKU Assignment')
visual_sku_df.to_excel(writer, sheet_name='SKU Layout')
order_lines_df.to_excel(writer, sheet_name='Order Line Pick Up')
order_distance_df.to_excel(writer, sheet_name='Order Line Distances')
print("The file for " + heuristicType +
" has been compiled. It has been saved to " + path)
# Close the Pandas Excel writer and output the Excel file.
writer.save()
"""
This will export all the distances for each model
"""
def exportDistancesOnly(randomD, coiD, weightD, abcHD, abcVD, exportPath):
path = exportPath + "/alldistance.xlsx"
random_df = pd.DataFrame(randomD).T
coi_df = pd.DataFrame(coiD).T
weight_df = pd.DataFrame(weightD).T
abch_df = pd.DataFrame(abcHD).T
abcv_df = pd.DataFrame(abcVD).T
with pd.ExcelWriter(path, engine='xlsxwriter') as writer:
random_df.to_excel(writer, sheet_name='Random')
coi_df.to_excel(writer, sheet_name='COI')
weight_df.to_excel(writer, sheet_name='Weight')
abch_df.to_excel(writer, sheet_name='ABC H')
abcv_df.to_excel(writer, sheet_name='ABC V')
print("The file for distance has been compiled. It has been saved to " + path)
# Close the Pandas Excel writer and output the Excel file.
writer.save()
"""
This will sum all values in a row
"""
def sumAllDistances(df, name: str):
df = df.fillna(0)
df[name] = df.sum(axis=1)
df2 = df[name]
return df2
"""
This will evaluate the different models based off what is decided (distributions, % comparison, etc...)
"""
def evaluationFile(randomD, coiD, weightD, abcHD, abcVD, exportPath):
path = exportPath + "/evaluation.xlsx"
random_df = pd.DataFrame(randomD)
coi_df = pd.DataFrame(coiD)
weight_df = pd.DataFrame(weightD)
abch_df = pd.DataFrame(abcHD)
abcv_df = pd.DataFrame(abcVD)
alldf = [random_df, coi_df, weight_df, abch_df, abcv_df]
names = ["random", "coi", "weight", "across aisle", "within aisle"]
sumDist = []
for i in range(len(alldf)):
sumDist.append(sumAllDistances(alldf[i], names[i]))
sum_df = pd.DataFrame(sumDist).T
sum_df['Lowest Distance'] = sum_df.idxmin(axis=1)
with pd.ExcelWriter(path, engine='xlsxwriter') as writer:
sum_df.to_excel(writer, sheet_name='Results')
writer.save()
print("Final evaluation results have been compiled. It has been saved to " + path)