Identifying duplicate customer tuples by either contact number or email address

Viewed 123

I am trying to group the indexes of the customers based on the following condition with python.

If database contains the same contact number or email, the result should return the indexes of the tuples grouped together in a sub-list.

For a given database:

data = [
 ("Customer1","contactA", "emailA"),
 ("CustomerX","contactA", "emailX"),
 ("CustomerZ","contactZ", "emailW"),
 ("CustomerY","contactY", "emailX"),
 ]

The above example shows that Customer1 and CustomerX shares the same contact number, and CustomerX and CustomerY shares the same email, hence Customer1, CustomerX and CustomerY are the same customer.

Hence the result is [[0, 1, 3], [2]]

3 Answers

You could build a graph where you connect elements with a common email or with a common contact and then find connected components (e.g., by using a bfs visit).
In this case I'm using the networkx library to build a graph and find connected components.

>>> contacts = defaultdict(list)
>>> emails = defaultdict(list)
>>> for idx, (name, contact, email) in enumerate(data):
...     contacts[contact].append(idx)
...     emails[email].append(idx)
...
>>> g = nx.Graph()
>>> for common_attr in itertools.chain(contacts.values(), emails.values()):
...     g.add_edges_from(itertools.combinations(common_attr,2))
... 
>>> list(nx.connected_components(g))
[{0, 1, 3}, {2}]

You could do this:

my_contact_dict = {}
my_email_dict = {}
my_list = []
for pos, cust in enumerate(data):
    contact_group = my_contact_dict.get(cust[1], set()) # returns empty set if not in dict
    email_group = my_email_dict.get(cust[2], set())     #
    contact_group.add (pos)
    email_group.add (pos)
    contact_group.update (email_group)                  # Share info between the two groups
    email_group.update (contact_group)                  #

    for member in contact_group:
        my_contact_dict[data[member][1]] = contact_group
    for member in email_group:
        my_email_dict[data[member][2]] = email_group

result = {tuple(x) for x in my_contact_dict.values()}
print (result)

Testing it out:

data = [
 ("Customer1","contactA", "emailA"),
 ("CustomerX","contactA", "emailX"),
 ("CustomerZ","contactZ", "emailW"),
 ("CustomerY","contactY", "emailX"),
 ]

gives:

{(2,), (0, 1, 3)}

And:

data = [
 ("Customer1","contactA", "emailA"),
 ("CustomerX","contactA", "emailX"),
 ("CustomerZ","contactZ", "emailW"),
 ("CustomerY","contactY", "emailX"),
 ("CustomerW","contactZ", "emailA"),
 ]

gives:

{(0, 1, 2, 3, 4)}

I usually approach these kinds of problems with the pandas package, as it makes handling of (large) datasets especially easy.

import pandas as pd

data = [
 ("Customer1","contactA", "emailA"),
 ("CustomerX","contactA", "emailX"),
 ("CustomerZ","contactZ", "emailW"),
 ("CustomerY","contactY", "emailX"),
 ("CustomerB","contactY", "emailZ"),
 ("CustomerC","contactB", "emailZ"),
 ("CustomerD","contactZ", "emailD"),
 ("CustomerE","contactO", "emailO"),
 ("CustomerF","contactF", "emailF")
 ]

df = pd.DataFrame(data, columns=["Customer", "Contact", "Email"])

#unique contact information
unique_contact = df.Contact.unique()
result=[]

for contact in unique_contact:
    #all entries with this contact
    contact_df = df[df.Contact == contact]
    #unique email adresses of these contacts
    contact_df_email = contact_df.Email.unique()

    matches1 = df.Contact == contact #where contact is the same
    matches2 = df.Email.isin(contact_df_email) #where email is the same as in any of the identical contacts
    #index values of entries that share contact OR email information
    result.append(df[matches1 | matches2].index.values.tolist()) 

#credit: https://stackoverflow.com/a/56567367
def over(coll):
     # gather the lists that do overlap 
     overlapping = [x for x in coll if any(x_element in [y for k in coll if k != x for y in k] for x_element in x)] 
     # flatten and get unique 
     overlapping = sorted(list(set([z for x in overlapping for z in x]))) 
     # get the rest
     non_overlapping = [x for x in coll if all(y not in overlapping for y in x)] 
     return [overlapping]+non_overlapping

print(over(result))

An answer found here was especially helpful in solving this and as can be seen in my example, this can be extended to more complex customer structures. For the input data provided in your question the output is

[[0, 1, 3], [2]]

Related