How to sort the columns of a CSV file by the ratio of two columns?

awk, bash

Solution

awk 'BEGIN { FS = OFS = ","} {$4 = $2/$3; print}' | sort -k4,4nr -t, | sed 's/,[^,]*$//' inputfile

or using GNU AWK (`gawk`):

awk -F, '{a[$3/$2] = $3/$2; b[$3/$2] = $0} END {c = asort(a); for (i = 1; i <= c; i++) print b[a[i]]}' inputfile

The methods above are better than the following, but this is more efficient than another answer which uses Bash and various utilities:

while IFS=, read animal dividend divisor
do
    quotient=$(echo "scale=4; $dividend/$divisor" | bc)
    echo "$animal,$dividend,$divisor,$quotient"
done < inputfile | sort -k4,4nr -t, | sed 's/,[^,]*$//'

As a one-liner:

while IFS=, read animal dividend divisor; do quotient=$(echo "scale=4; $dividend/$divisor" | bc); echo "$animal,$dividend,$divisor,$quotient"; done < inputfile | sort -k4,4nr -t | sed 's/,[^,]*$//'

Problem

I have a CSV file like this: ``` bear,1,2 fish,3,4 cats,1,5 mice,3,3 ``` I want to sort it, from highest to lowest, by the ratio of column 2 and 3. E.g.: ``` bear,1,2 # 1/2 = 0.5 fish,3,4 # 3/4 = 0.75 cats,1,5 # 1/5 = 0.2 mice,3,3 # 3/3 = 1 ``` This would be sorted like this: ``` mice,3,3 fish,3,4 bear,1,2 cats,1,5 ``` - How can I sort the columns from highest to lowest by the ratio of the two numbers in column 2 and 3?

Original source