Create strange edge matrix [duplicate]

Question

I've got a dataframe like this one:

import pandas as pd

df = pd.DataFrame(columns = ['id', 'tag'])

df['id'] = (['1925782942580621034', '1925782942580621034',
   '1925782942580621034', '1925782942580621034',
   '1930659617975470678', '1930659617975470678',
   '1930659617975470678', '1930659617975470678',
   '1930659617975470678', '1930659617975470678',
   '1930659617975470678', '1930659617975470678',
   '1971229370376634911', '1971229370376634911',
   '1971229370376634911', '1971229370376634911',
   '1971229370376634911', '1971229370376634911',
   '1971229370376634911', '1971229370376634911',
   '1971229370376634911'])

df['tag'] = (['nintendo', 'cosmetic', 'pen', 'office supplies', 'holding',
   'person', 'hand', 'text', 'design', 'pen', 'office supplies',
   'cosmetic', 'tool', 'office supplies', 'weapon', 'indoor',
   'everyday carry', 'pen', 'knife', 'electronics', 'case'])

df

I'd like to work on it in order to obtain something like:

df_wish = pd.DataFrame(columns = ['id_source', 'id_target', 'common_tags'])

Where:

df_with['id_source'] #is the "id" that we are taking care of
df_with['id_target'] #is the "id" that has at least one "tag" in common with "id_source"
df_with['common_tags'] #is the number of shared "tag" between "id_source" and "id_target"

Can you help me please? Thanks a lot

Answer 1

If you don't have too many tags/ID's, you can do a crosstab and broadcast:

s = pd.crosstab(df['id'], df['tag'])
idx = s.index

s = s.values
pd.DataFrame(np.sum(s[None,:] & s[:, None], axis=-1), 
             index=idx, columns=idx)

Output:

                       1925782942580621034    1930659617975470678    1971229370376634911
-------------------  ---------------------  ---------------------  ---------------------
1925782942580621034                      4                      3                      2
1930659617975470678                      3                      8                      2
1971229370376634911                      2                      2                      9