# Convert string to 10 character string with leading zeros if necessary

I have the below data and am obtaining the expected output column for it.

DATA          EXPECTED_OUTPUT       EXPLANATION
----------------------------------------------------------------------------------------------
123      0000000123       7 leading 0s to get to 10 characters.
nan            None       If data is not numeric, then null/None output.
123a            None       If data is not numeric, then null/None output.
1111111111119      1111111119       If data length >10, truncate to right 10 characters.
0      0000000000       9 leading 0s to get to 10 characters.
123.0      0000000123       Remove decimal part and leading 0s to get to 10 characters.


I have 3 ways currently of doing so, but I'm unsure whether they are the optimal solution to handle all data possibilities.

# Option 1
df['DATA'] =pd.to_numeric(df['DATA'], errors='coerce').fillna(0).\
astype(np.int64).apply(str).str.zfill(10)

# Option 2
df['DATA'] = df['DATA'].map('{:0>10}'.format).astype(str).\
str.slice(0, 10)

# Option 3
df['DATA'] = df['DATA'].astype(str).str.split('.', expand=True)[0].str\
.zfill(10).apply(lambda x_trunc: x_trunc[:10])


Any help on how to improve the way of doing this will be truly appreciated.

• You might want to look through the answers to Padding a hexadecimal string with zeros, as it's a very similar problem. Sep 28 at 8:33
• @TobySpeight Sadly, I don't think that's going to buy OP a lot, since the pandas vectorized approach doesn't support the typical native approach. Sep 29 at 0:39
• @Reinderien, fair enough - I've never worked with pandas, so I'm speaking from a relatively ignorant position. Sep 29 at 6:55

Are you sure you want the non-numeric case to result in None? Pandas more commonly uses NaN. Anyway.

You're missing an important test case: what do you want to happen for values with a non-zero post-decimal half?

Option 1 is broken because you're just substituting 0 for non-numeric cases.

Option 2 is non-vectorized due to the map, and will not catch 123a; so it's broken too. As a bonus, your slice(0, 10) is slicing from the wrong end of the string.

Option 3 is non-vectorized due to the apply, and does manual parsing of the decimal which is slightly awkward; it's also not going to catch non-numerics so it's broken.

So I mean... if I were to be picky, none of your code is functioning as intended so closure would be justified; but what the heck:

One approach that does not break vectorization and meets all of the edge cases would be

• Call to_numeric(errors='coerce') as you do in #1
• For valid numerals only, maintaining the original index:
• cast to int to drop the decimal
• cast to str
• zfill and slice (from the end, not the beginning)
• Save back to the data frame on valid indices only
• Fill the invalid indices with None (fillna is not sufficient for this purpose)

## Suggested

import pandas as pd

df = pd.DataFrame(
[
123,
float('nan'),
'123a',
1111111111119,
0,
123.0,
123.4,
],
columns=('DATA',)
)
as_floats = pd.to_numeric(df.DATA, errors='coerce')
as_strings = (
as_floats[pd.notna(as_floats)]
.astype(int)
.astype(str)
.str.zfill(10)
.str.slice(-10)
)
df['out'] = as_strings
df.loc[pd.isna(as_floats), 'out'] = None