Grep not returning identical matches from awk pipe

Question

I'm trying to identify all lines in common based on the first column of one file. I'm using the following command:

awk '{print $1}' File1 | fgrep -wf - File2 >Out

File1:

M01605:153:000000000-B55NK:1:1101:10003:14536   chr1    150129998   A   Rev 18
M01605:153:000000000-B55NK:1:1101:10007:14573   chr17   44166311    C   38  44166311
M01605:153:000000000-B55NK:1:1101:10007:14573   chr17   44166500    G   Rev 34
M01605:153:000000000-B55NK:1:1101:10009:9160    chr8    16716272    G   35  16716395
M01605:153:000000000-B55NK:1:1101:10009:9160    chr8    16716336    A   37  16716337
M01605:153:000000000-B55NK:1:1101:10009:9160    chr8    16716336    A   38  16716459
M01605:153:000000000-B55NK:1:1101:10010:14111   chr8    89574844    A   38  89574844
M01605:153:000000000-B55NK:1:1101:10010:19939   chr3    181151945   T   36  181151945
M01605:153:000000000-B55NK:1:1101:10011:22802   chr17   43984669    A   34  43984765
M01605:153:000000000-B55NK:1:1101:10011:22802   chr17   43984669    A   38  43984689

File2:

M01605:153:000000000-B55NK:1:1101:10003:14536   2:N:0:1 GTTTGCGCCGATGTA 
M01605:153:000000000-B55NK:1:1101:10003:4882    2:N:0:1 GCACTGTAAAAAGTA 
M01605:153:000000000-B55NK:1:1101:10007:14573   2:N:0:1 GGGGATAAGCGTTGC 
M01605:153:000000000-B55NK:1:1101:10007:5336    2:N:0:1 GTGTTTGTGTAGCTA 
M01605:153:000000000-B55NK:1:1101:10008:14477   2:N:0:1 GGGCGGAGGTGAAGA 
M01605:153:000000000-B55NK:1:1101:10009:18543   2:N:0:1 AGTTCGAGCGCAGTG 
M01605:153:000000000-B55NK:1:1101:10009:9160    2:N:0:1 CAGAAGAGGTAATGT 
M01605:153:000000000-B55NK:1:1101:10010:14111   2:N:0:1 CTGCGTACTGATAGC 
M01605:153:000000000-B55NK:1:1101:10010:19939   2:N:0:1 TCCGTGGTGCCGGCA 
M01605:153:000000000-B55NK:1:1101:10011:22802   1:N:0:1 TGAGTTCGGATAAAG

Out:

M01605:153:000000000-B55NK:1:1101:10003:14536 2:N:0:1   GTTTGCGCCGATGTA 
M01605:153:000000000-B55NK:1:1101:10007:14573 2:N:0:1   GGGGATAAGCGTTGC 
M01605:153:000000000-B55NK:1:1101:10009:9160 2:N:0:1    CAGAAGAGGTAATGT 
M01605:153:000000000-B55NK:1:1101:10010:14111 2:N:0:1   CTGCGTACTGATAGC 
M01605:153:000000000-B55NK:1:1101:10010:19939 2:N:0:1   TCCGTGGTGCCGGCA 
M01605:153:000000000-B55NK:1:1101:10011:22802 1:N:0:1   TGAGTTCGGATAAAG

Expected Out:

M01605:153:000000000-B55NK:1:1101:10003:14536 2:N:0:1 GTTTGCGCCGATGTA M01605:153:000000000-B55NK:1:1101:10007:14573 2:N:0:1 GGGGATAAGCGTTGC M01605:153:000000000-B55NK:1:1101:10007:14573 2:N:0:1 GGGGATAAGCGTTGC M01605:153:000000000-B55NK:1:1101:10009:9160 2:N:0:1 CAGAAGAGGTAATGT M01605:153:000000000-B55NK:1:1101:10009:9160 2:N:0:1 CAGAAGAGGTAATGT M01605:153:000000000-B55NK:1:1101:10009:9160 2:N:0:1 CAGAAGAGGTAATGT M01605:153:000000000-B55NK:1:1101:10010:14111 2:N:0:1 CTGCGTACTGATAGC M01605:153:000000000-B55NK:1:1101:10010:19939 2:N:0:1 TCCGTGGTGCCGGCA M01605:153:000000000-B55NK:1:1101:10011:22802 1:N:0:1 TGAGTTCGGATAAAG

Note the bolded lines are missing from the actual output generated and are what I want to be in the output file.

It seems like grep is running correctly, but then condensing all identical lines down into only one output line. Any suggestions?

I believe a line is missing from your expected output: there should be two lines starting with M01605:153:000000000-B55NK:1:1101:10011:22802, right? — xhienne
– xhienne, Commented Sep 14, 2017 at 21:25
Any suggestions? (1) When you ask a question, work harder on explaining the question in clear English. Based on your first paragraph, I have no idea what you want. I have to look at the command you tried and guess what you were trying to do, and that’s not much to go on. Even your presentation of the output you expect leaves me with the job of trying to figure out why you expect that output. … (Cont’d) — Scott - Слава Україні
– Scott - Слава Україні, Commented Sep 14, 2017 at 21:54
(Cont’d) … (2) Read How to create a Minimal, Complete, and Verifiable example (MCVE). The first 37 characters of every line of your data are the same. That makes it very hard to read, and it’s totally unnecessary. Strip off the duplicate characters. And do you really need to give us all ten lines of File1 and all ten lines of File2? I don’t think so; it seems to me that you can illustrate your problem with just the 10007 lines — and maybe also the 10003 lines, to avoid oversimplifying it. — Scott - Слава Україні
– Scott - Слава Україні, Commented Sep 14, 2017 at 21:54
Sorry Scott. First post here and guess I don't know the etiquette. Although it does seem like the others got what was going on. The duplicates are important as that's ultimately what I wanted. Also, ten lines helped to illustrate (at least to me) the point of the problem rather than giving one line, since grep appeared to be compressing them into one line. — William Law
– William Law, Commented Sep 14, 2017 at 22:26

John1024 · Accepted Answer · 2017-09-14 21:28:33Z

As I interpret your desired output, you want lines in File2 repeated as many times as the first field appears in File1. Grep will not do that. Instead, try:

$ awk 'FNR==NR{a[FNR]=$1;next} {for (k in a) if (a[k]==$1) print}' File1 File2
M01605:153:000000000-B55NK:1:1101:10003:14536 2:N:0:1 GTTTGCGCCGATGTA
M01605:153:000000000-B55NK:1:1101:10007:14573 2:N:0:1 GGGGATAAGCGTTGC
M01605:153:000000000-B55NK:1:1101:10007:14573 2:N:0:1 GGGGATAAGCGTTGC
M01605:153:000000000-B55NK:1:1101:10009:9160 2:N:0:1 CAGAAGAGGTAATGT
M01605:153:000000000-B55NK:1:1101:10009:9160 2:N:0:1 CAGAAGAGGTAATGT
M01605:153:000000000-B55NK:1:1101:10009:9160 2:N:0:1 CAGAAGAGGTAATGT
M01605:153:000000000-B55NK:1:1101:10010:14111 2:N:0:1 CTGCGTACTGATAGC
M01605:153:000000000-B55NK:1:1101:10010:19939 2:N:0:1 TCCGTGGTGCCGGCA
M01605:153:000000000-B55NK:1:1101:10011:22802 1:N:0:1 TGAGTTCGGATAAAG
M01605:153:000000000-B55NK:1:1101:10011:22802 1:N:0:1 TGAGTTCGGATAAAG

How it works

FNR==NR{a[FNR]=$1;next}

While reading the first file, File1, store the first field, $1, in array a under the key of the line number, FNR.
for (k in a) if (a[k]==$1) print

While reading the second file, go through each element of array a and print the line each time that a match is found between the first field of File2 and a value of array a.

More efficient alternative

$ awk 'FNR==NR{a[$1]++;next} {for (i=1;i<=a[$1];i++) print}' File1 File2
M01605:153:000000000-B55NK:1:1101:10003:14536 2:N:0:1 GTTTGCGCCGATGTA
M01605:153:000000000-B55NK:1:1101:10007:14573 2:N:0:1 GGGGATAAGCGTTGC
M01605:153:000000000-B55NK:1:1101:10007:14573 2:N:0:1 GGGGATAAGCGTTGC
M01605:153:000000000-B55NK:1:1101:10009:9160 2:N:0:1 CAGAAGAGGTAATGT
M01605:153:000000000-B55NK:1:1101:10009:9160 2:N:0:1 CAGAAGAGGTAATGT
M01605:153:000000000-B55NK:1:1101:10009:9160 2:N:0:1 CAGAAGAGGTAATGT
M01605:153:000000000-B55NK:1:1101:10010:14111 2:N:0:1 CTGCGTACTGATAGC
M01605:153:000000000-B55NK:1:1101:10010:19939 2:N:0:1 TCCGTGGTGCCGGCA
M01605:153:000000000-B55NK:1:1101:10011:22802 1:N:0:1 TGAGTTCGGATAAAG
M01605:153:000000000-B55NK:1:1101:10011:22802 1:N:0:1 TGAGTTCGGATAAAG

It works great for the small test file, but is painfully slow for the actual file size (~2 million lines per file 1 and 2). Any faster ways or am I just stuck waiting. Thanks for the answer though! — William Law
– William Law, Commented Sep 14, 2017 at 21:25
@WilliamLaw I just added alternate awk code which should be more efficient for large files. — John1024
– John1024, Commented Sep 14, 2017 at 21:29
This runs much much faster. However I'm not actually getting the same number of lines in the output that I'm getting in the input (for the larger file size). It's the same problem I'm having with the join command above, but none of the outputs actually agree with each other. — William Law
– William Law, Commented Sep 14, 2017 at 21:35
@WilliamLaw How big are the two files in bytes and in number of lines? — Kusalananda
– Kusalananda ♦, Commented Sep 14, 2017 at 21:37
File 1 is 2232720 lines - 157Mb and File 2 is 1410305 - 132Mb — William Law
– William Law, Commented Sep 14, 2017 at 21:38

xhienne · Accepted Answer · 2017-09-14 22:32:48Z

0

That's exactly what the join command is made for: it joins two files based on a common field:

$ awk '{print $1}' File1 | join - File2

M01605:153:000000000-B55NK:1:1101:10003:14536 2:N:0:1 GTTTGCGCCGATGTA 
M01605:153:000000000-B55NK:1:1101:10007:14573 2:N:0:1 GGGGATAAGCGTTGC 
M01605:153:000000000-B55NK:1:1101:10007:14573 2:N:0:1 GGGGATAAGCGTTGC 
M01605:153:000000000-B55NK:1:1101:10009:9160 2:N:0:1 CAGAAGAGGTAATGT 
M01605:153:000000000-B55NK:1:1101:10009:9160 2:N:0:1 CAGAAGAGGTAATGT 
M01605:153:000000000-B55NK:1:1101:10009:9160 2:N:0:1 CAGAAGAGGTAATGT 
M01605:153:000000000-B55NK:1:1101:10010:14111 2:N:0:1 CTGCGTACTGATAGC 
M01605:153:000000000-B55NK:1:1101:10010:19939 2:N:0:1 TCCGTGGTGCCGGCA 
M01605:153:000000000-B55NK:1:1101:10011:22802 1:N:0:1 TGAGTTCGGATAAAG 
M01605:153:000000000-B55NK:1:1101:10011:22802 1:N:0:1 TGAGTTCGGATAAAG

Your files may be sorted numerically but not alphabetically, as expected by join. If join is complaining, slightly modify the command above to sort the input with GNU sort:

$ awk '{print $1}' File1 | sort | join - <(sort -k1,1 --stable File2)

As your second file seems to have duplicated lines (see coments), you may want to change the second sort command to sort -k1,1 --stable --unique File2 (still assuming you are using GNU sort, use uniq).

edited Sep 14, 2017 at 22:32

answered Sep 14, 2017 at 21:23

xhienne

18.2k2 gold badges58 silver badges71 bronze badges

They are sorted on column 1, but it's claiming they are not. Even so it worked great and ran extremely fast!

William Law
– William Law

2017-09-14 21:29:14 +00:00
Commented Sep 14, 2017 at 21:29
@WilliamLaw The problem is that they are sorted numerically, not alphabetically. If you are certain they are sorted the same way, you may use the --nocheck-order option of GNU join. The best is to sort the files.

xhienne
– xhienne

2017-09-14 21:34:06 +00:00
Commented Sep 14, 2017 at 21:34
1

That makes sense. I'm having a problem with join that it's giving me more lines in the output than in the input file. It also doesn't agree with awk below either.

William Law
– William Law

2017-09-14 21:37:51 +00:00
Commented Sep 14, 2017 at 21:37
1

@WilliamLaw You have multiple identical join keys in the first file. If you have that in the second file as well, you'll get a cross product, increasing the number of resulting lines.

Kusalananda
– Kusalananda ♦

2017-09-14 21:49:20 +00:00
Commented Sep 14, 2017 at 21:49
2

Sorry, needed to step away for a moment. That's exactly what happened. Thank you both so much!

William Law
– William Law

2017-09-14 22:24:20 +00:00
Commented Sep 14, 2017 at 22:24

| Show 7 more comments

Kusalananda · Accepted Answer · 2017-09-14 21:34:57Z

Assuming that both files are sorted in the same way on their first field:

$ join -o1.1,1.2,1.3 file2 file1
M01605:153:000000000-B55NK:1:1101:10003:14536 2:N:0:1 GTTTGCGCCGATGTA
M01605:153:000000000-B55NK:1:1101:10007:14573 2:N:0:1 GGGGATAAGCGTTGC
M01605:153:000000000-B55NK:1:1101:10007:14573 2:N:0:1 GGGGATAAGCGTTGC
M01605:153:000000000-B55NK:1:1101:10009:9160 2:N:0:1 CAGAAGAGGTAATGT
M01605:153:000000000-B55NK:1:1101:10009:9160 2:N:0:1 CAGAAGAGGTAATGT
M01605:153:000000000-B55NK:1:1101:10009:9160 2:N:0:1 CAGAAGAGGTAATGT
M01605:153:000000000-B55NK:1:1101:10010:14111 2:N:0:1 CTGCGTACTGATAGC
M01605:153:000000000-B55NK:1:1101:10010:19939 2:N:0:1 TCCGTGGTGCCGGCA
M01605:153:000000000-B55NK:1:1101:10011:22802 1:N:0:1 TGAGTTCGGATAAAG
M01605:153:000000000-B55NK:1:1101:10011:22802 1:N:0:1 TGAGTTCGGATAAAG

This will perform a relational join operation using the first field as the key. The fields that are outputted are specified with the -o flag as the first three fields of the first file (file2).

I'm noticing that the last line in the output is extra compared to your expected result. I'm assuming this is an error is the question.

The sorting is important. You should either pre-sort the files:

$ sort -o file1 file1
$ sort -o file2 file2

... or sort the files on the fly when calling join (requires a shell that understands process substitution with <(...), like bash):

$ join -o1.1,1.2,1.3 <( sort file2 ) <( sort file1 )

This will be slow if you need to run the join multiple times though, especially if the two files are large.

Stack Exchange Network

Grep not returning identical matches from awk pipe

3 Answers 3

More efficient alternative

You must log in to answer this question.

Hot Network Questions

Grep not returning identical matches from awk pipe

3 Answers 3

More efficient alternative

You must log in to answer this question.

Related

Hot Network Questions