在几秒钟内将数千个类似的电子表格文本单元分组

+-----+-------------------+
| row |     fullname      |
+-----+-------------------+
|   1 | John F. Doe       |
|   2 | Esquivel, Mara    |
|   3 | Doe, John F       |
|   4 | Whyte, Luke       |
|   5 | Doe, John Francis |
+-----+-------------------+

+-----+-------------------+---------------+
| row |     fullname      |  name_groups  |
+-----+-------------------+---------------+
|   1 | John F. Doe       | Doe John F    |
|   2 | Esquivel, Mara    | Esquivel Mara |
|   3 | Doe, John F       | Doe John F    |
|   4 | Whyte, Luke       | Whyte Luke    |
|   5 | Doe, John Francis | Doe John F    |
+-----+-------------------+---------------+

+-----+----------------------+
| row |      legal_name      |
+-----+----------------------+
|   1 | Wal-mart Inc         |
|   2 | Walmart Stores Inc.  |
|   3 | Wal-mart stores Inc  |
|   4 | Wal-Mart stores Inc. |
+-----+----------------------+

+---------+-------+
|  term   | count |
+---------+-------+
| rhode   |     1 |
| island  |     2 |
| is      |     2 |
| neither |     1 |
| a       |     1 |
| road    |     1 |
| nor     |     1 |
| it      |     1 |
| an      |     1 |
| discuss |     1 |
+---------+-------+

import re
import pandas as pd
from sklearn.feature_extraction.text import TfidfVectorizer
 
# Import your data to a Pandas.DataFrame
df = pd.read_csv('./dol-data.csv')
 
# Grab the column you'd like to group, filter out duplicate values
# and make sure the values are Unicode
vals = df['legal_name'].unique().astype('U')
 
 
# Write a function for cleaning strings and returning an array of ngrams
def ngrams_analyzer(string):
    string = re.sub(r'[,-./]', r'', string)
    ngrams = zip(*[string[i:] for i in range(5)])  # N-Gram length is 5
    return [''.join(ngram) for ngram in ngrams]
 
# Construct your vectorizer for building the TF-IDF matrix
vectorizer = TfidfVectorizer(analyzer=ngrams_analyzer)
 
# Build the matrix!!!
tfidf_matrix = vectorizer.fit_transform(vals)

# Import IGN's awesome_cossim_topn module
from sparse_dot_topn import awesome_cossim_topn
 
# The arguments for awesome_cossim_topn are as follows:
### 1. Our TF-IDF matrix
### 2. Our TF-IDF matrix transposed (allowing us to build a pairwise cosine matrix)
### 3. A top_n filter, which allows us to filter the number of matches returned, which isn't useful for our purposes
### 4. This is our similarity threshold. Only values over 0.8 will be returned
cosine_matrix = awesome_cossim_topn(
  tf_idf_matrix,
  tf_idf_matrix.transpose(),
  vals.size,
  0.8
)

+ ------------ +
| 0,0,0,4 |
| 0,1,0,0 |
| 0,0,0,0 |
| 3,0,0,7 |
+ ------------ +

# Build a coordinate matrix from a cosine matrix
coo_matrix = cosine_matrix.tocoo()
 
# Instaniate our lookup hash table
group_lookup = {}
 
 
def find_group(row, col):
    # If either the row or the col string have already been given
    # a group, return that group. Otherwise return none
    if row in group_lookup:
        return group_lookup[row]
    elif col in group_lookup:
        return group_lookup[col]
    else:
        return None
 
 
def add_vals_to_lookup(group, row, col):
    # Once we know the group name, set it as the value
    # for both strings in the group_lookup
    group_lookup[row] = group
    group_lookup[col] = group
 
 
def add_pair_to_lookup(row, col):
    # in this function we'll add both the row and the col to the lookup
    group = find_group(row, col)  # first, see if one has already been added
    if group is not None:
        # if we already know the group, make sure both row and col are in lookup
        add_vals_to_lookup(group, row, col)
    else:
        # if we get here, we need to add a new group.
        # The name is arbitrary, so just make it the row
        add_vals_to_lookup(row, row, col)
 
# for each row and column in coo_matrix
# if they're not the same string add them to the group lookup
for row, col in zip(coo_matrix.row, coo_matrix.col):
    if row != col:
        # Note that what is passed to add_pair_to_lookup is the string at each index
        # (eg: the names in the legal_name column) not the indices themselves
        add_pair_to_lookup(vals[row], vals[col])

  (row, col) | data  
 ------------|------
  (0,0)      |    1
  (0,2)      | 0.84
  (1,1)      |    1
  (2,0)      | 0.84
  (2,2)      |    1

>>> group_lookup
{
    'Walmart': 'Walmart',
    'Wal-mart stores': 'Walmart'
}

df['Group'] = df['legal_name'].map(group_lookup).fillna(df['legal_name'])
 
df.to_csv('./dol-data-grouped.csv')

import re
import pandas as pd
from sklearn.feature_extraction.text import TfidfVectorizer
from sparse_dot_topn import awesome_cossim_topn
 
# Import your data to a Pandas.DataFrame
df = pd.read_csv('./dol-data.csv')
 
# Instaniate our lookup hash table
group_lookup = {}
 
 
# Write a function for cleaning strings and returning an array of ngrams
def ngrams_analyzer(string):
    string = re.sub(r'[,-./]', r'', string)
    ngrams = zip(*[string[i:] for i in range(5)])  # N-Gram length is 5
    return [''.join(ngram) for ngram in ngrams]
 
 
def find_group(row, col):
    # If either the row or the col string have already been given
    # a group, return that group. Otherwise return none
    if row in group_lookup:
        return group_lookup[row]
    elif col in group_lookup:
        return group_lookup[col]
    else:
        return None
 
 
def add_vals_to_lookup(group, row, col):
    # Once we know the group name, set it as the value
    # for both strings in the group_lookup
    group_lookup[row] = group
    group_lookup[col] = group
 
 
def add_pair_to_lookup(row, col):
    # in this function we'll add both the row and the col to the lookup
    group = find_group(row, col)  # first, see if one has already been added
    if group is not None:
        # if we already know the group, make sure both row and col are in lookup
        add_vals_to_lookup(group, row, col)
    else:
        # if we get here, we need to add a new group.
        # The name is arbitrary, so just make it the row
        add_vals_to_lookup(row, row, col)
 
 
# Construct your vectorizer for building the TF-IDF matrix
vectorizer = TfidfVectorizer(analyzer=ngrams_analyzer)
 
# Grab the column you'd like to group, filter out duplicate values
# and make sure the values are Unicode
vals = df['legal_name'].unique().astype('U')
 
# Build the matrix!!!
tfidf_matrix = vectorizer.fit_transform(vals)
 
cosine_matrix = awesome_cossim_topn(tf_idf_matrix, tf_idf_matrix.transpose(), vals.size, 0.8)
 
# Build a coordinate matrix
coo_matrix = cosine_matrix.tocoo()
 
# for each row and column in coo_matrix
# if they're not the same string add them to the group lookup
for row, col in zip(coo_matrix.row, coo_matrix.col):
    if row != col:
        add_pair_to_lookup(vals[row], vals[col])
 
df['Group'] = df['legal_name'].map(group_lookup).fillna(df['legal_name'])
 
df.to_csv('./dol-data-grouped.csv')

columns_to_group = ['legal_name', 'address']
df['grouper'] = df[
   columns_to_group.pop(0)
].astype(str).str.cat(
   df[columns_to_group].astype(str)
)

vals = df['grouper'].unique().astype('U')

df.drop(columns=['grouper']).to_csv('./dol-data-grouped.csv')