CODEWITHSUNDEEP
bashawk
Anand Abhay
Anand Abhay

Reputation: 359

subtract columns from 2 files and output to new files

I have 2 files in below formats.

File1_Stored.txt
ABC:100, 83
ABC:84, 53
ABC:14, 1222

File2_Stored.txt
ABC:100 , 83
ABC:84 , 1553
ABC:524 , 2626

I am trying to get the 3rd file in below format. So, whenever difference is 0 it shouldnt appear but if the diffence is not 0 then it should appear like 

Value , File1 Value , File2 Value , Difference
----------------------------------------------
ABC:84,  53          ,1553         , -1500
ABC:14,  1222        , 0           , 1222
ABC:524, 0           ,2626         ,-2626

I tried doing it using awk to get the difference but it failing

awk 'NR==FNR{A[$1]=$2;B[$1]=$2}{$2-=A[$1]}1' File1_Stored.txt  File2_Stored.txt

Any help is much appriciated.

Regards,

Upvotes: 2

Views: 78

Answers (3)

markp-fuso
markp-fuso

Reputation: 34124

awk script:

$ cat file.awk
BEGIN { FS=" *, *" }
NR==FNR { A[$1]=$2 ;      B[$1]=0  ; next }
        { A[$1]=A[$1]+0 ; B[$1]=$2 }
END {
for (x in A)
    { diff=A[x]-B[x]
      if (diff!=0) { printf "%s,%s,%s,%s\n",x,A[x],B[x],diff }
    }
}
  • BEGIN { FS=" *, *" } : define input field separator as a) zero or more spaces plus b) a comma plus c) zero or more spaces
  • NR==FNR : for first file ...
  • A[$1]=$2 : store values in array A and ...
  • B[$1]=0 : zero out an associated entry in the B array
  • { A[$1]=A[$1]+0... : for second file add zero to associated A array (this has the effect of creating a new A array item if it doesn't already exist), then store current record value in array B
  • at this point we should have entries in both arrays for all possible key values
  • END {...} : after processing the input files has completed ...
  • for (x in A) : loop through indexes of array A
  • diff=... : find the difference between corresponding array values
  • if (diff!=0) ... : if our difference is not zero then print the results

Script in action:

$ awk -f file.awk  File1_Stored.txt  File2_Stored.txt
ABC:524,0,2626,-2626
ABC:14,1222,0,1222
ABC:84,53,1553,-1500

Upvotes: 1

RomanPerekhrest
RomanPerekhrest

Reputation: 92854

awk solution:

awk -F' *, *' 'BEGIN{ fmt="%s,  %d,  %d,  %d\n" }   # output format
               NR==FNR{ a[$1]=$2; next }
               {   
                   d=a[$1]-$2;   # difference
                   if ($1 in a && d==0) { delete a[$1]; next }; 
                   printf fmt, $1,a[$1]+0,$2,a[$1]-$2; delete a[$1] 
               }
               END{ 
                   for(i in a) printf fmt, i,a[i],0,a[i];  # check for unmatched items
               }' file1 file2

The output:

ABC:84,  53,  1553,  -1500
ABC:524,  0,  2626,  -2626
ABC:14,  1222,  0,  1222

Upvotes: 2

Ed Morton
Ed Morton

Reputation: 203229

$ cat tst.awk
BEGIN {
    FS="[[:space:]]*,[[:space:]]*"
    OFS=","
    print "Value", "File1 Value", "File2 Value", "Difference"
}
NR==FNR {
    a[$1] = $2
    next
}
{
    diff = a[$1] - $2
    if ( diff != 0 ) {
        print $1, a[$1]+0, $2, diff
    }
    delete a[$1]
}
END {
    for (key in a) {
        print key, a[key], 0, 0
    }
}

$ awk -f tst.awk file1 file2
Value,File1 Value,File2 Value,Difference
ABC:84,53,1553,-1500
ABC:524,0,2626,-2626
ABC:14,1222,0,0

Upvotes: 2

Related Questions