How to separate numbers from a data frame using regular expression?

Question

I have read an entire column from a excel sheet into a dataframe. Each cell in that column has a bunch of words with numbers (like phone numbers). How do I loop the data frame and extract numbers using a specific pattern using regex.

I have tried the following code

for i in (df): 
   df.str.contains('(4[0-9]{12}([0-9]{3})|[25][1-7][0-9]{14}|6(011|5[0-9][0-9])[0-9]{12}|3[47][0-9]{13}')

I know my regex is wrong, but I am getting the following error.

Edit : I have updated my regex. The cells have the data like this

" Hello, I am trying to order something ... my card number is 45621.... ." I want to take out the card number and put it in a file.

Traceback (most recent call last):
  File "c:/Program Files/Python37/Scripts/output.py", line 12, in <module>
    df.str.contains('^f')
  File "C:\Program Files\Python37\lib\site-packages\pandas\core\generic.py", line 5067, in __getattr__
    return object.__getattribute__(self, name)
AttributeError: 'DataFrame' object has no attribute 'str'

https://stackoverflow.com/questions/3868753/find-phone-numbers-in-python-script — BENY, Jun 18 '19 at 17:24
Add some example data so we can see what your _"specific patterns"_ look like. — Erfan, Jun 18 '19 at 17:24
`.str` is defined for a `Series` object, not a `DataFrame` object. Please read the [docs](http://pandas.pydata.org/pandas-docs/stable/reference/api/pandas.Series.str.contains.html) — jeschwar, Jun 18 '19 at 18:08

score 0 · Answer 1 · answered Jun 18 '19 at 17:46

Right now you are calling df.str. This means you are trying to access the str object of dataframe which doesn't make sense to python hence the error. Not sure what you were trying to do there. In your loop i will be the column. From there you can loop through the rows and then apply the regex. This is documented throughout stack overflow but is probably not the approach you want to take.

Instead you should make a function that takes in a cell as a string and output the post-regex string. Then you can use the apply() within pandas to apply that function to each cell all at once. If you google "apply() pandas regex" a bunch of different examples will show you how to do this. One such example is this one.

If you provide a bit more detail of the regex you are trying to accomplish we can help you to make the above structure in more detail.

He's most likely trying to use the string accessor that pandas provides. Using `apply` shouldn't be his first option, even a list comprehension would be better. — user3483203, Jun 18 '19 at 17:49

score 0 · Answer 2 · edited Apr 04 '21 at 09:50

0

first import regular expression

import re

It's better to create a new column

df['new_1'] = re.search('4[0-9]{12}([0-9]{3})|[25][1-7][0-9]{14}|6(011|5[0-9][0-9])[0-9]{12}|3[47][0-9]{13}', df['<num_col_name>'])

Now check the new_1 column

df['new_1]

You haven't posted the column which you wanna go through, so I had to use your entered string.

edited Apr 04 '21 at 09:50

Ynjxsjmh

28,441
6
34
52

answered Apr 04 '21 at 09:40

Ashish Goyal

1

How to separate numbers from a data frame using regular expression?

2 Answers2