4

I have a pandas data frame in which one column contains data with leading zeros.I want to export the data frame in CSV by preserving leading zeros. So I tried following code

import numpy as np
import pandas as pd
import os
os.chdir(path)
x=np.array(['0134','0567','0012','0009'])

df=pd.DataFrame(x,columns=['Test'])
df.dtypes
df.Test=df.Test.astype("str")
df.to_csv("leadingZero.csv")

But in leadingZero.csv I'm finding 0's in one column & number discarded zeros in another column

Can you guide me how do I preserve leading zeros in CSV?

Sonia
  • 464
  • 10
  • 21
  • Should work as expected... are you opening `leadingZero.csv` in a text editor? Or are you perhaps opening the file in Excel..? – Chris Adams Jun 11 '19 at 09:37
  • Possible duplicate of [Pandas csv-import: Keep leading zeros in a column](https://stackoverflow.com/questions/13250046/pandas-csv-import-keep-leading-zeros-in-a-column) – 0xPrateek Jun 11 '19 at 13:17
  • "are you opening leadingZero.csv in a text editor" seriously? why do you even ask such a question. Do you think senior management of a corporation would open that file with a text editor? ... – Angelo Jul 19 '23 at 16:03

2 Answers2

2
  • To keep the leading zeros use df.Test.astype("str").
  • To remove index column use: index=False in to_csv method.
import numpy as np
import pandas as pd

x = np.array(['0134','0567','0012','0009'])

df = pd.DataFrame(x, columns=['Test'])
df.Test = df.Test.astype("str")
df.to_csv("leadingZero.csv", index=False)

Output (leadingZero.csv):

Test
0134
0567
0012
0009

View the CSV in Office application with leading Zeros

If you use Office application to open the CSV select the column as Text. I am using Libre Office Calc in an Ubuntu machine.

  • While opening the CSV file, select the column as Text field.

Select column as text field

  • The sheet will have leading zeros:

CSV file with leading zeros in Libre Office

If you are using Microsoft Excel, follow Microsoft's documentation on Format numbers as text.

arshovon
  • 13,270
  • 9
  • 51
  • 69
  • 1
    I recommend using this solution, since it is using the internal vectorized functions, while Prateek Mishra's answer uses lambda (not recommended) in a element-wise fashion (also not recommended). – JE_Muc Jun 11 '19 at 10:15
2

The issue here is with csv file. If you open csv file using different formats like wordpad, notepad, etc., leading zeros will appear

Use apply('="{}".format) on the column where you want to preserve the leading zeros

import pandas as pd
df = pd.DataFrame({'Test': ['0134','0567','0012','0009']})
df['Test'] = df['Test'].apply('="{}"'.format)
df.to_csv('withLeadingZeros'+'.csv', index = False)

output from withLeadingZeros.csv

enter image description here

Govinda
  • 789
  • 7
  • 6
  • This is the BEST solution across tons of totally useless answers where people suggest to use astype and so on. Thank you so much – Angelo Jul 19 '23 at 16:05