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 agree to our terms of service, privacy policy and cookie policy

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