how to fill a time series dataframe until end of month

user_13390380 0
Viewed 856

I have a dataframe where the index is a date (daily, skipping weekends). This data are downloaded from an API for further financial analysis however the analysis/code only runs when the last day in the date index is an end of month, if not I would like the code to find the end of month and fill the dataframe from the todays date to the end of month date by using the last available value of each column.

so I have:

     Date          Value
      ...         ...
  2020-06-23       7
  2020-06-24       7.1
  2020-06-25       7
  2020-06-26       7.3 (assume today)

what I want is:

     Date          Value
      ...         ...
  2020-06-23       7
  2020-06-24       7.1
  2020-06-25       7
  2020-06-26       7.3 
  2020-06-29       7.3 
  2020-06-30       7.3

skipping the weekends. Thanks. Luigi

python-datetime pandas time-series dataframe python-3.x
1 Answers

Use DataFrame.reindex by maximum datetime converted to end of month date and then remove weekends by boolean indexing with DatetimeIndex.weekday:

df['Date'] = pd.to_datetime(df['Date'])
df = df.set_index('Date')

idx = pd.date_range(df.index.min(), df.index.max().to_period('M').to_timestamp('M'))
df = df.reindex(idx, method='ffill')
df = df[df.index.weekday < 5]
print (df)
            Value
2020-06-23    7.0
2020-06-24    7.1
2020-06-25    7.0
2020-06-26    7.3
2020-06-29    7.3
2020-06-30    7.3
user_29010020
Related