Python pandas how to specify data types when reading an Excel file

Question

I am importing an excel file into a pandas dataframe with the pandas read excel   function   One of the columns is the primary key of the table  it s all numbers  but it s stored as text  the little green triangle in the top left of the Excel cells confirms this     However  when I import the file into a pandas dataframe  the column gets imported as a float  This means that  for example   0614  becomes 614   Is there a way to specify the datatype when importing a column  I understand this is possible when importing CSV files but couldn t find anything in the syntax of read excel      The only solution I can think of is to add an arbitrary letter at the beginning of the text  converting  0614  into  A0614   in Excel  to make sure the column is imported as text  and then chopping off the  A  in python  so I can match it to other tables I am importing from SQL

User · Answer

If you are able to read the excel file correctly and only the integer values are not showing up. you can specify like this.

df = pd.read_excel('my.xlsx',sheetname='Sheet1', engine="openpyxl", dtype=str)

this should change your integer values into a string and show in dataframe

User · Answer

Starting with v0 20 0  the dtype keyword argument in read excel   function could be used to specify the data types that needs to be applied to the columns just like it exists for  read csv   case   Using converters and dtype arguments together on the same column name would lead to the latter getting shadowed and the former gaining preferance     1  Inorder for it to not interpret the dtypes but rather pass all the contents of it s columns as they were originally in the file before  we could set this arg to str or object so that we don t mess up our data   one such case would be leading zeros in numbers which would be lost otherwise   pd read excel  file name xlsx   dtype str                or  dtype object   2  It even supports a dict mapping wherein the keys constitute the column names and values it s respective data type to be set especially when you want to alter the dtype for a subset of all the columns     Assuming data types for  a  and  b  columns to be altered pd read excel  file name xlsx   dtype   a   np float64   b   np int32

User · Answer

The read excel   function has a converters argument  where you can apply functions to input in certain columns   You can use this to keep them as strings  Documentation      Dict of functions for converting values in certain columns  Keys can either be integers or column labels  values are functions that take one input argument  the Excel cell content  and return the transformed content    Example code   pandas read excel my file  converters    my str column  str

User · Answer

If your key has a fixed number of digits  you should probably store as text rather than as numeric data  You can use the converters argument or read excel for this   Or  if this does not work  just manipulate your data once it s read into your dataframe   df  key zfill     df  key   astype str  str zfill 4     names   key key zfill 0   abc     5      0005 1   def  4962      4962 2   ghi   300      0300 3   jkl    14      0014 4   mno    20      0020

User · Answer

In case if you are not aware of the number and name of columns in dataframe then this method can be handy   column list      df column   pd read excel file name   Sheet1   columns for i in df column      column list append i  converter    col  str for col in column list   df actual   pd read excel file name  converters converter    where column list is the list of your column names

User · Answer

If you don t know the column names and you want to specify str data type to all columns   table   pd read excel  path to filename   cols   table columns conv   dict zip cols   str    len cols    table   pd read excel  path to filename   converters conv

User · Answer

You just specify converters   I created an excel spreadsheet of the following structure   names   ages bob     05 tom     4 suzy    3   Where the  ages  column is formatted as strings   To load    import pandas as pd  df   pd read excel  Book1 xlsx  sheetname  Sheet1  header 0 converters   names  str  ages  str    gt  gt  gt  df        names ages    0   bob   05    1   tom   4    2   suzy  3

[python] Python pandas: how to specify data types when reading an Excel file?

Examples related to python

Examples related to pandas

Examples related to dataframe