Htykuut

Background

I've two lists (of lists), each created by reading data from two address tables.

The first element in each row is the unique identifier of the list row and the remaining elements are used for address comparison.

Each list would look somewhat like this:

List 1 (cli add)

['3', 'V8T5G2', 'VICTORIA', 'BR', 'CANADA']

['543', '234', '654', 'BELMONT', 'AVENUE', 'V8S3T4', 'VICTORIA', 'BR', 'CANADA']    

['28', '70', 'RUSHTON', 'RD', 'M6C2X8', 'YK', 'ON', 'CANADA']

List 2 (struct add)

['7H0044', '234', '654', 'BELMONT', 'AVENUE', 'V8S3T4', 'VICTORIA', 'BC', 'CANADA']

['7H0033', 'V8T5G2', 'VICTORIA', 'BC', 'CANADA']

['7H0001', '700', 'RUSHTON', 'ROAD', 'M6C2X7', 'YORK', 'ON', 'CANADA']

['7H0034', '217', 'BONNYMUIR', 'DRIVE', 'V7S1L4', 'WEST', 'VANCOUVER', 'BC', 'CANADA']

Task Goal
My task is to compare the addresses from the test list to the other list, and flag all the matching and non matching records.

I am looping through each row in list 1 and comparing with each row of list 2, element wise. If all the elements pulled from list 1 row are found in any row from list 2, I mark that record as 'matching' and retain the row from list 2. Have been able to identify the completely matching records.

Problem Point
The real challenge is about the non matching rows. For the non matching records from list 1, I would want to identify the most closely matching row from list 2. e.g. if row from list 1 has matching elements in three rows from list 2, I would want to pick up the list 2 row which has the highest number of matching elements.

Expected Outcome
In the data shared above, from the list 1, second row (id 542) has complete match. But the other two records aren't yielding a complete match.

I want to be able to create a list of un-matching records and capture umatching elements:

[[comparison record from list 1],[Most matching record from list 2],[non-matching elements from list 1]]

So for above shared data, I need a new list (of lists) which looks something like:

[['3', 'V8T5G2', 'VICTORIA', 'BR', 'CANADA'],['7H0033', 'V8T5G2', 'VICTORIA', 'BC', 'CANADA'],['BR']]

[['28', '70', 'RUSHTON', 'RD', 'M6C2X8', 'YK', 'ON', 'CANADA'],['7H0001', '700', 'RUSHTON', 'ROAD', 'M6C2X7', 'YORK', 'ON', 'CANADA'],['70','RD','M6C2X8', 'YK']]

The code below gets me the results partially. I am not being able to find a way to filter the highest matching rows.

Code Snippet
Here is how I found the matching and non-matching records (list 1 is referred by cli_add_fnl and list 2 is struc_add_fnl). Have also figured the way to list the unmatched elements and count of matching elements. Just need a way to pull only the rows with max count for list element 1.

### Step 4 - Identifying the matching and non matching addresses ###  

validated_addresses_all = 

invalid_addresses_all = 



for cli_add in cli_add_fnl:            

    comparison_cli_add=cli_add.copy()



    #removing the id column from comparison

    comparison_cli_add.pop(0)





    for struct_add in struct_add_fnl:

        matching_elements = [address_element for address_element in comparison_cli_add if address_element in struct_add]



        #capture the matching records

        if matching_elements == comparison_cli_add:

            validated_addresses_all.append(cli_add)

        else:

            invalid_addresses_all.append(cli_add)

            invalid_addresses_all.append(struct_add)

            invalid_addresses_all.append(len(set(comparison_cli_add) & set(struct_add)))

            invalid_addresses_all.append(nonmatching_elements)



#remove the duplicate entries

fnl_validated_addresses =  

for add in validated_addresses_all: 

    if add not in fnl_validated_addresses: 

        fnl_validated_addresses.append(add)

This is one way to do it with ignoring position and the first item by comparing the values that are in adds and struct_adds and internally keeping a counter of the highest matches. As long there is a match it will update the counter and gets the index of the highest match else in the example below, it does nothing. Differences from item in add and the highest matches are then compared.

The results are then appended accordingly to a list.

adds = [['3', 'V8T5G2', 'VICTORIA', 'BR', 'CANADA'],

        ['543', '234', '654', 'BELMONT', 'AVENUE', 'V8S3T4', 'VICTORIA', 'BR', 'CANADA'],

        ['28', '70', 'RUSHTON', 'RD', 'M6C2X8', 'YK', 'ON', 'CANADA']]



struct_adds = [ ['7H0044', '234', '654', 'BELMONT', 'AVENUE', 'V8S3T4', 'VICTORIA', 'BC', 'CANADA'],

                ['7H0033', 'V8T5G2', 'VICTORIA', 'BC', 'CANADA'],

                ['7H0001', '700', 'RUSHTON', 'ROAD', 'M6C2X7', 'YORK', 'ON', 'CANADA'],

                ['7H0034', '217', 'BONNYMUIR', 'DRIVE', 'V7S1L4', 'WEST', 'VANCOUVER', 'BC', 'CANADA']]



results = 



for add in adds:

    match_count = 0

    match_index = 0

    for idx,struct_add in enumerate(struct_adds):

        matches = [add_item in struct_add[1:] for add_item in add[1:]]

        if matches.count(True) > match_count:

            match_count = matches.count(True)

            match_index = idx

    if match_count == 0:

        pass # no matches

    else:

        highest_match = struct_adds[match_index]

        differences = [i for i in add[1:] if i not in highest_match]

        results.append([add,highest_match,differences])

Or if you want to use set operations, which should be more effecient as suggested in the comments you can replace the for block with:

for add in adds:

    match_count = 0

    match_index = 0

    for idx,struct_add in enumerate(struct_adds):

        matches = set(add[1:]) & set(struct_add[1:])

        if len(matches) > match_count:

            match_count = len(matches)

            match_index = idx

    if match_count == 0:

        pass # no matches

    else:

        highest_match = struct_adds[match_index]

        differences = list(set(add[1:]) - set(highest_match[1:]))

        results.append([add,highest_match,differences])

Both yields the same results:

results

>>

[[['3', 'V8T5G2', 'VICTORIA', 'BR', 'CANADA'],

  ['7H0033', 'V8T5G2', 'VICTORIA', 'BC', 'CANADA'],

  ['BR']],

 [['543',

   '234',

   '654',

   'BELMONT',

   'AVENUE',

   'V8S3T4',

   'VICTORIA',

   'BR',

   'CANADA'],

  ['7H0044',

   '234',

   '654',

   'BELMONT',

   'AVENUE',

   'V8S3T4',

   'VICTORIA',

   'BC',

   'CANADA'],

  ['BR']],

 [['28', '70', 'RUSHTON', 'RD', 'M6C2X8', 'YK', 'ON', 'CANADA'],

  ['7H0001', '700', 'RUSHTON', 'ROAD', 'M6C2X7', 'YORK', 'ON', 'CANADA'],

  ['70', 'RD', 'M6C2X8', 'YK']]]

I should also add that in this example and also not to further complicate things, it will take the first highest match. This part is managed in the if clause comparing the count of true matches must be more than the current count of matches.