-1

I've got a script to get the following results
1st Column: Slot number
2nd Column: Port number
3rd Column: Number of connections

1 1 23,        
1 2 0,        
1 3 38,        
1 4 34,    
2 1 8,   
2 2 18,   
2 3 0,   
2 4 22,   
4 1 6,   
4 2 18,   
4 3 10,   
4 4 22,   

Now, the question is how do I transpose it into a prettier matrix (Rows x Slots) like below.

         Slot #
      1 | 2 | 3 | 4 |           
P  1| 23| 8 | - | 6 |
o  2| 0 | 18| - | 18|
r  3| 38| 0 | - | 10|
t  4| 34| 22| - | 22|
#

I would think that using awk would be the way to go, but not sure on how to start it.

  • Got it to display what i want using pr – loopback999 Jan 23 '17 at 8:27
2

Essentially what you are asking for is a contingency table (aka a cross tabulation or crosstab). For example with GNU datamash

datamash -sW crosstab 2,1 unique 3 < file
    1   2   4
1   23, 8,  6,
2   0,  18, 18,
3   38, 0,  10,
4   34, 22, 22,

If you want to remove the trailing commas from your data, a simple option would be tr

tr -d , < file | datamash -sW crosstab 2,1 unique 3
    1   2   4
1   23  8   6
2   0   18  18
3   38  0   10
4   34  22  22

If you want to add the "missing" column, you need to supply filler data - either

{ tr -d , < file ; printf '3 %d -\n' {1..4} ; } | 
  datamash -sW crosstab 2,1 unique 3
    1   2   3   4
1   23  8   -   6
2   0   18  -   18
3   38  0   -   10
4   34  22  -   22

or supply just a single point and let datamash fill the rest

{ tr -d , < file ; printf '3  1 -\n' ; } | 
  datamash --no-strict --filler=- -sW crosstab 2,1 unique 3
    1   2   3   4
1   23  8   -   6
2   0   18  -   18
3   38  0   -   10
4   34  22  -   22
1

used pr | column -t to display what I wanted. Added more of my results to show the full matrix. I figured that the 1st and 2nd columns were redundant and removed it from my dataset. So test_3.txt only contains the data results from the 3rd column.

pr -ts" " --columns 9 test_3.txt | column -t
23  8   6   11  18  33  39  16  33
0   18  18  32  18  16  27  13  12
38  0   10  10  21  16  24  16  29
34  22  22  20  16  16  33  21  21
33  23  19  16  13  10  29  16  53
33  19  8   21  10  13  21  17  29
43  31  20  19  14  15  15  0   9

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.