1

I need to subtract second and third columns between 2 files based on the first column and do not show rows with diff_column_2(DIFF-IO)=diff_column_3(DIFF-SELECT)=0. Noting that the order can be random except the last row which exists in both files and should remain at the END in the output.

The ref_file:

    testing 20 10
    jobs 15 20
    issues 0 1
    work 15 25
    Total 50 56

The head_file:

    testing 20 10
    jobs 15 30
    work 12 25
    games 1 2
    Total 48 67

Desired output:

TABLE,REF-IO,HEAD-IO,DIFF-IO,REF-SELECT,HEAD-SELECT,DIFF-SELECT
jobs,15,15,0,20,30,-10
work,15,12,3,25,25,0
games,0,1,-1,0,2,-2
issues,0,0,0,1,0,1
Total,50,48,2,56,67,-11
  • What have you tried so far? Showing your current code would help clarify what you are trying to do here. – John1024 Nov 7 '15 at 2:03
  • @John1024 I need to substract column 2 of ref_file by column 2 of head_file and show their diffrence, in addition substract column 3 of ref_file by column 3 of head_file and show their difference. All the above based on column 1 of both file and the output should not contain the rows with diff_column_2(DIFF-IO) = diff_column_3(DIFF-SELECT) = 0. Noting that the order can be random except the last row which exists in both files and should remain at the END in the output. – ekassis Nov 7 '15 at 8:49
  • Are the both files have same column No.1 and number of rows? – Costas Nov 7 '15 at 9:06
  • @Costas, No the files may have some different column No. 1 values and of course have different number of rows. – ekassis Nov 7 '15 at 9:22
  • And how to operate row which is not exist in head_file but exist in ref_file? – Costas Nov 7 '15 at 9:26
1
awk '
    BEGIN {
        print "TABLE,REF-IO,HEAD-IO,DIFF-IO,REF-SELECT,HEAD-SELECT,DIFF-SELECT"
        OFS = ", "
    }
    FNR==NR {
        A[$1]=$2
        B[$1]=$3
        next
    }
    {
        if (!($1 in A)) {
            A[$1] = B[$1] = 0
        }
        diff_io = $2 - A[$1]
        diff_sel= $3 - B[$1]
        C[$1] = 1
    }
    diff_io || diff_sel {
        if (first) {
            print line
        }
        first = 1
        line = $1 OFS $2 OFS A[$1] OFS diff_io OFS $3 OFS B[$1] OFS diff_sel
    }
    END {
        for (name in A) {
            if (!(name in C)) {
                print name, 0, A[name], -A[name], 0, B[name], -B[name]
            }
        }
        print line
    }
    ' head_file ref_file

To short typing you can do the task via join:

join -a1 -a2 -e0 <(sort head_file) <(sort ref_file) -o0,1.2,2.2,0,1.3,2.3 |
awk '
    BEGIN {
        print "TABLE,REF-IO,HEAD-IO,DIFF-IO,REF-SELECT,HEAD-SELECT,DIFF-SELECT"
        OFS = ", "
    }
    {
        $4=$2-$3
        $7=$5-$6
    }
    /Total/ {
        end=$0
        next
    }
    $4!=0 || $7!=0;
    END {
        print end
    }'
  • column No. 1 values that exist in one of the files should always be displayed as shown in my example – ekassis Nov 7 '15 at 9:58
  • @ekassis see updated – Costas Nov 7 '15 at 10:00
  • having wrong output. nawk 'FNR==NR {A[$1]=$2;B[$1]=$3; next}{if ( $1 not in A ) {A[$1] = B[$1] = 0}; diff_io = $2 - A[$1];diff_sel= $3 - B[$1];C[$1] = 1}; diff_io || diff_sel {print line; line = $1 " " $2 " " A[$1] " " diff_io " " $3 " " B[$1] " " diff_sel}END {for (name in A) {if ( name not in C ) {print name, 0, A[name], -A[name], 0, B[name], -B[name]}} print line}' head_file ref_file testing 20 0 20 10 0 10 jobs 15 0 15 20 0 20 issues 0 0 1 1 work 15 0 15 25 0 25 Total 0 0 -0 0 0 -0 issues 0 -0 0 -0 jobs 0 0 -0 0 0 -0 testing 0 0 -0 0 0 -0 work 0 0 -0 0 0 -0 Total 50 0 50 26 0 26 – ekassis Nov 7 '15 at 10:24
  • @ekassis Sorry it is my mistake: there is no not option in awk-syntax – Costas Nov 7 '15 at 10:44
  • Is there a way to include the header print in the command? – ekassis Nov 7 '15 at 11:31

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

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