Data Nitro and regular expression (Python)

Question

I have this column in MS Excel 2010 - which has a combination of 'zip code' and 'email-ids'

I am trying to extract these zip-codes(20530, 90012-3308 etc.) from this column.

    20530 mark@ucvxcx.gov
    20530 kidafd@gmail.com
    20530 vladfeodsaf@usdodfaadj.govv
    20530 syadfadsbil.vvbvx@vnvnvn.gov
    20004 safdbnvis9dfg@infdda.gov
    20530 vhlhsdlf8dlfha@vbvbcxbUI.GOV
    90012-3308  h.james@asdfad.gov
    90012-3308  madsfl.hjlkdjd@pkdoi.gov
    90012 mark.fraser@ruskgb.zx

I tried Python's re module.

import re


for i in range(1, 9):
     Cell(i, 4).value = re.findall(r'\d+', Cell(i, 1).value) #storing result in column4

I ran the regex on that column and I got this result:

[u'20530']
[u'20530']
[u'20530']
[u'20530']
[u'20004', u'9']
[u'20530', u'8']
[u'90012', u'3308']
[u'90012', u'3308']
[u'90012']

How can I extract the results, into the human readable zip-code form?

And what were the results of your regex experiment? Why not just `.split()[0]`? — jonrsharpe, May 15 '14 at 19:04
`[u'20530'] [u'20530'] [u'20530'] [u'20530'] [u'20004', u'9'] [u'20530', u'8'] [u'90012', u'3308'] [u'90012', u'3308'] [u'90012']` — hky404, May 15 '14 at 22:49

score 6 · Answer 1 · answered May 15 '14 at 19:03

Why can't you just split?

>>> '20530 mark@ucvxcx.gov'.split()
['20530', 'mark@ucvxcx.gov']

Then just grab the first element.

>>> '20530 mark@ucvxcx.gov'.split()[0]
'20530'

For all your data:

l = ['20530 mark@ucvxcx.gov',
     '20530 kidafd@gmail.com',
     '20530 vladfeodsaf@usdodfaadj.gov',
     '20530 syadfadsbil.vvbvx@vnvnvn.gov',
     '20004 safdbnvis9dfg@infdda.gov',
     '20530 vhlhsdlf8dlfha@vbvbcxbUI.GOV',
     '90012-3308  h.james@asdfad.gov',
     '90012-3308  madsfl.hjlkdjd@pkdoi.gov',
     '90012 mark.fraser@ruskgb.zx']

[entry.split()[0] for entry in l]

Result

['20530', '20530', '20530', '20530', '20004', '20530', '90012-3308', '90012-3308', '90012']

Actually, I am working with 400 rows here, and manually adding commas and quotations around a particular list item is not feasible, I am talking about your **list l** — hky404, May 15 '14 at 21:35
You can take `str(Cell(i).value)` for example to turn it into a string. — Cory Kramer, May 15 '14 at 22:42

Jonathan Benn · Accepted Answer · 2014-05-16T18:53:28.840

The following regular expression will match each string and extract the postal code as group 1:

([\d\-]+)\s+[\w@\.]+

Here's the Python code to extract all of the postal codes at once:

import re
text = r'''    20530 mark@ucvxcx.gov
    20530 kidafd@gmail.com
    20530 vladfeodsaf@usdodfaadj.govv
    20530 syadfadsbil.vvbvx@vnvnvn.gov
    20004 safdbnvis9dfg@infdda.gov
    20530 vhlhsdlf8dlfha@vbvbcxbUI.GOV
    90012-3308  h.james@asdfad.gov
    90012-3308  madsfl.hjlkdjd@pkdoi.gov
    90012 mark.fraser@ruskgb.zx'''
re.compile(r'([\d\-]+)\s+[\w@\.]+').findall(text)

score 1 · Answer 3 · answered Jul 30 '14 at 08:22

just an additional note making answer specific to your original question on DataNitro.

Have done lots of DataNitro loopinfg like that and the most efficiant way of reading in a whole column is:

l = Cell("A1").vertical
# returns a list of all values starting in A1 going down to 1st blank cell

combining with @cyber's solution two liner will give you your answer:

l = Cell("A1").vertical
[entry.split()[0] for entry in l]

or if you prefer flexibility of regex Johnathan Benn answer becomomes:

l = Cell("A1").vertical
[re.compile(r'([\d\-]+)\s+[\w@\.]+').findall(entry) for entry in l]

Data Nitro and regular expression (Python)

3 Answers3