Ndtyjky

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?

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

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