Shell – Two files with a common field – is it possible to sort one based on the other

awkcommand lineshellsorttext processing

Lets say I got two files with a common field. I want to sort the first one based on a field (eg. a numeric field) found in this file… but I want this re-ordering to apply to the other file as well – through the common field.

To take an example — completely grabbed out of thin air 😉 — lets take the /etc/passwd and /etc/shadow:

/etc/passwd:
(...)
sshd:x:124:65534::/var/run/sshd:/usr/sbin/nologin
sndiod:x:999:29::/var/lib/sndiod:/usr/sbin/nologin
dictd:x:125:135:Dictd Server,,,:/var/lib/dictd:/bin/false
postgres:x:126:136:PostgreSQL administrator,,,:/var/lib/postgresql:/bin/bash
(...)

/etc/shadow:
(...)
sshd:*:17055:0:99999:7:::
sndiod:!:17055::::::
dictd:*:17055:0:99999:7:::
postgres:*:17055:0:99999:7:::
(...)

Sorting /etc/passwd numerically on UID is easy enough:

sort -n -t: -k3 /etc/passwd > pw

Which with the lines above yields:

/etc/passwd:
(...)
sshd:x:124:65534::/var/run/sshd:/usr/sbin/nologin
dictd:x:125:135:Dictd Server,,,:/var/lib/dictd:/bin/false
postgres:x:126:136:PostgreSQL administrator,,,:/var/lib/postgresql:/bin/bash
sndiod:x:999:29::/var/lib/sndiod:/usr/sbin/nologin
(...)

However, /etc/shadow does not have a numeric UID-field I can sort after… It does however — like /etc/passwd — got a username-field…

So is there a way to re-order the lines in /etc/shadow so that the username-field had the same order as the username-field in the numerically sorted /etc/passwd?

Is there a command — sort or something similar — which can sort file A after some key in that file, and simultaneously sort file B on a common field shared by both files?
Alternatively, is there a suitable two-step-process — a command, a sed/AWK/Perl-script, or something — that can use the sorted file A to re-order file B after their common field?

Best Answer

Join the files, sort the combined file, and strip out the columns you don't want.

In this case, field 1 of /etc/passwd is to be joined with field 1 of /etc/shadow, with : as the field separator.

join -t : -1 1 -2 1 /etc/passwd /etc/shadow |
sort -t : -k 3,3n |
cut -d : -f 1,8-

Related Solutions

Text Processing Sort – Sort Based on Field1 and Field2 Ignoring Nulls in Field2

You need to specify where sort keys end. Otherwise they end at the end of the line.

And to apply numeric sort to one key only, that's with n appended to the key spec. -n alone would turn numeric sort on globally.

sort -t'|' -k1,1 -k2,2n

Join two files based on a common field

There are two things you need to do to get things working the way you want. First of all, by appending > File 3 to the end of the command, you are redirecting the output to File and also adding the extra argument 3 to the join command.

To explain why this happens, I recently (and coincidentally!) answered a similar question at Strange behaviour redirecting man pages. Basically, it has to do with Bash grammar, which you can read more about in man bash.

To fix that behaviour, you should either ensure there are no spaces in the output file, or you should escape the space, or you should surround the filename with quotation marks. Any of the following redirections would work:

... > File3
... > File\ 3
... > "File 3"

Now to address the missing records from the output. This is handled by the -a flag. From man join:

-a FILENUM
       also print unpairable lines from file FILENUM, where FILENUM is 1 or 2, corresponding to FILE1 or FILE2

Putting it all together, the following command works for me (I will use tee rather than a redirection to show the output):

$ join -t: -1 1 -2 4 -o '1.1 1.2 1.3 1.4 2.1 2.2 2.3' -a 1 File1 <(sort -t: -k4 File2) | tee "File 3"
C01:Nancy:Jones:njones@abc.com :R001:07/04/15:123.45
C02:Barbara:Madison:bmadison@bcd.com :R005:11/27/16:58.00
C03:Tim:Adams:tadams@cde.com:R002:12/20/15:167.50
C03:Tim:Adams:tadams@cde.com:R005:09/15/16:36.50
C04:Sarah:Moore:smoore@def.com:::
C05:John:Polk:jpolk@efg.com:::
C06:Paula:Jacobs:pjacobs@fgh.com:R003:03/14/16:298.00

Best Answer

Related Solutions

Text Processing Sort – Sort Based on Field1 and Field2 Ignoring Nulls in Field2

Join two files based on a common field

Related Question