Add Leading Zeros to Strings in Pandas Dataframe
PythonStringPandasPython Problem Overview
I have a pandas data frame where the first 3 columns are strings:
ID text1 text 2
0 2345656 blah blah
1 3456 blah blah
2 541304 blah blah
3 201306 hi blah
4 12313201308 hello blah
I want to add leading zeros to the ID:
ID text1 text 2
0 000000002345656 blah blah
1 000000000003456 blah blah
2 000000000541304 blah blah
3 000000000201306 hi blah
4 000012313201308 hello blah
I have tried:
df['ID'] = df.ID.zfill(15)
df['ID'] = '{0:0>15}'.format(df['ID'])
Python Solutions
Solution 1 - Python
Try:
df['ID'] = df['ID'].apply(lambda x: '{0:0>15}'.format(x))
or even
df['ID'] = df['ID'].apply(lambda x: x.zfill(15))
Solution 2 - Python
str attribute contains most of the methods in string.
df['ID'] = df['ID'].str.zfill(15)
See more: http://pandas.pydata.org/pandas-docs/stable/text.html
Solution 3 - Python
It can be achieved with a single line while initialization. Just use converters argument.
df = pd.read_excel('filename.xlsx', converters={'ID': '{:0>15}'.format})
so you'll reduce the code length by half :)
PS: read_csv have this argument as well.
Solution 4 - Python
With Python 3.6+, you can also use f-strings:
df['ID'] = df['ID'].map(lambda x: f'{x:0>15}')
Performance is comparable or slightly worse versus df['ID'].map('{:0>15}'.format). On the other hand, f-strings permit more complex output, and you can use them more efficiently via a list comprehension.
Performance benchmarking
# Python 3.6.0, Pandas 0.19.2
df = pd.concat([df]*1000)
%timeit df['ID'].map('{:0>15}'.format) # 4.06 ms per loop
%timeit df['ID'].map(lambda x: f'{x:0>15}') # 5.46 ms per loop
%timeit df['ID'].astype(str).str.zfill(15) # 18.6 ms per loop
%timeit list(map('{:0>15}'.format, df['ID'].values)) # 7.91 ms per loop
%timeit ['{:0>15}'.format(x) for x in df['ID'].values] # 7.63 ms per loop
%timeit [f'{x:0>15}' for x in df['ID'].values] # 4.87 ms per loop
%timeit [str(x).zfill(15) for x in df['ID'].values] # 21.2 ms per loop
# check results are the same
x = df['ID'].map('{:0>15}'.format)
y = df['ID'].map(lambda x: f'{x:0>15}')
z = df['ID'].astype(str).str.zfill(15)
assert (x == y).all() and (x == z).all()
Solution 5 - Python
If you are encountering the error:
Pandas error: Can only use .str accessor with string values, which use np.object_ dtype in pandas
df['ID'] = df['ID'].astype(str).str.zfill(15)
Solution 6 - Python
If you want a more customizable solution to this problem, you can try pandas.Series.str.pad
df['ID'] = df['ID'].astype(str).str.pad(15, side='left', fillchar='0')
str.zfill(n) is a special case equivalent to str.pad(n, side='left', fillchar='0')
Solution 7 - Python
rjust worked for me:
df['ID']= df['ID'].str.rjust(15,'0')