[SOLVED] Melted distance table into distance matrix

Issue

I have a table like this:

Var     score
1       1.00
1       1.06
1       1.03
1       0.65
1       0.68
2       1.06
2       1.07
2       0.64
2       1.05
3       0.71
3       0.72
3       1.03
4       0.68
4       1.08
5       0.11

Want to convert this into matrix like:

      1     2     3     4     5     6     
 1    0.00  1.00  1.06  1.03  0.65  0.68
 2    1.00  0.00  1.06  1.07  0.64  1.05
 3    1.06  1.06  0.00  0.71  0.72  1.03
 4    1.03  1.07  0.71  0.00  0.68  1.08
 5    0.65  0.64  0.72  0.68  0.00  0.11
 6    0.68  1.05  1.03  1.08  0.11  0.00  

I tried awk but its keep running:

awk '{if(NF>max) max=NF} END{while(getline<"file"){for(i=NF+1;i<=max;i++)$i="0";print}}'

Solution

It keeps running because you forgot to pass it the file name. So awk takes its input from the standard input and waits for you to enter something on the keyboard. Use awk '...' file, not just awk '...'. But even with this error fixed it will not work as you expect.

You don't need to read the file twice. You can build your matrix in one single pass and populate the missing cells in the END block (tested with GNU and BSD awk):

awk 'NR > 1 {
  num[$1] += 1
  mat[$1, $1 + num[$1]] = mat[$1 + num[$1], $1] = $2
  n = num[$1] > n ? num[$1] : n
}
END {
  n += 1
  mat[0, 0] = ""
  for(i = 1; i <= n; i += 1) {
    mat[0, i] = mat[i, 0] = i
    mat[i, i] = "0.00"
  }
  for(i = 0; i <= n; i += 1)
    for(j = 0; j <= n; j += 1)
      printf("%s%s", mat[i, j], j == n ? "\n" : "\t")
}' file
     1       2       3       4       5       6
1    0.00    1.00    1.06    1.03    0.65    0.68
2    1.00    0.00    1.06    1.07    0.64    1.05
3    1.06    1.06    0.00    0.71    0.72    1.03
4    1.03    1.07    0.71    0.00    0.68    1.08
5    0.65    0.64    0.72    0.68    0.00    0.11
6    0.68    1.05    1.03    1.08    0.11    0.00

Answered By - Renaud Pacalet

Answer Checked By - Clifford M. (WPSolving Volunteer)