replace values that cannot be converted to integer to zero python pandas

Question

Using python3.8, pandas 1.1.4

MRE:

df = pd.DataFrame({"a":[1,2,3,4,5, 6], "b":["01", "02", 1, "//N", None, np.nan]})

I want to convert column b to integer so that "01" = 1 (they should be same), "02"=2, etc... while replacing values that cannot be converted to integer to 0.

Current method:

c = []
for val in df["b"].unique():
    try:
        int(val)
    except:
        c.append(val)
        pass
    
df["b"] = df["b"].replace(c, 0)
df["b"] = df["b"].astype(int)

outputting (it's desired output):

Even though this works, I'm searching for more efficient and readable way.

Henry Ecker · Accepted Answer · 2021-08-20 02:21:10Z

3

We can use pd.to_numeric + fillna to replace NaN with 0 and downcast from float to int:

df['b'] = pd.to_numeric(df['b'], errors='coerce').fillna(0, downcast='int')

Or convert to int with astype:

df['b'] = pd.to_numeric(df['b'], errors='coerce').fillna(0).astype(int)

df:

edited Aug 20, 2021 at 2:21

answered Aug 20, 2021 at 2:15

Henry Ecker♦

35.8k19 gold badges48 silver badges67 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

haneulkim Over a year ago

from documentation, it says when downcast parameter is set to 'integer' it convert to smallest signed int dtype. So I've tried df['b'] = pd.to_numeric(df['b'], errors='coerce', downcast='integer').fillna(0) however this returns float datatype. Do you know why?

Henry Ecker Over a year ago

Smallest type possible. If there is a string "3.45" in b the smallest possible type is float. downcast will not make a dtype change that would result in a data loss. Additionally, NaN is a float so downcast will not work with to_numeric if coercing errors to NaN "As this behaviour is separate from the core conversion to numeric values, any errors raised during the downcasting will be surfaced regardless of the value of the ‘errors’ input.". This is why I added the astype(int) option in case you need to force the reduction from float to int.

U13-Forward · Accepted Answer · 2021-08-20 02:20:31Z

0

You could also try np.where:

x = df['b'].astype(str).str.extract('(\d+)')
df['b'] = np.where(x.notna(), x.astype(float), 0)

And now:

print(df)

Is:

answered Aug 20, 2021 at 2:20

U13-Forward

71.8k15 gold badges100 silver badges125 bronze badges

Collectives™ on Stack Overflow

replace values that cannot be converted to integer to zero python pandas

2 Answers 2

2 Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

2 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related