BUG: pandas.read_excel() with dtype=object is broken in pandas 2.1.1

[VisualDev-FR]

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

Create an excel file nammed xl_file.xlsx, with one worksheet named Sheet1 as following:

A	B	C
2	'dummy_str

Then save it and run this code in the directory where you created the excel file :

import pandas as pd
from pathlib import Path

df = pd.read_excel(
    io=Path("xl_file.xlsx"),
    sheet_name="Sheet1",
    dtype=object,
)

dtypes_1 = df.dtypes
dtypes_2 = df.fillna("").dtypes

>>> print(dtypes_1)
0   A    object # <- expected int, or int64
1   B    object
2   C    object

>>> print(dtypes_2)
0   A     int64 # <- when we use df.fillna(""), we have the expected behavior
1   B    object
2   C    object

Issue Description

The method pandas.read_excel() with argument dtypes=object does not prevent pandas from converting data types, as described on the offical documentation page

Expected Behavior

when we use the method pandas.read_excel() with argument dtypes=object, on an Excel workbook which contains int values, the dtypes of this columns should be int64

Installed Versions

INSTALLED VERSIONS ------------------ commit : e86ed37 python : 3.11.5.final.0 python-bits : 64 OS : Windows OS-release : 10 Version : 10.0.19044 machine : AMD64 processor : Intel64 Family 6 Model 141 Stepping 1, GenuineIntel byteorder : little LC_ALL : None LANG : fr_FR.UTF-8 LOCALE : fr_FR.cp1252

pandas : 2.1.1
numpy : 1.24.2
pytz : 2023.3
dateutil : 2.8.2
setuptools : 65.5.0
pip : 23.2.1
Cython : None
pytest : 7.3.1
hypothesis : None
sphinx : None
blosc : None
feather : None
xlsxwriter : 3.1.7
lxml.etree : None
html5lib : None
pymysql : None
psycopg2 : None
jinja2 : 3.1.2
IPython : None
pandas_datareader : None
bs4 : None
bottleneck : None
dataframe-api-compat: None
fastparquet : None
fsspec : None
gcsfs : None
matplotlib : None
numba : None
numexpr : None
odfpy : None
openpyxl : 3.1.2
pandas_gbq : None
pyarrow : None
pyreadstat : None
pyxlsb : None
s3fs : None
scipy : None
sqlalchemy : None
tables : None
tabulate : None
xarray : None
xlrd : None
zstandard : None
tzdata : 2023.3
qtpy : None
pyqt5 : None

[asishm-wk]

Can you share the excel file directly or a way to generate the excel file, e.g. using pd.DataFrame(...).to_excel(...) , that reproduces the issue?

[rhshadrach]

Thanks for the report, is specifying dtype=None expected to give the same result as dtype=object? I wonder if we should just change the docs to say None instead of object.

[rhshadrach]

cc @WillAyd

[rhshadrach]

This was added in 588e29d. I think this is behaving as expected.

df = pd.DataFrame({'a': [1, 1, 2], 'b': ['x', 'y', 'z']})
df.to_excel('test.xlsx')
print(type(pd.read_excel('test.xlsx', dtype=object).iloc[0, 1]))
# <class 'int'>

There is no inference done - including inferring that the value is an integer! So we must store them as Python objects, but they are indeed Python ints.

I think the doc could be clarified better - explicitly mentioning that the result will be object dtype.

[aryanVanaik]

Hi, could I work on this, would you prefer to change the docs maybe perhaps pointing me in the right direction

[rhshadrach]

Certainly! See the contributor guide.

For this particular issue, we're looking to better explain that specifying dtype=object will result in a DataFrame with object dtype. Perhaps just adding This will necessarily result in ``object`` dtype in the docstring?

[aryanVanaik]

perfect ill get that done :)