topic Re: How to split variable length field at first number - Field Calculator in Python Questions

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

BenVan_Kesteren1 — Sun, 12 Dec 2021 02:32:18 GMT

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?)

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

ModyBuchbinder — Thu, 06 Oct 2016 04:52:00 GMT

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)])

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

BenVan_Kesteren1 — Thu, 06 Oct 2016 05:08:03 GMT

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.

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

DanPatterson_Retired — Sun, 12 Dec 2021 02:32:21 GMT

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