change format of string values to numeric in df

Question

I have the following df where some values in the df are strings (those with %) while other ones aren't.

                          test    overall
Quents Ratio            270.01%  256.02%
Amount sulphur            0.17     0.19
Amount salt                  -    20.89
amount silica             4.29%    6.84%

I would like to make all the values numeric given that I would like to carry out some analysis among the 2 columns.

Desired output:

                          test    overall
Quents Ratio            270.01   256.02
Amount sulphur            0.17     0.19
Amount salt                  -    20.89
amount silica             4.29     6.84

What I have tried is to:

def numeric_df(df):
    df_detail=df.loc[['Quents Ratio','amount silica'],:]
    df_detail= df_detail.apply(lambda x:str(x)[:-1])
    return df

But returns same initial df.

How could I obtain the desired output?

hmmm, but then values cannot be numeric, because - is string. — jezrael
– jezrael, Commented Aug 13, 2017 at 9:14
Didn't thought about that, was going apply a 'to_numeric' without thinking that '-' could give me further problems — JamesHudson81
– JamesHudson81, Commented Aug 13, 2017 at 9:14

jezrael · Accepted Answer · 2017-08-13 09:26:15Z

1

I think you need replace, but values contains also -, so impossible convert to numeric:

 df = df.replace('%', '', regex=True)

If need all values numeric and values contains only - chars:

df = df.replace({'%': '', '^-$':np.nan}, regex=True).astype(float)
print (df)
                  test  overall
Quents Ratio    270.01   256.02
Amount sulphur    0.17     0.19
Amount salt        NaN    20.89
amount silica     4.29     6.84

Another solution with to_numeric - it replace all non numeric to NaNs too:

df = df.replace('%', '', regex=True).apply(pd.to_numeric, errors='coerce')
print (df)
                  test  overall
Quents Ratio    270.01   256.02
Amount sulphur    0.17     0.19
Amount salt        NaN    20.89
amount silica     4.29     6.84

edited Aug 13, 2017 at 9:26

answered Aug 13, 2017 at 9:11

jezrael

868k103 gold badges1.4k silver badges1.3k bronze badges

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

Collectives™ on Stack Overflow

change format of string values to numeric in df

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related