pandas applying regex to replace values

Question

I have read some pricing data into a pandas dataframe the values appear as:

$40,000*
$40000 conditions attached

I want to strip it down to just the numeric values. I know I can loop through and apply regex

[0-9]+

to each field then join the resulting list back together but is there a not loopy way?

Thanks

score 149 · Answer 1 · edited Jan 03 '22 at 03:12

149

You could use Series.str.replace:

import pandas as pd

df = pd.DataFrame(['$40,000*','$40000 conditions attached'], columns=['P'])
print(df)
#                             P
# 0                    $40,000*
# 1  $40000 conditions attached

df['P'] = df['P'].str.replace(r'\D+', '', regex=True).astype('int')
print(df)

yields

       P
0  40000
1  40000

since \D matches any character that is not a decimal digit.

edited Jan 03 '22 at 03:12

wjandrea

28,235
9
60
81

answered Mar 23 '14 at 12:39

unutbu

842,883
184
1,785
1,677

Any way to do this inPlace? – SagarM Jul 13 '23 at 12:09

Pluto · Answer 2 · 2017-01-12T09:33:19.953

28

You could use pandas' replace method; also you may want to keep the thousands separator ',' and the decimal place separator '.'

import pandas as pd

df = pd.DataFrame(['$40,000.32*','$40000 conditions attached'], columns=['pricing'])
df['pricing'].replace(to_replace="\$([0-9,\.]+).*", value=r"\1", regex=True, inplace=True)
print(df)
pricing
0  40,000.32
1      40000

edited Jan 12 '17 at 09:33

answered Jan 12 '17 at 09:17

Pluto

816
10
9

score 21 · Accepted Answer · answered Mar 23 '14 at 07:51

21

You could remove all the non-digits using re.sub():

value = re.sub(r"[^0-9]+", "", value)

regex101 demo

answered Mar 23 '14 at 07:51

Jerry

70,495
13
100
144

1

`\D+` will be the smallest :-P – Sabuj Hassan Mar 23 '14 at 07:56
2

whats the best way to apply it to the column in the dataframe? so I have df['pricing'] do I just loop row by row? – KillerSnail Mar 23 '14 at 07:57
@KillerSnail I don't have much experience with pandas, but I think that you should be able to use it like this: `df['pricing'] = re.sub(r"[^0-9]+", "", df['pricing'])`. – Jerry Mar 23 '14 at 08:14
@SabujHassan Smaller doesn't necessarily mean better. `\D` tends to be slower and that's not better at all. – Jerry Mar 23 '14 at 08:15
33

ok I think I got it for pandas use: df['Pricing'].replace(to_replace='[^0-9]+', value='',inplace==True,regex=True) the .replace method uses re.sub – KillerSnail Mar 23 '14 at 08:55
@KillerSnail Nice, thanks for letting me know too :) – Jerry Mar 23 '14 at 08:56
3

caution - stripping all non digit symbols would remove negative sign decimal point, and join together unrelated numbers, e.g. "$8.99 but $2 off with coupon" becomes "8992", "$5.99" becomes "499", "$5" becomes "5". – ChuckCottrill Apr 26 '17 at 17:52
3

@KillerSnail Your solution needs one correction: The double equals (==) after inplace should be replaced by single equals (=) df['Pricing'].replace(to_replace='[^0-9]+', value='',inplace=True,regex=True) – Tapa Dipti Sitaula Apr 24 '19 at 05:06

score 8 · Answer 4 · answered Jan 23 '16 at 20:23

8

You don't need regex for this. This should work:

df['col'] = df['col'].astype(str).convert_objects(convert_numeric=True)

answered Jan 23 '16 at 20:23

samthebrand

3,020
7
41
56

score 1 · Answer 5 · answered Jan 28 '21 at 04:37

In case anyone is still reading this. I'm working on a similar problem and need to replace an entire column of pandas data using a regex equation I've figured out with re.sub

To apply this on my entire column, here's the code.

#add_map is rules of replacement for the strings in pd df.
add_map = dict([
    ("AV", "Avenue"),
    ("BV", "Boulevard"),
    ("BP", "Bypass"), 
    ("BY", "Bypass"),
    ("CL", "Circle"),
    ("DR", "Drive"),
    ("LA", "Lane"),
    ("PY", "Parkway"),
    ("RD", "Road"),
    ("ST", "Street"),
    ("WY", "Way"),
    ("TR", "Trail"),
    
      
])

obj = data_909['Address'].copy() #data_909['Address'] contains the original address'
for k,v in add_map.items(): #based on the rules in the dict
    rule1 = (r"(\b)(%s)(\b)" % k) #replace the k only if they're alone (lookup \
b)
    rule2 = (lambda m: add_map.get(m.group(), m.group())) #found this online, no idea wtf this does but it works
    obj = obj.str.replace(rule1, rule2, regex=True, flags=re.IGNORECASE) #use flags here to avoid the dictionary iteration problem
data_909['Address_n'] = obj #store it!

Hope this helps anyone searching for the problem I had. Cheers

The `rule2 = (lambda...` is used as a callable, therefore in your `obj.str.replace` the regex is passed the match object, i.e. your dictionary key to lookup the value pair to replace. Read [pandas.Series.str.replace](https://pandas.pydata.org/pandas-docs/stable/reference/api/pandas.Series.str.replace.html) and [dict.get()](https://www.programiz.com/python-programming/methods/dictionary/get) for more information. If anyone has any clarification on the `m.group()` function please let me know. — cookies, Jan 30 '21 at 12:35

pandas applying regex to replace values

5 Answers5

Linked

Related