Using AWK, I'm trying to take a column of information as input, perform math on the input, and then return the results back into the column. My input file is in the format as below:
1, 1, 6, 3, 2, 4, 1.5, 1, 0.5, 1, 2.75
2, 3, 7, 6, 4, 6, 4.5, 2, 0.5, 2, 2.75
3, 11, 3, 5, 4, 5, 2.5, 6, 2.5, 3, 7.75
4, 9, 9, 10, 7.5, 9, 7, 7, 3.5, 4, 7.75
5, 7, 4, 2, 1, 2, 1, 3, 1, 5, 8.25
6, 8, 5, 9, 7, 7, 5.5, 8, 5.5, 6, 11
7, 6, 2, 1, 1, 1, 1, 4, 1.5, 7, 11.25
8, 10, 1, 4, 3, 3, 1.5, 5, 1.5, 8, 11.25
9, 5, 10, 8, 5.5, 8, 6, 9, 11.5, 9, 25.25
10, 2, 8, 7, 4.5, 10, 12, 10, 11.75, 10, 25.75
Besides the above table format, there are also several bash variables that need to used in the equations. They are as follows:
Feet=4290
FinalTime=76.79
FirstDistance=1320
FirstTime=21.67
SecondDistance=2640
SecondTime=44.65
ThirdDistance=3146
ThirdTime=70.33
I have come up with a long one liner that isn't outputting accurate information. I would like to have something that's easier to read in an AWK script that actually works. The one liner is:
awk -F, -v Feet="$Feet" -v FinalTime="$FinalTime" -v ThirdDistance="$ThirdDistance" -v ThirdTime="$ThirdTime" -v FirstDistance="$FirstDistance" -v FirstTime="$FirstTime" -v SecondDistance="$SecondDistance" -v SecondTime="$SecondTime" '{
$(NF-2)=(ThirdDistance-($NF-2)*8)/ThirdTime*.681818182;
$(NF-4)=((SecondDistance-(($NF-4)*8))/SecondTime)*.681818182;
$(NF-6)=((FirstDistance-(($NF-6)*8))/FirstTime)*.681818182;
$NF=((Feet-($NF*8))/FinalTime)*.681818182;
print $0
}' OFS=";" "$race".csv65
The results look like this:
1; 1; 6; 3; 42.3501; 4; 40.4663; 1; 30.4409; 1; 37.8956;
2; 3; 7; 6; 42.3501; 6; 40.4663; 2; 30.4409; 2; 37.8956;
3; 11; 3; 5; 41.0916; 5; 39.8554; 6; 30.0531; 3; 37.5404;
4; 9; 9; 10; 41.0916; 9; 39.8554; 7; 30.0531; 4; 37.5404;
5; 7; 4; 2; 40.9657; 2; 39.7944; 3; 30.0143; 5; 37.5049;
6; 8; 5; 9; 40.2735; 7; 39.4584; 8; 29.8011; 6; 37.3095;
7; 6; 2; 1; 40.2106; 1; 39.4279; 4; 29.7817; 7; 37.2918;
8; 10; 1; 4; 40.2106; 3; 39.4279; 5; 29.7817; 8; 37.2918;
9; 5; 10; 8; 36.6867; 8; 37.7176; 9; 28.6959; 9; 36.2973;
10; 2; 8; 7; 36.5608; 10; 37.6565; 10; 28.6571; 10; 36.2618;
The desired results should look like:
1; 1; 6; 3; 41.0287; 4; 40.1303; 1; 30.2664; 1; 37.8956;
2; 3; 7; 6; 40.5252; 6; 39.7638; 2; 30.2664; 2; 37.8956;
3; 11; 3; 5; 40.5252; 5; 40.0081; 6; 30.1113; 3; 37.5404;
4; 9; 9; 10; 39.6443; 9; 39.4584; 7; 30.0337; 4; 37.5404;
5; 7; 4; 2; 41.2804; 2; 40.1914; 3; 30.2276; 5; 37.5049;
6; 8; 5; 9; 39.7701; 7; 39.6417; 8; 29.8786; 6; 37.3095;
7; 6; 2; 1; 41.2804; 1; 40.1914; 4; 30.1888; 7; 37.2918;
8; 10; 1; 4; 40.7769; 3; 40.1303; 5 30.1888; 8; 37.2918;
9; 5; 10; 8; 40.1477; 8; 39.5806; 9; 29.4139; 9; 36.2973;
10; 2; 8; 7; 40.3994; 10; 38.8476; 10; 29.3939; 10; 36.2618;
I don't know what I'm doing wrong. Basically the equations I would like to accomplish are:
$(NF-2)=((ThirdDistance-($NF-2)*8))/ThirdTime*.681818182;
$(NF-4)=((SecondDistance-(($NF-4)*8))/SecondTime)*.681818182;
$(NF-6)=((FirstDistance-(($NF-6)*8))/FirstTime)*.681818182;
$NF=((Feet-($NF*8))/FinalTime)*.681818182;
But I'm obviously screwing up here. I'm not getting the correct results except for the last column and all the other columns with results are ordered in a descending order. Any constructive criticism is welcomed.
Thank You!
I think that the issue here is that you've misplaced the parentheses used to access the fields in the right hand side of each of your assignments. $(NF-2) means the value of the third field from the end, whereas ($NF-2) means the value of the last field, subtracted by two. It looks like you really meant to use the first option in the first three cases:
$ awk -F, -v Feet="$Feet" -v FinalTime="$FinalTime" -v ThirdDistance="$ThirdDistance" -v ThirdTime="$ThirdTime" -v FirstDistance="$FirstDistance" -v FirstTim e="$FirstTime" -v SecondDistance="$SecondDistance" -v SecondTime="$SecondTime" '{
$(NF-2)=(ThirdDistance-$(NF-2)*8)/ThirdTime*.681818182;
$(NF-4)=((SecondDistance-$(NF-4)*8)/SecondTime)*.681818182;
$(NF-6)=((FirstDistance-$(NF-6)*8)/FirstTime)*.681818182;
$NF=((Feet-$NF*8)/FinalTime)*.681818182;
print
}' OFS=";" file
1; 1; 6; 3;41.0287; 4;40.1303; 1;30.4603; 1;37.8956
2; 3; 7; 6;40.5252; 6;39.7638; 2;30.4603; 2;37.8956
3; 11; 3; 5;40.5252; 5;40.0081; 6;30.3052; 3;37.5404
4; 9; 9; 10;39.6443; 9;39.4584; 7;30.2276; 4;37.5404
5; 7; 4; 2;41.2804; 2;40.1914; 3;30.4215; 5;37.5049
6; 8; 5; 9;39.7701; 7;39.6417; 8;30.0725; 6;37.3095
7; 6; 2; 1;41.2804; 1;40.1914; 4;30.3827; 7;37.2918
8; 10; 1; 4;40.7769; 3;40.1303; 5;30.3827; 8;37.2918
9; 5; 10; 8;40.1477; 8;39.5806; 9;29.6072; 9;36.2973
10; 2; 8; 7;40.3994; 10;38.8476; 10;29.5878; 10;36.2618
In the assignment to $NF, you had the opposite problem: $(NF*8) attempts to access the NF*8th field, which doesn't exist. Due to operator precedence, you can remove the parentheses in this case to get what you want.
In terms of improving the readability of your script, some shorter variable names may help. Also, you may want to consider using an actual script and calling it with awk -f, rather than going for a really long "one-liner". I have also removed some unnecessary parentheses; personally I find them distracting but others may disagree.