0

I have a pandas data series with cumulative daily returns for a series:

Date CumReturn 3/31/2017 1 4/3/2017 .99 4/4/2017 .992 ... ... 4/28/2017 1.012 5/1/2017 1.011 ... ... 5/31/2017 1.022 ... ... 6/30/2017 1.033 ... ...

I want only the month-end values. 

Date CumReturn 4/28/2017 1.012 5/31/2017 1.022 6/30/2017 1.033

Because I want only the month-end values, resampling doesn't work as it aggregates the interim values.

What is the easiest way to get only the month end values as they appear in the original dataframe?

Improve this question
7
  • Can you use pandas.tseries.offsets.MonthEnd? Commented Jan 5, 2018 at 21:17
  • How would I do that? I'm a little shaky on timeseries manipulation. Commented Jan 5, 2018 at 21:18
  • It's pretty well documented here: pandas.pydata.org/pandas-docs/stable/timeseries.html Something like this: from pandas.tseries.offsets import MonthEnd df['EndOfMonth'] = pd.to_datetime(df['Date'], format="%m/%d/%Y") + MonthEnd(1) where MonthEnd(1) specifies increment one to the next date that's a month end Commented Jan 5, 2018 at 21:27
  • Thanks for the reference to documentation. I did have a look at that before posting question but it didn't seem to answer my question. If I understand your comment correctly, I would derive a list or series of month-end values using the offsets and then use that to get a subset of the dataframe? Commented Jan 5, 2018 at 21:28
  • 1
    Doing the above will get you EndOfMonth for each Date, so you will need to subset to only keep records for Date=EndOfMonth Commented Jan 5, 2018 at 21:30

3 Answers 3

Reset to default
3

Use the is_month_end component of the .dt date accessor:

# Ensure the date column is a Timestamp df['Date'] = pd.to_datetime(df['Date']) # Filter to end of the month only df = df[df['Date'].dt.is_month_end]

Applying this to the data you provided:

 Date CumReturn 0 2017-03-31 1.000 5 2017-05-31 1.022 6 2017-06-30 1.033

EDIT

To get business month end, compare using BMonthEnd(0):

from pandas.tseries.offsets import BMonthEnd # Ensure the date column is a Timestamp df['Date'] = pd.to_datetime(df['Date']) # Filter to end of the month only df = df[df['Date'] == df['Date'] + BMonthEnd(0)]

Applying this to the data you provided:

 Date CumReturn 0 2017-03-31 1.000 3 2017-04-28 1.012 5 2017-05-31 1.022 6 2017-06-30 1.033
Improve this answer
Sign up to request clarification or add additional context in comments.

5 Comments

Thanks for this. Can you provide a document reference? I didn't come across that in my searches.
@Windstorm1981: see the links in my answer. is_month_end won't work if you want business month end though.
Yes that might be problematic. What I really need is some way to subset the dataframe taking the max date per month. That is because this is business data and some month-end dates fall on the weekends. I can subset the dataframe by month and loop through to get the max but it seems like there should be an easier way. I just saw the second answer (below). Though a bit less elegant it might be a better solution to my particular problem.
I added a solution to get business month end. If all you really care about is the max date per month, regardless of if it's a month end, business month end, or neither, use @Evan's solution. Please formulate your questions more precisely in the future.
This is really nice. I will delete my answer; pandas' behavior is worth noting but the solution is not correct.
2 
df.sort_values('Date').groupby([df.Date.dt.year,df.Date.dt.month]).last() Out[197]: Date CumReturn Date Date 2017 3 2017-03-31 1.000 4 2017-04-28 1.012 5 2017-05-31 1.022 6 2017-06-30 1.033
Improve this answer

1 Comment

This is really nice. I will delete my answer; pandas' behavior is worth noting but the solution is not correct.
1

Assuming that the dataframe is already sorted by 'Date' and that the values in that column are Pandas timestamps, you can convert them to YYYY-mm string values for grouping and take the last value:

df.groupby(df['Date'].dt.strftime('%Y-%m'))['CumReturn'].last() # Example output: # 2017-01 0.127002 # 2017-02 0.046894 # 2017-03 0.005560 # 2017-04 0.150368
Improve this answer

Comments

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.