Grep a file on specific field

Question

I have two files, let's say

File1:

Locus_1
Locus_2
Locus_3

File2:

3  3  Locus_1  Locus_40  etc_849    
3  2  Locus_2  Locus_94  *    
2  2  Locus_6  Locus_1  *    
2  3  Locus_3,Locus_4  Locus_50  *    
3  3  Locus_9  Locus_3  etc_667

I want to do a grep -F for the first file only on the third column of the second file (in the original File2 fields are separated by tabs), such as the output should be:

Output:

3  3  Locus_1  Locus_40  etc_849    
3  2  Locus_2  Locus_94  *    
2  3  Locus_3,Locus_4  Locus_50  *

How can I do it?

Edit To Chaos: no, the comma is not a mistake. I can have more than one Locus_* in a column - and in case the second Locus_* (the one after the comma) matches one of the lines of File1 I want it to be retrieved, too!

Answer 1

If grep is not necessary, one simple solution would be to use join for that:

$ join -1 1 -2 3 <(sort file1) <(sort -k3 file2)
Locus_1 3 3 Locus_40 etc_849
Locus_2 3 2 Locus_94 *
Locus_3 2 3 Locus_4 Locus_50 *

Explanation:

• join -1 1 -2 3: join the two files where in the first file the first (and only) field is used and in the second file the third field. They are printed when they are equal.
• <(sort file1): join needs sorted input
• <(sort -k3 file2): the input must be sorted on the join field (3rd field here)

Answer 2

Adapting a solution from https://stackoverflow.com/a/9937241/1673337 you can use (g)awk to get:

awk 'NR==FNR{a[$0]=1;next} {for(i in a){if($3~i){print;break}}}' File1 File2

which provides the given Output.

While you could craft a RegEx to feed into grep to satisfy only matching on the third column, I feel using awk at this point is more understandable.

the if($3~i){print;break} part takes care of printing only if the third column matches a line from File1 (which is stored in the array a). See the linked post for an explanation of the rest.

Be aware this reads the entire contents of File1 into memory, however this should only be a concern if it is large, in which case you would want to optimize anyway, because of the multiplicative nature of the comparison.

Answer 3

Using the grep -F option searches for literal strings anywhere in the current line. By definition, literal means that you cannot use regular expressions to narrow down your search to be within just field 3 (TAB delimited).

You can, however, use the grep -f to read your pattern input file1 - but you do need to modify it into a list of regular expression. Here is one way using bash process substitution and sed to generate list of standard regular expressions which grep -f can handle.

Using grep with Basic Regular Expressions:

  grep -f <(sed 's/.*/^\\([^\t]\\+\t\\)\\{2\\}\\([^\t]\\+,\\)*&[,\t]/'  file1) file2

For grep's Basic regex, file1 is dynamically converted to:

^\([^   ]\+ \)\{2\}\([^ ]\+,\)*Locus_1[,    ]
^\([^   ]\+ \)\{2\}\([^ ]\+,\)*Locus_2[,    ]
^\([^   ]\+ \)\{2\}\([^ ]\+,\)*Locus_3[,    ]

---

OR: Using grep -E with Extended Regular Expressions visually simplifies the code by avoiding the need for most backslashes in both grep and sed

grep -Ef <(sed 's/.*/^([^\t]+\t){2}([^\t]+,)*&[,\t]/' file1) file2

For grep's Extended regex, file1 is dynamically converted to:

^([^    ]+  ){2}([^ ]+,)*Locus_1[,  ]
^([^    ]+  ){2}([^ ]+,)*Locus_2[,  ]
^([^    ]+  ){2}([^ ]+,)*Locus_3[,  ]

---

The output (in both cases):

3   3   Locus_1 Locus_40    etc_849     
3   2   Locus_2 Locus_94    *       
2   3   Locus_3,Locus_4 Locus_50    *       

---

Note that -f and -F can slow things down dramatically while file1 is large

Answer 4

A grep -P solution:

regexp=$( echo -n '('; < File1 tr '\n' '|' | sed 's/|$//'; echo ')' )
grep -P "^[^\s]+\s+[^s]+\s+([^\s]*,)*$regexp" File2

Output:

3  3  Locus_1  Locus_40  etc_849    
3  2  Locus_2  Locus_94  *    
2  3  Locus_3,Locus_4  Locus_50  *    

---

If your File1 may contain special regexp characters, you'll need to escape them:

regexp_escape() { ruby -pe '$_ = Regexp.escape($_.chomp("\n")) + "\n"'; }

regexp=$( echo -n '('; < File1 regexp_escape  |  tr '\n' '|' | sed 's/|$//'; echo ')' )
grep -P "^[^\s]+\s+[^s]+\s+([^\s]*,)*$regexp" File2

Explanation:

The second line creates strings such as: (Locus_1|Locus_2|Locus_3). and

"^[^\s]+\s+[^s]+\s+([^\s]*,)*"

means:

• [word] [whitespace(s)] [word] [whitespace(s)] [(optional word followed by comma) zero or arbitrarily many times]

Answer 5

(   t=$(printf \\t) ntt=[^$t]*$t ntc=[^$t,]*
### ^just makes it easy regardless of your sed version.
    sed  -ne"s/..*/^($ntt){2}($ntc,)*&(,$ntc)*$t/p" |
    grep -Ef- ./File2
)   <File1

---

3   3   Locus_1 Locus_40    etc_849
3   2   Locus_2 Locus_94    *
2   3   Locus_3,Locus_4 Locus_50    *

That will get a match for a line in File1 in the third column of File2 regardless of how many ($ntc,)* groups precede or (,$ntc)* groups follow it. It does depend, though, on there being no metacharacters in the search strings in File1. If there might be metachars in File1, then we have to clean it up, first:

(   t=$(printf \\t) ntt=[^$t]*$t ntc=[^$t,]*
    sed  -ne's/[]?{(^$|*.+)}\[]/\\&/g' \
          -e"s/..*/^($ntt){2}($ntc,)*&(,$ntc)*$t/p" |
    grep -Ef- ./File2
)   <File1

Answer 6

To "grep" columns awk is the tool of choice

BEGIN { f="Locus_2" }
$3==f { print $0; }

so you can loop through File1

for x in `cat File1`
do awk -v X="$x" '$3~X { print $0 }' <File2
done

.