How to remove illegal characters so a dataframe can write to Excel

Question

I am trying to write a dataframe to an Excel spreadsheet using ExcelWriter, but it keeps returning an error:

openpyxl.utils.exceptions.IllegalCharacterError

I'm guessing there's some character in the dataframe that ExcelWriter doesn't like. It seems odd, because the dataframe is formed from three Excel spreadsheets, so I can't see how there could be a character that Excel doesn't like!

Is there any way to iterate through a dataframe and replace characters that ExcelWriter doesn't like? I don't even mind if it simply deletes them.

What's the best way or removing or replacing illegal characters from a dataframe?

Answer 1

Based on Haipeng Su's answer, I added a function that does this:

dataframe = dataframe.applymap(lambda x: x.encode('unicode_escape').
                 decode('utf-8') if isinstance(x, str) else x)

Basically, it escapes the unicode characters if they exist. It worked and I can now write to Excel spreadsheets again!

Answer 2

The same problem happened to me. I solved it as follows:

1. install python package xlsxwriter:

pip install xlsxwriter

2. replace the default engine 'openpyxl' with 'xlsxwriter':

dataframe.to_excel("file.xlsx", engine='xlsxwriter')

Answer 3

try a different excel writer engine solved my problem.

writer = pd.ExcelWriter('file.xlsx', engine='xlsxwriter')