Replace some string values in a pandas dataframe column whose values should be float

Question

I have a dataframe with lat long values, whose types should be float. However, for some rows, you can find things like -74.128815°, with the '°' character in the end of the string.

id	Lat	Long
1	4.807	-75.684
2	4.5405	-75.6658
3		-74.128815°
4	5.35002	-72.4002
5	4.6774°	-75.693

I want to keep all float values as they are but replace the values containing '°' (and then convert them to float), so that in the end I have this:

id	Lat	Long
1	4.807	-75.684
2	4.5405	-75.6658
3		-74.128815
4	5.35002	-72.4002
5	4.6774	-75.693

The DataFrame is named df. I have tried df[df['Lat'].str.contains('°')] which raises ValueError: Cannot mask with non-boolean array containing NA / NaN values error

Also, I've also tried df['Lat'] = np.where(df['Lat'].str.contains('°'), df['Lat'][:-1], df['Lat']) which raises ValueError: operands could not be broadcast together

rafaelc · Accepted Answer · 2022-06-09 23:16:44Z

1

You can replace the invalid character with an empty string, and then just use pd.to_numeric:

degree_sign = u'\N{DEGREE SIGN}' # or degree_sign = "°"
df[['lat', 'long']].replace(degree_sign, '', regex=True)\
                   .apply(pd.to_numeric, errors='coerce')

answered Jun 9, 2022 at 23:16

rafaelc

59.4k15 gold badges64 silver badges87 bronze badges

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

1 Comment

Alex Fidalgo Over a year ago

Thanks a lot! I'm embarassed that didn't cross my mind haha

Onyambu · Accepted Answer · 2022-06-09 23:47:27Z

1

Using regex replace the last non-digit character(s) ('\D+')in the columns, then convert to float:

df[['Lat', 'Long']].replace('\\D+$', '', regex = True).astype(float)

       Lat       Long
0  4.80700 -75.684000
1  4.54050 -75.665800
2      NaN -74.128815
3  5.35002 -72.400200
4  4.67740 -75.693000

answered Jun 9, 2022 at 23:47

Onyambu

80.3k3 gold badges29 silver badges65 bronze badges

Comments

Gold79 · Accepted Answer · 2022-06-10 00:19:50Z

1

You can do a string replace, then change the column type, for example

df['Lat'] = df['Lat'].astype('str').str.replace('°', '', regex=True).astype('float')
df['Long'] = df['Long'].astype('str').str.replace('°', '', regex=True).astype('float')

answered Jun 10, 2022 at 0:19

Gold79

3423 silver badges9 bronze badges

Collectives™ on Stack Overflow

Replace some string values in a pandas dataframe column whose values should be float

3 Answers 3

1 Comment

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

1 Comment

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related