How can I stack multiple (>1000) fields into one continuous field?

Hi,

I'm struggling with a problem at the minute. Basically, I have a file with >1000 columns / fields (with headers), each containing a variable number of values. I would like to stack each column such that all the data appears in a single column, e.g. the first value of column 2 is moved to below the last value of column 1, and then followed by all other values in the column; the first value of column 3 is moved to below this new last value in column 1, and then followed by all other values in the column etc.

As I said, there are over 1000 columns, and the max number of values in any one is around 3500. I have played around with various cut / print / awk commands but not found anything that does what I want. Having said that, I just started working in Unix yesterday, so no idea if these actually can help. Any help would be greatly appreciated!

Thanks a lot

Tom

If your input is tab-separated:

awk -F"\t" '{ if(NF>M) M=NF; for(N=1; N<=NF; N++) if($N) D[N,++C[N]]=$N } END { for(N=1; N<=M; N++) for(X=1; X<=C[N]; X++) print D[N,X] }' inputfile > outputfile
4 Likes

Let me replay this a bit.
Pretend we have :

1 2 3 4 5
A B C E
X Y Q M

You want this be stacked all into single column:

1
2
3
4
5
A
B
C
E
X
Y
Q
M

Right?
Try this

awk '{ for (i=1 ; i<NF; i++) 
            {
               printf("%s\n", $(i) );
            } 
          } ' inputfilename  > outputfilename

Running the code

Owner@Owner-PC ~
$ mv infile inputfilename

Owner@Owner-PC ~
$ ./t.awk

Owner@Owner-PC ~
$ cat outputfilename
1
2
3
4
A
B
C
X
Y
Q

$ cat t.awk

awk '{ for (i=1 ; i<NF; i++)
            {
               printf("%s\n", $(i) );
            }
          } ' inputfilename  > outputfilename

Owner@Owner-PC ~

$ ls -l t.awk
$ ls -l t.awk
-rwxr-xr-x 1 Owner None 143 May  9 13:56 t.awk

You have to chmod +x t.awk to make it an executable script

1 Like

Thanks a lot Corona688, that has done exactly what I wanted!

Jim, that's not quite the format I wanted. If I have:

1 2 3 4 5 

A B C E 

X Y Q M

What I actually want is:

1
A
X
2
B
Y
3
C
Q
4
E
M
5

Corona's script sorted this though, so all good now :slight_smile:

1 Like
$ awk ' { for(i=1;i<=NF;i++) print i, $i } ' file | sort | awk ' { print $2 } '
1
A
X
2
B
Y
3
C
Q
4
E
M
5
1 Like

Hi.

Assume z3:

1       2       3       4       5
A       B       C       E
X       Y       Q       M

With invisibles shown:

1^I2^I3^I4^I5$
A^IB^IC^IE$
X^IY^IQ^IM$

Then:

transpose.pl z3 | tr '\t' '\n'

produces:

1
A
X
2
B
Y
3
C
Q
4
E
M
5

On a system like:

OS, ker|rel, machine: Linux, 3.16.0-7-amd64, x86_64
Distribution        : Debian 8.11 (jessie) 
bash GNU bash 4.3.30

And some details on transpose.pl:

transpose.pl    Swap rows and columns in the given tab-delimited table (MR). (what)
Path    : ~/bin/transpose.pl
Version : - ( local: RepRev 1.1, ~/bin/transpose.pl, 2017-01-29 )
Length  : 28 lines
Type    : Perl script, ASCII text executable
Shebang : #!/usr/bin/perl
Home    : http://www1.cuni.cz/~obo/textutils/ (doc)

Best wishes ... cheers, drl