Python -填充()与减法

Question

我试图通过使用以前的Total来填充NAs，然后减去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

期望输出值;

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

我已经尝试了各种方法来操作填充()，但没有成功;

df['Total'] = df['Total'].fillna(method = 'ffill' - df['Change'])

有更好的方法来尝试这个吗?任何帮助非常感激!

Answer 1

期望输出是ffill减去cumsum。前向填充最后一个有效值，然后减去每个NaN行的累积总数:

# 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

Answer 2

让我们试试combine_first

df = df.combine_first(df[['Total']].ffill().sub(df.loc[df.Total.isnull(),'Change'].cumsum(),axis=0))
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

Answer 3

您可以使用pd.Series.where:

df["Total"] = df["Total"].where(df["Total"].notnull(),
df['Total'].ffill() - df.loc[df['Total'].isnull(), 'Change'].cumsum())
print (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

Answer 4

可以这样使用np.where和fillna()::

change_when_total_null = df.loc[df['Total'].isnull(), 'Change']
df['Total'] = np.where(df['Total'].isnull(),df['Total'].fillna(method='ffill') -change_when_total_null.cumsum(),df['Total'])

打印:

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