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.

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

Reproducible Example

import pandas as pd
print(pd.__version__)
# 1.5.3

df = pd.DataFrame({'A': [1, 1, 2], 'B': [4, 5, 6], 'date': ['2018-01-01', '2018-01-02', '2018-01-03'], "category": ["a", "b", "c"]})
df["date"] = pd.to_datetime(df["date"])
print(df.dtypes)
# A                int64
# B                int64
# date    datetime64[ns]
# category        object
# dtype: object

pt = df.pivot_table(index=["date", "category"], columns=["A"], values=["B"], margins=True)
print(pt.index.dtypes)
# date             object
# category         object

Issue Description

When calling pivot_table with margins=True, the dtype of date columns changes from datetime64 to object. This does not happen when margins=False.

Expected Behavior

Dtypes should be preserved.

Installed Versions

INSTALLED VERSIONS ------------------ commit : 2e218d10984e9919f0296931d92ea851c6a6faf5 python : 3.10.6.final.0 python-bits : 64 OS : Windows OS-release : 10 Version : 10.0.19044 machine : AMD64 processor : Intel64 Family 6 Model 142 Stepping 12, GenuineIntel byteorder : little LC_ALL : None LANG : en_US.UTF-8 LOCALE : English_United States.1252 pandas : 1.5.3 numpy : 1.23.2 pytz : 2022.2.1 dateutil : 2.8.2 setuptools : 63.4.1 pip : 22.2.2 Cython : None pytest : 5.4.3 hypothesis : None sphinx : None blosc : None feather : None xlsxwriter : None lxml.etree : None html5lib : None pymysql : None psycopg2 : None jinja2 : 3.1.2 IPython : 8.3.0 pandas_datareader: None bs4 : 4.11.1 bottleneck : None brotli : None fastparquet : None fsspec : 2022.3.0 gcsfs : None matplotlib : 3.5.2 numba : None numexpr : None odfpy : None openpyxl : 3.0.10 pandas_gbq : None pyarrow : 9.0.0 pyreadstat : None pyxlsb : 1.0.10 s3fs : None scipy : 1.8.0 snappy : None sqlalchemy : None tables : None tabulate : None xarray : None xlrd : None xlwt : None zstandard : None tzdata : 2022.5

Comment From: wesleycheung0

Should this be the expected behavior instead, because of the added All row when margins=True?

Comment From: KarlisKV

@diego-pm I managed to get the correct type with margins True, got this warning together with it

pt = df.pivot_table(index=["A", "date"], columns=["A"], values=["B"], margins=True) print(pt.index.dtypes)

A object date datetime64[ns] dtype: object

FutureWarning: Inferring datetime64[ns] from data containing strings is deprecated and will be removed in a future version. To retain the old behavior explicitly pass Series(data, dtype=datetime64[ns])

Comment From: diego-pm

@KarlisKV It seems in your case works because the date is not in the first position of the index. The reason must be what @wesleycheung0 said.

Comment From: DeaMariaLeon

@diego-pm I think this is normal, since there is a mix of data types in the index. Maybe this helps:

>>> index= [df["date"][0], "my_string", True]
>>> y = pd.Series(data=(1, 2, 3), index=index)
>>> y.index

Index([2018-01-01 00:00:00, 'my_string', True], dtype='object')

Thank you for opening the issue. I will close it for now. If you feel that it should stay open please let me know.