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; } } }

Split text field into two new fields?

DavidMedeiros

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

Vind meer berichten getagd met

Accepted answers

All comments

BruceNielsen

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 "".

LyonMNGIS

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

BruceNielsen

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

LyonMNGIS

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

NiklasNorrthon

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.

charlieLatchford

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?

DavidMedeiros

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!

DavidMedeiros

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?

ChrisSnyder

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

DavidMedeiros

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?

DavidMedeiros

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

ChrisSnyder

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.

DavidMedeiros

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

DavidMedeiros

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.

JasonScheirer

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

ChrisSnyder

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

JasonScheirer

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.

Doe mee aan het gesprek

Aangemelde leden kunnen berichten plaatsen, updates volgen en meer. Nieuw hier? Registreer een gratis account.

Snelle Links

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