Why does the export of a styled pandas dataframe to Excel not work?

Question

I would like to apply the same background color to cells that have for each PEOPLE instance the name and the related name. I have tried to df.style.applymap, it does not return an error but it does not seem to work. Anyone has any ideas why? Thank you.

   clrs = list(mcolors.CSS4_COLORS.keys())
   for k in range(len(PEOPLE)):
        if PEOPLE[k].attribute == 'child':
            df1_data = [PEOPLE[k].name, PEOPLE[k].related]
    
            df.style.applymap([lambda x: 'background-color: yellow' if x in df1_data else 'background-color: red'])
    

    df.to_excel('styledz.xlsx', engine='openpyxl')

also `df.style` is the object, not `df`, so you need to use `df.style.to_excel`. And also you need not create multiple `df.style` instances. Basically there is a lot wrong with your code — Attack68, Mar 22 '21 at 22:12
Thank you for your answer. If i cannot put df.style in a loop, how can i color multiple cells with different colors? — Christina J, Mar 26 '21 at 08:26
have you tried reading the user guide for Styling at pandas documentation. it has many examples. — Attack68, Mar 26 '21 at 11:08
@ChristinaJ If you find my answer helpful, I'd appreciate it if you accept it or upvote it! — Albo, Apr 07 '21 at 14:23

Albo · Accepted Answer · 2021-03-26T09:22:34.380

3

Here is some more info on df.style. Here I'm using some simple example because I don't have your data available:

import pandas as pd
import numpy as np

df = pd.DataFrame({'a': np.random.randint(0, 10, 10), 'b': np.random.randint(0, 10, 10), 'late': np.random.choice([0, 1], 10).astype(np.bool)})

def highlight_late(s):
    return ['background-color: red' if s['late'] else 'background-color: green' for s_ in s]
df = df.style.apply(highlight_late, axis=1)
df.to_excel('style.xlsx', engine='openpyxl')

Looks in the excel file like this:

For cell based coloring use:

def highlight_late(s):
    return ['background-color: red' if s_ else 'background-color: green' for s_ in s]


df = df.style.apply(highlight_late, subset=["late"], axis=1)

This gives you:

edited Mar 26 '21 at 09:22

answered Mar 26 '21 at 06:55

Albo

1,584
9
27

Thank you for your answer. I would like to know how to color only certain cells with different colors not a whole axis. – Christina J Mar 26 '21 at 08:25
@ChristinaJ I updated the answer to only use one cell for coloring – Albo Mar 26 '21 at 09:23
Thank u! One last comment, what if i want to color cell a[5] and b[2]. I mean what if they are not in the same column? Lets say a[5] and b[2] have the same string written to it. – Christina J Apr 08 '21 at 07:42
`apply([...], axis=1)` works on the whole row, you can simply check in the function `highlight_late` for a keyword and skip the `subset=["late"]` in the `apply` – Albo Apr 08 '21 at 07:52

score 0 · Answer 2 · answered Mar 26 '21 at 11:27

Basically your solution will be a modification of the following:

df = DataFrame([['mark', 2], ['mike', 4], ['manny', 6]], columns=['name', 'attribute']) 
def style_row(row, people):
    output = Series("", index=row.index)
    if row["name"] in people:
        output['attribute'] = "background-color:red;"
    return output
styler = df.style.apply(style_row, axis=1, people=['mark', 'manny'])
styler

Why does the export of a styled pandas dataframe to Excel not work?

2 Answers2

Linked