Comparing and fetching words in two columns of different files

Question

I want to compare two different columns of different files and fetch the common entries among them:

file1

abc
123
ttt
kkk

file2

111 wed
222 kad
333 ttt
444 kkk

I want to compare column 1 of file1 to column 2 of file2. If there are any common entries, I want to print the match lines from file2:

Expected result:

333 ttt
444 kkk

I have tried below commands to fetch the result:

awk -F 'NR==FNR{c[$1$2]++;next};c[$1$2] > 0' file1 file2

or

join -t -1 1 -2 2 -o 2.1,2.2 file1 file2

but I didn't get the expected results.

Answer 1

awk 'NR==FNR {a[$1]; next} $2 in a' file1 file2
333 ttt
444 kkk

The above follows what you want: to compare column 1 of file1 to column 2 of file2. If there are any common entries, I want to print the match lines from file2

Answer 2

Your awk fails because you are using both fields as the key, but your file1 only has one field so both will never be present in the c array. Also, you are using the -F option to give a field separator but are not actually giving one. This means that the field separator will be the awk script itself:

awk -F 'script' file

Here's a working version of your awk attempt:

$ awk  'NR==FNR{c[$1]++;next};c[$2] ' file1 file2
333 ttt
444 kkk

And here's a more efficient version that doesn't use more memory than needed:

$ awk  'NR==FNR{c[$1]}; $2 in c' file1 file2 
333 ttt                 
444 kkk   

Your join also missuses the -t option. Just like with the -F of awk, the -t also takes an argument and you aren't giving it one. Next, join requires the input to be sorted. Here's a working version of your join approach:

$ join  -1 1 -2 2 -o 2.1,2.2 <(sort file1) <(sort -k2 file2)
444 kkk
333 ttt

Answer 3

Using csvjoin (part of the csvkit toolset):

Assuming file1 as:

x
abc
123
ttt
kkk

and file2 as:

y x
111 wed
222 kad
333 ttt
444 kkk

The following command would do:

csvjoin -d' ' -c x file{2,1} | tr ',' ' '

• -d' ' is using space as delimiter.

• -c x csvjoin would join files using column x.

As @terdon fixed the above command for file without headers. The command may be changed to this:

csvjoin -H -d ' ' -c 2,1 fil{2,1} | tr ',' ' ' | tail -n +2

The above command works for file that has no headers(-H argument).

And tail commands starts output from second record(+2). This is because csvjoin command adds a new header line at start of output.

Answer 4

Does this awk work for you?

awk 'FNR==NR {array[$2]=$0; next} {$0=array[$1]}/./{print}' file2 file1

Output

$ awk 'FNR==NR {array[$2]=$0; next} {$0=array[$1]}/./{print}' file2 file1
333 ttt
444 kkk

Answer 5

#!/usr/bin/python
k1=open('file1','r')
for f1 in k1:
    k2=open('file2','r')
    for f2 in k2:
        if f1.strip() in f2.strip():
            print f2.strip()
~                                  

output

333 ttt
444 kkk