Issues with OFS and sort working together in Bash

user_9219726 0
Viewed 58

Given this type of input:

1,Name1,Type1,100,50
2,Name2,Type2,85,80
3,Name3,Type3,95,92
4,Name4,Type4,60,55
5,Name5,Type5,87,77

I want to calculate the average of the last 2 values and then sort them in decreasing order, so I wrote this bash code:

awk 'BEGIN{FS=","} {avg=($4+$5)/2;print $1,$3,avg}' | sort -k3 -nr

which gives me this output which is very close to my expected output:

3 Type3 93.5
2 Type2 82.5
5 Type5 82
1 Type1 75
4 Type4 57.5

The final thing I want is to separate the output with | (pipes), so I use the variable OFS like this:

awk 'BEGIN{FS=",";OFS="|"} {avg=($4+$5)/2;print $1,$3,avg}' | sort -k3 -nr

The output from this:

5|Type5|82
4|Type4|57.5
3|Type3|93.5
2|Type2|82.5
1|Type1|75

It seems like OFS is breaking the sort. Is this behaviour expected? Is there any workaround for this?

awk
3 Answers

There are 2 issues in your shown code attempt. First is Input_file name is not passed in awk code(could be typo), 2nd is you need to set delimiter in sort by using -t'|' option so it will be like:

awk 'BEGIN{FS=",";OFS="|"} {avg=($4+$5)/2;print $1,$3,avg}' Input_file | sort -t'|' -k3 -nr
3|Type3|93.5
2|Type2|82.5
5|Type5|82
1|Type1|75
4|Type4|57.5

OR in a non-one liner form of code + removing avg variable you could get average of columns during printing of itself(in case you are using avg variable further any where in program then you could create it):

awk '
BEGIN{
  FS=","
  OFS="|"
}
{
  print $1,$3,($4 + $5)/2
}' Input_file | 
sort -t'|' -k3 -nr

From man sort page:

-t, --field-separator=SEP use SEP instead of non-blank to blank transition

user_58665800

Some more way,actually you can also use awk's print with sort :

$ cat testfile.csv 
1,Name1,Type1,100,50
2,Name2,Type2,85,80
3,Name3,Type3,95,92
4,Name4,Type4,60,55
5,Name5,Type5,87,77

$ awk 'BEGIN{FS=",";OFS="|"}{print $1,$3,($4+$5)/2 | "sort -t \"|\" -nrk3"}' testfile.csv 
3|Type3|93.5
2|Type2|82.5
5|Type5|82
1|Type1|75
4|Type4|57.5
user_31619930

Using GNU awk's controlling array traversal feature:

gawk  '
  BEGIN { FS = ","; SUBSEP = "|" }
  { avg = ($4+$5)/2; result[$1,$3,avg] = avg }
  END {
    PROCINFO["sorted_in"] = "@val_num_desc"
    for (line in result) print line
  }
' testfile.csv

3|Type3|93.5
2|Type2|82.5
5|Type5|82
1|Type1|75
4|Type4|57.5

SUBSEP is the variable that holds the join string for comma-separated array keys. It's default value is octal 034, the "FS" character.

user_75520
Related