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

3
  • The technical name for this type of database operation on two tables is a join.
    – JdeBP
    Commented Jun 16, 2020 at 9:01
  • Thanks for the info, I've added join as a tag.
    – amrezans
    Commented Jun 16, 2020 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
    Commented Jun 16, 2020 at 14:10

2 Answers 2

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
4
  • 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
    Commented Jun 16, 2020 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
    Commented Jun 16, 2020 at 14:10
  • By the way, @amrezans, you might be interested in our sister site, Bioinformatics.
    – terdon
    Commented Jun 16, 2020 at 14:11
  • Great, thanks for the info and link!
    – amrezans
    Commented Jun 16, 2020 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
1
  • 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
    Commented Jun 16, 2020 at 14:09

You must log in to answer this question.

Not the answer you're looking for? Browse other questions tagged .