Concatenate year, month, day and time problem in R

Question

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?

Also, you should provide a [reproducible example](https://stackoverflow.com/questions/5963269/how-to-make-a-great-r-reproducible-example) — bretauv, Dec 06 '22 at 10:01

DaveArmstrong · Accepted Answer · 2022-12-06T10:25:20.800

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)}

thats odd. arrange() does not work for me t his way. I have 2013-01-07 21:54:00, then 2013-03-20 13:59:00 and then 2013-01-03 18:40:00. — Bluetail, Dec 06 '22 at 10:32

harre · Answer 2 · 2022-12-06T10:24:58.830

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

it does not create this column.. I have checked sessionInfo() and I have R version 4.2.1 (2022-06-23 ucrt). so not sure why '|>' does not work for me. — Bluetail, Dec 06 '22 at 10:41
Based on your problems with `arrange` (other comments), I'll recommend a full restart of `R`. Alternatively use the `%>%` pipe (loaded with `dplyr`) or avoid the pipe altogether : `mutate(df, datetime = make_datetime(year, month, day, hour, minute))`. The solution is fully working here. — harre, Dec 06 '22 at 10:50

score 0 · Answer 3 · answered Dec 06 '22 at 10:08

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}

thanks. do you know how to sort by the datetime column after this? — Bluetail, Dec 06 '22 at 10:17

score 0 · Answer 4 · answered Dec 06 '22 at 10:12

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)}

Concatenate year, month, day and time problem in R

4 Answers4

Linked