Joining selected fields from table to shapefile using ArcGIS Pro?

Question

I have a line shapefile with 100+ fields and a separate dbf file with 100+ fields. I'd like to programatically (using arcpy and Python 3) eliminate all but 3 fields in both shapefile and the dbf, and then join on a common field. The desired output would only contain 5 fields.

If I do a simple join:

joined = arcpy.AddJoin(path_to_shp, "id_field", path_to_dbf, "id_field")

I cannot delete the extra fields. And if I write this joined table to a file, the joined field names become unrecognizable due to length constraints.

I was looking at the 'field_info' parameter of MakeFeatureLayer, but I can't find any examples of how this would be implemented, and it seems like none of the tools I'd normally use for this take FieldMappings.

Answer 1

AddJoin is an in memory join which typically you make and break within a session. Join Field tool does a permanent join of data.

But you are talking about joining 100's of fields to 100's of fields which may exceed the design limitations of a shapefile.

A better and more robust solution is to hide the fields you don't want and then use the Copy Features tool to create a temporary copy, this will honour hidden fields and the resulting output would be a dataset of the 3 fields. Copy Rows should honour hidden fields too. Then use Join Field to bring the two datasets together.

Answer 2

In the off chance you or a future reader has the option of doing this geopandas, here's the basic approach you'd take:

1. Read both files with geopandas.read_file
2. select only the columns you want to keep + the 'geometry' column of the shapefile
3. use the .merge method
4. pipe the merge result to geopandas.GeoDataFrame in case it returned a dataframe and not a geodataframe

Here's what that would look like:

import geopandas

new_gdf = (
    geopandas.read_file('/path/to/data.shp')
        .loc[:, ['A', 'B', 'C', 'geometry']]
        .merge(
            geopandas.read_file('/path/to/meta.dbf').loc[:, ['A', 'D', 'E']],
            on='A'
        )
        .pipe(geopandas.GeoDataFrame, geometry='geometry')
)

new_gdf.to_file('path/to/merged.shp')