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
215
216
217
218
219
220
221
222
223
224
225
226
227
228
|
## LIBRARIES
# //////////////////////////////////////////////////////////////////////////////
import pandas as pd # pandas for excel reading
import re # regex
import json # json
import os.path # ...
## LOCAL FUNCTIONS
# //////////////////////////////////////////////////////////////////////////////
# do-me-INTeger
# ---
def domeInt(x) :
if isinstance(x, str) : # if string
return int(x.strip())
if isinstance(x, float) : # if float
return round(x)
return x # otherwise is int already
# do-me-Float
# ---
def domeFloat(x) :
if isinstance(x, str) :
return float(x.strip())
else :
return x + 0.00 # make sure that result is float
def cleanText(x) :
removeList = [ ' με ', ' σε ', ' για ', ' του ', ' της ', ' των ', ' από ', '&', '.', ',', '!', '(', ')', '[', ']', '\'', '\"' ]
for r in removeList :
x = x.replace(r, ' ')
x.replace(' ', ' ') # remove spare spaces
x.replace(' ', ' ')
x.replace(' ', ' ')
return x
def isSignificant(x) :
# is significant if words has no digit-characters
return not bool(re.match("\S*\d+\S*", x))
# set root-keyQ: w (if not exist)
# update frequency: f
# into list: l
## ---
def rootKey ( w, f, l ) :
keyExists = False
for it in l :
if it['w'] == w :
keyExists = True
it['f'] += f
if keyExists == False :
l.append({
'w' : w,
'f' : f,
'c' : []
})
# connect keys: a , b
# of product with id: i
# with frequency: f
# into list: l
## ---
def connectKeys( a, b, i, f, l ) :
if a == b :
return False ## exclude just-in-case
for it in l :
if it['w'] == a :
# word a found;
# lets update the connection to: b
bExists = False
for jt in it['c'] :
if jt['w'] == b :
bExists = True
# update the connection's data
jt['f'] += f
jt['p'].append(i)
if bExists == False :
# create connection with word: b
it['c'].append({
'w': b,
'f': f,
'p': [ i ]
})
## LOCAL CONSTANTS
# //////////////////////////////////////////////////////////////////////////////
_COL = {
# -- main info
'freq' : 0, # frequency (based on recent orders)
'pid' : 1, # product id
'brand' : 2, # brand
'barcd' : 3, # barcode
'sklcd' : 4,
'eyscd' : 5,
'descr' : 6, # product description
'sap2' : 7 # SAP category level-2 id
}
## SET SOURCE and EXPORT FileNames
# ------------------------------------------------------------------------------
# location of excel file
loc = "./data/PRODucts2search-wBrands.xlsx"
print("default filename:", loc)
newXLfile = input("input other Excel filename [enter to keep default]: ")
if newXLfile != "" and os.path.exists(newXLfile):
loc = newXLfile
else :
print(newXLfile, "is not a file; default is kept;")
## baseEXPORTname = input("Base export name: ")
## Read data
# //////////////////////////////////////////////////////////////////////////////
df = pd.read_excel(loc) # read data from excel file
rows = df.iterrows() # set rows list
# --- Lists to fill
keywords_ = [] # all data
minilist_ = []
## keywords format:
## [
## {
## w : 'fresh',
## f : 150,
## c : [
## { w : 'milk', f : 150 , p : [122, 254, 907] },
## { w : 'juice', f : 50 , p : [254, 351] }
## ]
## },
## {...},
## ...
## ]
## --- index:
## w : word
## f : frequency
## c : combos / connections
## p : list of product-ids with this combo
# --- temporary variables (initialize)
## LOOP through the rows to pre-proccess all products
## ---
for idx, row in rows :
description = row[_COL['descr']].strip() # product description
pid = domeInt( row[_COL['pid']] ) # product-id
fq = domeInt( row[_COL['freq']] ) # frequency
# TODO:
# identify brands
# then ...
description = cleanText(description) # clean sescription string
words = description.strip().upper().split() # split to words
## words = [w.strip('.,!;()[]') for w in words] # clean strings
# identify significant words
keys = []
for w in words :
if isSignificant(w) :
keys.append(w)
print(pid, keys)
# append words (and their combos) to the list
for w in keys :
rootKey( w, fq, keywords_ )
for w2 in keys :
if w2 != w :
connectKeys( w, w2, pid, fq, keywords_ )
## SORT keywords
# //////////////////////////////////////////////////////////////////////////////
# --- sort childs of each key (per frequency, desc)
for it in keywords_ :
it['c'].sort(key=lambda x: x['f'], reverse=True)
# --- sort root keys
keywords_.sort(key=lambda x: x['f'], reverse=True)
# --- create mini list based on the sorted keywords_
for it in keywords_ :
minilist_.append( it['w'] )
## OUTPUT final data to a json-format file
# //////////////////////////////////////////////////////////////////////////////
with open("results/keywords-v2.json", "w", encoding="utf-8") as outfile :
data = json.dump(keywords_, outfile, sort_keys=False, indent=3, ensure_ascii=False)
with open("results/minilist.json", "w", encoding="utf-8") as outfile :
data = json.dump(minilist_, outfile, sort_keys=False, indent=3, ensure_ascii=False)
|
