Calculate the anniversary date for each row and for each index

user_7086646 0
Viewed 315

the first row of my contract 144 starts at 2008-02-11 and ends at 2011-03-28. I want to calculate the anniversary date for each year between 2008 and 2011 (For 144 and others contracts)

the main goal is to check for each row of contract if the anniversary date its ok, if its not calculate it and update the start and the end with the right values

*this is my pandas dataframe which concerns two contracts "144" and "150":

index NUM_contrat start end anniversary quantity
0 144 2008-02-11 2011-03-28 2009-02-11 550
1 144 2011-03-28 2011-09-19 2012-02-11 550
2 150 2011-09-19 2012-02-10 2012-09-19 900
3 150 2012-02-10 2013-02-10 2013-09-19 900

*this is the dataframe i want to get :

index NUM_contrat start end anniversary quantity
0 144 2008-02-11 2009-02-11 2009-02-11 550
0 144 2009-02-11 2010-02-11 2010-02-11 550
0 144 2010-02-11 2011-02-11 2011-02-11 550
0 144 2011-02-11 2011-03-28 2012-02-11 550
1 144 2011-03-28 2011-09-19 2012-02-11 550
2 150 2011-09-19 2012-02-10 2012-09-19 900
3 150 2012-03-28 2012-07-11 2013-09-19 900

this is my code that works only when i have only one Num_contract but doesnot work with 2 or more NUM_Contrat


    
for NUM_contrat in df['NUM_contrat'].unique():

    for i in df['index'].unique():

       for index,row in df.iterrows():

           if df.iloc[index]['end'] > df.iloc[index]['anniversary']:

               df1=pd.DataFrame(df.iloc[index]).transpose() 

               df.loc[index, 'end'] = df.loc[index, 'anniversary']

               df= pd.concat([df,df1],ignore_index=True).sort_values(['start','end']).reset_index(drop=True)

               df.loc[index+1,'start'] = df.loc[index,'anniversary']
               df.loc[index+1,'anniversary'] = df.loc[index,'anniversary'] + relativedelta(years=1)
                    
    return df
pandas for-loop python loops
2 Answers

Update: according to your comment to have more than one row for each contract.

The following snippet could answer to your problem.

>>> data
   NUM_contrat      start        end anniversary  quantity
0          144 2008-02-11 2011-03-28  2009-02-11       550
1          144 2011-03-28 2011-09-19  2012-02-11       550
2          150 2011-09-19 2012-02-10  2012-09-19       900
3          150 2012-02-10 2013-02-10  2013-09-19       900

for _, sr in data.loc[data["anniversary"] < data["end"]].iterrows():
    df = sr.to_frame().transpose()
    periods = sr["end"].year - sr["start"].year
    idx = pd.date_range(sr["anniversary"], periods=periods, freq="Y")
    idx += pd.DateOffset(days=sr["anniversary"].day, months=sr["anniversary"].month - 1)
    data = pd.concat([data, df.loc[df.index.repeat(len(idx))].assign(anniversary=idx)])

  NUM_contrat      start        end anniversary quantity
0         144 2008-02-11 2011-03-28  2009-02-11      550
0         144 2008-02-11 2011-03-28  2010-02-11      550
0         144 2008-02-11 2011-03-28  2011-02-11      550
0         144 2008-02-11 2011-03-28  2012-02-11      550
1         144 2011-03-28 2011-09-19  2012-02-11      550
2         150 2011-09-19 2012-02-10  2012-09-19      900
3         150 2012-02-10 2013-02-10  2013-09-19      900
user_152399510
  • generate a date range for start date
  • explode() it to generate required rows
  • calc end and anniversary
df = pd.read_csv(io.StringIO("""index   NUM_contrat start   end anniversary quantity
0   144 2008-02-11  2011-03-28  2009-02-11  550
1   144 2011-03-28  2011-09-19  2012-02-11  550
2   150 2011-09-19  2012-02-10  2012-09-19  900
3   150 2012-02-10  2013-02-10  2013-09-19  900"""), sep="\t", index_col=0)

# cleanup - make sure dates are dates
df.start = pd.to_datetime(df.start)
df.end = pd.to_datetime(df.end)
df.anniversary = pd.to_datetime(df.anniversary)
df
# # generate a date range for start, based on end date
df2 = (df.assign(start=df.apply(lambda r: pd.date_range(r.start, 
                                                 periods=((r.end.year+1)-r.start.year), 
                                                 freq=pd.DateOffset(years=1)), axis=1))
# explode the start dates
 .explode("start")
# calc end and anivversary dates
 .assign(end=lambda dfa: np.where(dfa.start.dt.year==dfa.end.dt.year,dfa.end, dfa.start+pd.DateOffset(years=1)),
        anniversary=lambda dfa: dfa.start+pd.DateOffset(years=1))
# anniversary is always the one from the first instance of the contract
 .assign(anniversary=lambda dfa: dfa.groupby(["NUM_contrat",dfa.start.dt.year])["anniversary"].transform("first"))
)

df2

output

index NUM_contrat start end anniversary quantity
0 144 2008-02-11 00:00:00 2009-02-11 00:00:00 2009-02-11 00:00:00 550
0 144 2009-02-11 00:00:00 2010-02-11 00:00:00 2010-02-11 00:00:00 550
0 144 2010-02-11 00:00:00 2011-02-11 00:00:00 2011-02-11 00:00:00 550
0 144 2011-02-11 00:00:00 2011-03-28 00:00:00 2012-02-11 00:00:00 550
1 144 2011-03-28 00:00:00 2011-09-19 00:00:00 2012-02-11 00:00:00 550
2 150 2011-09-19 00:00:00 2012-09-19 00:00:00 2012-09-19 00:00:00 900
2 150 2012-09-19 00:00:00 2012-02-10 00:00:00 2013-09-19 00:00:00 900
3 150 2012-02-10 00:00:00 2013-02-10 00:00:00 2013-09-19 00:00:00 900
3 150 2013-02-10 00:00:00 2013-02-10 00:00:00 2014-02-10 00:00:00 900
user_94414040
Related