Re: Export table problem
- To: mathgroup at smc.vnet.net
- Subject: [mg30938] Re: [mg30891] Export table problem
- From: Tomas Garza <tgarza01 at prodigy.net.mx>
- Date: Thu, 27 Sep 2001 02:17:00 -0400 (EDT)
- References: <OF544D7872.E7ECB40C-ONC1256AD2.00262E35@icos.be>
- Sender: owner-wri-mathgroup at wolfram.com
Maarten,
You're quite right. I was too hasty in assuming that people only use Excel
outside Mathematica!
I guess the problem has to do with a very complicated subject, viz. the
arithmetic used by Mathematica. After some thinking, I concluded that your
numbers
Round[100*Table[{i,i/5,i/3},{i,1,20}]]/100.
must have different degrees of error when they are computed, so that when
the error is "small" they are exported as they appear in the display, and
otherwise they are exported with as many figures as the precision allows.
This seems to be the case for i = 7, 14 and 19 above. I checked this in the
AddOn NumericalMath`Microscope`, where I found the following illuminating
text: "Numbers on a computer comprise a discrete set. There are gaps between
the numbers and when you do arithmetic the result often is not
representable. When the result falls between two representable numbers the
best that can be done is to use the closest representable number as the
result instead of the correct result. The set of numbers that can be
represented on a computer in floating-point format are commonly referred to
as the set of machine numbers." This led me to calculate the "MachineError"
for your table
In[1]:=
<<NumericalMath`Microscope`
In[2]:=
Table[MachineError[Round[100*x/5]/100.,x\[Rule]j],{j,1,20}]
Out[2]=
{0.25 Ulps,0.25 Ulps,-0.3125 Ulps,0.25 Ulps,-0.1875 Ulps,-0.3125 Ulps,
0.46875 Ulps,0.25 Ulps,0.03125 Ulps,-0.1875 Ulps,0.296875 Ulps,-0.3125
Ulps,
0.078125 Ulps,0.46875 Ulps,-0.140625 Ulps,0.25 Ulps,-0.359375 Ulps,
0.03125 Ulps,0.421875 Ulps,-0.1875 Ulps}
(the difference between two consecutive machine numbers is called an ulp -
"unit in the last place"). Here you can see that the machine errors for j =
7, 14 and 19 are indeed the largest ones (0.46875, 0.46875 and 0.421875,
respectively) in the list, and this is why (I venture to guess, since I am
far from being a computer expert, let alone a Mathematica expert) those 3
particular numbers are exported with so many decimals. We should look
forward to a good, informed explanation from the great gurus.
Now, back to your original problem. The difficulty arises because you want
to keep at most 2 decimal figures, and you want to force this using Round
together with multiplication and division by 100 (this, by the way, is what
I always do - but, then, I only use Excel if at all and this why I have
never have experienced the situation). We see that Mathematica refuses to
export the result with only 2 decimals because of the error problem
mentioned above. One way out (not the only one, I'm sure, but this one seems
to work) is to look at the real digits in the result, throw away all but the
first two decimals, and reconstruct each quotient i/5 and i/3. I propose the
following apparently messy procedure: the function red[r_] takes a number r,
works with the string of its real digits, throws away all but the first two
after rounding, and returns the new expression for r:
red[r_]:=Module[{z=RealDigits[r]},
v={{z[[1,1]], If[z[[1,3]]>=5, z[[1,2]]+1,z[[1,2]]]}, z[[2]]};
y=ToString/@v[[1]];
ToExpression[
If[v[[2]]>0,
ToString/@Take[y,v[[2]]][[1]]<>"."<>
ToString/@Take[y,-(Length[y]-v[[2]])][[1]],
"0"<>"."<>ToString/@v[[1]]]]]
(it's just an exercise in list and string manipulation). I then calculate
the following
In[3]:=
x=Table[{i,red[i/5.],red[i/3.]},{i,1,20}]
Out[3]=
{{1,0.2,0.33},{2,0.4,0.67},{3,0.6,1.},{4,0.8,1.3},{5,1.,1.7},{6,1.2,2.},{7,
1.4,2.3},{8,1.6,2.7},{9,1.8,3.},{10,2.,3.3},{11,2.2,3.7},{12,2.4,4.},{13,
2.6,4.3},{14,2.8,4.7},{15,3.,5.},{16,3.2,5.3},{17,3.4,5.7},{18,3.6,
6.},{19,3.8,6.3},{20,4.,6.7}}
and then
In[4]:=
Export["table.txt",x,"Table"]
Out[4]=
table.txt
Now I read in table.txt from Microsoft Word and it gives exactly what you
wanted! Actually, you don't need the ToExpression part in the function red,
since you are going to use a text editor, where there is no difference
between strings and numbers.
Regards,
Tomas
----- Original Message -----
From: <maarten.vanderburgt at icos.be>
To: mathgroup at smc.vnet.net
Subject: [mg30938] Re: [mg30891] Export table problem
>
> Tomas,
>
> You should open it in a text editor. Depending on your settings, Excel
> round or truncates when you open the txt file.
>
> BR
> maarten
>