Merge sorted files on 2 columns

Question

I want to merge some pre-sorted tab-delimited files:

• file bygroup.0:

  ancient-american    mercury 1   164
  ancient-american    mh25    2   8717664
  ancient-neolith tk11    262 40074321970
  ancientdna  jk21    6936    17069206689
  ancientdna  rm20    11267   372606702813
  ancientgen  ab34    1573    27800468142
  ancientgen  dg11    3516    45081427920
  ancientgen  fa8 7179    462396221983
  ancientgen  mp15    41  10248223517
  ancientgen  mp18    254 1049351143
  ancientgen  rm20    15100   1565340401
  ancientgen  tc9 1695    89861489631
  

• file bygroup.2:

  ancient-american    mercury 1   160
  ancient-american    mh25    2   10362712888
  ancient-neolith tk11    264 43842268110
  ancientdna  jk21    6919    16379509855
  ancientdna  rm20    11268   324906365415
  ancientgen  ab34    1577    33947364202
  ancientgen  dg11    3518    48092138390
  ancientgen  fa8 7174    472364587220
  ancientgen  mp15    39  32487920045
  ancientgen  mp18    254 1058177852
  ancientgen  rm20    15104   998615135
  ancientgen  tc9 1692    94858351562
  

You can see the 2 files have the same number of lines, and are in the same order based on columns 1 and 2, and the entries in those columns are the same.

Now I want to merge them so that all the lines with the same values in the first 2 columns are output sequentially.

I thought sort -m would be all I'd need, but:

$ sort -m bygroup.*
ancient-american    mercury 1   160
ancient-american    mercury 1   164
ancient-american    mh25    2   10362712888
ancient-american    mh25    2   8717664
ancient-neolith tk11    262 40074321970
ancientdna  jk21    6936    17069206689
ancientdna  rm20    11267   372606702813
ancientgen  ab34    1573    27800468142
ancientgen  dg11    3516    45081427920
ancientgen  fa8 7179    462396221983
ancientgen  mp15    41  10248223517
ancientgen  mp18    254 1049351143
ancientgen  rm20    15100   1565340401
ancientgen  tc9 1695    89861489631
ancient-neolith tk11    264 43842268110
ancientdna  jk21    6919    16379509855
ancientdna  rm20    11268   324906365415
ancientgen  ab34    1577    33947364202
ancientgen  dg11    3518    48092138390
ancientgen  fa8 7174    472364587220
ancientgen  mp15    39  32487920045
ancientgen  mp18    254 1058177852
ancientgen  rm20    15104   998615135
ancientgen  tc9 1692    94858351562

(I get the same results with other options I added, eg. sort -k 1,2 -ifm.)

It does what I expected for ancient-american, but not for the others. What's going on, and is there another fast and efficient way of doing this without resorting to a full sort (sort works without the -m here).

Answer 1

TXR Lisp solution:

$ txr merge.tl 
ancient-american    mercury 1   164
ancient-american    mercury 1   160
ancient-american    mh25    2   8717664
ancient-american    mh25    2   10362712888
ancient-neolith tk11    262 40074321970
ancient-neolith tk11    264 43842268110
ancientdna  jk21    6936    17069206689
ancientdna  jk21    6919    16379509855
ancientdna  rm20    11267   372606702813
ancientdna  rm20    11268   324906365415
ancientgen  ab34    1573    27800468142
ancientgen  ab34    1577    33947364202
ancientgen  dg11    3516    45081427920
ancientgen  dg11    3518    48092138390
ancientgen  fa8 7179    462396221983
ancientgen  fa8 7174    472364587220
ancientgen  mp15    41  10248223517
ancientgen  mp15    39  32487920045
ancientgen  mp18    254 1049351143
ancientgen  mp18    254 1058177852
ancientgen  rm20    15100   1565340401
ancientgen  rm20    15104   998615135
ancientgen  tc9 1695    89861489631
ancientgen  tc9 1692    94858351562

Code:

(defstruct record ()
  key
  line
  (:method equal (me) me.key))

(defun read-recs (file)
  (build
    (awk (:set fs "\t")
         (:inputs file)
         (t (add (new record
                      key [f 0..2]
                      line rec))))))

(mapdo [chain .line put-line] (merge (read-recs "bygroup.0") (read-recs "bygroup.1")))

For holding the information from each record of the file, we define a record structure type which holds a key, our sort key and a line, which is the original verbatim line. The key slot will be a list of two strings.

The record type has an equal method, which implements equality substitution. This means that whenever a record object is compared by the functions equal, less or greater, the value of that method is taken in place of the object. For instance, if we were to sort a list of record structures, without specifying a comparison function, they will sort according to the key.

The function read-recs reads the specified file with the help of awk. A field separator is specified as "\t" (tab). For each record, the tcondition (unconditional truth) dispatches an action which creates a record object. Thekeyis a sublist of thef(field) list, consisting of the first two fields. Thelineis therec: the whole record. recis like$0` in standard Awk.

The build macro is for implicit, procedural list construction. For instance (build (add 1) (add 2)) will return the list (1 2). build creates a scope in which add calls add to an implicit, hidden list, which is returned when build terminates.

Having read-recs and a record type properly keyed on the desired key via equality substitution, all we have to do is read the two files, and hand them to the merge function to get the sorted list.

The record objects in this list are then mapped through a chaining of two functions: [chain .line put-line]: the .line function retrieves the line slot of the object, which is then handed to put-line that dumps it to standard output, followed by a newline.

---

An alternative implementation of the read-recs function without build and awk might be:

(defun read-recs (file)
  (collect-each ((line (file-get-lines file)))
    (let ((fields (spl #\tab line)))
      (new record key [fields 0..2]
                  line line))))

Answer 2

Using the R-programming language

Text files were saved and one-or-more space characters were replaced with single tabs. The two data files were read into R:

> group0 <- read.delim("/Users/admin/bygroup.0", header=FALSE)
> group2 <- read.delim("/Users/admin/bygroup.2", header=FALSE)
> head(group0)
                V1      V2    V3           V4
1 ancient-american mercury     1          164
2 ancient-american    mh25     2      8717664
3  ancient-neolith    tk11   262  40074321970
4       ancientdna    jk21  6936  17069206689
5       ancientdna    rm20 11267 372606702813
6       ancientgen    ab34  1573  27800468142
> head(group2)
                V1      V2    V3           V4
1 ancient-american mercury     1          160
2 ancient-american    mh25     2  10362712888
3  ancient-neolith    tk11   264  43842268110
4       ancientdna    jk21  6919  16379509855
5       ancientdna    rm20 11268 324906365415
6       ancientgen    ab34  1577  33947364202

To merge the data files, use R's merge() function:

> merge(group0, group2, by = c("V1","V2"))
                 V1      V2  V3.x         V4.x  V3.y         V4.y
1  ancient-american mercury     1          164     1          160
2  ancient-american    mh25     2      8717664     2  10362712888
3   ancient-neolith    tk11   262  40074321970   264  43842268110
4        ancientdna    jk21  6936  17069206689  6919  16379509855
5        ancientdna    rm20 11267 372606702813 11268 324906365415
6        ancientgen    ab34  1573  27800468142  1577  33947364202
7        ancientgen    dg11  3516  45081427920  3518  48092138390
8        ancientgen     fa8  7179 462396221983  7174 472364587220
9        ancientgen    mp15    41  10248223517    39  32487920045
10       ancientgen    mp18   254   1049351143   254   1058177852
11       ancientgen    rm20 15100   1565340401 15104    998615135
12       ancientgen     tc9  1695  89861489631  1692  94858351562

Data can be written back out again using either write.delim() or write.csv() functions. For help, type the command at the prompt preceded by a question-mark, as in ?getwd(), ?setwd(), ?read.delim() or ?merge().

[Note: depending on the age of your R-installation, you may have to include the parameter stringsAsFactors=FALSE to each of your read.delim() function calls].

https://www.r-project.org/
https://cran.r-project.org/index.html