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_datesDayNumberdelta9am10am11am12pm1pm2pm3pm4pm2016-01-12         2-323.007522.797523.005023.597524.467525.245025.160024.95752016-01-12         3-222.912523.240023.857523.947524.042524.400025.795026.76252016-01-13         3-322.912523.240023.857523.947524.042524.400025.795026.76252016-01-12         4-125.750025.910025.880025.932526.765026.402524.942524.27252016-01-13         4-225.750025.910025.880025.932526.765026.402524.942524.27252016-01-12         5022.550022.690023.270023.255023.142522.817522.292522.41752016-01-13         5-122.550022.690023.270023.255023.142522.817522.292522.41752016-01-12         6121.817522.620022.522523.267523.965025.050024.957525.11002016-01-13         6021.817522.620022.522523.267523.965025.050024.957525.11002016-01-19         6-321.817522.620022.522523.267523.965025.050024.957525.11002016-01-12         7225.460025.005024.287524.205024.285023.780023.677523.95752016-01-13         7125.460025.005024.287524.205024.285023.780023.677523.95752016-01-19         7-225.460025.005024.287524.205024.285023.780023.677523.95752016-01-12         8328.320028.592527.840028.890029.292528.422527.652527.15252016-01-13         8228.320028.592527.840028.890029.292528.422527.652527.15252016-01-19         8-128.320028.592527.840028.890029.292528.422527.652527.15252016-01-13         9326.162526.340026.072526.255026.327526.922526.572526.00752016-01-19         9026.162526.340026.072526.255026.327526.922526.572526.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.