How to create a column using a function based of previous values in the column in python

Question

My Problem

I have a loop that creates a value for x in time period t based on x in time period t-1. The loop is really slow so i wanted to try and turn it into a function. I tried to use np.where with shift() but I had no joy. Any idea how i might be able to get around this problem?

Thanks!

My Code

import numpy as np
import pandas as pd

csv1 = pd.read_csv('y_list.csv', delimiter = ',')
df = pd.DataFrame(csv1)

df.loc[df.index[0], 'var'] = 0

for x in range(1,len(df.index)):
    if df["LAST"].iloc[x] > 0:
        df["var"].iloc[x] = ((df["var"].iloc[x - 1] * 2) + df["LAST"].iloc[x]) / 3
    else:
        df["var"].iloc[x] = (df["var"].iloc[x - 1] * 2) / 3 

df

Input Data

Dates,LAST
03/09/2018,-7
04/09/2018,5
05/09/2018,-4
06/09/2018,5
07/09/2018,-6
10/09/2018,6
11/09/2018,-7
12/09/2018,7
13/09/2018,-9

Output

Dates,LAST,var
03/09/2018,-7,0.000000
04/09/2018,5,1.666667
05/09/2018,-4,1.111111
06/09/2018,5,2.407407
07/09/2018,-6,1.604938
10/09/2018,6,3.069959
11/09/2018,-7,2.046639
12/09/2018,7,3.697759
13/09/2018,-9,2.465173

score 1 · Accepted Answer · answered Sep 18 '19 at 14:39

1

You are looking at ewm:

arg = df.LAST.clip(lower=0)
arg.iloc[0] = 0
arg.ewm(alpha=1/3, adjust=False).mean()

Output:

0    0.000000
1    1.666667
2    1.111111
3    2.407407
4    1.604938
5    3.069959
6    2.046639
7    3.697759
8    2.465173
Name: LAST, dtype: float64

answered Sep 18 '19 at 14:39

Quang Hoang

146,074
10
56
74

Many thanks. It doesnt seem to create a new column for me though? also what is the logic behind it? – pythonlearner13 Sep 18 '19 at 15:13
`df["var"] = arg.ewm(alpha=1/3, adjust=False).mean()`? – Quang Hoang Sep 18 '19 at 15:14
thanks that works. If i wanted to change the 2/3 ratio to 13/14 how would i do that in your code? – pythonlearner13 Sep 18 '19 at 15:41
`alpha = 1/3 = 1-2/3`, so `alpha = 1/14`? – Quang Hoang Sep 18 '19 at 15:42
I tried that for the below (the code in the other thread you answered) but it gave v different numbers. The code is... for x in range(1,len(df.index)): if df["delta"].iloc[x] > 0: df.iloc[x, -1] = ((df["avg_gain"].iloc[x - 1] * 13) + df["delta"].iloc[x]) / 14 else: df.iloc[x,-1] = ((df["avg_gain"].iloc[x - 1].copy() * 13) + 0) / 14 – pythonlearner13 Sep 18 '19 at 15:45
Any chance you may know how to solve this? https://stackoverflow.com/questions/57866947/how-to-turn-a-multi-conditional-loop-into-a-function-in-python – pythonlearner13 Sep 20 '19 at 12:10
I'm assume this one isn't an easy one? – pythonlearner13 Sep 23 '19 at 08:27

CT Zhu · Answer 2 · 2019-09-10T12:43:59.263

0

You can use df.shift to shift the dataframe be a default of 1 row, and convert the if-else block in to a vectorized np.where:

In [36]: df
Out[36]: 
        Dates  LAST  var
0  03/09/2018    -7  0.0
1  04/09/2018     5  1.7
2  05/09/2018    -4  1.1
3  06/09/2018     5  2.4
4  07/09/2018    -6  1.6
5  10/09/2018     6  3.1
6  11/09/2018    -7  2.0
7  12/09/2018     7  3.7
8  13/09/2018    -9  2.5

In [37]: (df.shift(1)['var']*2 + np.where(df['LAST']>0, df['LAST'], 0)) / 3
Out[37]: 
0         NaN
1    1.666667
2    1.133333
3    2.400000
4    1.600000
5    3.066667
6    2.066667
7    3.666667
8    2.466667
Name: var, dtype: float64

edited Sep 10 '19 at 12:43

answered Sep 10 '19 at 12:29

CT Zhu

52,648
17
120
133

Thank you for the answer. When i enter the code it just gives me NaN. Am i missing anything? – pythonlearner13 Sep 10 '19 at 12:41
1

@pythonlearner13, see edit. I suspect there is a data issue? Assume what you posted was the input? – CT Zhu Sep 10 '19 at 12:45
Sorry, that would be why. The Dates and LAST column are the input, and the var column is the expected output. Is there a way to calculate var without using the var column? – pythonlearner13 Sep 10 '19 at 12:48
I have amended the original post to make it clearer – pythonlearner13 Sep 10 '19 at 12:54

How to create a column using a function based of previous values in the column in python

2 Answers2

Linked