3

I’m taking two input files, one with certain ID numbers, and another with a large list of ID numbers and additional columns. The latter file contains multiple lines for each ID number and I need to extract all lines that match an ID from the first file. Those lines then must be printed in a new file.

Edit 1: Replaced sample files with excerpts from actual

Edit 2: Removed extra spaces that were in excerpt, but not actual file. Files likely need to be sanitized in some way, but how is unclear.

file1:

AT1G56430
AT3G55190
AT3G22880

file2:

AT1G01010|GO:0043090|RCA
AT1G56430|GO:0010233|IGI 
AT1G56430|GO:0009555|IGI 
AT1G56430|GO:0030418|IGI

expected output

AT1G56430|GO:0010233|IGI 
AT1G56430|GO:0009555|IGI 
AT1G56430|GO:0030418|IGI

[file1ss[file2ss

I have tried:

awk -F'|' 'NR==FNR{c[$1$2]++;next};c[$1$2] > 0' file1 file2 > output.txt

and:

grep -Ff file2 file1 > output.txt

I’m aware that there are many somewhat similar questions posted in these forums and others. However, these don’t mention how the output is handled… nor do they mention duplicates. I’ve tried solutions from 4 of them, have been messing with this for many hours and keep getting the same problem: a blank output file.

I’m new to awk and I greatly appreciate the help. Sorry if this is a simple problem with syntax etc; please let me know. Thanks for the help.

  • did you try my awk command ? – Kamaraj Jan 3 '17 at 8:35
  • Yes I did; not clear what's preventing it from working. – Michael Nagle Jan 3 '17 at 8:43
  • What does grep AT1G56430 file2 output? How about file file1 file2? (Could there be an issue with Windows line-endings?) – Stephen Kitt Jan 3 '17 at 8:47
  • post the output of od -c < file1 – Kamaraj Jan 3 '17 at 8:56
  • The files were prepared in Microsoft Excel 2008 for Mac. grep AT1G56430 file2 appears to output the entire file, surprisingly. file file1 file2 gives file1: ASCII text, with CR line terminators file2: ASCII text, with CR line terminators... od -c <file1 gives... 0000000 A T 1 G 5 6 4 3 0 \r A T 3 G 5 5 0000020 1 9 0 \r A T 3 G 2 2 8 8 0 \r A T and many more lines – Michael Nagle Jan 3 '17 at 9:02
3

Your AWK script is nearly there:

awk -F'|' 'NR==FNR{c[$1]++;next};c[$1] > 0' file1 file2 > output.txt

works, after changing the line-endings from Mac to Unix:

tr '\r' '\n' < file1 > file1.new
mv file1.new file1
tr '\r' '\n' < file2 > file2.new
mv file2.new file2

$1 is the first field in AWK.

Instead of c[$1] > 0, you can write c[$1]. The > 0 isn't needed: any non-zero value works, so we might as well use the contents of c directly:

awk -F'|' 'NR==FNR{c[$1]++;next};c[$1]' file1 file2 > output.txt
0

give a try this awk command.

bash-4.1$ cat file1
1
3

bash-4.1$ cat file2
1|A|B
1|C|D
2|E|F
3|G|H

bash-4.1$ awk -F\| 'NR==FNR{F1[$0];next}$1 in F1{print}' file1 file2
1|A|B
1|C|D
3|G|H
  • I entered the command exactly as follows and am getting the same issue. cat file1 | cat file2 | awk -F\| 'NR==FNR{F1[$0];next}$1 in F1{print}' file1 file2 > output7.txt The purpose of concatenation isn't clear to me, nor is the reason for using cat file1 | cat file2 instead of cat file1 file2. I would appreciate clarification and further advice. Thanks. – Michael Nagle Jan 3 '17 at 7:15
  • cat file1 and cat file2 is to show the contents of my file. for the solution, just run the awk command. – Kamaraj Jan 3 '17 at 8:00
0

Have you tried join already?

$ tail -n +1 -- file1 file2
==> file1 <==
AT1G56430
AT3G55190
AT3G22880

==> file2 <==
AT1G01010|GO:0043090|RCA
AT1G56430|GO:0010233|IGI 
AT1G56430|GO:0009555|IGI 
AT1G56430|GO:0030418|IGI

$ join -t'|' file1 file2
AT1G56430|GO:0010233|IGI 
AT1G56430|GO:0009555|IGI 
AT1G56430|GO:0030418|IGI
  • I'm having the same problem with this. Perhaps it's some problem with my files, as I mentioned as a reply to another answer here, but we're having a hard time figuring out what that problem is. – Michael Nagle Jan 3 '17 at 7:52

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.