1

I have a tabular file like this which is my index:

a   X001
a   X002
a   X003
b   X002
c   X006
z   X007
z   X008
z   X001

I want to search the following single column file against that index and return each match from the second column of the index.

a
b
z

So the output from that search would be this:

X001
X002
X003
X002
X007
X008
X001

Edit: Here is exact info from my files (top 10 lines from each)

Index

10803548    COG4190
10803554    COG1476
10803555    COG1192
10803559    COG3385
10803567    COG0071
10803570    COG1695
10803571    COG0467
10803573    COG3883
10803574    COG0714
10803576    COG1192

File I am searching:

10956722
11497860
11497860
11497924
11497924
11497924
11497924
11497924
11497979
11497979

The Expected output of this search:

COG3704
COG1474
COG1474
COG2801
COG2801
COG2801
COG2801
COG2801
COG2223
COG2223
  • grep -Po '[abz]\s*\K\S*' tabular.file – Costas Oct 16 '15 at 19:12
  • So the index file is comma separated? And what would be the output of the exact input example? – chaos Oct 16 '15 at 20:18
  • Sorry I was using the comma seperated file. I just modified it to tab. I updated my post again, – Blaze Oct 16 '15 at 20:20
  • I just updated the post again. I used the tab separated and sorted files again with both the join and awk command. The index is rather large, 1.9m lines this time. So the output of the exact input sequence would be "COG3704 COG1474 COG1474 COG2801 COG2801 COG2801 COG2801 COG2801 COG2223 COG2223" – Blaze Oct 16 '15 at 20:23
2

With join:

join -o1.2 <(sort -k1n index) <(sort -k1n file)
  • This will merge the two files at field number 1 and if they match, print the second field of the first file 1.2.

Or with awk:

awk 'FNR==NR{a[$1]=1} FNR!=NR&&a[$1]{print $2}' file index
  • The file file is loaded into an array a.
  • When the second file index in processed awk checks if the first field is in the array as index a[$1]. If yes, print the second field $2.
  • I tried both methods. First method says my files aren't sorted, even after I ran sort on them (when I head both files they are numerically sorted). The AWK method returns a 0 byte file. I edited the original post with exact information from my files. Thank you for your help! Sorry, my fault I was using my non-tab files. I'm going to try again with the tab format. – Blaze Oct 16 '15 at 20:14
  • Okay, I tried the command again and it worked, however it's not giving me the matches which are repeated. Like as my exact results example is. – Blaze Oct 16 '15 at 20:31
  • So awk only gives me one result, while join stops at a certain row, saying it's not sorted again. But the files are 100% sorted. – Blaze Oct 16 '15 at 20:44
  • @Blaze The input of join must be sorted at the join field with -k. Can you try the join command again? – chaos Oct 16 '15 at 20:49
  • Perfect! I saw that in another thread here and tried that just a few minutes ago. Thank you! – Blaze Oct 16 '15 at 20:55

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.