如何删除非法字符，以便数据框可以写入Excel [英] How to remove illegal characters so a dataframe can write to Excel

问题描述

我正在尝试使用ExcelWriter将数据框写入Excel电子表格，但它始终返回错误:

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

openpyxl.utils.exceptions.IllegalCharacterError

我猜想数据框中有些字符是ExcelWriter不喜欢的.似乎很奇怪，因为数据框是由三个Excel电子表格组成的，所以我看不到Excel可能不喜欢的字符！

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!

是否有任何方法可以遍历数据框并替换ExcelWriter不喜欢的字符?我什至不介意它是否只是删除它们.

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?

推荐答案

基于苏海鹏的回答，我添加了一个执行此操作的函数:

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)

基本上，如果存在unicode字符，它将转义.它奏效了，现在我可以再次写入Excel电子表格了！

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

这篇关于如何删除非法字符，以便数据框可以写入Excel的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！