Rearranging columns using awk

Question

I am trying to move 7th column of my csv file to the end by using

awk -F '{print $1,$2,$3,$4,$5,$6,$8,$9,$10,$11,$7}',OFS= "$file"

where $file is a .csv file in a directory. However, the output is

awk:                          ^ syntax error

Does anyone know how to fix this error?

When showing awk errors, you need to show the entire thing. The ^ indicates the specific part of the command where the error was encountered. — terdon, Jul 05 '16 at 13:19

score 15 · Accepted Answer · edited Apr 28 '20 at 12:31

15

The -F option needs an argument (field separator): -F, for example.

The end of the awk script must be separated with a (space char) with the rest of the parameters.

If the field separator is , and you wish to keep it, and if the number of column is constant and lower than or equal to 11, give a try to this:

awk -F, '{print $1,$2,$3,$4,$5,$6,$8,$9,$10,$11,$7}' OFS=, "$file"

If your field separator is a semicolon don't forget to set it in quotes like so

awk -f';' '{print $1,$2,$3,$4,$5,$6,$8,$9,$10,$11,$7}' OFS=';' "$file"

edited Apr 28 '20 at 12:31

sebisnow

105

answered Jul 05 '16 at 13:00

Jay jargot

1,175

8

@anuribs very few programs allow that. The standard way is command file > newfile && mv newfile file. That said, newer version of GNU awk to support this: gawk -i inplace '{blah blah}' file. – terdon Jul 05 '16 at 13:51
1

alternatively, instead of mv newfile file you can use cat newfile > file ; rm -f newfile - this preserves the inode and permissions of file. – cas Jul 06 '16 at 01:15
and it's generally a good idea to use mktemp rather than hard-coding temporary filenames into scripts. e.g. tf=$(mktemp) ; command file > "$tf" ; cat "$tf" > file ; rm -f "$tf" – cas Jul 06 '16 at 01:17

jimmij · Answer 2 · 2016-07-05T15:41:02.807

9

Shorter solution would be

awk -F',+' -v OFS=, '{$(NF+1)=$7; $7=""; $0=$0; $1=$1}1' file

I'm not sure if ,+ will work in all awk versions, but works at least in GNU awk, also with -compatibility mode.

Explanation:

$(NF+1)=$7: first we add 7th field to the end of the line (could be $12=$7 in this case)
$7="": in next step 7th field is erased (but surrounding delimiters stay)
to remove delimiters we need to re-set whole record (via $0=$0) treating multiple commas as field separator (this is done via -F',+', here + means one or more times), and also rearrange current record via $1=$1 to force rebuilding the line using previously set output field separator (set by an option -v OFS=,)
after all shuffling is done we are ready to print the result with 1

Example input:

1,2,3,4,5,6,7,8,9,10,11

output

1,2,3,4,5,6,8,9,10,11,7

edited Jul 05 '16 at 15:41

answered Jul 05 '16 at 13:37

jimmij

47,140

What if other columns are blank? But, yes, FS is a regular expression in POSIX (if it is multiple characters), so ,+ should work. – Random832 Jul 05 '16 at 16:30
(1) I understand that making the seventh column of input data “disappear”, and not just set it to null, is a tricky part of this problem. But, as Random832 says, your solution clobbers blank columns (for example, all,ball,call,,,fall → all,ball,call,fall). (2) $(NF+1)=$7 is a clever approach. IMHO, $0 = $0 OFS $7 is a little clearer, only a couple of characters longer, and it seems to do the same thing. Can you think of a situation in which $0 = $0 OFS $7 doesn’t do the same as your code? – G-Man Says 'Reinstate Monica' Jul 05 '16 at 19:46
@Random832 @G-Man yes, some edge cases like blank fields, blank lines or NF<7 should be treated separately or one should rearrange the code. This is just an idea, not "complete solution" for all general cases, that should be clear. $0=$0 OFS $7 is probably identical to $(NF+1)=$7, but only with the rest of the code unchanged, not in general. – jimmij Jul 05 '16 at 20:31

score 5 · Answer 3 · answered Jul 05 '16 at 13:34

If you're printing with OFS=, so with no separator between the fields, you can simply save the value of $7 in a variable, set $7 to empty and print the line and the variable directly. You don't need to specify all the fields:

$ cat file
1,2,3,4,5,6,7,8
$ awk -F, -vOFS= '{k=$7; $7=""; print $0,k}' file 
12345687

score 3 · Answer 4 · answered Jul 05 '16 at 12:55

3

You probably mean:

awk -F, -v OFS='' '{print $1,$2,$3,$4,$5,$6,$8,$9,$10,$11,$7}' "$file"

answered Jul 05 '16 at 12:55

Michael Vehrs

2,208

You know that awk never sees the single quotes in OFS='', right? You may as well just type OFS=; it's exactly the same. – Wildcard Jul 05 '16 at 23:34
1

Yes, I realize that. However, I dislike dangling assignments. – Michael Vehrs Jul 06 '16 at 04:37
@anuribs See http://stackoverflow.com/questions/16529716/awk-save-modifications-inplace – Barmar Jul 06 '16 at 17:32

Hastur · Answer 5 · 2016-07-05T21:45:47.537

A couple of awk variants (assuming your file is inside the variable $file)

Here you can cycle for all the coloumn, print with the field separator (OFS), and print the record terminator (ORS) at the end of the line.

awk  -F',' -v OFS=,                                \
'{for(i=1;i<=NF;i++) if (i!=7) printf "%s",$i OFS; \
printf "%s",$7;printf ORS}' "$file"

Here with using a regex and the gensub() function
```
gawk -F',+' -v OFS=, '{$0=gensub(/\s*\S+/,"",7) OFS $7}1' "$file"
```
killing the 7^th field and printing it at the end of the line.
- $0 is the whole record
- $n is the n^th record
- NF is the Number of Fields of the current line
- OFS the output filed separator
- ORS the output record terminator
- 1 is the trick to say to awk true and print the default ($0).

Update...

I almost forget, it's possible to shift all the columns following the 7^th one.

awk  -F',' -v OFS=, '{tmp=$7; for(i=7;i<=NF;i++) $i=$(i+1); $NF=tmp}1 ' "$file"

(1) Arguably, OFS $7 would be more robust than "," $7. (2) I believe that ", " $7 is wrong, insofar as the question indicates that the OP doesn’t want spaces after the commas. (And, if the input data had spaces after the commas, then $7 would already begin with a space, and you’d be adding an extra one.) — G-Man Says 'Reinstate Monica', Jul 05 '16 at 19:49
@G-Man It was mainly to propose some ideas, some variants. Thanks, for the spot, I agree about OFS $7, not only more robust, but even more general ( "haste makes waste") — Hastur, Jul 05 '16 at 21:44

score 3 · Answer 6 · answered Jul 05 '16 at 22:06

You did not specifically say you wanted to use awk, and you did say you wanted to use in-place editing like provided by sed -i, so here is a sed -i variant. Usually awk is better for working with columns, but this is one case where I prefer sed, because it naturally handles arbitrary numbers of columns.

MOVECOL=7
N=$((MOVECOL-1))
sed -r -e "s/^(([^,]*,){$N})([^,]*),(.*)/\1\4,\3/" -i test.csv

Explanation:

-r selects extended regexps so we avoid lots of backslashes
first group is $N repetitions of comma-terminated strings, in other words the columns before the one we want to move, with a final comma
second group is the $N-th repeat, we forget about it
third group is the column we want to move, without the final comma
fourth group is composed of all the columns after the one we want to move, with no comma before
we replace with first group, last group, and the column we extracted, inserting the comma as needed.

Of course this will not work with files that hide commas in quotes (or worse, escape them), but awk won't handle that either without some serious acrobatics. If you have that problem you'd be better off with perl module Text:CSV or the python module csv.

Rearranging columns using awk

6 Answers6

Linked