1

I have this df:

df <- structure(list(Created = structure(6:1, .Label = c("2018-12-27T08:53:32.794-0300", 
"2018-12-27T17:46:00.244-0300", "2019-01-17T17:16:08.222-0300", 
"2019-01-28T11:52:39.744-0300", "2019-01-28T11:55:34.723-0300", 
"2019-02-18T08:59:57.067-0300"), class = "factor"), Updated = structure(c(5L, 
3L, 2L, 1L, 4L, 6L), .Label = c("2019-03-04T17:41:30.895-0300", 
"2019-03-04T17:41:35.756-0300", "2019-03-08T15:37:32.071-0300", 
"2019-03-12T12:25:31.258-0300", "2019-03-12T16:20:48.210-0300", 
"2019-03-22T10:40:36.560-0300"), class = "factor"), Resolved = structure(c(5L, 
3L, 1L, 2L, 4L, 6L), .Label = c("2019-02-12T11:36:03.678-0300", 
"2019-02-27T09:09:58.990-0300", "2019-03-08T15:37:32.065-0300", 
"2019-03-12T12:25:31.251-0300", "2019-03-12T16:20:48.203-0300", 
"2019-03-22T10:40:36.553-0300"), class = "factor")), row.names = c(14L, 
28L, 29L, 30L, 37L, 38L), class = "data.frame")

> df
                        Created                      Updated                     Resolved
14 2019-02-18T08:59:57.067-0300 2019-03-12T16:20:48.210-0300 2019-03-12T16:20:48.203-0300
28 2019-01-28T11:55:34.723-0300 2019-03-08T15:37:32.071-0300 2019-03-08T15:37:32.065-0300
29 2019-01-28T11:52:39.744-0300 2019-03-04T17:41:35.756-0300 2019-02-12T11:36:03.678-0300
30 2019-01-17T17:16:08.222-0300 2019-03-04T17:41:30.895-0300 2019-02-27T09:09:58.990-0300
37 2018-12-27T17:46:00.244-0300 2019-03-12T12:25:31.258-0300 2019-03-12T12:25:31.251-0300
38 2018-12-27T08:53:32.794-0300 2019-03-22T10:40:36.560-0300 2019-03-22T10:40:36.553-0300

And I need to transform all of them to strptime(), so for column Created:

First step: to character:

df <- df %>% lapply(., as.character)

Second step: Split.

paste0(substr(df$Created,start=1,stop=10)," ", substr(df$Created,start=12,stop=19)," ",substr(df$Created,start=25,stop=29))

Third step: to strptime()

df2 <- df %>%
  separate(Created, into = c("date", "time", "timezone"), sep = " ") %>%
  unite(col = Created, c("date", "time"), sep = " ") %>%
  mutate(Created = ymd_hms(Created)) %>%
  mutate(Created = if_else(timezone %in% "0300", Created + hours(1), Created)) %>%
  select(-timezone)

And all is perfect:

> df2[1:5,c("Created")]
[1] "2019-02-18 11:59:57 UTC" "2019-01-28 14:55:34 UTC" "2019-01-28 14:52:39 UTC" "2019-01-17 20:16:08 UTC" "2018-12-27 20:46:00 UTC"

However, I'm struggling to put this in lapply() function as it's not just 3 columns but almost 30. Any suggestions?

Chris
  • 2,019
  • 5
  • 22
  • 67

2 Answers2

2

It is possible to parse datetime strings with time zone. For example: Parsing ISO8601 date and time format in R

With a bit of change, you can do something like this:

library(stringi)

df %>% 
  mutate_at(1:3, as.character) %>%
  mutate_at(1:3, function(x){
    x %>% stri_replace_first_regex("\\.\\d+", "") %>%
      strptime("%Y-%m-%dT%H:%M:%S%z", tz="UTC") %>%
      as.POSIXct()
  }) 

##               Created             Updated            Resolved
## 1 2019-02-18 11:59:57 2019-03-12 19:20:48 2019-03-12 19:20:48
## 2 2019-01-28 14:55:34 2019-03-08 18:37:32 2019-03-08 18:37:32
## 3 2019-01-28 14:52:39 2019-03-04 20:41:35 2019-02-12 14:36:03
## 4 2019-01-17 20:16:08 2019-03-04 20:41:30 2019-02-27 12:09:58
## 5 2018-12-27 20:46:00 2019-03-12 15:25:31 2019-03-12 15:25:31
## 6 2018-12-27 11:53:32 2019-03-22 13:40:36 2019-03-22 13:40:36

By changing the first argument of mutate_at, you can convert all the columns you want to convert.

Edit

Obviously, split-seconds can also be parsed. The modified code (with a bit of annotation) is:

string_conversion <- function(dt_string) {
  dt_string %>% 
    strptime("%Y-%m-%dT%H:%M:%S.%OS%z", tz="UTC") %>% # parse the string
    as.POSIXct() %>% # the end product is converted to POSIXct as POSIXlt is not supported.
    return()
}

df %>% 
  mutate_at(1:3, as.character) %>% # convert columns to characters
  mutate_at(1:3, string_conversion)
amatsuo_net
  • 2,409
  • 11
  • 20
  • Can you explain what does that function? In specific, the regex step. – Chris Apr 02 '19 at 15:03
  • I udpated the answer. The regex-part which is gone in the new implementation was to delete the milliseconds part of the time string. – amatsuo_net Apr 02 '19 at 15:09
1

The OP asked for the use of lapply so here it is.

First, second and third steps:

df[] <- lapply(df, function(column) {
    ## remove split seconds
    datetime <- stri_replace_first_regex(as.character(column), "\\.\\d+", "")
    ## identify the format of date, time, and zone in the string using strptime
    datetimestr <- strptime(datetime, "%Y-%m-%dT%H:%M:%S%z", tz="UTC")
    ## coerce to POSIXct and POSIXt classes 
    as.POSIXct(datetimestr)
})
mr148
  • 103
  • 6