How to find and group similar terms in a dataframe in order to sum their values?

user_9368043 0
Viewed 150

I have data like this:

| Term     | Value|
| -------- | -----|
| Apple    | 100  |
| Appel    | 50   |
| Banana   | 200  |
| Banan    | 25   |
| Orange   | 140  |
| Pear     | 75   |
| Lapel    | 10   |

Currently, I am using the following code:

matches = []
for term in terms:
    tlist = difflib.get_close_matches(term, terms, cutoff = .80, n=5)
    matches.append(tlist)
      
df["terms"] = matches

The output is like this

| Term                  | Value|
| --------------------- | -----|
| [Apple, Appel]        | 100  |
| [Appel, Apple, Lapel] | 50   |
| [Banana, Banan]       | 200  |
| [Banan, Banana]       | 25   |
| [Orange]              | 140  |
| [Pear]                | 75   |
| [Lapel, Appel]        | 10   |

This code isn't really helpful. My desired output is something like:

| Term     | Value|
| -------- | -----|
| Apple    | 150  |
| Banana   | 225  |
| Orange   | 140  |
| Pear     | 75   |
| Lapel    | 10   |

The main issue is that the lists aren't in the same order, and often there is only one or two words of overlap in the lists. For example, I might have

  • [apple, appel]
  • [appel, apple, lapel]

Ideally, I would like to have both these return "apple", because that has the highest value of the overlapping terms.

Is there a way to do this?

difflib pandas tf-idf python
1 Answers

One simple way to achieve your goal is to use the Python standard library difflib module, which provides helpers for computing deltas, like this:

from difflib import SequenceMatcher

import pandas as pd

# Toy dataframe
df = pd.DataFrame(
    {
        "Term": ["Apple", "Appel", "Banana", "Banan", "Orange", "Pear", "Lapel"],
        "Value": [100, 50, 200, 25, 140, 75, 10],
    }
)

KEY_TERMS = ("Apple", "Banana", "Orange", "Pear")

for i, row in df.copy().iterrows():
    # Get the similarity ratio for a given value in df "Term" column (row[0])
    # and each term from KEY_TERM, and store the pair "term:ratio" in a dict
    similarities = {
        term: SequenceMatcher(None, row[0], term).ratio() for term in KEY_TERMS
    }
    # Find the key term for which the similarity ratio is maximum
    # and use it to replace the original term in the dataframe
    df.loc[i, "Term"] = max(similarities, key=lambda key: similarities[key])

# Group by term and sum values
df = df.groupby("Term").agg("sum").reset_index()

Then:

print(df)
# Outputs
     Term  Value
0   Apple    160
1  Banana    225
2  Orange    140
3    Pear     75
user_112460560
Related