Arcpy: How to use da.cursors to 'pivot' one table into another

Question

Let say I have a table that looks this:

State--City
TX--Houston
TX--Dallas
OK--Stillwater

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

State--City1--City2--City3

For results that look like this:

State--City1--City2--City3
TX--Houston--Dallas--Null
OK--Stillwater--Null--Null

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

Answer 1

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]

#insert
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))

Answer 2

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.