用Python自动计算几个表的值，包括过滤

Question

我需要编写一个Python脚本来通过组合几个表（如果可能的话，在Arcmap 10.7中使用Arcpy）自动计算成本，但是我很难做到这一点，因为我从未真正做过这样的事情。 因此，我在这里，希望这个社区中的某种善良的灵魂能够帮助我。 我的表有几列，但我刚刚列出了与我的计算相关的onces，为简单起见，将表格为table1，table2，table3。不幸的是，我没有任何可以在此处发布的虚拟数据……

我的表格：

• table1，列：区域（int），number（string），code（int），e节（字符串）
• table2，列：代码（int） ，a_value（double）
• 表3，列：number（string），b _value（double），cost_calc（double）

table1和table2是通过相应列中的ID连接的«代码» ; Table1和Table3是通过相应列中的数字«数字»

存储在3个不同表中的值中的值自动计算成本...

目标：脚本应自动从 结果看起来像）：

步骤1：在表1中过滤，从列«章节»所有行都带有 值«b»（仅需要具有此值的那些行才能进一步 计算，不应包括其他行）

步骤2：在«a_value»（table2）中乘以所有值 «区域»（table1）（对于第1步中过滤的所有行 - 当然，步骤1和步骤2如果更容易（不确定）） - 可以将interim结果写入临时表或将Table1中的新列 - 任何更容易的…

步骤3：总结步骤2中根据其数字计算的结果（Table1）（在这里，我希望所有计算的结果根据其数字组合/汇总 - 计算后，有5个数字的结果。 12345（因为它们的«代码»不同），并且应该汇总到临时结果中，可以写入table3

中的临时表或新列。

示例 中平均值“根据数字汇总计算的临时结果（COST_INTERIM）”，这就是应该发生的事情： 计算后表2 table> table总结步骤3

步骤4：在步骤3中计算的结果（汇总值）应乘以“ b_value”列中的值。此步骤的结果也将是最终结果，因此写入列«cost_calc»（table3）

我知道我可以使用searchcursor（arcpy）函数执行第一步，但除此之外，我迷路了。 。 因此，除了导入Arcpy，数学等，并设置环境，并首先尝试使用搜索手机功能

。

我的第一次尝试：

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")))

我还将上述步骤添加到遵循的代码中：

# 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", "")

这里是我生成新列（可以的），但是每次加入或计算一个新表都会生成新表。我希望临时生成表，因此最终结果不在新表中，而是在介绍中所述写入表3。

仍然缺少的是如何根据其在字段/列中的“ conse cost_interim”汇总“ cost_interim”。

Answer 1

对于第2步：
我建议您在“代码”列上加入OB表1和表2。这应该为您提供一张包含两个表的列的表。
接下来，在生成的加入表中创建一个新字段，该字段从列“ a_field”列乘以“列”区域“ a_field”的值，

这是指向加入和创建字段的文档的链接

：

创建字段： https://pro.arcgis.com/de/pro-app/2.8/tool-reference/data-management/add-field.htm

步骤3
这是一份文档，可以汇总字段

https ：//pro.arcgis.com/de/pro-app/2.8/tool-reference/analisy/summary-statistics.htm

Answer 2

更新：
我使用arcpy.tableselect而不是arcpy.da.da.searchcursor（）
之后，如“添加”字段（Arcpy.addfield）和加入（Arcpy.Joinfield）的问题中已经提到的，以及摘要统计数据（最终似乎也有效）。
测试并确认它正在运行后，我将发布最终代码。

仍然对任何意见或想法开放

Answer 3

因此，最终且大部分清理的代码看起来像这样：

# Import system modules
import arcpy, arcgisscripting
from arcpy import env
from datetime import datetime
import os, os.path, sys, time, datetime, traceback, string, math, getpass
from re import sub
import pandas as pd
import numpy as np
import locale;
os.environ["PYTHONIOENCODING"] = "utf-8";
import re
reload(sys)
sys.setdefaultencoding('utf8')

arcpy.env.overwriteOutput = True

###############################################################################################################
# Variables
# Workspace - db
print "******************************************************************************************"
print "Workspace db"
print "******************************************************************************************"
workspace = "C:\datasources\MyProject\db" + '\\'
print 'Workspace:',                 workspace
db = workspace + "mydatabase.gdb"
backupdb = db.replace(".gdb", "_copy.gdb")
print 'db:       ',                 db
tempdb = db + '\\' + "tempDB.gdb"
tempgebuehren = workspace + "mydatabase_temp.gdb"
###############################################################################################################

# Create a copy of the db
arcpy.Copy_management(db, backupdb)

#User and timestamp
currentUser = getpass.getuser().upper()
arcpy.AddMessage("User: " + currentUser)
#currentTimestamp = str(datetime.datetime.now())
#arcpy.AddMessage("Timestamp: " + currentTimestamp)
expTimestamp = '''def add_date():
  import datetime
  return datetime.datetime.now()'''
# exp = '''def add_date():
#  import time
#  return time.strftime("%Y/%m/%d")'''

#set workspace environment
try:
   arcpy.env.workspace = db
   arcpy.AddMessage('Database read successfully ...')
except:
   arcpy.AddMessage('Database not found. Please try again')

#create temporary table for all calculations processes
try:
   arcpy.CreateFileGDB_management(workspace, "mydatabase_temp.gdb")
   arcpy.AddMessage('Database created successfully ...')
except:
   arcpy.AddMessage('Database could not be created. Please try again')

# set script parameter
TABLE1 = arcpy.GetParameterAsText(0)
if TABLE1 == '#' or not TABLE1:
    TABLE1 = db + '\\' + "TABLE1" # provide a default value if unspecified


table2 = arcpy.GetParameterAsText(1)
if table2 == '#' or not table2:
    table2 = db + '\\' + "AV" + '\\' + "table2" # provide a default value if unspecified


TABLE3 = arcpy.GetParameterAsText(2)
if TABLE3 == '#' or not TABLE3:
    TABLE3 = db + '\\' + "TABLE3" # provide a default value if unspecified


# Check if the Column u_value_type contains any NULL values and replace them with 0
with arcpy.da.UpdateCursor(table2, ["u_value_type"]) as cursor:
    for row in cursor:
        if row[0] == None:
            row[0] = 0
            cursor.updateRow(row)

arcpy.AddMessage('The column u_value_type contained NULL values and they were successfully replaced with 0')

del row

# Create a copy of the db
arcpy.Copy_management(db, backupdb)


# local variables (generated temporarily)
table2__2_ = table2

TABLE1_temp = workspace + '\\' + "mydatabase_temp.gdb" + '\\' + "TABLE1_temp"
TABLE1_temp__2_ = TABLE1_temp
TABLE1_temp__3_ = TABLE1_temp__2_
TABLE1_temp__4_ = TABLE1_temp__3_

table2__3_ = table2__2_
table2__4_ = table2__3_

TABLE3 = db + '\\' + "\\TABLE3"

TABLE1_SUM = workspace + '\\' + "mydatabase_temp.gdb" + '\\' + "TABLE1_SUM"


try:
# Calculation process
    arcpy.AddMessage('Starting calculation process ...')
# for column "sektion" select all rows with value "B" in table "TABLE1"
    arcpy.TableSelect_analysis(TABLE1, TABLE1_temp, "SEKTION = 'B'")
    arcpy.AddMessage('Filter table: ok')
# add a temporary field for later calculation results to table TABLE1
    arcpy.AddField_management(TABLE1_temp, "u_area_fees", "DOUBLE", "", "", "", "", "NULLABLE", "NON_REQUIRED", "")
# join fields from table TABLE1 with table TABLE3 on art=code for later calculation over both tables
    arcpy.JoinField_management(TABLE1_temp__2_, "ART", TABLE3, "CODE", "CODE;u_cvalue")
    arcpy.AddMessage('join tables in temporary gdb: ok')
# calculate field from values of joined tables into newly added field
    arcpy.CalculateField_management(TABLE1_temp__3_, "u_area_fees", "!FLAECHE! * !u_cvalue!", "PYTHON", "")
    arcpy.AddMessage('temporary calculations: ok')
# summary statistics to merge all results according to their number
    arcpy.Statistics_analysis(TABLE1_temp__4_, TABLE1_SUM, "u_area_fees SUM", "NUMMER")
    arcpy.AddMessage('summarize fields: ok')
# join fields from tables fpr TABLE1 on table2 on number=number
    arcpy.JoinField_management(table2__2_, "NUMMER", TABLE1_SUM, "NUMMER", "NUMMER;SUM_u_area_fees")
    arcpy.AddMessage('join tables: ok')
# add field for results
    arcpy.AddField_management(table2__3_, "u_area_fees_noncalculated", "DOUBLE", "", "", "", "", "NULLABLE", "", "")
    arcpy.AddMessage('field added')
# calculate field
    arcpy.CalculateField_management(table2__3_, "u_area_fees_noncalculated", "(!u_value_type! / 100) * !SUM_u_area_fees!", "PYTHON", "")
    arcpy.AddMessage('interim results calculated')
# calculate endresult rounded
    arcpy.CalculateField_management(table2__4_, "u_area_fees_calculated", "math.ceil(!u_area_fees_noncalculated!)", "PYTHON", "")
    arcpy.AddMessage('...calculation process finished without errors')
except Exception,e:
    arcpy.AddMessage('Something went wrong. Please try again')
    arcpy.AddMessage("Fehler: " + str(e))


#delete temporary fields in table table2
try:
  arcpy.DeleteField_management(table2, ["NUMMER_1", "SUM_u_area_fees"])
  arcpy.AddMessage('Temporary fields successfully deleted...')
except:
   arcpy.AddMessage('Something went wrong, fields could not be deleted. Please try again')


#delete temporary FileGBD
try:
   arcpy.Delete_management(tempmydatabase)
   arcpy.AddMessage('Temporary database successfully deleted...')
except:
   arcpy.AddMessage('Ups, Something went wrong, could not delete Database. Please try again')