Spit a column into multiple columns in bash

Question

I am working on VM Linux Ubuntu and my goal is to form this input

4,4
3,8
4,2
4,8
6,8
5,1
4,5
6,9
4,2
5,4
5,9
5,9
7,2
4,9
4,4
5,1

to this output

4,4 6,8 4,2 7,2
3,8 5,1 5,4 4,9
4,2 4,5 5,9 4,4
4,8 6,9 5,9 5,1

And copy these values to an Excel file on Windows.

I wrote this command

cat text.txt |cut -f3| awk '1 ; NR%4==0 {printf"\n"} '|pr  -4 -t|column -t| tr -s '[:blank:]' '\t'

which works fine!

I counted the blanked lines to create each column as below : cat text.txt |cut -f3| awk '1 ; NR%4==0 {printf"\n"} '| awk '!NF {sum += 1} END {print sum}' --> I got 4 blanked lines, so I am adding 4 to this command |pr -4 -t|

My issue was when I had a larger file, let's say 160 lines (40 blanked lines). Initially, the modification on this part |pr -40 -t gave an error of pr: page width too narrow so I changed to

 cat text.txt |cut -f3| awk '1 ; NR%4==0 {printf"\n"} '| pr -W200 -40 -t | column -t| tr -s '[:blank:]' '\t'

which seems to work; however, the copy and paste merges 4 values in the 26th column, like

  4,83,6     
  5,66,2     
  6,57,6     
  8,18,1    

instead of

  4,8  3,6   
  5,6  6,2   
  6,5  7,6   
  8,1  8,1  

Even though I tried different values on the W option, I assume that -W200 is responsible for this issue. I have two questions.

1. Can a good proportion between the wide and blanked cells be found to fix this merging issue?
2. As my goal is to “paste” 4 lines next to each other, could someone help me to paste the values directly instead of counting the number of blanked lines first and then modifying the wide range as I did?

I really appreciate any help you can provide.

Answer 1

Instead of trying to reverse-engineer and understand OP's current code, and since OP is already using awk, I'd like to propose a single awk script to replace all of OP's current code:

awk -v n=4 '                   # set awk variable "n" to the number of output lines
BEGIN { OFS="\t" }
      { sub(/\r$/,"")          # strip out a trailing dos/windows line ending "\r"
        ndx = NR%n
        lines[ndx] = lines[ndx] (NR <= n ? "" : OFS) $1
      }
END   { for (i=1; i<=n; i++) {
            print lines[i%n]
        }
      }
' text.txt

NOTE: you can remove the sub(/\r$/,"") if you know for a fact the input file will never contain dos/windows line endings (\r); if the file does not include \r line endings then the sub(/\r$/,"") becomes a no-op that does nothing (other than use up a few cpu cycles)

For 4 lines (n=4) of output:

4,4     6,8     4,2     7,2
3,8     5,1     5,4     4,9
4,2     4,5     5,9     4,4
4,8     6,9     5,9     5,1

For 3 lines (n=3) of output:

4,4     4,8     4,5     5,4     7,2     5,1
3,8     6,8     6,9     5,9     4,9
4,2     5,1     4,2     5,9     4,4

For 7 lines (n=7) of output:

4,4     6,9     4,4
3,8     4,2     5,1
4,2     5,4
4,8     5,9
6,8     5,9
5,1     7,2
4,5     4,9

Answer 2

Another simple approach is to buffer the elements in an array using string concatenation in awk. No other process is needed, e.g.

awk '{n++; a[n] = (FNR > 4) ? a[n] " " $0 : $0; if (n == 4) n = 0 } END {for (i=1; i<=4; i++) print a[i] }' file

Essentially you build a 4-element array with each element being one of the final rows you want in your output. You concatenate every 4^th value to each array element 1, 2, 3 and 4 in order, adding a space before each value after the first in each element.

After having read and concatenated all values into your array, you simply loop over the array elements in the END rule outputting each.

In an easier to read expanded form that would be:

awk '{
  n++
  a[n] = (FNR > 4) ? a[n] " " $0 : $0
  if (n == 4)
    n = 0
}
END {
  for (i=1; i<=4; i++)
     print a[i]
}' file

(adjust for whitespace as needed)

Example Use/Output

Using a heredoc to feed your example to awk you can do:

$ awk '{n++; a[n] = (FNR > 4) ? a[n] " " $0 : $0; if (n == 4) n = 0 } END {for (i=1; i<=4; i++) print a[i] }' << eof
4,4
3,8
4,2
4,8
6,8
5,1
4,5
6,9
4,2
5,4
5,9
5,9
7,2
4,9
4,4
5,1
eof

4,4 6,8 4,2 7,2
3,8 5,1 5,4 4,9
4,2 4,5 5,9 4,4
4,8 6,9 5,9 5,1