Convert one (long) column into multiple (short) columns of unequal lengths

Question

I have a file with one column of data (i.e., one data value per line).  I interpret these data as multiple data sets; the data sets are separated by one or more NA lines.  Note that the data sets are not the same length.  I want to convert this to a multi-column format in which each data set is in a column (in the same order as they appear in the input file).

For example, I have following file (in reality, the file contains much more data):

NA
4
3
5
7
8
3
NA
NA
NA
3
4
5
2
NA
2 
7
4
6
9
NA

My expected output is the following:

4 3 2
3 4 7
5 5 4
7 2 6
8 . 9
3

The point between 8 and 9 is not really needed, but could also be replaced by a space.

Answer 1

You could use awk to split each group of data (where those are between NA lines) into separate files and skipping NA lines, then paste them together.

awk '/^NA$/ && !NA{N++; NA=1; next} !/^NA$/{print >"file"N; NA=0}
    END{system("paste file*")}' inile.txt

The NA flag is used to create files in sequencial order, we could use below instead.

awk '/^NA$/{N++; next} !/^NA$/{print >"file"N}
    END{system("paste file*")}' inile.txt

The output is:

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

Answer 2

That output format doesn't make sense to me. I don't think it's going to be practical at all.

Anyway, just write it per line, then transpose (or better, leave it per line):

tr '\n' ' ' < example | tr 'N' '\n' | sed 's/^A //; /^$/d'

gives

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

Answer 3

Transposing in the shell is a pain in the butt. Here's a short answer with Perl that requires Array::Transpose::Ragged from CPAN

perl -MArray::Transpose::Ragged=transpose_ragged -lnE '
        if (/NA/) { $n++; next }      # next row
        push @{$data[$n]}, $_;        # creating the 2D matrix of data
    } END {
        say join "\t", @$_ for transpose_ragged [grep {defined} @data];
' file

Here's another approach: the pipeline before gawk is essentially the same as @n.caillou's answer, the awk code does the transposing

paste -sd " " file | sed 's/NA/\n/g' | sed '/^ *$/d' | gawk '
    {
        for (i=1; i<=NF; i++) data[FNR][i] = $i
        if (NF > max) max = NF
    }
    END {
        for (i=1; i<=max; i++) {
            for (j=1; j<=NR; j++) printf "%s\t", data[j][i]
            print ""
        }
    }
'

Uses GNU awk for multi-dimensional arrays

Answer 4

The GNU datamash version 1.1.1 is needed. In the version 1.0.7 it doesn't work correctly.

#!/bin/bash

tr '\n' ' ' < input.txt |
sed 's/\s*NA\s*/\n/g;' |
sed '/^$/d'  |
datamash --no-strict --filler="." -W -t' ' transpose

Explanation

1. tr '\n' ' ' < input.txt - replaces all newlines to spaces. In other words, join all lines to the one line.
2. sed 's/\s*NA\s*/\n/g;' - replaces all 'NA' and adjacent spaces to the newline. That is, it splits the big line to few separate lines, each is the future column, written horizontally.
3. sed '/^$/d' - removes all blank lines.
4. datamash --no-strict --filler="." -W -t' ' transpose
   • --no-strict - allow lines with varying number of fields
   • --filler="." - fill missing values with dot. It can be changed to the space.
   • -W - use whitespace (one or more spaces and/or tabs) for input field delimiters.
   • -t' ' - use space instead of TAB as output field delimiter.
   • transpose - converts rows to columns.

Input

NA
4
3
5
7
8
3
NA
NA
NA
3
4
5
2
NA
2
7
4
6
9
NA

Output

4 3 2
3 4 7
5 5 4
7 2 6
8 . 9
3 . .