In this tutorial, we will be using an UpdateCursor object to edit an attribute table. Specifically, we will join field values from three separate fields to a single field value and apply string formatting for each iteration of the cursor object.
An arcpy UpdateCursor object creates a read-write connection with a data file, so that records can be altered. In this tutorial, we will demonstrate how to use an UpdateCursor to reformat data from multiple columns to one, single field in that same data file.
An UpdateCursor use case: joining and formatting street address data
For this tutorial, we will again be using the Hospitals_England Feature Service Feature Class from ArcGIS Online that is added to a new, empty project file. Before we start, let us have a look at the attribute table and three columns in particular: Address1, Address2 and Address3.
The image below shows that the address for a single hospital is divided into three separate address fields, some of which are empty. The objective here is to write a Python script that joins the existing address data in a single field in a nicely formatted way, by using commas and spaces between the different address parts. The new and formatted address string value will overwrite the existing data in the Address1 field. Finally, we will delete the Address2 and Address3 as they will not be necessary after joining the field values. This is all done using the arcpy module.
How to do it
Before we can start writing the code, we need to copy our data to a new feature class as it is not possible to edit the attribute field data for the Feature Service Feature Layer. The Copy Features geoprocessing makes a locally referenced copy of the data that can be edited later, so we will do that first. Run the Copy Features geoprocessing tool with the Hospitals_England Feature Service Feature Layer referenced as input features and name the output feature class:
A copy of the Hospitals_England should be added to your map window automatically. We will edit the attribute table of this new feature class using arcpy. Next, open a new notebook and run the following code to create and run an UpdateCursor, format the individual fields and delete the Address2 and Address3 fields:
How it works
First, we import the arcpy module. Next, we reference the feature class we want to edit, which is the file we created using the Copy Features tool. Then, we reference the fields with a list item that contains the three data fields that will be reformatted using the UpdateCursor. Specifically, the current data from Address1 will be overwritten with a new string that joins the three separate strings from Address1, Address2 and Address3. Next, we create an UpdateCursor object and pass in the feature class and field variables defined earlier. This object is applied as an iterator object that iterates over each row of the file’s attribute table.
The bulk of this script exists of a long if-else clause that specifies how to format each row. This is necessary as we have many fields with one or more missing field values. To make sure that Python formats each address field correctly, we must check for each row if there are one or more missing field values, and based on that condition, format the address data correctly: that is, with or without added commas or spaces before or after the field value.
The line ‘cursor.updateRow(row)’ applies the UpdateRow cursor object based on the matching condition from the if-else clause. After running the code, open the Attribute Table of the Hospitals_England_Copyfeatures layer and look at the new values in the Address1 column. A snippet of the resulting output is listed below: note the added spaces and commas for multiple address parts and single field address values without spaces and commas:
This image contains the entire script:
Learn more about using Python with ArcGIS Pro in our Introduction to Programming ArcGIS Pro with Python and Intermediate ArcGIS Pro with Python classes.
