r - Fill missing rows in a column based on average of row above and below

Question

I'm working with water quality data and I need to correct water depth for the tide height at the time the measurement was taken. Measurements of pH, salinity, etc. were taken every minute across two or more days, but the tide height data I have only gives a measurement every hour, on the hour. Essentially, I'd like to expand my tide height data to give a tide measurement every minute by averaging between the tide heights for each hour. Once I have that I can left_join() to my water parameter data frame no problem

I'd prefer to use tidyverse but at this point I'd be ecstatic for any help at all. Sorry for any formatting issues, this is my first StackOverflow question.

Here is a sample tide height data frame for one day:

> Tide_x <- data.frame(Time_Eastern_hr = c(0, 1, 2, 3, 4, 5, 6, 7, 8, 9, 10,
+                                          11, 12, 13, 14, 15, 16, 17, 18, 19, 20, 21, 22, 23), 
+                     Tide_Height = c(1.9, 1.7, 1.4, 1.1, 0.8, 0.6, 0.5, 0.6, 0.8, 1.1, 1.4, 1.6, 1.7, 
+                                     1.6, 1.5, 1.2, 1, 0.8, 0.8, 0.9, 1.1, 1.4, 1.7, 1.9))
> Tide_x
   Time_Eastern_hr Tide_Height
1                0         1.9
2                1         1.7
3                2         1.4
4                3         1.1
5                4         0.8
6                5         0.6
7                6         0.5
8                7         0.6
9                8         0.8
10               9         1.1
11              10         1.4
12              11         1.6
13              12         1.7
14              13         1.6
15              14         1.5
16              15         1.2
17              16         1.0
18              17         0.8
19              18         0.8
20              19         0.9
21              20         1.1
22              21         1.4
23              22         1.7
24              23         1.9

But I need something like this, where Tide_Height is calculated each minute as an average of the Tide_Height measurement above and below. Here I've used 1.9 and 1.7, so 0.2/60 to get the amount that tide height changes every minute, then subtracted that change from the row above as I move down the column

    Time_Eastern_hr Tide_Height
 1               0          1.9
 2         0.01667       1.8967
 3         0.03334       1.8933
 4            0.05       1.8900
 5          0.0667       1.8867
 6          0.0833       1.8833
 7             0.1       1.8800
 8          0.1167       1.8767
 9          0.1333       1.8734
              0.15       1.8701
# … with 14 more rows

However, as you can see I can't just continuously subtract because the tide is either higher or lower than it was the hour before, rather than continuously lower. Also, the difference between the tide at each hour changes.

question from:https://stackoverflow.com/questions/65892651/fill-missing-rows-in-a-column-based-on-average-of-row-above-and-below

Answer 1

The approx function is our friend here! It linearly interpolates data between two points, which is the same as the weighted average it looks like you're calculating manually.

Tide_x <- data.frame(Time_Eastern_hr = c(0, 1, 2, 3, 4, 5, 6, 7, 8, 9, 10, 11, 12, 13, 14, 15, 16, 17, 18, 19, 20, 21, 22, 23), 
                     Tide_Height = c(1.9, 1.7, 1.4, 1.1, 0.8, 0.6, 0.5, 0.6, 0.8, 1.1, 1.4, 1.6, 1.7, 1.6, 1.5, 1.2, 1, 0.8, 0.8, 0.9, 1.1, 1.4, 1.7, 1.9))
new_tide_times <- seq(min(Tide_x$Time_Eastern_hr), max(Tide_x$Time_Eastern_hr), 1/60)
new_tide_heights <- approx(Tide_x$Time_Eastern_hr, Tide_x$Tide_Height, new_tide_times)$y

new_tides <- data.frame(new_tide_times, new_tide_heights)

returns

  new_tide_times new_tide_heights
1     0.00000000         1.900000
2     0.01666667         1.896667
3     0.03333333         1.893333
4     0.05000000         1.890000
5     0.06666667         1.886667
6     0.08333333         1.883333