Merging .dbf Tables in Python w/o merging all fields of same name

Question

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.

Answer 1

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"