<jupyter_start><jupyter_text>GMNS to AequilibraE example Inputs1. Nodes as a .csv flat file in GMNS format2. Links as a .csv flat file in GMNS format3. Trips as a .csv flat file, with the following columns:  orig_node, dest_node, trips4. Sqlite database used by AequilibraE Steps1.  Read the GMNS nodes    -  Place in SQLite database, then translate to AequilibraE nodes    -  Generate the dictionary of zones for the omx trip table (uses node_type = centroid)2. Read the GMNS links    -  Place in SQLite database, then translate to AequilibraE links3. Read the trips    -  Translate into .omx fileA separate Jupyter notebook, Route, performs the following steps4. Run AequilibraE shortest path and routing5. Generate detail and summary outputs<jupyter_code>#!/usr/bin/env python
# coding: utf-8
import os
import numpy as np
import pandas as pd
import sqlite3
#import shutil  # needed?
import openmatrix as omx
import math
#run_folder = 'C:/Users/Scott.Smith/GMNS/Lima'
run_folder = 'C:/Users/Scott/Documents/Work/AE/Lima'   #Change to match your local environment
#highest_centroid_node_number = 500   #we are now finding this from the nodes dataframe<jupyter_output><empty_output><jupyter_text>Read the nodes, and set up the dictionary of centroidsThe dictionary of centroids is used later in setting up the omx trip table<jupyter_code>#Read the nodes
node_csvfile = os.path.join(run_folder, 'GMNS_node.csv')
df_node = pd.read_csv(node_csvfile)   #data already has headers
print(df_node.head())   #debugging
df_size = df_node.shape[0]
print(df_size)
# Set up the dictionary of centroids
# Assumption: the node_type = 'centroid' for centroid nodes
# The centroid nodes are the lowest numbered nodes, at the beginning of the list of nodes,
# but node numbers need not be consecutive
tazdictrow = {}
for index in df_node.index:
    if df_node['node_type'][index]=='centroid':
        #DEBUG print(index, df_node['node_id'][index], df_node['node_type'][index])
        tazdictrow[df_node['node_id'][index]]=index
#tazdictrow = {1:0,2:1,3:2,4:3,...,492:447,493:448}
taz_list = list(tazdictrow.keys())
matrix_size = len(tazdictrow)   #Matches the number of nodes flagged as centroids
print(matrix_size)   #DEBUG
highest_centroid_node_number = max(tazdictrow, key=tazdictrow.get)  #for future use
print(highest_centroid_node_number)  #DEBUG<jupyter_output>449
493<jupyter_text>Read the links<jupyter_code># Read the links
link_csvfile = os.path.join(run_folder, 'GMNS_link.csv')
df_link = pd.read_csv(link_csvfile)   #data already has headers
#print(df_node.head())   #debugging
#df_size = df_link.shape[0]
print(df_link.shape[0])  #debug<jupyter_output>6095<jupyter_text>Put nodes and links into SQLite.  Then translate to AequilibraE 0.6.5 format1. Nodes are pushed into a table named GMNS_node2. node table used by AequilibraE is truncated, then filled with values from GMNS_node3. Centroid nodes are assumed to be the lowest numbered nodes, limited by the highest_centroid_node_number    -  Number of centroid nodes must equal matrix_size, the size of the trip OMX Matrix3. Links are pushed into a table named GMNS_link4. link table used by AequilibraE is truncated, then filled with values from GMNS_link Some notes1. All the nodes whole id is <= highest_centroid_node_number are set as centroids2. GMNS capacity is in veh/hr/lane,  AequilibraE is in veh/hr;  hence, capacity * lanes in the insert statement3. free_flow_time (minutes) is assumed to be 60 (minutes/hr) * length (miles) / free_speed (miles/hr)<jupyter_code>#Open the Sqlite database, and insert the nodes and links
network_db = os.path.join(run_folder,'1_project','Lima.sqlite')
with sqlite3.connect(network_db) as db_con:
    #nodes
    df_node.to_sql('GMNS_node',db_con, if_exists='replace',index=False)
    db_cur = db_con.cursor()
    sql0 = "delete from nodes;"
    db_cur.execute(sql0)
    sql1 = ("insert into nodes(ogc_fid, node_id, x, y, is_centroid)" +
            " SELECT node_id, node_id, x_coord,y_coord,0 from " +
            " GMNS_node")
    db_cur.execute(sql1)
    sql2 = ("update nodes set is_centroid = 1 where ogc_fid <= " + str(highest_centroid_node_number))
    db_cur.execute(sql2)
    
with sqlite3.connect(network_db) as db_con:
    df_link.to_sql('GMNS_link',db_con, if_exists='replace',index=False)
    db_cur = db_con.cursor()
    sql0 = "delete from links;"
    db_cur.execute(sql0)
    sql1 = ("insert into links(ogc_fid, link_id, a_node, b_node, direction, distance, modes," +
            " link_type, capacity_ab, speed_ab, free_flow_time) " +
            " SELECT link_id, link_id, from_node_id, to_node_id, directed, length, allowed_uses," +
            " facility_type, capacity*lanes, free_speed, 60*length / free_speed" +
            " FROM GMNS_link where GMNS_link.capacity > 0")
    db_cur.execute(sql1)
    sql2 = ("update links set capacity_ba = 0, speed_ba = 0, b=0.15, power=4")
    db_cur.execute(sql2)<jupyter_output><empty_output><jupyter_text>Next step is to update the links with the parameters for the volume-delay function.  This step is AequilibraE-specific and makes use of the link_types Sqlite table.  This table is taken from v 0.7.1 of AequilibraE, to ease future compatibility.  The link_types table expects at least one row with link_type = "default" to use for default values.  The user may add other rows with the real link_types.  Its CREATE statement is as follows```CREATE TABLE 'link_types' (link_type     VARCHAR UNIQUE NOT NULL PRIMARY KEY,                           link_type_id  VARCHAR UNIQUE NOT NULL,                           description   VARCHAR,                           lanes         NUMERIC,                           lane_capacity NUMERIC,                           alpha         NUMERIC,                           beta          NUMERIC,                           gamma         NUMERIC,                           delta         NUMERIC,                           epsilon       NUMERIC,                           zeta          NUMERIC,                           iota          NUMERIC,                           sigma         NUMERIC,                           phi           NUMERIC,                           tau           NUMERIC)```| link_type | link_type_id | description | lanes | lane_capacity | alpha | beta | other fields not used || ----- | ----- | ----- | ----- | ----- |----- |----- |----- || default  | 99 | Default general link type | 2 | 900 | 0.15 | 4 | |<jupyter_code>with sqlite3.connect(network_db) as db_con:
    db_cur = db_con.cursor()
    sql1 = "update links set b = (select alpha from link_types where link_type = links.link_type)"
    db_cur.execute(sql1)
    sql2 = ("update links set b = (select alpha from link_types where link_type = 'default') where b is NULL")
    db_cur.execute(sql2)
    sql3 = "update links set power = (select beta from link_types where link_type = links.link_type)"
    db_cur.execute(sql3)
    sql4 = ("update links set power = (select beta from link_types where link_type = 'default') where power is NULL")
    db_cur.execute(sql4)<jupyter_output><empty_output><jupyter_text>Read the trips, and translate to omx file<jupyter_code>#Read a flat file trip table into pandas dataframe
trip_csvfile = os.path.join(run_folder, 'demand.csv')
df_trip = pd.read_csv(trip_csvfile)   #data already has headers
print(df_trip.head())   #debugging
df_size = df_trip.shape[0]
print(df_size)
#print(df.iloc[50]['o_zone_id'])
#stuff for debugging
print(df_trip['total'].sum())  #for debugging: total number of trips
#for k in range(df_size):   #at most matrix_size*matrix_size
#    i = tazdictrow[df_trip.iloc[k]['orig_taz']]
#    j = tazdictrow[df_trip.iloc[k]['dest_taz']]
#    if k == 4:  print(k,"  i=",i,"  j=",j)  #debugging
#Write the dataframe to an omx file
# This makes use of tazdictrow and matrix_size, that was established earlier. 
# The rows are also written to a file that is used only for debugging
outfile = os.path.join(run_folder, '0_tntp_data' ,'demand.omx') 
outdebugfile = open(os.path.join(run_folder,'debug_demand.txt'),"w")
output_demand = np.zeros((matrix_size,matrix_size))
f_output = omx.open_file(outfile,'w')

f_output.create_mapping('taz',taz_list)
#write the data
for k in range(df_size):   #at most matrix_size*matrix_size
    i = tazdictrow[df_trip.iloc[k]['orig_taz']]
    j = tazdictrow[df_trip.iloc[k]['dest_taz']]
  
    output_demand[i][j] = df_trip.iloc[k]['total']
    print('Row: ',df_trip.iloc[k]['orig_taz'],i,"  Col: ",df_trip.iloc[k]['dest_taz'],j," Output",output_demand[i][j],file=outdebugfile)
   
f_output['matrix'] = output_demand   #puts the output_demand array into the omx matrix
f_output.close()
outdebugfile.close()
#You may stop here
# Not needed except for debugging
#Read the input omx trip table
infile = os.path.join(run_folder, '0_tntp_data' ,'demand.omx') 
f_input = omx.open_file(infile)
m1 = f_input['matrix']
input_demand = np.array(m1)

print('Shape:',f_input.shape())
print('Number of tables',len(f_input))
print('Table names:',f_input.list_matrices())
print('attributes:',f_input.list_all_attributes())
print('sum of trips',np.sum(m1))
f_input.close()<jupyter_output>Shape: (449, 449)
Number of tables 1
Table names: ['matrix']
attributes: []
sum of trips 32041.0
{'fraction_non_alphanumeric': 0.10320130222463375, 'fraction_numerical': 0.01410743353228432, 'mean_word_length': 3.666329113924051, 'pattern_counts': {'":': 0, '<': 24, '<?xml version=': 0, '>': 23, 'https://': 0, 'lorem ipsum': 0, 'www.': 0, 'xml': 0}, 'pii_count': 0, 'substrings_counts': 0, 'word_list_counts': {'cursed_substrings.json': 4, 'profanity_word_list.json': 0, 'sexual_word_list.json': 0, 'zh_pornsignals.json': 0}}
{'chain_length': '6', 'dir': 'jupyter-structured-clean-dedup', 'id': '10991', 'license': 'Apache-2.0', 'n_tokens_mistral': 3154, 'n_tokens_neox': 3019, 'n_words': 893, 'path': 'Small_Network_Examples/Lima/AequilibraE/GMNS_AE_Integrated.ipynb', 'repo_name': 'zephyr-data-specs/gmns', 'size': '9215'}