Replace string with numerical value while preserving NaN

Question

I have a case of needing to replace string values across a number of columns:

The value to substitute changes per column
I need to preserve existing NaN

I have a series of steps that seems that it should work, to me, but does not; the 'inplace' step does not work. Some dummy test code:

make a dataframe

df = pd.DataFrame([[np.nan, 2, np.nan, np.nan],
                   [3, 4, np.nan, 1],
                   [np.nan, np.nan, np.nan, 5],
                   [np.nan, 3, np.nan, 'foo']],
                  columns=list('ABCD'))

calculate substitute value, say from last column

special_value = pd.to_numeric(df['D'], errors='corece').min() / 2
special_value

0.5

have a look

df

seems to work here

pd.to_numeric(df['D'].dropna(), errors='coerce').fillna(value=special_value)

1    1.0
2    5.0
3    0.5
Name: D, dtype: float64

but no, it doesn't

pd.to_numeric(df['D'].dropna(), errors='coerce').fillna(value=special_value, inplace = True)

0    NaN
1      1
2      5
3    foo
Name: D, dtype: object

What is the code you shared meant to demonstrate? the 'inplace' step does not work. Isn't using inplace=True strongly discouraged, anyway? IIRC they're likely going to get rid of it. — AMC
– AMC, Commented Feb 15, 2020 at 6:01
I have added outputs, to show that the code I shared demonstrates the lack of working-ness (based on what I might have expected!) — Shawn
– Shawn, Commented Feb 15, 2020 at 21:27

Shawn · Accepted Answer · 2020-02-16 06:02:38Z

1

If you use .fillna, it is not going to preserve NaN values.

Try this:

def add_value(df,col):
    condition = df[col].apply(lambda x : True if type(x) == int else False) 
    sp_value = df[col][condition].min()/2 
    df[col] = df[col].apply(lambda x : sp_value if type(x) == str else x)

output is

add_value(df,'D')

   A    B   C   D
0   NaN 2.0 NaN NaN
1   3.0 4.0 NaN 1.0
2   NaN NaN NaN 5.0
3   NaN 3.0 NaN 0.5

edited Feb 16, 2020 at 6:02

Shawn

6133 gold badges8 silver badges17 bronze badges

answered Feb 15, 2020 at 12:59

LaChatonnn

1597 bronze badges

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

2 Comments

Shawn Over a year ago

Yes, I special_value runs without error; see my edits to question

LaChatonnn Over a year ago

@Shawn First, I know why I have an error now. You typo on the error arg errors='corece'. And for the inplace , I think it is about pd.to_numeric. It returns a new Series, not updating the original data. So, when you call .fillna, it applies to the new data. The solution should be df['D'] = pd.to_numeric(df['D'].dropna(), errors='coerce').fillna(special_value)

Collectives™ on Stack Overflow

Replace string with numerical value while preserving NaN

make a dataframe

have a look

seems to work here

but no, it doesn't

1 Answer 1

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

make a dataframe

have a look

seems to work here

but no, it doesn't

1 Answer 1

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related