UNIX Command Line

Input file:

$ cat expense.txt
Particulars,Item1,Item2,Item3
BudgetAmount,12000,4560,5000
Expense@2006,1800,3000,250
Expense@2007,2210,2100,3000
Expense@2008,100,1500,320
Expense@2009,0,100,20

Output required:

For all the items, calculate the amount left after expense i.e.

For an item:
Amount Left = (BudgetAmount - (Expense@2006 + Expense@2007 + Expense@2008 + Expense@2009))

i.e. required output:

BudgetAmount,12000,4560,5000
Expense@2006,1800,3000,250
Expense@2007,2210,2100,3000
Expense@2008,100,1500,320
Expense@2009,0,100,20
Amount Left,7890,-2140,1410

Microsoft Excel representation of the above:




The awk program:

$ awk 'BEGIN { FS=OFS="," }
$1 == "BudgetAmount" {
         bI1 = $2
         bI2 = $3
         bI3 = $4
         print
}
/^Expense@/ {
         bI1 -= $2
         bI2 -= $3
         bI3 -= $4
         print
}
END {
         print "Amount Left",bI1, bI2, bI3
}' expense.txt

Related post:

- Bash script for sequential subtraction of numbers