I want to change the time interval of my rain data. The current format is the sum of rain for each 1-hour interval.
print(rain)
date time rain_gauge_value
0 2020-08-07 00:00:00 0.0
1 2020-08-07 01:00:00 0.0
2 2020-08-07 02:00:00 0.0
3 2020-08-07 03:00:00 0.0
4 2020-08-07 04:00:00 0.0
... ... ... ...
2875 2020-12-04 19:00:00 0.9
2876 2020-12-04 20:00:00 1.3
2877 2020-12-04 21:00:00 0.7
2878 2020-12-04 22:00:00 0.0
2879 2020-12-04 23:00:00 0.0
How can I change the current format to 5-minute intervals?
Example:
date time rain_gauge_value
1 2020-10-09 00:00:00 6.0
magic.code()
date time rain_gauge_value
1 2020-10-08 23:05:00 0.5
2 2020-10-08 23:10:00 0.5
3 2020-10-08 23:15:00 0.5
4 2020-10-08 23:20:00 0.5
5 2020-10-08 23:25:00 0.5
6 2020-10-08 23:30:00 0.5
7 2020-10-08 23:35:00 0.5
8 2020-10-08 23:40:00 0.5
9 2020-10-08 23:45:00 0.5
10 2020-10-08 23:50:00 0.5
11 2020-10-08 23:55:00 0.5
12 2020-10-09 00:00:00 0.5
CodePudding user response:
Try with resample
:
df["datetime"] = pd.to_datetime(df["date"] df["time"],format="%Y-%m-%d%H:%M:%S")
#append the previous 55 minutes datetime value to get the required starting point
df = df.append({"datetime": df["datetime"].min()-pd.DateOffset(minutes=55)},ignore_index=True)
#resample and backfill with the hourly value/12
output = df.resample("5min", on="datetime")[["rain_gauge_value"]].last().div(12).bfill().reset_index()
#re-format to match original columns
output["date"] = output["datetime"].dt.date
output["time"] = output["datetime"].dt.time
output = output.drop("datetime", axis=1)[["date", "time", "rain_gauge_value"]]
>>> output
date time rain_gauge_value
0 2020-10-08 23:05:00 0.5
1 2020-10-08 23:10:00 0.5
2 2020-10-08 23:15:00 0.5
3 2020-10-08 23:20:00 0.5
4 2020-10-08 23:25:00 0.5
5 2020-10-08 23:30:00 0.5
6 2020-10-08 23:35:00 0.5
7 2020-10-08 23:40:00 0.5
8 2020-10-08 23:45:00 0.5
9 2020-10-08 23:50:00 0.5
10 2020-10-08 23:55:00 0.5
11 2020-10-09 00:00:00 0.5