How To Get Elements From A Dataframe Given Dates In Another Dataframe

I have a list of dates in a dataframe, and another dataframe containing percentage changes throughout a day. Sample dataframe with dates (df_date): df_test = pd.DataFrame({ 'Specif

Solution 1:

Your data looks like financial market data. Financial markets are not open every business days (there are trading floor holidays) so you cannot use BDay. Instead, it's better to label every day in your df_percent sequentially, so that Day 0 is 2016-01-05, day 1 is 2016-01-06, etc. This way you can easily reference n trading days before or after.

# Assign a sequential number to each trading day
df_melt_test_percent = df_melt_test_percent.sort_index().assign(DayNumber=lambda x: range(len(x)))

# Find the indices of the FOMC_dates
tmp = pd.merge(
    df_FOMC_dates, df_melt_test_percent[['DayNumber']],
    left_on='FOMC_dates', right_index=True
)

# For each row, get the FOMC_dates ± 3 days
tmp['delta'] = tmp.apply(lambda _: range(-3, 4), axis=1)

tmp = tmp.explode('delta')
tmp['DayNumber'] += tmp['delta']

# Assemble the result
result = pd.merge(tmp, df_melt_test_percent, on='DayNumber')

Result:

FOMC_dates DayNumber delta     9am    10am    11am    12pm     1pm     2pm     3pm     4pm
2016-01-12         2    -3 23.0075 22.7975 23.0050 23.5975 24.4675 25.2450 25.1600 24.9575
2016-01-12         3    -2 22.9125 23.2400 23.8575 23.9475 24.0425 24.4000 25.7950 26.7625
2016-01-13         3    -3 22.9125 23.2400 23.8575 23.9475 24.0425 24.4000 25.7950 26.7625
2016-01-12         4    -1 25.7500 25.9100 25.8800 25.9325 26.7650 26.4025 24.9425 24.2725
2016-01-13         4    -2 25.7500 25.9100 25.8800 25.9325 26.7650 26.4025 24.9425 24.2725
2016-01-12         5     0 22.5500 22.6900 23.2700 23.2550 23.1425 22.8175 22.2925 22.4175
2016-01-13         5    -1 22.5500 22.6900 23.2700 23.2550 23.1425 22.8175 22.2925 22.4175
2016-01-12         6     1 21.8175 22.6200 22.5225 23.2675 23.9650 25.0500 24.9575 25.1100
2016-01-13         6     0 21.8175 22.6200 22.5225 23.2675 23.9650 25.0500 24.9575 25.1100
2016-01-19         6    -3 21.8175 22.6200 22.5225 23.2675 23.9650 25.0500 24.9575 25.1100
2016-01-12         7     2 25.4600 25.0050 24.2875 24.2050 24.2850 23.7800 23.6775 23.9575
2016-01-13         7     1 25.4600 25.0050 24.2875 24.2050 24.2850 23.7800 23.6775 23.9575
2016-01-19         7    -2 25.4600 25.0050 24.2875 24.2050 24.2850 23.7800 23.6775 23.9575
2016-01-12         8     3 28.3200 28.5925 27.8400 28.8900 29.2925 28.4225 27.6525 27.1525
2016-01-13         8     2 28.3200 28.5925 27.8400 28.8900 29.2925 28.4225 27.6525 27.1525
2016-01-19         8    -1 28.3200 28.5925 27.8400 28.8900 29.2925 28.4225 27.6525 27.1525
2016-01-13         9     3 26.1625 26.3400 26.0725 26.2550 26.3275 26.9225 26.5725 26.0075
2016-01-19         9     0 26.1625 26.3400 26.0725 26.2550 26.3275 26.9225 26.5725 26.0075

Rows with delta = 0 are your original FOMC_dates. You can drop the columns you don't want and pivot it to your preferred shape.