Styjun

I have a Pandas DataFrame containing the results of entity sentiment using Google Cloud's Natural Language API (https://cloud.google.com/natural-language/docs/analyzing-entity-sentiment). I ran entity sentiment on a corpus of papers and want to compare how various entities are viewed by the various papers. However, the entity_name column contains duplicates due to subtle variations in how the organizations are referenced in the various papers. I would like to replace each entity name with one version so I can compare the sentiment analysis across papers.

Here's an example of what the DataFrame looks like:

entity_name paper_name score magnitude
Dept. of Commerce paper_1.pdf 0.67 0.13
Department of Commerce paper_2.pdf 0.42 0.21
US Department of Commerce paper_3.pdf 0.07 0.15

What I would like is to find, for example, all references to "Department of Commerce" so I can compare the sentiment scores for this entity across the various papers. This is not a large DataFrame (less than 100k rows), so the fastest/most optimal answer isn't a concern.

I've tried fuzzy matching the names using the fuzzywuzzy library in Python. I took the entity_name column and made two separate Numpy arrays using of the unique values

names_1 = df.entity_name.unique()
names_2 = df.entity_name.unique()

I then ran a fuzzy match for all pairs across the two columns. The issue is that I still have duplicates. This is an example of what the match DataFrame looks like

name_1 name_2 match_ratio
Dept. of Commerce Department of Commerce 82
Department of Commerce Dept. of Commerce 82
US Department of Commerce Department of Commerce 100
Department of Commerce US Department of Commerce 100
Dept. of Commerce US Department of Commerce 82
US Department of Commerce Dept. of Commerce 82

So in summary, what I'm looking for is a way to replace all variations of "Department of Commerce" with one version (it doesn't matter which one) so I can compare the sentiment scores across papers. I've found solutions that involve merging two DataFrames with fuzzy matching and others that involve fuzzy matching two separate columns in the same DataFrame, but so far haven't found a way to normalize a single String column in a DataFrame.