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

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

nav[aria-label="Primary Navigation"] { padding: 0; & ul { list-style: none; width: 100%; display: flex; flex-direction: row; justify-content: start; align-items: start; gap: 30px; padding: 0; & li { margin: 0; } & ul li { list-style: none; } } }

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

BenVan_Kesteren1

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 <SPAN class="number token">200</SPAN> <SPAN class="number token">250</SPAN>
Incandescent <SPAN class="number token">300</SPAN>
Incandescent <SPAN class="number token">500</SPAN>
Incandescent <SPAN class="number token">60</SPAN>
Incandescent <SPAN class="number token">75</SPAN>
Low Pressure Sodium <SPAN class="number token">135</SPAN>
Low Pressure Sodium <SPAN class="number token">150</SPAN>
Low Pressure Sodium <SPAN class="number token">310</SPAN>
Low Pressure Sodium <SPAN class="number token">55</SPAN>
Low Pressure Sodium <SPAN class="number token">90</SPAN> <SPAN class="number token">100</SPAN>
Mercury Vapour <SPAN class="number token">1000</SPAN>
Mercury Vapour <SPAN class="number token">125</SPAN>
Mercury Vapour <SPAN class="number token">175</SPAN>
Mercury Vapour <SPAN class="number token">250</SPAN>
Mercury Vapour <SPAN class="number token">400</SPAN>
Mercury Vapour <SPAN class="number token">50</SPAN>
Mercury Vapour <SPAN class="number token">500</SPAN>
Mercury Vapour <SPAN class="number token">700</SPAN>
Mercury Vapour <SPAN class="number token">80</SPAN>
Metal Hallide <SPAN class="punctuation token">(</SPAN>Reactor Control Gear<SPAN class="punctuation token">)</SPAN> <SPAN class="number token">1000</SPAN>
Metal Hallide <SPAN class="punctuation token">(</SPAN>Reactor Control Gear<SPAN class="punctuation token">)</SPAN> <SPAN class="number token">150</SPAN>
Metal Hallide <SPAN class="punctuation token">(</SPAN>Reactor Control Gear<SPAN class="punctuation token">)</SPAN> <SPAN class="number token">250</SPAN>
Metal Hallide <SPAN class="punctuation token">(</SPAN>Reactor Control Gear<SPAN class="punctuation token">)</SPAN> <SPAN class="number token">400</SPAN>
Metal Hallide <SPAN class="punctuation token">(</SPAN>Reactor Control Gear<SPAN class="punctuation token">)</SPAN> <SPAN class="number token">70</SPAN>‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍<SPAN class="line-numbers-rows"><SPAN>‍</SPAN><SPAN>‍</SPAN><SPAN>‍</SPAN><SPAN>‍</SPAN><SPAN>‍</SPAN><SPAN>‍</SPAN><SPAN>‍</SPAN><SPAN>‍</SPAN><SPAN>‍</SPAN><SPAN>‍</SPAN><SPAN>‍</SPAN><SPAN>‍</SPAN><SPAN>‍</SPAN><SPAN>‍</SPAN><SPAN>‍</SPAN><SPAN>‍</SPAN><SPAN>‍</SPAN><SPAN>‍</SPAN><SPAN>‍</SPAN><SPAN>‍</SPAN><SPAN>‍</SPAN><SPAN>‍</SPAN><SPAN>‍</SPAN><SPAN>‍</SPAN></SPAN>

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

Find more posts tagged with

Accepted answers

DanPatterson_Retired

<SPAN class="operator token">>></SPAN><SPAN class="operator token">></SPAN> a <SPAN class="operator token">=</SPAN> <SPAN class="string token">"Low Pressure Sodium 90 100"</SPAN>
<SPAN class="operator token">>></SPAN><SPAN class="operator token">></SPAN> bulb <SPAN class="operator token">=</SPAN> <SPAN class="string token">""</SPAN><SPAN class="punctuation token">.</SPAN>join<SPAN class="punctuation token">(</SPAN><SPAN class="punctuation token">[</SPAN>i <SPAN class="keyword token">for</SPAN> i <SPAN class="keyword token">in</SPAN> a <SPAN class="keyword token">if</SPAN> i<SPAN class="punctuation token">.</SPAN>isalpha<SPAN class="punctuation token">(</SPAN><SPAN class="punctuation token">)</SPAN> <SPAN class="operator token">or</SPAN> i <SPAN class="operator token">==</SPAN> <SPAN class="string token">" "</SPAN><SPAN class="punctuation token">]</SPAN><SPAN class="punctuation token">)</SPAN><SPAN class="punctuation token">.</SPAN>strip<SPAN class="punctuation token">(</SPAN><SPAN class="punctuation token">)</SPAN>
<SPAN class="operator token">>></SPAN><SPAN class="operator token">></SPAN> watts <SPAN class="operator token">=</SPAN> <SPAN class="string token">""</SPAN><SPAN class="punctuation token">.</SPAN>join<SPAN class="punctuation token">(</SPAN><SPAN class="punctuation token">[</SPAN>i <SPAN class="keyword token">for</SPAN> i <SPAN class="keyword token">in</SPAN> a <SPAN class="keyword token">if</SPAN> i<SPAN class="punctuation token">.</SPAN>isdigit<SPAN class="punctuation token">(</SPAN><SPAN class="punctuation token">)</SPAN> <SPAN class="operator token">or</SPAN> i <SPAN class="operator token">==</SPAN> <SPAN class="string token">" "</SPAN><SPAN class="punctuation token">]</SPAN><SPAN class="punctuation token">)</SPAN><SPAN class="punctuation token">.</SPAN>strip<SPAN class="punctuation token">(</SPAN><SPAN class="punctuation token">)</SPAN>&nbsp; 
<SPAN class="operator token">>></SPAN><SPAN class="operator token">></SPAN>
<SPAN class="operator token">>></SPAN><SPAN class="operator token">></SPAN> a
<SPAN class="string token">'Low Pressure Sodium 90 100'</SPAN>
<SPAN class="operator token">>></SPAN><SPAN class="operator token">></SPAN> bulb
<SPAN class="string token">'Low Pressure Sodium'</SPAN>
<SPAN class="operator token">>></SPAN><SPAN class="operator token">></SPAN> watts
<SPAN class="string token">'90 100'</SPAN>
<SPAN class="line-numbers-rows"><SPAN>‍</SPAN><SPAN>‍</SPAN><SPAN>‍</SPAN><SPAN>‍</SPAN><SPAN>‍</SPAN><SPAN>‍</SPAN><SPAN>‍</SPAN><SPAN>‍</SPAN><SPAN>‍</SPAN><SPAN>‍</SPAN></SPAN>

All comments

DanPatterson_Retired

<SPAN class="operator token">>></SPAN><SPAN class="operator token">></SPAN> a <SPAN class="operator token">=</SPAN> <SPAN class="string token">"Low Pressure Sodium 90 100"</SPAN>
<SPAN class="operator token">>></SPAN><SPAN class="operator token">></SPAN> bulb <SPAN class="operator token">=</SPAN> <SPAN class="string token">""</SPAN><SPAN class="punctuation token">.</SPAN>join<SPAN class="punctuation token">(</SPAN><SPAN class="punctuation token">[</SPAN>i <SPAN class="keyword token">for</SPAN> i <SPAN class="keyword token">in</SPAN> a <SPAN class="keyword token">if</SPAN> i<SPAN class="punctuation token">.</SPAN>isalpha<SPAN class="punctuation token">(</SPAN><SPAN class="punctuation token">)</SPAN> <SPAN class="operator token">or</SPAN> i <SPAN class="operator token">==</SPAN> <SPAN class="string token">" "</SPAN><SPAN class="punctuation token">]</SPAN><SPAN class="punctuation token">)</SPAN><SPAN class="punctuation token">.</SPAN>strip<SPAN class="punctuation token">(</SPAN><SPAN class="punctuation token">)</SPAN>
<SPAN class="operator token">>></SPAN><SPAN class="operator token">></SPAN> watts <SPAN class="operator token">=</SPAN> <SPAN class="string token">""</SPAN><SPAN class="punctuation token">.</SPAN>join<SPAN class="punctuation token">(</SPAN><SPAN class="punctuation token">[</SPAN>i <SPAN class="keyword token">for</SPAN> i <SPAN class="keyword token">in</SPAN> a <SPAN class="keyword token">if</SPAN> i<SPAN class="punctuation token">.</SPAN>isdigit<SPAN class="punctuation token">(</SPAN><SPAN class="punctuation token">)</SPAN> <SPAN class="operator token">or</SPAN> i <SPAN class="operator token">==</SPAN> <SPAN class="string token">" "</SPAN><SPAN class="punctuation token">]</SPAN><SPAN class="punctuation token">)</SPAN><SPAN class="punctuation token">.</SPAN>strip<SPAN class="punctuation token">(</SPAN><SPAN class="punctuation token">)</SPAN>&nbsp; 
<SPAN class="operator token">>></SPAN><SPAN class="operator token">></SPAN>
<SPAN class="operator token">>></SPAN><SPAN class="operator token">></SPAN> a
<SPAN class="string token">'Low Pressure Sodium 90 100'</SPAN>
<SPAN class="operator token">>></SPAN><SPAN class="operator token">></SPAN> bulb
<SPAN class="string token">'Low Pressure Sodium'</SPAN>
<SPAN class="operator token">>></SPAN><SPAN class="operator token">></SPAN> watts
<SPAN class="string token">'90 100'</SPAN>
<SPAN class="line-numbers-rows"><SPAN>‍</SPAN><SPAN>‍</SPAN><SPAN>‍</SPAN><SPAN>‍</SPAN><SPAN>‍</SPAN><SPAN>‍</SPAN><SPAN>‍</SPAN><SPAN>‍</SPAN><SPAN>‍</SPAN><SPAN>‍</SPAN></SPAN>

BenVan_Kesteren1

That will work. The output is:

First part = [Incandescent ], second part = [200 250]<SPAN class="line-numbers-rows"><SPAN>‍</SPAN></SPAN>

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

<SPAN class="keyword token">print</SPAN> <SPAN class="string token">"First part = [%s], second part = [%s]"</SPAN> <SPAN class="operator token">%</SPAN> <SPAN class="punctuation token">(</SPAN>str1<SPAN class="punctuation token">[</SPAN><SPAN class="punctuation token">:</SPAN>pos<SPAN class="number token">-1</SPAN><SPAN class="punctuation token">]</SPAN><SPAN class="punctuation token">,</SPAN> str1<SPAN class="punctuation token">[</SPAN>pos<SPAN class="punctuation token">:</SPAN>len<SPAN class="punctuation token">(</SPAN>str1<SPAN class="punctuation token">)</SPAN><SPAN class="punctuation token">]</SPAN><SPAN class="punctuation token">)</SPAN><SPAN class="line-numbers-rows"><SPAN>‍</SPAN></SPAN>

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

Looks good, thanks.

mody_buchbinder

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

Join the Conversation

Signed in members can post, follow updates, and more. New here? Register a free account.

Quick Links

Community Help
Find useful guides, FAQs, and documents to help you navigate and make the most of Esri Community.