2

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

Henry Ecker
  • 34,399
  • 18
  • 41
  • 57
sara
  • 109
  • 1
  • 7
  • [this](https://stackoverflow.com/questions/43596579/how-to-use-pandas-stylers-for-coloring-an-entire-row-based-on-a-given-column/43601040?noredirect=1#comment133163644_43601040) Q/A is related – Marte Valerio Falcone Feb 20 '23 at 13:53

1 Answers1

3

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)
Henry Ecker
  • 34,399
  • 18
  • 41
  • 57
  • Thanks now they're getting colored but facing a weird issue when i put the condition as styler.applymap(lambda x: 'background-color : yellow' if x ==None else '') it didn't color anything and when i put it as x==0 it colored even the text False but didn't color any empty cells – sara Dec 16 '21 at 18:35
  • So that's more of a general python logic issue unrelated to the actual pandas styling. `== None` is unlikely to give the expected results especially in pandas with the various different ways None-like data can be stored. Additionally it sounds like you have a boolean column. In python `False == 0` evaluates to `True` which is why it's being coloured. – Henry Ecker Dec 16 '21 at 18:36
  • If you need help with specific styles based on specific styling rules you could put together a new question with a sample DataFrame `df = pd.DataFrame({... sample data ...})` then explain how it should be styled. Someone may be able to help you define your styling rules appropriately. – Henry Ecker Dec 16 '21 at 18:38