Python如何将Excel表中数据列中的前三个数据标记为红色

Question

这是查找前三个的代码

import pandas as pd
df = pd.read_excel('test.xlsx')
Three = df.nlargest(3, 'AAA', keep='all')

这是更改颜色的代码

import openpyxl
from openpyxl.styles import Font
wb = openpyxl.load_workbook('test.xlsx')
ws = wb.active
font = Font(size=12, bold=True, color="0000FF")
ws.cell(1, 2).font = font
wb.save('after.xlsx')

我如何将两者混合，我最终想要的结果是用红色标记前三个数据

如有任何帮助，将不胜感激

Answer 1

import pandas as pd

# function that returns the style
def set_font_color(x, color):
    return [f'color: {color};' for _ in x]

df = pd.read_excel('test.xlsx')
Three = df.nlargest(3, 'AAA', keep='all')
# excel writer
writer = pd.ExcelWriter(f"after.xlsx", engine="xlsxwriter")
# pandas index (to create slice)
idx = pd.IndexSlice
# slice to apply the styler
slice_ = idx[idx[Three.index], idx['AAA']]
# applying the color to the slice and the new excel
df.style.apply(set_font_color, color='red', subset=slice_).to_excel(writer)
# save
writer.save()

更新：

当您需要对多个块进行样式设置时：

import pandas as pd

# function that returns the style
def set_font_color(x, color, vals):
    return f'color: {color if x in vals else "black"};'

blocks = [
    {'AAA': {'start': 1, 'end': 10}},  # A1:A10
    {'BBB': {'start': 1, 'end': 10}},  # B1:B10
    {'AAA': {'start': 12, 'end': 22}},  # A12:A22
    {'BBB': {'start': 12, 'end': 22}},  # B12:B22
]
df = pd.read_excel('test.xlsx')
# excel writer
writer = pd.ExcelWriter("after.xlsx", engine="xlsxwriter")
# pandas index (to create slice)
idx = pd.IndexSlice
s = None
for block in blocks:
    for col, rows in block.items():
        three = df.loc[rows['start']:rows['end'], :].nlargest(3, col, keep='all')
        # slice to apply the styler
        slice_ = idx[idx[three.index], idx[col]]
        if not s:  # assigning s df styler with new style
            s = df.style.applymap(set_font_color, color='red', vals=df.loc[slice_].values, subset=slice_)
        else:  # assigning s with previous s styler with addition style (to avoid overwritten of previous styles)
            s = s.applymap(set_font_color, color='red', vals=df.loc[slice_].values, subset=slice_)
s.to_excel(writer)
# save
writer.save()