TypeError: NAN/INF not supported in write_number() without 'nan_inf_to_errors' Workbook() option

Question

I want to save X (ndarray) with dimensions (3960, 225) in excel file (.xlsx). In X I have some missing values (nan). I made a code for it. However, I am getting the error.

Here is the Code:

workbook = xlsxwriter.Workbook('arrays.xlsx')
    worksheet = workbook.add_worksheet()
    row = 0
    for col, data in enumerate(X):
        worksheet.write_column(row, col, data)
    workbook.close()
     df = pd.DataFrame(X)
## save to xlsx file
     filepath = 'my_excel_file.xlsx'
      df.to_excel(filepath, index=False)

Here is the traceback:

Traceback (most recent call last):
  File "C:\Program Files\JetBrains\PyCharm 2020.2.1\plugins\python\helpers\pydev\pydevd.py", line 1448, in _exec
    pydev_imports.execfile(file, globals, locals)  # execute the script
  File "C:\Program Files\JetBrains\PyCharm 2020.2.1\plugins\python\helpers\pydev\_pydev_imps\_pydev_execfile.py", line 18, in execfile
    exec(compile(contents+"\n", file, 'exec'), glob, loc)
  File "C:/Users/Nafees Ahmed/PycharmProjects/Extra_Sensory_Experimetns/main.py", line 475, in <module>
    worksheet.write_column(row, col, data)
  File "C:\Users\Nafees Ahmed\AppData\Local\Programs\Python\Python38\lib\site-packages\xlsxwriter\worksheet.py", line 69, in cell_wrapper
    return method(self, *args, **kwargs)
  File "C:\Users\Nafees Ahmed\AppData\Local\Programs\Python\Python38\lib\site-packages\xlsxwriter\worksheet.py", line 1164, in write_column
    error = self._write(row, col, token, cell_format)
  File "C:\Users\Nafees Ahmed\AppData\Local\Programs\Python\Python38\lib\site-packages\xlsxwriter\worksheet.py", line 481, in _write
    return self._write_number(row, col, *args)
  File "C:\Users\Nafees Ahmed\AppData\Local\Programs\Python\Python38\lib\site-packages\xlsxwriter\worksheet.py", line 589, in _write_number
    raise TypeError(
TypeError: NAN/INF not supported in write_number() without 'nan_inf_to_errors' Workbook() option
 

Perhaps, it is coming due to nan (missing) values. Is there any simple way to handle this error?

Answer 1

Filling NaN values with zero, does not solve the problem, If you want to keep NaN values as NaN, you should skip filling value in like that:

row = 0
for col, data in enumerate(X):
    try:
        worksheet.write_column(row, col, data)
    except:
        pass

Answer 2

You can use fillna method of pandas.

df.fillna(0) will replace all NaN with 0.0