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

pandas export-to-excel
user4896331 picture user4896331 · Feb 17, 2017 · Viewed 19.4k times · Source

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

user4896331 picture user4896331 · Feb 21, 2017

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!

Related questions

Export from pandas to_excel without row names (index)?

I'm trying to print out a dataframe from pandas into Excel. Here I am using to_excel() functions. However, I found that the 1st column in Excel is the "index", 0 6/6/2021 0:00 8/6/2021 0:00 1 4/10/2024 0:00 6/10/2024 0:00 2 4/14/2024 0:00 6/14/2024 0:00 Is there any ways to get rid of the first …

Read More
Pandas - Writing an excel file containing unicode - IllegalCharacterError

I have the following code: import pandas as pd x = [u'string with some unicode: \x16'] df = pd.DataFrame(x) If I try to write this dataframe as an excel file: df.to_excel("test.xlsx") Or, if I try …

Read More
pandas cut: how to convert categorical labels to strings (otherwise cannot export to Excel)?

I use pandas.cut() to discretise a continuous variable into a range, and then group by the result. After a lot of swearing because I couldn't figure out what was wrong, I have learnt that, if I don't supply custom …

Read More