Shifting Elements Of Column Based On Index Given Condition On Another Column

I have a dataframe (df) with 2 columns and 1 index. Index is datetime index and is in format of 2001-01-30 .... etc and the index is ordered by DATE and there are thousands of ide

Solution 1:

Consider the example dataframe df below

np.random.seed([3,1415])
df = pd.concat(dict(
        A=pd.Series(np.random.rand(10), pd.date_range('2016-09-30', periods=10)),
        B=pd.Series(np.random.rand(7), pd.date_range('2016-09-25', periods=7)),
        C=pd.Series(np.random.rand(10), pd.date_range('2016-09-20', periods=10)),
        D=pd.Series(np.random.rand(8), pd.date_range('2016-10-30', periods=8)),
        E=pd.Series(np.random.rand(12), pd.date_range('2016-10-25', periods=12)),
        F=pd.Series(np.random.rand(14), pd.date_range('2016-08-30', periods=14)),

    )).rename_axis(['ColumnA', None]).reset_index('ColumnA', name='ColumnB')

print(df.head(10))

           ColumnA   ColumnB
2016-09-30       A  0.4449392016-10-01       A  0.4075542016-10-02       A  0.4601482016-10-03       A  0.4652392016-10-04       A  0.4626912016-10-05       A  0.0165452016-10-06       A  0.8504452016-10-07       A  0.8177442016-10-08       A  0.7779622016-10-09       A  0.757983

---

use groupby + shift

d1 = df.set_index('ColumnA', append=True)
g = d1.groupby(level='ColumnA').ColumnB
keys = ['Forward', 'Back']
new_df = d1.join(pd.concat([g.shift(i) for i in [-1, 1]], axis=1, keys=keys))
print(new_df.query('ColumnA == "A"').head(10))

                     ColumnB   Forward      Back
           ColumnA                              
2016-09-30 A        0.4449390.407554       NaN
2016-10-01 A        0.4075540.4601480.4449392016-10-02 A        0.4601480.4652390.4075542016-10-03 A        0.4652390.4626910.4601482016-10-04 A        0.4626910.0165450.4652392016-10-05 A        0.0165450.8504450.4626912016-10-06 A        0.8504450.8177440.0165452016-10-07 A        0.8177440.7779620.8504452016-10-08 A        0.7779620.7579830.8177442016-10-09 A        0.757983       NaN  0.777962