[PYTHON] [openpyxl] What to do when IllegalCharacterError appears in pandas.DataFrame.to_excel

Prerequisites

• Python3.5
• Pandas0.19.1
• openpyxl2.4.1

The problem you want to deal with this time

import pandas
target_df = pandas.DataFrame(Given data)

So, when I try to output this data frame to an Excel file,

---------------------------------------------------------------------------
IllegalCharacterError                     Traceback (most recent call last)
<ipython-input-88-d629d1ba2e9a> in <module>()
      1 # subset_df = subset_df.applymap(illegal_char_remover)
      2 
----> 3 subset_df.to_excel("./test.xlsx")

/Users/noname/.pyenv/versions/3.5.2/lib/python3.5/site-packages/pandas/core/frame.py in to_excel(self, excel_writer, sheet_name, na_rep, float_format, columns, header, index, index_label, startrow, startcol, engine, merge_cells, encoding, inf_rep, verbose)
   1462         formatted_cells = formatter.get_formatted_cells()
   1463         excel_writer.write_cells(formatted_cells, sheet_name,
-> 1464                                  startrow=startrow, startcol=startcol)
   1465         if need_save:
   1466             excel_writer.save()

/Users/noname/.pyenv/versions/3.5.2/lib/python3.5/site-packages/pandas/io/excel.py in write_cells(self, cells, sheet_name, startrow, startcol)
   1313                 column=startcol + cell.col + 1
   1314             )
-> 1315             xcell.value = _conv_value(cell.val)
   1316 
   1317             style_kwargs = {}

/Users/noname/.pyenv/versions/3.5.2/lib/python3.5/site-packages/openpyxl/cell/cell.py in value(self, value)
    290     def value(self, value):
    291         """Set the value and infer type and display options."""
--> 292         self._bind_value(value)
    293 
    294     @property

/Users/noname/.pyenv/versions/3.5.2/lib/python3.5/site-packages/openpyxl/cell/cell.py in _bind_value(self, value)
    189 
    190         elif isinstance(value, STRING_TYPES):
--> 191             value = self.check_string(value)
    192             self.data_type = self.TYPE_STRING
    193             if len(value) > 1 and value.startswith("="):

/Users/noname/.pyenv/versions/3.5.2/lib/python3.5/site-packages/openpyxl/cell/cell.py in check_string(self, value)
    154         value = value[:32767]
    155         if next(ILLEGAL_CHARACTERS_RE.finditer(value), None):
--> 156             raise IllegalCharacterError
    157         return value
    158 

IllegalCharacterError: 

The problem of getting angry. Even if I searched, there was no workaround, so I will post it.

approach

IllegalCharacter is defined in openpyxl as follows. (https://bitbucket.org/openpyxl/openpyxl/src/dad834128adcb5ca4330bd2bc4bc714b54ccfbb9/openpyxl/cell/cell.py?at=default&fileviewer=file-view-default Line69)

ILLEGAL_CHARACTERS_RE = re.compile(r'[\000-\010]|[\013-\014]|[\016-\037]')

So I think I should remove this IllegalCharacter from the data frame.

import re

# ILLEGAL_CHARACTERS_RE = re.compile(r'[\000-\010]|[\013-\014]|[\016-\037]')
ILLEGAL_CHARACTERS_RE = re.compile(r'[\000-\010]|[\013-\014]|[\016-\037]|[\x00-\x1f\x7f-\x9f]|[\uffff]')
def illegal_char_remover(data):
    """Remove ILLEGAL CHARACTER."""
    if isinstance(data, str):
        return ILLEGAL_CHARACTERS_RE.sub("", data)
    else:
        return data

target_df = target_df.applymap(illegal_char_remover)

So, if you remove it, this error will not occur.

This fall (solved)

After removing \ uffff, the error below no longer appears.

~~ Unfortunately, even if you remove this error, at the time of export ~~

ValueError: All strings must be XML compatible: Unicode or ASCII, no NULL bytes or control characters

Please note that you may need to make some improvements as you will get angry. ~~ ~~ A story about cleaning data before experimenting. ~~

Postscript

This is my first post, so if you have any problems, please point it out. Then.