多索引条件格式

Question

我有一个multiindex，我希望根据另一个子列值对不同列上的背景颜色进行条件格式设置

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

当使用xlsxwriter导出到excel时，是否可以根据“库存”的值为每个区域的“颜色”列上色？（Y=绿色、=黄色、N=红色）

是否有办法对产品进行定制排序（如家具、用品、产品的订单）

Answer 1

我将回答这个问题的条件格式部分，您可以单独询问多索引部分

您可以对数据帧应用条件格式，如下所示：

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

输出：