I have a pivot table created using pandas which looks like below:
**Account** AA-PRD AB-PRD AC-PRD AD-PRD **Product** 10 20 30 50 PROD1 50 50 60 12 PROD2 44 78 567 678 PROD3 56 234 45 77
I want to apply color for the entire column based on account name starts with. Ex: If account name starts with “AA” color=yellow, if starts with “AB” then color = red
How can I do that in python and save it into excel file? “Account” has been used as “columns” in pd.pivot_table function. Used below code to create the pivot table
df_summary_table = pd.pivot_table(df_final,values=["cost"],index = "Product", columns="Account")
Advertisement
Answer
You can create DataFrame of styles with Styler.apply
and set rows by masks with loc
:
def color(x): c1 = 'background-color: yellow' c2 = 'background-color: red' c = '' m1 = x.columns.str.startswith('AA') m2 = x.columns.str.startswith('AB') df1 = pd.DataFrame(c, index=x.index, columns=x.columns) df1.loc[:, m1] = c1 df1.loc[:, m2] = c2 return df1 (df_summary_table.style.apply(color,axis=None) .to_excel('styled.xlsx', engine='openpyxl', index=False))