Highlighting excel cells based on the cell values using pandas dataframe and xlsxwriter

Question

I have a csv file. After doing certain process, it has to be saved as an excel file.

I am opening it as pandas dataframe and after doing some cleaning (renaming and rearranging columns, dropping few columns), i have to replace null values or if the cell value is "N/A" to "DN". Currently i am using two lines of code for this.

df.replace('', np.nan, inplace = True)
df.replace('N/A', np.nan, inplace = True)
df = df.fillna("DN")

Then, i have to highlight cells which has the value "DN" with yellow color

I am trying with the code mentioned in this post How Do I Highlight Rows Of Data? Python Pandas issue. But in the output excel nothing is getting highlighted. Below is the code i am currently working with

df.replace('', np.nan, inplace = True)
df.replace('N/A', np.nan, inplace = True)
df = df.fillna("NA")
df.index = np.arange(1, len(df) + 1)

def high_color(val):
    color = 'yellow' if val == 'NA' else ''
    return 'color: {}'.format(color)
result = df.style.applymap(high_color)

writer_orig = pd.ExcelWriter(out_name, engine='xlsxwriter')
df.to_excel(writer_orig, sheet_name='report', index=True, index_label="S_No", freeze_panes=(1,1))

workbook  = writer_orig.book
worksheet = writer_orig.sheets['report']
# Add a header format.
header_format = workbook.add_format({
    'bold': True,
    'fg_color': '#ffcccc',
    'border': 1})
    
for col_num, value in enumerate(df.columns.values):
    worksheet.write(0, col_num + 1, value, header_format)
writer_orig.close()

Any kind of suggestions will be greatly helpful.

You have to use result (the Styler) and not df => result.to_excel(...) — Corralien
– Corralien, Commented Jan 24, 2023 at 12:42

Timeless · Accepted Answer · 2023-01-24 13:32:41Z

2

You can't save a Styler Object to an Excel spreadsheet by using pandas.ExcelWriter.

class pandas.ExcelWriter(path, engine=None, date_format=None, datetime_format=None, mode='w', storage_options=None, if_sheet_exists=None, engine_kwargs=None, **kwargs)
Class for writing DataFrame objects into excel sheets.

You need to use worksheet.conditional_format from xlsxwriter to highlight a value in every cell. Also, you can pass na_values as a kwarg to pandas.read_csv to automatically consider a list of values as NaN.

from xlsxwriter.utility import xl_rowcol_to_cell

df = pd.read_csv('/tmp/inputfile.csv', na_values=['', 'N/A']).fillna('DN')

l = df.columns.get_indexer(df.columns).tolist()
xshape = list(map(xl_col_to_name, [e+1 for e in l])) 
max_row, max_col = df.shape

with pd.ExcelWriter("/tmp/outputfile.xlsx") as writer:
    df.to_excel(writer, sheet_name='report', index=True,
                index_label='S_No', freeze_panes=(1,1))
    
    wb = writer.book
    ws = writer.sheets['report']
    
    format_header = wb.add_format({'bold': True, 'fg_color': '#ffcccc', 'border': 1})
    for idx, col in enumerate(['S_No'] + list(df.columns)):
        ws.write(0, idx, col, format_header)  
        
    format_dn = wb.add_format({'bg_color':'yellow', 'font_color': 'black'})
    ws.conditional_format(f'{xshape[0]}2:{xshape[-1]}{str(max_row+1)}',
                                 {'type': 'cell', 'criteria': '==',
                                  'value': '"DN"', 'format': format_dn})

Output :

edited Jan 24, 2023 at 13:32

answered Jan 24, 2023 at 13:01

Timeless

38.3k6 gold badges33 silver badges54 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

joseph_k Over a year ago

This code worked fine for me. But since i already written the code - as mentioned above - i couldn't use this.

Timeless Over a year ago

No worries, it was no more than a proposition to solve your problem ;)

Corralien · Accepted Answer · 2023-01-24 12:55:44Z

1

You have to export to excel with result Styler:

# Demo
def high_color(val):
    return 'background-color: yellow' if val == 'NA' else None

result = df.style.applymap(high_color)
result.to_excel('styler1.xlsx')
df.to_excel('styler2.xlsx')

Export from result

Export from df

answered Jan 24, 2023 at 12:55

Corralien

121k8 gold badges44 silver badges69 bronze badges

1 Comment

Corralien Over a year ago

Glad to help. Sometimes we don't see mistakes anymore when we don't take a break :-)

Collectives™ on Stack Overflow

Highlighting excel cells based on the cell values using pandas dataframe and xlsxwriter

2 Answers 2

2 Comments

1 Comment

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

2 Comments

1 Comment

Your Answer

Sign up or log in

Post as a guest

Linked

Related