# Join two files with different number of columns and rows

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?

-

## 2 Answers

This problem makes for a typical application of

``````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
``````
-

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.

-