Inline variable with "Table of Values" option and its handling
Hello Community,
@DavidPike , @ShitijMehta , @XanderBakker , @Robert_LeClair , @Anonymous User , @JoshuaBixby , @RobertScheitlin__GISP , @JoeBorgione , @OwenGeo , @curtvprice
So here is a thing:
I am building a tool to calculate Green House Gas emission from the city buildings. And there are 25 to 30 Variables that are involved in the calculations and there are around 90 new calculated fields that will be added in the process to incorporate these variables.
There are 2 main fields present in the data set that will be involved in building scenarios !Year! and !ARCHETYPE! (See Image Year and Archetype fields in dataset.jpeg)
So, I have created a Variable in Model builder with “Table of Values “option, this option gives me a chance to build scenarios using multiple values for same variable, as shown in image (Table of Values Variable.jpeg). Year and Archetype are the fields as well as variable.
My first Question:
How do I select data which matches my %Year% and %Archetype% variable values in dataset, since !Year! and !ARCHETYPE! fields are present in the dataset.
My second Question:
How do I separate OR use all these variables with their names (See image Collect Values from Variable.jpeg) so that I could use them in calculations later? If I use Collect Values tool, I need to split these values. I am not so good with Python so If I need to call a function to split these values, I will need help with the function.
I can share data if requires.
I would really appreciate the help and thanks in advance.
Hussain
Accepted Solutions
I suggested creating a table with the 5200 records for all archetype, year and variable combinations. Like this:
(I used this snippet to create the content:)
import random
# 26(archetype)*4(years)*50(variables)
for x in range(26):
archetype = "archetype {}".format(x + 1)
for y in range(4):
year = 2020 + y * 10
for z in range(50):
variable = "variable {}".format(z + 1)
value = random.random() * 25
print("{}\t{}\t{}\t{}".format(archetype, year, variable, value))
I converted the data to an ArcGIS table in a FGDB and ow you can read the values either into a dictionary and query directly on archetype and year (if you are planning to use all the data) or simply load the variables related to a specified archetype and year using a query definition while reading the table.
See the code below (including both example):
import arcpy
def main():
# table location and field names
tbl = r'D:\GeoNet\EnergyCalc\ArchetypeYear\Default.gdb\ArchetypeYearData'
fld_arche = 'Archetype'
fld_year = 'Year'
fld_var = 'Variable'
fld_val = 'Value'
# input values to query
archetype = 'archetype 19'
year = 2030
# get values using a dictionary
dct = ReadDataIntoDict(tbl, fld_arche, fld_year, fld_var, fld_val)
dct_res1 = GetDictValuesForArchetypeAndYear(dct, archetype, year)
print(dct_res1)
print("")
# get values using the table and a query definition
dct_res2 = GetTblValuesForArchetypeAndYear(tbl, fld_arche, fld_year, fld_var, fld_val, archetype, year)
print(dct_res2)
def ReadDataIntoDict(tbl, fld_arche, fld_year, fld_var, fld_val):
dct = {}
with arcpy.da.SearchCursor(tbl, [fld_arche, fld_year, fld_var, fld_val]) as curs:
for row in curs:
archetype = row[0]
year = row[1]
variable = row[2]
value = row[3]
if archetype in dct:
dct_arche = dct[archetype]
if year in dct_arche:
dct_year = dct_arche[year]
dct_year[variable] = value
dct_arche[year] = dct_year
dct[archetype] = dct_arche
else:
dct_arche[year] = {variable: value}
dct[archetype] = dct_arche
else:
dct[archetype] = {year: {variable: value}}
return dct
def GetDictValuesForArchetypeAndYear(dct, archetype, year):
return dct[archetype][year]
def GetTblValuesForArchetypeAndYear(tbl, fld_arche, fld_year, fld_var, fld_val, archetype, year):
dct = {}
query = "{} = '{}' AND {} = {}".format(fld_arche, archetype, fld_year, year)
with arcpy.da.SearchCursor(tbl, [fld_var, fld_val], query) as curs:
for row in curs:
dct[row[0]] = row[1]
return dct
if __name__ == '__main__':
main()
In this example, specifying archetype "archetype 19" and year 2030 used the following query definition:
Archetype = 'archetype 19' AND Year = 2030
...and returned a dictionary with all the variables for this query (providing access to all the 50 variables):
{'variable 1': 15.5244401870236, 'variable 2': 23.3657321633143, 'variable 3': 15.1579971665677, 'variable 4': 22.4358991530334, 'variable 5': 4.29857939816623, 'variable 6': 9.85983758111721, 'variable 7': 18.5286923152794, 'variable 8': 5.79755699855013, 'variable 9': 22.7302585515292, 'variable 10': 20.7952926242024, 'variable 11': 3.15393340209763, 'variable 12': 6.55497889635969, 'variable 13': 23.534915278783, 'variable 14': 0.320347367664924, 'variable 15': 7.48007771767636, 'variable 16': 19.7211779286983, 'variable 17': 5.28625570863067, 'variable 18': 7.73552043487549, 'variable 19': 17.2153002750518, 'variable 20': 6.89264095850893, 'variable 21': 2.36278203597135, 'variable 22': 7.46633514134018, 'variable 23': 3.48576873823912, 'variable 24': 4.14460495556789, 'variable 25': 21.7298557989618, 'variable 26': 12.3477762544827, 'variable 27': 5.06567178346359, 'variable 28': 23.3938881028709, 'variable 29': 9.22447098083539, 'variable 30': 20.6246227233889, 'variable 31': 9.2655784448017, 'variable 32': 16.7829187579809, 'variable 33': 5.41408450739008, 'variable 34': 10.4562086747899, 'variable 35': 12.3017493699073, 'variable 36': 4.87453196521707, 'variable 37': 21.2273082111302, 'variable 38': 9.83037462145755, 'variable 39': 6.71378949875001, 'variable 40': 17.1075560942317, 'variable 41': 13.9113667208803, 'variable 42': 21.2160384165932, 'variable 43': 10.144770899068, 'variable 44': 7.1533553156325, 'variable 45': 5.7455521841622, 'variable 46': 9.92663148960041, 'variable 47': 3.13525525773836, 'variable 48': 12.5155910723043, 'variable 49': 20.245329425554, 'variable 50': 0.227518270529811}
This can then be used to do the calculations.
When I looked at your features, I noticed that you have 4 copies of the same geometry (one for each year( and you have repetitions of multiple archetypes for a large part of the data. I don't quite see how you are planning to connect this data to the features you have.
Can you explain more about this? What is a scenario supposed to return?
