Obtain patterns from a file, compare to a column of another file, print matching lines, using awk

Question

I'd essentially like to combine the power of

grep -f 

with

awk '{ if($2=="this is where I'd like to input a file of fixed string patterns") print $0}'

Which is to say, I'd like to search a specific column of a file (File 1) with an input file of patterns (File 2). If a match is found simply:

> outputfile.txt

From a previous post, this awk line is really close:

awk 'NR==FNR{a[$0]=1;next} {n=0;for(i in a){if($0~i){n=1}}} n' file1 file2

Taken from Obtain patterns in one file from another using ack or awk or better way than grep?

But it doesn't search a specific column of file 1. I'm open to other tools as well.

Answer 1

The example you found is indeed very close to what you want, the only difference is that you don't want to match the whole line ($0).

Modify it to something like this:

awk 'NR==FNR { pats[$0]=1; next } { for(p in pats) if($2 ~ p) { print $0; break } }' patterns file

If you only need a fixed string match, use the index() function instead, i.e. replace $2 ~ p with index($2, p).

You could also provide the column number as an argument to awk, e.g.:

awk -v col=$col 'NR==FNR { pats[$0]=1; next } { for(p in pats) if($col ~ p) { print $0; break } }' patterns file

Edit - whole field matching

You can accomplish this with the == operator:

awk -v col=$col 'NR==FNR { pats[$0]=1; next } { for(p in pats) if($col == p) { print $0; break } }' patterns file

Answer 2

This is using awk:

awk 'BEGIN { while(getline l < "patterns.txt") PATS[l] } $2 in PATS' file2

Where file1 is the file you are searching, and patterns.txt is a file with one exact pattern per file. The implicit {print} has been omitted but you can add it and do anything you like there.

The condition $2 in PATS will be true is the second column is exactly one of the patterns.

If patterns.txt are to be treated as regexp matches, modify it to

ok=0;{for (p in PATS) if ($2 ~ p) ok=1}; ok

So, for example, to test $2 against all the regexps in patterns.txt, and print the third column if the 2nd column matched:

awk 'BEGIN { while(getline l < "patterns.txt") PATS[l] } 
     ok=0;{for (p in PATS) if ($2 ~ p) ok=1}; ok 
    {print $3}' < file2

And here's a version in perl. Similar to the awk version except that it uses regexps instead of fields.

perl -ne 'BEGIN{open $pf, "<patterns.txt"; %P=map{chomp;$_=>1}<$pf>} 
   /^\s*([^\s]+)\s+([^\s]+).*$/ and exists $P{$2} and print' < file2

Taking that apart:

BEGIN{
  open $pf, "<patterns.txt"; 
  %P = map {chomp;$_=>1} <$pf>;
}

Reads in your patterns file into a has %P for fast lookup.

/^\s*([^\s]+)\s+([^\s]+).*$/ and  # extract your fields into $1, $2, etc
exists $P{$2} and                 # See if your field is in the patterns hash
print;                            # just print the line (you could also 
                                  # print anything else; print "$1\n"; etc)

It gets slightly shorter if your input file is tab-separated (and when you know that there's exactly one tab between fields). Here's an example that matches the patterns against the 5th column:

 perl -F"\t" -ane '
    BEGIN{open $pf, "<patterns.txt"; %P=map{chomp;$_=>1}<$pf>} 
    exists $P{$F[4]} and print ' file2

This is thanks to perl's -F operator that tells perl to auto-split into columns based on the separator (\t in this case). Note that since arrays in perl start from 0, $F[4] is the 5th field.

Answer 3

I am not quite sure which part the distinction of columns plays in this scenario. Do you process some kind of csv file? Do you take care of column delimiters in the regex list file? If there are no distinct columns separated by certain delimiters in your file, you could just use grep:

grep -o -f file2 file1

If columns is an issue, maybe something like this:

grep -o "[^,]*" file1 | grep -f file2

where , is the delimiter.