Skip to content

Pandas read_excel uses blank column heading as index #21032

Closed
@kentmaxwell

Description

@kentmaxwell

Problem description

When using Pandas read_excel function with an Excel file that has a column heading that is blank, Pandas will regard the column with the blank heading as the index.

Consider this example file: https://tinyurl.com/y8br2js4
Within this file, column B does not have a column heading, it is just a blank cell. The rest of the columns have names.

When running this code:

import pandas as pd

xlsx_file = './Input/Test_File.xlsx'

test_df = pd.read_excel(xlsx_file,
                        index_col=None,
                        usecols='B, C, E, F')

The resulting dataframe will use column B as the index, even though index_col=None is specified. If I add column A to usecols, the dataframe processes the file as expected.

Output of pd.show_versions()

INSTALLED VERSIONS

commit: None
python: 3.6.5.final.0
python-bits: 64
OS: Windows
OS-release: 10
machine: AMD64
processor: Intel64 Family 6 Model 94 Stepping 3, GenuineIntel
byteorder: little
LC_ALL: None
LANG: en
LOCALE: None.None

pandas: 0.22.0
pytest: 3.5.1
pip: 10.0.1
setuptools: 39.1.0
Cython: 0.28.2
numpy: 1.14.3
scipy: 1.1.0
pyarrow: 0.8.0
xarray: None
IPython: 6.4.0
sphinx: 1.7.4
patsy: 0.5.0
dateutil: 2.7.2
pytz: 2018.4
blosc: None
bottleneck: 1.2.1
tables: 3.4.3
numexpr: 2.6.5
feather: 0.4.0
matplotlib: 2.2.2
openpyxl: 2.5.3
xlrd: 1.1.0
xlwt: 1.3.0
xlsxwriter: 1.0.4
lxml: 4.2.1
bs4: 4.6.0
html5lib: 1.0.1
sqlalchemy: 1.2.7
pymysql: None
psycopg2: None
jinja2: 2.10
s3fs: None
fastparquet: None
pandas_gbq: None
pandas_datareader: None

Metadata

Metadata

Assignees

No one assigned

    Labels

    No labels
    No labels

    Type

    No type

    Projects

    No projects

    Milestone

    No milestone

    Relationships

    None yet

    Development

    No branches or pull requests

    Issue actions