Forwardfill combined with computation (method = 'ffill' * xyz) in python pandas
I need to fill in the blanks with NaN calculations that depend on the previous values in dataframe = df. What I am so far is:
df = pd.DataFrame({'a': [None] * 6, 'b': [2, 3, 10, 3, 5, 8]})
df["c"] =np.NaN
df["c"][0] = 1
df["c"][2] = 3
i = 1
while i<10:
df.c.fillna(df.c.shift(i)*df.b,inplace=True)
i+1
Unfortunately the solution with this while loop doesn't work and is definitely a very bad solution for pandas. So what I'm looking for is this
df.c.fillna(method='ffill'*df.b,inplace=True)
I know this doesn't work either, I just think it makes it clearer what I'm looking for.
Before filling the data frame, it looks like this:
b c
0 2 1
1 3 NaN
2 10 3
3 3 NaN
4 5 NaN
5 8 NaN
The desired output should look like this:
b c
0 2 1 # nothing filled in since data is set from df["c"][0] = 1
1 3 3 # fill in previous c * b = 1 * 3 = 3
2 10 3 # nothing filled in since data is set from df["c"][2] = 3
3 3 9 # fill in previous c * b = 3 * 3 = 9
4 5 45 # fill in previous c * b = 9 * 5 = 45
5 8 360 # fill in previous c * b = 45 * 8 = 360
So basically: if there is no data, it should be filled with caculation.
source to share
I cannot figure out how to do this in one loop, the problem is that you want to apply some kind of sliding application that can then look at the previous line, the problem is that the update of the previous line will not be observed until apply
will not end, so, for example, the following works because we run the application 3 times. This is not good IMO:
In [103]:
def func(x):
if pd.notnull(x['c']):
return x['c']
else:
return df.iloc[x.name - 1]['c'] * x['b']
df['c'] = df.apply(func, axis =1)
df['c'] = df.apply(func, axis =1)
df['c'] = df.apply(func, axis =1)
df
Out[103]:
a b c
0 None 2 1
1 None 3 3
2 None 10 3
3 None 3 9
4 None 5 45
5 None 8 360
source to share