Create strange edge matrix

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

Something like 15k unique IDs and around 100k unique tags. I've got 32GB RAM memory and i7 cpu. Thanks — SkuPak, Nov 25 '19 at 18:19

score 3 · Answer 1 · answered Nov 25 '19 at 17:54

3

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

answered Nov 25 '19 at 17:54

Quang Hoang

146,074
10
56
74

1

You can also just do `s = pd.crosstab(df['id'], df['tag']); s @ s.T`. – jdehesa Nov 25 '19 at 17:59
@jdehesa that's actually an excellent solution. Why didn't I think of that :-). – Quang Hoang Nov 25 '19 at 18:02
1

Well tbh I just answered [nearly the same question](https://stackoverflow.com/q/59033743/1782792) a moment ago :) About the complete question, I suppose the full requested output could be formed with something like `df2 = (s @ s.T).unstack(); df2.name = 'common_tags'; df2.index.names = ['id_source', 'id_target']; out = df2.reset_index()` – jdehesa Nov 25 '19 at 18:09

Create strange edge matrix

1 Answers1