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:

    viewer  23
    test    27
    remark  2
    
  • file2:

    viewer  2990
    exam    200
    remark  240
    

merged file should be as shown below:

type    value1  value2  difference
viewer  23      2990       -2967
test    27        0          27
remark  2        240        -238
exam    0        200        -200
share|improve this question
    
I removed the blank lines from your files, if they are actually part of the original file, please let me know and I'll fix it. –  terdon Apr 1 at 18:13
    
thank you Terdon. –  ekassis Apr 2 at 11:33

2 Answers 2

up vote 3 down vote accepted

The 'x' file:

FNR==1 {
    ++FILENO
}
{
    T[$1]++
    X[$1,FILENO]=$2
}
END {
    print "type\tvalue1\tvalue2\tdifference"
    for(t in T)
        print t"\t"X[t,1]+0"\t"X[t,2]+0"\t"0+X[t,1]-X[t,2]
}

The run:

$ awk -f x file1 file2
type    value1  value2  difference
viewer  23      2990    -2967
remark  2       240     -238
test    27      0       27
exam    0       200     -200

Version with line for "total", sorting and skipping lines without difference:

FNR==1 {
    ++FILENO
}
{
    T[$1]++
    X[$1,FILENO]=$2
}
END {
    print "type\tvalue1\tvalue2\tdifference"
    for(t in T) {
        if(X[t,1]!=X[t,2])
            print t"\t"X[t,1]+0"\t"X[t,2]+0"\t"0+X[t,1]-X[t,2] | "sort"
        S[1]+=X[t,1]
        S[2]+=X[t,2]
    }
    close("sort")
    print "total\t"S[1]+0"\t"S[2]+0"\t"S[1]-S[2]+0
}

I added a line "yawns 3" to both input files...

file1:

yawns   3
viewer  23
test    27
remark  2

file2:

viewer  2990
yawns   3
exam    200
remark  240

Running:

$ awk -f x file1 file2
type    value1  value2  difference
exam    0       200     -200
remark  2       240     -238
test    27      0       27
viewer  23      2990    -2967
total   55      3433    -3378

...and "yawns" does not show in the output.

GAWK can sort without external sort, but as long as possible, I prefer not to unse GAWKy features.

share|improve this answer
    
hi yeti, I got below errors when launching your command. awk: syntax error near line 6 awk: illegal statement near line 6 awk: syntax error near line 11 awk: illegal statement near line 11 –  ekassis Apr 2 at 9:07
    
I took exactly the code from here using cut'n'paste to verify it again and it runs on my system without any errors. –  yeti Apr 2 at 10:45
    
I should be using a Linux machine? –  ekassis Apr 2 at 11:12
    
I tried this on linux. Nevertheless I do not see why AWK on other systems should behave different. –  yeti Apr 2 at 11:16
    
It worked perfectly on Linux. Do you know how I can fix it on SunOS? –  ekassis Apr 2 at 11:19

Another awk solution:

$ awk '
FNR==NR {
    a[$1] = $2;
    c[$1] = $1;
    next;
}
{
    b[$1] = $2;
    c[$1] = $1;
}   
END {
    printf "type\tvalue1\tvalue2\tdifference\n";
    for(i in c)
        printf "%s\t%d\t%d\t%d\n", i, a[i], b[i], a[i]-b[i]
}' file1 file2
type    value1  value2  difference
remark  2       240     -238
test    27      0       27
viewer  23      2990    -2967
exam    0       200     -200
share|improve this answer
    
hi Gnouc, when running your command I had the Column value1 all equal to 0. –  ekassis Apr 2 at 9:04
    
Are you sure you type the command correctly? –  cuonglm Apr 2 at 10:22
    
yes I'm sure I did. bash$ awk 'FNR==NR {a[$1] = $2;c[$1] = $1;next;}{b[$1] = $2;c[$1] = $1;} END {printf "TABLE\tREF\tHEAD\tdifference\n"; for(i in c) printf "%s\t%d\t%d\t%d\n", i, a[i], b[i], a[i]-b[i]}' file1 file2 TABLE REF HEAD difference viewer 0 2990 -2990 exam 0 200 -200 remark 0 240 -240 test 0 27 -27 –  ekassis Apr 2 at 11:13
    
Please check your input file, remove all blank line. Here is my result when running above command:pastebin.com/ugxK6R5E –  cuonglm Apr 2 at 11:19
    
Even on Linux it is not working. –  ekassis Apr 2 at 11:20

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.