Error while using to_excel function after applying style.apply

Question

I am getting error in below code. Can someone guide me what needs to be corrected to get the output in excel with the format set using the function highlight_mismatch -

import pandas as pd
df = pd.DataFrame(
    {
        'ID': ['one2', 'one3', 'one3', 'one4' ],
        'Volume': [5.0, 6.0, 7.0, 2.2],
        'BOX-XML1': ['one', 'two', 'three', 'four'],
        'BOX-XML2': ['one', 'two', 'five', 'one hundred'],
        'BOX-RESULT': ['PASS','PASS','FAIL','FAIL'],
        'ROOM-XML1': ['A', 'B', 'C', 'D'],
        'ROOM-XML2':['A','C','B','D'],
        'ROOM-RESULT': ['PASS','FAIL','FAIL','PASS']
    }
)


def highlight_mismatch(result_df):
    df = pd.DataFrame(columns=result_df.columns, index=result_df.index)

    result_column_list = result_df.columns.to_list()
    comparison_column_dict = dict()
    for column in result_column_list:
        if '-RESULT' in column:
            col1 = column.split('-RESULT')[0] + '-XML1'
            col2 = column.split('-RESULT')[0] + '-XML2'
            comparison_column_dict[col1] = col2
            df.loc[result_df[col1].ne(result_df[col2]), [col1, col2]] = 'background: red'
    return df

df = df.astype(str)
df_new = df.style.apply(highlight_mismatch, axis=None)

df_new.to_excel(r'C:\dummy_path\test.xlsx', sheet_name = 'TEST', index=False)

Error - Traceback (most recent call last): File "C:\01_Work\Automation_Codes\01_my_tasks\my_test_project\dataframe_formatting_trials.py", line 33, in df_new.to_excel(r'C:\dummy_path\test.xlsx', sheet_name = 'TEST', index=False) File "C:\Program Files\Python39\lib\site-packages\pandas\io\formats\style.py", line 407, in to_excel formatter.write( File "C:\Program Files\Python39\lib\site-packages\pandas\io\formats\excel.py", line 840, in write writer.write_cells( File "C:\Program Files\Python39\lib\site-packages\pandas\io\excel_xlsxwriter.py", line 227, in write_cells for cell in cells: File "C:\Program Files\Python39\lib\site-packages\pandas\io\formats\excel.py", line 777, in get_formatted_cells for cell in itertools.chain(self._format_header(), self._format_body()): File "C:\Program Files\Python39\lib\site-packages\pandas\io\formats\excel.py", line 677, in _format_regular_rows yield from self._generate_body(coloffset) File "C:\Program Files\Python39\lib\site-packages\pandas\io\formats\excel.py", line 762, in _generate_body styles = self.styler._compute().ctx File "C:\Program Files\Python39\lib\site-packages\pandas\io\formats\style_render.py", line 160, in _compute r = func(self)(*args, **kwargs) File "C:\Program Files\Python39\lib\site-packages\pandas\io\formats\style.py", line 1086, in _apply self._update_ctx(result) File "C:\Program Files\Python39\lib\site-packages\pandas\io\formats\style.py", line 966, in _update_ctx self.ctx[(i, j)].extend(css_list) TypeError: 'float' object is not iterable

Answer 1

Added your condition as separate line c = np.where(result_df[col1] != result_df[col2], 'background-color: red', ''), then applied to that column.

Changed the dataframe name inside function as df1.

def highlight_mismatch(result_df):
    df1 = pd.DataFrame('', columns=result_df.columns, index=result_df.index)

    result_column_list = result_df.columns.to_list()
    comparison_column_dict = dict()
    for column in result_column_list:
        if '-RESULT' in column:
            col1 = column.split('-RESULT')[0] + '-XML1'
            col2 = column.split('-RESULT')[0] + '-XML2'
            comparison_column_dict[col1] = col2
            
            #added conditon            
            c = np.where(result_df[col1] != result_df[col2], 'background-color: red', '')
            df1[col1] = c
            df1[col2] = c

            #df.loc[result_df[col1].ne(result_df[col2]), [col1, col2]] = 'background: red'
    return df1

#df = df.astype(str)
df_new = df.style.apply(highlight_mismatch, axis=None)

df_new.to_excel(r'C:\dummy_path\test.xlsx')