Pandas data shift cyclically down

Question

If we have the following data:

X = pd.DataFrame({"t":[1,2,3,4,5],"A":[34,12,78,84,26], "B":[54,87,35,25,82], "C":[56,78,0,14,13], "D":[0,23,72,56,14], "E":[78,12,31,0,34]})
X

    A   B   C   D   E  t
0  34  54  56   0  78  1
1  12  87  78  23  12  2
2  78  35   0  72  31  3
3  84  25  14  56   0  4
4  26  82  13  14  34  5 

How can I move data cyclically, so the next step:

    A   B   C   D   E  t
4  26  82  13  14  34  5 
0  34  54  56   0  78  1
1  12  87  78  23  12  2
2  78  35   0  72  31  3
3  84  25  14  56   0  4

And then:

    A   B   C   D   E  t
3  84  25  14  56   0  4
4  26  82  13  14  34  5 
0  34  54  56   0  78  1
1  12  87  78  23  12  2
2  78  35   0  72  31  3

etc..

It should also shift index values ​​with a string.

I know pandas X.shift (), but it did not do the cyclical thing.

Answer 1

You can combine reindexwith np.roll:

X = X.reindex(np.roll(X.index, 1))

Another option is to combine concatwith iloc:

shift = 1
X = pd.concat([X.iloc[-shift:], X.iloc[:-shift]])

Result:

    A   B   C   D   E  t
4  26  82  13  14  34  5
0  34  54  56   0  78  1
1  12  87  78  23  12  2
2  78  35   0  72  31  3
3  84  25  14  56   0  4

Delay

Using the following setting to create a larger DataFrame and functions for synchronization:

df = pd.concat([X]*10**5, ignore_index=True)

def root1(df, shift):
    return df.reindex(np.roll(df.index, shift))

def root2(df, shift):
    return pd.concat([df.iloc[-shift:], df.iloc[:-shift]])

def ed_chum(df, num):
    return pd.DataFrame(np.roll(df, num, axis=0), np.roll(df.index, num), columns=df.columns)

def divakar1(df, shift):
    return df.iloc[np.roll(np.arange(df.shape[0]), shift)]

def divakar2(df, shift):
    idx = np.mod(np.arange(df.shape[0])-1,df.shape[0])
    for _ in range(shift):
        df = df.iloc[idx]
    return df

I get the following timings:

%timeit root1(df.copy(), 25)
10 loops, best of 3: 61.3 ms per loop

%timeit root2(df.copy(), 25)
10 loops, best of 3: 26.4 ms per loop

%timeit ed_chum(df.copy(), 25)
10 loops, best of 3: 28.3 ms per loop

%timeit divakar1(df.copy(), 25)
10 loops, best of 3: 177 ms per loop

%timeit divakar2(df.copy(), 25)
1 loop, best of 3: 4.18 s per loop

Answer 2

You can use in a custom function: np.roll

In [83]:
def roll(df, num):
    return pd.DataFrame(np.roll(df,num,axis=0), np.roll(df.index, num), columns=df.columns)
​
roll(X,1)

Out[83]:
    A   B   C   D   E  t
4  26  82  13  14  34  5
0  34  54  56   0  78  1
1  12  87  78  23  12  2
2  78  35   0  72  31  3
3  84  25  14  56   0  4

In [84]:
roll(X,2)

Out[84]:
    A   B   C   D   E  t
3  84  25  14  56   0  4
4  26  82  13  14  34  5
0  34  54  56   0  78  1
1  12  87  78  23  12  2
2  78  35   0  72  31  3

df, df,

Answer 3

One approach would be to create such a shifted index array once and reuse it again and again for indexing into strings using .iloc , as well -

idx = np.mod(np.arange(X.shape[0])-1,X.shape[0])
X = X.iloc[idx]

Another way to create a idxwill np.roll: np.roll(np.arange(X.shape[0]),1).

Run Example -

In [113]: X   # Starting version
Out[113]: 
    A   B   C   D   E  t
0  34  54  56   0  78  1
1  12  87  78  23  12  2
2  78  35   0  72  31  3
3  84  25  14  56   0  4
4  26  82  13  14  34  5

In [114]: idx = np.mod(np.arange(X.shape[0])-1,X.shape[0]) # Creating once       

In [115]: X = X.iloc[idx] # Using idx

In [116]: X
Out[116]: 
    A   B   C   D   E  t
4  26  82  13  14  34  5
0  34  54  56   0  78  1
1  12  87  78  23  12  2
2  78  35   0  72  31  3
3  84  25  14  56   0  4

In [117]: X = X.iloc[idx] # Re-using idx

In [118]: X
Out[118]: 
    A   B   C   D   E  t
3  84  25  14  56   0  4
4  26  82  13  14  34  5
0  34  54  56   0  78  1
1  12  87  78  23  12  2
2  78  35   0  72  31  3   ## and so on

Answer 4

You can use numpy.roll:

import numpy as np

nb_iterations = 3 # number of steps you want
for i in range(nb_iterations):
    for col in X.columns :
        df[col] = numpy.roll(df[col], 1)

Which is equivalent:

for col in X.columns :
        df[col] = numpy.roll(df[col], nb_iterations)

Here is a link to the documentation of this useful function.