5

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?

Improve this question

3 Answers 3

Reset to default
8

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}'
Improve this answer
0
5

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
Improve this answer
0

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
Improve this answer

You must log in to answer this question.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.