# Sort with unequal whitespace in first column

I need to sort this list by name, high temp and low temp:

Kuala Lumpur 78 56
Seoul 85 66
Karachi 95 75
Tokyo 85 60
Lahore 85 75
Manila 90 85


I figured since whitespace is the delimiter for a column I could just sort -k 1 which gives me this:

Karachi         95 75
Kuala Lumpur    78 56
Lahore          85 75
Manila          90 85
Seoul           85 66
Tokyo           85 60


But the "Kuala Lumpur" is causing problems because of the space.

So I tried to treat "Lumpur" as a column and to sort the first set of nums I did

sort -k 3n


but I get this:

Tokyo           85 60
Seoul           85 66
Karachi         95 75
Lahore          85 75
Kuala Lumpur    78 56 <---Why is this out of order?
Manila          90 85


How do I deal with this one space?

-
How is the file generated? You should use a different separator. –  Bernhard Apr 1 '13 at 19:11
the file was generated with cat. What different separator? I was thinking of using tr and removing the space –  healix Apr 1 '13 at 19:12
A comma , would be a decent separator here. I don't see how cat would generate this file. There is something behind, right? –  Bernhard Apr 1 '13 at 19:14
Could you post the desired output? Sorting alphabetically seems to work in your example. The only problem seems to be sorting by temp, is that correct? –  terdon Apr 1 '13 at 19:15
@terdon Correct. When the temp is sorted the "Kuala Lumpur" doesn't sort properly. –  healix Apr 1 '13 at 19:18

As others have commented, it will make it easier to work with the data if it is comma separated values (CSV).

Here is my solution for converting the data to CSV:

$cat file | sed 's/ $$[0-9]$$/,\1/g' Kuala Lumpur,78,56 Seoul,85,66 Karachi,95,75 Tokyo,85,60 Lahore,85,75 Manila,90,85  It replaces any space preceding a digit with a comma. \1 references the group ([0-9]), the digit after the space. From there you can use sort with the -t argument to specify a field separator. $ cat file | sed 's/ $$[0-9]$$/,\1/g' | sort -t, -k2
Kuala Lumpur,78,56
Tokyo,85,60
Seoul,85,66
Lahore,85,75
Manila,90,85
Karachi,95,75


If you'd like to convert back to spaces or make a table, here are two examples:

$cat test | sed 's/ $$[0-9]$$/,\1/g' | sort -t, -k2 | tr , ' ' Kuala Lumpur 78 56 Tokyo 85 60 Seoul 85 66 Lahore 85 75 Manila 90 85 Karachi 95 75$ cat test | sed 's/ $$[0-9]$$/,\1/g' | sort -t, -k2 | column -s, -t
Kuala Lumpur  78  56
Tokyo         85  60
Seoul         85  66
Lahore        85  75
Manila        90  85
Karachi       95  75

-

If you can change your file to have tab separated columns, your life will be easier. If changing the file is not an option, this Perl one-liner will do it for you:

perl -ne 's/\s+/\t/g; s/([a-z])\s([a-z])/$1$2/ig; s/\t$/\n/; print;' file | sort -t$'\t' -nk3
Kuala Lumpur    78  56
Tokyo   85  60
Seoul   85  66
Karachi 95  75
Lahore  85  75
Manila  90  85


EXPLANATION:

• s/\s+/\t/g : change ALL spaces to TABs.
• s/([a-z])\s([a-z])/$1$2/ig : change TABs that are between two letters (no numbers) back to single spaces.
• s/\t$/\n/ : the first substitution introduces a TAB at the end of each line, change that back to a newline character (\n). • sort -t$'\t' -nk3 : use TAB as a delimiter (sort has a strange syntax there, I know, see here for more information) and sort numerically on the third column.

-
awk '{print $NF,$0}' file.txt | sort -nr -k1 | cut -d' ' -f2-

• $NF: number of fields, $0: whole line
• sort -nr: numeric reverse (descending)
• sort -k1: sort by first column (delimited by sequences of spaces and tabs)
• cut -d: delimiter (defaults to tab)
• cut -f2-: fields 2 to last (doesn't collapse or strip delimiters)
ruby -e 'puts readlines.sort_by{|l|l.split[-1].to_i}.reverse' file.txt

• readlines = ARGF.readlines
• split splits on whitespace by default
-
+1 for the gawk one, should have thought of that myself. Still, please add some information on what these commands actually do. –  terdon Apr 1 '13 at 23:35
@terdon I edited the answer. –  ؘؘؘؘ Apr 2 '13 at 0:14