[prev in list] [next in list] [prev in thread] [next in thread]
List: ruby-talk
Subject: Ruby floating point IEEE 754 rounding issue in Excel column
From: Arup Rakshit <aruprakshit () rocketmail ! com>
Date: 2017-07-29 12:55:36
Message-ID: A0B19C7B-5B15-4B7D-ADC1-2DAE85EB01D1 () rocketmail ! com
[Download RAW message or body]
[Attachment #2 (multipart/alternative)]
Hi,
I am reading some floating point numbers from one excel column to other column. While \
it is pasting/inserting the value to new column of the excel, it is rounding the \
values to 1. How can I preserve the actual value?
My rake task:
desc 'pull gnome data in memory'
task :store_in_memory do
puts "Starts caching"
start_row = 2 # should be changed as per the sheet organization
last_row = 2244 # should be changed as per the sheet organization
CAID_CELL = 6
MAF_CELL = 18
sheet = Alleleregistry::SpreadSheet.new('POLG-gnomAD.xlsx')
sheet.each_row(start_row) do |row|
break if row.r > last_row
caid = sheet.read_from_cell(row, CAID_CELL)
maf = sheet.read_from_cell(row, MAF_CELL)
puts maf.inspect
break if row.r > 5
cache_store.put caid, maf.to_f
end
puts "Cache size is: #{cache_store.size}"
puts "Finshed caching"
end
And the SpreadSheet class is
require 'rubyXL'
module Alleleregistry
class SpreadSheet
HGVS_VELL_CELL = 12
CAID_CELL = 13
MAF_CELL = 14
CLIN_VAR_URL_CELL = 15
def initialize path_to_file
@workbook = RubyXL::Parser.parse(File.expand_path("../../data/#{path_to_file}", \
__dir__)) end
def write_to_cell row, col, val
worksheet.add_cell(row, col, val)
end
def read_from_cell row, col
row.cells[col].value
end
def each_row start_row = 1, &block
worksheet.each do |row|
next if row && row.r < start_row
yield row
end
ensure
@workbook.save
end
private
def worksheet
@worksheet ||= @workbook[0]
end
end
end
Here is the full repo of the code: https://gitlab.com/aruprakshit/alleleregistry \
<https://gitlab.com/aruprakshit/alleleregistry>
sample values when it reads from the excel column looks like:
arup@ror ~/Ruby/alleleregistry (master)$ rake spreadsheet:store_in_memory
Starts caching
3.229e-05
0.0003874
0.03139
6.456e-05
0.0001292
Cache size is: 4
Finshed caching
Thanks,
~A
[Attachment #5 (unknown)]
<html><head><meta http-equiv="Content-Type" content="text/html \
charset=us-ascii"></head><body style="word-wrap: break-word; -webkit-nbsp-mode: \
space; -webkit-line-break: after-white-space;" class=""><div class="">Hi,</div><div \
class=""><br class=""></div><div class="">I am reading some floating point numbers \
from one excel column to other column. While it is pasting/inserting the value to new \
column of the excel, it is rounding the values to 1. How can I preserve the actual \
value?</div><div class=""><br class=""></div><div class="">My rake task:</div><div \
class=""><br class=""></div><div class=""><div class="">desc 'pull gnome data in \
memory'</div><div class=""> task :store_in_memory do</div><div class=""> \
puts "Starts caching"</div><div class=""><br class=""></div><div \
class=""> start_row = 2 # should be changed as per the \
sheet organization</div><div class=""> last_row = 2244 # should be \
changed as per the sheet organization</div><div class=""> CAID_CELL = \
6</div><div class=""> MAF_CELL = 18</div><div class=""><br \
class=""></div><div class=""> sheet = \
Alleleregistry::SpreadSheet.new('POLG-gnomAD.xlsx')</div><div class=""> \
sheet.each_row(start_row) do |row|</div><div class=""> break if \
row.r > last_row</div><div class=""><br class=""></div><div class=""> \
caid = sheet.read_from_cell(row, CAID_CELL)</div><div class=""> \
maf = sheet.read_from_cell(row, MAF_CELL)</div><div class=""> \
puts maf.inspect</div><div class=""> break if row.r \
> 5</div><div class=""> cache_store.put caid, \
maf.to_f</div><div class=""> end</div><div class=""><br \
class=""></div><div class=""> puts "Cache size is: \
#{cache_store.size}"</div><div class=""> puts "Finshed \
caching"</div><div class=""> end</div></div><div class=""><br \
class=""></div><div class="">And the SpreadSheet class is </div><div \
class=""><br class=""></div><div class=""><div class="">require 'rubyXL'</div><div \
class=""><br class=""></div><div class="">module Alleleregistry</div><div \
class=""> class SpreadSheet</div><div class=""> HGVS_VELL_CELL \
= 12</div><div class=""> CAID_CELL \
= 13</div><div class=""> MAF_CELL \
= 14</div><div class=""> CLIN_VAR_URL_CELL = 15</div><div \
class=""><br class=""></div><div class=""> def initialize \
path_to_file</div><div class=""> @workbook = \
RubyXL::Parser.parse(File.expand_path("../../data/#{path_to_file}", \
__dir__))</div><div class=""> end</div><div class=""><br \
class=""></div><div class=""> def write_to_cell row, col, val</div><div \
class=""> worksheet.add_cell(row, col, val)</div><div \
class=""> end</div><div class=""><br class=""></div><div class=""> \
def read_from_cell row, col</div><div class=""> \
row.cells[col].value</div><div class=""> end</div><div class=""><br \
class=""></div><div class=""> def each_row start_row = 1, \
&block</div><div class=""> worksheet.each do |row|</div><div \
class=""> next if row && row.r < \
start_row</div><div class=""> yield row</div><div \
class=""> end</div><div class=""><br class=""></div><div \
class=""> ensure</div><div class=""> \
@workbook.save</div><div class=""> end</div><div class=""><br \
class=""></div><div class=""> private</div><div class=""><br \
class=""></div><div class=""> def worksheet</div><div \
class=""> @worksheet ||= @workbook[0]</div><div \
class=""> end</div><div class=""> end</div><div \
class="">end</div></div><div class=""><br class=""></div><div class=""><br \
class=""></div><div class="">Here is the full repo of the code: <a \
href="https://gitlab.com/aruprakshit/alleleregistry" \
class="">https://gitlab.com/aruprakshit/alleleregistry</a></div><div class=""><br \
class=""></div><div class="">sample values when it reads from the excel column looks \
like:</div><div class=""><br class=""></div><div class=""><div class="">arup@ror \
~/Ruby/alleleregistry (master)$ rake spreadsheet:store_in_memory</div><div \
class="">Starts caching</div><div class="">3.229e-05</div><div \
class="">0.0003874</div><div class="">0.03139</div><div class="">6.456e-05</div><div \
class="">0.0001292</div><div class="">Cache size is: 4</div><div class="">Finshed \
caching</div></div><div class=""><br class=""></div><div class=""><br \
class=""></div><br class=""><div class=""> <div style="color: rgb(0, 0, 0); \
font-family: Courier; font-size: 12px; font-style: normal; font-variant-caps: normal; \
font-weight: normal; letter-spacing: normal; orphans: auto; text-align: start; \
text-indent: 0px; text-transform: none; white-space: normal; widows: auto; \
word-spacing: 0px; -webkit-text-size-adjust: auto; -webkit-text-stroke-width: \
0px;">Thanks,</div><div style="color: rgb(0, 0, 0); font-family: Courier; font-size: \
12px; font-style: normal; font-variant-caps: normal; font-weight: normal; \
letter-spacing: normal; orphans: auto; text-align: start; text-indent: 0px; \
text-transform: none; white-space: normal; widows: auto; word-spacing: 0px; \
-webkit-text-size-adjust: auto; -webkit-text-stroke-width: 0px;">~A</div><div \
style="color: rgb(0, 0, 0); font-family: Courier; font-size: 12px; font-style: \
normal; font-variant-caps: normal; font-weight: normal; letter-spacing: normal; \
orphans: auto; text-align: start; text-indent: 0px; text-transform: none; \
white-space: normal; widows: auto; word-spacing: 0px; -webkit-text-size-adjust: auto; \
-webkit-text-stroke-width: 0px;"><br class=""></div><br \
class="Apple-interchange-newline"> </div>
<br class=""></body></html>
Unsubscribe: <mailto:ruby-talk-request@ruby-lang.org?subject=unsubscribe>
<http://lists.ruby-lang.org/cgi-bin/mailman/options/ruby-talk>
[prev in list] [next in list] [prev in thread] [next in thread]
Configure |
About |
News |
Add a list |
Sponsored by KoreLogic