Coloring pivot table pandas dataframe

Question

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

jezrael · Accepted Answer · 2020-02-03T14:36:52.983

2

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

edited Feb 03 '20 at 14:36

answered Feb 03 '20 at 14:18

jezrael

822,522
95
1,334
1,252

Here "x" is the input pivot table dataframe right? @jezrael – greenking Feb 03 '20 at 14:36
@greenking - no, `df` is `df_summary_table` – jezrael Feb 03 '20 at 14:37
and then in function `x` is also `df_summary_table` – jezrael Feb 03 '20 at 14:37
1

Thanks a lot @ jezrael – greenking Feb 03 '20 at 14:42
Say for example I want to have list from this pivot table like below: list1 = [AA-PRD,AB-PRD,AC-PRD,AD-PRD] , How can I that @jezrael? – greenking Feb 04 '20 at 13:51
@greenking - Do you think `L = df_summary_table.columns.tolist()` ? – jezrael Feb 04 '20 at 13:57

Coloring pivot table pandas dataframe

1 Answers1