Creating numpy.array with variable number of fields to test arcpy.da.ExtendTable performance?

Question

There was anArcPy Café blog posting entitled Adding Fields: Performance Tips which advocated:

Two approaches to help increase performance when adding numerous fields to a table or feature class.

...

2. Use the data access and NumPy modules. The data access module function named ExtendTable() joins the contents of a NumPy structured array to a table based on a common attribute field.
This is the faster approach, however, the types of fields you can add using numpy are limited. There is no support for adding blobs, raster, and date fields. In addition, the field alias can not be defined or altered.

I have done some performance testing, using ArcGIS 10.2 for Desktop, of this method versus multiple (three and nine) Add Fields on file geodatabase feature classes of 16 to 1,000,000 polygons. My initial findings suggest that any gains for nine and especially three fields are lost as the number of polygons increases. Consequently, I suspect that this method should only be advocated for many fields and not so many features.

Below is my current test code, and its current output but these are presented purely for interest/background.

What I would like know is how to make the number of fields placed into the NumPy Array become configurable?

I suspect that there is a Python technique unknown to me but my searches have not yet turned it up so I will try to explain ...

If I have a variable numFields = 10 can I use something like for i in range(numFields) to expand 'TEST_INTEGER'+str(i) to become ...

narray = numpy.array([],
                     numpy.dtype([('_ID', numpy.int),
                                  ('TEST_INTEGER'+str(0), numpy.int),
                                  ('TEST_INTEGER'+str(1), numpy.int),
                                  ('TEST_INTEGER'+str(2), numpy.int),
                                  ('TEST_INTEGER'+str(3), numpy.int),
                                  ('TEST_INTEGER'+str(4), numpy.int),
                                  ('TEST_INTEGER'+str(5), numpy.int),
                                  ('TEST_INTEGER'+str(6), numpy.int),
                                  ('TEST_INTEGER'+str(7), numpy.int),
                                  ('TEST_INTEGER'+str(8), numpy.int),
                                  ('TEST_INTEGER'+str(9), numpy.int),
                                  ]))

import arcpy,numpy,time

fc = r"C:\polygeo\Projects\test.gdb\testFishnet"
fc2 = r"C:\polygeo\Projects\test.gdb\testFishnet2"
cellWidthHeightList = ["0.25","0.1","0.025","0.01","0.0025","0.001"]

for cellWidthHeight in cellWidthHeightList:
    numCells = (1 / float(cellWidthHeight)) ** 2
    print "Creating fishnet of {0} polygons".format(str(int(numCells)))
    if not arcpy.Exists(r"C:\polygeo\Projects\test.gdb"):
        arcpy.CreateFileGDB_management(r"C:\polygeo\Projects","test.gdb")
    if arcpy.Exists(r"C:\polygeo\Projects\test.gdb\testFishnet"):
        arcpy.Delete_management(r"C:\polygeo\Projects\test.gdb\testFishnet")
        arcpy.CreateFishnet_management("C:/polygeo/Projects/test.gdb/testFishnet",
                                       "0 0","0 1",cellWidthHeight,cellWidthHeight,
                                       "#","#","1 1","NO_LABELS","#","POLYGON")
    if arcpy.Exists(r"C:\polygeo\Projects\test.gdb\testFishnet2"):
        arcpy.Delete_management(r"C:\polygeo\Projects\test.gdb\testFishnet2")
        arcpy.Copy_management(r"C:\polygeo\Projects\test.gdb\testFishnet",
                              r"C:\polygeo\Projects\test.gdb\testFishnet2",
                              "FeatureClass")

    start = time.clock()
    arcpy.AddField_management(fc,"TEST_INTEGER","LONG","#","#","#","#",
                              "NULLABLE","NON_REQUIRED","#")
    arcpy.AddField_management(fc,"TEST_TEXT","TEXT","#","#","100","#",
                              "NULLABLE","NON_REQUIRED","#")
    arcpy.AddField_management(fc,"TEST_FLOAT","DOUBLE","#","#","#","#",
                              "NULLABLE","NON_REQUIRED","#")
    arcpy.AddField_management(fc,"TEST_INTEGER2","LONG","#","#","#","#",
                              "NULLABLE","NON_REQUIRED","#")
    arcpy.AddField_management(fc,"TEST_TEXT2","TEXT","#","#","100","#",
                              "NULLABLE","NON_REQUIRED","#")
    arcpy.AddField_management(fc,"TEST_FLOAT2","DOUBLE","#","#","#","#",
                              "NULLABLE","NON_REQUIRED","#")
    arcpy.AddField_management(fc,"TEST_INTEGER3","LONG","#","#","#","#",
                              "NULLABLE","NON_REQUIRED","#")
    arcpy.AddField_management(fc,"TEST_TEXT3","TEXT","#","#","100","#",
                              "NULLABLE","NON_REQUIRED","#")
    arcpy.AddField_management(fc,"TEST_FLOAT3","DOUBLE","#","#","#","#",
                              "NULLABLE","NON_REQUIRED","#")
    elapsed = (time.clock() - start)
    print " - AddField took {0} seconds to add NINE fields".format(elapsed)

    start = time.clock()
    narray = numpy.array([],
                         numpy.dtype([('_ID', numpy.int),
                                      ('TEST_INTEGER', numpy.int),
                                      ('TEST_TEXT', '|S100'),
                                      ('TEST_FLOAT', numpy.float),
                                      ('TEST_INTEGER2', numpy.int),
                                      ('TEST_TEXT2', '|S100'),
                                      ('TEST_FLOAT2', numpy.float),
                                      ('TEST_INTEGER3', numpy.int),
                                      ('TEST_TEXT3', '|S100'),
                                      ('TEST_FLOAT3', numpy.float),
                                      ]))
    print " - numpy.array() took {0} seconds".format((time.clock() - start))
    arcpy.da.ExtendTable(fc2, "OID@", narray, "_ID")
    print " - arcpy.da.ExtendTable() took {0} seconds".format((time.clock() - start))
    elapsed2 = (time.clock() - start)
    print " - NumPy and ExtendTable took {0} seconds to add NINE fields".format(elapsed2)
    print " - NumPyExtendTable:AddField ratio = {0}".format(str(float(elapsed2) / float(elapsed)))

>>> ================================ RESTART ================================
>>> 
Creating fishnet of 16 polygons
 - AddField took 6.15530941159 seconds to add NINE fields
 - numpy.array() took 8.66656530336e-05 seconds
 - arcpy.da.ExtendTable() took 0.251475596777 seconds
 - NumPy and ExtendTable took 0.25444199483 seconds to add NINE fields
 - NumPyExtendTable:AddField ratio = 0.0413369950748
Creating fishnet of 100 polygons
 - AddField took 7.01486277938 seconds to add NINE fields
 - numpy.array() took 6.49992397754e-05 seconds
 - arcpy.da.ExtendTable() took 0.523825072221 seconds
 - NumPy and ExtendTable took 0.554671962901 seconds to add NINE fields
 - NumPyExtendTable:AddField ratio = 0.0790709640866
Creating fishnet of 1600 polygons
 - AddField took 6.93555420404 seconds to add NINE fields
 - numpy.array() took 2.96487760387e-05 seconds
 - arcpy.da.ExtendTable() took 0.297160559526 seconds
 - NumPy and ExtendTable took 0.334817165881 seconds to add NINE fields
 - NumPyExtendTable:AddField ratio = 0.0482754738887
Creating fishnet of 10000 polygons
 - AddField took 6.60505587654 seconds to add NINE fields
 - numpy.array() took 2.77482134763e-05 seconds
 - arcpy.da.ExtendTable() took 0.612328569257 seconds
 - NumPy and ExtendTable took 0.672550554964 seconds to add NINE fields
 - NumPyExtendTable:AddField ratio = 0.101823598094
Creating fishnet of 160000 polygons
 - AddField took 9.54403527444 seconds to add NINE fields
 - numpy.array() took 3.57305762435e-05 seconds
 - arcpy.da.ExtendTable() took 5.36002166641 seconds
 - NumPy and ExtendTable took 5.40552797628 seconds to add NINE fields
 - NumPyExtendTable:AddField ratio = 0.566377619198
Creating fishnet of 1000000 polygons
 - AddField took 28.7716610917 seconds to add NINE fields
 - numpy.array() took 3.61106887681e-05 seconds
 - arcpy.da.ExtendTable() took 34.4609012468 seconds
 - NumPy and ExtendTable took 34.4646480158 seconds to add NINE fields
 - NumPyExtendTable:AddField ratio = 1.19786785706
>>>

I'd love to see the results; we can use them to improve performance in common cases. — Jason Scheirer, Commented Jun 4, 2014 at 1:40
@JasonScheirer You should be able to run some quick tests using the code I posted as an Answer - when number of fields added are say 3 and number of polygons is 1,000,000 it is much better (4.5x - see last test results I just added) to Add Field, but as the number fields to be added increase then NumPyExtendTable becomes quicker. My testing is no fancier than what I have written up here. — PolyGeo, Commented Jun 4, 2014 at 3:40

Evil Genius · Accepted Answer · 2014-06-03 11:10:31Z

5

You can use a list comprehension:

narray = numpy.array([], numpy.dtype([('_ID', numpy.int)] +
                                     [('TEST_INTEGER'+str(x), numpy.int) for x in range(numFields)]))

answered Jun 3, 2014 at 11:10

Evil Genius

6,2892 gold badges28 silver badges40 bronze badges

Add a comment |

PolyGeo · Accepted Answer · 2014-06-04 03:38:42Z

My final code, based on the Answer by @EvilGenius, and the results it produced for interest, are as below. You can see that the maximum benefit to using the Data Access and NumPy modules, over multiple uses of Add Field, to add additional fields is when there are lots of fields and fewer features.

If you have only a few fields to add, and lots of features then using a few Add Fields is quicker.

I was expecting that more features would always widen the gap in favour of the Data Access and NumPy modules method so was a little surprised to see the opposite gap open up when only a few fields are added with many features involved.

import arcpy,numpy,time

fc = r"C:\polygeo\Projects\test.gdb\testFishnet"
fc2 = r"C:\polygeo\Projects\test.gdb\testFishnet2"
cellWidthHeightList = ["0.1","0.01","0.001"]
numFloatFieldsList = [8,28,98]
for numFloatFields in numFloatFieldsList:
    for cellWidthHeight in cellWidthHeightList:
        numCells = (1 / float(cellWidthHeight)) ** 2

        if not arcpy.Exists(r"C:\polygeo\Projects\test.gdb"):
            arcpy.CreateFileGDB_management(r"C:\polygeo\Projects","test.gdb")
        if arcpy.Exists(r"C:\polygeo\Projects\test.gdb\testFishnet"):
            arcpy.Delete_management(r"C:\polygeo\Projects\test.gdb\testFishnet")
        print "Creating fishnet of {0} polygons".format(str(int(numCells)))
        arcpy.CreateFishnet_management(r"C:\polygeo\Projects\test.gdb\testFishnet",
                                       "0 0","0 1",cellWidthHeight,cellWidthHeight,
                                       "#","#","1 1","NO_LABELS","#","POLYGON")
        if arcpy.Exists(r"C:\polygeo\Projects\test.gdb\testFishnet2"):
            arcpy.Delete_management(r"C:\polygeo\Projects\test.gdb\testFishnet2")
        arcpy.Copy_management(r"C:\polygeo\Projects\test.gdb\testFishnet",
                              r"C:\polygeo\Projects\test.gdb\testFishnet2",
                              "FeatureClass")

        start = time.clock()
        for x in range(numFloatFields):
            arcpy.AddField_management(fc,"TEST_FLOAT"+str(x),"DOUBLE","#","#","#","#",
                                      "NULLABLE","NON_REQUIRED","#")
        arcpy.AddField_management(fc,"TEST_INTEGER","LONG","#","#","#","#",
                                  "NULLABLE","NON_REQUIRED","#")
        arcpy.AddField_management(fc,"TEST_TEXT","TEXT","#","#","100","#",
                                  "NULLABLE","NON_REQUIRED","#")
        elapsed = (time.clock() - start)
        print " - AddField took {0} seconds to add {1} fields".format(elapsed,str(2 + numFloatFields))

        start = time.clock()
        narray = numpy.array([],
                             numpy.dtype([('_ID', numpy.int)] +
                                         [('TEST_FLOAT'+str(x), numpy.float) for x in range(numFloatFields)] +
                                         [('TEST_INTEGER', numpy.int),('TEST_TEXT', '|S100')]))
        arcpy.da.ExtendTable(fc2, "OID@", narray, "_ID")
        elapsed2 = (time.clock() - start)
        print " - NumPy and ExtendTable took {0} seconds to add {1} fields".format(elapsed2,str(2 + numFloatFields))
        print " - NumPyExtendTable:AddField ratio = {0}".format(str(float(elapsed2) / float(elapsed)))

>>> ================================ RESTART ================================
>>> 
Creating fishnet of 100 polygons
 - AddField took 7.70771034263 seconds to add 10 fields
 - NumPy and ExtendTable took 0.248153882235 seconds to add 10 fields
 - NumPyExtendTable:AddField ratio = 0.0321955381306
Creating fishnet of 10000 polygons
 - AddField took 6.89551317455 seconds to add 10 fields
 - NumPy and ExtendTable took 0.665253910326 seconds to add 10 fields
 - NumPyExtendTable:AddField ratio = 0.0964763453401
Creating fishnet of 1000000 polygons
 - AddField took 30.2332401168 seconds to add 10 fields
 - NumPy and ExtendTable took 33.6147804976 seconds to add 10 fields
 - NumPyExtendTable:AddField ratio = 1.11184842801

Creating fishnet of 100 polygons
 - AddField took 20.4502870049 seconds to add 30 fields
 - NumPy and ExtendTable took 0.726164488171 seconds to add 30 fields
 - NumPyExtendTable:AddField ratio = 0.0355087675785
Creating fishnet of 10000 polygons
 - AddField took 23.9535915244 seconds to add 30 fields
 - NumPy and ExtendTable took 1.59976486159 seconds to add 30 fields
 - NumPyExtendTable:AddField ratio = 0.0667860124427
Creating fishnet of 1000000 polygons
 - AddField took 129.248610019 seconds to add 30 fields
 - NumPy and ExtendTable took 111.991675127 seconds to add 30 fields
 - NumPyExtendTable:AddField ratio = 0.866482626862

Creating fishnet of 100 polygons
 - AddField took 69.8152053888 seconds to add 100 fields
 - NumPy and ExtendTable took 2.02338346148 seconds to add 100 fields
 - NumPyExtendTable:AddField ratio = 0.0289819882389
Creating fishnet of 10000 polygons
 - AddField took 72.8902866096 seconds to add 100 fields
 - NumPy and ExtendTable took 4.54466470351 seconds to add 100 fields
 - NumPyExtendTable:AddField ratio = 0.0623493872077
Creating fishnet of 1000000 polygons
 - AddField took 418.9092904 seconds to add 100 fields
 - NumPy and ExtendTable took 330.8793667 seconds to add 100 fields
 - NumPyExtendTable:AddField ratio = 0.789859223185
>>>

And results of one extra test:

>>> ================================ RESTART ================================
>>> 
Creating fishnet of 100 polygons
 - AddField took 1.828699526 seconds to add 3 fields
 - NumPy and ExtendTable took 0.30685530312 seconds to add 3 fields
 - NumPyExtendTable:AddField ratio = 0.167799738971
Creating fishnet of 10000 polygons
 - AddField took 3.06924217256 seconds to add 3 fields
 - NumPy and ExtendTable took 0.202566890045 seconds to add 3 fields
 - NumPyExtendTable:AddField ratio = 0.0659989921473
Creating fishnet of 1000000 polygons
 - AddField took 2.04487743319 seconds to add 3 fields
 - NumPy and ExtendTable took 9.13887966064 seconds to add 3 fields
 - NumPyExtendTable:AddField ratio = 4.469157668
>>>

Interesting results, and thanks for sharing them. One other thing you may want to test is how the number of existing fields effects the performance. I know anecdotally that adding a field to a table that already has 100 fields will take longer than adding one to a table with 10 even if they have the same amount of rows. It'd be interesting if one method was better than another in that case too. — Evil Genius, Commented Jun 4, 2014 at 10:50

Stack Exchange Network

Creating numpy.array with variable number of fields to test arcpy.da.ExtendTable performance?

2 Answers 2

Your Answer

Not the answer you're looking for? Browse other questions tagged
arcpy
arcgis-10.2
performance
fields-attributes
numpy
or ask your own question.

Linked

Hot Network Questions

Creating numpy.array with variable number of fields to test arcpy.da.ExtendTable performance?

2 Answers 2

Your Answer

Sign up or log in

Post as a guest

Not the answer you're looking for? Browse other questions tagged arcpyarcgis-10.2performancefields-attributesnumpy or ask your own question.

Linked

Related

Hot Network Questions

Not the answer you're looking for? Browse other questions tagged
arcpy
arcgis-10.2
performance
fields-attributes
numpy
or ask your own question.