0

I have a Excel file 'test.xls' file which I could read in as following:

    df=  pd.read_excel('test.xls',sep='\t',header=1)

I checked df and found there is a column zip_code which contain zip code like 00051, 00123, but the first 0 was cut off while the system read in the excel file.

So that column will be 51, 123. How could I keep the first zeros intact while reading the file? Thanks. Found solution as I posted below.

Alexandr
  • 243
  • 2
  • 3
  • 15
newleaf
  • 2,257
  • 8
  • 32
  • 52
  • 1
    it converted text to integer, check if you can in read_excel declare type of data in columns. Or use string formating to convert integers to string with leading zeros. – furas Jan 25 '17 at 22:33
  • use a dictionary to set `dtype` of the column considered – MMF Jan 25 '17 at 22:35
  • Since the OP found the answer in the linked question, this should be closed as a duplicate of [Python pandas: how to specify data types when reading an Excel file?](http://stackoverflow.com/questions/32591466/python-pandas-how-to-specify-data-types-when-reading-an-excel-file) – Tony Jan 25 '17 at 23:14
  • Except by reading the previous comment (Tony), there is no indication that is related to the `pandas` library. I suggest adding the tag. – Sci Prog Jan 27 '17 at 00:31

1 Answers1

1

Found a answer here:

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

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})

 df
      names ages
  0   bob   05
  1   tom   4
  2   suzy  3

Thanks @tnknepp

Community
  • 1
  • 1
newleaf
  • 2,257
  • 8
  • 32
  • 52
  • I tried the above converter but still have the problem. I checked the original Excel file , the zip code was stored as zip code format. I finally found this to work around: http://stackoverflow.com/questions/33137686/python-loading-zip-codes-into-a-dataframe-as-strings df['zipcode'] = df['zipcode'].astype(str).str.zfill(5) – newleaf Jan 27 '17 at 00:04