2

I have over one hundred dbf files to merge. Each of files have two columns: ID and another field which differs from one file to another. I want to merge those fields based on ID such that each raw corresponds to an unique ID containing all merged fields. For example:

file 1:
ID FirstName
1  Alan
2  Bell

file 2:
ID LastName
1  R
2  A

merged file:
ID FirstName LastName
1  Alan      R
2  Bell      A

Solution:

I have a series of files named as 'rawfile200101', 'rawfiles200102',...,'rawfile201104'. Each of files have two columns: ID and another field same as the file name. I want to merge those fields based on ID such that each raw corresponds to an unique ID containing all merged fields.

Different from Stata, the "merge" process is called "Join Field" in ArcGIS. Since I have lots of these files, I will do this in batch. Take the following as an example

import arcpy
from arcpy import env
from arcpy.sa import *
arcpy.CheckOutExtension("Spatial")
env.overwriteOutput = True

yrmn = ['200102', '200103', '200104']

for list in yrmn:
    inFeatures = "I:/rawfile200101.dbf"
    joinField = "VALUE"
    joinTable = "I:/rawfile{}.dbf".format(list)
    fieldList = ["rawfile{}".format(list)]

arcpy.JoinField_management (inFeatures, joinField, joinTable, joinField, fieldList)
  • 2
    This looks like you just need to use Join Field in a manual test. Would you be able to edit your Question to mention if that solves the test case you describe and, if so, details about where a Python script needs to look to find these and your other hundred files. – PolyGeo Jan 5 '14 at 21:24
  • 1
    Thanks very much for your help, @PolyGeo. IT WORKS!!! I am very new to python and arcgis. What I want is called "Merge" in statistics programing like Stata, but "Merge" here is totally different story, which doing things like "Append" in Stata. Similar confusions have caused lots of troubles to me. – user25458 Jan 6 '14 at 5:35
2

What you are calling a Merge appears to be what I would use the Join Field tool for because it ...

Joins the contents of a table to another table based on a common attribute field. The input table is updated to contain the fields from the join table. You can select which fields from the join table will be added to the input table.

2

you can either iterate using arcpy.da.searchCursor and arcpy.da.insertCursor (easy is you have perfect matching between the ID's) (see http://resources.arcgis.com/en/help/main/10.1/index.html#//018w00000011000000), or use the built in function arcpy.Addjoin_management (http://help.arcgis.com/en/arcgisdesktop/10.0/help/index.html#//001700000064000000)

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.