topic Re: Field Mapping Help With Merge Tool in Python Questions

Field Mapping Help With Merge Tool

LornaMurison — Sat, 11 Dec 2021 08:04:28 GMT

Hello,
I'm new to python and using fieldmappings for the first time.
I have a script that creates two dBASE tables. I would like one of the final steps in my script to be merging these two tables and keeping only two fields (UCID and Temp). In the table "statTableV" these fields are "UCID" and "MEAN". In the table "pointValuesV" these fields are "CATCH_02_3" and "RASTERVALU". So I need to rename the fields and merge them.

I wrote the code below based on the code example provided here: http://webhelp.esri.com/arcgisdesktop/9.3/index.cfm?id=949&pid=944&topicname=Mapping_fields
but that is resulting in this error: ExecuteError: ERROR 000468: Input shape types are not equal
Failed to execute (Merge).
Which brings me to my question:
a) What is causing the error?
b) Most of the fields in my input tables are ones I don't need. Do I have to specify that they be removed in field mapping or with they be ignored if they are not specified in the code?

Thank-you

# Import Modules
import arcgisscripting

# Create geoprocessor object
gp = arcgisscripting.create (9.3)

# Get parameters
catchments = r"F:\Climate\test\Catchments.gdb\Catch_02AA"
#catchments = gp.GetParameterAsText(0)                                  # Feature Layer
watershed = "02AA"
#watershed = gp.GetParameterAsText(1)
idField = "UCID"
#idField = gp.GetParameterAsText(2)                                     # Field
climateRaster = r"F:\Climate\test\Climate.gdb\Avg_Temp_January"
#climateRaster = gp.GetParameterAsText(3)                               # Raster Layer
month = "January"
#month = gp.GetParameterAsText (4)                                      # String
workspace = r"F:\Climate\test\Climate.gdb"
#workspace = gp.GetParameterAsText (5)                                  # Workspace
outWorkspace = r"F:\Climate\test"
#outWorkspace = gp.GetParameterAsText (6)                               # Folder

statTable = "zStat" + month
statTableV = "zStat" + month + "V"
missingStats = "zStat" + month + "Missing"
points = month + "Point"
pointValues = month + "PointValue"
pointValuesV = month + "PointValuesV"
finalTable = month + "Averages" + watershed
gp.Workspace = workspace
gp.overwriteoutput = True

# Check-out the Spatial Analyst extension
gp.CheckOutExtension ("Spatial")

# Run Zonal Statistics As Table
gp.ZonalStatisticsAsTable_sa (catchments, idField, climateRaster, statTable, "DATA")

# Create a feature layer from the catchments
gp.MakeFeatureLayer_management (catchments, "layer", "", workspace, "")

# Join zonal statistics table to catchments layer
gp.AddJoin_management ("layer", idField, statTable, idField, "KEEP_ALL")

# Select and export the catchments that don't have any zonal statistics
gp.Select_analysis ("layer", missingStats, '"' + statTable + "." + idField + '"' + ' is null')

# Un-join zonal statistics table from catchments
gp.RemoveJoin_management ("layer", statTable)

# Convert the exported "missing stats" catchments to point centroids
gp.FeatureToPoint_management (missingStats, points, "INSIDE")

# Add the underlying temperature values to the points
gp.ExtractValuesToPoints_sa (points, climateRaster, pointValues, "INTERPOLATE", "VALUE_ONLY")

# Make table views from the zonal statistics and value to points tables
gp.MakeTableView_management (statTable, statTableV, "", workspace, idField + " " + idField + " VISIBLE, RASTERVALU Temp VISIBLE")
gp.MakeTableView_management (pointValues, pointValuesV, "", workspace, idField + " " + idField + " VISIBLE, MEAN Temp VISIBLE")

# Convert table views to dBASE tables
gp.TableToDBASE_conversion (statTableV, outWorkspace)
gp.TableToDBASE_conversion (pointValuesV, outWorkspace)

# Prepare the field maps for the final table
fieldMappings = gp.createobject ("FieldMappings")
fldmap_UCID = gp.createobject ("FieldMap")
fldmap_Temp = gp.createobject ("FieldMap")
vt = gp.CreateObject("ValueTable")
fieldMappings.AddTable(statTableV)
fieldMappings.AddTable(pointValuesV)
fldmap_UCID.AddInputField(statTableV, "UCID")
fldmap_UCID.AddInputField(pointValuesV, "CATCH_02_3")
fldmap_Temp.AddInputField(statTableV, "MEAN")
fldmap_Temp.AddInputField(pointValuesV, "RASTERVALU")
fld_UCID = fldmap_UCID.OutputField
fld_Temp = fldmap_Temp.OutputField
fld_UCID.Name = "UCID"
fld_Temp.Name = "Temp"
fldmap_UCID.OutputField = fld_UCID
fldmap_Temp.OutputField = fld_Temp
fieldMappings.AddFieldMap(fldmap_UCID)
fieldMappings.AddFieldMap(fldmap_Temp)
vt.AddRow(statTableV)
vt.AddRow(pointValuesV)

# Merge the two tables
gp.Merge_management (vt, finalTable, fieldMappings)

# Delete all the temporary datasets
gp.Delete_management (statTable)
gp.Delete_management (missingStats)
gp.Delete_management (points)
gp.Delete_management (pointValues)
gp.Delete_management (statTableV + ".dbf")
gp.Delete_management (pointValuesV + ".dbf")

Re: Field Mapping Help With Merge Tool

ChrisMathers — Mon, 21 Mar 2011 14:51:56 GMT

If you are only editing the existing field map and dont delete them from the field map they will be transfered to your new feature class. You can create a new field map and only add the fields you want to that object though. I do not really like the field mapping functions very much.

Re: Field Mapping Help With Merge Tool

LornaMurison — Mon, 21 Mar 2011 17:57:02 GMT

Okay, that makes sense and that is what I thought I was doing (adding the fields I want to a new field map).
Any ideas why this is causing an error?

Re: Field Mapping Help With Merge Tool

LornaMurison — Sun, 12 Dec 2021 16:06:26 GMT

Alright, I could use a fresh perspective on this again.
I have been making slow progress, the merge function works if I do it without using a value table.
The field mapping is not working, I am getting more fields than I like and they are all empty.

I am having the strangest issue right now:
This line: print fieldMappings.GetFieldMap(16).OutputField.Name works perfectly

However this block:

count = fieldMappings.FieldCount
print fieldMappings.GetFieldMap(16).OutputField.Name <<-- No error here
for i in range (0, count-1, 1):
    print str(i) + "/" + str(count-1)
    fieldMap = fieldMappings.GetFieldMap(i) <<----RuntimeError: FieldMappings: Error in getting field map from field mapping for GetFieldMap
    outField = fieldMap.OutputField
    print outField.Name
    if fieldMappings.GetFieldMap(i) == fldmap_UCID:
        print "Found UCID: " + str(i)
    elif outField.Name == "Temp":
        print "Found Temp: " + str(i)
    else:
        fieldMappings.RemoveFieldMap(i)
    print 'done'
print 'done'

continues looping with no problem until it gets to i = 16.

I am baffled.

Re: Field Mapping Help With Merge Tool

LornaMurison — Wed, 23 Mar 2011 14:47:50 GMT

fyi
What happened was everytime I ran through the loop, on of the field maps was deleted, which reset the index, so although there were 30 to begin with, by the time the loop got to index 16, there were only 15 field maps.
I changed it so that instead of adding two tables and removing excess fields, I am now adding only the fields that I need.
Except I am still getting empty tables in my output.

Re: Field Mapping Help With Merge Tool

MathewCoyle — Sat, 11 Dec 2021 08:04:31 GMT

If you are only editing the existing field map and dont delete them from the field map they will be transfered to your new feature class. You can create a new field map and only add the fields you want to that object though. I do not really like the field mapping functions very much.

I second that sentiment exactly.

I find it easier to just delete unwanted fields with something like this. To me it is more logical and faster from what I have tried it on.

ouput = #<feature class>
dropFields = list()
fieldList = gp.ListFields(output)
for f in fieldList:
    if f.name <> "BLOCKSTAGE" and f.type <> "OID" and f.type <> "Geometry":
        print "Adding field to drop list "+f.name
        dropFields.append(f.name)
    gp.DeleteField_management(output, dropFields)

Also, ensure that the fields you are merging are the same data type.

Re: Field Mapping Help With Merge Tool

LornaMurison — Wed, 23 Mar 2011 20:03:20 GMT

Thanks for the reply, Matthew, I'm definitely jumping on the I do not like field mapping functions bandwagon.

I've figured out my delete fields issue (by not adding them in the first place).
I checked to see if my field types were the same, for the first field (UCID) the inputs are both string.
For the second field (Temp) one input is a float, and the other is a double. I have specified the output field as both float and double; both give me an empty output.

They are both producing empty values though. Do you think that the double/float conversion would cause both fields to fail?

I changed the output Temp field to double and it still gives me an empty output. I'm not sure if/how I can change the input field types but I'll try.

Re: Field Mapping Help With Merge Tool

MathewCoyle — Wed, 23 Mar 2011 20:10:02 GMT

Yes, attempting to merge fields of different data types is tricky at best. What I would do is create a field in each of your tables you are attempting to merge, with the same name and data type/structure, do a field calc or update cursor to copy the values from the original field to your new one, then merge your tables on those new fields.

Re: Field Mapping Help With Merge Tool

LornaMurison — Sat, 11 Dec 2021 08:04:34 GMT

Alright, I tried what you said,
I used add and calculate field to make sure that the input fields on all input tables are absolutely identical (string, 9 and float 5,1). I checked these tables and the new fields are properly added and calculated.
This still results in empty output table.

# Convert table views to dBASE tables
gp.TableToDBASE_conversion (statTableV, outWorkspace)
gp.TableToDBASE_conversion (pointValuesV, outWorkspace)
gp.Workspace = outWorkspace
# Convert the table views to geodatabase tables
#gp.TableToGeodatabase_conversion (statTableV, workspace)
#gp.TableToGeodatabase_conversion (pointValuesV, workspace)

# Change the temp fields to be float
gp.AddField_management (pointValuesV + ".dbf", "TempFloat", "FLOAT", 5, 1,"#","", "NULLABLE", "NON_REQUIRED","")
gp.CalculateField_management (pointValuesV + ".dbf", "TempFloat", "!RASTERVALU!", "PYTHON_9.3","")
gp.AddField_management (statTableV + ".dbf", "TempFloat2", "FLOAT", 5, 1, "#","", "NULLABLE", "NON_REQUIRED","")
gp.CalculateField_management (statTableV + ".dbf", "TempFloat2", "!MEAN!", "PYTHON_9.3","")

# Prepare the field maps for the final table
#* Create the field mappings object
fieldMappings = gp.createobject ("FieldMappings")
#* Create a UCID field map object and add the two UCID fields
fldmap_UCID = gp.CreateObject ("FieldMap")
fldmap_UCID.AddInputField(pointValuesV + ".dbf", "CATCH_02_3")
fldmap_UCID.AddInputField(statTableV + ".dbf", "UCID")
print "UCID input fields: " + str(fldmap_UCID.InputFieldCount)
#* Create a field map for temperature and add the two fields
fldmap_Temp = gp.createobject ("FieldMap")
fldmap_Temp.AddInputField(statTableV + ".dbf", "TempFloat2")
fldmap_Temp.AddInputField(pointValuesV + ".dbf", "TempFloat")
print "Temp input fields: " + str(fldmap_Temp.InputFieldCount)
#* Create both of the output fields and add them to the field mappings object
fld_UCID = fldmap_UCID.OutputField
fld_UCID.Name = "UCID"
fld_UCID.Type = "Text"
fld_UCID.Length = 9
fldmap_UCID.OutputField = fld_UCID
fieldMappings.AddFieldMap(fldmap_UCID)
fld_Temp = fldmap_Temp.OutputField
fld_Temp.Name = "Temp"
fld_Temp.Type = "Float"
fld_Temp.Precision = 5
fld_Temp.Scale = 1
fldmap_Temp.OutputField = fld_Temp
fieldMappings.AddFieldMap(fldmap_Temp)

# Merge the two tables
#gp.Merge_management (statTableV + ".dbf;" + pointValuesV + ".dbf", finalTable, fieldMappings)
gp.Merge_management (outWorkspace + "/" + statTableV + ".dbf;" + outWorkspace + "/" + pointValuesV + ".dbf", outWorkspace + "/" + finalTable, fieldMappings)

Re: Field Mapping Help With Merge Tool

MathewCoyle — Thu, 24 Mar 2011 13:25:27 GMT

Alrighty, let's see if I am following, so both input tables you are trying to merge are

F:\Climate\test\zStatJanuaryV.dbf
F:\Climate\test\JanuaryPointValuesV.dbf

They are populated with various fields, only two of which you are trying to merge, correct? The TempFloat and TempFloat2 which come from the UCID and RASTERVALU/MEAN fields.

Have you tried merging these two tables using the tools directly in catalog or arcmap? Does that work or do you get an error or the same empty output? You might also want to try using double \\ to avoid any confusion by python.

Re: Field Mapping Help With Merge Tool

LornaMurison — Thu, 24 Mar 2011 13:53:38 GMT

I just switched "/" to "\\" and it works! So simple, thank-you so much.
I had a whole reply typed out, so I'll post it anyway in case anyone wants to know what I was trying to do:

You are almost correct
My two input tables and the fields I am interested in are as follows:
F:\Climate\test\zStatJanuaryV.dbf UCID, TempFloat
F:\Climate\test\JanuaryPointValuesV.dbf Catch_02_3, TempFloat2

output fields:
UCID = zStatJanuaryV.UCID and JanuaryPointValuesV.Catch_02_3
Temp = zStatJanuaryV.TempFloat and JanuaryPointValuesV.TempFloat2

TempFloat is MEAN converted to float precision 5 scale 1
TempFloat2 is RASTERVALU converted to float precision 5 scale 1

Re: Field Mapping Help With Merge Tool

MathewCoyle — Thu, 24 Mar 2011 13:57:23 GMT

No problem, those kinds of things are a real pain to track down. Python takes things so literally sometimes.

Re: Field Mapping Help With Merge Tool

GraemeBrowning — Thu, 26 Jul 2012 21:17:19 GMT

A similar Question has just been posed in Stack Exchange GIS - thanks for having worked through this so thoroughly here so that I could offer your work as a possible/likely Answer.

I had major dramas with Field Mapping of Table To Table being unexpectedly reset in some very large models at 9.3 and was glad to see those resolved at 10.0.

- Graeme

Re: Field Mapping Help With Merge Tool

AdamCox1 — Sat, 11 Dec 2021 08:04:36 GMT

I second that sentiment exactly.

I find it easier to just delete unwanted fields with something like this. To me it is more logical and faster from what I have tried it on.
ouput = #<feature class>
dropFields = list()
fieldList = gp.ListFields(output)
for f in fieldList:
    if f.name <> "BLOCKSTAGE" and f.type <> "OID" and f.type <> "Geometry":
        print "Adding field to drop list "+f.name
        dropFields.append(f.name)
    gp.DeleteField_management(output, dropFields)
Also, ensure that the fields you are merging are the same data type.

Hello, this looks very close to what I'm trying to do, and failing at. Instead of hard-coding the values "BLOCKSTAGE", "OID", etc., I'm wondering if there is a way to get those values from a list of fields in another fc. In other words, I'd like to check against one fc and then and then delete any duplicate fields before I merge the two.

Thanks for any help you can offer.
Adam

Re: Field Mapping Help With Merge Tool

MathewCoyle — Sat, 11 Dec 2021 08:04:39 GMT

Hello, this looks very close to what I'm trying to do, and failing at. Instead of hard-coding the values "BLOCKSTAGE", "OID", etc., I'm wondering if there is a way to get those values from a list of fields in another fc. In other words, I'd like to check against one fc and then and then delete any duplicate fields before I merge the two.

Thanks for any help you can offer.
Adam

I'm not sure I follow 100% but something like this should get you on your way. You may need to switch around which field list you loop through and which fc you are deleting from.

fc_target = #fc1
fc_merge = #fc2

fc_target_fields = [field.name for field in arcpy.ListFields(fc_target)]
fc_merge_fields = [field.name for field in arcpy.ListFields(fc_merge)]

dropFields = list()
for field in fc_target_fields:
    if field in fc_merge_fields:
        print "Adding field to drop list %s" % field
        dropFields.append(field)
    arcpy.DeleteField_management(fc_target, dropFields)

Re: Field Mapping Help With Merge Tool

AdamCox1 — Thu, 20 Dec 2012 15:32:37 GMT

Splendid, thanks a million. Only thing I had to do was dedent the DeleteField line so that it did not try to run every time after checking each field.