Applying style to index labels and column labels pandas dataframe

Viewed 101

I have a dataframe which when dump to excel appears as following:

enter image description here

I need to dump to excel with formatting such that it appears as:

enter image description here

i.e. I have a dictionary which is used to apply color to column name and index names.

colorIndex = {'A':'Bb', 'B':'B'}
colorColumn = {'ATC':'X1', 'P25':'Y'}

I am using the following code to generate dataframe and dump to excel:

import pandas as pd, numpy as np, sys, os

def getDF():
   df = pd.DataFrame()
   df['ATC'] =np.random.rand(1, 7).round(2).flatten()
   df['P25'] =np.random.rand(1, 7).round(2).flatten()
   df['P75'] =np.random.rand(1, 7).round(2).flatten()
   df['Type1'] = ['A', 'B', 'B', 'A', 'B', 'B', 'A']
   df['Type11'] = ['A', 'Aa', 'Bb', 'A', 'Bb', 'B', 'Bb']
   df['Type2'] = ['X', 'X', 'X1', 'Y', 'Y', 'Y1', 'Y']
   df = df.pivot_table(index=['Type1', 'Type11'], columns='Type2', aggfunc=[np.mean])['mean']
   return df

df = getDF()
fn = r'C:\Users\Desktop\format_file.xlsx'
df.to_excel(fn, engine='openpyxl')

But I don't have clue how to generate the style parameters for this kind of excel dump.

0 Answers
Related