2

I've been trying to use awk to merge two files when they have the same first column. Here are my example files:

FileA.txt

A2M    1
A4GALT    11
AAAS    35
AAGAB    7

FileB.txt

A4GALT    2
AAAS    17
AAGAB    7

As you can see, the second file is missing the entry for A2M. If I am missing an entry, then I want the entry to read 0 in the final output. As so:

A2M    1    0
A4GALT    11    2
AAAS    35    17
AAGAB    7    7

My lab mate suggested that I use awk since join isn't working properly for me. With some help, I've come up with this awk command:

awk -F "\t" 'FNR==NR {h[$1] = $2;next} BEGIN{ OFS = "\t"} {print $0,$2?h[$1]:"0"}' FileB.txt FileA.txt

However, my output does not output the 0 when there isn't a match in FileB.txt and instead prints nothing. Any idea about what's going wrong?

1 Answer 1

3

If you join two files, it's a job for join:

join -1 1 -2 1 -a 1 -o 1.1 -o 1.2 -o 2.2 -e "0" FileA.txt FileB.txt

Where:

  • -1 1 -2 1 defines the fields to join (in both files the 1st)
  • -a 1 to force join to print the unpairable lines from FileA.txt
  • -o 1.1 1.2 2.2 is the output format
  • and -e "0" defines the value to store in an empty field

The output:

A2M 1 0
A4GALT 11 2
AAAS 35 17
AAGAB 7 7
6
  • When I use this method I get this response usage: join [-a fileno | -v fileno ] [-e string] [-1 field] [-2 field] [-o list] [-t char] file1 file2 and my outputted file has nothing in it. Commented Sep 1, 2015 at 17:05
  • @stellar01 Your join doesn't support the -j flag, you have to use -1 1 -2 1 instead, I edited my answer
    – chaos
    Commented Sep 1, 2015 at 17:11
  • I'm still getting the usage response with the updated code. Commented Sep 1, 2015 at 17:13
  • @stellar01 Edited (the -o flag seems also not portable), try again please^^ If it's not working, can you post the output of join --version?
    – chaos
    Commented Sep 1, 2015 at 17:18
  • Worked perfectly. I gave up on join because I couldn't seem to get it to work, but you found the problem. Thanks a bunch. Commented Sep 1, 2015 at 17:20

You must log in to answer this question.

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