Issue with datetime formatting

Question

I am having an issue with the datetime format of a set of data. The issue is due to the hour of day ranging from 1-24, with the 24th hour set to the wrong day (more specifically, the previous day). I have a sample of the data below,

1/1/2019,14:00,0.2,0.1,0.0,0.2,3.0,36.7,3,153
1/1/2019,15:00,0.2,0.6,0.2,0.4,3.9,36.7,1,199
1/1/2019,16:00,1.8,2.4,0.8,1.6,1.1,33.0,0,307
1/1/2019,17:00,3.0,3.2,0.6,2.6,6.0,32.8,1,310
1/1/2019,18:00,1.6,2.2,0.5,1.7,7.9,33.1,4,293
1/1/2019,19:00,1.7,1.1,0.6,0.6,5.9,35.0,5,262
1/1/2019,20:00,1.0,0.5,0.2,0.2,2.9,32.6,5,201
1/1/2019,21:00,0.6,0.3,0.0,0.4,2.1,31.8,6,182
1/1/2019,22:00,0.4,0.3,0.0,0.4,5.1,31.4,6,187
1/1/2019,23:00,0.8,0.6,0.3,0.3,9.9,30.2,5,227
1/1/2019,24:00,1.0,0.7,0.3,0.4,6.9,27.9,4,225  --- Here the date should be 1/2/2019
1/2/2019,01:00,1.3,0.9,0.5,0.4,4.0,26.9,6,236
1/2/2019,02:00,0.4,0.4,0.2,0.2,5.0,27.3,6,168
1/2/2019,03:00,0.7,0.5,0.3,0.3,6.9,30.2,4,219
1/2/2019,04:00,1.3,0.8,0.5,0.3,5.9,32.3,4,242
1/2/2019,05:00,0.7,0.2,0.0,0.2,3.0,33.8,4,177
1/2/2019,06:00,0.5,0.2,0.2,0.1,5.1,36.1,4,195
1/2/2019,07:00,0.6,0.3,0.2,0.2,9.9,38.0,4,200
1/2/2019,08:00,0.5,0.6,0.4,0.3,6.8,38.9,4,179
1/2/2019,09:00,0.5,0.2,0.0,0.2,3.0,39.0,4,193
1/2/2019,10:00,0.3,0.3,0.2,0.1,4.0,38.7,5,198
1/2/2019,11:00,0.3,0.3,0.2,0.0,4.9,38.4,5,170
1/2/2019,12:00,0.6,0.3,0.3,0.0,2.0,38.4,4,172
1/2/2019,13:00,0.2,0.3,0.2,0.0,2.0,38.8,4,154
1/2/2019,14:00,0.3,0.1,0.0,0.2,1.9,39.3,4,145

This is a fairly large set of data which I need to make a time series plot of, and as such I need to find a way to fix this formatting issue. I was attempting to iterate through the rows and in a pandas dataframe to fix problematic rows, but this does not provide any results. Thank you for any help beforehand.

Answer 1

You can convert date to datetimes by to_datetime and then add time column converted to timedeltas by to_timedelta:

df['date'] = pd.to_datetime(df['date']) + pd.to_timedelta(df['time'] + ':00')

Or if need remove time column also:

print (df)
        date   time    a    b    c    d    e     f  g    h
0   1/1/2019  14:00  0.2  0.1  0.0  0.2  3.0  36.7  3  153
1   1/1/2019  15:00  0.2  0.6  0.2  0.4  3.9  36.7  1  199
2   1/1/2019  16:00  1.8  2.4  0.8  1.6  1.1  33.0  0  307
3   1/1/2019  17:00  3.0  3.2  0.6  2.6  6.0  32.8  1  310
4   1/1/2019  18:00  1.6  2.2  0.5  1.7  7.9  33.1  4  293
5   1/1/2019  19:00  1.7  1.1  0.6  0.6  5.9  35.0  5  262
6   1/1/2019  20:00  1.0  0.5  0.2  0.2  2.9  32.6  5  201
7   1/1/2019  21:00  0.6  0.3  0.0  0.4  2.1  31.8  6  182
8   1/1/2019  22:00  0.4  0.3  0.0  0.4  5.1  31.4  6  187
9   1/1/2019  23:00  0.8  0.6  0.3  0.3  9.9  30.2  5  227
10  1/1/2019  24:00  1.0  0.7  0.3  0.4  6.9  27.9  4  225
11  1/2/2019  01:00  1.3  0.9  0.5  0.4  4.0  26.9  6  236
12  1/2/2019  02:00  0.4  0.4  0.2  0.2  5.0  27.3  6  168
13  1/2/2019  03:00  0.7  0.5  0.3  0.3  6.9  30.2  4  219
14  1/2/2019  04:00  1.3  0.8  0.5  0.3  5.9  32.3  4  242
15  1/2/2019  05:00  0.7  0.2  0.0  0.2  3.0  33.8  4  177
16  1/2/2019  06:00  0.5  0.2  0.2  0.1  5.1  36.1  4  195
17  1/2/2019  07:00  0.6  0.3  0.2  0.2  9.9  38.0  4  200
18  1/2/2019  08:00  0.5  0.6  0.4  0.3  6.8  38.9  4  179
19  1/2/2019  09:00  0.5  0.2  0.0  0.2  3.0  39.0  4  193
20  1/2/2019  10:00  0.3  0.3  0.2  0.1  4.0  38.7  5  198
21  1/2/2019  11:00  0.3  0.3  0.2  0.0  4.9  38.4  5  170
22  1/2/2019  12:00  0.6  0.3  0.3  0.0  2.0  38.4  4  172
23  1/2/2019  13:00  0.2  0.3  0.2  0.0  2.0  38.8  4  154
24  1/2/2019  14:00  0.3  0.1  0.0  0.2  1.9  39.3  4  145

---

df['date'] = pd.to_datetime(df['date']) + pd.to_timedelta(df.pop('time') + ':00')
print (df)
                  date    a    b    c    d    e     f  g    h
0  2019-01-01 14:00:00  0.2  0.1  0.0  0.2  3.0  36.7  3  153
1  2019-01-01 15:00:00  0.2  0.6  0.2  0.4  3.9  36.7  1  199
2  2019-01-01 16:00:00  1.8  2.4  0.8  1.6  1.1  33.0  0  307
3  2019-01-01 17:00:00  3.0  3.2  0.6  2.6  6.0  32.8  1  310
4  2019-01-01 18:00:00  1.6  2.2  0.5  1.7  7.9  33.1  4  293
5  2019-01-01 19:00:00  1.7  1.1  0.6  0.6  5.9  35.0  5  262
6  2019-01-01 20:00:00  1.0  0.5  0.2  0.2  2.9  32.6  5  201
7  2019-01-01 21:00:00  0.6  0.3  0.0  0.4  2.1  31.8  6  182
8  2019-01-01 22:00:00  0.4  0.3  0.0  0.4  5.1  31.4  6  187
9  2019-01-01 23:00:00  0.8  0.6  0.3  0.3  9.9  30.2  5  227
10 2019-01-02 00:00:00  1.0  0.7  0.3  0.4  6.9  27.9  4  225
11 2019-01-02 01:00:00  1.3  0.9  0.5  0.4  4.0  26.9  6  236
12 2019-01-02 02:00:00  0.4  0.4  0.2  0.2  5.0  27.3  6  168
13 2019-01-02 03:00:00  0.7  0.5  0.3  0.3  6.9  30.2  4  219
14 2019-01-02 04:00:00  1.3  0.8  0.5  0.3  5.9  32.3  4  242
15 2019-01-02 05:00:00  0.7  0.2  0.0  0.2  3.0  33.8  4  177
16 2019-01-02 06:00:00  0.5  0.2  0.2  0.1  5.1  36.1  4  195
17 2019-01-02 07:00:00  0.6  0.3  0.2  0.2  9.9  38.0  4  200
18 2019-01-02 08:00:00  0.5  0.6  0.4  0.3  6.8  38.9  4  179
19 2019-01-02 09:00:00  0.5  0.2  0.0  0.2  3.0  39.0  4  193
20 2019-01-02 10:00:00  0.3  0.3  0.2  0.1  4.0  38.7  5  198
21 2019-01-02 11:00:00  0.3  0.3  0.2  0.0  4.9  38.4  5  170
22 2019-01-02 12:00:00  0.6  0.3  0.3  0.0  2.0  38.4  4  172
23 2019-01-02 13:00:00  0.2  0.3  0.2  0.0  2.0  38.8  4  154
24 2019-01-02 14:00:00  0.3  0.1  0.0  0.2  1.9  39.3  4  145