How to create an edge list from pandas dataframe?

Question

I have a pandas dataframe (df) of the form-

    Col1
A  [Green,Red,Purple]
B  [Red, Yellow, Blue]
C  [Brown, Green, Yellow, Blue]

I need to convert this to an edge list i.e. a dataframe of the form:

Source    Target    Weight
  A         B         1
  A         C         1
  B         C         2

EDIT Note that the new dataframe has rows equal to the total number of possible pairwise combinations. Also, to compute the 'Weight' column, we simply find the intersection between the two lists. For instance, for B&C, the elements share two colors: Blue and Yellow. Therefore, the 'Weight' for the corresponding row is 2.

What is the fastest way to do this? The original dataframe contains about 28,000 elements.

Sorry, it isn't clear how you want to get from the first to second. — cs95, Jul 09 '17 at 01:50
@cᴏʟᴅsᴘᴇᴇᴅ The lists for each element are being compared pairwise. For example, for A-B, the elements have one element in common (Red). Therefore, the weight for Source-A Target-B row is 1. In total, the new dataframe would have all pairwise combinations of the original dataframe rows. — Melsauce, Jul 09 '17 at 01:54
When you say 28k elements do you mean rows/nodes? If so, doing a generate all combinations approach will be quite [large](https://www.google.com/search?q=28000+choose+2&oq=28000+cho&aqs=chrome.0.69i59j69i57j0.6150j0j8&sourceid=chrome&ie=UTF-8) — Bob Haffner, Jul 09 '17 at 03:44
Yes @BobHaffner! That is currently a huge problem I have to work around. — Melsauce, Jul 09 '17 at 04:22
If your dataframe is well-defined (i.e. every row has the same number of elements). You can easily do this with pd.melt(). Its not clear why you'd want to store lists instead a dataframe -- seems to defeat the purpose of using dataframes — mortonjt, Jun 11 '19 at 23:40

cs95 · Accepted Answer · 2019-06-18T18:00:09.357

First, starting off with the dataframe:

from itertools import combinations

df = pd.DataFrame({
        'Col1': [['Green','Red','Purple'], 
                 ['Red', 'Yellow', 'Blue'], 
                 ['Brown', 'Green', 'Yellow', 'Blue']]
     }, index=['A', 'B', 'C'])

df['Col1'] = df['Col1'].apply(set)    
df

                           Col1
A          {Purple, Red, Green}
B           {Red, Blue, Yellow}
C  {Green, Yellow, Blue, Brown}

Each list in Col1 has been converted into a set to find the union efficiently. Next, we'll use itertools.combinations to create pairwise combinations of all rows in df:

df1 = pd.DataFrame(
    data=list(combinations(df.index.tolist(), 2)), 
    columns=['Src', 'Dst'])

df1

  Src Dst
0   A   B
1   A   C
2   B   C

Now, apply a function to take the union of the sets and find its length. The Src and Dst columns act as a lookup into df.

df1['Weights'] = df1.apply(lambda x: len(
    df.loc[x['Src']]['Col1'].intersection(df.loc[x['Dst']]['Col1'])), axis=1)
df1

  Src Dst  Weights
0   A   B        1
1   A   C        1
2   B   C        2

I advice set conversion at the very beginning. Converting your lists to a set each time on the fly is expensive and wasteful.

For more speedup, you'd probably want to also copy the sets into two columns in the new dataframe because calling df.loc constantly will slow it down a notch.

@Wen Sorry! I wrote my answer independent of yours. There are some subtle differences, but they are similar in many aspects. I believe you deserve a +1 too :) — cs95, Jul 09 '17 at 03:56

score 5 · Answer 2 · answered Jul 09 '17 at 02:16

Try this. Not very neat but work. PS: The final out put you can adjust it , I did not drop columns and change the columns name

import pandas as pd 
df=pd.DataFrame({"Col1":[['Green','Red','Purple'],['Red', 'Yellow', 'Blue'],['Brown', 'Green', 'Yellow', 'Blue']],"two":['A','B','C']})
df=df.set_index('two')
del df.index.name
from itertools import combinations
DF=pd.DataFrame()
dict1=df.T.to_dict('list')
DF=pd.DataFrame(data=[x for x in combinations(df.index, 2)])
DF['0_0']=DF[0].map(df['Col1'])
DF['1_1']=DF[1].map(df['Col1'])
DF['Weight']=DF.apply(lambda x : len(set(x['0_0']).intersection(x['1_1'])),axis=1)



DF
Out[174]: 
   0  1                   0_0                           1_1  Weight
0  A  B  [Green, Red, Purple]           [Red, Yellow, Blue]       1
1  A  C  [Green, Red, Purple]  [Brown, Green, Yellow, Blue]       1
2  B  C   [Red, Yellow, Blue]  [Brown, Green, Yellow, Blue]       2

score 2 · Answer 3 · answered Jul 09 '17 at 05:51

get an array of sets
get pairwise indices representing all combinations using np.triu_indices
use & operator to get the pairwise intersections and get lengths via a comprehension

c = df.Col1.apply(set).values

i, j = np.triu_indices(c.size, 1)

pd.DataFrame(dict(
        Source=df.index[i],
        Target=df.index[j],
        Weight=[len(s) for s in c[i] & c[j]]
    ))

  Source Target  Weight
0      A      B       1
1      A      C       1
2      B      C       2

How to create an edge list from pandas dataframe?

3 Answers3