1

I have a file which looks like this

0   1   1   2.3
0   2   2   3.1
0   3   4   1.3
0   4   5   2.5
0   5   6   7.1
1   1   1   3.3
1   2   2   1.1
1   3   4   2.3
1   4   5   4.5
1   5   6   6.1
2   1   1   2.7
2   2   2   3.5
2   3   4   1.7
2   4   5   2.4
2   5   6   7.5
3   1   1   2.9
3   2   2   3.8
3   3   4   1.9
3   4   5   2.8
3   5   6   7.9

which I would like to convert it into

#    #    0     1    2    3
1    1   2.3  3.3  2.7  2.9
2    2   3.1  1.1  3.5  3.8
3    4   1.3  2.3  1.7  1.9
4    5   2.5  4.5  2.4  2.8
5    6   7.1  6.1  7.5  7.9

Based on the comment of "steeldriver" and this post, I was able convert three column data, let's say excluding the third column, into a matrix by

 datamash -W crosstab 2,1 unique 3 < file

But when I am adding another column to be grouped by the second column, modifying the script by "--g 2,3", I am getting "datamash: conflicting operation ‘crosstab’". Any suggestion on fixing this issue?

Shasa
  • 143

1 Answers1

2

The KISS way to do it is probably to pre-process the data so that the second and third columns are treated as a single field e.g. (using TAB as the "real" delimiter):

$ awk '{printf "%s\t%s   %s\t%s\n", $1, $2, $3, $4}' file | datamash crosstab 2,1 unique 3
    0   1   2   3
1   1   2.3 3.3 2.7 2.9
2   2   3.1 1.1 3.5 3.8
3   4   1.3 2.3 1.7 1.9
4   5   2.5 4.5 2.4 2.8
5   6   7.1 6.1 7.5 7.9
steeldriver
  • 81,074