Applying the Lambda function for Style data in the excel

Question

I am facing an issue while applying an applymap() with lambda function to replace the existing string from Styler Dataframe. E.g. We have to replace Change with 'yellow' color and New with 'green'. I stuck in applying the function to replace value from Styler Dataframe.

import pandas as pd

out=r'C:\Users\test\changes.xlsx'
df = pd.read_excel(r'C:\Users\test\update.xlsx')

def mycolors(val):
    #print(val)
    stra = 'Changed-'
    stri = 'New-'
    color = 'white'
    if stra in str(val):
        color = 'yellow'
    elif stri in str(val):
        color = 'green'
        #print(type(color))
    return 'background-color: %s' % color


df = df.style.applymap(mycolors)
df = df.applymap(lambda x: str(x).replace('Changed-',''))

#print(df)
df.to_excel(out, header=True, index=False)

That looks correct to me, what problem are you seeing exactly? — afterburner, May 18 '20 at 19:55
It is getting an exception while using applymap() for second time with lambda function, to remove 'Changed-' . It is happening only when I am using lambda along with style function to color excel. — Akshay Math, May 18 '20 at 20:05
Great, would you mind posting the stacktrace for the exception in the question? — afterburner, May 18 '20 at 20:07
Im getting this warning, 'CSSWarning: Ill-formatted attribute: expected a colon in 'nan' CSSWarning,'. But if I run by keeping single applymap it is working for replace function. But there is no change in output, it just highlights without removing the Changed- tag — Akshay Math, May 18 '20 at 20:19

Ehsan · Answer 1 · 2020-05-18T21:12:17.040

0

After the first applymap the dataframe is now a pandas.styler. What you want to do is using the format of styler to access the text of cells:pandas.styler Styler.format: Formats the text display value of cells.

df = df.style.applymap(mycolors)
df = df.format(lambda x: str(x).replace('Changed-',''))

edited May 18 '20 at 21:12

answered May 18 '20 at 20:10

Ehsan

711
2
7
21

it is not working, after writing to xlsx it is not replacing the tag 'Changed-' and there is no error in the code. – Akshay Math May 19 '20 at 03:58

Akshay Math · Accepted Answer · 2020-05-19T19:29:34.890

0

This problem got resolved by using openpyxl, by reopening the excel and replacing the content from it.

Click here

edited May 19 '20 at 19:29

answered May 19 '20 at 19:19

Akshay Math

1
2

Applying the Lambda function for Style data in the excel

2 Answers2