CODEWITHSUNDEEP
pythonpython-3.xpandas
Ankit
Ankit

Reputation: 203

Identify IDs with similar address

I have a data in a csv file which basically has some IDs, their corresponding address and the matching similarity percentage of 1 address with other. I want to identify the IDs which have got similar address alongwith their match percentage

I have done the text matching and found the similarity percentage between the address strings comparing 1 address to every other address. 

import pandas as pd
from fuzzywuzzy import process, fuzz

pd.set_option('display.width', 1000)
pd.set_option('display.max_columns', 10)

data = pd.read_csv(r"address_details.csv", skiprows=0)
id = data['COD_CUST_ID'].values.tolist()
address = data['ADDRESS'].values.tolist()

dict_list=[]

for i in range(0,len(id)):
    for add in range(0,len(address)):
        score=process.extractBests(address[add], address, limit=len(address), score_cutoff=40)
        #print(type(score))

        for sc in score:
            #print(sc)
            for scr in sc:
                print(scr)

            dict_={}
            dict_.update({"Cust_Id": id[i]})
            dict_.update({"Match Ratio": sc})
            dict_.update({"Search String": address[add]})
        #dict_.update({"Address List": address})

            dict_list.append(dict_)

df=pd.DataFrame(dict_list)


matches = df['Match Ratio'].tolist()
matches = [x[0][0] for x in matches]

found  = []
for s in df['Search String']:
    data_list=[]

    if s in matches:
        index=[i for i, x in enumerate(matches) if x == s]
        Cust_Id = list([df['Cust_Id'][i]] for i in index)
        data_list.append(s)
        data_list.append(Cust_Id)
        found.append(data_list)
print(found)

sd=df.to_csv("match_score.csv",sep=',',index=None)

Suppose i have this dataframe as my code output

Cust_Id Match Ratio Search String
1   [('ABC', 100)]  ABC
2   [('DEF', 100)]  DEF
3   [('DEF', 100)]  XYZ
4   [('ABC', 100)]  PQR
5   [('PQR', 100)]  TUV
6   [('DEF', 100)]  LMN

I want to get a list of the IDS having similar data under Match Ratio column

Upvotes: 0

Views: 198

Answers (1)

0xPrateek
0xPrateek

Reputation: 1188

I have wrote a code which gives a list containg the "Search string" and it's corresponding matching 'Cust_Id'.

The Code is,

 import pandas as pd

def duplicates(lst, item):
   return [i for i, x in enumerate(lst) if x == item]

# Creating Data frame
data = {'Cust_Id' : ['1 ','2' , '3','4','5','6'],
        'Match Ratio'  : [[('ABC', 100)],[('DEF', 100)],[('DEF', 100)], [('ABC', 100)],[('PQR', 100)],[('DEF', 100)]],
        'Search' : ['ABC','DEF','XYZ','PQR','TUV','LMN']
        }
df = pd.DataFrame(data)

print(df)
# Creating a list of 1'st value of tuple Match Ratio
matches = df['Match Ratio'].tolist()
matches = [x[0][0] for x in matches]

found  = []
for s in df['Search']:
    data_list = []
    if s in matches:
        index = duplicates(matches,s)
        Cust_Id = list([df['Cust_Id'][i]] for i in index)
        data_list.append(s)
        data_list.append(Cust_Id)
        found.append(data_list)
print(found)

Dataframe output

  Cust_Id   Match Ratio Search
0      1   [(ABC, 100)]    ABC
1       2  [(DEF, 100)]    DEF
2       3  [(DEF, 100)]    XYZ
3       4  [(ABC, 100)]    PQR
4       5  [(PQR, 100)]    TUV
5       6  [(DEF, 100)]    LMN

Found List output

[['ABC', [['1 '], ['4']]], ['DEF', [['2'], ['3'], ['6']]], ['PQR', [['5']]]]

Hope you got what you were looking for :)

Upvotes: 1

Related Questions