Join: Two files - but only append last two columns

Question

Given the files:

1.txt

1, abc, 123, 456, 789
2, lmn, 123, 456, 789
3, pqr, 123, 456, 789

2.txt

1, abc, 123, 000, 000
3, lmn, 123, 000, 000
9, opq, 123, 000, 000  

OUTPUT.txt

ID, NAME, X,    1A,    1B,  2A,   2B   
1, abc, 123, 456, 789, 000, 000
2, lmn, 123, 456, 789, MISSING, MISSING
3, pqr, 123, 456, 789, 000, 000
9, opq, 123, MISSING, MISSING, 000, 000 

I'ved used this for reference.

I tried using the following:

join -t , -a1 -a2 -1 1 -2 1 -o 0 -o 1.2 -o 1.3 -o 1.4 -o 1.5 -o 2.4 -o 2.5 -e "MISSING" 1.txt 2.txt

Which produces:

1, abc, 123, 456, 789, 000, 000
2, lmn, 123, 456, 789,MISSING,MISSING
3, pqr, 123, 456, 789, 000, 000
9,MISSING,MISSING,MISSING,MISSING, 000, 000

Any help?

Answer 1

I don't think you can do it with join alone. You could do:

join -t, -a1 -a2 -o0,1.2,1.3,1.4,1.5,2.2,2.3,2.4,2.5 -e MISSING 1.txt 2.txt |
  perl -F, -lape '@F[1..2]=@F[5..6] if $F[1] eq "MISSING";
                  $_=join",",@F[0..4],@F[7..8]'

• -p: use a line by line reading loop like in sed/awk
• -a, -F,: like awk, split the lines into fields (into the @F array).
• -l: works on the content of lines (works like awk where the input is split on RS ($/) (but RS not included in $0) and ORS ($\) is appended before printing).
• -e ...: perl [e]xpression to evaluate for each line.
• Then it reads almost like English: fields 1 to 2 are set to fields 5 to 6 if field 1 (the second field as indexes start at 0) is "MISSING". Then set the content of the current record ($_ is like $0 in awk) to the fields 0 to 4 and 7 to 8.

Actually, writing the same in awk is not more complicated:

awk -F, -vOFS=, '$2 == "MISSING"{$2=$6;$3=$7}
                 {print $1,$2,$3,$4,$5,$8,$9}'

Answer 2

using just awk:

awk -F, -v OFS=, '
    BEGIN {m = " MISSING"}

    # process file1
    NR == FNR {lines[$1] = $0; next} 

    # process file2
    {
        added[$1] = $4 OFS $5
        if (!($1 in lines)) {
            $4 = m
            $5 = m
            lines[$1] = $0
        }
    } 

    # print the combined output
    END {
        for (id in lines) {
            if (!(id in added)) 
                added[id] = m OFS m
            print lines[id], added[id]
        }
    }
' 1.txt 2.txt | sort -n

1, abc, 123, 456, 789, 000, 000
2, lmn, 123, 456, 789, MISSING, MISSING
3, pqr, 123, 456, 789, 000, 000
9, opq, 123, MISSING, MISSING, 000, 000  

Answer 3

Sounds like you want to join on first three fields. You should then change the first two delimiters, join on the new 1st field and then restore the delimiters:

join -t, -j1 -a1 -a2 -o 0 1.2 1.3 2.2 2.3 -e " MISSING" \
<(sed 's/, /\x02/;s/, /\x02/' 1.txt) <(sed 's/, /\x02/;s/, /\x02/' 2.txt) \
| sed 's/\x02/, /g'

returns

1, abc, 123, 456, 789, 000, 000
2, lmn, 123, 456, 789, MISSING, MISSING
3, pqr, 123, 456, 789, 000, 000
9, opq, 123, MISSING, MISSING, 000, 000