9

In Python 3 and pandas I have a dataframe with a column cpf with codes

candidatos_2014.info()
<class 'pandas.core.frame.DataFrame'>
Int64Index: 26245 entries, 0 to 1063
Data columns (total 7 columns):
uf                 26245 non-null object
cargo              26245 non-null object
nome_completo      26245 non-null object
cpf                26245 non-null object
nome_urna          26245 non-null object
partido_eleicao    26245 non-null object
situacao           26245 non-null object
dtypes: object(7)
memory usage: 1.6+ MB

The codes are numbers like these: "00229379273", "84274662268", "09681949153", "53135636534"...

I saved as CSV

candidatos_2014.to_csv('candidatos_2014.csv')

I use Ubuntu and LibreOffice. But when I opened the file the cpf column does not show the leading zeros: 

"229379273", "9681949153"

Please, is there a way to save a CSV that keeps zeros to the left in a column that only has numbers?

Sociopath
  • 13,068
  • 19
  • 47
  • 75
Reinaldo Chaves
  • 965
  • 4
  • 16
  • 43

4 Answers4

6

Specify dtype as string while reading the csv file as below:

# if you are reading data with leading zeros
candidatos_2014 = pd.read_csv('candidatos_2014.csv', dtype ='str')

or convert data column into string

# if data is generated in python you can convert column into string first
candidatos_2014['cpf'] = candidatos_2014['cpf'].astype('str')
candidatos_2014.to_csv('candidatos_2014.csv')
Sociopath
  • 13,068
  • 19
  • 47
  • 75
  • Thank you. It is a generated dataframe. I used: candidatos_2014['cpf'] = candidatos_2014['cpf'].astype('str') – Reinaldo Chaves Feb 21 '18 at 10:41
  • candidatos_2014.info() -> cpf 26245 non-null object – Reinaldo Chaves Feb 21 '18 at 10:41
  • I used the command to save: candidatos_2014.to_csv('candidatos_2014.csv') – Reinaldo Chaves Feb 21 '18 at 10:42
  • But still save without leading zeros – Reinaldo Chaves Feb 21 '18 at 10:42
  • what is `print(candidatos_2014.head())` after converting into string? I suggest better edit the question with what you tried rather than commenting. – Sociopath Feb 21 '18 at 10:45
  • Zeros appear to the left when I type the command. I think it might be a LibreOffice problem, it opens the file and ignores the zeros. Could be the configuration of it – Reinaldo Chaves Feb 21 '18 at 10:48
  • 1
    I am using LibreOffice too but I'm getting data with leading zeros. – Sociopath Feb 21 '18 at 10:49
  • I opened the file in gedit. The zeros are on the left yes. Like this: 1,PA,DEPUTADO ESTADUAL,HELEN JANE DA SILVA E SILVA,00229379273,HELEN JANE,PEN,SUPLENTE – Reinaldo Chaves Feb 21 '18 at 10:53
  • Maybe I need to generate this "cpf" column with quotation marks? LibreOffice has the command on the opening screen - "Fields in quotation marks as text" – Reinaldo Chaves Feb 21 '18 at 10:55
  • 1
    Anyway, in the LibreOffice opening screen I selected Column type as text in the "cpf" column and it opened right. Before, the leading zeros did not appear on the opening screen. Thank you very much – Reinaldo Chaves Feb 21 '18 at 11:03
  • @ReinaldoChaves I just came across this same problem, it's was solved with this solution: converters={'column_name': lambda x: str(x)} source:https://stackoverflow.com/questions/13250046/how-to-keep-leading-zeros-in-a-column-when-reading-csv-with-pandas – Jayron Soares Apr 14 '21 at 20:47
4

First, make sure that output in your csv file does not have zeros. If it does, but you are opening that file in Excel or another spreadsheet, you still sometimes can see values without leading zeros. In this case, Go to Data menu, then Import form Text. Excel's import utility will give you options to define each column's data type.

I am sure that it should be similar in other apps.

Hope it helps!

Ivan S.
  • 81
  • 2
3

TLDR: you don't have to do anything if your pandas columns are type object

I feel like both answers here, but especially the accepted answer, are confusing. The short answer is that, if the dtype of your column is object, then pandas will write it with leading zeros. There's nothing to do.

If like me, you came here because you didn't know that for sure and when you opened the CSV, the leading zeros were gone, then follow Ivan S's advice -- take a look at the file you wrote to verify, but you should see the leading zeros there.

If you do, then both answers give guidance on how to read the data back in preserving leading zeros.

If you don't, the the datatype wasn't correct in pandas when you saved the CSV. Just changing that column using astype wouldn't restore the zeros. You'd also need to use str.zfill as described in this SO answer.

Joe Germuska
  • 1,638
  • 1
  • 18
  • 34
0

In case this is helpful for someone, I leveraged to_excel() method rather than to_csv() and this seems to have solved the problem (that Microsoft Excel will destroy leading zeroes when you open a CSV in Excel). Assumes you have retained leading zeroes in the data itself, though.

dbell
  • 35
  • 4