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-27 13:38:45Z

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, 2019 at 13:38

answered Mar 27, 2019 at 10:26

anubhava

790k67 gold badges603 silver badges671 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

sander Over a year ago

Hi, your first solution using awk works. I don't know why you deleted it but it was the correct solution. Thanks!

anubhava Over a year ago

No I didn't delete Awk solution. Just formatted it to make it more redable.

kvantour · Accepted Answer · 2019-03-27 10:44:27Z

1

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.

edited Mar 27, 2019 at 10:44

answered Mar 27, 2019 at 10:20

kvantour

26.9k4 gold badges57 silver badges81 bronze badges

1 Comment

kvantour Over a year ago

@anubhava you are correct. I added an ugly fix for it.

Collectives™ on Stack Overflow

Sort numeric values except text in Bash

2 Answers 2

2 Comments

1 Comment

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

2 Comments

1 Comment

Your Answer

Sign up or log in

Post as a guest

Linked

Related