CODEWITHSUNDEEP
pythonexcelpandasxlsxwriter
jbogart
jbogart

Reputation: 141

Applying conditional formatting to excel column from pandas dataframe

Im trying to make an excel document with multiple sheets and apply conditional formatting to select columns in the sheet, however, for some reason I cannot get the conditional formatting to apply when I open the sheet. 

newexcelfilename= 'ResponseData_'+date+'.xlsx'
exceloutput = "C:\\Users\\jimbo\\Desktop\\New folder (3)\\output\\"+newexcelfilename

print("Writing to Excel file...")
# Given a dict of pandas dataframes
dfs = {'Tracts': tracts_finaldf, 'Place':place_finaldf,'MCDs':MCD_finaldf,'Counties': counties_finaldf, 'Congressional Districts':cd_finaldf,'AIAs':aia_finaldf}

writer = pd.ExcelWriter(exceloutput, engine='xlsxwriter')
workbook  = writer.book
## columns for 3 color scale formatting export out of pandas as text, need to convert to 
number format.
numberformat = workbook.add_format({'num_format': '00.0'})
## manually applying header format
header_format = workbook.add_format({
    'bold': True,
    'text_wrap': False,
    'align': 'left',
    })


for sheetname, df in dfs.items():  # loop through `dict` of dataframes
    df.to_excel(writer, sheet_name=sheetname, startrow=1,header=False,index=False)  # send df to writer
    worksheet = writer.sheets[sheetname]  # pull worksheet object
    for col_num, value in enumerate(df.columns.values):
        worksheet.write(0, col_num, value, header_format)
    for idx, col in enumerate(df):  # loop through all columns
        series = df[col]
        col_len = len(series.name)  # len of column name/header
        worksheet.set_column(idx,idx,col_len)
        if col in ['Daily Internet Response Rate (%)',
                   'Daily Response Rate (%)',
                   'Cumulative Internet Response Rate (%)',
                   'Cumulative Response Rate (%)']:
            worksheet.set_column(idx,idx,col_len,numberformat)
        if col == 'DATE':
            worksheet.set_column(idx,idx,10)
        if col == 'ACO':
            worksheet.set_column(idx,idx,5)
    ## applying conditional formatting to columns which were converted to the 
    numberformat
    if worksheet == 'Tracts':
        worksheet.conditional_format('E2:H11982', {'type':'3_color_scale',
                                    'min_color': 'FF5733',
                                    'mid_color':'FFB233',
                                    'max_color': 'C7FF33',
                                    'min_value': 0,
                                    'max_vallue': 100})

writer.save()

Everything functions properly in the code in terms of resizing column widths and applying the numeric format to the specified columns, however I cannot get the conditional formatting to apply.

Ive tried to search all other questions on stack exchange but I cannot find an answer.

Upvotes: 1

Views: 3556

Answers (1)

jmcnamara
jmcnamara

Reputation: 41644

You have a few syntax errors in the conditional format, such as not specifying the colours in Html format and a typo in max_value. Once those are fixed it should work. Here is a smaller working example based on yours:

import pandas as pd


# Create a Pandas dataframe from some data.
df = pd.DataFrame({'Data': [10, 20, 30, 20, 15, 30, 45]})

# 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']

# Apply a conditional format to the cell range.
worksheet.conditional_format('B2:B8', 
                             {'type': '3_color_scale',
                              'min_color': '#FF5733',
                              'mid_color': '#FFB233',
                              'max_color': '#C7FF33',
                              'min_value': 0,
                              'max_value': 100})

# Close the Pandas Excel writer and output the Excel file.
writer.save()

Output:

enter image description here

Also, this line:

    if worksheet == 'Tracts':

Should probably be:

    if sheetname == 'Tracts':

Upvotes: 1

Related Questions