column with mixed decimals

user_10955284 0
Viewed 95

I have a value column with all sorts of problems it returns an object dtype and it goes like this :

import pandas as pd

data = {'Observation':['1', '2', '3', '4', '5', '6', '7', '8', '9'], 
        'Value':['5.6' , '1 068,0', '1 956.3', '', 'NaN', '65,0', 'nan', '23,5', '30.0']} 
  
df = pd.DataFrame(data)

the main issues are :

  • mixed decimals (point and comma)
  • thousand separator in both types
  • mixed NaN and nan and the empty string for na values
  • point 0 or comma 0 at the end

the desired outcome is a column with float64 datatype with correct NaN values

thank you!

pandas python
3 Answers

Pandas series.replace accepts a dictionary and regex option:

df['Value'] = pd.to_numeric(df['Value'].replace({' ':'', ',':'.'}, regex=True), 
                            errors='coerce')

Output:

  Observation   Value
0           1     5.6
1           2  1068.0
2           3  1956.3
3           4     NaN
4           5     NaN
5           6    65.0
6           7     NaN
7           8    23.5
8           9    30.0
user_42384080

You can just do:

 df['Value'] = pd.to_numeric(df['Value'].str.replace(',', '.').str.replace(' ', ''), 
                             errors='coerce')

Output:

    Observation   Value
0             1     5.6
1             2  1068.0
2             3  1956.3
3             4     NaN
4             5     NaN
5             6    65.0
6             7     NaN
7             8    23.5
8             9    30.0
user_133401430

First you need to preprocess your data, i.e. replace blank spaces, commas and finally remove nans. Here's how I'd do.

df['Value']=df['Value'].astype(str)
df['Value']=df['Value'].apply(lambda x:x.replace(' ',''))
df['Value']=df['Value'].apply(lambda x:x.replace(',','.'))

You can add other preprocessing procedures, according to how your data are depicted. I encapsulate the casting procedure in a function, to further re-use.

def cast_float(x):
   try:
      return float(x)
   except:
      return np.NaN

df['Value']=df['Value'].apply(cast_float)
user_61678390
Related