Shift multiple daily value columns forward by one year in Pandas-CodePudding

Given a dataframe df as follows:

import pandas as pd
import numpy as np

np.random.seed(2021)
dates = pd.date_range('20130101', periods=720)
df = pd.DataFrame(np.random.randint(0, 100, size=(720, 3)), index=dates, columns=list('ABC'))
df

Out:

             A   B   C
2013-01-01  85  57   0
2013-01-02  94  86  44
2013-01-03  62  91  29
2013-01-04  21  93  24
2013-01-05  12  70  70
         ..  ..  ..
2014-12-17  38  42  20
2014-12-18  67  93  47
2014-12-19  27  10  74
2014-12-20  18  92  62
2014-12-21  90  40  31

How could I shift column B and C forward by one year? Thanks. Please note leap year issue.

The code below seems works for example data, but for real data with NaNs inside B and C, it generates: ValueError: cannot reindex from a duplicate axis:

df[['B', 'C']] = df[['B', 'C']].shift(freq=pd.DateOffset(years=1))
print(df)

Out:

             A     B     C
2013-01-01  85   NaN   NaN
2013-01-02  94   NaN   NaN
2013-01-03  62   NaN   NaN
2013-01-04  21   NaN   NaN
2013-01-05  12   NaN   NaN
         ..   ...   ...
2014-12-17  38  33.0  79.0
2014-12-18  67  24.0  53.0
2014-12-19  27  54.0  39.0
2014-12-20  18  68.0  80.0
2014-12-21  90  65.0  65.0

CodePudding user response：

Code below seems works:

df[['B', 'C']] = df[['B', 'C']].shift(freq=pd.DateOffset(years=1))
print(df)

Out:

             A     B     C
2013-01-01  85   NaN   NaN
2013-01-02  94   NaN   NaN
2013-01-03  62   NaN   NaN
2013-01-04  21   NaN   NaN
2013-01-05  12   NaN   NaN
         ..   ...   ...
2014-12-17  38  33.0  79.0
2014-12-18  67  24.0  53.0
2014-12-19  27  54.0  39.0
2014-12-20  18  68.0  80.0
2014-12-21  90  65.0  65.0