How can I color the empty rows in and export to an excel file with pandas?-CodePudding

I am trying to automate a few tasks on excel, some include setting the cells without any values in red color (empty cells in my DataFrame dimensions and not outside it), I tried the following after checking previous similar answers:

import pandas as pd

# Create a dataframe
df = pd.read_excel(r'input.xls', sheet_name='sheet1')
print(df)

df.style.applymap(lambda x: 'background-color : yellow' if x>1 else '')


# create excel writer object
writer = pd.ExcelWriter(r'Output.xls')

# write dataframe to excel
df.to_excel(writer)
# save the excel
writer.save()
print('DataFrame is written successfully to Excel File.')

I've also tried other ways like

def color(row):
    if row.isnull().values.any() == True:
        return ['background-color: red'] * len(row)
    return [''] * len(row)

# Apply the function
df.style.apply(color, axis=1)

None of which seem to work, in the console I am getting the proper values printed and I am getting an output file with the additional row enumeration from 0, but nothing is getting colored in the output excel file

My dataset in excel has x by y dimensions and each cell can contain numbers(decimal) or text depending on the column name

CodePudding user response：

The pandas Styler object is a separate object from the df which creates it. To write out a styled DataFrame to excel we need to use the actual Styler object not df. The easiest way to do this is to use Styler.to_excel:

# Save Styler Object for Later
styler = df.style
# Apply Styles (This can be chained or on separate lines)
styler.applymap(lambda x: 'background-color : yellow' if x > 1 else '')
styler.apply(color, axis=1)
# Export the styler to excel
styler.to_excel('Output.xls', index=False)

Method chaining also works:

df.style \
    .applymap(lambda x: 'background-color : yellow' if x > 1 else '') \
    .apply(color, axis=1) \
    .to_excel('Output.xls', index=False)

*Note: index=False ensures that the DataFrame index is not included in the output. (the "additional row enumeration from 0")

We can also use pd.ExcelWriter with the Styler in a similar way:

# Save Styler Object for Later
styler = df.style
# Apply Styles (This can be chained or on separate lines)
styler.applymap(lambda x: 'background-color : yellow' if x > 1 else '')
styler.apply(color, axis=1)

with pd.ExcelWriter('Output.xls') as writer:
    styler.to_excel(writer, index=False)

As a general improvement, we can set Styles at the DataFrame level by passing axis=None to Styler.apply and performing all modifications in one function:

def color(df_):
    styles_df = pd.DataFrame('', index=df_.index, columns=df_.columns)
    # Color cells yellow where they are greater than 1
    styles_df[df_ > 1] = 'background-color: yellow'
    # Color rows red where there are any null values across rows
    styles_df.loc[df.isnull().any(axis=1), :] = 'background-color: red'
    return styles_df


with pd.ExcelWriter('Output.xls') as writer:
    df.style.apply(color, axis=None).to_excel(writer, index=False)