0

I have a large file with two columns, I would like to interpret the value at 0.00, 0.50, 1.00, 1.50 ...etc (if it is not already exist) by calculating the mean from the previous value and the next value. For instance the corresponding value of 3.00 is missed so I want to add a line with 3.00 in the first column and a corresponding value in the second column =(2.99 + 3.56)/2=3.14 and do the same for 3.50, 4.00 , 4.50 ...etc this is my input

0.00  0.29
0.50  0.79
1.00  1.31
1.50  1.86
2.00  2.42
2.50  2.99
3.25  3.56
3.75  4.15
4.25  4.73
4.75  5.32
5.00  5.92
5.50  6.53
6.00  7.15

and this my desired output

0.00    0.29
0.50    0.79
1.00    1.31
1.50    1.86
2.00    2.42
2.50    2.99
3.00    3.28
3.50    3.86
4.00    4.44
4.50    5.03
5.00    5.92
5.50    6.53
6.00    7.15
4
  • If the value for x.00 is missing, is it guaranteed that the values for (x-1).75 and x.25 are present? Likewise for x.25 and x.75 if x.50 is missing?
    – Kusalananda
    Jan 16, 2017 at 16:55
  • yes the file always like this, the maximum interval for the first column is 0.5 and the minimum interval is 0.25 Jan 16, 2017 at 16:57
  • This can be done with any language. Do you have a language of choice? What have you tried so far with it?
    – xhienne
    Jan 16, 2017 at 18:09
  • I have tried with awk but I cannot find reliable solution Jan 16, 2017 at 18:16

1 Answer 1

1

I would do it in two steps.

First, interpolate the missing data. Here is an example with awk:

awk '
    ($1 - prev1) > 0.25 { printf "%.2f  %.2f\n", prev1 + 0.25, (prev2 + $2)/2 }
    { print; prev1 = $1; prev2 = $2 }
' data_file

It works like this:

  • First, if we notice that a value in column 1 is missing (gap with previous value is > 0.25) we print the missing line by calculating an average for the column 2.
  • Next, we print the current line and set the previous values for the next cycle

Finally, you can filter out the unwanted lines:

(previous awk command) | awk '$1 ~ /0$/'

This last awk command only displays the lines whose first value ends with a 0.

You must log in to answer this question.

Not the answer you're looking for? Browse other questions tagged .