why pandas dataframe style lost when saved with "to_excel"?

Question

Per this example the to_excel method should save the Excel file with background color. However, my saved Excel file does not have any color in it. I tried to write using both openpyxl and xlsxwriter engines. In both cases, the Excel file was saved, but the cell color/style was lost.

I can read the file back and reformat with openpyxl, but if this to_excel method is supposed to work, why doesn't it?

Here is the sample code.

 import pandas as pd # version 0.24.2
 dict = {'A': [1, 1, 1, 1, 1], 'B':[2, 1, 2, 1, 2], 'C':[1, 2, 1, 2, 1]}
 df = pd.DataFrame(dict)
 df_styled = df.style.apply(lambda x: ["background: #ffa31a" if x.iloc[0] < v else " " for v in x], axis=1)

 df_styled 
 ''' in my jupyter notebook, this displayed my dataframe with background color when condition is met, (all the 2s highlighted)'''

 '''Save the styled data frame to excel using to_excel'''
 df_styled.to_excel('example_file_openpyxl.xlsx', engine='openpyxl')
 df_styled.to_excel('example_file_xlsxwriter.xlsx', engine='xlsxwriter')

Esquimau

What version of Pandas are you using? – Acutance

I am using '0.24.2'. Thanks! – Esquimau

Personally, I recommend you convert the dataframe to rows and apply any styles you want manually in either openpyxl or xlsxwriter. – Prudery

@ Charlie Clark, Styling a df with pandas seemed an easier way, mainly because i have never worked with openpyxl or xlxswriter before. I am starting to learn openpyxl now for this purpose, which i think is also good. I was mainly curious if i am missing something/ or doing something wrong with df styling & saving using pandas. The example in the documentation seems to be working. Also wondering, If you got same result as mine with the provided code? – Esquimau

In your fourth line, try changing background for background-color. This solved the very same issue for me. – Outbound

Answer 1

I stumbled across this myself and as far as I'm aware there isn't support for exporting to excel like this yet. I've adjusted your code to match the output to excel in the documentation.

This is the documentation output to excel method.

df.style.\
    applymap(color_negative_red).\
    apply(highlight_max).\
    to_excel('styled.xlsx', engine='openpyxl')

This is your code adjusted:

import pandas as pd
dict = {'A': [1, 1, 1, 1, 1], 'B':[2,1,2,1,2], 'C':[1,2,1,2,1]}
df = pd.DataFrame(dict)

def highlight(df, color = "yellow"):

    attr = 'background-color: {}'.format(color)
    df_bool = pd.DataFrame(df.apply(lambda x: [True if x.iloc[0] < v else False for v in x],axis=1).apply(pd.Series),
                      index=df.index)
    df_bool.columns =df.columns
    return pd.DataFrame(np.where(df_bool, attr, ""),
                       index= df.index, columns=df.columns)
df.style. \
    apply(highlight, axis=None).\
    to_excel("styled.xlsx", engine="openpyxl")

Inside the highlight function, I create a boolean dataframe based on the conditions applied in the list comprehension above. Then, I assign styling based on the result of this dataframe.

Smog