Build Arcpy Field Mapping Object from Table

[MicahBabinski]

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

[NeilAyres]

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.

[MicahBabinski]

Thanks Neil. Here was my concept:

One table for each output dataset (there are many). The columns would be something along the lines of:

• SourceTablePath
• SourceFieldName
• SourceFieldType
• DestinationFieldName
• DestinationFieldType
• DestinationFieldLength (if text)

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.

[JoshuaBixby]

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

[MicahBabinski]

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

[XanderBakker]

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:

• Read the mappings from a table and create a dictionary
• Create a nested search cursor inside a insert cursor to insert the features into the destination featureclass.
• You will need to have some reference between the mappings table and the featureclasses it is based on (for instance use the featureclass names for the field names in your mapping table). 

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()‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍

[MicahBabinski · Accepted 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:

1. Obtain a unique list of input feature classes (full paths, for use in the append GP tool)
2. Obtain a unique list of the output fields (one arcpy.FieldMap() object is needed per output field)
3. Create an empty field mappings object
4. For each output field, create an empty field map object, obtain the input fields for that output field with a da.SearchCursor, add those to the field map object, set the name of the output field, and add the field map to the field mappings object

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