Seeking help with arcpy.Merge_management

[IanMCDONALD]

I am seeking a method to merge table outputs from ZonalHistogram (ZH) using a Python script. These files have a simple two-column format. E.g., AAA.dbf has two fields:

• LABEL - the X-axis with values ranging from 0 to 100
• AAA - the Y-axis counts produced by ZH

Similarly, BBB.dbf has two fields, LABEL and BBB, CCC.dbf has LABEL and CCC. I am able to plot these individual table files with arcpy graph, or render them in Excel.

I am trying to merge these files into a single output with the structure:
LABEL    AAA             BBB             CCC
0 - 100    ZH values    ZH values    ZH values

I started simply with:

tablesList = arcpy.ListTables()
arcpy.Merge_management(tablesList, "merged.dbf")‍‍‍‍‍‍

which gave me the four columns I was after but the output was arranged as:

rows 0 - 100:        LABEL (0 - 100)    ZH values        0                   0
rows 101 - 201:    LABEL (0 - 100)    0                      ZH values     0
rows 202 - 302:    Label (0 - 100)       0                      0                   ZH values

I have attempted to use field mapping as follows:

import arcpy
TESTDIR = <some dir>

arcpy.env.workspace = TESTDIR
arcpy.env.scratchWorkspace = TESTDIR
arcpy.env.overwriteOutput = True

tablesList = arcpy.ListTables()
inFields = [u'LABEL', u'AAA', u'BBB', u'CCC'] # Generated by arcpy.Describe()
fieldMappings = arcpy.FieldMappings()

 # Deal with "LABEL" as special case (first field of first table)
fMap = arcpy.FieldMap()
fMap.addInputField(tablesList[0], inFields[0])
outField = fMap.outputField
outField.name = inFields[0]
fMap.outputField = outField
fieldMappings.addFieldMap(fMap)

 # Add the second field of each file to the map
for i in range(len(tablesList)):
 fMap = arcpy.FieldMap()
 fMap.addInputField(tablesList[i], inFields[i + 1])
 outField = fMap.outputField
 outField.name = inFields[i + 1]
 fMap.outputField = outField
 fieldMappings.addFieldMap(fMap)
arcpy.Merge_management(tablesList, "merged.dbf", fieldMappings)‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍

The output was the same. It seems I have merely replicated the built-in Merge_management. I would be most grateful for any advice on modifying the script to generate the merged output I am after.

[DanPatterson_Retired]

When you do explore the join options check your data for singularities of the Label field otherwise you may have to deal with an additional 'summarize' as I indicated.  If they are singular then either of the 'join' options would make things a lot easier for you

[RandyBurton]

While it is good to know something about field mappings, they can be annoying to work with.  And there are alternatives.  Here's some code I was testing that might be of interest.

It starts by making a copy of the first dbf file.  Then lines 14-21 checks for the specific field of interest, and if found, creates an identical type in the dbf copy.  After creating the field, the script loads the second dbf's data of interest into a dictionary.  An update cursor uses the dictionary to populate the newly created field.  Note this is assuming there is a 1:1 match on the LABEL field.

As script was just a quick test, some additional error checking may be necessary.

import arcpy
import sys

# make a copy of the first dbf file
dbfAAA = r"C:\test\dbf\AAA.dbf" # this is the first dbf
dbfOUT = r"C:\test\dbf\OUT.dbf" # this is the copy fields will be added to
arcpy.Copy_management(dbfAAA, dbfOUT)

# this is the dbf containing the field we want
dbfBBB = r"C:\test\dbf\BBB.dbf"
fldsBBB = ['LABEL', 'BBB'] # fields for our dictionary, the second one is the one we need to copy

# get the field's information and add the same type of field to our copy dbf file
for field in arcpy.ListFields(dbfBBB,fldsBBB[1]):
    if field.name is not None: # add field, (assuming all these properties are available)
        arcpy.AddField_management(dbfOUT, field.name, field.type, field.precision,
                                  field.scale, field.length, field.aliasName,
                                  field.isNullable, field.required, field.domain)
    else:
        print "ERROR: field {} not found".format(fldsBBB[1])
        sys.exit(0) # exit on error, may want to recode for more graceful exit

# read second dbf file into a dictionary
dicBBB = {r[0]:r[1] for r in arcpy.da.SearchCursor(dbfBBB, fldsBBB)}

# use update cursor to add the data to the dbf copy
with arcpy.da.UpdateCursor(dbfOUT,fldsBBB) as cursor:
    for row in cursor:
        row[1] = dicBBB[row[0]]
        cursor.updateRow(row)‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍

Regarding field mappings, here is an old article from ArcUser that might be helpful:

Use Field Mapping and Python Scripting to Make Your Job Easier   PDF version

[IanMCDONALD]

Thanks very much for this contribution, Randy.  I'll do some work with it over the weekend.

[IanMCDONALD]

I have run this too and it works well.  I found it a most instructive alternative to the join approach.  Thank you