Add values from two similar rows

[SalmanAhmed]

Hello,

I want to add the values in the RESULT field from two rows which are similar but the numbers are inverted. For example I want to add the values from the row with SUB_REGION field having 1 and SUB_REGI_1 field having 5 to the row with SUB_REIGON field having 5 and SUB_REGI_1 field having 1. These are actually calculations from commuter data between sub-regions. So I want to have the RESULT value to contain the sum of values from people commuting from 1 to 5 and from 5 to 1. Any way I can do this with a script? It seems impossible to me at the moment.

Thanks alot!

[TedKowal]

I can possibly give you an approach that may help you get started....

I occasionally have to put Origin/Destination data together for various transportation planners.  The approach I take:

I export the data to a database and write an SQL query to do the sums for me.  Note that the query syntax will be dependent upon the database you use... ie in SQL Server I believe the syntax for min will be least and max will be greatest.....

hope this sparks some ideas that will help.

SELECT min(Sub_Region, Sub_Regi_1) as SubRegion1,
       max(Sub_Region, Sub_Regi_1) as SubRegion2, sum(dataCountfield) as cnt
FROM YourDataSet 
GROUP BY min(Sub_Region, Sub_Regi_1), max(Sub_Region, Sub_Regi_1)

[SalmanAhmed]

This seems helpful thanks.

[WesMiller]

You could use the field calculator not sure i understand what you want the end result to be.

If you want them to be 1 - 5 you would need to convert the numbers to stings so using the python parser in the field calculator you would

str(!SUB_REGI_1!)+"-"str(SUB_REIGON )

[SalmanAhmed]

Oh no I think you did not understand the question completely. I don't want to modify the case ID field. I wanted to sum up the values in the result field for the rows with case IDs 1 5 and 5 1 and write that sum to the result field in both rows.

[WesMiller]

Then using your field calculator, change your parser to python on the left hand side double click the first field then click the plus operator then double click the second field you want to add. 5+1 = 6 Is that what your asking?

[SalmanAhmed]

No. There is a field called 'Result' right? I want to add the values in this field for row having case ID 1 5 and the row having case ID 5 1 and write this new value in the same Result field for both rows.

[SalmanAhmed]

So I want to add 0.146561 with 2.072405 and write the summed up number in place of these two numbers in the Result field

[WesMiller]

I think i see now. I haven't tested the code below but my thought is  create a dictionary and a list. Check the list to see if the value is in there or not and if it is add to the dictionary if not add it to the list and the dictionary. You could then either create a group field to dissolve them by or get the values from the results field add them up and put the new value in place.

import arcpy


fc = yourFeatureclassortablehere
my_dict = {}
my_list = []
desc  =arcpy.Describe(fc)
oidfld = desc.OIDFieldName
rows = arcpy.da.SearchCursor(fc,[oidfld,"PRIM_ID","SEC_ID"])


for row in rows:
    if str(row[1])+':'+str(row[2]) in my_list:
        my_dict[str(row[1])+':'+str(row[2])].append(row[0])
    elif str(row[2])+':'+str(row[1]) in my_list:
        my_dict[str(row[2])+':'+str(row[1])].append(row[0])
    else:
        my_list.append(str(row[1])+':'+str(row[2]))
        my_dict[str(row[1])+':'+str(row[2])] = [row[0]]
print my_dict
del row,rows