BUG: Conversion from datetime64[ns] to datetime does not effect .info() and probably not the DataFrame itself

[couzhei]

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

from datetime import datetime
import pandas as pd
df = pd.read_csv(... 
df.info()

<class 'pandas.core.frame.DataFrame'>
RangeIndex: 100 entries, 0 to 99
Data columns (total 7 columns):
 #   Column       Non-Null Count  Dtype         
---  ------       --------------  -----         
 0   datetime     100 non-null    datetime64[ns]
 1   consumption  100 non-null    float64
.
.
.

 df["datetime"] = df.datetime.dt.to_pydatetime()
# or
df["datetime"] = df.datetime.astype(datetime)

<class 'pandas.core.frame.DataFrame'>
RangeIndex: 100 entries, 0 to 99
Data columns (total 7 columns):
 #   Column       Non-Null Count  Dtype         
---  ------       --------------  -----         
 0   datetime     100 non-null    datetime64[ns]
 1   consumption  100 non-null    float64
.
.
.

# or

---------------------------------------------------------------------------
TypeError                                 Traceback (most recent call last)
Cell In[75], line 1
----> 1 df = reduce_size(df)
      2 df.info()

Cell In[74], line 4, in reduce_size(df)
      3 def reduce_size(df):
----> 4     df["datetime"] = df.datetime.astype(datetime)
      5     df["total_hours"] = df.total_hours.astype(np.int32)
      6     for col in ["peak", "standard", "valley", "consumption"]:

File [~/miniconda3/envs/tensor/lib/python3.11/site-packages/pandas/core/generic.py:6532](https://file+.vscode-resource.vscode-cdn.net/home/couzhei/code/electricity_consumption/~/miniconda3/envs/tensor/lib/python3.11/site-packages/pandas/core/generic.py:6532), in NDFrame.astype(self, dtype, copy, errors)
   6528     results = [ser.astype(dtype, copy=copy) for _, ser in self.items()]
   6530 else:
   6531     # else, only a single dtype is given
-> 6532     new_data = self._mgr.astype(dtype=dtype, copy=copy, errors=errors)
   6533     res = self._constructor_from_mgr(new_data, axes=new_data.axes)
   6534     return res.__finalize__(self, method="astype")

File [~/miniconda3/envs/tensor/lib/python3.11/site-packages/pandas/core/internals/managers.py:414](https://file+.vscode-resource.vscode-cdn.net/home/couzhei/code/electricity_consumption/~/miniconda3/envs/tensor/lib/python3.11/site-packages/pandas/core/internals/managers.py:414), in BaseBlockManager.astype(self, dtype, copy, errors)
    411 elif using_copy_on_write():
    412     copy = False
--> 414 return self.apply(
    415     "astype",
...
   1656 elif npdtype.kind == "O":
-> 1657     raise TypeError(f"dtype '{dtype}' not understood")
   1659 return npdtype

TypeError: dtype '' not understood

Issue Description

I happened to be working with pandas.to_datetime recently and I noticed it doesn't provide the expected lower resolution datetime anyhow. A little search on the web might suggest using either astype() or .dt.to_pydatetime() methods in order to achieve such a desire.

Expected Behavior

<class 'pandas.core.frame.DataFrame'>
RangeIndex: 100 entries, 0 to 99
Data columns (total 7 columns):
 #   Column       Non-Null Count  Dtype         
---  ------       --------------  -----         
 0   datetime     100 non-null    datetime          <---------( Here )
 1   consumption  100 non-null    float64
.
.
.

Installed Versions

INSTALLED VERSIONS

commit : ba1cccd
python : 3.11.4.final.0
python-bits : 64
OS : Linux
OS-release : 5.15.0-83-generic
Version : #92~20.04.1-Ubuntu SMP Mon Aug 21 14:00:49 UTC 2023
machine : x86_64
processor : x86_64
byteorder : little
LC_ALL : None
LANG : en_US.UTF-8
LOCALE : en_US.UTF-8

pandas : 2.1.0
numpy : 1.24.3
pytz : 2023.3
dateutil : 2.8.2
setuptools : 68.0.0
pip : 23.2.1
Cython : None
pytest : None
hypothesis : None
...
zstandard : None
tzdata : 2023.3
qtpy : None
pyqt5 : None

[jbrockmendel]

The conversion now works as expected (though astype(datetime) correctly raises). Could use a test (first check to see if one already exists)

[litang99]

Take

[litang99]

Got the following warning:
/tmp/ipykernel_1983/4209712964.py:1: FutureWarning: The behavior of DatetimeProperties.to_pydatetime is deprecated, in a future version this will return a Series containing python datetime objects instead of an ndarray. To retain the old behavior, call np.array on the result
df["datetime"] = np.array(df.datetime.dt.to_pydatetime())
to_pydatetime() is not available anymore.
Should this issue be closed then?