Python - use a list of names to find exact match in pandas column containing emails

Question

I have an Excel file containing a free-form text column (that sometimes is structured like an email), where I need to find all first and last names and add an extra columns saying TRUE/FALSE to these fields. I do not need to extract matched data (i.e. note it down in an adjacent column), although that could be an advantage.

NB: I do not know the names that I need to find, so it is pure guesswork. I have a list of registered first names with 40k+ entries, as well as a list of most common last names with another 16k+ entries.

So far, I managed to filter out roughly 10000 rows out out ~20000 row file, although my solution contains a lot of false positives. e.g. some rows marked TRUE for first names, contain text like "Det er OK.", where Python (I assume) merges the entire text together and extracts any matching substing to a name from a list, in this case I guess that could be "t er O" or "r OK", since my list has names "Tero" and "Rok" (although the case does not match and it combines letters from 2/3 separate words, which is not what I want)... Weirdly enough, this is NOT TRUE for the same text written in lowercase and without "." at the end, i.e. "det er ok", which is marked as FALSE! P.S. there are unfortunatelly few names in the emails that are written in lowercase letters and not sentence case as it should be...

Sample email (with names Thomas, Lars, Ole, Per):

Hej Thomas,

De 24 timer var en af mange sager som vi havde med til møde med Lars og Ole. De har godkendt den under dette møde.

Mvh. Per

My code:

# Import datasets and create lists/variables
import pandas as pd
from pandas import ExcelWriter

namesdf = pd.read_excel('names.xlsx', sheet_name='Alle Navne')
names = list(namesdf['Names'])

lastnamesdf = pd.read_excel('names.xlsx', sheet_name='Frie Efternavne')
lastnames = list(lastnamesdf['Frie Efternavne'])


# Import dataset and drop NULLS
df = pd.read_excel(r'Entreprise Beskeder.xlsx', sheet_name='dataark')
df["Besked"].dropna(inplace = True)


# Compare dataset to the created lists to match first and last names
df["Navner"] = df["Besked"].str.contains("|".join(names)) # Creates new column and adds TRUE/FALSE for first names
df["Efternavner"] = df["Besked"].str.contains("|".join(lastnames)) # Creates new column and adds TRUE/FALSE for last names


# Save the result
writer = ExcelWriter('PythonExport.xlsx')
df.to_excel(writer)
writer.save()

I would appreciate any suggestions that could potentially improve my code and reduce manual work that it will take to filter out all of these false positive cells that I found! I think the best case scenario would be a case sensitive piece of code that finds only the specific name without merging the text together. Also, it would be great if I could extract a specific string that Python finds a match in, as that would reduce manual work when trying to figure out why exactly a specific block of text was marked as TRUE. All in all, every suggestion is welcome! Thanks :)

Answer 1

It sounds like the thing you're trying to do is somewhat insane. With 40k first names to search for, false positives are inevitable. At the same time, with only 40k names, false negatives are also inevitable. People's names are untidy; hopefully you have plans to accommodate. Even when you get correct matches for a "first" and "last" name, as your example email shows, there's no guarantee that they'll be the first and last names of the same person.

Maybe someone with experience in natural-language-processing AI would be able to solve your problem in a robust way. More likely you've resigned yourself to a solution that simply isn't robust. You still pretty definitely need case-sensitivity and "whole word" matching.

I'm not convinced by the example you give of a false positive. The pandas function you're using is regex-based. r'tero' does not match 't er o'; it does match 'interoperability'. With name lists as long as you're using, it seems more likely that you over-looked some other match in the email in question. I would kinda expect just a few of the names to be responsible for the majority of false-positives; outputting the matched text will help you identify them.

• Case-sensitive regex matching should be the default.
• I think \b...\b as a regex pattern will give the kind of "whole word" matching you need.
• pandas.extract will do the capturing.

Given the size of your datasets, you may be a bit concerned with the performance. Or you may not, it's up to you.

I haven't tested this at all:

# Import datasets and create lists/variables
import pandas as pd
from pandas import ExcelWriter
from typing import Iterable

# Document, sheet, and column names:
names_source_file = 'names.xlsx'
first_names_sheet = 'Alle Navne'
first_names_column = 'Names'
last_names_sheet = 'Frie Efternavne'
last_names_column = 'Frie Efternavne'
subject_file = 'Entreprise Beskeder.xlsx'
subject_sheet = 'dataark'
subject_column = 'Besked'
output_first_name = 'Navner'
output_last_name = 'Efternavner'
output_file = 'PythonExport.xlsx'

# Build (very large!) search patterns:
first_names_df = pd.read_excel(names_file, sheet_name=first_names_sheet)
first_names: Iterable[str] = namesdf[first_names_column]
first_names_regex = '''\b{}\b'''.format('|'.join(first_names))
last_names_df = pd.read_excel(names_file, sheet_name=last_names_sheet)
last_names: Iterable[str] = lastnamesdf[last_names_column]
last_names_regex = '''\b{}\b'''.format('|'.join(last_names))

# Import dataset and drop NULLS:
data_frame = pd.read_excel(subject_file, sheet_name=subject_sheet)
data_frame[subject_column].dropna(inplace=True)

# Add columns for found first and last names:
data_frame[output_first_name] = data_frame[subject_column].str.extract(
    first_names_regex,
    expand=False
)
data_frame[output_last_name] = data_frame[subject_column].str.extract(
    last_names_regex,
    expand=False
)

# Save the result
writer = ExcelWriter(output_file)
df.to_excel(writer)
writer.save()

One obvious problem that I still haven't talked about is that there may be multiple name matches in a given subject. Assuming that you care about multiple matches, you can probably do something with extractall.

Answer 2

To see what is being matched, use apply() with a python function:

import re

regex = re.compile(pat)

def search(item):
    mo = regex.search(item)
    if mo:
        return mo[0]
    else:
        return ''

df.msg.apply(search)

This will yield a Series with the names that matched or '' if there isn't a match.