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

[mgab]

• 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 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 : c7f7443
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 : 0691c5c
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