NA when convert date in format YYYY-mm-dd hh:mm:ss.0000000 in R

user_4529548 0
Viewed 113

i try convert date format

tt=structure(list(gr = c(2L, 2L, 2L, 2L, 2L, 2L, 2L, 2L, 2L, 2L, 
2L, 2L, 2L, 2L, 2L, 2L, 2L, 2L, 2L, 2L), date = c("2021-01-02 11:53:53.0000000", 
"2021-01-02 11:54:00.0000000", "2021-01-02 10:00:46.0000000", 
"2021-01-02 10:00:23.0000000", "2021-01-02 10:09:00.0000000", 
"2021-01-02 11:05:01.0000000", "2021-01-02 11:03:49.0000000", 
"2021-01-02 13:24:02.0000000", "2021-01-02 13:24:02.0000000", 
"2021-01-02 13:39:40.0000000", "2021-01-02 13:39:40.0000000", 
"2021-01-02 13:39:40.0000000", "2021-01-02 13:39:40.0000000", 
"2021-01-02 13:39:40.0000000", "2021-01-02 13:39:40.0000000", 
"2021-01-02 13:24:02.0000000", "2021-01-02 13:24:02.0000000", 
"2021-01-02 13:24:02.0000000", "2021-01-02 13:24:02.0000000", 
"2021-01-02 12:57:00.0000000"), metric = c(23749.14, 1000, 3050, 
1550, 8900, 1550, 0, 300, 0, 499, 5450, 300, 0, 499, 599, 599, 
6050, 300, 599, 1400)), class = "data.frame", row.names = c(NA, 
-20L))

i use this way,but something goes wrong

tibble(tt) %>%

  mutate(date = mdy(date),

         date_time = ymd_hms(format(date, "%Y-%m-%d 00:00:00")),

         week_num = week(date),

         weekday = weekdays(date)) %>%

  select(sales_count, date_time, date, week_num, weekday)

and got the error

Problem with `mutate()` input `index_date`.
i All formats failed to parse. No formats found.
i Input `index_date` is `dmy(date)`

and variables with NA. But i need that result was something like this

        metric       date_time        date    week_num weekday              gr
                
 1          10 2021-01-01 00:00:00 2021-01-01        1 Friday               2
 2           4 2021-01-01 00:00:00 2021-01-01        1 Friday               2
 3           8 2021-01-01 00:00:00 2021-01-01        1 Friday               2
 4           6 2021-01-01 00:00:00 2021-01-01        1 Friday               2
 5           4 2021-01-01 00:00:00 2021-01-01        1 Friday               2
 6           4 2021-01-01 00:00:00 2021-01-01        1 Friday               2
 7           4 2021-01-01 00:00:00 2021-01-01        1 Friday               2
 8           3 2021-01-01 00:00:00 2021-01-01        1 Friday               2
 9           1 2021-01-01 00:00:00 2021-01-01        1 Friday               2
10           5 2021-01-01 00:00:00 2021-01-01        1 Friday               2
11           2 2021-01-01 00:00:00 2021-01-01        1 Friday               2

How correct do it to get desired output? thank you.

r
3 Answers

Strip those .0000000 with substr and then make the transformation you need with lubridate::ymd_hms():

tt %>%
  dplyr::mutate(date2 = base::substr(date, 1, 19)) %>%
  dplyr::mutate(date3 = lubridate::ymd_hms(date2))
user_63176880

using mdy() is a wrong format. correct is ymd_hms()

Therefor is suggest:

library(dplyr)
library(lubridate)

tibble(tt) %>%
  mutate(date_time = ymd_hms(date),       
         date = as_date(date),     
         week_num = week(date),         
         weekday = weekdays(date))

output is:

# A tibble: 20 × 6
      gr date       metric date_time           week_num weekday 
   <int> <date>      <dbl> <dttm>                 <dbl> <chr>   
 1     2 2021-01-02 23749. 2021-01-02 11:53:53        1 Saturday
 2     2 2021-01-02  1000  2021-01-02 11:54:00        1 Saturday
 3     2 2021-01-02  3050  2021-01-02 10:00:46        1 Saturday
 4     2 2021-01-02  1550  2021-01-02 10:00:23        1 Saturday
 5     2 2021-01-02  8900  2021-01-02 10:09:00        1 Saturday
 6     2 2021-01-02  1550  2021-01-02 11:05:01        1 Saturday
 7     2 2021-01-02     0  2021-01-02 11:03:49        1 Saturday
 8     2 2021-01-02   300  2021-01-02 13:24:02        1 Saturday
 9     2 2021-01-02     0  2021-01-02 13:24:02        1 Saturday
10     2 2021-01-02   499  2021-01-02 13:39:40        1 Saturday
11     2 2021-01-02  5450  2021-01-02 13:39:40        1 Saturday
12     2 2021-01-02   300  2021-01-02 13:39:40        1 Saturday
13     2 2021-01-02     0  2021-01-02 13:39:40        1 Saturday
14     2 2021-01-02   499  2021-01-02 13:39:40        1 Saturday
15     2 2021-01-02   599  2021-01-02 13:39:40        1 Saturday
16     2 2021-01-02   599  2021-01-02 13:24:02        1 Saturday
17     2 2021-01-02  6050  2021-01-02 13:24:02        1 Saturday
18     2 2021-01-02   300  2021-01-02 13:24:02        1 Saturday
19     2 2021-01-02   599  2021-01-02 13:24:02        1 Saturday
20     2 2021-01-02  1400  2021-01-02 12:57:00        1 Saturday
user_85983770

slightly different apporach using functions from lubridate

library(tidyverse)
library(lubridate)
tt %>%
  mutate(date = as.POSIXct(date, "%Y-%m-%d %H:%M:%OS", tz = "UTC"),
         weekday = lubridate::wday(date, label = TRUE, abbr = FALSE),
         week = lubridate::week(date))
user_63562780
Related