Hi Jeremy,I normally prefer using an insert cursor, although James Crandall once showed me a more elegant way using panda and numpy. Below the code using the insert cursor:
- I added a reference to the os module
- two extra variables to indicate the output workspace (path of fgdb) and the output table name
- I copied your code for creating the empty table
- I changed adding the fields slightly (now uses the same variables used earlier in the script)
- The insert cursor create a row (tuple in the same order of fields as the 'flds' variable)
- and finally insert the row into the cursor
import arcpy, os
arcpy.env.overwriteOutput = True
# your data:
tbl = r'C:\Project\_Forums\_maxDict\fgdb\test.gdb\aTable2'
fld_freq = 'Frequency'
fld_mapcode = 'Map_Code'
fld_datasource = 'DataSource'
# extra vars for output table
out_ws = r'C:\Output' # r'C:\Project\_Forums\_maxDict\fgdb\test.gdb'
tbl_name = 'mmc2.dbf' # 'mmc2'
# create empty dictionary to store results
dct = {}
# loop through table
flds = (fld_freq, fld_mapcode, fld_datasource)
with arcpy.da.SearchCursor(tbl, flds) as curs:
for row in curs:
freq = row[0]
mc = row[1]
ds = row[2]
if mc in dct:
freq_tot = dct[mc][0] + freq
lst_ds = dct[mc][1]
lst_ds.append(ds)
dct[mc] = (freq_tot, lst_ds)
else:
dct[mc] = (freq, [ds])
# write results to table
arcpy.CreateTable_management(out_ws, tbl_name)
tbl_out = os.path.join(out_ws, tbl_name)
arcpy.AddField_management(tbl_out, fld_mapcode, "Text", 9, "", 10, "", "NULLABLE", "")
arcpy.AddField_management(tbl_out, fld_freq, "LONG", 9, 0, "", "", "NULLABLE", "")
arcpy.AddField_management(tbl_out, fld_datasource, "Text", 9, "", 255, "", "NULLABLE", "")
# we still have the var flds
with arcpy.da.InsertCursor(tbl_out, flds) as curs:
for mc, val in dct.items():
lst_ds = sorted(set(val[1]))
freq_tot = val[0]
row = (freq_tot, mc, ";".join(lst_ds))
curs.insertRow(row)
With the chance of confusing you a little bit here is a numpy solution:import arcpy, os, numpy
arcpy.env.overwriteOutput = True
# your data:
tbl = r'C:\Project\_Forums\_maxDict\fgdb\test.gdb\aTable2'
fld_freq = 'Frequency'
fld_mapcode = 'Map_Code'
fld_datasource = 'DataSource'
# extra vars for output
out_ws = r'C:\Output' # r'C:\Project\_Forums\_maxDict\fgdb\test.gdb'
tbl_name = 'mmc2.dbf' # 'mmc2'
# create empty dictionary to store results
dct = {}
# loop through table
flds = (fld_freq, fld_mapcode, fld_datasource)
with arcpy.da.SearchCursor(tbl, flds) as curs:
for row in curs:
freq = row[0]
mc = row[1]
ds = row[2]
if mc in dct:
freq_tot = dct[mc][0] + freq
lst_ds = dct[mc][1]
lst_ds.append(ds)
dct[mc] = (freq_tot, lst_ds)
else:
dct[mc] = (freq, [ds])
# create list for use in numpy; each item is a tuple of frequency, map code and list of datasources
lst_out = [(val[0], mc, ";".join(sorted(set(val[1])))) for mc, val in dct.items()]
# convert the list to a numpy array
npa = numpy.array(lst_out, numpy.dtype([(fld_freq, numpy.int32), (fld_mapcode, '|S9'), (fld_datasource, '|S255')]))
# store the table
arcpy.da.NumPyArrayToTable(npa, os.path.join(out_ws, tbl_name), flds)
