Decimals type for pandas

Introduction

Some time ago I had a go at implementing a "decimals" extension type for pandas. This was following stumbling upon parquet data of that type, which pandas could not read: pyarrow would error and fastparquet would convert to floats. The decimal type, with known, fixed precision, is very important in real-world applications such as finance, where exact equality of fractional values is required. The following should succeed, but with standard python or pandas does not:

>>> 0.1 + 0.1 + 0.1 == 0.3
False
>>> pd.Series([0.1, 0.1, 0.1]).sum() == 0.3
False

Although one could solve this with python's builtin decimal.Decimal objects, we want something that supports vectorised compute, which will be orders of magnitude faster.

Implementations

I set out make an implementation for discussion. This made use of pandas' "extension types" system, which allows non-standard dtypes and array types to be registered. A decent proof of concept was pretty quick to make (see the repo). However, in conversations around it, it was brought to my attention that the advent of more generalised arrow type support in pandas would expose their decimal type for use in pandas too.

So, if arrow could handle this, then the need for pandas-decimal goes away. I accepted this, but later conversation spurred me to look again. So let's summarise the situation.

Comparisons

Ease of use

From the python perspective, pyarrow interoperates with decimal.Decimal objects; internally it holds an optimised binary representation. Arithmetic with integers is automatic and explicit conversion to/from float is supported, but arithmetic with floats is not supported.

import decimal
import pandas as pd
import dask.dataframe as dd
import pyarrow as pa

# dtype object
pa_dtype = pa.decimal128(precision=7, scale=3)

# creation from decimal objects
data = pa.array(
    [
        decimal.Decimal("8093.012"),
        decimal.Decimal("8094.123"),
        decimal.Decimal("8095.234"),
        decimal.Decimal("8096.345"),
        decimal.Decimal("8097.456"),
        decimal.Decimal("8098.567"),
    ],
    type=pa_dtype,
)
df = pd.DataFrame({"x": data}, dtype=pd.ArrowDtype(pa_dtype))

# explicit conversion
df_float = df.astype("float")
df2 = df_float.astype(pd.ArrowDtype(pa_dtype))
assert df.x.equals(df2.x)

# fail to operate on floats
df.x[0] = 1.1  # ArrowTypeError
df + 1.1  # ArrowTypeError
df + decimal.Decimal("1.1")  # OK

Conversely, pandas-decimal does not handle decimal.Decimal only, but implicitly converts to and from floats on access. This means that arithmetic works as you would normally expect, except you lose the precision guarantees of the decimal representation.

import pandas_decimal

# creation with floats
df = pd.DataFrame(
    {"x": [8093.012, 8094.123, 8095.234, 8096.345, 8097.456, 8098.567]},
    dtype="decimal[3]",
)

# conversion
# explicit conversion
df_float = df.astype("float")
df2 = df_float.astype("decimal[3]")
assert df.x.equals(df2.x)

# operating on floats
df.x[0] = 1.1  # OK
df + 1.1  # OK
df + decimal.Decimal("1.1")  # ValueError

Performance

Comparing the two vectorised array types to python objects and each other, we see clearly that pandas-decimal wins on all operations. I include also just one float calculation, showing that pandas-decimal is even faster than that.

data = [8093.012, 8094.123, 8095.234, 8096.345, 8097.456, 8098.567] * 1000000

# pyarrow
df = pd.DataFrame({"x": data).astype(dtype=pd.ArrowDtype(pa_dtype))
%timeit df.x + 1
16.2 ms ± 85.8 µs
%timeit df.x + decimal.Decimal(0.1)
150 ms ± 1.09 ms
%timeit df.x.mean()
17.5 ms ± 54.1 µs
%timeit df.x.std()  # Error

# pandas-decimal
df = pd.DataFrame({"x":  data}, dtype="decimal[3]")
%timeit df.x + 1
4.62 ms ± 48.5 µs
%timeit df.x + 0.1
4.9 ms ± 530 µs
%timeit df.x.mean()
2.16 ms ± 1.79 µs
%timeit df.x.std()
11 ms ± 119 µs

# pure python
df["x"] = df.x.map(lambda x: decimal.Decimal(str(x)))
%timeit df.x + 1
475 ms ± 4.79 ms
%timeit df.x + decimal.Decimal(0.1)
460 ms ± 7.96 ms
%timeit df.x.mean()
392 ms ± 573 µs
%timeit df.x.mean()  # Error

# inexact floats
df["x"] = df.x.astype("float")
%timeit df.x.mean()
7.6 ms ± 115 µs

IO

pyarrow's decimal column can be saved to/loaded from parquet without conversion, and quickly. Conversion to text (e.g., for CSV) is as slow as you would expect, since it doesn't gain from vectorisation. pandas-decimal is not integrated with any IO. In the original design, it was anticipated that integration with fastparquet would be trivial, and would not need any additional dependencies, but this has not been implemented since development of pandas-decimal halted

Summary

pyarrow does not provide the all-encompassing solution to fixed-precision decimals we would have hoped for - at least not yet. One assumes that the functionality may well already exist within arrow itself, but is not well exposed to pandas due to lack of interest/motivation. This surprised me somewhat, since finance is such a big user of pandas, and has traditionally required exact decimals for instance in their database models.

For the time being, pandas-decimal, a very small-effort proof-of concept, shows clear advantages in speed and flexibility. However, I still don't particuylarly recommend it to anyone for three mean reasons:

  • it is a separate, non-standard install, whereas pyarrow is likely already available in the environments
  • it lacks testing or any support (the status of this in pandas/pyarrow is not known to me)
  • the integration with parquet has not been done

I am hoping, that by writing this post, I will motivate arrow developers to improve decimal support, so that pandas-decimal can be retired even as a proof of concept.

Anaconda