3

I'd like to merge several tables together using Python. They all have the same field names: "TIME", "X", "Y", and "VALUE".

I would like the "VALUE" fields to remain separate when merged, so that the new table has the fields: "TIME" "X" "Y" "VALUE1" "VALUE 2" "VALUE3"...and so on.

I wrote a script based off the ERSI script for merging, and tried to use Field Mapping in a way that would let me rename "VALUE" for each table before merging.

# Name: Merge.py
import arcpy
from arcpy import env

# Set environment settings
env.workspace = "C:/Users/user1/Documents/Processing"

# Get tables to be merged
listTable = arcpy.ListTables("*", "ALL")

# Create FieldMappings object to manage merge output fields
fieldMappings = arcpy.FieldMappings()
i = 1

# All tables have a column "VALUE" only I don't want them to merge
for tables in listTable:
    fieldMappings.addTable(tables)
    fldMap_SOSvalue = arcpy.FieldMap()
    fldMap_SOSvalue.addInputField(tables,"VALUE")
    SOSvalue = fldMap_SOSvalue.outputField
    SOSvalue.name = "SOSvalue" + str(i)
    fldMap_SOSvalue.outputField = SOSvalue
    fieldMappings.addFieldMap(fldMap_SOSvalue)
    i += 1

# Use Merge tool to move features into single dataset
mergetable = "C:/Users/breezevi/Documents/ConModelTest/Tables/2013.dbf"
arcpy.Merge_management(listTable, mergetable, fieldMappings)
print "Merge is done"

This script runs, however, it does not seem to merge correctly. The resulting table has the fields "TIME" "X" "Y" "VALUE1" "VALUE2" "VALUE3" ... but all the numbers in each "VALUE#" column seem to be the same (e.g. it reads 1 | 1 | 1 | 1 across the whole row, but it should read 1 | 3 | 5 | 2 ). Almost like the script is creating all the new fields, but only filling them with values from one table. Only, I'm not sure where I went wrong in the script.

  • Starting in Arc 10.2.1, you can change the fields in your original tables (arcpy.AlterField_management(tables, "VALUE", "VALUE" + str(i))) -- but I take it from the 10.0 tag you don't have that available? – Erica Sep 4 '14 at 16:18
  • Correct - still running 10.0. There are rumors we're upgrading to 10.2.1, but nothing yet. So for now, stuck looking for a workaround. – Savi Sep 4 '14 at 16:28
2

What's happening is that if you have 5 tables, 5 value fields are being created, but the mapping looks like this,

Value --> Value1

Value --> Value2

Value --> Value3

etc...

So that when you run the Merge in the second to last line, the value column in each table gets written to all of these columns.

I would suggest implementing what @Erica hints at.

i = 1
for table in listTables:
  // Add a field to every table, call it "Value" + i
  arcpy.AddField_management(table, "Value" + i, field_type)
  // Populate the new field with the values from the "Value" field
  arcpy.CalculateField_management(table, "Value" + i, expression)
  i += 1

mergetable = "C:/Users/breezevi/Documents/ConModelTest/Tables/2013.dbf"
arcpy.Merge_management(listTable, mergetable, fieldMappings)

// Delete the Value field as it's duplicated now
arcpy.DeleteField_management(mergetable, "Value")

print "Merge is done"
  • Thank you! I ended up having mistakes before the merge issue which was exasperating things, but am leaving this question and answer up in case anyone else searches for the same thing later on. – Savi Sep 4 '14 at 20:08

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.