Pandas ExcelFile read columns as string

Question

I know that you can specify data types when reading excels using pd.read_excel (as outlined here). Can you do the same using pd.ExcelFile?

I have the following code:

 if ".xls" in 
     xl = pd.ExcelFile(path + "\\" + name, )
     for sheet in xl.sheet_names:
         xl_parsed = xl.parse(sheet)

When parsing the sheet, some of the values in the columns are displayed in scientific notation. I don't know the column names before loading so I need to import everything as string. Ideally I would like to be able to do something like xl_parsed = xl.parse(sheet, dtype = str). Any suggestions?

Please make sure that you refer to the documentation before asking. The accepted answer in the question you linked shows that read_excel takes a converters parameter. So does pd.ExcelFile.parse. Did you try it? — roganjosh
– roganjosh, Commented Mar 19, 2018 at 22:04
Although I should have read the end of your question more closely as that deals with something not covered by the docs. There was an issue about it here which got closed but doesn't seem to have been resolved in any proper way. There is one approach in there that you might be able to adapt; open the Excel in xlrd, inspect the headers to get the names, and create a dictionary comprehension to then pass as converters. Kinda ugly. — roganjosh
– roganjosh, Commented Mar 19, 2018 at 22:20
@roganjosh thanks for the input, that's what i did! I'll post it as an answer — michal111
– michal111, Commented Mar 27, 2018 at 10:28
The solution you suggested is supported by the parse method although it is not mentioned explicitly on the documentation, so you can just use xl_parsed = xl.parse(sheet, dtype=str) — Luiscri
– Luiscri, Commented Nov 29, 2021 at 11:43

GammaGames · Accepted Answer · 2020-01-15 18:31:23Z

3

If you would prefer a cleaner solution, I used the following:

excel = pd.ExcelFile(path)
for sheet in excel.sheet_names:
    columns = excel.parse(sheet).columns
    converters = {column: str for column in columns}

    data = excel.parse(sheet, converters=converters)

answered Jan 15, 2020 at 18:31

GammaGames

1,9742 gold badges27 silver badges42 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

ggorlen · Accepted Answer · 2021-11-09 18:42:52Z

1

I went with roganjosh's suggestion - open the excel first, get column names and then pass as converter.

if ".xls" in name:
    xl = pd.ExcelFile(path)
    sheetCounter = 1
        for sheet in xl.sheet_names:
        ### Force to read as string ###
        column_list = []
        df_column = pd.read_excel(path, sheetCounter - 1).columns
        for i in df_column:
            column_list.append(i)
        converter = {col: str for col in column_list}
        ##################
        xl_parsed = xl.parse(sheet, converters=converter)
        sheetCounter = sheetCounter + 1

edited Nov 9, 2021 at 18:42

ggorlen

59.3k8 gold badges119 silver badges173 bronze badges

answered Mar 27, 2018 at 10:31

michal111

4005 silver badges18 bronze badges

1 Comment

ggorlen Over a year ago

Thanks for the answer. column_list seems pointless, you can just use df_column directly. You can also use for i, sheet in enumerate(xl.sheet_names): instead of sheetCounter. I assume the for sheet in ... should be indented -4 spaces.

Collectives™ on Stack Overflow

Pandas ExcelFile read columns as string

2 Answers 2

Comments

1 Comment

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

1 Comment

Your Answer

Sign up or log in

Post as a guest

Linked

Related