Find rows with the same value in a column in two f

Question

I've got two files (millions of columns)

File1.txt, ~4k rows

some_key1 some_text1
some_key2 some_text2
...
some_keyn some_textn

File2.txt, ~20 M rows

some_key11 some_key11 some_text1
some_key22 some_key22 some_text2
...
some_keynn some_keynn some_textn

When there is an exact match between column 2 in File1.txt and column 3 in File2.txt, I want to print out the particular rows from both files.

EDIT

I've tried this (I forgot to write it) but it doesn't work

awk 'NR{a[$2]}==FNR{b[$3]}'$1 in a{print $1}' file1.txt file2.txt

Answer 1

You need to fix your awk program

To print all records in file2 if field 1 (file1) exists in field 3 (file2):-

awk 'NR==FNR{A[$2];next}$3 in A' file1.txt file2.txt
some_key11 some_key11 some_text1
some_key22 some_key22 some_text2
...
some_keynn some_keynn some_textn

To print just field 1 in file2 if field 1 (file1) exists in field 3 (file2):-

awk 'NR==FNR{A[$2];next}$3 in A{ print $1 }' file1.txt file2.txt
some_key11
some_key22
...
some_keynn

Answer 2

Let's say your dataset is big in both dimensions - rows and columns. Then you want to use join. To use join, you have to sort your data first. Something along those lines:

<File1.txt sort -k2,2 > File1-sorted.txt
<File2.txt sort -k3,3 -S1G > File2-sorted.txt

join -1 2 -2 3 File1-sorted.txt File2-sorted.txt > matches.txt

The sort -k2,2 means 'sort whole rows so the values of second column are in ascending order. The join -1 2 means 'the key in the first file is the second column'.

If your files are bigger than say 100 MB it pays of to assign additional memory to the sort via the -S option. The rule of thumb is to assign 1.3 times the size of the input to avoid any disk swapping by sort. But only if your system can handle that.

---

If one of your data files is very small (say up to 100 lines), you can consider doing something like

<File2.txt fgrep -F <( <File1.txt cut -f2 ) > File2-matches.txt

to avoid the sort, but then you'd have to look up the 'keys' from that file.

The decision which one to use is very similar to the 'hash join' and 'merge join' in the database world.