1

I'd like to combine the lines from two sorted files, not necessarily of the same length but with the same data fields and with the same header, starting after a maintained header based on the order of a particular column. For example, file 1 is:

header 1
header 2
header 3

cat    4    aa
dog    5    ab
ostrich    10    cd
fish    13    cc

and file 2 is:

header 1
header 2
header 3

lemur    3    dd
alligator    4    ca
lemming    16    ad

and I'd like to 1) keep the identical header but 2) sort the following lines based on column 2. The output I'd like is:

header 1
header 2
header 3

lemur    3    dd
cat    4    aa
alligator     4    ca
dog    5    ab
ostrich    10    cd
fish    13    cc
lemming     16    ad

I've looked but couldn't find a solution for exactly this situation using awk or join.

1

awk and join are the wrong tools for this.

sed '/^$/q' file1; sort -snmk2,2 <(sed '1,/^$/d' file1) <(sed '1,/^$/d' file2)
  • Ah, that explains a lot. Unfortunately, this doesn't keep the header intact. In addition, would you mind explaining the arguments? Thank you! – itf Dec 1 '16 at 5:33
  • Why do you think it doesn't keep the header intact? It does for me, the sed '/^$/q' prints it. – jthill Dec 22 '16 at 0:35
  • Does this notice that the files are seekable already sorted, or does it take O(n log(n)) time and O(n) memory even though it can be done in linear time and constant memory? – Daniel H Jun 27 '17 at 20:46
  • @DanielH The m option means merge, it's one pass. – jthill Jun 27 '17 at 21:29
  • @jthill Oh, that makes much more sense. I got in confused with -M; it seemed weird to include it here but not enough that I looked more closely at the man page to see I’d read it wrong. – Daniel H Jun 27 '17 at 22:39
1

With a modern (version > 4.0) of GNU awk, you could do

awk '
  FNR>4 {a[$0]=$2; next}; 
  NR==FNR; 
  END {
    PROCINFO["sorted_in"] = "@val_num_asc"; 
    for (i in a) print i;
  }
' file1 file2

Explanation:

  • FNR>4 {a[$0]=$2; next}; create an array of the sort fields of non-header lines
  • NR==FNR; evaluates TRUE for the first file only, and is only reached for FNR>4, causing header lines to be printed for the first file
  • PROCINFO["sorted_in"] = "@val_num_asc" sort the array by value (i.e. stored field $2)
  • for (i in a) print i print the indices of the sorted array (which are the stored non-header lines)

Testing

$ awk 'FNR>4 {a[$0]=$2; next}; NR==FNR; END {PROCINFO["sorted_in"] = "@val_num_asc"; for (i in a) print i;}' file1 file2
header 1
header 2
header 3

lemur    3    dd
cat    4    aa
alligator    4    ca
dog    5    ab
ostrich    10    cd
fish    13    cc
lemming    16    ad
0

Using a shell that has process substitutions (ksh93, bash, ...) (see end for a process substitution-free alternative):

cat <( head -n 3 file1 ) \
    <( sort -k2,2n <( tail -n +4 file1 | tr -s ' ' '\t' ) \
                   <( tail -n +4 file2 | tr -s ' ' '\t' ) | uniq )

This will result in:

header 1
header 2
header 3

lemur   3       dd
alligator       4       ca
cat     4       aa
dog     5       ab
ostrich 10      cd
fish    13      cc
lemming 16      ad

The command concatenates the header rows of file1 with the result of a sorting operation. The sorting is done numerically on the second field of some input, and any duplicated lines (alligators, lemures and lemmings) are removed with uniq from the result.

The input to sort will be the header-less contents of both file1 and file2, passed through tr to replace consecutive spaces with single tabs (there were uneven number of spaces between the columns in the example data).

The result is tab-delimited.


An equivalent way, using the same tools:

cat <( head -n 3 file1 ) \
    <( sort -k2,2n <( cat <( tail -n +4 file1 ) \
                          <( tail -n +4 file2 ) | tr -s ' ' '\t' ) | uniq )

Without the cats and the process substitutions:

{ head -n 3 file1;
    { tail -n +4 file1; tail -n +4 file2; } | tr -s ' ' '\t' | sort -k2,2n | uniq; }

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.