convert table that has many records per parcel to a table that has a single record

Question

Hello,

I have a table that has one or more lines in for each parcel depending on how long the legal description is.  I would like to change it so that there is only one line for each parcel, and several lines for the legal description.

Currently the table looks like this

Parcel             Record       Legal
02-0002-901       1             NW1/4SE1/4 LESS W 500 FT, SW1/4NE1/4 LESS W 5
02-0002-901       2             00 FT OF S 753 FT & N1/2NE1/4 LESS L.P. BIG S
02-0002-901       3             TONE LAKE-WHETSTONE RIVER PROJECT

I would like to change it so that it looks like this

Parcel             Record       Legal Line 1                                                                                Legal Line 2                                                       Legal Line 3
02-0002-901       1             NW1/4SE1/4 LESS W 500 FT, SW1/4NE1/4 LESS W 5    00 FT OF S 753 FT & N1/2NE1/4 LESS L.P. BIG S      TONE LAKE-WHETSTONE RIVER PROJECT

Any ideas on how to do this via model builder?>

LyonMNGIS · Answer

Tim,

It looks like your doing what I want to do with pivot tables.  I changed my record number from a double to a short int and I am still having trouble with the pivot table.  Here is what my inputs are.

Input Fields: PIN  (text)
Pivot Field: RecordNum (Short Integer)
Value Field: Legal (text)

I think this looks right, but it is not working.  I get a generic 999999 Error.  Let me know if I am doing something wrong

If I cant get this working maybe I can try calling support or using the python code

markdenil · Answer

This looks like something you would want to build a python script tool to handle.

A search cursor can be run through the table, and a python dictionary populated using the Parcel field value as the key.
Inside each Parcel entry you would build another dictionary, keyed to the record, that holds the bit of the description found in each Legal field.

You would end up with something like this:bigDict = {
    "02-0002-901":{1:"NW1/4SE1/4 LESS W 500 FT, SW1/4NE1/4 LESS W 5",
                 2:"00 FT OF S 753 FT & N1/2NE1/4 LESS L.P. BIG S",
                 3:"TONE LAKE-WHETSTONE RIVER PROJECT"},
    
    "02-0002-902":{1:"NW2/4SE2/4 LESS W 501 FT, SW1/4NE1/4 LESS W 5",
                 2:"00 FT OF S 754 FT & N1/2NE1/4 LESS L.P. BIG S",
                 3:"TICK LAKE-BLUESTONE RIVER PROJECT"}
    }

to get that you would need some code like this:bigDict = {}
cur = arcpy.SearchCursor(theTable)
for row in cur:
    par = row.Parcel
    rec = row.Record
    leg = row.Legal
    if bigDict.has_key(par):
        if not bigDict[par].has_key(rec):
            bigDict[par][rec] = leg
        else:
            pass
    else:
        bigDict[par]= {rec:leg}

You can then use an insert cursor to build a new table with a single legal description for each Parcel ID:
(you need to make the empty table first)iCur = InsertCursor(newTable)
#   for each parcel ID
for pKey in bigDict:
    #   make a blank legal description
    outString = ""
    #   sort the Record keys for this parcel, so they will be in order
    keyList = bigDict[pKey].keys().sort
    #   loop through them in order: 1, 2, 3 etc
    for aKey in keyList:
        #   build the concatenated string
        outString = "%s%s" % (outString, bigDict[pKey][aKey])
    #   make a new row in the table
    aNewRow = iCur.newRow()
    #   set the Parcel item to the parcel ID
    aNewRow.Parcel = pKey
    #   set the legal description to the contactenated string
    aNewRow.Legal = outString
    #   commit the new row
    iCur.insertRow(aNewRow)

make this script into a tool with the appropriate input and output parameters and add it to your model

TimHopper · Answer

Hmm...

I wasn't aware that the pivot field had to be an integer type (which is what I think you were referring to) but I just tried it and I'm getting strange results.

According to the help doc, it lists the input data type for the pivot field as "field".  Also, it says, "If the pivot field is a numeric type, its value will be appended to its original field name in the output table.".  To me, this would mean that your pivot field could also be a string.

Do you actually have three fields in your input table?  Parcel ID, Record, Legal Description?

I ran a test with a table with the three fields above (with Record being an integer) and the output was produced successfully.

[ATTACH=CONFIG]15062[/ATTACH]

[ATTACH=CONFIG]15063[/ATTACH]

However, I tried the same thing with the "Record" field as a string and I got empty rows returned.

[ATTACH=CONFIG]15064[/ATTACH]

A quick search didn't return anything for me that is bug related, so I'll need to dig into this one a little deeper.  Can't say I've spent much time with the Pivot Table tool before.

You mention that "I need a string".  Can you clarify that statement?  Is that a string for the "Record" field or a string for the "Legal Description" field?

ScreenShot192.jpg

ScreenShot191.jpg

ScreenShot190.jpg