CODEWITHSUNDEEP
pythonpython-3.xpandas
Maryo David
Maryo David

Reputation: 589

Unable to write dataframes to excel

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?

Upvotes: 1

Views: 271

Answers (2)

DavideBrex
DavideBrex

Reputation: 2414

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:

enter image description here

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:

enter image description here

Upvotes: 1

formicaman
formicaman

Reputation: 1357

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.

Upvotes: 0

Related Questions