0

I would like to merge two tables like this

Table_1

Chr1 5
Chr1 10
Chr1 20
Chr2 10
Chr2 30

Table_2

Chr1 10 value value2
Chr1 20 value value2
Chr2 30 value value2

Desired output

Chr1 5
Chr1 10 value value2
Chr1 20 value value2
Chr2 10
Chr2 30 value value2

I have found scripts in awk to merge tables conserving only rows that match by two columns in the two tables. But here, I would like all the rows of Table_1 but add the values for Table_2 if they match. Could you please tell me how to achieve this?

1

2 Answers 2

2
$ awk '{ key = $1 FS $2 };
       NR == FNR { t[key] = $0; next };
       key in t { print t[key]; next };
       1' table2.txt table1.txt
Chr1 5
Chr1 10 value value2
Chr1 20 value value2
Chr2 10
Chr2 30 value value2
  • On every input line read (for both files read), the variable key is set to the first two fields ($1 and $2), with the field separator (FS) between them. FS is used because it's the only character guaranteed not to be in either field), so is the only character that is guaranteed to generate unique keys. This key is used as the index for an associative array called t.

  • While it reads table2.txt (which must be listed as the first filename arg on the command line), each input line is stored in an element of the t array.

    If table2.txt contains duplicate entries - i.e. multiple lines with the same first two fields - it will only remember the last one seen. If you want it to remember all such duplicates (in the order that they were seen), change the second line of the awk script to:

     NR == FNR { if (key in t) { t[key] = t[key] "\n" $0 } else { t[key] = $0 }; next };
    
  • When it has finished reading the first file, it reads in table1.txt (the second filename arg) and prints the corresponding entry from the table2 array if it exists, otherwise it prints the current line.

  • The 1 on the last line of the awk script is idiomatic awk shorthand for {print} - the value 1 evaluates as true, and the default action when something evaluates as true is to print the current line.

NOTE: if table2.txt is huge, this will use a lot of RAM. This is unlikely to be a problem on any modern system with gigabytes of RAM.

3
  • You can't create a unique key by concatenating 2 strings that could be of different lengths. For example, input of Chr1 15 and Chr11 5 would both concatenate to Chr115. You need to use SUBSEP or FS or something between the 2 strings so the result is unique so use t[$1,$2] throughout, not t[$1$2], so the results of the concatenation for those 2 examples would be Chr1<SUBSEP>15 and Chr11<SUBSEP>5 respectively.
    – Ed Morton
    Aug 24, 2021 at 13:32
  • Your test should be ($1,$2) in t, not t[$1,$2] as the latter will add an entry to t for every pair in either input file (and so use more memory than necessary ans slow the execution) while the former will only add each pair from table2.txt.
    – Ed Morton
    Aug 24, 2021 at 13:35
  • Perhaps you should use ORS instead of "\n",  so your code will work in environments where ORS is CRLF. Aug 31, 2021 at 6:14
1
$ awk '{k=$1 FS $2} NR==FNR{map[k]=$0; next} {print (k in map ? map[k] : $0)}' table2 table1
Chr1 5
Chr1 10 value value2
Chr1 20 value value2
Chr2 10
Chr2 30 value value2

You must log in to answer this question.

Not the answer you're looking for? Browse other questions tagged .