src/local/data_processing_start.ipynb

{
 "cells": [
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "# Introduction to the data processing with PySpark"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 1,
   "metadata": {},
   "outputs": [],
   "source": [
    "import pandas as pd\n",
    "from pyspark.sql import SparkSession"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## Import data \n",
    "Import the lausanne rental data stored in `../../data/lausanne_rentl.csv` as a Dataframe."
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "**Pandas**"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 2,
   "metadata": {},
   "outputs": [],
   "source": [
    "df = "
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "**PySpark**"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 3,
   "metadata": {},
   "outputs": [],
   "source": [
    "spark = SparkSession.builder.getOrCreate()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 4,
   "metadata": {},
   "outputs": [],
   "source": [
    "dfs = "
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## Preprocessing"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Quick look: show the first 5 rows of the dataframe. "
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "**Pandas**"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": []
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "**Spark**"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": []
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "**Possible convert to pandas:** "
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "As you see the PySpark output is not as nice as the Pandas. Any solutions? Can you pass in the output of PySpark to a Pandas dataframe? "
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "pd.DataFrame()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### Get column names"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Get the column names:"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "**Pandas**"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": []
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "**PySpark**"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": []
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### Get types"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "**Pandas**"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": []
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "**PySpark**"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": []
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Note that the types in PySpark dont match with those of Pandas. This is becauase pandas automatically recognizes (guesses) the types, while in PySpark we need to explicitely specify the types. "
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### Converting column types (or Add columns)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "**Pandas**"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "As you saw above the `id` column is in `float24` format. Can you convert it to `int`?"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 29,
   "metadata": {},
   "outputs": [],
   "source": [
    "df['Id'] = df['Id']. # complete"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "df.dtypes # now check if it worked"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "**PySpark**"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "As you saw above all the PySpark dataframe types were reported as `string`. Now we want to convert `Id` to `int`, `SurfaceArea`, `Rent`, `NumRooms` to `float`. "
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 50,
   "metadata": {},
   "outputs": [],
   "source": [
    "dfs = dfs.withColumn() # complete\n",
    "dfs = dfs.withColumn()\n",
    "dfs = dfs.withColumn()\n",
    "dfs = dfs.withColumn()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "dfs.dtypes # check if it worked"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### Rename Columns"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "**Pandas**"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 9,
   "metadata": {},
   "outputs": [],
   "source": [
    "dftmp = df.copy()  # if you don't put the .copy() dftmp \"points\" to the same dataframe as df, so any change of dftmp affects df"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 10,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "Index(['Id', 'SurfaceArea', 'NumRooms', 'Type', 'Address', 'Description',\n",
       "       'Rent', 'Bookmark', 'Link'],\n",
       "      dtype='object')"
      ]
     },
     "execution_count": 10,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "dftmp.columns"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Rename the dftmp columns to A, B, C, D, E, F, G, H, I:"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 11,
   "metadata": {},
   "outputs": [],
   "source": [
    "dftmp.columns =  "
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "dftmp.columns # check"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Now rename only column A back to Id: "
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 13,
   "metadata": {},
   "outputs": [],
   "source": [
    "dftmp = "
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "dftmp.head() # check if it worked"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "**PySpark**"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Rename the dftmp columns to A, B, C, D, E, F, G, H, I:"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "dfs. # complete. Hint: Use dfs.toDF(?) note that the changes in PySpark are immutable"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "dfs.columns # check"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Now rename only column A back to Id: "
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "dfs. # complete again immutable"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "dfs.columns # check"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### Drop columns"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "**Pandas**"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Drop the Id column."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "df # Note: is immutable"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "**PySpark**:"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "dfs"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### Summary Statistics: Describe"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "**Pandas**"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": []
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "**PySpark**"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": []
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Can you show the output more nicely? Hint: use `.toPandas()`. "
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": []
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### Slicing (Filtering)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "**Pandas**"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Define a dataframe from `df` of apartment listings with a surface area less than 50 m2. "
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "**PySpark**"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": []
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### Aggregate functions"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "**Pandas**"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 96,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "1993.539115646258"
      ]
     },
     "execution_count": 96,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df['Rent'].mean()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 108,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "8900.0"
      ]
     },
     "execution_count": 108,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df['Rent'].max()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "**PySpark**"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 98,
   "metadata": {},
   "outputs": [
    {
     "ename": "TypeError",
     "evalue": "'Column' object is not callable",
     "output_type": "error",
     "traceback": [
      "\u001b[0;31m---------------------------------------------------------------------------\u001b[0m",
      "\u001b[0;31mTypeError\u001b[0m                                 Traceback (most recent call last)",
      "\u001b[0;32m<ipython-input-98-31bcc57b9a35>\u001b[0m in \u001b[0;36m<module>\u001b[0;34m()\u001b[0m\n\u001b[0;32m----> 1\u001b[0;31m \u001b[0mdfs\u001b[0m\u001b[0;34m[\u001b[0m\u001b[0;34m'Rent'\u001b[0m\u001b[0;34m]\u001b[0m\u001b[0;34m.\u001b[0m\u001b[0mmean\u001b[0m\u001b[0;34m(\u001b[0m\u001b[0;34m)\u001b[0m\u001b[0;34m\u001b[0m\u001b[0m\n\u001b[0m",
      "\u001b[0;31mTypeError\u001b[0m: 'Column' object is not callable"
     ]
    }
   ],
   "source": [
    "dfs['Rent'].mean()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Does not work out the same way! You need to first download the PySpark SQL functions:"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 99,
   "metadata": {},
   "outputs": [],
   "source": [
    "from pyspark.sql import functions as F"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 142,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "+------------------+\n",
      "|         avg(Rent)|\n",
      "+------------------+\n",
      "|1984.3287904599658|\n",
      "+------------------+\n",
      "\n"
     ]
    }
   ],
   "source": [
    "dfs.select(F.mean('Rent')).show()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 128,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "+---------+\n",
      "|max(Rent)|\n",
      "+---------+\n",
      "|   8900.0|\n",
      "+---------+\n",
      "\n"
     ]
    }
   ],
   "source": [
    "dfs.select(F.max('Rent')).show()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "If you want to get the value:"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 165,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "1984.3287904599658"
      ]
     },
     "execution_count": 165,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "value = dfs.select(F.mean('Rent')).collect()[0].asDict()['avg(Rent)']\n",
    "value"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### Fill Nulls"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "**Pandas**"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 240,
   "metadata": {},
   "outputs": [],
   "source": [
    "df['Rent'] = df['Rent'].fillna(df['Rent'].mean()) # generally not a good way of filling nan, how about median? How about filling based on correlation with other features? \n",
    "df['NumRooms'] = df['NumRooms'].fillna(df['NumRooms'].mean())"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "**PySpark**"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 175,
   "metadata": {},
   "outputs": [],
   "source": [
    "dfs = dfs.fillna(value, subset=['Rent'])"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 252,
   "metadata": {},
   "outputs": [],
   "source": [
    "value_numrooms = dfs.select(F.mean('NumRooms')).collect()[0].asDict()['avg(NumRooms)']"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 253,
   "metadata": {},
   "outputs": [],
   "source": [
    "dfs = dfs.fillna(value_numrooms, subset=['NumRooms'])"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### Aggregation"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "What is the averagne rent for apartments with different room numbers in Lausanne? So, we want a list of room numbers with their average rent. "
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "**Pandas**"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": []
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "**PySpark**"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": []
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Can you list the average surface area as well as the average rent?"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": []
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### Transformations"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "**Pandas**"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 195,
   "metadata": {},
   "outputs": [],
   "source": [
    "import numpy as np"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 197,
   "metadata": {},
   "outputs": [],
   "source": [
    "df['logRent'] = np.log(df['Rent'])"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 201,
   "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>SurfaceArea</th>\n",
       "      <th>NumRooms</th>\n",
       "      <th>Type</th>\n",
       "      <th>Address</th>\n",
       "      <th>Description</th>\n",
       "      <th>Rent</th>\n",
       "      <th>Bookmark</th>\n",
       "      <th>Link</th>\n",
       "      <th>logRent</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>5286965</td>\n",
       "      <td>52.0</td>\n",
       "      <td>2.5</td>\n",
       "      <td>flat</td>\n",
       "      <td>Route Aloys-Fauquez 124, 1018 Lausanne, VD</td>\n",
       "      <td>Agréable 2 pièces proche de toutes les commodités</td>\n",
       "      <td>1460.0</td>\n",
       "      <td>New</td>\n",
       "      <td>/en/d/flat-rent-lausanne/5286965?s=2&amp;t=1&amp;l=202...</td>\n",
       "      <td>7.286192</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>5277530</td>\n",
       "      <td>80.0</td>\n",
       "      <td>3.0</td>\n",
       "      <td>flat</td>\n",
       "      <td>Avenue de Béthusy 53, 1012 Lausanne, VD</td>\n",
       "      <td>Proche du centre ville</td>\n",
       "      <td>2100.0</td>\n",
       "      <td>NaN</td>\n",
       "      <td>/en/d/flat-rent-lausanne/5277530?s=2&amp;t=1&amp;l=202...</td>\n",
       "      <td>7.649693</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>5249019</td>\n",
       "      <td>74.0</td>\n",
       "      <td>3.5</td>\n",
       "      <td>stepped-apartment</td>\n",
       "      <td>Route des Plaines-du-Loup 42, 1018 Lausanne, VD</td>\n",
       "      <td>Magnifique 3,5 pièces avec jardin privé -Immeu...</td>\n",
       "      <td>2150.0</td>\n",
       "      <td>Highlight</td>\n",
       "      <td>/en/d/stepped-apartment-rent-lausanne/5249019?...</td>\n",
       "      <td>7.673223</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>5274375</td>\n",
       "      <td>108.0</td>\n",
       "      <td>3.5</td>\n",
       "      <td>duplex-maisonette</td>\n",
       "      <td>Route de Prilly 12, 1004 Lausanne, VD</td>\n",
       "      <td>Magnifique logement en duplex entièrement neuf</td>\n",
       "      <td>3160.0</td>\n",
       "      <td>NaN</td>\n",
       "      <td>/en/d/duplex-maisonette-rent-lausanne/5274375?...</td>\n",
       "      <td>8.058327</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>5274374</td>\n",
       "      <td>80.0</td>\n",
       "      <td>3.5</td>\n",
       "      <td>flat</td>\n",
       "      <td>Route de Prilly 12, 1004 Lausanne, VD</td>\n",
       "      <td>Nouveaux logements</td>\n",
       "      <td>2085.0</td>\n",
       "      <td>NaN</td>\n",
       "      <td>/en/d/flat-rent-lausanne/5274374?s=2&amp;t=1&amp;l=202...</td>\n",
       "      <td>7.642524</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "        Id  SurfaceArea  NumRooms               Type  \\\n",
       "0  5286965         52.0       2.5               flat   \n",
       "1  5277530         80.0       3.0               flat   \n",
       "2  5249019         74.0       3.5  stepped-apartment   \n",
       "3  5274375        108.0       3.5  duplex-maisonette   \n",
       "4  5274374         80.0       3.5               flat   \n",
       "\n",
       "                                           Address  \\\n",
       "0       Route Aloys-Fauquez 124, 1018 Lausanne, VD   \n",
       "1          Avenue de Béthusy 53, 1012 Lausanne, VD   \n",
       "2  Route des Plaines-du-Loup 42, 1018 Lausanne, VD   \n",
       "3            Route de Prilly 12, 1004 Lausanne, VD   \n",
       "4            Route de Prilly 12, 1004 Lausanne, VD   \n",
       "\n",
       "                                         Description    Rent   Bookmark  \\\n",
       "0  Agréable 2 pièces proche de toutes les commodités  1460.0        New   \n",
       "1                             Proche du centre ville  2100.0        NaN   \n",
       "2  Magnifique 3,5 pièces avec jardin privé -Immeu...  2150.0  Highlight   \n",
       "3     Magnifique logement en duplex entièrement neuf  3160.0        NaN   \n",
       "4                                 Nouveaux logements  2085.0        NaN   \n",
       "\n",
       "                                                Link   logRent  \n",
       "0  /en/d/flat-rent-lausanne/5286965?s=2&t=1&l=202...  7.286192  \n",
       "1  /en/d/flat-rent-lausanne/5277530?s=2&t=1&l=202...  7.649693  \n",
       "2  /en/d/stepped-apartment-rent-lausanne/5249019?...  7.673223  \n",
       "3  /en/d/duplex-maisonette-rent-lausanne/5274375?...  8.058327  \n",
       "4  /en/d/flat-rent-lausanne/5274374?s=2&t=1&l=202...  7.642524  "
      ]
     },
     "execution_count": 201,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df.head()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "**PySpark**"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 196,
   "metadata": {},
   "outputs": [],
   "source": [
    "import pyspark.sql.functions as F"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 203,
   "metadata": {},
   "outputs": [],
   "source": [
    "dfs = dfs.withColumn('logRent', F.log(dfs['Rent']))"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 200,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "+-------+-----------+--------+--------------------+--------------------+--------------------+------+---------+--------------------+-----------------+\n",
      "|     Id|SurfaceArea|NumRooms|                Type|             Address|         Description|  Rent| Bookmark|                Link|          logRent|\n",
      "+-------+-----------+--------+--------------------+--------------------+--------------------+------+---------+--------------------+-----------------+\n",
      "|5286965|       52.0|     2.5|                flat|Route Aloys-Fauqu...|Agréable 2 pièces...|1460.0|      New|/en/d/flat-rent-l...|7.286191714702382|\n",
      "|5277530|       80.0|     3.0|                flat|Avenue de Béthusy...|Proche du centre ...|2100.0|     null|/en/d/flat-rent-l...|7.649692623711514|\n",
      "|5249019|       74.0|     3.5|   stepped-apartment|Route des Plaines...|Magnifique 3,5 pi...|2150.0|Highlight|/en/d/stepped-apa...|7.673223121121708|\n",
      "|5274375|      108.0|     3.5|   duplex-maisonette|Route de Prilly 1...|Magnifique logeme...|3160.0|     null|/en/d/duplex-mais...|8.058327306580958|\n",
      "|5274374|       80.0|     3.5|                flat|Route de Prilly 1...|  Nouveaux logements|2085.0|     null|/en/d/flat-rent-l...|7.642524134232902|\n",
      "|5278160|       55.0|     2.5|                flat|Route Aloys-Fauqu...|Grand 2,5 pièces ...|1450.0|     null|/en/d/flat-rent-l...| 7.27931883541462|\n",
      "|5277696|      128.0|     3.5|   duplex-maisonette|Rue du Bourg 15, ...|Superbe duplex à ...|3000.0|     null|/en/d/duplex-mais...|8.006367567650246|\n",
      "|5256077|      130.0|     4.5|           penthouse|Avenue Druey 13, ...|Belle Attique Rén...|4580.0|     null|/en/d/penthouse-r...|8.429454277108231|\n",
      "|5134449|      123.0|     4.5|                flat|Chemin de Pierref...|Appartement 4.5 p...|2950.0|     null|/en/d/flat-rent-l...|7.989560449333865|\n",
      "|5275170|       13.0|    null|         single-room|Av. de Chailly 52...|Pour petit budget...| 360.0|     null|/en/d/single-room...|5.886104031450156|\n",
      "|5212203|       13.0|    null|         single-room|Av. de Chailly 52...|Proche de toutes ...| 380.0|     null|/en/d/single-room...|5.940171252720432|\n",
      "|5251291|       19.0|    null|                flat|Av. de Riant-Mont...|Appartement, 1.0 ...| 637.0|     null|/en/d/flat-rent-s...|6.456769655572163|\n",
      "|5279333|       20.0|    null|                flat|Av. de Tivoli 19B...|Appartement, 1.0 ...| 725.0|     null|/en/d/flat-rent-s...|6.586171654854675|\n",
      "|5280881|       27.0|    null|                flat|Isabelle de Monto...|Haut de la Ville ...| 780.0|     null|/en/d/flat-rent-l...|6.659293919683638|\n",
      "|5286962|       21.0|    null|                flat|Rue St-Martin 28,...|Votre nouveau che...| 840.0|      New|/en/d/flat-rent-l...|6.733401891837359|\n",
      "|5280449|       24.0|    null|                flat|Avenue Sainte-Luc...|Lausanne, studio ...| 850.0|     null|/en/d/flat-rent-l...|6.745236349484363|\n",
      "|4939726|       18.0|    null|         single-room|Chemin de Montoli...|1006 Lausanne - M...| 860.0|     null|/en/d/single-room...|6.756932389247553|\n",
      "|5085420|       29.0|    null|                flat|Ch. du Devin 64, ...|Grand studio au c...| 940.0|     null|/en/d/flat-rent-l...| 6.84587987526405|\n",
      "|5234626|       33.0|    null|              studio|Chemin de Boston ...|Spacieux et fonct...|1030.0|     null|/en/d/studio-rent...|6.937314081223682|\n",
      "|5189187|       14.0|    null|furnished-residen...|Avenue de Menthon...|Avenue de Menthon...|1050.0|     null|/en/d/furnished-r...|6.956545443151569|\n",
      "+-------+-----------+--------+--------------------+--------------------+--------------------+------+---------+--------------------+-----------------+\n",
      "only showing top 20 rows\n",
      "\n"
     ]
    }
   ],
   "source": [
    "dfs.show()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "**Note**: pyspark.sql.functions perform fast computations as the whole calcluations are doen in JVM! "
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### User-defined functions"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 208,
   "metadata": {},
   "outputs": [],
   "source": [
    "df['NumRoomsOnly'] = df['NumRooms'].apply(lambda x: x - 1)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 209,
   "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>SurfaceArea</th>\n",
       "      <th>NumRooms</th>\n",
       "      <th>Type</th>\n",
       "      <th>Address</th>\n",
       "      <th>Description</th>\n",
       "      <th>Rent</th>\n",
       "      <th>Bookmark</th>\n",
       "      <th>Link</th>\n",
       "      <th>logRent</th>\n",
       "      <th>NumRoomsOnly</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>5286965</td>\n",
       "      <td>52.0</td>\n",
       "      <td>2.5</td>\n",
       "      <td>flat</td>\n",
       "      <td>Route Aloys-Fauquez 124, 1018 Lausanne, VD</td>\n",
       "      <td>Agréable 2 pièces proche de toutes les commodités</td>\n",
       "      <td>1460.0</td>\n",
       "      <td>New</td>\n",
       "      <td>/en/d/flat-rent-lausanne/5286965?s=2&amp;t=1&amp;l=202...</td>\n",
       "      <td>7.286192</td>\n",
       "      <td>1.5</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>5277530</td>\n",
       "      <td>80.0</td>\n",
       "      <td>3.0</td>\n",
       "      <td>flat</td>\n",
       "      <td>Avenue de Béthusy 53, 1012 Lausanne, VD</td>\n",
       "      <td>Proche du centre ville</td>\n",
       "      <td>2100.0</td>\n",
       "      <td>NaN</td>\n",
       "      <td>/en/d/flat-rent-lausanne/5277530?s=2&amp;t=1&amp;l=202...</td>\n",
       "      <td>7.649693</td>\n",
       "      <td>2.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>5249019</td>\n",
       "      <td>74.0</td>\n",
       "      <td>3.5</td>\n",
       "      <td>stepped-apartment</td>\n",
       "      <td>Route des Plaines-du-Loup 42, 1018 Lausanne, VD</td>\n",
       "      <td>Magnifique 3,5 pièces avec jardin privé -Immeu...</td>\n",
       "      <td>2150.0</td>\n",
       "      <td>Highlight</td>\n",
       "      <td>/en/d/stepped-apartment-rent-lausanne/5249019?...</td>\n",
       "      <td>7.673223</td>\n",
       "      <td>2.5</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>5274375</td>\n",
       "      <td>108.0</td>\n",
       "      <td>3.5</td>\n",
       "      <td>duplex-maisonette</td>\n",
       "      <td>Route de Prilly 12, 1004 Lausanne, VD</td>\n",
       "      <td>Magnifique logement en duplex entièrement neuf</td>\n",
       "      <td>3160.0</td>\n",
       "      <td>NaN</td>\n",
       "      <td>/en/d/duplex-maisonette-rent-lausanne/5274375?...</td>\n",
       "      <td>8.058327</td>\n",
       "      <td>2.5</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>5274374</td>\n",
       "      <td>80.0</td>\n",
       "      <td>3.5</td>\n",
       "      <td>flat</td>\n",
       "      <td>Route de Prilly 12, 1004 Lausanne, VD</td>\n",
       "      <td>Nouveaux logements</td>\n",
       "      <td>2085.0</td>\n",
       "      <td>NaN</td>\n",
       "      <td>/en/d/flat-rent-lausanne/5274374?s=2&amp;t=1&amp;l=202...</td>\n",
       "      <td>7.642524</td>\n",
       "      <td>2.5</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "        Id  SurfaceArea  NumRooms               Type  \\\n",
       "0  5286965         52.0       2.5               flat   \n",
       "1  5277530         80.0       3.0               flat   \n",
       "2  5249019         74.0       3.5  stepped-apartment   \n",
       "3  5274375        108.0       3.5  duplex-maisonette   \n",
       "4  5274374         80.0       3.5               flat   \n",
       "\n",
       "                                           Address  \\\n",
       "0       Route Aloys-Fauquez 124, 1018 Lausanne, VD   \n",
       "1          Avenue de Béthusy 53, 1012 Lausanne, VD   \n",
       "2  Route des Plaines-du-Loup 42, 1018 Lausanne, VD   \n",
       "3            Route de Prilly 12, 1004 Lausanne, VD   \n",
       "4            Route de Prilly 12, 1004 Lausanne, VD   \n",
       "\n",
       "                                         Description    Rent   Bookmark  \\\n",
       "0  Agréable 2 pièces proche de toutes les commodités  1460.0        New   \n",
       "1                             Proche du centre ville  2100.0        NaN   \n",
       "2  Magnifique 3,5 pièces avec jardin privé -Immeu...  2150.0  Highlight   \n",
       "3     Magnifique logement en duplex entièrement neuf  3160.0        NaN   \n",
       "4                                 Nouveaux logements  2085.0        NaN   \n",
       "\n",
       "                                                Link   logRent  NumRoomsOnly  \n",
       "0  /en/d/flat-rent-lausanne/5286965?s=2&t=1&l=202...  7.286192           1.5  \n",
       "1  /en/d/flat-rent-lausanne/5277530?s=2&t=1&l=202...  7.649693           2.0  \n",
       "2  /en/d/stepped-apartment-rent-lausanne/5249019?...  7.673223           2.5  \n",
       "3  /en/d/duplex-maisonette-rent-lausanne/5274375?...  8.058327           2.5  \n",
       "4  /en/d/flat-rent-lausanne/5274374?s=2&t=1&l=202...  7.642524           2.5  "
      ]
     },
     "execution_count": 209,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df.head()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Pyspark user-defined function, note that the udf has to be deterministic. "
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 220,
   "metadata": {},
   "outputs": [],
   "source": [
    "from pyspark.sql.types import FloatType"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 221,
   "metadata": {},
   "outputs": [],
   "source": [
    "myFun = F.udf(lambda x: x - 1, returnType=FloatType()) # need to specify the return type otherwise you may get NaN"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 254,
   "metadata": {},
   "outputs": [],
   "source": [
    "dfs2 = dfs.withColumn('NumRoomsOnly', myFun(dfs['NumRooms'])) # Note that if you don't fillna you get an error when you collect the data i.e. in the next line! "
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 255,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "+-------+-----------+---------+--------------------+--------------------+--------------------+------+---------+--------------------+------------+\n",
      "|     Id|SurfaceArea| NumRooms|                Type|             Address|         Description|  Rent| Bookmark|                Link|NumRoomsOnly|\n",
      "+-------+-----------+---------+--------------------+--------------------+--------------------+------+---------+--------------------+------------+\n",
      "|5286965|       52.0|      2.5|                flat|Route Aloys-Fauqu...|Agréable 2 pièces...|1460.0|      New|/en/d/flat-rent-l...|         1.5|\n",
      "|5277530|       80.0|      3.0|                flat|Avenue de Béthusy...|Proche du centre ...|2100.0|     null|/en/d/flat-rent-l...|         2.0|\n",
      "|5249019|       74.0|      3.5|   stepped-apartment|Route des Plaines...|Magnifique 3,5 pi...|2150.0|Highlight|/en/d/stepped-apa...|         2.5|\n",
      "|5274375|      108.0|      3.5|   duplex-maisonette|Route de Prilly 1...|Magnifique logeme...|3160.0|     null|/en/d/duplex-mais...|         2.5|\n",
      "|5274374|       80.0|      3.5|                flat|Route de Prilly 1...|  Nouveaux logements|2085.0|     null|/en/d/flat-rent-l...|         2.5|\n",
      "|5278160|       55.0|      2.5|                flat|Route Aloys-Fauqu...|Grand 2,5 pièces ...|1450.0|     null|/en/d/flat-rent-l...|         1.5|\n",
      "|5277696|      128.0|      3.5|   duplex-maisonette|Rue du Bourg 15, ...|Superbe duplex à ...|3000.0|     null|/en/d/duplex-mais...|         2.5|\n",
      "|5256077|      130.0|      4.5|           penthouse|Avenue Druey 13, ...|Belle Attique Rén...|4580.0|     null|/en/d/penthouse-r...|         3.5|\n",
      "|5134449|      123.0|      4.5|                flat|Chemin de Pierref...|Appartement 4.5 p...|2950.0|     null|/en/d/flat-rent-l...|         3.5|\n",
      "|5275170|       13.0|3.2301426|         single-room|Av. de Chailly 52...|Pour petit budget...| 360.0|     null|/en/d/single-room...|   2.2301426|\n",
      "|5212203|       13.0|3.2301426|         single-room|Av. de Chailly 52...|Proche de toutes ...| 380.0|     null|/en/d/single-room...|   2.2301426|\n",
      "|5251291|       19.0|3.2301426|                flat|Av. de Riant-Mont...|Appartement, 1.0 ...| 637.0|     null|/en/d/flat-rent-s...|   2.2301426|\n",
      "|5279333|       20.0|3.2301426|                flat|Av. de Tivoli 19B...|Appartement, 1.0 ...| 725.0|     null|/en/d/flat-rent-s...|   2.2301426|\n",
      "|5280881|       27.0|3.2301426|                flat|Isabelle de Monto...|Haut de la Ville ...| 780.0|     null|/en/d/flat-rent-l...|   2.2301426|\n",
      "|5286962|       21.0|3.2301426|                flat|Rue St-Martin 28,...|Votre nouveau che...| 840.0|      New|/en/d/flat-rent-l...|   2.2301426|\n",
      "|5280449|       24.0|3.2301426|                flat|Avenue Sainte-Luc...|Lausanne, studio ...| 850.0|     null|/en/d/flat-rent-l...|   2.2301426|\n",
      "|4939726|       18.0|3.2301426|         single-room|Chemin de Montoli...|1006 Lausanne - M...| 860.0|     null|/en/d/single-room...|   2.2301426|\n",
      "|5085420|       29.0|3.2301426|                flat|Ch. du Devin 64, ...|Grand studio au c...| 940.0|     null|/en/d/flat-rent-l...|   2.2301426|\n",
      "|5234626|       33.0|3.2301426|              studio|Chemin de Boston ...|Spacieux et fonct...|1030.0|     null|/en/d/studio-rent...|   2.2301426|\n",
      "|5189187|       14.0|3.2301426|furnished-residen...|Avenue de Menthon...|Avenue de Menthon...|1050.0|     null|/en/d/furnished-r...|   2.2301426|\n",
      "+-------+-----------+---------+--------------------+--------------------+--------------------+------+---------+--------------------+------------+\n",
      "only showing top 20 rows\n",
      "\n"
     ]
    }
   ],
   "source": [
    "dfs2.show()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### Create dataframe"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "**Pandas**"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "myMap = {'id':[121, 122, 123], 'name':['John', 'Mary', 'Alice']}"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "df0 = # Create a dataframe from myMap "
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "**PySpark**"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "df0s = spark.createDataFrame(myMap) # does this work? "
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "from pyspark.sql.types import *"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Similar to SQL to make a dataframe (\"table\" in SQL) you need to specify the types for each column."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "fields = [StructField('id', IntegerType(), nullable=False), StructField('name', StringType(), nullable=True)]\n",
    "schema = StructType(fields)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "df0s = spark.createDataFrame(myMap, schema=schema) # does this one work now? "
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "The entries need to be inserted \"row by row\":"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "myMapS = [[121, 'John'], [122, 'Mary'], [123, 'Alice']]"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "df0s = spark.createDataFrame(myMapS, schema=schema)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "df0s.show()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Or:"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "df0s = spark.createDataFrame(df0) # from pandas dataframe! "
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "df0s.show()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### Join/Merge "
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "**Pandas**"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 23,
   "metadata": {},
   "outputs": [],
   "source": [
    "map1 = {'name':['Switzerland', 'France', 'Germany', 'Italy'], 'AlphaCode':['CH', 'FR', 'DE', 'IT']}\n",
    "df1 = pd.DataFrame(map1)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 25,
   "metadata": {},
   "outputs": [],
   "source": [
    "map2 = {'AlphaCode':['FR', 'CH', 'IT', 'DE'], 'Population':[67, 8.5, 60, 82]}\n",
    "df2 = pd.DataFrame(map2)"
   ]
  },
  {
   "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>name</th>\n",
       "      <th>AlphaCode</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>Switzerland</td>\n",
       "      <td>CH</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>France</td>\n",
       "      <td>FR</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>Germany</td>\n",
       "      <td>DE</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>Italy</td>\n",
       "      <td>IT</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "          name AlphaCode\n",
       "0  Switzerland        CH\n",
       "1       France        FR\n",
       "2      Germany        DE\n",
       "3        Italy        IT"
      ]
     },
     "execution_count": 20,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df1"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 21,
   "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>AlphaCode</th>\n",
       "      <th>Population</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>FR</td>\n",
       "      <td>67.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>CH</td>\n",
       "      <td>8.5</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>IT</td>\n",
       "      <td>60.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>DE</td>\n",
       "      <td>82.0</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "  AlphaCode  Population\n",
       "0        FR        67.0\n",
       "1        CH         8.5\n",
       "2        IT        60.0\n",
       "3        DE        82.0"
      ]
     },
     "execution_count": 21,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df2"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Get a table with name of the countries, their Alpha code and their population."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": []
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "**PySpark**"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 29,
   "metadata": {},
   "outputs": [],
   "source": [
    "df1s = spark.createDataFrame(df1) # you can create a Spark dataframe from a Pandas dataframe\n",
    "df2s = spark.createDataFrame(df2) "
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 31,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "+---------+-----------+----------+\n",
      "|AlphaCode|       name|Population|\n",
      "+---------+-----------+----------+\n",
      "|       DE|    Germany|      82.0|\n",
      "|       FR|     France|      67.0|\n",
      "|       CH|Switzerland|       8.5|\n",
      "|       IT|      Italy|      60.0|\n",
      "+---------+-----------+----------+\n",
      "\n"
     ]
    }
   ],
   "source": [
    "df1s.join(df2s, on='AlphaCode').show()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### Plotting"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 34,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "<matplotlib.axes._subplots.AxesSubplot at 0x7f624244ad30>"
      ]
     },
     "execution_count": 34,
     "metadata": {},
     "output_type": "execute_result"
    },
    {
     "data": {
      "image/png": "iVBORw0KGgoAAAANSUhEUgAAAXoAAAD8CAYAAAB5Pm/hAAAABHNCSVQICAgIfAhkiAAAAAlwSFlzAAALEgAACxIB0t1+/AAAADl0RVh0U29mdHdhcmUAbWF0cGxvdGxpYiB2ZXJzaW9uIDMuMC4yLCBodHRwOi8vbWF0cGxvdGxpYi5vcmcvOIA7rQAAEUxJREFUeJzt3X9sXWd9x/H3dwmUErMmXVcrJNGcShlaIFp/WNCNabLpRH9NS5FgSoUgBbagrWywRRou/AETqhQmfkxoDBRIaTp+mK7t1ijNxrKsHkKCQtJ1TUsoNTRrk2YpXUuoC4KFfffHfcKuPd/cG9s31378fknWPec5zz3nOd8ef3J87rmnkZlIkur1c70egCSpuwx6SaqcQS9JlTPoJalyBr0kVc6gl6TKGfSSVDmDXpIqZ9BLUuWW9noAABdccEEODAxManv++edZtmxZbwY0z1mb6VmX1qxNawu5NgcOHHg6M3+xXb95EfQDAwPs379/UtvY2BhDQ0O9GdA8Z22mZ11aszatLeTaRMR/dNLPSzeSVDmDXpIqZ9BLUuUMekmqnEEvSZUz6CWpcga9JFXOoJekyrUN+ohYExH3RsShiHg4It5Z2t8fEUcj4oHyc03Te26KiPGIeCQiruzmDkiSTq+Tb8aeBLZm5v0R8RLgQETsLcs+mpkfau4cEeuBTcDLgZcC/xwRv5yZP53Lgc8HAyP39GS7t161ML+uLak32p7RZ+axzLy/TD8HHAJWneYtG4HRzPxxZj4GjAOvnIvBSpLO3Bldo4+IAeAS4L7S9I6IeDAibomIFaVtFfBE09uOcPp/GCRJXRSZ2VnHiD7gX4GbM/OuiOgHngYS+ACwMjPfGhEfB76amZ8t79sB7MnMO6esbwuwBaC/v/+y0dHRSdubmJigr69vVjvXbQePnujJdteet2Te16YXFsIx0yvWprWFXJvh4eEDmTnYrl9HT6+MiBcAdwKfy8y7ADLzeNPyTwG7y+wRYE3T21cDT05dZ2ZuB7YDDA4O5tSnxy2EJ8rd0MNr9PO9Nr2wEI6ZXrE2rS2G2nRy100AO4BDmfmRpvaVTd1eBzxUpncBmyLinIhYC6wDvj53Q5YknYlOzuhfDbwJOBgRD5S29wDXR8TFNC7dHAbeDpCZD0fE7cA3adyxc2ONd9xI0kLRNugz8ytATLNoz2neczNw8yzGJUmaI34zVpIqZ9BLUuUMekmqnEEvSZUz6CWpcga9JFXOoJekyhn0klQ5g16SKmfQS1LlDHpJqpxBL0mVM+glqXIGvSRVzqCXpMoZ9JJUOYNekipn0EtS5Qx6SaqcQS9JlTPoJalyBr0kVc6gl6TKGfSSVDmDXpIqZ9BLUuUMekmqnEEvSZUz6CWpcga9JFXOoJekyhn0klS5tkEfEWsi4t6IOBQRD0fEO0v7+RGxNyIeLa8rSntExMciYjwiHoyIS7u9E5Kk1jo5oz8JbM3MXwEuB26MiPXACLAvM9cB+8o8wNXAuvKzBfjEnI9aktSxtkGfmccy8/4y/RxwCFgFbAR2lm47gevK9Ebgtmz4GrA8IlbO+cglSR05o2v0ETEAXALcB/Rn5jFo/GMAXFi6rQKeaHrbkdImSeqBpZ12jIg+4E7gXZn5g4ho2XWatpxmfVtoXNqhv7+fsbGxScsnJib+X9t8s3XDyZ5sdyHUphesS2vWprXFUJuOgj4iXkAj5D+XmXeV5uMRsTIzj5VLM0+V9iPAmqa3rwaenLrOzNwObAcYHBzMoaGhScvHxsaY2jbf3DByT0+2e+tVy+Z9bXphIRwzvWJtWlsMtenkrpsAdgCHMvMjTYt2AZvL9Gbg7qb2N5e7by4HTpy6xCNJOvs6OaN/NfAm4GBEPFDa3gNsA26PiLcBjwNvKMv2ANcA48APgbfM6YglSWekbdBn5leY/ro7wBXT9E/gxlmOS5I0R/xmrCRVzqCXpMoZ9JJUOYNekipn0EtS5Qx6SaqcQS9JlTPoJalyBr0kVc6gl6TKGfSSVDmDXpIqZ9BLUuUMekmqnEEvSZUz6CWpcga9JFXOoJekyhn0klQ5g16SKmfQS1LlDHpJqpxBL0mVM+glqXIGvSRVzqCXpMoZ9JJUOYNekipn0EtS5Qx6SaqcQS9JlTPoJalybYM+Im6JiKci4qGmtvdHxNGIeKD8XNO07KaIGI+IRyLiym4NXJLUmU7O6G8Frpqm/aOZeXH52QMQEeuBTcDLy3v+OiKWzNVgJUlnrm3QZ+aXgWc6XN9GYDQzf5yZjwHjwCtnMT5J0izN5hr9OyLiwXJpZ0VpWwU80dTnSGmTJPVIZGb7ThEDwO7MfEWZ7weeBhL4ALAyM98aER8HvpqZny39dgB7MvPOada5BdgC0N/ff9no6Oik5RMTE/T19c18z86Cg0dP9GS7a89bMu9r0wsL4ZjpFWvT2kKuzfDw8IHMHGzXb+lMVp6Zx09NR8SngN1l9giwpqnrauDJFuvYDmwHGBwczKGhoUnLx8bGmNo239wwck9PtnvrVcvmfW16YSEcM71ibVpbDLWZ0aWbiFjZNPs64NQdObuATRFxTkSsBdYBX5/dECVJs9H2jD4ivgAMARdExBHgfcBQRFxM49LNYeDtAJn5cETcDnwTOAncmJk/7c7QJUmdaBv0mXn9NM07TtP/ZuDm2QxKkjR3/GasJFVuRh/GqrcOHj3Rsw+CD2+7tifblTRzntFLUuUMekmqnEEvSZUz6CWpcga9JFXOoJekyhn0klQ5g16SKmfQS1LlDHpJqpxBL0mVM+glqXIGvSRVzqCXpMoZ9JJUOYNekipn0EtS5Qx6SaqcQS9JlTPoJalyBr0kVc6gl6TKGfSSVDmDXpIqZ9BLUuUMekmqnEEvSZUz6CWpcga9JFXOoJekyrUN+oi4JSKeioiHmtrOj4i9EfFoeV1R2iMiPhYR4xHxYERc2s3BS5LaW9pBn1uBvwJua2obAfZl5raIGCnz7wauBtaVn1cBnyivXTMwck83Vy9JC17bM/rM/DLwzJTmjcDOMr0TuK6p/bZs+BqwPCJWztVgJUlnLjKzfaeIAWB3Zr6izH8/M5c3LX82M1dExG5gW2Z+pbTvA96dmfunWecWYAtAf3//ZaOjo5OWT0xM0NfX13ZsB4+eaNunNv3nwvEf9WbbG1ad15sNd6DTY2YxsjatLeTaDA8PH8jMwXb9Orl0cyZimrZp/yXJzO3AdoDBwcEcGhqatHxsbIypbdO5YRFeutm64SQfPjjX/+k6c/iNQz3Zbic6PWYWI2vT2mKozUzvujl+6pJMeX2qtB8B1jT1Ww08OfPhSZJma6ZBvwvYXKY3A3c3tb+53H1zOXAiM4/NcoySpFlo+/d/RHwBGAIuiIgjwPuAbcDtEfE24HHgDaX7HuAaYBz4IfCWLoxZknQG2gZ9Zl7fYtEV0/RN4MbZDkqSNHf8ZqwkVc6gl6TKGfSSVDmDXpIqZ9BLUuUMekmqnEEvSZUz6CWpcga9JFXOoJekyhn0klQ5g16SKmfQS1LlDHpJqpxBL0mVM+glqXIGvSRVzqCXpMoZ9JJUOYNekipn0EtS5Qx6SaqcQS9JlTPoJalyBr0kVW5prweghWVg5J6ebPfwtmt7sl2pBp7RS1LlDHpJqpxBL0mVM+glqXIGvSRVblZ33UTEYeA54KfAycwcjIjzgS8CA8Bh4Hcz89nZDVOSNFNzcUY/nJkXZ+ZgmR8B9mXmOmBfmZck9Ug3Lt1sBHaW6Z3AdV3YhiSpQ7MN+gT+KSIORMSW0tafmccAyuuFs9yGJGkWIjNn/uaIl2bmkxFxIbAX+CNgV2Yub+rzbGaumOa9W4AtAP39/ZeNjo5OWj4xMUFfX1/bMRw8emLG41+o+s+F4z/q9SjOrg2rzmvbp9NjZjGyNq0t5NoMDw8faLps3tKsgn7SiiLeD0wAvw8MZeaxiFgJjGXmy0733sHBwdy/f/+ktrGxMYaGhtput1dfye+lrRtO8uGDi+vpFZ08AqHTY2YxsjatLeTaRERHQT/jSzcRsSwiXnJqGngt8BCwC9hcum0G7p7pNiRJszeb08J+4O8i4tR6Pp+Z/xgR3wBuj4i3AY8Db5j9MCVJMzXjoM/M7wK/Ok37fwFXzGZQkqS54zdjJalyBr0kVc6gl6TKGfSSVDmDXpIqZ9BLUuUMekmqnEEvSZUz6CWpcga9JFXOoJekyhn0klQ5g16SKmfQS1LlDHpJqpxBL0mVM+glqXIGvSRVzqCXpMoZ9JJUOYNekipn0EtS5Qx6SaqcQS9JlVva6wFInRgYuadtn60bTnJDB/3O1OFt1875OqWzyTN6SaqcQS9JlTPoJalyBr0kVc6gl6TKedeN1EYnd/x0g3f7aK54Ri9Jleta0EfEVRHxSESMR8RIt7YjSTq9rgR9RCwBPg5cDawHro+I9d3YliTp9Lp1jf6VwHhmfhcgIkaBjcA3u7Q9SZqxXn0OA2fns5huBf0q4Imm+SPAq7q0LalKcxk+3Xo8RA22bjhJ7feldGvvYpq2nNQhYguwpcxORMQjU/pfADzdhbEteH9sbaZlXVqzNq31ujbxwVm9/Zc66dStoD8CrGmaXw082dwhM7cD21utICL2Z+Zgd4a3sFmb6VmX1qxNa4uhNt266+YbwLqIWBsRLwQ2Abu6tC1J0ml05Yw+M09GxDuALwFLgFsy8+FubEuSdHpd+wQiM/cAe2axipaXdWRtWrAurVmb1qqvTWRm+16SpAXLRyBIUuXmXdAvxkcnRMSaiLg3Ig5FxMMR8c7Sfn5E7I2IR8vritIeEfGxUqMHI+LSpnVtLv0fjYjNvdqnuRQRSyLi3yJid5lfGxH3lX38YvnAn4g4p8yPl+UDTeu4qbQ/EhFX9mZP5lZELI+IOyLiW+XY+TWPmYaI+JPyu/RQRHwhIl60qI+bzJw3PzQ+uP0OcBHwQuDfgfW9HtdZ2O+VwKVl+iXAt2k8OuIvgJHSPgJ8sExfA/wDje8rXA7cV9rPB75bXleU6RW93r85qM+fAp8Hdpf524FNZfqTwB+U6T8EPlmmNwFfLNPry7F0DrC2HGNLer1fc1CXncDvlekXAss9ZhIaX9h8DDi36Xi5YTEfN/PtjP5nj07IzJ8Apx6dULXMPJaZ95fp54BDNA7WjTR+mSmv15XpjcBt2fA1YHlErASuBPZm5jOZ+SywF7jqLO7KnIuI1cC1wKfLfACvAe4oXabW5VS97gCuKP03AqOZ+ePMfAwYp3GsLVgR8fPAbwI7ADLzJ5n5fTxmTlkKnBsRS4EXA8dYxMfNfAv66R6dsKpHY+mJ8mfjJcB9QH9mHoPGPwbAhaVbqzrVWL+/BP4M+J8y/wvA9zPzZJlv3sef7X9ZfqL0r7EuFwHfAz5TLmt9OiKW4TFDZh4FPgQ8TiPgTwAHWMTHzXwL+raPTqhZRPQBdwLvyswfnK7rNG15mvYFKSJ+G3gqMw80N0/TNdssq6ouxVLgUuATmXkJ8DyNSzWtLJralM8lNtK43PJSYBmNJ+lOtWiOm/kW9G0fnVCriHgBjZD/XGbeVZqPlz+vKa9PlfZWdaqtfq8GficiDtO4jPcaGmf4y8uf5DB5H3+2/2X5ecAz1FcXaOzTkcy8r8zfQSP4F/sxA/BbwGOZ+b3M/G/gLuDXWcTHzXwL+kX56IRyPXAHcCgzP9K0aBdw6i6IzcDdTe1vLndSXA6cKH+mfwl4bUSsKGc1ry1tC1Jm3pSZqzNzgMax8C+Z+UbgXuD1pdvUupyq1+tL/yztm8rdFWuBdcDXz9JudEVm/ifwRES8rDRdQeMx4Iv6mCkeBy6PiBeX361TtVm8x02vPw2e+kPj7oBv0/iE+729Hs9Z2uffoPEn4YPAA+XnGhrXCfcBj5bX80v/oPE/dvkOcBAYbFrXW2l8aDQOvKXX+zaHNRri/+66uYjGL9w48LfAOaX9RWV+vCy/qOn97y31egS4utf7M0c1uRjYX46bv6dx14zHTGOf/hz4FvAQ8Dc07pxZtMeN34yVpMrNt0s3kqQ5ZtBLUuUMekmqnEEvSZUz6CWpcga9JFXOoJekyhn0klS5/wUIcSifbis2RwAAAABJRU5ErkJggg==\n",
      "text/plain": [
       "<Figure size 432x288 with 1 Axes>"
      ]
     },
     "metadata": {},
     "output_type": "display_data"
    }
   ],
   "source": [
    "df['Rent'].hist()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 58,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "<matplotlib.axes._subplots.AxesSubplot at 0x7f62372f00b8>"
      ]
     },
     "execution_count": 58,
     "metadata": {},
     "output_type": "execute_result"
    },
    {
     "data": {
      "image/png": "iVBORw0KGgoAAAANSUhEUgAAAXoAAAD8CAYAAAB5Pm/hAAAABHNCSVQICAgIfAhkiAAAAAlwSFlzAAALEgAACxIB0t1+/AAAADl0RVh0U29mdHdhcmUAbWF0cGxvdGxpYiB2ZXJzaW9uIDMuMC4yLCBodHRwOi8vbWF0cGxvdGxpYi5vcmcvOIA7rQAAEUxJREFUeJzt3X+MHGd9x/H3tzaE4KOx0zQnY1s9R3JRDVHz4wRpqao7UpFfVR0kqBwhcIDWqA0ttJGKA39AhSKZih8VKgUZHOKUH0eapI3luKWumytCgoCdpnGCCTmIm9hxHdIEkwsIavrtH/uY7l1vveu7Xe/dc++XdNqZZ56deeabuY/nZmcnkZlIkur1c/0egCSptwx6SaqcQS9JlTPoJalyBr0kVc6gl6TKGfSSVDmDXpIqZ9BLUuWW9nsAAOedd14ODQ1NaXv++edZtmxZfwY0z1mbmVmX1qxNawu5Nvv37386M3+xXb95EfRDQ0Ps27dvStv4+DgjIyP9GdA8Z21mZl1aszatLeTaRMR/dNLPSzeSVDmDXpIqZ9BLUuUMekmqnEEvSZUz6CWpcga9JFXOoJekyrUN+ohYExH3RsTBiHg4It5Z2t8fEUci4oHyc3XTe26KiImIeCQirujlDkiSTq2Tb8aeAG7MzPsj4iXA/ojYU5Z9NDM/1Nw5ItYDG4GXAy8F/jkifjkzf9rNgc8HQ1vu6ct2b71yYX5dW1J/tD2jz8yjmXl/mX4OOAisOsVbNgBjmfnjzHwMmABe2Y3BSpJO32ldo4+IIeBi4L7S9I6IeDAibomIFaVtFfBE09sOc+p/GCRJPRSZ2VnHiAHgX4GbM/OuiBgEngYS+ACwMjPfGhEfB76amZ8t79sO7M7MO6etbzOwGWBwcPDSsbGxKdubnJxkYGBgTjvXaweOHO/Ldtees2Te16YfFsIx0y/WprWFXJvR0dH9mTncrl9HT6+MiBcAdwKfy8y7ADLzWNPyTwG7yuxhYE3T21cDT05fZ2ZuA7YBDA8P5/Snxy2EJ8pd38dr9PO9Nv2wEI6ZfrE2rS2G2nRy100A24GDmfmRpvaVTd1eBzxUpncCGyPirIhYC6wDvt69IUuSTkcnZ/SvBt4EHIiIB0rbe4DrIuIiGpduDgFvB8jMhyPiduCbNO7YuaHGO24kaaFoG/SZ+RUgZli0+xTvuRm4eQ7jkiR1id+MlaTKGfSSVDmDXpIqZ9BLUuUMekmqnEEvSZUz6CWpcga9JFXOoJekyhn0klQ5g16SKmfQS1LlDHpJqpxBL0mVM+glqXIGvSRVzqCXpMoZ9JJUOYNekipn0EtS5Qx6SaqcQS9JlTPoJalyBr0kVc6gl6TKGfSSVDmDXpIqZ9BLUuUMekmqnEEvSZUz6CWpcga9JFWubdBHxJqIuDciDkbEwxHxztJ+bkTsiYhHy+uK0h4R8bGImIiIByPikl7vhCSptU7O6E8AN2bmrwCXATdExHpgC7A3M9cBe8s8wFXAuvKzGfhE10ctSepY26DPzKOZeX+Zfg44CKwCNgA7SrcdwLVlegNwWzZ8DVgeESu7PnJJUkdO6xp9RAwBFwP3AYOZeRQa/xgA55duq4Anmt52uLRJkvpgaacdI2IAuBN4V2b+ICJadp2hLWdY32Yal3YYHBxkfHx8yvLJycn/1zbf3Hjhib5sdyHUph+sS2vWprXFUJuOgj4iXkAj5D+XmXeV5mMRsTIzj5ZLM0+V9sPAmqa3rwaenL7OzNwGbAMYHh7OkZGRKcvHx8eZ3jbfXL/lnr5s99Yrl8372vTDQjhm+sXatLYYatPJXTcBbAcOZuZHmhbtBDaV6U3A3U3tby5331wGHD95iUeSdOZ1ckb/auBNwIGIeKC0vQfYCtweEW8DHgfeUJbtBq4GJoAfAm/p6oglSaelbdBn5leY+bo7wOUz9E/ghjmOS5LUJX4zVpIqZ9BLUuUMekmqnEEvSZUz6CWpcga9JFXOoJekyhn0klQ5g16SKmfQS1LlDHpJqpxBL0mVM+glqXIGvSRVzqCXpMoZ9JJUOYNekipn0EtS5Qx6SaqcQS9JlTPoJalyBr0kVc6gl6TKGfSSVDmDXpIqZ9BLUuUMekmqnEEvSZUz6CWpcga9JFXOoJekyhn0klS5tkEfEbdExFMR8VBT2/sj4khEPFB+rm5adlNETETEIxFxRa8GLknqTCdn9LcCV87Q/tHMvKj87AaIiPXARuDl5T1/HRFLujVYSdLpaxv0mfll4JkO17cBGMvMH2fmY8AE8Mo5jE+SNEdzuUb/joh4sFzaWVHaVgFPNPU5XNokSX0Smdm+U8QQsCszX1HmB4GngQQ+AKzMzLdGxMeBr2bmZ0u/7cDuzLxzhnVuBjYDDA4OXjo2NjZl+eTkJAMDA7PfszPgwJHjfdnu2nOWzPva9MNCOGb6xdq0tpBrMzo6uj8zh9v1WzqblWfmsZPTEfEpYFeZPQysaeq6GniyxTq2AdsAhoeHc2RkZMry8fFxprfNN9dvuacv2731ymXzvjb9sBCOmX6xNq0thtrM6tJNRKxsmn0dcPKOnJ3Axog4KyLWAuuAr89tiJKkuWh7Rh8RXwBGgPMi4jDwPmAkIi6icenmEPB2gMx8OCJuB74JnABuyMyf9mbokqROtA36zLxuhubtp+h/M3DzXAYlSeoevxkrSZWb1Yex6q8DR4737YPgQ1uv6ct2Jc2eZ/SSVDmDXpIqZ9BLUuUMekmqnEEvSZUz6CWpcga9JFXOoJekyhn0klQ5g16SKmfQS1LlDHpJqpxBL0mVM+glqXIGvSRVzqCXpMoZ9JJUOYNekipn0EtS5Qx6SaqcQS9JlTPoJalyBr0kVc6gl6TKGfSSVDmDXpIqZ9BLUuUMekmqnEEvSZUz6CWpcm2DPiJuiYinIuKhprZzI2JPRDxaXleU9oiIj0XEREQ8GBGX9HLwkqT2lnbQ51bgr4Dbmtq2AHszc2tEbCnz7wauAtaVn1cBnyivPTO05Z5erl6SFry2Z/SZ+WXgmWnNG4AdZXoHcG1T+23Z8DVgeUSs7NZgJUmnLzKzfaeIIWBXZr6izH8/M5c3LX82M1dExC5ga2Z+pbTvBd6dmftmWOdmYDPA4ODgpWNjY1OWT05OMjAw0HZsB44cb9unNoNnw7Ef9WfbF646pz8b7kCnx8xiZG1aW8i1GR0d3Z+Zw+36dXLp5nTEDG0z/kuSmduAbQDDw8M5MjIyZfn4+DjT22Zy/SK8dHPjhSf48IFu/6frzKE3jvRlu53o9JhZjKxNa4uhNrO96+bYyUsy5fWp0n4YWNPUbzXw5OyHJ0maq9kG/U5gU5neBNzd1P7mcvfNZcDxzDw6xzFKkuag7d//EfEFYAQ4LyIOA+8DtgK3R8TbgMeBN5Tuu4GrgQngh8BbejBmSdJpaBv0mXldi0WXz9A3gRvmOihJUvf4zVhJqpxBL0mVM+glqXIGvSRVzqCXpMoZ9JJUOYNekipn0EtS5Qx6SaqcQS9JlTPoJalyBr0kVc6gl6TKGfSSVDmDXpIqZ9BLUuUMekmqnEEvSZUz6CWpcga9JFXOoJekyhn0klQ5g16SKmfQS1LlDHpJqtzSfg9AC8vQlnv6st1DW6/py3alGnhGL0mVM+glqXIGvSRVzqCXpMoZ9JJUuTnddRMRh4DngJ8CJzJzOCLOBb4IDAGHgN/NzGfnNkxJ0mx144x+NDMvyszhMr8F2JuZ64C9ZV6S1Ce9uHSzAdhRpncA1/ZgG5KkDs016BP4p4jYHxGbS9tgZh4FKK/nz3EbkqQ5iMyc/ZsjXpqZT0bE+cAe4I+AnZm5vKnPs5m5Yob3bgY2AwwODl46NjY2Zfnk5CQDAwNtx3DgyPFZj3+hGjwbjv2o36M4sy5cdU7bPp0eM4uRtWltIddmdHR0f9Nl85bmFPRTVhTxfmAS+H1gJDOPRsRKYDwzX3aq9w4PD+e+ffumtI2PjzMyMtJ2u/36Sn4/3XjhCT58YHE9vaKTRyB0eswsRtamtYVcm4joKOhnfekmIpZFxEtOTgOvBR4CdgKbSrdNwN2z3YYkae7mclo4CPxdRJxcz+cz8x8j4hvA7RHxNuBx4A1zH6YkabZmHfSZ+V3gV2do/y/g8rkMSpLUPX4zVpIqZ9BLUuUMekmqnEEvSZUz6CWpcga9JFXOoJekyhn0klQ5g16SKmfQS1LlDHpJqpxBL0mVM+glqXIGvSRVzqCXpMoZ9JJUOYNekipn0EtS5Qx6SaqcQS9JlTPoJalyBr0kVc6gl6TKGfSSVLml/R6A1ImhLfe07XPjhSe4voN+p+vQ1mu6vk7pTPKMXpIqZ9BLUuUMekmqnEEvSZUz6CWpct51I7XRyR0/veDdPuoWz+glqXI9C/qIuDIiHomIiYjY0qvtSJJOrSdBHxFLgI8DVwHrgesiYn0vtiVJOrVeXaN/JTCRmd8FiIgxYAPwzR5tT5JmrV+fw8CZ+SymV0G/Cniiaf4w8KoebUuqUjfDp1ePh+g2P4DujV4FfczQllM6RGwGNpfZyYh4ZFr/84CnezC2Be+Prc2MrEtrC6U28cG+bLavtZnjPv9SJ516FfSHgTVN86uBJ5s7ZOY2YFurFUTEvswc7s3wFjZrMzPr0pq1aW0x1KZXd918A1gXEWsj4oXARmBnj7YlSTqFnpzRZ+aJiHgH8CVgCXBLZj7ci21Jkk6tZ9+MzczdwO45rKLlZR1ZmxasS2vWprXqaxOZ2b6XJGnB8hEIklS5eRf0i/HRCRGxJiLujYiDEfFwRLyztJ8bEXsi4tHyuqK0R0R8rNTowYi4pGldm0r/RyNiU7/2qZsiYklE/FtE7CrzayPivrKPXywf+BMRZ5X5ibJ8qGkdN5X2RyLiiv7sSXdFxPKIuCMivlWOnV/zmGmIiD8pv0sPRcQXIuJFi/q4ycx580Pjg9vvABcALwT+HVjf73Gdgf1eCVxSpl8CfJvGoyP+AthS2rcAHyzTVwP/QOP7CpcB95X2c4HvltcVZXpFv/evC/X5U+DzwK4yfzuwsUx/EviDMv2HwCfL9Ebgi2V6fTmWzgLWlmNsSb/3qwt12QH8Xpl+IbDcYyah8YXNx4Czm46X6xfzcTPfzuh/9uiEzPwJcPLRCVXLzKOZeX+Zfg44SONg3UDjl5nyem2Z3gDclg1fA5ZHxErgCmBPZj6Tmc8Ce4Arz+CudF1ErAauAT5d5gN4DXBH6TK9LifrdQdweem/ARjLzB9n5mPABI1jbcGKiJ8HfhPYDpCZP8nM7+Mxc9JS4OyIWAq8GDjKIj5u5lvQz/TohFV9GktflD8bLwbuAwYz8yg0/jEAzi/dWtWpxvr9JfBnwP+U+V8Avp+ZJ8p88z7+bP/L8uOlf411uQD4HvCZclnr0xGxDI8ZMvMI8CHgcRoBfxzYzyI+buZb0Ld9dELNImIAuBN4V2b+4FRdZ2jLU7QvSBHx28BTmbm/uXmGrtlmWVV1KZYClwCfyMyLgedpXKppZdHUpnwusYHG5ZaXAstoPEl3ukVz3My3oG/76IRaRcQLaIT85zLzrtJ8rPx5TXl9qrS3qlNt9Xs18DsRcYjGZbzX0DjDX17+JIep+/iz/S/LzwGeob66QGOfDmfmfWX+DhrBv9iPGYDfAh7LzO9l5n8DdwG/ziI+buZb0C/KRyeU64HbgYOZ+ZGmRTuBk3dBbALubmp/c7mT4jLgePkz/UvAayNiRTmreW1pW5Ay86bMXJ2ZQzSOhX/JzDcC9wKvL92m1+VkvV5f+mdp31jurlgLrAO+foZ2oycy8z+BJyLiZaXpchqPAV/Ux0zxOHBZRLy4/G6drM3iPW76/Wnw9B8adwd8m8Yn3O/t93jO0D7/Bo0/CR8EHig/V9O4TrgXeLS8nlv6B43/sct3gAPAcNO63krjQ6MJ4C393rcu1miE/7vr5gIav3ATwN8CZ5X2F5X5ibL8gqb3v7fU6xHgqn7vT5dqchGwrxw3f0/jrhmPmcY+/TnwLeAh4G9o3DmzaI8bvxkrSZWbb5duJEldZtBLUuUMekmqnEEvSZUz6CWpcga9JFXOoJekyhn0klS5/wWdOSjOZFvQ5QAAAABJRU5ErkJggg==\n",
      "text/plain": [
       "<Figure size 432x288 with 1 Axes>"
      ]
     },
     "metadata": {},
     "output_type": "display_data"
    }
   ],
   "source": [
    "dfs.toPandas()['Rent'].hist()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "However, usually `dfs` is too big to be able to plot it (otherwise you may use pandas from the beginning), solution? Sampling:"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 60,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "<matplotlib.axes._subplots.AxesSubplot at 0x7f6237208da0>"
      ]
     },
     "execution_count": 60,
     "metadata": {},
     "output_type": "execute_result"
    },
    {
     "data": {
      "image/png": "iVBORw0KGgoAAAANSUhEUgAAAXQAAAD8CAYAAABn919SAAAABHNCSVQICAgIfAhkiAAAAAlwSFlzAAALEgAACxIB0t1+/AAAADl0RVh0U29mdHdhcmUAbWF0cGxvdGxpYiB2ZXJzaW9uIDMuMC4yLCBodHRwOi8vbWF0cGxvdGxpYi5vcmcvOIA7rQAAD/tJREFUeJzt3XuMXOV5x/HvU5sEylJsAqxcg7pEsaKguLmwokRU1S65QxT4g1QglJqW1lIvUaq4ak0jRY3Uqk4lkrRSpcQKaf1HkoXmIixISiyHbVSpJbEDxFCX2kFuCli20oCbjVBbp0//2Nfpxt31zF7mss9+P9JoznnPO2eexxr/9uyZmbORmUiSVr+fGnQBkqSVYaBLUhEGuiQVYaBLUhEGuiQVYaBLUhEGuiQVYaBLUhEGuiQVsb6fT3bppZfm2NhYP5+yL374wx9y4YUXDrqMvrHf+tZaz8Pe78GDB7+XmZd1mtfXQB8bG+PAgQP9fMq+mJ6eZmJiYtBl9I391rfWeh72fiPiX7uZ5ykXSSrCQJekIgx0SSrCQJekIgx0SSrCQJekIgx0SSrCQJekIgx0SSqir98U1eKM7XxoIM97bNdNA3leScvjEbokFWGgS1IRBrokFWGgS1IRBrokFWGgS1IRBrokFWGgS1IRBrokFWGgS1IRBrokFWGgS1IRBrokFWGgS1IRXV0+NyKOAT8AfgSczszxiLgEuA8YA44Bv5yZL/SmTElSJ4s5Qp/MzNdn5nhb3wnsz8wtwP62LkkakOWccrkZ2NOW9wC3LL8cSdJSdRvoCXw1Ig5GxPY2NpqZxwHa/eW9KFCS1J3IzM6TIn42M5+PiMuBfcD7gL2ZuWHOnBcyc+M8j90ObAcYHR29ZmpqasWKHxYzMzOMjIys+H4PPXdqxffZja2bLz7n9l71O6zWWr+w9noe9n4nJycPzjndvaCuAv0nHhDxR8AM8BvARGYej4hNwHRmvvpcjx0fH88DBw4s6vlWg+npaSYmJlZ8v8P6N0V71e+wWmv9wtrredj7jYiuAr3jKZeIuDAiLjqzDLwNeBLYC2xr07YBDyy9XEnScnXzscVR4EsRcWb+ZzPzbyPim8D9EXEX8F3gPb0rU5LUScdAz8xngNfNM/7vwJt7UZQkafH8pqgkFWGgS1IRBrokFWGgS1IRBrokFWGgS1IRBrokFWGgS1IRBrokFWGgS1IRXf0JurWsmyse7th6mjsHdGXEXujUcy/77XSlR0kL8whdkoow0CWpCANdkoow0CWpCANdkoow0CWpCANdkoow0CWpCANdkoow0CWpCANdkoow0CWpCANdkoow0CWpCANdkoow0CWpCANdkoow0CWpiK4DPSLWRcRjEfFgW78qIh6NiCMRcV9EvKx3ZUqSOlnMEfr7gcNz1j8CfCwztwAvAHetZGGSpMXpKtAj4grgJuBTbT2AG4DPtyl7gFt6UaAkqTuRmZ0nRXwe+FPgIuD3gDuBf8zMV7XtVwJfyczXzvPY7cB2gNHR0WumpqZWrPh+OPTcqY5zRi+AEy/1oZgh0ct+t26+uDc7XoaZmRlGRkYGXUZfrbWeh73fycnJg5k53mne+k4TIuJdwMnMPBgRE2eG55k670+GzNwN7AYYHx/PiYmJ+aYNrTt3PtRxzo6tp7nnUMd/yjJ62e+xOyZ6st/lmJ6eZrW9bpdrrfVcpd9u/ldeD7w7Im4Ezgd+Bvg4sCEi1mfmaeAK4PnelSlJ6qTjOfTMvDszr8jMMeA24GuZeQfwCHBrm7YNeKBnVUqSOlrO59D/APhARBwFXgHcuzIlSZKWYlEnQjNzGphuy88A1658SZKkpfCbopJUhIEuSUUY6JJUhIEuSUUY6JJUhIEuSUUY6JJUhIEuSUUY6JJUhIEuSUUY6JJUhIEuSUUY6JJUhIEuSUUY6JJUhIEuSUUY6JJUhIEuSUUY6JJUhIEuSUUY6JJUhIEuSUUY6JJUhIEuSUUY6JJUhIEuSUUY6JJUhIEuSUV0DPSIOD8ivhERT0TEUxHx4TZ+VUQ8GhFHIuK+iHhZ78uVJC2kmyP0/wRuyMzXAa8H3hER1wEfAT6WmVuAF4C7elemJKmTjoGes2ba6nntlsANwOfb+B7glp5UKEnqSlfn0CNiXUQ8DpwE9gHfAV7MzNNtyrPA5t6UKEnqRmRm95MjNgBfAj4E/FVmvqqNXwl8OTO3zvOY7cB2gNHR0WumpqZWou6+OfTcqY5zRi+AEy/1oZgh0ct+t26+uDc7XoaZmRlGRkYGXUZfrbWeh73fycnJg5k53mne+sXsNDNfjIhp4DpgQ0Ssb0fpVwDPL/CY3cBugPHx8ZyYmFjMUw7cnTsf6jhnx9bT3HNoUf+Uq1ov+z12x0RP9rsc09PTrLbX7XKttZ6r9NvNp1wua0fmRMQFwFuAw8AjwK1t2jbggV4VKUnqrJvDrE3AnohYx+wPgPsz88GI+CdgKiL+GHgMuLeHdUqSOugY6Jn5beAN84w/A1zbi6IkSYvnN0UlqQgDXZKKMNAlqQgDXZKKMNAlqQgDXZKKMNAlqQgDXZKKMNAlqQgDXZKKWDuXCNSqMNbF1S174diumwbyvNJK8ghdkoow0CWpCANdkoow0CWpCANdkoow0CWpCANdkoow0CWpCANdkoow0CWpCANdkoow0CWpCANdkoow0CWpCANdkoow0CWpCANdkoow0CWpiI6BHhFXRsQjEXE4Ip6KiPe38UsiYl9EHGn3G3tfriRpId0coZ8GdmTma4DrgN+OiKuBncD+zNwC7G/rkqQB6RjomXk8M7/Vln8AHAY2AzcDe9q0PcAtvSpSktRZZGb3kyPGgK8DrwW+m5kb5mx7ITP/32mXiNgObAcYHR29ZmpqakmFHnru1JIe1w+jF8CJlwZdRf9U7Hfr5osX3DYzM8PIyEgfqxm8tdbzsPc7OTl5MDPHO83rOtAjYgT4O+BPMvOLEfFiN4E+1/j4eB44cKCr5zvb2M6HlvS4ftix9TT3HFo/6DL6pmK/x3bdtOC26elpJiYm+lfMEFhrPQ97vxHRVaB39SmXiDgP+ALwmcz8Yhs+ERGb2vZNwMmlFitJWr5uPuUSwL3A4cz86JxNe4FtbXkb8MDKlydJ6lY3vzdfD7wXOBQRj7exPwR2AfdHxF3Ad4H39KZESVI3OgZ6Zv49EAtsfvPKliNJWiq/KSpJRRjoklSEgS5JRRjoklSEgS5JRRjoklSEgS5JRRjoklSEgS5JRRjoklSEgS5JRRjoklSEgS5JRRjoklSEgS5JRRjoklSEgS5JRRjoklSEgS5JRRjoklSEgS5JRawfdAHSMBjb+dCC23ZsPc2d59i+Wh3bddOgS9AK8whdkoow0CWpCANdkoow0CWpCANdkoow0CWpiI6BHhGfjoiTEfHknLFLImJfRBxp9xt7W6YkqZNujtD/GnjHWWM7gf2ZuQXY39YlSQPUMdAz8+vA988avhnY05b3ALescF2SpEVa6jn00cw8DtDuL1+5kiRJSxGZ2XlSxBjwYGa+tq2/mJkb5mx/ITPnPY8eEduB7QCjo6PXTE1NLanQQ8+dWtLj+mH0Ajjx0qCr6B/7rWHr5osX3DYzM8PIyEgfqxmsYe93cnLyYGaOd5q31Gu5nIiITZl5PCI2AScXmpiZu4HdAOPj4zkxMbGkJxzma2ns2Hqaew6tncvi2G8Nx+6YWHDb9PQ0S/2/uhpV6Xepp1z2Atva8jbggZUpR5K0VN18bPFzwD8Ar46IZyPiLmAX8NaIOAK8ta1Lkgao4++RmXn7ApvevMK1SJKWwW+KSlIRBrokFWGgS1IRBrokFWGgS1IRBrokFWGgS1IRBrokFWGgS1IRBrokFWGgS1IRBrokFWGgS1IRBrokFVHvz7BI6srYOf4K2I6tp3v6V8KO7bqpZ/teyzxCl6QiDHRJKsJAl6QiDHRJKsJAl6QiDHRJKsJAl6QiDHRJKsJAl6QiDHRJKsJAl6QiDHRJKsJAl6QiDHRJKmJZl8+NiHcAfw6sAz6VmbtWpCpJpZ3r0r2DUOVywUs+Qo+IdcBfAu8ErgZuj4irV6owSdLiLOeUy7XA0cx8JjP/C5gCbl6ZsiRJi7WcQN8M/Nuc9WfbmCRpACIzl/bAiPcAb8/MX2/r7wWuzcz3nTVvO7C9rb4aeHrp5Q6tS4HvDbqIPrLf+tZaz8Pe789l5mWdJi3nTdFngSvnrF8BPH/2pMzcDexexvMMvYg4kJnjg66jX+y3vrXWc5V+l3PK5ZvAloi4KiJeBtwG7F2ZsiRJi7XkI/TMPB0RvwM8zOzHFj+dmU+tWGWSpEVZ1ufQM/PLwJdXqJbVrPQppXnYb31rrecS/S75TVFJ0nDxq/+SVISBPo+I+HREnIyIJ+eMXRIR+yLiSLvf2MYjIv4iIo5GxLcj4o1zHrOtzT8SEdsG0Us3IuLKiHgkIg5HxFMR8f42Xrnn8yPiGxHxROv5w238qoh4tNV/X3vDn4h4eVs/2raPzdnX3W386Yh4+2A66k5ErIuIxyLiwbZevd9jEXEoIh6PiANtrOzrmsz0dtYN+CXgjcCTc8b+DNjZlncCH2nLNwJfAQK4Dni0jV8CPNPuN7bljYPubYF+NwFvbMsXAf/C7OUcKvccwEhbPg94tPVyP3BbG/8E8Jtt+beAT7Tl24D72vLVwBPAy4GrgO8A6wbd3zn6/gDwWeDBtl6932PApWeN1X1dD7qAYb0BY2cF+tPApra8CXi6LX8SuP3secDtwCfnjP/EvGG+AQ8Ab10rPQM/DXwL+AVmv1yyvo2/CXi4LT8MvKktr2/zArgbuHvOvn48b9huzH5XZD9wA/Bgq79sv62++QK97OvaUy7dG83M4wDt/vI2vtAlEFblpRHar9ZvYPaItXTP7fTD48BJYB+zR5svZubpNmVu/T/urW0/BbyC1dXzx4HfB/6nrb+C2v0CJPDViDjYvrUOhV/Xy/rYooDZo5az5TnGh1ZEjABfAH43M/8jYr4WZqfOM7bqes7MHwGvj4gNwJeA18w3rd2v6p4j4l3Aycw8GBETZ4bnmVqi3zmuz8znI+JyYF9E/PM55q76nj1C796JiNgE0O5PtvGFLoHQ1aURhkVEnMdsmH8mM7/Yhkv3fEZmvghMM3vedENEnDnQmVv/j3tr2y8Gvs/q6fl64N0RcYzZK6PewOwRe9V+AcjM59v9SWZ/aF9L4de1gd69vcCZd7e3MXue+cz4r7R3yK8DTrVf4x4G3hYRG9u76G9rY0MnZg/F7wUOZ+ZH52yq3PNl7ciciLgAeAtwGHgEuLVNO7vnM/8WtwJfy9kTqnuB29qnQq4CtgDf6E8X3cvMuzPziswcY/ZNzq9l5h0U7RcgIi6MiIvOLDP7enySwq/rgZ/EH8Yb8DngOPDfzP50vovZ84f7gSPt/pI2N5j9Qx/fAQ4B43P282vA0Xb71UH3dY5+f5HZXyG/DTzebjcW7/nngcdaz08CH2rjr2Q2oI4CfwO8vI2f39aPtu2vnLOvD7Z/i6eBdw66ty56n+D/PuVStt/W2xPt9hTwwTZe9nXtN0UlqQhPuUhSEQa6JBVhoEtSEQa6JBVhoEtSEQa6JBVhoEtSEQa6JBXxv6x1qnqNmugOAAAAAElFTkSuQmCC\n",
      "text/plain": [
       "<Figure size 432x288 with 1 Axes>"
      ]
     },
     "metadata": {},
     "output_type": "display_data"
    }
   ],
   "source": [
    "dfs.sample(withReplacement=False, fraction=0.4).toPandas()['Rent'].hist()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### SQL"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "**Pandas**"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "no built-in SQL to Pandas! "
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "**PySpark**"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 62,
   "metadata": {},
   "outputs": [],
   "source": [
    "dfs.createOrReplaceTempView('dfview')"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 66,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "+--------------------+-----+\n",
      "|             Address| Rent|\n",
      "+--------------------+-----+\n",
      "|Av. de Chailly 52...|360.0|\n",
      "|Av. de Chailly 52...|380.0|\n",
      "|Av. de Riant-Mont...|637.0|\n",
      "|Av. de Tivoli 19B...|725.0|\n",
      "|Isabelle de Monto...|780.0|\n",
      "|Rue St-Martin 28,...|840.0|\n",
      "|Avenue Sainte-Luc...|850.0|\n",
      "|Chemin de Montoli...|860.0|\n",
      "|Ch. du Devin 64, ...|940.0|\n",
      "|Chemin des Alling...|300.0|\n",
      "|   1007 Lausanne, VD|400.0|\n",
      "|1004 Lausanne, VD...|500.0|\n",
      "|Chemin de Longera...|500.0|\n",
      "|Chailly, 1012 Lau...|600.0|\n",
      "|   1004 Lausanne, VD|600.0|\n",
      "|Avenue de Rhodani...|600.0|\n",
      "|1007 Lausanne, 10...|600.0|\n",
      "|avenue Druey coll...|600.0|\n",
      "|Avenue Marc-Dufou...|640.0|\n",
      "|   1010 Lausanne, VD|650.0|\n",
      "+--------------------+-----+\n",
      "only showing top 20 rows\n",
      "\n"
     ]
    }
   ],
   "source": [
    "spark.sql('SELECT Address, Rent FROM dfview WHERE Rent < 1000').show()"
   ]
  }
 ],
 "metadata": {
  "kernelspec": {
   "display_name": "Python 3",
   "language": "python",
   "name": "python3"
  },
  "language_info": {
   "codemirror_mode": {
    "name": "ipython",
    "version": 3
   },
   "file_extension": ".py",
   "mimetype": "text/x-python",
   "name": "python",
   "nbconvert_exporter": "python",
   "pygments_lexer": "ipython3",
   "version": "3.6.6"
  },
  "toc-autonumbering": false,
  "toc-showmarkdowntxt": true
 },
 "nbformat": 4,
 "nbformat_minor": 2
}