string column conversion to float in Pandas DataFrame-CodePudding

I want to get left value (LD) pipe separated value from the DataFrame column "'CA Distance Nominal (LD | au)" here is the code. when I convert the string to float I get all the values as NaN.

cneos = pd.read_csv('cneos.csv')
print(cneos['CA Distance Nominal (LD | au)'].head())
cneos['Distance']=pd.to_numeric(cneos['CA Distance Nominal (LD | au)'], errors='coerce')
print(cneos['Distance'].head())

Result

0    2.02 | 0.00520
1    0.39 | 0.00100
2    8.98 | 0.02307
3    3.88 | 0.00996
4    4.84 | 0.01244
Name: CA Distance Nominal (LD | au), dtype: object

After to_numeric()

0   NaN
1   NaN
2   NaN
3   NaN
4   NaN
Name: Distance, dtype: float64

How can I get the both values LD and AU separated in float

CodePudding user response：

I'm not sure that it is the best way to resolve your problem, but it works:

separeted_data_frame = pd.DataFrame(cneos['CA Distance Nominal (LD | au)'].apply(lambda x: x.split('|')).to_list())
separeted_data_frame.columns = ['LD', 'AU']
separeted_data_frame.LD = separeted_data_frame.LD.astype(float)
separeted_data_frame.AU = separeted_data_frame.AU.astype(float)
cneos = cneos.join(separeted_data_frame).drop('CA Distance Nominal (LD | au)', 1)

The result is:

    LD  AU
0   2.02    0.00520
1   0.39    0.00100
2   8.98    0.02307
3   3.88    0.00996
4   4.84    0.01244

Is it what you wanted?