-1

In the text:

35 EAST 23rd Street           SOUTH AFRICA   5    600   5000000   6 
83 NORTH YELLOWLIGHT AVENUE   SOUTH AFRICA   4    700   7000000   5 
777 NEW AVENUE                SAUDIA         2    900   5000000   3 
FIVE VISA ROAD                MEXICO         3    300    500000   7 
450 JACKSON BLVD              USA            3   1500    300000   4 
25 QUEENS ROAD SOUTH          SOUTH AFRICA   1    900    400000   3

How do I use gawk or awk and sort to make sure that only South Africa come as the output in ascending order of the second to the last column?

I've tried :

awk -F. '/SOUTH AFRICA/ {print }' | sort -n -k5  

but it doesn't seem to work.

  • 1
    Please provide your expected output. – Sparhawk Dec 7 '15 at 1:22
4

The short answer is, you need to fix the original input first, it was inconsistent to begin with, thus causing problems later when you try running commands.

Here are the details of how to fix this:

Examine what you posted

First, let's see the problem. For example, if we copy what you posted and saved it as original_file.tsv:

35 EAST 23rd Street     SOUTH AFRICA        5   600 5000000     6 
83 NORTH YELLOWLIGHT AVENUE SOUTH AFRICA    4   700 7000000     5 
777 NEW AVENUE  SAUDIA      2   900 5000000     3 
FIVE VISA ROAD      MEXICO      3   300 500000      7 
450 JACKSON BLVD        USA     3   1500    300000      4 
25 QUEENS ROAD SOUTH        SOUTH AFRICA        1   900 400000      3

It looks neat and proper at first glance. Unfortunately, when we take a closer look using cat -A, like this, you will see on your command prompt:

$ cat -A original_file.tsv
35 EAST 23rd Street^I^ISOUTH AFRICA^I^I5^I600^I5000000^I^I6 $
83 NORTH YELLOWLIGHT AVENUE^ISOUTH AFRICA^I4^I700^I7000000^I^I5 $
777 NEW AVENUE^ISAUDIA^I^I2^I900^I5000000^I^I3 $
FIVE VISA ROAD^I^IMEXICO^I^I3^I300^I500000^I^I7 $
450 JACKSON BLVD^I^IUSA^I^I3^I1500^I300000^I^I4 $
25 QUEENS ROAD SOUTH^I^ISOUTH AFRICA^I^I1^I900^I400000^I^I3$
  • the ^I means "there is a tab here"
  • the $ means "this is the end of this line"

This reveals inconsistencies right away, for example:

  • line 1: 35 EAST 23rd Street tab tab SOUTH AFRICA ...
  • line 2: 83 NORTH YELLOWLIGHT AVENUE tab SOUTH AFRICA ...

One line has two tabs separating field 1 and field 2, the next line has only one tab. It is not the same per line.

But you cannot properly sort data here when each line is so different, if there are inconsistent arrangements of separators or delimiters on each line.

Cleaned up version

It looks like the only problem, at least given this sample, is the appearance of double tabs when in fact it should be single tab. So instead of manually editing to clean this up, we always should try to use tools where possible. Here we can use sed to clean it up, and save the results to a file, for example we can call the results clean_file.tsv:

$ sed 's/\t\t/\t/g;s/ $//g' original_file.tsv  > clean_file.tsv
  • s/\t\t/\t/g searches two tabs and replaces it with one
  • ; to separate multiple commands within sed argument
  • s/ $//g because there seems to be a trailing space at some end of lines so here this removes it
  • > file redirect to save the sed output to a file, which we call clean_file.tsv

clean_file.tsv looks like:

35 EAST 23rd Street SOUTH AFRICA    5   600 5000000 6
83 NORTH YELLOWLIGHT AVENUE SOUTH AFRICA    4   700 7000000 5
777 NEW AVENUE  SAUDIA  2   900 5000000 3
FIVE VISA ROAD  MEXICO  3   300 500000  7
450 JACKSON BLVD    USA 3   1500    300000  4
25 QUEENS ROAD SOUTH    SOUTH AFRICA    1   900 400000  3

We can double check that it is consistent, again using cat -A but now on our clean_file.tsv:

35 EAST 23rd Street^ISOUTH AFRICA^I5^I600^I5000000^I6$
83 NORTH YELLOWLIGHT AVENUE^ISOUTH AFRICA^I4^I700^I7000000^I5$
777 NEW AVENUE^ISAUDIA^I2^I900^I5000000^I3$
FIVE VISA ROAD^IMEXICO^I3^I300^I500000^I7$
450 JACKSON BLVD^IUSA^I3^I1500^I300000^I4$
25 QUEENS ROAD SOUTH^ISOUTH AFRICA^I1^I900^I400000^I3$

We see indeed everything is now consistent, if we were to carefully count them, there are now the same number of fields (here, 6), and same number of tabs (here, 5) as field separators or field delimeters, on each line.

Awk, Sort

Now that we have clean_file.tsv being an input that is properly formatted, we can now run the command, and see:

$ awk '/SOUTH AFRICA/ {print }' clean_file.tsv | sort -t $'\t' -k5,5n
25 QUEENS ROAD SOUTH    SOUTH AFRICA    1       900     400000  3
35 EAST 23rd Street     SOUTH AFRICA    5       600     5000000 6
83 NORTH YELLOWLIGHT AVENUE     SOUTH AFRICA    4       700     7000000 5
  • the -F is removed compared to your original commands, because -F is for specifying field separator, not needed here since awk is not doing any field manipulation, and is only being used here to print lines that match SOUTH AFRICA in them
  • -t $'\t' specifies the field separator to be tab.
  • -k5,5 to sort from column 5, to column 5, because in this case you wanted second-to-last and column 5 here is second-to- last in this 6-column data sample
  • n means numeric sort. Since default is ascending order, we don't need to specify anything further.

So by cleaning the original data and running this awk and sort, you will now be able to find the SOUTH AFRICA entries and sort them by the fifth field, ascending.

1

There are two basic things wrong here:

  1. The content as you gave it has no field separators, so "column 5" isn't meaningful. You'll have to edit the data (using, for example, a semicolon between columns) to make the notion of "column" meaningful. I usually use tabs as column separators, but you can use any character that doesn't appear in the data.
  2. You need to tell sort what the field separator is.

Once you've edited the data, you could do what I think you're asking for like this:

% grep "SOUTH AFRICA" file | sort "-t;" -k5n

(presuming that "file" contains your data). That gives the following output:

25 QUEENS ROAD SOUTH;SOUTH AFRICA;1;900;400000;3
35 EAST 23rd Street SOUTH AFRICA;5;600;5000000;6 
83 NORTH YELLOWLIGHT AVENUE;SOUTH AFRICA;4;700;7000000;5 

If you want to make sure that SOUTH AFRICA is only in the second column, you can do it like this:

% awk '-F;' '$2 == "SOUTH AFRICA"' file | sort '-t;' -k5n

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.