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

Question

I am trying to export a dataframe with a column with leading zeros like this:

df["CD_LIN_NEG"]

0     001
1     001
2     004
3     001
4     001
5     001
6     003
7     006
Name: CD_LIN_NEG, dtype: object

But when I export to csv, all of the leading zeros are cut off any numbers when I open the file in Excel. How can I keep the zeros?

I have tried to convert to string but it doesn't work:

df["CD_LIN_NEG"] = df['T_PROD_CP.LN'].astype(str).apply(lambda x: x.zfill(3))

or in this way:

df["CD_LIN_NEG"] = '00' + df['T_PROD_CP.LN'].astype(str)

Answer 1

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

Answer 2

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)

Answer 3

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.