Pandas conditionally change cells with regex

user_2415706 0
Viewed 306

I have a data frame with some cell values such as $1,245 which are interpreted as objects.

How can I write a lambda function to remove the '$' and ',' and cast to float in only cells whose entry contains a '$'?

I was able to get it working without the if-else before. but that also messed up other string columns that do not have $ in them.

df = df.apply(lambda x: x.str.replace('[$,]', 
'').astype(float) if '$' in x else x)

Sample Data:

Programmer, Hawaii,"$115,887","$9,657","$2,229",$55.72
Engineering Manager, West Virginia,"$115,420","$9,618","$2,220",$55.49
Data Scientist, Pennsylvania,"$114,863","$9,572","$2,209",$55.22
pandas apply regex
1 Answers

Change the replace() to use regex=True with a raw r-string and change the filter to test x.str.contains('[$,]').all():

df = (df.apply(lambda x:
    x.str.replace(r'[$,]', '', regex=True).astype(float)
    if x.str.contains('[$,]').all() else x))

#                      0               1         2       3       4      5
# 0           Programmer          Hawaii  115887.0  9657.0  2229.0  55.72
# 1  Engineering Manager   West Virginia  115420.0  9618.0  2220.0  55.49
# 2       Data Scientist    Pennsylvania  114863.0  9572.0  2209.0  55.22

You can also applymap() by chaining 2x replace():

df = df.applymap(lambda x: float(x.replace('$', '').replace(',', '')) if '$' in x else x)
user_131383640
Related