BUG: Dataframe.pivot with multiple `values` columns does not preserve dtype of numeric columns

[mgab]

• [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 master branch of pandas.

Reproducible Example

import pandas as pd

df = pd.DataFrame({'idx': [0, 1, 0, 1],
                   'col': list('aabb'),
                   'ints': [10, 20, 30, 40],
                   'ints2': [11, 21, 31, 41],
                   'floats': [0.1, 0.2, 0.3, 0.4],
                   'strs': list('ABCD'),
                   'dts': pd.date_range('2021-01-01 10:00:00', '2021-01-04 10:00:00').to_series()})

df.pivot(index='idx', columns='col', values=['ints', 'floats'])
#      ints       floats
# col     a     b      a    b
# idx
# 0    10.0  30.0    0.1  0.3
# 1    20.0  40.0    0.2  0.4

df.pivot(index='idx', columns='col', values=['ints', 'ints2']).dtypes  # -> all int
#        col
# ints   a      int64
#        b      int64
# ints2  a      int64
#        b      int64
# dtype: object

df.pivot(index='idx', columns='col', values=['ints', 'floats']).dtypes  # -> all float
#         col
# ints    a      float64
 #        b      float64
# floats  a      float64
#         b      float64
# dtype: object

df.pivot(index='idx', columns='col', values=['ints', 'strs']).dtypes  # -> all object
#       col
# ints  a      object
#       b      object
# strs  a      object
#       b      object
# dtype: object

df.pivot(index='idx', columns='col', values=['ints', 'dts']).dtypes  # -> object and datetime64
#       col
# ints  a              object
#       b              object
# dts   a      datetime64[ns]
#       b      datetime64[ns]
# dtype: object

Issue Description

When calling DataFrame.pivot with a list of column names as the values argument, numeric columns are cast to a common ancestor datatype of the selected columns. So depending on the dtypes of the columns selected in the values argument:

• If only int columns are selected, the dtype is maintained
• If at least a float column is selected, all int columns are cast to float.
• If at least an object or datetime column is selected, all numeric columns (int and floats) are cast to object
• datetime columns remain as datetime column even if some object column is selected
• Object columns remain as object dtype

Expected Behavior

As far as I understand, the dtype of pivoted columns could be maintained. If there are missing values on the pivot table, int columns might be casted to floats to allow NaNs, but otherwise no dtype transformations should occur due to pivot.

Perhaps, the behaviour is expected as if one were to do df.stack().unstack(). In that case, perhaps it could be included in the notes section of the documentation.

Installed Versions

INSTALLED VERSIONS

commit : c7f7443c1bad8262358114d5e88cd9c8a308e8aa python : 3.9.2.final.0 python-bits : 64 OS : Darwin OS-release : 20.6.0 Version : Darwin Kernel Version 20.6.0: Wed Jun 23 00:26:31 PDT 2021; root:xnu-7195.141.2~5/RELEASE_X86_64 machine : x86_64 processor : i386 byteorder : little LC_ALL : None LANG : None LOCALE : None.UTF-8

pandas : 1.3.1 numpy : 1.21.1 pytz : 2021.1 dateutil : 2.8.2 pip : 21.2.3 setuptools : 57.4.0 Cython : None pytest : 6.2.4 hypothesis : None sphinx : None blosc : None feather : None xlsxwriter : None lxml.etree : None html5lib : None pymysql : None psycopg2 : 2.9.1 (dt dec pq3 ext lo64) jinja2 : 3.0.1 IPython : 7.26.0 pandas_datareader: None bs4 : None bottleneck : None fsspec : None fastparquet : None gcsfs : None matplotlib : 3.4.2 numexpr : None odfpy : None openpyxl : None pandas_gbq : None pyarrow : None pyxlsb : None s3fs : None scipy : 1.7.1 sqlalchemy : None tables : None tabulate : 0.8.9 xarray : None xlrd : None xlwt : None numba : None

[hvassard]

Hello !

I'd like to contribute by trying to fix this issue, is it possible ?

[mattharrison]

Here's another example:

import io
data = '''name,age,test1,test2,teacher
Adam,15,95.0,80,Ashby
Bob,16,81.0,82,Ashby
Dave,16,89.0,84,Jones
Fred,15,,88,Jones'''
scores = pd.read_csv(io.StringIO(data), dtype_backend='pyarrow')


(scores
  .pivot(columns='teacher', values=['test1', 'test2']).dtypes
)

The types of the pivot are object...

[mgab]

Just wanted to check if the issue could be closed but apparently it can't. I can reproduce the behavior with Pandas 2.2.3

Installed Versions

INSTALLED VERSIONS

commit : 0691c5cf90477d3503834d983f69350f250a6ff7 python : 3.12.5 python-bits : 64 OS : Darwin OS-release : 23.6.0 Version : Darwin Kernel Version 23.6.0: Mon Jul 29 21:13:04 PDT 2024; root:xnu-10063.141.2~1/RELEASE_ARM64_T6020 machine : arm64 processor : arm byteorder : little LC_ALL : None LANG : en_GB.UTF-8 LOCALE : en_GB.UTF-8

pandas : 2.2.3 numpy : 2.1.2 pytz : 2024.2 dateutil : 2.9.0.post0 pip : 24.2 Cython : None sphinx : None IPython : 8.28.0 adbc-driver-postgresql: None adbc-driver-sqlite : None bs4 : None blosc : None bottleneck : None dataframe-api-compat : None fastparquet : None fsspec : None html5lib : None hypothesis : None gcsfs : None jinja2 : None lxml.etree : None matplotlib : None numba : None numexpr : None odfpy : None openpyxl : None pandas_gbq : None psycopg2 : None pymysql : None pyarrow : None pyreadstat : None pytest : None python-calamine : None pyxlsb : None s3fs : None scipy : None sqlalchemy : None tables : None tabulate : None xarray : None xlrd : None xlsxwriter : None zstandard : None tzdata : 2024.2 qtpy : None pyqt5 : None