0
\$\begingroup\$

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.

\$\endgroup\$
3
  • 1
    \$\begingroup\$ You might want to look through the answers to Padding a hexadecimal string with zeros, as it's a very similar problem. \$\endgroup\$ Sep 28 at 8:33
  • 1
    \$\begingroup\$ @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. \$\endgroup\$
    – Reinderien
    Sep 29 at 0:39
  • \$\begingroup\$ @Reinderien, fair enough - I've never worked with pandas, so I'm speaking from a relatively ignorant position. \$\endgroup\$ Sep 29 at 6:55
2
\$\begingroup\$

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
\$\endgroup\$

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.