0

i have xlsx data, @ ten minute intervals from origin (first row)

xlsx df

the date-time column loaded into R, in a df is numeric

class(data)
[1] "data.frame"
> class(data$X1)
[1] "numeric"

i have tried a few things to convert df to correct date-time, respective of data origin and intervals

data$date <- as_date(data$X1, origin = lubridate::origin)
data$date <- as_datetime(data$X1, origin = "2001-09-25 12:00:00", tz = "WST")

data$date <-  as.Date(as.character(data$X1),format = "Y%m%d H%M%S")

data$date <- strptime(data$X1, format = "d%m%%Y% H%M%S")

data$date <- as.Date(as.character(data$X1),          # as.Date & as.character functions
                   format = "d%m%%Y% H%M%S")

data$date <-  as.POSIXct(strptime(paste(as.numeric(data[,1])), format = "%Y%m%d %H:%M:%S"))

either resulting in NA's or incorrect

summary(data$date)
                 Min.               1st Qu.                Median                  Mean 
"2001-09-25 22:19:19" "2001-09-25 22:48:38" "2001-09-25 23:17:58" "2001-09-25 23:17:58" 
              3rd Qu.                  Max. 
"2001-09-25 23:47:17" "2001-09-26 00:16:36"

help to get convert the numeric date-time into a POSIX format, much appreciated

rdfleay
  • 71
  • 1
  • 7
  • How is the last chunk of code showing NA or incorrect ? – Julien Sep 03 '22 at 07:44
  • What is your question? – Julien Sep 03 '22 at 07:44
  • Convert your `date` column to character format. Then use `as.POSIXct(data$date, format = "%d/%m/%Y %H:%M")`. It worked for me – Fatih Aslan Sep 03 '22 at 07:47
  • 1
    See https://stackoverflow.com/questions/73581776/how-to-change-a-number-into-datetime-format-in-r/73582352#73582352 – G. Grothendieck Sep 03 '22 at 12:58
  • @Julien my question is, How do I get R to return a list of dates and times with origin date-time @ 25-09-2001 12:00, proceeding at 10 minute intervals, ie. 25-09-2001 12:00 25-09-2001 12:10 25-09-2001 12:20 ... for the length of the list (1013400 Observations) – rdfleay Sep 08 '22 at 01:54
  • @Julien i do not know why the last chunk is showing NA.. or why it is incorrect. Lubridate `data$date <- as_datetime(data$X1, origin = "2001-09-25 12:00:00", tz = "WST")` ..gives.. `Min."2001-09-25 22:19:19" Max. "2001-09-26 00:16:36".` ..even when the origin is stated. – rdfleay Sep 08 '22 at 02:00
  • @FatihAslan `data$date <- as.character(data$X1)` `data$date <- as.POSIXct(data$date, format = "%d/%m/%Y %H:%M")` `summary(data$date)` `Min. 1st Qu. Median Mean 3rd Qu. Max. NA's NA NA NA NA NA NA "1013400" ` . Can you post your result, as my result is.. NA's – rdfleay Sep 08 '22 at 02:04
  • @G.Grothendieck OPtion 3. `data$date <- as_datetime(as_date(data$X1, origin = "1899-12-30"), tz = "")` .. `MIn "2001-09-25 20:00:00" Max. "2021-01-01 07:50:00"` ..correct dates, no timezone stated, incorrect origin time (12:00). Adding `data$date <- as_datetime(as_date(data$X1, origin = "1899-12-30"), tz = "UTC + 8:00") ` returns correct date-time `Min "2001-09-25 12:00:00" Max. "2020-12-31 23:50:00"`. Thank you – rdfleay Sep 08 '22 at 02:21

1 Answers1

0

data$date <- as_datetime(as_date(data$X1, origin = "1899-12-30"), tz = "UTC + 8:00")

create&label new column of date-times <- create list of dates and times(convert to date(specify data, correct start date), specify correct time zone for time ie:local time zone)

summary(data$date)

Min. "2001-09-25 12:00:00" 1st Qu."2006-07-20 20:57:30" Median "2011-05-15 05:55:00" Mean "2011-05-15 05:55:00" 3rd Qu."2016-03-08 14:52:30" Max. "2020-12-31 23:50:00"

Thank you @G.Grothendieck for the great resource Thank you all for your attention and helping to confirm incorrect workflow, more than half the time is spent trying wrong code x)

rdfleay
  • 71
  • 1
  • 7