1

I have two files--file1 and file2 that I want to join but some fields are missing in second file for which I want to insert string 'null'. One requirement is that the keys must be in the same order as in file1.

The input files and expected output result are as below:

file1.txt    file2.txt
a 7 nah      a anau
b 0 blah     c bau
c 5 bah      d cau
d 1 gah
e 0 hah

Expected output result:

a 7 nah anau
b 0 blah null
c 5 bah bau
d 1 gah cau
e 0 hah null
1
3

join + sort solution:

join -o1.1,1.2,1.3,2.2 -a1 -e"null" <(sort file1.txt) <(sort file2.txt)

The output:

a 7 nah anau
b 0 blah null
c 5 bah bau
d 1 gah cau
e 0 hah null
3
  • I am getting a different result.
    – mkc
    May 24 '18 at 17:07
  • give some screenshot and specify your OS May 24 '18 at 17:10
  • I tried again. My bad. I misread results in first try. Thanks.
    – mkc
    May 25 '18 at 14:10
1

Solution using join:

join file1.txt file2.txt -e null -o auto -a 1 2>/dev/null

It isn't clear from the man pages, but the -e option only works with -o.

3
  • This is giving error.
    – mkc
    May 24 '18 at 17:06
  • Can you elaborate? I used your example and got the expected results. My version of coreutils is 8.26.
    – m0dular
    May 24 '18 at 17:10
  • I am on mac OSX. Unable to find join's version. The error message is: usage: join [-a fileno | -v fileno ] [-e string] [-1 field] [-2 field] [-o list] [-t char] file1 file2
    – mkc
    May 24 '18 at 17:31
1

I was able to solve it with awk. I found that in my data as in the example, the second field of file1.txt has to be 0 for it to be missing in file2.txt--which helped. The script looks like so:

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

$2!=0{
  print $0,a[$1]
}

$2==0{
  print $0,"null"
}

Invocation:

awk -f merge.awk file2.txt file1.txt

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.