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