Skip to main content
added 600 characters in body
Source Link
Stéphane Chazelas
  • 587.9k
  • 96
  • 1.1k
  • 1.7k

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}' 

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]' 

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}' 
Source Link
Stéphane Chazelas
  • 587.9k
  • 96
  • 1.1k
  • 1.7k

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]'