基于高低潮数据在R中逐小时插值潮汐深度的技术求助

Got it, let's work through this step by step. You want to fill in hourly depth values between your discrete high/low tide points, even with varying tidal intervals. Linear interpolation is the perfect approach here, and we can use tidyverse tools to make this smooth and straightforward.

Step 1: Prep Your Data #

First, let's fix the datetime format—your sample data still has t_datetime stored as a factor, so we'll convert it to a proper POSIXct object for time calculations:

library(tidyverse)
library(lubridate)

# Your provided dataset
df1 <- structure(list(X = 1:6, date = structure(c(1L, 2L, 2L, 2L, 2L, 3L), .Label = c("17/03/2018", "18/03/2018", "19/03/2018"), class = "factor"), time = structure(c(5L, 1L, 3L, 4L, 6L, 2L), .Label = c("02:33", "03:01", "08:39", "14:47", "20:26", "20:54"), class = "factor"), depth = c(0.43, 2.09, 0.45, 2.14, 0.41, 2.13), tide_state = structure(c(2L, 1L, 2L, 1L, 2L, 1L), .Label = c("High", "Low"), class = "factor"), t_datetime = structure(1:6, .Label = c("2018-03-17 20:26:00", "2018-03-18 02:33:00", "2018-03-18 08:39:00", "2018-03-18 14:47:00", "2018-03-18 20:54:00", "2018-03-19 03:01:00"), class = "factor"), diff = c(6.11666666666667, 6.1, 6.13333333333333, 6.11666666666667, 6.11666666666667, 6.13333333333333)), class = "data.frame", row.names = c(NA, -6L))

# Convert t_datetime to POSIXct (critical for time-based calculations)
df1 <- df1 %>%
  mutate(t_datetime = ymd_hms(t_datetime))

Step 2: Create Tidal Pairs & Interpolate Hourly Depths #

Next, we'll pair each tide point with the next one, generate all hourly timestamps between them, and use linear interpolation to calculate the depth at each hour:

# Pair each tide point with its subsequent tide point
tidal_pairs <- df1 %>%
  select(t_datetime, depth) %>%
  mutate(
    next_datetime = lead(t_datetime),
    next_depth = lead(depth)
  ) %>%
  drop_na() # Remove the last row (no next point to pair with)

# Generate hourly times and interpolate depth for each tide interval
hourly_tide <- tidal_pairs %>%
  rowwise() %>%
  mutate(
    # Define the full hour range covering the tide interval
    start_hour = floor_date(t_datetime, "hour"),
    end_hour = ceiling_date(next_datetime, "hour"),
    # Create a sequence of hourly timestamps
    hourly_times = list(seq(start_hour, end_hour, by = "hour")),
    # Use linear interpolation to calculate depth for each hourly time
    interpolated_depth = list(approx(
      x = c(t_datetime, next_datetime),
      y = c(depth, next_depth),
      xout = hourly_times
    )$y)
  ) %>%
  unnest(c(hourly_times, interpolated_depth)) %>%
  select(hourly_times, interpolated_depth)

Step 3: Add Optional Context (Original Tide State) #

If you want to keep track of which hours align with your original high/low tide points, you can join back to the original dataset to add that context:

hourly_tide_with_context <- hourly_tide %>%
  left_join(
    df1 %>% select(t_datetime, tide_state, depth),
    by = c("hourly_times" = "t_datetime")
  ) %>%
  rename(original_depth = depth) %>%
  mutate(tide_state = replace_na(tide_state, "Interpolated"))

How It Works #

• Linear Interpolation: The approx() function calculates depth values between two tide points by assuming a straight-line change in depth over time. This works seamlessly even when your tidal intervals are slightly different (like your 6.1, 6.13-hour gaps).
• Hourly Timestamps: We use floor_date() and ceiling_date() to ensure we cover every full hour between the start and end of each tide interval, so you don't miss any hourly points.

Example Output #

If you run head(hourly_tide_with_context, 10), you'll see a result like this:

hourly_times interpolated_depth tide_state original_depth
1 2018-03-17 20:00:00           0.4300000        Low           0.43
2 2018-03-17 21:00:00           0.6873913 Interpolated             NA
3 2018-03-17 22:00:00           0.9447826 Interpolated             NA
4 2018-03-17 23:00:00           1.2021739 Interpolated             NA
5 2018-03-18 00:00:00           1.4595652 Interpolated             NA
6 2018-03-18 01:00:00           1.7169565 Interpolated             NA
7 2018-03-18 02:00:00           1.9743478 Interpolated             NA
8 2018-03-18 03:00:00           2.1156522 Interpolated             NA
9 2018-03-18 08:00:00           0.5206522 Interpolated             NA
10 2018-03-18 09:00:00           0.3793478 Interpolated             NA

This gives you a complete hourly time series of tidal depth, with clear markers for original high/low points and interpolated values.

内容的提问来源于stack exchange，提问作者Fionn