Convert string to 10 character string with leading zeros if necessary

Question

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.

Answer 1

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