cytomining / cytomining/CytoTable
Verify how large floating-point numbers are exported
- Dominant language
- Python
- Stars
- 21
- Forks
- 6
- Avg merge
- 2d 3h
- Merged PRs (30d)
- 6
Description
While working on `cytominer-database` data comparisons for #30 I noticed variations in how floating point numbers are treated. As an example, the number `3.5215257120407011` may be found within `.tests/data/cytominer-database/data_b/A01-2/Cytoplasm.csv`. This number is extracted as `3.521525712040701` (one decimal place less, through automatic CSV settings in DuckDB interpreted as a DOUBLE). Similarly, `cyotminer-database` appears to change this number to `3.5215257120407006` (three additional decimal places, I believe this is done through Pandas CSV and/or `pd.Series.as_type()` casts).
I found that DuckDB can correctly interpret this number automatically as a [DECIMAL](https://duckdb.org/docs/sql/data_types/numeric.html#fixed-point-decimals) but that this option doesn't appear to be available through the CSV reader yet (planning to open an issue here). As it stands, this number is interpreted as a [DOUBLE](https://duckdb.org/docs/sql/data_types/numeric#floating-point-types), which truncates the data.
I did some work to try and research how this number is interpreted in Python, NumPy, Pandas, DuckDB, and Parquet. Both Python (through Decimal) and NumPy (through [longdouble](https://numpy.org/doc/stable/reference/arrays.scalars.html#numpy.longdouble)) were able to interpret an extended version of the number but not the precise number itself (similar to how `cytominer-database` operated above, adding decimal places). Pandas is able to accurately interpret the number through PyArrow types (specifically [decimal128](https://arrow.apache.org/docs/python/generated/pyarrow.decimal128.html#pyarrow.decimal128)). DuckDB seems to follow the same decimal-style formatting as PyArrow and is able to inference the width + scale when reading the number alone. From an Arrow decimal128 type, Parquet is able to write and read the number accurately through I believe the [decimal logical type](https://github.com/apache/parquet-format/blob/master/LogicalTypes.md#decimal).
See here for a [Google Colab notebook](https://colab.research.google.com/drive/112x55m_YOfid3_Lt8SBdnbShCmoTl76l) with findings (and a [gist backup](https://gist.github.com/d33bs/bf6e71c381e1d8bcc50c4c69e500caf8)).
Contributor guide
Assessment
This issue has not been assessed yet.