MultiIndex index conditional formatting

Question

I have a multiindex that I want conditional formatting for the background colour on the different columns based on another sub-column value.

data = {'Product':['Electronic','Electronic','Furniture','Furniture','Electronic','Electronic','Furniture','Furniture','Furniture'],
    'Item':['Phone','Computer','Table','Chair','Phone','Computer','Table','Chair','Couch'],
    'Region':['Canada','Canada','Canada','Canada','USA','USA','USA','USA','USA'],'In Stock':['Y','N','Y','Y','Y','Y','?','Y','Y'],
    'Colour':['Black','Silver','Brown','Black','Black','Black','Black','Black','Black']}

df = pd.DataFrame(data)

df2 = (df.melt(id_vars=['Product','Item','Region'])
    .sort_values(['Region', 'variable'], ascending=[True,False])
    .pivot(index=['Product','Item'], columns=['Region', 'variable'])
    .droplevel(0, axis=1))

Can the columns "Colour" in each Region be coloured based on the value of "In Stock" when exported to excel with xlsxwriter - (Y = Green, ? = Yellow, N = Red)

Is there a way to have a custom sort on the Product (such as the order of Furniture, Supplies, Product)?

score 0 · Answer 1 · answered Nov 05 '20 at 12:46

I'll answer the conditional formatting part of this question and you can ask the multi-index part separately.

You can apply a conditional formatting to your dataframe as follows:

import pandas as pd


data = {'Product': ['Electronic', 'Electronic', 'Furniture',
                    'Furniture', 'Electronic', 'Electronic',
                    'Furniture', 'Furniture', 'Furniture'],

        'Item':['Phone', 'Computer', 'Table', 'Chair', 'Phone',
                'Computer', 'Table', 'Chair', 'Couch'],

        'Region': ['Canada', 'Canada', 'Canada', 'Canada',
                   'USA', 'USA', 'USA', 'USA', 'USA'],

        'In Stock': ['Y', 'N', 'Y', 'Y', 'Y', 'Y', '?', 'Y', 'Y'],

        'Colour': ['Black', 'Silver', 'Brown', 'Black', 'Black',
                   'Black', 'Black', 'Black', 'Black']}

df = pd.DataFrame(data)

# Create a Pandas Excel writer using XlsxWriter as the engine.
writer = pd.ExcelWriter('pandas_conditional.xlsx', engine='xlsxwriter')

# Convert the dataframe to an XlsxWriter Excel object.
df.to_excel(writer, sheet_name='Sheet1')

# Get the xlsxwriter workbook and worksheet objects.
workbook  = writer.book
worksheet = writer.sheets['Sheet1']

# Add some formats for the conditional formatting.

# Light red fill with dark red text.
format1 = workbook.add_format({'bg_color':   '#FFC7CE',
                               'font_color': '#9C0006'})

# Light yellow fill with dark yellow text.
format2 = workbook.add_format({'bg_color':   '#FFEB9C',
                               'font_color': '#9C6500'})

# Green fill with dark green text.
format3 = workbook.add_format({'bg_color':   '#C6EFCE',
                               'font_color': '#006100'})

# Get the range of cell in the dataframe.
(max_row, max_col) = df.shape

# Add some conditional formats.
worksheet.conditional_format(1, 1, max_row, 1, {'type':     'formula',
                                                'criteria': '=$C2="N"',
                                                'format':   format1})

worksheet.conditional_format(1, 1, max_row, 1, {'type':     'formula',
                                                'criteria': '=$C2="?"',
                                                'format':   format2})

worksheet.conditional_format(1, 1, max_row, 1, {'type':     'formula',
                                                'criteria': '=$C2="Y"',
                                                'format':   format3})

writer.save()

Output:

MultiIndex index conditional formatting

1 Answers1