Sort numeric values except text in Bash

Question

I have a csv file called prices.csv that looks like this:

Name, Price, Description
Apple, 2.85, fruit
Kiwi, 1.96, fruit
Banana, 0.99, fruit
Peach, Not available, fruit
Orange, 2.02, fruit

I would like to sort the second column (Price) in ascending order, except for the values that are 'Not available', these should be placed at the bottom.

What I've done so far is this:

sort -t, -k2,2 -n prices.csv > ordered_prices.csv

This creates the following file:

Name, Price, Description
Peach, Not available, fruit
Banana, 0.99, fruit
Kiwi, 1.96, fruit
Orange, 2.02, fruit
Apple, 2.85, fruit

As you can see this places the products with price 'Not available' at the top instead of at the bottom. How do I place the text at the bottom with generic code?

anubhava · Accepted Answer · 2019-03-27T13:38:45.750

2

If you have gnu-awk then you can do this using PROCINFO:

awk -F ', ' 'NR == 1 {
   print
   next
}
$2+0 == $2 {
   a[NR] = $2
   rec[NR] = $0
   next
}
{
   rest = rest $0 RS
}
END {
   PROCINFO["sorted_in"] = "@val_num_asc"
   for (i in a)
      print rec[i]
   printf "%s", rest
}' file

Name, Price, Description
Banana, 0.99, fruit
Kiwi, 1.96, fruit
Orange, 2.02, fruit
Apple, 2.85, fruit
Peach, Not available, fruit

Alternatively you can use head + tail + sort command like this:

head -n 1 file && sort -t, -k2V <(tail -n +2 file)

edited Mar 27 '19 at 13:38

answered Mar 27 '19 at 10:26

anubhava

761,203
64
569
643

1

Hi, your first solution using awk works. I don't know why you deleted it but it was the correct solution. Thanks! – sander Mar 27 '19 at 11:11
No I didn't delete Awk solution. Just formatted it to make it more redable. – anubhava Mar 27 '19 at 11:13

kvantour · Answer 2 · 2019-03-27T10:44:27.747

You might consider using version sort instead of a numeric sort:

$ sort -t, -k2,2V  prices.csv > ordered_prices.csv

More information about version sorting can be found here. Note that this will move your header to the back. You could use this method:

$ OUTPUTFILE=outputfile
$ awk -v out="$OUTPUTFILE" '(NR==1){print > out; close(out)}(NR>1)' inputfile \
  | sort -t, -k2,2V > $OUTPUTFILE

But that is super ugly and at this point I would switch to the solution of Anubhava.

Another way you could do this is by doing a ridiculous substitution:

$ sed '2,$s/\([[:alpha:]]\+\)/999999\1/g' | sort -t, -k2n | sed 's/999999//g'

which keeps the header in place.

@anubhava you are correct. I added an ugly fix for it. – kvantour Mar 27 '19 at 10:46 — kvantour, Mar 27 '19 at 10:46

Sort numeric values except text in Bash

2 Answers2