Tabla 65 columnas count-Copy1.ipynb

{
 "cells": [
  {
   "cell_type": "code",
   "execution_count": 1,
   "metadata": {},
   "outputs": [],
   "source": [
    "import numpy as np\n",
    "import pandas as pd\n",
    "from sklearn import preprocessing\n",
    "import category_encoders as ce\n",
    "\n",
    "from sklearn.model_selection import train_test_split\n",
    "from sklearn.metrics import accuracy_score\n",
    "import numpy as np\n",
    "from sklearn.metrics import mean_squared_error\n",
    "from sklearn.ensemble import RandomForestClassifier\n",
    "import random\n",
    "import xgboost as xgb"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 2,
   "metadata": {},
   "outputs": [
    {
     "name": "stderr",
     "output_type": "stream",
     "text": [
      "C:\\Users\\Santi\\AppData\\Roaming\\Python\\Python36\\site-packages\\IPython\\core\\interactiveshell.py:2728: DtypeWarning: Columns (3,5,6,7,8,9,10,11,12,13,14,15,16,17,18,19,20,21,22) have mixed types. Specify dtype option on import or set low_memory=False.\n",
      "  interactivity=interactivity, compiler=compiler, result=result)\n"
     ]
    }
   ],
   "source": [
    "datos = pd.read_csv('events_up_to_01062018.csv')\n",
    "tabla_completa = datos.copy()\n",
    "res = pd.read_csv('labels_training_set.csv');"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 3,
   "metadata": {},
   "outputs": [],
   "source": [
    "tabla_completa['aux']=1\n",
    "#Voy a contar la cantidad para cada tipo de evento de cada persona\n",
    "num_eventos = tabla_completa.groupby(['person','event'])['aux'].sum()\n",
    "\n",
    "num_eventos = num_eventos.unstack().reset_index().fillna(0)\n",
    "num_eventos = num_eventos.set_index('person')\n",
    "num_eventos['total_eventos'] = num_eventos.sum(axis=1)\n",
    "#normalizo los numeros obtenidos según la cantidad de eventos totales de esa persona\n",
    "num_eventos_norm = num_eventos.loc[:,\"ad campaign hit\":\"visited site\"].div(num_eventos[\"total_eventos\"], axis=0)\n",
    "num_eventos_norm = num_eventos_norm.reset_index()\n",
    "num_eventos_norm.head()\n",
    "\n",
    "tabla_completa['timestamp'] = pd.to_datetime(tabla_completa['timestamp'])\n",
    "#tabla_completa['timestamp'].max() -tabla_completa['timestamp'].min()\n",
    "\n",
    "time_for_user = tabla_completa.groupby(['person'])['timestamp'].apply(lambda x: x.max() - x.min())\n",
    "time_for_user = time_for_user.reset_index()\n",
    "time_for_user['total_horas'] = time_for_user['timestamp'].dt.total_seconds()/3600\n",
    "time_for_user = time_for_user.drop(['timestamp'],axis='columns')\n",
    "\n",
    "max_time = tabla_completa.groupby(['person'])['timestamp'].apply(lambda x: x.max())\n",
    "max_time = max_time.reset_index()\n",
    "max_time['max'] = max_time['timestamp'].dt.month\n",
    "max_time = max_time.drop(['timestamp'],axis='columns')\n",
    "\n",
    "time_for_user = pd.merge(max_time,time_for_user,on='person',how='inner')\n",
    "\n",
    "min_time = tabla_completa.groupby(['person'])['timestamp'].apply(lambda x: x.min())\n",
    "min_time = min_time.reset_index()\n",
    "min_time['min'] = min_time['timestamp'].dt.month\n",
    "min_time = min_time.drop(['timestamp'],axis='columns')\n",
    "\n",
    "time_for_user = pd.merge(min_time,time_for_user,on='person',how='inner')\n",
    "\n",
    "num_eventos_norm = num_eventos_norm.reset_index()\n",
    "num_eventos = num_eventos.reset_index()\n",
    "total_eventos = pd.merge(num_eventos,num_eventos_norm, on='person',how='inner')\n",
    "total_eventos = pd.merge(total_eventos,time_for_user,on='person',how='inner')"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 4,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "(38829, 28)"
      ]
     },
     "execution_count": 4,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "total_eventos.shape"
   ]
  },
  {
   "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>timestamp</th>\n",
       "      <th>event</th>\n",
       "      <th>person</th>\n",
       "      <th>url</th>\n",
       "      <th>sku</th>\n",
       "      <th>model</th>\n",
       "      <th>condition</th>\n",
       "      <th>storage</th>\n",
       "      <th>color</th>\n",
       "      <th>skus</th>\n",
       "      <th>...</th>\n",
       "      <th>search_engine</th>\n",
       "      <th>channel</th>\n",
       "      <th>new_vs_returning</th>\n",
       "      <th>city</th>\n",
       "      <th>region</th>\n",
       "      <th>country</th>\n",
       "      <th>device_type</th>\n",
       "      <th>screen_resolution</th>\n",
       "      <th>operating_system_version</th>\n",
       "      <th>browser_version</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>2018-05-18 00:11:59</td>\n",
       "      <td>viewed product</td>\n",
       "      <td>4886f805</td>\n",
       "      <td>NaN</td>\n",
       "      <td>9288.0</td>\n",
       "      <td>Samsung Galaxy J7 Prime</td>\n",
       "      <td>Excelente</td>\n",
       "      <td>32GB</td>\n",
       "      <td>Dourado</td>\n",
       "      <td>NaN</td>\n",
       "      <td>...</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>2018-05-18 00:11:27</td>\n",
       "      <td>viewed product</td>\n",
       "      <td>ad93850f</td>\n",
       "      <td>NaN</td>\n",
       "      <td>304.0</td>\n",
       "      <td>iPhone 5s</td>\n",
       "      <td>Muito Bom</td>\n",
       "      <td>32GB</td>\n",
       "      <td>Cinza espacial</td>\n",
       "      <td>NaN</td>\n",
       "      <td>...</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>2018-05-18 00:11:16</td>\n",
       "      <td>viewed product</td>\n",
       "      <td>0297fc1e</td>\n",
       "      <td>NaN</td>\n",
       "      <td>6888.0</td>\n",
       "      <td>iPhone 6S</td>\n",
       "      <td>Muito Bom</td>\n",
       "      <td>64GB</td>\n",
       "      <td>Prateado</td>\n",
       "      <td>NaN</td>\n",
       "      <td>...</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>2018-05-18 00:11:14</td>\n",
       "      <td>viewed product</td>\n",
       "      <td>2d681dd8</td>\n",
       "      <td>NaN</td>\n",
       "      <td>11890.0</td>\n",
       "      <td>iPhone 7</td>\n",
       "      <td>Bom</td>\n",
       "      <td>128GB</td>\n",
       "      <td>Vermelho</td>\n",
       "      <td>NaN</td>\n",
       "      <td>...</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>2018-05-18 00:11:09</td>\n",
       "      <td>viewed product</td>\n",
       "      <td>cccea85e</td>\n",
       "      <td>NaN</td>\n",
       "      <td>7517.0</td>\n",
       "      <td>LG G4 H818P</td>\n",
       "      <td>Excelente</td>\n",
       "      <td>32GB</td>\n",
       "      <td>Branco</td>\n",
       "      <td>NaN</td>\n",
       "      <td>...</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "<p>5 rows × 23 columns</p>\n",
       "</div>"
      ],
      "text/plain": [
       "             timestamp           event    person  url      sku  \\\n",
       "0  2018-05-18 00:11:59  viewed product  4886f805  NaN   9288.0   \n",
       "1  2018-05-18 00:11:27  viewed product  ad93850f  NaN    304.0   \n",
       "2  2018-05-18 00:11:16  viewed product  0297fc1e  NaN   6888.0   \n",
       "3  2018-05-18 00:11:14  viewed product  2d681dd8  NaN  11890.0   \n",
       "4  2018-05-18 00:11:09  viewed product  cccea85e  NaN   7517.0   \n",
       "\n",
       "                     model  condition storage           color skus  \\\n",
       "0  Samsung Galaxy J7 Prime  Excelente    32GB         Dourado  NaN   \n",
       "1                iPhone 5s  Muito Bom    32GB  Cinza espacial  NaN   \n",
       "2                iPhone 6S  Muito Bom    64GB        Prateado  NaN   \n",
       "3                 iPhone 7        Bom   128GB        Vermelho  NaN   \n",
       "4              LG G4 H818P  Excelente    32GB          Branco  NaN   \n",
       "\n",
       "        ...       search_engine channel new_vs_returning city region country  \\\n",
       "0       ...                 NaN     NaN              NaN  NaN    NaN     NaN   \n",
       "1       ...                 NaN     NaN              NaN  NaN    NaN     NaN   \n",
       "2       ...                 NaN     NaN              NaN  NaN    NaN     NaN   \n",
       "3       ...                 NaN     NaN              NaN  NaN    NaN     NaN   \n",
       "4       ...                 NaN     NaN              NaN  NaN    NaN     NaN   \n",
       "\n",
       "  device_type screen_resolution operating_system_version browser_version  \n",
       "0         NaN               NaN                      NaN             NaN  \n",
       "1         NaN               NaN                      NaN             NaN  \n",
       "2         NaN               NaN                      NaN             NaN  \n",
       "3         NaN               NaN                      NaN             NaN  \n",
       "4         NaN               NaN                      NaN             NaN  \n",
       "\n",
       "[5 rows x 23 columns]"
      ]
     },
     "execution_count": 5,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "tabla_encode = datos.copy()\n",
    "tabla_encode.head()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 6,
   "metadata": {},
   "outputs": [],
   "source": [
    "tabla_encode = tabla_encode.fillna(0)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 7,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "device_type\n",
       "0             2137612\n",
       "Computer        97485\n",
       "Smartphone     103502\n",
       "Tablet           2799\n",
       "Unknown           283\n",
       "Name: person, dtype: int64"
      ]
     },
     "execution_count": 7,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "#device_type\n",
    "tabla_encode.groupby('device_type')['person'].count()\n",
    "#0 no cuenta (es que no hay evento)\n",
    "#son solo 4 tipos distintos, así que los mantengo todos"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 8,
   "metadata": {},
   "outputs": [
    {
     "name": "stderr",
     "output_type": "stream",
     "text": [
      "C:\\Users\\Santi\\AppData\\Roaming\\Python\\Python36\\site-packages\\ipykernel_launcher.py:2: 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",
      "  \n"
     ]
    },
    {
     "data": {
      "text/plain": [
       "(38829, 5)"
      ]
     },
     "execution_count": 8,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "dev_type = tabla_encode[['person','device_type']]\n",
    "dev_type['aux'] = 1\n",
    "dev_type = dev_type.groupby(['person','device_type'])['aux'].count().unstack().reset_index().fillna(0)\n",
    "dev_type = dev_type.drop([0],axis=1)\n",
    "dev_type = dev_type.set_index('person')\n",
    "dev_type = dev_type.add_prefix('device_type_')\n",
    "dev_type = dev_type.reset_index()\n",
    "dev_type.shape"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 9,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "browser_version\n",
       "0                                  2137612\n",
       "Chrome 66.0                          57953\n",
       "Chrome Mobile 66.0                   45589\n",
       "Chrome Mobile 65.0                   15542\n",
       "Chrome 65.0                          14920\n",
       "Chrome Mobile 64.0                    7787\n",
       "Chrome 64.0                           5670\n",
       "Mobile Safari 11                      4501\n",
       "Chrome 63.0                           4358\n",
       "Chrome Mobile 63.0                    4217\n",
       "Samsung Internet 6.4                  2884\n",
       "Firefox 59                            2589\n",
       "Firefox 60                            2298\n",
       "Chrome Mobile 39                      2042\n",
       "Mobile Safari 10                      1970\n",
       "Edge 16.16299                         1574\n",
       "Chrome Mobile 55.0                    1549\n",
       "Chrome Mobile 43.0                    1266\n",
       "Chrome 49.0                           1028\n",
       "IE 11                                  999\n",
       "Chrome Mobile 62.0                     891\n",
       "Samsung Internet 3.3                   820\n",
       "Chrome 67.0                            798\n",
       "Mobile Safari 9                        749\n",
       "Opera 52.0                             720\n",
       "Chrome Mobile 61.0                     708\n",
       "Chrome 58.0                            697\n",
       "Chrome Mobile 56.0                     695\n",
       "Facebook 173                           664\n",
       "Facebook 172                           561\n",
       "                                    ...   \n",
       "BingPreview 1                            1\n",
       "Opera Mobile 28.0                        1\n",
       "Chrome 35.0                              1\n",
       "Chrome 53.19                             1\n",
       "Opera Mini 35.0                          1\n",
       "Opera Mini 31.0                          1\n",
       "Chromium 64.0                            1\n",
       "Facebook 139                             1\n",
       "Facebook 145                             1\n",
       "Facebook 147                             1\n",
       "Facebook 151                             1\n",
       "Facebook 68                              1\n",
       "Firefox 15                               1\n",
       "Firefox 15.0                             1\n",
       "Chrome 57.7                              1\n",
       "Chrome 57.3                              1\n",
       "Chrome 57.21                             1\n",
       "Firefox 24                               1\n",
       "Firefox 26                               1\n",
       "Firefox 28                               1\n",
       "K-Meleon 76                              1\n",
       "Chrome 56.13                             1\n",
       "Mobile Safari 9.2                        1\n",
       "Mobile Safari UI/WKWebView 10.2          1\n",
       "Chrome 48.4                              1\n",
       "Mobile Safari UI/WKWebView 11.3          1\n",
       "Opera 42.0                               1\n",
       "Opera 47.0                               1\n",
       "Opera Mini 28.0                          1\n",
       "Samsung Internet 1.1                     1\n",
       "Name: person, Length: 367, dtype: int64"
      ]
     },
     "execution_count": 9,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "#device_type\n",
    "tabla_encode.groupby('browser_version')['person'].count().sort_values(ascending=False)\n",
    "#0 no cuenta (es que no hay evento)\n",
    "#son 367 tipos distintos. Voy a vovler a mirar, pero uniendo por versiones"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 10,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "3            Chrome\n",
       "4     Chrome Mobile\n",
       "16    Mobile Safari\n",
       "10          Firefox\n",
       "Name: browser_version, dtype: object"
      ]
     },
     "execution_count": 10,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "test_b = tabla_encode['browser_version'].str.replace('\\d+', '')\n",
    "test_b = test_b.str.replace('.', '')\n",
    "test_b = test_b.str.strip()\n",
    "test_b2 = pd.DataFrame(test_b)\n",
    "test_b2['aux']=1\n",
    "test_b2 = test_b2.groupby('browser_version').count().reset_index().sort_values(by='aux',ascending=False)\n",
    "#Ahora me quedan 30 browsers. Voy a quedarme con los 5 más usados. El resto irá a una columna \"otros\".\n",
    "browsers = test_b2['browser_version'].head(4)\n",
    "browsers"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 11,
   "metadata": {},
   "outputs": [
    {
     "name": "stderr",
     "output_type": "stream",
     "text": [
      "C:\\Users\\Santi\\AppData\\Roaming\\Python\\Python36\\site-packages\\ipykernel_launcher.py:6: 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",
      "  \n"
     ]
    },
    {
     "data": {
      "text/plain": [
       "(38242, 6)"
      ]
     },
     "execution_count": 11,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "tabla_encode['browser_version'] = tabla_encode['browser_version'].str.replace('\\d+', '')\n",
    "tabla_encode['browser_version'] = tabla_encode['browser_version'].str.replace('.', '')\n",
    "tabla_encode['browser_version'] = tabla_encode['browser_version'].str.strip()\n",
    "\n",
    "browser_version = tabla_encode[['person','browser_version']]\n",
    "browser_version['aux'] = 1\n",
    "browser_version = browser_version.groupby(['person','browser_version'])['aux'].count().unstack().reset_index().fillna(0)\n",
    "browser_version = browser_version.set_index('person')\n",
    "selected_browser_version = browser_version.loc[:,browsers]\n",
    "selected_browser_version = selected_browser_version.reset_index()\n",
    "\n",
    "\n",
    "other_browser = browser_version.drop(browsers,axis=1)\n",
    "other_browser['Other'] = other_browser.max(axis=1)\n",
    "other_browser = other_browser['Other']\n",
    "other_browser = other_browser.reset_index()\n",
    "\n",
    "browser_version = pd.merge(selected_browser_version,other_browser,on='person',how='inner')\n",
    "browser_version = browser_version.set_index('person')\n",
    "\n",
    "browser_version = browser_version.add_prefix('browser_version_')\n",
    "browser_version = browser_version.reset_index()\n",
    "browser_version.shape"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 12,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "241      360x640\n",
       "83      1366x768\n",
       "150    1920x1080\n",
       "220      320x534\n",
       "79      1360x768\n",
       "Name: screen_resolution, dtype: object"
      ]
     },
     "execution_count": 12,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "#screen_resolution\n",
    "test_s = tabla_encode['screen_resolution']\n",
    "test_s = pd.DataFrame(test_s)\n",
    "test_s['aux']=1\n",
    "test_s = test_s.groupby('screen_resolution').count().reset_index().sort_values(by='aux',ascending=False)\n",
    "test_s = test_s.drop([0],axis=0)\n",
    "\n",
    "resolutions = test_s['screen_resolution'].head()\n",
    "resolutions\n",
    "#Me quedo con las 5 resoluciones que mas buscan"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 13,
   "metadata": {},
   "outputs": [
    {
     "name": "stderr",
     "output_type": "stream",
     "text": [
      "C:\\Users\\Santi\\AppData\\Roaming\\Python\\Python36\\site-packages\\ipykernel_launcher.py:2: 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",
      "  \n"
     ]
    },
    {
     "data": {
      "text/plain": [
       "(38829, 7)"
      ]
     },
     "execution_count": 13,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "screen_resolution = tabla_encode[['person','screen_resolution']]\n",
    "screen_resolution['aux'] = 1\n",
    "screen_resolution = screen_resolution.groupby(['person','screen_resolution'])['aux'].count().unstack().reset_index().fillna(0)\n",
    "screen_resolution = screen_resolution.set_index('person')\n",
    "selected_screen_resolution = screen_resolution.loc[:,resolutions]\n",
    "selected_screen_resolution = selected_screen_resolution.reset_index()\n",
    "\n",
    "\n",
    "other_screen_resolution = screen_resolution.drop(resolutions,axis=1)\n",
    "other_screen_resolution = other_screen_resolution.drop(0,axis=1)\n",
    "other_screen_resolution['Other'] = other_screen_resolution.max(axis=1)\n",
    "other_screen_resolution = other_screen_resolution['Other']\n",
    "other_screen_resolution = other_screen_resolution.reset_index()\n",
    "\n",
    "screen_resolution = pd.merge(selected_screen_resolution,other_screen_resolution,on='person',how='inner')\n",
    "screen_resolution = screen_resolution.set_index('person')\n",
    "\n",
    "screen_resolution = screen_resolution.add_prefix('screen_resolution_')\n",
    "screen_resolution = screen_resolution.reset_index()\n",
    "screen_resolution.shape"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 14,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "0           Android\n",
       "11          Windows\n",
       "16              iOS\n",
       "15       Windows XP\n",
       "12    Windows Phone\n",
       "Name: operating_system_version, dtype: object"
      ]
     },
     "execution_count": 14,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "#operating_system_version\n",
    "test_os = tabla_encode['operating_system_version'].str.replace('\\d+', '')\n",
    "test_os = test_os.str.replace('.', '')\n",
    "test_os = test_os.str.strip()\n",
    "test_os2 = pd.DataFrame(test_os)\n",
    "test_os2['aux']=1\n",
    "test_os2 = test_os2.groupby('operating_system_version').count().reset_index().sort_values(by='aux',ascending=False)\n",
    "test_os2\n",
    "\n",
    "OSs = test_os2['operating_system_version'].head()\n",
    "OSs\n",
    "#Me quedo con las 5 resoluciones que mas buscan"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 15,
   "metadata": {},
   "outputs": [
    {
     "name": "stderr",
     "output_type": "stream",
     "text": [
      "C:\\Users\\Santi\\AppData\\Roaming\\Python\\Python36\\site-packages\\ipykernel_launcher.py:6: 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",
      "  \n"
     ]
    },
    {
     "data": {
      "text/plain": [
       "(38242, 7)"
      ]
     },
     "execution_count": 15,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "tabla_encode['operating_system_version'] = tabla_encode['operating_system_version'].str.replace('\\d+', '')\n",
    "tabla_encode['operating_system_version'] = tabla_encode['operating_system_version'].str.replace('.', '')\n",
    "tabla_encode['operating_system_version'] = tabla_encode['operating_system_version'].str.strip()\n",
    "\n",
    "operating_system_version = tabla_encode[['person','operating_system_version']]\n",
    "operating_system_version['aux'] = 1\n",
    "operating_system_version = operating_system_version.groupby(['person','operating_system_version'])['aux'].count().unstack().reset_index().fillna(0)\n",
    "operating_system_version = operating_system_version.set_index('person')\n",
    "selected_OS = operating_system_version.loc[:,OSs]\n",
    "selected_OS = selected_OS.reset_index()\n",
    "\n",
    "\n",
    "other_OS = operating_system_version.drop(OSs,axis=1)\n",
    "other_OS['Other'] = other_OS.max(axis=1)\n",
    "other_OS = other_OS['Other']\n",
    "other_OS = other_OS.reset_index()\n",
    "\n",
    "operating_system_version = pd.merge(selected_OS,other_OS,on='person',how='inner')\n",
    "operating_system_version = operating_system_version.set_index('person')\n",
    "\n",
    "operating_system_version = operating_system_version.add_prefix('OSV_')\n",
    "operating_system_version = operating_system_version.reset_index()\n",
    "operating_system_version.shape"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 16,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "2     16GB\n",
       "4     32GB\n",
       "7     64GB\n",
       "1    128GB\n",
       "Name: storage, dtype: object"
      ]
     },
     "execution_count": 16,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "#storage\n",
    "test_st = tabla_encode['storage']\n",
    "test_st = pd.DataFrame(test_st)\n",
    "test_st['aux']=1\n",
    "test_st = test_st.groupby('storage').count().reset_index().sort_values(by='aux',ascending=False)\n",
    "test_st = test_st.drop([0],axis=0)\n",
    "\n",
    "storages = test_st['storage'].head(4)\n",
    "storages\n",
    "#Me quedo con las 5 resoluciones que mas buscan"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 17,
   "metadata": {},
   "outputs": [
    {
     "name": "stderr",
     "output_type": "stream",
     "text": [
      "C:\\Users\\Santi\\AppData\\Roaming\\Python\\Python36\\site-packages\\ipykernel_launcher.py:2: 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",
      "  \n"
     ]
    },
    {
     "data": {
      "text/plain": [
       "(38829, 6)"
      ]
     },
     "execution_count": 17,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "storage = tabla_encode[['person','storage']]\n",
    "storage['aux'] = 1\n",
    "storage = storage.groupby(['person','storage'])['aux'].count().unstack().reset_index().fillna(0)\n",
    "storage = storage.set_index('person')\n",
    "selected_storage = storage.loc[:,storages]\n",
    "selected_storage = selected_storage.reset_index()\n",
    "\n",
    "\n",
    "other_storage = storage.drop(storages,axis=1)\n",
    "other_storage = other_storage.drop(0,axis=1)\n",
    "other_storage['Other'] = other_storage.max(axis=1)\n",
    "other_storage = other_storage['Other']\n",
    "other_storage = other_storage.reset_index()\n",
    "\n",
    "storage = pd.merge(selected_storage,other_storage,on='person',how='inner')\n",
    "storage = storage.set_index('person')\n",
    "\n",
    "storage = storage.add_prefix('storage_')\n",
    "storage = storage.reset_index()\n",
    "storage.shape"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 18,
   "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>condition</th>\n",
       "      <th>aux</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>Bom</td>\n",
       "      <td>547617</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>Excelente</td>\n",
       "      <td>378697</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>Muito Bom</td>\n",
       "      <td>359053</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>Bom - Sem Touch ID</td>\n",
       "      <td>31742</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>5</th>\n",
       "      <td>Novo</td>\n",
       "      <td>3421</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "            condition     aux\n",
       "1                 Bom  547617\n",
       "3           Excelente  378697\n",
       "4           Muito Bom  359053\n",
       "2  Bom - Sem Touch ID   31742\n",
       "5                Novo    3421"
      ]
     },
     "execution_count": 18,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "#condition\n",
    "test_c = tabla_encode['condition']\n",
    "test_c = pd.DataFrame(test_c)\n",
    "test_c['aux']=1\n",
    "test_c = test_c.groupby('condition').count().reset_index().sort_values(by='aux',ascending=False)\n",
    "test_c = test_c.drop([0],axis=0)\n",
    "test_c\n",
    "\n",
    "#Ya son 5, me quedo todas."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 19,
   "metadata": {},
   "outputs": [
    {
     "name": "stderr",
     "output_type": "stream",
     "text": [
      "C:\\Users\\Santi\\AppData\\Roaming\\Python\\Python36\\site-packages\\ipykernel_launcher.py:2: 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",
      "  \n"
     ]
    },
    {
     "data": {
      "text/plain": [
       "(38829, 6)"
      ]
     },
     "execution_count": 19,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "condition = tabla_encode[['person','condition']]\n",
    "condition['aux'] = 1\n",
    "condition = condition.groupby(['person','condition'])['aux'].count().unstack().reset_index().fillna(0)\n",
    "condition = condition.drop([0],axis=1)\n",
    "condition = condition.set_index('person')\n",
    "condition = condition.add_prefix('condition_')\n",
    "condition = condition.reset_index()\n",
    "condition.shape"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 20,
   "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>new_vs_returning</th>\n",
       "      <th>aux</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>Returning</td>\n",
       "      <td>165827</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>New</td>\n",
       "      <td>38242</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "  new_vs_returning     aux\n",
       "2        Returning  165827\n",
       "1              New   38242"
      ]
     },
     "execution_count": 20,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "#new_vs_returning\n",
    "test_nv = tabla_encode['new_vs_returning']\n",
    "test_nv = pd.DataFrame(test_nv)\n",
    "test_nv['aux']=1\n",
    "test_nv = test_nv.groupby('new_vs_returning').count().reset_index().sort_values(by='aux',ascending=False)\n",
    "test_nv = test_nv.drop([0],axis=0)\n",
    "test_nv\n",
    "#Son solo 2 valores"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 21,
   "metadata": {},
   "outputs": [
    {
     "name": "stderr",
     "output_type": "stream",
     "text": [
      "C:\\Users\\Santi\\AppData\\Roaming\\Python\\Python36\\site-packages\\ipykernel_launcher.py:3: 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",
      "  This is separate from the ipykernel package so we can avoid doing imports until\n"
     ]
    },
    {
     "data": {
      "text/plain": [
       "(38829, 3)"
      ]
     },
     "execution_count": 21,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "#Esta la hago un count.\n",
    "new_vs_returning = tabla_encode[['person','new_vs_returning']]\n",
    "new_vs_returning['aux'] = 1\n",
    "new_vs_returning = new_vs_returning.groupby(['person','new_vs_returning'])['aux'].count().unstack().reset_index().fillna(0)\n",
    "new_vs_returning = new_vs_returning.drop([0],axis=1)\n",
    "new_vs_returning = new_vs_returning.set_index('person')\n",
    "new_vs_returning = new_vs_returning.add_prefix('new_vs_returning_')\n",
    "new_vs_returning = new_vs_returning.reset_index()\n",
    "new_vs_returning.shape"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 22,
   "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>search_engine</th>\n",
       "      <th>aux</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>Google</td>\n",
       "      <td>105195</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>Bing</td>\n",
       "      <td>676</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>Yahoo</td>\n",
       "      <td>519</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>Ask</td>\n",
       "      <td>16</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "  search_engine     aux\n",
       "3        Google  105195\n",
       "2          Bing     676\n",
       "4         Yahoo     519\n",
       "1           Ask      16"
      ]
     },
     "execution_count": 22,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "#search_engine\n",
    "test_se = tabla_encode['search_engine']\n",
    "test_se = pd.DataFrame(test_se)\n",
    "test_se['aux']=1\n",
    "test_se = test_se.groupby('search_engine').count().reset_index().sort_values(by='aux',ascending=False)\n",
    "test_se = test_se.drop([0],axis=0)\n",
    "test_se\n",
    "#Son solo 4 valores"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 23,
   "metadata": {},
   "outputs": [
    {
     "name": "stderr",
     "output_type": "stream",
     "text": [
      "C:\\Users\\Santi\\AppData\\Roaming\\Python\\Python36\\site-packages\\ipykernel_launcher.py:3: 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",
      "  This is separate from the ipykernel package so we can avoid doing imports until\n"
     ]
    },
    {
     "data": {
      "text/plain": [
       "(38829, 5)"
      ]
     },
     "execution_count": 23,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "#Esta la hago un count.\n",
    "search_engine = tabla_encode[['person','search_engine']]\n",
    "search_engine['aux'] = 1\n",
    "search_engine = search_engine.groupby(['person','search_engine'])['aux'].count().unstack().reset_index().fillna(0)\n",
    "search_engine = search_engine.drop([0],axis=1)\n",
    "search_engine = search_engine.set_index('person')\n",
    "search_engine = search_engine.add_prefix('search_engine_')\n",
    "search_engine = search_engine.reset_index()\n",
    "search_engine.shape"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 24,
   "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>channel</th>\n",
       "      <th>aux</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>Paid</td>\n",
       "      <td>91753</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>Direct</td>\n",
       "      <td>50675</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>5</th>\n",
       "      <td>Referral</td>\n",
       "      <td>33990</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>Organic</td>\n",
       "      <td>25867</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>6</th>\n",
       "      <td>Social</td>\n",
       "      <td>1406</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>Email</td>\n",
       "      <td>371</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>7</th>\n",
       "      <td>Unknown</td>\n",
       "      <td>7</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "    channel    aux\n",
       "4      Paid  91753\n",
       "1    Direct  50675\n",
       "5  Referral  33990\n",
       "3   Organic  25867\n",
       "6    Social   1406\n",
       "2     Email    371\n",
       "7   Unknown      7"
      ]
     },
     "execution_count": 24,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "#channel\n",
    "test_ch = tabla_encode['channel']\n",
    "test_ch = pd.DataFrame(test_ch)\n",
    "test_ch['aux']=1\n",
    "test_ch = test_ch.groupby('channel').count().reset_index().sort_values(by='aux',ascending=False)\n",
    "test_ch = test_ch.drop([0],axis=0)\n",
    "test_ch\n",
    "#Mantengo los 7 valores"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 25,
   "metadata": {},
   "outputs": [
    {
     "name": "stderr",
     "output_type": "stream",
     "text": [
      "C:\\Users\\Santi\\AppData\\Roaming\\Python\\Python36\\site-packages\\ipykernel_launcher.py:3: 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",
      "  This is separate from the ipykernel package so we can avoid doing imports until\n"
     ]
    },
    {
     "data": {
      "text/plain": [
       "(38829, 8)"
      ]
     },
     "execution_count": 25,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "#Esta la hago un count.\n",
    "channel = tabla_encode[['person','channel']]\n",
    "channel['aux'] = 1\n",
    "channel = channel.groupby(['person','channel'])['aux'].count().unstack().reset_index().fillna(0)\n",
    "channel = channel.drop([0],axis=1)\n",
    "channel = channel.set_index('person')\n",
    "channel = channel.add_prefix('channel_')\n",
    "channel = channel.reset_index()\n",
    "channel.shape"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 26,
   "metadata": {},
   "outputs": [],
   "source": [
    "#Ahora a unir todo..."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 51,
   "metadata": {},
   "outputs": [],
   "source": [
    "tabla_final = pd.merge(total_eventos,dev_type,on='person',how='inner')\n",
    "tabla_final = pd.merge(tabla_final,screen_resolution,on='person',how='inner')\n",
    "tabla_final = pd.merge(tabla_final,storage,on='person',how='inner')#4\n",
    "tabla_final = pd.merge(tabla_final,condition,on='person',how='inner')\n",
    "#tabla_final = pd.merge(tabla_final,new_vs_returning,on='person',how='inner')\n",
    "tabla_final = pd.merge(tabla_final,search_engine,on='person',how='inner')\n",
    "tabla_final = pd.merge(tabla_final,channel,on='person',how='inner')\n",
    "#tabla_final = pd.merge(tabla_final,browser_version,on='person',how='left')\n",
    "tabla_final = pd.merge(tabla_final,operating_system_version,on='person',how='left')\n",
    "tabla_final = tabla_final.fillna(0.5)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 52,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "<class 'pandas.core.frame.DataFrame'>\n",
      "Int64Index: 38829 entries, 0 to 38828\n",
      "Data columns (total 65 columns):\n",
      "person                          38829 non-null object\n",
      "ad campaign hit_x               38829 non-null float64\n",
      "brand listing_x                 38829 non-null float64\n",
      "checkout_x                      38829 non-null float64\n",
      "conversion_x                    38829 non-null float64\n",
      "generic listing_x               38829 non-null float64\n",
      "lead_x                          38829 non-null float64\n",
      "search engine hit_x             38829 non-null float64\n",
      "searched products_x             38829 non-null float64\n",
      "staticpage_x                    38829 non-null float64\n",
      "viewed product_x                38829 non-null float64\n",
      "visited site_x                  38829 non-null float64\n",
      "total_eventos                   38829 non-null float64\n",
      "index                           38829 non-null int64\n",
      "ad campaign hit_y               38829 non-null float64\n",
      "brand listing_y                 38829 non-null float64\n",
      "checkout_y                      38829 non-null float64\n",
      "conversion_y                    38829 non-null float64\n",
      "generic listing_y               38829 non-null float64\n",
      "lead_y                          38829 non-null float64\n",
      "search engine hit_y             38829 non-null float64\n",
      "searched products_y             38829 non-null float64\n",
      "staticpage_y                    38829 non-null float64\n",
      "viewed product_y                38829 non-null float64\n",
      "visited site_y                  38829 non-null float64\n",
      "min                             38829 non-null int64\n",
      "max                             38829 non-null int64\n",
      "total_horas                     38829 non-null float64\n",
      "device_type_Computer            38829 non-null float64\n",
      "device_type_Smartphone          38829 non-null float64\n",
      "device_type_Tablet              38829 non-null float64\n",
      "device_type_Unknown             38829 non-null float64\n",
      "screen_resolution_360x640       38829 non-null float64\n",
      "screen_resolution_1366x768      38829 non-null float64\n",
      "screen_resolution_1920x1080     38829 non-null float64\n",
      "screen_resolution_320x534       38829 non-null float64\n",
      "screen_resolution_1360x768      38829 non-null float64\n",
      "screen_resolution_Other         38829 non-null float64\n",
      "storage_16GB                    38829 non-null float64\n",
      "storage_32GB                    38829 non-null float64\n",
      "storage_64GB                    38829 non-null float64\n",
      "storage_128GB                   38829 non-null float64\n",
      "storage_Other                   38829 non-null float64\n",
      "condition_Bom                   38829 non-null float64\n",
      "condition_Bom - Sem Touch ID    38829 non-null float64\n",
      "condition_Excelente             38829 non-null float64\n",
      "condition_Muito Bom             38829 non-null float64\n",
      "condition_Novo                  38829 non-null float64\n",
      "search_engine_Ask               38829 non-null float64\n",
      "search_engine_Bing              38829 non-null float64\n",
      "search_engine_Google            38829 non-null float64\n",
      "search_engine_Yahoo             38829 non-null float64\n",
      "channel_Direct                  38829 non-null float64\n",
      "channel_Email                   38829 non-null float64\n",
      "channel_Organic                 38829 non-null float64\n",
      "channel_Paid                    38829 non-null float64\n",
      "channel_Referral                38829 non-null float64\n",
      "channel_Social                  38829 non-null float64\n",
      "channel_Unknown                 38829 non-null float64\n",
      "OSV_Android                     38829 non-null float64\n",
      "OSV_Windows                     38829 non-null float64\n",
      "OSV_iOS                         38829 non-null float64\n",
      "OSV_Windows XP                  38829 non-null float64\n",
      "OSV_Windows Phone               38829 non-null float64\n",
      "OSV_Other                       38829 non-null float64\n",
      "dtypes: float64(61), int64(3), object(1)\n",
      "memory usage: 19.6+ MB\n"
     ]
    }
   ],
   "source": [
    "tabla_final.info()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 53,
   "metadata": {},
   "outputs": [],
   "source": [
    "\n",
    "intento_f = pd.merge(tabla_final,res,on='person',how='inner')\n",
    "\n",
    "intento_f = intento_f.set_index('person')"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 54,
   "metadata": {},
   "outputs": [],
   "source": [
    "final = pd.read_csv('trocafone_kaggle_test.csv')\n",
    "final = pd.merge(tabla_final,final,on='person',how='inner')\n",
    "final = final.set_index('person')\n",
    "x_entrega = final"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 55,
   "metadata": {},
   "outputs": [],
   "source": [
    "#XGBoost\n",
    "\n",
    "X, y = intento_f.iloc[:,:-1],intento_f.iloc[:,-1]\n",
    "data_dmatrix = xgb.DMatrix(data=X,label=y)\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 56,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "XGBRegressor(alpha=7, base_score=0.5, booster='gbtree', colsample_bylevel=1,\n",
       "       colsample_bytree=0.3, gamma=0, learning_rate=0.1, max_delta_step=0,\n",
       "       max_depth=5, min_child_weight=1, missing=None, n_estimators=10,\n",
       "       n_jobs=1, nthread=None, objective='reg:linear', random_state=0,\n",
       "       reg_alpha=0, reg_lambda=1, scale_pos_weight=1, seed=None,\n",
       "       silent=True, subsample=1)"
      ]
     },
     "execution_count": 56,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "from sklearn.model_selection import train_test_split\n",
    "\n",
    "X_train, X_test, y_train, y_test = \\\n",
    "    train_test_split(X, y, test_size=0.2, random_state=123)\n",
    "    \n",
    "xg_reg = xgb.XGBRegressor(objective ='reg:linear', \n",
    "                colsample_bytree = 0.3, learning_rate = 0.1,\n",
    "                max_depth = 5, alpha = 7, n_estimators = 10)\n",
    "\n",
    "xg_reg.fit(X_train,y_train)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 57,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "RMSE: 0.258482\n"
     ]
    }
   ],
   "source": [
    "preds = xg_reg.predict(X_test)\n",
    "rmse = np.sqrt(mean_squared_error(y_test, preds))\n",
    "print(\"RMSE: %f\" % (rmse))\n",
    "#0.258491"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 58,
   "metadata": {},
   "outputs": [],
   "source": [
    "entrega = xg_reg.predict(x_entrega)\n",
    "entrega = entrega.clip(min=0)  \n",
    "\n",
    "y_summit = pd.DataFrame(entrega)\n",
    "y_summit.columns = ['label']\n",
    "y_summit['person'] = x_entrega.reset_index()['person']\n",
    "y_person = x_entrega.reset_index()['person']\n",
    "\n",
    "y_summit = y_summit[['person','label']]\n",
    "y_summit.to_csv('summit036.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.4"
  }
 },
 "nbformat": 4,
 "nbformat_minor": 2
}