topic Re: Seeking help with arcpy.Merge_management in Python Questions

Seeking help with arcpy.Merge_management

IanMCDONALD — Sat, 11 Dec 2021 07:17:38 GMT

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.

Re: Seeking help with arcpy.Merge_management

DanPatterson_Retired — Tue, 19 Mar 2019 03:18:01 GMT

LABEL AAA BBB CCC
0 - 100 ZH values ZH values ZH values

This suggests to me a join on the LABEL field. But is there a many to one, one to one etc.

Maybe if you have a larger sample to show, it might help.

Join with a summarize? hard to tell

Re: Seeking help with arcpy.Merge_management

RandyBurton — Sat, 11 Dec 2021 07:17:40 GMT

It looks as if you want to join tables, not merge them. See Add Join for more information. You would join on the LABEL field.

An alternative to the join is to read the dbf files into dictionaries using a SearchCursor, combining them and saving the results. An example:

import arcpy

dbf = r"C:\path\to\dbf\AAA.dbf"
flds = ['LABEL', 'AAA']
dic1 = {r[0]: r[1] for r in arcpy.da.SearchCursor(dbf, flds)} 

dbf = r"C:\path\to\dbf\BBB.dbf"
flds = ['LABEL', 'BBB']
dic2 = {r[0]: r[1]  for r in arcpy.da.SearchCursor(dbf, flds)}

dbf = r"C:\path\to\dbf\CCC.dbf"
flds = ['LABEL', 'CCC']
dic3 = {r[0]: r[1]  for r in arcpy.da.SearchCursor(dbf, flds)}

print "LABEL\tAAA\tBBB\tCCC"
for k, v in dic1.iteritems():
    print "{}\t{}\t{}\t{}".format(k, v, dic2, dic3)

# would give something like:
'''
LABEL	AAA	BBB	CCC
0	100	200	300
1	101	201	301
2	102	202	302
3	103	203	303
4	104	204	304
'''‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍

Re: Seeking help with arcpy.Merge_management

curtvprice — Tue, 19 Mar 2019 05:28:40 GMT

Or, Add Join tables 2 and 3 to table 1 and copy the table view with Table to Table. This method has a nice advantage in that you can use a field map to control the order and names of the output fields.

Re: Seeking help with arcpy.Merge_management

IanMCDONALD — Tue, 19 Mar 2019 09:18:54 GMT

Thank you Dan, Randy and Curtis. I am most grateful for your ideas. I am unable to follow up on them this evening but I will let you know how I get on.

Re: Seeking help with arcpy.Merge_management

DanPatterson_Retired — Tue, 19 Mar 2019 09:32:37 GMT

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

Re: Seeking help with arcpy.Merge_management

IanMCDONALD — Sat, 11 Dec 2021 07:17:43 GMT

Hi All,

I'm having a go at both approaches. I need to gain some experience with field mappings, so ...

After the join, tbl1 has the fieldnames:

[u'AAA.OID', u'AAA.LABEL', u'AAA.AAA', u'BBB.OID', u'BBB.LABEL', u'BBB.BBB', u'CCC.OID', u'CCC.LABEL', u'CCC.CCC']

I determine which of these I want to keep for the TableToTable_conversion with:

keepFields = [u'AAA.OID', u'AAA.LABEL', u'AAA.AAA', u'BBB.BBB', u'CCC.CCC']

and attempt to build the fieldMappings with:

fieldMappings = arcpy.FieldMappings()
for field in fieldnames:
 fMap = arcpy.FieldMap()
 if field in keepFields:
  fMap.addInputField(tbl1, field)
  outField = fMap.outputField
  outField.name = field
  fMap.outputField = outField
 fieldMappings.addFieldMap(fMap)‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍

which produces the error:

RuntimeError: FieldMap: Error in adding input field to field map

for line 5.

Can you suggest how I might follow this up? My limited experience can find no help in the error message at all!

Ian

Re: Seeking help with arcpy.Merge_management

curtvprice — Fri, 22 Mar 2019 01:50:34 GMT

I would print field in the loop so you can figure which one failed.

Re: Seeking help with arcpy.Merge_management

RandyBurton — Sat, 11 Dec 2021 07:17:46 GMT

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

Re: Seeking help with arcpy.Merge_management

IanMCDONALD — Fri, 22 Mar 2019 09:49:19 GMT

Yes, I had started that task when your suggestion arrived. It turns out all of them break it.

I see when the TableToTable conversion is complete, the dbf has had its field names mangled with trailing "_" and digits and restricting them to 10 characters. I was thinking this happened at this step but perhaps it occurs at the AddJoin_management? If this is the case, I can understand the addInputField might get confused.

Re: Seeking help with arcpy.Merge_management

IanMCDONALD — Fri, 22 Mar 2019 09:51:07 GMT

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

Re: Seeking help with arcpy.Merge_management

curtvprice — Fri, 22 Mar 2019 14:42:08 GMT

I see when the TableToTable conversion is complete, the dbf has had its field names mangled with trailing "_" and digits and restricting them to 10 character

The 10 character truncation is because you your output is in shapefile or .dbf format. If you output to a geodatabase your field names will not be mangled to fit in the 10 character limit. The field names changes are those that are required to make the field name valid for the output format see the ValidateFieldName method in the help for more on that.

You may have better luck if you set the environment arcpy.env.qualifiedFieldNames to False, this will prevent your merge from trying to include the prefix in the output name. (When I think about it, this may be your main issue.)

Re: Seeking help with arcpy.Merge_management

IanMCDONALD — Sat, 23 Mar 2019 10:30:02 GMT

Hi Curtis,

Setting the environment in this way certainly helped to make sense of what was happening with the field names. All the field names still failed so I figured I should look at the other parameter in addInputField. It turns out I was trying to use the arcpy.mapping.TableView view of the input dataset rather than the dataset name itself.

Thanks also for the 10-character limit explanation.

Ian

Re: Seeking help with arcpy.Merge_management

IanMCDONALD — Sun, 24 Mar 2019 05:54:26 GMT

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