4

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.

  • 1
    Please specify 1. if the NA lines have to be discarded (why mention them then?) 2. what made you decide to stop the first column and start the next one at the 7th number, why not the 6th one (and you would not need a dot) – xhienne Sep 15 '17 at 21:17
  • 1) yes Na have to be discarded. That is how my file looks like – Froelicher Sep 15 '17 at 21:33
  • 2) always stop a column when Na appears – Froelicher Sep 15 '17 at 21:33
  • Are the values always a single digit? – seshoumara Sep 17 '17 at 0:46
2

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
1

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
1

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

0

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 . .

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.