## 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)