Join two tables based on two columns in common and add NA or empty values if does not match

Question

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?

Does this answer your question? [Join two files each with two columns, including non-matching lines](https://unix.stackexchange.com/questions/274548/join-two-files-each-with-two-columns-including-non-matching-lines) — roaima, Aug 24 '21 at 10:39

cas · Accepted Answer · 2021-08-25T06:21:13.107

$ 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.

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 `Chr115` and `Chr115` respectively. — Ed Morton, Aug 24 '21 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 '21 at 13:35
Perhaps you should use `ORS` instead of `"\n"`, so your code will work in environments where `ORS` is CRLF. — G-Man Says 'Reinstate Monica', Aug 31 '21 at 06:14

score 1 · Answer 2 · answered Aug 24 '21 at 13:39

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

answered Aug 24 '21 at 13:39

Ed Morton

28,789
5
20
47

Join two tables based on two columns in common and add NA or empty values if does not match

2 Answers2