Python Pandas : Return The Consecutive Missing Weekdays Dates And Assign Rate Next To Missing Dates In A Dataframe

Dates rates 7/26/2019 1.04 7/30/2019 1.0116 7/31/2019 1.005 8/1/2019 1.035 8/2/2019 1.01 8/6/2019 0.9886 8/12/2019 0.965 df = df.merge( pd.DataFrame({'D

Solution 1:

you can use reindex with bdate_range to create all the missing values in rates for business days only:

new_df=df.set_index('Dates')\.reindex(pd.bdate_range(df.Dates.min(),df.Dates.max(),name='Dates'),method='bfill')\.reset_index()print(new_df)Datesrates02019-07-26  1.040012019-07-29  1.011622019-07-30  1.011632019-07-31  1.005042019-08-01  1.035052019-08-02  1.010062019-08-05  0.988672019-08-06  0.988682019-08-07  0.965092019-08-08  0.9650102019-08-09  0.9650112019-08-12  0.9650

Solution 2:

You could create a Series of all business days then outermerge and bfill the missing values. This will retain any non-business days in your initial DataFrame (if any) and will also use their values in the filling.

import pandas as pd
#df['Dates'] = pd.to_datetime(df['Dates'])

s = pd.Series(pd.date_range(df['Dates'].min(), df['Dates'].max(), freq='D'),
              name='Dates')
s = s[s.dt.dayofweek.lt(5)]

df = df.merge(s, how='outer').sort_values('Dates').bfill()

---

Datesrates02019-07-26  1.040072019-07-29  1.011612019-07-30  1.011622019-07-31  1.005032019-08-01  1.035042019-08-02  1.010082019-08-05  0.988652019-08-06  0.988692019-08-07  0.9650102019-08-08  0.9650112019-08-09  0.965062019-08-12  0.9650