0

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?

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
  • Perfect. It works neatly. – Shasa Jan 10 at 4:23

Your Answer

By clicking "Post Your Answer", you acknowledge that you have read our updated terms of service, privacy policy and cookie policy, and that your continued use of the website is subject to these policies.

Not the answer you're looking for? Browse other questions tagged or ask your own question.