I have these columns in my dataframe, df:
year month day hour minute
2013 1 7 21 54
2013 3 20 13 59
2013 1 3 18 40
.. cols(
.. year = col_double(),
.. month = col_double(),
.. day = col_double(),
.. hour = col_double(),
.. minute = col_double(),
I want to have a new column, datetime:
datetime
2013/1/7 21:54
2013/3/20 13:59
2013/1/3 18:40
I have tried this:
library(readr)
library(dplyr)
df$datetime <- with(df, as.POSIXct(paste(year, month, day, hour, minute),
format = "%Y/%m/%d %H:%M:%S"))
and this:
df$DT <- as.POSIXct((paste(df$year, df$month, df$day, df$hour, df$minute)), format="%Y/%m/%d %H:%M:%S")
However, it gives me all NA values.
I could merge just the year, month and day with as.Date() though. How can I add times to it?
Also, how can I sort by datetime later on?
CodePudding user response：
You could use your original syntax, but make sure you put the right separators between the various components of the date-time:
dat <- tibble::tribble(
~year, ~month, ~day, ~hour, ~minute,
2013, 1, 7, 21, 54,
2013, 3, 20, 13, 59,
2013, 1, 3, 18, 40)
dat$datetime <- with(dat, as.POSIXct(paste0(year, "/", month, "/", day, " ", hour, ":", minute, ":00"),
format = "%Y/%m/%d %H:%M:%S"))
dat
#> # A tibble: 3 × 6
#> year month day hour minute datetime
#> <dbl> <dbl> <dbl> <dbl> <dbl> <dttm>
#> 1 2013 1 7 21 54 2013-01-07 21:54:00
#> 2 2013 3 20 13 59 2013-03-20 13:59:00
#> 3 2013 1 3 18 40 2013-01-03 18:40:00
Created on 2022-12-06 by the reprex package (v2.0.1)
When you tell as.POSIXct() that the format is "%Y/%m/%d %H:%M:%S", it expects to see a string that looks like that (e.g., "2013/01/03 13:59:00"). Your syntax was pasting them together with just spaces, making something like "2013 01 03 13 59" so when as.POSIXct() tried to parse the string, it didn't see the expected separators. You could also have gotten the same result by maintaining your original paste() specification and changing the format:
library(dplyr)
dat <- tibble::tribble(
~year, ~month, ~day, ~hour, ~minute,
2013, 1, 7, 21, 54,
2013, 3, 20, 13, 59,
2013, 1, 3, 18, 40)
dat$datetime <- with(dat, as.POSIXct(paste(year, month, day, hour, minute),
format = "%Y %m %d %H %M"))
arrange(dat, desc(datetime))
#> # A tibble: 3 × 6
#> year month day hour minute datetime
#> <dbl> <dbl> <dbl> <dbl> <dbl> <dttm>
#> 1 2013 3 20 13 59 2013-03-20 13:59:00
#> 2 2013 1 7 21 54 2013-01-07 21:54:00
#> 3 2013 1 3 18 40 2013-01-03 18:40:00
Created on 2022-12-06 by the reprex package (v2.0.1)
CodePudding user response：
The easiest way is to use make_datetime from lubridate. This function accepts the double inputs directly so you don't need to concatenate into a string yourself.
library(dplyr)
library(lubridate)
df |> mutate(datetime = make_datetime(year, month, day, hour, minute))
Output:
# A tibble: 3 × 6
year month day hour minute datetime
<dbl> <dbl> <dbl> <dbl> <dbl> <dttm>
1 2013 1 7 21 54 2013-01-07 21:54:00
2 2013 3 20 13 59 2013-03-20 13:59:00
3 2013 1 3 18 40 2013-01-03 18:40:00
Data:
library(readr)
df <- read_table("year month day hour minute
2013 1 7 21 54
2013 3 20 13 59
2013 1 3 18 40")
Update: This can also be sorted using arrange:
library(dplyr)
library(lubridate)
df |>
mutate(datetime = make_datetime(year, month, day, hour, minute)) |>
arrange(datetime)
Output:
# A tibble: 3 × 6
year month day hour minute datetime
<dbl> <dbl> <dbl> <dbl> <dbl> <dttm>
1 2013 1 3 18 40 2013-01-03 18:40:00
2 2013 1 7 21 54 2013-01-07 21:54:00
3 2013 3 20 13 59 2013-03-20 13:59:00
CodePudding user response：
An alternative to @DaveArmstrong's answer, using tidyverse:
suppressPackageStartupMessages({
library(tidyr)
library(lubridate)
library(dplyr)
})
#> Warning: package 'lubridate' was built under R version 4.2.2
#> Warning: package 'timechange' was built under R version 4.2.2
test <- tibble::tribble(
~year, ~month, ~day, ~hour, ~minute,
2013, 1, 7, 21, 54,
2013, 3, 20, 13, 59,
2013, 1, 3, 18, 40)
test
#> # A tibble: 3 × 5
#> year month day hour minute
#> <dbl> <dbl> <dbl> <dbl> <dbl>
#> 1 2013 1 7 21 54
#> 2 2013 3 20 13 59
#> 3 2013 1 3 18 40
test |>
unite(col = datetime, everything(), sep = "-", remove = FALSE) |>
mutate(
datetime = ymd_hm(datetime)
)
#> # A tibble: 3 × 6
#> datetime year month day hour minute
#> <dttm> <dbl> <dbl> <dbl> <dbl> <dbl>
#> 1 2013-01-07 21:54:00 2013 1 7 21 54
#> 2 2013-03-20 13:59:00 2013 3 20 13 59
#> 3 2013-01-03 18:40:00 2013 1 3 18 40
Created on 2022-12-06 with reprex v2.0.2
CodePudding user response：
To concatenate the year, month, day, and time in R, you can use the paste() function to combine the different date and time components into a single string. Here is an example of how this can be done:
# Define the year, month, day, and time variables
year <- 2018
month <- 12
day <- 31
time <- "23:59:59"
# Use the paste() function to concatenate the date and time components
date_time <- paste(year, month, day, time, sep = " ")
# Print the concatenated date and time
print(date_time)
This will print the concatenated date and time as a string, such as "2018 12 31 23:59:59". You can specify the separator string using the sep argument in the paste() function to control how the different date and time components are combined.
CodePudding user response：
library(magrittr)
df <- tibble::tribble(
~year, ~month, ~day, ~hour, ~minute,
2013, 1, 7, 21, 54,
2013, 3, 20, 13, 59,
2013, 1, 3, 18, 40,
)
df %>%
# pad date elements with leading zeros so parsing works out
dplyr::mutate(month = stringr::str_pad(month, width = 2, pad = "0"),
day = stringr::str_pad(day, width = 2, pad = "0")) %>%
# parse as actual datetime
dplyr::mutate(datetime = lubridate::ymd_hm(paste0(year, month, day, hour, minute)))
#> # A tibble: 3 x 6
#> year month day hour minute datetime
#> <dbl> <chr> <chr> <dbl> <dbl> <dttm>
#> 1 2013 01 07 21 54 2013-01-07 21:54:00
#> 2 2013 03 20 13 59 2013-03-20 13:59:00
#> 3 2013 01 03 18 40 2013-01-03 18:40:00
Created on 2022-12-06 by the reprex package (v2.0.1)