How to get odd hours to even hours in pandas dataframe?-CodePudding

I have such a dataframe with "normal" steps of two hours between the timestamps. But sometimes there are unfortunately gaps within my data. Because of that I would like to round timestamps with odd hours (01:00, 03:00 etc.) to even hours (02:00, 04:00 etc.). Time is my index coolumn.

My dataframe looks like this:

Time    Values      
2021-10-24 22:00:00 2
2021-10-25 00:00:00 4
2021-10-25 02:00:00 78
2021-10-25 05:00:00 90
2021-10-25 07:00:00 1

How can I get a dataframe like this?

Time    Values      
2021-10-24 22:00:00 2
2021-10-25 00:00:00 4
2021-10-25 02:00:00 78
2021-10-25 06:00:00 90
2021-10-25 08:00:00 1

CodePudding user response：

Use DateTimeIndex.floor or DateTimeIndex.ceil with a frequency string 2H depending if you want to down or upsample.

df.index = df.index.ceil('2H')
>>> df
                     Values
Time                       
2021-10-24 22:00:00       2
2021-10-25 00:00:00       4
2021-10-25 02:00:00      78
2021-10-25 06:00:00      90
2021-10-25 08:00:00       1

CodePudding user response：

If "Time" is a column (and not the index), you can use dt.ceil:

df["Time"] = df["Time"].dt.ceil("2H")

>>> df
                 Time  Values
0 2021-10-24 22:00:00       2
1 2021-10-25 00:00:00       4
2 2021-10-25 02:00:00      78
3 2021-10-25 06:00:00      90
4 2021-10-25 08:00:00       2

Alternatively, if you want to ensure that the data contains every 2-hour interval, you could resample:

df = df.resample("2H", on="Time", closed="right").sum()

>>> df
                     Values
Time                       
2021-10-24 22:00:00       2
2021-10-25 00:00:00       4
2021-10-25 02:00:00      78
2021-10-25 04:00:00       0
2021-10-25 06:00:00      90
2021-10-25 08:00:00       2