How to map values from grouped dataframe onto non-grouped dataframe by day-CodePudding

I have an ordinary dataframe with a datetimeindex. (df1) There's another dataframe with derived values grouped by day.(df2) Now I need to pour in the values from df2 to df1, so that the values are repeatedly inserted to the corresponding day

df1:

                     Open     High     Low      Close       volume
datetime                                                       
2021-07-07 00:00:00  4328.00  4328.75  4327.50  4328.75     774
2021-07-07 00:05:00  4328.75  4330.00  4328.75  4329.50     462
2021-07-07 00:10:00  4329.50  4329.50  4328.75  4329.25     344
2021-07-07 00:15:00  4329.50  4330.50  4329.50  4330.50     423
2021-07-07 00:20:00  4330.50  4330.75  4330.00  4330.50     458
...                      ...      ...      ...      ...     ...
2022-07-06 21:35:00  3871.50  3875.00  3865.50  3866.75   19667
2022-07-06 21:40:00  3866.75  3867.00  3861.00  3862.50   19962
2022-07-06 21:45:00  3862.25  3862.25  3855.00  3857.75   20707
2022-07-06 21:50:00  3857.75  3860.00  3841.00  3843.50   41929
2022-07-06 21:55:00  3843.75  3850.00  3842.00  3849.50   56018

df2:

2021-07-07    4347.50
2021-07-08    4301.25
2021-07-09    4349.25
2021-07-10        NaN
2021-07-11        NaN
2021-07-12    4372.50
2021-07-13    4379.25
2021-07-14    4384.50
2021-07-15    4360.75
2021-07-16    4365.75
2021-07-17        NaN
2021-07-18        NaN
2021-07-19    4272.00
2021-07-20    4299.50
2021-07-21    4342.00
2021-07-22    4355.25
2021-07-23    4381.25
2021-07-24        NaN
2021-07-25        NaN
2021-07-26    4407.25
2021-07-27    4404.00
.....

desired output:

                     Open     High     Low      Close       volume  newcol
datetime                                                       
2021-07-07 00:00:00  4328.00  4328.75  4327.50  4328.75     774     4347.50
2021-07-07 00:05:00  4328.75  4330.00  4328.75  4329.50     462     4347.50
2021-07-07 00:10:00  4329.50  4329.50  4328.75  4329.25     344     4347.50
2021-07-07 00:15:00  4329.50  4330.50  4329.50  4330.50     423     4347.50
2021-07-07 00:20:00  4330.50  4330.75  4330.00  4330.50     458     4347.50
...                      ...      ...      ...      ...     ...

Any ideas how to do this?

CodePudding user response：

Use DatetimeIndex.normalize for remove times, so possible mapping by another DataFrame by Index.map:

df1['newcol'] = df1.index.normalize().map(df2['col'])

#if in df2 are columns date, col
#df1['newcol'] = df1.index.normalize().map(df2.set_index('date')['col'])
print (df1)
                        Open     High      Low    Close  volume  newcol
datetime                                                               
2021-07-07 00:00:00  4328.00  4328.75  4327.50  4328.75     774  4347.5
2021-07-07 00:05:00  4328.75  4330.00  4328.75  4329.50     462  4347.5
2021-07-07 00:10:00  4329.50  4329.50  4328.75  4329.25     344  4347.5
2021-07-07 00:15:00  4329.50  4330.50  4329.50  4330.50     423  4347.5
2021-07-07 00:20:00  4330.50  4330.75  4330.00  4330.50     458  4347.5
2022-07-06 21:35:00  3871.50  3875.00  3865.50  3866.75   19667     NaN
2022-07-06 21:40:00  3866.75  3867.00  3861.00  3862.50   19962     NaN
2022-07-06 21:45:00  3862.25  3862.25  3855.00  3857.75   20707     NaN
2022-07-06 21:50:00  3857.75  3860.00  3841.00  3843.50   41929     NaN
2022-07-06 21:55:00  3843.75  3850.00  3842.00  3849.50   56018     NaN