Convert list of edges to adjacency matrix

user_4379965 0
Viewed 2418

My dataframe represents a list of edges of a graph and has the following format:

  node1 node2 weight
0     a     c      1
1     b     c      2
2     d     c      3

My goal is to generate the equivalent adjacency matrix:

    a b c d
a   0 0 1 0
b   0 0 2 0
c   0 0 0 3
d   0 0 0 0

At the moment, while constructing the the dataframe of edges I count the number of nodes and create an NxN data frame and fill in the values manually. what is the pandas way of generating the second dataframe from the first one?

pandas python
2 Answers

Decided to have a little fun with the problem.

You can convert node1 and node2 to Categorical dtype and then use groupby. 

from functools import partial

vals = np.unique(df[['node1', 'node2']])
p = partial(pd.Categorical, categories=vals) 
df['node1'], df['node2'] = p(df['node1']), p(df['node2'])

(df.groupby(['node1', 'node2'])
   .first()
   .fillna(0, downcast='infer')
   .weight
   .unstack())

node2  a  b  c  d
node1            
a      0  0  1  0
b      0  0  2  0
c      0  0  0  0
d      0  0  3  0

Another option is setting the underlying array values directly.

df2 = pd.DataFrame(0, index=vals, columns=vals)
f = df2.index.get_indexer
df2.values[f(df.node1), f(df.node2)] = df.weight.values

print(df2)
   a  b  c  d
a  0  0  1  0
b  0  0  2  0
c  0  0  0  0
d  0  0  3  0
user_49090870

Use pivot with reindex

In [20]: vals = np.unique(df[['node1', 'node2']])

In [21]: df.pivot(index='node1', columns='node2', values='weight'
                  ).reindex(columns=vals, index=vals, fill_value=0)
Out[21]:
node2  a  b  c  d
node1
a      0  0  1  0
b      0  0  2  0
c      0  0  0  0
d      0  0  3  0

Or use set_index and unstack

In [27]: (df.set_index(['node1', 'node2'])['weight'].unstack()
            .reindex(columns=vals, index=vals, fill_value=0))
Out[27]:
node2  a  b  c  d
node1
a      0  0  1  0
b      0  0  2  0
c      0  0  0  0
d      0  0  3  0
user_21372550
Related