python - 如何使用 Pandas 数据框的余额列计算银行对帐单借方/贷方列？

Question

我有一个如下所示的数据框:

                   Date_1                Date_2         DR      CR      Bal
0                5 Dec 2017               5 Dec 2017    500     NaN     1000
1               14 Dec 2017               14 Dec 2017   NaN     NaN     1500
2               15 Dec 2017               15 Dec 2017   NaN     NaN     1200
3               18 Dec 2017               18 Dec 2017   NaN     NaN     1700
4               21 Dec 2017               21 Dec 2017   NaN     NaN     2000
5               22 Dec 2017                22 Dec 2017  NaN     NaN     1000

在上面的数据框中，“Bal”列包含余额值，并希望根据下一个“Bal”金额填充 DR/CR 值。

我是用简单的 python 做的，但似乎 pandas 可以以非常智能的方式执行此操作。

预期输出:

                   Date_1                Date_2         DR      CR      Bal
0                5 Dec 2017               5 Dec 2017    500     NaN     1000
1               14 Dec 2017               14 Dec 2017   NaN     500     1500
2               15 Dec 2017               15 Dec 2017   300     NaN     1200
3               18 Dec 2017               18 Dec 2017   NaN     500     1700
4               21 Dec 2017               21 Dec 2017   NaN     300     2000
5               22 Dec 2017                22 Dec 2017  1000    NaN     1000

Answer 1

你可以使用 pd.mask .首先使用diff计算余额差额.通过使用掩码，如果一列为负，则用其绝对值填充，并在另一列为正时屏蔽 np.nan 值。

diff = df['Bal'].diff()
df['DR'] = df['DR'].mask(diff < 0, diff.abs())
df['CR'] = df['CR'].mask(diff > 0, diff)

#Output
#                 Date_1  Date_2      DR     CR   Bal
#0 5  Dec 2017 5     Dec    2017   500.0    NaN  1000
#1 14 Dec 2017 14    Dec    2017     NaN  500.0  1500
#2 15 Dec 2017 15    Dec    2017   300.0    NaN  1200
#3 18 Dec 2017 18    Dec    2017     NaN  500.0  1700
#4 21 Dec 2017 21    Dec    2017     NaN  300.0  2000
#5 22 Dec 2017 22    Dec    2017  1000.0    NaN  1000