Algo to identify slightly different uniquely identifiable common names in 3 DataFrame columns

user_5570089 0
Viewed 26

Sample DataFrame df has 3 columns to identify any given person, viz., name, nick_name, initials. They can have slight differences in the way they are specified but looking at three columns together it is possible to overcome these differences and separate out all the rows for given person and normalize these 3 columnns with single value for each person.

>>> import pandas as pd
>>> df = pd.DataFrame({'ID':range(9), 'name':['Theodore', 'Thomas', 'Theodore', 'Christian', 'Theodore', 'Theodore R', 'Thomas', 'Tomas', 'Cristian'], 'nick_name':['Tedy', 'Tom', 'Ted', 'Chris', 'Ted', 'Ted', 'Tommy', 'Tom', 'Chris'], 'initials':['TR', 'Tb', 'TRo', 'CS', 'TR', 'TR', 'tb', 'TB', 'CS']})
>>> df
   ID         name nick_name initials
0   0     Theodore      Tedy       TR
1   1       Thomas       Tom       Tb
2   2     Theodore       Ted      TRo
3   3    Christian     Chris       CS
4   4     Theodore       Ted       TR
5   5   Theodore R       Ted       TR
6   6       Thomas     Tommy       tb
7   7        Tomas       Tom       TB
8   8     Cristian     Chris       CS

In this case desired output is as follows:

   ID         name nick_name initials
0   0     Theodore       Ted       TR
1   1       Thomas       Tom       TB
2   2     Theodore       Ted       TR
3   3    Christian     Chris       CS
4   4     Theodore       Ted       TR
5   5     Theodore       Ted       TR
6   6       Thomas       Tom       TB
7   7       Thomas       Tom       TB
8   8    Christian     Chris       CS

The common value can be anything as long as it is normalized to same value. For example, name is Theodore or Theodore R - both fine. My actual DataFrame is about 4000 rows. Could someone help specify optimal algo to do this.

string-matching dataframe python-3.x search algorithm
1 Answers

You'll want to use Levenshtein distance to identify similar strings. A good Python package for this is fuzzywuzzy. Below I used a basic dictionary approach to collect similar rows together, then overwrite each chunk with a designated master row. Note this leaves a CSV with many duplicate rows, I don't know if this is what you want, but if not, easy enough to take the duplicates out.

import pandas as pd
from itertools import chain
from fuzzywuzzy import fuzz


def cluster_rows(df):
    row_clusters = {}
    threshold = 90
    name_rows = list(df.iterrows())

    for i, nr in name_rows:
        name = nr['name']
        new_cluster = True
        for other in row_clusters.keys():
            if fuzz.ratio(name, other) >= threshold:
                row_clusters[other].append(nr)
                new_cluster = False
            
        if new_cluster:
            row_clusters[name] = [nr]

    return row_clusters

def normalize_rows(row_clusters):
    for name in row_clusters:
        master = row_clusters[name][0]
        for row in row_clusters[name][1:]:
            for key in row.keys():
                row[key] = master[key]

    return row_clusters

if __name__ == '__main__':
    df = pd.read_csv('names.csv')
    rc = cluster_rows(df)
    normalized = normalize_rows(rc)
    pd.DataFrame(chain(*normalized.values())).to_csv('norm-names.csv')
user_151467720
Related