I need to write a Python script to automatically calculate costs into a given column by combining several tables (if possible using ArcPy for ArcMap 10.7) but I’m struggling to do that since I’ve never actually done something like that.
My tables have several columns, but I just listed the ones relevant for my calculations, for simplicity the table names will just be table1, table2, table3. Unfortunately, I don’t have any dummy data that I could post here.
My tables:
- Table1, Columns: area(int), number(string), code(int), section(string)
- Table2, Columns : code(int), a_value(double)
- Table3, Columns : number(string), b _value(double),costs_calc(double)
Table1 and Table2 are connected via the ID in the respective column «code» ; Table1 and Table3 are connected via the number in the respective column «number»
Goal : script should automatically calculate costs in column «costs_calc» from values stored in 3 different tables.
Process I want to script (or how I figured the steps to get a result look like):
Step 1: Filter in Table1, from column « section » all rows with the value «B» (only those rows with this value are needed for further calculation, the other rows should not be included)
Step 2: Multiply Values in «a_value» (Table2) with all values from «area» (Table1) (for all rows filtered in Step 1 – of course Step 1 and Step 2 can be swaped if it’s easier (not sure)) --Interim results could be written into a temporary table or into a new column (costs_interim) in table1 – whatever is easier…
Step 3: Summarize the result calculated in Step 2 according to their number (Table1) (here I want all the calculated results combined/summarized according to their number -- after calculation there are for example 5 results for number 12345 (because their «code» is different) and these should be summarized into) interim results could be written into a temporary table or a new column in table3
Example for what I mean with "summarize calculated interim results (costs_interim) according to number", that's what should happen:
Step 4: Results calculated in Step 3 (summarized values) should be multiplied with the values in column « b_value ». The results of this step will also be the final results and are thus written into the column «costs_calc» (Table3)
I know I can execute the first step by using the SearchCursor(arcpy)function but beyond that, I’m lost.
Hence I also have no code up to now except for import arcpy, math, etc. and setting the environment and a first try with the SearchCursor-function..
my first try:
fc="table1"
fields = ['section','area']
delimfield = arcpy.AddFieldDelimiters(fc, fields[1])
cursor = arcpy.da.SearchCursor(fc, fields, delimfield +"= 'B'")
#Open a searchcursor
rows = arcpy.SearchCursor("c:/Users/myuser/mydatabase/table1",
fields="section")
#Iterate through the rows in the cursor and print out the section value.
for row in rows:
print("section: {0}".format(
row.getValue("B")))
I've also added the mentioned steps as followed into the code:
# Local variables:
table1 = "C:\\Users\\myuser\\Documents\\mydatabase.gdb\\table1"
table2 = "C:\\Users\\myuser\\Documents\\mydatabase.gdb\\table2"
table3 = "C:\\Users\\myuser\\Documents\\mydatabase.gdb\\table3"
table1_a = table1
table1_b = table1_a
table1_c = table1_b
table1_d = table1_c
table1_e = table1_d
table3_a = table3
table1_end = table1_e
# Process: Add field in table1 for interim results
arcpy.AddField_management(talbe1, "costs_interim", "DOUBLE", "", "", "", "", "NULLABLE", "NON_REQUIRED", "")
# Process: Join table1 and table2 via shared fields "code"
arcpy.JoinField_management(table_b, "code", table2, "code", "")
# Process: calculate field costs_interim
arcpy.CalculateField_management(table1_c, "costs_interim", "!area!* !a_value!", "")
# Process: add field in table3
arcpy.AddField_management(table3, "costs_calc_interim", "DOUBLE", "", "", "", "", "NULLABLE", "NON_REQUIRED", "")
# Process: join table1 and table3 via shared field "number"
arcpy.JoinField_management(table1_d, "number", table3_a, "number", "")
# Process: calculate endresult into field "cost_calc"
arcpy.CalculateField_management(table1_e, "costs_calc", "!costs_calc_interim! * !b_value! / 100", "")
The things here is, that I add new columns (which is ok) but with every join or calculation a new table gets generated. I'd prefer the tables to be generated temporarily, so that the final result is not in a new table but written into table3 as described in the intro.
What is still missing, is how to summarize the calculated field "costs__interim" according to their value in the field/column "number" before joining the tables in the step afterwards.
And how to integrate the filter with the first calculation (aso to only calculate the filtered options and ommit the others)
arcpy.dacursors instead of the old style.