Create symmetric matrix from pandas data frame

Question

I'd like to create a plot like this.

In my case, I need a symmetric 20x20 matrix where the entry (i,j) should be taken from the ns column within data file which has the format (just a piece):

areas ns i j
0.500000 1.00 10 10
0.513611 0.80 10 11
0.582778 0.12 10 12
0.725278 0.00 10 13
0.528472 0.59 10 14
0.655000 0.00 10 15
0.616667 0.03 10 16
0.751806 0.00 10 17
0.519722 0.71 10 18
0.917045 0.00 10 19
0.849583 0.00 10 20
0.804333 0.00 1 10
0.500000 1.00 11 11
0.599861 0.06 11 12
0.611389 0.03 11 13
0.525417 0.64 11 14
0.533889 0.52 11 15
0.590833 0.09 11 16
0.609722 0.04 11 17
0.573472 0.17 11 18
0.802652 0.00 11 19
0.764000 0.00 1 11
0.677083 0.00 11 20
0.730667 0.00 1 12
0.879667 0.00 1 13
0.778667 0.00 1 14
0.858333 0.00 1 15
0.726333 0.00 1 16
0.884000 0.00 1 17
0.772667 0.00 1 18
0.959545 0.00 1 19
0.500000 1.00 1 1
0.919667 0.00 1 20
0.500000 1.00 12 12
0.769444 0.00 12 13
0.606667 0.04 12 14
0.688611 0.00 12 15
0.509444 0.86 12 16
0.789722 0.00 12 17
0.604722 0.05 12 18
0.934091 0.00 12 19
0.874583 0.00 12 20
0.614231 0.11 1 2
0.500000 1.00 13 13
0.664028 0.00 13 14
0.627500 0.02 13 15
0.803194 0.00 13 16
0.517500 0.74 13 17
0.515278 0.78 13 18
0.781439 0.00 13 19
0.634861 0.01 13 20
0.567667 0.34 1 3
0.500000 1.00 14 14
0.559583 0.26 14 15
0.616111 0.03 14 16
0.669306 0.00 14 17
0.569583 0.19 14 18
0.874242 0.00 14 19
0.772083 0.00 14 20
0.580000 0.25 1 4
0.500000 1.00 15 15
0.735139 0.00 15 16
0.656944 0.00 15 17
0.502083 0.97 15 18
0.890341 0.00 15 19
0.791944 0.00 15 20
0.787222 0.00 1 5
0.500000 1.00 16 16
0.821250 0.00 16 17
0.580278 0.13 16 18
0.950568 0.00 16 19
0.908750 0.00 16 20
0.510333 0.88 1 6
0.500000 1.00 17 17
0.502500 0.96 17 18
0.795644 0.00 17 19
0.625556 0.02 17 20
0.797333 0.00 1 7
0.500000 1.00 18 18
0.617235 0.04 18 19
0.516250 0.76 18 20
0.732000 0.00 1 8
0.500000 1.00 19 19
0.720265 0.00 19 20
0.851228 0.00 1 9
0.500000 1.00 20 20
0.797917 0.00 2 10
0.709455 0.00 2 11
0.675641 0.00 2 12
0.876282 0.00 2 13
0.741667 0.00 2 14
0.851442 0.00 2 15
0.710256 0.00 2 16
0.880128 0.00 2 17
0.694872 0.00 2 18
0.949519 0.00 2 19
0.912500 0.00 2 20
0.500000 1.00 2 2
0.867308 0.00 2 3
0.891667 0.00 2 4
0.763889 0.00 2 5
0.694872 0.00 2 6
0.785256 0.00 2 7
0.729647 0.00 2 8
0.844298 0.00 2 9
0.991250 0.00 3 10
0.943194 0.00 3 11
0.930972 0.00 3 12
0.999167 0.00 3 13
0.963472 0.00 3 14
0.999722 0.00 3 15
0.964167 0.00 3 16
0.998333 0.00 3 17
0.921944 0.00 3 18
1.000000 0.00 3 19
1.000000 0.00 3 20
0.500000 1.00 3 3
0.572222 0.18 3 4
0.975463 0.00 3 5
0.752639 0.00 3 6
0.985278 0.00 3 7
0.978889 0.00 3 8
0.991520 0.00 3 9
0.979444 0.00 4 10
0.948611 0.00 4 11
0.938056 0.00 4 12
0.992917 0.00 4 13
0.964583 0.00 4 14
0.991250 0.00 4 15
0.963472 0.00 4 16
0.994444 0.00 4 17
0.935139 0.00 4 18
1.000000 0.00 4 19
0.998333 0.00 4 20
0.500000 1.00 4 4
0.968056 0.00 4 5
0.806389 0.00 4 6
0.975278 0.00 4 7
0.965972 0.00 4 8
0.984942 0.00 4 9
0.522685 0.72 5 10
0.503241 0.96 5 11
0.576389 0.21 5 12
0.679861 0.00 5 13
0.509259 0.89 5 14
0.632176 0.03 5 15
0.594907 0.13 5 16
0.698148 0.00 5 17
0.502315 0.97 5 18
0.823232 0.00 5 19
0.767824 0.00 5 20
0.500000 1.00 5 5
0.921991 0.00 5 6
0.514815 0.80 5 7
0.615741 0.06 5 8
0.624513 0.04 5 9
0.954444 0.00 6 10
0.844583 0.00 6 11
0.834722 0.00 6 12
0.979306 0.00 6 13
0.889444 0.00 6 14
0.977222 0.00 6 15
0.895972 0.00 6 16
0.980000 0.00 6 17
0.813194 0.00 6 18
0.992045 0.00 6 19
0.984028 0.00 6 20
0.500000 1.00 6 6
0.940556 0.00 6 7
0.920139 0.00 6 8
0.960088 0.00 6 9
0.501389 0.98 7 10
0.529028 0.59 7 11
0.584028 0.11 7 12
0.723611 0.00 7 13
0.533750 0.52 7 14
0.648472 0.01 7 15
0.617222 0.03 7 16
0.755694 0.00 7 17
0.535139 0.52 7 18
0.929735 0.00 7 19
0.864861 0.00 7 20
0.500000 1.00 7 7
0.665278 0.00 7 8
0.656287 0.00 7 9
0.660694 0.00 8 10
0.586944 0.11 8 11
0.531667 0.55 8 12
0.838889 0.00 8 13
0.630000 0.01 8 14
0.803056 0.00 8 15
0.509028 0.87 8 16
0.861944 0.00 8 17
0.569722 0.19 8 18
0.969697 0.00 8 19
0.935833 0.00 8 20
0.500000 1.00 8 8
0.761696 0.00 8 9
0.652485 0.00 9 10
0.590936 0.09 9 11
0.718567 0.00 9 12
0.539766 0.46 9 13
0.629532 0.01 9 14
0.560819 0.27 9 15
0.747953 0.00 9 16
0.548099 0.37 9 17
0.519006 0.72 9 18
0.770734 0.00 9 19
0.646345 0.01 9 20
0.500000 1.00 9 9

This is what I tried so far:

import pandas as pd
import numpy as np
import matplotlib.pyplot as plt

data_file = 'areas-ns.txt'
df = pd.read_csv(data_file, delim_whitespace=True,header=0)
df = df.sort_values(by=['i','j','ns','areas'], ascending=[True,True,True,True])
areas = np.array(df)[:,0]
ns = np.array(df)[:,1]
grupo1 = np.array(df)[:,2]
grupo2 = np.array(df)[:,3]

def make_sym_matrix(n):
  m = np.zeros([n,n], dtype=np.double)
  for i in range(n):
    for j in range(i,n):
        m[i,j]= ns[20*i+j] # here is the problem
        m[j,i]=m[i,j]
  return m

print ns
print make_sym_matrix(20)

Answer 1

Probably is a better way but I think this works using an unstack() trick. It would be helpful if you made your example data smaller btw.

By setting two columns as indexes, and then unstacking one of them, we essentially convert data into a square shape:

arr = df.set_index(['i','j'])['ns'].unstack().values

print(arr[:4,:4])

[[ 1.    0.11  0.34  0.25]
 [  nan  1.    0.    0.  ]
 [  nan   nan  1.    0.18]
 [  nan   nan   nan  1.  ]]

As you can see above, this is an upper triangle matrix, which we can pretty easily make into a symetric matrix using the handy numpy function triu along with a transpose (T):

arr2 = np.triu(arr) + np.triu(arr,1).T

print(arr2[:4,:4])

[[ 1.    0.11  0.34  0.25]
 [ 0.11  1.    0.    0.  ]
 [ 0.34  0.    1.    0.18]
 [ 0.25  0.    0.18  1.  ]]