compare two columns of two files

Question

[Sam]

Dear All

I have two files of gene list in tab format, I want to compare 2nd column if 1st file file with 1st column of 2nd file and report all columns in both files for each match

any help ?

Thanks

file 1 
1st column        2nd column
chr10_17026 VIT_202s0033g00190
chr18_37402 VIT_210s0003g03400
chr19_39195 VIT_200s0313g00080


files 2
1st column                                                  2nd column 3rd column
VIT_200s0313g00072 VIT_200s0313g00080 VIT_200s0313g00066    7.86            6.22
VIT_202s0033g00190  8.48        6.49
VIT_210s0003g03400  8.18        6.05
VIT_210s0116g01020  8.67        5.82
VIT_213s0064g01330  9.02        5.84

output :
chr10_17026 VIT_202s0033g00190     8.48        6.49
chr18_37402 VIT_210s0003g03400     8.18        6.05
chr19_39195 VIT_200s0313g00080     7.86         6.22

[finswimmer]

Hello Sam ,

what have you tried so far?

fin swimmer

[husensofteng]

another tip is to use sort and join commands.

[Benn]

One tip, use R. Import the files into R and use subsetting. There are many tutorials, for example here, so try to do it yourself.

Answer 1

[Kevin Blighe]

The most simple way to do it with awk is like this:

awk 'FNR==NR {lookup[$2]; next} {if ($1 in lookup) print}' file1.tsv file2.tsv 
VIT_202s0033g00190  8.48    6.49
VIT_210s0003g03400  8.18    6.05

You may want to tidy up this type of line, though, as its formatting is inconsistent:

VIT_200s0313g00072 VIT_200s0313g00080 VIT_200s0313g00066    7.86            6.22

Answer 2

[bioguy24]

Another awk:

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

Answer 3

[curious]

If you know python you can look at the pandas package. You probably want to perform an inner merge. Learning SQL-like processes can take a long time to get used to, but after a while become second nature. It might not help you now depending on your situation, but you eventually might want to look into pandas. It is my daily driver for working with tab separated data like this. It is incredibly powerful

https://pandas.pydata.org/pandas-docs/stable/user_guide/merging.html