How do I sum an amount field for the last week of each month in python?

user_3487190 0
Viewed 244

I have a dataframe that has an amount field and a date field. The date field is not the index (I am fine with a solution that would require setting it as the index though). I would like to ignore the time portion. The goal is to get a sum of the amount field grouped by the end of the month for each year so there should be one aggregated amount for each month based on the last 7 (would like to be able to adjust this number) days.

#code to create random data with date and amount field.
import pandas as pd
import numpy as np
from datetime import datetime, timedelta

date_today = datetime.now()
start_date = '01-01-2019'
days = pd.date_range(start_date, date_today, freq='D')

np.random.seed(seed=1111)
data = np.random.randint(1, high=100, size=len(days))
df = pd.DataFrame({'date': days, 'amount': data})
print(df)

I tried hard coding all the the last 7 or 8 days for each month before going ahead with a groupby. But it does not work. 

last_week = (
     '2019-01-31','2019-01-30','2019-01-29','2019-01-28','2019-01-27','2019-01-26','2019-01-25',
'2019-02-28','2019-02-27','2019-02-26','2019-02-25','2019-02-24','2019-02-23','2019-02-22',
'2019-03-31','2019-03-30','2019-03-29','2019-03-28','2019-03-27','2019-03-26','2019-03-25',
'2019-04-30','2019-04-29','2019-04-28','2019-04-27','2019-04-26','2019-04-25','2019-04-24',
'2019-05-31','2019-05-30','2019-05-29','2019-05-28','2019-05-27','2019-05-26','2019-05-25',
'2019-06-30','2019-06-29','2019-06-28','2019-06-27','2019-06-26','2019-06-25','2019-06-24',
'2019-07-31','2019-07-30','2019-07-29','2019-07-28','2019-07-27','2019-07-26','2019-07-25',
'2019-08-31','2019-08-30','2019-08-29','2019-08-28','2019-08-27','2019-08-26','2019-08-25',
'2019-09-30','2019-09-29','2019-09-28','2019-09-27','2019-09-26','2019-09-25','2019-09-24',
'2019-10-31','2019-10-30','2019-10-29','2019-10-28','2019-10-27','2019-10-26','2019-10-25',
'2019-11-30','2019-11-29','2019-11-28','2019-11-27','2019-11-26','2019-11-25','2019-11-24',
'2019-12-31','2019-12-30','2019-12-29','2019-12-28','2019-12-27','2019-12-26','2019-12-25',
'2020-01-31','2020-01-30','2020-01-29','2020-01-28','2020-01-27','2020-01-26','2020-01-25'
)
mask = (df_GL_FSLI_date_regIndex['New_Entry_Date'] == last_week)
last_week_jes = df_GL_FSLI_date.loc[mask]

# Desired output
End_Of_Jan_2019 1453.45
End_Of_Feb_2019 347543.23
...

Is there an elegant way to do this whether it is keeping the date as a field or an index?

pandas datetime python
2 Answers

Instead of manually specifying the dates you can use pd.offsets.MonthEnd, together with a subtraction to get the number of days from the end of the month. Then susbet and group

s = ((df['date'] + pd.offsets.MonthEnd(0)) - df['date']).dt.days

Ndays = 7  # This many from the end
df[s.lt(Ndays)].groupby(df['date'].dt.to_period('M')).sum()

         amount
date           
2019-01     247
2019-02     420
2019-03     223
2019-04     387
2019-05     382
2019-06     240
2019-07     410
2019-08     365
2019-09     344
2019-10     444
2019-11     274
2019-12     339
2020-01     391
2020-02     131
user_43333590

You can do to_period and then groupby().sum() as usual:

(df.assign(month=df['date'].dt.to_period('M'))
   .groupby('month').tail(7)                   # change 7 here
   .groupby('month',as_index=False)
    ['amount'].sum()
)

Output:

      month  amount
0   2019-01     247
1   2019-02     420
2   2019-03     223
3   2019-04     387
4   2019-05     382
5   2019-06     240
6   2019-07     410
7   2019-08     365
8   2019-09     344
9   2019-10     444
10  2019-11     274
11  2019-12     339
12  2020-01     391
13  2020-02     238

Note if you want month to appear as End_of_Jan_2019 change .to_period('M') to .strftime('End_of_%b_%Y') and pass sort=False to both groupby(). But I wouldn't recommend that.

user_42384080
Related