Python: Integrate daily list into df with hourly index-CodePudding

I have daily weather data:

           rain (mm)
date
01/01/2022   0.0
02/01/2022   0.5
03/01/2022   2.0
...

And I have another table (df) broken down by hour

                 value
datetime
01/01/2022 01:00       x
01/01/2022 02:00       x
01/01/2022 03:00       x
...

And I want to join them like this:

                 value   rain
datetime
01/01/2022 01:00       x    0.0
01/01/2022 02:00       x    0.0
01/01/2022 03:00       x    0.0
...
02/01/2022 01:00       x    0.5
02/01/2022 02:00       x    0.5
02/01/2022 03:00       x    0.5
...
03/01/2022 01:00       x    2.0
03/01/2022 02:00       x    2.0
03/01/2022 03:00       x    2.0
...

(nb: all dates are in d%/m%/Y% format, and all dates are the index of their respective df)

I'm sure there is a straight-forward solution, but I can't find it... Thanks in advance for any help!

CodePudding user response：

Under assumption that the 1st dataframe is named 'weather' and the 2nd named 'df'.

Let's try the following code:

df['rain']=[weather['rain (mm)'][i] for i in df.index]

CodePudding user response：

You probably want to resample weather then join df:

weather = weather.resample("H").ffill()
df_out = weather.join(df)

This will keep the resampled index of weather you might want to keep df index or the intersection, or all indexes instead. Take a look at the doc and kwarg how.

Output (default how="left"):

                     rain (mm) value
date                                
2022-01-01 00:00:00        0.0   NaN
2022-01-01 01:00:00        0.0     x
2022-01-01 02:00:00        0.0     x
2022-01-01 03:00:00        0.0     x
2022-01-01 04:00:00        0.0   NaN
...                        ...   ...
2022-02-28 20:00:00        0.5   NaN
2022-02-28 21:00:00        0.5   NaN
2022-02-28 22:00:00        0.5   NaN
2022-02-28 23:00:00        0.5   NaN
2022-03-01 00:00:00        2.0   NaN