How can I keep leading zeros in a column, when I export to CSV?

This is an excel problem as @EdChum suggested. You'll want to wrap your column in ="" with apply('="{}".format). This will tell excel to treat the entry as a formula that returns the text within quotes. That text will be your values with leading zeros.

Consider the following example.

df = pd.DataFrame(dict(A=['001', '002']))
df.A = df.A.apply('="{}"'.format)
df.to_excel('test_leading_zeros.xlsx')

This may not be directly relevant to the question but if the data is read from external sources via pandas.read_csv() or pandas.read_excel(), then we could specify converters for relevant columns using str.

For example,

import pandas as pd

df = pd.read_excel(
    './myexcel.xlsx',
    converters={
        "serialno": str, # Ensure serialno is read as string, maintaining leading 0's
        "location": lambda x: '-' if x=='' else str(x),
    }

df1 = pd.read_excel(
    './mycsv.csv',
    converters={
        "serialno": str, # Ensure serialno is read as string, maintaining leading 0's
        "location": lambda x: '-' if x=='' else str(x),
    }

When the data is saved to Excel or CSV files, the leading 0's are maintained.


The most simple solution is to just add dtype=str while reading txt or csv file in Pandas:

df = pd.read_csv(r'C:\my_folder\my_file.csv', dtype=str)