[SOLVED] How to use relativedelta to dynamically add dates to a list of dates in a dataframe

How to use relativedelta to dynamically add dates to a list of dates in a dataframe

I am new to python and have a few questions regarding dates.

Here is an example - I have a list of dates going from 01/01/2012 - 01/01/2025 with a monthly frequency. They also will change based on the data frame. Say one column of dates will have 130 months in between, the other will have 140 months, and so on.

The end goal is: regardless of how many months each set has, I need each "group" to have 180 months. So, in the above example of 01/01/2012 - 1/1/2025, I would need to add enough months to reach 1/1/2027.

Please let me know if this makes sense.

Solution

So if I understand you correctly, you have some data like:

import pandas as pd, numpy as np
from datetime import date
from dateutil.relativedelta import relativedelta
from random import randint
starts = [dt for i in range(130) if (dt := date(2012, 1, 1) + relativedelta(months=i)) <= date(2020, 1, 1)]
ends = [dt + relativedelta(months=randint(1, 5)) for dt in starts]
df = pd.DataFrame({ 'start': starts, 'end': ends })

so the current duration in months is:

df['duration'] = ((df.end - df.start)/np.timedelta64(1, 'M')).round().astype(int)

and you want to know how many to add to make the duration 180 months?

df['need_to_add'] = 180 - df.duration

then you can calculate a new end by something like:

def add_months(start_date, delta_period):
    end_date = start_date + relativedelta(months=delta_period)
    return end_date
df['new_end'] = df.apply(lambda r: add_months(r['end'], r['need_to_add']), axis=1)

I'm sure I haven't quite understood, as you could just add 180 months to the start date, but hopefully this gets you close to where you need to be.