22_ETL_CreateGraph.ipynb

{
 "cells": [
  {
   "cell_type": "markdown",
   "metadata": {
    "Collapsed": "false"
   },
   "source": [
    "# Generacion Features de Grafo"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {
    "Collapsed": "false"
   },
   "source": [
    "## Imports & Load Data"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 1,
   "metadata": {
    "Collapsed": "false"
   },
   "outputs": [],
   "source": [
    "import warnings\n",
    "warnings.filterwarnings('ignore')"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 2,
   "metadata": {
    "Collapsed": "false"
   },
   "outputs": [],
   "source": [
    "import geopandas as gpd\n",
    "import networkx as nx\n",
    "\n",
    "import numpy as np\n",
    "import pandas as pd\n",
    "\n",
    "import pickle"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 3,
   "metadata": {
    "Collapsed": "false"
   },
   "outputs": [],
   "source": [
    "# Cargar endpoint\n",
    "infile = open('./data/1_GDB_Layers.pkl','rb')\n",
    "GDB = pickle.load(infile)\n",
    "infile.close()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {
    "Collapsed": "false"
   },
   "source": [
    "## Generar Grafos"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {
    "Collapsed": "true"
   },
   "source": [
    "### Grafo General Red de Media"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 4,
   "metadata": {
    "Collapsed": "false"
   },
   "outputs": [],
   "source": [
    "# Creamos un grafo cuyos edges son los segmentos de la red de media tension\n",
    "# Asignamos pesos en base a la longitud de cada segmento\n",
    "MTG = nx.Graph()\n",
    "MTG.add_weighted_edges_from(GDB['SSDMT'][['PN_CON_1','PN_CON_2','COMP']].values.tolist(), weight='length')"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 5,
   "metadata": {
    "Collapsed": "false"
   },
   "outputs": [],
   "source": [
    "# Adicionar propiedades\n",
    "\n",
    "# Agrego el COD_ID de cada segmento como atributo de Edge\n",
    "ID_attr_dict = {tuple((x[0], x[1])): x[2] for x in GDB['SSDMT'][['PN_CON_1','PN_CON_2','COD_ID']].values}\n",
    "nx.set_edge_attributes(MTG, ID_attr_dict, 'SEG_ID')"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 6,
   "metadata": {
    "Collapsed": "false"
   },
   "outputs": [],
   "source": [
    "# Correcciones manuales por inspeccion\n",
    "\n",
    "# Agregos segmentos faltantes del grafo en base a analisis espacial\n",
    "MTG.add_weighted_edges_from([['27153484','27147209', 29.9]], weight='length')\n",
    "MTG.add_weighted_edges_from([['27134272','27134273', 2]], weight='length')"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {
    "Collapsed": "true"
   },
   "source": [
    "### Diccionario de Grafos en base al circuito de media CTMT"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 7,
   "metadata": {
    "Collapsed": "false"
   },
   "outputs": [],
   "source": [
    "g_dict = dict()\n",
    "\n",
    "for c in GDB['SSDMT'].CTMT.unique():\n",
    "    ss_ctmt = GDB['SSDMT'][GDB['SSDMT'].CTMT == c]\n",
    "    \n",
    "    gg = nx.Graph()\n",
    "    gg.add_weighted_edges_from(ss_ctmt[['PN_CON_1','PN_CON_2','COMP']].values.tolist(), weight='length')\n",
    "    \n",
    "    ID_attr_dict = {tuple((x[0], x[1])): x[2] for x in ss_ctmt[['PN_CON_1','PN_CON_2','COD_ID']].values}\n",
    "    nx.set_edge_attributes(gg, ID_attr_dict, 'COD_ID')\n",
    "    \n",
    "    g_dict[c] = gg"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 8,
   "metadata": {
    "Collapsed": "false"
   },
   "outputs": [],
   "source": [
    "# Agregos segmentos faltantes del grafo en base a analisis espacial\n",
    "g_dict['27117490'].add_weighted_edges_from([['27153484','27147209', 29.9]], weight='length', COD_ID = 'A1')\n",
    "g_dict['27117499'].add_weighted_edges_from([['27134272','27134273', 2]], weight='length', COD_ID = 'A2')"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 9,
   "metadata": {
    "Collapsed": "false"
   },
   "outputs": [],
   "source": [
    "# Agrego el COD_ID de cada segmento como atributo de Edge\n",
    "for gr in g_dict:\n",
    "    nx.set_edge_attributes(g_dict[gr], ID_attr_dict, 'COD_ID')"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {
    "Collapsed": "false"
   },
   "source": [
    "### Definir puntos de conexion a Subestacion"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 117,
   "metadata": {
    "Collapsed": "false"
   },
   "outputs": [],
   "source": [
    "### !!! MEJORAR EFICIENCIA DE ESTO !!!\n",
    "\n",
    "# Las subestaciones tienen multiples puntos de llegada\n",
    "# por ende definimos una lista de puntos para cada una\n",
    "CTMT_SUB_PCON = pd.DataFrame(columns= ['CTMT','CTMT_PCON'])\n",
    "\n",
    "for _, sub_poly in GDB['SUB'].iterrows():\n",
    "    for i, seg_pcon in GDB['SSDMT'].iterrows():\n",
    "        if sub_poly.geometry.boundary.intersects(seg_pcon.geometry) and MTG.has_node(seg_pcon.PN_CON_1):\n",
    "            CTMT_SUB_PCON.loc[seg_pcon.COD_ID] = [seg_pcon.CTMT, seg_pcon.PN_CON_1]"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 118,
   "metadata": {
    "Collapsed": "false"
   },
   "outputs": [],
   "source": [
    "# Agregar punto de conexion no encontrados\n",
    "CTMT_SUB_PCON.loc['26705414'] = ['27117513', '27807944']\n",
    "CTMT_SUB_PCON.loc['26705413'] = ['27117507', '27760710']"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 119,
   "metadata": {
    "Collapsed": "false"
   },
   "outputs": [],
   "source": [
    "CTMT_SUB_PCON = CTMT_SUB_PCON[~CTMT_SUB_PCON.CTMT.duplicated()]"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 120,
   "metadata": {
    "Collapsed": "false"
   },
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>CTMT</th>\n",
       "      <th>CTMT_PCON</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>count</th>\n",
       "      <td>29</td>\n",
       "      <td>29</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>unique</th>\n",
       "      <td>29</td>\n",
       "      <td>25</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>top</th>\n",
       "      <td>27117511</td>\n",
       "      <td>27148235</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>freq</th>\n",
       "      <td>1</td>\n",
       "      <td>2</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "            CTMT CTMT_PCON\n",
       "count         29        29\n",
       "unique        29        25\n",
       "top     27117511  27148235\n",
       "freq           1         2"
      ]
     },
     "execution_count": 120,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "CTMT_SUB_PCON.describe()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {
    "Collapsed": "false"
   },
   "source": [
    "### Definir punto de conexion a red de media de cada Tranformador"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 121,
   "metadata": {
    "Collapsed": "false"
   },
   "outputs": [],
   "source": [
    "# !!! MEJORAR EFICIENCIA !!!\n",
    "\n",
    "def get_pn_con(trd_pac):\n",
    "    PAC_ID = f\"{trd_pac[:-2]}MT\"\n",
    "\n",
    "    if PAC_ID in GDB['SSDMT'].PAC_1.values:\n",
    "        pn_con = GDB['SSDMT'][GDB['SSDMT'].PAC_1 == PAC_ID].PN_CON_1.values[0]\n",
    "    elif PAC_ID in GDB['SSDMT'].PAC_2.values:\n",
    "        pn_con = GDB['SSDMT'][GDB['SSDMT'].PAC_2 == PAC_ID].PN_CON_2.values[0]\n",
    "    else:\n",
    "        pn_con = ''\n",
    "    \n",
    "    return pn_con"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 122,
   "metadata": {
    "Collapsed": "false"
   },
   "outputs": [],
   "source": [
    "UNTRD_CONECTIONS = GDB['UNTRD'][['COD_ID','PAC_1','CTMT','SUB']]"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 123,
   "metadata": {
    "Collapsed": "false"
   },
   "outputs": [
    {
     "name": "stderr",
     "output_type": "stream",
     "text": [
      "C:\\Users\\RamiroJC\\AppData\\Roaming\\Python\\Python37\\site-packages\\ipykernel_launcher.py:1: SettingWithCopyWarning: \n",
      "A value is trying to be set on a copy of a slice from a DataFrame.\n",
      "Try using .loc[row_indexer,col_indexer] = value instead\n",
      "\n",
      "See the caveats in the documentation: http://pandas.pydata.org/pandas-docs/stable/indexing.html#indexing-view-versus-copy\n",
      "  \"\"\"Entry point for launching an IPython kernel.\n"
     ]
    }
   ],
   "source": [
    "UNTRD_CONECTIONS['UNTRD_PCON'] = UNTRD_CONECTIONS.PAC_1.apply(get_pn_con)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 124,
   "metadata": {
    "Collapsed": "false"
   },
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>COD_ID</th>\n",
       "      <th>PAC_1</th>\n",
       "      <th>CTMT</th>\n",
       "      <th>SUB</th>\n",
       "      <th>UNTRD_PCON</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>26879192</td>\n",
       "      <td>75475200753159000ET</td>\n",
       "      <td>27117500</td>\n",
       "      <td>27335510</td>\n",
       "      <td>27156187</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>26880293</td>\n",
       "      <td>75333100753159000ET</td>\n",
       "      <td>27117512</td>\n",
       "      <td>27335511</td>\n",
       "      <td>27132900</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>26880295</td>\n",
       "      <td>75333800753149800ET</td>\n",
       "      <td>27117512</td>\n",
       "      <td>27335511</td>\n",
       "      <td>27137461</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>26880296</td>\n",
       "      <td>75334300753147200ET</td>\n",
       "      <td>27117512</td>\n",
       "      <td>27335511</td>\n",
       "      <td>27137303</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>26880297</td>\n",
       "      <td>75332600753159300ET</td>\n",
       "      <td>27117512</td>\n",
       "      <td>27335511</td>\n",
       "      <td>27148730</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "     COD_ID                PAC_1      CTMT       SUB UNTRD_PCON\n",
       "0  26879192  75475200753159000ET  27117500  27335510   27156187\n",
       "1  26880293  75333100753159000ET  27117512  27335511   27132900\n",
       "2  26880295  75333800753149800ET  27117512  27335511   27137461\n",
       "3  26880296  75334300753147200ET  27117512  27335511   27137303\n",
       "4  26880297  75332600753159300ET  27117512  27335511   27148730"
      ]
     },
     "execution_count": 124,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "UNTRD_CONECTIONS.head(5)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {
    "Collapsed": "false"
   },
   "source": [
    "## Generar Tabla de conexiones"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 125,
   "metadata": {
    "Collapsed": "false"
   },
   "outputs": [
    {
     "data": {
      "text/plain": [
       "(3848, 5)"
      ]
     },
     "execution_count": 125,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "UNTRD_CONECTIONS.shape"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 127,
   "metadata": {
    "Collapsed": "false"
   },
   "outputs": [],
   "source": [
    "UNTRD_CONECTIONS = pd.merge(UNTRD_CONECTIONS, CTMT_SUB_PCON, how='left', on='CTMT')"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 139,
   "metadata": {
    "Collapsed": "false"
   },
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>COD_ID</th>\n",
       "      <th>PAC_1</th>\n",
       "      <th>CTMT</th>\n",
       "      <th>SUB</th>\n",
       "      <th>UNTRD_PCON</th>\n",
       "      <th>CTMT_PCON</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>2487</th>\n",
       "      <td>26877916</td>\n",
       "      <td>75002900754086600ET</td>\n",
       "      <td>27117490</td>\n",
       "      <td>27335509</td>\n",
       "      <td>27154833</td>\n",
       "      <td>27128870</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2826</th>\n",
       "      <td>26877058</td>\n",
       "      <td>76425433751994355MT</td>\n",
       "      <td>27117484</td>\n",
       "      <td>27335513</td>\n",
       "      <td>27159475</td>\n",
       "      <td>44571767</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2048</th>\n",
       "      <td>26877716</td>\n",
       "      <td>75541000753715800ET</td>\n",
       "      <td>80345444</td>\n",
       "      <td>27335511</td>\n",
       "      <td>27142033</td>\n",
       "      <td>27708175</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1634</th>\n",
       "      <td>26877845</td>\n",
       "      <td>75266500753839800ET</td>\n",
       "      <td>27117490</td>\n",
       "      <td>27335509</td>\n",
       "      <td>27141205</td>\n",
       "      <td>27128870</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3622</th>\n",
       "      <td>26876672</td>\n",
       "      <td>76672700753178400ET</td>\n",
       "      <td>27117484</td>\n",
       "      <td>27335513</td>\n",
       "      <td>27146517</td>\n",
       "      <td>44571767</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2407</th>\n",
       "      <td>26877437</td>\n",
       "      <td>75656048753957141ET</td>\n",
       "      <td>27117486</td>\n",
       "      <td>27335509</td>\n",
       "      <td>27157771</td>\n",
       "      <td>27152908</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>56</th>\n",
       "      <td>26880372</td>\n",
       "      <td>75391600753158800MT</td>\n",
       "      <td>27117512</td>\n",
       "      <td>27335511</td>\n",
       "      <td>27153411</td>\n",
       "      <td>27148235</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3281</th>\n",
       "      <td>26876797</td>\n",
       "      <td>76973453752623547ET</td>\n",
       "      <td>27117484</td>\n",
       "      <td>27335513</td>\n",
       "      <td>27145716</td>\n",
       "      <td>44571767</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1979</th>\n",
       "      <td>26879037</td>\n",
       "      <td>75581900753351800ET</td>\n",
       "      <td>27117499</td>\n",
       "      <td>27335510</td>\n",
       "      <td>27134865</td>\n",
       "      <td>27707916</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>26880296</td>\n",
       "      <td>75334300753147200ET</td>\n",
       "      <td>27117512</td>\n",
       "      <td>27335511</td>\n",
       "      <td>27137303</td>\n",
       "      <td>27148235</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "        COD_ID                PAC_1      CTMT       SUB UNTRD_PCON CTMT_PCON\n",
       "2487  26877916  75002900754086600ET  27117490  27335509   27154833  27128870\n",
       "2826  26877058  76425433751994355MT  27117484  27335513   27159475  44571767\n",
       "2048  26877716  75541000753715800ET  80345444  27335511   27142033  27708175\n",
       "1634  26877845  75266500753839800ET  27117490  27335509   27141205  27128870\n",
       "3622  26876672  76672700753178400ET  27117484  27335513   27146517  44571767\n",
       "2407  26877437  75656048753957141ET  27117486  27335509   27157771  27152908\n",
       "56    26880372  75391600753158800MT  27117512  27335511   27153411  27148235\n",
       "3281  26876797  76973453752623547ET  27117484  27335513   27145716  44571767\n",
       "1979  26879037  75581900753351800ET  27117499  27335510   27134865  27707916\n",
       "3     26880296  75334300753147200ET  27117512  27335511   27137303  27148235"
      ]
     },
     "execution_count": 139,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "UNTRD_CONECTIONS.sample(10)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {
    "Collapsed": "false"
   },
   "source": [
    "## Exportar exit point"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 20,
   "metadata": {
    "Collapsed": "false"
   },
   "outputs": [],
   "source": [
    "filename = './data/22_Full_Graph.pkl'\n",
    "outfile = open(filename, 'wb')\n",
    "pickle.dump(MTG,outfile)\n",
    "outfile.close()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 22,
   "metadata": {
    "Collapsed": "false"
   },
   "outputs": [],
   "source": [
    "filename = './data/22_CTMT_Graph_Dict.pkl'\n",
    "outfile = open(filename, 'wb')\n",
    "pickle.dump(g_dict, outfile)\n",
    "outfile.close()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 131,
   "metadata": {
    "Collapsed": "false"
   },
   "outputs": [],
   "source": [
    "filename = './data/22_UNTRD_Conections.pkl'\n",
    "outfile = open(filename, 'wb')\n",
    "pickle.dump(UNTRD_CONECTIONS, outfile)\n",
    "outfile.close()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "Collapsed": "false"
   },
   "outputs": [],
   "source": []
  }
 ],
 "metadata": {
  "kernelspec": {
   "display_name": "Python 3",
   "language": "python",
   "name": "python3"
  },
  "language_info": {
   "codemirror_mode": {
    "name": "ipython",
    "version": 3
   },
   "file_extension": ".py",
   "mimetype": "text/x-python",
   "name": "python",
   "nbconvert_exporter": "python",
   "pygments_lexer": "ipython3",
   "version": "3.7.3"
  }
 },
 "nbformat": 4,
 "nbformat_minor": 4
}