Python pandas如何基于多条件从4列数据提取值生成新列？

问题场景 #

使用Python pandas做数据处理时，需要从3个颜色判断列中提取符合条件的有效值，合并生成新的目标列color，匹配每个color ID对应的实际颜色。

初始测试数据集构造代码 #

import pandas as pd

example = {
"color ID": [1, 2,3, 4, 5],
    "blue colored": ["blue", "not blue", "blue", "not blue", "not blue"],
  "red colored":  ["not red", "red", "not red", "not red", "not red"],
"green colored": ["not green", "not green", "not green", "green", "green"]
}

# 加载为DataFrame
example = pd.DataFrame(example)
print(example) 

预期输出结构 #

需要新增color列，最终结果如下：

expected_result = {
    "color ID": [1, 2,3, 4, 5],
  "blue colored": ["blue", "not blue", "blue", "not blue", "not blue"],
  "red colored":  ["not red", "red", "not red", "not red", "not red"],
"green colored": ["not green", "not green", "not green", "green", "green"],
"color": ["blue", "red", "blue", "green", "green"]
}

expected_result = pd.DataFrame(expected_result)
print(expected_result) 

可行实现方法 #

以下三种方法都可以实现需求，可根据数据量和代码习惯选择：

• 逐行遍历提取法：逻辑直观易理解，适合小数据集

  # 指定需要判断的颜色列
  color_columns = ["blue colored", "red colored", "green colored"]
  # 逐行查找不带"not "前缀的有效值
  example["color"] = example[color_columns].apply(
      lambda row: next(cell_val for cell_val in row if not cell_val.startswith("not ")),
      axis=1
  )
  

• 向量化空值填充法：运行性能最优，适合十万行以上的大数据集

  color_columns = ["blue colored", "red colored", "green colored"]
  # 把所有带"not "前缀的单元格替换为空值
  temp_df = example[color_columns].mask(example[color_columns].apply(lambda col: col.str.startswith("not ")))
  # 按行向后取第一个非空值，即为当前行的实际颜色
  example["color"] = temp_df.bfill(axis=1).iloc[:, 0]
  

• 字符串替换拼接法：代码最简洁，适合规则固定的场景

  color_columns = ["blue colored", "red colored", "green colored"]
  # 把所有带"not "前缀的内容替换为空字符串，按行拼接后剩余的内容就是实际颜色
  example["color"] = example[color_columns].replace("^not .*$", "", regex=True).sum(axis=1)
  

以上三种方法运行后得到的结果和预期输出完全一致，每行只会匹配到一个有效颜色值，不会出现冲突。

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