# awk get groups of lines and perform operations on columns

I have a file that has the following structure:

``````Ti    1.9699858320     2.0810775390    4.162155079     5.20200
O     1.6428341970     2.0810775390    4.162155079    -2.14259
O     1.6428341970     2.0810775390    4.162155079    -2.14259
Pb    4.1621550790     4.1621550790    4.192557641     3.39279
O     3.7662066970     4.1621550790    4.192557641    -4.29652
Ti    6.1302323500     6.2584338990    4.192557641     5.23841
O     5.8163744340     6.2584338990    4.192557641    -2.13267
O     5.8163744340     6.2584338990    4.192557641    -2.13267
Pb    8.3547127200     8.3547127200    4.196295567     3.40984
O     7.9266344100     8.3547127200    4.196295567    -4.36260
Ti    10.318243871     10.452860504    4.196295567     5.26652
O     9.9935741680     10.452860504    4.196295567    -2.13625
O     9.9935741680     10.452860504    4.196295567    -2.13625
Pb    12.551008287     12.551008287    4.193631562     3.43289
O     12.112224767     12.551008287    4.193631562    -4.38552
``````

I need to to the following operations:

1. substract column 3 from column 2)
2. multiply the results from 1) with column 5: this I do with:

``````awk '{print \$0,"    ",(\$2-\$3)*\$5 > "file-out.dat"}' file-in.dat
``````
3. (this is the tricky part) with the result of 2) I need to get the sum of each group of 5 entries. Here's how the file should look after point 2). I need to add the entries from the last column in groups of 5 and write the result as follows:

``````Ti    1.9699858320     2.0810775390    4.162155079     5.20200      -0.577899    1 result_of_sum_of_first_group_of_5
O     1.6428341970     2.0810775390    4.162155079    -2.14259      0.938976    2 result_of_sum_of_second_group_of_5
O     1.6428341970     2.0810775390    4.162155079    -2.14259      0.938976    3 result_of_sum_of_third_group_of_5
Pb    4.1621550790     4.1621550790    4.192557641     3.39279      0
O     3.7662066970     4.1621550790    4.192557641    -4.29652      1.7012
Ti    6.1302323500     6.2584338990    4.192557641     5.23841      -0.671572
O     5.8163744340     6.2584338990    4.192557641    -2.13267      0.942767
O     5.8163744340     6.2584338990    4.192557641    -2.13267      0.942767
Pb    8.3547127200     8.3547127200    4.196295567     3.40984      0
O     7.9266344100     8.3547127200    4.196295567    -4.36260      1.86753
Ti    10.318243871     10.452860504    4.196295567     5.26652      -0.708961
O     9.9935741680     10.452860504    4.196295567    -2.13625      0.98115
O     9.9935741680     10.452860504    4.196295567    -2.13625      0.98115
Pb    12.551008287     12.551008287    4.193631562     3.43289      0
O     12.112224767     12.551008287    4.193631562    -4.38552      1.92429
``````

Is there a way to do all these operations in one awk line?

• Do the results of 3) need to be in the first three lines? Would it be OK to print them on the 5th, 10th and 15th line instead? If not, is the file small enough that reading the whole thing into memory is not a problem?
– terdon
Mar 30, 2018 at 11:00
• the file I am working on has about 100 lines, and I don't expect the future ones to be larger than that. the purpose of this formatting is that I would like to use gnuplot to plot the last two columns in the final file. basically the column before last should give the number of each group of 5 as an index. I am not sure how gnuplot will understand what you propose but I can give it a try! thank you Mar 30, 2018 at 11:06

In two steps using two temporary files:

First step: Create the intermediate file with exactly six columns as `tmpfile1` and the file with the sum of all three `Ti` groups as `tmpfile2`:

``````awk '{ \$6 = (\$2 - \$3)*\$5; print }' OFS="\t" file | tee tmpfile1 |
awk '\$1 == "Ti" && NR > 1 { print ++i, sum; sum = 0 } { sum += \$6 } END { print ++i, sum }' OFS="\t" >tmpfile2
``````

The first `awk` command simply adds a sixth column with the values calculated according to your formula. The `tee` writes the result to `tmpfile1` and also passes the data through to the second `awk` program.

The second `awk` sums up the new sixth column. When it hits a `Ti` line, unless it's the very first line of the file, it outputs the current sum and resets the `sum` variable. The sum for the last set of lines is outputted in the `END` block. The variable `i` in incremented before each output and is the index you wanted in that column. This creates the `tmpfile2` file.

`tmpfile1`:

``````Ti  1.9699858320    2.0810775390    4.162155079 5.20200 -0.577899
O   1.6428341970    2.0810775390    4.162155079 -2.14259    0.938976
O   1.6428341970    2.0810775390    4.162155079 -2.14259    0.938976
Pb  4.1621550790    4.1621550790    4.192557641 3.39279 0
O   3.7662066970    4.1621550790    4.192557641 -4.29652    1.7012
Ti  6.1302323500    6.2584338990    4.192557641 5.23841 -0.671572
O   5.8163744340    6.2584338990    4.192557641 -2.13267    0.942767
O   5.8163744340    6.2584338990    4.192557641 -2.13267    0.942767
Pb  8.3547127200    8.3547127200    4.196295567 3.40984 0
O   7.9266344100    8.3547127200    4.196295567 -4.36260    1.86753
Ti  10.318243871    10.452860504    4.196295567 5.26652 -0.708961
O   9.9935741680    10.452860504    4.196295567 -2.13625    0.98115
O   9.9935741680    10.452860504    4.196295567 -2.13625    0.98115
Pb  12.551008287    12.551008287    4.193631562 3.43289 0
O   12.112224767    12.551008287    4.193631562 -4.38552    1.92429
``````

`tmpfile2`:

``````1       3.00125
2       3.08149
3       3.17763
``````

Second step: Paste these together:

``````paste tmpfile1 tmpfile2
``````

This produces

``````Ti      1.9699858320    2.0810775390    4.162155079     5.20200 -0.577899       1       3.00125
O       1.6428341970    2.0810775390    4.162155079     -2.14259        0.938976        2       3.08149
O       1.6428341970    2.0810775390    4.162155079     -2.14259        0.938976        3       3.17763
Pb      4.1621550790    4.1621550790    4.192557641     3.39279 0
O       3.7662066970    4.1621550790    4.192557641     -4.29652        1.7012
Ti      6.1302323500    6.2584338990    4.192557641     5.23841 -0.671572
O       5.8163744340    6.2584338990    4.192557641     -2.13267        0.942767
O       5.8163744340    6.2584338990    4.192557641     -2.13267        0.942767
Pb      8.3547127200    8.3547127200    4.196295567     3.40984 0
O       7.9266344100    8.3547127200    4.196295567     -4.36260        1.86753
Ti      10.318243871    10.452860504    4.196295567     5.26652 -0.708961
O       9.9935741680    10.452860504    4.196295567     -2.13625        0.98115
O       9.9935741680    10.452860504    4.196295567     -2.13625        0.98115
Pb      12.551008287    12.551008287    4.193631562     3.43289 0
O       12.112224767    12.551008287    4.193631562     -4.38552        1.92429
``````

The result is tab-delimited.

• thank you for your answer! it worked although I don't really understand it. The first awk command I understand and in my initial question I managed to get that far. the second awk I don't get and I struggled with. Where is it that the grouping by 5 is done? can you add a bit more detail to your solution please? Mar 30, 2018 at 11:31
• @lucian The "grouping by 5" is really "summing from one `Ti` line to the next `Ti` line". My code doesn't care how many lines are between the `Ti` lines. Mar 30, 2018 at 11:32

Here's a pure `awk` approach:

``````\$ awk 'BEGIN{c=0}
{
\$6 = (\$2 - \$3)*\$5;
a[NR]=\$0;
sum+=\$6
if(NR%5==0){
a[++c]=\$0" "sum;
sum=0;
}
}
END{
for(i in a){
print a[i]
}
}' file
O 3.7662066970 4.1621550790 4.192557641 -4.29652 1.7012 3.00125
O 7.9266344100 8.3547127200 4.196295567 -4.36260 1.86753 3.0815
O 12.112224767 12.551008287 4.193631562 -4.38552 1.92429 3.17763
Pb 4.1621550790 4.1621550790 4.192557641 3.39279 0
O 3.7662066970 4.1621550790 4.192557641 -4.29652 1.7012
Ti 6.1302323500 6.2584338990 4.192557641 5.23841 -0.671572
O 5.8163744340 6.2584338990 4.192557641 -2.13267 0.942767
O 5.8163744340 6.2584338990 4.192557641 -2.13267 0.942767
Pb 8.3547127200 8.3547127200 4.196295567 3.40984 0
O 7.9266344100 8.3547127200 4.196295567 -4.36260 1.86753
Ti 10.318243871 10.452860504 4.196295567 5.26652 -0.708961
O 9.9935741680 10.452860504 4.196295567 -2.13625 0.98115
O 9.9935741680 10.452860504 4.196295567 -2.13625 0.98115
Pb 12.551008287 12.551008287 4.193631562 3.43289 0
O 12.112224767 12.551008287 4.193631562 -4.38552 1.92429
``````

Note that this will remove the tabs if, as seems to be the case, your input file is tab separated. If that's a problem, you could put them back with `sed`:

``````\$ awk '...' | sed 's/ /\t/g'
O   3.7662066970    4.1621550790    4.192557641 -4.29652    1.7012  3.00125
O   7.9266344100    8.3547127200    4.196295567 -4.36260    1.86753 3.0815
O   12.112224767    12.551008287    4.193631562 -4.38552    1.92429 3.17763
Pb  4.1621550790    4.1621550790    4.192557641 3.39279 0
O   3.7662066970    4.1621550790    4.192557641 -4.29652    1.7012
Ti  6.1302323500    6.2584338990    4.192557641 5.23841 -0.671572
O   5.8163744340    6.2584338990    4.192557641 -2.13267    0.942767
O   5.8163744340    6.2584338990    4.192557641 -2.13267    0.942767
Pb  8.3547127200    8.3547127200    4.196295567 3.40984 0
O   7.9266344100    8.3547127200    4.196295567 -4.36260    1.86753
Ti  10.318243871    10.452860504    4.196295567 5.26652 -0.708961
O   9.9935741680    10.452860504    4.196295567 -2.13625    0.98115
O   9.9935741680    10.452860504    4.196295567 -2.13625    0.98115
Pb  12.551008287    12.551008287    4.193631562 3.43289 0
O   12.112224767    12.551008287    4.193631562 -4.38552    1.92429
``````
• i took your solution and transformed it on a single line and it gives me an error at If: `awk: cmd. line:1: BEGIN{c=0} {\$6 = (\$2 - \$3)*\$5; a[NR]=\$0; sum+=\$6 if(NR%5==0){a[++c]=\$0" "sum; sum=0;} } END{for(i in a){print " ",i," ",a[i]} } awk: cmd. line:1: ^ syntax error` Mar 30, 2018 at 12:16
• @lucian not sure why you transformed it. You can just copy paste the multi-line version directly. Then, hit the up arrow and it will be single line. I also have the single line version in the second code block. Anyway, you removed the `;` after the `sum+=\$6`, that's why you're getting an error.
– terdon
Mar 30, 2018 at 12:19
• that did it! thank you. one more thing. how do I add an extra column with an index like in my example from the beginning? Mar 30, 2018 at 19:33