Let say I have a table that looks this:


And I want to insert it into a table that looks like this:


For results that look like this:


Should I use a search cursor to build a list then use that list to populate the 'insertRow'?


Make use of a dictionary. Create sets of cities associated with each state. Then use an updatecursor to create your pivot table. You can customize the number of city fields as well by getting the highest count of cities.

Something like this (untested):

#table with state/city data
tab = r"C:\path\to\tab"
#pivot table to be populated
pivTab = r"C:\path\to\pivot\tab"
#table state field
stateFld = "State"
#table city field
cityFld = "City"
#pivot table state field
pivStateFld = "State"

#create dictionary of state/cities
di = {}
with arcpy.da.SearchCursor(tab, [stateFld, cityFld]) as curs:
            for state, city in curs:
                #add city to set
                try: di [state].add (city)
                except: di [state] = set ([city])

#get max count of cities
maxCityCount = max ([len (di [v]) for v in di])

#add city fields
updateFlds = []
for i in range (1, maxCityCount + 1):
    #city field name (numbered)
    fldName = "City{}".format (i)
    #add field
    arcpy.AddField_management (fldName, "TEXT")
    #add to list of city fields
    updateFlds += [fldName]

with arcpy.da.InsertCursor (pivTab, updateFlds + [pivStateFld]) as curs:
    for state in sorted (di.keys ()):
        #get list of cities by state
        row = sorted (list (di [state]))
        #add nulls to row
        while len (row) < maxCityCount:
            row += [None]
        #add state to row
        row += [state]
        #update row
        curs.insertRow (tuple (row))
  • Great. What does (tuple (row)) do? – Rex Sep 27 '17 at 20:03
  • It converts the list row into a tuple. This may be unnecessary. – Emil Brundage Sep 27 '17 at 20:07

Yes you can use a search cursor to build a dictionary like this:

StateDict = {}        
with arcpy.da.SearchCursor(Table1, ["State", "City"]) as Cursor:
            for row in Cursor:
                StateDict[row[1]] = row[0]

Then use an Insert Cursor to update table2.

  • 1
    No Houston in final table when using your solution. Dictionary value should be list. – FelixIP Sep 27 '17 at 14:48
  • Why would there be no houston? it would look like {"Houston":"Texas", "Dallas":"Texas", "Stillwater":"Oklahoma"} where the city is the key. – atxgis Sep 27 '17 at 15:26
  • I think the confusion is that you call it state dict but the key is city (row[1]). I think @FelixIP missed that you are adding to the dictionary by city name and not state. If you added by state then you overwrite Houston, hence no Houston! Issue here is, is there a scenario where you have two cities with the same name in one state? Don't know I'm not American. – Hornbydd Sep 27 '17 at 16:03
  • I see , my fault. Anyway it produces 2 columns table, which is equivalent of source table and nothing to do with objective defined. – FelixIP Sep 27 '17 at 16:57
  • Cool good feedback – atxgis Sep 27 '17 at 17:41

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.