Merging two files with columns in bash

Question

I have a file1.txt and the output is:

test4  30
test6  29
test3  17
test2  12
test5  5

This is file is ordered by second column. I sorted it with sort -nr -k 2 .

I have also file2.txt with the content of:

test2   A
test3   B
test4   C
test5   D
test6   E

What I want as result(result.txt) is:

test4   C  30
test6   E  29
test3   B  17
test2   A  12
test5   D  5

I used `awk` for creating `file1.txt` and `file2.txt`. But I couldn't create result.txt . — MLSC, Jul 24 '14 at 12:49
Refer this link this may help for your requirement. http://stackoverflow.com/questions/5467690/how-to-merge-two-files-using-awk — Chandru, Jul 24 '14 at 12:56
You should post what you have tried. This way your post may not have been closed. — Jotne, Jul 24 '14 at 17:10

Aserre · Answer 1 · 2014-07-24T12:59:05.120

Don't sort the file before processing it, keep the sorting by 1st column.

Assuming you have :

file1          file 2
________________________
test2  12      test2  A
test3  17      test3  B
test4  30      test4  C
test5  5       test5  D
test6  29      test6  E

Using join file2 file1 | sort -nr -k 3 will yield :

test4   C  30
test6   E  29
test3   B  17
test2   A  12
test5   D  5

use -t' ' if you want your spacing unmodified by join

konsolebox · Accepted Answer · 2014-07-24T13:07:39.183

Using awk:

awk 'FNR == NR { a[$1] = $2; next } { print $1,  a[$1], $2 }' file2 file1

Output:

test4 C 30
test6 E 29
test3 B 17
test2 A 12
test5 D 5

If file1 is not yet sorted, you can do:

sort -nr -k 2 file1 | awk 'FNR == NR { a[$1] = $2; next } { print $1, a[$1], $2 }' file2 -

Or

awk 'FNR == NR { a[$1] = $2; next } { print $1,  a[$1], $2 }' file2 <(sort -nr -k 2 file1)

There are many ways to format the output. You can use column -t:

... | column -t

Output:

test4  C  30
test6  E  29
test3  B  17
test2  A  12
test5  D  5

Or you can use printf. Although I'd prefer using column -t since table would be broken if one column grows larger than the actual size that printf has provided.

... { printf "%s%3s%4.2s\n", $1, a[$1], $2 }' ...

Output:

test4  C  30
test6  E  29
test3  B  17
test2  A  12
test5  D   5

Merging two files with columns in bash

2 Answers2