CODEWITHSUNDEEP
pythonpandas
Doc1FT
Doc1FT

Reputation: 43

Python/Pandas style column headers

I would like to highlight specific column headers before exporting my dataframe to excel.

I have tried using the Pandas Styler to highlight specified columns. 

cm = sns.light_palette("green", as_cmap = True)

etc = etc.style.background_gradient(cmap=cm)

I started with this basic code to highlight my entire dataframe in hopes of adjusting and refining my selection. However, even using this broad approach not all values are highlighted. The desired result is highlighting just the column headers, if it is not possible then just the data associated to the specific header.

Upvotes: 4

Views: 4545

Answers (1)

ASGM
ASGM

Reputation: 11381

Here's an answer based on the documentation for xlsxwriter:

Start by creating an xlsxwriter object and writing the dataframe to it. header=False means that we don't write the column names and startrow=1 leaves a blank row at the top (where we can put our custom columns next). Then we get the relevant objects for the workbook and worksheet.

writer = pd.ExcelWriter("output.xlsx", engine='xlsxwriter')
df.to_excel(writer, sheet_name='Sheet1', startrow=1, header=False)
workbook  = writer.book
worksheet = writer.sheets['Sheet1']

We create a header format:

header_format = workbook.add_format({
    'bold': True,
    'text_wrap': True,
    'valign': 'top',
    'fg_color': '#D7E4BC',
    'border': 1})

Let's say you have three columns, A, B, and C, but only want to highlight A and C. We make a list of the column names we want to highlight and apply the formatting to them selectively:

columns_to_highlight = ['A', 'C']
for col_num, col_name in enumerate(df.columns.values):
    if col_name in columns_to_highlight:
        worksheet.write(0, col_num + 1, col_name, header_format)
    else:
        worksheet.write(0, col_num + 1, col_name)
writer.save()

Upvotes: 8

Related Questions