How To Exclude Date In Pandas Dataframe If Not "end Of Month"

I have the following dataset: import datetime import pandas as pd df = pd.DataFrame({'PORTFOLIO': ['A', 'A', 'A', 'A','A', 'A', 'A', 'A','A', 'A','A', 'A', 'A', 'A'],

Solution 1:

This can be done in a one-liner: use pandas.Series.dt.is_month_end

df[pd.to_datetime(df["DATE"]).dt.is_month_end]

will give you your result.

Solution 2:

You can use pandas.tseries.offsets.MonthEnd in order to compare the current dates with the end of month dates, and perform a boolean indexation on the dataframe to keep only those that satisfy the condition:

frompandas.tseries.offsetsimportMonthEnddf.DATE=pd.to_datetime(df.DATE)df[df.DATE==df.DATE+MonthEnd(0)]PORTFOLIODATEIRR0A2018-02-28  0.71A2018-03-31  0.82A2018-04-30  0.93A2018-05-31  0.44A2018-06-30  0.25A2018-07-31  0.36A2018-08-31  0.47A2018-09-30  0.98A2018-10-31  0.79A2018-11-30  0.810A2018-12-31  0.911A2019-01-31  0.412A2019-02-28  0.7

Solution 3:

I am putting this to expand on @Christian Sloper's answer. I find it easier to reference, if the answer is self contained and I think it will help others.

I created a new column called MonthEnd and used a filter to get only those that are not month end.

import datetime
import pandas as pd

df = pd.DataFrame({'PORTFOLIO': ['A', 'A', 'A', 'A','A', 'A', 'A', 'A','A', 'A','A', 'A', 'A', 'A'],
               'DATE': ['28-02-2018','31-03-2018','30-04-2018','31-05-2018','30-06-2018','31-07-2018','31-08-2018',
                        '30-09-2018','31-10-2018','30-11-2018','31-12-2018','31-01-2019','28-02-2019','05-03-2019'],
               'IRR': [.7, .8, .9, .4, .2, .3, .4, .9, .7, .8, .9, .4,.7, .8],
               })
#new column called MonthEnd df['MonthEnd'] =  pd.to_datetime(df['DATE']).dt.is_month_end
#filter to get only those that are not month enddf[~df["MonthEnd"]]

dataframe:

DATE    IRR PORTFOLIO   MonthEnd
028-02-20180.7A   True
131-03-20180.8A   True
230-04-20180.9A   True
331-05-20180.4A   True
430-06-20180.2A   True
531-07-20180.3A   True
631-08-20180.4A   True
730-09-20180.9A   True
831-10-20180.7A   True
930-11-20180.8A   True
1031-12-20180.9A   True
1131-01-20190.4A   True
1228-02-20190.7A   True
1305-03-20190.8A   False

After Filter:

DATE    IRR PORTFOLIO   MonthEnd
1305-03-20190.8 A   False