5

Can someone please explain what's happening me or am I missing something really obvious?

import pandas as pd
df = pd.DataFrame([[0.0094909865]])
df = df.round(9)
print(df)

This gives me 0.009490986.

I was expecting a rounded value of 0.009490987

Can someone help me understand? I am using Pandas '0.20.3'

seralouk
  • 30,938
  • 9
  • 118
  • 133
Gayathri
  • 274
  • 3
  • 17

1 Answers1

7

Good question. This is an undocumented quirk. Actually, DataFrame.round uses numpy.around. It should be mentioned in pandas documentation.

The numpy.around() does the following:

For values exactly halfway between rounded decimal values, Numpy rounds to the nearest even value. Thus 1.5 and 2.5 round to 2.0, -0.5 and 0.5 round to 0.0, etc.

See the Reference for more details.

To achieve what you want you can do the following:

from math import ceil, floor
import pandas as pd

df = pd.DataFrame([[0.0094909865]])

def float_round(num, places = 0, direction = floor):
    return direction(num * (10**places)) / float(10**places)

print( float_round(df.values,9,ceil) )
print( float_round(df.values,9) )

print( float_round(1.1389182804 ,9) )

Results

0.009490987
0.009490986

1.13891828
seralouk
  • 30,938
  • 9
  • 118
  • 133
  • Thank you @seralouk, that at least helps with me getting my sanity back! I was struggling the whole evening thinking I am missing something fairly obvious, but am glad it wasn't me. – Gayathri May 13 '18 at 21:42
  • any idea how I can achieve what I am setting about to do - plain rounding like excel does? Thanks again. – Gayathri May 13 '18 at 21:43
  • Brilliant, much appreciated. – Gayathri May 14 '18 at 07:49
  • when I attempt to use your function on value 1.1389182804, it gives me 1.138918281 instead of 1.138918280 when i round to 9 decimals? – Gayathri May 14 '18 at 08:00
  • use this: float_round(1.1389182804 ,9) – seralouk May 14 '18 at 08:07
  • This is part of [IEEE 754](https://en.wikipedia.org/wiki/IEEE_754#Rounding_rules) actually, I wouldn't suggest going against the standard. – ayhan May 14 '18 at 09:22
  • 1
    Yes, I agree. I just suggested a way to get what @Gayathri needs. – seralouk May 14 '18 at 09:25
  • Why is this a quirk? It is the mathematical definition of rounding and implemented in Python round() as well. That MS Excel is unaware of mathematics (just try their stats functions) is not Python's problem. – Mr. T May 14 '18 at 09:30
  • I just said that the fact that pandas round uses numpy around is a undocumented quirk. It should be clearly mentioned in pandas documentation (https://pandas.pydata.org/pandas-docs/stable/generated/pandas.DataFrame.round.html) – seralouk May 14 '18 at 10:44
  • The documentation clause you quote isn't strictly relevant here, because the value you're testing is _not_ "exactly halfway between rounded decimal values". The actual value being stored is `0.009490986499999999603804923253846936859190464019775390625`, which is *closer* to `0.009490986` than to `0.009490987`. Having said that, the method used internally by NumPy also potentially introduces intermediate errors. In practice, it's hard to predict reliably which way _apparent_ halfway cases will actually round. – Mark Dickinson May 14 '18 at 18:04