topic Re: Extracting numbers from a column in Data Management Questions

Extracting numbers from a column

Anonymous User — Tue, 18 Jun 2013 17:26:44 GMT

Original User: powell21

Hey,
I am trying to execute a join of a table to a shapefile. There is a column (in properties, type is "doube") in the shapefile that contains a long code (11 characters). I need the middle five numbers to be in their own column in order to have it correspond to the correct column in the table.

i.e. Field in column = 11705120000 and I need 05120 to be in its own column for it to match up with the table correctly.

Is it possible to extract just those numbers, and if so, how?

Thanks!

Emily

Re: Extracting numbers from a column

Anonymous User — Tue, 18 Jun 2013 18:06:31 GMT

Original User: Wayne_Whitley

Well, have to understand your value is stored as double, and it sounds like you really want to extract string.
For example, what if you had a double value as in the following:

>>> dblVal = 11705120000.999
>>> print dblVal
11705120001.0

Depending on what you do with it, how you treat it, you may have some rounding...no worries though, and if you convert it to a different data type, as in the following to integer you don't have to worry about unexpected rounding values:

>>> intVal = int(dblVal)
>>> print intVal
11705120000

Then, converting that result to string, you can 'slice off' what you need:

>>> strVal = str(intVal)
>>> print strVal
11705120000

>>> strVal[3:-3]
'05120'
>>>

Have to be careful that the characters you wish to extract are in the same position in the integer value...otherwise you can test the input values and apply what techniques you need to conditionally extract the desired result.

Hope that helps,
Wayne

EDIT: I didn't realize this is the 'Editing' forum so the techique I use may look foreign -- the last 'slicing' technique I used above is also demonstrated along with the use of the field calculator here:

Calculate Field examples
Geodata » Data types » Tables
http://resources.arcgis.com/en/help/main/10.1/index.html#//005s0000002m000000

(see under the heading near the top of the page, Simple string examples)

Re: Extracting numbers from a column

T__WayneWhitley — Thu, 20 Jun 2013 13:00:21 GMT

uh, thought something looked strange, and if you're checking out the documentation at the webhelp link provided in the last post, there is a bit of an error in the explanation for:

!fieldname![1:4]

The result depicts the correct result 'sliced' from a string 'abcde' as being 'bcd' -- think of the character position indexes 0-5, and in the [1:4] expression, 4 is the 'upper bound' limit not included in the result. In other words 4 - 1 = 3 characters to return starting at position 1, or 'b'. (so the web doc explanation should not have included the fifth character)

You may have to play with it some...

Enjoy,
Wayne