Export metrics to a spreadsheet

Download the sample

This sample shows how to retrieve metrics from a plan and save them to a spreadsheet. Two ways of exporting the metrics are presented:

From all scenarios in the selected plan.
From selected scenarios in the selected plan.

The sample works with a provided urban model of the city of Hardeeville, created specifically for the sample code. The urbanModelId is "8d199b9a69664aac9b2c3a69573600a6".

Import relevant libraries and the Urban API schema stored as a Python module. See the Python sgqlc client library section for instructions on how to export the schema to a Python module.

Use dark colors for code blocksCopy
from sgqlc.operation import Operation
from sgqlc.endpoint.http import HTTPEndpoint
from urban_api_schema import urban_api_schema as schema

Provide the endpoint_url, urban_model_id and plan_id variables.

Use dark colors for code blocksCopy
endpoint_url = 'https://urban-api.arcgis.com/graphql'
endpoint = HTTPEndpoint(endpoint_url)
urban_model_id = "8d199b9a69664aac9b2c3a69573600a6"
plan_id = "cbb88034-39f6-4b5d-a5ac-a88852029f9d"

Get information for all scenarios in the selected plan

Create an Operation instance and request specific fields on selected objects. Specify which plan to retrieve the scenarios from by including its plan_id in the global_ids filter. Note, that global_ids expects a list which means you can request multiple plans in a single query. In the next step, select the scenarios (which are named branches in the codebase) and metrics from the plan. If you do not specify a filter then all branches and metrics will be returned. Select the appropriate return fields as following:

Select the branch_name and metric_values for branches. The metric_values variable contains the actual metric values for each scenario.
Select the global_id, unit_type and metric_name for metrics. These variables contain the id, unit type, and name of metrics used in this plan.

Use dark colors for code blocksCopy
# initialize the query
op = Operation(schema.Query)

# set the body of the query
plan = op.urban_model(urban_model_id=urban_model_id).urban_database.plans(
    filter={'global_ids':[plan_id]}
)

branches = plan.branches()
metrics = plan.metrics()

# select relevant return fields
branches.attributes.metric_values()
branches.attributes.branch_name()
metrics.attributes.global_id()
metrics.attributes.unit_type()
metrics.attributes.metric_name()

Call the endpoint to retrieve the metrics.

Use dark colors for code blocksCopy
returned_metrics = endpoint(op)
errors = returned_metrics.get('errors')
if errors:
    print(errors)

Implement helper functions that customize the format of the data returned by the API. Functions get_id_name_pairs and get_id_value_pairs write id-name and id-value pairs to dictionaries. They are used later to save the actual name of the metric instead of its global identifier in the .csv file. The function metrics_to_list saves all metrics from a single branch in a list. This format helps to simplify the process of saving the .csv file that you will implement in the next steps.

Use dark colors for code blocksCopy
def get_id_name_pairs(metrics):
    '''Save the global_id and metric_name (with unit_type) pairs in a key-value dict.'''
    id_name_pairs = {}

    for metric in metrics:
        name_with_unit = metric.attributes.metric_name + ' ['+ metric.attributes.unit_type + ']'
        id_name_pairs[metric.attributes.global_id] = name_with_unit

    return id_name_pairs


def get_id_value_pairs(branch):
    '''Save the global_id and metric value pairs from a single scenario to a key-value dict.'''

    id_value_pairs = {}

    all_metrics = branch.attributes.metric_values

    if all_metrics:
        for metric in all_metrics:
            id_value_pairs[metric.metric_id] = metric.value

    return id_value_pairs


def metrics_to_list(branch, id_name_pairs):
    '''Save the name and all values of the scenario metrics in a list.'''

    metrics = []
    metrics.append(branch.attributes.branch_name)
    m_pairs = get_id_value_pairs(branch)
    if m_pairs:
        for m_id in id_name_pairs.keys():
            if m_id in m_pairs:
                metrics.append(m_pairs[m_id])
            else:
                metrics.append('null')
    return metrics

To parse the returned JSON data into more convenient native objects, add the query operation to the results variable. Loop through all branches of the selected plan and transform them with the helper function. Add a transformed list of the attributes of each scenario to a metrics list.

Use dark colors for code blocksCopy
obj = op + returned_metrics

branches = obj.urban_model.urban_database.plans[0].branches
metrics = obj.urban_model.urban_database.plans[0].metrics
id_name_pairs = get_id_name_pairs(metrics)

# data to be written row-wise in csv file
metrics = []

# adapt the format
for branch in branches:
    metrics.append(metrics_to_list(branch, id_name_pairs))

Save the information to the .csv file.

Use dark colors for code blocksCopy
import csv

header = ['branch_name'] + list(id_name_pairs.values())

# opening the csv file in 'a+' mode
file = open('metrics.csv', 'a+', newline ='')

# writing the data into the file
with file:
    write = csv.writer(file)
    write.writerow(header)
    write.writerows(metrics)

Get information for selected scenarios in the selected plan

If you want to export metrics for selected scenarios only, provide a filter for the branches object in your query. To do this, modify the previous code snippets by adding a global_id filter and a list of branch_ids to the branches node of the query. The rest of the steps remain the same as when working with all scenarios.

Use dark colors for code blocksCopy
branch_id_1 = "b98a136b-0600-48f2-ac1d-9255f66bf460" # scenario named 'Phase 1'
branch_id_2 = "0e5b4fcd-f5b4-4ab6-a5fb-a1fb12aee9a7" # scenario named 'Phase 2'

branches = plan.branches(filter={'global_ids': [branch_id_4, branch_id_5]})