Convert 'Hour of Year' into a datetime

Question

I have time-series data starting from Jan 1st with columns including 'Month', 'Hour of Day' and 'Hour of Year'. I would like to create a datetime column which expresses all this information in the format MM/DD/YYYY HH/MM.

I have tried converting the 'Hour of Year' column to datetime and timedelta objects however both times I receive an error saying that hour must have a value between 0 and 23. As I have data for the whole year, my column ranges from 1 to 8760.

I expect to get data that looks like this : 1/1/2018 1:00.

Here is a sample of the dataset I am working with:

Month   Hour_of_Day Hour_of_Year
1   1   1
1   2   2
1   3   3
1   4   4
1   5   5
1   6   6
1   7   7
1   8   8
1   9   9
1   10  10
1   11  11
1   12  12
1   13  13
1   14  14
1   15  15
1   16  16
1   17  17
1   18  18
1   19  19
1   20  20
1   21  21
1   22  22
1   23  23
1   24  24
1   1   25
1   2   26
1   3   27
1   4   28
1   5   29
1   6   30
1   7   31
1   8   32
1   9   33
1   10  34
1   11  35
1   12  36
1   13  37
1   14  38
1   15  39
1   16  40
1   17  41
1   18  42
1   19  43
1   20  44
1   21  45
1   22  46
1   23  47
1   24  48
1   1   49
1   2   50
1   3   51
1   4   52
1   5   53
1   6   54
1   7   55
1   8   56
1   9   57
1   10  58
1   11  59
1   12  60
1   13  61

Answer 1

pd.to_timedelta is your friend here:

df['ts'] = pd.Timestamp('2018-01-01')+pd.to_timedelta(df.Hour_of_Year, unit='H')

gives:

    Month  Hour_of_Day  Hour_of_Year                  ts
0       1            1             1 2018-01-01 01:00:00
1       1            2             2 2018-01-01 02:00:00
2       1            3             3 2018-01-01 03:00:00
3       1            4             4 2018-01-01 04:00:00
4       1            5             5 2018-01-01 05:00:00
5       1            6             6 2018-01-01 06:00:00
6       1            7             7 2018-01-01 07:00:00
7       1            8             8 2018-01-01 08:00:00
8       1            9             9 2018-01-01 09:00:00
9       1           10            10 2018-01-01 10:00:00
10      1           11            11 2018-01-01 11:00:00
11      1           12            12 2018-01-01 12:00:00
12      1           13            13 2018-01-01 13:00:00
13      1           14            14 2018-01-01 14:00:00
14      1           15            15 2018-01-01 15:00:00
15      1           16            16 2018-01-01 16:00:00
16      1           17            17 2018-01-01 17:00:00
17      1           18            18 2018-01-01 18:00:00
18      1           19            19 2018-01-01 19:00:00
19      1           20            20 2018-01-01 20:00:00
20      1           21            21 2018-01-01 21:00:00
21      1           22            22 2018-01-01 22:00:00
22      1           23            23 2018-01-01 23:00:00
23      1           24            24 2018-01-02 00:00:00
24      1            1            25 2018-01-02 01:00:00
25      1            2            26 2018-01-02 02:00:00
26      1            3            27 2018-01-02 03:00:00
27      1            4            28 2018-01-02 04:00:00
28      1            5            29 2018-01-02 05:00:00
29      1            6            30 2018-01-02 06:00:00
..    ...          ...           ...                 ...
31      1            8            32 2018-01-02 08:00:00
32      1            9            33 2018-01-02 09:00:00
33      1           10            34 2018-01-02 10:00:00
34      1           11            35 2018-01-02 11:00:00
35      1           12            36 2018-01-02 12:00:00
36      1           13            37 2018-01-02 13:00:00
37      1           14            38 2018-01-02 14:00:00
38      1           15            39 2018-01-02 15:00:00
39      1           16            40 2018-01-02 16:00:00
40      1           17            41 2018-01-02 17:00:00
41      1           18            42 2018-01-02 18:00:00
42      1           19            43 2018-01-02 19:00:00
43      1           20            44 2018-01-02 20:00:00
44      1           21            45 2018-01-02 21:00:00
45      1           22            46 2018-01-02 22:00:00
46      1           23            47 2018-01-02 23:00:00
47      1           24            48 2018-01-03 00:00:00
48      1            1            49 2018-01-03 01:00:00
49      1            2            50 2018-01-03 02:00:00
50      1            3            51 2018-01-03 03:00:00
51      1            4            52 2018-01-03 04:00:00
52      1            5            53 2018-01-03 05:00:00
53      1            6            54 2018-01-03 06:00:00
54      1            7            55 2018-01-03 07:00:00
55      1            8            56 2018-01-03 08:00:00
56      1            9            57 2018-01-03 09:00:00
57      1           10            58 2018-01-03 10:00:00
58      1           11            59 2018-01-03 11:00:00
59      1           12            60 2018-01-03 12:00:00
60      1           13            61 2018-01-03 13:00:00