Take the 2-minute tour ×
Unix & Linux Stack Exchange is a question and answer site for users of Linux, FreeBSD and other Un*x-like operating systems.. It's 100% free, no registration required.

I need to merge below 2 files:

  • file1:

TABLES REF-IO HEAD-IO DIFF-IO
test 200 500 -300
exam 2 3 -1
final 2 1 1
mail 4 2 2
TOTAL 208 506 -298

  • file2:

TABLES REF-SELECT HEAD-SELECT DIFF-SELECT
test 5 7 -2
game 3 3 0
exam 0 7 -7
final 12 6 6
TOTAL 20 23 -3

merged file should be as shown below:

TABLES REF-IO HEAD-IO DIFF-IO REF-SELECT HEAD-SELECT DIFF-SELECT
test 200 500 -300 5 7 -2
exam 2 3 -1 0 7 -7
final 2 1 1 12 6 6
mail 4 2 2 0 0 0
TOTAL 208 506 -298 20 23 -3

share|improve this question
    
You might want to see man join. –  devnull Apr 3 '14 at 10:32
    
"game" does not appear in the merged file? –  glenn jackman Apr 3 '14 at 10:39
    
for my case JOIN does not work properly, since I do have the same value in column one of each file. –  ekassis Apr 3 '14 at 11:18
    
join can still work if the line order is not important, see my answer below. –  Thor Apr 3 '14 at 13:36

4 Answers 4

up vote 5 down vote accepted
awk '
    NR==FNR {vals[$1] = $2 " " $3 " " $4; next} 
    !($1 in vals) {vals[$1] = "0 0 0"} 
    {$(NF+1) = vals[$1]; print}
' file2 file1
TABLES REF-IO HEAD-IO DIFF-IO REF-SELECT HEAD-SELECT DIFF-SELECT
test 200 500 -300 5 7 -2
exam 2 3 -1 0 7 -7
final 2 1 1 12 6 6
mail 4 2 2 0 0 0
TOTAL 208 506 -298 20 23 -3
share|improve this answer
    
You answer is better than mine because it gives the exact expected result :p But I'm still wondering whether it is normal not to have "game" in the expected result, whereas there is "mail"... –  Laurent C. Apr 3 '14 at 10:47
    
The OP will have to tell us. –  glenn jackman Apr 3 '14 at 10:48
    
game should not appear since my aim is to merge the 2 files based on only all values of column one of FILE1. (in case a value does not exist in file2 then it should be filled zeros) –  ekassis Apr 3 '14 at 11:08
    
I have a question why you are giving as input file2 before file1 ? –  ekassis Apr 3 '14 at 11:11
    
The NR==FNR condition means the file record number == the cumulative record number, and this is only true for the first file. So I'm reading the values from file2, but not printing any of them. You only care about the keys from file1\ –  glenn jackman Apr 3 '14 at 11:24

This script should work:

touch resultFile
while read f; do
    header1=$(echo $f | awk '{print $1;}');
    values1=$(echo $f | awk -F "$header1 " '{print $NF;}');
    while read g; do
        header2=$(echo $g | awk '{print $1;}');
        values2=$(echo $g | awk -F "$header2 " '{print $NF;}');
        if [ $header1 = $header2 ]; then
            echo "$header1 $values1 $values2" >> resultFile
        fi
    done < file2
done < file1

Headers must be unique, otherwise only the last line found will be merged.

PS: I have considered that "mail" header should not be present in the result as it is not present in both files, as "game" (in your example "mail" is present but not "game").

share|improve this answer
    
mail is intended not to be present in the second file. My aim is to join the 2 files based on only all values of column one of first file. (in case a value does not exist in file2 then it should be filled zeros) –  ekassis Apr 3 '14 at 11:02

In case you were wondering, it is possible to do this with join, although the order of the lines is not preserved:

(
  join <(head -n1 file1) <(head -n1 file2)
  join -a 1 -e 0 -o '0 1.2 1.3 1.4 2.2 2.3 2.4' \
                    <(sed -n 2,5p file1 | sort) \
                    <(sed -n 2,5p file2 | sort)
  join <(tail -n1 file1) <(tail -n1 file2)
)

Output:

TABLES REF-IO HEAD-IO DIFF-IO REF-SELECT HEAD-SELECT DIFF-SELECT
exam 2 3 -1 0 7 -7
final 2 1 1 12 6 6
mail 4 2 2 0 0 0
test 200 500 -300 5 7 -2
TOTAL 208 506 -298 20 23 -3
share|improve this answer
    
It's worth noting that join requires its input files to be sorted--that often makes it tricky for files with headers. As these files have both headers and footers, it could be downright annoying. –  laindir Apr 3 '14 at 13:44
    
@laindir - not really. –  don_crissti Jul 14 at 19:41

Here's how to do it with join and preserve line order, headers, footers etc...

join -1 2 -2 1 -a 1 -e 0 -o 1.1,1.2,1.3,1.4,1.5,2.2,2.3,2.4 \
<(sort -k2,2 <(nl -ba -nrz file1)) <(sort -k1,1 file2) | \
sort -k1,1n | cut -d\  -f2-

result:

TABLES REF-IO HEAD-IO DIFF-IO REF-SELECT HEAD-SELECT DIFF-SELECT
test 200 500 -300 5 7 -2
exam 2 3 -1 0 7 -7
final 2 1 1 12 6 6
mail 4 2 2 0 0 0
TOTAL 208 506 -298 20 23 -3

How it works:

nl -ba -nrz file1 numbers the lines in file1, the output is then sorted on 2nd field; file2 is also sorted on 1st field and the results are then joined on 2nd field from 1st input and 1st field from 2nd input, replacing missing input fields with 0:

000003 exam 2 3 -1 0 7 -7
000004 final 2 1 1 12 6 6
000005 mail 4 2 2 0 0 0
000001 TABLES REF-IO HEAD-IO DIFF-IO REF-SELECT HEAD-SELECT DIFF-SELECT
000002 test 200 500 -300 5 7 -2
000006 TOTAL 208 506 -298 20 23 -3

this is then sorted on 1st field with sort -k1,1n to restore line order and then cut -d\ -f2- removes the line numbers. You could further prettify it with column e.g. .... | column -t:

TABLES  REF-IO  HEAD-IO  DIFF-IO  REF-SELECT  HEAD-SELECT  DIFF-SELECT
test    200     500      -300     5           7            -2
exam    2       3        -1       0           7            -7
final   2       1        1        12          6            6
mail    4       2        2        0           0            0
TOTAL   208     506      -298     20          23           -3
share|improve this answer

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.