1

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

Vishnudev Krishnadas
  • 10,679
  • 2
  • 23
  • 55
Krish1992
  • 81
  • 8

2 Answers2

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
Vishnudev Krishnadas
  • 10,679
  • 2
  • 23
  • 55
  • Why do you have to convert it to datetime when the column already has the type date? Your answer work as such without the conversion. – soumith Mar 14 '21 at 07:09
  • The OP was using string manipulation functions, it might be string column for him. @soumith – Vishnudev Krishnadas Mar 14 '21 at 07:14
  • I think OP was trying to use string manipulation and it was not working because the type is a date. – soumith Mar 14 '21 at 07:18
  • We cannot assume that @soumith. The type conversion is just to be on the safe side. If OP sees that it is already of Date type, he can ommit that line. – Vishnudev Krishnadas Mar 14 '21 at 07:19
  • Following I have tried, data.index = pd.to_datetime(data.index) data.loc[(data.index.dt.month==12)&(data.index.dt.day==31)].. But result shows Datetimeindex has no attribute dt.. I converted the index column to datetime, but I imported datetime as dt only . How can I change the code of line accordingly? – Krish1992 Mar 14 '21 at 08:24
  • Just do `data.loc[(data.index.month==12)&(data.index.day==31)]` @Krish1992 – Vishnudev Krishnadas Mar 14 '21 at 11:17
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']]
SeaBean
  • 22,547
  • 3
  • 13
  • 25
  • thanks. I achieved this. But for some years last day is 12-30 and for some it is 12-29. Can we dynamically get the values lastday of year? I tried with Yearend(). It was not working. Please let me know how to get last day of year dynamically even if it 29,30, 31?. Thanks – Krish1992 Mar 14 '21 at 09:39
  • @Krish1992 You mean last business day, right ? – SeaBean Mar 14 '21 at 09:45
  • Yes, it should be from calendar year end. Not sure whether it is last business day.. – Krish1992 Mar 14 '21 at 09:50
  • Calendar year end will always be December 31st for every year. I guess you mean last business day of a year then. It will be a completely new question then. Suggest you have a look at [this post](https://stackoverflow.com/questions/37441323/finding-last-business-day-of-a-month-in-python) – SeaBean Mar 14 '21 at 09:58
  • @Krish1992 See my edit above, which provides sample codes for dynamically get the values last business day of year. – SeaBean Mar 14 '21 at 10:54