Rankings for All Columns.ipynb

{
 "cells": [
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "# Librerias"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 1,
   "metadata": {},
   "outputs": [],
   "source": [
    "import numpy as np\n",
    "import pandas as pd\n",
    "import seaborn as sns\n",
    "import graphviz\n",
    "\n",
    "import matplotlib.pyplot as plt\n",
    "from matplotlib.colors import ListedColormap\n",
    "%matplotlib inline\n",
    "\n",
    "cm = plt.cm.RdBu\n",
    "cm_bright = ListedColormap(['#FF0000', '#0000FF'])\n",
    "\n",
    "import warnings\n",
    "warnings.filterwarnings('ignore')\n",
    "\n",
    "from sklearn.preprocessing import LabelEncoder, StandardScaler\n",
    "from sklearn.model_selection import train_test_split, GridSearchCV\n",
    "from sklearn.feature_selection import f_classif, mutual_info_classif\n",
    "from sklearn.metrics import mean_squared_error\n",
    "from sklearn.linear_model import LinearRegression\n",
    "from sklearn.tree import DecisionTreeRegressor\n",
    "from sklearn.ensemble import RandomForestRegressor\n",
    "\n",
    "import xgboost as xgb"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "# Análisis exploratorio"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 2,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "(87438, 43)"
      ]
     },
     "execution_count": 2,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "data = pd.read_csv('Data/ds_index_next.csv')\n",
    "data.shape"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 3,
   "metadata": {},
   "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>YEAR</th>\n",
       "      <th>CODE</th>\n",
       "      <th>VQ_MARKET_CAP</th>\n",
       "      <th>CSR__ROE</th>\n",
       "      <th>VR__EV_TO_EBIT</th>\n",
       "      <th>CSR__ROC_JOEL_GREENBLATT</th>\n",
       "      <th>CSR__OPERATING_MARGIN</th>\n",
       "      <th>VQ__PIOTROSKI_F_SCORE</th>\n",
       "      <th>VR__PS_RATIO</th>\n",
       "      <th>VR__PB_RATIO</th>\n",
       "      <th>...</th>\n",
       "      <th>RISK</th>\n",
       "      <th>INDUSTRY01</th>\n",
       "      <th>INDUSTRY02</th>\n",
       "      <th>RETURNS_NEXT</th>\n",
       "      <th>RETURNS_CAT</th>\n",
       "      <th>RETURNS_NEXT_CAT</th>\n",
       "      <th>INDEX_RETURNS</th>\n",
       "      <th>INDEX_RETURNS_NEXT</th>\n",
       "      <th>RETURNS_vs_INDEX</th>\n",
       "      <th>RETURNS_vs_INDEX_NEXT</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>2014</td>\n",
       "      <td>ETE</td>\n",
       "      <td>31002.7009</td>\n",
       "      <td>72.22</td>\n",
       "      <td>28.98</td>\n",
       "      <td>8.00</td>\n",
       "      <td>5.10</td>\n",
       "      <td>7</td>\n",
       "      <td>0.56</td>\n",
       "      <td>46.73</td>\n",
       "      <td>...</td>\n",
       "      <td>3.472920</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>-0.512675</td>\n",
       "      <td>POSITIVE</td>\n",
       "      <td>NEGATIVE</td>\n",
       "      <td>0.123866</td>\n",
       "      <td>-0.006928</td>\n",
       "      <td>WINS</td>\n",
       "      <td>LOSES</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>2010</td>\n",
       "      <td>ETE</td>\n",
       "      <td>8712.4952</td>\n",
       "      <td>317.83</td>\n",
       "      <td>24.53</td>\n",
       "      <td>9.21</td>\n",
       "      <td>15.92</td>\n",
       "      <td>5</td>\n",
       "      <td>1.33</td>\n",
       "      <td>72.37</td>\n",
       "      <td>...</td>\n",
       "      <td>0.730981</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>0.045082</td>\n",
       "      <td>POSITIVE</td>\n",
       "      <td>POSITIVE</td>\n",
       "      <td>0.110019</td>\n",
       "      <td>-0.011220</td>\n",
       "      <td>WINS</td>\n",
       "      <td>WINS</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>2015</td>\n",
       "      <td>ETE</td>\n",
       "      <td>14384.6808</td>\n",
       "      <td>0.00</td>\n",
       "      <td>28.67</td>\n",
       "      <td>5.90</td>\n",
       "      <td>7.28</td>\n",
       "      <td>7</td>\n",
       "      <td>0.41</td>\n",
       "      <td>0.00</td>\n",
       "      <td>...</td>\n",
       "      <td>6.159618</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>0.897959</td>\n",
       "      <td>NEGATIVE</td>\n",
       "      <td>POSITIVE</td>\n",
       "      <td>-0.006928</td>\n",
       "      <td>0.112374</td>\n",
       "      <td>LOSES</td>\n",
       "      <td>WINS</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>2012</td>\n",
       "      <td>ETE</td>\n",
       "      <td>12732.3534</td>\n",
       "      <td>27.89</td>\n",
       "      <td>19.82</td>\n",
       "      <td>11.46</td>\n",
       "      <td>8.02</td>\n",
       "      <td>5</td>\n",
       "      <td>0.73</td>\n",
       "      <td>6.03</td>\n",
       "      <td>...</td>\n",
       "      <td>0.649843</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>0.657980</td>\n",
       "      <td>POSITIVE</td>\n",
       "      <td>POSITIVE</td>\n",
       "      <td>0.116776</td>\n",
       "      <td>0.263905</td>\n",
       "      <td>WINS</td>\n",
       "      <td>WINS</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>2007</td>\n",
       "      <td>ETE</td>\n",
       "      <td>8191.2308</td>\n",
       "      <td>2122.84</td>\n",
       "      <td>25.13</td>\n",
       "      <td>14.74</td>\n",
       "      <td>11.92</td>\n",
       "      <td>5</td>\n",
       "      <td>1.16</td>\n",
       "      <td>0.00</td>\n",
       "      <td>...</td>\n",
       "      <td>0.797453</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>-0.502987</td>\n",
       "      <td>POSITIVE</td>\n",
       "      <td>NEGATIVE</td>\n",
       "      <td>0.036538</td>\n",
       "      <td>-0.375847</td>\n",
       "      <td>WINS</td>\n",
       "      <td>LOSES</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "<p>5 rows × 43 columns</p>\n",
       "</div>"
      ],
      "text/plain": [
       "   YEAR CODE  VQ_MARKET_CAP  CSR__ROE  VR__EV_TO_EBIT  \\\n",
       "0  2014  ETE     31002.7009     72.22           28.98   \n",
       "1  2010  ETE      8712.4952    317.83           24.53   \n",
       "2  2015  ETE     14384.6808      0.00           28.67   \n",
       "3  2012  ETE     12732.3534     27.89           19.82   \n",
       "4  2007  ETE      8191.2308   2122.84           25.13   \n",
       "\n",
       "   CSR__ROC_JOEL_GREENBLATT  CSR__OPERATING_MARGIN  VQ__PIOTROSKI_F_SCORE  \\\n",
       "0                      8.00                   5.10                      7   \n",
       "1                      9.21                  15.92                      5   \n",
       "2                      5.90                   7.28                      7   \n",
       "3                     11.46                   8.02                      5   \n",
       "4                     14.74                  11.92                      5   \n",
       "\n",
       "   VR__PS_RATIO  VR__PB_RATIO          ...                RISK  INDUSTRY01  \\\n",
       "0          0.56         46.73          ...            3.472920         NaN   \n",
       "1          1.33         72.37          ...            0.730981         NaN   \n",
       "2          0.41          0.00          ...            6.159618         NaN   \n",
       "3          0.73          6.03          ...            0.649843         NaN   \n",
       "4          1.16          0.00          ...            0.797453         NaN   \n",
       "\n",
       "   INDUSTRY02  RETURNS_NEXT  RETURNS_CAT  RETURNS_NEXT_CAT  INDEX_RETURNS  \\\n",
       "0         NaN     -0.512675     POSITIVE          NEGATIVE       0.123866   \n",
       "1         NaN      0.045082     POSITIVE          POSITIVE       0.110019   \n",
       "2         NaN      0.897959     NEGATIVE          POSITIVE      -0.006928   \n",
       "3         NaN      0.657980     POSITIVE          POSITIVE       0.116776   \n",
       "4         NaN     -0.502987     POSITIVE          NEGATIVE       0.036538   \n",
       "\n",
       "   INDEX_RETURNS_NEXT  RETURNS_vs_INDEX  RETURNS_vs_INDEX_NEXT  \n",
       "0           -0.006928              WINS                  LOSES  \n",
       "1           -0.011220              WINS                   WINS  \n",
       "2            0.112374             LOSES                   WINS  \n",
       "3            0.263905              WINS                   WINS  \n",
       "4           -0.375847              WINS                  LOSES  \n",
       "\n",
       "[5 rows x 43 columns]"
      ]
     },
     "execution_count": 3,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "data.head()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## Preprocesamiento de variables"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 4,
   "metadata": {},
   "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>YEAR</th>\n",
       "      <th>CODE</th>\n",
       "      <th>CSR__ROE</th>\n",
       "      <th>VR__EV_TO_EBIT</th>\n",
       "      <th>CSR__ROC_JOEL_GREENBLATT</th>\n",
       "      <th>CSR__OPERATING_MARGIN</th>\n",
       "      <th>VQ__PIOTROSKI_F_SCORE</th>\n",
       "      <th>VR__PS_RATIO</th>\n",
       "      <th>VR__PB_RATIO</th>\n",
       "      <th>VQ__ALTMAN_Z_SCORE</th>\n",
       "      <th>...</th>\n",
       "      <th>BS__TOTAL_EQUITY</th>\n",
       "      <th>IS__EBITDA</th>\n",
       "      <th>BS__TOTAL_ASSETS</th>\n",
       "      <th>BS__LONG_TERM_DEBT</th>\n",
       "      <th>BS__TOTAL_CURRENT_LIABILITIES</th>\n",
       "      <th>IS_EPS_BASIC</th>\n",
       "      <th>VR_PE_RATIO</th>\n",
       "      <th>RETURNS</th>\n",
       "      <th>RISK</th>\n",
       "      <th>RETURNS_NEXT</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>2014</td>\n",
       "      <td>ETE</td>\n",
       "      <td>72.22</td>\n",
       "      <td>28.98</td>\n",
       "      <td>8.00</td>\n",
       "      <td>5.10</td>\n",
       "      <td>7</td>\n",
       "      <td>0.56</td>\n",
       "      <td>46.73</td>\n",
       "      <td>1.44</td>\n",
       "      <td>...</td>\n",
       "      <td>22329.000</td>\n",
       "      <td>4564.00</td>\n",
       "      <td>64279.000</td>\n",
       "      <td>29477.000</td>\n",
       "      <td>6683.000</td>\n",
       "      <td>0.580</td>\n",
       "      <td>50.33</td>\n",
       "      <td>0.392368</td>\n",
       "      <td>3.472920</td>\n",
       "      <td>-0.512675</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>2010</td>\n",
       "      <td>ETE</td>\n",
       "      <td>317.83</td>\n",
       "      <td>24.53</td>\n",
       "      <td>9.21</td>\n",
       "      <td>15.92</td>\n",
       "      <td>5</td>\n",
       "      <td>1.33</td>\n",
       "      <td>72.37</td>\n",
       "      <td>1.05</td>\n",
       "      <td>...</td>\n",
       "      <td>6247.732</td>\n",
       "      <td>1390.00</td>\n",
       "      <td>17378.730</td>\n",
       "      <td>9346.067</td>\n",
       "      <td>1081.075</td>\n",
       "      <td>0.215</td>\n",
       "      <td>45.44</td>\n",
       "      <td>0.245752</td>\n",
       "      <td>0.730981</td>\n",
       "      <td>0.045082</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>2015</td>\n",
       "      <td>ETE</td>\n",
       "      <td>0.00</td>\n",
       "      <td>28.67</td>\n",
       "      <td>5.90</td>\n",
       "      <td>7.28</td>\n",
       "      <td>7</td>\n",
       "      <td>0.41</td>\n",
       "      <td>0.00</td>\n",
       "      <td>0.81</td>\n",
       "      <td>...</td>\n",
       "      <td>23613.000</td>\n",
       "      <td>4577.00</td>\n",
       "      <td>71189.000</td>\n",
       "      <td>36837.000</td>\n",
       "      <td>4910.000</td>\n",
       "      <td>1.110</td>\n",
       "      <td>12.27</td>\n",
       "      <td>-0.512675</td>\n",
       "      <td>6.159618</td>\n",
       "      <td>0.897959</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>2012</td>\n",
       "      <td>ETE</td>\n",
       "      <td>27.89</td>\n",
       "      <td>19.82</td>\n",
       "      <td>11.46</td>\n",
       "      <td>8.02</td>\n",
       "      <td>5</td>\n",
       "      <td>0.73</td>\n",
       "      <td>6.03</td>\n",
       "      <td>0.74</td>\n",
       "      <td>...</td>\n",
       "      <td>16350.000</td>\n",
       "      <td>3326.00</td>\n",
       "      <td>48904.000</td>\n",
       "      <td>21440.000</td>\n",
       "      <td>5845.000</td>\n",
       "      <td>0.285</td>\n",
       "      <td>37.04</td>\n",
       "      <td>0.120197</td>\n",
       "      <td>0.649843</td>\n",
       "      <td>0.657980</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>2007</td>\n",
       "      <td>ETE</td>\n",
       "      <td>2122.84</td>\n",
       "      <td>25.13</td>\n",
       "      <td>14.74</td>\n",
       "      <td>11.92</td>\n",
       "      <td>5</td>\n",
       "      <td>1.16</td>\n",
       "      <td>0.00</td>\n",
       "      <td>1.52</td>\n",
       "      <td>...</td>\n",
       "      <td>2091.156</td>\n",
       "      <td>1007.41</td>\n",
       "      <td>9462.094</td>\n",
       "      <td>5916.585</td>\n",
       "      <td>1241.433</td>\n",
       "      <td>0.390</td>\n",
       "      <td>30.33</td>\n",
       "      <td>0.144703</td>\n",
       "      <td>0.797453</td>\n",
       "      <td>-0.502987</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "<p>5 rows × 32 columns</p>\n",
       "</div>"
      ],
      "text/plain": [
       "   YEAR CODE  CSR__ROE  VR__EV_TO_EBIT  CSR__ROC_JOEL_GREENBLATT  \\\n",
       "0  2014  ETE     72.22           28.98                      8.00   \n",
       "1  2010  ETE    317.83           24.53                      9.21   \n",
       "2  2015  ETE      0.00           28.67                      5.90   \n",
       "3  2012  ETE     27.89           19.82                     11.46   \n",
       "4  2007  ETE   2122.84           25.13                     14.74   \n",
       "\n",
       "   CSR__OPERATING_MARGIN  VQ__PIOTROSKI_F_SCORE  VR__PS_RATIO  VR__PB_RATIO  \\\n",
       "0                   5.10                      7          0.56         46.73   \n",
       "1                  15.92                      5          1.33         72.37   \n",
       "2                   7.28                      7          0.41          0.00   \n",
       "3                   8.02                      5          0.73          6.03   \n",
       "4                  11.92                      5          1.16          0.00   \n",
       "\n",
       "   VQ__ALTMAN_Z_SCORE      ...       BS__TOTAL_EQUITY  IS__EBITDA  \\\n",
       "0                1.44      ...              22329.000     4564.00   \n",
       "1                1.05      ...               6247.732     1390.00   \n",
       "2                0.81      ...              23613.000     4577.00   \n",
       "3                0.74      ...              16350.000     3326.00   \n",
       "4                1.52      ...               2091.156     1007.41   \n",
       "\n",
       "   BS__TOTAL_ASSETS  BS__LONG_TERM_DEBT  BS__TOTAL_CURRENT_LIABILITIES  \\\n",
       "0         64279.000           29477.000                       6683.000   \n",
       "1         17378.730            9346.067                       1081.075   \n",
       "2         71189.000           36837.000                       4910.000   \n",
       "3         48904.000           21440.000                       5845.000   \n",
       "4          9462.094            5916.585                       1241.433   \n",
       "\n",
       "   IS_EPS_BASIC  VR_PE_RATIO   RETURNS      RISK  RETURNS_NEXT  \n",
       "0         0.580        50.33  0.392368  3.472920     -0.512675  \n",
       "1         0.215        45.44  0.245752  0.730981      0.045082  \n",
       "2         1.110        12.27 -0.512675  6.159618      0.897959  \n",
       "3         0.285        37.04  0.120197  0.649843      0.657980  \n",
       "4         0.390        30.33  0.144703  0.797453     -0.502987  \n",
       "\n",
       "[5 rows x 32 columns]"
      ]
     },
     "execution_count": 4,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "gb_plus = data[['YEAR', 'CODE', 'CSR__ROE', 'VR__EV_TO_EBIT', 'CSR__ROC_JOEL_GREENBLATT',\n",
    "                'CSR__OPERATING_MARGIN', 'VQ__PIOTROSKI_F_SCORE', 'VR__PS_RATIO', 'VR__PB_RATIO',\n",
    "                'VQ__ALTMAN_Z_SCORE', 'CSR__ROA', 'PSDA__EARNINGS_PER_SHARE_DILUTED', 'CSR__ROIC',\n",
    "                'CSR__GROSS_MARGIN', 'VQ__YOY_EPS_GROWTH', 'VQ__YOY_EBITDA_GROWTH',\n",
    "                'PSDA__EBITDA_PER_SHARE', 'PSDA__TOTAL_DEBT_PER_SHARE', 'CSR__NET_MARGIN',\n",
    "                'IS__REVENUE', 'IS__NET_INCOME', 'IS__COST_OF_GOODS_SOLD', 'BS__TOTAL_EQUITY',\n",
    "                'IS__EBITDA', 'BS__TOTAL_ASSETS', 'BS__LONG_TERM_DEBT', 'BS__TOTAL_CURRENT_LIABILITIES',\n",
    "                'IS_EPS_BASIC', 'VR_PE_RATIO', 'RETURNS', 'RISK', 'RETURNS_NEXT']]\n",
    "gb_plus.head()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 5,
   "metadata": {},
   "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>YEAR</th>\n",
       "      <th>CODE</th>\n",
       "      <th>CSR__ROE</th>\n",
       "      <th>VR__EV_TO_EBIT</th>\n",
       "      <th>CSR__ROC_JOEL_GREENBLATT</th>\n",
       "      <th>CSR__OPERATING_MARGIN</th>\n",
       "      <th>VQ__PIOTROSKI_F_SCORE</th>\n",
       "      <th>VR__PS_RATIO</th>\n",
       "      <th>VR__PB_RATIO</th>\n",
       "      <th>VQ__ALTMAN_Z_SCORE</th>\n",
       "      <th>...</th>\n",
       "      <th>IS__EBITDA</th>\n",
       "      <th>BS__TOTAL_ASSETS</th>\n",
       "      <th>BS__LONG_TERM_DEBT</th>\n",
       "      <th>BS__TOTAL_CURRENT_LIABILITIES</th>\n",
       "      <th>IS_EPS_BASIC</th>\n",
       "      <th>VR_PE_RATIO</th>\n",
       "      <th>RETURNS</th>\n",
       "      <th>RISK</th>\n",
       "      <th>RETURNS_NEXT</th>\n",
       "      <th>RETURNS_NEXT_RANKING</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>2014</td>\n",
       "      <td>ETE</td>\n",
       "      <td>72.22</td>\n",
       "      <td>28.98</td>\n",
       "      <td>8.00</td>\n",
       "      <td>5.10</td>\n",
       "      <td>7</td>\n",
       "      <td>0.56</td>\n",
       "      <td>46.73</td>\n",
       "      <td>1.44</td>\n",
       "      <td>...</td>\n",
       "      <td>4564.00</td>\n",
       "      <td>64279.000</td>\n",
       "      <td>29477.000</td>\n",
       "      <td>6683.000</td>\n",
       "      <td>0.580</td>\n",
       "      <td>50.33</td>\n",
       "      <td>0.392368</td>\n",
       "      <td>3.472920</td>\n",
       "      <td>-0.512675</td>\n",
       "      <td>3510.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>2010</td>\n",
       "      <td>ETE</td>\n",
       "      <td>317.83</td>\n",
       "      <td>24.53</td>\n",
       "      <td>9.21</td>\n",
       "      <td>15.92</td>\n",
       "      <td>5</td>\n",
       "      <td>1.33</td>\n",
       "      <td>72.37</td>\n",
       "      <td>1.05</td>\n",
       "      <td>...</td>\n",
       "      <td>1390.00</td>\n",
       "      <td>17378.730</td>\n",
       "      <td>9346.067</td>\n",
       "      <td>1081.075</td>\n",
       "      <td>0.215</td>\n",
       "      <td>45.44</td>\n",
       "      <td>0.245752</td>\n",
       "      <td>0.730981</td>\n",
       "      <td>0.045082</td>\n",
       "      <td>809.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>2015</td>\n",
       "      <td>ETE</td>\n",
       "      <td>0.00</td>\n",
       "      <td>28.67</td>\n",
       "      <td>5.90</td>\n",
       "      <td>7.28</td>\n",
       "      <td>7</td>\n",
       "      <td>0.41</td>\n",
       "      <td>0.00</td>\n",
       "      <td>0.81</td>\n",
       "      <td>...</td>\n",
       "      <td>4577.00</td>\n",
       "      <td>71189.000</td>\n",
       "      <td>36837.000</td>\n",
       "      <td>4910.000</td>\n",
       "      <td>1.110</td>\n",
       "      <td>12.27</td>\n",
       "      <td>-0.512675</td>\n",
       "      <td>6.159618</td>\n",
       "      <td>0.897959</td>\n",
       "      <td>283.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>2012</td>\n",
       "      <td>ETE</td>\n",
       "      <td>27.89</td>\n",
       "      <td>19.82</td>\n",
       "      <td>11.46</td>\n",
       "      <td>8.02</td>\n",
       "      <td>5</td>\n",
       "      <td>0.73</td>\n",
       "      <td>6.03</td>\n",
       "      <td>0.74</td>\n",
       "      <td>...</td>\n",
       "      <td>3326.00</td>\n",
       "      <td>48904.000</td>\n",
       "      <td>21440.000</td>\n",
       "      <td>5845.000</td>\n",
       "      <td>0.285</td>\n",
       "      <td>37.04</td>\n",
       "      <td>0.120197</td>\n",
       "      <td>0.649843</td>\n",
       "      <td>0.657980</td>\n",
       "      <td>560.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>2007</td>\n",
       "      <td>ETE</td>\n",
       "      <td>2122.84</td>\n",
       "      <td>25.13</td>\n",
       "      <td>14.74</td>\n",
       "      <td>11.92</td>\n",
       "      <td>5</td>\n",
       "      <td>1.16</td>\n",
       "      <td>0.00</td>\n",
       "      <td>1.52</td>\n",
       "      <td>...</td>\n",
       "      <td>1007.41</td>\n",
       "      <td>9462.094</td>\n",
       "      <td>5916.585</td>\n",
       "      <td>1241.433</td>\n",
       "      <td>0.390</td>\n",
       "      <td>30.33</td>\n",
       "      <td>0.144703</td>\n",
       "      <td>0.797453</td>\n",
       "      <td>-0.502987</td>\n",
       "      <td>993.0</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "<p>5 rows × 33 columns</p>\n",
       "</div>"
      ],
      "text/plain": [
       "   YEAR CODE  CSR__ROE  VR__EV_TO_EBIT  CSR__ROC_JOEL_GREENBLATT  \\\n",
       "0  2014  ETE     72.22           28.98                      8.00   \n",
       "1  2010  ETE    317.83           24.53                      9.21   \n",
       "2  2015  ETE      0.00           28.67                      5.90   \n",
       "3  2012  ETE     27.89           19.82                     11.46   \n",
       "4  2007  ETE   2122.84           25.13                     14.74   \n",
       "\n",
       "   CSR__OPERATING_MARGIN  VQ__PIOTROSKI_F_SCORE  VR__PS_RATIO  VR__PB_RATIO  \\\n",
       "0                   5.10                      7          0.56         46.73   \n",
       "1                  15.92                      5          1.33         72.37   \n",
       "2                   7.28                      7          0.41          0.00   \n",
       "3                   8.02                      5          0.73          6.03   \n",
       "4                  11.92                      5          1.16          0.00   \n",
       "\n",
       "   VQ__ALTMAN_Z_SCORE          ...           IS__EBITDA  BS__TOTAL_ASSETS  \\\n",
       "0                1.44          ...              4564.00         64279.000   \n",
       "1                1.05          ...              1390.00         17378.730   \n",
       "2                0.81          ...              4577.00         71189.000   \n",
       "3                0.74          ...              3326.00         48904.000   \n",
       "4                1.52          ...              1007.41          9462.094   \n",
       "\n",
       "   BS__LONG_TERM_DEBT  BS__TOTAL_CURRENT_LIABILITIES  IS_EPS_BASIC  \\\n",
       "0           29477.000                       6683.000         0.580   \n",
       "1            9346.067                       1081.075         0.215   \n",
       "2           36837.000                       4910.000         1.110   \n",
       "3           21440.000                       5845.000         0.285   \n",
       "4            5916.585                       1241.433         0.390   \n",
       "\n",
       "   VR_PE_RATIO   RETURNS      RISK  RETURNS_NEXT  RETURNS_NEXT_RANKING  \n",
       "0        50.33  0.392368  3.472920     -0.512675                3510.0  \n",
       "1        45.44  0.245752  0.730981      0.045082                 809.0  \n",
       "2        12.27 -0.512675  6.159618      0.897959                 283.0  \n",
       "3        37.04  0.120197  0.649843      0.657980                 560.0  \n",
       "4        30.33  0.144703  0.797453     -0.502987                 993.0  \n",
       "\n",
       "[5 rows x 33 columns]"
      ]
     },
     "execution_count": 5,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "gb_plus.query('YEAR > 1990 & YEAR < 2016', inplace=True)\n",
    "gb_plus.dropna(inplace=True)\n",
    "gb_plus['RETURNS_NEXT_RANKING'] = gb_plus.groupby(by=['YEAR'])['RETURNS_NEXT'] \\\n",
    "        .transform(lambda x: x.rank(method='dense', ascending=False))\n",
    "gb_plus.head()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "# Aproximación 1"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 6,
   "metadata": {},
   "outputs": [],
   "source": [
    "gb3 = gb_plus.drop(['CODE', 'RETURNS_NEXT'], axis=1)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 7,
   "metadata": {},
   "outputs": [],
   "source": [
    "for c in gb3.columns:\n",
    "    if c not in ['YEAR', 'VQ__PIOTROSKI_F_SCORE', 'RETURNS_NEXT_RANKING']:\n",
    "        for y in gb3['YEAR'].unique():\n",
    "            tmp = gb3.query('YEAR == @y')[c]\n",
    "            q1 = tmp.quantile(0.25)\n",
    "            q3 = tmp.quantile(0.75)\n",
    "            iqr = q3-q1 #Interquartile range\n",
    "            fence_low  = q1-1.5*iqr\n",
    "            fence_high = q3+1.5*iqr\n",
    "\n",
    "            gb3.drop(gb3.loc[(gb3['YEAR'] == y) &\n",
    "                             ((gb3[c] < fence_low) |\n",
    "                              (gb3[c] > fence_high))].index, inplace=True)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 8,
   "metadata": {},
   "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>YEAR</th>\n",
       "      <th>VQ__PIOTROSKI_F_SCORE</th>\n",
       "      <th>RETURNS_NEXT_RANKING</th>\n",
       "      <th>CSR__ROE_RANKING</th>\n",
       "      <th>VR__EV_TO_EBIT_RANKING</th>\n",
       "      <th>CSR__ROC_JOEL_GREENBLATT_RANKING</th>\n",
       "      <th>CSR__OPERATING_MARGIN_RANKING</th>\n",
       "      <th>VR__PS_RATIO_RANKING</th>\n",
       "      <th>VR__PB_RATIO_RANKING</th>\n",
       "      <th>VQ__ALTMAN_Z_SCORE_RANKING</th>\n",
       "      <th>...</th>\n",
       "      <th>IS__COST_OF_GOODS_SOLD_RANKING</th>\n",
       "      <th>BS__TOTAL_EQUITY_RANKING</th>\n",
       "      <th>IS__EBITDA_RANKING</th>\n",
       "      <th>BS__TOTAL_ASSETS_RANKING</th>\n",
       "      <th>BS__LONG_TERM_DEBT_RANKING</th>\n",
       "      <th>BS__TOTAL_CURRENT_LIABILITIES_RANKING</th>\n",
       "      <th>IS_EPS_BASIC_RANKING</th>\n",
       "      <th>VR_PE_RATIO_RANKING</th>\n",
       "      <th>RETURNS_RANKING</th>\n",
       "      <th>RISK_RANKING</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>13</th>\n",
       "      <td>2009</td>\n",
       "      <td>4</td>\n",
       "      <td>2564.0</td>\n",
       "      <td>225.0</td>\n",
       "      <td>189.0</td>\n",
       "      <td>127.0</td>\n",
       "      <td>140.0</td>\n",
       "      <td>143.0</td>\n",
       "      <td>153.0</td>\n",
       "      <td>177.0</td>\n",
       "      <td>...</td>\n",
       "      <td>213.0</td>\n",
       "      <td>210.0</td>\n",
       "      <td>222.0</td>\n",
       "      <td>220.0</td>\n",
       "      <td>181.0</td>\n",
       "      <td>225.0</td>\n",
       "      <td>203.0</td>\n",
       "      <td>216.0</td>\n",
       "      <td>54.0</td>\n",
       "      <td>18.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>37</th>\n",
       "      <td>2008</td>\n",
       "      <td>6</td>\n",
       "      <td>136.0</td>\n",
       "      <td>193.0</td>\n",
       "      <td>14.0</td>\n",
       "      <td>211.0</td>\n",
       "      <td>198.0</td>\n",
       "      <td>116.0</td>\n",
       "      <td>140.0</td>\n",
       "      <td>112.0</td>\n",
       "      <td>...</td>\n",
       "      <td>186.0</td>\n",
       "      <td>210.0</td>\n",
       "      <td>217.0</td>\n",
       "      <td>208.0</td>\n",
       "      <td>128.0</td>\n",
       "      <td>202.0</td>\n",
       "      <td>156.0</td>\n",
       "      <td>11.0</td>\n",
       "      <td>175.0</td>\n",
       "      <td>209.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>44</th>\n",
       "      <td>1998</td>\n",
       "      <td>5</td>\n",
       "      <td>401.0</td>\n",
       "      <td>91.0</td>\n",
       "      <td>55.0</td>\n",
       "      <td>79.0</td>\n",
       "      <td>108.0</td>\n",
       "      <td>73.0</td>\n",
       "      <td>80.0</td>\n",
       "      <td>49.0</td>\n",
       "      <td>...</td>\n",
       "      <td>88.0</td>\n",
       "      <td>103.0</td>\n",
       "      <td>103.0</td>\n",
       "      <td>107.0</td>\n",
       "      <td>79.0</td>\n",
       "      <td>101.0</td>\n",
       "      <td>86.0</td>\n",
       "      <td>60.0</td>\n",
       "      <td>113.0</td>\n",
       "      <td>89.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>46</th>\n",
       "      <td>1997</td>\n",
       "      <td>4</td>\n",
       "      <td>956.0</td>\n",
       "      <td>96.0</td>\n",
       "      <td>4.0</td>\n",
       "      <td>92.0</td>\n",
       "      <td>108.0</td>\n",
       "      <td>64.0</td>\n",
       "      <td>39.0</td>\n",
       "      <td>26.0</td>\n",
       "      <td>...</td>\n",
       "      <td>90.0</td>\n",
       "      <td>104.0</td>\n",
       "      <td>108.0</td>\n",
       "      <td>108.0</td>\n",
       "      <td>80.0</td>\n",
       "      <td>101.0</td>\n",
       "      <td>95.0</td>\n",
       "      <td>1.0</td>\n",
       "      <td>28.0</td>\n",
       "      <td>88.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>79</th>\n",
       "      <td>2006</td>\n",
       "      <td>6</td>\n",
       "      <td>132.0</td>\n",
       "      <td>3.0</td>\n",
       "      <td>62.0</td>\n",
       "      <td>20.0</td>\n",
       "      <td>137.0</td>\n",
       "      <td>100.0</td>\n",
       "      <td>17.0</td>\n",
       "      <td>109.0</td>\n",
       "      <td>...</td>\n",
       "      <td>126.0</td>\n",
       "      <td>158.0</td>\n",
       "      <td>151.0</td>\n",
       "      <td>146.0</td>\n",
       "      <td>116.0</td>\n",
       "      <td>104.0</td>\n",
       "      <td>106.0</td>\n",
       "      <td>99.0</td>\n",
       "      <td>56.0</td>\n",
       "      <td>169.0</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "<p>5 rows × 31 columns</p>\n",
       "</div>"
      ],
      "text/plain": [
       "    YEAR  VQ__PIOTROSKI_F_SCORE  RETURNS_NEXT_RANKING  CSR__ROE_RANKING  \\\n",
       "13  2009                      4                2564.0             225.0   \n",
       "37  2008                      6                 136.0             193.0   \n",
       "44  1998                      5                 401.0              91.0   \n",
       "46  1997                      4                 956.0              96.0   \n",
       "79  2006                      6                 132.0               3.0   \n",
       "\n",
       "    VR__EV_TO_EBIT_RANKING  CSR__ROC_JOEL_GREENBLATT_RANKING  \\\n",
       "13                   189.0                             127.0   \n",
       "37                    14.0                             211.0   \n",
       "44                    55.0                              79.0   \n",
       "46                     4.0                              92.0   \n",
       "79                    62.0                              20.0   \n",
       "\n",
       "    CSR__OPERATING_MARGIN_RANKING  VR__PS_RATIO_RANKING  VR__PB_RATIO_RANKING  \\\n",
       "13                          140.0                 143.0                 153.0   \n",
       "37                          198.0                 116.0                 140.0   \n",
       "44                          108.0                  73.0                  80.0   \n",
       "46                          108.0                  64.0                  39.0   \n",
       "79                          137.0                 100.0                  17.0   \n",
       "\n",
       "    VQ__ALTMAN_Z_SCORE_RANKING      ...       IS__COST_OF_GOODS_SOLD_RANKING  \\\n",
       "13                       177.0      ...                                213.0   \n",
       "37                       112.0      ...                                186.0   \n",
       "44                        49.0      ...                                 88.0   \n",
       "46                        26.0      ...                                 90.0   \n",
       "79                       109.0      ...                                126.0   \n",
       "\n",
       "    BS__TOTAL_EQUITY_RANKING  IS__EBITDA_RANKING  BS__TOTAL_ASSETS_RANKING  \\\n",
       "13                     210.0               222.0                     220.0   \n",
       "37                     210.0               217.0                     208.0   \n",
       "44                     103.0               103.0                     107.0   \n",
       "46                     104.0               108.0                     108.0   \n",
       "79                     158.0               151.0                     146.0   \n",
       "\n",
       "    BS__LONG_TERM_DEBT_RANKING  BS__TOTAL_CURRENT_LIABILITIES_RANKING  \\\n",
       "13                       181.0                                  225.0   \n",
       "37                       128.0                                  202.0   \n",
       "44                        79.0                                  101.0   \n",
       "46                        80.0                                  101.0   \n",
       "79                       116.0                                  104.0   \n",
       "\n",
       "    IS_EPS_BASIC_RANKING  VR_PE_RATIO_RANKING  RETURNS_RANKING  RISK_RANKING  \n",
       "13                 203.0                216.0             54.0          18.0  \n",
       "37                 156.0                 11.0            175.0         209.0  \n",
       "44                  86.0                 60.0            113.0          89.0  \n",
       "46                  95.0                  1.0             28.0          88.0  \n",
       "79                 106.0                 99.0             56.0         169.0  \n",
       "\n",
       "[5 rows x 31 columns]"
      ]
     },
     "execution_count": 8,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "for c in gb3.columns:\n",
    "    if c not in ['YEAR', 'VQ__PIOTROSKI_F_SCORE', 'RETURNS_NEXT_RANKING']:\n",
    "        gb3[c + '_RANKING'] = gb3.groupby(by=['YEAR'])[c] \\\n",
    "            .transform(lambda x: x.rank(method='dense', ascending=False))\n",
    "        gb3.drop([c], axis=1, inplace=True)\n",
    "gb3.head()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 9,
   "metadata": {},
   "outputs": [],
   "source": [
    "final = pd.DataFrame()\n",
    "for x in range(2000, 2018):\n",
    "    gbT = gb3.query('YEAR >= @x-5 & YEAR <= @x-1').drop('YEAR', axis=1)\n",
    "    X3 = gbT.drop(['RETURNS_NEXT_RANKING'], axis=1)\n",
    "    y3 = gbT['RETURNS_NEXT_RANKING']\n",
    "\n",
    "    feature_names = X3.columns\n",
    "\n",
    "    scaler = StandardScaler().fit(X3)\n",
    "    X3s = scaler.transform(X3)\n",
    "    \n",
    "    param_dist = {'objective': 'reg:linear',  \n",
    "                  'max_depth': 5,\n",
    "                  'colsample_bytree': 0.7,\n",
    "                  'alpha': 10,\n",
    "                  'learning_rate': 0.15,\n",
    "                  'silent': 1}\n",
    "\n",
    "    n_folds = 3\n",
    "    early_stopping = 10\n",
    "\n",
    "    xg_train = xgb.DMatrix(X3s, label=y3)\n",
    "\n",
    "    cv = xgb.cv(param_dist, xg_train, 500, nfold=n_folds, early_stopping_rounds=early_stopping)\n",
    "    \n",
    "    xg_reg_fold = xgb.train(params=param_dist, dtrain=xg_train, num_boost_round=cv.shape[0])\n",
    "    \n",
    "    save = data[['YEAR', 'CODE', 'CSR__ROE', 'VR__EV_TO_EBIT', 'CSR__ROC_JOEL_GREENBLATT',\n",
    "                 'CSR__OPERATING_MARGIN', 'VQ__PIOTROSKI_F_SCORE', 'VR__PS_RATIO', 'VR__PB_RATIO',\n",
    "                 'VQ__ALTMAN_Z_SCORE', 'CSR__ROA', 'PSDA__EARNINGS_PER_SHARE_DILUTED', 'CSR__ROIC',\n",
    "                 'CSR__GROSS_MARGIN', 'VQ__YOY_EPS_GROWTH', 'VQ__YOY_EBITDA_GROWTH',\n",
    "                 'PSDA__EBITDA_PER_SHARE', 'PSDA__TOTAL_DEBT_PER_SHARE', 'CSR__NET_MARGIN',\n",
    "                 'IS__REVENUE', 'IS__NET_INCOME', 'IS__COST_OF_GOODS_SOLD', 'BS__TOTAL_EQUITY',\n",
    "                 'IS__EBITDA', 'BS__TOTAL_ASSETS', 'BS__LONG_TERM_DEBT', 'BS__TOTAL_CURRENT_LIABILITIES',\n",
    "                 'IS_EPS_BASIC', 'VR_PE_RATIO', 'RETURNS', 'RISK']]\n",
    "    save.query('YEAR == @x', inplace=True)\n",
    "    for c in save.columns:\n",
    "        if c not in ['YEAR', 'VQ__PIOTROSKI_F_SCORE', 'CODE']:\n",
    "            save[c + '_RANKING'] = save.groupby(by=['YEAR'])[c] \\\n",
    "                .transform(lambda x: x.rank(method='dense', ascending=False))\n",
    "            save.drop([c], axis=1, inplace=True)\n",
    "    \n",
    "    result = save.drop(['YEAR', 'CODE'], axis=1)\n",
    "    result.dropna(inplace=True)\n",
    "    \n",
    "    scaler = StandardScaler().fit(result)\n",
    "    result_s = scaler.transform(result)\n",
    "    result_xg = xgb.DMatrix(result_s)\n",
    "    result['prediction'] = xg_reg_fold.predict(result_xg)\n",
    "    \n",
    "    save = pd.concat([data, result['prediction']], axis=1, join='inner')\n",
    "    save['prediction'] = save.groupby(by=['YEAR'])['prediction'] \\\n",
    "        .transform(lambda x: x.rank(method='dense', ascending=True))\n",
    "    save = save[['YEAR', 'CODE', 'VQ_MARKET_CAP', 'IS_EPS_BASIC', 'IS__REVENUE', 'VR_PE_RATIO',\n",
    "                 'VQ__PIOTROSKI_F_SCORE', 'PRICE_START', 'PRICE_END', 'RETURNS', 'RISK', 'prediction']]\n",
    "    save.columns = ['YEAR', 'CODE', 'CAP', 'EPS', 'REVENUE', 'PER', 'PIOTROSKI',\n",
    "                    'PRICE_START', 'PRICE_END', 'RETURNS', 'RISK', 'RANKING']\n",
    "    save.sort_values(['YEAR', 'RANKING'], ascending=[True, True], inplace=True)\n",
    "    \n",
    "    final = final.append(save)\n",
    "\n",
    "final.to_csv('Results/metrics_XGB_KF_W5_ALLRANK.csv', index=False)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "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.6.6"
  },
  "toc": {
   "base_numbering": 1,
   "nav_menu": {},
   "number_sections": true,
   "sideBar": false,
   "skip_h1_title": false,
   "title_cell": "Table of Contents",
   "title_sidebar": "Contents",
   "toc_cell": false,
   "toc_position": {
    "height": "398px",
    "left": "1024px",
    "top": "110px",
    "width": "256px"
   },
   "toc_section_display": true,
   "toc_window_display": true
  }
 },
 "nbformat": 4,
 "nbformat_minor": 2
}