CODEWITHSUNDEEP
pythonexcelpython-3.xpandasdataframe
DanielaK
DanielaK

Reputation: 43

How to import all fields from xls as strings into a Pandas dataframe?

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!

Upvotes: 4

Views: 9385

Answers (3)

Obernil
Obernil

Reputation: 51

Use dtype=str when calling .read_excel()

import pandas as pd
filename = 'C:\DemoFile.xlsx'

df = pd.read_excel(filename, dtype=str)

Upvotes: 2

MaxU - stand with Ukraine
MaxU - stand with Ukraine

Reputation: 210882

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

Upvotes: 3

ℕʘʘḆḽḘ
ℕʘʘḆḽḘ

Reputation: 19395

the usual solution is:

  1. read in one row of data just to get the column names and number of columns
  2. create the dictionary automatically where each columns has a string type
  3. re-read the full data using the dictionary created at step 2.

Upvotes: 0

Related Questions