Does anyone know if it is possible to store information on input datasets, input fields, and destination fields in a table, and then use arcpy to build a field mapping object from said table? I have built arcpy field mapping objects in the past using blocks of code for each field, but don't have any experience reading the info out of a table and building the table using an iterator.
I'd love to get your thoughts!
Warm Regards,
Micah Babinski
Solved! Go to Solution.
Thank you all very much for your input! In the end I only needed a table with three fields:
From there I wrote the following script (as it would be implemented in a script tool):
# Name: appendWithFieldMap.py
# Author: Micah Babinski
# Date: 9/8/2016
# Description: Appends features to an output feature class using field mapping information stored in a table
#
# USAGE NOTE: The table must contain three columns
# 1. SourceTablepath (fully-qualified path to the source feature class
# 2. SourceFieldName (name of the input field as it appears in the source feature class
# 3. DestinationFieldName (destination field name as it appears in the destination feature class)
import arcpy
import sys
# obtain user parameters
outputFeatureClass = arcpy.GetParameterAsText(0) #r"W:\FieldMappingResearch\gdb\Output.gdb\roads"
fieldMapTable = arcpy.GetParameterAsText(1) #r"W:\FieldMappingResearch\gdb\Inputs.gdb\fieldMapTable"
# list the fields which contain the field map info
fields = ["SourceTablePath", "SourceFieldname", "DestinationFieldName"]
# define function to get unique field values from a table
def GetUniqueFieldValues(table, field):
"""
Retrieves and prints a list of unique values in a user-specified field
Args:
table (str): path or name of a feature class, layer, table, or table view
field (str): name of the field for which the user wants unique values
Returns:
uniqueValues (list): a list of the unique values in the field
"""
# get the values
with arcpy.da.SearchCursor(table, [field]) as cursor:
return sorted({row[0] for row in cursor})
# get a list of unique input feature classes
arcpy.AddMessage("Listing the unique input feature classes.")
inputs = GetUniqueFieldValues(fieldMapTable, "SourceTablePath")
# get a list of unique output fields
arcpy.AddMessage("Listing the unique output fields.")
outputFields = GetUniqueFieldValues(fieldMapTable, "DestinationFieldName")
# create an empty field mappings object
arcpy.AddMessage("Creating an empty field mappings object.")
fm = arcpy.FieldMappings()
# build the field mappings object
arcpy.AddMessage("Building the field mappings object.")
for f in outputFields:
arcpy.AddMessage("\t" + f + "...")
# create a field map object
arcpy.AddMessage("\t...Creating a field map object.")
fMap = arcpy.FieldMap()
with arcpy.da.SearchCursor(fieldMapTable, fields, """{0} = '{1}'""".format("DestinationFieldName", f)) as cursor:
for row in cursor:
# add the input field to the field map
arcpy.AddMessage("\t...Adding " + row[1] + " as an input field.")
fMap.addInputField(row[0], row[1])
# set the output name
arcpy.AddMessage("\t...Setting the output name.")
outputFieldName = fMap.outputField
outputFieldName.name = f
fMap.outputField = outputFieldName
# add the field map to the field mappings object
arcpy.AddMessage("\t...Adding the field map to the field mappings object.")
fm.addFieldMap(fMap)
# perform the append
arcpy.AddMessage("\nAppending!")
arcpy.Append_management(inputs, outputFeatureClass, "NO_TEST", fm)
To summarize, the workflow was:
I imagine that there is a lot more that could be done with this in terms of validation and error handling (for when field types are mismatched) but I think this will get me what I need.
Thanks again!
Micah
I am sure it is possible, what sort of info does your "metadata table" contain.
It has always been a minor irritation to me that arcpy doesn't (at least I haven't seen it anyway) allow you to use CreatFeatureClass and have as input a Field Map to define all the attributes you want.
But then again, I suppose Field Mappings are focused on input / output rather than to create something from scratch.
So maybe, this is too late in the day to be speculating like this.
Thanks Neil. Here was my concept:
One table for each output dataset (there are many). The columns would be something along the lines of:
It seems like that should cover most of the parameters for a field mapping object. I just don't have quite enough fieldmappings experience to piece together a working script. This is for a compilation of nation-wide datasets from tons of inputs from states, district, and field offices. My colleagues have prepared the "crosswalk" tables and I am responsible for merging them. One dataset had 91 inputs! So this could really save me a lot of time.
Thanks again.
"One table for each output dataset...." Do you mean one row in a table for each dataset? I am still not clear on the specifics of what you are attempting. In a more general sense, it appears you are trying to merge a collection of nationwide datasets that have similar data from different naming conventions for columns/fields. I assume you are using the Merge tool at some point? Can you provide a bit more context or higher level information about what you are attempting to accomplish?
Thanks Joshua for pointing out that I wasn't very clear about the overall process at a high level!
You are correct. We have a large number of desired output datasets, each with many inputs from state, district, and field offices. People I work with have developed "crosswalk" tables, which show each desired output field on the left column, the input datasets along the top row, and which field input field should map into the output field in the cell where they intersect. A simple example might be:
Destination Field | ROW_Oregon | ROW_Arizona | ROW_Wyoming | ROW_Nevada |
---|---|---|---|---|
ROW_NAME | ROW_NM | NAME | row_nm_txt | name |
ROW_TYPE | ROW_TYPE | ROW_TP | row_tp_txt | type |
GIS_ACRES | ACRES | GIS_ACRES | AREA_ACRES | acre |
EFFECTIVE_DATE | ROW_DT | EFFECTV_DATE | ROW_DATE | date |
I am using another table which shows the desired field names, types, and lengths (for text fields) of each dataset, then adding fields to an empty feature class, using code like this:
table = "schema_table"
fields = ("field_name", "field_type", "field_length")
target = "output_fc"
with arcpy.da.SearchCursor(table, fields) as cursor:
for row in cursor:
if row[1] == "TEXT":
arcpy.AddField_management(target, row[0], row[1], "#", "#", row[2])
else:
arcpy.AddField_management(target, row[0], row[1])
Once I have an empty feature class with the desired output schema, I want to create an arcpy field mapping object and use it as a parameter to the Append tool to load the data into the output feature class. Currently I am just using the stand-alone Append GP tool (with the NO_TEST option) and selecting the input fields for each output field, which is extremely time-consuming as some of our datasets have more than 90 inputs.
The examples given for creating field maps in the help documentation are pretty basic and use hard-coded field names and properties. I am wondering if it is possible to reformat the crosswalk table above in a way that I could read it with a SearchCursor and create the fieldmapping object automatically.
Hope that clears it up.
Micah
I'm not very fond of using FieldMapping, although it is a very powerful thing. The alternative is using an insert cursor. Suppose you have the mapping in a table as you posted:
Then you could:
The code would look something like this:
def main():
import arcpy
# if you already have the output featureclass:
fc_destination = r'C:\GeoNet\FieldMapping\gdb\data.gdb\yourOutputFeatureclass'
tbl_mapping = r'C:\GeoNet\FieldMapping\gdb\data.gdb\Mapping'
flds_mapping = [tbl.name for tbl in arcpy.ListFields(tbl_mapping)[1:]]
# [u'DestinationField', u'ROW_Oregon', u'ROW_Arizona', u'ROW_Wyoming', u'ROW_Nevada']
# append featureclass in same order as the fields of the field mapping
lst_fcs = ['path to fc Oregon', 'path to fc Arizona',
'path to fc Wyoming', 'path to fc Nevada']
# create a dictionary with the "field mappings"
dct ={}
with arcpy.da.SearchCursor(tbl_mapping, flds_mapping) as curs:
for row in curs:
lst = list(row)
key = lst.pop(0)
dct[key] = lst
# The dct will have the following content:
# {u'EFFECTIVE_DATE': [u'ROW_DT', u'EFFECTV_DATE', u'ROW_DATE', u'date'],
# u'ROW_TYPE': [u'ROW_TYPE', u'ROW_TP', u'row_tp_txt', u'type'],
# u'GIS_ACRES': [u'ACRES', u'GIS_ACRES', u'AREA_ACRES', u'acre'],
# u'ROW_NAME': [u'ROW_NM', u'NAME', u'row_nm_txt', u'name']}
flds_destination = dct.keys()
flds_destination.insert(0, 'SHAPE@')
# [u'SHAPE@', u'EFFECTIVE_DATE', u'ROW_TYPE', u'GIS_ACRES', u'ROW_NAME']
# insert cursor
with arcpy.da.InsertCursor(fc_destination, flds_destination) as curs:
i = -1
for fc in lst_fcs:
i =+ 1
flds_in = getFieldsIn(flds_destination, dct, i)
# search cursors to insert the features
with arcpy.da.SearchCursor(fc, flds_in) as curs_in:
for row_in in curs_in:
curs.insertRow(row_in)
def getFieldsIn(flds_destination, dct, i):
flds_in = []
for fld_dest in flds_destination[1:]:
lst = dct[fld_dest]
flds_in.append(lst[i])
flds_in.insert(0, 'SHAPE@')
return flds_in
if __name__ == '__main__':
main()
Thank you all very much for your input! In the end I only needed a table with three fields:
From there I wrote the following script (as it would be implemented in a script tool):
# Name: appendWithFieldMap.py
# Author: Micah Babinski
# Date: 9/8/2016
# Description: Appends features to an output feature class using field mapping information stored in a table
#
# USAGE NOTE: The table must contain three columns
# 1. SourceTablepath (fully-qualified path to the source feature class
# 2. SourceFieldName (name of the input field as it appears in the source feature class
# 3. DestinationFieldName (destination field name as it appears in the destination feature class)
import arcpy
import sys
# obtain user parameters
outputFeatureClass = arcpy.GetParameterAsText(0) #r"W:\FieldMappingResearch\gdb\Output.gdb\roads"
fieldMapTable = arcpy.GetParameterAsText(1) #r"W:\FieldMappingResearch\gdb\Inputs.gdb\fieldMapTable"
# list the fields which contain the field map info
fields = ["SourceTablePath", "SourceFieldname", "DestinationFieldName"]
# define function to get unique field values from a table
def GetUniqueFieldValues(table, field):
"""
Retrieves and prints a list of unique values in a user-specified field
Args:
table (str): path or name of a feature class, layer, table, or table view
field (str): name of the field for which the user wants unique values
Returns:
uniqueValues (list): a list of the unique values in the field
"""
# get the values
with arcpy.da.SearchCursor(table, [field]) as cursor:
return sorted({row[0] for row in cursor})
# get a list of unique input feature classes
arcpy.AddMessage("Listing the unique input feature classes.")
inputs = GetUniqueFieldValues(fieldMapTable, "SourceTablePath")
# get a list of unique output fields
arcpy.AddMessage("Listing the unique output fields.")
outputFields = GetUniqueFieldValues(fieldMapTable, "DestinationFieldName")
# create an empty field mappings object
arcpy.AddMessage("Creating an empty field mappings object.")
fm = arcpy.FieldMappings()
# build the field mappings object
arcpy.AddMessage("Building the field mappings object.")
for f in outputFields:
arcpy.AddMessage("\t" + f + "...")
# create a field map object
arcpy.AddMessage("\t...Creating a field map object.")
fMap = arcpy.FieldMap()
with arcpy.da.SearchCursor(fieldMapTable, fields, """{0} = '{1}'""".format("DestinationFieldName", f)) as cursor:
for row in cursor:
# add the input field to the field map
arcpy.AddMessage("\t...Adding " + row[1] + " as an input field.")
fMap.addInputField(row[0], row[1])
# set the output name
arcpy.AddMessage("\t...Setting the output name.")
outputFieldName = fMap.outputField
outputFieldName.name = f
fMap.outputField = outputFieldName
# add the field map to the field mappings object
arcpy.AddMessage("\t...Adding the field map to the field mappings object.")
fm.addFieldMap(fMap)
# perform the append
arcpy.AddMessage("\nAppending!")
arcpy.Append_management(inputs, outputFeatureClass, "NO_TEST", fm)
To summarize, the workflow was:
I imagine that there is a lot more that could be done with this in terms of validation and error handling (for when field types are mismatched) but I think this will get me what I need.
Thanks again!
Micah