1

I can't seem to get either grep or awk to do a relatively simple index pull of a list. I suspect it's because of adjacent duplicates in the index file, something I wouldn't have thought would cause an issue. Oddly looking for a solution online wasn't successful as all the queries I found are people who want to remove duplicates, not keep them!

The Index file looks like this with ~40k entries, many being sorted duplicates:

n0000003
n0000003
n0000008
n0000008
n0000017
n0000017
n0000017
n0000017
.....etc

And the search file looks like this, with ~10k unique entries of each identifier:

n0000003    216 -0.334  0.229   0.088   0.154
n0000008    16  0.117   0.200   0.508   0.621
n0000017    218 -0.353  0.196   0.042   0.084
...etc

What I need is output like this, with repeat output entries equaling the number of repeat index entries in the index file:

n0000003    216 -0.334  0.229   0.088   0.154
n0000003    216 -0.334  0.229   0.088   0.154
n0000008    16  0.117   0.200   0.508   0.621
n0000008    16  0.117   0.200   0.508   0.621
n0000017    218 -0.353  0.196   0.042   0.084
n0000017    218 -0.353  0.196   0.042   0.084
n0000017    218 -0.353  0.196   0.042   0.084
n0000017    218 -0.353  0.196   0.042   0.084
...etc

But instead both grep and awk give only one entry each (making it identical to the search file). I figured a grep could handle repeat duplicates no problem but I can't find a workaround.

These are commands I would have expected to work for example:

grep -f index.txt searchfile.txt > output.txt
awk -F'\t' 'NR==FNR{c[$1]++;next};c[$1]' index.txt searchfile.txt > output.txt

Any advice on how I could get grep or awk to output the proper number of repeats would be great! Thanks so much! Andrew

  • The technical name for this type of database operation on two tables is a join. – JdeBP Jun 16 at 9:01
  • Thanks for the info, I've added join as a tag. – amrezans Jun 16 at 13:38
  • For the record this command works in addition to the awk options below: awk 'NR==FNR{a[$1]=$0;next} ($0 in a){print a[$0]}' search.txt index.txt > out.txt – amrezans Jun 16 at 14:10
2

I don't think you can do this with grep, no, but you can in awk. The simplest approach I can think of is to store the contents of searchfile.txt in memory and then print its lines each time you see an index:

$ awk -F'\t' 'NR==FNR{c[$1]=$0;next}{if(c[$1]){print c[$1]}}' searchfile.txt index.txt 
n0000003    216 -0.334  0.229   0.088   0.154
n0000003    216 -0.334  0.229   0.088   0.154
n0000008    16  0.117   0.200   0.508   0.621
n0000008    16  0.117   0.200   0.508   0.621
n0000017    218 -0.353  0.196   0.042   0.084
n0000017    218 -0.353  0.196   0.042   0.084
n0000017    218 -0.353  0.196   0.042   0.084
n0000017    218 -0.353  0.196   0.042   0.084

If both files are sorted on the index, you can also use join:

$ join -t$'\t' searchfile.txt index.txt 
n0000003    216 -0.334  0.229   0.088   0.154
n0000003    216 -0.334  0.229   0.088   0.154
n0000008    16  0.117   0.200   0.508   0.621
n0000008    16  0.117   0.200   0.508   0.621
n0000017    218 -0.353  0.196   0.042   0.084
n0000017    218 -0.353  0.196   0.042   0.084
n0000017    218 -0.353  0.196   0.042   0.084
n0000017    218 -0.353  0.196   0.042   0.084
| improve this answer | |
  • Thanks it worked! (I'd upvote if I could) For the record a colleague gave it a go as well and came up with: awk 'NR==FNR{a[$1]=$0;next} ($0 in a){print a[$0]}' search.txt index.txt > out – amrezans Jun 16 at 14:07
  • @amrezans yes, that's essentially the same idea, although it would be better to write that as awk 'NR==FNR{a[$0]=$0;next} ($0 in a){print a[$0]}' search.txt index.txt > out, so you don't use $1 in one instance and $0 in the next. – terdon Jun 16 at 14:10
  • By the way, @amrezans, you might be interested in our sister site, Bioinformatics. – terdon Jun 16 at 14:11
  • Great, thanks for the info and link! – amrezans Jun 16 at 14:16
0

Looking at your attempt, it seems you went almost near the goal post but didn't put the football past it Simply added a while loop to your attempt.

awk -F'\t' '
  FNR == NR { c[$1]++; next }
  k = c[$1] { while (k--) print }
' index.txt search.txt
| improve this answer | |
  • Thanks! It appears to work well, only it is not sorted in the same fashion (which of course can be done afterwards). (sorry can't upvote yet) – amrezans Jun 16 at 14:09

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.