基于列间条件移除DataFrame中y系列列的多余非NaN值

原始数据 #

import pandas as pd

# 构造原始DataFrame
df = pd.DataFrame({
    'x': [-17.7, -15.0, -12.5, -12.4, -12.1],
    'y1_x': [-0.785430, pd.NA, pd.NA, pd.NA, pd.NA],
    'y2_x': [pd.NA, pd.NA, pd.NA, pd.NA, 2.227343],
    'y3_x': [pd.NA, pd.NA, 2.138833, 1.721205, 2.227343],
    'y4_x': [pd.NA, -3820.085000, pd.NA, pd.NA, pd.NA],
    'd1': [0.053884, pd.NA, pd.NA, pd.NA, pd.NA],
    'd2': [pd.NA, pd.NA, pd.NA, pd.NA, 0.127343],
    'd3': [pd.NA, pd.NA, 0.143237, 0.251180, 0.440931],
    'd4': [pd.NA, 0.085000, pd.NA, pd.NA, pd.NA]
})

需求说明 #

要求y1_x至y4_x每行仅保留1个非NaN值：

• 若一行存在多个非NaN的y系列值，移除对应d列值更大的那个y系列列的非NaN值（例如第4行d3 > d2，需将y3_x设为NaN）

解决方案 #

通过逐行遍历，结合y列与d列的对应关系，保留对应d值最小的y列值，其余设为NaN：

# 定义y列与d列的映射关系
y_cols = ['y1_x', 'y2_x', 'y3_x', 'y4_x']
d_cols = ['d1', 'd2', 'd3', 'd4']
col_map = dict(zip(y_cols, d_cols))

def process_single_row(row):
    # 筛选当前行非NaN的y列
    valid_y = [col for col in y_cols if pd.notna(row[col])]
    # 只有0或1个有效y值时直接返回
    if len(valid_y) <= 1:
        return row
    # 获取对应d列的值
    d_vals = [row[col_map[col]] for col in valid_y]
    # 找到d值最小的y列
    keep_col = valid_y[d_vals.index(min(d_vals))]
    # 将其他有效y列设为NaN
    for col in valid_y:
        if col != keep_col:
            row[col] = pd.NA
    return row

# 应用处理逻辑到整个DataFrame
result_df = df.apply(process_single_row, axis=1)
print(result_df)

处理后结果 #

x      y1_x      y2_x      y3_x         y4_x        d1        d2        d3        d4
0  -17.7 -0.785430       NaN       NaN          NaN  0.053884       NaN       NaN       NaN
1  -15.0       NaN       NaN       NaN -3820.085000       NaN       NaN       NaN  0.085000
2  -12.5       NaN       NaN  2.138833          NaN       NaN       NaN  0.143237       NaN
3  -12.4       NaN       NaN  1.721205          NaN       NaN       NaN  0.251180       NaN
4  -12.1       NaN  2.227343       NaN          NaN       NaN  0.127343  0.440931       NaN

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