[GIS] How to merge dbf files in Python or arcpy

arcgis-desktoparcpydbfmergepython

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)

Best Answer

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.

Related Question