Create New Pandas DataFrame Column with Values using Previous Row

Question

If we have a Pandas DataFrame containing the following values

            x          
date
2017-07-30  1
2017-07-31  2
2017-08-01  3
2017-08-02  4

how can we create a new column y whose value is calculated using

today's y = 2*(previous day's y) + (today's x)

for the oldest date, y will be 1

Expected Result:

            x       y     
date
2017-07-30  1       1
2017-07-31  2       4
2017-08-01  3       11
2017-08-02  4       26

Attempt:

import pandas as pd 

d = {
    'date': ['2017-07-30', '2017-07-31', '2017-08-01', '2017-08-02'],
    'x': [1,2,3,4]
}
df = pd.DataFrame.from_dict(d).set_index('date')
df['y'] = 1
df['y'] = df['y'].shift(1)*2 + df['x']
print(df)

Attempt's Result

            x    y
date
2017-07-30  1  NaN
2017-07-31  2  4.0
2017-08-01  3  5.0
2017-08-02  4  6.0

BENY · Accepted Answer · 2018-03-01 22:39:46Z

1

IIUC..cumsum?

df.x.cumsum()
Out[864]: 
date
2017-07-30     1
2017-07-31     3
2017-08-01     6
2017-08-02    10
Name: x, dtype: int64

Updated

n=2
s=n**(np.arange(len(df)))[::-1]
df.x.rolling(window=len(df),min_periods=1).apply(lambda x : sum(x*s[-len(x):]))
Out[894]: 
date
2017-07-30     1.0
2017-07-31     4.0
2017-08-01    11.0
2017-08-02    26.0
Name: x, dtype: float64

edited Mar 1, 2018 at 22:39

answered Mar 1, 2018 at 22:23

BENY

324k22 gold badges176 silver badges250 bronze badges

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

2 Comments

Nyxynyx Over a year ago

Is there a more general solution, to handle something like today's y = 2*(previous day's y) + (today's x). Updated the question.

BENY Over a year ago

@Nyxynyx this will involve some math, hold on one second

user2285236 · Accepted Answer · 2018-03-01 22:52:05Z

1

What you describe is a recursive calculation and in pandas general way to do is to use expanding objects with a custom function:

from functools import reduce  # Python 3
df['x'].expanding().apply(lambda r: reduce(lambda prev, value: 2*prev + value, r))
Out: 
date
2017-07-30     1.0
2017-07-31     4.0
2017-08-01    11.0
2017-08-02    26.0
Name: x, dtype: float64

See one of my previous answers for a detailed discussion on performance of expanding. (tl;dr: a for loop is generally better.)

answered Mar 1, 2018 at 22:52

user2285236

Collectives™ on Stack Overflow

Create New Pandas DataFrame Column with Values using Previous Row

2 Answers 2

2 Comments

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

2 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related