Make a list of months and years in Pandas

Issue

I am trying to make a date_list that is a range of the months in the last year. It should be formatted ['Oct 2014', 'Nov 2014', 'Dec 2014', 'Jan 2015', 'Feb 2015',... 'Sep 2015']

Sometimes it works, and sometimes it cuts off the current month. And on the last day of the month it wasn’t working so I made this if statement.

#If it is the last day of the month, go back a year and add a day to start at beginning of the month
#Otherwise, go back a year, go to the end of the month, and add a day to start at the beginning of the month

if datetime.datetime.now().day == calendar.monthrange(date.year, date.month)[1]:
    range_min = range_max - pd.tseries.offsets.DateOffset(years=1)+ pd.tseries.offsets.DateOffset(days=1, normalize = True)
else:
    range_min = range_max - pd.tseries.offsets.DateOffset(years=1)+ pd.tseries.offsets.MonthEnd(1) + pd.tseries.offsets.DateOffset(days=1, normalize = True)

Then I take a slice of my data, which right now goes from 2014-10-01 to 2015-09-02 as expected.
Then I make the date list! This is the critical bit that I am struggling with.

# take slice with required of data
df = df[(df['recvd_dttm'] >= range_min) & 
               (df['recvd_dttm'] <= range_max)]

#Make a date list in order to get the months to plot later on           
date_list = pd.DataFrame(index=pd.date_range(start = range_min, end = datetime.datetime.now(), freq='M'))
date_list = date_list.index.to_series().apply(lambda x: datetime.datetime.strftime(x, '%b %Y')).tolist()            

It may be a problem with my range, which is this:

range_min
Out[5]: Timestamp('2014-10-01 00:00:00')

range_max
Out[6]: datetime.datetime(2015, 9, 2, 10, 53, 24, 66000)

but the first date_list command outputs this:

pd.DataFrame(index=pd.date_range(start = range_min, end = datetime.datetime.now(), freq='M'))
Out[8]: 
Empty DataFrame
Columns: []
Index: [2014-10-31 00:00:00, 2014-11-30 00:00:00, 2014-12-31 00:00:00, 2015-01-31 00:00:00, 2015-02-28 00:00:00, 2015-03-31 00:00:00, 2015-04-30 00:00:00, 2015-05-31 00:00:00, 2015-06-30 00:00:00, 2015-07-31 00:00:00, 2015-08-31 00:00:00]

Which doesn’t include 2015-09 , so my date_list cuts off September. Not sure why this is happening. Also if anyone knows of an easier way to create this list, feel free to include that!

Thanks!

Solution

You can make a list of months in the past year without using pandas.

date_list=[datetime.date.today()- dateutil.relativedelta.relativedelta(months = x) for x in range(11,-1,-1)]
month_list=[datetime.date.strftime(x,'%b %Y') for x in date_list]

If needed you can convert the above list to pandas dateframe with index as months.

month_list=pd.DataFrame(index=month_list)

Answered By – kanatti

Answer Checked By – Jay B. (AngularFixing Admin)

Leave a Reply

Your email address will not be published.