I am trying to import a file from xlsx into a Python Pandas dataframe. I would like to prevent fields/columns being interpreted as integers and thus losing leading zeros or other desired heterogenous formatting.
So for an Excel sheet with 100 columns, I would do the following using a dict comprehension with range(99).
import pandas as pd filename = 'C:DemoFile.xlsx' fields = {col: str for col in range(99)} df = pd.read_excel(filename, sheetname=0, converters=fields)
These import files do have a varying number of columns all the time, and I am looking to handle this differently than changing the range manually all the time.
Does somebody have any further suggestions or alternatives for reading Excel files into a dataframe and treating all fields as strings by default?
Many thanks!
Advertisement
Answer
Try this:
xl = pd.ExcelFile(r'C:DemoFile.xlsx') ncols = xl.book.sheet_by_index(0).ncols df = xl.parse(0, converters={i : str for i in range(ncols)})
UPDATE:
In [261]: type(xl) Out[261]: pandas.io.excel.ExcelFile In [262]: type(xl.book) Out[262]: xlrd.book.Book