BUG (2.0rc0): groupby changes dtype unexpectedly from timedelta64[s] to timedelta64[ns]

[aberres]

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

import pandas as pd

df = pd.DataFrame(
    {"date": pd.date_range("2023-01-01", periods=5, unit="s"), "values": 1}
)

# datetime64[s]
print(df["date"].dtype)

grouped = df.groupby("date", as_index=False).sum()
# datetime64[ns]
print(grouped["date"].dtype)

concatted = pd.concat([df, grouped])["date"]
# object
print(concatted.dtype)

Issue Description

I stumbled upon this behavior when test-driving 2.0rc0 with my code base.

Some data frames now contain date columns of type datetime64[s]. Did not investigate yet why. The problem occurs when working with these columns. After a groupby, the type changes to datetime64[ns].

When now concatting datetime64[s] to datetime64[ns] things get even worse and I have an object column.

Expected Behavior

As I started with a datetime column, I'd expect the datetime column dtype not to change magically. If a later pd.merge wouldn't have complained, it could have been hard to track down what was going on.

Installed Versions

INSTALLED VERSIONS

commit : 1a2e300
python : 3.11.0.final.0
python-bits : 64
OS : Darwin
OS-release : 22.1.0
Version : Darwin Kernel Version 22.1.0: Sun Oct 9 20:14:54 PDT 2022; root:xnu-8792.41.9~2/RELEASE_X86_64
machine : x86_64
processor : i386
byteorder : little
LC_ALL : None
LANG : None
LOCALE : None.UTF-8

pandas : 2.0.0rc0
numpy : 1.24.2
pytz : 2022.7.1
dateutil : 2.8.2
setuptools : 67.4.0
pip : 23.0.1
Cython : None
pytest : 7.2.1
hypothesis : None
sphinx : None
blosc : None
feather : None
xlsxwriter : None
lxml.etree : 4.9.2
html5lib : 1.1
pymysql : None
psycopg2 : 2.9.5
jinja2 : 3.1.2
IPython : 8.11.0
pandas_datareader: None
bs4 : 4.11.2
bottleneck : 1.3.7
brotli : None
fastparquet : None
fsspec : 2023.1.0
gcsfs : 2023.1.0
matplotlib : 3.7.0
numba : None
numexpr : 2.8.4
odfpy : None
openpyxl : 3.1.0
pandas_gbq : None
pyarrow : 11.0.0
pyreadstat : None
pyxlsb : None
s3fs : None
scipy : 1.10.1
snappy : None
sqlalchemy : 1.4.46
tables : None
tabulate : 0.9.0
xarray : None
xlrd : None
zstandard : None
tzdata : None
qtpy : None
pyqt5 : None

[MarcoGorelli]

thanks @aberres for the report (and for having tried out the RC)

cc @jbrockmendel (though I'll try to take a look myself this week)

[aberres]

I looked at where the initial datetime64[s] comes from.

Similar to this:

d = dt.date.today()
np_array = np.arange(
    np.datetime64(d), np.datetime64(d + dt.timedelta(days=1))  # dtype='datetime64[D]'
)
df = pd.DataFrame({"date": np_array})  # dtype='datetime64[s]'

[MarcoGorelli]

The supported resolutions in pandas datetimes are now:

• s
• ms
• us
• ns

and so numpy's D resolution gets converted to pandas' 's' resolution. Previously, the only supported resolution in pandas was 'ns' (nanosecond)

Non-nanosecond resolution in pandas is pretty new, and admittedly poorly documented

I'd say that the bug here is that groupby didn't preserve the second resolution

[aberres]

I'd say that the bug here is that groupby didn't preserve the second resolution

Yes, definitely. I just wanted to double-check that the appearance of the s timedelta is not happening by accident.

[aberres]

FWIW: Now I am explicitly using .astype('timedelta64[ns]') and everything is fine on my side.

[MarcoGorelli]

I think this is the issue:

(Pdb) self.grouping_vector
<DatetimeArray>
['2023-01-01 00:00:00', '2023-01-02 00:00:00', '2023-01-03 00:00:00',
 '2023-01-04 00:00:00', '2023-01-05 00:00:00']
Length: 5, dtype: datetime64[s]
(Pdb) algorithms.factorize(self.grouping_vector, sort=self._sort, use_na_sentinel=self._dropna)
(array([0, 1, 2, 3, 4]), array(['2023-01-01T00:00:00.000000000', '2023-01-02T00:00:00.000000000',
       '2023-01-03T00:00:00.000000000', '2023-01-04T00:00:00.000000000',
       '2023-01-05T00:00:00.000000000'], dtype='datetime64[ns]'))

from

pandas/pandas/core/groupby/grouper.py

Lines 797 to 799 in 4af5756

	codes, uniques = algorithms.factorize( # type: ignore[assignment]
	self.grouping_vector, sort=self._sort, use_na_sentinel=self._dropna
	)

[jbrockmendel]

Hah marco is moments ahead of me in tracking this down. I had only gotten as far as group_arraylike when seeing his post

[jbrockmendel]

There are a few potential ways of fixing this, with different levels of cleanliness/quickness tradeoff.

Things go wrong in algorithms.factorize bc we go through a path that casts non-nano to nano (in _ensure_data and _reconstruct_data). My attempts to get rid of that casting (#48670) have turned into yak shaving (most recently #50960 which was broken by the change in the C code to use a capsule xref #51952, #51951).

Shorter-term, we could patch algorithms.factorize to dispatch on ExtensionArray rather than on is_extension_array_dtype. Update... which seems to raise an AssertionError, yikes