Pandas：如何在for循环中应用映射

Question

我有一个python脚本，它从Provsvar-fixed.xlsx读取原始数据

并选择列并保存到Provsvar-summary.xlsx

这些相关列在config.xlsx中定义如下：

写入Provsvar-summary.xlsx时，如果Provsvar-fixed.xlsx中的单元格值不在范围（LabReference.min，LabReference.Max）（在config.xlsx中定义）内，则该单元格将高亮显示

下面的脚本工作得很好，但很难看，因为有太多的样板代码.applymap(lambda x: get_style(x, lab_references[1]), subset=[lab_references[1].name])。 如果要在config.xlsx中添加新行，我必须添加新行脚本代码.applymap(lambda x: get_style(x, lab_references[23]), subset=[lab_references[23].name])，这很烦人

import pandas as pd


origin_provsvar = 'Provsvar-fixed.xlsx'
output_file = 'provsvar-summary.xlsx'
config = pd.read_excel("config.xlsx")


class LabReference:
    def __init__(self, name, min, max):
        self.name = name
        self.min = min
        self.max = max


# Provsvar-fixed
provsvar = pd.read_excel(origin_provsvar, sheet_name='Sheet1')

bad_style_low = 'background-color: #FFFB00'
bad_style_high = 'background-color: #FFC7CE'


def get_style(val, lab_reference):
    if not isinstance(val, int) and not isinstance(val, float):
        return ''
    if val < lab_reference.min:
        return bad_style_low
    elif val > lab_reference.max:
        return bad_style_high
    else:
        return ''


lab_references = []
for i, row in config.iterrows():
    '''
    Name                                Min Max
    Datum       
    S-ACE (E/L)                         0   70
    P--25-OH Vitamin D2+D3 (nmol/L)     50  250
    S-1,25-OH-Vitamin D (pmol/L)        48  190
    ......
    
    '''
    lab_item = LabReference(row['Name'], row['Min'], row['Max'])
    lab_references.append(lab_item)


for row in provsvar.index:
    for lab_item in lab_references:
        value = provsvar[lab_item.name].at[row]
        if isinstance(value, str):
            # Fix abnormal cell: "0,33, 0,33", convert "," to "."
            provsvar[lab_item.name].at[row] = float(value.split(", ")[0].replace(",", "."))

headers = list(map(lambda x: x.name, lab_references))

provsvar_new = pd.DataFrame(provsvar, columns=headers)


# ugly code 
provsvar_new.style.set_properties(**{'background-color': 'white',
                                     'color': 'black',
                                     'border-color': 'black',
                                     'border-width': '1px',
                                     'border-style': 'solid'}) \
    .applymap(lambda x: get_style(x, lab_references[1]), subset=[lab_references[1].name]) \
    .applymap(lambda x: get_style(x, lab_references[1]), subset=[lab_references[1].name]) \
    .applymap(lambda x: get_style(x, lab_references[2]), subset=[lab_references[2].name]) \
    .applymap(lambda x: get_style(x, lab_references[3]), subset=[lab_references[3].name]) \
    .applymap(lambda x: get_style(x, lab_references[4]), subset=[lab_references[4].name]) \
    .applymap(lambda x: get_style(x, lab_references[5]), subset=[lab_references[5].name]) \
    .applymap(lambda x: get_style(x, lab_references[6]), subset=[lab_references[6].name]) \
    .applymap(lambda x: get_style(x, lab_references[7]), subset=[lab_references[7].name]) \
    .applymap(lambda x: get_style(x, lab_references[8]), subset=[lab_references[8].name]) \
    .applymap(lambda x: get_style(x, lab_references[9]), subset=[lab_references[9].name]) \
    .applymap(lambda x: get_style(x, lab_references[10]), subset=[lab_references[10].name]) \
    .applymap(lambda x: get_style(x, lab_references[11]), subset=[lab_references[11].name]) \
    .applymap(lambda x: get_style(x, lab_references[12]), subset=[lab_references[12].name]) \
    .applymap(lambda x: get_style(x, lab_references[13]), subset=[lab_references[13].name]) \
    .applymap(lambda x: get_style(x, lab_references[14]), subset=[lab_references[14].name]) \
    .applymap(lambda x: get_style(x, lab_references[15]), subset=[lab_references[15].name]) \
    .applymap(lambda x: get_style(x, lab_references[16]), subset=[lab_references[16].name]) \
    .applymap(lambda x: get_style(x, lab_references[17]), subset=[lab_references[17].name]) \
    .applymap(lambda x: get_style(x, lab_references[18]), subset=[lab_references[18].name]) \
    .applymap(lambda x: get_style(x, lab_references[19]), subset=[lab_references[19].name]) \
    .applymap(lambda x: get_style(x, lab_references[20]), subset=[lab_references[20].name]) \
    .applymap(lambda x: get_style(x, lab_references[21]), subset=[lab_references[21].name]) \
    .applymap(lambda x: get_style(x, lab_references[22]), subset=[lab_references[22].name]) \
    .to_excel(output_file, engine='openpyxl', sheet_name='summary', index=False)

# ugly code 
print("complete")

我试图通过将applymap放在下面这样的for循环中来替换# ugly code，但根本不起作用

for lab_reference in lab_references:
    print(lab_reference.name, lab_reference.max)
    provsvar_new.style.set_properties(**{'background-color': 'white',
                                         'color': 'black',
                                         'border-color': 'black',
                                         'border-width': '1px',
                                         'border-style': 'solid'}) \
        .applymap(lambda x: get_style(x, lab_reference), subset=[lab_reference.name])

provsvar_new.to_excel(output_file, engine='openpyxl', sheet_name='summary', index=False)

我是新来的熊猫，希望有人能帮我找出如何简化代码

Answer 1

        provsvar_new.style.set_properties(**{'background-color': 'white',
                                         'color': 'black',
                                         'border-color': 'black',
                                         'border-width': '1px',
                                         'border-style': 'solid'}) \
.apply(lambda x: x.map(lambda y: get_style(y, config[config.name == x.name]))) 

希望这能奏效。这是短的和动态的，但仍然丑陋，我想。 为了改进，您可以让get_样式func接受col作为arg并迭代这些值

Answer 2

您可以对循环使用范围，如下所示：

for i in range(0,23):

    provsvar_new.style.set_properties(**{'background-color': 'white',
                                 'color': 'black',
                                 'border-color': 'black',
                                 'border-width': '1px',
                                 'border-style': 'solid'}) \
    .applymap(lambda x: get_style(x, lab_references[i]), subset=[lab_references[i].name])


provsvar_new.to_excel(output_file, engine='openpyxl', sheet_name='summary', index=False)