Python pandas统计非空列并在非空数为1时提取对应列值

实现思路 #

1. 先将Found1、Found2两列的空字符串替换为NaN，方便后续非空统计
2. 按行统计两列的非空值数量，直接得到Want_Sum列
3. 对两列做横向向后填充，取每行第一个非空值，再通过条件筛选：仅当该行非空值数量等于1时保留该值，否则返回空字符串，得到Value_of_Found列

完整可运行代码 #

import pandas as pd
import numpy as np

# 原始数据和Found列生成逻辑
data = {"Search1":["one_two","two_ten", "five_ten"],
        "Search2":["three_four","one_four","two_twelve"],
        "FindMe":["three","one","nine"],
        "FindMe2":["five","two","nine"]}
df = pd.DataFrame(data)

df["Found1"] = np.where(df.apply(lambda x: str(x.FindMe) in str(x.Search1), axis =1) ==1, df.Search1,
                np.where(df.apply(lambda x: str(x.FindMe) in str(x.Search2), axis =1) ==1, df.Search2,""))

df["Found2"] = np.where(df.apply(lambda x: str(x.FindMe2) in str(x.Search1), axis =1) ==1, df.Search1,
                np.where(df.apply(lambda x: str(x.FindMe2) in str(x.Search2), axis =1) ==1, df.Search2,""))

# 新增需求实现代码
# 替换空字符串为NaN便于统计
found_cols = ["Found1", "Found2"]
df[found_cols] = df[found_cols].replace("", np.nan)

# 计算非空值数量
df["Want_Sum"] = df[found_cols].notna().sum(axis=1)

# 提取符合条件的非空值
df["Value_of_Found"] = df[found_cols].bfill(axis=1).iloc[:, 0].where(df["Want_Sum"] == 1, "")

# 可选：如果需要把Found列的空值还原回空字符串，执行下面这行
df[found_cols] = df[found_cols].fillna("")

print(df)

输出结果验证 #

运行后生成的Want_Sum和Value_of_Found和示例中的预期值完全一致：

Want_Sum列值为[1,2,0]，Value_of_Found列值为["three_four", "", ""]

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