0

I have a large file "F1" with listed id-pairs:

id1 = digits, id2 = characters

id1 id2 id1 id2 ...

The goal is to extract the lines containing the id-pairs from a second file "F2", formatted as follows:

id1 TYHYU 61728497 rtyheyia id2 8372819203948 id1 UJLJF 57383930 utkjruak id2 5683903048377 ...

Thought about processing it with almighty "grep" but I'm facing a few obstacles.

Each Id1 and Id2 from F1 repeats a few times in F2, rendering obsolete grep -Fwf F1.txt F2.txt > F3.txt. In other words, ID1+ID2 represents a full identifier.

And although executing grep -w "$id1.*$id2" db.txt would solve it, I'm not sure how to get there (probably running an awkward while read -r loop to process each line from F1 as a variable-set before running grep??).

1

The solution by @John Moon is worthy, and I've upvoted it. But I note that you describe your files as 'large'. The awk-based solution requires one full pass through your large file f1, which has N lines. It then requires N full passes through your large (?) file f2.

I'm not an awk expert. Someone who was could likely make one pass through the f1 file to gather IDs, and then make one pass through the f2 file to print the matches.

Here's a rather klunky solution in grep which does it almost as quickly as an optimal awk solution.

First, convert the f1.txt file (N lines long) into a file of regexp's, also N lines long:

$ while read id1 id2; do
   printf '^%s[[:blank:]]+' "$id1"
   printf '[^[:blank:]]+[[:blank:]]+'
   printf '[^[:blank:]]+[[:blank:]]+'
   printf '[^[:blank:]]+[[:blank:]]+'
   printf '%s[[:blank:]]\n' "$id2"
done < f1.txt > regexp.txt

That ugly printf sequence creates a regexp that forces a string match at the beginning of the line (to match column 1), then arbitrary whitespace; then 3 repetitions of (non-blank)(blank) string pairs (to ignore columns 2, 3 and 4); then matches another string in column 5, followed by a blank.

So in one pass through the N lines of f1.txt, we've created a list of N regexps that will match lines in f2.txt that contain the same ID pairs. That list is stored in regexp.txt.

The matching lines can now be found in one pass through f2.txt using the command:

$ egrep -f regexp.txt f2.txt

So all told, the script would be:

$ while read id1 id2; do
   printf '^%s[[:blank:]]+' "$id1"
   printf '[^[:blank:]]+[[:blank:]]+'
   printf '[^[:blank:]]+[[:blank:]]+'
   printf '[^[:blank:]]+[[:blank:]]+'
   printf '%s[[:blank:]]\n' "$id2"
done < f1.txt > regexp.txt
$ egrep -f regexp.txt f2.txt

Sample data:

f1.txt:

id1 id2
id1 id2
id3 id4
id3 id5
id4 id5
id4 id6

f2.txt:

id1 TYHYU 61728497 rtyheyia id2 8372819203948
id1 UJLJF 57383930 utkjruak id2 5683903048377
id1 UJLJF 57383930 utkjruak id2 5683903048377
id3 THREE 4444444 adfhdd id4 182i3746
id2 NOPE 4444444 adfhdd id4 182i3746
id3 TREEE 555555 affff id5 8435987345
id4 FOUR  555055 asdfl id5 3728462
id4 FORE  6666666 dfiuyd id6 845687234

Intermediate file regexp.txt (created by the script):

^id1[[:blank:]]+[^[:blank:]]+[[:blank:]]+[^[:blank:]]+[[:blank:]]+[^[:blank:]]+[[:blank:]]+id2[[:blank:]]
^id1[[:blank:]]+[^[:blank:]]+[[:blank:]]+[^[:blank:]]+[[:blank:]]+[^[:blank:]]+[[:blank:]]+id2[[:blank:]]
^id3[[:blank:]]+[^[:blank:]]+[[:blank:]]+[^[:blank:]]+[[:blank:]]+[^[:blank:]]+[[:blank:]]+id4[[:blank:]]
^id3[[:blank:]]+[^[:blank:]]+[[:blank:]]+[^[:blank:]]+[[:blank:]]+[^[:blank:]]+[[:blank:]]+id5[[:blank:]]
^id4[[:blank:]]+[^[:blank:]]+[[:blank:]]+[^[:blank:]]+[[:blank:]]+[^[:blank:]]+[[:blank:]]+id5[[:blank:]]
^id4[[:blank:]]+[^[:blank:]]+[[:blank:]]+[^[:blank:]]+[[:blank:]]+[^[:blank:]]+[[:blank:]]+id6[[:blank:]]

Resulting egrep output:

$ egrep -f regexp.txt f2.txt 
id1 TYHYU 61728497 rtyheyia id2 8372819203948
id1 UJLJF 57383930 utkjruak id2 5683903048377
id1 UJLJF 57383930 utkjruak id2 5683903048377
id3 THREE 4444444 adfhdd id4 182i3746
id3 TREEE 555555 affff id5 8435987345
id4 FOUR  555055 asdfl id5 3728462
id4 FORE  6666666 dfiuyd id6 845687234

Again a pure awk solution would likely be faster and more elegant. Also, the approach I've outlined may cause grep to run out of memory if the number of patterns in regexp.txt is too large. But I just thought I'd throw this out there as a somewhat speed-optimised grep-based solution.

2

You're almost there. The read loop should work fine for this situation, so something like this:

while read -r line; do
    id1=$(echo "$line" | cut -d ' ' -f 1)
    id2=$(echo "$line" | cut -d ' ' -f 2)
    grep -w "${id1}.*${id2}" "$F2"
done < "$F1"

But this could also potentially return false positives if the IDs you're grepping for are present anywhere in other fields of the data. If you can guarantee the ID fields in F2 always appear in columns 1 and 5, I'd recommend checking the field position too. This can be done pretty quickly with awk in place of the grep line:

awk -v id1="$id1" -v id2="$id2" '$1 == id1 && $5 == id2 {print $0}' "$F2"

This roughly says, "For every line in F2, if column 1 is id1 and column 5 is id2, print the whole line".

Disclaimer, I did not test this.

  • 1
    +1 IMO, the awk solution is definitely better, for the reasons you cite. Slightly simpler might be while read id1 id2; do awk .... – Jim L. Jun 24 at 23:49
  • That read syntax hadn't occurred to me - thanks for pointing it out! – John Moon Jun 25 at 0:24

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.