04_merge_with_features.ipynb

{
 "cells": [
  {
   "cell_type": "code",
   "execution_count": 1,
   "metadata": {},
   "outputs": [],
   "source": [
    "import pandas as pd\n",
    "from pprint import pprint\n",
    "from statistics import mean\n",
    "import os\n",
    "import gmplot\n",
    "import pandas as pd"
   ]
  },
  {
   "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>id</th>\n",
       "      <th>nome</th>\n",
       "      <th>ano_registo</th>\n",
       "      <th>type</th>\n",
       "      <th>n_reg</th>\n",
       "      <th>lat</th>\n",
       "      <th>lon</th>\n",
       "      <th>score_global</th>\n",
       "      <th>score_location</th>\n",
       "      <th>score_price/quality</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>1</td>\n",
       "      <td>metro apartment</td>\n",
       "      <td>2016.0</td>\n",
       "      <td>Apartamento</td>\n",
       "      <td>35375/AL</td>\n",
       "      <td>41.152554</td>\n",
       "      <td>-8.610274</td>\n",
       "      <td>6.9</td>\n",
       "      <td>8.4</td>\n",
       "      <td>6.2</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>2</td>\n",
       "      <td>be happy flat</td>\n",
       "      <td>2016.0</td>\n",
       "      <td>Apartamento</td>\n",
       "      <td>23120/AL</td>\n",
       "      <td>41.154861</td>\n",
       "      <td>-8.610286</td>\n",
       "      <td>9.2</td>\n",
       "      <td>9.3</td>\n",
       "      <td>9.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>3</td>\n",
       "      <td>camões apartment</td>\n",
       "      <td>2016.0</td>\n",
       "      <td>Apartamento</td>\n",
       "      <td>28842/AL</td>\n",
       "      <td>41.155397</td>\n",
       "      <td>-8.610221</td>\n",
       "      <td>9.4</td>\n",
       "      <td>9.1</td>\n",
       "      <td>9.6</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>9</td>\n",
       "      <td>estudio 312</td>\n",
       "      <td>2016.0</td>\n",
       "      <td>Apartamento</td>\n",
       "      <td>35922/AL</td>\n",
       "      <td>41.158680</td>\n",
       "      <td>-8.597245</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>13</td>\n",
       "      <td>the nature mezzanine</td>\n",
       "      <td>2016.0</td>\n",
       "      <td>Apartamento</td>\n",
       "      <td>30839/AL</td>\n",
       "      <td>41.149781</td>\n",
       "      <td>-8.601978</td>\n",
       "      <td>9.5</td>\n",
       "      <td>9.1</td>\n",
       "      <td>9.2</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "   id                  nome  ano_registo         type     n_reg        lat  \\\n",
       "0   1       metro apartment       2016.0  Apartamento  35375/AL  41.152554   \n",
       "1   2         be happy flat       2016.0  Apartamento  23120/AL  41.154861   \n",
       "2   3      camões apartment       2016.0  Apartamento  28842/AL  41.155397   \n",
       "3   9           estudio 312       2016.0  Apartamento  35922/AL  41.158680   \n",
       "4  13  the nature mezzanine       2016.0  Apartamento  30839/AL  41.149781   \n",
       "\n",
       "        lon  score_global  score_location  score_price/quality  \n",
       "0 -8.610274           6.9             8.4                  6.2  \n",
       "1 -8.610286           9.2             9.3                  9.0  \n",
       "2 -8.610221           9.4             9.1                  9.6  \n",
       "3 -8.597245           NaN             NaN                  NaN  \n",
       "4 -8.601978           9.5             9.1                  9.2  "
      ]
     },
     "execution_count": 4,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df = pd.read_csv(\"final_dataset.csv\")\n",
    "df.head()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 11,
   "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>id</th>\n",
       "      <th>data_levan</th>\n",
       "      <th>nome</th>\n",
       "      <th>ano_registo</th>\n",
       "      <th>type</th>\n",
       "      <th>address</th>\n",
       "      <th>freg</th>\n",
       "      <th>lat</th>\n",
       "      <th>lon</th>\n",
       "      <th>nearest_metro</th>\n",
       "      <th>nearest_bus</th>\n",
       "      <th>nearest_tur_point</th>\n",
       "      <th>POI_closer_than_1000</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>1</td>\n",
       "      <td>1.488758e+12</td>\n",
       "      <td>metro apartment</td>\n",
       "      <td>2016.0</td>\n",
       "      <td>Apartamento</td>\n",
       "      <td>Rua Rua Camões, 77 1º 1.1, 4000-144 Porto, Por...</td>\n",
       "      <td>NaN</td>\n",
       "      <td>41.152554</td>\n",
       "      <td>-8.610274</td>\n",
       "      <td>78.587973</td>\n",
       "      <td>38.058922</td>\n",
       "      <td>272.813299</td>\n",
       "      <td>8</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>2</td>\n",
       "      <td>1.488758e+12</td>\n",
       "      <td>be happy flat</td>\n",
       "      <td>2016.0</td>\n",
       "      <td>Apartamento</td>\n",
       "      <td>Rua Rua Camões, 327 1.º Hab. 1.5, 4000-145 Por...</td>\n",
       "      <td>NaN</td>\n",
       "      <td>41.154861</td>\n",
       "      <td>-8.610286</td>\n",
       "      <td>283.877729</td>\n",
       "      <td>66.832204</td>\n",
       "      <td>523.688312</td>\n",
       "      <td>7</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>3</td>\n",
       "      <td>1.488758e+12</td>\n",
       "      <td>camões apartment</td>\n",
       "      <td>2016.0</td>\n",
       "      <td>Apartamento</td>\n",
       "      <td>Rua Rua Camões, 387 2º Dt, 4000-146 Porto, Por...</td>\n",
       "      <td>NaN</td>\n",
       "      <td>41.155397</td>\n",
       "      <td>-8.610221</td>\n",
       "      <td>230.698904</td>\n",
       "      <td>100.771066</td>\n",
       "      <td>583.297994</td>\n",
       "      <td>7</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>4</td>\n",
       "      <td>1.488758e+12</td>\n",
       "      <td>zarolho</td>\n",
       "      <td>2016.0</td>\n",
       "      <td>Apartamento</td>\n",
       "      <td>None, None Porto, Portugal</td>\n",
       "      <td>NaN</td>\n",
       "      <td>41.157035</td>\n",
       "      <td>-8.610126</td>\n",
       "      <td>82.091015</td>\n",
       "      <td>6.434237</td>\n",
       "      <td>764.713348</td>\n",
       "      <td>7</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>5</td>\n",
       "      <td>1.488758e+12</td>\n",
       "      <td>casa carvalheiras</td>\n",
       "      <td>2016.0</td>\n",
       "      <td>Moradia</td>\n",
       "      <td>None, 4000-159 Porto, Portugal</td>\n",
       "      <td>NaN</td>\n",
       "      <td>41.154907</td>\n",
       "      <td>-8.605619</td>\n",
       "      <td>405.762343</td>\n",
       "      <td>103.531424</td>\n",
       "      <td>700.770281</td>\n",
       "      <td>7</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "   id    data_levan               nome  ano_registo         type  \\\n",
       "0   1  1.488758e+12    metro apartment       2016.0  Apartamento   \n",
       "1   2  1.488758e+12      be happy flat       2016.0  Apartamento   \n",
       "2   3  1.488758e+12   camões apartment       2016.0  Apartamento   \n",
       "3   4  1.488758e+12            zarolho       2016.0  Apartamento   \n",
       "4   5  1.488758e+12  casa carvalheiras       2016.0      Moradia   \n",
       "\n",
       "                                             address  freg        lat  \\\n",
       "0  Rua Rua Camões, 77 1º 1.1, 4000-144 Porto, Por...   NaN  41.152554   \n",
       "1  Rua Rua Camões, 327 1.º Hab. 1.5, 4000-145 Por...   NaN  41.154861   \n",
       "2  Rua Rua Camões, 387 2º Dt, 4000-146 Porto, Por...   NaN  41.155397   \n",
       "3                         None, None Porto, Portugal   NaN  41.157035   \n",
       "4                     None, 4000-159 Porto, Portugal   NaN  41.154907   \n",
       "\n",
       "        lon  nearest_metro  nearest_bus  nearest_tur_point  \\\n",
       "0 -8.610274      78.587973    38.058922         272.813299   \n",
       "1 -8.610286     283.877729    66.832204         523.688312   \n",
       "2 -8.610221     230.698904   100.771066         583.297994   \n",
       "3 -8.610126      82.091015     6.434237         764.713348   \n",
       "4 -8.605619     405.762343   103.531424         700.770281   \n",
       "\n",
       "   POI_closer_than_1000  \n",
       "0                     8  \n",
       "1                     7  \n",
       "2                     7  \n",
       "3                     7  \n",
       "4                     7  "
      ]
     },
     "execution_count": 11,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df_f = pd.read_csv(\"alojamentos2.csv\")\n",
    "df_f[\"nome\"] = df_f[\"nome\"].str.lower()\n",
    "df_f.head()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 18,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "1753\n",
      "1753\n",
      "Index(['id', 'nome', 'ano_registo', 'type', 'n_reg', 'lat', 'lon',\n",
      "       'score_global', 'score_location', 'score_price/quality', 'data_levan',\n",
      "       'nome_y', 'ano_registo_y', 'type_y', 'address', 'freg', 'lat_y',\n",
      "       'lon_y', 'nearest_metro', 'nearest_bus', 'nearest_tur_point',\n",
      "       'POI_closer_than_1000'],\n",
      "      dtype='object')\n"
     ]
    },
    {
     "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>id</th>\n",
       "      <th>nome</th>\n",
       "      <th>ano_registo</th>\n",
       "      <th>type</th>\n",
       "      <th>n_reg</th>\n",
       "      <th>lat</th>\n",
       "      <th>lon</th>\n",
       "      <th>score_global</th>\n",
       "      <th>score_location</th>\n",
       "      <th>score_price/quality</th>\n",
       "      <th>data_levan</th>\n",
       "      <th>nearest_metro</th>\n",
       "      <th>nearest_bus</th>\n",
       "      <th>nearest_tur_point</th>\n",
       "      <th>POI_closer_than_1000</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>1</td>\n",
       "      <td>metro apartment</td>\n",
       "      <td>2016.0</td>\n",
       "      <td>Apartamento</td>\n",
       "      <td>35375/AL</td>\n",
       "      <td>41.152554</td>\n",
       "      <td>-8.610274</td>\n",
       "      <td>6.9</td>\n",
       "      <td>8.4</td>\n",
       "      <td>6.2</td>\n",
       "      <td>1.488758e+12</td>\n",
       "      <td>78.587973</td>\n",
       "      <td>38.058922</td>\n",
       "      <td>272.813299</td>\n",
       "      <td>8</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>2</td>\n",
       "      <td>be happy flat</td>\n",
       "      <td>2016.0</td>\n",
       "      <td>Apartamento</td>\n",
       "      <td>23120/AL</td>\n",
       "      <td>41.154861</td>\n",
       "      <td>-8.610286</td>\n",
       "      <td>9.2</td>\n",
       "      <td>9.3</td>\n",
       "      <td>9.0</td>\n",
       "      <td>1.488758e+12</td>\n",
       "      <td>283.877729</td>\n",
       "      <td>66.832204</td>\n",
       "      <td>523.688312</td>\n",
       "      <td>7</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>3</td>\n",
       "      <td>camões apartment</td>\n",
       "      <td>2016.0</td>\n",
       "      <td>Apartamento</td>\n",
       "      <td>28842/AL</td>\n",
       "      <td>41.155397</td>\n",
       "      <td>-8.610221</td>\n",
       "      <td>9.4</td>\n",
       "      <td>9.1</td>\n",
       "      <td>9.6</td>\n",
       "      <td>1.488758e+12</td>\n",
       "      <td>230.698904</td>\n",
       "      <td>100.771066</td>\n",
       "      <td>583.297994</td>\n",
       "      <td>7</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>9</td>\n",
       "      <td>estudio 312</td>\n",
       "      <td>2016.0</td>\n",
       "      <td>Apartamento</td>\n",
       "      <td>35922/AL</td>\n",
       "      <td>41.158680</td>\n",
       "      <td>-8.597245</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>1.488758e+12</td>\n",
       "      <td>662.910408</td>\n",
       "      <td>79.433694</td>\n",
       "      <td>1501.107361</td>\n",
       "      <td>2</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>13</td>\n",
       "      <td>the nature mezzanine</td>\n",
       "      <td>2016.0</td>\n",
       "      <td>Apartamento</td>\n",
       "      <td>30839/AL</td>\n",
       "      <td>41.149781</td>\n",
       "      <td>-8.601978</td>\n",
       "      <td>9.5</td>\n",
       "      <td>9.1</td>\n",
       "      <td>9.2</td>\n",
       "      <td>1.488758e+12</td>\n",
       "      <td>299.687528</td>\n",
       "      <td>84.189898</td>\n",
       "      <td>674.653218</td>\n",
       "      <td>5</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "   id                  nome  ano_registo         type     n_reg        lat  \\\n",
       "0   1       metro apartment       2016.0  Apartamento  35375/AL  41.152554   \n",
       "1   2         be happy flat       2016.0  Apartamento  23120/AL  41.154861   \n",
       "2   3      camões apartment       2016.0  Apartamento  28842/AL  41.155397   \n",
       "3   9           estudio 312       2016.0  Apartamento  35922/AL  41.158680   \n",
       "4  13  the nature mezzanine       2016.0  Apartamento  30839/AL  41.149781   \n",
       "\n",
       "        lon  score_global  score_location  score_price/quality    data_levan  \\\n",
       "0 -8.610274           6.9             8.4                  6.2  1.488758e+12   \n",
       "1 -8.610286           9.2             9.3                  9.0  1.488758e+12   \n",
       "2 -8.610221           9.4             9.1                  9.6  1.488758e+12   \n",
       "3 -8.597245           NaN             NaN                  NaN  1.488758e+12   \n",
       "4 -8.601978           9.5             9.1                  9.2  1.488758e+12   \n",
       "\n",
       "   nearest_metro  nearest_bus  nearest_tur_point  POI_closer_than_1000  \n",
       "0      78.587973    38.058922         272.813299                     8  \n",
       "1     283.877729    66.832204         523.688312                     7  \n",
       "2     230.698904   100.771066         583.297994                     7  \n",
       "3     662.910408    79.433694        1501.107361                     2  \n",
       "4     299.687528    84.189898         674.653218                     5  "
      ]
     },
     "execution_count": 18,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df_final = pd.merge(df, df_f, how=\"inner\", left_on=[\"id\"], right_on=[\"id\"], suffixes=('', '_y'))\n",
    "print(len(df))\n",
    "print(len(df_final))\n",
    "print(df_final.columns)\n",
    "df_final = df_final[[\"id\", \"nome\", \"ano_registo\", \"type\", \"n_reg\", \"lat\", \"lon\", \"score_global\", \"score_location\", \"score_price/quality\", \"data_levan\", \"nearest_metro\", \"nearest_bus\", \"nearest_tur_point\", \"POI_closer_than_1000\"]]\n",
    "df_final.head(5)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 19,
   "metadata": {},
   "outputs": [],
   "source": [
    "df_final.to_csv(\"dataset_com_features.csv\", index=None)"
   ]
  }
 ],
 "metadata": {
  "kernelspec": {
   "display_name": "Python 3",
   "language": "python",
   "name": "python3"
  },
  "language_info": {
   "codemirror_mode": {
    "name": "ipython",
    "version": 3
   },
   "file_extension": ".py",
   "mimetype": "text/x-python",
   "name": "python",
   "nbconvert_exporter": "python",
   "pygments_lexer": "ipython3",
   "version": "3.7.2"
  },
  "latex_envs": {
   "LaTeX_envs_menu_present": true,
   "autoclose": false,
   "autocomplete": true,
   "bibliofile": "biblio.bib",
   "cite_by": "apalike",
   "current_citInitial": 1,
   "eqLabelWithNumbers": true,
   "eqNumInitial": 1,
   "hotkeys": {
    "equation": "Ctrl-E",
    "itemize": "Ctrl-I"
   },
   "labels_anchors": false,
   "latex_user_defs": false,
   "report_style_numbering": false,
   "user_envs_cfg": false
  }
 },
 "nbformat": 4,
 "nbformat_minor": 2
}