Filter date index based on regex in pandas

Question

I have date column with price index like below,

Date	Price
2010-01-01	23
2010-12-31	25
2013-02-03	24
2013-12-31	28
2016-03-04	27
2016-12-31	28
2018-01-01	31
2020-01-01	30
2020-12-31	20

I want to extract dates which ends with 12-31.How can I do that?

I tried with data.index.loc['*-12-31] it is not working.

Since this is date str.contains or startswith or endswith is not working.

Is there any way to do this?

Thanks

Answer 1

If our goal is to extract relevant dates from the DataFrame without modification to the contents of DataFrame, we can do it this way:

Case 1: 'Date' column is already in string format, use:

df[df['Date'].str.endswith('12-31')]

Case 2: 'Date' column is already in datetime format, use:

df[df.assign(Date=df['Date'].astype(str))['Date'].str.endswith('12-31')]

Both give you the desired extraction output according to what's the current data type it is, without modification to the DataFrame.

Edit

If you want to automate the extraction of entries falling in business year ends, you can try the following code. This is for your reference and you may want to further fine-tune it to clean up some intermediate columns.

df['Date1'] = pd.to_datetime(df['Date'])    
df['BYearEnd'] = pd.to_datetime(df['Date1'].dt.year.astype(str) + '-12-01') + pd.offsets.BMonthEnd(1)

Here, we created temporary columns Date1 and BYearEnd with values corresponding to the column Date Column BYearEnd contains the business year end dates for the respective dates in column Date.

Then we can extract the relevant dates with the following code:

df[df['Date1'] == df['BYearEnd']] 

Answer 2

Convert Date column to datetime data type

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

Filter by month and day

df.loc[(df.Date.dt.month == 12) & (df.Date.dt.day == 31)]

Output

        Date  Price
1 2010-12-31     25
3 2013-12-31     28
5 2016-12-31     28
8 2020-12-31     20