2

I'm a python newbie - so please bear with me.

I am trying to create a set of shifted columns in a dataframe. For example I have a DataFrame df:

                    heading      speed
time                
2018-09-03 14:53:02   195.0   0.000000
2018-09-03 14:53:07   195.0   0.416667
2018-09-03 14:53:12   195.0   0.138889
2018-09-03 14:53:17   195.0   0.277778
2018-09-03 14:53:22   195.0   0.972222
2018-09-03 14:53:27   289.0   1.944444

I would like to end up with:

                    heading      speed   prev_heading   prev_speed
time                
2018-09-03 14:53:02   190.0   0.000000   NaN            NaN
2018-09-03 14:53:07   195.0   0.416667   190.0          0.000000
2018-09-03 14:53:12   195.0   0.138889   195.0          0.416667
2018-09-03 14:53:17   195.0   0.277778   195.0          0.138889
2018-09-03 14:53:22   195.0   0.972222   195.0          0.277778
2018-09-03 14:53:27   289.0   1.944444   195.0          0.972222

I know how to do this with a single column:

df['prev_heading'] = df['heading'].shift(1)

But is there a way to do this with multiple columns at once say something like:

df['prev_heading', 'prev_speed'] = df['heading', 'speed'].shift(1)

It seems inefficient if I have to run the shift command multiple times, as I would like to parse the dataframe on once if I can.

Any help would be much appreciated.

paul frith
  • 551
  • 2
  • 4
  • 21
  • You can use df[['heading', 'speed']].shift() – Vaishali Dec 11 '18 at 18:12
  • This is not a duplicate - I am wanting to keep my original columns and create new shifted ones - as is clear in the example I gave. – paul frith Dec 11 '18 at 18:14
  • 1
    So you assign the result of shift to new columns, df[['prev_heading', 'prev_speed']] = df[['heading', 'speed']].shift() – Vaishali Dec 11 '18 at 18:15
  • This is what I orginally tried - however I got the error `KeyError: "['prev_speed'] not in index"` – paul frith Dec 11 '18 at 18:18
  • To be clear code run was: `df[['prev_heading', 'prev_speed']] = df[['heading', 'prev_speed']].shift(1)` – paul frith Dec 11 '18 at 18:19
  • 3
    That works for me. What about `pd.concat([df, df.shift().add_prefix('prev_')], 1)` – ALollz Dec 11 '18 at 18:21
  • df[['prev_heading', 'prev_speed']] = df[['heading', 'speed']].shift() works for me on your example dataset. What is the error message? – Vaishali Dec 11 '18 at 18:21
  • `File "./testitworks.py", line 116, in df[['prev_heading', 'prev_speed']] = df[['heading', 'prev_speed']].shift(1) File "/home/actian/.local/lib/python3.6/site-packages/pandas/core/frame.py", line 2682, in __getitem__ return self._getitem_array(key) File "/home/actian/.local/lib/python3.6/site-packages/pandas/core/frame.py", line 2726, in _getitem_array indexer = self.loc._convert_to_indexer(key, axis=1) File "/home/actian/.local/lib/python3.6/site-packages/pandas/core/indexing.py", line 1327, in _convert_to_indexer .format(mask=objarr[mask]))` – paul frith Dec 11 '18 at 18:25
  • Thank Alollz - this worked for me. – paul frith Dec 17 '18 at 16:24

0 Answers0