How to split variable length field at first number - Field Calculator

965
3
Jump to solution
10-05-2016 09:32 PM
BenVan_Kesteren1
Regular Contributor

Hi All, 

Sorry for the poorly worded heading, I can't quite find the words to summarise what im trying to say.

So basically I have been given a table with thousands of rows, and I need to split one of the fields, moving the first part of the field into a string column, and the right half of the split to a numeric column.

Here is a Pivot table (grouped) #view of what I need to split:

Incandescent 200 250
Incandescent 300
Incandescent 500
Incandescent 60
Incandescent 75
Low Pressure Sodium 135
Low Pressure Sodium 150
Low Pressure Sodium 310
Low Pressure Sodium 55
Low Pressure Sodium 90 100
Mercury Vapour 1000
Mercury Vapour 125
Mercury Vapour 175
Mercury Vapour 250
Mercury Vapour 400
Mercury Vapour 50
Mercury Vapour 500
Mercury Vapour 700
Mercury Vapour 80
Metal Hallide (Reactor Control Gear) 1000
Metal Hallide (Reactor Control Gear) 150
Metal Hallide (Reactor Control Gear) 250
Metal Hallide (Reactor Control Gear) 400
Metal Hallide (Reactor Control Gear) 70‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍

So with the first row as an example I would like to end up with a column called STRING with 'Incandescent' in it, and a second column called WATTS with '200 250'.

I am trying to use field calculator, I understand I will need to run it twice, once to calculate the STRING and once for WATTS.

Python is my preference, but in this instance im unsure where to start the split.

Cheers!

(Nathan Duncan‌ any ideas?)

0 Kudos
1 Solution

Accepted Solutions
DanPatterson_Retired
MVP Esteemed Contributor
>>> a = "Low Pressure Sodium 90 100"
>>> bulb = "".join([i for i in a if i.isalpha() or i == " "]).strip()
>>> watts = "".join([i for i in a if i.isdigit() or i == " "]).strip() 
>>>
>>> a
'Low Pressure Sodium 90 100'
>>> bulb
'Low Pressure Sodium'
>>> watts
'90 100'

View solution in original post

3 Replies
ModyBuchbinder
Regular Contributor II

Try This:

import re

str1 = "Incandescent 200 250"

loc = re.search("\d",str1)

pos = loc.start()

print "First part = [%s], second part = [%s]" % (str1[:pos], str1[pos:len(str1)])

BenVan_Kesteren1
Regular Contributor

That will work. The output is:

First part = [Incandescent ], second part = [200 250]

So a minor tweak to strip the space (last character) from the first part:

print "First part = [%s], second part = [%s]" % (str1[:pos-1], str1[pos:len(str1)])

I just needed to add the -1 to achieve this.

Looks good, thanks.

DanPatterson_Retired
MVP Esteemed Contributor
>>> a = "Low Pressure Sodium 90 100"
>>> bulb = "".join([i for i in a if i.isalpha() or i == " "]).strip()
>>> watts = "".join([i for i in a if i.isdigit() or i == " "]).strip() 
>>>
>>> a
'Low Pressure Sodium 90 100'
>>> bulb
'Low Pressure Sodium'
>>> watts
'90 100'

View solution in original post