BUG: inconsistent behaviour of 'to_timedelta' for float 'arg' and different 'unit'

[miccoli]

Pandas version checks

• [X] I have checked that this issue has not already been reported.

• [X] I have confirmed this bug exists on the latest version of pandas.

• [x] I have confirmed this bug exists on the main branch of pandas.

Reproducible Example

import pandas as pd

td1 = pd.to_timedelta(1.75, unit='ns')
td2 = pd.to_timedelta(1.75e-3, unit='us')

assert td1 == td2, (td1, td2) # fails with AssertionError: (Timedelta('0 days 00:00:00.000000001'), Timedelta('0 days 00:00:00.000000002'))

Issue Description

to_timedelta docs are not clear on how sub nanosecond precision should be rounded/truncated when different unit are used in the conversion.

Expected Behavior

Equivalent definitions [^1] of timedelta should give raise to the same result. Current behaviour seems to be

• truncate if unit='ns'
• round otherwise

[^1]: Here “equivalent“ is meant within float precision, in fact 1.75 == 1.75e-3 * 1000

This is very confusing, and can give raise to small glitches in the conversions.

Please note that this behaviour is not due to the fact that decimal literals have no “exact” float representation:

td1 = pd.to_timedelta(2000 / 1024, unit='ns')
td2 = pd.to_timedelta(2 / 1024, unit='us')

assert td1 == td2, (td1, td2)

still fails with

AssertionError: (Timedelta('0 days 00:00:00.000000001'), Timedelta('0 days 00:00:00.000000002'))

although there is no rounding in 2000 / 1024 and 2 / 1024.

Please note also that truncating sub nanosecond precision is not consistent with datetime.timedelta which has µs resolution. In fact the docs clearly state that

If any argument is a float and there are fractional microseconds, the fractional microseconds left over from all arguments are combined and their sum is rounded to the nearest microsecond using round-half-to-even tiebreaker.

Analogoulsy to_timedelta and the Timedelta constructor should always round to the nearest nanosecond using round-half-to-even tiebreaker when float args are used.

Installed Versions

``` INSTALLED VERSIONS ------------------ commit : a671b5a8bf5dd13fb19f0e88edc679bc9e15c673 python : 3.11.4.final.0 python-bits : 64 OS : Darwin OS-release : 23.1.0 Version : Darwin Kernel Version 23.1.0: Mon Oct 9 21:27:27 PDT 2023; root:xnu-10002.41.9~6/RELEASE_X86_64 machine : x86_64 processor : i386 byteorder : little LC_ALL : None LANG : None LOCALE : None.UTF-8 pandas : 2.1.4 numpy : 1.26.1 pytz : 2023.3.post1 dateutil : 2.8.2 setuptools : 68.1.2 pip : 23.3.2 Cython : None pytest : None hypothesis : None sphinx : None blosc : None feather : None xlsxwriter : None lxml.etree : 4.9.3 html5lib : None pymysql : None psycopg2 : None jinja2 : 3.1.2 IPython : 8.15.0 pandas_datareader : None bs4 : 4.12.2 bottleneck : None dataframe-api-compat: None fastparquet : None fsspec : 2023.9.0 gcsfs : None matplotlib : 3.8.0 numba : 0.59.0rc1 numexpr : None odfpy : None openpyxl : 3.1.2 pandas_gbq : None pyarrow : 13.0.0 pyreadstat : None pyxlsb : None s3fs : None scipy : 1.11.3 sqlalchemy : None tables : None tabulate : None xarray : 2023.8.0 xlrd : 2.0.1 zstandard : None tzdata : 2023.3 qtpy : None pyqt5 : None ```

[miccoli]

Another strange edge case, reported in #46819

>>> pd.to_timedelta(2.5225, 's').asm8
numpy.timedelta64(2522499999,'ns')
>>> decimal.Decimal(2.5225)*10**9
Decimal('2522499999.999999964472863212')

but

>>> pd.to_timedelta(2.5223, 's').asm8
numpy.timedelta64(2522300000,'ns')
>>> decimal.Decimal(2.5223)*10**9
Decimal('2522299999.999999986499688021')

Why is 2.5225 seconds truncated to 2522499999 nanoseconds, while 2.5223 seconds rounded up to 2522300000 nanoseconds? Behaviour here seems quite erratic.

[miccoli]

The problem should be in line 223–225 and 228 below:

https://github.com/pandas-dev/pandas/blob/12d69c8b5739e7070b8ca2ff86587f828c9a96c0/pandas/_libs/tslibs/conversion.pyx#L214-L232

In fact

>>> 0.5225 * 1e9
522499999.99999994
>>> 0.5223 * 1e9
522300000.0

despite the fact that

>>> round(0.5225, 9) == 0.5225
True

I would say that the bug is the naive assumption that

trunc(round(x, p) * 10**p)

give raise to the same results in decimal and binary floating point:

>>> math.trunc(round(0.5225, 6) * 10**6)
522499
>>> math.trunc(round(decimal.Decimal(0.5225), 6) * 10**6)
522500

[miccoli]

After some more experimentation I arrived at some preliminary remarks.

1. In to_timedelta there are two different possible paths:
   1. truncation if p == 0
   2. rounding if p > 0
2. The rounding artihmetic is flawed.

So actually we have here two separate issues.

Point 1. could be considered a desing choice: it is open to discussion if other more coherent coherent behaviours (always round, always truncate) are worth the risk of breaking current code.

Point 2. is clearly a bug, which is orthogonal to 1. However, it makes no sense to submit a PR for 2. alone, if also 1. has to be addressed.

[miccoli]

Please see my POC implementation with correct rounding but preserving truncation in the ns → ns case: https://github.com/miccoli/pandas/tree/GH%2356629