python - 计算每一行和每个索引的周年日期

Question

我的合约第一行 144 开始于 2008-02-11 并在 结束2011-03-28 .我要计算周年纪念日 之间的每一年2008 和 2011 (对于 144 和其他契约(Contract))
主要目标是检查每一行契约(Contract)的周年日期是否正常，如果没有计算它并使用正确的值更新开始和结束
*这是我的 Pandas 数据框，涉及两个合约 "144"和 "150" :


指数
NUM_contrat
开始
结尾
周年纪念日
数量


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


*这是我想获得的数据框:


指数
NUM_contrat
开始
结尾
周年纪念日
数量


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


这是我的代码，仅当我只有一个 Num_contract 但不适用于 2 个或更多 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

Answer 1

为 生成日期范围开始 日期

explode()它生成所需的行

计算 结束 和 周年纪念

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

输出


指数
NUM_contrat
开始
结尾
周年纪念日
数量


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