How do you remove similar (not duplicated) rows in pandas dataframe using text similarity?

user_4914597 0
Viewed 361

I have thousands of data that may or may not be similar to each other. Using python's default function drop_duplicates() doesn't really help since they only detect similar data only, for example, what if my data contains something like these:

  1. Hey, good morning!
  2. Hey, good morning.

Python wouldn't detect them as duplicates. There are many variations to this really, that simply cleaning the text wouldn't suffice, so I opt for text similarity.

I have tried the following code,

import textdistance
from tqdm import tqdm 
tqdm.pandas()

all_sims = []

for id1, text1 in tqdm(enumerate(df1['cleaned'])):
    for id2, text2 in enumerate(df1['cleaned'].iloc[id1:]):
        if id1==id2:
            continue
        sim = textdistance.jaro_winkler(text1, text2)
        if sim>=0.9:
#             print("similarity value: ",sim)
#             print("text 1 >> ",text1)
#             print("text 2 >> ",text2)
#             print("====><====")
            all_sims.append(id1)

Basically I tried to iterate all the rows in the column and check it with themselves. If the jaro-winkler value detected turns out to be >= 0.9 then the index will be saved to a list.

I will then remove all these similar indices with the following code.

df1[~df1.index.isin(all_sims)]

But my code is really slow and inefficient, and I am not sure if it's the right approach. Do you have any idea to improve this?

pandas dataframe text python
1 Answers

You could try this:

import pandas as pd
import textdistance

# Toy dataframe
df = pd.DataFrame(
    {
        "name": [
            "Mulligan Nick",
            "Hitt S C",
            "Breda Joy Mulligen",
            "David James Tsan",
            "Mulligan Nick",
            "Htti S C ",
            "Brenda Joy Mulligan",
            "Dave James Tsan",
        ],
    }
)

# Calculate similarities between rows
# and save corresponding indexes in a new column "match"
df["match"] = df["name"].map(
    lambda x: [
        i
        for i, text in enumerate(df["name"])
        if textdistance.jaro_winkler(x, text) >= 0.9
    ]
)

# Iterate to remove similar rows (keeping only the first one)
indices = []
for i, row in df.iterrows():
    indices.append(i)
    df = df.drop(
        index=[item for item in row["match"] if item not in indices], errors="ignore"
    )

# Clean up
df = df.drop(columns="match")

print(df)
# Outputs
                 name
0       Mulligan Nick
1            Hitt S C
2  Breda Joy Mulligen
3    David James Tsan
user_112460560
Related