Add hours to year-month-day data in pandas data frame

November 26, 2021

I have the following data frame with hourly resolution

day_ahead_DK1
Out[27]: 
      DateStamp    DK1
0    2017-01-01  20.96
1    2017-01-01  20.90
2    2017-01-01  18.13
3    2017-01-01  16.03
4    2017-01-01  16.43
        ...    ...
8756 2017-12-31  25.56
8757 2017-12-31  11.02
8758 2017-12-31   7.32
8759 2017-12-31   1.86
type(day_ahead_DK1)
Out[28]: pandas.core.frame.DataFrame

But the current column DateStamp is missing hours. How can I add hours 00:00:00, to 2017-01-01 for Index 0 so it will be 2017-01-01 00:00:00, and then 01:00:00, to 2017-01-01 for Index 1 so it will be 2017-01-01 01:00:00, and so on, so that all my days will have hours from 0 to 23. Thank you!

The expected output:

day_ahead_DK1
Out[27]: 
      DateStamp            DK1
0    2017-01-01 00:00:00  20.96
1    2017-01-01 01:00:00  20.90
2    2017-01-01 02:00:00  18.13
3    2017-01-01 03:00:00  16.03
4    2017-01-01 04:00:00  16.43
        ...    ...
8756 2017-12-31 20:00:00  25.56
8757 2017-12-31 21:00:00  11.02
8758 2017-12-31 22:00:00   7.32
8759 2017-12-31 23:00:00   1.86

>Solution :

Use GroupBy.cumcount for counter with to_timedelta for hours and add to DateStamp column:

df['DateStamp'] = pd.to_datetime(df['DateStamp'])

df['DateStamp'] += pd.to_timedelta(df.groupby('DateStamp').cumcount(), unit='H')
print (df)
               DateStamp    DK1
0    2017-01-01 00:00:00  20.96
1    2017-01-01 01:00:00  20.90
2    2017-01-01 02:00:00  18.13
3    2017-01-01 03:00:00  16.03
4    2017-01-01 04:00:00  16.43
8756 2017-12-31 00:00:00  25.56
8757 2017-12-31 01:00:00  11.02
8758 2017-12-31 02:00:00   7.32
8759 2017-12-31 03:00:00   1.86