topic Re: convert table that has many records per parcel to a table that has a single recor in Geoprocessing Questions

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

MarkVolz — Thu, 07 Jun 2012 18:19:04 GMT

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?>

Re: convert table that has many records per parcel to a table that has a single recor

MathieuCain — Thu, 07 Jun 2012 19:04:38 GMT

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?>

How about creating temporary feature class or table outputs based on record (e.g., one output where record = 1, 2nd output where record = 2, etc.), then link the outputs back together based on the Parcel value (e.g., join and output new feature class).
If your

Re: convert table that has many records per parcel to a table that has a single recor

TimHopper — Thu, 07 Jun 2012 19:07:59 GMT

Hi Mark.

You'll want to use the Pivot Table GP tool (ArcInfo only) to achieve this result.

1. Select your input table
2. Choose the input fields (Parcel ID)
3. Choose the pivot field (Legal Description count - 1, 2, 3, etc)
4. Choose the value field (field with actual description)

Re: convert table that has many records per parcel to a table that has a single recor

MarkVolz — Fri, 08 Jun 2012 12:17:21 GMT

@ m.cain

I thought about creating a temporary table, then joining them back together. Unfortunately I will be dealing with up to 50 or more fields. That could be a lot of work, that is unless I find a way to automatically iterate through the results.

@ rthopper

I do have ArcInfo, and after sending the original message out I found the pivot table function. Despite double checking my inputs the function failed. I thought it might have been a bug in 10.1RC, until I came across a forum for Micorosoft Excel, which limits the values for pivot tables to numbers. In this case I need a string. Do you know if Pivot Tables in ArcGIS are limited to numbers?

Also,

I found a note stating that it might be possible to use strings with Pivot Tables in Access... I haven't checked it out yet, but I would still prefer to do all my work via model builder. http://help.lockergnome.com/office/Displaying-Text-numbers-Pivot-Table--ftopict942562.html

Re: convert table that has many records per parcel to a table that has a single recor

TimHopper — Fri, 08 Jun 2012 15:02:29 GMT

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?

Re: convert table that has many records per parcel to a table that has a single recor

MarkVolz — Fri, 08 Jun 2012 17:36:16 GMT

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

Re: convert table that has many records per parcel to a table that has a single recor

markdenil — Sun, 12 Dec 2021 02:17:36 GMT

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