BUG: read_excel doesn't honor dtype for index

[akaihola]

• I have checked that this issue has not already been reported.

• I have confirmed this bug exists on the latest version of pandas.

• (optional) I have confirmed this bug exists on the master branch of pandas.

---

Code Sample, a copy-pastable example

Make sure you have Pandas and xlrd installed in a virtualenv, and launch the Python interpreter. You'll need openpyxl only if you decide to create the Excel file from Python (see below).

python3 -m venv /tmp/pandas-issue-35816
. /tmp/pandas-issue-35816/bin/activate
pip install pandas xlrd openpyxl
python

To get the Excel files for reproducing the bug, you can either

1. download index_dtype.xls (note: attachment gzipped) or index_dtype.xlsx, or
2. copy-paste the text inside Details below into a index_dtype.csv file, import that into Excel, and save it in Excel format as index_dtype.xlsx, or

   string,value for string index
   42,value for int index

3. create an Excel file from Python by copy-pasting from Details below:

   df = pd.DataFrame(
       {1: ["value for string index", "value for int index"]}, ["string", 42]
   )
   df.to_excel("index_dtype.xlsx", header=None)

Finally, copy-paste the following code into Python:

import pandas as pd
pd.read_excel("index_dtype.xlsx", header=None, index_col=0, dtype=str).iloc[:, 0].to_dict()              

Output:

{'string': 'value for string index', 42: 'value for int index'}

Problem description

The integer value in the index column appears as an int instead of a str in the index of the DataFrame read from an Excel file, even though dtype=str was specified.

I verified this behavior with spreadsheets

• exported from LibreOffice 6.2.8.2 as Excel 2007-2019 (.xlsx),
• exported from LibreOffice 6.2.8.2 as Excel 97-2003 (.xls), and
• generated like shown in 3. create an Excel file from Python above.

Unfortunately I don't have Excel to check if "genuine" Excel spreadsheets cause this as well.

The index dtype is correct if:

• read_csv() is used, or
• index_col=0 is omitted (values in the first column all become str)

The index dtype is still wrong even if dtype={0: str, 1: str} is used to specify the dtype for each column separately.

Expected Output

{'string': 'value for string index', '42': 'value for int index'}

Output of pd.show_versions()

INSTALLED VERSIONS

commit : d9fff27
python : 3.8.2.final.0
python-bits : 64
OS : Linux
OS-release : 5.3.11-100.fc29.x86_64
Version : #1 SMP Tue Nov 12 20:41:25 UTC 2019
machine : x86_64
processor : x86_64
byteorder : little
LC_ALL : None
LANG : en_US.UTF-8
LOCALE : en_US.UTF-8

pandas : 1.1.0
numpy : 1.19.1
pytz : 2020.1
dateutil : 2.8.1
pip : 20.2.1
setuptools : 49.2.1
Cython : None
pytest : None
hypothesis : None
sphinx : None
blosc : None
feather : None
xlsxwriter : None
lxml.etree : None
html5lib : None
pymysql : None
psycopg2 : None
jinja2 : None
IPython : None
pandas_datareader: None
bs4 : None
bottleneck : None
fsspec : None
fastparquet : None
gcsfs : None
matplotlib : None
numexpr : None
odfpy : None
openpyxl : None
pandas_gbq : None
pyarrow : None
pytables : None
pyxlsb : None
s3fs : None
scipy : None
sqlalchemy : None
tables : None
tabulate : None
xarray : None
xlrd : 1.2.0
xlwt : None
numba : None

[arw2019]

Thanks for the report!

We typically ask that you don't upload data and instead provide a copy-pastable code sample that people can run. See bug report guidelines: https://matthewrocklin.com/blog/work/2018/02/28/minimal-bug-reports

[akaihola]

Thanks for your reply @arw2019!

I did provide a copy-pastable code sample (the second code block from the top).

I also provided the contents of the Excel spreadsheet exported as CSV (topmost code block).

While Excel files themselves are not "copy-pastable", I still wanted to provide the exact files I had this problem with, in case someone wants to just download them instead of copy-pasting the CSV into a file, importing that into Excel, and saving the .xlsx file.

Update:
I now clarified the description of the bug report so it's clear you don't need to download the binary files. I also added copy-pastable shell commands for creating a virtualenv, installing required packages, and launching Python. Finally, I added copy-paste code for generating a test Excel file from Python using Pandas and openpyxl.

[BhavyaGulati]

take

[simonjayhawkins]

Thanks @akaihola for the report.

can reproduce. on master

>>> import pandas as pd
>>>
>>> pd.__version__
'1.2.0.dev0+155.gd3d74c590'
>>>
>>> import tempfile
>>>
>>> df = pd.DataFrame(
...     {1: ["value for string index", "value for int index"]}, ["string", 42]
... )
>>> df
                             1
string  value for string index
42         value for int index
>>>
>>> with tempfile.TemporaryFile() as fp:
...     df.to_excel(fp, header=None)
...     df2 = pd.read_excel(fp, header=None, index_col=0, dtype=str)
...
>>> df2
                             1
0
string  value for string index
42         value for int index
>>>
>>> df2.index
Index(['string', 42], dtype='object', name=0)
>>>

expected

>>> df2.index
Index(['string', '42'], dtype='object', name=0)
>>>

[simonjayhawkins]

a similar issue exists for read_csv #32930 and #9435

[ivasve]

Any news on this one? Just asking because the issues mentioned above have been closed. #32930 and #9435

[simonjayhawkins]

Looks like there may still be issues here

print(pd.__version__)
# 1.5.0.dev0+1099.gd52f2ce0c6
df = pd.DataFrame(
    {1: ["value for string index", "value for int index"]}, ["string", 42]
)
with tempfile.TemporaryFile() as fp:
    df.to_excel(fp, header=None)
    df2 = pd.read_excel(fp, header=None, index_col=0, dtype=str)

print(df2.index)
# Index(['string', 42], dtype='object', name=0)

with tempfile.TemporaryFile() as fp:
    df.to_excel(fp, header=None)
    df3 = pd.read_excel(fp, header=None, index_col=0, dtype="string")

print(df3.index)
# Index(['string', '42'], dtype='object', name=0)

when explicitly specifying dtype=str, the values are not coerced. The int value remains a Python int in an object Index.

when explicitly specifying dtype="string", it appears that the values are coerced, but that the dtype remains object

This is not consistent with the DataFrame and Index constructors...

print(pd.DataFrame(["string", 42], dtype=str).dtypes.item())
# object
print(type(pd.DataFrame(["string", 42], dtype=str).iloc[1].item()))
# <class 'str'>
print(pd.DataFrame(["string", 42], dtype="string").dtypes.item())
# string
print(type(pd.DataFrame(["string", 42], dtype="string").iloc[1].item()))
# <class 'str'>

print(pd.Index(["string", 42], dtype=str))
# Index(['string', '42'], dtype='object')
print(pd.Index(["string", 42], dtype="string"))
# Index(['string', '42'], dtype='string')

which appear to coerce the values for both str and "string" dtypes, giving object and string dtypes respectively.