what is the easiest way to subtract 2 columns of float in dataframe?

Question

Let's say I have a dataframe with 2 columns of floats with 4 or 5 digits of pricision as below:

dt = pd.DataFrame({"OPEN": [-0.00011,-0.0114, 0.0066,-0.0044,-0.0012,-0.0005,
                             0.0005,-0.0037, -0.0029, 0.0034, 0.0003, 0.0001 ],
                   "CLOSE": [-1.20011,-3.0114, 0.4066,-0.0074,-0.0016,-5.0005,
                             0.0225,-0.0027, -0.0026, 0.0064, 0.0043, 4.0001 ],})

and I would like to obtain dt["delta"]=dt["OPEN"] - dt["CLOSE"] correctly. However, as these columns are floats then the result value is not precise! so, for example 0.0003-0.0002 should be 0.0001 however it gives me 0.0000999999999999999

Here is my curruent solution, but it does not work!

from decimal import *
getcontext().prec = 6

delta = []
for i in np.arange((dt.size)-1):

    print("CLOSE: ",Decimal(dt.loc[i,"Close"]))
    delta.append(Decimal(dt.loc[i,"Close"]) - Decimal(dt.loc[i,"Open"]))

print("delta",delta)

My question is, how can i subtract the "CLOSE" and "OPEN" columns correctly ?

Point:

When i run my code, i got the following as print. i am wondering, why the Decimal values still have 52 digits, meanwhile i speicified the prec==6

`CLOSE:  1.0578000000000000735411731511703692376613616943359375

CLOSE:  1.046300000000000007815970093361102044582366943359375

CLOSE:  1.052799999999999958077978590154089033603668212890625

CLOSE:  1.0484999999999999875655021241982467472553253173828125

CLOSE:  1.0471999999999999086952584548271261155605316162109375

CLOSE:  1.0464999999999999857891452847979962825775146484375

CLOSE:  1.047099999999999919708670859108678996562957763671875`

What do you mean correctly? Floats will never be precised. And what do you mean your solution doesn't work? — drum, Nov 27 '21 at 14:50
so, for example `0.0003-0.0002` should be `0.0001` however it gives me `0.0000999999999999999` — Jeff, Nov 27 '21 at 14:53
https://stackoverflow.com/questions/588004/is-floating-point-math-broken — Dani Mesejo, Nov 27 '21 at 15:01

score 1 · Answer 1 · answered Nov 27 '21 at 15:03

Setting getcontext().prec = 6 when using the decimal module should work:

In [1]: import pandas as pd
   ...: from decimal import Decimal, getcontext
In [2]: getcontext().prec = 6
In [3]: df = pd.DataFrame({
   ...:     "OPEN": [
   ...:         -0.00011, -0.0114, 0.0066, -0.0044, -0.0012, -0.0005, 0.0005, -0.0037,
   ...:         -0.0029, 0.0034, 0.0003, 0.0001
   ...:     ],
   ...:     "CLOSE": [
   ...:         -1.20011, -3.0114, 0.4066, -0.0074, -0.0016, -5.0005, 0.0225, -0.0027,
   ...:         -0.0026, 0.0064, 0.0043, 4.0001
   ...:     ],
   ...: })
In [4]: df
Out[4]: 
       OPEN    CLOSE
0  -0.00011 -1.20011
1  -0.01140 -3.01140
2   0.00660  0.40660
3  -0.00440 -0.00740
4  -0.00120 -0.00160
5  -0.00050 -5.00050
6   0.00050  0.02250
7  -0.00370 -0.00270
8  -0.00290 -0.00260
9   0.00340  0.00640
10  0.00030  0.00430
11  0.00010  4.00010
In [5]: df["DELTA"] = df["OPEN"].apply(Decimal) - df["CLOSE"].apply(Decimal)
In [6]: df
Out[6]: 
       OPEN    CLOSE         DELTA
0  -0.00011 -1.20011       1.20000
1  -0.01140 -3.01140       3.00000
2   0.00660  0.40660     -0.400000
3  -0.00440 -0.00740    0.00300000
4  -0.00120 -0.00160   0.000400000
5  -0.00050 -5.00050       5.00000
6   0.00050  0.02250    -0.0220000
7  -0.00370 -0.00270   -0.00100000
8  -0.00290 -0.00260  -0.000300000
9   0.00340  0.00640   -0.00300000
10  0.00030  0.00430   -0.00400000
11  0.00010  4.00010      -4.00000

what is the easiest way to subtract 2 columns of float in dataframe?

1 Answers1