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

1
votes

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
2
linuxbashawk
And what you have done so far? – Joao Vitorino
I've edited the question. I've forgot to add my try. @JoaoVitorino – Aso Strife
Please be more specific on what matches to what. Is there only one column which should match? Why the files have the same names? – liborm
@liborm could be more than one row with the same column. File with same name is a typo. – Aso Strife
You said you tried awk 'NR{a[$2]}==FNR{b[$3]}'$1 in a{print $1}' which has 3 single quotes in it: awk 'NR...}'$1...}'. Obviously you can't have a [unescaped] character in the middle of a string or script that's delimited by that character so - what were you hoping the single quote in the middle would mean? What you have is like writing an English sentence like He said "here is"the answer" to me. It doesn't make sense to have " in the middle of a "-delimited statement and that's true for any given character so what was your intent with the ' mid-script? – Ed Morton

2 Answers

1
votes

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
0
votes

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.