15 Replies Latest reply on Dec 20, 2012 7:32 AM by AdamCox

    Field Mapping Help With Merge Tool

    lady_jane
      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
          clm42
          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
            lady_jane
            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
              lady_jane
              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
                lady_jane
                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
                  mzcoyle
                  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
                    lady_jane
                    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
                      mzcoyle
                      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
                        lady_jane
                        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
                          mzcoyle
                          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
                            lady_jane
                            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
                              mzcoyle
                              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
                                graeme
                                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
                                  AdamCox
                                  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
                                    mzcoyle
                                    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
                                      AdamCox
                                      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.