Construct matrix from tsv in python

user_12069909 0
Viewed 142

I have a .tsv file, that looks like this:

A               B               C
SRX479927.bed   SRX821810.bed   0.258912
SRX032890.bed   SRX821809.bed   0.161135
SRX660083.bed   SRX821816.bed   0.00379202
SRX821817.bed   SRX821818.bed   0.0902696
SRX660084.bed   SRX479928.bed   0.2671
SRX821806.bed   SRX821811.bed   0.458604
SRX476926.bed   SRX821806.bed   0.0303042
SRX196107.bed   SRX414970.bed   0.221135

Where A and B are files, and C is an index of the similarity of these files. I am trying to construct a matrix in python with A on the y-axis and B on the x-axis, filling in the corresponding values. I have done this before for .tsv files with 2 columns (files and values), but cannot succeed with 3 columns. Can anybody provide an approach on how to solve this? I am sorry if this was asked before, but i can only find questions on how to construct a matrix from 2 columns.

pandas matrix python
1 Answers

You can load it as a data frame and then set A and B as the index, sort accordingly and then unstack the index:

matrix = df.set_index(['A', 'B'])['C'].sort_index().unstack()

If your data doesn't contain similarity scores for similar files (i.e. comparing a file to itself), you can add them manually. If the scores are symmetric and only the upper triangle of the matrix is present you can use numpy.triu_indices for constructing the matrix.

user_37672390
Related