Join two files, keep key order of first file and fill missing values with string 'null'

Question

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

Solutions proposed in https://unix.stackexchange.com/questions/34655/join-filling-in-missing-key-values does not work for me. — Ketan Maheshwari, May 24 '18 at 17:03

score 3 · Accepted Answer · answered May 24 '18 at 17:03

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

answered May 24 '18 at 17:03

RomanPerekhrest

29,703
3
43
67

I am getting a different result. – Ketan Maheshwari May 24 '18 at 17:07
give some screenshot and specify your OS – RomanPerekhrest May 24 '18 at 17:10
I tried again. My bad. I misread results in first try. Thanks. – Ketan Maheshwari May 25 '18 at 14:10

m0dular · Answer 2 · 2018-05-24T17:12:34.463

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.

edited May 24 '18 at 17:12

answered May 24 '18 at 17:04

m0dular

1,231
7
8

This is giving error. – Ketan Maheshwari 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 – Ketan Maheshwari May 24 '18 at 17:31

score 1 · Answer 3 · answered May 24 '18 at 18:54

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

Join two files, keep key order of first file and fill missing values with string 'null'

3 Answers3