Unable to write dataframes to excel

Question

I am trying to style dataframes and trying to write the results to an excel sheet. But when I try to write the results to excel, it doesn't preserve the styling. Here is what I have tried.

import pandas as pd
df1 = pd.DataFrame({'Data': ["Hello", "Hai", "Hello", "Hai", "Hello", "Hai", "Hello"],'Data1': [10, 20, 30, 20, 15, 30, 45], })
df2 = pd.DataFrame({'Data': ["Hello", "Hai", "Hello", "Hai", "Hello", "Hai", "Hello"],'Data1': [10, 20, 30, 20, 15, 30, 45], })
data_to_be_colored = "Hello"

df1 = df1.style.apply(lambda x: ['background:lightblue' if x == data_to_be_colored else 'background:lightgrey' for x in df1.Data], axis=0)

df3 = {'Test 1': df1, 'Test 2': df2}

writer = pd.ExcelWriter(r'Styled_Excel.xlsx')
for sheetname, df in df3.items():
    df.to_excel(writer, sheet_name=sheetname, index = False)
    worksheet = writer.sheets[sheetname]
writer.save()

Receiving Error:

AttributeError: 'Styler' object has no attribute 'style'

Can someone tell me what's wrong with the above code and how to write to excel preserving the styles?

Perhaps not the answer, but where is "df" defined in "df1 = df.style.apply(lambda x: ['background:lightblue' if x == data_to_be_colored else 'background:lightgrey' for x in df.Data], axis=0)"? It seems like you only have df1 and df2. — formicaman, Jun 06 '20 at 15:37
There is still one df in your code. Also I get an error when I run the line with apply. — DavideBrex, Jun 06 '20 at 16:25

DavideBrex · Accepted Answer · 2020-06-07T09:35:35.087

I tried to reproduce the answer from here here. It seems like the problem is that you need to set background-color instead of background.

from IPython.display import HTML

def highlight(x):
    r = '#ADD8E6'
    g = '#B0B0B0'

    m1 = x["Data"] == "Hello"
    m2 = x["Data"] != "Hello"
    df1 = pd.DataFrame('', index=x.index, columns=x.columns)
    for col in x:
        df1[col] = np.where(m1, 'background-color: {}'.format(r), df1[col])
        df1[col] = np.where(m2, 'background-color: {}'.format(g), df1[col])

    return df1

df1.style.apply(highlight, axis=None).to_excel('df.xlsx', engine='openpyxl')

Output:

If I try with lightblue and lightgray it does not work though, so I tried with HTML colors.

Update

In case you want to color different columns, try this:

def highlight(x):
    r = '#ADD8E6'
    g = '#B0B0B0'
    red  = "#FF0000"

    m1 = x["A"] == "Hello"
    m2 = x["A"] != "Hello"
    df1 = pd.DataFrame('', index=x.index, columns=x.columns)
    for col in x[["A","B"]]:
        df1[col] = np.where(m1, 'background-color: {}'.format(r), df1[col])
        df1[col] = np.where(m2, 'background-color: {}'.format(g), df1[col])

    for col in x[["C","D"]]:
        df1[col] = 'background-color: {}'.format(red)

    return df1

df1.style.apply(highlight, axis=None)

Output:

Thank you, can't we select range instead of selecting every individual columns ? Say for example, if I waned to select large number of columns, the function will go very lengthy. — Maryo David, Jun 06 '20 at 20:12

score 0 · Answer 2 · answered Jun 06 '20 at 17:20

Try this:

import pandas as pd
df1 = pd.DataFrame({'Data': ["Hello", "Hai", "Hello", "Hai", "Hello", "Hai", "Hello"],'Data1': [10, 20, 30, 20, 15, 30, 45], })
df2 = pd.DataFrame({'Data': ["Hello", "Hai", "Hello", "Hai", "Hello", "Hai", "Hello"],'Data1': [10, 20, 30, 20, 15, 30, 45], })
data_to_be_colored = "Hello"

df1 = df1.style.applymap(lambda x: 'background-color : lightblue' if x == data_to_be_colored else '', subset = ['Data'])

df3 = {'Test 1': df1, 'Test 2': df2}

writer = pd.ExcelWriter(r'Styled_Excel.xlsx')
for sheetname, df in df3.items():
    df.to_excel(writer, sheet_name=sheetname, index = False)
    worksheet = writer.sheets[sheetname]
writer.save()

You need the () after save and can specify a specific column you want to highlight with subset.

Unable to write dataframes to excel

2 Answers2