BUG: pd.to_datetime with low-reso unit and high-reso origin

[jbrockmendel]

Pandas version checks

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

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

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

Reproducible Example

ts = pd.Timestamp("2016-01-01 00:00:00.000001")
dti = pd.to_datetime([1, 2, 3], unit="D", origin=ts)

>>> dti
DatetimeIndex(['2016-01-02', '2016-01-03', '2016-01-04'], dtype='datetime64[ns]', freq=None)

Issue Description

@jorisvandenbossche pointed this out in #63347, but it turns out to be a problem on main too.

Expected Behavior

dti
DatetimeIndex(['2016-01-02 00:00:00.01', '2016-01-03 00:00:00.01', '2016-01-04 00:00:00.01'], dtype='datetime64[ns]', freq=None)

Installed Versions

Details

Replace this line with the output of pd.show_versions()

[jorisvandenbossche]

This is also the behaviour on released 2.x. It seems the origin gets rounded/truncated at the specified unit of numeric input (which also makes somewhat sense? but maybe it should not happen silently)

[AmoghDeshmukh]

I'd like to work on this issue.

[Sharl0tteIsTaken]

Hello @jbrockmendel,

I think this issue was caused by floor division, replace // with / in the following line should solve this issue.

pandas/pandas/core/tools/datetimes.py

Line 633 in cc0bcaa

ioffset = td_offset // Timedelta(1, unit=unit)

But there is another underlying issue, result (dti) is not as expected after the replacement:

DatetimeIndex(['2016-01-02 00:00:00.000000941',
               '2016-01-03 00:00:00.000000941',
               '2016-01-04 00:00:00.000000941'],
              dtype='datetime64[ns]', freq=None)

I think this is caused by something in function cast_from_unit_vectorized(), but I don't know much about C level Python.

The comment before edit (Click to expand):

Hello @jbrockmendel,

I have done some digging using pdb on this, and here are the results.

The short answer

The function to_datetime() calls another function _adjust_to_origin(), which is the only place where the variable origin is used. And in function _adjust_to_origin(), the variable origin is floor divided.

pandas/pandas/core/tools/datetimes.py

Lines 614 to 633 in cc0bcaa

	# we are going to offset back to unix / epoch time
	try:
	if lib.is_integer(origin) or lib.is_float(origin):
	offset = Timestamp(origin, unit=unit)
	else:
	offset = Timestamp(origin)
	except OutOfBoundsDatetime as err:
	raise OutOfBoundsDatetime(f"origin {origin} is Out of Bounds") from err
	except ValueError as err:
	raise ValueError(
	f"origin {origin} cannot be converted to a Timestamp"
	) from err
	if offset.tz is not None:
	raise ValueError(f"origin offset {offset} must be tz-naive")
	td_offset = offset - Timestamp(0)
	# convert the offset to the unit of the arg
	# this should be lossless in terms of precision
	ioffset = td_offset // Timedelta(1, unit=unit)

But even if changed from using floor division to using division, the result is not as expected.

Explanation

The parameter in function _adjust_to_origin() in file pandas/core/tools/datetimes.py is the only place where the variable origin is used (line 1015). Line 633 used floor division to state variable ioffset, this removes the digits below decimal point (0.00000000001 in this case). Changing the line to use just division solves part of the problem.

In line 637 arg = np.asarray(arg) will set variable arg to array([1. 2. 3.]), but this is only because print truncate small digits below decimal point. Set np.set_printoptions(precision=20) in pdb will show the actual value of array([16802.00000000001, 16803.00000000001, 16804.00000000001]) instead.

The following is the return value of function pd.to_datetime(), so I am still missing something:

DatetimeIndex(['2016-01-02 00:00:00.000000941',
               '2016-01-03 00:00:00.000000941',
               '2016-01-04 00:00:00.000000941'],
              dtype='datetime64[ns]', freq=None)

Function calls it go through but I skipped, because I couldn't explain it (Click to expand):

1. On line 1072 of function to_datetime(), function convert_listlike() was called.
2. On line 412 of function _convert_listlike_datetimes(), function _to_datetime_with_unit() was called.
3. On line 529 of function _to_datetime_with_unit(), cast_from_unit_vectorized() function was called.

• Before function cast_from_unit_vectorized(), variable arg is still array([16802.00000000001, 16803.00000000001, 16804.00000000001]) with type <class 'numpy.ndarray'>.
• I don't understand the following function call, I think it needs to be split into two parts.
  • Follow pdb steps (No idea what function calls for _round_dispatcher() and round()):
    1. Function _round_dispatcher() on line 3614 of file pandas-dev/Lib/site-packages/numpy/_core/fromnumeric.py was called. After the function call and before any line of code in function is exectued, variable a has become array([1.0913936421275139e-11, 1.0913936421275139e-11, 1.0913936421275139e-11]).
    2. Function round() on line 3619 was called.
    3. Returns with rounded value array([1.09e-11, 1.09e-11, 1.09e-11]).
  • Follow function calls:
    1. On line 98 of function cast_from_unit_vectorized in file pandas/_libs/tslibs/conversion.pyx was called.
    2. The return value should be [86400000000864 172800000000864 259200000000864] with type <class 'numpy.ndarray'>.

4. After line 529 variable arr has become array([1451692800000000941, 1451779200000000941, 1451865600000000941]), which is the value I get following the function call and add 1451606400000000000.
5. On line 539 Variable arr become type(arr)=<class 'numpy.ndarray'>: ['2016-01-02T00:00:00.000000941' '2016-01-03T00:00:00.000000941' '2016-01-04T00:00:00.000000941'] with arr = arr.view("M8[ns]").

TL;DR of the function call I skipped:

• In line 529 arr = cast_from_unit_vectorized(arg, unit=unit), function round() on line 3619 of file pandas-dev/Lib/site-packages/numpy/_core/fromnumeric.py was called. This rounds variable arg from array([1.0913936421275139e-11, 1.0913936421275139e-11, 1.0913936421275139e-11]) to variable arr of array([1.09e-11, 1.09e-11, 1.09e-11]).
• Line 539 formats variable arr to readable form array(['2016-01-02T00:00:00.000000941', '2016-01-03T00:00:00.000000941', '2016-01-04T00:00:00.000000941'], dtype='datetime64[ns]').
• But this couldn't fully explain where the result 00:00:00.000000941 comes from.

There are only two questions remaining:

• Why did line 633 in file pandas/core/tools/datetimes.py used floor division? Can this be discussed?
• What happens during the function call cast_from_unit_vectorized()?

The difference between my result and the expected result (Click to expand):

The reproducible example has the result as follows:

DatetimeIndex(['2016-01-02', '2016-01-03', '2016-01-04'], dtype='datetime64[ns]', freq=None)

But I have the result:

DatetimeIndex(['2016-01-02', '2016-01-03', '2016-01-04'], dtype='datetime64[s]', freq=None)

The only difference is the unit in dtype, here is the code I used:

import pandas as pd

ts = pd.Timestamp("2016-01-01 00:00:00.000001")
dti = pd.to_datetime([1, 2, 3], unit="D", origin=ts)
print(dti)

On a side note, large number are also rounded (Click to expand):

With the floor division used in function _adjust_to_origin() of file pandas/core/tools/datetimes.py. This will also produce the same result:

import pandas as pd

ts = pd.Timestamp("2016-01-01 10:00:00")
dti = pd.to_datetime([1, 2, 3], unit="D", origin=ts)
print(dti)

Please let me know your thoughts on this, I would love to continue researching this further.
Thank you in advance! ✨

[jbrockmendel]

@Sharl0tteIsTaken I'm not going to read a wall of text from an AI. If you have something you'd like my input on, please write it yourself (briefly).

[Sharl0tteIsTaken]

Hello @jbrockmendel,

The previous comment is edited to be more concise, all processes, path the code took and other stuff is now hidden.
For the record, all my code and text were manually entered via keyboard.
Hope you like the new version :)

[jbrockmendel]

I suspect Timestamp(origin) + pd.to_timedelta(arg, unit=unit) will work

[Sharl0tteIsTaken]

I am confused, isn't that the same as the reproducible example?

[jbrockmendel]

No, it gets the extra microsecond right.

[Sharl0tteIsTaken]

You meant that it will get the 00:00:00.000000941 part to be 00:00:00.000001?
I still don't see the difference, could you please give an example?

[jbrockmendel]

origin = pd.Timestamp("2016-01-01 00:00:00.000001")
arg = [1, 2, 3]

>>> origin + pd.to_timedelta([1, 2, 3], unit="D")
DatetimeIndex(['2016-01-02 00:00:00.000001', '2016-01-03 00:00:00.000001',
               '2016-01-04 00:00:00.000001'],
              dtype='datetime64[us]', freq=None)