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 at 10:32
    
"game" does not appear in the merged file? –  glenn jackman Apr 3 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 at 11:18
    
join can still work if the line order is not important, see my answer below. –  Thor Apr 3 at 13:36
add comment

3 Answers 3

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 at 10:47
    
The OP will have to tell us. –  glenn jackman Apr 3 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 at 11:08
    
I have a question why you are giving as input file2 before file1 ? –  ekassis Apr 3 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 at 11:24
show 2 more comments

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 at 11:02
add comment

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 at 13:44
add comment

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.