I have a daily/weekly data series that I want to convert into monthly data such that I only retain the data for the last date of every month. I have set the index of the table as date_time function however I am still getting an error.
Code:
spread = pd.read_excel('/data/...............) #reads the excel
spread = spread.iloc[9: , 9:15]
spread.rename(columns = {'Unnamed: 9':'Date'}, inplace = True)
spread['Date'] = spread['Date'].apply(lambda x: str(x))
spread['Date'] = pd.to_datetime(spread['Date']).dt.date
spread.set_index('Date', inplace=True)
spread.columns = ['CRT M1A','STACR M1B/CAS M2', 'STACR M2', 'CRTB1', 'CRTB2']
spread = spread.replace(0,np.nan)
spread = spread[spread.index >= report_start_date]
spread = spread[spread.index <= report_end_date] #trims to give last two years of data
spread = spread.fillna(method='ffill')
spread= spread["Date"].resample('M')
spread
Error: TypeError: Only valid with DatetimeIndex, TimedeltaIndex or PeriodIndex, but got an instance of 'Index'