CODEWITHSUNDEEP
pandasdataframe
Rutkay Karabulak
Rutkay Karabulak

Reputation: 115

Converting daily data to monthly and get months last value in pandas

I have data consists of daily data that belongs to specific month and year like this enter image description here

I want to convert all daily data to monthly data and I want to get the last value of that month as a return value of that monthly data for example:

AccoutId,   Date,    Return

  1       2016-01   -4.1999  (Because this return value is last value of january 1/29/16)

  1       2016-02    0.19    (Same here last value of february 2/29/16)
and so on

I've looked some of topics about converting daily data to monthly data but the problem is that after converting daily data to monthly data, they take the mean() or sum() of that month as a return value. Conversely, I want the last return value of that month as the return value.

Upvotes: 1

Views: 1220

Answers (1)

David Erickson
David Erickson

Reputation: 16683

You can groupby AccountId and the Year-Month. Convert to datetime first and then format as Year-Month as follows: df['Date'].dt.strftime('%Y-%m'). Then just use last():

df['Date'] = pd.to_datetime(df['Date'])
df = df.groupby(['AccountId', df['Date'].dt.strftime('%Y-%m')])['Return'].last().reset_index()
df

Sample data:

In[1]:
   AccountId     Date  Return
0          1   1/7/16      15
1          1  1/29/16      10
2          1   2/1/16      25
3          1  2/15/16      20
4          1  2/28/16      30

df['Date'] = pd.to_datetime(df['Date'])
df = df.groupby(['AccountId', df['Date'].dt.strftime('%Y-%m')])['Return'].last().reset_index()
df

Out[1]: 
   AccountId     Date  Return
0          1  2016-01      10
1          1  2016-02      30

Upvotes: 1

Related Questions