Author Topic: representing nans  (Read 2257 times)

mtsm

  • Newbie
  • *
  • Posts: 13
  • Karma: +0/-0
    • View Profile
representing nans
« on: June 03, 2012, 03:51:38 AM »
hey,

I would like to know if I can have a correspondence in excel to something that represents
a nan in python.

So for example, if I have a numpy array of floats, some of whose elements I have set to
numpy.nan, it would be great if that were translated in excel as #N/A or some other error
code.

It seems that numpy.nan is presently translated as some noise, like I am seeing 2.7E308.

 

Tony

  • Administrator
  • Hero Member
  • *****
  • Posts: 606
  • Karma: +14/-1
    • View Profile
Re: representing nans
« Reply #1 on: June 04, 2012, 01:52:42 PM »
Hi,

numpy uses the IEEE representation of NaN, which is what you're seeing in Excel. Unfortunately Excel doesn't recognize that as NaN.

Have a look at http://www.pyxll.com/docs/udfs.html#passing-errors-as-values to see how to pass Exceptions back to Excel. If you return an array as var and some elements are instances of exceptions they show up as errors in Excel.

Best regards,
Tony

mtsm

  • Newbie
  • *
  • Posts: 13
  • Karma: +0/-0
    • View Profile
Re: representing nans
« Reply #2 on: June 04, 2012, 09:43:05 PM »
yes, I have obviously not yet done enough homework in the pyxll documentation. so I did use float_nan in a numpy_array<float_nan> and indeed numpy.nan will come through to excel as a #NUM!. That is fine, except that excel graphing capabilities don't actually behave a nicely with #NUM!
as they do with#N/A. There would thus be a big advantage to having a way to represent python nans as #N/A in excel.

Regarding your last comment. The issue is not so much returning something as a var to excel, but having for example a calculation on a numpy
array set elements in the array to 'RuntimeError', which in your classification would get returned as a #N/A. Numpy will let you do that, but then
the dtype of the resulting array is 'object'. I am not sure you would want to set the dtype in a numerical calculation to anything else than 'Float',
see?

So I am effectively stuck with the natural numpy.nana to #NUM! translation, which as said is not so nice in excel.

Tony

  • Administrator
  • Hero Member
  • *****
  • Posts: 606
  • Karma: +14/-1
    • View Profile
Re: representing nans
« Reply #3 on: June 06, 2012, 07:56:02 PM »
Hi,

yes I would agree that keeping everything as doubles as much as possible is a good thing to do. You can however convert a numpy array of one type to another using the astype method. That way you can keep everything as doubles and then only at the final stage (in a custom type perhaps) you can replace the nans with whatever exception you like.

Changing the behaviour of float_nan to return #NA! instead of #NUM! is a very simple change, and I'll look at making that in the next release.

Best regards,
Tony