1

I am joining some tables to a shapefile and exporting with the following code:

import arcpy, pandas as pd, os
import arcpy.mapping
arcpy.env.overwriteOutput = 1
arcpy.env.qualifiedFieldNames = False

arcpy.CheckOutExtension('Spatial')
arcpy.env.workspace=r'F:\Sheyenne\Pixel_Regression\spatial_ts\NDII\monthly_regress'
shapefile=r'F:\Sheyenne\Pixel_Regression\raster_to_point\Sheyenne\NDII\sheyenne_19840517.shp'
out=r'F:\Sheyenne\Pixel_Regression\spatial_ts\NDII\monthly_shapefiles'
if not os.path.isdir(out):
    os.mkdir(out)
#loopo through tables  
for table in arcpy.ListTables("*"):  
#  #Create a new feature layer, this will remove previous join  
  arcpy.MakeFeatureLayer_management(shapefile, "parcelLyr")  
#   Join table to feature layer  
  arcpy.AddJoin_management("parcelLyr", "POINTID", table, "Month", 'KEEP_COMMON') 
  tableName = table.split(".")[0] 
  if not os.path.isdir(out):
            os.mkdir(out)
#   #Export joined layer to new feature clas  
  arcpy.FeatureClassToFeatureClass_conversion("parcelLyr", out, tableName)

Each table has 290,000 rows (and therefore joins), so maybe there is no faster way to do this. To join 5 tables with this method takes around 15 hours though which has me wondering if there is an alternative method.

EDIT:

I have explored using cursors to do this. To start I will show what my data looks like (very simplified). The shapefile I am joining to (hereafter called targshp) has an attribute table which looks like this:

FID   Shape   POINTID   GRID_CODE 
0     Point   1         -999
1     Point   87275     -999
2     Point   87276     -999
3     Point   87278     -999

my csv file (hereafter called joinshp) looks like this:

Month    RGR_Slope   RGR_pvalue   RGR_significant
87275    0.023       0.5979       No
87276   -0.021       0.0139       Yes
87278    0.061       0.2345       No

My join fields are POINTID for targshp and Month for joinshp. joinshp does not have as many fields as targshp and therefore this would be a type of KEEP_COMMON join. I am using the following approach using the cursors:

import arcpy
arcpy.env.overwriteOutput = 1
arcpy.env.qualifiedFieldNames = False

targshp=r'F:\Sheyenne\Pixel_Regression\spatial_ts\RGR\cursor_try\sheyenne_19840517.shp'
joinshp=r'F:\Sheyenne\Pixel_Regression\spatial_ts\RGR\monthly_regress\05.csv'
joinfields=['Month', 'RGR_slope', 'RGR_pvalue', 'RGR_significant']
joindict={}
with arcpy.da.SearchCursor(joinshp, joinfields) as rows:
    for row in rows:
        joinval = row[0]
        val1 = row[1]
        joindict[joinval]=[val1]
del row, rows

#
#
arcpy.AddField_management(targshp, "Month", "LONG", "5")
targfields = ['POINTID', 'Month' ]
with arcpy.da.UpdateCursor(targshp, targfields) as recs:
    for rec in recs:
        keyval = rec[0]

        if joindict.has_key(keyval):
            rec[1] = joindict[keyval][0]
        else:
            rec[1] = 0
        recs.updateRow(rec)
del rec, recs

This code runs through without error, and Month is added to targshp but the rest of the data is not actually joined into it, so I am still slightly off here.

  • 4
    You could explore combining Python dictionaries and ArcPy data access search cursors to perform dictionary comprehension on your table, and then read from that dictionary as you run an update cursor through your feature class. – PolyGeo Aug 23 '16 at 1:01
  • got it to work, reduced runtime to 10 minutes. – Stefano Potter Aug 23 '16 at 17:57
1

You could explore combining Python dictionaries and ArcPy data access search cursors to perform dictionary comprehension on your table, and then read from that dictionary as you run an update cursor through your feature class.

2

Not sure how often you will run the code or the frequency of data changing, however I would recommend importing the layer and tables to geodatabase and adding attribute indexes on all the join id fields. This will help speed up reoccurring joining.

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.