I need to produce a table with the 1st column as the top row and the 2nd column as the 1st column of the table. the 3rd column is my data and slotted in the table based on the 1st column and 2nd column.
1) test.txt : the file containing the text. 2) test.out : The output file.
This will have some problem in formatting which on using printf can be changed but again if the col1 length increases, then the formatting will be lost. You can try playing with formatting.
I think each columnar heading needs to appear only once. The output below is based on the sample data posted, except that I omitted the last (3G128) column due to width constraints here.
If you were to copy/paste this output into a proportional spaced forum, the columns would be aligned, but in this forum, spaces are narrower than other characters.
This solution assumes that the input file is already sorted in the desired order. It will be processed/printed in that order, as regards both the horizontal headings and the vertical columns.
Maximum width for table column 1 is currently set for 9. To adjust this, change the c1w value.
Space allocated for each column across the page is 7. To adjust this, change the three 7's to something else, but keep them all the same.
Since your data is numeric, I right-justified both the columnar headings and the data columns.
Jim, ur solution is great. however need a bit more help here.
for eg. M1-CSC is seperated into different rows. I need one row just for M1-CSC and the various numbers all in 1 line which is linked to M1-CSC like 5 under M32, 46 under N32 but all on the same horizontal row.
I will try to work on ur solution and see if I can tweak it to get it to work i needed.
I call this an xy report where x represents the horizontal columnar headings and y represents the vertical labels.
The solution below does an initial sort on the input file, which is then piped into awk pass #1. Note that this first sort is completely optional, and its sole purpose is to control the sequencing of the x columnar headings. The sort can be eliminated (change awk pass #1 to read the input file directly), or the sort could be fancied up to get the desired order of x.
Like the prior solution, the data is passed twice, but while the prior solution passed it once in BEGIN processing and once in main processing, this solution uses two separate awk's with a sort in between.
awk pass #1 assigns numbers to the x values as encountered (based on either the non-sorted or sorted file), and rewrites the input file and includes these numerical column assignments for sorting purposes. The intermediate file is then sorted primary on y and secondary on numerical x. Controlling the secondary x sort on a generated value allows you to have/sort the file in any desired order for controlling the order of the x columns.
This solution prints multiple y entries all on one line, and does so by constructing the y line by doing multiple printf commands without closing the line out, thus I require the multiple y entries to arrive in the order that they will be printed across the line horizontally.
awk pass #1 also constructs the column heading for the report as it discovers each unique x column. This heading is written at the end of the pass, and the blanks at the beginning of this heading line just happen to serve the purpose of making this line sort to the front.
awk pass #2 implements control-break logic on the y lines. At the start of a new y group, the previous y line is closed out, and construction of a new line begins by printing the y label. Then one by one, each data value is printed across the line. I print it under the proper column by calculating the proper amount of space padding.
The width of a y column is now hard-coded at 9 characters. To change this, change the number of spaces assigned to rptheading, and also change the "%-9s" respectively.
