Creating a new date.time "interval" column based on start date.time and end date.time

Question

I have a data set of start and end times of when a reef is exposed during low tide. I am trying to create a new column called "interval" using the lubridate::interval function. I have about 400 rows of data.

https://github.com/LCRoysterproject/bird/blob/STEVEN-L--DEVELOPMENT/data/birdcamtides.csv

  Site     Start.Date.Time       End.Date.Time

1 LCOR_1 2017-11-08 13:20:00 2017-11-08 15:00:00 2 LCOR_1 2017-11-09 08:40:00 2017-11-09 15:45:00 3 LCOR_1 2017-11-10 09:20:00 2017-11-10 18:30:00 4 LCOR_1 2017-11-11 10:00:00 2017-11-11 18:55:00 5 LCOR_1 2017-11-12 12:05:00 2017-11-12 18:30:00 6 LCOR_1 2017-11-13 13:40:00 2017-11-13 18:55:00 7 LCOR_1 2017-11-14 07:35:00 2017-11-14 09:10:00 8 LCOR_1 2017-11-14 14:45:00 2017-11-14 18:55:00 9 LCOR_1 2017-11-15 07:30:00 2017-11-15 10:30:00 10 LCOR_1 2017-11-15 15:50:00 2017-11-15 18:55:00

library(lubridate)
tides <- read.csv("data/birdcamtides.csv", header = T)

tides$Start.Date.Time <- paste(tides$Date, tides$Start.Low.Tide)
tides$End.Date.Time <- paste(tides$Date, tides$End.Low.Tide)

tides$Start.Date.Time <- mdy_hm(tides$Start.Date.Time, tz = 'EST')
tides$End.Date.Time <- mdy_hm(tides$End.Date.Time, tz = 'EST')

tides$interval <- interval(tides$Start.Low.Tides,
tides$End.Low.Tide)

I keep getting the error "Error in as.POSIXlt.character(as.character(x), ...) : character string is not in a standard unambiguous format"

I don't understand how I am getting this error, since all of my dates are in the POSIX.ct format. My question is different because I am trying to write a function or something to create a new column with an interval for each corresponding start and end time.

please provide us a reproducible example using dput() otherwise it is difficult to help you — Hunaidkhan

william3031 william3031 · Accepted Answer · 2018-11-16T05:15:59

dput:

tides <- structure(list(Date = structure(c(15L, 16L, 1L, 2L, 3L, 4L, 5L, 
5L, 6L, 6L, 7L, 7L, 8L, 8L, 9L, 10L, 11L, 12L, 13L, 14L), .Label = c("11/10/2017", 
"11/11/2017", "11/12/2017", "11/13/2017", "11/14/2017", "11/15/2017", 
"11/16/2017", "11/17/2017", "11/18/2017", "11/19/2017", "11/20/2017", 
"11/21/2017", "11/22/2017", "11/23/2017", "11/8/2017", "11/9/2017"
), class = "factor"), Site = structure(c(1L, 1L, 1L, 1L, 1L, 
1L, 1L, 1L, 1L, 1L, 1L, 1L, 1L, 1L, 1L, 1L, 1L, 1L, 1L, 1L), .Label = "LCOR_1", class = "factor"), 
    Camera = structure(c(1L, 1L, 1L, 1L, 1L, 1L, 1L, 1L, 1L, 
    1L, 1L, 1L, 1L, 1L, 1L, 1L, 1L, 1L, 1L, 1L), .Label = "HC500", class = "factor"), 
    Start.Low.Tide = structure(c(3L, 13L, 14L, 1L, 2L, 4L, 10L, 
    5L, 9L, 6L, 9L, 7L, 9L, 8L, 9L, 10L, 9L, 11L, 11L, 12L), .Label = c("10:00", 
    "12:05", "13:20", "13:40", "14:45", "15:50", "16:40", "17:55", 
    "7:30", "7:35", "7:40", "8:00", "8:40", "9:20"), class = "factor"), 
    End.Low.Tide = structure(c(10L, 11L, 12L, 14L, 12L, 14L, 
    15L, 14L, 2L, 13L, 1L, 13L, 4L, 13L, 3L, 5L, 8L, 6L, 7L, 
    9L), .Label = c("10:25", "10:30:00", "10:45", "10:55", "12:20", 
    "12:50", "13:25", "13:40", "14:35", "15:00:00", "15:45:00", 
    "18:30:00", "18:55", "18:55:00", "9:10:00"), class = "factor"), 
    Notes = structure(c(1L, 1L, 1L, 3L, 1L, 3L, 2L, 3L, 2L, 3L, 
    2L, 3L, 2L, 3L, 2L, 2L, 2L, 2L, 2L, 2L), .Label = c("", "reef showing on the first frame of the day", 
    "reef showing on the last frame of the day"), class = "factor"), 
    Start.Date.Time = structure(c(1510165200, 1510234800, 1510323600, 
    1510412400, 1510506300, 1510598400, 1510662900, 1510688700, 
    1510749000, 1510779000, 1510835400, 1510868400, 1510921800, 
    1510959300, 1511008200, 1511094900, 1511181000, 1511268000, 
    1511354400, 1511442000), class = c("POSIXct", "POSIXt"), tzone = "EST"), 
    End.Date.Time = structure(c(NA, NA, NA, NA, NA, NA, NA, NA, 
    NA, 1510790100, 1510845900, 1510876500, 1510934100, 1510962900, 
    1511019900, 1511112000, 1511203200, 1511286600, 1511375100, 
    1511465700), class = c("POSIXct", "POSIXt"), tzone = "EST")), .Names = c("Date", 
"Site", "Camera", "Start.Low.Tide", "End.Low.Tide", "Notes", 
"Start.Date.Time", "End.Date.Time"), row.names = c(NA, 20L), class = "data.frame")

The variable for the interval is wrong. It should be: tides$interval <- interval(tides$Start.Date.Time, tides$End.Date.Time) as this is the lubridate version that you have calculated.

Creating a new date.time "interval" column based on start date.time and end date.time

1 Answers