Unix script to join data from different rows into one single row

Question

We have a large tsv file where the data for a single row are splitted into different rows with new line delimiter.

We need to join them together based on the tab counts.

For eg: If suppose the total tab count for a single row is 995 , the data is split in between as follows,

Row Number Tab Count Row 1 660 Row 2 0 Row 3 300 Row 5 20 Row 6 15 Total 995 

N.B The above row split is not consitent and varies.

I want to add the tab counts and once we get 995 as total , need to join the data from different rows into one single row.

We have the below command to join the lines based on new line delimiter.

paste -sd '\n' inputfile > output file 

I want to know,

1. If we can get the tab counts for different rows
2. Add the tab counts to get sum of 995
3. Once the sum is achieved , whichever tab counts were added from those rows , need to be joined into one single row.

Please let me know if this can be achieved using shell script.

Thanks.!

Answer 1

As always with these type of questions, it would be better to correct the process that creates the data in the first place rather than appending a post-processing stage to the process. Having said that, here's what you can do.

$ cat file 1 2 3 1 2 3 1 2 3 

$ awk -v w=3 -f script.awk file 1 2 3 1 2 3 1 2 3 

This awk script will collect the tab-delimited fields from the input until a preset number of fields have been collected. It will then output these collected fields as its own line before continuing reading from the input.

The number of fields in the output is given by the value of w, which is passed on the command line as shown above. Note that this is the number of fields, not the number of tab characters.

BEGIN { OFS = FS = "\t" } function output_line () { # a function that outputs the nf elements in the array a # separated by OFS (tab) and terminated by ORS (newline) for (j = 1; j < nf; ++j) printf("%s%s", a[j], OFS) printf("%s%s", a[nf], ORS) } { # a: an array of fields that we want to output together # nf: the length of that array # just add each field to the a array for (i = 1; i <= NF; ++i) { a[++nf] = $i # if enough has been read, output the collected data if (nf == w) { output_line() nf = 0 } } } END { # output any data remaining in a if (nf > 0) output_line() } 

This is the same as

tr '\t' '\n' <file | paste - - - 

for my small example. In your case, you could use the awk script above with -v w=996, or you could type the tr+paste command with 996 dashes.

Answer 2

Would continued reading lines until reaching field count help? From another post:

awk -F'\t' ' {while (NF<996) {getline X $0 = $0 FS X } } 1 ' file