CODEWITHSUNDEEP
pythonpandasloopsdataframesequencematcher
Shawn Atlas
Shawn Atlas

Reputation: 25

How to iterate through 2 columns and match one by one

Lets say i have 2 excel files each containing a column of names and dates

Excel 1:

Name
0      Bla bla bla June 04 2018 
1      Puppy Dog June 01 2017
2      Donald Duck February 24 2017
3      Bruno Venus April 24 2019

Excel 2:

                             Name
0        Pluto Feb 09 2019
1        Donald Glover Feb 22 2020
2        Dog Feb 22 2020
3        Bla Bla Feb 22 2020

I want to match each cell from column 1 to each cell in column 2 and then locate the biggest similarity.

The following function will give a percentage value of how much two input match each other.

SequenceMatcher code example:

from difflib import SequenceMatcher

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


x = "Adam Clausen a Feb 09 2019"
y = "Adam Clausen Feb 08 2019"
print(similar(x,y))

Output:0.92

Upvotes: 0

Views: 1518

Answers (2)

Shawn Atlas
Shawn Atlas

Reputation: 25

UPDATED/SOLVED PART

The following code does:

  • It takes the 2 input files and turn them into dataframes
  • It will then take a specific Column ( in this case they are both called Name) and use it as its match input
  • It takes one name from File 1 and runs through all the names in File 2
  • It then takes the name with the highest match and saves their respective row and save them next to each other in the Output File

Code:

import pandas as pd
import numpy as np
from difflib import SequenceMatcher

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

#Load Batchlog to Data frame

data1 = pd.read_excel (r'File1.xlsx')
data2 = pd.read_excel (r'File2.xlsx')

df1 = pd.DataFrame(data1)
df2 = pd.DataFrame(data2)

df1['Name'] = df1['Name'].astype(str)
df2['Name'] = df2['Name'].astype(str)

#Function/LOOP
order = []
for index, row in df1.iterrows():
    maxima = [similar(row['Name'], j) for j in df2['Name']]

#best_Ratio=Best Match
    best_ratio = max(maxima)
    best_row = np.argmax(maxima)

#Rearrange new order and save in Output File
    order.append(best_row)

df2 = df2.iloc[order].reset_index()

pd.concat([df1, df2], axis=1)

dfFinal=pd.concat([df1, df2], axis=1)

dfFinal.to_excel("OUTPUT.xlsx")  
#Thank you for the help!

Upvotes: 0

pritam samanta
pritam samanta

Reputation: 445

If u know how to load colums as dataframe..this code should get your job done..

from difflib import SequenceMatcher

col_1 = ['potato','tomato', 'apple']
col_2 = ['tomatoe','potatao','appel']

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

for i in col_1:
    print(max(similar(i,j) for j in col_2))

Upvotes: 1

Related Questions