Join files using awk with duplicate ID values

UNIX for Beginners Q & A
1

I am trying to join two files based on ID values in the first column, but my files have duplicate IDs, and I want to have all possible matches represented in the output file. Here's an example:

file1

aaa	111
bbb	222
ccc	333
aaa	555
eee	666

file2

aaa	123	abc
bbb	234	def
bbb	123	mno
ccc	567	ghi

desired output (order doesn't matter)

aaa	111	123	abc
bbb	222	234	def
bbb	222	123	mno
ccc	333	567	ghi
aaa	555	123	abc
eee	666

I have tried this:

awk -F '\t' 'FNR==NR{A[$1]=$2 FS $3;next}{print $0, A[$1]}' OFS='\t' file2.tsv file1.tsv

But it doesn't give me the second matching bbb row in file2

aaa	111	123	abc
bbb	222	123	mno
ccc	333	567	ghi
aaa	555	123	abc
eee	666
2

Using your (sorted) input, does the join command below satisfy or must it be an awk solution ?

echo file1; cat file1 ; echo file2; cat file2 ; echo required; cat expected
file1
aaa	111
aaa	555
bbb	222
ccc	333
eee	666
file2
aaa	123	abc
bbb	123	mno
bbb	234	def
ccc	567	ghi
required
aaa	111	123	abc
aaa	555	123	abc
bbb	222	123	mno
bbb	222	234	def
ccc	333	567	ghi
eee	666

join  -j 1 file1 file2 -a 1 |tr ' ' '\t'
aaa	111	123	abc
aaa	555	123	abc
bbb	222	123	mno
bbb	222	234	def
ccc	333	567	ghi
eee	666
3

assuming your awk is actually gawk version 4.0.2++ (supporting true multidimensional arrays): gawk -f kaktus3.awk file2.txt file1.txt where kaktus3.awk is:

BEGIN {
  PROCINFO["sorted_in"]="@ind_num_asc"
  OFS=" "
}
FNR==NR {
  f2[$1][FNR]=($2 OFS $3)
  next
}
$1 in f2{
  for(i in f2[$1])
     print $1, $2, f2[$1][i]
  next
}
1

yields:

aaa 111 123 abc
bbb 222 234 def
bbb 222 123 mno
ccc 333 567 ghi
aaa 555 123 abc
eee     666

A bit verbose, but doesn't assume any sorting.

4

FYI, (personally) have replaced/substituted a number of awk/shell manipulations to duckdb where it 'made sense' (lots of junior coders with no exposure to the holy trinity (awk/grep/sed), below a simple duckdb script

cat d
.headers off
.nullvalue ''
.mode tab
select f1.column0,f1.column1,f2.column1, f2.column2 from 'file2.csv' as f2 full outer join 'file1.csv' as f1 on f2.column0 = f1.column0

duckdb < d
aaa	555	123	abc
bbb	222	123	mno
bbb	222	234	def
ccc	333	567	ghi
aaa	111	123	abc
eee	666
5

Thanks @munkeHoller This works great. I was asking for an awk solution, because I am trying to gain a better understanding of awk. But it's good to know this other way of doing it.

6

Thanks @vgersh99, your solution works great for me with gawk 5.3.1. Thanks for the duckdb tip. I'll check it out.

7

The 1, being a true condition, does a { print $0 }.
Replacing it with a { print $1, $2 } would better align with the other print statement.
The OFS (output field separator) is put where the comma is.