CODEWITHSUNDEEP
rdatedatetime
Kristen Cyr
Kristen Cyr

Reputation: 726

How to create a Time Column Using the time from a Date/Time column in R?

I had a data frame with a column labelled Date_Time_GMT_3 which contained date/times. I used the Date_Time_GMT_3 column to create another data frame with 3 extra columns that have the month year and day seperated. This new data frame looks like so:

df = structure(list(Date_Time_GMT_3 = structure(list(sec = c(0, 0, 
                                                             0, 0, 0, 0), min = c(0L, 0L, 0L, 0L, 0L, 0L), hour = c(8L, 8L, 
                                                                                                                    8L, 8L, 8L, 8L), mday = c(1L, 1L, 1L, 1L, 1L, 1L), mon = c(5L, 
                                                                                                                                                                               5L, 5L, 5L, 5L, 5L), year = c(121L, 121L, 121L, 121L, 121L, 121L
                                                                                                                                                                               ), wday = c(2L, 2L, 2L, 2L, 2L, 2L), yday = c(151L, 151L, 151L, 
                                                                                                                                                                                                                             151L, 151L, 151L), isdst = c(0L, 0L, 0L, 0L, 0L, 0L), zone = c("EST", 
                                                                                                                                                                                                                                                                                            "EST", "EST", "EST", "EST", "EST"), gmtoff = c(NA_integer_, NA_integer_, 
                                                                                                                                                                                                                                                                                                                                           NA_integer_, NA_integer_, NA_integer_, NA_integer_)), tzone = "EST", class = c("POSIXlt", 
                                                                                                                                                                                                                                                                                                                                                                                                                          "POSIXt")), name = c("X20676880_X3WR_AIR_Stationary", "X20819740_X3WR_U_Stationary", 
                                                                                                                                                                                                                                                                                                                                                                                                                                               "X20819740_X3WR_S_Stationary", "X21092860_X3WR_U_Compare", "X20676883_13WR_U_Stationary", 
                                                                                                                                                                                                                                                                                                                                                                                                                                               "X20676883_13WR_S_Stationary"), value = c(11.431, 11.625, NA, 
                                                                                                                                                                                                                                                                                                                                                                                                                                                                                         NA, 10.651, NA), month = c(6, 6, 6, 6, 6, 6), year = c(2021, 
                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                2021, 2021, 2021, 2021, 2021), day = c(1L, 1L, 1L, 1L, 1L, 1L
                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                )), row.names = c(NA, -6L), class = c("tbl_df", "tbl", "data.frame"
                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                ))

The code I used to get the month day and year columns from the Date_Time_GMT_3 column looks like this

 mutate(month = lubridate::month(Date_Time_GMT_3), 
         year = lubridate::year(Date_Time_GMT_3),
         day = lubridate::day(Date_Time_GMT_3))

Is there a way to use the lubridate function to get a time column. I've tried this line of code

 mutate(month = lubridate::month(Date_Time_GMT_3), 
         year = lubridate::year(Date_Time_GMT_3),
         day = lubridate::day(Date_Time_GMT_3),
#New LINE OF CODE
time = lubridate::hms(Date_Time_GMT_3))

When I use that new line of code I get this error

Warning message:
Problem with `mutate()` column `TIME`.
i `TIME = lubridate::hms(Date_Time_GMT_3)`.
i Some strings failed to parse, or all strings are NAs

Any ideas how to make it work?

Upvotes: 0

Views: 552

Answers (1)

Mr.Rlover
Mr.Rlover

Reputation: 2623

It doesn't work because hms() expects only numbers in triples, where you have a date before the time, so you need to remove that portion before passing it to hms(). I have used substr since all the dates must have the same format, in this case, YYYY-MM-DD, so keep everything starting from the 11th character.

lubridate::hms(substr(df$Date_Time_GMT_3, 11, nchar(df$Date_Time_GMT_3)))
[1] "8H 0M 0S" "8H 0M 0S" "8H 0M 0S" "8H 0M 0S" "8H 0M 0S" "8H 0M 0S"

In dplyr

df %>% 
  mutate(hms = lubridate::hms(substr(Date_Time_GMT_3, 11, nchar(Date_Time_GMT_3))))

# A tibble: 6 x 7
  Date_Time_GMT_3     name                        value month  year   day hms     
  <dttm>              <chr>                       <dbl> <dbl> <dbl> <int> <Period>
1 2021-06-01 08:00:00 X20676880_X3WR_AIR_Station~  11.4     6  2021     1 8H 0M 0S
2 2021-06-01 08:00:00 X20819740_X3WR_U_Stationary  11.6     6  2021     1 8H 0M 0S
3 2021-06-01 08:00:00 X20819740_X3WR_S_Stationary  NA       6  2021     1 8H 0M 0S
4 2021-06-01 08:00:00 X21092860_X3WR_U_Compare     NA       6  2021     1 8H 0M 0S
5 2021-06-01 08:00:00 X20676883_13WR_U_Stationary  10.7     6  2021     1 8H 0M 0S
6 2021-06-01 08:00:00 X20676883_13WR_S_Stationary  NA       6  2021     1 8H 0M 0S

Upvotes: 1

Related Questions