rent_prices_dataset_cleaning.ipynb

{
 "cells": [
  {
   "cell_type": "code",
   "execution_count": 1,
   "metadata": {
    "collapsed": false,
    "jupyter": {
     "outputs_hidden": false
    }
   },
   "outputs": [],
   "source": [
    "import pandas as pd\n",
    "import glob"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 2,
   "metadata": {
    "collapsed": false,
    "jupyter": {
     "outputs_hidden": false
    }
   },
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "['./data/rent_prices/2017_lloguer_preu_trim.csv', './data/rent_prices/2014_lloguer_preu_trim.csv', './data/rent_prices/2019_lloguer_preu_trim.csv', './data/rent_prices/2016_lloguer_preu_trim.csv', './data/rent_prices/2020_lloguer_preu_trim.csv', './data/rent_prices/2015_lloguer_preu_trim.csv', './data/rent_prices/2018_lloguer_preu_trim.csv']\n"
     ]
    }
   ],
   "source": [
    "# get data file names\n",
    "path =r'./data/rent_prices'\n",
    "filenames = glob.glob(path + \"/*.csv\")\n",
    "print(filenames)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 3,
   "metadata": {
    "collapsed": false,
    "jupyter": {
     "outputs_hidden": false
    }
   },
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "Size of ./data/rent_prices/2017_lloguer_preu_trim.csv: (584, 8)\n",
      "Size of ./data/rent_prices/2014_lloguer_preu_trim.csv: (584, 8)\n",
      "Size of ./data/rent_prices/2019_lloguer_preu_trim.csv: (584, 8)\n",
      "Size of ./data/rent_prices/2016_lloguer_preu_trim.csv: (584, 8)\n",
      "Size of ./data/rent_prices/2020_lloguer_preu_trim.csv: (584, 8)\n",
      "Size of ./data/rent_prices/2015_lloguer_preu_trim.csv: (584, 8)\n",
      "Size of ./data/rent_prices/2018_lloguer_preu_trim.csv: (584, 8)\n"
     ]
    }
   ],
   "source": [
    "# read all the *.csv inside rent_prices/\n",
    "all_rent_dfs = []\n",
    "for file in filenames:\n",
    "    df_per_year = pd.read_csv(file)\n",
    "    all_rent_dfs.append(df_per_year)\n",
    "    print(f'Size of {file}: {df_per_year.shape}')"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 4,
   "metadata": {
    "collapsed": false,
    "jupyter": {
     "outputs_hidden": false
    }
   },
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "(4088, 8)\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>Any</th>\n",
       "      <th>Trimestre</th>\n",
       "      <th>Codi_Districte</th>\n",
       "      <th>Nom_Districte</th>\n",
       "      <th>Codi_Barri</th>\n",
       "      <th>Nom_Barri</th>\n",
       "      <th>Lloguer_mitja</th>\n",
       "      <th>Preu</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>2017</td>\n",
       "      <td>1</td>\n",
       "      <td>1</td>\n",
       "      <td>Ciutat Vella</td>\n",
       "      <td>1</td>\n",
       "      <td>el Raval</td>\n",
       "      <td>Lloguer mitjà mensual (Euros/mes)</td>\n",
       "      <td>734.99</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>2017</td>\n",
       "      <td>1</td>\n",
       "      <td>1</td>\n",
       "      <td>Ciutat Vella</td>\n",
       "      <td>2</td>\n",
       "      <td>el Barri Gòtic</td>\n",
       "      <td>Lloguer mitjà mensual (Euros/mes)</td>\n",
       "      <td>905.26</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>2017</td>\n",
       "      <td>1</td>\n",
       "      <td>1</td>\n",
       "      <td>Ciutat Vella</td>\n",
       "      <td>3</td>\n",
       "      <td>la Barceloneta</td>\n",
       "      <td>Lloguer mitjà mensual (Euros/mes)</td>\n",
       "      <td>722.78</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>2017</td>\n",
       "      <td>1</td>\n",
       "      <td>1</td>\n",
       "      <td>Ciutat Vella</td>\n",
       "      <td>4</td>\n",
       "      <td>Sant Pere, Santa Caterina i la Ribera</td>\n",
       "      <td>Lloguer mitjà mensual (Euros/mes)</td>\n",
       "      <td>895.28</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>2017</td>\n",
       "      <td>1</td>\n",
       "      <td>2</td>\n",
       "      <td>Eixample</td>\n",
       "      <td>5</td>\n",
       "      <td>el Fort Pienc</td>\n",
       "      <td>Lloguer mitjà mensual (Euros/mes)</td>\n",
       "      <td>871.08</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "    Any  Trimestre  Codi_Districte Nom_Districte  Codi_Barri  \\\n",
       "0  2017          1               1  Ciutat Vella           1   \n",
       "1  2017          1               1  Ciutat Vella           2   \n",
       "2  2017          1               1  Ciutat Vella           3   \n",
       "3  2017          1               1  Ciutat Vella           4   \n",
       "4  2017          1               2      Eixample           5   \n",
       "\n",
       "                               Nom_Barri                      Lloguer_mitja  \\\n",
       "0                               el Raval  Lloguer mitjà mensual (Euros/mes)   \n",
       "1                         el Barri Gòtic  Lloguer mitjà mensual (Euros/mes)   \n",
       "2                         la Barceloneta  Lloguer mitjà mensual (Euros/mes)   \n",
       "3  Sant Pere, Santa Caterina i la Ribera  Lloguer mitjà mensual (Euros/mes)   \n",
       "4                          el Fort Pienc  Lloguer mitjà mensual (Euros/mes)   \n",
       "\n",
       "     Preu  \n",
       "0  734.99  \n",
       "1  905.26  \n",
       "2  722.78  \n",
       "3  895.28  \n",
       "4  871.08  "
      ]
     },
     "execution_count": 4,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# make a super-dataframe with all the years\n",
    "rent_df = pd.concat(all_rent_dfs, axis=0, ignore_index=True)\n",
    "print(rent_df.shape)\n",
    "rent_df.head()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 5,
   "metadata": {
    "collapsed": false,
    "jupyter": {
     "outputs_hidden": false
    }
   },
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>year</th>\n",
       "      <th>quarter</th>\n",
       "      <th>district_code</th>\n",
       "      <th>district_name</th>\n",
       "      <th>neighbourhood_code</th>\n",
       "      <th>neighbourhood_name</th>\n",
       "      <th>type_mean_rent</th>\n",
       "      <th>price</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>2017</td>\n",
       "      <td>1</td>\n",
       "      <td>1</td>\n",
       "      <td>Ciutat Vella</td>\n",
       "      <td>1</td>\n",
       "      <td>el Raval</td>\n",
       "      <td>Lloguer mitjà mensual (Euros/mes)</td>\n",
       "      <td>734.99</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>2017</td>\n",
       "      <td>1</td>\n",
       "      <td>1</td>\n",
       "      <td>Ciutat Vella</td>\n",
       "      <td>2</td>\n",
       "      <td>el Barri Gòtic</td>\n",
       "      <td>Lloguer mitjà mensual (Euros/mes)</td>\n",
       "      <td>905.26</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>2017</td>\n",
       "      <td>1</td>\n",
       "      <td>1</td>\n",
       "      <td>Ciutat Vella</td>\n",
       "      <td>3</td>\n",
       "      <td>la Barceloneta</td>\n",
       "      <td>Lloguer mitjà mensual (Euros/mes)</td>\n",
       "      <td>722.78</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>2017</td>\n",
       "      <td>1</td>\n",
       "      <td>1</td>\n",
       "      <td>Ciutat Vella</td>\n",
       "      <td>4</td>\n",
       "      <td>Sant Pere, Santa Caterina i la Ribera</td>\n",
       "      <td>Lloguer mitjà mensual (Euros/mes)</td>\n",
       "      <td>895.28</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>2017</td>\n",
       "      <td>1</td>\n",
       "      <td>2</td>\n",
       "      <td>Eixample</td>\n",
       "      <td>5</td>\n",
       "      <td>el Fort Pienc</td>\n",
       "      <td>Lloguer mitjà mensual (Euros/mes)</td>\n",
       "      <td>871.08</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "   year  quarter  district_code district_name  neighbourhood_code  \\\n",
       "0  2017        1              1  Ciutat Vella                   1   \n",
       "1  2017        1              1  Ciutat Vella                   2   \n",
       "2  2017        1              1  Ciutat Vella                   3   \n",
       "3  2017        1              1  Ciutat Vella                   4   \n",
       "4  2017        1              2      Eixample                   5   \n",
       "\n",
       "                      neighbourhood_name                     type_mean_rent  \\\n",
       "0                               el Raval  Lloguer mitjà mensual (Euros/mes)   \n",
       "1                         el Barri Gòtic  Lloguer mitjà mensual (Euros/mes)   \n",
       "2                         la Barceloneta  Lloguer mitjà mensual (Euros/mes)   \n",
       "3  Sant Pere, Santa Caterina i la Ribera  Lloguer mitjà mensual (Euros/mes)   \n",
       "4                          el Fort Pienc  Lloguer mitjà mensual (Euros/mes)   \n",
       "\n",
       "    price  \n",
       "0  734.99  \n",
       "1  905.26  \n",
       "2  722.78  \n",
       "3  895.28  \n",
       "4  871.08  "
      ]
     },
     "execution_count": 5,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# rename the columns\n",
    "rent_df = rent_df.rename(columns={'Any':'year',\n",
    "                   'Trimestre':'quarter', \n",
    "                   'Codi_Districte':'district_code', \n",
    "                   'Nom_Districte':'district_name', \n",
    "                   'Codi_Barri':'neighbourhood_code', \n",
    "                   'Nom_Barri':'neighbourhood_name', \n",
    "                   'Lloguer_mitja':'type_mean_rent', \n",
    "                   'Preu':'price'})\n",
    "rent_df.head()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 6,
   "metadata": {
    "collapsed": false,
    "jupyter": {
     "outputs_hidden": false
    }
   },
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>year</th>\n",
       "      <th>quarter</th>\n",
       "      <th>district_code</th>\n",
       "      <th>district_name</th>\n",
       "      <th>neighbourhood_code</th>\n",
       "      <th>neighbourhood_name</th>\n",
       "      <th>type_mean_rent</th>\n",
       "      <th>price</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>2017</td>\n",
       "      <td>1</td>\n",
       "      <td>1</td>\n",
       "      <td>Ciutat Vella</td>\n",
       "      <td>1</td>\n",
       "      <td>el Raval</td>\n",
       "      <td>Euros/month</td>\n",
       "      <td>734.99</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>2017</td>\n",
       "      <td>1</td>\n",
       "      <td>1</td>\n",
       "      <td>Ciutat Vella</td>\n",
       "      <td>2</td>\n",
       "      <td>el Barri Gòtic</td>\n",
       "      <td>Euros/month</td>\n",
       "      <td>905.26</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>2017</td>\n",
       "      <td>1</td>\n",
       "      <td>1</td>\n",
       "      <td>Ciutat Vella</td>\n",
       "      <td>3</td>\n",
       "      <td>la Barceloneta</td>\n",
       "      <td>Euros/month</td>\n",
       "      <td>722.78</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>2017</td>\n",
       "      <td>1</td>\n",
       "      <td>1</td>\n",
       "      <td>Ciutat Vella</td>\n",
       "      <td>4</td>\n",
       "      <td>Sant Pere, Santa Caterina i la Ribera</td>\n",
       "      <td>Euros/month</td>\n",
       "      <td>895.28</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>2017</td>\n",
       "      <td>1</td>\n",
       "      <td>2</td>\n",
       "      <td>Eixample</td>\n",
       "      <td>5</td>\n",
       "      <td>el Fort Pienc</td>\n",
       "      <td>Euros/month</td>\n",
       "      <td>871.08</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "   year  quarter  district_code district_name  neighbourhood_code  \\\n",
       "0  2017        1              1  Ciutat Vella                   1   \n",
       "1  2017        1              1  Ciutat Vella                   2   \n",
       "2  2017        1              1  Ciutat Vella                   3   \n",
       "3  2017        1              1  Ciutat Vella                   4   \n",
       "4  2017        1              2      Eixample                   5   \n",
       "\n",
       "                      neighbourhood_name type_mean_rent   price  \n",
       "0                               el Raval    Euros/month  734.99  \n",
       "1                         el Barri Gòtic    Euros/month  905.26  \n",
       "2                         la Barceloneta    Euros/month  722.78  \n",
       "3  Sant Pere, Santa Caterina i la Ribera    Euros/month  895.28  \n",
       "4                          el Fort Pienc    Euros/month  871.08  "
      ]
     },
     "execution_count": 6,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "rent_df = rent_df.assign(type_mean_rent= lambda df: \n",
    "                         df['type_mean_rent'].map({'Lloguer mitjà mensual (Euros/mes)':'Euros/month','Lloguer mitjà per superfície (Euros/m2 mes)':'Euros/m2 month'}))\n",
    "rent_df.head()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 7,
   "metadata": {
    "collapsed": false,
    "jupyter": {
     "outputs_hidden": false
    }
   },
   "outputs": [],
   "source": [
    "def make_period(row):\n",
    "    return f\"{row['year']}Q{row['quarter']}\""
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 8,
   "metadata": {
    "collapsed": false,
    "jupyter": {
     "outputs_hidden": false
    }
   },
   "outputs": [
    {
     "data": {
      "text/plain": [
       "0      2017-01-01\n",
       "1      2017-01-01\n",
       "2      2017-01-01\n",
       "3      2017-01-01\n",
       "4      2017-01-01\n",
       "          ...    \n",
       "4083   2018-10-01\n",
       "4084   2018-10-01\n",
       "4085   2018-10-01\n",
       "4086   2018-10-01\n",
       "4087   2018-10-01\n",
       "Name: period, Length: 4088, dtype: datetime64[ns]"
      ]
     },
     "execution_count": 8,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "rent_df['period'] = rent_df.apply(make_period, axis=1)\n",
    "rent_df['period'] = pd.to_datetime(rent_df['period'])\n",
    "rent_df['period']"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 9,
   "metadata": {
    "collapsed": false,
    "jupyter": {
     "outputs_hidden": false
    }
   },
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "(2044, 9)\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>year</th>\n",
       "      <th>quarter</th>\n",
       "      <th>district_code</th>\n",
       "      <th>district_name</th>\n",
       "      <th>neighbourhood_code</th>\n",
       "      <th>neighbourhood_name</th>\n",
       "      <th>type_mean_rent</th>\n",
       "      <th>price</th>\n",
       "      <th>period</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>2017</td>\n",
       "      <td>1</td>\n",
       "      <td>1</td>\n",
       "      <td>Ciutat Vella</td>\n",
       "      <td>1</td>\n",
       "      <td>el Raval</td>\n",
       "      <td>Euros/month</td>\n",
       "      <td>734.99</td>\n",
       "      <td>2017-01-01</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>2017</td>\n",
       "      <td>1</td>\n",
       "      <td>1</td>\n",
       "      <td>Ciutat Vella</td>\n",
       "      <td>2</td>\n",
       "      <td>el Barri Gòtic</td>\n",
       "      <td>Euros/month</td>\n",
       "      <td>905.26</td>\n",
       "      <td>2017-01-01</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>2017</td>\n",
       "      <td>1</td>\n",
       "      <td>1</td>\n",
       "      <td>Ciutat Vella</td>\n",
       "      <td>3</td>\n",
       "      <td>la Barceloneta</td>\n",
       "      <td>Euros/month</td>\n",
       "      <td>722.78</td>\n",
       "      <td>2017-01-01</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>2017</td>\n",
       "      <td>1</td>\n",
       "      <td>1</td>\n",
       "      <td>Ciutat Vella</td>\n",
       "      <td>4</td>\n",
       "      <td>Sant Pere, Santa Caterina i la Ribera</td>\n",
       "      <td>Euros/month</td>\n",
       "      <td>895.28</td>\n",
       "      <td>2017-01-01</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>2017</td>\n",
       "      <td>1</td>\n",
       "      <td>2</td>\n",
       "      <td>Eixample</td>\n",
       "      <td>5</td>\n",
       "      <td>el Fort Pienc</td>\n",
       "      <td>Euros/month</td>\n",
       "      <td>871.08</td>\n",
       "      <td>2017-01-01</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "   year  quarter  district_code district_name  neighbourhood_code  \\\n",
       "0  2017        1              1  Ciutat Vella                   1   \n",
       "1  2017        1              1  Ciutat Vella                   2   \n",
       "2  2017        1              1  Ciutat Vella                   3   \n",
       "3  2017        1              1  Ciutat Vella                   4   \n",
       "4  2017        1              2      Eixample                   5   \n",
       "\n",
       "                      neighbourhood_name type_mean_rent   price     period  \n",
       "0                               el Raval    Euros/month  734.99 2017-01-01  \n",
       "1                         el Barri Gòtic    Euros/month  905.26 2017-01-01  \n",
       "2                         la Barceloneta    Euros/month  722.78 2017-01-01  \n",
       "3  Sant Pere, Santa Caterina i la Ribera    Euros/month  895.28 2017-01-01  \n",
       "4                          el Fort Pienc    Euros/month  871.08 2017-01-01  "
      ]
     },
     "execution_count": 9,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "groupby_object = rent_df.groupby(by='type_mean_rent')\n",
    "cleaned_rent_df = groupby_object.get_group('Euros/month')\n",
    "print(cleaned_rent_df.shape)\n",
    "cleaned_rent_df.head()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 10,
   "metadata": {
    "collapsed": false,
    "jupyter": {
     "outputs_hidden": false
    }
   },
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>year</th>\n",
       "      <th>quarter</th>\n",
       "      <th>district_code</th>\n",
       "      <th>district_name</th>\n",
       "      <th>neighbourhood_code</th>\n",
       "      <th>neighbourhood_name</th>\n",
       "      <th>type_mean_rent</th>\n",
       "      <th>price_month(€)</th>\n",
       "      <th>period</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>2017</td>\n",
       "      <td>1</td>\n",
       "      <td>1</td>\n",
       "      <td>Ciutat Vella</td>\n",
       "      <td>1</td>\n",
       "      <td>el Raval</td>\n",
       "      <td>Euros/month</td>\n",
       "      <td>734.99</td>\n",
       "      <td>2017-01-01</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>2017</td>\n",
       "      <td>1</td>\n",
       "      <td>1</td>\n",
       "      <td>Ciutat Vella</td>\n",
       "      <td>2</td>\n",
       "      <td>el Barri Gòtic</td>\n",
       "      <td>Euros/month</td>\n",
       "      <td>905.26</td>\n",
       "      <td>2017-01-01</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>2017</td>\n",
       "      <td>1</td>\n",
       "      <td>1</td>\n",
       "      <td>Ciutat Vella</td>\n",
       "      <td>3</td>\n",
       "      <td>la Barceloneta</td>\n",
       "      <td>Euros/month</td>\n",
       "      <td>722.78</td>\n",
       "      <td>2017-01-01</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>2017</td>\n",
       "      <td>1</td>\n",
       "      <td>1</td>\n",
       "      <td>Ciutat Vella</td>\n",
       "      <td>4</td>\n",
       "      <td>Sant Pere, Santa Caterina i la Ribera</td>\n",
       "      <td>Euros/month</td>\n",
       "      <td>895.28</td>\n",
       "      <td>2017-01-01</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>2017</td>\n",
       "      <td>1</td>\n",
       "      <td>2</td>\n",
       "      <td>Eixample</td>\n",
       "      <td>5</td>\n",
       "      <td>el Fort Pienc</td>\n",
       "      <td>Euros/month</td>\n",
       "      <td>871.08</td>\n",
       "      <td>2017-01-01</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "   year  quarter  district_code district_name  neighbourhood_code  \\\n",
       "0  2017        1              1  Ciutat Vella                   1   \n",
       "1  2017        1              1  Ciutat Vella                   2   \n",
       "2  2017        1              1  Ciutat Vella                   3   \n",
       "3  2017        1              1  Ciutat Vella                   4   \n",
       "4  2017        1              2      Eixample                   5   \n",
       "\n",
       "                      neighbourhood_name type_mean_rent  price_month(€)  \\\n",
       "0                               el Raval    Euros/month          734.99   \n",
       "1                         el Barri Gòtic    Euros/month          905.26   \n",
       "2                         la Barceloneta    Euros/month          722.78   \n",
       "3  Sant Pere, Santa Caterina i la Ribera    Euros/month          895.28   \n",
       "4                          el Fort Pienc    Euros/month          871.08   \n",
       "\n",
       "      period  \n",
       "0 2017-01-01  \n",
       "1 2017-01-01  \n",
       "2 2017-01-01  \n",
       "3 2017-01-01  \n",
       "4 2017-01-01  "
      ]
     },
     "execution_count": 10,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "cleaned_rent_df = cleaned_rent_df.rename(columns={'price':'price_month(€)'})\n",
    "cleaned_rent_df.head()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 11,
   "metadata": {
    "collapsed": false,
    "jupyter": {
     "outputs_hidden": false
    }
   },
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>year</th>\n",
       "      <th>quarter</th>\n",
       "      <th>district_code</th>\n",
       "      <th>district_name</th>\n",
       "      <th>neighbourhood_code</th>\n",
       "      <th>neighbourhood_name</th>\n",
       "      <th>price_month(€)</th>\n",
       "      <th>period</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>2017</td>\n",
       "      <td>1</td>\n",
       "      <td>1</td>\n",
       "      <td>Ciutat Vella</td>\n",
       "      <td>1</td>\n",
       "      <td>el Raval</td>\n",
       "      <td>734.99</td>\n",
       "      <td>2017-01-01</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>2017</td>\n",
       "      <td>1</td>\n",
       "      <td>1</td>\n",
       "      <td>Ciutat Vella</td>\n",
       "      <td>2</td>\n",
       "      <td>el Barri Gòtic</td>\n",
       "      <td>905.26</td>\n",
       "      <td>2017-01-01</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>2017</td>\n",
       "      <td>1</td>\n",
       "      <td>1</td>\n",
       "      <td>Ciutat Vella</td>\n",
       "      <td>3</td>\n",
       "      <td>la Barceloneta</td>\n",
       "      <td>722.78</td>\n",
       "      <td>2017-01-01</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>2017</td>\n",
       "      <td>1</td>\n",
       "      <td>1</td>\n",
       "      <td>Ciutat Vella</td>\n",
       "      <td>4</td>\n",
       "      <td>Sant Pere, Santa Caterina i la Ribera</td>\n",
       "      <td>895.28</td>\n",
       "      <td>2017-01-01</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>2017</td>\n",
       "      <td>1</td>\n",
       "      <td>2</td>\n",
       "      <td>Eixample</td>\n",
       "      <td>5</td>\n",
       "      <td>el Fort Pienc</td>\n",
       "      <td>871.08</td>\n",
       "      <td>2017-01-01</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "   year  quarter  district_code district_name  neighbourhood_code  \\\n",
       "0  2017        1              1  Ciutat Vella                   1   \n",
       "1  2017        1              1  Ciutat Vella                   2   \n",
       "2  2017        1              1  Ciutat Vella                   3   \n",
       "3  2017        1              1  Ciutat Vella                   4   \n",
       "4  2017        1              2      Eixample                   5   \n",
       "\n",
       "                      neighbourhood_name  price_month(€)     period  \n",
       "0                               el Raval          734.99 2017-01-01  \n",
       "1                         el Barri Gòtic          905.26 2017-01-01  \n",
       "2                         la Barceloneta          722.78 2017-01-01  \n",
       "3  Sant Pere, Santa Caterina i la Ribera          895.28 2017-01-01  \n",
       "4                          el Fort Pienc          871.08 2017-01-01  "
      ]
     },
     "execution_count": 11,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "cleaned_rent_df.drop('type_mean_rent', axis=1, inplace=True)\n",
    "cleaned_rent_df.head()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 12,
   "metadata": {
    "collapsed": false,
    "jupyter": {
     "outputs_hidden": false
    }
   },
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>year</th>\n",
       "      <th>quarter</th>\n",
       "      <th>district_code</th>\n",
       "      <th>district_name</th>\n",
       "      <th>neighbourhood_code</th>\n",
       "      <th>neighbourhood_name</th>\n",
       "      <th>type_mean_rent</th>\n",
       "      <th>price</th>\n",
       "      <th>period</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>292</th>\n",
       "      <td>2017</td>\n",
       "      <td>1</td>\n",
       "      <td>1</td>\n",
       "      <td>Ciutat Vella</td>\n",
       "      <td>1</td>\n",
       "      <td>el Raval</td>\n",
       "      <td>Euros/m2 month</td>\n",
       "      <td>13.50</td>\n",
       "      <td>2017-01-01</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>293</th>\n",
       "      <td>2017</td>\n",
       "      <td>1</td>\n",
       "      <td>1</td>\n",
       "      <td>Ciutat Vella</td>\n",
       "      <td>2</td>\n",
       "      <td>el Barri Gòtic</td>\n",
       "      <td>Euros/m2 month</td>\n",
       "      <td>14.14</td>\n",
       "      <td>2017-01-01</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>294</th>\n",
       "      <td>2017</td>\n",
       "      <td>1</td>\n",
       "      <td>1</td>\n",
       "      <td>Ciutat Vella</td>\n",
       "      <td>3</td>\n",
       "      <td>la Barceloneta</td>\n",
       "      <td>Euros/m2 month</td>\n",
       "      <td>19.46</td>\n",
       "      <td>2017-01-01</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>295</th>\n",
       "      <td>2017</td>\n",
       "      <td>1</td>\n",
       "      <td>1</td>\n",
       "      <td>Ciutat Vella</td>\n",
       "      <td>4</td>\n",
       "      <td>Sant Pere, Santa Caterina i la Ribera</td>\n",
       "      <td>Euros/m2 month</td>\n",
       "      <td>15.01</td>\n",
       "      <td>2017-01-01</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>296</th>\n",
       "      <td>2017</td>\n",
       "      <td>1</td>\n",
       "      <td>2</td>\n",
       "      <td>Eixample</td>\n",
       "      <td>5</td>\n",
       "      <td>el Fort Pienc</td>\n",
       "      <td>Euros/m2 month</td>\n",
       "      <td>12.77</td>\n",
       "      <td>2017-01-01</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "     year  quarter  district_code district_name  neighbourhood_code  \\\n",
       "292  2017        1              1  Ciutat Vella                   1   \n",
       "293  2017        1              1  Ciutat Vella                   2   \n",
       "294  2017        1              1  Ciutat Vella                   3   \n",
       "295  2017        1              1  Ciutat Vella                   4   \n",
       "296  2017        1              2      Eixample                   5   \n",
       "\n",
       "                        neighbourhood_name  type_mean_rent  price     period  \n",
       "292                               el Raval  Euros/m2 month  13.50 2017-01-01  \n",
       "293                         el Barri Gòtic  Euros/m2 month  14.14 2017-01-01  \n",
       "294                         la Barceloneta  Euros/m2 month  19.46 2017-01-01  \n",
       "295  Sant Pere, Santa Caterina i la Ribera  Euros/m2 month  15.01 2017-01-01  \n",
       "296                          el Fort Pienc  Euros/m2 month  12.77 2017-01-01  "
      ]
     },
     "execution_count": 12,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "rent_m2_month_df = groupby_object.get_group('Euros/m2 month')\n",
    "rent_m2_month_df.head()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 13,
   "metadata": {
    "collapsed": false,
    "jupyter": {
     "outputs_hidden": false
    }
   },
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>year</th>\n",
       "      <th>quarter</th>\n",
       "      <th>district_code</th>\n",
       "      <th>district_name</th>\n",
       "      <th>neighbourhood_code</th>\n",
       "      <th>neighbourhood_name</th>\n",
       "      <th>price_month(€)</th>\n",
       "      <th>period</th>\n",
       "      <th>year_repeated</th>\n",
       "      <th>quarter_repeated</th>\n",
       "      <th>district_code_repeated</th>\n",
       "      <th>district_name_repeated</th>\n",
       "      <th>neighbourhood_name_repeated</th>\n",
       "      <th>type_mean_rent</th>\n",
       "      <th>price</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>2017</td>\n",
       "      <td>1</td>\n",
       "      <td>1</td>\n",
       "      <td>Ciutat Vella</td>\n",
       "      <td>1</td>\n",
       "      <td>el Raval</td>\n",
       "      <td>734.99</td>\n",
       "      <td>2017-01-01</td>\n",
       "      <td>2017</td>\n",
       "      <td>1</td>\n",
       "      <td>1</td>\n",
       "      <td>Ciutat Vella</td>\n",
       "      <td>el Raval</td>\n",
       "      <td>Euros/m2 month</td>\n",
       "      <td>13.50</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>2017</td>\n",
       "      <td>1</td>\n",
       "      <td>1</td>\n",
       "      <td>Ciutat Vella</td>\n",
       "      <td>2</td>\n",
       "      <td>el Barri Gòtic</td>\n",
       "      <td>905.26</td>\n",
       "      <td>2017-01-01</td>\n",
       "      <td>2017</td>\n",
       "      <td>1</td>\n",
       "      <td>1</td>\n",
       "      <td>Ciutat Vella</td>\n",
       "      <td>el Barri Gòtic</td>\n",
       "      <td>Euros/m2 month</td>\n",
       "      <td>14.14</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>2017</td>\n",
       "      <td>1</td>\n",
       "      <td>1</td>\n",
       "      <td>Ciutat Vella</td>\n",
       "      <td>3</td>\n",
       "      <td>la Barceloneta</td>\n",
       "      <td>722.78</td>\n",
       "      <td>2017-01-01</td>\n",
       "      <td>2017</td>\n",
       "      <td>1</td>\n",
       "      <td>1</td>\n",
       "      <td>Ciutat Vella</td>\n",
       "      <td>la Barceloneta</td>\n",
       "      <td>Euros/m2 month</td>\n",
       "      <td>19.46</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>2017</td>\n",
       "      <td>1</td>\n",
       "      <td>1</td>\n",
       "      <td>Ciutat Vella</td>\n",
       "      <td>4</td>\n",
       "      <td>Sant Pere, Santa Caterina i la Ribera</td>\n",
       "      <td>895.28</td>\n",
       "      <td>2017-01-01</td>\n",
       "      <td>2017</td>\n",
       "      <td>1</td>\n",
       "      <td>1</td>\n",
       "      <td>Ciutat Vella</td>\n",
       "      <td>Sant Pere, Santa Caterina i la Ribera</td>\n",
       "      <td>Euros/m2 month</td>\n",
       "      <td>15.01</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>2017</td>\n",
       "      <td>1</td>\n",
       "      <td>2</td>\n",
       "      <td>Eixample</td>\n",
       "      <td>5</td>\n",
       "      <td>el Fort Pienc</td>\n",
       "      <td>871.08</td>\n",
       "      <td>2017-01-01</td>\n",
       "      <td>2017</td>\n",
       "      <td>1</td>\n",
       "      <td>2</td>\n",
       "      <td>Eixample</td>\n",
       "      <td>el Fort Pienc</td>\n",
       "      <td>Euros/m2 month</td>\n",
       "      <td>12.77</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "   year  quarter  district_code district_name  neighbourhood_code  \\\n",
       "0  2017        1              1  Ciutat Vella                   1   \n",
       "1  2017        1              1  Ciutat Vella                   2   \n",
       "2  2017        1              1  Ciutat Vella                   3   \n",
       "3  2017        1              1  Ciutat Vella                   4   \n",
       "4  2017        1              2      Eixample                   5   \n",
       "\n",
       "                      neighbourhood_name  price_month(€)     period  \\\n",
       "0                               el Raval          734.99 2017-01-01   \n",
       "1                         el Barri Gòtic          905.26 2017-01-01   \n",
       "2                         la Barceloneta          722.78 2017-01-01   \n",
       "3  Sant Pere, Santa Caterina i la Ribera          895.28 2017-01-01   \n",
       "4                          el Fort Pienc          871.08 2017-01-01   \n",
       "\n",
       "   year_repeated  quarter_repeated  district_code_repeated  \\\n",
       "0           2017                 1                       1   \n",
       "1           2017                 1                       1   \n",
       "2           2017                 1                       1   \n",
       "3           2017                 1                       1   \n",
       "4           2017                 1                       2   \n",
       "\n",
       "  district_name_repeated            neighbourhood_name_repeated  \\\n",
       "0           Ciutat Vella                               el Raval   \n",
       "1           Ciutat Vella                         el Barri Gòtic   \n",
       "2           Ciutat Vella                         la Barceloneta   \n",
       "3           Ciutat Vella  Sant Pere, Santa Caterina i la Ribera   \n",
       "4               Eixample                          el Fort Pienc   \n",
       "\n",
       "   type_mean_rent  price  \n",
       "0  Euros/m2 month  13.50  \n",
       "1  Euros/m2 month  14.14  \n",
       "2  Euros/m2 month  19.46  \n",
       "3  Euros/m2 month  15.01  \n",
       "4  Euros/m2 month  12.77  "
      ]
     },
     "execution_count": 13,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "cleaned_rent_df = cleaned_rent_df.merge(rent_m2_month_df, how='left', on=['neighbourhood_code', 'period'], suffixes=('', '_repeated'))\n",
    "cleaned_rent_df.head()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 14,
   "metadata": {
    "collapsed": false,
    "jupyter": {
     "outputs_hidden": false
    }
   },
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>year</th>\n",
       "      <th>quarter</th>\n",
       "      <th>district_code</th>\n",
       "      <th>district_name</th>\n",
       "      <th>neighbourhood_code</th>\n",
       "      <th>neighbourhood_name</th>\n",
       "      <th>price_month(€)</th>\n",
       "      <th>period</th>\n",
       "      <th>type_mean_rent</th>\n",
       "      <th>price</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>2017</td>\n",
       "      <td>1</td>\n",
       "      <td>1</td>\n",
       "      <td>Ciutat Vella</td>\n",
       "      <td>1</td>\n",
       "      <td>el Raval</td>\n",
       "      <td>734.99</td>\n",
       "      <td>2017-01-01</td>\n",
       "      <td>Euros/m2 month</td>\n",
       "      <td>13.50</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>2017</td>\n",
       "      <td>1</td>\n",
       "      <td>1</td>\n",
       "      <td>Ciutat Vella</td>\n",
       "      <td>2</td>\n",
       "      <td>el Barri Gòtic</td>\n",
       "      <td>905.26</td>\n",
       "      <td>2017-01-01</td>\n",
       "      <td>Euros/m2 month</td>\n",
       "      <td>14.14</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>2017</td>\n",
       "      <td>1</td>\n",
       "      <td>1</td>\n",
       "      <td>Ciutat Vella</td>\n",
       "      <td>3</td>\n",
       "      <td>la Barceloneta</td>\n",
       "      <td>722.78</td>\n",
       "      <td>2017-01-01</td>\n",
       "      <td>Euros/m2 month</td>\n",
       "      <td>19.46</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>2017</td>\n",
       "      <td>1</td>\n",
       "      <td>1</td>\n",
       "      <td>Ciutat Vella</td>\n",
       "      <td>4</td>\n",
       "      <td>Sant Pere, Santa Caterina i la Ribera</td>\n",
       "      <td>895.28</td>\n",
       "      <td>2017-01-01</td>\n",
       "      <td>Euros/m2 month</td>\n",
       "      <td>15.01</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>2017</td>\n",
       "      <td>1</td>\n",
       "      <td>2</td>\n",
       "      <td>Eixample</td>\n",
       "      <td>5</td>\n",
       "      <td>el Fort Pienc</td>\n",
       "      <td>871.08</td>\n",
       "      <td>2017-01-01</td>\n",
       "      <td>Euros/m2 month</td>\n",
       "      <td>12.77</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "   year  quarter  district_code district_name  neighbourhood_code  \\\n",
       "0  2017        1              1  Ciutat Vella                   1   \n",
       "1  2017        1              1  Ciutat Vella                   2   \n",
       "2  2017        1              1  Ciutat Vella                   3   \n",
       "3  2017        1              1  Ciutat Vella                   4   \n",
       "4  2017        1              2      Eixample                   5   \n",
       "\n",
       "                      neighbourhood_name  price_month(€)     period  \\\n",
       "0                               el Raval          734.99 2017-01-01   \n",
       "1                         el Barri Gòtic          905.26 2017-01-01   \n",
       "2                         la Barceloneta          722.78 2017-01-01   \n",
       "3  Sant Pere, Santa Caterina i la Ribera          895.28 2017-01-01   \n",
       "4                          el Fort Pienc          871.08 2017-01-01   \n",
       "\n",
       "   type_mean_rent  price  \n",
       "0  Euros/m2 month  13.50  \n",
       "1  Euros/m2 month  14.14  \n",
       "2  Euros/m2 month  19.46  \n",
       "3  Euros/m2 month  15.01  \n",
       "4  Euros/m2 month  12.77  "
      ]
     },
     "execution_count": 14,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "cleaned_rent_df = cleaned_rent_df.drop(list(cleaned_rent_df.filter(regex='_repeated')), axis=1)\n",
    "cleaned_rent_df.head()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 15,
   "metadata": {
    "collapsed": false,
    "jupyter": {
     "outputs_hidden": false
    }
   },
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>year</th>\n",
       "      <th>quarter</th>\n",
       "      <th>district_code</th>\n",
       "      <th>district_name</th>\n",
       "      <th>neighbourhood_code</th>\n",
       "      <th>neighbourhood_name</th>\n",
       "      <th>price_month(€)</th>\n",
       "      <th>period</th>\n",
       "      <th>price</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>2017</td>\n",
       "      <td>1</td>\n",
       "      <td>1</td>\n",
       "      <td>Ciutat Vella</td>\n",
       "      <td>1</td>\n",
       "      <td>el Raval</td>\n",
       "      <td>734.99</td>\n",
       "      <td>2017-01-01</td>\n",
       "      <td>13.50</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>2017</td>\n",
       "      <td>1</td>\n",
       "      <td>1</td>\n",
       "      <td>Ciutat Vella</td>\n",
       "      <td>2</td>\n",
       "      <td>el Barri Gòtic</td>\n",
       "      <td>905.26</td>\n",
       "      <td>2017-01-01</td>\n",
       "      <td>14.14</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>2017</td>\n",
       "      <td>1</td>\n",
       "      <td>1</td>\n",
       "      <td>Ciutat Vella</td>\n",
       "      <td>3</td>\n",
       "      <td>la Barceloneta</td>\n",
       "      <td>722.78</td>\n",
       "      <td>2017-01-01</td>\n",
       "      <td>19.46</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>2017</td>\n",
       "      <td>1</td>\n",
       "      <td>1</td>\n",
       "      <td>Ciutat Vella</td>\n",
       "      <td>4</td>\n",
       "      <td>Sant Pere, Santa Caterina i la Ribera</td>\n",
       "      <td>895.28</td>\n",
       "      <td>2017-01-01</td>\n",
       "      <td>15.01</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>2017</td>\n",
       "      <td>1</td>\n",
       "      <td>2</td>\n",
       "      <td>Eixample</td>\n",
       "      <td>5</td>\n",
       "      <td>el Fort Pienc</td>\n",
       "      <td>871.08</td>\n",
       "      <td>2017-01-01</td>\n",
       "      <td>12.77</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "   year  quarter  district_code district_name  neighbourhood_code  \\\n",
       "0  2017        1              1  Ciutat Vella                   1   \n",
       "1  2017        1              1  Ciutat Vella                   2   \n",
       "2  2017        1              1  Ciutat Vella                   3   \n",
       "3  2017        1              1  Ciutat Vella                   4   \n",
       "4  2017        1              2      Eixample                   5   \n",
       "\n",
       "                      neighbourhood_name  price_month(€)     period  price  \n",
       "0                               el Raval          734.99 2017-01-01  13.50  \n",
       "1                         el Barri Gòtic          905.26 2017-01-01  14.14  \n",
       "2                         la Barceloneta          722.78 2017-01-01  19.46  \n",
       "3  Sant Pere, Santa Caterina i la Ribera          895.28 2017-01-01  15.01  \n",
       "4                          el Fort Pienc          871.08 2017-01-01  12.77  "
      ]
     },
     "execution_count": 15,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "cleaned_rent_df.drop('type_mean_rent', axis=1, inplace=True)\n",
    "cleaned_rent_df.head()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 16,
   "metadata": {
    "collapsed": false,
    "jupyter": {
     "outputs_hidden": false
    }
   },
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>year</th>\n",
       "      <th>quarter</th>\n",
       "      <th>district_code</th>\n",
       "      <th>district_name</th>\n",
       "      <th>neighbourhood_code</th>\n",
       "      <th>neighbourhood_name</th>\n",
       "      <th>price_month(€)</th>\n",
       "      <th>period</th>\n",
       "      <th>price_m2_month(€)</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>2017</td>\n",
       "      <td>1</td>\n",
       "      <td>1</td>\n",
       "      <td>Ciutat Vella</td>\n",
       "      <td>1</td>\n",
       "      <td>el Raval</td>\n",
       "      <td>734.99</td>\n",
       "      <td>2017-01-01</td>\n",
       "      <td>13.50</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>2017</td>\n",
       "      <td>1</td>\n",
       "      <td>1</td>\n",
       "      <td>Ciutat Vella</td>\n",
       "      <td>2</td>\n",
       "      <td>el Barri Gòtic</td>\n",
       "      <td>905.26</td>\n",
       "      <td>2017-01-01</td>\n",
       "      <td>14.14</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>2017</td>\n",
       "      <td>1</td>\n",
       "      <td>1</td>\n",
       "      <td>Ciutat Vella</td>\n",
       "      <td>3</td>\n",
       "      <td>la Barceloneta</td>\n",
       "      <td>722.78</td>\n",
       "      <td>2017-01-01</td>\n",
       "      <td>19.46</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>2017</td>\n",
       "      <td>1</td>\n",
       "      <td>1</td>\n",
       "      <td>Ciutat Vella</td>\n",
       "      <td>4</td>\n",
       "      <td>Sant Pere, Santa Caterina i la Ribera</td>\n",
       "      <td>895.28</td>\n",
       "      <td>2017-01-01</td>\n",
       "      <td>15.01</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>2017</td>\n",
       "      <td>1</td>\n",
       "      <td>2</td>\n",
       "      <td>Eixample</td>\n",
       "      <td>5</td>\n",
       "      <td>el Fort Pienc</td>\n",
       "      <td>871.08</td>\n",
       "      <td>2017-01-01</td>\n",
       "      <td>12.77</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "   year  quarter  district_code district_name  neighbourhood_code  \\\n",
       "0  2017        1              1  Ciutat Vella                   1   \n",
       "1  2017        1              1  Ciutat Vella                   2   \n",
       "2  2017        1              1  Ciutat Vella                   3   \n",
       "3  2017        1              1  Ciutat Vella                   4   \n",
       "4  2017        1              2      Eixample                   5   \n",
       "\n",
       "                      neighbourhood_name  price_month(€)     period  \\\n",
       "0                               el Raval          734.99 2017-01-01   \n",
       "1                         el Barri Gòtic          905.26 2017-01-01   \n",
       "2                         la Barceloneta          722.78 2017-01-01   \n",
       "3  Sant Pere, Santa Caterina i la Ribera          895.28 2017-01-01   \n",
       "4                          el Fort Pienc          871.08 2017-01-01   \n",
       "\n",
       "   price_m2_month(€)  \n",
       "0              13.50  \n",
       "1              14.14  \n",
       "2              19.46  \n",
       "3              15.01  \n",
       "4              12.77  "
      ]
     },
     "execution_count": 16,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "cleaned_rent_df = cleaned_rent_df.rename(columns={'price':'price_m2_month(€)'})\n",
    "cleaned_rent_df.head()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 17,
   "metadata": {
    "collapsed": false,
    "jupyter": {
     "outputs_hidden": false
    }
   },
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>year</th>\n",
       "      <th>quarter</th>\n",
       "      <th>period</th>\n",
       "      <th>district_code</th>\n",
       "      <th>district_name</th>\n",
       "      <th>neighbourhood_code</th>\n",
       "      <th>neighbourhood_name</th>\n",
       "      <th>price_month(€)</th>\n",
       "      <th>price_m2_month(€)</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>2017</td>\n",
       "      <td>1</td>\n",
       "      <td>2017-01-01</td>\n",
       "      <td>1</td>\n",
       "      <td>Ciutat Vella</td>\n",
       "      <td>1</td>\n",
       "      <td>el Raval</td>\n",
       "      <td>734.99</td>\n",
       "      <td>13.50</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>2017</td>\n",
       "      <td>1</td>\n",
       "      <td>2017-01-01</td>\n",
       "      <td>1</td>\n",
       "      <td>Ciutat Vella</td>\n",
       "      <td>2</td>\n",
       "      <td>el Barri Gòtic</td>\n",
       "      <td>905.26</td>\n",
       "      <td>14.14</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>2017</td>\n",
       "      <td>1</td>\n",
       "      <td>2017-01-01</td>\n",
       "      <td>1</td>\n",
       "      <td>Ciutat Vella</td>\n",
       "      <td>3</td>\n",
       "      <td>la Barceloneta</td>\n",
       "      <td>722.78</td>\n",
       "      <td>19.46</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>2017</td>\n",
       "      <td>1</td>\n",
       "      <td>2017-01-01</td>\n",
       "      <td>1</td>\n",
       "      <td>Ciutat Vella</td>\n",
       "      <td>4</td>\n",
       "      <td>Sant Pere, Santa Caterina i la Ribera</td>\n",
       "      <td>895.28</td>\n",
       "      <td>15.01</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>2017</td>\n",
       "      <td>1</td>\n",
       "      <td>2017-01-01</td>\n",
       "      <td>2</td>\n",
       "      <td>Eixample</td>\n",
       "      <td>5</td>\n",
       "      <td>el Fort Pienc</td>\n",
       "      <td>871.08</td>\n",
       "      <td>12.77</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "   year  quarter     period  district_code district_name  neighbourhood_code  \\\n",
       "0  2017        1 2017-01-01              1  Ciutat Vella                   1   \n",
       "1  2017        1 2017-01-01              1  Ciutat Vella                   2   \n",
       "2  2017        1 2017-01-01              1  Ciutat Vella                   3   \n",
       "3  2017        1 2017-01-01              1  Ciutat Vella                   4   \n",
       "4  2017        1 2017-01-01              2      Eixample                   5   \n",
       "\n",
       "                      neighbourhood_name  price_month(€)  price_m2_month(€)  \n",
       "0                               el Raval          734.99              13.50  \n",
       "1                         el Barri Gòtic          905.26              14.14  \n",
       "2                         la Barceloneta          722.78              19.46  \n",
       "3  Sant Pere, Santa Caterina i la Ribera          895.28              15.01  \n",
       "4                          el Fort Pienc          871.08              12.77  "
      ]
     },
     "execution_count": 17,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "cleaned_rent_df = cleaned_rent_df.reindex(columns=['year', 'quarter', 'period', 'district_code', 'district_name', 'neighbourhood_code', 'neighbourhood_name', 'price_month(€)', 'price_m2_month(€)'])\n",
    "cleaned_rent_df.head()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 31,
   "metadata": {
    "collapsed": false,
    "jupyter": {
     "outputs_hidden": false
    }
   },
   "outputs": [],
   "source": [
    "cleaned_rent_df.to_csv('./data/cleaned/rent/rent_prices.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.8.5"
  }
 },
 "nbformat": 4,
 "nbformat_minor": 4
}