I'm trying to fill NAs by using the previous Total and then subtracting the Change
Change Total
01/01/2021 -12 100
02/01/2021 -54 154
03/01/2021 -23 177
04/01/2021 -2 NaN
05/01/2021 -54 NaN
06/01/2021 -72 NaN
Desired output;
Change Total
01/01/2021 -12 100
02/01/2021 -54 154
03/01/2021 -23 177
04/01/2021 -2 179
05/01/2021 -54 233
06/01/2021 -72 305
I've attempted various ways of manipulating the ffill() but with no success;
df['Total'] = df['Total'].fillna(method = 'ffill' - df['Change'])
Is there a better way to attempt this? Any help much appreciated!
The expected output looks like ffill minus cumsum. Forward fill the last valid value then subtract the cumulative total at each NaN row:
# Select NaN rows
m = df['Total'].isna()
# Update NaN rows with the last valid value minus the current total Change
df.loc[m, 'Total'] = df['Total'].ffill() - df.loc[m, 'Change'].cumsum()
df:
Change Total
01/01/2021 -12 100.0
02/01/2021 -54 154.0
03/01/2021 -23 177.0
04/01/2021 -2 179.0
05/01/2021 -54 233.0
06/01/2021 -72 305.0