Match multiple changing time periods between two datasets

user_11644783 0
Viewed 128

I have two data frames, the first of which spans 3 months and has recordings of depth every 2.5 minutes.

shark depth temperature   datetime    date      location
A     49.5  26.2   20/03/2018 08:00 20/03/2018    SS04
A     49.5  25.3   20/03/2018 08:02 20/03/2018    SS04
A     53.0  24.2   20/03/2018 08:04 20/03/2018    SS04
A     39.5  26.5   20/03/2018 08:32 20/03/2018    Absent
A     43.0  26.2   21/03/2018 09:10 21/03/2018    Absent
A     44.5  26.5   21/03/2018 10:18 21/03/2018    SS04

I have a second data frame which lists the tidal state during these three months.

   date    time  depth  tide_state   datetime
18/03/2018 02:33  2.09  High    20/03/2018 02:33
18/03/2018 08:39  0.45   Low    20/03/2018 08:39
18/03/2018 14:47  2.14  High    20/03/2018 14:47
18/03/2018 20:54  0.41   Low    20/03/2018 20:54
19/03/2018 03:01  2.13  High    21/03/2019 03:01
19/03/2018 09:09  0.41   Low    21/03/2019 09:09

I would like to create a new dataset which inserts the tidal state for all the values on the first dataset, based on the datetime column in each. For example if low tide was at 08:39, and high tide at 14:47, I would like every value in df1 greater than 08:39 but less than 14:47 to be recorded as 'Low', and values after this but before the next low tide to be 'High'.

As the time of the tides change three to four times per day, I'm not quite sure how I could go about merging these in R. I'm not sure if there is an easy way to do this using data.table?

I have both datetime columns in each dataframe set as POSIXct values.

Ideally I would like to produce a table a dataframe like this:

shark depth temperature   datetime    date    location tide_state
A     49.5  26.2   20/03/2018 08:00 20/03/2018  SS04     High
A     49.5  25.3   20/03/2018 08:02 20/03/2018  SS04     High
A     53.0  24.2   20/03/2018 08:04 20/03/2018  SS04     High
A     39.5  26.5   20/03/2018 08:32 20/03/2018  Absent   Low
A     43.0  26.2   20/03/2018 09:10 21/03/2018  Absent   Low  
A     44.5  26.5   20/03/2018 10:18 21/03/2018  SS04     Low
lubridate r
1 Answers

If the data were much larger or the join were more complicated, I'd suggest a non-equi join using SQL or data.table. For this size of data, where you just need "most recent value from table2," we can use a simpler approach in dplyr and I expect it will be plenty fast.

# First some housekeeping. It will be useful to have datetimes for sorting
library(dplyr)
df1   <- df1   %>% mutate(datetime = lubridate::dmy_hm(datetime))
tides <- tides %>% mutate(datetime = lubridate::dmy_hm(datetime))

# I collate the two tables, sort by datetime, fill in the tide info, and then remove the tide rows.
df1 %>%
  bind_rows(tides %>% 
     select(datetime, tide_state, tide_depth = depth) %>%
     mutate(tide_row_to_cut = TRUE)) %>%      # EDIT
  arrange(datetime) %>%
  tidyr::fill(tide_depth, tide_state) %>%
  filter(!tide_row_to_cut) %>%                # EDIT
  select(-tide_row_to_cut)                    # EDIT

Edit: Prior version using NA's in Temperature to cut out tide rows wasn't working for original poster, so I've added an explicit column in the tide data called tide_row_to_cut to make that pruning step more robust.

  shark depth temperature            datetime       date location tide_state tide_depth
1     A  49.5        26.2 2018-03-20 08:00:00 20/03/2018     SS04       High       2.09
2     A  49.5        25.3 2018-03-20 08:02:00 20/03/2018     SS04       High       2.09
3     A  53.0        24.2 2018-03-20 08:04:00 20/03/2018     SS04       High       2.09
4     A  39.5        26.5 2018-03-20 08:32:00 20/03/2018   Absent       High       2.09
5     A  43.0        26.2 2018-03-21 09:10:00 21/03/2018   Absent        Low       0.41
6     A  44.5        26.5 2018-03-21 10:18:00 21/03/2018     SS04        Low       0.41

I believe this follows the instructions, but it varies slightly from the requested output, since a Low tide occurs at 08:39, a few minutes after the 08:32 reading. The tide will be pretty low then, but won't have reached max low yet. You might want to look for "closest" tide. One way to do this would be to shift the time of the tides halfway back to the prior tide, or by a fixed amount (eg 2 hours?).

user_68518250
Related