1. Pitfalls.ipynb

{
 "cells": [
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "<div style=\"width: 100%; overflow: hidden;\">\n",
    "    <div style=\"width: 150px; float: left;\"> <img src=\"https://raw.githubusercontent.com/DataForScience/Networks/master/data/D4Sci_logo_ball.png\" alt=\"Data For Science, Inc\" align=\"left\" border=\"0\" width=150px> </div>\n",
    "    <div style=\"float: left; margin-left: 10px;\"> <h1>Transforming Excel Analysis into pandas Data Models</h1>\n",
    "<h1>Excel Pitfalls</h1>\n",
    "        <p>Bruno Gonçalves<br/>\n",
    "        <a href=\"http://www.data4sci.com/\">www.data4sci.com</a><br/>\n",
    "            @bgoncalves, @data4sci</p></div>\n",
    "</div>"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 1,
   "metadata": {},
   "outputs": [],
   "source": [
    "from collections import Counter\n",
    "from pprint import pprint\n",
    "\n",
    "import pandas as pd\n",
    "import numpy as np\n",
    "import numpy_financial as npf\n",
    "\n",
    "import matplotlib\n",
    "import matplotlib.pyplot as plt \n",
    "\n",
    "import watermark\n",
    "\n",
    "%load_ext watermark\n",
    "%matplotlib inline"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "We start by print out the versions of the libraries we're using for future reference"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 2,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "Python implementation: CPython\n",
      "Python version       : 3.8.5\n",
      "IPython version      : 7.19.0\n",
      "\n",
      "Compiler    : Clang 10.0.0 \n",
      "OS          : Darwin\n",
      "Release     : 21.6.0\n",
      "Machine     : x86_64\n",
      "Processor   : i386\n",
      "CPU cores   : 16\n",
      "Architecture: 64bit\n",
      "\n",
      "Git hash: 8cd8c4969f6d4b5f0dbf5d55746efe085989b1e3\n",
      "\n",
      "pandas         : 1.1.3\n",
      "numpy_financial: 1.0.0\n",
      "json           : 2.0.9\n",
      "watermark      : 2.1.0\n",
      "numpy          : 1.19.2\n",
      "matplotlib     : 3.3.2\n",
      "\n"
     ]
    }
   ],
   "source": [
    "%watermark -n -v -m -g -iv"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Load default figure style"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 3,
   "metadata": {},
   "outputs": [],
   "source": [
    "plt.style.use('./d4sci.mplstyle')"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## Large file"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Data Frames are limited only by available memory, and have no fixed limit on the number of rows or columns"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 4,
   "metadata": {},
   "outputs": [],
   "source": [
    "taxis = pd.read_csv('data/green_tripdata_2014-04.csv.gz', \n",
    "        parse_dates=['lpep_pickup_datetime', 'Lpep_dropoff_datetime']\n",
    "                   )"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 5,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "-rw-r--r--@ 1 bgoncalves  staff    51M Sep  1  2020 data/green_tripdata_2014-04.csv.gz\r\n"
     ]
    }
   ],
   "source": [
    "!ls -lh 'data/green_tripdata_2014-04.csv.gz'"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Even relatively small files can have a large number of rows"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 6,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "(1309155, 22)"
      ]
     },
     "execution_count": 6,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "taxis.shape"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "And we can be sure that each column has a unique format without any unexpected changes in formatting"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 7,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "<class 'pandas.core.frame.DataFrame'>\n",
      "RangeIndex: 1309155 entries, 0 to 1309154\n",
      "Data columns (total 22 columns):\n",
      " #   Column                 Non-Null Count    Dtype         \n",
      "---  ------                 --------------    -----         \n",
      " 0   VendorID               1309155 non-null  int64         \n",
      " 1   lpep_pickup_datetime   1309155 non-null  datetime64[ns]\n",
      " 2   Lpep_dropoff_datetime  1309155 non-null  datetime64[ns]\n",
      " 3   Store_and_fwd_flag     1309155 non-null  object        \n",
      " 4   RateCodeID             1309155 non-null  int64         \n",
      " 5   Pickup_longitude       1309155 non-null  float64       \n",
      " 6   Pickup_latitude        1309155 non-null  float64       \n",
      " 7   Dropoff_longitude      1309155 non-null  float64       \n",
      " 8   Dropoff_latitude       1309155 non-null  float64       \n",
      " 9   Passenger_count        1309155 non-null  int64         \n",
      " 10  Trip_distance          1309155 non-null  float64       \n",
      " 11  Fare_amount            1309155 non-null  float64       \n",
      " 12  Extra                  1309155 non-null  float64       \n",
      " 13  MTA_tax                1309155 non-null  float64       \n",
      " 14  Tip_amount             1309155 non-null  float64       \n",
      " 15  Tolls_amount           1309155 non-null  float64       \n",
      " 16  Ehail_fee              0 non-null        float64       \n",
      " 17  Total_amount           1309155 non-null  float64       \n",
      " 18  Payment_type           1309155 non-null  int64         \n",
      " 19  Trip_type              1225061 non-null  float64       \n",
      " 20  Unnamed: 20            0 non-null        float64       \n",
      " 21                         0 non-null        float64       \n",
      "dtypes: datetime64[ns](2), float64(15), int64(4), object(1)\n",
      "memory usage: 219.7+ MB\n"
     ]
    }
   ],
   "source": [
    "taxis.info()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Computations are column based and written in a compact form"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 8,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "array([ 1., nan,  2.])"
      ]
     },
     "execution_count": 8,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "taxis['Trip_type'].unique()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 9,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "12.339962212266684"
      ]
     },
     "execution_count": 9,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "taxis['Fare_amount'].mean()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Easily index any part of the full DataFrame"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 10,
   "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>VendorID</th>\n",
       "      <th>lpep_pickup_datetime</th>\n",
       "      <th>Lpep_dropoff_datetime</th>\n",
       "      <th>Store_and_fwd_flag</th>\n",
       "      <th>RateCodeID</th>\n",
       "      <th>Pickup_longitude</th>\n",
       "      <th>Pickup_latitude</th>\n",
       "      <th>Dropoff_longitude</th>\n",
       "      <th>Dropoff_latitude</th>\n",
       "      <th>Passenger_count</th>\n",
       "      <th>...</th>\n",
       "      <th>Extra</th>\n",
       "      <th>MTA_tax</th>\n",
       "      <th>Tip_amount</th>\n",
       "      <th>Tolls_amount</th>\n",
       "      <th>Ehail_fee</th>\n",
       "      <th>Total_amount</th>\n",
       "      <th>Payment_type</th>\n",
       "      <th>Trip_type</th>\n",
       "      <th>Unnamed: 20</th>\n",
       "      <th></th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>1000</th>\n",
       "      <td>2</td>\n",
       "      <td>2014-04-01 01:00:44</td>\n",
       "      <td>2014-04-01 01:13:30</td>\n",
       "      <td>N</td>\n",
       "      <td>1</td>\n",
       "      <td>-73.891830</td>\n",
       "      <td>40.747021</td>\n",
       "      <td>-73.917595</td>\n",
       "      <td>40.834263</td>\n",
       "      <td>5</td>\n",
       "      <td>...</td>\n",
       "      <td>0.5</td>\n",
       "      <td>0.5</td>\n",
       "      <td>0.0</td>\n",
       "      <td>5.33</td>\n",
       "      <td>NaN</td>\n",
       "      <td>28.33</td>\n",
       "      <td>2</td>\n",
       "      <td>1.0</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1001</th>\n",
       "      <td>1</td>\n",
       "      <td>2014-04-01 01:00:45</td>\n",
       "      <td>2014-04-01 01:08:54</td>\n",
       "      <td>N</td>\n",
       "      <td>1</td>\n",
       "      <td>-73.918846</td>\n",
       "      <td>40.759144</td>\n",
       "      <td>-73.944969</td>\n",
       "      <td>40.753735</td>\n",
       "      <td>1</td>\n",
       "      <td>...</td>\n",
       "      <td>0.5</td>\n",
       "      <td>0.5</td>\n",
       "      <td>2.0</td>\n",
       "      <td>0.00</td>\n",
       "      <td>NaN</td>\n",
       "      <td>11.50</td>\n",
       "      <td>1</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1002</th>\n",
       "      <td>2</td>\n",
       "      <td>2014-04-01 01:00:46</td>\n",
       "      <td>2014-04-01 01:13:03</td>\n",
       "      <td>N</td>\n",
       "      <td>5</td>\n",
       "      <td>-73.919205</td>\n",
       "      <td>40.815300</td>\n",
       "      <td>-73.946342</td>\n",
       "      <td>40.797829</td>\n",
       "      <td>1</td>\n",
       "      <td>...</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.00</td>\n",
       "      <td>NaN</td>\n",
       "      <td>0.12</td>\n",
       "      <td>2</td>\n",
       "      <td>2.0</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1003</th>\n",
       "      <td>2</td>\n",
       "      <td>2014-04-01 01:00:59</td>\n",
       "      <td>2014-04-01 01:18:16</td>\n",
       "      <td>N</td>\n",
       "      <td>1</td>\n",
       "      <td>-73.937576</td>\n",
       "      <td>40.758251</td>\n",
       "      <td>-73.958313</td>\n",
       "      <td>40.731274</td>\n",
       "      <td>1</td>\n",
       "      <td>...</td>\n",
       "      <td>0.5</td>\n",
       "      <td>0.5</td>\n",
       "      <td>2.0</td>\n",
       "      <td>0.00</td>\n",
       "      <td>NaN</td>\n",
       "      <td>21.50</td>\n",
       "      <td>1</td>\n",
       "      <td>1.0</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1004</th>\n",
       "      <td>2</td>\n",
       "      <td>2014-04-01 01:01:07</td>\n",
       "      <td>2014-04-01 01:01:24</td>\n",
       "      <td>N</td>\n",
       "      <td>5</td>\n",
       "      <td>-73.871689</td>\n",
       "      <td>40.852695</td>\n",
       "      <td>-73.871452</td>\n",
       "      <td>40.846371</td>\n",
       "      <td>1</td>\n",
       "      <td>...</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>4.0</td>\n",
       "      <td>0.00</td>\n",
       "      <td>NaN</td>\n",
       "      <td>24.00</td>\n",
       "      <td>1</td>\n",
       "      <td>2.0</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1005</th>\n",
       "      <td>2</td>\n",
       "      <td>2014-04-01 01:01:07</td>\n",
       "      <td>2014-04-01 01:06:28</td>\n",
       "      <td>N</td>\n",
       "      <td>1</td>\n",
       "      <td>-73.947624</td>\n",
       "      <td>40.711250</td>\n",
       "      <td>-73.934898</td>\n",
       "      <td>40.703197</td>\n",
       "      <td>1</td>\n",
       "      <td>...</td>\n",
       "      <td>0.5</td>\n",
       "      <td>0.5</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.00</td>\n",
       "      <td>NaN</td>\n",
       "      <td>7.50</td>\n",
       "      <td>2</td>\n",
       "      <td>1.0</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1006</th>\n",
       "      <td>1</td>\n",
       "      <td>2014-04-01 01:01:08</td>\n",
       "      <td>2014-04-01 01:07:29</td>\n",
       "      <td>N</td>\n",
       "      <td>1</td>\n",
       "      <td>-73.997536</td>\n",
       "      <td>40.594048</td>\n",
       "      <td>-73.998772</td>\n",
       "      <td>40.577499</td>\n",
       "      <td>1</td>\n",
       "      <td>...</td>\n",
       "      <td>0.5</td>\n",
       "      <td>0.5</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.00</td>\n",
       "      <td>NaN</td>\n",
       "      <td>9.00</td>\n",
       "      <td>2</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1007</th>\n",
       "      <td>1</td>\n",
       "      <td>2014-04-01 01:01:15</td>\n",
       "      <td>2014-04-01 01:21:49</td>\n",
       "      <td>N</td>\n",
       "      <td>1</td>\n",
       "      <td>-73.975716</td>\n",
       "      <td>40.687069</td>\n",
       "      <td>-73.974289</td>\n",
       "      <td>40.646133</td>\n",
       "      <td>1</td>\n",
       "      <td>...</td>\n",
       "      <td>0.5</td>\n",
       "      <td>0.5</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.00</td>\n",
       "      <td>NaN</td>\n",
       "      <td>19.00</td>\n",
       "      <td>2</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1008</th>\n",
       "      <td>2</td>\n",
       "      <td>2014-04-01 01:01:19</td>\n",
       "      <td>2014-04-01 01:10:20</td>\n",
       "      <td>N</td>\n",
       "      <td>1</td>\n",
       "      <td>-73.844254</td>\n",
       "      <td>40.721008</td>\n",
       "      <td>-73.806267</td>\n",
       "      <td>40.678631</td>\n",
       "      <td>1</td>\n",
       "      <td>...</td>\n",
       "      <td>0.5</td>\n",
       "      <td>0.5</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.00</td>\n",
       "      <td>NaN</td>\n",
       "      <td>14.50</td>\n",
       "      <td>1</td>\n",
       "      <td>1.0</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1009</th>\n",
       "      <td>2</td>\n",
       "      <td>2014-04-01 01:01:22</td>\n",
       "      <td>2014-04-01 01:03:55</td>\n",
       "      <td>N</td>\n",
       "      <td>1</td>\n",
       "      <td>-73.931755</td>\n",
       "      <td>40.765072</td>\n",
       "      <td>-73.918663</td>\n",
       "      <td>40.758862</td>\n",
       "      <td>1</td>\n",
       "      <td>...</td>\n",
       "      <td>0.5</td>\n",
       "      <td>0.5</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.00</td>\n",
       "      <td>NaN</td>\n",
       "      <td>5.00</td>\n",
       "      <td>2</td>\n",
       "      <td>1.0</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1010</th>\n",
       "      <td>1</td>\n",
       "      <td>2014-04-01 01:01:23</td>\n",
       "      <td>2014-04-01 01:06:46</td>\n",
       "      <td>Y</td>\n",
       "      <td>1</td>\n",
       "      <td>-73.952744</td>\n",
       "      <td>40.810692</td>\n",
       "      <td>-73.940605</td>\n",
       "      <td>40.828094</td>\n",
       "      <td>1</td>\n",
       "      <td>...</td>\n",
       "      <td>0.5</td>\n",
       "      <td>0.5</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.00</td>\n",
       "      <td>NaN</td>\n",
       "      <td>8.50</td>\n",
       "      <td>2</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1011</th>\n",
       "      <td>1</td>\n",
       "      <td>2014-04-01 01:01:31</td>\n",
       "      <td>2014-04-01 01:10:57</td>\n",
       "      <td>N</td>\n",
       "      <td>1</td>\n",
       "      <td>-73.956474</td>\n",
       "      <td>40.674782</td>\n",
       "      <td>-73.930473</td>\n",
       "      <td>40.674763</td>\n",
       "      <td>1</td>\n",
       "      <td>...</td>\n",
       "      <td>0.5</td>\n",
       "      <td>0.5</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.00</td>\n",
       "      <td>NaN</td>\n",
       "      <td>9.50</td>\n",
       "      <td>1</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1012</th>\n",
       "      <td>1</td>\n",
       "      <td>2014-04-01 01:01:34</td>\n",
       "      <td>2014-04-01 01:08:22</td>\n",
       "      <td>N</td>\n",
       "      <td>1</td>\n",
       "      <td>-73.946671</td>\n",
       "      <td>40.831303</td>\n",
       "      <td>-73.923103</td>\n",
       "      <td>40.826168</td>\n",
       "      <td>2</td>\n",
       "      <td>...</td>\n",
       "      <td>0.5</td>\n",
       "      <td>0.5</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.00</td>\n",
       "      <td>NaN</td>\n",
       "      <td>8.50</td>\n",
       "      <td>1</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1013</th>\n",
       "      <td>2</td>\n",
       "      <td>2014-04-01 01:01:37</td>\n",
       "      <td>2014-04-01 01:06:59</td>\n",
       "      <td>N</td>\n",
       "      <td>1</td>\n",
       "      <td>-73.891136</td>\n",
       "      <td>40.746952</td>\n",
       "      <td>-73.881996</td>\n",
       "      <td>40.755840</td>\n",
       "      <td>1</td>\n",
       "      <td>...</td>\n",
       "      <td>0.5</td>\n",
       "      <td>0.5</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.00</td>\n",
       "      <td>NaN</td>\n",
       "      <td>7.00</td>\n",
       "      <td>2</td>\n",
       "      <td>1.0</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1014</th>\n",
       "      <td>2</td>\n",
       "      <td>2014-04-01 01:01:40</td>\n",
       "      <td>2014-04-01 01:21:38</td>\n",
       "      <td>N</td>\n",
       "      <td>1</td>\n",
       "      <td>-73.850029</td>\n",
       "      <td>40.724995</td>\n",
       "      <td>-73.945465</td>\n",
       "      <td>40.745953</td>\n",
       "      <td>2</td>\n",
       "      <td>...</td>\n",
       "      <td>0.5</td>\n",
       "      <td>0.5</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.00</td>\n",
       "      <td>NaN</td>\n",
       "      <td>21.00</td>\n",
       "      <td>2</td>\n",
       "      <td>1.0</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1015</th>\n",
       "      <td>1</td>\n",
       "      <td>2014-04-01 01:01:40</td>\n",
       "      <td>2014-04-01 01:14:44</td>\n",
       "      <td>N</td>\n",
       "      <td>1</td>\n",
       "      <td>-73.957581</td>\n",
       "      <td>40.718067</td>\n",
       "      <td>-73.962898</td>\n",
       "      <td>40.687035</td>\n",
       "      <td>2</td>\n",
       "      <td>...</td>\n",
       "      <td>0.5</td>\n",
       "      <td>0.5</td>\n",
       "      <td>2.6</td>\n",
       "      <td>0.00</td>\n",
       "      <td>NaN</td>\n",
       "      <td>15.60</td>\n",
       "      <td>1</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1016</th>\n",
       "      <td>2</td>\n",
       "      <td>2014-04-01 01:01:43</td>\n",
       "      <td>2014-04-01 01:23:42</td>\n",
       "      <td>N</td>\n",
       "      <td>1</td>\n",
       "      <td>-73.918800</td>\n",
       "      <td>40.743397</td>\n",
       "      <td>-73.963913</td>\n",
       "      <td>40.798000</td>\n",
       "      <td>1</td>\n",
       "      <td>...</td>\n",
       "      <td>0.5</td>\n",
       "      <td>0.5</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.00</td>\n",
       "      <td>NaN</td>\n",
       "      <td>22.50</td>\n",
       "      <td>2</td>\n",
       "      <td>1.0</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1017</th>\n",
       "      <td>1</td>\n",
       "      <td>2014-04-01 01:01:45</td>\n",
       "      <td>2014-04-01 01:11:38</td>\n",
       "      <td>N</td>\n",
       "      <td>1</td>\n",
       "      <td>-73.953041</td>\n",
       "      <td>40.672836</td>\n",
       "      <td>-73.958801</td>\n",
       "      <td>40.711212</td>\n",
       "      <td>1</td>\n",
       "      <td>...</td>\n",
       "      <td>0.5</td>\n",
       "      <td>0.5</td>\n",
       "      <td>3.1</td>\n",
       "      <td>0.00</td>\n",
       "      <td>NaN</td>\n",
       "      <td>15.60</td>\n",
       "      <td>1</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1018</th>\n",
       "      <td>2</td>\n",
       "      <td>2014-04-01 01:01:52</td>\n",
       "      <td>2014-04-01 01:25:58</td>\n",
       "      <td>N</td>\n",
       "      <td>1</td>\n",
       "      <td>-73.895386</td>\n",
       "      <td>40.726269</td>\n",
       "      <td>-73.895363</td>\n",
       "      <td>40.732250</td>\n",
       "      <td>1</td>\n",
       "      <td>...</td>\n",
       "      <td>0.5</td>\n",
       "      <td>0.5</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.00</td>\n",
       "      <td>NaN</td>\n",
       "      <td>20.50</td>\n",
       "      <td>2</td>\n",
       "      <td>1.0</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1019</th>\n",
       "      <td>2</td>\n",
       "      <td>2014-04-01 01:01:56</td>\n",
       "      <td>2014-04-01 01:05:10</td>\n",
       "      <td>N</td>\n",
       "      <td>1</td>\n",
       "      <td>-73.940369</td>\n",
       "      <td>40.839466</td>\n",
       "      <td>-73.947311</td>\n",
       "      <td>40.830639</td>\n",
       "      <td>1</td>\n",
       "      <td>...</td>\n",
       "      <td>0.5</td>\n",
       "      <td>0.5</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.00</td>\n",
       "      <td>NaN</td>\n",
       "      <td>5.50</td>\n",
       "      <td>2</td>\n",
       "      <td>1.0</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "<p>20 rows × 22 columns</p>\n",
       "</div>"
      ],
      "text/plain": [
       "      VendorID lpep_pickup_datetime Lpep_dropoff_datetime Store_and_fwd_flag  \\\n",
       "1000         2  2014-04-01 01:00:44   2014-04-01 01:13:30                  N   \n",
       "1001         1  2014-04-01 01:00:45   2014-04-01 01:08:54                  N   \n",
       "1002         2  2014-04-01 01:00:46   2014-04-01 01:13:03                  N   \n",
       "1003         2  2014-04-01 01:00:59   2014-04-01 01:18:16                  N   \n",
       "1004         2  2014-04-01 01:01:07   2014-04-01 01:01:24                  N   \n",
       "1005         2  2014-04-01 01:01:07   2014-04-01 01:06:28                  N   \n",
       "1006         1  2014-04-01 01:01:08   2014-04-01 01:07:29                  N   \n",
       "1007         1  2014-04-01 01:01:15   2014-04-01 01:21:49                  N   \n",
       "1008         2  2014-04-01 01:01:19   2014-04-01 01:10:20                  N   \n",
       "1009         2  2014-04-01 01:01:22   2014-04-01 01:03:55                  N   \n",
       "1010         1  2014-04-01 01:01:23   2014-04-01 01:06:46                  Y   \n",
       "1011         1  2014-04-01 01:01:31   2014-04-01 01:10:57                  N   \n",
       "1012         1  2014-04-01 01:01:34   2014-04-01 01:08:22                  N   \n",
       "1013         2  2014-04-01 01:01:37   2014-04-01 01:06:59                  N   \n",
       "1014         2  2014-04-01 01:01:40   2014-04-01 01:21:38                  N   \n",
       "1015         1  2014-04-01 01:01:40   2014-04-01 01:14:44                  N   \n",
       "1016         2  2014-04-01 01:01:43   2014-04-01 01:23:42                  N   \n",
       "1017         1  2014-04-01 01:01:45   2014-04-01 01:11:38                  N   \n",
       "1018         2  2014-04-01 01:01:52   2014-04-01 01:25:58                  N   \n",
       "1019         2  2014-04-01 01:01:56   2014-04-01 01:05:10                  N   \n",
       "\n",
       "      RateCodeID  Pickup_longitude  Pickup_latitude  Dropoff_longitude  \\\n",
       "1000           1        -73.891830        40.747021         -73.917595   \n",
       "1001           1        -73.918846        40.759144         -73.944969   \n",
       "1002           5        -73.919205        40.815300         -73.946342   \n",
       "1003           1        -73.937576        40.758251         -73.958313   \n",
       "1004           5        -73.871689        40.852695         -73.871452   \n",
       "1005           1        -73.947624        40.711250         -73.934898   \n",
       "1006           1        -73.997536        40.594048         -73.998772   \n",
       "1007           1        -73.975716        40.687069         -73.974289   \n",
       "1008           1        -73.844254        40.721008         -73.806267   \n",
       "1009           1        -73.931755        40.765072         -73.918663   \n",
       "1010           1        -73.952744        40.810692         -73.940605   \n",
       "1011           1        -73.956474        40.674782         -73.930473   \n",
       "1012           1        -73.946671        40.831303         -73.923103   \n",
       "1013           1        -73.891136        40.746952         -73.881996   \n",
       "1014           1        -73.850029        40.724995         -73.945465   \n",
       "1015           1        -73.957581        40.718067         -73.962898   \n",
       "1016           1        -73.918800        40.743397         -73.963913   \n",
       "1017           1        -73.953041        40.672836         -73.958801   \n",
       "1018           1        -73.895386        40.726269         -73.895363   \n",
       "1019           1        -73.940369        40.839466         -73.947311   \n",
       "\n",
       "      Dropoff_latitude  Passenger_count  ...  Extra  MTA_tax  Tip_amount  \\\n",
       "1000         40.834263                5  ...    0.5      0.5         0.0   \n",
       "1001         40.753735                1  ...    0.5      0.5         2.0   \n",
       "1002         40.797829                1  ...    0.0      0.0         0.0   \n",
       "1003         40.731274                1  ...    0.5      0.5         2.0   \n",
       "1004         40.846371                1  ...    0.0      0.0         4.0   \n",
       "1005         40.703197                1  ...    0.5      0.5         0.0   \n",
       "1006         40.577499                1  ...    0.5      0.5         0.0   \n",
       "1007         40.646133                1  ...    0.5      0.5         0.0   \n",
       "1008         40.678631                1  ...    0.5      0.5         0.0   \n",
       "1009         40.758862                1  ...    0.5      0.5         0.0   \n",
       "1010         40.828094                1  ...    0.5      0.5         0.0   \n",
       "1011         40.674763                1  ...    0.5      0.5         0.0   \n",
       "1012         40.826168                2  ...    0.5      0.5         0.0   \n",
       "1013         40.755840                1  ...    0.5      0.5         0.0   \n",
       "1014         40.745953                2  ...    0.5      0.5         0.0   \n",
       "1015         40.687035                2  ...    0.5      0.5         2.6   \n",
       "1016         40.798000                1  ...    0.5      0.5         0.0   \n",
       "1017         40.711212                1  ...    0.5      0.5         3.1   \n",
       "1018         40.732250                1  ...    0.5      0.5         0.0   \n",
       "1019         40.830639                1  ...    0.5      0.5         0.0   \n",
       "\n",
       "      Tolls_amount  Ehail_fee  Total_amount  Payment_type  Trip_type  \\\n",
       "1000          5.33        NaN         28.33             2        1.0   \n",
       "1001          0.00        NaN         11.50             1        NaN   \n",
       "1002          0.00        NaN          0.12             2        2.0   \n",
       "1003          0.00        NaN         21.50             1        1.0   \n",
       "1004          0.00        NaN         24.00             1        2.0   \n",
       "1005          0.00        NaN          7.50             2        1.0   \n",
       "1006          0.00        NaN          9.00             2        NaN   \n",
       "1007          0.00        NaN         19.00             2        NaN   \n",
       "1008          0.00        NaN         14.50             1        1.0   \n",
       "1009          0.00        NaN          5.00             2        1.0   \n",
       "1010          0.00        NaN          8.50             2        NaN   \n",
       "1011          0.00        NaN          9.50             1        NaN   \n",
       "1012          0.00        NaN          8.50             1        NaN   \n",
       "1013          0.00        NaN          7.00             2        1.0   \n",
       "1014          0.00        NaN         21.00             2        1.0   \n",
       "1015          0.00        NaN         15.60             1        NaN   \n",
       "1016          0.00        NaN         22.50             2        1.0   \n",
       "1017          0.00        NaN         15.60             1        NaN   \n",
       "1018          0.00        NaN         20.50             2        1.0   \n",
       "1019          0.00        NaN          5.50             2        1.0   \n",
       "\n",
       "      Unnamed: 20      \n",
       "1000          NaN NaN  \n",
       "1001          NaN NaN  \n",
       "1002          NaN NaN  \n",
       "1003          NaN NaN  \n",
       "1004          NaN NaN  \n",
       "1005          NaN NaN  \n",
       "1006          NaN NaN  \n",
       "1007          NaN NaN  \n",
       "1008          NaN NaN  \n",
       "1009          NaN NaN  \n",
       "1010          NaN NaN  \n",
       "1011          NaN NaN  \n",
       "1012          NaN NaN  \n",
       "1013          NaN NaN  \n",
       "1014          NaN NaN  \n",
       "1015          NaN NaN  \n",
       "1016          NaN NaN  \n",
       "1017          NaN NaN  \n",
       "1018          NaN NaN  \n",
       "1019          NaN NaN  \n",
       "\n",
       "[20 rows x 22 columns]"
      ]
     },
     "execution_count": 10,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "taxis.iloc[1000:1020]"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 11,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "<class 'pandas.core.frame.DataFrame'>\n",
      "RangeIndex: 1309155 entries, 0 to 1309154\n",
      "Data columns (total 22 columns):\n",
      " #   Column                 Non-Null Count    Dtype         \n",
      "---  ------                 --------------    -----         \n",
      " 0   VendorID               1309155 non-null  int64         \n",
      " 1   lpep_pickup_datetime   1309155 non-null  datetime64[ns]\n",
      " 2   Lpep_dropoff_datetime  1309155 non-null  datetime64[ns]\n",
      " 3   Store_and_fwd_flag     1309155 non-null  object        \n",
      " 4   RateCodeID             1309155 non-null  int64         \n",
      " 5   Pickup_longitude       1309155 non-null  float64       \n",
      " 6   Pickup_latitude        1309155 non-null  float64       \n",
      " 7   Dropoff_longitude      1309155 non-null  float64       \n",
      " 8   Dropoff_latitude       1309155 non-null  float64       \n",
      " 9   Passenger_count        1309155 non-null  int64         \n",
      " 10  Trip_distance          1309155 non-null  float64       \n",
      " 11  Fare_amount            1309155 non-null  float64       \n",
      " 12  Extra                  1309155 non-null  float64       \n",
      " 13  MTA_tax                1309155 non-null  float64       \n",
      " 14  Tip_amount             1309155 non-null  float64       \n",
      " 15  Tolls_amount           1309155 non-null  float64       \n",
      " 16  Ehail_fee              0 non-null        float64       \n",
      " 17  Total_amount           1309155 non-null  float64       \n",
      " 18  Payment_type           1309155 non-null  int64         \n",
      " 19  Trip_type              1225061 non-null  float64       \n",
      " 20  Unnamed: 20            0 non-null        float64       \n",
      " 21                         0 non-null        float64       \n",
      "dtypes: datetime64[ns](2), float64(15), int64(4), object(1)\n",
      "memory usage: 219.7+ MB\n"
     ]
    }
   ],
   "source": [
    "taxis.info()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## Mortgage Calculator"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "The logic underlying any computation is always clear, as each cell displays the code instead of just the computed values"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 12,
   "metadata": {},
   "outputs": [],
   "source": [
    "price = 1_000_000\n",
    "down = .2 * price\n",
    "ammount = price-down\n",
    "years = 30\n",
    "months = 12*years\n",
    "rate = .03/12"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 13,
   "metadata": {},
   "outputs": [],
   "source": [
    "payment = npf.pmt(rate, months, -ammount)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 14,
   "metadata": {},
   "outputs": [],
   "source": [
    "dates = pd.date_range('08/01/2020', periods=months, freq='M')+pd.Timedelta('1D')"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "From these few lines of code we can easily audit how the computation is performing and find any existing bugs"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 15,
   "metadata": {},
   "outputs": [],
   "source": [
    "rows = []\n",
    "\n",
    "balance = price - down \n",
    "extra_payment = 0\n",
    "\n",
    "for month in dates:\n",
    "    row = [month]\n",
    "    row.append(balance)\n",
    "    row.append(payment)\n",
    "    row.append(extra_payment)\n",
    "    row.append(payment + extra_payment)\n",
    "    \n",
    "    interest = balance*rate\n",
    "    principal = payment-interest\n",
    "    \n",
    "    row.append(principal+extra_payment)\n",
    "    row.append(interest)\n",
    "    \n",
    "    balance -= principal+extra_payment\n",
    "    \n",
    "    row.append(balance)\n",
    "    rows.append(row)\n",
    "    \n",
    "    if balance <= 0:\n",
    "        break"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "And convert the data into a compact DataFrame that can be used to perform further computations"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 16,
   "metadata": {},
   "outputs": [],
   "source": [
    "mortgage = pd.DataFrame(rows, columns=['Date', 'Beginning Balance', 'Scheduled Payment', 'Extra Payment', \n",
    "                                       'Total Payment', 'Principal', 'Interest', 'Ending Balance'])\n",
    "mortgage['Cummulative Interest'] = mortgage.Interest.cumsum()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 17,
   "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>Date</th>\n",
       "      <th>Beginning Balance</th>\n",
       "      <th>Scheduled Payment</th>\n",
       "      <th>Extra Payment</th>\n",
       "      <th>Total Payment</th>\n",
       "      <th>Principal</th>\n",
       "      <th>Interest</th>\n",
       "      <th>Ending Balance</th>\n",
       "      <th>Cummulative Interest</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>2020-09-01</td>\n",
       "      <td>800000.000000</td>\n",
       "      <td>3372.83227</td>\n",
       "      <td>0</td>\n",
       "      <td>3372.83227</td>\n",
       "      <td>1372.832270</td>\n",
       "      <td>2000.000000</td>\n",
       "      <td>7.986272e+05</td>\n",
       "      <td>2000.000000</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>2020-10-01</td>\n",
       "      <td>798627.167730</td>\n",
       "      <td>3372.83227</td>\n",
       "      <td>0</td>\n",
       "      <td>3372.83227</td>\n",
       "      <td>1376.264351</td>\n",
       "      <td>1996.567919</td>\n",
       "      <td>7.972509e+05</td>\n",
       "      <td>3996.567919</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>2020-11-01</td>\n",
       "      <td>797250.903380</td>\n",
       "      <td>3372.83227</td>\n",
       "      <td>0</td>\n",
       "      <td>3372.83227</td>\n",
       "      <td>1379.705011</td>\n",
       "      <td>1993.127258</td>\n",
       "      <td>7.958712e+05</td>\n",
       "      <td>5989.695178</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>2020-12-01</td>\n",
       "      <td>795871.198368</td>\n",
       "      <td>3372.83227</td>\n",
       "      <td>0</td>\n",
       "      <td>3372.83227</td>\n",
       "      <td>1383.154274</td>\n",
       "      <td>1989.677996</td>\n",
       "      <td>7.944880e+05</td>\n",
       "      <td>7979.373174</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>2021-01-01</td>\n",
       "      <td>794488.044094</td>\n",
       "      <td>3372.83227</td>\n",
       "      <td>0</td>\n",
       "      <td>3372.83227</td>\n",
       "      <td>1386.612160</td>\n",
       "      <td>1986.220110</td>\n",
       "      <td>7.931014e+05</td>\n",
       "      <td>9965.593284</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>...</th>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>355</th>\n",
       "      <td>2050-04-01</td>\n",
       "      <td>16738.414274</td>\n",
       "      <td>3372.83227</td>\n",
       "      <td>0</td>\n",
       "      <td>3372.83227</td>\n",
       "      <td>3330.986234</td>\n",
       "      <td>41.846036</td>\n",
       "      <td>1.340743e+04</td>\n",
       "      <td>414135.716101</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>356</th>\n",
       "      <td>2050-05-01</td>\n",
       "      <td>13407.428039</td>\n",
       "      <td>3372.83227</td>\n",
       "      <td>0</td>\n",
       "      <td>3372.83227</td>\n",
       "      <td>3339.313700</td>\n",
       "      <td>33.518570</td>\n",
       "      <td>1.006811e+04</td>\n",
       "      <td>414169.234671</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>357</th>\n",
       "      <td>2050-06-01</td>\n",
       "      <td>10068.114340</td>\n",
       "      <td>3372.83227</td>\n",
       "      <td>0</td>\n",
       "      <td>3372.83227</td>\n",
       "      <td>3347.661984</td>\n",
       "      <td>25.170286</td>\n",
       "      <td>6.720452e+03</td>\n",
       "      <td>414194.404957</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>358</th>\n",
       "      <td>2050-07-01</td>\n",
       "      <td>6720.452356</td>\n",
       "      <td>3372.83227</td>\n",
       "      <td>0</td>\n",
       "      <td>3372.83227</td>\n",
       "      <td>3356.031139</td>\n",
       "      <td>16.801131</td>\n",
       "      <td>3.364421e+03</td>\n",
       "      <td>414211.206088</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>359</th>\n",
       "      <td>2050-08-01</td>\n",
       "      <td>3364.421217</td>\n",
       "      <td>3372.83227</td>\n",
       "      <td>0</td>\n",
       "      <td>3372.83227</td>\n",
       "      <td>3364.421217</td>\n",
       "      <td>8.411053</td>\n",
       "      <td>-2.686556e-08</td>\n",
       "      <td>414219.617141</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "<p>360 rows × 9 columns</p>\n",
       "</div>"
      ],
      "text/plain": [
       "          Date  Beginning Balance  Scheduled Payment  Extra Payment  \\\n",
       "0   2020-09-01      800000.000000         3372.83227              0   \n",
       "1   2020-10-01      798627.167730         3372.83227              0   \n",
       "2   2020-11-01      797250.903380         3372.83227              0   \n",
       "3   2020-12-01      795871.198368         3372.83227              0   \n",
       "4   2021-01-01      794488.044094         3372.83227              0   \n",
       "..         ...                ...                ...            ...   \n",
       "355 2050-04-01       16738.414274         3372.83227              0   \n",
       "356 2050-05-01       13407.428039         3372.83227              0   \n",
       "357 2050-06-01       10068.114340         3372.83227              0   \n",
       "358 2050-07-01        6720.452356         3372.83227              0   \n",
       "359 2050-08-01        3364.421217         3372.83227              0   \n",
       "\n",
       "     Total Payment    Principal     Interest  Ending Balance  \\\n",
       "0       3372.83227  1372.832270  2000.000000    7.986272e+05   \n",
       "1       3372.83227  1376.264351  1996.567919    7.972509e+05   \n",
       "2       3372.83227  1379.705011  1993.127258    7.958712e+05   \n",
       "3       3372.83227  1383.154274  1989.677996    7.944880e+05   \n",
       "4       3372.83227  1386.612160  1986.220110    7.931014e+05   \n",
       "..             ...          ...          ...             ...   \n",
       "355     3372.83227  3330.986234    41.846036    1.340743e+04   \n",
       "356     3372.83227  3339.313700    33.518570    1.006811e+04   \n",
       "357     3372.83227  3347.661984    25.170286    6.720452e+03   \n",
       "358     3372.83227  3356.031139    16.801131    3.364421e+03   \n",
       "359     3372.83227  3364.421217     8.411053   -2.686556e-08   \n",
       "\n",
       "     Cummulative Interest  \n",
       "0             2000.000000  \n",
       "1             3996.567919  \n",
       "2             5989.695178  \n",
       "3             7979.373174  \n",
       "4             9965.593284  \n",
       "..                    ...  \n",
       "355         414135.716101  \n",
       "356         414169.234671  \n",
       "357         414194.404957  \n",
       "358         414211.206088  \n",
       "359         414219.617141  \n",
       "\n",
       "[360 rows x 9 columns]"
      ]
     },
     "execution_count": 17,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "mortgage"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## Non-standardized data"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 18,
   "metadata": {},
   "outputs": [],
   "source": [
    "movies = pd.read_excel('data/movies.xlsx')"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 19,
   "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>Title</th>\n",
       "      <th>Year</th>\n",
       "      <th>Genres</th>\n",
       "      <th>Language</th>\n",
       "      <th>Country</th>\n",
       "      <th>Content Rating</th>\n",
       "      <th>Duration</th>\n",
       "      <th>Aspect Ratio</th>\n",
       "      <th>Budget</th>\n",
       "      <th>Gross Earnings</th>\n",
       "      <th>...</th>\n",
       "      <th>Facebook Likes - Actor 1</th>\n",
       "      <th>Facebook Likes - Actor 2</th>\n",
       "      <th>Facebook Likes - Actor 3</th>\n",
       "      <th>Facebook Likes - cast Total</th>\n",
       "      <th>Facebook likes - Movie</th>\n",
       "      <th>Facenumber in posters</th>\n",
       "      <th>User Votes</th>\n",
       "      <th>Reviews by Users</th>\n",
       "      <th>Reviews by Crtiics</th>\n",
       "      <th>IMDB Score</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>Intolerance: Love's Struggle Throughout the Ages</td>\n",
       "      <td>1916</td>\n",
       "      <td>Drama|History|War</td>\n",
       "      <td>NaN</td>\n",
       "      <td>USA</td>\n",
       "      <td>Not Rated</td>\n",
       "      <td>123</td>\n",
       "      <td>1.33</td>\n",
       "      <td>385907.0</td>\n",
       "      <td>NaN</td>\n",
       "      <td>...</td>\n",
       "      <td>436</td>\n",
       "      <td>22</td>\n",
       "      <td>9.0</td>\n",
       "      <td>481</td>\n",
       "      <td>691</td>\n",
       "      <td>1</td>\n",
       "      <td>10718</td>\n",
       "      <td>88</td>\n",
       "      <td>69.0</td>\n",
       "      <td>8.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>Over the Hill to the Poorhouse</td>\n",
       "      <td>1920</td>\n",
       "      <td>Crime|Drama</td>\n",
       "      <td>NaN</td>\n",
       "      <td>USA</td>\n",
       "      <td>NaN</td>\n",
       "      <td>110</td>\n",
       "      <td>1.33</td>\n",
       "      <td>100000.0</td>\n",
       "      <td>3000000.0</td>\n",
       "      <td>...</td>\n",
       "      <td>2</td>\n",
       "      <td>2</td>\n",
       "      <td>0.0</td>\n",
       "      <td>4</td>\n",
       "      <td>0</td>\n",
       "      <td>1</td>\n",
       "      <td>5</td>\n",
       "      <td>1</td>\n",
       "      <td>1.0</td>\n",
       "      <td>4.8</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>The Big Parade</td>\n",
       "      <td>1925</td>\n",
       "      <td>Drama|Romance|War</td>\n",
       "      <td>NaN</td>\n",
       "      <td>USA</td>\n",
       "      <td>Not Rated</td>\n",
       "      <td>151</td>\n",
       "      <td>1.33</td>\n",
       "      <td>245000.0</td>\n",
       "      <td>NaN</td>\n",
       "      <td>...</td>\n",
       "      <td>81</td>\n",
       "      <td>12</td>\n",
       "      <td>6.0</td>\n",
       "      <td>108</td>\n",
       "      <td>226</td>\n",
       "      <td>0</td>\n",
       "      <td>4849</td>\n",
       "      <td>45</td>\n",
       "      <td>48.0</td>\n",
       "      <td>8.3</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>Metropolis</td>\n",
       "      <td>1927</td>\n",
       "      <td>Drama|Sci-Fi</td>\n",
       "      <td>German</td>\n",
       "      <td>Germany</td>\n",
       "      <td>Not Rated</td>\n",
       "      <td>145</td>\n",
       "      <td>1.33</td>\n",
       "      <td>6000000.0</td>\n",
       "      <td>26435.0</td>\n",
       "      <td>...</td>\n",
       "      <td>136</td>\n",
       "      <td>23</td>\n",
       "      <td>18.0</td>\n",
       "      <td>203</td>\n",
       "      <td>12000</td>\n",
       "      <td>1</td>\n",
       "      <td>111841</td>\n",
       "      <td>413</td>\n",
       "      <td>260.0</td>\n",
       "      <td>8.3</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>Pandora's Box</td>\n",
       "      <td>1929</td>\n",
       "      <td>Crime|Drama|Romance</td>\n",
       "      <td>German</td>\n",
       "      <td>Germany</td>\n",
       "      <td>Not Rated</td>\n",
       "      <td>110</td>\n",
       "      <td>1.33</td>\n",
       "      <td>NaN</td>\n",
       "      <td>9950.0</td>\n",
       "      <td>...</td>\n",
       "      <td>426</td>\n",
       "      <td>20</td>\n",
       "      <td>3.0</td>\n",
       "      <td>455</td>\n",
       "      <td>926</td>\n",
       "      <td>1</td>\n",
       "      <td>7431</td>\n",
       "      <td>84</td>\n",
       "      <td>71.0</td>\n",
       "      <td>8.0</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "<p>5 rows × 25 columns</p>\n",
       "</div>"
      ],
      "text/plain": [
       "                                               Title  Year  \\\n",
       "0  Intolerance: Love's Struggle Throughout the Ages   1916   \n",
       "1                    Over the Hill to the Poorhouse   1920   \n",
       "2                                    The Big Parade   1925   \n",
       "3                                        Metropolis   1927   \n",
       "4                                     Pandora's Box   1929   \n",
       "\n",
       "                Genres Language  Country Content Rating  Duration  \\\n",
       "0    Drama|History|War      NaN      USA      Not Rated       123   \n",
       "1          Crime|Drama      NaN      USA            NaN       110   \n",
       "2    Drama|Romance|War      NaN      USA      Not Rated       151   \n",
       "3         Drama|Sci-Fi   German  Germany      Not Rated       145   \n",
       "4  Crime|Drama|Romance   German  Germany      Not Rated       110   \n",
       "\n",
       "   Aspect Ratio     Budget  Gross Earnings  ... Facebook Likes - Actor 1  \\\n",
       "0          1.33   385907.0             NaN  ...                      436   \n",
       "1          1.33   100000.0       3000000.0  ...                        2   \n",
       "2          1.33   245000.0             NaN  ...                       81   \n",
       "3          1.33  6000000.0         26435.0  ...                      136   \n",
       "4          1.33        NaN          9950.0  ...                      426   \n",
       "\n",
       "  Facebook Likes - Actor 2 Facebook Likes - Actor 3  \\\n",
       "0                       22                      9.0   \n",
       "1                        2                      0.0   \n",
       "2                       12                      6.0   \n",
       "3                       23                     18.0   \n",
       "4                       20                      3.0   \n",
       "\n",
       "  Facebook Likes - cast Total  Facebook likes - Movie  Facenumber in posters  \\\n",
       "0                         481                     691                      1   \n",
       "1                           4                       0                      1   \n",
       "2                         108                     226                      0   \n",
       "3                         203                   12000                      1   \n",
       "4                         455                     926                      1   \n",
       "\n",
       "   User Votes  Reviews by Users  Reviews by Crtiics  IMDB Score  \n",
       "0       10718                88                69.0         8.0  \n",
       "1           5                 1                 1.0         4.8  \n",
       "2        4849                45                48.0         8.3  \n",
       "3      111841               413               260.0         8.3  \n",
       "4        7431                84                71.0         8.0  \n",
       "\n",
       "[5 rows x 25 columns]"
      ]
     },
     "execution_count": 19,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "movies.head()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Unexpected spaces in the data"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 20,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "'It Happened One Night\\xa0'"
      ]
     },
     "execution_count": 20,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "movies['Title'].iloc[10]"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "That can easily be cleaned"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 21,
   "metadata": {},
   "outputs": [],
   "source": [
    "movies['Title'] = movies['Title'].transform(lambda x: x.strip())"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 22,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "'It Happened One Night'"
      ]
     },
     "execution_count": 22,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "movies['Title'].iloc[10]"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "<div style=\"width: 100%; overflow: hidden;\">\n",
    "     <img src=\"data/D4Sci_logo_full.png\" alt=\"Data For Science, Inc\" align=\"center\" border=\"0\" width=300px> \n",
    "</div>"
   ]
  }
 ],
 "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"
  },
  "toc": {
   "base_numbering": 1,
   "nav_menu": {},
   "number_sections": true,
   "sideBar": true,
   "skip_h1_title": true,
   "title_cell": "Table of Contents",
   "title_sidebar": "Contents",
   "toc_cell": false,
   "toc_position": {},
   "toc_section_display": true,
   "toc_window_display": false
  },
  "varInspector": {
   "cols": {
    "lenName": 16,
    "lenType": 16,
    "lenVar": 40
   },
   "kernels_config": {
    "python": {
     "delete_cmd_postfix": "",
     "delete_cmd_prefix": "del ",
     "library": "var_list.py",
     "varRefreshCmd": "print(var_dic_list())"
    },
    "r": {
     "delete_cmd_postfix": ") ",
     "delete_cmd_prefix": "rm(",
     "library": "var_list.r",
     "varRefreshCmd": "cat(var_dic_list()) "
    }
   },
   "types_to_exclude": [
    "module",
    "function",
    "builtin_function_or_method",
    "instance",
    "_Feature"
   ],
   "window_display": false
  }
 },
 "nbformat": 4,
 "nbformat_minor": 2
}