How to use pandas to shift the last row to the first

user_8749168 0
Viewed 8547

So I have a dataframe that looks like this:

                         #1                     #2
1980-01-01               11.6985                126.0
1980-01-02               43.6431                134.0
1980-01-03               54.9089                130.0
1980-01-04               63.1225                126.0
1980-01-05               72.4399                120.0

What I want to do is to shift the first row of the first column (11.6985) down 1 row, and then the last row of the first column (72.4399) would be shifted to the first row, first column, like so:

                         #1                     #2
1980-01-01               72.4399                126.0
1980-01-02               11.6985                134.0
1980-01-03               43.6431                130.0
1980-01-04               54.9089                126.0
1980-01-05               63.1225                120.0

The idea is that I want to use these dataframes to find an R^2 value for every shift, so I need to use all the data or it might not work. I have tried to use pandas.Dataframe.shift():

print(data)

#Output
1980-01-01               11.6985                126.0
1980-01-02               43.6431                134.0
1980-01-03               54.9089                130.0
1980-01-04               63.1225                126.0
1980-01-05               72.4399                120.0

print(data.shift(1,axis = 0))

1980-01-01                   NaN                  NaN
1980-01-02               11.6985                126.0
1980-01-03               43.6431                134.0
1980-01-04               54.9089                130.0
1980-01-05               63.1225                126.0

So it just shifts both columns down and gets rid of the last row of data, which is not what I want.

Any advice?

pandas python-3.x python
4 Answers

Not sure about the performance, but you could try numpy.roll:

import numpy as np
print(df.apply(np.roll, shift=1))

#                 #1     #2
#1980-01-01  72.4399  120.0
#1980-01-02  11.6985  126.0
#1980-01-03  43.6431  134.0
#1980-01-04  54.9089  130.0
#1980-01-05  63.1225  126.0

To shift column #1 only:

df['#1'] = np.roll(df['#1'], shift=1)

print(df)
#                 #1     #2
#1980-01-01  72.4399  126.0
#1980-01-02  11.6985  134.0
#1980-01-03  43.6431  130.0
#1980-01-04  54.9089  126.0
#1980-01-05  63.1225  120.0
user_49834500

I don't think you could do this with shift, but one way is to use iloc:

In [11]: res = df.iloc[np.arange(-1, len(df)-1)]

In [12]: res
Out[12]:
                 #1     #2
1980-01-05  72.4399  120.0
1980-01-01  11.6985  126.0
1980-01-02  43.6431  134.0
1980-01-03  54.9089  130.0
1980-01-04  63.1225  126.0

In [13]: res.index = df.index

In [14]: res
Out[14]:
                 #1     #2
1980-01-01  72.4399  120.0
1980-01-02  11.6985  126.0
1980-01-03  43.6431  134.0
1980-01-04  54.9089  130.0
1980-01-05  63.1225  126.0

If you want this as a new column (just for #1):

In [21]: df.iloc[np.arange(-1, len(df)-1), 0]
Out[21]:
1980-01-05    72.4399
1980-01-01    11.6985
1980-01-02    43.6431
1980-01-03    54.9089
1980-01-04    63.1225
Name: #1, dtype: float64

In [22]: df.iloc[np.arange(-1, len(df)-1), 0].values
Out[22]: array([ 72.4399,  11.6985,  43.6431,  54.9089,  63.1225])

In [23]: df["new#1"] = df.iloc[np.arange(-1, len(df)-1), 0].values

In [24]: df
Out[24]:
                 #1     #2    new#1
1980-01-01  11.6985  126.0  72.4399
1980-01-02  43.6431  134.0  11.6985
1980-01-03  54.9089  130.0  43.6431
1980-01-04  63.1225  126.0  54.9089
1980-01-05  72.4399  120.0  63.1225
user_12402680

I know this is a very old question, but this was my solution, which I believe was a bit neater:

df = df.reindex(np.roll(df.index, shift=1))
user_47033670

create a new column with the desired ordering and then just sort the rows.

df['new_col'] = range(1, len(df)+1)
df['new_col'].loc[len(df)-1] = 0
df['new_col'].loc[0] = len(df)
df = df.reset_index()
user_48700050
Related