New issue

Have a question about this project? Sign up for a free GitHub account to open an issue and contact its maintainers and the community.

By clicking “Sign up for GitHub”, you agree to our terms of service and privacy statement. We’ll occasionally send you account related emails.

Already on GitHub? Sign in to your account

Support writing NaN/Inf #150

Closed
eudoxos opened this Issue Aug 14, 2014 · 17 comments

Comments

4 participants
@eudoxos

eudoxos commented Aug 14, 2014

Witing NaN or Inf values into a cell raises

TypeError: NAN/INF not supported in write_number()

Those numbers should be handled transparently by writing repective values understood by XLSX.

import xlsxwriter
wb=xlsxwriter.Workbook('aa.xlsx')
ws=wb.add_worksheet()
ws.write(0,0,float('nan'))
ws.write(0,1,float('inf'))
wb.close()

As fas as I researched, those values should be written as literal 'NaN' or 'INF' or similar ( see e.g. http://www.mathworks.com/matlabcentral/answers/91798-how-can-i-write-the-nan-values-in-the-matrix-to-excel-using-xlswrite), acccording to other source (like http://office.microsoft.com/en-us/excel-help/number-nan-HA104109973.aspx) NaN could be written as the 0/0 formula.

@jmcnamara jmcnamara added the question label Aug 14, 2014

@jmcnamara jmcnamara self-assigned this Aug 14, 2014

@jmcnamara

This comment has been minimized.

Show comment
Hide comment
@jmcnamara

jmcnamara Aug 14, 2014

Owner

Hi @eudoxos,

Thank you for the clear issue posting and the links.

The main problem with supporting NAN/INF in XlsxWriter is that they aren't supported by Excel.

At least they aren't supported as a number type. They are supported as the result of a formula but they don't have a direct equivalent to the Python number values.

If there was a direct way to implement NAN/INF in Excel I would support it immediately. However, there isn't so some workaround would be required.

Your assessment is also correct about the possible ways to support this:

  1. Convert NAN/INF to strings.
  2. Convert NAN/INF to formulas with an equivalent result.

However, in either of these cases the module would be changing the type of the data. Most users probably wouldn't care but some probably would.

So on balance I think it would be better if users do the conversion themselves prior to writing the data rather than have the module magically do it for them.

I'll leave this issue open since it is an almost FAQ and so that people can see the issues involved.

Thanks for the input and the nice breakdown.

John

Owner

jmcnamara commented Aug 14, 2014

Hi @eudoxos,

Thank you for the clear issue posting and the links.

The main problem with supporting NAN/INF in XlsxWriter is that they aren't supported by Excel.

At least they aren't supported as a number type. They are supported as the result of a formula but they don't have a direct equivalent to the Python number values.

If there was a direct way to implement NAN/INF in Excel I would support it immediately. However, there isn't so some workaround would be required.

Your assessment is also correct about the possible ways to support this:

  1. Convert NAN/INF to strings.
  2. Convert NAN/INF to formulas with an equivalent result.

However, in either of these cases the module would be changing the type of the data. Most users probably wouldn't care but some probably would.

So on balance I think it would be better if users do the conversion themselves prior to writing the data rather than have the module magically do it for them.

I'll leave this issue open since it is an almost FAQ and so that people can see the issues involved.

Thanks for the input and the nice breakdown.

John

@jmcnamara

This comment has been minimized.

Show comment
Hide comment
@jmcnamara

jmcnamara Mar 18, 2015

Owner

Hi,

I don't see any way to progress this issue and I haven't received any other feedback so I'm going to close this issue.

Thank you for your input.

John

Owner

jmcnamara commented Mar 18, 2015

Hi,

I don't see any way to progress this issue and I haven't received any other feedback so I'm going to close this issue.

Thank you for your input.

John

@jmcnamara jmcnamara closed this Mar 18, 2015

@eudoxos

This comment has been minimized.

Show comment
Hide comment
@eudoxos

eudoxos Mar 18, 2015

Opps, sorry, I thought the ball was still on your site ;) Could we re-open?

My favorite would be to convert to formulas with that result (0/0 or 1/0 or whatever that should be).

Since there is currently (AFAIK) no module to read xlsx back, there is no way/need to make sure reading the value back will yield NaN/Inf again.

eudoxos commented Mar 18, 2015

Opps, sorry, I thought the ball was still on your site ;) Could we re-open?

My favorite would be to convert to formulas with that result (0/0 or 1/0 or whatever that should be).

Since there is currently (AFAIK) no module to read xlsx back, there is no way/need to make sure reading the value back will yield NaN/Inf again.

@jmcnamara

This comment has been minimized.

Show comment
Hide comment
@jmcnamara

jmcnamara Mar 18, 2015

Owner

Opps, sorry, I thought the ball was still on your site ;) Could we re-open?

I don't see any point on re-opening this issue since it isn't fixable. The same comments that I made above still apply: Excel doesn't have a native NAN/INF type to map to so some workaround would be required and I think that should be left to the end user to decide.

My favorite would be to convert to formulas with that result (0/0 or 1/0 or whatever that should be).

I am open to adding support for NAN/INF to formula results. I'll look into implementing that.

John

Owner

jmcnamara commented Mar 18, 2015

Opps, sorry, I thought the ball was still on your site ;) Could we re-open?

I don't see any point on re-opening this issue since it isn't fixable. The same comments that I made above still apply: Excel doesn't have a native NAN/INF type to map to so some workaround would be required and I think that should be left to the end user to decide.

My favorite would be to convert to formulas with that result (0/0 or 1/0 or whatever that should be).

I am open to adding support for NAN/INF to formula results. I'll look into implementing that.

John

@jmcnamara jmcnamara reopened this Mar 18, 2015

@jmcnamara

This comment has been minimized.

Show comment
Hide comment
@jmcnamara

jmcnamara Mar 18, 2015

Owner

Re-opening as a feature request to add NAN/INF and other Excel error values to formula results.

Owner

jmcnamara commented Mar 18, 2015

Re-opening as a feature request to add NAN/INF and other Excel error values to formula results.

@jmcnamara

This comment has been minimized.

Show comment
Hide comment
@jmcnamara

jmcnamara Mar 18, 2015

Owner

I had a look into implementing the Excel error codes for formulas. The available values are:

#DIV/0! 
#N/A    
#NAME?  
#NULL!  
#NUM!   
#REF!   
#VALUE! 

See also the StackOverflow question.

Unfortunately none of these really equate to Nan.

Owner

jmcnamara commented Mar 18, 2015

I had a look into implementing the Excel error codes for formulas. The available values are:

#DIV/0! 
#N/A    
#NAME?  
#NULL!  
#NUM!   
#REF!   
#VALUE! 

See also the StackOverflow question.

Unfortunately none of these really equate to Nan.

@eudoxos

This comment has been minimized.

Show comment
Hide comment
@eudoxos

eudoxos Mar 18, 2015

I'd go with #NUM! as NaN (not-a-number), that's what the anwer at SO suggests, and seems intuitive in a way.

eudoxos commented Mar 18, 2015

I'd go with #NUM! as NaN (not-a-number), that's what the anwer at SO suggests, and seems intuitive in a way.

@jmcnamara

This comment has been minimized.

Show comment
Hide comment
@jmcnamara

jmcnamara Mar 18, 2015

Owner

Just to be clear. I'm only going to implement the error code writing as part of write_formula().

It will still be up to you, or other users, to map NAN or INF or whatever to an appropriate formula of your choosing.

Owner

jmcnamara commented Mar 18, 2015

Just to be clear. I'm only going to implement the error code writing as part of write_formula().

It will still be up to you, or other users, to map NAN or INF or whatever to an appropriate formula of your choosing.

jmcnamara added a commit that referenced this issue Mar 18, 2015

Added option to add Excel error codes as formula results.
Added option to add boolean or Excel error codes as formula
results.

Issue #150.
@jmcnamara

This comment has been minimized.

Show comment
Hide comment
@jmcnamara

jmcnamara Mar 19, 2015

Owner

I've pushed a change to master that allows you to add Excel error codes to formulas in order to replicate Excel error conditions:

        worksheet.write_formula('A9',  '#NUM!',   None, '#NUM!')
        worksheet.write_formula('A10', '#REF!',   None, '#REF!')
        worksheet.write_formula('A11', '#VALUE!', None, '#VALUE!')
        worksheet.write_formula('A12', '1/0',     None, '#DIV/0!')

You can use these, if you wish, to map NAN/INF to Excel error values. It would be nice if Excel had proper types to represent these values but unfortunately that isn't the case.

I think this is as much as can be done for this issue so I'm going to close it again.

Thanks for your input.

John

Owner

jmcnamara commented Mar 19, 2015

I've pushed a change to master that allows you to add Excel error codes to formulas in order to replicate Excel error conditions:

        worksheet.write_formula('A9',  '#NUM!',   None, '#NUM!')
        worksheet.write_formula('A10', '#REF!',   None, '#REF!')
        worksheet.write_formula('A11', '#VALUE!', None, '#VALUE!')
        worksheet.write_formula('A12', '1/0',     None, '#DIV/0!')

You can use these, if you wish, to map NAN/INF to Excel error values. It would be nice if Excel had proper types to represent these values but unfortunately that isn't the case.

I think this is as much as can be done for this issue so I'm going to close it again.

Thanks for your input.

John

@jmcnamara jmcnamara closed this Mar 19, 2015

jmcnamara added a commit that referenced this issue Mar 19, 2015

Added option to add Excel error codes as formula results.
Added option to add boolean or Excel error codes as formula
results.

Issue #150.
@eudoxos

This comment has been minimized.

Show comment
Hide comment
@eudoxos

eudoxos Mar 19, 2015

Thanks, though the original problem (passing floats to xlsxwriter and have it do the conversion by itself) is still unresolved. Would it be perhaps possible to add convert_nan_inf (perhaps false by default) similar to e.g. strings_to_numbers and other internal options, and write the error code instead of failing?

eudoxos commented Mar 19, 2015

Thanks, though the original problem (passing floats to xlsxwriter and have it do the conversion by itself) is still unresolved. Would it be perhaps possible to add convert_nan_inf (perhaps false by default) similar to e.g. strings_to_numbers and other internal options, and write the error code instead of failing?

@jmcnamara

This comment has been minimized.

Show comment
Hide comment
@jmcnamara

jmcnamara Mar 19, 2015

Owner

Would it be perhaps possible to add convert_nan_inf (perhaps false by default) similar to e.g. strings_to_numbers and other internal options, and write the error code instead of failing

That might be a workable solution. With suitable documentation and caveats.

I'll look into it.

John

Owner

jmcnamara commented Mar 19, 2015

Would it be perhaps possible to add convert_nan_inf (perhaps false by default) similar to e.g. strings_to_numbers and other internal options, and write the error code instead of failing

That might be a workable solution. With suitable documentation and caveats.

I'll look into it.

John

@jmcnamara jmcnamara reopened this Mar 19, 2015

jmcnamara added a commit that referenced this issue Mar 20, 2015

Added 'nan_inf_to_errors' constructor option
Added 'nan_inf_to_errors' constructor option to turn on conversion
of NAN/INF to Excel error codes.

Issue #150
@jmcnamara

This comment has been minimized.

Show comment
Hide comment
@jmcnamara

jmcnamara Mar 21, 2015

Owner

Released in version 8.7.0 on PyPi.

Let me know if you have any issues.

Thanks for the input.

John

Owner

jmcnamara commented Mar 21, 2015

Released in version 8.7.0 on PyPi.

Let me know if you have any issues.

Thanks for the input.

John

@jmcnamara jmcnamara closed this Mar 21, 2015

@arnav2

This comment has been minimized.

Show comment
Hide comment
@arnav2

arnav2 Jun 10, 2016

How can I write a column of nan in excel ?

arnav2 commented Jun 10, 2016

How can I write a column of nan in excel ?

@dmvianna

This comment has been minimized.

Show comment
Hide comment
@dmvianna

dmvianna Jul 15, 2016

/Volumes/Home/daniel/.virtual_envs/system/lib/python2.7/site-packages/xlsxwriter/worksheet.pyc in write_number(self, row, col, number, cell_format)
    507             else:
    508                 raise TypeError(
--> 509                     "NAN/INF not supported in write_number() "
    510                     "without 'nan_inf_to_errors' Workbook() option")
    511 

TypeError: NAN/INF not supported in write_number() without 'nan_inf_to_errors' Workbook() option
import xlsxwriter
xlsxwriter.__version__
Out[104]:
'0.9.3'

Called by pandas:

writer = ExcelWriter('Reports FY 2010-2011 vs 2011-2012.xlsx')
order_qty_.to_excel(writer, 'order_qty')
writer.save()
import pandas
pandas.__version__
u'0.18.1'

dmvianna commented Jul 15, 2016

/Volumes/Home/daniel/.virtual_envs/system/lib/python2.7/site-packages/xlsxwriter/worksheet.pyc in write_number(self, row, col, number, cell_format)
    507             else:
    508                 raise TypeError(
--> 509                     "NAN/INF not supported in write_number() "
    510                     "without 'nan_inf_to_errors' Workbook() option")
    511 

TypeError: NAN/INF not supported in write_number() without 'nan_inf_to_errors' Workbook() option
import xlsxwriter
xlsxwriter.__version__
Out[104]:
'0.9.3'

Called by pandas:

writer = ExcelWriter('Reports FY 2010-2011 vs 2011-2012.xlsx')
order_qty_.to_excel(writer, 'order_qty')
writer.save()
import pandas
pandas.__version__
u'0.18.1'
@dmvianna

This comment has been minimized.

Show comment
Hide comment
@dmvianna

dmvianna Jul 15, 2016

I tracked it down to a Decimal('NaN') value in a DataFrame cell. No idea how to replace it conveniently, the DataFrame.fillna() method doesn't find it. Actually, not even calling DataFrame.Replace(Decimal('NaN')) or Decimal(NaN) work.

dmvianna commented Jul 15, 2016

I tracked it down to a Decimal('NaN') value in a DataFrame cell. No idea how to replace it conveniently, the DataFrame.fillna() method doesn't find it. Actually, not even calling DataFrame.Replace(Decimal('NaN')) or Decimal(NaN) work.

@jmcnamara

This comment has been minimized.

Show comment
Hide comment
@jmcnamara

jmcnamara Jul 15, 2016

Owner

@dmvianna

This is an old, closed, issue. Open a new issue if you need to.

But first note this part of the error message and see the appropriate section of the docs:

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

See also the Passing XlsxWriter constructor options to Pandas section of the docs.

Owner

jmcnamara commented Jul 15, 2016

@dmvianna

This is an old, closed, issue. Open a new issue if you need to.

But first note this part of the error message and see the appropriate section of the docs:

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

See also the Passing XlsxWriter constructor options to Pandas section of the docs.

@jmcnamara

This comment has been minimized.

Show comment
Hide comment
@jmcnamara

jmcnamara Jul 15, 2016

Owner

Locking this thread.

Owner

jmcnamara commented Jul 15, 2016

Locking this thread.

Repository owner locked and limited conversation to collaborators Jul 15, 2016

Sign up for free to subscribe to this conversation on GitHub. Already have an account? Sign in.