conditional_format using ExcelWriter based on mapped numeric value among adjacent columns of dataframe

I want to write the bellow data frame into excel

I have a dictionary where each string value of the above cell has a numeric value as shown bellow

dict1 = {'Aaa': 1, 'Aa1': 2, 'Aa2': 3, 'Aa3': 4, 'A1': 5, 'A2': 6, 'NR': 7, 'WR': 8, 'Baa2': 9,
                 'Baa3': 10, 'Ba1': 11, 'Ba2': 12, 'Ba3': 13, 'B1': 14, 'B2': 15, 'B3': 16, 'Caa1': 17,
                 'Caa2': 18, 'Caa3': 19, 'Ca': 20}

Now I want to write the data frame to excel using conditional_format (ExcelWriter) such that we compare each cell for example MD Asset and MD Asset-7 get the corresponding number value from dict1 ,

if the numeric value of MD Asset < numeric value of MD Asset-7 then MD Asset cell is green if the numeric value of MD Asset > numeric value of MD Asset-7 then MD Asset cell is red if the numeric value of MD Asset = numeric value of MD Asset-7 then MD Asset cell then no change

So the output that i will write to excel will look like this

So I know we can do conditional formatting on numeric value as bellow but how to go about it in the above case

Appreciate your help

        worksheet.conditional_format(start_row, start_col, end_row, end_cold,
                                     {'type': 'cell',
                                      'criteria': '>',
                                      'value': 20,
                                      'format': format1})

EDITED (tried solution suggested by Alberto)

I tried what was suggested bellow (with a little twist like i used to_excel to save raw data instead Create random data but seems like no color change when I saved the excel

    with pd.ExcelWriter(file_name, engine='xlsxwriter') as writer:

        workbook = writer.book
        # sheets
        sheet_0 = workbook.add_worksheet('Sheet-0')
        # sheet_1 = workbook.add_worksheet('Sheet-1')
        df.to_excel(writer, sheet_name='Sheet-1', index=False)

        # write the dictionary as excel columns
        for row, (key, val) in enumerate(MOODY_RATINGS.items()):
            sheet_0.write(row, 0, key)
            sheet_0.write(row, 1, val)

        sheet_1 = writer.sheets['Sheet-1']
        # define formats
        format1 = workbook.add_format({
            'bg_color': '#FF0000',  # red
            'font_color': '#000000',  # black
        })
        format2 = workbook.add_format({
            'bg_color': '#92D050',  # green
            'font_color': '#000000',  # black
        })
        # define conditional formats
        # https://xlsxwriter.readthedocs.io/working_with_conditional_formats.html
        sheet_1.conditional_format(
            '$C$1:$C$1000', {
                'type': 'formula',
                'criteria': (
                    "=INDEX('Sheet-0'!$B:$B, MATCH(A1, 'Sheet-0'!$A:$A, 0))"
                    " > "
                    "INDEX('Sheet-0'!$B:$B, MATCH(B1, 'Sheet-0'!$A:$A, 0))"
                ),
                'format': format1
            }
        )

        sheet_1.conditional_format(
            '$C$1:$C$1000', {
                'type': 'formula',
                'criteria': (
                    "=INDEX('Sheet-0'!$B:$B, MATCH(A1, 'Sheet-0'!$A:$A, 0))"
                    " < "
                    "INDEX('Sheet-0'!$B:$B, MATCH(B1, 'Sheet-0'!$A:$A, 0))"
                ),
                'format': format2
            }
        )

        # hide the sheet-0
        sheet_1.activate()
        sheet_0.hide()

Solution

we can solve this in 2 ways I guess.

by creating a new 2 columns in the excel that identify either green or red after calculating the numeric values from the dictionary and compare them in the conditional formating method you mentioned.

worksheet.conditional_format(
    start_row, start_col, end_row, end_cold, {
        'type': 'cell',
        'criteria': '>',
        'value': 20,
        'format': format1
    }
)

The other way I can think of is to create a new sheet that has a (key, value) pair in columns A, and B.

then using the match and index functions in excel to extract the value of the mf-asset, .. etc.

and compare them in the conditional formatting.

here is the code:

import xlsxwriter, random

# Create a workbook and add a worksheet.
workbook = xlsxwriter.Workbook('test.xlsx')

# sheets
sheet_0 = workbook.add_worksheet('Sheet-0')
sheet_1 = workbook.add_worksheet('Sheet-1')

# data we want to write to the worksheet.
dict1 = {
    'Aaa': 1, 'Aa1': 2, 'Aa2': 3, 'Aa3': 4, 'A1': 5, 'A2': 6, 'NR': 7, 'WR': 8,
    'Baa2': 9, 'Baa3': 10, 'Ba1': 11, 'Ba2': 12, 'Ba3': 13, 'B1': 14, 'B2': 15,
    'B3': 16, 'Caa1': 17, 'Caa2': 18, 'Caa3': 19, 'Ca': 20,
}

# write the dictionary as excel columns
for row, (key, val) in enumerate(dict1.items()):
    sheet_0.write(row, 0, key)
    sheet_0.write(row, 1, val)

# Create random data
for row in range(random.randint(30, 50)):
    sheet_1.write(row, 0, random.choice([*dict1.keys()]))
    sheet_1.write(row, 1, random.choice([*dict1.keys()]))

# define formats
format1 = workbook.add_format({
    'bg_color':   '#FF0000', # red
    'font_color': '#000000', # black
})

format2 = workbook.add_format({
    'bg_color':   '#92D050', # green
    'font_color': '#000000', # black
})

# define conditional formats
# https://xlsxwriter.readthedocs.io/working_with_conditional_formats.html
sheet_1.conditional_format(
    '$A$1:$A$1000', {
        'type':     'formula',
        'criteria': (
            "=INDEX('Sheet-0'!$B:$B, MATCH(A1, 'Sheet-0'!$A:$A, 0))"
            " > "
            "INDEX('Sheet-0'!$B:$B, MATCH(B1, 'Sheet-0'!$A:$A, 0))"
        ),
        'format':   format1
    }
)

sheet_1.conditional_format(
    '$A$1:$A$1000', {
        'type':     'formula',
        'criteria': (
            "=INDEX('Sheet-0'!$B:$B, MATCH(A1, 'Sheet-0'!$A:$A, 0))"
            " < "
            "INDEX('Sheet-0'!$B:$B, MATCH(B1, 'Sheet-0'!$A:$A, 0))"
        ),
        'format':   format2
    }
)

# hide the sheet-0
sheet_1.activate()
sheet_0.hide()

# save and exit
workbook.close()

excel images:

Rules
formula
Hidden sheet
Hidden sheet structure