Transform the relationship data with weight into a Matrix in python -

input data format that: data.txt

 col1    col2    weight        b       1        c       2        d       0  b       c       3  b       d       0  c       d       0 

i want output data format that: result.txt

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

i use pandas in way

import pandas pd  # read data .csv file df = pd.read_csv('yourdata.csv')  # pivot table mat = pd.pivot_table(df,index='col1',columns='col2',values='weight')  # rebuild index index = mat.index.union(mat.columns)  # build new full matrix , fill nan values 0 mat = mat.reindex(index=index, columns=index).fillna(0)  # make matrix symmetric m = mat + mat.t 

this returns:

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

edit: instead of pivot_table() can use:

mat = df.pivot(index='col1',columns='col2',values='weight')