AnsweredAssumed Answered

Advice on "roll up" / consolidation tool

Question asked by fallingdog on May 24, 2017
Latest reply on Jun 5, 2017 by abekohen

I need to make a script tool that will consolidate attributes from many rows into to one across a number of columns. The tool will need to be able to let the user select the field "key" field as well as the fields that will be consolidated. I made a quick mockup table as my input table (it has been processed with spatial sort so that parts of the key features that are spatially adjacent to each other are also in adjacent rows in the table).

 

Mockup table. It has land ownership and PLSS (Public Land Survey System) MTRS (meridian township range section) information.

Key_FeatureOwnerMTRSECTIONMTRSMER
Facility 1PrivateU009N008E33U009N008E33U
Facility 1FederalU009N008E33U009N008E33U
Facility 1PrivateU009N008E34U009N008E34U
Facility 1FederalU009N008E34U009N008E34U
Facility 1PrivateU008N008E4U008N008E04U
Facility 2PrivateU008N008E3U008N008E03U

 

 

The output should look like this.

Key_FeatureMTRSOwner
Facility 1U, T09N, R08E: 33, 34
U, T08N, R08E: 4
Federal, Private
Facility 2U, T08N, R08E: 3Private

 

The question: I would like input on the best method to get the result. And if there are any out of the box Arc tools that I could leverage...

 

My first pass at this was to use arcpy.da.SearchCursor and iterate over the input table and check to see if the current row's key feature is the same as the previous row's key feature. If so, then add it to a string variable. When the key features do not match write the string variable and other fields to the output file. The MTRS roll up is a little more complex because sections needs to rolled up for each unique MTR associated with the key feature. It feels a little wired because you are always looking back in rows as you iterate over the table. So, I used a number of temp_last variables for key features, MTRS, etc. I was thinking that it would be better to read the whole table into a list of lists and then use that to generate the output. That way I could use indexing to look at the next and last rows without having to use the temp_last variables.

 

The below code I made for a similar one off.

 

import arcpy
import os

arcpy.env.overwriteOutput = True

in_path = r"xxxx"
out_path ="xxxx"


def tsv_write(tsv_row):
    for item in tsv_row:
        tsv.write(item + "\t")
    tsv.write("\n")

def rollup(rollup_list):
    rollup_list = set(rollup_list)
    rollup_list = list(rollup_list)
    rollup_list.sort()
    rollup_str = ""
    for sec in rollup_list:
        rollup_str = rollup_str + str(sec) + ", "
    rollup_str = rollup_str[:-2]
    return rollup_str

def from_to_fix(x):
    x = round(x, 1)
    x = str(x)
    return x


search_cursor = arcpy.da.SearchCursor(in_path, ["Main_LandOwnership_MTRS_Mbook_Meridian",
                                                "Main_LandOwnership_MTRS_Mbook_Town_Range",
                                                "Main_LandOwnership_MTRS_Mbook_SECTION",
                                                "Main_LandOwnership_MTRS_Mbook_F_MP",
                                                "Main_LandOwnership_MTRS_Mbook_T_MP",
                                                "Main_LandOwnership_MTRS_Mbook_Index_Order",
                                                "Main_LandOwnership_MTRS_Mbook_ROW_Designation"])

tsv = open(out_path, "w")

meridian_last = None
township_last = None
f_mile_last = None
t_mile_last = None
owner_last = None

township_list = []
township_section_list = []

first_row_flag = True
township_append_flag = True
section_list = []
index_order_list = []

for row in search_cursor:
    meridian = row[0]
    township = row[1]
    section = row[2]
    f_mile = row[3]
    t_mile = row[4]
    index_order = row[5]
    owner = row[6]

    if first_row_flag:
        township_last = township
        f_mile_last = f_mile
        t_mile_last = t_mile
        owner_last = owner
        first_row_flag = False

    if township == township_last:
        township_append_flag = True
        section_list.append(int(section))
    else:
        township_append_flag = False
        section_rollup = rollup(section_list)
        township_section_list.append(township_last + ": " + section_rollup)
        section_list = []
        section_list.append(int(section))

    if f_mile == f_mile_last:
        index_order_list.append(int(index_order))
    else:
        index_order_rollup = rollup(index_order_list)
        write_flag = True
        for trs in township_section_list:
            if write_flag:
                write_flag = False
                print [from_to_fix(f_mile_last), from_to_fix(t_mile_last), trs, owner_last, index_order_rollup]
                tsv_write([from_to_fix(f_mile_last), from_to_fix(t_mile_last), trs, owner_last, index_order_rollup])
            else:
                print ["", "", trs, "", ""]
                tsv_write(["", "", trs, "", ""])
        township_section_list = []
        index_order_list = []
        index_order_list.append(index_order)


    if meridian != meridian_last:
        print meridian
        tsv_write([meridian])
        print ["From MP", "To MP", "MTRS", "Ownership", "Map Book Sheet"]
        tsv_write(["From MP", "To MP", "MTRS", "Ownership", "Map Book Sheet"])

    meridian_last = meridian
    township_last = township
    f_mile_last = f_mile
    t_mile_last = t_mile
    owner_last = owner

if not township_append_flag:
    township_section_list.append(township_last + ": " + str(int(section)))

index_order_rollup = rollup(index_order_list)
write_flag = True
for trs in township_section_list:
    if write_flag:
        write_flag = False
        print [from_to_fix(f_mile_last), from_to_fix(t_mile_last), trs, owner_last, index_order_rollup]
        tsv_write([from_to_fix(f_mile_last), from_to_fix(t_mile_last), trs, owner, index_order_rollup])
    else:
        print ["", "", trs, "", ""]
        tsv_write(["", "", trs, "", ""])
township_section_list = []
index_order_list = []
index_order_list.append(index_order)

tsv.close()

 

Thanks for any input!

 

Forest

Outcomes