4

Working with currency data in Python Pandas/Numpy, I am experiencing "unexpected" rounding behavior due to the float representation of decimals. For example

import numpy as np    
np.round(19.95 * 0.9, 2)
17.949999999999999

Calculating in decimals as I learned in school, the result is 17.955 so I would expect rounding to 17.96.

How can I represent numbers in Python Pandas to achieve "decimal style" rounding? I have seen the Decimal module and the currency package, but I'm not sure how to efficiently use them with Pandas data frames.

SOLUTION:

In case anybody else is interested, this is how I solved the problem:

from decimal import Decimal
import pandas as pd

def float2dec(value):
    "Convert `value` to Decimal cents."
    return Decimal(value).quantize(Decimal('0.01'))

df = pd.DataFrame({'price': [1.90, 1.95, 19.95]})

df['price_reduced'] = (df['price'].map(float2dec) * float2dec(0.9)).map(float2dec)

#    price      price_reduced
#0  1.90        1.71
#1  1.95        1.76
#2  19.95       17.96

Also, I ended up using the cdecimal implementation, which uses the same syntax but is some 10x faster.

Community
  • 1
  • 1
malte
  • 559
  • 1
  • 8
  • 25
  • The original answer is actually slightly lower than 17.955 (17.9549999999999982946974341757595539093017578125) due to floating point. The rounding down is therefore correct. 17.95 is also not able to be exactly represented in floating point, giving you your final answer of 17.949999999999999. See [Floating Point Arithmetic: Issues and Limitations](https://docs.python.org/3/tutorial/floatingpoint.html). This issue is not specific to Python. – Steven Rumbalski Jan 08 '16 at 16:34
  • Yes, anytime you are working with currency data, *use decimal types* – reptilicus Jan 08 '16 at 20:07
  • Excellent, I had forgotten cdecimal exists. – JBGreen Jan 11 '16 at 17:53

1 Answers1

4

You can use the Decimal type in pandas dataframes. Everything should work as expected since the Decimal type implements it's own versions of addition and multiplication, which pandas will happily use behind the scenes for sum, cumsum, etc.

It won't be as fast as floating points, but using precise decimal representations is never as fast as using floating points.

I wouldn't worry about efficiency unless you actually run into performance problems in your program.

http://rhodesmill.org/brandon/2014/pandas-payroll/

JBGreen
  • 534
  • 2
  • 6