0

I have two files (file1 and file2) and I would like to mount a third (file3) containing a column that the two do not have. I usually do the sort and then together with the join. But it is not working, because it has values with letters and numbers from the example below:

File1:

ARS-BFGL-BAC-1180 14 20574088
ARS-BFGL-BAC-11805 12 14897445
ARS-BFGL-BAC-11852 1 34309921
ARS-BFGL-BAC-11867 13 12616676 

File2

AC16359 ARS-BFGL-BAC-11798 B B
AC16359 ARS-BFGL-BAC-11805 B B
AC16359 ARS-BFGL-BAC-1180 B B
AC16359 ARS-BFGL-BAC-11852 A A
AC16359 ARS-BFGL-BAC-11867 B B 

I notice that I use the same sort command for both and note that the final SNP_Name 11805 is above 1180 and the other is not.

1 Answer 1

0

Sort by the matching fields only (i.e. -k1,1 for file1 and -k2,2 for file2).

e.g.

$ join -1 1 -2 2  <(sort -k1,1 file1) <(sort -k2,2 file2) 
ARS-BFGL-BAC-1180 14 20574088 AC16359 B B
ARS-BFGL-BAC-11805 12 14897445 AC16359 B B
ARS-BFGL-BAC-11852 1 34309921 AC16359 A A
ARS-BFGL-BAC-11867 13 12616676 AC16359 B B

I initially tried this using just a version sort (-V, --version-sort, which is a non-standard option which requires either GNU or *BSD sort). That worked too, both with and without the -k1,1 and -k2,2, but it may not work reliably with a larger or different set of data files.

Given that the key fields can contain either 4 or 5 digit numbers (and possibly more variations), using a natural sort is ideal. So, I'd use a version sort and specify the key fields.

join -1 1 -2 2  <(sort -V -k1,1 file1) <(sort -V -k2,2 file2)
2
  • It worked, thank you very much. My mistake was that I was doing -k2.1. Please, why is it 2 (-k, 2,2)?
    – Augusto II
    Apr 29, 2021 at 20:02
  • because, for -k, both numbers (i.e. are before and after the comma) are field numbers, not a field number and a count. -k[start field],[stop field], not -k[start],[count]. See man sort and search for "KEYDEF" for details.
    – cas
    Apr 30, 2021 at 0:40

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.