#1
  1. No Profile Picture
    Registered User
    Devshed Newbie (0 - 499 posts)

    Join Date
    May 2013
    Posts
    4
    Rep Power
    0

    Large number of significant digits in output from python script


    I have a python script, which reads a xls file and writes output into tab delimited file. I modified the xls2txt.py script, which is a part of the pyexcelerator-0.6.4.1 package. My script works fine, except some numbers, not all of them, have large number of significant digits > 10. I have the same case, when I use a perl script. I appreciate any advice on how to preserve the original number of significant digits in python, without checking, if this particular data is a number and format this number.
  2. #2
  3. Contributing User
    Devshed Demi-God (4500 - 4999 posts)

    Join Date
    Aug 2011
    Posts
    4,837
    Rep Power
    480
    You might just need formatting.
    >>> '{:.15f}'.format(1/7)
    '0.142857142857143'


    Then again, you might need the decimal module.

    >>> import decimal
    >>> decimal.Decimal(1/7)
    Decimal('0.142857142857142849212692681248881854116916656494140625')
    [code]Code tags[/code] are essential for python code and Makefiles!
  4. #3
  5. No Profile Picture
    Registered User
    Devshed Newbie (0 - 499 posts)

    Join Date
    May 2013
    Posts
    4
    Rep Power
    0

    Large number of significant digits in output from python script


    Since I cannot post attachment, below is my test data taken from the xls file.

    0.14
    0.06
    0.80
    0.69
    1.14
    9.59
    0.83
    4.15
    1.9
    2.17
    1.65
    4.04
    6.24
    3.91
    3.11
    2.21
    2.61
    1.93
    4.09
    21.27
    5.49

    After I put these data into xls file, and run xls2txt.py script, which is part of a pyexcelerator-0.6.4.1 package, I get the following data.

    extracting data from TestData.xls
    Sheet = "Sheet1"
    ----------------
    (0, 0) = 0.14
    (1, 0) = 0.06000000000000005
    (2, 0) = 0.8
    (3, 0) = 0.69
    (4, 0) = 1.14
    (5, 0) = 9.59
    (6, 0) = 0.83
    (7, 0) = 4.15
    (8, 0) = 1.9
    (9, 0) = 2.17
    (10, 0) = 1.65
    (11, 0) = 4.04
    (12, 0) = 6.24
    (13, 0) = 3.91
    (14, 0) = 3.11
    (15, 0) = 2.21
    (16, 0) = 2.61
    (17, 0) = 1.9300000000000002
    (18, 0) = 4.09
    (19, 0) = 21.27
    (20, 0) = 5.49

    Why am I getting 0.06000000000000005 instead of 0.06 or
    1.9300000000000002 instead of 1.93?

    Python 2.7.1 (r271:86832, Jul 31 2011, 19:30:53)
    [GCC 4.2.1 (Based on Apple Inc. build 5658) (LLVM build 2335.15.00)]
  6. #4
  7. Contributing User
    Devshed Demi-God (4500 - 4999 posts)

    Join Date
    Aug 2011
    Posts
    4,837
    Rep Power
    480
    Oh you have too many digits and you want fewer.

    Consider representing one third in base 10.

    0.33333333333


    Hmm, it's not quite right.

    Your binary computer has the same problem, but in base 2. See link.

    You should expect accurate representation for numbers like 1/2 and 1/16.
    [code]Code tags[/code] are essential for python code and Makefiles!
  8. #5
  9. No Profile Picture
    Registered User
    Devshed Newbie (0 - 499 posts)

    Join Date
    May 2013
    Posts
    4
    Rep Power
    0

    Large number of significant digits in output from python script


    Below are some outputs from python

    Python 2.7.1 (r271:86832, Jul 31 2011, 19:30:53)
    [GCC 4.2.1 (Based on Apple Inc. build 5658) (LLVM build 2335.15.00)] on darwin
    Type "help", "copyright", "credits" or "license" for more information.
    >>> 1./3.
    0.3333333333333333
    >>> 1/16.
    0.0625
    >>> 1/16
    0
    >>> 3.91
    3.91
    >>> 0.58
    0.58
    >>> 1.93
    1.93
    >>> 1/2.
    0.5
    >>> 1/2
    0
    >>>

    I run this on Mac Pro with two Intel processors, but it runs in 32 bit mode. I have received the same result, i.e. extra digits
    while converting xls file into text file using perl xls2tab.pl script.
  10. #6
  11. Contributing User
    Devshed Demi-God (4500 - 4999 posts)

    Join Date
    Aug 2011
    Posts
    4,837
    Rep Power
    480
    Use string formats.

    >>> '{:.2f}'.format(1.0/7)
    '0.14'
    [code]Code tags[/code] are essential for python code and Makefiles!
  12. #7
  13. No Profile Picture
    Contributing User
    Devshed Newbie (0 - 499 posts)

    Join Date
    May 2009
    Posts
    475
    Rep Power
    33
    The simple solution would be to input into a Python program and have it do the calculations instead of a spreadsheet. If you also use the decimal module then problem solved. Also, if you know there will never be more than 2 or 3 places after the decimal, then round and use a string as suggested above, but converting back to a float will yield the same result (possibly-depends on what Excel does and if the number is calculated or entered) so convert from a string to a decimal if you want to do arithmetic on it or use it as a number.
    Code:
    rounded = "%s" % (round(0.06000000000000005, 3))
    print rounded
    print float(rounded)
    
    from decimal import Decimal as dec
    print dec(rounded)
    Last edited by dwblas; May 8th, 2013 at 12:55 PM.
  14. #8
  15. No Profile Picture
    Registered User
    Devshed Newbie (0 - 499 posts)

    Join Date
    May 2013
    Posts
    4
    Rep Power
    0
    I use xls files to enter numerical data of various formats. Afterward, I have to convert a bunch of xls files into tab delimited format. By the way, I saved this xls files as a csv file using LibreOffice and all numbers in output file have two significant digits.

IMN logo majestic logo threadwatch logo seochat tools logo