I need to write a python script to automatically calculate costs into a given column by combining several tables (if possible using arcpy in ArcMap 10.7) but I’m struggling to do that since I’ve never actually done something like that. Hence here I am, hoping that some kind soul in this community might be able to help me. My tables have several columns, but I just listed the onces relevant for my calculations, for simplicity the tablenames 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 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: Table after calculation step 2 Table after summarizing step 3
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..
I’d be so very grateful for some hints, solutions or ideas!!
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!", "")
#Summarize costs_interim according to their number
# 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 generate 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.
CodePudding user response：
For step 2: I propose that you Join ob Table 1 and Table 2 on the "Code" column. This should give you a Table containing the columns from both Tables. Next, create a new field in the resulting join table that calculates the value from column "a_field" multiplied by value from column "area"
Here are links to documentations for join and creating fields
Join: https://pro.arcgis.com/de/pro-app/2.8/tool-reference/data-management/join-field.htm
Create field: https://pro.arcgis.com/de/pro-app/2.8/tool-reference/data-management/add-field.htm
For step 3 Here is a documentation to summarize fields
https://pro.arcgis.com/de/pro-app/2.8/tool-reference/analysis/summary-statistics.htm
CodePudding user response：
Update: Instead of arcpy.da.SearchCursor() I used arcpy.TableSelect afterwards as mentioned already in the questions just adding fields (arcpy.AddField) and joining (arcpy.JoinField), as well as the summary statistics (finally seems to work as well). Once I test it and confirm it's running, I'll post the final code.
Still open for any inputs or ideas though