0

Is there an efficient way to update each individual row in a DataFrame with values from the row below it in sequence?

The DataFrame contains a sequence of positions to which a vehicle travelled in order. I've added two new columns next_x and next_y, and want to populate those columns, by row, with the values in the current_x and current_y column from the row directly beneath. The idea being each row will then contain pairs of coordinates describing a position, and the next position a vehicle travelled.

This is working using iterrows but is prohibitively slow over the full dataset.

for index, row in df.iterrows():
    if int(index) < len(df)-1:
        df['next_x'].iloc[int(index)] = df['current_x'].iloc[(int(index)+1)]
        df['next_y'].iloc[int(index)] = df['current_y'].iloc[(int(index)+1)]

I can't figure out a good way to use apply - am I missing something?

Cate
  • 431
  • 1
  • 7
  • 22
  • Please provide a sample data and the output expected. Refer to this post for cues: https://stackoverflow.com/questions/20109391/how-to-make-good-reproducible-pandas-examples – YOLO Feb 26 '18 at 19:03

2 Answers2

1

Try this:

df[['next_x', 'next_y']] = df[['current_x', 'current_y']].shift(-1)
Peter Leimbigler
  • 10,775
  • 1
  • 23
  • 37
1

You're looking for "shift" in pandas.

    df = pd.DataFrame(np.random.randint(low=0, high=10, size=(5, 5)),columns=['a', 'b', 'c', 'd', 'e'])

df.shift(1) will give you all the rows shifted down by one.

Then you just need something like:

df- df.shift(1)

Good luck!

EHB
  • 1,127
  • 3
  • 13
  • 24
  • Nice! Quick note - to populate each row with the values of the next row, you'll need `df['next_x'] = df['current_x'].shift(-1)` to shift each column *up* by one place. – Peter Leimbigler Feb 26 '18 at 19:10
  • @PeterLeimbigler definitely. A little unclear exactly what the OP wants to do, but I'm sure this would get them on the right track. – EHB Feb 26 '18 at 19:13
  • Thanks both, I was putting together some sample data but @PeterLeimbigler was too fast. `df- df.shift(1)` as it is shifts all the data down a row and I only needed the two columns, but this is really useful as well. – Cate Feb 26 '18 at 19:18