Combining Date and Hour (24 hour style) Columns

[HTP]

Hello everyone!

Suppose that I have a dataset that looks like this:

Date	Hour	Number
2017-01-01	1	5724.0
2017-01-01	2	5585.7
2017-01-01	3	5456.7
2017-01-01	4	5358.3
2017-01-01	5	5350.0
2017-01-01	6	5265.7
2017-01-01	7	5287.0
2017-01-01	8	5379.0
2017-01-01	9	5381.0
2017-01-01	10	5510.0
2017-01-01	11	5542.7
2017-01-01	12	5545.7
2017-01-01	13	5532.0
2017-01-01	14	5505.0
2017-01-01	15	5541.0
2017-01-01	16	5662.0
2017-01-01	17	5964.0
2017-01-01	18	6390.3
2017-01-01	19	6345.7
2017-01-01	20	6233.3
2017-01-01	21	6258.7
2017-01-01	22	6057.3
2017-01-01	23	5802.0
2017-01-01	24	5598.3
2017-01-02	1	5399.3
2017-01-02	2	5399.3
.	.	.
.	.	.
2017-12-31	23	8499.3
2017-12-31	24	8689.7

I want to combine the first two "Date" and "Hour" columns to make it look like this (or something similar):

Date & Hour	Number
2017-01-01: 01	5724.0
2017-01-01: 02	5585.7
2017-01-01: 03	5456.7
2017-01-01: 04	5358.3
2017-01-01: 05	5350.0
.	.
.	.
2017-01-01: 23	5802.0
2017-01-01: 24	5598.3

Anybody know how to do this? Any help would be much appreciated!

[andresrcs]

You can get your desired output this way but, be aware that the resulting column is of class "character", not a POSIXct column.

library(tidyverse)

sample_df <- data.frame(
        Date = c("2017-01-01","2017-01-01","2017-01-01",
                 "2017-01-01","2017-01-01","2017-01-01","2017-01-01",
                 "2017-01-01","2017-01-01","2017-01-01","2017-01-01","2017-01-01",
                 "2017-01-01","2017-01-01","2017-01-01","2017-01-01",
                 "2017-01-01","2017-01-01","2017-01-01","2017-01-01","2017-01-01",
                 "2017-01-01","2017-01-01","2017-01-01","2017-01-02",
                 "2017-01-02"),
        Hour = c(1,2,3,4,5,6,7,8,9,10,11,12,13,
                 14,15,16,17,18,19,20,21,22,23,24,1,2),
      Number = c(5724,5585.7,5456.7,5358.3,5350,5265.7,
                 5287,5379,5381,5510,5542.7,5545.7,5532,5505,5541,5662,
                 5964,6390.3,6345.7,6233.3,6258.7,6057.3,5802,5598.3,
                 5399.3,5399.3)
)

sample_df %>%
    mutate(Hour = str_pad(Hour, 2, side = "left", pad = "0")) %>% 
    unite(Date_Hour, Date, Hour, sep = ": ")
#>         Date_Hour Number
#> 1  2017-01-01: 01 5724.0
#> 2  2017-01-01: 02 5585.7
#> 3  2017-01-01: 03 5456.7
#> 4  2017-01-01: 04 5358.3
#> 5  2017-01-01: 05 5350.0
#> 6  2017-01-01: 06 5265.7
#> 7  2017-01-01: 07 5287.0
#> 8  2017-01-01: 08 5379.0
#> 9  2017-01-01: 09 5381.0
#> 10 2017-01-01: 10 5510.0
#> 11 2017-01-01: 11 5542.7
#> 12 2017-01-01: 12 5545.7
#> 13 2017-01-01: 13 5532.0
#> 14 2017-01-01: 14 5505.0
#> 15 2017-01-01: 15 5541.0
#> 16 2017-01-01: 16 5662.0
#> 17 2017-01-01: 17 5964.0
#> 18 2017-01-01: 18 6390.3
#> 19 2017-01-01: 19 6345.7
#> 20 2017-01-01: 20 6233.3
#> 21 2017-01-01: 21 6258.7
#> 22 2017-01-01: 22 6057.3
#> 23 2017-01-01: 23 5802.0
#> 24 2017-01-01: 24 5598.3
#> 25 2017-01-02: 01 5399.3
#> 26 2017-01-02: 02 5399.3

^{Created on 2022-08-26 with reprex v2.0.2}

[EconProf]

lubridate's ymd_h() will give you POSIXct y-m-d h:m:s format, but that may not be what you want

library(dplyr)
library(lubridate)

sample_df <- data.frame(
  Date = c("2017-01-01","2017-01-01","2017-01-01",
           "2017-01-01","2017-01-01","2017-01-01","2017-01-01",
           "2017-01-01","2017-01-01","2017-01-01","2017-01-01","2017-01-01",
           "2017-01-01","2017-01-01","2017-01-01","2017-01-01",
           "2017-01-01","2017-01-01","2017-01-01","2017-01-01","2017-01-01",
           "2017-01-01","2017-01-01","2017-01-01","2017-01-02",
           "2017-01-02"),
  Hour = c(1,2,3,4,5,6,7,8,9,10,11,12,13,
           14,15,16,17,18,19,20,21,22,23,24,1,2),
  Number = c(5724,5585.7,5456.7,5358.3,5350,5265.7,
             5287,5379,5381,5510,5542.7,5545.7,5532,5505,5541,5662,
             5964,6390.3,6345.7,6233.3,6258.7,6057.3,5802,5598.3,
             5399.3,5399.3)
)

sample_df %>%
  mutate(Date_Hour = ymd_h(paste(Date, Hour))) %>% 
  select(Date_Hour, Number)
#>              Date_Hour Number
#> 1  2017-01-01 01:00:00 5724.0
#> 2  2017-01-01 02:00:00 5585.7
#> 3  2017-01-01 03:00:00 5456.7
#> 4  2017-01-01 04:00:00 5358.3
#> 5  2017-01-01 05:00:00 5350.0
#> 6  2017-01-01 06:00:00 5265.7
#> 7  2017-01-01 07:00:00 5287.0
#> 8  2017-01-01 08:00:00 5379.0
#> 9  2017-01-01 09:00:00 5381.0
#> 10 2017-01-01 10:00:00 5510.0
#> 11 2017-01-01 11:00:00 5542.7
#> 12 2017-01-01 12:00:00 5545.7
#> 13 2017-01-01 13:00:00 5532.0
#> 14 2017-01-01 14:00:00 5505.0
#> 15 2017-01-01 15:00:00 5541.0
#> 16 2017-01-01 16:00:00 5662.0
#> 17 2017-01-01 17:00:00 5964.0
#> 18 2017-01-01 18:00:00 6390.3
#> 19 2017-01-01 19:00:00 6345.7
#> 20 2017-01-01 20:00:00 6233.3
#> 21 2017-01-01 21:00:00 6258.7
#> 22 2017-01-01 22:00:00 6057.3
#> 23 2017-01-01 23:00:00 5802.0
#> 24 2017-01-02 00:00:00 5598.3
#> 25 2017-01-02 01:00:00 5399.3
#> 26 2017-01-02 02:00:00 5399.3

^{Created on 2022-08-26 with reprex v2.0.2}

[nedallo]

you can also use the following syntax :

df <- data.frame(Date = c("2017-01-01","2017-01-01","2017-01-01",
"2017-01-01","2017-01-01","2017-01-01","2017-01-01",
"2017-01-01","2017-01-01","2017-01-01","2017-01-01","2017-01-01",
"2017-01-01","2017-01-01","2017-01-01","2017-01-01",
"2017-01-01","2017-01-01","2017-01-01","2017-01-01","2017-01-01",
"2017-01-01","2017-01-01","2017-01-01","2017-01-02",
"2017-01-02"),
Hour = c(1,2,3,4,5,6,7,8,9,10,11,12,13,
14,15,16,17,18,19,20,21,22,23,24,1,2),
Number = c(5724,5585.7,5456.7,5358.3,5350,5265.7,
5287,5379,5381,5510,5542.7,5545.7,5532,5505,5541,5662,
5964,6390.3,6345.7,6233.3,6258.7,6057.3,5802,5598.3,
5399.3,5399.3))

unite(df, col='Date&Hour', c('Date', 'Hour'), sep=':')

hope this helpful
Kind Regards