Need help in matching strings from phrases from multiple columns of a dataframe in python

Question

Need help in matching phrases in the data given below where I need to match phrases from both TextA and TextB.

The following code did not helped me in doing it how can I address this I had 100s of them to match

#sorting jumbled phrases

def sorts(string_value):
    sorted_string = sorted(string_value.split())
    sorted_string = ' '.join(sorted_string)
    return sorted_string

#Removing punctuations in string

punc = '''!()-[]{};:'"\,<>./?@#$%^&*_~'''

def punt(test_str):
    for ele in test_str:
        if ele in punc:
            test_str = test_str.replace(ele, "")
    return(test_str)

#matching strings

def lets_match(x):

    for text1 in TextA:
        for text2 in TextB:
            try:
                if sorts(punt(x[text1.casefold()])) == sorts(punt(x[text2.casefold()])):
                    return True
            except:
                continue
    return False
df['result'] = df.apply(lets_match,axis =1)

even after implementing string sort, removing punctuations and case sensitivity I am still getting those strings as not matching. I am I missing something here can some help me in achieving it

if sorts(punt(x[text1.casefold()])) == sorts(punt(x[text2])): maybe you need to use text2.casefold too? — IoaTzimas
– IoaTzimas, Commented Aug 2, 2021 at 8:46
I used the same on both ends but missed typing here...but still facing issues — Sherlock
– Sherlock, Commented Aug 2, 2021 at 8:56
Should POLY NOVA INDUSTRIES LTD be equal to POLYNOVA INDUSTRIES LTD.? and what about CNOC LIMITED and CNOOC LIMITED? Even though they seem to be the same, they are not actually. So, it'd be better if you added your expected True/False values for the sample data you have in the question. — ThePyGuy
– ThePyGuy, Commented Aug 2, 2021 at 9:11
Then you can start with small length string, then compare the values in order, but that can not guarantee to always match such strings. — ThePyGuy
– ThePyGuy, Commented Aug 2, 2021 at 9:39

imxitiz · Accepted Answer · 2021-08-02 09:34:52Z

2

Actually you can use difflib to match two text, here's what you can try:

from difflib import SequenceMatcher

def similar(a, b):
    a=str(a).lower()
    b=str(b).lower()
    return SequenceMatcher(None, a, b).ratio()

def lets_match(d):
    print(d[0]," --- ",d[1])
    result=similar(d[0],d[1])
    print(result)
    if result>0.6:
        return True
    else:
        return False
    
df["result"]=df.apply(lets_match,axis =1)

You can play with if result>0.6 value.

For more information about difflib you can visit here. There are other sequence matchers also like textdistance but I found it easy so I tried this.

answered Aug 2, 2021 at 9:34

imxitiz

4,0253 gold badges14 silver badges36 bronze badges

Sign up to request clarification or add additional context in comments.

10 Comments

Sherlock Over a year ago

There are multiple columns I have to compare on both the ends like Text1, Text2, Text3 againest TextA, TextB, TextC all combinations in a row between the rows

imxitiz Over a year ago

Does this mean there are pattern in column name? like TextABCD..... and Text1234.....?

Sherlock Over a year ago

there are 3 and 4 columns on both ends with different names

imxitiz Over a year ago

I didn't understand, example? Actually understand but confused!

Sherlock Over a year ago

say I had Text1, Text2, Text3 and TextA, TextB, TextC between them I should run matching....if found match between any of these 2 batches it should be positive

|

Will_d0g · Accepted Answer · 2021-08-02 17:18:32Z

Is there any issues with using the fuzzy match lib? The implementation is pretty straight forward and works well given the above data is relatively similar. I've performed the below without preprocessing.

    import pandas as pd
    """ Install the libs below via terminal:

            $pip install fuzzywuzzy
            $pip install python-Levenshtein
    """

    from fuzzywuzzy import fuzz
    from fuzzywuzzy import process


    #creating the data frames
        text_a = ['AKIL KUMAR SINGH','OUSMANI DJIBO','PETER HRYB','CNOC LIMITED','POLY NOVA INDUSTRIES LTD','SAM GAWED JR','ADAN GENERAL LLC','CHINA MOBLE LIMITED','CASTAR CO., LTD.','MURAN','OLD SAROOP FOR CAR SEAT COVERS','CNP HEALTHCARE, LLC','GLORY PACK LTD','AUNCO VENTURES','INTERNATIONAL COMPANY','SAMEERA HEAT AND ENERGY FUND']
        text_b = ['Singh, Akil Kumar','DJIBO, Ousmani Illiassou','HRYB, Peter','CNOOC LIMITED','POLYNOVA INDUSTRIES LTD.','GAWED, SAM','ADAN GENERAL TRADING FZE','CHINA MOBILE LIMITED','CASTAR GROUP CO., LTD.','MURMAN','Old Saroop for Car Seat Covers','CNP HEATHCARE, LLC','GLORY PACK LTD.','AUNCO VENTURE','INTL COMPANY','SAMEERA HEAT AND ENERGY PROPERTY FUND']
        df_text_a = pd.DataFrame(text_a, columns=['text_a'])
        df_text_b = pd.DataFrame(text_b, columns=['text_b'])
        
        def lets_match(txt: str, chklist: list) -> str: 
            return process.extractOne(txt, chklist, scorer=fuzz.token_set_ratio)


    #match Text_A against Text_B
        result_txt_ab = df_text_a.apply(lambda x: lets_match(str(x), text_b), axis=1, result_type='expand')
        result_txt_ab.rename(columns={0:'Return Match', 1:'Match Value'}, inplace=True)
        df_text_a[result_txt_ab.columns]=result_txt_ab
        df_text_a

    text_a  Return Match    Match Value
    0   AKIL KUMAR SINGH    Singh, Akil Kumar   100
    1   OUSMANI DJIBO   DJIBO, Ousmani Illiassou    72
    2   PETER HRYB  HRYB, Peter 100
    3   CNOC LIMITED    CNOOC LIMITED   70
    4   POLY NOVA INDUSTRIES LTD    POLYNOVA INDUSTRIES LTD.    76
    5   SAM GAWED JR    GAWED, SAM  100
    6   ADAN GENERAL LLC    ADAN GENERAL TRADING FZE    67
    7   CHINA MOBLE LIMITED CHINA MOBILE LIMITED    79
    8   CASTAR CO., LTD.    CASTAR GROUP CO., LTD.  81
    9   MURAN   SAMEERA HEAT AND ENERGY PROPERTY FUND   41
    10  OLD SAROOP FOR CAR SEAT COVERS  Old Saroop for Car Seat Covers  100
    11  CNP HEALTHCARE, LLC CNP HEATHCARE, LLC  58
    12  GLORY PACK LTD  GLORY PACK LTD. 100
    13  AUNCO VENTURES  AUNCO VENTURE   56
    14  INTERNATIONAL COMPANY   INTL COMPANY    74
    15  SAMEERA HEAT AND ENERGY FUND    SAMEERA HEAT AND ENERGY PROPERTY FUND   86

    #match Text_B against Text_A
    result_txt_ba= df_text_b.apply(lambda x: lets_match(str(x), text_a), axis=1, result_type='expand')
    result_txt_ba.rename(columns={0:'Return Match', 1:'Match Value'}, inplace=True)
    df_text_b[result_txt_ba.columns]=result_txt_ba
    df_text_b

text_b  Return Match    Match Value
0   Singh, Akil Kumar   AKIL KUMAR SINGH    100
1   DJIBO, Ousmani Illiassou    OUSMANI DJIBO   100
2   HRYB, Peter PETER HRYB  100
3   CNOOC LIMITED   CNOC LIMITED    74
4   POLYNOVA INDUSTRIES LTD.    POLY NOVA INDUSTRIES LTD    74
5   GAWED, SAM  SAM GAWED JR    86
6   ADAN GENERAL TRADING FZE    ADAN GENERAL LLC    86
7   CHINA MOBILE LIMITED    CHINA MOBLE LIMITED 81
8   CASTAR GROUP CO., LTD.  CASTAR CO., LTD.    100
9   MURMAN  ADAN GENERAL LLC    33
10  Old Saroop for Car Seat Covers  OLD SAROOP FOR CAR SEAT COVERS  100
11  CNP HEATHCARE, LLC  CNP HEALTHCARE, LLC 56
12  GLORY PACK LTD. GLORY PACK LTD  100
13  AUNCO VENTURE   AUNCO VENTURES  53
14  INTL COMPANY    INTERNATIONAL COMPANY   50
15  SAMEERA HEAT AND ENERGY PROPERTY FUND   SAMEERA HEAT AND ENERGY FUND    100

Dharman · Accepted Answer · 2021-08-02 09:41:14Z

I think you can't do it without a strings distance notion, what you can do is use, for example record linkage.

I will not get into details, but i'll show you an example of usage on this case.

import pandas as pd 
import recordlinkage as rl 
from recordlinkage.preprocessing import clean

# creating first dataframe
df_text_a = pd.DataFrame({
    "Text A":[
        "AKIL KUMAR SINGH",
        "OUSMANI DJIBO",
        "PETER HRYB",
        "CNOC LIMITED",
        "POLY NOVA INDUSTRIES LTD",
        "SAM GAWED JR",
        "ADAN GENERAL LLC",
        "CHINA MOBLE LIMITED",
        "CASTAR CO., LTD.",
        "MURAN",
        "OLD SAROOP FOR CAR SEAT COVERS",
        "CNP HEALTHCARE, LLC",
        "GLORY PACK LTD",
        "AUNCO VENTURES",
        "INTERNATIONAL COMPANY",
        "SAMEERA HEAT AND ENERGY FUND"]
                }
            )

# creating second dataframe
df_text_b = pd.DataFrame({
        "Text B":[
            "Singh, Akil Kumar",
            "DJIBO, Ousmani Illiassou",
            "HRYB, Peter",
            "CNOOC LIMITED",
            "POLYNOVA INDUSTRIES LTD. ",
            "GAWED, SAM",
            "ADAN GENERAL TRADING FZE",
            "CHINA MOBILE LIMITED",
            "CASTAR GROUP CO., LTD.",
            "MURMAN    ",
            "Old Saroop for Car Seat Covers",
            "CNP HEATHCARE, LLC",
            "GLORY PACK LTD.",
            "AUNCO VENTURE",
            "INTL COMPANY",
            "SAMEERA HEAT AND ENERGY PROPERTY FUND"
            ]
        }
)

# preprocessing in very important on results, you have to find which fit well on yuor problem.
cleaned_a = pd.DataFrame(clean(df_text_a["Text A"], lowercase=True))
cleaned_b = pd.DataFrame(clean(df_text_b["Text B"], lowercase=True))

# creating an indexing which will be used for comprison, you have various type of indexing, watch documentation.

indexer = rl.Index()
indexer.full()
# generating all passible pairs
pairs = indexer.index(cleaned_a, cleaned_b)

# starting evaluation phase
compare = rl.Compare(n_jobs=-1)  
compare.string("Text A", "Text B", method='jarowinkler', label = 'text')
matches = compare.compute(pairs, cleaned_a, cleaned_b)

matches is now a MultiIndex DataFrame, what you want to do next is to find all max on the second index by first index. So you will have the results you need.

Results can be improved working on distance, indexing and/or preprocessing.

Collectives™ on Stack Overflow

Need help in matching strings from phrases from multiple columns of a dataframe in python

3 Answers 3

10 Comments

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

10 Comments

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related