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

965
3
10-05-2016 09:32 PM
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 250Incandescent 300Incandescent 500Incandescent 60Incandescent 75Low Pressure Sodium 135Low Pressure Sodium 150Low Pressure Sodium 310Low Pressure Sodium 55Low Pressure Sodium 90 100Mercury Vapour 1000Mercury Vapour 125Mercury Vapour 175Mercury Vapour 250Mercury Vapour 400Mercury Vapour 50Mercury Vapour 500Mercury Vapour 700Mercury Vapour 80Metal Hallide (Reactor Control Gear) 1000Metal Hallide (Reactor Control Gear) 150Metal Hallide (Reactor Control Gear) 250Metal Hallide (Reactor Control Gear) 400Metal 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?)

Tags (3)
1 Solution

Accepted Solutions
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'‍‍‍‍‍‍‍‍‍‍``
3 Replies
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)])

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.

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'‍‍‍‍‍‍‍‍‍‍``