Is there a way to convert all the columns (space separated) of a file into rows?

Question

I have a file with data

180607 093423   123.12.23.122 133
180607 121234   125.25.45.221 153
190607 084849   202.178.23.4 44
190607 084859   164.78.22.64 12
200607 012312   202.188.3.2 13
210607 084849   202.178.23.4 34
210607 121435   202.178.23.4 32
210607 132423   202.188.3.2 167

I want the out put as :

180607 180607 190607 190607 200607 210607 210607 210607 
093423 121234 084849  084859 012312 084849  121435 132423
123.12.23.122 125.25.45.221 202.178.23.4 164.78.22.64  202.188.3.2 202.178.23.4 202.178.23.4 202.188.3.2
133 153 44 12 13 34 32 167

please suggest

Your output makes no sense. simplfy the problem more to get a reasonable response — NinjaGaiden, Dec 27 '16 at 18:52
Possible duplicate of [Transpose a file in bash](http://stackoverflow.com/questions/1729824/transpose-a-file-in-bash) — Jose Ricardo Bustos M., Dec 27 '16 at 18:53
i see, what he is trying to do now. Basically transpose a file. — NinjaGaiden, Dec 27 '16 at 19:01
Yes its a file transpose I have modified the input data to reflect the correct output , James can your script be modified to give a transpose ? — Vicky, Dec 27 '16 at 19:27

James Brown · Accepted Answer · 2016-12-27T21:16:38.310

5

In awk, the order should be like in the expected output, reversed (EDIT: OP corrected the reversed dataset back to normal after this solution. I left it as-is for educational purposes.):

$ awk '{ for((nf=NF)&&i=1;i<=NF;i++)  # every field of every record
             a[i]=$i OFS a[i] }       # prepend new data to previous data in a
       END {                          # in the end
             for(i=1;i<=nf;i++)       # 
                 print a[i] }' file   # output data
180607 180607 190607 190607 200607 210607 210607 210607 
093423 121234 084849 084859 012312 084849 121435 132423 
123.12.23.122 125.25.45.221 202.178.23.4 164.78.22.64 202.188.3.2 202.178.23.4 202.178.23.4 202.188.3.2 
133 153 44 12 13 34 32 167

If the order should not be reversed but only transpose is desired, change a[i]=$i OFS a[i] to a[i]=a[i] $i OFS (untested).

edited Dec 27 '16 at 21:16

answered Dec 27 '16 at 18:58

James Brown

36,089
7
43
59

1

Thanks james I used this , it worked as well , awk '{ for(i=1;i<=NF;i++) a[i]=a[i]" "$i } END { for(i=1;i<=length(a);i++) print a[i] }' file – Vicky Dec 27 '16 at 20:10
`NF` is guaranteed to retain its value in the `END` section (unlike `$0`, `$1`, etc) so you don't need a separate `nf` variable nor do you need to use `length(a)`. Just use `NF`. – Ed Morton Dec 28 '16 at 00:30
@EdMorton NF will not help when then the number of words in the columns is non consistent e.g add one more words at the end of first column and use NF in the for loop in the END section of awk it will print only first line of transpose out put because for the last row value of NF will be only 1 , in that case length(a) works best. – Vicky Dec 28 '16 at 06:34
The lengths aren't inconsistent though - see the sample you posted. That would obviously be an extremely important point to make in your question if it could happen but you didn't, and the data is clearly regular/meaningful with a pair of numbers then an IP address and a port or similar, not just some random list of numbers that can stop mid-line. If the columns could be different lengths then we'd need you to tell us what separates columns so we could handle that and then the test in the final `for` would be `for (i=1;i in a;i++)` instead of `for (i=1;i<=NF;i++)`. – Ed Morton Dec 28 '16 at 16:30

score 1 · Answer 2 · answered Dec 28 '16 at 01:23

There's a program csvtool available in Ubuntu. Here's a description of what it does:

http://colin.maudry.com/csvtool-manual-page/

in particular you would install via

sudo apt-get install csvtool

and then your problem reduces to

csvtool -t " " -u " " transpose tmp.csv

Is there a way to convert all the columns (space separated) of a file into rows?

2 Answers2