nav[aria-label="Primary Navigation"] { padding: 0; & ul { list-style: none; width: 100%; display: flex; flex-direction: row; justify-content: start; align-items: start; gap: 30px; padding: 0; & li { margin: 0; } & ul li { list-style: none; } } }

Inline variable with "Table of Values" option and its handling

HussainMalik1

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

Table of Values Variable.JPG

Collect Values from Variable.JPG

Year and Archetype fields in dataset.JPG

Capture.JPG

export.zip

textures2.JPG

AlexaVlahakis_1-1713377776124.png

AlexaVlahakis_0-1713377775784.png

AdamArcichowski_1-1617652500201.png

Encontre mais publicações marcadas com

Analysis

Geodatabase

Desktop

Accepted answers

XanderBakker

Hi @HussainMalik1

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?

All comments

HussainMalik1

Thanks @XanderBakker

Objective of the project:

"Build scenarios for different years between 2020 to 2050 to reduce the energy consumption from the city buildings."

Purpose of the Project

I am calculating the change in electricity and natural gas demand for all the buildings present in the city from year 2020 to 2050, the energy consumption is further divided into smaller components based on the type of energy these components are using whether its Electricity or Natural gas such as domestics appliances (refrigerator, freezers and oven etc.), Electronic plugs loads , lighting and Electric domestics hot water equipment (NOTE: there are more components involved in this project). The cumulative energy consumption data is provided by utility companies for each archetype.

Now the idea is to break down the cumulative energy value (both electric and Natural gas) and assign some percentage of the value to these components mentioned above. I am using Inline variable method to create these variables where user can put the percentage value for each components.

Here is a link where you can access the excel model fields that would show the breakdown of energy into different household components, that might help you understand the complexity of the model. (Please let me know if it requires more explanation from my end).

https://mysait-my.sharepoint.com/:x:/g/personal/hussain_malik_sait_ca/EY481gBl235Bsa3-dZmBMGoBU5WO_kSh8r4PS-GZf3JVEA?e=Og7e7c

Little bit about dataset:

Dataset contains one feature class that has Electricity_ENERGY_USAGE and NATURAL_GAS_USAGE values in 2 separate fields along with the ARCHETYPE and Year fields. Year and Archetype field are the main fields that will be used to build scenarios e.g: user might want to do following : if in Year 2030 Electric consumption for Domestic Appliance is 30% of Electricity_ENERGY_USAGE but for 2040 its reduced down to 25% of Electricity_ENERGY_USAGE (in this scenario Electric % change in Domestic Appliance is one of the variables for my calculation. And ultimately the result from this calculation will be saved in the new field i.e: Change in Domestic Appliance Electricity Attribute table image is attached in this post for your review

About my Logic in ModelBuilder

Selecting Data by Year and Archetype:

I am trying to create inline variable with "Table of Values" that would give me an option to incorporate Year and Archetype along with other variables in it. Since Year and Archetype are fields already present in the dataset i am hoping to call these fields from the variable and select all the record that matched the criteria.

Assigning multiple values of variable to the selected data:

Assuming I am able to find a way to select records from variable Year and Archetype, then I would want to assign other variable values such as %change in Domestic Appliances to those selected record.

As far as Calculation goes i am only calculating %change for each component of energy consumption, which is not complicating.

Only issue I am facing is to assign multiple variable values to selected rows.

This is the overall summary of my project and what i am trying to accomplish here, I really appreciate @XanderBakker for your time and you agreeing to help me with it.

Please let me know if you require more explanation from me on any part of this message.

Thanks

Hussain

curtvprice

This table of values input capability looks pretty slick, I am not familiar with it.

It seems to me a workflow to extract all these variables into model builder variables would not be ideal, you would probably have better luck doing calculations with a table, using a Python function embedded inside a the Calculate Value tool. This python function would do your calculations using a an update cursor (arcpy.da.UpdateCursor). I think this approach would be much better for what you are trying to do.

A little bit of Python can make ModelBuilder go a lot farther. However, as you add complexity you may be forced to move toward Python scripting.

XanderBakker

Hi @HussainMalik1 ,

Can you elaborate a bit more on the calculations that you are planning to carry out? You mention 25-30 variables and are planning to create about 90 calculated fields. Are these fields all required or are these intermediate results. Maybe it is not necessary to have all these new fields in the end result.

As far as I understand you are planning to create a model. Model Builder is a very powerful tool to build a model and automatize processes. However, when things get very complicated it might be better to use scripts to solve the problem. This provided more flexibility to solve the problem but will require a certain level of Python knowledge.

I am happy to help you further, but my time might be limited. So, depending on the complexity of what you are trying to accomplish, I might be able to help. It will be important to share more details so the scope and complexity can be determined correctly.

HussainMalik1

Hi @XanderBakker

Can you suggest any workflow or any logic around this.

Thanks

Hussain

XanderBakker

Hi @HussainMalik1 ,

I think I would probably use a matrix (table) to store the values for each Archetype and Year combination. This makes it easier to edit in case values change over time.

Participe da Conversa

Membros conectados podem postar, seguir atualizações e mais. Novo aqui? Registre uma conta gratuita.

Links Rápidos

Community Help
Find useful guides, FAQs, and documents to help you navigate and make the most of Esri Community.