Join two files with different number of columns and rows

Question

I have two files. file_1.txt looks like this:

R1     C1     C2     C3     C4     C5
R2     C1     C2     C3     C4     C5
R3     C1     C2     C3     C4     C5
R4     C1     C2     C3     C4     C5
R5     C1     C2     C3     C4     C5
R6     C1     C2     C3     C4     C5
R7     C1     C2     C3     C4     C5
R8     C1     C2     C3     C4     C5
R9     C1     C2     C3     C4     C5
R10    C1     C2     C3     C4     C5

file_2.txt looks like this:

R4 C4 C5
R6 C4 C5
R7 C4 C5
R9 C4 C5

I would like to replace the C4 and C5 values in file_1.txt by those that correspond to them from file_2.txt, while keeping the C1, C2, and C3 values in file_1.txt unchanged.

So the resulting file_3.txt should look like this:

R1     C1     C2     C3     C4     C5
R2     C1     C2     C3     C4     C5
R3     C1     C2     C3     C4     C5
R4     C1     C2     C3     C4_new C5_new
R5     C1     C2     C3     C4     C5
R6     C1     C2     C3     C4_new C5_new
R7     C1     C2     C3     C4_new C5_new
R8     C1     C2     C3     C4     C5
R9     C1     C2     C3     C4_new C5_new
R10    C1     C2     C3     C4     C5

All values are numbers. The first columns in file_1.txt and file_2.txt are key fields and are sorted in ascending numerical order.

Is this something that join alone can do?

Answer 1

This problem makes for a typical application of awk

awk 'NR == FNR{a1[$1]=$2; a2[$1]=$3; next};
    $1 in a1{$5=a1[$1]; $6=a2[$1]};{print}' file_2.txt file_1.txt     

You may have to set the output field separator explicitly to tab, in which case

awk -v OFS='\t' 'NR == FNR{a1[$1]=$2; a2[$1]=$3; next};
    $1 in a1{$5=a1[$1]; $6=a2[$1]};{print}' file_2.txt file_1.txt 

Answer 2

This doesn't solve your problem but rather shows you why join may in name sound like it should work in this particular situation but doesn't. I spent a fair amount of time trying to force join to work in a similar problem to yours on this site, titled: Joining two files with unique identifier.

1st approach

An approach to a solution to your problem using join would go something like this:

$ join -a1 -1 1 -2 1 -o 1.1 1.2 1.3 1.4 2.2 2.3 <(sort file_1.txt) <(sort file_2.txt)
R10 C1 C2 C3  
R1 C1 C2 C3  
R2 C1 C2 C3  
R3 C1 C2 C3  
R4 C1 C2 C3 C4_new C5_new
R5 C1 C2 C3  
R6 C1 C2 C3 C4_new C5_new
R7 C1 C2 C3 C4_new C5_new
R8 C1 C2 C3  
R9 C1 C2 C3 C4_new C5_new

As you can see join needs to put your files into a sorted form, so for starters, if the original order of your files is important this option could pose problems there.

Additionally there is no way to tell join to conditionally print a column's value from one of the files or the other based on either its value or the presence of a value, or lack of a value.

2nd approach

Another approach with join might be this:

$ join -a1 -1 1 -2 1  <(sort file_1.txt) <(sort file_2.txt)
R10 C1 C2 C3 C4 C5
R1 C1 C2 C3 C4 C5
R2 C1 C2 C3 C4 C5
R3 C1 C2 C3 C4 C5
R4 C1 C2 C3 C4 C5 C4_new C5_new
R5 C1 C2 C3 C4 C5
R6 C1 C2 C3 C4 C5 C4_new C5_new
R7 C1 C2 C3 C4 C5 C4_new C5_new
R8 C1 C2 C3 C4 C5
R9 C1 C2 C3 C4 C5 C4_new C5_new

Again it's close to what you want but doesn't allow for you to use any conditional logic to print a column from one file or the other.

3rd approach

This one works, but we had to break down and get some outside assistance from awk to omit the trailing columns that get displaced when their counterparts exist in file_2.txt.

$ join -a1 -1 1 -2 1 -o 1.1 1.2 1.3 1.4 2.2 2.3 1.5 1.6 1.7 <(sort file_1.txt) <(sort file_2.txt) | awk '{$7=$8=""}1'
R10 C1 C2 C3 C4 C5  
R1 C1 C2 C3 C4 C5  
R2 C1 C2 C3 C4 C5  
R3 C1 C2 C3 C4 C5  
R4 C1 C2 C3 C4_new C5_new  
R5 C1 C2 C3 C4 C5  
R6 C1 C2 C3 C4_new C5_new  
R7 C1 C2 C3 C4_new C5_new  
R8 C1 C2 C3 C4 C5  
R9 C1 C2 C3 C4_new C5_new  

Use just join?

Join has a very narrow niche where it can be a useful tool. For a substitution type of problem, which your's is more inline with, a tool such as awk, perl, or sed would be a better fit.