1

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?

4
  • 1
    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
    Sep 14 '17 at 21:25
  • 1
    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
    Sep 14 '17 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
    Sep 14 '17 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. Sep 14 '17 at 22:26
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).

12
  • They are sorted on column 1, but it's claiming they are not. Even so it worked great and ran extremely fast! Sep 14 '17 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
    Sep 14 '17 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. Sep 14 '17 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
    Sep 14 '17 at 21:49
  • 2
    Sorry, needed to step away for a moment. That's exactly what happened. Thank you both so much! Sep 14 '17 at 22:24
2

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
6
  • 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! Sep 14 '17 at 21:25
  • @WilliamLaw I just added alternate awk code which should be more efficient for large files.
    – John1024
    Sep 14 '17 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. Sep 14 '17 at 21:35
  • @WilliamLaw How big are the two files in bytes and in number of lines?
    – Kusalananda
    Sep 14 '17 at 21:37
  • File 1 is 2232720 lines - 157Mb and File 2 is 1410305 - 132Mb Sep 14 '17 at 21:38
0

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.

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.