How to divide a date range into 365 day periods in a python pandas data frame

user_10278559 0
Viewed 289

I have a pandas data frame which contains data that looks like below

import pandas as pd
data = [['0','2018-03-01','2020-04-11'],['1','2017-11-17','2020-11-16'],['2','2017-07-12','2020-07-12']]
df = pd.DataFrame(data,columns=['account_number','contract_start_date','contract_end_date'])
df

enter image description here

I need to break each account's contract into 365 day periods using the contract_start_date and contract_end_date. The last period needs to contain whatever days remain. An example of that is below enter image description here

what I have tried so far: I tried to create a new data frame and thought I could generate the 365 periods using date ranges. It hasn't worked as I hoped. Would appreciate any guidance.

new_df= pd.concat([pd.DataFrame({'start_date': pd.date_range(row.contract_start_date, row.contract_end_date, freq ='365D'),
 'account_number': row.account_number
                                    }) for row in df.itertuples()], ignore_index=True)
new_df
pandas date python
1 Answers

There are leaps years, so output is different.

Idea is mapped last values by Series.duplicated in new account_number by original contract_end_date and for another values are added 365 days:

s1 = pd.to_datetime(df.set_index('account_number')['contract_end_date'])

s2 = new_df['account_number'].map(s1)
s3 = new_df['start_date'] + pd.Timedelta(365, 'd')
mask = new_df['account_number'].duplicated(keep='last')

new_df['contract_end_date'] = np.where(mask, s3, s2)
new_df['days'] = new_df['contract_end_date'] - new_df['start_date']

print (new_df)
   start_date account_number contract_end_date     days
0  2018-03-01              0        2019-03-01 365 days
1  2019-03-01              0        2020-02-29 365 days
2  2020-02-29              0        2020-04-11  42 days
3  2017-11-17              1        2018-11-17 365 days
4  2018-11-17              1        2019-11-17 365 days
5  2019-11-17              1        2020-11-16 365 days
6  2020-11-16              1        2020-11-16   0 days
7  2017-07-12              2        2018-07-12 365 days
8  2018-07-12              2        2019-07-12 365 days
9  2019-07-12              2        2020-07-11 365 days
10 2020-07-11              2        2020-07-12   1 days
user_29010020
Related