Single-digit hour fails in col_datetime() but not parse_datetime()

[k5cents]

In this case, col_datetime() fails when using the format %m/%d/%Y %H:%M:%S %p.

Seems to stem from the single digit day in the %d position. Using %e does not fix the problem.

Possibly related to tidyverse/vroom#170 and tidyverse/vroom#123, which were fixed and closed.

packageVersion("readr")
#> [1] '2.0.0'
library(readr)

tmp <- tempfile(fileext = ".csv")
dat <- tibble::tibble(
  x = c("A", "B"),
  y = c("4/9/2021 2:18:25 PM", "4/13/2021 10:15:57 PM")
)
write_csv(dat, file = tmp)
fmt <- "%m/%d/%Y %H:%M:%S %p"

read_csv(
  file = tmp,
  col_types = cols(
    x = col_character(),
    y = col_datetime(fmt)
  )
)
#> Warning: One or more parsing issues, see `problems()` for details
#> # A tibble: 2 × 2
#>   x     y                  
#>   <chr> <dttm>             
#> 1 A     NA                 
#> 2 B     2021-04-13 22:15:57

parse_datetime(dat$y, format = fmt)
#> [1] "2021-04-09 14:18:25 UTC" "2021-04-13 22:15:57 UTC"

type_convert(
  df = dat,
  col_types = cols(
    x = col_character(),
    y = col_datetime(fmt)
  )
)
#> # A tibble: 2 × 2
#>   x     y                  
#>   <chr> <dttm>             
#> 1 A     2021-04-09 14:18:25
#> 2 B     2021-04-13 22:15:57

^{Created on 2021-08-17 by the reprex package (v2.0.0)}

[nickyrong]

@kiernann I think the culprit is not the lack of leading zero in day or month but the lack of leading zero in hour.

The unfortunate thing about this is that when saving a CSV in Excel, the default format is m/d/yyyy h:mm with a single digit in month, day, and hour (at least default in Canadian Excel). As you see in the example below, col_datetime() can actually deal with single digit day and month, just not hour.

library(tidyr)
library(readr)
packageVersion("readr")
[1] ‘2.0.1’
################# Scenario 1 ############################
tibble(Datetime = "3/2/2018 15:09") %>% write_csv("test_1.csv")
read_csv("test_1.csv",
         col_types = cols(
           Datetime = col_datetime(format = "%m/%d/%Y %H:%M")
         )
)
# A tibble: 1 x 1                                                                                                                                                                               
  Datetime           
  <dttm>             
1 2018-03-02 15:09:00

################# Scenario 2 ############################
tibble(Datetime = "3/2/2018 5:09") %>% write_csv("test_2.csv")
read_csv("test_2.csv",
         col_types = cols(
           Datetime = col_datetime(format = "%m/%d/%Y %H:%M")
         )
)
# A tibble: 1 x 1                                                                                                                                                                               
  Datetime
  <dttm>  
1 NA      
Warning message:
One or more parsing issues, see `problems()` for details 
> problems()
# A tibble: 1 x 5
    row   col expected                 actual        file                                                                      
  <int> <int> <chr>                    <chr>         <chr>                                                                     
1     2     1 date like %m/%d/%Y %H:%M 3/2/2018 5:09 ...test_2.csv

################# Scenario 3 ############################
tibble(Datetime = "3/2/2018 05:09") %>% write_csv("test_3.csv")
read_csv("test_3.csv",
         col_types = cols(
           Datetime = col_datetime(format = "%m/%d/%Y %H:%M")
         )
)

# A tibble: 1 x 1                                                                                                                                                                               
  Datetime           
  <dttm>             
1 2018-03-02 05:09:00

The possible go around is to parse the datetime column as character first then use other functions to convert. But that kinda defeats the point of having the option to directly parse datetime in read_csv.

read_csv("test_2.csv",
         col_types = cols(
           Datetime = col_character()
         )
) %>% mutate(Datetime = as.POSIXct(Datetime, format = "%m/%d/%Y %H:%M"))

# A tibble: 1 x 1                                                                                                                                                                               
  Datetime           
  <dttm>             
1 2018-03-02 05:09:00

Update

After scanning the open issues, I think this issue is related to #1269. Jim gave a possible solution to adding \n to the end of the string to make it recognizable. But...I am not sure how to inject string into a csv before it is read in by read_csv()?

################# UPDATED Scenario 2 ############################
tibble(Datetime = "3/2/2018 5:09\n") %>% write_csv("test_4.csv")
read_csv("test_4.csv",
         col_types = cols(
           Datetime = col_datetime(format = "%m/%d/%Y %H:%M")
         )
)
# A tibble: 1 x 1                                                                                                                                                                               
  Datetime           
  <dttm>             
1 2018-03-02 05:09:00

[nickyrong]

This bug seems to be related to the recent major update of version 2.x. Lack of leading zero in version 1.4.0 does not give error.

packageVersion("readr")
[1] ‘1.4.0’

tibble(Datetime = "3/2/2018 5:09") %>% write_csv("test_2.csv")
read_csv("test_2.csv",
         col_types = cols(
           Datetime = col_datetime(format = "%m/%d/%Y %H:%M")
         )
)
# A tibble: 1 x 1
  Datetime           
  <dttm>             
1 2018-03-02 05:09:00

``

[k5cents]

Good find, definitely related to the hour not the day.

Per help(parse_datetime), I tried using %I for the hour and the problem persists.

Hour: "%H" or "%I" or "%h", use I (and not H) with AM/PM, use h (and not H) if your times represent durations longer than one day.

packageVersion("readr")
#> [1] '2.0.0'
library(readr)

tmp <- tempfile(fileext = ".csv")
dat <- tibble::tibble(
  x = c("A", "B"),
  y = c("4/19/2021 2:18:25 PM", "4/13/2021 10:15:57 PM")
)
write_csv(dat, file = tmp)
fmt <- "%m/%d/%Y %I:%M:%S %p"

read_csv(
  file = tmp,
  col_types = cols(
    x = col_character(),
    y = col_datetime(fmt)
  )
)
#> Warning: One or more parsing issues, see `problems()` for details
#> # A tibble: 2 × 2
#>   x     y                  
#>   <chr> <dttm>             
#> 1 A     NA                 
#> 2 B     2021-04-13 22:15:57

^{Created on 2021-08-18 by the reprex package (v2.0.0)}

[peterdesmet]

Good to see this one is already reported, I encountered the same issue: frictionlessdata/frictionless-r#29 (with col_time() vs parse_time()).