how to merge values onto dates of a dataframe by the hour given intervals between days pandas-CodePudding

so I have constructed weights for values between specific timestamps in pandas. I want to be able to assign these weights to another dataframe that has datetime objects with values down to the hour. I essentially want to weight 2 columns (hourly data) of another frame by the timeframe they are in from the other dataframe.

weight_df

Here is the dataframe with the weights for each day

weight_df = 
    |Datetime               |w1         |w2
    |-----------------------|-----------|-----------
0   |2021-01-03 05:00:00    |0.961538   |0.038462
1   |2021-01-04 05:00:00    |0.923077   |0.076923
2   |2021-01-05 05:00:00    |0.884615   |0.115385

price_df

Here is the sales data I have where I want the weights mapped to.

price_df =
                     Price_1  Quantity_1 Price_2    Quantity_2
Datetime                
2021-01-03 18:00:00 2.630859    3127.0  2.607422    507.0
2021-01-03 19:00:00 2.634766    601.0   2.609375    218.0
2021-01-03 20:00:00 2.628906    1162.0  2.607422    369.0
2021-01-03 21:00:00 2.623047    605.0   2.601562    69.0
2021-01-03 22:00:00 2.628906    306.0   2.605469    50.0
2021-01-03 23:00:00 2.632812    496.0   2.609375    35.0
2021-01-04 00:00:00 2.634766    458.0   2.611328    59.0
2021-01-04 01:00:00 2.638672    673.0   2.613281    128.0
2021-01-04 02:00:00 2.626953    1903.0  2.603516    316.0
2021-01-04 03:00:00 2.619141    1500.0  2.597656    190.0
2021-01-04 04:00:00 2.615234    1075.0  2.593750    231.0
2021-01-04 05:00:00 2.619141    597.0   2.597656    123.0
...
2021-01-05 04:00:00 2.695312    1401.0  2.660156    289.0
2021-01-05 05:00:00 2.689453    1021.0  2.658203    211.0


# Where Datetime is the index of this dataframe.

I'm hoping to map/merge the w1 and w2 column from weight_df to any row in price_df that falls in the dates from weight_df.

ex. output:

                   Price_1    Quantity_1 Price_2  Quantity_2   w1        w2
Datetime                
2021-01-03 18:00:00 2.630859    3127.0  2.607422    507.0      0.961538  0.038462
...
2021-01-04 04:00:00 2.615234    1075.0  2.593750    231.0      0.961538  0.038462
2021-01-04 05:00:00 2.619141    597.0   2.597656    123.0      0.923077  0.076923
...
2021-01-05 04:00:00 2.695312    1401.0  2.660156    289.0      0.923077  0.076923
2021-01-05 05:00:00 2.689453    1021.0  2.658203    211.0      0.884615  0.115385

So far I've tried using .merge and making the left_on the day, hour and the right_on the day, but that just isn't working.

CodePudding user response：

Use merge_asof:

df = pd.merge_asof(price_df.reset_index(),weight_df, on='Datetime').set_index('Datetime')

Or:

df = pd.merge_asof(price_df, weight_df, on='Datetime').set_index('Datetime')

Or:

df = pd.merge_asof(price_df, weight_df.set_index('Datetime'),
                   left_index=True, right_index=True)

print (df)
                      Price_1  Quantity_1   Price_2  Quantity_2        w1  \
Datetime                                                                    
2021-01-03 18:00:00  2.630859      3127.0  2.607422       507.0  0.961538   
2021-01-03 19:00:00  2.634766       601.0  2.609375       218.0  0.961538   
2021-01-03 20:00:00  2.628906      1162.0  2.607422       369.0  0.961538   
2021-01-03 21:00:00  2.623047       605.0  2.601562        69.0  0.961538   
2021-01-03 22:00:00  2.628906       306.0  2.605469        50.0  0.961538   
2021-01-03 23:00:00  2.632812       496.0  2.609375        35.0  0.961538   
2021-01-04 00:00:00  2.634766       458.0  2.611328        59.0  0.961538   
2021-01-04 01:00:00  2.638672       673.0  2.613281       128.0  0.961538   
2021-01-04 02:00:00  2.626953      1903.0  2.603516       316.0  0.961538   
2021-01-04 03:00:00  2.619141      1500.0  2.597656       190.0  0.961538   
2021-01-04 04:00:00  2.615234      1075.0  2.593750       231.0  0.961538   
2021-01-04 05:00:00  2.619141       597.0  2.597656       123.0  0.923077   
2021-01-05 04:00:00  2.695312      1401.0  2.660156       289.0  0.923077   
2021-01-05 05:00:00  2.689453      1021.0  2.658203       211.0  0.884615   

                           w2  
Datetime                       
2021-01-03 18:00:00  0.038462  
2021-01-03 19:00:00  0.038462  
2021-01-03 20:00:00  0.038462  
2021-01-03 21:00:00  0.038462  
2021-01-03 22:00:00  0.038462  
2021-01-03 23:00:00  0.038462  
2021-01-04 00:00:00  0.038462  
2021-01-04 01:00:00  0.038462  
2021-01-04 02:00:00  0.038462  
2021-01-04 03:00:00  0.038462  
2021-01-04 04:00:00  0.038462  
2021-01-04 05:00:00  0.076923  
2021-01-05 04:00:00  0.076923  
2021-01-05 05:00:00  0.115385