Importing FAF matrices.ipynb

{
 "cells": [
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "# Objective\n",
    "Import the FAF freight matrices provided with FAF into AequilibraE's matrix format\n",
    "\n",
    "## Input data\n",
    "\n",
    "* FAF: https://faf.ornl.gov/fafweb/\n",
    "* Matrices: https://faf.ornl.gov/fafweb/Data/FAF4.4_HiLoForecasts.zip\n",
    "* Zones System: http://www.census.gov/econ/cfs/AboutGeographyFiles/CFS_AREA_shapefile_010215.zip\n",
    "* FAF User Guide: https://faf.ornl.gov/fafweb/data/FAF4%20User%20Guide.pdf\n",
    "* The blog post (with data): http://www.xl-optim.com/matrix-api-and-multi-class-assignment"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "# The code\n",
    "We import all libraries we will need, including the AequilibraE, after putting it in our Python path "
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 42,
   "metadata": {
    "collapsed": true
   },
   "outputs": [],
   "source": [
    "import sys\n",
    "# On Linux\n",
    "# sys.path.append('/home/pedro/.qgis2/python/plugins/AequilibraE')\n",
    "# On Windows\n",
    "sys.path.append('C:/Users/Pedro/.qgis2/python/plugins/AequilibraE')\n",
    "import pandas as pd\n",
    "import numpy as np\n",
    "import os\n",
    "from aequilibrae.matrix import AequilibraeMatrix\n",
    "from scipy.sparse import coo_matrix"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Now we set all the paths for files and parameters we need"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 34,
   "metadata": {
    "collapsed": true
   },
   "outputs": [],
   "source": [
    "data_folder = 'Y:/ALL DATA/DATA/Pedro/Professional/Data/USA/FAF/4.4'\n",
    "data_file = 'FAF4.4_HiLoForecasts.csv'\n",
    "sctg_names_file = 'sctg_codes.csv'  # Simplified to 50 characters, which is AequilibraE's limit\n",
    "output_folder = data_folder"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "We import the the matrices"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 31,
   "metadata": {
    "scrolled": true
   },
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "  fr_orig  dms_orig  dms_dest fr_dest fr_inmode  dms_mode fr_outmode  sctg2  \\\n",
      "0                11        11                           1                 1   \n",
      "1                11        19                           1                 1   \n",
      "2                11       129                           1                 1   \n",
      "3                11       131                           1                 1   \n",
      "4                11       139                           1                 1   \n",
      "5                11       280                           1                 1   \n",
      "6                11       379                           1                 1   \n",
      "7                11       472                           1                 1   \n",
      "8                11       479                           1                 1   \n",
      "9                12        12                           1                 1   \n",
      "\n",
      "   trade_type  tons_2012     ...       tmiles_2015  tmiles_2020  tmiles_2025  \\\n",
      "0           1    17.3922     ...            0.3817       0.4318       0.4791   \n",
      "1           1   220.0650     ...           25.7995      27.4014      28.5980   \n",
      "2           1     1.0854     ...            0.5211       0.5033       0.4917   \n",
      "3           1     6.1655     ...            1.4469       1.3902       1.4049   \n",
      "4           1     2.7483     ...            0.7550       0.6872       0.6569   \n",
      "5           1    69.7733     ...           16.8310      16.5863      15.8050   \n",
      "6           1     7.1994     ...            3.0364       2.8807       2.6488   \n",
      "7           1     2.0029     ...            0.3240       0.3106       0.2884   \n",
      "8           1    23.1675     ...            4.8585       4.6936       4.3909   \n",
      "9           1    13.2773     ...            0.3417       0.3951       0.4391   \n",
      "\n",
      "   tmiles_2030  tmiles_2035  tmiles_2040  tmiles_2045  curval_2013  \\\n",
      "0       0.5265       0.5854       0.6449       0.6656      36.7205   \n",
      "1      29.6591      30.9511      32.5587      33.8550     283.6623   \n",
      "2       0.4819       0.4688       0.4860       0.5272       1.3439   \n",
      "3       1.4619       1.5058       1.5015       1.6357      10.7475   \n",
      "4       0.6488       0.6353       0.6072       0.6476       4.5875   \n",
      "5      15.2127      14.8790      14.8032      15.5691     113.2926   \n",
      "6       2.5256       2.5153       2.4165       2.5699       6.5644   \n",
      "7       0.2698       0.2553       0.2457       0.2521       4.1936   \n",
      "8       4.2165       4.1382       4.0512       4.2904      17.5325   \n",
      "9       0.4800       0.5261       0.5729       0.6069      51.1500   \n",
      "\n",
      "   curval_2014  curval_2015  \n",
      "0      46.3411      42.7443  \n",
      "1     333.8676     301.8007  \n",
      "2       1.4783       1.3083  \n",
      "3      10.9878       9.6642  \n",
      "4       4.5780       3.9517  \n",
      "5      98.2313      84.4300  \n",
      "6       7.2946       6.4304  \n",
      "7       4.4779       3.9434  \n",
      "8      19.1832      16.9555  \n",
      "9      62.3887      57.5727  \n",
      "\n",
      "[10 rows x 66 columns]\n"
     ]
    }
   ],
   "source": [
    "matrices = pd.read_csv(os.path.join(data_folder, data_file), low_memory=False)\n",
    "print matrices.head(10)\n"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "We import the sctg codes"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 45,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "                                Commodity Description\n",
      "Code                                                 \n",
      "1                             Animals and Fish (live)\n",
      "2                       Cereal Grains (includes seed)\n",
      "3                               Agricultural Products\n",
      "4     Animal Feed, Eggs, Honey, Prod of Animal Origin\n",
      "5      Meat, Poultry, Fish, Seafood, and Preparations\n"
     ]
    }
   ],
   "source": [
    "sctg_names = pd.read_csv(os.path.join(data_folder, sctg_names_file), low_memory=False)\n",
    "sctg_names.set_index('Code', inplace=True)\n",
    "sctg_descr = list(sctg_names['Commodity Description'])\n",
    "print sctg_names.head(5)\n",
    "\n"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "We now process the matrices to collect all the data we need, such as:\n",
    "* the list of zones\n",
    "* CSTG codes\n",
    "* Matrices/scenarios we are importing"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 33,
   "metadata": {
    "collapsed": true
   },
   "outputs": [],
   "source": [
    "# lists the zones\n",
    "all_zones = np.array(sorted(list(set( list(matrices.dms_orig.unique()) + list(matrices.dms_dest.unique())))))\n",
    "\n",
    "# Count them and create a 0-based index\n",
    "num_zones = all_zones.shape[0]\n",
    "idx = np.arange(num_zones)\n",
    "\n",
    "# Creates the indexing dataframes\n",
    "origs = pd.DataFrame({\"from_index\": all_zones, \"from\":idx})\n",
    "dests = pd.DataFrame({\"to_index\": all_zones, \"to\":idx})\n",
    "\n",
    "# adds the new index columns to the pandas dataframe\n",
    "matrices = matrices.merge(origs, left_on='dms_orig', right_on='from_index', how='left')\n",
    "matrices = matrices.merge(dests, left_on='dms_dest', right_on='to_index', how='left')\n",
    "\n",
    "# Lists sctg codes and all the years/scenarios we have matrices for\n",
    "mat_years = [x for x in matrices.columns if 'tons' in x]\n",
    "sctg_codes = matrices.sctg2.unique()\n",
    "\n"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {
    "collapsed": true
   },
   "source": [
    "We now import one matrix for each year, saving all the SCTG codes as different matrix cores in our zoning system\n",
    "\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 51,
   "metadata": {
    "collapsed": true
   },
   "outputs": [],
   "source": [
    "# aggregate the matrix according to the relevant criteria\n",
    "agg_matrix = matrices.groupby(['from', 'to', 'sctg2'])[mat_years].sum()\n",
    "\n",
    "# returns the indices\n",
    "agg_matrix.reset_index(inplace=True)\n",
    "\n",
    "\n",
    "for y in mat_years:\n",
    "    mat = AequilibraeMatrix()\n",
    "    \n",
    "    kwargs = {'file_name': os.path.join(output_folder, y + '.aem'),\n",
    "              'zones': num_zones,\n",
    "              'matrix_names': sctg_descr}\n",
    "    \n",
    "    mat.create_empty(**kwargs)\n",
    "    mat.index[:] = all_zones[:]\n",
    "    # for all sctg codes\n",
    "    for i in sctg_names.index:\n",
    "        prod_name = sctg_names['Commodity Description'][i]\n",
    "        mat_filtered_sctg = agg_matrix[agg_matrix.sctg2 == i]\n",
    "        \n",
    "        m = coo_matrix((mat_filtered_sctg[y], (mat_filtered_sctg['from'], mat_filtered_sctg['to'])),\n",
    "                                           shape=(num_zones, num_zones)).toarray().astype(np.float64)\n",
    "        \n",
    "        mat.matrix[prod_name][:,:] = m[:,:]\n",
    "        \n",
    "    "
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "collapsed": true
   },
   "outputs": [],
   "source": []
  }
 ],
 "metadata": {
  "kernelspec": {
   "display_name": "Python 2",
   "language": "python",
   "name": "python2"
  },
  "language_info": {
   "codemirror_mode": {
    "name": "ipython",
    "version": 2
   },
   "file_extension": ".py",
   "mimetype": "text/x-python",
   "name": "python",
   "nbconvert_exporter": "python",
   "pygments_lexer": "ipython2",
   "version": "2.7.9"
  }
 },
 "nbformat": 4,
 "nbformat_minor": 2
}