<jupyter_start><jupyter_text>Helpful Links* https://www.dataquest.io/blog/jupyter-notebook-tips-tricks-shortcuts/ All Important Imports (Please don't misuse my api key...) Plan*<jupyter_code># Sauce: https://www.geeksforgeeks.org/python-merge-list-with-common-elements-in-a-list-of-lists/
#Importing  
from collections import defaultdict 
  
#merge function to  merge all sublist having common elements. 
def merge_common(lists): 
    neigh = defaultdict(set) 
    visited = set() 
    for each in lists: 
        for item in each: 
            neigh[item].update(each) 
    def comp(node, neigh = neigh, visited = visited, vis = visited.add): 
        nodes = set([node]) 
        next_node = nodes.pop 
        while nodes: 
            node = next_node() 
            vis(node) 
            nodes |= neigh[node] - visited 
            yield node 
    for node in neigh: 
        if node not in visited: 
            yield sorted(comp(node)) 
%run ./utils.ipynb
import googlemaps
gmaps = googlemaps.Client(key="<KEY>")
import pymongo
mongoclient = pymongo.MongoClient("mongodb+srv://markusmo3:<EMAIL>/test?retryWrites=true&w=majority")
db = mongoclient["dmdb-restaurant"]
origdata = pd.read_csv('../data/work/restaurants.tsv', delimiter='\t')
origdata
#origdata[origdata.name.str.contains(r"\(")]
origdata2 = origdata.copy()
origdata2.type = origdata2.type.fillna("")
#d[d.name.str.contains(r"\(")]
cityDict = {
    "w. hollywood": "west hollywood",
    "new york city": "new york",
    "west la": "los angeles",
    "la": "los angeles"
}

addressDict = {
    r"(ave|av)": "ave",
    r"(blvd|blv)": "blvd",
    r"(sts)": "st",
    r"s\.": "s",
    r" ?between.*$": ""
}

BRACKETS_REGEX = r" ?\(.*\)"
NON_ALPHA_OR_SPACE_REGEX = r"[^a-zA-Z0-9 ]"
NON_ALPHA_REGEX = r"[^a-zA-Z0-9]"
def preProcess(origData):
    df = origData.copy()
    #df = df.drop(labels=['id'], axis=1)
    #df.set_index("id", inplace = True)
    
    for k, v in addressDict.items():
        df.address = df.address.str.replace(k, v, case=False, regex=True)
    for k, v in cityDict.items():
        df.city = df.city.str.replace(k, v, case=False)
    df.type = df.type.fillna("")
    df.type = df.type.str.replace(BRACKETS_REGEX, '', case=False)\
        .str.replace(r"^.*[0-9] ?", "", case=False)
    df.phone = df.phone.str.replace(NON_ALPHA_REGEX, '', case=False)
    df.name = df.name.str.replace(BRACKETS_REGEX, '', case=False)
    
    df["cname"] = df.name.copy()
    df["caddress"] = df.address.copy()
    df.cname = df.cname.str.replace(NON_ALPHA_OR_SPACE_REGEX, '', case=False)\
        .str.replace('  the$', '', case=False)
    df.caddress = df.caddress.str.replace(NON_ALPHA_OR_SPACE_REGEX, '', case=False)
    
    #df = df.drop_duplicates(["city", "phone", "type", "name", "address"])
    return df
preProcessedData = preProcess(origdata)
preProcessedData
groupedData = preProcessedData.groupby(["city", "cname", "caddress"]).agg(set).reset_index()
groupedData
dedupeData = preProcessedData.groupby(["phone", "cname"]).agg(set).reset_index()
#groupedData[groupedData.id.map(len) > 1]
#preProcessedData.describe(include=["object"])

#printUniqueTokens(preProcessedData.address)
#us = preProcessedData.type.unique()
#us = sorted(us, key=lambda x: len(x))
#us
print("# SIMPLE GROUP")
compareToGold(groupedData)
print("# GROUP BY PHONE")
a, b, c = compareToGold(dedupeData)
b<jupyter_output># SIMPLE GROUP
True positives: 56
False negatives: 56
False positives: 0
Precision: 1.0
Recall: 0.5
Fscore: 0.6666666666666666
# GROUP BY PHONE
True positives: 88
False negatives: 24
False positives: 0
Precision: 1.0
Recall: 0.7857142857142857
Fscore: 0.88<jupyter_text>Now try using token based textdistance<jupyter_code>td.jaccard("Hallo Welt s", "Welt Hallo s")
s = frozenset(["Hallo", "welt"])
dict1 = {s: "wow"}
dict1

combis = set(map(frozenset, combinations(set(preProcessedData.cname.unique()), 2)))
len(combis)
from itertools import combinations

def calcDistances(nameset):
    def bias(p1, p2):
        if p1 in p2 or p2 in p1:
            return 1
        else:
            return 0
    alldists = []
    
    combis = set(map(frozenset, combinations(set(nameset), 2)))
    #for c in combis:
    dists = []
    dists = [(p1, p2, p1 == p2 and -1 or td.jaccard(p1, p2) + bias(p1, p2)) for p1, p2 in combis]
    alldists += dists
    
    #for someset in nameseries:
    #    if len(someset) <= 1:
    #        continue
    #    dists = []
    #    dists = [(p1, p2, p1 == p2 and -1 or td.jaccard(p1, p2) + bias(p1, p2)) for p1 in someset for p2 in someset]
    #    alldists += dists
    alldists.sort(key=lambda x: x[2], reverse=True)
    return alldists

alldists = calcDistances(preProcessedData.cname.unique())
def transformToEqualityRings(l):
    sets = map(lambda x: set(x[0:2]), l)
    return list(merge_common(sets)) 

filtered = list(filter(lambda x: x[2] >= 0.70, alldists))
filtered
eqRing = transformToEqualityRings(filtered)
#uniquemappings = dict(map(lambda x: set(x[0:2]), filtered))
def findClosestMatch(x):
    for p1, p2, v in filtered:
        if x == p1:
            return p1
        elif x == p2:
            return p1
    return x

def findClosestEqMatch(x):
    for ring in eqRing:
        for e in ring:
            if e == x:
                return ring[0]
    return x

def mapTextDistanceToUnique(df):
    df["tdkey"] = df.cname.copy()
    #df.tdkey = df.tdkey.apply(findClosestMatch)
    df.tdkey = df.tdkey.apply(findClosestEqMatch)
    return df
    
tdData = preProcessedData.copy()
tdData = mapTextDistanceToUnique(tdData)
tdData
dedupeData2 = tdData.groupby(["phone", "tdkey"]).agg(set).reset_index()
print("# SIMPLE GROUP")
compareToGold(groupedData)
print("# GROUP BY PHONE")
a, b, c = compareToGold(dedupeData2)
missing = [y for x in b for y in x]
tdData[tdData.id.isin(missing)]<jupyter_output><empty_output><jupyter_text>----- Google Stuff from here....<jupyter_code>callgoogle = False
if callgoogle:
    locdata = origdata.copy()
    uniqueList = locdata[["address", "city"]].apply(tuple, axis=1).unique()
    addDict = {}
    for add, city in uniqueList:
        string = add + ", " + city
        addDict[string] = gmaps.geocode(string)
        #addDict[string] = [{"wow": string}]

    def computeGeocode(s):
        string = s["address"] + ", " + s["city"]
        return json.dumps(addDict[string])
    
    locdata["google_geocode"] = locdata.apply(computeGeocode, axis=1)
    
    # There is one 'broken' entry, but according to the phone number it existed a while ago
    print("# Entry not found by Google:")
    print(locdata.loc[locdata["google_geocode"] == "[]"])

    # fix it by setting it manually
    locdata.loc[locdata["google_geocode"] == "[]", ["google_geocode"]] = json.dumps(gmaps.geocode("1022 3rd ave., new york"))

    # now it has a proper google_geocode
    print("# Entry now with proper google_geocode:")
    locdata[locdata["name"] == "<NAME>"]
    
    locdata.to_csv(r'/data/restaurants_geocoded.csv', index=False)
else:
    locdata = pd.read_csv('/data/restaurants_geocoded.csv')

locdata.head(10)
# Access the json like that
json.loads(locdata.iloc[0].google_geocode)[0]["geometry"]["location"]
# extract some useful info from the json
def getloc(x, t):
    if x == "[]":
        return -1
    return json.loads(x)[0]["geometry"]["location"][t]

def getplaceid(x):
    if x == "[]" or len(x) == 0:
        return -1
    return json.loads(x)[0]["place_id"]
locdata["lat"] = locdata.google_geocode.map(lambda x: getloc(x, "lat"))
locdata["lng"] = locdata.google_geocode.map(lambda x: getloc(x, "lng"))
locdata["place_id"] = locdata.google_geocode.map(lambda x: getplaceid(x))

locdata.head()
# no missing values
locdata[locdata["place_id"] == -1]
locdata[locdata.duplicated(['place_id'], keep=False)]
groupdata = locdata.groupby("place_id")["name"].apply(list).reset_index()
groupdata[groupdata["name"].map(len) > 1]
cleandata = locdata.copy()
#cleandata = cleandata.drop(labels=['id'], axis=1)
cleandata.set_index("id", inplace = True) 
cleandata = cleandata.drop(labels=['google_geocode'], axis=1)
cleandata.name = cleandata.name.str.replace('[^a-zA-Z0-9 ]', '', case=False)
cleandata.name = cleandata.name.str.replace('  the$', '', case=False)
cleandata.address = cleandata.address.str.replace('[^a-zA-Z0-9 ]', '', case=False)
cleandata.phone = cleandata.phone.str.replace('[^a-zA-Z0-9]', '', case=False)
cleandata.city = cleandata.city.str.replace('new york city', 'new york', case=False)
cleandata.type = cleandata.type.str.replace(' \(new\)', '', case=False)
#cleandata.city = cleandata.city.str.replace('studio city', 'los angeles', case=False)

cleandata = cleandata.drop_duplicates(["name", "place_id"])
#cleandata = cleandata.groupby(["name", "place_id", "lat", "lng"]).agg(set)
cleandata
def refactor(k, x):
    x["id"] = k
    x["location"] = {
      "type": "Point",
      "coordinates": [x["lng"], x["lat"]]
    }
    del x["lng"]
    del x["lat"]
    return x

newdict = cleandata.to_dict("index")
for k, v in newdict.items():
    newdict[k] = refactor(k, v)
newdict
newdict[1]["location"]
# clear db
db.dmdb.delete_many({})
db.dmdb.insert_many(newdict.values())
for i, row in newdict.items():
    cursor = db.dmdb.find({"location":
       { "$near":
          {
            "$geometry": row["location"],
            "$minDistance": 1,
            "$maxDistance": 300
          }
       }
    })
    newlist = list(cursor)
    for e in newlist:
        newdict[e["id"]]["place_id"] = row["place_id"]
        #print("Found close one: " + str(row))
newdict
def clean(x):
    x.place_id = newdict[x.name]["place_id"]
    return x

cleandata = cleandata.apply(clean, axis=1)
cleandata
namesetdata = cleandata[["name", "place_id"]].groupby(["place_id"]).agg(set)
namesetdata[namesetdata["name"].map(len) > 1]
from itertools import combinations

def bias(p1, p2):
    if p1 in p2 or p2 in p1:
        return 0.5
    else:
        return 0

alldists = []
for someset in namesetdata.name:
    if len(someset) <= 1:
        continue
    dists = []
#    for p1 in someset:
#        for p2 in someset:
#            if p1 == p2:
#                break
#            else:
#                dists.append((p1, p2, td.jaccard(p1, p2)))
    dists = [(p1, p2, p1 == p2 and -1 or td.jaccard(p1, p2) + bias(p1, p2)) for p1 in someset for p2 in someset]
#    for comb in combinations(someset, 2):
#        dists.append((comb[0], comb[1], td.jaccard(comb[0], comb[1])))
    alldists += dists

alldists.sort(key=lambda x: x[2])
alldists

filtered = list(filter(lambda x: x[2] > 0.7, alldists))
# treat the filtered as equal...
uniquemappings = dict(map(lambda x: set(x[0:2]), filtered))
uniquemappings
cleandata.name = cleandata.name.apply(lambda x: uniquemappings.get(x, x))
cleandata
cleandata = cleandata.drop_duplicates(["name", "place_id"])
cleandata<jupyter_output><empty_output>
{'fraction_non_alphanumeric': 0.16469719350073855, 'fraction_numerical': 0.01763293943870015, 'mean_word_length': 3.576679340937896, 'pattern_counts': {'":': 18, '<': 17, '<?xml version=': 0, '>': 20, 'https://': 2, 'lorem ipsum': 0, 'www.': 2, 'xml': 0}, 'pii_count': 0, 'substrings_counts': 1, 'word_list_counts': {'cursed_substrings.json': 0, 'profanity_word_list.json': 0, 'sexual_word_list.json': 0, 'zh_pornsignals.json': 0}}
{'chain_length': '3', 'dir': 'jupyter-structured-clean-dedup', 'id': '190139', 'license': 'MIT', 'n_tokens_mistral': 4190, 'n_tokens_neox': 4041, 'n_words': 866, 'path': 'notebooks/restaurant-prepare.ipynb', 'repo_name': 'markusmo3/oth-dmdb-restaurant', 'size': '10900'}