calculate dataframe column based on two rows in another column

Question

I have a dataframe, containing one column with prices. whats the best way to create a column calculating the rate of return between two rows (leaving the first or last Null).

For example the data frame looks as follows:

    Date        Price
2008-11-21    23.400000
2008-11-24    26.990000
2008-11-25    28.000000
2008-11-26    25.830000

Trying to add a column as follows:

    Date        Price       Return
2008-11-21    23.400000     0.1534
2008-11-24    26.990000     0.0374
2008-11-25    28.000000    -0.0775
2008-11-26    25.830000      NaN

Where the calculation of return column as follows:

Return Row 0 = Price Row 1 / Price Row 0 - 1

Should i for loop, or is there a better way?

Can you post raw data, code, your attempts and desired result. — EdChum
– EdChum, Commented Nov 25, 2016 at 11:02

EdChum · Accepted Answer · 2016-11-25 11:22:49Z

3

You can use shift to shift the rows and then div to divide the Series against itself shifted:

In [44]:
df['Return'] = (df['Price'].shift(-1).div(df['Price']) - 1)
df

Out[44]:
         Date  Price    Return
0  2008-11-21  23.40  0.153419
1  2008-11-24  26.99  0.037421
2  2008-11-25  28.00 -0.077500
3  2008-11-26  25.83       NaN

edited Nov 25, 2016 at 11:22

answered Nov 25, 2016 at 11:12

EdChum

397k204 gold badges837 silver badges583 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

calculate dataframe column based on two rows in another column

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related