基于其他列条件转换多列值的Pandas数据处理需求

原始DataFrame定义 #

d = {
    'MIN_VALUE': [None, None, None, 1, None, None, None],
    'MAX_VALUE': [None, None, None, None, None, 2, None],
    'OPERATOR': ['<', '>', '=', '>', None, "<", "="],
    'RAW_VALUE': [1, 2, 3, 4, 5, 6, 7],
    'MIN_UNITS_': [None, None, None, None, None, None, None],
    'MAX_UNITS': [None, None, None, None, None, None, None],
    'RAW_UNITS': ['mol/L', 'mol/L', 'mol/L', 'mol/L', 'mol/L', 'mol/L', 'mol/L']
}

转换规则 #

• 当MIN_VALUE和MAX_VALUE均为NaN时：
  • 若OPERATOR为<，则设置MAX_VALUE = RAW_VALUE，且MAX_UNITS = RAW_UNITS
  • 若OPERATOR为>，则设置MIN_VALUE = RAW_VALUE，且MIN_UNITS = RAW_UNITS
  • 若OPERATOR为=，则设置MIN_VALUE与MAX_VALUE均等于RAW_VALUE，同时MIN_UNITS与MAX_UNITS均等于RAW_UNITS

转换函数实现 #

def convert_to_range_values(data: pd.DataFrame) -> pd.Series:
    # 筛选MIN和MAX值均为NaN的行索引
    non_ranged_indices = pd.isna(data["MIN_VALUE_STANDARDIZED"]) & pd.isna(
        data["MAX_VALUE_STANDARDIZED"])

    # 处理OPERATOR为"<"的场景
    data.loc[(data["RAW_VALUE_OPERATOR"] == "<") & non_ranged_indices, 
             "MAX_VALUE_STANDARDIZED"] = data["RAW_VALUE_STANDARDIZED"]
    data.loc[(data["RAW_VALUE_OPERATOR"] == "<") & non_ranged_indices, 
             "MAX_VALUE_UNITS_STANDARDIZED"] = data["RAW_UNITS_STANDARDIZED"]
    
    # 处理OPERATOR为">"的场景
    data.loc[(data["RAW_VALUE_OPERATOR"] == ">") & non_ranged_indices, 
             "MIN_VALUE_STANDARDIZED"] = data["RAW_VALUE_STANDARDIZED"]
    data.loc[(data["RAW_VALUE_OPERATOR"] == ">") & non_ranged_indices, 
             "MIN_VALUE_UNITS_STANDARDIZED"] = data["RAW_UNITS_STANDARDIZED"]

    # 处理OPERATOR为"="的场景
    data.loc[(data["RAW_VALUE_OPERATOR"] == "=") & non_ranged_indices, 
             "MAX_VALUE_STANDARDIZED"] = data["RAW_VALUE_STANDARDIZED"]
    data.loc[(data["RAW_VALUE_OPERATOR"] == "=") & non_ranged_indices, 
             "MIN_VALUE_STANDARDIZED"] = data["RAW_VALUE_STANDARDIZED"]
    data.loc[(data["RAW_VALUE_OPERATOR"] == "=") & non_ranged_indices, 
             "MAX_VALUE_UNITS_STANDARDIZED"] = data["RAW_UNITS_STANDARDIZED"]
    data.loc[(data["RAW_VALUE_OPERATOR"] == "=") & non_ranged_indices, 
             "MIN_VALUE_UNITS_STANDARDIZED"] = data["RAW_UNITS_STANDARDIZED"]

    return non_ranged_indices, data

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