python - Pandas Column 中有 NaN 的地方我想用 1 替换，除了 Totals 需要对 1 求和

Question

在 Month Return 列中，我需要将 NaN 替换为 1，但 Category 列中的 ros 替换为“TOTAL”。我需要那些来总结前一个“总计”行之后的 1。分组行的长度(按日期和帐户)可能因长度而异。

Return Date    Account      Category    Month Return
 7/31/2003      abcdef       BOND        NaN
 7/31/2003      abcdef       CASH        NaN
 7/31/2003      abcdef       EQUITY      NaN
 7/31/2003      abcdef       TOTAL       Nan
 7/31/2003      ghijkl       BOND        0.25
 7/31/2003      ghijkl       CASH        0.25
 7/31/2003      ghijkl       EQUITY      1.25
 7/31/2003      ghijkl       TOTAL       1.75
 7/31/2003      mnopqr       BOND        NaN
 7/31/2003      mnopqr       CASH        NaN
 7/31/2003      mnopqr       EQUITY      NaN
 7/31/2003      mnopqr       REAL        NaN
 7/31/2003      mnopqr       TOTAL       Nan

希望它看起来像这样:

Return Date    Account      Category    Month Return
 7/31/2003      abcdef       BOND        1
 7/31/2003      abcdef       CASH        1
 7/31/2003      abcdef       EQUITY      1
 7/31/2003      abcdef       TOTAL       3
 7/31/2003      ghijkl       BOND        0.25
 7/31/2003      ghijkl       CASH        0.25
 7/31/2003      ghijkl       EQUITY      1.25
 7/31/2003      ghijkl       TOTAL       1.75
 7/31/2003      mnopqr       BOND        1
 7/31/2003      mnopqr       CASH        1
 7/31/2003      mnopqr       EQUITY      1
 7/31/2003      mnopqr       REAL        1
 7/31/2003      mnopqr       TOTAL       4

Answer 1

您可以使用 DataFrame.fillna与 DataFrame.loc :

df=df.replace('Nan',np.nan)
c=df['Category'].ne('TOTAL')
df.loc[c,'Month_Return']=df.loc[c,'Month_Return'].fillna(1)
fill=df.groupby('Account')['Month_Return'].apply(lambda x: x.eq(1).cumsum())
df['Month_Return'].fillna(fill,inplace=True)
print(df)

   Return_Date Account Category Month_Return
0    7/31/2003  abcdef     BOND            1
1    7/31/2003  abcdef     CASH            1
2    7/31/2003  abcdef   EQUITY            1
3    7/31/2003  abcdef    TOTAL            3
4    7/31/2003  ghijkl     BOND         0.25
5    7/31/2003  ghijkl     CASH         0.25
6    7/31/2003  ghijkl   EQUITY         1.25
7    7/31/2003  ghijkl    TOTAL         1.75
8    7/31/2003  mnopqr     BOND            1
9    7/31/2003  mnopqr     CASH            1
10   7/31/2003  mnopqr   EQUITY            1
11   7/31/2003  mnopqr     REAL            1
12   7/31/2003  mnopqr    TOTAL            4