Home > Mobile >  Change pandas data frame from 1 hour to 5 minute intervals
Change pandas data frame from 1 hour to 5 minute intervals

Time:09-23

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
  • Related