Linux - Calculations between multiple rows of data

Shell Programming and Scripting
1

Morning All,

I am needing assistance with a calculation process, which performs calculations on a group of records.

Here is a breakdown of my requirement:

Col1 = Always same value.
Col2 = Grouping Column, and will have the same value for 5/6/7 records for example.
Col3 = Date
Col4 = Amount 1
Col5 = Amount 2
Col6 = Amount 3 (to be derived as part of this code)
Col7 = Amount 4 (to be derived as part of this code)

Here is an example piece of input data:

I need to perform inter row calculations for each group of data (Col2 e.g. All the 00000001 records together):

  1. Take the first group of records (all 00000001 values in Col2):
  • If the record is the first record in the array, Amount 3 needs to be set to 100.
  • For all other records, it needs to be: "(Col5 current record / Col5 First record)*100"
 E.g. Second record -- "(80,000/110,000)*100" 

 Third Record -- "(77,000/110,000)*100"

And so on....

Output Result:

  1. Using the data derived above, I now need to find the midpoint values:
  • I need to take the current row col6 value and add it together with the row below col6 value (unless it is the last record of the group, and therefore it will be 0), and then divide by 2 to get the midpoint "(Col6 current record + Col6 below record)/2"
    E.g. First Record --
(100+72.72727273)/2

Second Record --

(72.72727273+70)/2

And so on....
Output Result:

Any help would be much appreciated. I am sure I could perform the above via a normal Linux Loop function, however this will be running over large volumes of data, and therefore I am sure something like Awk will be more efficient.

2

Hello RichZR,

Thank you for using code tags as per forum rules:b:. You could use code tags for sample Inputs too which you have shown us in you post.
Following may help you in same.
For your 1st requirement following may help you:

awk -F"|" 'NR==1{$(NF+1)="col6";print;next} NR==2{$(NF)=100;VAL=$(NF-1);print;next} {$(NF)=$(NF-1) * 100/VAL;print}' OFS="|"  Input_file > Output_file

Output will be as follows.

Col1|Col2|Col3|Col4|Col5|col6
ABC|00000001|15-Dec-15|13,400|110,000|100
ABC|00000001|31-Jan-16|13,490|80,000|72.7273
ABC|00000001|29-Feb-16|13,500|77,000|70
ABC|00000001|31-May-16|40,200|37,000|33.6364
ABC|00000001|31-Aug-16|42,000|0|0
ABC|00000002|15-Dec-15|13,400|110,000|100
ABC|00000002|31-Jan-16|13,490|80,000|72.7273
ABC|00000002|29-Feb-16|13,500|77,000|70
ABC|00000002|31-May-16|40,200|37,000|33.6364
ABC|00000002|31-Aug-16|42,000|0|0

Now for your 2nd requirement you could try as follows. Let's say we have taken output of 1st requirement into a file named Output_file .

awk -F"|" 'NR==1{$(NF+1)="col7";print;next} {B[++i]=$0;A=$NF} END{for(i=1;i<NR;i++){VAL=(A + A[i+1])/2;print B FS VAL}}' OFS="|"   Output_file

Output will be as follows.

Col1|Col2|Col3|Col4|Col5|col6|col7
ABC|00000001|15-Dec-15|13,400|110,000|100|86.3637
ABC|00000001|31-Jan-16|13,490|80,000|72.7273|71.3637
ABC|00000001|29-Feb-16|13,500|77,000|70|51.8182
ABC|00000001|31-May-16|40,200|37,000|33.6364|16.8182
ABC|00000001|31-Aug-16|42,000|0|0|50
ABC|00000002|15-Dec-15|13,400|110,000|100|86.3637
ABC|00000002|31-Jan-16|13,490|80,000|72.7273|71.3637
ABC|00000002|29-Feb-16|13,500|77,000|70|51.8182
ABC|00000002|31-May-16|40,200|37,000|33.6364|16.8182
ABC|00000002|31-Aug-16|42,000|0|0|0

Thanks,
R. Singh

3

In one go:

awk -F\| '
NR == 1         {print $0 "|Col6|Col7"
                 next
                }
$2 != LK        {LK = $2
                 LV = $5
                 IX = 1
                }
                {PC = $5 / LV * 100
                 if (NR > 2) printf "|%s\n", IX?0:(LP + PC) / 2
                 LP = PC
                 IX = 0
                }
                {printf "%s%s", $0, LP
                }
END             {printf "|%s\n", (LP + PC) / 2
                }
' file
Col1|Col2|Col3|Col4|Col5|Col6|Col7
ABC|00000001|15-Dec-15|13,400|110,000|100|86.3636
ABC|00000001|31-Jan-16|13,490|80,000|72.7273|71.3636
ABC|00000001|29-Feb-16|13,500|77,000|70|51.8182
ABC|00000001|31-May-16|40,200|37,000|33.6364|16.8182
ABC|00000001|31-Aug-16|42,000|0|0|0
ABC|00000002|15-Dec-15|13,400|110,000|100|86.3636
ABC|00000002|31-Jan-16|13,490|80,000|72.7273|71.3636
ABC|00000002|29-Feb-16|13,500|77,000|70|51.8182
ABC|00000002|31-May-16|40,200|37,000|33.6364|16.8182
ABC|00000002|31-Aug-16|42,000|0|0|0
4

Thanks Rudi - This worked a treat! :D:b: