在两个文件的列中查找具有相同值的行 [英] Find rows with the same value in a column in two files

问题描述

我有两个文件(数百万列)

I've got two files (millions of columns)

File1.txt ，约4k行

some_key1 some_text1
some_key2 some_text2
...
some_keyn some_textn

File2.txt ，约2000万行

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

当File1.txt中的第2列与File2.txt中的第3列完全匹配时，我想从两个文件中打印出特定的行.

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.

编辑

我已经尝试过了(我忘记写了)，但是它不起作用

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

推荐答案

假设您的数据集在维度(行和列)方面都很大.然后，您要使用join.要使用join，您必须先对数据进行排序.遵循这些原则:

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

sort -k2,2的意思是'对整个行进行排序，因此第二列的值按升序排列. join -1 2表示第一个文件中的键是第二列".

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

如果文件大于100 MB，则需要通过-S选项为sort分配更多的内存.经验法则是分配输入大小的1.3倍，以避免任何磁盘被sort交换.但前提是您的系统可以处理该问题.

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.

如果其中一个数据文件非常小(最多100行)，则可以考虑做类似的事情

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

以避免sort，但是您必须从该文件中查找'keys'.

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.

这篇关于在两个文件的列中查找具有相同值的行的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！