topic Re: Split text field into two new fields? in Python Questions

Split text field into two new fields?

DavidMedeiros — Wed, 24 Nov 2010 16:51:40 GMT

Hi,

I'm a GIS MA student looking for some help on creating a Python script in ArcGIS for a class project. I have a text table with several fields (Tenant Name; Address; Sq Ft; Destination). The destination column contains information that I want to separate into two new fields - one for the first word in the original column and one for the second word in the original column. I then need to write this new info back into the original table.

What ArcPy commands should I be looking at to accomplish this? Can anyone point me to a similar finished script?

Thanks,

David

Re: Split text field into two new fields?

JasonScheirer — Wed, 24 Nov 2010 17:22:03 GMT

You'll want to use AddField to add the new field for splitting, then use two CalculateValue calls -- one to populate the new field and another to truncate down the old field into the first word.

Re: Split text field into two new fields?

ChrisSnyder — Wed, 24 Nov 2010 17:51:06 GMT

If you want:
ORIG_FIELD = "My House"
SPLIT1 = "My"
SPLIT2 = "House"

The Python syntax for the Calculate Field tool would be:
SPLIT1 = !ORIG_FIELD!.split(" ")[0] #0 being the 1st word
SPLIT1 = !ORIG_FIELD!.split(" ")[-1] #-1 being the last word

Re: Split text field into two new fields?

JasonScheirer — Wed, 24 Nov 2010 18:26:34 GMT

If you have records with multiple spaces this will fall down. There's an optional second argument to string.split to limit the number of splits performed:

SPLIT1 = !ORIG_FIELD!.split(" ", 1)[0] #0 being the 1st word
SPLIT2 = !ORIG_FIELD!.split(" ", 1)[-1] #-1 being the last word

And then you may only have one token, in which case you can use a conditional variable to rightly leave the second field as blank (otherwise, [0] and [-1] will be pointing to the same index in the list, meaning both fields are populated with the same value). So a more refined version would be:

SPLIT1 = !ORIG_FIELD!.split(" ", 1)[0]
SPLIT2 = !ORIG_FIELD!.split(" ", 1)[1] if " " in !ORIG_FIELD! else ""

Re: Split text field into two new fields?

DavidMedeiros — Wed, 24 Nov 2010 18:45:40 GMT

I should probably have waited to post this question until after the holiday so I could be trying this out in the lab, but this looks like it will be a great help, thank you!

All records in that field have single space, but some do only have one entry instead of two.

Re: Split text field into two new fields?

DavidMedeiros — Wed, 01 Dec 2010 17:24:17 GMT

Alright here's where I've gotten to on this:

[INDENT]# split table script

#add field step (keep)
import arcpy

from arcpy import env
inputTable = arcpy.GetParameterAsText(0)
inputField = arcpy.GetParameterAsText(1) #"Dest" column, set in toolbox properties

#list fields (stops duplicate add fields)
theFieldList = arcpy.ListFields (inputTable, "Type")
arcpy.AddMessage ("the count" + str(len(theFieldList)))

#following exectues the add field (keep)
if len(theFieldList) == 0:
arcpy.AddField_management(inputTable, "Type", "TEXT")
else:
arcpy.AddMessage ("Field Exists")

#split field loop
theItemList = arcpy.SearchCursor (inputTable) #gets all vlaues for table

for thisItem in theItemList: #loops through vlaues
thisValue = thisItem.getValue (inputField)
split = !thisValue!.split(" ",1)[-1] if " " in !thisValue! else ""
setValue...[/INDENT]

The add field portion works, I'm not sure if I'm going in the right direction with the loop above and I don't know what the syntax is to set the value back into my new field. Does this look correct (aside from being incomplete)?

Re: Split text field into two new fields?

ChrisSnyder — Wed, 01 Dec 2010 17:52:17 GMT

If you want to write new data to the table you would need to use an update cursor (not a search cursor). Also, you have to use cursor syntax, not field calculator syntax.

Re: Split text field into two new fields?

DavidMedeiros — Tue, 07 Dec 2010 23:18:50 GMT

If you want to write new data to the table you would need to use an update cursor (not a search cursor). Also, you have to use cursor syntax, not field calculator syntax.

So I would drop the calculate field "split" command? Or use it together with the update cursor?

What I have so far is a new field, into which I want to place the second value from another filed (forget about truncating first field, not needed).

Re: Split text field into two new fields?

DavidMedeiros — Tue, 07 Dec 2010 23:43:31 GMT

Ok, I'm successfully updating the row - bigger achievement for me than you might think! 😉 But, I'm only getting the last character instead of the second word from the original row. Here's my script:

# split table script

#add field step (keep)
import arcpy

from arcpy import env
inputTable = arcpy.GetParameterAsText(0)
arcpy.AddMessage ("input " + inputTable)

#list fields (stops duplicate add fields)
theFieldList = arcpy.ListFields (inputTable, "Type")
arcpy.AddMessage ("the count" + str(len(theFieldList)))

#following exectues the add field (keep)
if len(theFieldList) == 0:
    arcpy.AddField_management(inputTable, "Type", "TEXT")

else:
    arcpy.AddMessage ("Field Exists")

#split field loop
rows = arcpy.UpdateCursor (inputTable) #gets all vlaues for table

for row in rows: #loops through vlaues
    row.Type = row.Dest[-1]
    rows.updateRow(row)

del row
del rows

I know I need to change the modifier on the row.Dest[-1] syntax but I thought the [-1] gave me the second word?

Re: Split text field into two new fields?

ChrisSnyder — Tue, 07 Dec 2010 23:51:46 GMT

No, -1 gives you the last value (which may coincidentally be the 2nd item if there are only two items). For example:

list = ["a","b","c","d"]

list[0] = "a"
list[1] = "b"
list[2] = "c"
list[3] = "d"
list[4] = ERROR! Index out of range...

list[-1] = "d"
list[-2] = "c"
list[-3] = "b"
list[-4] = "a"

list[0:2] = ["a","b"]

Re: Split text field into two new fields?

DavidMedeiros — Wed, 08 Dec 2010 00:01:17 GMT

Ahh, I mistook last value for last whole word. Is there a modifier to the row.Type = row.Dest statement that can select the entire last word from the original row?

Re: Split text field into two new fields?

DavidMedeiros — Wed, 08 Dec 2010 00:23:47 GMT

Ok, I think I got it. Here's the script:

Script:
# split table script

#add field step
import arcpy

from arcpy import env
inputTable = arcpy.GetParameterAsText(0)
arcpy.AddMessage ("input " + inputTable)

#list fields (stops duplicate add fields)
theFieldList = arcpy.ListFields (inputTable, "Type")
arcpy.AddMessage ("the count" + str(len(theFieldList)))

#following exectues the add field
if len(theFieldList) == 0:
    arcpy.AddField_management(inputTable, "Type", "TEXT")

else:
    arcpy.AddMessage ("Field Exists")

#split field loop
rows = arcpy.UpdateCursor (inputTable) #gets all vlaues for table

for row in rows: #loops through vlaues and updates new field basedon last word of orig field
    row.Type = row.Dest.split(" ")[-1] if " " in row.Dest else ""
    rows.updateRow(row)

del row
del rows

resulting table has a new field "Type" with the second word from the "dest" row copied over. If no second word nothing is copied over. Thanks for the help all!

Re: Split text field into two new fields?

charlieLatchford — Fri, 11 Feb 2011 04:09:24 GMT

hi iam trying to do the same thing.. ive got a text field that contains an address each seperated with a "," i want to split into three new fields between the comers. for example

28, king road, new york

i want to be able to have the following field's
28
king road
new york

Does any one know any python script?

Re: Split text field into two new fields?

NiklasNorrthon — Sat, 11 Dec 2021 23:55:10 GMT

text = '28, king road, new york'
split_text = text.split(', ') # -> ['28', 'king road', 'new york']

A tip is to test the logic in the python interpreter before putting it together in a scirpt.

Re: Split text field into two new fields?

MarkVolz — Mon, 15 Aug 2011 14:56:36 GMT

Hello,

I am trying to split a name field into lastname and firstname. The name field may or may not contain a last name.

examples:
"Anderson, Bill"
"CHS Company"

I am able to pull out the last name from the field. However I am having trouble getting the first name (if there is any) out.

I tried the code:
!TAX_NAME!.split(",", 1)[-1] if "," in !TAX_NAME! else "" however ArcGIS reported a syntax error

Re: Split text field into two new fields?

BruceNielsen — Mon, 15 Aug 2011 15:54:55 GMT

I don't use the CalculateField command very often (IMO UpdateCursors are easier to understand), but I think the syntax you're looking for is:

if ',' in !TAX_NAME! then !TAX_NAME!.split(',',1)[-1] else ""

Re: Split text field into two new fields?

MarkVolz — Mon, 15 Aug 2011 16:39:50 GMT

Bruce,

Thank you for your reply.

I am still getting an error:

ERROR 000539: Error running expression: if ',' in "PETERSEN,ERIC J & GAIL" then "PETERSEN,ERIC J & GAIL".split(',',1)[-1] else "" <type 'exceptions.SyntaxError'>: invalid syntax (<string>, line 1)
Failed to execute (Calculate Field (2)).

Re: Split text field into two new fields?

BruceNielsen — Mon, 15 Aug 2011 17:25:56 GMT

Like I said, I don't use this syntax very often. Here's the amended code:

if ',' in !TAX_NAME! then !NEW_FIELD!=!TAX_NAME!.split(',',1)[-1] else "".