Pandas pivot透视时保留可变值列且避免产生大量NaN值

现有名为jpm_2021的pandas DataFrame，共包含16个字段：SRC、SRCDate、Ticker、Coupon、Vintage、Bal、WAC、WAM、WALA、LNSZ、LTV、FICO、Refi%、Month_Assessed、CPR、Month_key，样例数据如下：

SRC     SRCDate Ticker  Coupon  Vintage   Bal   WAC  WAM  WALA       LNSZ  LTV  FICO  Refi% Month_Assessed       CPR Month_key
894   JPM  02/05/2021   FNCI     1.5     2020  28.7  2.25  175     4  293 / 286   60   777     91            Apr  7.536801       M+2
1528  JPM  03/05/2021   FNCI     1.5     2020  28.7  2.25  175     4  293 / 286   60   777     91            Apr  5.131145       M+1
2162  JPM  04/07/2021   FNCI     1.5     2020  28.0  2.25  173     6  292 / 281   60   777     91            Apr  7.233214         M
2796  JPM  05/07/2021   FNCI     1.5     2020  27.6  2.25  171     7  292 / 279   60   777     91            Apr  8.900000       M-1
3430  JPM  06/07/2021   FNCI     1.5     2020  27.2  2.25  170     8  292 / 277   60   777     91            Apr  8.900000       M-2

当前使用pivot()做数据透视的代码如下：

jpm_final = jpm_2021.pivot(
    index=['SRC', 'Ticker', 'Coupon', 'Vintage', 'Month_Assessed'], 
    columns="Month_key", 
    values="CPR"
).rename_axis(columns=None).reset_index()

透视后以['SRC', 'Ticker', 'Coupon', 'Vintage', 'Month_Assessed']为行索引，Month_key的枚举值（M、M+1、M+2、M-1、M-2）为列，单元格填充对应CPR值，结果样例：

SRC Ticker  Coupon  Vintage Month_Assessed  M    M+1    M+2    M-1  M-2
0   JPM FNCI    1.5     2020    Apr            7.23  5.13   7.53   8.9  8.9
1   JPM FNCI    1.5     2020    Aug            15.16 14.92  11.97  24.9 24.9
2   JPM FNCI    1.5     2020    Dec            11.58 14.51  19.00  5.0  5.0
3   JPM FNCI    1.5     2020    Feb            6.70  4.18   9.84   6.6  8.8
4   JPM FNCI    1.5     2020    Jan            4.29  10.19  12.88  6.6  5.0

存在问题 #

需要在透视结果中保留Bal到Refi%之间的所有属性列，但直接把这些列加入pivot()的index参数会出现异常：同一分组下这些属性值存在小幅波动，会生成大量冗余行，同时M~M-2的CPR列出现大量空值（NaN）。比如加入Bal列后的异常结果：

SRC Ticker  Coupon  Vintage Month_Assessed  Bal ($bn)   M   M+1 M+2 M-1 M-2
0   JPM     FNCI    1.5 2020    Apr            27.2   NaN   NaN NaN NaN 8.9
1   JPM     FNCI    1.5 2020    Apr            27.6   NaN   NaN NaN 8.9 NaN
2   JPM     FNCI    1.5 2020    Apr            28     7.23  NaN NaN NaN NaN
3   JPM     FNCI    1.5 2020    Apr            28.7   NaN  5.13 7.53 NaN NaN
4   JPM     FNCI    1.5 2020    Aug            24.9       NaN   NaN NaN NaN 24.9
... ... ... ... ... ... ... ... ... ... ... ...
7069 JPM    G2SF    5.5 2008    May        1.2        24    21 21 24.3 NaN
7070 JPM    G2SF    5.5 2008    Nov        1.1        23    21 20 23.2  NaN
7071 JPM    G2SF    5.5 2008    Nov        1.3        NaN  NaN  NaN NaN 21.9
7072 JPM    G2SF    5.5 2008    Oct        1.1        21   20 23 24 25  NaN
7073 JPM    G2SF    5.5 2008    Sep        1.1        21   24 25 22 22 23

需要在透视结果中正确添加这些中间列，同时避免冗余行和大量NaN值。

核心逻辑：禁止把存在波动的属性列加入pivot索引参数，拆分两步处理：先单独完成CPR字段的透视，再按分组维度匹配对应属性值，最后合并结果，操作步骤：

• 第一步：保留原有pivot逻辑，生成仅含分组维度和各Month_key对应CPR值的透视表
• 第二步：按相同分组维度（['SRC', 'Ticker', 'Coupon', 'Vintage', 'Month_Assessed']）聚合原表，提取每个分组下Bal到Refi%的属性值，聚合规则按业务需求选择：
  • 优先选Month_key == 'M'对应基准月的属性值，和MBS数据披露逻辑对齐，避免时间差带来的数值波动
  • 若允许小幅统计误差，可对分组内属性取均值、首值、末值
• 第三步：将CPR透视表和聚合得到的属性表按分组维度做左连接，即可得到无冗余行、无多余NaN值的最终结果

实现代码 #

# 保留原有CPR透视逻辑
jpm_cpr_pivot = jpm_2021.pivot(
    index=['SRC', 'Ticker', 'Coupon', 'Vintage', 'Month_Assessed'], 
    columns="Month_key", 
    values="CPR"
).rename_axis(columns=None).reset_index()

# 定义分组键和需要保留的属性列
group_keys = ['SRC', 'Ticker', 'Coupon', 'Vintage', 'Month_Assessed']
attr_cols = ['Bal', 'WAC', 'WAM', 'WALA', 'LNSZ', 'LTV', 'FICO', 'Refi%']

# 提取属性值：优先取基准月(M)的统计值，和CPR基准期对齐
jpm_attr = jpm_2021[jpm_2021['Month_key'] == 'M'][group_keys + attr_cols].drop_duplicates(subset=group_keys)

# 若要使用均值抹平波动，替换上面一行为：
# jpm_attr = jpm_2021.groupby(group_keys)[attr_cols].mean().reset_index()

# 合并结果
jpm_final = jpm_cpr_pivot.merge(jpm_attr, on=group_keys, how='left')

注意：同一资产池在相邻观测月的属性小幅波动是统计时间差导致的，最终宽表不需要保留多份波动值，和基准期对齐即可。

内容的提问来源于stack exchange，提问作者Hefe