Find all duplicate values across two columns and make a single distinct row

userk047 0
Viewed 38

I have a df with two columns IP address and user id. I am attempting to determine the distinct values across both the user id and the IP addresses. I know how to get a distinct list of users per IP and vis versa, but can't figure out if I wanted them smashed into a single row and how I'd do it.

what's the recommended way to get this result I'm looking for?

Example Data:

ip_address user_id
127.0.0.1 111
192.168.1.1 111
127.0.0.1 444
10.10.0.1 555
8.8.8.8 666
8.8.8.8 888
8.8.8.8 999
10.0.0.1 777

Final Format:

ip_address user_id
127.0.0.1, 192.168.1.1 111, 444, 555
8.8.8.8 666, 888, 999
10.0.0.1 777
data-analysis data-science dataframe pandas python
1 Answers

I would use networkx to find the connected components, then groupby.agg:

import networkx as nx

# build graph
G = nx.from_pandas_edgelist(df, 'ip_address', 'user_id')

# create a dictionary to cluster connected nodes
d = {n:i for i,g in enumerate(nx.connected_components(G)) for n in g}

# map clusters
group = df['ip_address'].map(d)

# groupby.agg
out = (df.astype(str)
         .groupby(group, as_index=False)
         .agg(lambda x: ', '.join(dict.fromkeys(x)))
      )

print(out)

Output:

               ip_address        user_id
0  127.0.0.1, 192.168.1.1       111, 444
1               10.10.0.1            555
2                 8.8.8.8  666, 888, 999
3                10.0.0.1            777

NB. I am assuming here that the values in both columns are not overlapping. If you had similar type of data in both columns (e.g ip addresses) and did not want to cross merge the groups (e.g. if 777 was 127.0.0.1 this would merge the first and last group), then you would need to add a unique identifier for each column (e.g. A:127.0.0.1/B:127.0.0.1).

mozway0
Related