Concatenate Row Values

2370
4
02-29-2016 04:19 PM
ToddHenry1
Occasional Contributor

I'm currently using this great tool to concatenate row values into a single record. Right now it sorts by the field that is concatenated but I would like to sort by a third field.  Does anyone have any suggestions on how to change that?

http://www.arcgis.com/home/item.html?id=52dfcef46fdb4c76bfbc08dc01570f3c

Tags (2)
0 Kudos
4 Replies
DanPatterson_Retired
MVP Esteemed Contributor

More to the question, does the field that is concatenated the primary or secondary sort field?

What type of data are you sorting on?

Do you want to sort by two or more fields

      (ie About sorting records in tables—Help | ArcGIS for Desktop )

Do you have a few sample rows if these questions don't twig an answer

ToddHenry1
Occasional Contributor

Dan,

1.   The concatenated field does not have to be sorted but would be the secondary sort, if I used it.

2.  I'm sorting numeric data (distances)

Here is a quick illustrative sample

ID       Competitor   Distance       Current Concatenated Output      Desired Output

1         A  (5.00)              5                   A (5.00), B(2.00)                    B(2.00), A (5.00)

1         B (2.00)               2                  A (5.00), B (2.00)                    B (2.00), A (5.00)

The current tool sorts by the Competitor Field which is the field to be concatenated.

I then use subsequent tools in my model to get just one records for ID 1.

0 Kudos
DanPatterson_Retired
MVP Esteemed Contributor

I would have done the concatenation differently.  Assuming that there are fewer than 10 competitors to be ranked

rank, (compet, rank)

a = '5, ("A",5)'

b = '2, ("B",2)'

c = [a,b]

c.sort()

c

['2, ("B",2)', '5, ("A",5)']

Then if you don't want the preceding rank, you can parse it off after

0 Kudos
ToddHenry1
Occasional Contributor

Dan,

Sometimes I would have more than 10 items to concatenate.  Also, I'm not sure where I would insert that into the code.  I took a stab at creating a new variable called sort_field which I can get to populate in the tool, but I'm getting a data type error when running it because it is looking for a string and found a float field.   Here is the part of the code where I think I want to use that sort_field.

# Group a sequence of case field ID's and value pairs into a dictionary of lists.
    dictionary = collections.defaultdict(list)
    try:
        srows = None
        srows = arcpy.SearchCursor(input_table, '', '', '', '{0} A;{1} A'.format(case_field, from_field))
        for row in srows:
            case_id = row.getValue(case_field)
            value = row.getValue(sort_field)
            if from_field in ['Double', 'Float']:
                value = locale.format('%s', (row.getValue(from_field)))
            if value <> None:
                dictionary[case_id].append(value)
    except RuntimeError as re:
        arcpy.AddError('Error in accessing {0}. {1}'.format(input_table, re.args[0]))
    finally:
        if srows:
            del srows
    try:
        urows = None
        urows = arcpy.UpdateCursor(input_table)
        for row in urows:
            case_id = row.getValue(case_field)
            values = dictionary[case_id]
            f = u''.join(unicode(val) for val in values)

            if not delimiter == '':
                if (len(f) + (len(values)-1)) > to_field_length:
                    arcpy.AddError('Length of the Copy to Field is less than the length of the content you are trying to copy.')
                else:
                    if from_field_type in ['String']:
                        if to_field_type in ['String']:
                            row.setValue(to_field, delimiter.join(sorted(set([val for val in values if not value is None]))))
                    else:
                        row.setValue(to_field, delimiter.join(sorted(set([str(val) for val in values if not value is None]))))
            else:
                if to_field_type in ['String']:
                    if len(f) > to_field_length:
                        arcpy.AddError('Length of the Copy to Field is less than the length of the content you are trying to

copy.')
                else:
                    if from_field_type in ['String']:
                        if to_field_type in ['String']:
                            row.setValue(to_field, delimiter.join(sorted(set([val for val in values if not value is None]))))
                    else:
                        if to_field_type in ['String']:
                            row.setValue(to_field, delimiter.join(sorted(set([str(val) for val in values if not value is

None]))))
                        elif to_field_type in ['Integer', 'SmallInteger'] :
                            row.setValue(to_field, int(delimiter.join(sorted(set([str(val) for val in values if not val is

None])))))
                        elif to_field_type in ['Double', 'Float']:
                            row.setValue(to_field, float(delimiter.join(sorted(set([str(val) for val in values if not val is

None])))))

            # Date formatting can be edited to match local.
            if from_field_type in ['Date']:
                row.setValue(sort(to_field, delimiter.join([val.strftime('%d%m%Y') for val in values if not val is None])))
            urows.updateRow(row)

0 Kudos