Tide times are off by hours after a join #

Problem statement #

The tide level attached to each survey point is wrong, and wrong by an amount that looks like a whole tide. High water in the joined table falls in the middle of the night. The residual between observation and prediction is metres rather than decimetres.

There are five causes, and each has a signature you can read from a single plot of the joined series against the raw one: a naive timestamp read as UTC, a local-time request joined to UTC data, daylight saving, a datum mismatch that looks like a time error, and a nearest-time join that reached across a gap.

Quick answer #

Make every timestamp timezone-aware at the boundary, and join with a tolerance:

import pandas as pd

gauge = gauge.copy()
gauge["t"] = pd.to_datetime(gauge["t"], utc=True)          # the API was asked for GMT
events = events.copy()
events["time"] = (pd.to_datetime(events["time"])
                  .dt.tz_localize("Europe/London")          # if the source is local
                  .dt.tz_convert("UTC"))

joined = pd.merge_asof(events.sort_values("time"),
                       gauge.sort_values("t").rename(columns={"t": "time"}),
                       on="time", direction="nearest",
                       tolerance=pd.Timedelta("15min"))
print(f"{joined['v'].isna().sum()} of {len(joined)} events had no reading within 15 min")

tz_localize attaches a zone to a naive timestamp; tz_convert changes the zone of an aware one. Using the wrong one is the single commonest cause, and it shifts everything by the UTC offset.

Step-by-step solution #

1. Read the offset, because it names the cause #

2. Request GMT from the API #

time_zone=gmt on the NOAA datagetter returns UTC timestamps, which removes daylight saving from the problem entirely. lst_ldt returns local standard or daylight time depending on the date, which is the worst option for a join.

3. Localise, do not convert, a naive timestamp #

# the source recorded local wall-clock time
s = s.dt.tz_localize("Europe/London", ambiguous="NaT", nonexistent="NaT")
# the source recorded UTC and lost the marker
s = s.dt.tz_localize("UTC")

ambiguous="NaT" and nonexistent="NaT" make the two daylight-saving edge cases explicit rather than guessed: an hour that occurs twice in autumn and one that does not exist in spring.

4. Check whether the error is actually a datum error #

A constant offset in level rather than time looks like a time error on a plot, because a tide curve shifted vertically resembles one shifted horizontally near the turning points. Compare the observed and predicted series: a level offset appears as a constant residual, a time offset as a residual that oscillates with the tide.

5. Join with a tolerance #

merge_asof with direction="nearest" will happily match an event to an observation six hours away if the gauge has a gap. A tolerance turns that into a NaN you can count.

6. Check the sampling interval #

Six-minute data joined at a 15-minute tolerance is fine. Hourly data joined at the same tolerance leaves most events unmatched, and high_low data is irregular by construction.

7. Validate on a known high water #

Pick a date, look up the predicted high water, and check that the joined series peaks within a few minutes of it. One verification catches every one of the five causes.

Code examples #

Example 1 — measure the offset rather than guessing it #

import numpy as np, pandas as pd

def time_offset(observed, predicted, max_lag_min=360, step_min=6):
    """Cross-correlate two tide series to find the lag that aligns them."""
    o = observed["v"].astype(float)
    p = predicted["v"].astype(float).reindex(o.index).interpolate()
    best, best_r = 0, -np.inf
    for lag in range(-max_lag_min, max_lag_min + 1, step_min):
        shifted = p.shift(lag // step_min)
        ok = o.notna() & shifted.notna()
        if ok.sum() < 100:
            continue
        r = float(np.corrcoef(o[ok], shifted[ok])[0, 1])
        if r > best_r:
            best, best_r = lag, r
    return {"lag_minutes": best, "correlation": round(best_r, 4)}

print(time_offset(observed, predicted))

A lag of 0 with a correlation above 0.99 means the alignment is right. A lag of 60 or 120 is a time-zone offset; a lag near 745 minutes is a whole tidal cycle.

Example 2 — a timestamp parser that refuses to guess #

import pandas as pd

def to_utc(series, source_tz=None, assume_utc=False):
    s = pd.to_datetime(series, errors="coerce")
    if s.dt.tz is not None:
        return s.dt.tz_convert("UTC")
    if assume_utc:
        return s.dt.tz_localize("UTC")
    if source_tz is None:
        raise ValueError(
            "naive timestamps and no source_tz: state the zone, or pass assume_utc=True")
    return (s.dt.tz_localize(source_tz, ambiguous="NaT", nonexistent="NaT")
             .dt.tz_convert("UTC"))

events["time"] = to_utc(events["time"], source_tz="Europe/London")
print(f"{events['time'].isna().sum()} timestamps were ambiguous or nonexistent")

Raising on a naive timestamp with no stated zone is the point. Every silent default here is a two-hour error somewhere downstream.

Example 3 — separate a time error from a datum error #

import numpy as np, pandas as pd

def residual_signature(observed, predicted):
    both = observed[["v"]].join(predicted[["v"]], lsuffix="_obs", rsuffix="_pred",
                                how="inner")
    r = both["v_obs"] - both["v_pred"]
    # a level offset is a constant; a time offset correlates with the rate of change
    rate = both["v_pred"].diff()
    ok = r.notna() & rate.notna()
    corr = float(np.corrcoef(r[ok], rate[ok])[0, 1])
    return {"mean_residual_m": round(float(r.mean()), 3),
            "sd_residual_m": round(float(r.std()), 3),
            "corr_with_rate": round(corr, 3),
            "likely": "datum offset" if abs(r.mean()) > 2 * r.std()
                      else "time offset" if abs(corr) > 0.5 else "meteorological"}

print(residual_signature(observed, predicted))

The correlation with the rate of change is the discriminator: a series shifted in time has its largest residuals where the tide is moving fastest, while a datum error has the same residual everywhere.

Explanation #

Why tz_localize and tz_convert are so easy to confuse #

tz_localize says "these wall-clock times are in this zone"; tz_convert says "express these known instants in this zone". Applied to a naive series, tz_convert raises, which is helpful. Applied to an aware series, tz_localize raises too. The dangerous case is tz_localize("UTC") on a series that was recorded in local time — it produces an aware series that is wrong by the offset and raises nothing.

Why GMT removes most of the problem #

Requesting local time from an API means the returned timestamps change their offset twice a year, and the transition hours are ambiguous or nonexistent. UTC has neither property. Converting to local time for display at the very end is easy; parsing local time at the input boundary is not.

Why a level error can look like a time error #

Near high and low water the tide curve is flat, so a vertical shift and a horizontal shift produce similar-looking displacement there. On the steep part of the curve they differ: a time shift produces a residual proportional to the rate of change, while a datum offset produces a constant one. Correlating the residual with the derivative separates them in one line.

Why a nearest join needs a tolerance #

merge_asof with direction="nearest" always finds a match. If the gauge has a six-hour gap — ice, maintenance, a failed sensor — an event in the middle of it is matched to an observation three hours away, which on a semidiurnal tide can be the opposite state. A tolerance makes the gap visible as a NaN, and counting them is the check.

Edge cases or notes #

• ambiguous and nonexistent must be set for any local-time localisation.
• Some gauges report in local standard time year-round. Read the metadata.
• high_low products are irregular and must not be resampled.
• Leap seconds are not in pandas. They are below the noise here.
• Survey timestamps may be the file's mtime. Check what the field means.
• Satellite acquisition times are UTC. Join to UTC gauge data directly.
• A whole-cycle error is 12h 25m, not 12h — semidiurnal, not solar.
• Plot the joined series once. Every one of these is visible.

Internal links #

• How to fetch and align tide gauge data in Python — the fetch and join done properly
• Tidal datums explained: which shoreline is the shoreline — the datum half of the confusion
• Spatial data has timezone errors in the time column — the same problem in general
• How to standardise dates in spatial data with Python — parsing at the boundary
• How to measure shoreline change between two dates — where the tide state is needed
• How to resample a time series in xarray — resampling without crossing gaps
• cftime and datetime errors in xarray — calendars in gridded data
• Sea level rise data explained — the residual as the interesting series

FAQ #

Why is my joined tide level wrong by a constant amount of time? #

Almost always a naive local timestamp localised as UTC. The error equals the UTC offset and is constant outside daylight-saving transitions.

What is the difference between tz_localize and tz_convert? #

tz_localize attaches a zone to naive times; tz_convert re-expresses aware times in another zone. Using the first on local times labelled as UTC is the classic bug.

Why does the offset change twice a year? #

Daylight saving. Request GMT from the API and convert to local only for display.

How do I tell a time error from a datum error? #

Correlate the observed-minus-predicted residual with the rate of change of the tide. A time shift correlates; a datum offset is constant.

Why did some events get a tide level from six hours away? #

Because merge_asof with direction="nearest" has no tolerance by default. Set one, and count the NaNs.

How do I verify the join? #

Pick a date, look up the predicted high water, and check the joined series peaks within a few minutes of it.