21_ETL_Tabular.ipynb

{
 "cells": [
  {
   "cell_type": "markdown",
   "metadata": {
    "Collapsed": "false"
   },
   "source": [
    "# Extraer variables Tabulares"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 2,
   "metadata": {
    "Collapsed": "false"
   },
   "outputs": [],
   "source": [
    "import geopandas as gpd\n",
    "\n",
    "import matplotlib.pyplot as plt\n",
    "import seaborn as sns\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": "code",
   "execution_count": 5,
   "metadata": {
    "Collapsed": "false"
   },
   "outputs": [
    {
     "data": {
      "text/plain": [
       "(3848, 33)"
      ]
     },
     "execution_count": 5,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "trd = GDB['UNTRD']\n",
    "trd.shape"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {
    "Collapsed": "false"
   },
   "source": [
    "### Pre Filtro"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 6,
   "metadata": {
    "Collapsed": "false"
   },
   "outputs": [],
   "source": [
    "pre_filter = ['COD_ID', 'CTMT', 'UNI_TR_S', 'FAS_CON_P', 'FAS_CON_S', 'FAS_CON_T', 'TEN_LIN_SE', 'CAP_ELO', 'CAP_CHA',\n",
    "              'CONF','POSTO', 'POT_NOM', 'PER_FER', 'PER_TOT', 'TIP_TRAFO', 'ARE_LOC']\n",
    "\n",
    "trd = trd[pre_filter]"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {
    "Collapsed": "false"
   },
   "source": [
    "## Expandir Features a partir de otras layers"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {
    "Collapsed": "true"
   },
   "source": [
    "### Preparar UCBT"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 7,
   "metadata": {
    "Collapsed": "false"
   },
   "outputs": [],
   "source": [
    "UCBT_ENE = ['ENE_01', 'ENE_02', 'ENE_03', 'ENE_04', 'ENE_05', 'ENE_06', \n",
    "            'ENE_07','ENE_08', 'ENE_09', 'ENE_10', 'ENE_11', 'ENE_12']"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 8,
   "metadata": {
    "Collapsed": "false"
   },
   "outputs": [],
   "source": [
    "(GDB['UCBT'])['UCBT_ENE_MED'] = GDB['UCBT'][UCBT_ENE].median(axis=1)\n",
    "(GDB['UCBT'])['UCBT_ENE_STD'] = GDB['UCBT'][UCBT_ENE].std(axis=1)\n",
    "(GDB['UCBT'])['UCBT_ENE_MAX'] = GDB['UCBT'][UCBT_ENE].max(axis=1)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 9,
   "metadata": {
    "Collapsed": "false"
   },
   "outputs": [],
   "source": [
    "## Simplified version of mode to return an integer\n",
    "def _mode(s):\n",
    "    return s.mode()[0]"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 10,
   "metadata": {
    "Collapsed": "false"
   },
   "outputs": [],
   "source": [
    "UCBTgTRD = GDB['UCBT'].groupby('UNI_TR_D').agg({\n",
    "    'CLAS_SUB': _mode,\n",
    "    'CNAE': _mode, \n",
    "    'TIP_CC': _mode, \n",
    "    'TEN_FORN': _mode, \n",
    "    'GRU_TAR': _mode, \n",
    "    'CAR_INST':'sum',\n",
    "    'ARE_LOC': lambda x: (x == 'NU').sum(),\n",
    "    'TEN_FORN': _mode,\n",
    "    'UCBT_ENE_MED':'sum', \n",
    "    'UCBT_ENE_STD':'median',\n",
    "    'UCBT_ENE_MAX': 'max',\n",
    "    'PAC':'count'\n",
    "}).reset_index()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 11,
   "metadata": {
    "Collapsed": "false"
   },
   "outputs": [],
   "source": [
    "# Rename PAC a NCON = Numero de usuarios conectados al transformador\n",
    "UCBTgTRD.rename(columns= {'PAC':'NCON'}, inplace=True)\n",
    "\n",
    "# Rename PAC a NCON_URB = Numero de usuarios urbanos conectados al transformador\n",
    "UCBTgTRD.rename(columns= {'ARE_LOC':'NCON_URB'}, inplace=True)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 12,
   "metadata": {
    "Collapsed": "false"
   },
   "outputs": [],
   "source": [
    "# Seleccionar las features que vamos a adicionar de UCBT\n",
    "ucbt_ft = ['UNI_TR_D', 'CLAS_SUB', 'CNAE', 'TIP_CC', 'TEN_FORN', 'GRU_TAR', 'CAR_INST',\n",
    "           'UCBT_ENE_MED', 'UCBT_ENE_STD', 'UCBT_ENE_MAX', 'NCON', 'NCON_URB']"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 13,
   "metadata": {
    "Collapsed": "false"
   },
   "outputs": [],
   "source": [
    "trd = pd.merge(trd, UCBTgTRD[ucbt_ft], how='left', left_on='COD_ID', right_on='UNI_TR_D')"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 14,
   "metadata": {
    "Collapsed": "false"
   },
   "outputs": [
    {
     "data": {
      "text/plain": [
       "Index(['COD_ID', 'CTMT', 'UNI_TR_S', 'FAS_CON_P', 'FAS_CON_S', 'FAS_CON_T',\n",
       "       'TEN_LIN_SE', 'CAP_ELO', 'CAP_CHA', 'CONF', 'POSTO', 'POT_NOM',\n",
       "       'PER_FER', 'PER_TOT', 'TIP_TRAFO', 'ARE_LOC', 'UNI_TR_D', 'CLAS_SUB',\n",
       "       'CNAE', 'TIP_CC', 'TEN_FORN', 'GRU_TAR', 'CAR_INST', 'UCBT_ENE_MED',\n",
       "       'UCBT_ENE_STD', 'UCBT_ENE_MAX', 'NCON', 'NCON_URB'],\n",
       "      dtype='object')"
      ]
     },
     "execution_count": 14,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "trd.columns"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {
    "Collapsed": "true"
   },
   "source": [
    "### Preparar CTMT"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 15,
   "metadata": {
    "Collapsed": "false"
   },
   "outputs": [],
   "source": [
    "CT_ENE = ['ENE_01', 'ENE_02', 'ENE_03', 'ENE_04', 'ENE_05', 'ENE_06', \n",
    "          'ENE_07','ENE_08', 'ENE_09', 'ENE_10', 'ENE_11', 'ENE_12']\n",
    "\n",
    "CT_PNTMT = ['PNTMT_01', 'PNTMT_02', 'PNTMT_03', 'PNTMT_04', 'PNTMT_05', 'PNTMT_06',\n",
    "            'PNTMT_07','PNTMT_08', 'PNTMT_09', 'PNTMT_10', 'PNTMT_11', 'PNTMT_12']\n",
    "\n",
    "CT_PNTBT = ['PNTBT_01', 'PNTBT_02', 'PNTBT_03', 'PNTBT_04', 'PNTBT_05', 'PNTBT_06',\n",
    "            'PNTBT_07','PNTBT_08', 'PNTBT_09', 'PNTBT_10', 'PNTBT_11', 'PNTBT_12']"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 16,
   "metadata": {
    "Collapsed": "false"
   },
   "outputs": [],
   "source": [
    "(GDB['CTMT'])['CTMT_ENE_MED'] = GDB['CTMT'][CT_ENE].median(axis=1)\n",
    "(GDB['CTMT'])['CTMT_ENE_STD'] = GDB['CTMT'][CT_ENE].std(axis=1)\n",
    "(GDB['CTMT'])['CTMT_ENE_MAX'] = GDB['CTMT'][CT_ENE].max(axis=1)\n",
    "\n",
    "(GDB['CTMT'])['CTMT_PNTMT_MED'] = GDB['CTMT'][CT_PNTMT].median(axis=1)\n",
    "(GDB['CTMT'])['CTMT_PNTMT_STD'] = GDB['CTMT'][CT_PNTMT].std(axis=1)\n",
    "(GDB['CTMT'])['CTMT_PNTMT_MAX'] = GDB['CTMT'][CT_PNTMT].max(axis=1)\n",
    "\n",
    "(GDB['CTMT'])['CTMT_PNTBT_MED'] = GDB['CTMT'][CT_PNTBT].median(axis=1)\n",
    "(GDB['CTMT'])['CTMT_PNTBT_STD'] = GDB['CTMT'][CT_PNTBT].std(axis=1)\n",
    "(GDB['CTMT'])['CTMT_PNTBT_MAX'] = GDB['CTMT'][CT_PNTBT].max(axis=1)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 17,
   "metadata": {
    "Collapsed": "false"
   },
   "outputs": [],
   "source": [
    "GDB['CTMT'].rename(columns={'COD_ID':'CTMT'}, inplace=True)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 18,
   "metadata": {
    "Collapsed": "false"
   },
   "outputs": [],
   "source": [
    "ctmt_ft = ['CTMT','CTMT_ENE_MED','CTMT_ENE_STD','CTMT_ENE_MAX','PERD_A3a', 'PERD_A4', 'PERD_B', 'PERD_MED',\n",
    "           'PERD_A3a_B', 'PERD_A4_B', 'PERD_B_A3a', 'PERD_B_A4','CTMT_PNTMT_MED','CTMT_PNTMT_STD','CTMT_PNTMT_MAX',\n",
    "           'CTMT_PNTBT_MED','CTMT_PNTBT_STD','CTMT_PNTBT_MAX']"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 19,
   "metadata": {
    "Collapsed": "false"
   },
   "outputs": [],
   "source": [
    "trd = pd.merge(trd, GDB['CTMT'][ctmt_ft], how='left', on='CTMT')"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 20,
   "metadata": {
    "Collapsed": "false"
   },
   "outputs": [
    {
     "data": {
      "text/plain": [
       "Index(['COD_ID', 'CTMT', 'UNI_TR_S', 'FAS_CON_P', 'FAS_CON_S', 'FAS_CON_T',\n",
       "       'TEN_LIN_SE', 'CAP_ELO', 'CAP_CHA', 'CONF', 'POSTO', 'POT_NOM',\n",
       "       'PER_FER', 'PER_TOT', 'TIP_TRAFO', 'ARE_LOC', 'UNI_TR_D', 'CLAS_SUB',\n",
       "       'CNAE', 'TIP_CC', 'TEN_FORN', 'GRU_TAR', 'CAR_INST', 'UCBT_ENE_MED',\n",
       "       'UCBT_ENE_STD', 'UCBT_ENE_MAX', 'NCON', 'NCON_URB', 'CTMT_ENE_MED',\n",
       "       'CTMT_ENE_STD', 'CTMT_ENE_MAX', 'PERD_A3a', 'PERD_A4', 'PERD_B',\n",
       "       'PERD_MED', 'PERD_A3a_B', 'PERD_A4_B', 'PERD_B_A3a', 'PERD_B_A4',\n",
       "       'CTMT_PNTMT_MED', 'CTMT_PNTMT_STD', 'CTMT_PNTMT_MAX', 'CTMT_PNTBT_MED',\n",
       "       'CTMT_PNTBT_STD', 'CTMT_PNTBT_MAX'],\n",
       "      dtype='object')"
      ]
     },
     "execution_count": 20,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "trd.columns"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {
    "Collapsed": "false"
   },
   "source": [
    "### Preparar UNTRS"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 21,
   "metadata": {
    "Collapsed": "false"
   },
   "outputs": [],
   "source": [
    "TRS_ENES = ['ENES_01', 'ENES_02', 'ENES_03', 'ENES_04', 'ENES_05', 'ENES_06',\n",
    "           'ENES_07', 'ENES_08', 'ENES_09', 'ENES_10', 'ENES_11', 'ENES_12']\n",
    "\n",
    "TRS_ENET = ['ENET_01', 'ENET_02', 'ENET_03', 'ENET_04', 'ENET_05', 'ENET_06',\n",
    "            'ENET_07', 'ENET_08', 'ENET_09', 'ENET_10', 'ENET_11', 'ENET_12']"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 22,
   "metadata": {
    "Collapsed": "false"
   },
   "outputs": [],
   "source": [
    "GDB['UNTRS']['UNTRS_ENES_MED'] = GDB['UNTRS'][TRS_ENES].median(axis=1)\n",
    "GDB['UNTRS']['UNTRS_ENES_STD'] = GDB['UNTRS'][TRS_ENES].std(axis=1)\n",
    "GDB['UNTRS']['UNTRS_ENES_MAX'] = GDB['UNTRS'][TRS_ENES].max(axis=1)\n",
    "\n",
    "GDB['UNTRS']['UNTRS_ENET_MED'] = GDB['UNTRS'][TRS_ENET].median(axis=1)\n",
    "GDB['UNTRS']['UNTRS_ENET_STD'] = GDB['UNTRS'][TRS_ENET].std(axis=1)\n",
    "GDB['UNTRS']['UNTRS_ENET_MAX'] = GDB['UNTRS'][TRS_ENET].max(axis=1)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 23,
   "metadata": {
    "Collapsed": "false"
   },
   "outputs": [],
   "source": [
    "GDB['UNTRS'].rename(columns={'COD_ID':'UNI_TR_S'}, inplace=True)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 24,
   "metadata": {
    "Collapsed": "false"
   },
   "outputs": [],
   "source": [
    "untrs_ft = ['UNI_TR_S','POT_NOM','POT_F01','POT_F02','PER_FER','PER_TOT','TIP_TRAFO','UNTRS_ENES_MED','UNTRS_ENES_STD',\n",
    "           'UNTRS_ENES_MAX','UNTRS_ENET_MED','UNTRS_ENET_STD','UNTRS_ENET_MAX']"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 25,
   "metadata": {
    "Collapsed": "false"
   },
   "outputs": [],
   "source": [
    "trd = pd.merge(trd, GDB['UNTRS'][untrs_ft], how='left', on='UNI_TR_S', suffixes=('','_TRS'))"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 26,
   "metadata": {
    "Collapsed": "false"
   },
   "outputs": [
    {
     "data": {
      "text/plain": [
       "Index(['COD_ID', 'CTMT', 'UNI_TR_S', 'FAS_CON_P', 'FAS_CON_S', 'FAS_CON_T',\n",
       "       'TEN_LIN_SE', 'CAP_ELO', 'CAP_CHA', 'CONF', 'POSTO', 'POT_NOM',\n",
       "       'PER_FER', 'PER_TOT', 'TIP_TRAFO', 'ARE_LOC', 'UNI_TR_D', 'CLAS_SUB',\n",
       "       'CNAE', 'TIP_CC', 'TEN_FORN', 'GRU_TAR', 'CAR_INST', 'UCBT_ENE_MED',\n",
       "       'UCBT_ENE_STD', 'UCBT_ENE_MAX', 'NCON', 'NCON_URB', 'CTMT_ENE_MED',\n",
       "       'CTMT_ENE_STD', 'CTMT_ENE_MAX', 'PERD_A3a', 'PERD_A4', 'PERD_B',\n",
       "       'PERD_MED', 'PERD_A3a_B', 'PERD_A4_B', 'PERD_B_A3a', 'PERD_B_A4',\n",
       "       'CTMT_PNTMT_MED', 'CTMT_PNTMT_STD', 'CTMT_PNTMT_MAX', 'CTMT_PNTBT_MED',\n",
       "       'CTMT_PNTBT_STD', 'CTMT_PNTBT_MAX', 'POT_NOM_TRS', 'POT_F01', 'POT_F02',\n",
       "       'PER_FER_TRS', 'PER_TOT_TRS', 'TIP_TRAFO_TRS', 'UNTRS_ENES_MED',\n",
       "       'UNTRS_ENES_STD', 'UNTRS_ENES_MAX', 'UNTRS_ENET_MED', 'UNTRS_ENET_STD',\n",
       "       'UNTRS_ENET_MAX'],\n",
       "      dtype='object')"
      ]
     },
     "execution_count": 26,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "trd.columns"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 52,
   "metadata": {
    "Collapsed": "false"
   },
   "outputs": [
    {
     "data": {
      "text/plain": [
       "(3848, 60)"
      ]
     },
     "execution_count": 52,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# Quito las columnas que corresponden al key de CTMT y UNI_TR_S\n",
    "trd.drop(['CTMT','UNI_TR_])\n",
    "trd.shape"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {
    "Collapsed": "false"
   },
   "source": [
    "## Exportar Exit Point"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 54,
   "metadata": {
    "Collapsed": "false"
   },
   "outputs": [],
   "source": [
    "filename = './data/21_ETL_Tabular.pkl'\n",
    "outfile = open(filename,'wb')\n",
    "pickle.dump(trd, 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
}