CODEWITHSUNDEEP
pythonpython-3.xpandas
Ankit
Ankit

Reputation: 203

Get ids with similar address in a new column

I have a dataframe from which i process some columns to get the matching percentage of address of each customer id with address of every other customer id. If some addresses match the other addresses with percentage above 80 then I want to gather their corresponding customer ids in a new column

I have made the code in which i get the list of tuples having the address and their corresponding scores in each tuple.

import pandas as pd
from fuzzywuzzy import process


def pat_match(id,address):

    length01=len(id)    # normal integer sequence 1 to 10


    for y in range(0,length01):
        score=process.extractBests(address[y],address,score_cutoff=80)
        print(score)                    # actual results(list of tuples)
        d2=[sc[1] for sc in score]
        #print(d2)                       # variable having list of scores per address tuple



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

Suppose I have an input data as 

Customer_ID Address
21213944    VPO. SAHWA   CHURU RAJASTHAN 331302
21991538    WARD NO.-3 NATT ROAD TALWANDI SABO BATHINDA  BATHINDA PUNJAB 151302
21991539    H.NO.-137 RAMA ROAD TALWANDI SABO BATHINDA  BATHINDA PUNJAB 151302
21603327    VAGPUR KARCHCHA KALAN   UDAIPUR RAJASTHAN 313803
21215934    VILLAGE GORIYAN TEHSIL UDAIPURWATI DIST JHUNJHUNU  JHUJHUNU RAJASTHAN 333307

And the intermediate output of the variable SCORE is

[('WARD NO 25 GHADSISAR ROAD BASANT KUNJ KE SAMNE HANUMAN MANDIR KE PASS CHOUDHARY COLONY GANGASHAR BIKANER RAJASTHAN 334001', 100), ('VPO. SAHWA   CHURU RAJASTHAN 331302', 86), ('WARD NO.-3 NATT ROAD TALWANDI SABO BATHINDA  BATHINDA PUNJAB 151302', 86), ('H.NO.-137 RAMA ROAD TALWANDI SABO BATHINDA  BATHINDA PUNJAB 151302', 86), ('Karchha Kalan   UDAIPUR RAJASTHAN 313803', 86)]
[('Karchha Kalan   UDAIPUR RAJASTHAN 313803', 100), ('VAGPUR KARCHCHA KALAN   UDAIPUR RAJASTHAN 313803', 91), ('WARD NO 25 GHADSISAR ROAD BASANT KUNJ KE SAMNE HANUMAN MANDIR KE PASS CHOUDHARY COLONY GANGASHAR BIKANER RAJASTHAN 334001', 86), ('VILLAGE GORIYAN TEHSIL UDAIPURWATI DIST JHUNJHUNU  JHUJHUNU RAJASTHAN 333307', 86)]
[('VAGPUR KARCHCHA KALAN   UDAIPUR RAJASTHAN 313803', 100), ('Karchha Kalan   UDAIPUR RAJASTHAN 313803', 91), ('WARD NO 25 GHADSISAR ROAD BASANT KUNJ KE SAMNE HANUMAN MANDIR KE PASS CHOUDHARY COLONY GANGASHAR BIKANER RAJASTHAN 334001', 86), ('VILLAGE GORIYAN TEHSIL UDAIPURWATI DIST JHUNJHUNU  JHUJHUNU RAJASTHAN 333307', 86)]
[('VILLAGE GORIYAN TEHSIL UDAIPURWATI DIST JHUNJHUNU  JHUJHUNU RAJASTHAN 333307', 100), ('VPO. SAHWA   CHURU RAJASTHAN 331302', 86), ('WARD NO 25 GHADSISAR ROAD BASANT KUNJ KE SAMNE HANUMAN MANDIR KE PASS CHOUDHARY COLONY GANGASHAR BIKANER RAJASTHAN 334001', 86), ('Karchha Kalan   UDAIPUR RAJASTHAN 313803', 86), ('VAGPUR KARCHCHA KALAN   UDAIPUR RAJASTHAN 313803', 86)]

The final output I want to be is like

Search String   Match Customer Ids
WARD NO.-3 NATT ROAD TALWANDI SABO BATHINDA  BATHINDA PUNJAB 151302 21991538,21991539
VAGPUR KARCHCHA KALAN   UDAIPUR RAJASTHAN 313803    21603327,21215934

Upvotes: 2

Views: 207

Answers (1)

0xPrateek
0xPrateek

Reputation: 1188

As per your problem, this solution will work, Code is self-explanatory :) 

# Getting the DataFrame as the parameter
def pat_match(df):

    # Getting the column values of id and address in seprate list
    id = df['COD_CUST_ID'].values.tolist()
    address = df['ADDRESS'].values.tolist()

    # Creating a new column with name 'Ids'
    df['Ids'] = ""
    length01=len(id)   

    for y in range(0,length01):

        # The mathched address Id will will be appended in a list for every address
        matched_ids = []

        # Calculating list of address with match percentage more than 80%
        score=process.extractBests(address[y],address,score_cutoff=80)

        # Iterating over every address returned by score one by one
        for matched_address in score:

            # Getting Customer_ID of every Address
            get = df['Customer_ID'][df['Address']==matched_address].tolist()[0]

            # Appending the Id into a list
            matched_ids.append(get)

        # Finally Appending the list of matched ID to the column 
        df['Ids'][df['Customer_ID']==id[y]] = str(matched_ids)

main function :

  if __name__ == '__main__':
    data = pd.read_csv(r"address_details.csv", skiprows=0)
    pat_match(data)

Upvotes: 1

Related Questions