[Python] Group rows that contains at least one common element in list from same column and...

I have a dataframe with 1 column that contains list element, and 1 column with integer. I would like to group all lists which have at least one element in common, and than aggregate the other column.

import pandas as pd
import json
import networkx as nx

data = {'lot': [['6309025'],
['6309025', '6375538', '6375540'],
['6410558'], ['6314113']],
'count': [1, 2, 3, 3]}

df = pd.DataFrame(data)


df['id'] = df.index
df = df.explode('lot')

G = nx.from_pandas_edgelist(df, 'lot', 'id')

l = list(nx.connected_components(G))

L = [dict.fromkeys(y, x) for x, y in enumerate(l)]

d = {k: v for d in L for k, v in d.items()}

s = df.groupby(df.id.map(d)).lot.apply(set)


I used the solution from this question. However, I can't find a way to aggregate the count column.

Expected output :

lot count
0 {6309025, 6410558, 6375540, 6375538} 3
1 {6410558} 3
2 {6314113} 3


Any thoughts ?

Continue reading...