diff options
Diffstat (limited to 'python')
| -rw-r--r-- | python/check-linked.py | 300 | ||||
| -rw-r--r-- | python/code-examples.py | 24 | ||||
| -rw-r--r-- | python/products-dict-v3.py | 338 | ||||
| -rw-r--r-- | python/products-dict-v4.py | 383 | ||||
| -rw-r--r-- | python/products-dict-v5.py | 428 | ||||
| -rw-r--r-- | python/products-dictionary.py | 228 | ||||
| -rw-r--r-- | python/products-src-json.py | 581 | ||||
| -rw-r--r-- | python/products-src-mysql-v2.py | 623 | ||||
| -rw-r--r-- | python/products-src-mysql.py | 535 | ||||
| -rw-r--r-- | python/read-brands.py | 270 | ||||
| -rw-r--r-- | python/readmysql.py | 121 | ||||
| -rw-r--r-- | python/test.py | 104 |
12 files changed, 3935 insertions, 0 deletions
diff --git a/python/check-linked.py b/python/check-linked.py new file mode 100644 index 0000000..2492ac5 --- /dev/null +++ b/python/check-linked.py @@ -0,0 +1,300 @@ +## LIBRARIES +# ////////////////////////////////////////////////////////////////////////////// + +# import pandas as pd # pandas for excel reading +import re # regex +import json # json +import os.path # ... +import datetime + +t0_ = datetime.datetime.now() + + +## LOCAL FUNCTIONS +# ////////////////////////////////////////////////////////////////////////////// + + +## Clean Text ... +# -> removes some general/neutral words and symbols +# -> ignores some in-line characters +# -> also strips spare spaces +# function is applied onto the full title/description +# --- +def cleanText(x) : + ignoreList = '" ( ) [ ]'.split(' ') + + for r in ignoreList : + x = x.replace(r, ' ') + + x = x.replace(' ', ' ') # remove spare spaces + x = x.replace(' ', ' ') + x = x.replace(' ', ' ') + + return x.replace(' ', ' ') # one lase (just in case) + + +# isSignificant +# decides if the term is significant to be indexed; +# a term is significant if does not contain digit-chars [0-9], comma (,) or period (.) +# --- +def isSignificant(x) : + # fisrts exclude some notable exceptions (mostly brands) + if x in ['7UP', '3ΑΛΦΑ', '17'] : + return True + + return not bool( re.match("\S*\d+\S*", x) ) + + +def kbLatinString( txt ) : + maTable = txt.maketrans( + "ςερτυθιοπασδφγηξκλζχψωβνμΕΡΤΥΘΙΟΠΑΣΔΦΓΗΞΚΛΖΧΨΩΒΝΜάέήίόύώϊΐϋΆΈΉΊΌΎΏΪΫQWERTYUIOPASDFGHJKLZXCVBNM", + "sertyuiopasdfghjklzxcvbnmertyuiopasdfghjklzxcvbnmaehioyviiyaehioyviyqwertyuiopasdfghjklzxcvbnm" + ) + txt = txt.replace('\'', '') + return txt.translate(maTable).lower() + + + + +# letters-only translation to key-pressed characters (latin) +# --- +def kbLatinLetter( txt ) : + maTable = txt.maketrans( + "ςερτυθιοπασδφγηξκλζχψωβνμΕΡΤΥΘΙΟΠΑΣΔΦΓΗΞΚΛΖΧΨΩΒΝΜάέήίόύώϊΐϋΆΈΉΊΌΎΏΪΫ", + "sertyuiopasdfghjklzxcvbnmertyuiopasdfghjklzxcvbnmaehioyviiyaehioyviy" + ) + return txt.translate(maTable).lower() + + +## mark a link to a text +# conecting them with a dash/minus character +# --- +def markLink(lws, text) : + text_kb = kbLatinLetter(text.replace(' ', '-')) + lws_kb = kbLatinLetter(lws) + try: + index_l = text_kb.lower().index(lws_kb.lower()) + except: + return text + else: + return text[:index_l] + lws + text[index_l + len(lws):] + + +### # --- list of normalized word combinations +### replaceWords = [ +### 'HEAD & SHOULDERS; HEAD&SOULDERS', +### 'ΟΛΙΚΗΣ 'ΑΛΕΣΗΣ; Ολικής Άλεσης', +### 'Χωρίς προσθήκη ζάχαρης; Χωρίς-Ζάχαρη' +### ] + + +# --- list of linked-words +linkedWords = [ + 'Χωρίς-Γλουτένη', + 'Χωρίς-Ζάχαρη', + 'Χωρίς-Αλάτι', + 'Χωρίς-Λακτόζη', + 'Χωρίς-Συντηρητικά', + 'Χωρίς-Αλκοόλ', + 'Χωρίς-Kαφεϊνη', + 'Χωρίς-Γλυκάνισο', + 'Χωρίς-Ανθρακικό', + 'Χωρίς-Προσθήκη' + 'Υψηλής-Παστερίωσης', + 'Ολικής-Άλεσης', + 'Ολικής-Aλέσεως', + 'Χαρτί-Υγείας', + 'ρολό-υγείας', + 'χαρτί-τουαλέτας', + 'Χαρτί-Κουζίνας', + 'Μπάρες-Δημητριακών', + 'Μπαρμα-Στάθης', + 'Coca-Cola' + 'Aς-Μαγειρέψουμε', + 'ΚΡΙΣ-ΚΡΙΣ', + 'ΚΡΙ-ΚΡΙ', + 'ΕΛ-ΓΚΡΕΚΟ', + 'FREE-STEP', + 'EL-SABOR', + 'LE-PETIT-MARSEILLAIS', + 'DOUWE-EGBERTS', + 'ΕΝ-ΕΛΛΑΔΙ', + 'SPIN-SPAN', + 'CRETA-FARMS', + 'CRETA-FARM', + 'NES-CAFE', + 'Ολες-τις-Χρήσεις', +] + + +# text after "all-links" marked +# --- +def markLinkedWords(text) : + for lw in linkedWords : + text = markLink( lw, text ) + + return text + + +## PREPADE (or build) exception objects +# ////////////////////////////////////////////////////////////////////////////// + + +# --- list of possible combos to check +_2check_kb = [] +_2check = ['με', 'σε', 'για', 'όλες', 'χωρίς' ] +for it in _2check : + _2check_kb.append(kbLatinString(it)) + +linkedWordsFound = [ + { 'w' : 'se', 'links' : [] }, + { 'w' : 'oles-tis', 'links' : [] }, + { 'w' : 'xvris', 'links' : [] } +] + +def recordLink( parent, child, id ) : + if parent != '' : + for it in linkedWordsFound : + if parent == it['w'] : + is_a_new_combo = True + for li in it['links'] : + if li['w'] == child : + li['p'].append(id) + li['c'] += 1 + is_a_new_combo = False + break + if is_a_new_combo : + it['links'].append({ 'w': child, 'p': [ id ], 'c': 1 }) + + + +# --- list of words to exclude from keywords +# NOTE: +# APPLIED in PER-WORD base -> after spliting description to words +removeList = [] +removeOriginals = 'μας του της των από στο στον & r s ft l τ e g h k m n o p s x'.split(' ') +for it in removeOriginals : + removeList.append(kbLatinString(it)) + + +# --- list of synonyms +# in fact +synonyms = [ + 'μπίρα μπύρα μπίρες μπύρες', + 'αυγά αβγά αυγό', + 'σίκαλης σικάλεως', + 'ξηρά ξερά', + 'ρολό ρολλό', + 'coca-cola cocacola coke', + 'χαρτί-υγείας ρολό-υγείας χαρτί-τουαλέτας', + 'χαρτί-κουζίνας ρολό-κουζίνας', + 'οινος κρασι', + 'ΚΑΤΣΕΛΗΣ ΚΑΤΣΕΛΗ', + 'DR-OETKER OETKER', + 'DR.BECKMANN BECKMANN', + 'NES-CAFE NESCAFE', + 'Ολικής-Άλεσης Ολικής-Aλέσεως Ολικής', + 'τσίπουρο ρακή', + 'Βρώμη Βρώμης', + 'Φράουλα Φράουλες Φράουλας', + 'Μαλλιά Μαλλιών', + 'Κέικ, Cake', + 'CRETA-FARMS CRETA-FARM', + 'MARSEILLAIS LE-PETIT-MARSEILLAIS', + 'Γαϊδούρας Γαϊδάρου', + 'ΚΑΛΟΓΕΡΑΚΗΣ ΚΑΛΟΓΕΡΑΚΗ', + 'ΚΑΪΔΑΝΤΖΗΣ ΚΑΪΔΑΝΤΖΗ', + 'ΥΦΑΝΤΗΣ ΥΦΑΝΤΗ', + 'ΣΥΝΑΓΡΙΔΑ ΣΥΝΑΓΡΙΔΕΣ', + 'Ντομάτα Ντομάτας', + 'Ελαφρύ Ελαφρά Light', + 'Εγχώρια Ελληνικό Ελληνικά', + 'τριμμένη τριμμένο', + 'Τόνος Τόνου', + 'Κριθαρένια κρίθινα' +] + + +## Read data +# ////////////////////////////////////////////////////////////////////////////// + + +_file = open ('data/eshop-products.json', "r") # JSON source file +results_ = json.loads(_file.read()) # Reading from file +_file.close() # Closing file + + +t_read = datetime.datetime.now() + +# --- Lists to fill +keywords_ = [] # all data; main exported object +minilist_ = [] +products_ = [] + +## keywords format: +## [ +## { +## w : [ word, word-synonym, ... ], +## kb : = kbLatinString(word) +## f : 150, +## c : [ +## { w: ['fish', 'fishes'], f: 150, p: [122, 254, 907] }, +## { w: ['juice'], f: 50, p: [254, 351] } +## ] +## }, +## ... +## ] +## +## --- index: +## w : words / list of synonyms (str/utf-8) +# kb : ascii-latin-keypoard format of first item of "w" list +## f : frequency (int) +## c : combos / connections (list of objects) +## p : list of product-ids found in specific words-combination (list of int) + + +records_counter = 0 +## LOOP through the rows to pre-proccess all products +## --- +for row in results_ : + records_counter += 1 + + description = row['Title'] # product description + pid = row['ID'] # product-id + fq = row['freq'] # frequency + + description = cleanText(description) # clean description string before spliting + + description = markLinkedWords(description) # ... + + keys = description.split() # split to words = keys + + is_combo_key = False + combo_key = '' + + # append words (and their combos) to the list + for w in keys : + + if kbLatinString(w) in _2check_kb : + combo_key = w + is_combo_key = True + else : + if is_combo_key : + recordLink(kbLatinString(combo_key), kbLatinString(w), pid) + is_combo_key = False + combo_key = '' + +# print(linkedWordsFound) + +# PRINT RESULTS +# --- +for ri in linkedWordsFound : + print('---', ri['w'], ':', len(ri['links'])) + + subtotal = 0 + for li in ri['links'] : + subtotal += li['c'] + + for li in ri['links'] : + ## if li['c'] > 10 or li['c']/subtotal > .2 : + print( ri['w'], li['w'], ' : ', li['c'], ' (', int(li['c']*100/subtotal), '%)' ) diff --git a/python/code-examples.py b/python/code-examples.py new file mode 100644 index 0000000..d8d0042 --- /dev/null +++ b/python/code-examples.py @@ -0,0 +1,24 @@ +# test +a = 1 +b = 4 + +def addto(x, l) : + l.append({ + "n" : x, + "c": [] + }) + for it in l : + if it["n"] == 4 : + subl = it["c"] + subl.append(x) + it['c'] = subl + + +malist = [] + +malist.append({ "n" : a }) +print(malist) + +addto(b, malist) +addto(b, malist) +print(malist) diff --git a/python/products-dict-v3.py b/python/products-dict-v3.py new file mode 100644 index 0000000..a9b2c97 --- /dev/null +++ b/python/products-dict-v3.py @@ -0,0 +1,338 @@ +## 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 + + +## Clean Text ... +# -> removes some general/neutral words and symbols +# -> ignores some in-line characters +# -> also strips spare spaces +# function is applied onto the full title/description +# --- +def cleanText(x) : + ignoreList = '" ( ) [ ]'.split(' ') + + for r in removeList : + x = x.replace(r, ' ') + + x = x.replace(' ', ' ') # remove spare spaces + x = x.replace(' ', ' ') + x = x.replace(' ', ' ') + + return x + + +# isSignificant +# decides if the term is significant to be indexed; +# a term is significant if does not contain digit-chars +# --- +def isSignificant(x) : + # fisrts exclude some notable exceptions (mostly brands) + if x in ['7UP', '3ΑΛΦΑ'] : + return True + + return not bool(re.match("\S*\d+\S*", x)) + + + +def kbLatinString( txt ) : + maTable = txt.maketrans( + "ςερτυθιοπασδφγηξκλζχψωβνμΕΡΤΥΘΙΟΠΑΣΔΦΓΗΞΚΛΖΧΨΩΒΝΜάέήίόύώϊϋΆΈΉΊΌΎΏΪΫ", + "sertyuiopasdfghjklzxcvbnmertyuiopasdfghjklzxcvbnmaehioyviyaehioyviy" + ) + + txt = txt.replace('\'', '') + txt = txt.replace('-', '') + txt = txt.replace(' ', '') + + return txt.translate(maTable).lower() + + +# set root-keyQ: w (if not exist) +# update frequency: f +# into list: l +# NOTE: in this version, +# comparison is based on the *keyboard* format +## --- +def rootKey ( w, f, l ) : + keyExists = False + kbW = kbLatinString(w) + + for it in l : + if it['kb'] == kbW : + keyExists = True + it['f'] += f + if w not in it['alt'] : + it['alt'].append(w) + + if keyExists == False : + l.append({ + 'w' : w, + 'f' : f, + 'alt' : [ w ], + 'kb' : kbW, + 'c' : [] + }) + + + +# connect keys: a , b +# of product with id: i +# with frequency: f +# into list: l +## --- +def connectKeys( a, b, i, f, l ) : + kbA = kbLatinString(a) + kbB = kbLatinString(b) + + if kbA == kbB : + return False ## exclude just-in-case + + for it in l : + if it['kb'] == kbA : + + # found: a; + # lets update the connection to: b + bExists = False + + for jt in it['c'] : + if jt['kb'] == kbLatinString(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, + 'kb': kbLatinString(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 +} + + + +## PREPADE (build) exception objects +# ////////////////////////////////////////////////////////////////////////////// + + +# --- list of words to exclude from keywords +# applied in a per-word base (after spliting description to words) +removeList = [] +removeOriginals = 'Μας με σε για του της των από ΜΕ ΣΕ ΓΙΑ στο στον Στο από e g h k m n o p s x'.split(' ') +for it in removeOriginals : + removeList.append(kbLatinString(it)) + +""" +# --- list of linked-words +linkedWords = [] +linkedWordOriginals = [ + 'Χωρίς-Γλουτένη', + 'Χωρίς-Ζάχαρη', + 'Χωρίς-Αλάτι', + 'Χωρίς-Λακτόζη', + 'Χωρίς-Συντηρητικά', + 'Χωρίς-Αλκοόλ', + 'Υψηλής-Παστερίωσης', + 'Ολικής-Άλεσης', + 'Χαρτί-Υγείας', + 'Χαρτί-Κουζίνας', + 'Μπάρες-Δημητριακών', + 'Φυσικός-Χυμός' + 'Μπαρμα-Στάθης', + 'Coca-Cola' +] +for it in linkedWordOriginals : + linkedWords.append(kbLatinString(it)) + + +synonyms = [] +synonymOriginals = [ + 'μπίρα, μπύρα, μπίρες, μπύρες', + 'αυγά, αβγά, αυγό, αβγό', + 'σίκαλης, σικάλεως', + 'ξηρά, ξερά', + 'ρολό, ρολλό' + 'coca-cola, cocacola, coke', + 'χαρτί-υγείας, ρολό-υγείας, χαρτί-τουαλέτας', + 'χαρτί-κουζίνας, ρολό-κουζίνας', + 'μπίρα, μπίρες', + 'αυγά, αυγό' +] +for it in synonymOriginals : + synonyms.append(kbLatinString(it)) +""" + + +## 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_ = [] +products_ = [] + +## keywords format: +## [ +## { +## w : 'fresh', +## alt : [ 'Fresh', 'FRESH', 'fresh' ] +## kb : +## f : 150, +## c : [ +## { w : 'milk', f : 150 , p : [122, 254, 907] }, +## { w : 'juice', f : 50 , p : [254, 351] } +## ] +## }, +## {...}, +## ... +## ] +## --- index: +## w : word (str/utf-8) +## f : frequency (int) +## c : combos / connections (list of objects) +## p : list of product-ids found in specific words-combination (list of int) +## alt : list of alternative writtings (list of str/utf-8) +## kb: *keyboard* writting (str/latin-ascii) + + +# --- temporary variables (initialize) + +## LOOP through the rows to pre-proccess all products +## --- +for idx, row in rows : + + description = row[_COL['descr']] # product description + pid = domeInt( row[_COL['pid']] ) # product-id + fq = domeInt( row[_COL['freq']] ) # frequency + + # setup product + # --- + products_.append({ + 'w' : description, + 'id' : pid, + 'f' : fq + }) + + # TODO: + # identify brands + # then ... + + description = cleanText(description) # clean sescription string + words = description.strip().split() # split to words + + # identify significant words + keys = [] + for w in words : + if isSignificant(w) : + keys.append(w) + + print(pid, description, words, keys) + # append words (and their combos) to the list + for w in keys : + rootKey( w, fq, keywords_ ) + for w2 in keys : + if w2 != w and isSignificant(w2) : + 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({ + 'w' : it['w'], + 'f' : it['f'], + 'kb': it['kb'] + }) + + +## OUTPUT final data to a json-format file +# ////////////////////////////////////////////////////////////////////////////// + +with open("results/keywords-v3.json", "w", encoding="utf-8") as outfile : + data = json.dump(keywords_, outfile, sort_keys=False, indent=3, ensure_ascii=False) + +with open("results/minilist-v3.json", "w", encoding="utf-8") as outfile : + data = json.dump(minilist_, outfile, sort_keys=False, indent=3, ensure_ascii=False) + +with open("results/products.json", "w", encoding="utf-8") as outfile : + data = json.dump(products_, outfile, sort_keys=False, indent=3, ensure_ascii=False)
\ No newline at end of file diff --git a/python/products-dict-v4.py b/python/products-dict-v4.py new file mode 100644 index 0000000..72a68d2 --- /dev/null +++ b/python/products-dict-v4.py @@ -0,0 +1,383 @@ +## LIBRARIES +# ////////////////////////////////////////////////////////////////////////////// + +# import pandas as pd # pandas for excel reading +import mysql.connector as mysql # mysql connector +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 + + +## Clean Text ... +# -> removes some general/neutral words and symbols +# -> ignores some in-line characters +# -> also strips spare spaces +# function is applied onto the full title/description +# --- +def cleanText(x) : + ignoreList = '" ( ) [ ]'.split(' ') + + for r in ignoreList : + x = x.replace(r, ' ') + + x = x.replace(' ', ' ') # remove spare spaces + x = x.replace(' ', ' ') + x = x.replace(' ', ' ') + + return x + + +# isSignificant +# decides if the term is significant to be indexed; +# a term is significant if does not contain digit-chars +# --- +def isSignificant(x) : + # fisrts exclude some notable exceptions (mostly brands) + if x in ['7UP', '3ΑΛΦΑ'] : + return True + + return not bool(re.match("\S*\d+\S*", x)) + + + +def kbLatinString( txt ) : + maTable = txt.maketrans( + "ςερτυθιοπασδφγηξκλζχψωβνμΕΡΤΥΘΙΟΠΑΣΔΦΓΗΞΚΛΖΧΨΩΒΝΜάέήίόύώϊϋΆΈΉΊΌΎΏΪΫQWERTYUIOPASDFGHJKLZXCVBNM", + "sertyuiopasdfghjklzxcvbnmertyuiopasdfghjklzxcvbnmaehioyviyaehioyviyqwertyuiopasdfghjklzxcvbnm" + ) + + txt = txt.replace('\'', '') + txt = txt.replace('-', '') + txt = txt.replace(' ', '') + + return txt.translate(maTable).lower() + + +# set root-keyQ: w (if not exist) +# update frequency: f +# into list: l +# NOTE: in this version, +# comparison is based on the *keyboard* format +## --- +def rootKey ( w, f, l ) : + keyExists = False + kbW = kbLatinString(w) + + for it in l : + if it['kb'] == kbW : + keyExists = True + it['f'] += f + if w not in it['alt'] : + it['alt'].append(w) + + if keyExists == False : + l.append({ + 'w' : w, + 'f' : f, + 'alt' : [ w ], + 'kb' : kbW, + 'c' : [] + }) + + + +# connect keys: a , b +# of product with id: i +# with frequency: f +# into list: l +## --- +def connectKeys( a, b, i, f, l ) : + kbA = kbLatinString(a) + kbB = kbLatinString(b) + + if kbA == kbB : + return False ## exclude just-in-case + + for it in l : + if it['kb'] == kbA : + + # found: a; + # lets update the connection to: b + bExists = False + + for jt in it['c'] : + if jt['kb'] == kbLatinString(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, + 'kb': kbLatinString(b), + 'f': f, + 'p': [ i ] + }) + + +## let mysql to return valid strings +## (otherwise it returns strings with missed characters) +# credit: https://stackoverflow.com/a/68784172 +# analytical credit: https://stackoverflow.com/questions/27566078/ +def get_data_from_db(cursor, sql): + output = [] + cursor.execute(sql) + row = cursor.fetchone() + while row is not None: + row_to_return = row.decode('utf-8') if isinstance(row, bytearray) else row + output.append(row_to_return) + row = cursor.fetchone() + + return output + + + +## 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 +} + + + +## PREPADE (build) exception objects +# ////////////////////////////////////////////////////////////////////////////// + + +# --- list of words to exclude from keywords +# applied in a per-word base (after spliting description to words) +removeList = [] +removeOriginals = 'μας με σε για του της των από στο στον από & r s ft l τ e g h k m n o p s x'.split(' ') +for it in removeOriginals : + removeList.append(kbLatinString(it)) + +""" +# --- list of linked-words +linkedWords = [] +linkedWordOriginals = [ + 'Χωρίς-Γλουτένη', + 'Χωρίς-Ζάχαρη', + 'Χωρίς-Αλάτι', + 'Χωρίς-Λακτόζη', + 'Χωρίς-Συντηρητικά', + 'Χωρίς-Αλκοόλ', + 'Υψηλής-Παστερίωσης', + 'Ολικής-Άλεσης', + 'Χαρτί-Υγείας', + 'Χαρτί-Κουζίνας', + 'Μπάρες-Δημητριακών', + 'Φυσικός-Χυμός' + 'Μπαρμα-Στάθης', + 'Coca-Cola' + 'Aς-Μαγειρέψουμε', + 'ΚΡΙΣ-ΚΡΙΣ', + 'ΚΡΙ-ΚΡΙ', + 'ΕΛ-ΓΚΡΕΚΟ', + 'FREE-STEP', + 'EL-SABOR', + 'LE-PETIT-MARSEILLAIS', + 'ΧΡΥΣΑ-ΑΥΓΑ', + 'DOUWE-EGBERTS', + 'ΕΝ-ΕΛΛΑΔΙ', + 'SPIN-SPAN', + 'CRETA-FARMS' +] +for it in linkedWordOriginals : + linkedWords.append(kbLatinString(it)) + + + +synonyms = [] +synonymOriginals = [ + 'μπίρα, μπύρα, μπίρες, μπύρες', + 'αυγά, αβγά, αυγό, αβγό', + 'σίκαλης, σικάλεως', + 'ξηρά, ξερά', + 'ρολό, ρολλό' + 'coca-cola, cocacola, coke', + 'χαρτί-υγείας, ρολό-υγείας, χαρτί-τουαλέτας', + 'χαρτί-κουζίνας, ρολό-κουζίνας', + 'μπίρα, μπίρες', + 'οινος, κρασι', + 'ΚΑΤΣΕΛΗΣ, ΚΑΤΣΕΛΗ', + 'DR-OETKER, OETKER' +] +for it in synonymOriginals : + synonyms.append(kbLatinString(it)) +""" + + + +## Read data +# ////////////////////////////////////////////////////////////////////////////// + +# enter your +HOST = "127.0.0.1" # server IP address/domain name +DATABASE = "dev_pythia_db" # database name +USER = "pythia_db_user_dev" +PASSWORD = "VnEP0eysjiXDHcfM" + +# connect to MySQL server +_dbc = mysql.connect( + host=HOST, + database=DATABASE, + user=USER, + password=PASSWORD, + use_unicode=True, + charset='utf8' + ) +print("Connected to:", _dbc.get_server_info()) + +# execute SQL to get all data you need +crs = _dbc.cursor() +query = ''' + SELECT count(pl.eys_code) as FREQuency, + pl.product_id as product_id, + pb.brand_name, + pl.barcode, pl.skl_code, pl.eys_code, + IF( pd.description IS NOT NULL , pd.description , pl.product_description) AS product_description + FROM product_list as pl + LEFT JOIN delivery_orders_products AS dop ON dop.product_id = pl.eys_code + LEFT JOIN delivery_orders AS do ON dop.order_id = do.id + LEFT JOIN product_list AS replacement ON dop.replacement_for = replacement.eys_code + LEFT JOIN product_details AS pd ON pl.eys_code = pd.eys_code + INNER JOIN product_brands pb ON pl.brand_id = pb.id + WHERE pl.active = 1 AND pl.sap_code IS NOT NULL + GROUP BY pl.product_id + ORDER BY FREQuency DESC +''' +results_ = get_data_from_db(crs, query) + +# --- Lists to fill +keywords_ = [] # all data +minilist_ = [] +products_ = [] + +## keywords format: +## [ +## { +## w : 'fresh', +## alt : [ 'Fresh', 'FRESH', 'fresh' ] +## kb : +## f : 150, +## c : [ +## { w : 'milk', f : 150 , p : [122, 254, 907] }, +## { w : 'juice', f : 50 , p : [254, 351] } +## ] +## }, +## {...}, +## ... +## ] +## --- index: +## w : word (str/utf-8) +## f : frequency (int) +## c : combos / connections (list of objects) +## p : list of product-ids found in specific words-combination (list of int) +## alt : list of alternative writtings (list of str/utf-8) +## kb: *keyboard* writting (str/latin-ascii) + + +## LOOP through the rows to pre-proccess all products +## --- +for row in results_ : + + description = row[_COL['descr']] # product description + pid = domeInt( row[_COL['pid']] ) # product-id + fq = domeInt( row[_COL['freq']] ) # frequency + + # setup product + # --- + products_.append({ + 'w' : description, + 'id' : pid, + 'f' : fq + }) + + # TODO: + # identify brands + # then ... + + description = cleanText(description) # clean description string before spliting + + keys = [] # list of product's key(word)s + words = description.split() # split to words + for w in words : + if kbLatinString(w) not in removeList: # if not in removeList + if isSignificant(w) : # and if significant + keys.append(w) # keep it + + + print(pid, description, words, keys) + # append words (and their combos) to the list + for w in keys : + rootKey( w, fq, keywords_ ) + for w2 in keys : + if w2 != w and isSignificant(w2) : + 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({ + 'w' : it['w'], + 'f' : it['f'], + 'kb': it['kb'] + }) + + +## OUTPUT final data to a json-format file +# ////////////////////////////////////////////////////////////////////////////// + +with open("results/keywords-v3.json", "w", encoding="utf-8") as outfile : + data = json.dump(keywords_, outfile, sort_keys=False, indent=3, ensure_ascii=False) + +with open("results/minilist-v3.json", "w", encoding="utf-8") as outfile : + data = json.dump(minilist_, outfile, sort_keys=False, indent=3, ensure_ascii=False) + +with open("results/products.json", "w", encoding="utf-8") as outfile : + data = json.dump(products_, outfile, sort_keys=False, indent=3, ensure_ascii=False)
\ No newline at end of file diff --git a/python/products-dict-v5.py b/python/products-dict-v5.py new file mode 100644 index 0000000..329a212 --- /dev/null +++ b/python/products-dict-v5.py @@ -0,0 +1,428 @@ +## PRODUCTS DICTIONARY +# for eShop +# ////////////////////////////////////////////////////////////////////////////// + +## LIBRARIES +# ////////////////////////////////////////////////////////////////////////////// + +# import pandas as pd # pandas for excel reading +import mysql.connector as mysql # mysql connector +import re # regex +import json # json +import os.path # ... +import sys + + + +## 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 + + +## Clean Text ... +# -> removes some general/neutral words and symbols +# -> ignores some in-line characters +# -> also strips spare spaces +# function is applied onto the full title/description +# --- +def cleanText(x) : + ignoreList = '" ( ) [ ]'.split(' ') + + for r in ignoreList : + x = x.replace(r, ' ') + + x = x.replace(' ', ' ') # remove spare spaces + x = x.replace(' ', ' ') + x = x.replace(' ', ' ') + + return x + + +# isSignificant +# decides if the term is significant to be indexed; +# a term is significant if does not contain digit-chars +# --- +def isSignificant(x) : + # fisrts exclude some notable exceptions (mostly brands) + if x in ['7UP', '3ΑΛΦΑ'] : + return True + + return not bool(re.match("\S*\d+\S*", x)) + + + +def kbLatinString( txt ) : + maTable = txt.maketrans( + "ςερτυθιοπασδφγηξκλζχψωβνμΕΡΤΥΘΙΟΠΑΣΔΦΓΗΞΚΛΖΧΨΩΒΝΜάέήίόύώϊϋΆΈΉΊΌΎΏΪΫQWERTYUIOPASDFGHJKLZXCVBNM", + "sertyuiopasdfghjklzxcvbnmertyuiopasdfghjklzxcvbnmaehioyviyaehioyviyqwertyuiopasdfghjklzxcvbnm" + ) + + txt = txt.replace('\'', '') + txt = txt.replace('-', '') + txt = txt.replace(' ', '') + + return txt.translate(maTable).lower() + + +# set root-keyQ: w (if not exist) +# update frequency: f +# into list: l +# NOTE: in this version, +# comparison is based on the *keyboard* format +## --- +def rootKey ( w, f, l ) : + keyExists = False + kbW = kbLatinString(w) + + for it in l : + if it['kb'] == kbW : + keyExists = True + it['f'] += f + if w not in it['alt'] : + it['alt'].append(w) + + if keyExists == False : + l.append({ + 'w' : w, + 'f' : f, + 'alt' : [ w ], + 'kb' : kbW, + 'c' : [] + }) + + + +# connect keys: a , b +# of product with id: i +# with frequency: f +# into list: l +## --- +def connectKeys( a, b, i, f, l ) : + kbA = kbLatinString(a) + kbB = kbLatinString(b) + + if kbA == kbB : + return False ## exclude just-in-case + + for it in l : + if it['kb'] == kbA : + + # found: a; + # lets update the connection to: b + bExists = False + + for jt in it['c'] : + if jt['kb'] == kbLatinString(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, + 'kb': kbLatinString(b), + 'f': f, + 'p': [ i ] + }) + + +## let mysql to return valid strings +## (otherwise it returns strings with missed characters) +# credit: https://stackoverflow.com/a/68784172 +# analytical credit: https://stackoverflow.com/questions/27566078/ +def get_data_from_db(cursor, sql): + output = [] + cursor.execute(sql) + row = cursor.fetchone() + while row is not None: + row_to_return = row.decode('utf-8') if isinstance(row, bytearray) else row + output.append(row_to_return) + row = cursor.fetchone() + + return output + + + +## 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 +} + + + +## PREPADE (build) exception objects +# ////////////////////////////////////////////////////////////////////////////// + + +# --- list of words to exclude from keywords +# applied in a per-word base (after spliting description to words) +removeList = [] +removeOriginals = 'μας με σε για του της των από στο στον από & r s ft l τ e g h k m n o p s x'.split(' ') +for it in removeOriginals : + removeList.append(kbLatinString(it)) + +""" +# --- list of linked-words +linkedWords = [] +linkedWordOriginals = [ + 'Χωρίς-Γλουτένη', + 'Χωρίς-Ζάχαρη', + 'Χωρίς-Αλάτι', + 'Χωρίς-Λακτόζη', + 'Χωρίς-Συντηρητικά', + 'Χωρίς-Αλκοόλ', + 'Υψηλής-Παστερίωσης', + 'Ολικής-Άλεσης', + 'Χαρτί-Υγείας', + 'Χαρτί-Κουζίνας', + 'Μπάρες-Δημητριακών', + 'Φυσικός-Χυμός' + 'Μπαρμα-Στάθης', + 'Coca-Cola' + 'Aς-Μαγειρέψουμε', + 'ΚΡΙΣ-ΚΡΙΣ', + 'ΚΡΙ-ΚΡΙ', + 'ΕΛ-ΓΚΡΕΚΟ', + 'FREE-STEP', + 'EL-SABOR', + 'LE-PETIT-MARSEILLAIS', + 'ΧΡΥΣΑ-ΑΥΓΑ', + 'DOUWE-EGBERTS', + 'ΕΝ-ΕΛΛΑΔΙ', + 'SPIN-SPAN', + 'CRETA-FARMS' +] +for it in linkedWordOriginals : + linkedWords.append(kbLatinString(it)) + + + +synonyms = [] +synonymOriginals = [ + 'μπίρα, μπύρα, μπίρες, μπύρες', + 'αυγά, αβγά, αυγό, αβγό', + 'σίκαλης, σικάλεως', + 'ξηρά, ξερά', + 'ρολό, ρολλό' + 'coca-cola, cocacola, coke', + 'χαρτί-υγείας, ρολό-υγείας, χαρτί-τουαλέτας', + 'χαρτί-κουζίνας, ρολό-κουζίνας', + 'μπίρα, μπίρες', + 'οινος, κρασι', + 'ΚΑΤΣΕΛΗΣ, ΚΑΤΣΕΛΗ', + 'DR-OETKER, OETKER' +] +for it in synonymOriginals : + synonyms.append(kbLatinString(it)) +""" + + + +## Read data +# ////////////////////////////////////////////////////////////////////////////// + +## # enter your +## HOST = "mariadb" # server IP address/domain name +## DATABASE = "emarket_laravel" # database name +## USER = "emarket_laravel" +## PASSWORD = "" +## +## # connect to MySQL server +## _dbc = mysql.connect( +## host=HOST, +## database=DATABASE, +## user=USER, +## password=PASSWORD, +## use_unicode=True, +## charset='utf8' +## ) +## print("Connected to:", _dbc.get_server_info()) +## +## # execute SQL to get all data you need +## crs = _dbc.cursor() +## query = ''' +## SELECT p.product_title, p.SKU , p.FriendlyUrl as `seoUrl`, +## c.FullFriendlyUrl as `path` +## FROM products p +## LEFT JOIN category_product cp ON cp.product_id = p.id +## LEFT JOIN categories c ON c.id = cp.category_id +## WHERE p.isActive = 1 AND p.Published = 1 AND p.IsCurrentlyActive = 1 +## AND c.isActive AND c.IsCurrentlyActive = 1; +## ''' +## results_ = get_data_from_db(crs, query) + + +# enter your +HOST = "127.0.0.1" # server IP address/domain name +DATABASE = "emarket_laravel_dev" # database name +USER = "emarket_laravel" +PASSWORD = "SzvRYl4Y0XU9JXVc" +DB_SOCKET='/cloudsql/pythia-251711:europe-west4:pythia-db-eu' + +# connect to MySQL server +_dbc = mysql.connect( + host=HOST, + database=DATABASE, + user=USER, + password=PASSWORD, + use_unicode=True, + charset='utf8' + ) +print("Connected to:", _dbc.get_server_info()) + +sys.exit() + +# execute SQL to get all data you need +crs = _dbc.cursor() +query = ''' + SELECT count(pl.eys_code) as FREQuency, + pl.product_id as product_id, + pb.brand_name, + pl.barcode, pl.skl_code, pl.eys_code, + IF( pd.description IS NOT NULL , pd.description , pl.product_description) AS product_description + FROM product_list as pl + LEFT JOIN delivery_orders_products AS dop ON dop.product_id = pl.eys_code + LEFT JOIN delivery_orders AS do ON dop.order_id = do.id + LEFT JOIN product_list AS replacement ON dop.replacement_for = replacement.eys_code + LEFT JOIN product_details AS pd ON pl.eys_code = pd.eys_code + INNER JOIN product_brands pb ON pl.brand_id = pb.id + WHERE pl.active = 1 AND pl.sap_code IS NOT NULL + GROUP BY pl.product_id + ORDER BY FREQuency DESC +''' +results_ = get_data_from_db(crs, query) + + + +# --- Lists to fill +keywords_ = [] # all data +minilist_ = [] +products_ = [] + +## keywords format: +## [ +## { +## w : 'fresh', +## alt : [ 'Fresh', 'FRESH', 'fresh' ] +## kb : +## f : 150, +## c : [ +## { w : 'milk', f : 150 , p : [122, 254, 907] }, +## { w : 'juice', f : 50 , p : [254, 351] } +## ] +## }, +## {...}, +## ... +## ] +## --- index: +## w : word (str/utf-8) +## f : frequency (int) +## c : combos / connections (list of objects) +## p : list of product-ids found in specific words-combination (list of int) +## alt : list of alternative writtings (list of str/utf-8) +## kb: *keyboard* writting (str/latin-ascii) + + +## LOOP through the rows to pre-proccess all products +## --- +for row in results_ : + + description = row[_COL['product_title']] # product description + pid = domeInt( row[_COL['SKU']] ) # product-id + fq = domeInt( 1 ) # frequency + # url = '/'+ row[_COL['path']] +'/'+ row[_COL['seoUrl']] # product url + + # setup product + # --- + products_.append({ + 't' : description, + 'i' : pid, + 'f' : fq + # 'u' : url + }) + + # TODO: + # identify brands + # then ... + + description = cleanText(description) # clean description string before spliting + + keys = [] # list of product's key(word)s + words = description.split() # split to words + for w in words : + if kbLatinString(w) not in removeList: # if not in removeList + if isSignificant(w) : # and if significant + keys.append(w) # keep it + + + # print(pid, description, words, keys) + + # append words (and their combos) to the list + for w in keys : + rootKey( w, fq, keywords_ ) + for w2 in keys : + if w2 != w and isSignificant(w2) : + 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({ + 'w' : it['w'], + 'f' : it['f'], + 'kb': it['kb'] + }) + + +## OUTPUT final data to a json-format file +# ////////////////////////////////////////////////////////////////////////////// + +with open("results/keywords-v5.json", "w", encoding="utf-8") as outfile : + data = json.dump(keywords_, outfile, sort_keys=False, indent=3, ensure_ascii=False) + +with open("results/minilist-v5.json", "w", encoding="utf-8") as outfile : + data = json.dump(minilist_, outfile, sort_keys=False, indent=3, ensure_ascii=False) + +with open("results/products-v5.json", "w", encoding="utf-8") as outfile : + data = json.dump(products_, outfile, sort_keys=False, indent=3, ensure_ascii=False)
\ No newline at end of file diff --git a/python/products-dictionary.py b/python/products-dictionary.py new file mode 100644 index 0000000..1f35a67 --- /dev/null +++ b/python/products-dictionary.py @@ -0,0 +1,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) diff --git a/python/products-src-json.py b/python/products-src-json.py new file mode 100644 index 0000000..9e3da8a --- /dev/null +++ b/python/products-src-json.py @@ -0,0 +1,581 @@ +## LIBRARIES +# ////////////////////////////////////////////////////////////////////////////// + +# import pandas as pd # pandas for excel reading +import re # regex +import json # json +import os.path # ... +import datetime + +t0_ = datetime.datetime.now() + +## 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 + + +## Clean Text ... +# -> removes some general/neutral words and symbols +# -> ignores some in-line characters +# -> also strips spare spaces +# function is applied onto the full title/description +# --- +def cleanText(x) : + ignoreList = '" ( ) [ ]'.split(' ') + + for r in ignoreList : + x = x.replace(r, ' ') + + x = x.replace(' ', ' ') # remove spare spaces + x = x.replace(' ', ' ') + x = x.replace(' ', ' ') + + return x.replace(' ', ' ') # one lase (just in case) + + +## kbLatinString ... +# -> translate/re-wrrite string using latin-characters +# -> function is used used anywhere +def kbLatinString( txt ) : + maTable = txt.maketrans( + "ςερτυθιοπασδφγηξκλζχψωβνμΕΡΤΥΘΙΟΠΑΣΔΦΓΗΞΚΛΖΧΨΩΒΝΜάέήίόύώϊΐϋΆΈΉΊΌΎΏΪΫQWERTYUIOPASDFGHJKLZXCVBNM", + "sertyuiopasdfghjklzxcvbnmertyuiopasdfghjklzxcvbnmaehioyviiyaehioyviyqwertyuiopasdfghjklzxcvbnm" + ) + txt = txt.replace('\'', '') + return txt.translate(maTable).lower() + + +# letters-only translation to key-pressed characters (latin) +# this minimized version of kbLatinString is used in markLink() +# --- +def kbLatinLetter( txt ) : + maTable = txt.maketrans( + "ςερτυθιοπασδφγηξκλζχψωβνμΕΡΤΥΘΙΟΠΑΣΔΦΓΗΞΚΛΖΧΨΩΒΝΜάέήίόύώϊΐϋΆΈΉΊΌΎΏΪΫ", + "sertyuiopasdfghjklzxcvbnmertyuiopasdfghjklzxcvbnmaehioyviiyaehioyviy" + ) + return txt.translate(maTable).lower() + + +# isSignificant +# decides if the term is significant to be indexed; +# a term is significant if does not contain digit-chars [0-9], comma (,) or period (.) +# --- +significantExceptios = '7UP 3ΑΛΦΑ 17 3Π'.split(' ') +def isSignificant(x) : + # fisrts exclude some notable exceptions (mostly brands) + if x in significantExceptios : + return True + + return not bool( re.match("\S*\d+\S*", x) ) + + +# check if word: w +# … has synonyms; return list of synonyms +# --- +def synonymKeys(w) : + w_kb = kbLatinString(w) + syns = [ w ] + found = False + # check if has synonyms + for group in synonyms : + possibles = group.split() + for wi in possibles : + if kbLatinString(wi) == w_kb : + syns = possibles + found = True + break + if found : + break + return syns + + +# set root-keyword: wl (if not exist) +# update frequency: f +# into list: l +# NOTE: +# * wl is a list of synonym-words +# ** comparison is based on the *keyboard* format +## --- +def rootKey ( wl, f, l ) : + keyExists = False + w_kb = kbLatinString(wl[0]) # cache kb format + + # check if exists in root keys already + # NOTE: you only need to check the 1st word of synonyms-list + for it in l : + if it['kb'] == w_kb : + keyExists = True + it['f'] += f + break + + # if not exists, append keyword + if keyExists == False : + l.append({ + 'w' : wl, + 'kb' : w_kb, + 'f' : f, + 'c' : [] + }) + + +# connect keys: a , b (each one is a list of synonmyms) +# of product with id: i +# with frequency: f +# into list: l +## --- +def connectKeys( a, b, i, f, l ) : + kbA = kbLatinString(a[0]) + kbB = kbLatinString(b[0]) + + if kbA == kbB : + return False ## exclude just-in-case + + for it in l : + if it['kb'] == kbA : + # found: a; + + # let's update connection to: b + bExists = False + for jt in it['c'] : + if jt['kb'] == kbB : + bExists = True + # update the connection's data + jt['f'] += f + jt['p'].append(i) + break + + if bExists == False : + # create connection with word: b + it['c'].append({ + 'w': b, + 'kb': kbB, + 'f': f, + 'p': [ i ] + }) + break + + +## let mysql to return valid strings +## (otherwise it returns strings with missed characters) +# credit: https://stackoverflow.com/a/68784172 +# analytical credit: https://stackoverflow.com/questions/27566078/ +def get_data_from_db(cursor, sql): + output = [] + cursor.execute(sql) + row = cursor.fetchone() + while row is not None: + row_to_return = row.decode('utf-8') if isinstance(row, bytearray) else row + output.append(row_to_return) + row = cursor.fetchone() + + return output + + +## replaces +# do all replaces in place +# --- (preproccessing) +replaces = [] +replaceSource = [ + '3 ΑΛΦΑ ;3ΑΛΦΑ ', + 'HEAD & SHOULDERS ;HEAD&SHOULDERS ', + 'W.K Kellogg ; ', + 'ΦΙΛΕΤ ;Φιλέτο ', + 'ΕΝΕΛΛΑΔ ;Εν-Ελλάδι ', + 'ΓΑΛΟΠΟΥΛ ;Γαλοπούλα ', + ' ΓΥΝ.; ΓΥΝ ', + +] +for it in replaceSource : + st = it.split(';') + replaces.append({ 'src': st[0], 'trg': st[1]}) + +def do_replaces(w) : + for it in replaces : + w = w.replace(it['src'], it['trg']) + return w + + +## main preproccess function for product descriptions +# --- +def preprocessEdit(w) : + w = do_replaces(w) + # ... do other things if needed + # then ... + return w + + +## mark a link to a text +# conecting them with a dash/minus character +# --- +def markLink(lws, text) : + text_kb = kbLatinLetter(text.replace(' ', '-')) + lws_kb = kbLatinLetter(lws) + try: + index_l = text_kb.lower().index(lws_kb.lower()) + except: + return text + else: + return text[:index_l] + lws + text[index_l + len(lws):] + + +### # --- list of normalized word combinations +### replaceWords = [ +### 'HEAD & SHOULDERS; HEAD&SOULDERS', +### 'ΟΛΙΚΗΣ 'ΑΛΕΣΗΣ; Ολικής Άλεσης', +### 'Χωρίς προσθήκη ζάχαρης; Χωρίς-Ζάχαρη', +### 'ΚΑΠΝ.CRETA-FARMS; ΚΑΠΝΙΣΤΗ CRETA-FARMS' +### ] + + +# --- list of linked-words +linkedWords = [ + 'Χωρίς-Γλουτένη', + 'Χωρίς-Ζάχαρη', + 'Χωρίς-Αλάτι', + 'Χωρίς-Λακτόζη', + 'Χωρίς-Συντηρητικά', + 'Χωρίς-Αλκοόλ', + 'Χωρίς-Kαφεϊνη', + 'Χωρίς-Γλυκάνισο', + 'Χωρίς-Ανθρακικό', + 'Υψηλής-Παστερίωσης', + 'Ολικής-Άλεσης', + 'Ολικής-Aλέσεως', + 'Χαρτί-Υγείας', + 'ρολό-υγείας', + 'χαρτί-τουαλέτας', + 'Χαρτί-Κουζίνας', + 'Μπάρες-Δημητριακών', + 'Μπαρμπα-Στάθης', + 'COCA-COLA' + 'Aς-Μαγειρέψουμε', + 'ΚΡΙΣ-ΚΡΙΣ', + 'ΚΡΙ-ΚΡΙ', + 'ΕΛ-ΓΚΡΕΚΟ', + 'FREE-STEP', + 'EL-SABOR', + 'LE-PETIT-MARSEILLAIS', + 'DOUWE-EGBERTS', + 'ΕΝ-ΕΛΛΑΔΙ', + 'SPIN-SPAN', + 'CRETA-FARMS', + 'CRETA-FARM', + 'NES-CAFE', + 'Ολες-τις-Χρήσεις', + 'Το-Μάννα', + 'Χωρίς-προσθήκη-ζάχαρης' +] + + + +# mark linked words (connect them with a dash) +# return new text after "all-links" are marked +# --- +def markLinkedWords(text) : + for lw in linkedWords : + text = markLink( lw, text ) + return text + + +## handle words that can never be the first word on a search +noRootKeywords = [] +noRoot = [ + 'χωρίς', + 'εισαγωγής', + 'δώρο', + 'γεύση', + 'γεύσεις', + 'φέτες', + 'Χωρίς-Γλουτένη', + 'Χωρίς-Ζάχαρη', + 'Χωρίς-Αλάτι', + 'Χωρίς-Λακτόζη', + 'Χωρίς-Συντηρητικά', + 'Χωρίς-Αλκοόλ', + 'Χωρίς-Kαφεϊνη', + 'Χωρίς-Γλυκάνισο', + 'Χωρίς-Ανθρακικό', + 'Υψηλής-Παστερίωσης', + 'Ολες-τις-Χρήσεις', + 'Ολικής-Άλεσης', + 'Ολικής-Aλέσεως', + 'Ολικής', + 'Γαϊδούρας', + 'Γαϊδάρου', + 'Ρούχων', + 'Πιάτων', + 'πλύσεις', + 'Πλυντηρίου', + 'Φύλλων', + 'Γάλακτος', + 'Χρήσης', + 'Τύπου', + 'Ολλανδίας', + 'Απορριμμάτων', + 'Medium', + 'Μαλλιά', + 'Μαλλιών', + 'Γενικής', + 'Plus', + 'Classic', + 'Έκπληξη', + 'Μάνης', + 'Ελάτου', + 'Άγριων', + 'Βοτάνων', + 'Λακωνίας' +] +for w in noRoot : + noRootKeywords.append(kbLatinString(w)) + + +## PREPARE (or build) exception objects +# ////////////////////////////////////////////////////////////////////////////// + + +# --- list of words to exclude from keywords +# NOTE: +# APPLIED in PER-WORD base -> after spliting description to words +removeList = [] +removeOriginals = 'μας με σε για του της των από στο στον & r s ft l τ e g h k m n o p s x'.split(' ') +for it in removeOriginals : + removeList.append(kbLatinString(it)) + + +# --- list of synonyms +# in fact +synonyms = [ + 'μπίρα μπύρα μπίρες μπύρες', + 'αυγά αβγά αυγό', + 'σίκαλης σικάλεως', + 'ξηρά ξερά', + 'ρολό ρολλό', + 'coca-cola cocacola coke', + 'χαρτί-υγείας ρολό-υγείας χαρτί-τουαλέτας', + 'χαρτί-κουζίνας ρολό-κουζίνας', + 'οινος κρασι', + 'ΚΑΤΣΕΛΗΣ ΚΑΤΣΕΛΗ', + 'DR-OETKER OETKER', + 'DR.BECKMANN BECKMANN', + 'NES-CAFE NESCAFE', + 'Ολικής-Άλεσης Ολικής-Aλέσεως Ολικής', + 'τσίπουρο ρακή', + 'Βρώμη Βρώμης', + 'Φράουλα Φράουλες Φράουλας', + 'Μαλλιά Μαλλιών', + 'Κέικ, Cake', + 'CRETA-FARMS CRETA-FARM', + 'MARSEILLAIS LE-PETIT-MARSEILLAIS', + 'Γαϊδούρας Γαϊδάρου', + 'ΚΑΛΟΓΕΡΑΚΗΣ ΚΑΛΟΓΕΡΑΚΗ', + 'ΚΑΪΔΑΝΤΖΗΣ ΚΑΪΔΑΝΤΖΗ', + 'ΥΦΑΝΤΗΣ ΥΦΑΝΤΗ', + 'ΣΥΝΑΓΡΙΔΑ ΣΥΝΑΓΡΙΔΕΣ', + 'Ντομάτα Ντομάτας', + 'Ελαφρύ Ελαφρά Light', + 'Εγχώρια Εγχώριες Ελληνικό Ελληνική Ελληνικά', + 'τριμμένη τριμμένο', + 'Τόνος Τόνου', + 'Κριθαρένια κρίθινα', + 'Χωρίς-Kαφεϊνη Decaffeine', + 'Το-Μάννα Μάννα', + 'Κράνμπερι Κράνμπερις' +] + + +## Read data +# ////////////////////////////////////////////////////////////////////////////// + + +_file = open ('data/eshop-products.json', "r") # JSON source file +results_ = json.loads(_file.read()) # Reading from file +_file.close() # Closing file + + +t_read = datetime.datetime.now() + + +# --- Lists to fill +keywords_ = [] # all data; main exported object +minilist_ = [] +products_ = [] + +## keywords format: +## [ +## { +## w : [ word, word-synonym, ... ], +## kb : = kbLatinString(word) +## f : 150, +## c : [ +## { w: ['fish', 'fishes'], f: 150, p: [122, 254, 907] }, +## { w: ['juice'], f: 50, p: [254, 351] } +## ] +## }, +## ... +## ] +## +## --- index: +## w : words / list of synonyms (str/utf-8) +# kb : ascii-latin-keypoard format of first item of "w" list +## f : frequency (int) +## c : combos / connections (list of objects) +## p : list of product-ids found in specific words-combination (list of int) + + +records_counter = 0 +## LOOP through the rows to pre-proccess all products +## --- +for row in results_ : + records_counter += 1 + + description = row['Title'] # product description + pid = row['ID'] # product-id + fq = row['freq'] # frequency + + # edit descriptions + description = preprocessEdit(description) + + # setup product + # --- + products_.append({ + 'w' : description, + 'id' : pid, + 'f' : fq + }) + + # TODO: + # identify brands + # then ... + + description = cleanText(description) # clean description string before spliting + + description = markLinkedWords(description) # ... + + keys = [] # list of product's key(word)s + words = description.split() # split to words + for w in words : + if kbLatinString(w) not in removeList: # if not in removeList + if isSignificant(w) : # and if significant + keys.append(w) # keep it + + + ## print(pid, description, words, keys) + ## print(pid, keys) + + # append words (and their combos) to the list + for w in keys : + wl = synonymKeys(w) + + # update root word frequency (if w CAN be a root word) + if kbLatinLetter(w) not in noRootKeywords : + rootKey( wl, fq, keywords_ ) + + for w2 in keys : + if w2 != w : + w2syns = synonymKeys(w2) + connectKeys( wl, w2syns, 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) + + + + +## alternative formats to test ------------------------------------------- START + +with open("results/keywords-full.json", "w", encoding="utf-8") as outfile : + data = json.dump(keywords_, outfile, sort_keys=False, indent=2, ensure_ascii=False) + +keyhashes_ = [] +hashedkeys_ = [] + +# --- remove 'kb' keywords +for ki in keywords_ : + h = ki['kb'] + keyhashes_.append({ h : ki['w'] }) + conns = [] + del ki['kb'] + for ci in ki['c'] : + conns.append({ + 'h' : ci['kb'], + 'f' : ci['f'], + 'p' : ci['p'] + }) + del ci['kb'] + hashedkeys_.append({ + 'h' : h, + 'f' : ci['f'], + 'c' : conns + }) + +with open("results/hashes.json", "w", encoding="utf-8") as outfile : + data = json.dump(keyhashes_, outfile, sort_keys=False, indent=2, ensure_ascii=False) + +with open("results/hashedkeys.json", "w", encoding="utf-8") as outfile : + data = json.dump(hashedkeys_, outfile, sort_keys=False, indent=2, ensure_ascii=False) + +# --- create mini list based on the sorted keywords_ +### for it in keywords_ : +### minilist_.append({ +### 'w' : it['w'], +### 'f' : it['f'], +### 'kb': it['kb'] +### }) +### +### with open("results/minilist.json", "w", encoding="utf-8") as outfile : +### data = json.dump(minilist_, outfile, sort_keys=False, indent=3, ensure_ascii=False) + + +## alternative formats to test --------------------------------------------- END + + + + +t_main = datetime.datetime.now() +print('Proccessing ended; saving results in json format ...') + + +## OUTPUT final data to a json-format file +# ////////////////////////////////////////////////////////////////////////////// + +with open("results/keywords.json", "w", encoding="utf-8") as outfile : + data = json.dump(keywords_, outfile, sort_keys=False, ensure_ascii=False) + +with open("results/products.json", "w", encoding="utf-8") as outfile : + data = json.dump(products_, outfile, sort_keys=False, ensure_ascii=False) + + +t_end = datetime.datetime.now() + + +print(records_counter, 'products proccessed') +print('execution time:', (t_end - t0_)) +print('read.n.parse sources:', (t_read - t0_)) +print('proccessing products:', (t_main - t_read)) diff --git a/python/products-src-mysql-v2.py b/python/products-src-mysql-v2.py new file mode 100644 index 0000000..034da0d --- /dev/null +++ b/python/products-src-mysql-v2.py @@ -0,0 +1,623 @@ +## LIBRARIES +# ////////////////////////////////////////////////////////////////////////////// + +# import pandas as pd # pandas for excel reading +import mysql.connector as mysql # mysql connector +import re # regex +import json # json +import os.path # ... +import datetime + +t0_ = datetime.datetime.now() + +## 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 + + +## Clean Text ... +# -> removes some general/neutral words and symbols +# -> ignores some in-line characters +# -> also strips spare spaces +# function is applied onto the full title/description +# --- +def cleanText(x) : + ignoreList = '" ( ) [ ]'.split(' ') + + for r in ignoreList : + x = x.replace(r, ' ') + + x = x.replace(' ', ' ') # remove spare spaces + x = x.replace(' ', ' ') + x = x.replace(' ', ' ') + + return x.replace(' ', ' ') # one lase (just in case) + + +## kbLatinString ... +# -> translate/re-wrrite string using latin-characters +# -> function is used used anywhere +def kbLatinString( txt ) : + maTable = txt.maketrans( + "ςερτυθιοπασδφγηξκλζχψωβνμΕΡΤΥΘΙΟΠΑΣΔΦΓΗΞΚΛΖΧΨΩΒΝΜάέήίόύώϊΐϋΆΈΉΊΌΎΏΪΫQWERTYUIOPASDFGHJKLZXCVBNM", + "sertyuiopasdfghjklzxcvbnmertyuiopasdfghjklzxcvbnmaehioyviiyaehioyviyqwertyuiopasdfghjklzxcvbnm" + ) + txt = txt.replace('\'', '') + return txt.translate(maTable).lower() + + +# letters-only translation to key-pressed characters (latin) +# this minimized version of kbLatinString is used in markLink() +# --- +def kbLatinLetter( txt ) : + maTable = txt.maketrans( + "ςερτυθιοπασδφγηξκλζχψωβνμΕΡΤΥΘΙΟΠΑΣΔΦΓΗΞΚΛΖΧΨΩΒΝΜάέήίόύώϊΐϋΆΈΉΊΌΎΏΪΫ", + "sertyuiopasdfghjklzxcvbnmertyuiopasdfghjklzxcvbnmaehioyviiyaehioyviy" + ) + return txt.translate(maTable).lower() + + +# isSignificant +# decides if the term is significant to be indexed; +# a term is significant if does not contain digit-chars [0-9], comma (,) or period (.) +# --- +significantExceptios = '7UP 3ΑΛΦΑ 17 3Π 7DAYS K2R'.split(' ') +def isSignificant(x) : + # fisrts exclude some notable exceptions (mostly brands) + if x in significantExceptios : + return True + + return not bool( re.match("\S*\d+\S*", x) ) + + +# check if word: w +# … has synonyms; return list of synonyms +# --- +def synonymKeys(w) : + w_kb = kbLatinString(w) + syns = [ w ] + found = False + # check if has synonyms + for group in synonyms : + possibles = group.split() + for wi in possibles : + if kbLatinString(wi) == w_kb : + syns = possibles + found = True + break + if found : + break + return syns + + +# set root-keyword: wl (if not exist) +# update frequency: f +# into list: l +# NOTE: +# * wl is a list of synonym-words +# ** comparison is based on the *keyboard* format +## --- +def rootKey ( wl, f, l ) : + keyExists = False + w_kb = kbLatinString(wl[0]) # cache kb format + + # check if exists in root keys already + # NOTE: you only need to check the 1st word of synonyms-list + for it in l : + if it['kb'] == w_kb : + keyExists = True + it['f'] += f + break + + # if not exists, append keyword + if keyExists == False : + l.append({ + 'w' : wl, + 'kb' : w_kb, + 'f' : f, + 'c' : [] + }) + + +# connect keys: a , b (each one is a list of synonmyms) +# of product with id: i +# with frequency: f +# into list: l +## --- +def connectKeys( a, b, i, f, l ) : + kbA = kbLatinString(a[0]) + kbB = kbLatinString(b[0]) + + if kbA == kbB : + return False ## exclude just-in-case + + for it in l : + if it['kb'] == kbA : + # found: a; + + # let's update connection to: b + bExists = False + for jt in it['c'] : + if jt['kb'] == kbB : + bExists = True + # update the connection's data + jt['f'] += f + jt['p'].append(i) + break + + if bExists == False : + # create connection with word: b + it['c'].append({ + 'w': b, + 'kb': kbB, + 'f': f, + 'p': [ i ] + }) + break + + +## let mysql to return valid strings +## (otherwise it returns strings with missed characters) +# credit: https://stackoverflow.com/a/68784172 +# analytical credit: https://stackoverflow.com/questions/27566078/ +def get_data_from_db(cursor, sql): + output = [] + cursor.execute(sql) + row = cursor.fetchone() + while row is not None: + row_to_return = row.decode('utf-8') if isinstance(row, bytearray) else row + output.append(row_to_return) + row = cursor.fetchone() + + return output + + +## replaces +# do all replaces in place +# --- (preproccessing) +replaces = [] +replaceSource = [ + '3 ΑΛΦΑ ;3ΑΛΦΑ ', + 'HEAD & SHOULDERS ;HEAD&SHOULDERS ', + 'W.K Kellogg ; ', + 'ΦΙΛΕΤ ;Φιλέτο ', + 'ΕΝΕΛΛΑΔ ;Εν-Ελλάδι ', + 'ΓΑΛΟΠΟΥΛ ;Γαλοπούλα ', + '7 DAYS ;7DAYS ', + 'ΜΠΑΡΜΠΑ ΣΤΑΘΗ ;ΜΠΑΡΜΠΑ-ΣΤΑΘΗΣ ' +] +for it in replaceSource : + st = it.split(';') + replaces.append({ 'src': st[0], 'trg': st[1] }) + +def do_replaces(w) : + for it in replaces : + w = w.replace(it['src'], it['trg']) + return w + + +## main preproccess function for product descriptions +# --- +def preprocessEdit(w) : + w = do_replaces(w) + # ... do other things if needed + # then ... + return w + + +## mark a link to a text +# conecting them with a dash/minus character +# --- +def markLink(lws, text) : + text_kb = kbLatinLetter(text.replace(' ', '-')) + lws_kb = kbLatinLetter(lws) + try: + index_l = text_kb.lower().index(lws_kb.lower()) + except: + return text + else: + return text[:index_l] + lws + text[index_l + len(lws):] + + +### # --- list of normalized word combinations +### replaceWords = [ +### 'HEAD & SHOULDERS; HEAD&SOULDERS', +### 'ΟΛΙΚΗΣ 'ΑΛΕΣΗΣ; Ολικής Άλεσης', +### 'Χωρίς προσθήκη ζάχαρης; Χωρίς-Ζάχαρη', +### 'ΚΑΠΝ.CRETA-FARMS; ΚΑΠΝΙΣΤΗ CRETA-FARMS' +### ] + + +# --- list of linked-words +linkedWords = [ + 'Χωρίς-Γλουτένη', + 'Χωρίς-Ζάχαρη', + 'Χωρίς-Αλάτι', + 'Χωρίς-Λακτόζη', + 'Χωρίς-Συντηρητικά', + 'Χωρίς-Αλκοόλ', + 'Χωρίς-Kαφεϊνη', + 'Χωρίς-Γλυκάνισο', + 'Χωρίς-Ανθρακικό', + 'Υψηλής-Παστερίωσης', + 'Ολικής-Άλεσης', + 'Ολικής-Aλέσεως', + 'Χαρτί-Υγείας', + 'ρολό-υγείας', + 'χαρτί-τουαλέτας', + 'Χαρτί-Κουζίνας', + 'Μπάρες-Δημητριακών', + 'Μπαρμπα-Στάθης', + 'COCA-COLA' + 'Aς-Μαγειρέψουμε', + 'ΚΡΙΣ-ΚΡΙΣ', + 'ΚΡΙ-ΚΡΙ', + 'ΕΛ-ΓΚΡΕΚΟ', + 'FREE-STEP', + 'EL-SABOR', + 'LE-PETIT-MARSEILLAIS', + 'DOUWE-EGBERTS', + 'ΕΝ-ΕΛΛΑΔΙ', + 'SPIN-SPAN', + 'CRETA-FARMS', + 'CRETA-FARM', + 'NES-CAFE', + 'Ολες-τις-Χρήσεις', + 'Το-Μάννα', + 'Χωρίς-προσθήκη-ζάχαρης' +] + + + +# mark linked words (connect them with a dash) +# return new text after "all-links" are marked +# --- +def markLinkedWords(text) : + for lw in linkedWords : + text = markLink( lw, text ) + return text + + +## handle words that can never be the first word on a search +noRootKeywords = [] +noRoot = [ + 'χωρίς', + 'εισαγωγής', + 'δώρο', + 'γεύση', + 'γεύσεις', + 'φέτες', + 'Χωρίς-Γλουτένη', + 'Χωρίς-Ζάχαρη', + 'Χωρίς-Αλάτι', + 'Χωρίς-Λακτόζη', + 'Χωρίς-Συντηρητικά', + 'Χωρίς-Αλκοόλ', + 'Χωρίς-Kαφεϊνη', + 'Χωρίς-Γλυκάνισο', + 'Χωρίς-Ανθρακικό', + 'Υψηλής-Παστερίωσης', + 'Ολες-τις-Χρήσεις', + 'Ολικής-Άλεσης', + 'Ολικής-Aλέσεως', + 'Ολικής', + 'Γαϊδούρας', + 'Γαϊδάρου', + 'Ρούχων', + 'Πιάτων', + 'πλύσεις', + 'Πλυντηρίου', + 'Φύλλων', + 'Γάλακτος', + 'Χρήσης', + 'Τύπου', + 'Ολλανδίας', + 'Απορριμμάτων', + 'Medium', + 'Μαλλιά', + 'Μαλλιών', + 'Γενικής', + 'Plus', + 'Classic', + 'Έκπληξη', + 'Μάνης', + 'Ελάτου', + 'Άγριων', + 'Βοτάνων', + 'Λακωνίας', + 'ΠΑΡΑΓΓΕΛΙΩΝ' +] +for w in noRoot : + noRootKeywords.append(kbLatinString(w)) + + +## PREPARE (or build) exception objects +# ////////////////////////////////////////////////////////////////////////////// + + +# --- list of words to exclude from keywords +# NOTE: +# APPLIED in PER-WORD base -> after spliting description to words +removeList = [] +removeOriginals = 'μας με σε για του της των από στο στον & r s ft l τ e g h k m n o p s x'.split(' ') +for it in removeOriginals : + removeList.append(kbLatinString(it)) + + +# --- list of synonyms +# in fact +synonyms = [ + 'μπίρα μπύρα μπίρες μπύρες', + 'αυγά αβγά αυγό', + 'σίκαλης σικάλεως', + 'ξηρά ξερά', + 'ρολό ρολλό', + 'coca-cola cocacola coke', + 'χαρτί-υγείας ρολό-υγείας χαρτί-τουαλέτας', + 'χαρτί-κουζίνας ρολό-κουζίνας', + 'οινος κρασι', + 'ΚΑΤΣΕΛΗΣ ΚΑΤΣΕΛΗ', + 'DR-OETKER OETKER', + 'DR.BECKMANN BECKMANN', + 'NES-CAFE NESCAFE', + 'Ολικής-Άλεσης Ολικής-Aλέσεως Ολικής', + 'τσίπουρο ρακή', + 'Βρώμη Βρώμης', + 'Φράουλα Φράουλες Φράουλας', + 'Μαλλιά Μαλλιών', + 'Κέικ, Cake', + 'CRETA-FARMS CRETA-FARM', + 'MARSEILLAIS LE-PETIT-MARSEILLAIS PETIT-MARSEILLAIS', + 'Γαϊδούρας Γαϊδάρου', + 'ΚΑΛΟΓΕΡΑΚΗΣ ΚΑΛΟΓΕΡΑΚΗ', + 'ΚΑΪΔΑΝΤΖΗΣ ΚΑΪΔΑΝΤΖΗ', + 'ΥΦΑΝΤΗΣ ΥΦΑΝΤΗ', + 'ΣΥΝΑΓΡΙΔΑ ΣΥΝΑΓΡΙΔΕΣ', + 'Ντομάτα Ντομάτας', + 'Ελαφρύ Ελαφρά Light', + 'Εγχώρια Εγχώριες Ελληνικό Ελληνική Ελληνικά', + 'τριμμένη τριμμένο', + 'Τόνος Τόνου', + 'Κριθαρένια κρίθινα', + 'Χωρίς-Kαφεϊνη Decaffeine', + 'Το-Μάννα Μάννα', + 'Κράνμπερι Κράνμπερις', + 'Κρήτης Κρητικό', + 'Πέννες Πένες', + 'Μακαρόνια Σπαγγέτι Σπαγγετίνι Σπαγγετόνι', + 'Καρτέλλα Καρτέλα Καρτέλλες' +] + + +## Read data +# ////////////////////////////////////////////////////////////////////////////// + + +## 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 + 'bpcs' : 7, # bpcs_code + 'img' : 8 # product's image file-name +} + +# enter your +HOST = "127.0.0.1" # server IP address/domain name +DATABASE = "dev_pythia_db" # database name +USER = "pythia_db_user_dev" +PASSWORD = "VnEP0eysjiXDHcfM" + +# connect to MySQL server +_dbc = mysql.connect( + host=HOST, + database=DATABASE, + user=USER, + password=PASSWORD, + use_unicode=True, + charset='utf8' + ) +print("Connected to:", _dbc.get_server_info()) + +# execute SQL to get all data you need +crs = _dbc.cursor() +query = ''' + SELECT count(pl.eys_code) as FREQuency, + pl.product_id as product_id, + pb.brand_name, + pl.barcode, pl.skl_code, pl.eys_code, + IF( pd.description IS NOT NULL , pd.description , pl.product_description ) AS product_description, + pl.bpcs_code, + pd.image_path + FROM product_list as pl + LEFT JOIN delivery_orders_products AS dop ON dop.product_id = pl.eys_code + LEFT JOIN delivery_orders AS do ON dop.order_id = do.id + LEFT JOIN product_list AS replacement ON dop.replacement_for = replacement.eys_code + LEFT JOIN product_details AS pd ON pl.eys_code = pd.eys_code + LEFT JOIN product_brands pb ON pl.brand_id = pb.id + WHERE pl.active = 1 AND pl.sap_code IS NOT NULL AND pl.product_category_sap_4 NOT LIKE '72%' + GROUP BY pl.product_id + ORDER BY FREQuency DESC +''' +results_ = get_data_from_db(crs, query) + +t_read = datetime.datetime.now() + + +# --- Lists to fill +keywords_ = [] # all data; main exported object +minilist_ = [] +products_ = [] + +## keywords format: +## [ +## { +## w : [ word, word-synonym, ... ], +## kb : = kbLatinString(word) +## f : 150, +## c : [ +## { w: ['fish', 'fishes'], f: 150, p: [122, 254, 907] }, +## { w: ['juice'], f: 50, p: [254, 351] } +## ] +## }, +## ... +## ] +## +## --- index: +## w : words / list of synonyms (str/utf-8) +# kb : ascii-latin-keypoard format of first item of "w" list +## f : frequency (int) +## c : combos / connections (list of objects) +## p : list of product-ids found in specific words-combination (list of int) + + +records_counter = 0 +## LOOP through the rows to pre-proccess all products +## --- +for row in results_ : + records_counter += 1 + + description = row[_COL['descr']] # product description + ## depricate: pid = domeInt( row[_COL['pid']] ) # product-id + fq = 0 if None else domeInt( row[_COL['freq']] ) # frequency + barcode = 0 if None else domeInt( row[_COL['barcd']] ) + sklcode = 0 if None else domeInt( row[_COL['sklcd']] ) + eyscode = 0 if None else domeInt( row[_COL['eyscd']] ) + bpcs = 0 if None else domeInt( row[_COL['bpcs']] ) + img = row[_COL['img']] + + pid = eyscode # actual product id (pid) it the eys_code + + # edit descriptions + description = preprocessEdit(description) + + # setup product + # --- + products_.append({ + 'w' : description, + 'id' : eyscode, + 'f' : fq, + 'bc' : barcode, + 'sc' : sklcode, + 'bp' : bpcs, + 'i' : img + }) + + + description = cleanText(description) # clean description string before spliting + + description = markLinkedWords(description) # ... + + keys = [] # list of product's key(word)s + words = description.split() # split to words + for w in words : + if kbLatinString(w) not in removeList: # if not in removeList + if isSignificant(w) : # and if significant + keys.append(w) # keep it + + + ## print(pid, description, words, keys) + ## print(pid, keys) + + # append words (and their combos) to the list + for w in keys : + wl = synonymKeys(w) + + # update root word frequency (if w CAN be a root word) + if kbLatinLetter(w) not in noRootKeywords : + rootKey( wl, fq, keywords_ ) + + ## if no other keyword in description add a dummy one + # so preserve reference to the final product + if len(keys) == 1 : + connectKeys( wl, ['*'], pid, fq, keywords_ ) + + for w2 in keys : + if w2 != w : + w2syns = synonymKeys(w2) + connectKeys( wl, w2syns, pid, fq, keywords_ ) + + + +# --- remove 'kb' keywords +for ki in keywords_ : + del ki['kb'] # kb not needed (kb_translation in js is really fast) + + +## 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) + + + +t_main = datetime.datetime.now() +print('Proccessing ended; saving results in json format ...') + + +## OUTPUT final data to a json-format file +# ////////////////////////////////////////////////////////////////////////////// + +with open("results/keywords.json", "w", encoding="utf-8") as outfile : + data = json.dump(keywords_, outfile, sort_keys=False, ensure_ascii=False) + +with open("results/products.json", "w", encoding="utf-8") as outfile : + data = json.dump(products_, outfile, sort_keys=False, ensure_ascii=False, separators=(',', ':')) + + +t_end = datetime.datetime.now() + +print(records_counter, 'products proccessed') +print('execution time:', (t_end - t0_)) +print('read.n.parse sources:', (t_read - t0_)) +print('proccessing products:', (t_main - t_read)) + + + +## NOTE: +## prepare cloud-sql-proxy +## --- +## * install cloud-sql-proxy +## : sudo wget https://dl.google.com/cloudsql/cloud_sql_proxy.linux.amd64 -O /usr/local/cloud_sql_proxy +## : sudo chmod +x /usr/local/cloud_sql_proxy +## +## * prepare/export/publish/copy credentials ... +## : sudo cp /some/path/to/cloudsqlproxy.json /usr/local +## +## * finaly run the database instance +## : /usr/local/cloud_sql_proxy -instances=pythia-251711:europe-west4:pythia-db-eu=tcp:3306 -credential_file=cloudsqlproxy.json +## +## after installation only the last command needs to run before connecting to the cloud-sql + + +# ΠΑΝΤΕΛΟΝΙ ΑΝΔ ΦΟΥΤ ΑΝ ΣΤΑ ΠΡΑΣ XXXL +# MAYBELLINECONCEALERAGEREWBLMEDIUM
\ No newline at end of file diff --git a/python/products-src-mysql.py b/python/products-src-mysql.py new file mode 100644 index 0000000..07bc7d3 --- /dev/null +++ b/python/products-src-mysql.py @@ -0,0 +1,535 @@ +## LIBRARIES +# ////////////////////////////////////////////////////////////////////////////// + +# import pandas as pd # pandas for excel reading +import mysql.connector as mysql # mysql connector +import re # regex +import json # json +import os.path # ... +import datetime + +t0_ = datetime.datetime.now() + +## 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 + + +## Clean Text ... +# -> removes some general/neutral words and symbols +# -> ignores some in-line characters +# -> also strips spare spaces +# function is applied onto the full title/description +# --- +def cleanText(x) : + ignoreList = '" ( ) [ ]'.split(' ') + + for r in ignoreList : + x = x.replace(r, ' ') + + x = x.replace(' ', ' ') # remove spare spaces + x = x.replace(' ', ' ') + x = x.replace(' ', ' ') + + return x.replace(' ', ' ') # one lase (just in case) + + +# isSignificant +# decides if the term is significant to be indexed; +# a term is significant if does not contain digit-chars [0-9], comma (,) or period (.) +# --- +def isSignificant(x) : + # fisrts exclude some notable exceptions (mostly brands) + if x in ['7UP', '3ΑΛΦΑ', '17'] : + return True + + return not bool( re.match("\S*\d+\S*", x) ) + + +def kbLatinString( txt ) : + maTable = txt.maketrans( + "ςερτυθιοπασδφγηξκλζχψωβνμΕΡΤΥΘΙΟΠΑΣΔΦΓΗΞΚΛΖΧΨΩΒΝΜάέήίόύώϊϋΆΈΉΊΌΎΏΪΫQWERTYUIOPASDFGHJKLZXCVBNM", + "sertyuiopasdfghjklzxcvbnmertyuiopasdfghjklzxcvbnmaehioyviyaehioyviyqwertyuiopasdfghjklzxcvbnm" + ) + txt = txt.replace('\'', '') + return txt.translate(maTable).lower() + + +# check if word: w +# … has synonyms; return list of synonyms +# --- +def synonymKeys(w) : + w_kb = kbLatinString(w) + syns = [ w ] + found = False + # check if has synonyms + for group in synonyms : + possibles = group.split() + for wi in possibles : + if kbLatinString(wi) == w_kb : + syns = possibles + found = True + break + if found : + break + return syns + + +# set root-keyword: wl (if not exist) +# update frequency: f +# into list: l +# NOTE: +# * wl is a list of synonym-words +# ** comparison is based on the *keyboard* format +## --- +def rootKey ( wl, f, l ) : + keyExists = False + w_kb = kbLatinString(wl[0]) # cache kb format + + # check if exists in root keys already + # NOTE: you only need to check the 1st word of synonyms-list + for it in l : + if it['kb'] == w_kb : + keyExists = True + it['f'] += f + break + + # if not exists, append keyword + if keyExists == False : + l.append({ + 'w' : wl, + 'kb' : w_kb, + 'f' : f, + 'c' : [] + }) + + +# connect keys: a , b (each one is a list of synonmyms) +# of product with id: i +# with frequency: f +# into list: l +## --- +def connectKeys( a, b, i, f, l ) : + kbA = kbLatinString(a[0]) + kbB = kbLatinString(b[0]) + + if kbA == kbB : + return False ## exclude just-in-case + + for it in l : + if it['kb'] == kbA : + # found: a; + + # let's update connection to: b + bExists = False + for jt in it['c'] : + if jt['kb'] == kbB : + bExists = True + # update the connection's data + jt['f'] += f + jt['p'].append(i) + break + + if bExists == False : + # create connection with word: b + it['c'].append({ + 'w': b, + 'kb': kbB, + 'f': f, + 'p': [ i ] + }) + break + + +## let mysql to return valid strings +## (otherwise it returns strings with missed characters) +# credit: https://stackoverflow.com/a/68784172 +# analytical credit: https://stackoverflow.com/questions/27566078/ +def get_data_from_db(cursor, sql): + output = [] + cursor.execute(sql) + row = cursor.fetchone() + while row is not None: + row_to_return = row.decode('utf-8') if isinstance(row, bytearray) else row + output.append(row_to_return) + row = cursor.fetchone() + + return output + + + +# letters-only translation to key-pressed characters (latin) +# --- +def kbLatinLetter( txt ) : + maTable = txt.maketrans( + "ςερτυθιοπασδφγηξκλζχψωβνμΕΡΤΥΘΙΟΠΑΣΔΦΓΗΞΚΛΖΧΨΩΒΝΜάέήίόύώϊϋΆΈΉΊΌΎΏΪΫ", + "sertyuiopasdfghjklzxcvbnmertyuiopasdfghjklzxcvbnmaehioyviyaehioyviy" + ) + return txt.translate(maTable).lower() + + +## mark a link to a text +# conecting them with a dash/minus character +# --- +def markLink(lws, text) : + text_kb = kbLatinLetter(text.replace(' ', '-')) + lws_kb = kbLatinLetter(lws) + try: + index_l = text_kb.lower().index(lws_kb.lower()) + except: + return text + else: + return text[:index_l] + lws + text[index_l + len(lws):] + + +### # --- list of normalized word combinations +### replaceWords = [ +### 'HEAD & SHOULDERS; HEAD&SOULDERS', +### 'ΟΛΙΚΗΣ 'ΑΛΕΣΗΣ; Ολικής Άλεσης', +### 'Χωρίς προσθήκη ζάχαρης; Χωρίς-Ζάχαρη' +### ] + + +# --- list of linked-words +linkedWords = [ + 'Χωρίς-Γλουτένη', + 'Χωρίς-Ζάχαρη', + 'Χωρίς-Αλάτι', + 'Χωρίς-Λακτόζη', + 'Χωρίς-Συντηρητικά', + 'Χωρίς-Αλκοόλ', + 'Υψηλής-Παστερίωσης', + 'Ολικής-Άλεσης', + 'Ολικής-Aλέσεως', + 'Χαρτί-Υγείας', + 'ρολό-υγείας', + 'χαρτί-τουαλέτας', + 'Χαρτί-Κουζίνας', + 'Μπάρες-Δημητριακών', + 'Μπαρμα-Στάθης', + 'Coca-Cola' + 'Aς-Μαγειρέψουμε', + 'ΚΡΙΣ-ΚΡΙΣ', + 'ΚΡΙ-ΚΡΙ', + 'ΕΛ-ΓΚΡΕΚΟ', + 'FREE-STEP', + 'EL-SABOR', + 'LE-PETIT-MARSEILLAIS', + 'DOUWE-EGBERTS', + 'ΕΝ-ΕΛΛΑΔΙ', + 'SPIN-SPAN', + 'CRETA-FARMS', + 'CRETA-FARM', + 'NES-CAFE' +] + + +# text after "all-links" marked +# --- +def markLinkedWords(text) : + for lw in linkedWords : + text = markLink( lw, text ) + + return text + + + +## 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 +} + + + +## PREPADE (or build) exception objects +# ////////////////////////////////////////////////////////////////////////////// + + +# --- list of words to exclude from keywords +# NOTE: +# APPLIED in PER-WORD base -> after spliting description to words +removeList = [] +removeOriginals = 'μας με σε για του της των από στο στον & r s ft l τ e g h k m n o p s x'.split(' ') +for it in removeOriginals : + removeList.append(kbLatinString(it)) + + +# --- list of synonyms +# in fact +synonyms = [ + 'μπίρα μπύρα μπίρες μπύρες', + 'αυγά αβγά αυγό αβγό', + 'σίκαλης σικάλεως', + 'ξηρά ξερά', + 'ρολό ρολλό', + 'coca-cola cocacola coke', + 'χαρτί-υγείας ρολό-υγείας χαρτί-τουαλέτας', + 'χαρτί-κουζίνας ρολό-κουζίνας', + 'οινος κρασι', + 'ΚΑΤΣΕΛΗΣ ΚΑΤΣΕΛΗ', + 'DR-OETKER OETKER', + 'DR.BECKMANN BECKMANN', + 'NES-CAFE NESCAFE', + 'Ολικής-Άλεσης Ολικής-Aλέσεως Ολικής', + 'τσίπουρο ρακή', + 'Βρώμη Βρώμης', + 'Φράουλα Φράουλες Φράουλας', + 'Μαλλιά Μαλλιών', + 'Κέικ, Cake', + 'CRETA-FARMS CRETA-FARM', + 'MARSEILLAIS LE-PETIT-MARSEILLAIS', + 'Γαϊδούρας Γαϊδάρου', + 'ΚΑΛΟΓΕΡΑΚΗΣ ΚΑΛΟΓΕΡΑΚΗ', + 'ΚΑΪΔΑΝΤΖΗΣ ΚΑΪΔΑΝΤΖΗ', + 'ΥΦΑΝΤΗΣ ΥΦΑΝΤΗ', + 'ΣΥΝΑΓΡΙΔΑ ΣΥΝΑΓΡΙΔΕΣ' +] + + +## Read data +# ////////////////////////////////////////////////////////////////////////////// + +# enter your +HOST = "127.0.0.1" # server IP address/domain name +DATABASE = "dev_pythia_db" # database name +USER = "pythia_db_user_dev" +PASSWORD = "VnEP0eysjiXDHcfM" + +# connect to MySQL server +_dbc = mysql.connect( + host=HOST, + database=DATABASE, + user=USER, + password=PASSWORD, + use_unicode=True, + charset='utf8' + ) +print("Connected to:", _dbc.get_server_info()) + +# execute SQL to get all data you need +crs = _dbc.cursor() +query = ''' + SELECT count(pl.eys_code) as FREQuency, + pl.product_id as product_id, + pb.brand_name, + pl.barcode, pl.skl_code, pl.eys_code, + IF( pd.description IS NOT NULL , pd.description , pl.product_description) AS product_description + FROM product_list as pl + LEFT JOIN delivery_orders_products AS dop ON dop.product_id = pl.eys_code + LEFT JOIN delivery_orders AS do ON dop.order_id = do.id + LEFT JOIN product_list AS replacement ON dop.replacement_for = replacement.eys_code + LEFT JOIN product_details AS pd ON pl.eys_code = pd.eys_code + LEFT JOIN product_brands pb ON pl.brand_id = pb.id + WHERE pl.active = 1 AND pl.sap_code IS NOT NULL AND pl.product_category_sap_4 NOT LIKE '72%' + GROUP BY pl.product_id + ORDER BY FREQuency DESC +''' +results_ = get_data_from_db(crs, query) + +t_db = datetime.datetime.now() + + +# --- Lists to fill +keywords_ = [] # all data; main exported object +minilist_ = [] +products_ = [] + +## keywords format: +## [ +## { +## w : [ word, word-synonym, ... ], +## kb : = kbLatinString(word) +## f : 150, +## c : [ +## { w: ['fish', 'fishes'], f: 150, p: [122, 254, 907] }, +## { w: ['juice'], f: 50, p: [254, 351] } +## ] +## }, +## ... +## ] +## +## --- index: +## w : words / list of synonyms (str/utf-8) +# kb : ascii-latin-keypoard format of first item of "w" list +## f : frequency (int) +## c : combos / connections (list of objects) +## p : list of product-ids found in specific words-combination (list of int) + + +records_counter = 0 +## LOOP through the rows to pre-proccess all products +## --- +for row in results_ : + records_counter += 1 + + description = row[_COL['descr']] # product description + pid = domeInt( row[_COL['pid']] ) # product-id + fq = domeInt( row[_COL['freq']] ) # frequency + + # setup product + # --- + products_.append({ + 'w' : description, + 'id' : pid, + 'f' : fq + }) + + # TODO: + # identify brands + # then ... + + description = cleanText(description) # clean description string before spliting + + description = markLinkedWords(description) # ... + + keys = [] # list of product's key(word)s + words = description.split() # split to words + for w in words : + if kbLatinString(w) not in removeList: # if not in removeList + if isSignificant(w) : # and if significant + keys.append(w) # keep it + + + ## print(pid, description, words, keys) + print(pid, keys) + + # append words (and their combos) to the list + for w in keys : + wl = synonymKeys(w) + rootKey( wl, fq, keywords_ ) + for w2 in keys : + if w2 != w : + w2syns = synonymKeys(w2) + connectKeys( wl, w2syns, 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) + + + + +## alternative formats to test ------------------------------------------- START + +with open("results/keywords-full.json", "w", encoding="utf-8") as outfile : + data = json.dump(keywords_, outfile, sort_keys=False, indent=2, ensure_ascii=False) + +keyhashes_ = [] +hashedkeys_ = [] + +# --- remove 'kb' keywords +for ki in keywords_ : + h = ki['kb'] + keyhashes_.append({ h : ki['w'] }) + conns = [] + del ki['kb'] + for ci in ki['c'] : + conns.append({ + 'h' : ci['kb'], + 'f' : ci['f'], + 'p' : ci['p'] + }) + del ci['kb'] + hashedkeys_.append({ + 'h' : h, + 'f' : ci['f'], + 'c' : conns + }) + +with open("results/hashes.json", "w", encoding="utf-8") as outfile : + data = json.dump(keyhashes_, outfile, sort_keys=False, indent=2, ensure_ascii=False) + +with open("results/hashedkeys.json", "w", encoding="utf-8") as outfile : + data = json.dump(hashedkeys_, outfile, sort_keys=False, indent=2, ensure_ascii=False) + +# --- create mini list based on the sorted keywords_ +### for it in keywords_ : +### minilist_.append({ +### 'w' : it['w'], +### 'f' : it['f'], +### 'kb': it['kb'] +### }) +### +### with open("results/minilist.json", "w", encoding="utf-8") as outfile : +### data = json.dump(minilist_, outfile, sort_keys=False, indent=3, ensure_ascii=False) + + +## alternative formats to test --------------------------------------------- END + + + + +t_main = datetime.datetime.now() +print('Proccessing ended; saving results in json format ...') + + +## OUTPUT final data to a json-format file +# ////////////////////////////////////////////////////////////////////////////// + +with open("results/keywords.json", "w", encoding="utf-8") as outfile : + data = json.dump(keywords_, outfile, sort_keys=False, indent=2, ensure_ascii=False) + +with open("results/products.json", "w", encoding="utf-8") as outfile : + data = json.dump(products_, outfile, sort_keys=False, indent=2, ensure_ascii=False) + + +t_end = datetime.datetime.now() + + +print(records_counter, 'products proccessed') +print('execution time:', (t_end - t0_)) +print('from which ... database:', (t_db - t0_)) +print('... records proccessing:', (t_main - t_db)) + + + +## NOTE: +## prepare cloud-sql-proxy +## --- +## * install cloud-sql-proxy +## : sudo wget https://dl.google.com/cloudsql/cloud_sql_proxy.linux.amd64 -O /usr/local/cloud_sql_proxy +## : sudo chmod +x /usr/local/cloud_sql_proxy +## +## * prepare/export/publish/copy credentials ... +## : sudo cp /some/path/to/cloudsqlproxy.json /usr/local +## +## * finaly run the database instance +## : /usr/local/cloud_sql_proxy -instances=pythia-251711:europe-west4:pythia-db-eu=tcp:3306 -credential_file=cloudsqlproxy.json +## +## after installation only the last command needs to run before connecting to the cloud-sql diff --git a/python/read-brands.py b/python/read-brands.py new file mode 100644 index 0000000..2bbc767 --- /dev/null +++ b/python/read-brands.py @@ -0,0 +1,270 @@ +## 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) : + removeOriginals = 'Μας με σε για του της των από ΜΕ ΣΕ ΓΙΑ στο στον Στο από e g h k m n o p s x'.split(' ') + + for r in removeOriginals : + x = x.replace(r, ' ') + + x.replace(' ', ' ') # remove spare spaces + x.replace(' ', ' ') + x.replace(' ', ' ') + + return x + + +# function isSignificant +# decides if the term is significant to be indexed; +# a term is significant if does not contain digit-chars +# --- +def isSignificant(x) : + # fisrts exclude some notable exceptions + if x in ['7UP', '3ΑΛΦΑ'] : + return True + + return not bool(re.match("\S*\d+\S*", x)) + + +def kbLatinString( txt ) : + maTable = txt.maketrans( + "ςερτυθιοπασδφγηξκλζχψωβνμΕΡΤΥΘΙΟΠΑΣΔΦΓΗΞΚΛΖΧΨΩΒΝΜάέήίόύώϊϋΆΈΉΊΌΎΏΪΫ", + "sertyuiopasdfghjklzxcvbnmertyuiopasdfghjklzxcvbnmaehioyviyaehioyviy" + ) + return txt.translate(maTable).lower() + + +# set root-keyQ: w (if not exist) +# update frequency: f +# into list: l +# NOTE: in this version, +# comparison is based on the *keyboard* format +## --- +def rootKey ( w, f, l ) : + keyExists = False + kbW = kbLatinString(w) + + for it in l : + if it['kb'] == kbW : + keyExists = True + it['f'] += f + if w not in it['alt'] : + it['alt'].append(w) + + if keyExists == False : + l.append({ + 'w' : w, + 'f' : f, + 'alt' : [ w ], + 'kb' : kbW, + '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 : + + # found: a; + # lets update the connection to: b + bExists = False + + for jt in it['c'] : + if jt['kb'] == kbLatinString(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, + 'kb': kbLatinString(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_ = [] +products_ = [] + +## keywords format: +## [ +## { +## w : 'fresh', +## alt : [ 'Fresh', 'FRESH', 'fresh' ] +## kb : +## f : 150, +## c : [ +## { w : 'milk', f : 150 , p : [122, 254, 907] }, +## { w : 'juice', f : 50 , p : [254, 351] } +## ] +## }, +## {...}, +## ... +## ] +## --- index: +## w : word (str/utf-8) +## f : frequency (int) +## c : combos / connections (list of objects) +## p : list of product-ids found in specific words-combination (list of int) +## alt : list of alternative writtings (list of str/utf-8) +## kb: *keyboard* writting (str/latin-ascii) + + +# --- 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 + + # setup product + # --- + products_.append({ + 'w' : description, + 'id' : pid, + 'f' : fq + }) + + # TODO: + # identify brands + # then ... + + description = cleanText(description) # clean sescription string + words = description.strip().split() # split to words + + # 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 and isSignificant(w2) : + 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({ + 'w' : it['w'], + 'f' : it['f'], + 'kb': it['kb'] + }) + + +## OUTPUT final data to a json-format file +# ////////////////////////////////////////////////////////////////////////////// + +with open("results/keywords-v3.json", "w", encoding="utf-8") as outfile : + data = json.dump(keywords_, outfile, sort_keys=False, indent=3, ensure_ascii=False) + +with open("results/minilist-v3.json", "w", encoding="utf-8") as outfile : + data = json.dump(minilist_, outfile, sort_keys=False, indent=3, ensure_ascii=False) + +with open("results/products.json", "w", encoding="utf-8") as outfile : + data = json.dump(products_, outfile, sort_keys=False, indent=3, ensure_ascii=False)
\ No newline at end of file diff --git a/python/readmysql.py b/python/readmysql.py new file mode 100644 index 0000000..a3cd31f --- /dev/null +++ b/python/readmysql.py @@ -0,0 +1,121 @@ +## pip3 install mysql-connector-python +import mysql.connector as mysql + +# enter your server IP address/domain name +HOST = "127.0.0.1" # or "domain.com" +# database name, if you want just to connect to MySQL server, leave it empty +DATABASE = "dev_pythia_db" +# this is the user you create +USER = "pythia_db_user_dev" ## "pythia_db_user_dev@cloudsqlproxy~35.203.252.44" +# user password +PASSWORD = "VnEP0eysjiXDHcfM" +# connect to MySQL server +_dbc = mysql.connect(host=HOST, database=DATABASE, user=USER, password=PASSWORD) +print("Connected to:", _dbc.get_server_info()) +# enter your code here! + + +def get_data_from_db(cursor, sql): + output = [] + cursor.execute(sql) + row = cursor.fetchone() + while row is not None: + row_to_return = row.decode('utf-8') if isinstance(row, bytearray) else row + output.append(row_to_return) + row = cursor.fetchone() + + return output + + + +cursor_ = _dbc.cursor() +#### cursor_.execute(''' +#### SELECT count(dop.order_id) as FREQuency, +#### dop.product_id as product_id, +#### pb.brand_name, +#### pl.barcode, pl.skl_code, pl.eys_code, +#### IF( pd.description IS NOT NULL , pd.description , pl.product_description) AS product_description +#### FROM delivery_orders_products AS dop +#### LEFT JOIN delivery_orders AS do ON dop.order_id = do.id +#### LEFT JOIN product_list AS pl ON dop.product_id = pl.eys_code +#### LEFT JOIN product_list AS replacement ON dop.replacement_for = replacement.eys_code +#### LEFT JOIN product_details AS pd ON pl.eys_code = pd.eys_code +#### INNER JOIN product_brands pb ON pl.brand_id = pb.id +#### GROUP BY dop.product_id +#### ORDER BY FREQuency DESC +#### ''') +#### +#### results = cursor_.fetchall() +#### +#### for rec in results : +#### print(rec) + + +sqlq = ''' + SELECT count(dop.order_id) as FREQuency, + dop.product_id as product_id, + pb.brand_name, + pl.barcode, pl.skl_code, pl.eys_code, + IF( pd.description IS NOT NULL , pd.description , pl.product_description) AS product_description + FROM delivery_orders_products AS dop + LEFT JOIN delivery_orders AS do ON dop.order_id = do.id + LEFT JOIN product_list AS pl ON dop.product_id = pl.eys_code + LEFT JOIN product_list AS replacement ON dop.replacement_for = replacement.eys_code + LEFT JOIN product_details AS pd ON pl.eys_code = pd.eys_code + INNER JOIN product_brands pb ON pl.brand_id = pb.id + GROUP BY dop.product_id + ORDER BY FREQuency DESC +''' + + +results = get_data_from_db(cursor_, sqlq) + +for r in results : + if r[1] in [1176465, 1430400, 1220273] : + print(r[1], r[6].split()) + + + + + + + + + + + + + + +## prepare cloud-sql-proxy +## --- +## * install cloud-sql-proxy +## : sudo wget https://dl.google.com/cloudsql/cloud_sql_proxy.linux.amd64 -O /usr/local/cloud_sql_proxy +## : sudo chmod +x /usr/local/cloud_sql_proxy +## +## * prepare/export/publish/copy credentials ... +## : sudo cp /some/path/to/cloudsqlproxy.json /usr/local +## +## * finaly run the database instance +## : /usr/local/cloud_sql_proxy -instances=pythia-251711:europe-west4:pythia-db-eu=tcp:3306 -credential_file=cloudsqlproxy.json + +## Queries +## --- +''' +-- ORDERS PER PRODUCT +SELECT count(dop.order_id) as FREQuency, + dop.product_id as product_id, + pb.brand_name, + pl.barcode, pl.skl_code, pl.eys_code, + IF( pd.description IS NOT NULL , pd.description , pl.product_description) AS product_description, + pcs4.description AS productGroup +FROM delivery_orders_products AS dop +LEFT JOIN delivery_orders AS do ON dop.order_id = do.id +LEFT JOIN product_list AS pl ON dop.product_id = pl.eys_code +LEFT JOIN product_list AS replacement ON dop.replacement_for = replacement.eys_code +LEFT JOIN product_details AS pd ON pl.eys_code = pd.eys_code +LEFT JOIN product_categories_sap_4 pcs4 ON pcs4.id = pl.product_category_sap_4 +INNER JOIN product_brands pb ON pl.brand_id = pb.id +GROUP BY dop.product_id +ORDER BY FREQuency DESC +''' diff --git a/python/test.py b/python/test.py new file mode 100644 index 0000000..4c5a361 --- /dev/null +++ b/python/test.py @@ -0,0 +1,104 @@ +# letters-only translation to key-pressed characters (latin) +# --- +def kbLatinLetter( txt ) : + maTable = txt.maketrans( + "ςερτυθιοπασδφγηξκλζχψωβνμΕΡΤΥΘΙΟΠΑΣΔΦΓΗΞΚΛΖΧΨΩΒΝΜάέήίόύώϊϋΆΈΉΊΌΎΏΪΫ", + "sertyuiopasdfghjklzxcvbnmertyuiopasdfghjklzxcvbnmaehioyviyaehioyviy" + ) + return txt.translate(maTable).lower() + + +# mark a link to a text +# conecting them with a dash/minus character +# --- +def markLink(lws, text) : + text_kb = kbLatinLetter(text.replace(' ', '-')) + lws_kb = kbLatinLetter(lws) + try: + index_l = text_kb.lower().index(lws_kb.lower()) + except: + return text + else: + return text[:index_l] + lws + text[index_l + len(lws):] + + +# text after "all-links" marked +# --- +def linksMarked(text) : + allinked = [ + 'Χωρίς-Ζάχαρη', + 'COCA-COLA', + 'Χωρίς-Αλάτι' + ] + for lw in allinked : + text = markLink( lw, text ) + + return text + + +# example +# --- +products = [ + "Μπάρες δημητριακών Nestle χωρίς ζάχαρη 2+1 δώρο", + "Coca Cola Zero χωρίς ζάχαρη 300ml", + "Καφές ΠΑΠΑΓΑΛΟΣ ΛΟΥΜΙΔΗΣ 100gr Κλασσικός", + "Μουσακάς μερίδα 300gr χωρίς αλάτι", + "Bic Metal ξυραφάκια 8+2 δώρο" +] + + +synonyms = [] +synonymOriginals = [ + 'μπίρα μπύρα μπίρες μπύρες', + 'αυγά αβγά αυγό αβγό', + 'σίκαλης σικάλεως', + 'ξηρά ξερά', + 'ρολό ρολλό' + 'coca-cola cocacola coke', + 'χαρτί-υγείας ρολό-υγείας χαρτί-τουαλέτας', + 'χαρτί-κουζίνας ρολό-κουζίνας', + 'οινος κρασι', + 'ΚΑΤΣΕΛΗΣ ΚΑΤΣΕΛΗ', + 'DR-OETKER OETKER', + 'DR.BECKMANN BECKMANN', + 'NES-CAFE NESCAFE', + 'Ολικής-Άλεσης Ολικής-Aλέσεως', + 'τσίπουρο ρακή' +] +for group in synonymOriginals : + words = group.split() + syns = [] + for w in words : # for every word in group of synonyms + w_kb = kbLatinLetter(w) + exist = False + for s in syns : # check if synonym exists + if s[1] == w_kb : + exist = True + if not exist : # if not: append it + #### syns.append({ + #### 'w' : w, + #### 'kb' : w_kb + #### }) + syns.append([ w, w_kb ]) + synonyms.append(syns) + +for p in products : + print( linksMarked(p) ) + +import json + +print(json.dumps(synonyms, ensure_ascii=False)) + + +import datetime + +start = datetime.datetime.now() + +malist = [ "καλαμπόκι-διαβητικών", "cocacola-zero", "γιουβαρλάκια", "WELCOME", "σφενδόνα", "σκλαβενίτης", 'bonora', 'kris-κρις-παπαδοπούλου', "τηλεφώνημα", "χωρίς-αλάτι" ] + +for i in range(1, 1000000) : + for w in malist : + tmp = kbLatinLetter(malist[i%10]) + +end = datetime.datetime.now() +print('execution time:', (end-start), 's')
\ No newline at end of file |