rearrange columns using awk or cut command

Question

I have large file with 1000 columns. I want to rearrange so that last column should be the 3rd column. FOr this i have used,

cut -f1-2,1000,3- file > out.txt

But this does not change the order.

Could anyone help using cut or awk?

Also, I want to rearrange columns 10 and 11 as shown below:

Example:

1   10   11   2   3   4   5   6   7   8   9   12  13  14  15  16  17  18  19  20

Are you trying to insert the final column between the original 2nd and 3rd columns so the 3rd column becomes the 4th, or are you trying to replace the 3rd column? Provide some sample input and expected output (and, of course, using 5 or 6 columns, not 1000!). — Ed Morton, Jul 29 '13 at 13:55
Possible duplicate of [Rearrange columns using cut](https://stackoverflow.com/questions/2129123/rearrange-columns-using-cut) — underscore_d, Mar 22 '18 at 10:47

score 10 · Accepted Answer · edited Jul 16 '23 at 11:35

10

try this awk one-liner:

awk '{$3=$NF OFS $3;$NF=""}7' file

this is moving the last col to the 3rd col. if you have 1000, then it does it with 1000th col.

EDIT

if the file is tab-delimited, you could try:

awk -F'\t' -v OFS="\t" '{$3=$NF OFS $3;$NF=""}7' file

EDIT2

add an example:

kent$  seq 20|paste -s -d'\t'                              
1   2   3   4   5   6   7   8   9   10  11  12  13  14  15  16  17  18  19  20

kent$  seq 20|paste -s -d'\t'|awk -F'\t' -v OFS="\t" '{$3=$NF OFS $3;$NF=""}7'
1   2   20  3   4   5   6   7   8   9   10  11  12  13  14  15  16  17  18  19

EDIT3

You didn't give any input example. so assume you don't have empty columns in original file. (no continuous multi-tabs):

kent$  seq 20|paste -s -d'\t'|awk -F'\t'  -v OFS="\t" '{$3=$10 FS $11 FS $3;$10=$11="";gsub(/\t+/,"\t")}7'
1       2       10      11      3       4       5       6       7       8       9       12      13      14      15      16      17      18      19      20

After all we could print those fields in a loop.

edited Jul 16 '23 at 11:35

Benjamin Loison

3,782
4
16
33

answered Jul 29 '13 at 11:33

Kent

189,393
32
233
301

Thanks. the original file is tab separated and with this command the delimiter is changing. What should be done to retain tab as the delimiter. – chas Jul 29 '13 at 11:38
thanks. but it is printing the last column twice. i.e. in the 3rd column and also in the last column. It is required only once – chas Jul 29 '13 at 11:44
@user1779730 I cannot see it happen. check my EDIT2 with an example. – Kent Jul 29 '13 at 12:00
@user1779730 I don't get you. my command gives output in "EDIT2" – Kent Jul 30 '13 at 08:37
@user1779730 where is EDIT3??? what do you want? I didn't write `$1 $2 $3 $4...$20`. did you check the code in EDIT2? Your question is move last col to before the original 3rd col. my command (awk) does it, EDIT2 shows an example. what's your problem? if your requirement changes, edit your question. or create a new question. – Kent Jul 30 '13 at 08:46
@user1779730 I see. you edited my answer to add new requirement. don't do it. edit your question pls. – Kent Jul 30 '13 at 08:48
@user1779730 see EDIT3. – Kent Jul 30 '13 at 09:01
I notice you have a number after the bracket statement. Would I be correct to assume that's a shorthand for "print $0" as the last command in the program? Is there any significance to which number is used? – John Chesshir Oct 17 '17 at 13:58
you are correct. a non-zero number will do awk's default action: `print` @JohnChesshir – Kent Oct 17 '17 at 14:25
this is beautiful. – jimh Apr 30 '20 at 04:01

score 2 · Answer 2 · edited Jul 16 '23 at 11:35

2

I THINK what you want is:

awk 'BEGIN{FS=OFS="\t"} {$3=$NF OFS $3; sub(OFS "[^" OFS "]*$","")}1' file

This might also work for you depending on your awk version:

awk 'BEGIN{FS=OFS="\t"} {$3=$NF OFS $3; NF--}1' file

Without the part after the semi-colon you'll have trailing tabs in your output.

edited Jul 16 '23 at 11:35

Benjamin Loison

3,782
4
16
33

answered Jul 29 '13 at 14:00

Ed Morton

188,023
17
78
185

1

I think we need the `OFS` var. because without the `OFS` when we set `$3=xxx` and output the line, awk will use the default `OFS`(space). It will change the original file format. but OP didn't give an example output. .. btw, I like the `NF--` very clever. Master! – Kent Jul 30 '13 at 08:54

score 0 · Answer 3 · edited Jul 16 '23 at 11:36

Since many people are searching for this and even the best awk solution is not really pretty and easy to use I wanted to post my solution (mycut) written in Python:

#!/usr/bin/env python3

import sys
from signal import signal, SIGPIPE, SIG_DFL
signal(SIGPIPE,SIG_DFL)

#example usage: cat file | mycut 3 2 1

columns = [int(x) for x in sys.argv[1:]]
delimiter = "\t"

for line in sys.stdin:
    parts = line.split(delimiter)

    print("\t".join([parts[col] for col in columns]))

I think about adding the other features of cut like changing the delimiter and a feature to use a * to print the remaning columns. But then it will get an own page.

score 0 · Answer 4 · edited Jul 16 '23 at 11:36

A shell wrapper function for awk' that uses simpler syntax:

# Usage: rearrange int_n [int_o int_p ... ] < file
rearrange () 
{ 
    unset n;
    n="{ print ";
    while [ "$1" ]; do
        n="$n\$$1\" \" ";
        shift;
    done;
    n="$n }";
    awk "$n" | grep '\w'
}

Examples...

echo foo bar baz | rearrange 2 3 1
bar baz foo

Using bash brace expansion, rearrange first and last 5 items in descending order:

echo {1..1000}a | tr '\n' ' ' | rearrange {1000..995} {5..1}
1000a 999a 998a 997a 996a 995a 5a 4a 3a 2a 1a

Sorted 3-letter shells in /bin:

ls -lLSr /bin/?sh | rearrange 5 9 
150792 /bin/csh 
154072 /bin/ash 
771552 /bin/zsh 
1554072 /bin/ksh

rearrange columns using awk or cut command

4 Answers4

Linked

Related