How does Python mark the top three data in a column of data in an Excel table in red

user_19540809 0
Viewed 58

This is the code to find the top three

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

Here is the code to change the color

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')

How can I mix the two, my final desired result is to have the top three data marked in red

Any help would be greatly appreciated

openpyxl pandas python
1 Answers
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()

UPDATE:

When you need to style multiple blocks:

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()
user_181607540
Related