2

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?

2
  • 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 '18 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 – lucian Mar 30 '18 at 11:06
1

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.

2
  • 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? – lucian Mar 30 '18 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. – Kusalananda Mar 30 '18 at 11:32
0

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
3
  • 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 – lucian Mar 30 '18 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 '18 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? – lucian Mar 30 '18 at 19:33

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.