Deleting decimals and non digits from string column using regex

Question

I have a dataframe column with strings like this:

df.column1:
0 R$ 27.467.522,00 (Vinte e sete milhões, quatro...
1 NaN
2 R$ 35.314.312,12 (Trinta e cinco milhões, trezentos...
3 R$ 1.231,34 (Mil duzentos e trinta e um reais e...

I want only to get the numbers, disconsidering the decimals, so it gets to look like this:

df.column1:
0 27467522
1 NaN
2 35314312
3 1231

I'm trying to do that with regex:

df['column1']=df['column1'].str.extract('[REGEX CODE]')

However I'm not used with Regex. I tried solutions like:

df['column1']=df['column1'].str.extract('(.*?,)').str.extract('(\d+)')
df['column1']=df['column1'].str.extract('(\s*,.*)').str.extract('(\d+)')

But I haven't been able to make it right. Can someone help?

user3483203 · Accepted Answer · 2018-06-04 00:56:18Z

2

Use str.replace then str.extract

df.column1.str.replace('.', '').str.extract(r'(\d+)')

          0
0  27467522
1       NaN
2  35314312
3      1231

Decimals are indicated by commas here, so by replacing periods and using extract to find the first match, the number will be matched, ignoring the decimal.

edited Jun 4, 2018 at 0:56

answered Jun 4, 2018 at 0:41

user3483203

51.3k10 gold badges72 silver badges104 bronze badges

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

4 Comments

aabujamra Over a year ago

Thanks for your effort but that doesn't pal. We will keep the decimals this way

user3483203 Over a year ago

Can you explain? It seems to me this matches your desired output.

aabujamra Over a year ago

Sorry, you are correct, my confusion here. I didn't get how you made that work but it is working perfectly

Dux Over a year ago

if you want to keep the decimals, change the regex to r'(\d+(,\d+)?). This way, it can cover both integers and decimals. Afterwards, you may replace the ',' with '.'

Collectives™ on Stack Overflow

Deleting decimals and non digits from string column using regex

1 Answer 1

4 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

4 Comments

Your Answer

Sign up or log in

Post as a guest

Related