Python 2.7: Shift A Dataframe By Day And A Column Value

I have a dataframe named df1 as following: df1: a b id 2010-01-01 2 3 21 2010-01-01 2 4 22 2010-01-01 3 5 23 2010-01-01 4 6

Solution 1:

If all groups are same length (in sample 4) and DatetimeIndex is sorted:

df2 = df.shift((df.index == df.index[0]).sum())
print (df2)
              a    b    id
2010-01-01  NaN  NaN   NaN
2010-01-01  NaN  NaN   NaN
2010-01-01  NaN  NaN   NaN
2010-01-01  NaN  NaN   NaN
2010-01-02  2.0  3.0  21.0
2010-01-02  2.0  4.0  22.0
2010-01-02  3.0  5.0  23.0
2010-01-02  4.0  6.0  24.0
2010-01-03  1.0  4.0  21.0
2010-01-03  2.0  5.0  22.0
2010-01-03  3.0  6.0  23.0
2010-01-03  4.0  7.0  24.0

But if need shift values of index by one day:

df3 = df.shift(1, freq='D')
print (df3)
            a   b  id
2010-01-02  2   3  21
2010-01-02  2   4  22
2010-01-02  3   5  23
2010-01-02  4   6  24
2010-01-03  1   4  21
2010-01-03  2   5  22
2010-01-03  3   6  23
2010-01-03  4   7  24
2010-01-04  1   8  21
2010-01-04  2   9  22
2010-01-04  3  10  23
2010-01-04  4  11  24

Solution 2:

One of the way is with the help of shape if the dates are sorted i.e

df.shift(df.loc[df.index[0]].shape[0])
# Or len 
df.shift(len(df.loc[df.index[0]]))

Output :

              a    b    id
2010-01-01  NaN  NaN   NaN
2010-01-01  NaN  NaN   NaN
2010-01-01  NaN  NaN   NaN
2010-01-01  NaN  NaN   NaN
2010-01-02  2.0  3.0  21.0
2010-01-02  2.0  4.0  22.0
2010-01-02  3.0  5.0  23.0
2010-01-02  4.0  6.0  24.0
2010-01-03  1.0  4.0  21.0
2010-01-03  2.0  5.0  22.0
2010-01-03  3.0  6.0  23.0
2010-01-03  4.0  7.0  24.0