2. Pandas DataFrames.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>Pandas DataFrames</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": [
    "import matplotlib\n",
    "import matplotlib.pyplot as plt\n",
    "\n",
    "import pandas as pd\n",
    "import numpy as np\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",
      "matplotlib: 3.3.2\n",
      "numpy     : 1.19.2\n",
      "json      : 2.0.9\n",
      "watermark : 2.1.0\n",
      "\n"
     ]
    }
   ],
   "source": [
    "%watermark -n -v -m -g -iv"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Set the default figure style"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 3,
   "metadata": {},
   "outputs": [],
   "source": [
    "plt.style.use('./d4sci.mplstyle')"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## DataFrames and Series"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Series and DataFrames can be thought of as dictionaries associating keys to lists of values"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 4,
   "metadata": {},
   "outputs": [],
   "source": [
    "data = {  \"id\": [23,    42,      12,     86], \n",
    "        \"Name\": [\"Bob\", \"Karen\", \"Kate\", \"Bill\"]}"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 5,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "{'id': [23, 42, 12, 86], 'Name': ['Bob', 'Karen', 'Kate', 'Bill']}"
      ]
     },
     "execution_count": 5,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "data"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "A Series corresponds to just a sigle list of values"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 6,
   "metadata": {},
   "outputs": [],
   "source": [
    "series = pd.Series(data[\"id\"])"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 7,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "0    23\n",
       "1    42\n",
       "2    12\n",
       "3    86\n",
       "dtype: int64"
      ]
     },
     "execution_count": 7,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "series"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "While a DataFrame can have multiple"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 8,
   "metadata": {},
   "outputs": [],
   "source": [
    " df = pd.DataFrame(data)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 9,
   "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>Name</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>23</td>\n",
       "      <td>Bob</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>42</td>\n",
       "      <td>Karen</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>12</td>\n",
       "      <td>Kate</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>86</td>\n",
       "      <td>Bill</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "   id   Name\n",
       "0  23    Bob\n",
       "1  42  Karen\n",
       "2  12   Kate\n",
       "3  86   Bill"
      ]
     },
     "execution_count": 9,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Another way of looking at it, is that DataFrames are essentially groups of individual Series. Each Series can have it's own datatype **dtype**"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 10,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "id       int64\n",
       "Name    object\n",
       "dtype: object"
      ]
     },
     "execution_count": 10,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df.dtypes"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "We can get general information about how the DataFrame is being stored by calling __info()__"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 11,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "<class 'pandas.core.frame.DataFrame'>\n",
      "RangeIndex: 4 entries, 0 to 3\n",
      "Data columns (total 2 columns):\n",
      " #   Column  Non-Null Count  Dtype \n",
      "---  ------  --------------  ----- \n",
      " 0   id      4 non-null      int64 \n",
      " 1   Name    4 non-null      object\n",
      "dtypes: int64(1), object(1)\n",
      "memory usage: 192.0+ bytes\n"
     ]
    }
   ],
   "source": [
    "df.info()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Subsetting a DataFrame by column name we retrieve the underlying Series"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 12,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "pandas.core.series.Series"
      ]
     },
     "execution_count": 12,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "type(df['id'])"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Both columns and index values have types and possibly names"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 13,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "Index(['id', 'Name'], dtype='object')"
      ]
     },
     "execution_count": 13,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df.columns"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 14,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "RangeIndex(start=0, stop=4, step=1)"
      ]
     },
     "execution_count": 14,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df.index"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "And we can query the shape and number of dimensions of the DataFrame easily"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 15,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "(4, 2)"
      ]
     },
     "execution_count": 15,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df.shape"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 16,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "2"
      ]
     },
     "execution_count": 16,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df.ndim"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "And relabel both index and column values"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 17,
   "metadata": {},
   "outputs": [],
   "source": [
    "df.index = [\"row\" + str(i) for i in range(4)]\n",
    "df.columns = ['ID', 'First Name']"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 18,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "ID               42\n",
       "First Name    Karen\n",
       "Name: row1, dtype: object"
      ]
     },
     "execution_count": 18,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df.loc['row1']"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 19,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "ID               42\n",
       "First Name    Karen\n",
       "Name: row1, dtype: object"
      ]
     },
     "execution_count": 19,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df.iloc[1]"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## Importing and exporting data"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### Read csv files"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "File can be zipped"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 20,
   "metadata": {},
   "outputs": [],
   "source": [
    "green = pd.read_csv('data/green_tripdata_2014-04.csv.gz', \n",
    "        parse_dates=['lpep_pickup_datetime', 'Lpep_dropoff_datetime'], \n",
    "                    nrows=2000, index_col='VendorID', \n",
    "        dtype={'RateCodeID':'str', 'Trip_type': 'str'}\n",
    "                   )"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "We read only 2,000 rows as expected"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 21,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "(2000, 21)"
      ]
     },
     "execution_count": 21,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "green.shape"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "And used the right dtypes for each column"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 22,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "<class 'pandas.core.frame.DataFrame'>\n",
      "Int64Index: 2000 entries, 2 to 2\n",
      "Data columns (total 21 columns):\n",
      " #   Column                 Non-Null Count  Dtype         \n",
      "---  ------                 --------------  -----         \n",
      " 0   lpep_pickup_datetime   2000 non-null   datetime64[ns]\n",
      " 1   Lpep_dropoff_datetime  2000 non-null   datetime64[ns]\n",
      " 2   Store_and_fwd_flag     2000 non-null   object        \n",
      " 3   RateCodeID             2000 non-null   object        \n",
      " 4   Pickup_longitude       2000 non-null   float64       \n",
      " 5   Pickup_latitude        2000 non-null   float64       \n",
      " 6   Dropoff_longitude      2000 non-null   float64       \n",
      " 7   Dropoff_latitude       2000 non-null   float64       \n",
      " 8   Passenger_count        2000 non-null   int64         \n",
      " 9   Trip_distance          2000 non-null   float64       \n",
      " 10  Fare_amount            2000 non-null   float64       \n",
      " 11  Extra                  2000 non-null   float64       \n",
      " 12  MTA_tax                2000 non-null   float64       \n",
      " 13  Tip_amount             2000 non-null   float64       \n",
      " 14  Tolls_amount           2000 non-null   float64       \n",
      " 15  Ehail_fee              0 non-null      float64       \n",
      " 16  Total_amount           2000 non-null   float64       \n",
      " 17  Payment_type           2000 non-null   int64         \n",
      " 18  Trip_type              1590 non-null   object        \n",
      " 19  Unnamed: 20            0 non-null      float64       \n",
      " 20                         0 non-null      float64       \n",
      "dtypes: datetime64[ns](2), float64(14), int64(2), object(3)\n",
      "memory usage: 343.8+ KB\n"
     ]
    }
   ],
   "source": [
    "green.info()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### Read excel spreadsheets"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "By default, __read_excel__ reads the first spreadsheet"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 23,
   "metadata": {},
   "outputs": [],
   "source": [
    "movies = pd.read_excel('data/movies.xlsx', index_col='Title', engine='openpyxl')"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 24,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>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>Director</th>\n",
       "      <th>...</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",
       "      <th>Unnamed: 25</th>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Title</th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>Intolerance: Love's Struggle Throughout the Ages</th>\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>D.W. Griffith</td>\n",
       "      <td>...</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",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Over the Hill to the Poorhouse</th>\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>Harry F. Millarde</td>\n",
       "      <td>...</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",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>The Big Parade</th>\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>King Vidor</td>\n",
       "      <td>...</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",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Metropolis</th>\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>Fritz Lang</td>\n",
       "      <td>...</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",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Pandora's Box</th>\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>Georg Wilhelm Pabst</td>\n",
       "      <td>...</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",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>The Broadway Melody</th>\n",
       "      <td>1929</td>\n",
       "      <td>Musical|Romance</td>\n",
       "      <td>English</td>\n",
       "      <td>USA</td>\n",
       "      <td>Passed</td>\n",
       "      <td>100</td>\n",
       "      <td>1.37</td>\n",
       "      <td>379000.0</td>\n",
       "      <td>2808000.0</td>\n",
       "      <td>Harry Beaumont</td>\n",
       "      <td>...</td>\n",
       "      <td>28</td>\n",
       "      <td>4.0</td>\n",
       "      <td>109</td>\n",
       "      <td>167</td>\n",
       "      <td>8</td>\n",
       "      <td>4546</td>\n",
       "      <td>71</td>\n",
       "      <td>36.0</td>\n",
       "      <td>6.3</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Hell's Angels</th>\n",
       "      <td>1930</td>\n",
       "      <td>Drama|War</td>\n",
       "      <td>English</td>\n",
       "      <td>USA</td>\n",
       "      <td>Passed</td>\n",
       "      <td>96</td>\n",
       "      <td>1.20</td>\n",
       "      <td>3950000.0</td>\n",
       "      <td>NaN</td>\n",
       "      <td>Howard Hughes</td>\n",
       "      <td>...</td>\n",
       "      <td>12</td>\n",
       "      <td>4.0</td>\n",
       "      <td>457</td>\n",
       "      <td>279</td>\n",
       "      <td>1</td>\n",
       "      <td>3753</td>\n",
       "      <td>53</td>\n",
       "      <td>35.0</td>\n",
       "      <td>7.8</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>A Farewell to Arms</th>\n",
       "      <td>1932</td>\n",
       "      <td>Drama|Romance|War</td>\n",
       "      <td>English</td>\n",
       "      <td>USA</td>\n",
       "      <td>Unrated</td>\n",
       "      <td>79</td>\n",
       "      <td>1.37</td>\n",
       "      <td>800000.0</td>\n",
       "      <td>NaN</td>\n",
       "      <td>Frank Borzage</td>\n",
       "      <td>...</td>\n",
       "      <td>164</td>\n",
       "      <td>99.0</td>\n",
       "      <td>1284</td>\n",
       "      <td>213</td>\n",
       "      <td>1</td>\n",
       "      <td>3519</td>\n",
       "      <td>46</td>\n",
       "      <td>42.0</td>\n",
       "      <td>6.6</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>42nd Street</th>\n",
       "      <td>1933</td>\n",
       "      <td>Comedy|Musical|Romance</td>\n",
       "      <td>English</td>\n",
       "      <td>USA</td>\n",
       "      <td>Unrated</td>\n",
       "      <td>89</td>\n",
       "      <td>1.37</td>\n",
       "      <td>439000.0</td>\n",
       "      <td>2300000.0</td>\n",
       "      <td>Lloyd Bacon</td>\n",
       "      <td>...</td>\n",
       "      <td>105</td>\n",
       "      <td>45.0</td>\n",
       "      <td>995</td>\n",
       "      <td>439</td>\n",
       "      <td>2</td>\n",
       "      <td>7921</td>\n",
       "      <td>97</td>\n",
       "      <td>65.0</td>\n",
       "      <td>7.7</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>She Done Him Wrong</th>\n",
       "      <td>1933</td>\n",
       "      <td>Comedy|Drama|History|Musical|Romance</td>\n",
       "      <td>English</td>\n",
       "      <td>USA</td>\n",
       "      <td>Approved</td>\n",
       "      <td>66</td>\n",
       "      <td>1.37</td>\n",
       "      <td>200000.0</td>\n",
       "      <td>NaN</td>\n",
       "      <td>Lowell Sherman</td>\n",
       "      <td>...</td>\n",
       "      <td>85</td>\n",
       "      <td>28.0</td>\n",
       "      <td>583</td>\n",
       "      <td>328</td>\n",
       "      <td>1</td>\n",
       "      <td>4152</td>\n",
       "      <td>59</td>\n",
       "      <td>35.0</td>\n",
       "      <td>6.5</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "<p>10 rows × 25 columns</p>\n",
       "</div>"
      ],
      "text/plain": [
       "                                                   Year  \\\n",
       "Title                                                     \n",
       "Intolerance: Love's Struggle Throughout the Ages   1916   \n",
       "Over the Hill to the Poorhouse                     1920   \n",
       "The Big Parade                                     1925   \n",
       "Metropolis                                         1927   \n",
       "Pandora's Box                                      1929   \n",
       "The Broadway Melody                                1929   \n",
       "Hell's Angels                                      1930   \n",
       "A Farewell to Arms                                 1932   \n",
       "42nd Street                                        1933   \n",
       "She Done Him Wrong                                 1933   \n",
       "\n",
       "                                                                                 Genres  \\\n",
       "Title                                                                                     \n",
       "Intolerance: Love's Struggle Throughout the Ages                      Drama|History|War   \n",
       "Over the Hill to the Poorhouse                                              Crime|Drama   \n",
       "The Big Parade                                                        Drama|Romance|War   \n",
       "Metropolis                                                                 Drama|Sci-Fi   \n",
       "Pandora's Box                                                       Crime|Drama|Romance   \n",
       "The Broadway Melody                                                     Musical|Romance   \n",
       "Hell's Angels                                                                 Drama|War   \n",
       "A Farewell to Arms                                                    Drama|Romance|War   \n",
       "42nd Street                                                      Comedy|Musical|Romance   \n",
       "She Done Him Wrong                                 Comedy|Drama|History|Musical|Romance   \n",
       "\n",
       "                                                  Language  Country  \\\n",
       "Title                                                                 \n",
       "Intolerance: Love's Struggle Throughout the Ages       NaN      USA   \n",
       "Over the Hill to the Poorhouse                         NaN      USA   \n",
       "The Big Parade                                         NaN      USA   \n",
       "Metropolis                                          German  Germany   \n",
       "Pandora's Box                                       German  Germany   \n",
       "The Broadway Melody                                English      USA   \n",
       "Hell's Angels                                      English      USA   \n",
       "A Farewell to Arms                                 English      USA   \n",
       "42nd Street                                        English      USA   \n",
       "She Done Him Wrong                                 English      USA   \n",
       "\n",
       "                                                  Content Rating  Duration  \\\n",
       "Title                                                                        \n",
       "Intolerance: Love's Struggle Throughout the Ages       Not Rated       123   \n",
       "Over the Hill to the Poorhouse                               NaN       110   \n",
       "The Big Parade                                         Not Rated       151   \n",
       "Metropolis                                             Not Rated       145   \n",
       "Pandora's Box                                          Not Rated       110   \n",
       "The Broadway Melody                                       Passed       100   \n",
       "Hell's Angels                                             Passed        96   \n",
       "A Farewell to Arms                                       Unrated        79   \n",
       "42nd Street                                              Unrated        89   \n",
       "She Done Him Wrong                                      Approved        66   \n",
       "\n",
       "                                                   Aspect Ratio     Budget  \\\n",
       "Title                                                                        \n",
       "Intolerance: Love's Struggle Throughout the Ages           1.33   385907.0   \n",
       "Over the Hill to the Poorhouse                             1.33   100000.0   \n",
       "The Big Parade                                             1.33   245000.0   \n",
       "Metropolis                                                 1.33  6000000.0   \n",
       "Pandora's Box                                              1.33        NaN   \n",
       "The Broadway Melody                                        1.37   379000.0   \n",
       "Hell's Angels                                              1.20  3950000.0   \n",
       "A Farewell to Arms                                         1.37   800000.0   \n",
       "42nd Street                                                1.37   439000.0   \n",
       "She Done Him Wrong                                         1.37   200000.0   \n",
       "\n",
       "                                                   Gross Earnings  \\\n",
       "Title                                                               \n",
       "Intolerance: Love's Struggle Throughout the Ages              NaN   \n",
       "Over the Hill to the Poorhouse                          3000000.0   \n",
       "The Big Parade                                                NaN   \n",
       "Metropolis                                                26435.0   \n",
       "Pandora's Box                                              9950.0   \n",
       "The Broadway Melody                                     2808000.0   \n",
       "Hell's Angels                                                 NaN   \n",
       "A Farewell to Arms                                            NaN   \n",
       "42nd Street                                             2300000.0   \n",
       "She Done Him Wrong                                            NaN   \n",
       "\n",
       "                                                              Director  ...  \\\n",
       "Title                                                                   ...   \n",
       "Intolerance: Love's Struggle Throughout the Ages         D.W. Griffith  ...   \n",
       "Over the Hill to the Poorhouse                       Harry F. Millarde  ...   \n",
       "The Big Parade                                              King Vidor  ...   \n",
       "Metropolis                                                  Fritz Lang  ...   \n",
       "Pandora's Box                                      Georg Wilhelm Pabst  ...   \n",
       "The Broadway Melody                                     Harry Beaumont  ...   \n",
       "Hell's Angels                                            Howard Hughes  ...   \n",
       "A Farewell to Arms                                       Frank Borzage  ...   \n",
       "42nd Street                                                Lloyd Bacon  ...   \n",
       "She Done Him Wrong                                      Lowell Sherman  ...   \n",
       "\n",
       "                                                  Facebook Likes - Actor 2  \\\n",
       "Title                                                                        \n",
       "Intolerance: Love's Struggle Throughout the Ages                        22   \n",
       "Over the Hill to the Poorhouse                                           2   \n",
       "The Big Parade                                                          12   \n",
       "Metropolis                                                              23   \n",
       "Pandora's Box                                                           20   \n",
       "The Broadway Melody                                                     28   \n",
       "Hell's Angels                                                           12   \n",
       "A Farewell to Arms                                                     164   \n",
       "42nd Street                                                            105   \n",
       "She Done Him Wrong                                                      85   \n",
       "\n",
       "                                                  Facebook Likes - Actor 3  \\\n",
       "Title                                                                        \n",
       "Intolerance: Love's Struggle Throughout the Ages                       9.0   \n",
       "Over the Hill to the Poorhouse                                         0.0   \n",
       "The Big Parade                                                         6.0   \n",
       "Metropolis                                                            18.0   \n",
       "Pandora's Box                                                          3.0   \n",
       "The Broadway Melody                                                    4.0   \n",
       "Hell's Angels                                                          4.0   \n",
       "A Farewell to Arms                                                    99.0   \n",
       "42nd Street                                                           45.0   \n",
       "She Done Him Wrong                                                    28.0   \n",
       "\n",
       "                                                  Facebook Likes - cast Total  \\\n",
       "Title                                                                           \n",
       "Intolerance: Love's Struggle Throughout the Ages                          481   \n",
       "Over the Hill to the Poorhouse                                              4   \n",
       "The Big Parade                                                            108   \n",
       "Metropolis                                                                203   \n",
       "Pandora's Box                                                             455   \n",
       "The Broadway Melody                                                       109   \n",
       "Hell's Angels                                                             457   \n",
       "A Farewell to Arms                                                       1284   \n",
       "42nd Street                                                               995   \n",
       "She Done Him Wrong                                                        583   \n",
       "\n",
       "                                                   Facebook likes - Movie  \\\n",
       "Title                                                                       \n",
       "Intolerance: Love's Struggle Throughout the Ages                      691   \n",
       "Over the Hill to the Poorhouse                                          0   \n",
       "The Big Parade                                                        226   \n",
       "Metropolis                                                          12000   \n",
       "Pandora's Box                                                         926   \n",
       "The Broadway Melody                                                   167   \n",
       "Hell's Angels                                                         279   \n",
       "A Farewell to Arms                                                    213   \n",
       "42nd Street                                                           439   \n",
       "She Done Him Wrong                                                    328   \n",
       "\n",
       "                                                   Facenumber in posters  \\\n",
       "Title                                                                      \n",
       "Intolerance: Love's Struggle Throughout the Ages                       1   \n",
       "Over the Hill to the Poorhouse                                         1   \n",
       "The Big Parade                                                         0   \n",
       "Metropolis                                                             1   \n",
       "Pandora's Box                                                          1   \n",
       "The Broadway Melody                                                    8   \n",
       "Hell's Angels                                                          1   \n",
       "A Farewell to Arms                                                     1   \n",
       "42nd Street                                                            2   \n",
       "She Done Him Wrong                                                     1   \n",
       "\n",
       "                                                   User Votes  \\\n",
       "Title                                                           \n",
       "Intolerance: Love's Struggle Throughout the Ages        10718   \n",
       "Over the Hill to the Poorhouse                              5   \n",
       "The Big Parade                                           4849   \n",
       "Metropolis                                             111841   \n",
       "Pandora's Box                                            7431   \n",
       "The Broadway Melody                                      4546   \n",
       "Hell's Angels                                            3753   \n",
       "A Farewell to Arms                                       3519   \n",
       "42nd Street                                              7921   \n",
       "She Done Him Wrong                                       4152   \n",
       "\n",
       "                                                   Reviews by Users  \\\n",
       "Title                                                                 \n",
       "Intolerance: Love's Struggle Throughout the Ages                 88   \n",
       "Over the Hill to the Poorhouse                                    1   \n",
       "The Big Parade                                                   45   \n",
       "Metropolis                                                      413   \n",
       "Pandora's Box                                                    84   \n",
       "The Broadway Melody                                              71   \n",
       "Hell's Angels                                                    53   \n",
       "A Farewell to Arms                                               46   \n",
       "42nd Street                                                      97   \n",
       "She Done Him Wrong                                               59   \n",
       "\n",
       "                                                   Reviews by Crtiics  \\\n",
       "Title                                                                   \n",
       "Intolerance: Love's Struggle Throughout the Ages                 69.0   \n",
       "Over the Hill to the Poorhouse                                    1.0   \n",
       "The Big Parade                                                   48.0   \n",
       "Metropolis                                                      260.0   \n",
       "Pandora's Box                                                    71.0   \n",
       "The Broadway Melody                                              36.0   \n",
       "Hell's Angels                                                    35.0   \n",
       "A Farewell to Arms                                               42.0   \n",
       "42nd Street                                                      65.0   \n",
       "She Done Him Wrong                                               35.0   \n",
       "\n",
       "                                                   IMDB Score  Unnamed: 25  \n",
       "Title                                                                       \n",
       "Intolerance: Love's Struggle Throughout the Ages          8.0          NaN  \n",
       "Over the Hill to the Poorhouse                            4.8          NaN  \n",
       "The Big Parade                                            8.3          NaN  \n",
       "Metropolis                                                8.3          NaN  \n",
       "Pandora's Box                                             8.0          NaN  \n",
       "The Broadway Melody                                       6.3          NaN  \n",
       "Hell's Angels                                             7.8          NaN  \n",
       "A Farewell to Arms                                        6.6          NaN  \n",
       "42nd Street                                               7.7          NaN  \n",
       "She Done Him Wrong                                        6.5          NaN  \n",
       "\n",
       "[10 rows x 25 columns]"
      ]
     },
     "execution_count": 24,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "movies.head(10)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "But it preserves no information about the sheet name. An alternative is to use"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 25,
   "metadata": {},
   "outputs": [],
   "source": [
    "dfs = pd.read_excel('data/movies.xlsx', sheet_name=None, engine='openpyxl')"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "To retrieve a dictionary with all the available sheets keyed by name"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 26,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "4"
      ]
     },
     "execution_count": 26,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "len(dfs)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 27,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "dict_keys(['1900s', '2000s', '2010s', '3000s'])"
      ]
     },
     "execution_count": 27,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "dfs.keys()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 28,
   "metadata": {},
   "outputs": [],
   "source": [
    "movies_1900 = dfs['1900s']"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 29,
   "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 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",
       "      <th>Unnamed: 25</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>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",
       "      <td>NaN</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>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",
       "      <td>NaN</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>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",
       "      <td>NaN</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>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",
       "      <td>NaN</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>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",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "<p>5 rows × 26 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 2  \\\n",
       "0          1.33   385907.0             NaN  ...                       22   \n",
       "1          1.33   100000.0       3000000.0  ...                        2   \n",
       "2          1.33   245000.0             NaN  ...                       12   \n",
       "3          1.33  6000000.0         26435.0  ...                       23   \n",
       "4          1.33        NaN          9950.0  ...                       20   \n",
       "\n",
       "  Facebook Likes - Actor 3 Facebook Likes - cast Total Facebook likes - Movie  \\\n",
       "0                      9.0                         481                    691   \n",
       "1                      0.0                           4                      0   \n",
       "2                      6.0                         108                    226   \n",
       "3                     18.0                         203                  12000   \n",
       "4                      3.0                         455                    926   \n",
       "\n",
       "   Facenumber in posters  User Votes  Reviews by Users  Reviews by Crtiics  \\\n",
       "0                      1       10718                88                69.0   \n",
       "1                      1           5                 1                 1.0   \n",
       "2                      0        4849                45                48.0   \n",
       "3                      1      111841               413               260.0   \n",
       "4                      1        7431                84                71.0   \n",
       "\n",
       "   IMDB Score  Unnamed: 25  \n",
       "0         8.0          NaN  \n",
       "1         4.8          NaN  \n",
       "2         8.3          NaN  \n",
       "3         8.3          NaN  \n",
       "4         8.0          NaN  \n",
       "\n",
       "[5 rows x 26 columns]"
      ]
     },
     "execution_count": 29,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "movies_1900.head()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Or to select a specific work sheet directly by name"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 30,
   "metadata": {},
   "outputs": [],
   "source": [
    "movies_2000 = pd.read_excel('data/movies.xlsx', sheet_name='2000s', engine='openpyxl')"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 31,
   "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 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",
       "      <th>Unnamed: 25</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>102 Dalmatians</td>\n",
       "      <td>2000</td>\n",
       "      <td>Adventure|Comedy|Family</td>\n",
       "      <td>English</td>\n",
       "      <td>USA</td>\n",
       "      <td>G</td>\n",
       "      <td>100.0</td>\n",
       "      <td>1.85</td>\n",
       "      <td>85000000.0</td>\n",
       "      <td>66941559.0</td>\n",
       "      <td>...</td>\n",
       "      <td>795.0</td>\n",
       "      <td>439.0</td>\n",
       "      <td>4182</td>\n",
       "      <td>372</td>\n",
       "      <td>1</td>\n",
       "      <td>26413</td>\n",
       "      <td>77.0</td>\n",
       "      <td>84.0</td>\n",
       "      <td>4.8</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>28 Days</td>\n",
       "      <td>2000</td>\n",
       "      <td>Comedy|Drama</td>\n",
       "      <td>English</td>\n",
       "      <td>USA</td>\n",
       "      <td>PG-13</td>\n",
       "      <td>103.0</td>\n",
       "      <td>1.37</td>\n",
       "      <td>43000000.0</td>\n",
       "      <td>37035515.0</td>\n",
       "      <td>...</td>\n",
       "      <td>10000.0</td>\n",
       "      <td>664.0</td>\n",
       "      <td>23864</td>\n",
       "      <td>0</td>\n",
       "      <td>1</td>\n",
       "      <td>34597</td>\n",
       "      <td>194.0</td>\n",
       "      <td>116.0</td>\n",
       "      <td>6.0</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>3 Strikes</td>\n",
       "      <td>2000</td>\n",
       "      <td>Comedy</td>\n",
       "      <td>English</td>\n",
       "      <td>USA</td>\n",
       "      <td>R</td>\n",
       "      <td>82.0</td>\n",
       "      <td>1.85</td>\n",
       "      <td>6000000.0</td>\n",
       "      <td>9821335.0</td>\n",
       "      <td>...</td>\n",
       "      <td>706.0</td>\n",
       "      <td>585.0</td>\n",
       "      <td>3354</td>\n",
       "      <td>118</td>\n",
       "      <td>1</td>\n",
       "      <td>1415</td>\n",
       "      <td>10.0</td>\n",
       "      <td>22.0</td>\n",
       "      <td>4.0</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>Aberdeen</td>\n",
       "      <td>2000</td>\n",
       "      <td>Drama</td>\n",
       "      <td>English</td>\n",
       "      <td>UK</td>\n",
       "      <td>NaN</td>\n",
       "      <td>106.0</td>\n",
       "      <td>1.85</td>\n",
       "      <td>6500000.0</td>\n",
       "      <td>64148.0</td>\n",
       "      <td>...</td>\n",
       "      <td>2.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>846</td>\n",
       "      <td>260</td>\n",
       "      <td>0</td>\n",
       "      <td>2601</td>\n",
       "      <td>35.0</td>\n",
       "      <td>28.0</td>\n",
       "      <td>7.3</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>All the Pretty Horses</td>\n",
       "      <td>2000</td>\n",
       "      <td>Drama|Romance|Western</td>\n",
       "      <td>English</td>\n",
       "      <td>USA</td>\n",
       "      <td>PG-13</td>\n",
       "      <td>220.0</td>\n",
       "      <td>2.35</td>\n",
       "      <td>57000000.0</td>\n",
       "      <td>15527125.0</td>\n",
       "      <td>...</td>\n",
       "      <td>861.0</td>\n",
       "      <td>820.0</td>\n",
       "      <td>15006</td>\n",
       "      <td>652</td>\n",
       "      <td>2</td>\n",
       "      <td>11388</td>\n",
       "      <td>183.0</td>\n",
       "      <td>85.0</td>\n",
       "      <td>5.8</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "<p>5 rows × 26 columns</p>\n",
       "</div>"
      ],
      "text/plain": [
       "                    Title  Year                   Genres Language Country  \\\n",
       "0         102 Dalmatians   2000  Adventure|Comedy|Family  English     USA   \n",
       "1                28 Days   2000             Comedy|Drama  English     USA   \n",
       "2              3 Strikes   2000                   Comedy  English     USA   \n",
       "3               Aberdeen   2000                    Drama  English      UK   \n",
       "4  All the Pretty Horses   2000    Drama|Romance|Western  English     USA   \n",
       "\n",
       "  Content Rating  Duration  Aspect Ratio      Budget  Gross Earnings  ...  \\\n",
       "0              G     100.0          1.85  85000000.0      66941559.0  ...   \n",
       "1          PG-13     103.0          1.37  43000000.0      37035515.0  ...   \n",
       "2              R      82.0          1.85   6000000.0       9821335.0  ...   \n",
       "3            NaN     106.0          1.85   6500000.0         64148.0  ...   \n",
       "4          PG-13     220.0          2.35  57000000.0      15527125.0  ...   \n",
       "\n",
       "  Facebook Likes - Actor 2 Facebook Likes - Actor 3  \\\n",
       "0                    795.0                    439.0   \n",
       "1                  10000.0                    664.0   \n",
       "2                    706.0                    585.0   \n",
       "3                      2.0                      0.0   \n",
       "4                    861.0                    820.0   \n",
       "\n",
       "  Facebook Likes - cast Total Facebook likes - Movie  Facenumber in posters  \\\n",
       "0                        4182                    372                      1   \n",
       "1                       23864                      0                      1   \n",
       "2                        3354                    118                      1   \n",
       "3                         846                    260                      0   \n",
       "4                       15006                    652                      2   \n",
       "\n",
       "   User Votes  Reviews by Users  Reviews by Crtiics  IMDB Score  Unnamed: 25  \n",
       "0       26413              77.0                84.0         4.8          NaN  \n",
       "1       34597             194.0               116.0         6.0          NaN  \n",
       "2        1415              10.0                22.0         4.0          NaN  \n",
       "3        2601              35.0                28.0         7.3          NaN  \n",
       "4       11388             183.0                85.0         5.8          NaN  \n",
       "\n",
       "[5 rows x 26 columns]"
      ]
     },
     "execution_count": 31,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "movies_2000.head()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "If the cells contain formulas, pandas simply returns the current values (the current output of each formula)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 32,
   "metadata": {},
   "outputs": [
    {
     "name": "stderr",
     "output_type": "stream",
     "text": [
      "/Users/bgoncalves/opt/anaconda3/lib/python3.8/site-packages/openpyxl/worksheet/header_footer.py:48: UserWarning: Cannot parse header or footer so it will be ignored\n",
      "  warn(\"\"\"Cannot parse header or footer so it will be ignored\"\"\")\n"
     ]
    }
   ],
   "source": [
    "mortgage = pd.read_excel('data/excel-mortgage-calculator.xlsx', skiprows=15, engine='openpyxl')"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 33,
   "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>Unnamed: 0</th>\n",
       "      <th>PMT NO</th>\n",
       "      <th>PAYMENT 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>CUMULATIVE INTEREST</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>NaN</td>\n",
       "      <td>1</td>\n",
       "      <td>2020-09-01</td>\n",
       "      <td>200000.000000</td>\n",
       "      <td>1073.643246</td>\n",
       "      <td>100</td>\n",
       "      <td>1173.643246</td>\n",
       "      <td>340.309913</td>\n",
       "      <td>833.333333</td>\n",
       "      <td>199659.690087</td>\n",
       "      <td>833.333333</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>NaN</td>\n",
       "      <td>2</td>\n",
       "      <td>2020-10-01</td>\n",
       "      <td>199659.690087</td>\n",
       "      <td>1073.643246</td>\n",
       "      <td>100</td>\n",
       "      <td>1173.643246</td>\n",
       "      <td>341.727871</td>\n",
       "      <td>831.915375</td>\n",
       "      <td>199317.962217</td>\n",
       "      <td>1665.248709</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>NaN</td>\n",
       "      <td>3</td>\n",
       "      <td>2020-11-01</td>\n",
       "      <td>199317.962217</td>\n",
       "      <td>1073.643246</td>\n",
       "      <td>100</td>\n",
       "      <td>1173.643246</td>\n",
       "      <td>343.151737</td>\n",
       "      <td>830.491509</td>\n",
       "      <td>198974.810480</td>\n",
       "      <td>2495.740218</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>NaN</td>\n",
       "      <td>4</td>\n",
       "      <td>2020-12-01</td>\n",
       "      <td>198974.810480</td>\n",
       "      <td>1073.643246</td>\n",
       "      <td>100</td>\n",
       "      <td>1173.643246</td>\n",
       "      <td>344.581536</td>\n",
       "      <td>829.061710</td>\n",
       "      <td>198630.228944</td>\n",
       "      <td>3324.801928</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>NaN</td>\n",
       "      <td>5</td>\n",
       "      <td>2021-01-01</td>\n",
       "      <td>198630.228944</td>\n",
       "      <td>1073.643246</td>\n",
       "      <td>100</td>\n",
       "      <td>1173.643246</td>\n",
       "      <td>346.017292</td>\n",
       "      <td>827.625954</td>\n",
       "      <td>198284.211652</td>\n",
       "      <td>4152.427882</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",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>355</th>\n",
       "      <td>NaN</td>\n",
       "      <td>356</td>\n",
       "      <td>2050-04-01</td>\n",
       "      <td>0.000000</td>\n",
       "      <td>1073.643246</td>\n",
       "      <td>0</td>\n",
       "      <td>0.000000</td>\n",
       "      <td>0.000000</td>\n",
       "      <td>0.000000</td>\n",
       "      <td>0.000000</td>\n",
       "      <td>149442.538588</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>356</th>\n",
       "      <td>NaN</td>\n",
       "      <td>357</td>\n",
       "      <td>2050-05-01</td>\n",
       "      <td>0.000000</td>\n",
       "      <td>1073.643246</td>\n",
       "      <td>0</td>\n",
       "      <td>0.000000</td>\n",
       "      <td>0.000000</td>\n",
       "      <td>0.000000</td>\n",
       "      <td>0.000000</td>\n",
       "      <td>149442.538588</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>357</th>\n",
       "      <td>NaN</td>\n",
       "      <td>358</td>\n",
       "      <td>2050-06-01</td>\n",
       "      <td>0.000000</td>\n",
       "      <td>1073.643246</td>\n",
       "      <td>0</td>\n",
       "      <td>0.000000</td>\n",
       "      <td>0.000000</td>\n",
       "      <td>0.000000</td>\n",
       "      <td>0.000000</td>\n",
       "      <td>149442.538588</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>358</th>\n",
       "      <td>NaN</td>\n",
       "      <td>359</td>\n",
       "      <td>2050-07-01</td>\n",
       "      <td>0.000000</td>\n",
       "      <td>1073.643246</td>\n",
       "      <td>0</td>\n",
       "      <td>0.000000</td>\n",
       "      <td>0.000000</td>\n",
       "      <td>0.000000</td>\n",
       "      <td>0.000000</td>\n",
       "      <td>149442.538588</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>359</th>\n",
       "      <td>NaN</td>\n",
       "      <td>360</td>\n",
       "      <td>2050-08-01</td>\n",
       "      <td>0.000000</td>\n",
       "      <td>1073.643246</td>\n",
       "      <td>0</td>\n",
       "      <td>0.000000</td>\n",
       "      <td>0.000000</td>\n",
       "      <td>0.000000</td>\n",
       "      <td>0.000000</td>\n",
       "      <td>149442.538588</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "<p>360 rows × 11 columns</p>\n",
       "</div>"
      ],
      "text/plain": [
       "     Unnamed: 0  PMT NO PAYMENT DATE  BEGINNING BALANCE  SCHEDULED PAYMENT  \\\n",
       "0           NaN       1   2020-09-01      200000.000000        1073.643246   \n",
       "1           NaN       2   2020-10-01      199659.690087        1073.643246   \n",
       "2           NaN       3   2020-11-01      199317.962217        1073.643246   \n",
       "3           NaN       4   2020-12-01      198974.810480        1073.643246   \n",
       "4           NaN       5   2021-01-01      198630.228944        1073.643246   \n",
       "..          ...     ...          ...                ...                ...   \n",
       "355         NaN     356   2050-04-01           0.000000        1073.643246   \n",
       "356         NaN     357   2050-05-01           0.000000        1073.643246   \n",
       "357         NaN     358   2050-06-01           0.000000        1073.643246   \n",
       "358         NaN     359   2050-07-01           0.000000        1073.643246   \n",
       "359         NaN     360   2050-08-01           0.000000        1073.643246   \n",
       "\n",
       "     EXTRA PAYMENT  TOTAL PAYMENT   PRINCIPAL    INTEREST  ENDING BALANCE  \\\n",
       "0              100    1173.643246  340.309913  833.333333   199659.690087   \n",
       "1              100    1173.643246  341.727871  831.915375   199317.962217   \n",
       "2              100    1173.643246  343.151737  830.491509   198974.810480   \n",
       "3              100    1173.643246  344.581536  829.061710   198630.228944   \n",
       "4              100    1173.643246  346.017292  827.625954   198284.211652   \n",
       "..             ...            ...         ...         ...             ...   \n",
       "355              0       0.000000    0.000000    0.000000        0.000000   \n",
       "356              0       0.000000    0.000000    0.000000        0.000000   \n",
       "357              0       0.000000    0.000000    0.000000        0.000000   \n",
       "358              0       0.000000    0.000000    0.000000        0.000000   \n",
       "359              0       0.000000    0.000000    0.000000        0.000000   \n",
       "\n",
       "     CUMULATIVE INTEREST  \n",
       "0             833.333333  \n",
       "1            1665.248709  \n",
       "2            2495.740218  \n",
       "3            3324.801928  \n",
       "4            4152.427882  \n",
       "..                   ...  \n",
       "355        149442.538588  \n",
       "356        149442.538588  \n",
       "357        149442.538588  \n",
       "358        149442.538588  \n",
       "359        149442.538588  \n",
       "\n",
       "[360 rows x 11 columns]"
      ]
     },
     "execution_count": 33,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "mortgage"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Wherever possible, dypes are chosen according to the excel format specified"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 34,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "<class 'pandas.core.frame.DataFrame'>\n",
      "RangeIndex: 360 entries, 0 to 359\n",
      "Data columns (total 11 columns):\n",
      " #   Column               Non-Null Count  Dtype         \n",
      "---  ------               --------------  -----         \n",
      " 0   Unnamed: 0           0 non-null      float64       \n",
      " 1   PMT NO               360 non-null    int64         \n",
      " 2   PAYMENT DATE         360 non-null    datetime64[ns]\n",
      " 3   BEGINNING BALANCE    360 non-null    float64       \n",
      " 4   SCHEDULED PAYMENT    360 non-null    float64       \n",
      " 5   EXTRA PAYMENT        360 non-null    int64         \n",
      " 6   TOTAL PAYMENT        360 non-null    float64       \n",
      " 7   PRINCIPAL            360 non-null    float64       \n",
      " 8   INTEREST             360 non-null    float64       \n",
      " 9   ENDING BALANCE       360 non-null    float64       \n",
      " 10  CUMULATIVE INTEREST  360 non-null    float64       \n",
      "dtypes: datetime64[ns](1), float64(8), int64(2)\n",
      "memory usage: 31.1 KB\n"
     ]
    }
   ],
   "source": [
    "mortgage.info()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### ExcelFile"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 35,
   "metadata": {},
   "outputs": [],
   "source": [
    "book = pd.ExcelFile('data/movies.xlsx')"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "We can easily get a list of all worksheets"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 36,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "['1900s', '2000s', '2010s', '3000s']"
      ]
     },
     "execution_count": 36,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "book.sheet_names"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "We can easily parse a specific sheet and convert it to a DataFrame"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 37,
   "metadata": {},
   "outputs": [],
   "source": [
    "?book.parse"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 38,
   "metadata": {},
   "outputs": [],
   "source": [
    "df3 = book.parse('2000s')"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 39,
   "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>102 Dalmatians</td>\n",
       "      <td>2000</td>\n",
       "      <td>Adventure|Comedy|Family</td>\n",
       "      <td>English</td>\n",
       "      <td>USA</td>\n",
       "      <td>G</td>\n",
       "      <td>100.0</td>\n",
       "      <td>1.85</td>\n",
       "      <td>85000000.0</td>\n",
       "      <td>66941559.0</td>\n",
       "      <td>...</td>\n",
       "      <td>2000.0</td>\n",
       "      <td>795.0</td>\n",
       "      <td>439.0</td>\n",
       "      <td>4182</td>\n",
       "      <td>372</td>\n",
       "      <td>1</td>\n",
       "      <td>26413</td>\n",
       "      <td>77.0</td>\n",
       "      <td>84.0</td>\n",
       "      <td>4.8</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>28 Days</td>\n",
       "      <td>2000</td>\n",
       "      <td>Comedy|Drama</td>\n",
       "      <td>English</td>\n",
       "      <td>USA</td>\n",
       "      <td>PG-13</td>\n",
       "      <td>103.0</td>\n",
       "      <td>1.37</td>\n",
       "      <td>43000000.0</td>\n",
       "      <td>37035515.0</td>\n",
       "      <td>...</td>\n",
       "      <td>12000.0</td>\n",
       "      <td>10000.0</td>\n",
       "      <td>664.0</td>\n",
       "      <td>23864</td>\n",
       "      <td>0</td>\n",
       "      <td>1</td>\n",
       "      <td>34597</td>\n",
       "      <td>194.0</td>\n",
       "      <td>116.0</td>\n",
       "      <td>6.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>3 Strikes</td>\n",
       "      <td>2000</td>\n",
       "      <td>Comedy</td>\n",
       "      <td>English</td>\n",
       "      <td>USA</td>\n",
       "      <td>R</td>\n",
       "      <td>82.0</td>\n",
       "      <td>1.85</td>\n",
       "      <td>6000000.0</td>\n",
       "      <td>9821335.0</td>\n",
       "      <td>...</td>\n",
       "      <td>939.0</td>\n",
       "      <td>706.0</td>\n",
       "      <td>585.0</td>\n",
       "      <td>3354</td>\n",
       "      <td>118</td>\n",
       "      <td>1</td>\n",
       "      <td>1415</td>\n",
       "      <td>10.0</td>\n",
       "      <td>22.0</td>\n",
       "      <td>4.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>Aberdeen</td>\n",
       "      <td>2000</td>\n",
       "      <td>Drama</td>\n",
       "      <td>English</td>\n",
       "      <td>UK</td>\n",
       "      <td>NaN</td>\n",
       "      <td>106.0</td>\n",
       "      <td>1.85</td>\n",
       "      <td>6500000.0</td>\n",
       "      <td>64148.0</td>\n",
       "      <td>...</td>\n",
       "      <td>844.0</td>\n",
       "      <td>2.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>846</td>\n",
       "      <td>260</td>\n",
       "      <td>0</td>\n",
       "      <td>2601</td>\n",
       "      <td>35.0</td>\n",
       "      <td>28.0</td>\n",
       "      <td>7.3</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>All the Pretty Horses</td>\n",
       "      <td>2000</td>\n",
       "      <td>Drama|Romance|Western</td>\n",
       "      <td>English</td>\n",
       "      <td>USA</td>\n",
       "      <td>PG-13</td>\n",
       "      <td>220.0</td>\n",
       "      <td>2.35</td>\n",
       "      <td>57000000.0</td>\n",
       "      <td>15527125.0</td>\n",
       "      <td>...</td>\n",
       "      <td>13000.0</td>\n",
       "      <td>861.0</td>\n",
       "      <td>820.0</td>\n",
       "      <td>15006</td>\n",
       "      <td>652</td>\n",
       "      <td>2</td>\n",
       "      <td>11388</td>\n",
       "      <td>183.0</td>\n",
       "      <td>85.0</td>\n",
       "      <td>5.8</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "<p>5 rows × 25 columns</p>\n",
       "</div>"
      ],
      "text/plain": [
       "                    Title  Year                   Genres Language Country  \\\n",
       "0         102 Dalmatians   2000  Adventure|Comedy|Family  English     USA   \n",
       "1                28 Days   2000             Comedy|Drama  English     USA   \n",
       "2              3 Strikes   2000                   Comedy  English     USA   \n",
       "3               Aberdeen   2000                    Drama  English      UK   \n",
       "4  All the Pretty Horses   2000    Drama|Romance|Western  English     USA   \n",
       "\n",
       "  Content Rating  Duration  Aspect Ratio      Budget  Gross Earnings  ...  \\\n",
       "0              G     100.0          1.85  85000000.0      66941559.0  ...   \n",
       "1          PG-13     103.0          1.37  43000000.0      37035515.0  ...   \n",
       "2              R      82.0          1.85   6000000.0       9821335.0  ...   \n",
       "3            NaN     106.0          1.85   6500000.0         64148.0  ...   \n",
       "4          PG-13     220.0          2.35  57000000.0      15527125.0  ...   \n",
       "\n",
       "  Facebook Likes - Actor 1 Facebook Likes - Actor 2 Facebook Likes - Actor 3  \\\n",
       "0                   2000.0                    795.0                    439.0   \n",
       "1                  12000.0                  10000.0                    664.0   \n",
       "2                    939.0                    706.0                    585.0   \n",
       "3                    844.0                      2.0                      0.0   \n",
       "4                  13000.0                    861.0                    820.0   \n",
       "\n",
       "  Facebook Likes - cast Total  Facebook likes - Movie  Facenumber in posters  \\\n",
       "0                        4182                     372                      1   \n",
       "1                       23864                       0                      1   \n",
       "2                        3354                     118                      1   \n",
       "3                         846                     260                      0   \n",
       "4                       15006                     652                      2   \n",
       "\n",
       "   User Votes  Reviews by Users  Reviews by Crtiics  IMDB Score  \n",
       "0       26413              77.0                84.0         4.8  \n",
       "1       34597             194.0               116.0         6.0  \n",
       "2        1415              10.0                22.0         4.0  \n",
       "3        2601              35.0                28.0         7.3  \n",
       "4       11388             183.0                85.0         5.8  \n",
       "\n",
       "[5 rows x 25 columns]"
      ]
     },
     "execution_count": 39,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df3.head()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "__parse__ supports most of the parameters available for read_excel"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 40,
   "metadata": {},
   "outputs": [],
   "source": [
    "df4 = book.parse('2000s', index_col=0, \n",
    "                 usecols=['Title', 'Year', 'Director', 'Budget'])"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 41,
   "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>Year</th>\n",
       "      <th>Budget</th>\n",
       "      <th>Director</th>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Title</th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>102 Dalmatians</th>\n",
       "      <td>2000</td>\n",
       "      <td>85000000.0</td>\n",
       "      <td>Kevin Lima</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>28 Days</th>\n",
       "      <td>2000</td>\n",
       "      <td>43000000.0</td>\n",
       "      <td>Betty Thomas</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3 Strikes</th>\n",
       "      <td>2000</td>\n",
       "      <td>6000000.0</td>\n",
       "      <td>DJ Pooh</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Aberdeen</th>\n",
       "      <td>2000</td>\n",
       "      <td>6500000.0</td>\n",
       "      <td>Hans Petter Moland</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>All the Pretty Horses</th>\n",
       "      <td>2000</td>\n",
       "      <td>57000000.0</td>\n",
       "      <td>Billy Bob Thornton</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "                        Year      Budget            Director\n",
       "Title                                                       \n",
       "102 Dalmatians          2000  85000000.0          Kevin Lima\n",
       "28 Days                 2000  43000000.0        Betty Thomas\n",
       "3 Strikes               2000   6000000.0             DJ Pooh\n",
       "Aberdeen                2000   6500000.0  Hans Petter Moland\n",
       "All the Pretty Horses   2000  57000000.0  Billy Bob Thornton"
      ]
     },
     "execution_count": 41,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df4.head()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### Web pages"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "We're going to use the Wikipedia page with the current numbers of cases for CoVID-19"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 42,
   "metadata": {},
   "outputs": [],
   "source": [
    "url = 'https://en.wikipedia.org/wiki/COVID-19_pandemic_by_country_and_territory'"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "All we have to do is to provide the url"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 43,
   "metadata": {},
   "outputs": [],
   "source": [
    "dfs = pd.read_html(url)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Which retrieves all the tables in the page, in the order they appear"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 44,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "70"
      ]
     },
     "execution_count": 44,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "len(dfs)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "So the first one is the infobox on the top right hand corner"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 45,
   "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>COVID-19 pandemic</th>\n",
       "      <th>COVID-19 pandemic.1</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>Confirmed deaths per 100,000 population,[1][no...</td>\n",
       "      <td>Confirmed deaths per 100,000 population,[1][no...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>Disease</td>\n",
       "      <td>COVID-19</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>Virus strain</td>\n",
       "      <td>SARS-CoV-2</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>Source</td>\n",
       "      <td>Probably bats, possibly via pangolins[2][3]</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>Location</td>\n",
       "      <td>Worldwide</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>5</th>\n",
       "      <td>First outbreak</td>\n",
       "      <td>China[4]</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>6</th>\n",
       "      <td>Index case</td>\n",
       "      <td>Wuhan, Hubei, China.mw-parser-output .geo-defa...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>7</th>\n",
       "      <td>Date</td>\n",
       "      <td>17 November 2019[4] – present(2 years, 10 mont...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>8</th>\n",
       "      <td>Confirmed cases</td>\n",
       "      <td>621,868,879[5]</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>9</th>\n",
       "      <td>Deaths</td>\n",
       "      <td>6,558,461[5]</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>10</th>\n",
       "      <td>Vaccinations</td>\n",
       "      <td>5,392,445,281[5] (total vaccinated)4,964,805,8...</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "                                    COVID-19 pandemic  \\\n",
       "0   Confirmed deaths per 100,000 population,[1][no...   \n",
       "1                                             Disease   \n",
       "2                                        Virus strain   \n",
       "3                                              Source   \n",
       "4                                            Location   \n",
       "5                                      First outbreak   \n",
       "6                                          Index case   \n",
       "7                                                Date   \n",
       "8                                     Confirmed cases   \n",
       "9                                              Deaths   \n",
       "10                                       Vaccinations   \n",
       "\n",
       "                                  COVID-19 pandemic.1  \n",
       "0   Confirmed deaths per 100,000 population,[1][no...  \n",
       "1                                            COVID-19  \n",
       "2                                          SARS-CoV-2  \n",
       "3         Probably bats, possibly via pangolins[2][3]  \n",
       "4                                           Worldwide  \n",
       "5                                            China[4]  \n",
       "6   Wuhan, Hubei, China.mw-parser-output .geo-defa...  \n",
       "7   17 November 2019[4] – present(2 years, 10 mont...  \n",
       "8                                      621,868,879[5]  \n",
       "9                                        6,558,461[5]  \n",
       "10  5,392,445,281[5] (total vaccinated)4,964,805,8...  "
      ]
     },
     "execution_count": 45,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "dfs[0]"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "And the fifteenth one is the number of cases and deaths per country"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 46,
   "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>Region[29]</th>\n",
       "      <th>Total cases</th>\n",
       "      <th>Total deaths</th>\n",
       "      <th>Cases per million</th>\n",
       "      <th>Deaths per million</th>\n",
       "      <th>Current weekly cases</th>\n",
       "      <th>Current weekly deaths</th>\n",
       "      <th>Population millions</th>\n",
       "      <th>Vaccinated %[30]</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>European Union</td>\n",
       "      <td>163770077</td>\n",
       "      <td>1138276</td>\n",
       "      <td>366114</td>\n",
       "      <td>2545</td>\n",
       "      <td>648555</td>\n",
       "      <td>2543</td>\n",
       "      <td>447</td>\n",
       "      <td>75.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>North America</td>\n",
       "      <td>98420934</td>\n",
       "      <td>1090029</td>\n",
       "      <td>266863</td>\n",
       "      <td>2956</td>\n",
       "      <td>523615</td>\n",
       "      <td>2936</td>\n",
       "      <td>369</td>\n",
       "      <td>74.6</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>Other Europe</td>\n",
       "      <td>55795334</td>\n",
       "      <td>491883</td>\n",
       "      <td>238808</td>\n",
       "      <td>2105</td>\n",
       "      <td>130330</td>\n",
       "      <td>812</td>\n",
       "      <td>234</td>\n",
       "      <td>61.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>South America</td>\n",
       "      <td>63162506</td>\n",
       "      <td>1300158</td>\n",
       "      <td>146932</td>\n",
       "      <td>3025</td>\n",
       "      <td>118462</td>\n",
       "      <td>1168</td>\n",
       "      <td>430</td>\n",
       "      <td>81.2</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>Russia and Central Asia</td>\n",
       "      <td>23934534</td>\n",
       "      <td>426857</td>\n",
       "      <td>101077</td>\n",
       "      <td>1803</td>\n",
       "      <td>339111</td>\n",
       "      <td>668</td>\n",
       "      <td>237</td>\n",
       "      <td>54.4</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "                Region[29]  Total cases  Total deaths  Cases per million  \\\n",
       "0           European Union    163770077       1138276             366114   \n",
       "1            North America     98420934       1090029             266863   \n",
       "2             Other Europe     55795334        491883             238808   \n",
       "3            South America     63162506       1300158             146932   \n",
       "4  Russia and Central Asia     23934534        426857             101077   \n",
       "\n",
       "   Deaths per million  Current weekly cases  Current weekly deaths  \\\n",
       "0                2545                648555                   2543   \n",
       "1                2956                523615                   2936   \n",
       "2                2105                130330                    812   \n",
       "3                3025                118462                   1168   \n",
       "4                1803                339111                    668   \n",
       "\n",
       "   Population millions  Vaccinated %[30]  \n",
       "0                  447              75.0  \n",
       "1                  369              74.6  \n",
       "2                  234              61.0  \n",
       "3                  430              81.2  \n",
       "4                  237              54.4  "
      ]
     },
     "execution_count": 46,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "dfs[15].head()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Due to the formatting of the table, pandas interpreted the first two rows to be the headers. We can fix this by explicitly telling it to just use the first row for the column headers"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 47,
   "metadata": {},
   "outputs": [],
   "source": [
    "dfs = pd.read_html(url, header=0)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 48,
   "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>Region[29]</th>\n",
       "      <th>Total cases</th>\n",
       "      <th>Total deaths</th>\n",
       "      <th>Cases per million</th>\n",
       "      <th>Deaths per million</th>\n",
       "      <th>Current weekly cases</th>\n",
       "      <th>Current weekly deaths</th>\n",
       "      <th>Population millions</th>\n",
       "      <th>Vaccinated %[30]</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>European Union</td>\n",
       "      <td>163770077</td>\n",
       "      <td>1138276</td>\n",
       "      <td>366114</td>\n",
       "      <td>2545</td>\n",
       "      <td>648555</td>\n",
       "      <td>2543</td>\n",
       "      <td>447</td>\n",
       "      <td>75.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>North America</td>\n",
       "      <td>98420934</td>\n",
       "      <td>1090029</td>\n",
       "      <td>266863</td>\n",
       "      <td>2956</td>\n",
       "      <td>523615</td>\n",
       "      <td>2936</td>\n",
       "      <td>369</td>\n",
       "      <td>74.6</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>Other Europe</td>\n",
       "      <td>55795334</td>\n",
       "      <td>491883</td>\n",
       "      <td>238808</td>\n",
       "      <td>2105</td>\n",
       "      <td>130330</td>\n",
       "      <td>812</td>\n",
       "      <td>234</td>\n",
       "      <td>61.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>South America</td>\n",
       "      <td>63162506</td>\n",
       "      <td>1300158</td>\n",
       "      <td>146932</td>\n",
       "      <td>3025</td>\n",
       "      <td>118462</td>\n",
       "      <td>1168</td>\n",
       "      <td>430</td>\n",
       "      <td>81.2</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>Russia and Central Asia</td>\n",
       "      <td>23934534</td>\n",
       "      <td>426857</td>\n",
       "      <td>101077</td>\n",
       "      <td>1803</td>\n",
       "      <td>339111</td>\n",
       "      <td>668</td>\n",
       "      <td>237</td>\n",
       "      <td>54.4</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>5</th>\n",
       "      <td>Central America</td>\n",
       "      <td>10976906</td>\n",
       "      <td>378719</td>\n",
       "      <td>61095</td>\n",
       "      <td>2108</td>\n",
       "      <td>37351</td>\n",
       "      <td>292</td>\n",
       "      <td>180</td>\n",
       "      <td>67.3</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>6</th>\n",
       "      <td>Middle East</td>\n",
       "      <td>22266626</td>\n",
       "      <td>236911</td>\n",
       "      <td>85315</td>\n",
       "      <td>908</td>\n",
       "      <td>28133</td>\n",
       "      <td>253</td>\n",
       "      <td>261</td>\n",
       "      <td>51.7</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>7</th>\n",
       "      <td>Oceania and islands in East Asia</td>\n",
       "      <td>47860321</td>\n",
       "      <td>292366</td>\n",
       "      <td>82894</td>\n",
       "      <td>506</td>\n",
       "      <td>1031665</td>\n",
       "      <td>2590</td>\n",
       "      <td>577</td>\n",
       "      <td>71.7</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>8</th>\n",
       "      <td>Caribbean</td>\n",
       "      <td>2573339</td>\n",
       "      <td>25811</td>\n",
       "      <td>59437</td>\n",
       "      <td>596</td>\n",
       "      <td>4997</td>\n",
       "      <td>55</td>\n",
       "      <td>43</td>\n",
       "      <td>46.1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>9</th>\n",
       "      <td>South Asia</td>\n",
       "      <td>50123528</td>\n",
       "      <td>617393</td>\n",
       "      <td>27001</td>\n",
       "      <td>333</td>\n",
       "      <td>45511</td>\n",
       "      <td>211</td>\n",
       "      <td>1856</td>\n",
       "      <td>68.5</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>10</th>\n",
       "      <td>North Africa</td>\n",
       "      <td>3717201</td>\n",
       "      <td>83807</td>\n",
       "      <td>18332</td>\n",
       "      <td>413</td>\n",
       "      <td>1208</td>\n",
       "      <td>8</td>\n",
       "      <td>203</td>\n",
       "      <td>41.2</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>11</th>\n",
       "      <td>East Asia</td>\n",
       "      <td>50715909</td>\n",
       "      <td>182084</td>\n",
       "      <td>28409</td>\n",
       "      <td>102</td>\n",
       "      <td>573700</td>\n",
       "      <td>610</td>\n",
       "      <td>1785</td>\n",
       "      <td>86.9</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>12</th>\n",
       "      <td>Sub-Saharan Africa</td>\n",
       "      <td>8516338</td>\n",
       "      <td>171848</td>\n",
       "      <td>7492</td>\n",
       "      <td>151</td>\n",
       "      <td>7070</td>\n",
       "      <td>63</td>\n",
       "      <td>1137</td>\n",
       "      <td>20.4</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>13</th>\n",
       "      <td>Totals (or average)</td>\n",
       "      <td>601833553</td>\n",
       "      <td>6436142</td>\n",
       "      <td>77566</td>\n",
       "      <td>830</td>\n",
       "      <td>3489708</td>\n",
       "      <td>12209</td>\n",
       "      <td>7759</td>\n",
       "      <td>65.4</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "                          Region[29]  Total cases  Total deaths  \\\n",
       "0                     European Union    163770077       1138276   \n",
       "1                      North America     98420934       1090029   \n",
       "2                       Other Europe     55795334        491883   \n",
       "3                      South America     63162506       1300158   \n",
       "4            Russia and Central Asia     23934534        426857   \n",
       "5                    Central America     10976906        378719   \n",
       "6                        Middle East     22266626        236911   \n",
       "7   Oceania and islands in East Asia     47860321        292366   \n",
       "8                          Caribbean      2573339         25811   \n",
       "9                         South Asia     50123528        617393   \n",
       "10                      North Africa      3717201         83807   \n",
       "11                         East Asia     50715909        182084   \n",
       "12                Sub-Saharan Africa      8516338        171848   \n",
       "13               Totals (or average)    601833553       6436142   \n",
       "\n",
       "    Cases per million  Deaths per million  Current weekly cases  \\\n",
       "0              366114                2545                648555   \n",
       "1              266863                2956                523615   \n",
       "2              238808                2105                130330   \n",
       "3              146932                3025                118462   \n",
       "4              101077                1803                339111   \n",
       "5               61095                2108                 37351   \n",
       "6               85315                 908                 28133   \n",
       "7               82894                 506               1031665   \n",
       "8               59437                 596                  4997   \n",
       "9               27001                 333                 45511   \n",
       "10              18332                 413                  1208   \n",
       "11              28409                 102                573700   \n",
       "12               7492                 151                  7070   \n",
       "13              77566                 830               3489708   \n",
       "\n",
       "    Current weekly deaths  Population millions  Vaccinated %[30]  \n",
       "0                    2543                  447              75.0  \n",
       "1                    2936                  369              74.6  \n",
       "2                     812                  234              61.0  \n",
       "3                    1168                  430              81.2  \n",
       "4                     668                  237              54.4  \n",
       "5                     292                  180              67.3  \n",
       "6                     253                  261              51.7  \n",
       "7                    2590                  577              71.7  \n",
       "8                      55                   43              46.1  \n",
       "9                     211                 1856              68.5  \n",
       "10                      8                  203              41.2  \n",
       "11                    610                 1785              86.9  \n",
       "12                     63                 1137              20.4  \n",
       "13                  12209                 7759              65.4  "
      ]
     },
     "execution_count": 48,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "dfs[15]"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## Subsetting"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "The top/bottom N number of values are easy to access"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 49,
   "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>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>Director</th>\n",
       "      <th>...</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",
       "      <th>Unnamed: 25</th>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Title</th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>Intolerance: Love's Struggle Throughout the Ages</th>\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>D.W. Griffith</td>\n",
       "      <td>...</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",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Over the Hill to the Poorhouse</th>\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>Harry F. Millarde</td>\n",
       "      <td>...</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",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>The Big Parade</th>\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>King Vidor</td>\n",
       "      <td>...</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",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Metropolis</th>\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>Fritz Lang</td>\n",
       "      <td>...</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",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Pandora's Box</th>\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>Georg Wilhelm Pabst</td>\n",
       "      <td>...</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",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>The Broadway Melody</th>\n",
       "      <td>1929</td>\n",
       "      <td>Musical|Romance</td>\n",
       "      <td>English</td>\n",
       "      <td>USA</td>\n",
       "      <td>Passed</td>\n",
       "      <td>100</td>\n",
       "      <td>1.37</td>\n",
       "      <td>379000.0</td>\n",
       "      <td>2808000.0</td>\n",
       "      <td>Harry Beaumont</td>\n",
       "      <td>...</td>\n",
       "      <td>28</td>\n",
       "      <td>4.0</td>\n",
       "      <td>109</td>\n",
       "      <td>167</td>\n",
       "      <td>8</td>\n",
       "      <td>4546</td>\n",
       "      <td>71</td>\n",
       "      <td>36.0</td>\n",
       "      <td>6.3</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Hell's Angels</th>\n",
       "      <td>1930</td>\n",
       "      <td>Drama|War</td>\n",
       "      <td>English</td>\n",
       "      <td>USA</td>\n",
       "      <td>Passed</td>\n",
       "      <td>96</td>\n",
       "      <td>1.20</td>\n",
       "      <td>3950000.0</td>\n",
       "      <td>NaN</td>\n",
       "      <td>Howard Hughes</td>\n",
       "      <td>...</td>\n",
       "      <td>12</td>\n",
       "      <td>4.0</td>\n",
       "      <td>457</td>\n",
       "      <td>279</td>\n",
       "      <td>1</td>\n",
       "      <td>3753</td>\n",
       "      <td>53</td>\n",
       "      <td>35.0</td>\n",
       "      <td>7.8</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>A Farewell to Arms</th>\n",
       "      <td>1932</td>\n",
       "      <td>Drama|Romance|War</td>\n",
       "      <td>English</td>\n",
       "      <td>USA</td>\n",
       "      <td>Unrated</td>\n",
       "      <td>79</td>\n",
       "      <td>1.37</td>\n",
       "      <td>800000.0</td>\n",
       "      <td>NaN</td>\n",
       "      <td>Frank Borzage</td>\n",
       "      <td>...</td>\n",
       "      <td>164</td>\n",
       "      <td>99.0</td>\n",
       "      <td>1284</td>\n",
       "      <td>213</td>\n",
       "      <td>1</td>\n",
       "      <td>3519</td>\n",
       "      <td>46</td>\n",
       "      <td>42.0</td>\n",
       "      <td>6.6</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>42nd Street</th>\n",
       "      <td>1933</td>\n",
       "      <td>Comedy|Musical|Romance</td>\n",
       "      <td>English</td>\n",
       "      <td>USA</td>\n",
       "      <td>Unrated</td>\n",
       "      <td>89</td>\n",
       "      <td>1.37</td>\n",
       "      <td>439000.0</td>\n",
       "      <td>2300000.0</td>\n",
       "      <td>Lloyd Bacon</td>\n",
       "      <td>...</td>\n",
       "      <td>105</td>\n",
       "      <td>45.0</td>\n",
       "      <td>995</td>\n",
       "      <td>439</td>\n",
       "      <td>2</td>\n",
       "      <td>7921</td>\n",
       "      <td>97</td>\n",
       "      <td>65.0</td>\n",
       "      <td>7.7</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>She Done Him Wrong</th>\n",
       "      <td>1933</td>\n",
       "      <td>Comedy|Drama|History|Musical|Romance</td>\n",
       "      <td>English</td>\n",
       "      <td>USA</td>\n",
       "      <td>Approved</td>\n",
       "      <td>66</td>\n",
       "      <td>1.37</td>\n",
       "      <td>200000.0</td>\n",
       "      <td>NaN</td>\n",
       "      <td>Lowell Sherman</td>\n",
       "      <td>...</td>\n",
       "      <td>85</td>\n",
       "      <td>28.0</td>\n",
       "      <td>583</td>\n",
       "      <td>328</td>\n",
       "      <td>1</td>\n",
       "      <td>4152</td>\n",
       "      <td>59</td>\n",
       "      <td>35.0</td>\n",
       "      <td>6.5</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "<p>10 rows × 25 columns</p>\n",
       "</div>"
      ],
      "text/plain": [
       "                                                   Year  \\\n",
       "Title                                                     \n",
       "Intolerance: Love's Struggle Throughout the Ages   1916   \n",
       "Over the Hill to the Poorhouse                     1920   \n",
       "The Big Parade                                     1925   \n",
       "Metropolis                                         1927   \n",
       "Pandora's Box                                      1929   \n",
       "The Broadway Melody                                1929   \n",
       "Hell's Angels                                      1930   \n",
       "A Farewell to Arms                                 1932   \n",
       "42nd Street                                        1933   \n",
       "She Done Him Wrong                                 1933   \n",
       "\n",
       "                                                                                 Genres  \\\n",
       "Title                                                                                     \n",
       "Intolerance: Love's Struggle Throughout the Ages                      Drama|History|War   \n",
       "Over the Hill to the Poorhouse                                              Crime|Drama   \n",
       "The Big Parade                                                        Drama|Romance|War   \n",
       "Metropolis                                                                 Drama|Sci-Fi   \n",
       "Pandora's Box                                                       Crime|Drama|Romance   \n",
       "The Broadway Melody                                                     Musical|Romance   \n",
       "Hell's Angels                                                                 Drama|War   \n",
       "A Farewell to Arms                                                    Drama|Romance|War   \n",
       "42nd Street                                                      Comedy|Musical|Romance   \n",
       "She Done Him Wrong                                 Comedy|Drama|History|Musical|Romance   \n",
       "\n",
       "                                                  Language  Country  \\\n",
       "Title                                                                 \n",
       "Intolerance: Love's Struggle Throughout the Ages       NaN      USA   \n",
       "Over the Hill to the Poorhouse                         NaN      USA   \n",
       "The Big Parade                                         NaN      USA   \n",
       "Metropolis                                          German  Germany   \n",
       "Pandora's Box                                       German  Germany   \n",
       "The Broadway Melody                                English      USA   \n",
       "Hell's Angels                                      English      USA   \n",
       "A Farewell to Arms                                 English      USA   \n",
       "42nd Street                                        English      USA   \n",
       "She Done Him Wrong                                 English      USA   \n",
       "\n",
       "                                                  Content Rating  Duration  \\\n",
       "Title                                                                        \n",
       "Intolerance: Love's Struggle Throughout the Ages       Not Rated       123   \n",
       "Over the Hill to the Poorhouse                               NaN       110   \n",
       "The Big Parade                                         Not Rated       151   \n",
       "Metropolis                                             Not Rated       145   \n",
       "Pandora's Box                                          Not Rated       110   \n",
       "The Broadway Melody                                       Passed       100   \n",
       "Hell's Angels                                             Passed        96   \n",
       "A Farewell to Arms                                       Unrated        79   \n",
       "42nd Street                                              Unrated        89   \n",
       "She Done Him Wrong                                      Approved        66   \n",
       "\n",
       "                                                   Aspect Ratio     Budget  \\\n",
       "Title                                                                        \n",
       "Intolerance: Love's Struggle Throughout the Ages           1.33   385907.0   \n",
       "Over the Hill to the Poorhouse                             1.33   100000.0   \n",
       "The Big Parade                                             1.33   245000.0   \n",
       "Metropolis                                                 1.33  6000000.0   \n",
       "Pandora's Box                                              1.33        NaN   \n",
       "The Broadway Melody                                        1.37   379000.0   \n",
       "Hell's Angels                                              1.20  3950000.0   \n",
       "A Farewell to Arms                                         1.37   800000.0   \n",
       "42nd Street                                                1.37   439000.0   \n",
       "She Done Him Wrong                                         1.37   200000.0   \n",
       "\n",
       "                                                   Gross Earnings  \\\n",
       "Title                                                               \n",
       "Intolerance: Love's Struggle Throughout the Ages              NaN   \n",
       "Over the Hill to the Poorhouse                          3000000.0   \n",
       "The Big Parade                                                NaN   \n",
       "Metropolis                                                26435.0   \n",
       "Pandora's Box                                              9950.0   \n",
       "The Broadway Melody                                     2808000.0   \n",
       "Hell's Angels                                                 NaN   \n",
       "A Farewell to Arms                                            NaN   \n",
       "42nd Street                                             2300000.0   \n",
       "She Done Him Wrong                                            NaN   \n",
       "\n",
       "                                                              Director  ...  \\\n",
       "Title                                                                   ...   \n",
       "Intolerance: Love's Struggle Throughout the Ages         D.W. Griffith  ...   \n",
       "Over the Hill to the Poorhouse                       Harry F. Millarde  ...   \n",
       "The Big Parade                                              King Vidor  ...   \n",
       "Metropolis                                                  Fritz Lang  ...   \n",
       "Pandora's Box                                      Georg Wilhelm Pabst  ...   \n",
       "The Broadway Melody                                     Harry Beaumont  ...   \n",
       "Hell's Angels                                            Howard Hughes  ...   \n",
       "A Farewell to Arms                                       Frank Borzage  ...   \n",
       "42nd Street                                                Lloyd Bacon  ...   \n",
       "She Done Him Wrong                                      Lowell Sherman  ...   \n",
       "\n",
       "                                                  Facebook Likes - Actor 2  \\\n",
       "Title                                                                        \n",
       "Intolerance: Love's Struggle Throughout the Ages                        22   \n",
       "Over the Hill to the Poorhouse                                           2   \n",
       "The Big Parade                                                          12   \n",
       "Metropolis                                                              23   \n",
       "Pandora's Box                                                           20   \n",
       "The Broadway Melody                                                     28   \n",
       "Hell's Angels                                                           12   \n",
       "A Farewell to Arms                                                     164   \n",
       "42nd Street                                                            105   \n",
       "She Done Him Wrong                                                      85   \n",
       "\n",
       "                                                  Facebook Likes - Actor 3  \\\n",
       "Title                                                                        \n",
       "Intolerance: Love's Struggle Throughout the Ages                       9.0   \n",
       "Over the Hill to the Poorhouse                                         0.0   \n",
       "The Big Parade                                                         6.0   \n",
       "Metropolis                                                            18.0   \n",
       "Pandora's Box                                                          3.0   \n",
       "The Broadway Melody                                                    4.0   \n",
       "Hell's Angels                                                          4.0   \n",
       "A Farewell to Arms                                                    99.0   \n",
       "42nd Street                                                           45.0   \n",
       "She Done Him Wrong                                                    28.0   \n",
       "\n",
       "                                                  Facebook Likes - cast Total  \\\n",
       "Title                                                                           \n",
       "Intolerance: Love's Struggle Throughout the Ages                          481   \n",
       "Over the Hill to the Poorhouse                                              4   \n",
       "The Big Parade                                                            108   \n",
       "Metropolis                                                                203   \n",
       "Pandora's Box                                                             455   \n",
       "The Broadway Melody                                                       109   \n",
       "Hell's Angels                                                             457   \n",
       "A Farewell to Arms                                                       1284   \n",
       "42nd Street                                                               995   \n",
       "She Done Him Wrong                                                        583   \n",
       "\n",
       "                                                   Facebook likes - Movie  \\\n",
       "Title                                                                       \n",
       "Intolerance: Love's Struggle Throughout the Ages                      691   \n",
       "Over the Hill to the Poorhouse                                          0   \n",
       "The Big Parade                                                        226   \n",
       "Metropolis                                                          12000   \n",
       "Pandora's Box                                                         926   \n",
       "The Broadway Melody                                                   167   \n",
       "Hell's Angels                                                         279   \n",
       "A Farewell to Arms                                                    213   \n",
       "42nd Street                                                           439   \n",
       "She Done Him Wrong                                                    328   \n",
       "\n",
       "                                                   Facenumber in posters  \\\n",
       "Title                                                                      \n",
       "Intolerance: Love's Struggle Throughout the Ages                       1   \n",
       "Over the Hill to the Poorhouse                                         1   \n",
       "The Big Parade                                                         0   \n",
       "Metropolis                                                             1   \n",
       "Pandora's Box                                                          1   \n",
       "The Broadway Melody                                                    8   \n",
       "Hell's Angels                                                          1   \n",
       "A Farewell to Arms                                                     1   \n",
       "42nd Street                                                            2   \n",
       "She Done Him Wrong                                                     1   \n",
       "\n",
       "                                                   User Votes  \\\n",
       "Title                                                           \n",
       "Intolerance: Love's Struggle Throughout the Ages        10718   \n",
       "Over the Hill to the Poorhouse                              5   \n",
       "The Big Parade                                           4849   \n",
       "Metropolis                                             111841   \n",
       "Pandora's Box                                            7431   \n",
       "The Broadway Melody                                      4546   \n",
       "Hell's Angels                                            3753   \n",
       "A Farewell to Arms                                       3519   \n",
       "42nd Street                                              7921   \n",
       "She Done Him Wrong                                       4152   \n",
       "\n",
       "                                                   Reviews by Users  \\\n",
       "Title                                                                 \n",
       "Intolerance: Love's Struggle Throughout the Ages                 88   \n",
       "Over the Hill to the Poorhouse                                    1   \n",
       "The Big Parade                                                   45   \n",
       "Metropolis                                                      413   \n",
       "Pandora's Box                                                    84   \n",
       "The Broadway Melody                                              71   \n",
       "Hell's Angels                                                    53   \n",
       "A Farewell to Arms                                               46   \n",
       "42nd Street                                                      97   \n",
       "She Done Him Wrong                                               59   \n",
       "\n",
       "                                                   Reviews by Crtiics  \\\n",
       "Title                                                                   \n",
       "Intolerance: Love's Struggle Throughout the Ages                 69.0   \n",
       "Over the Hill to the Poorhouse                                    1.0   \n",
       "The Big Parade                                                   48.0   \n",
       "Metropolis                                                      260.0   \n",
       "Pandora's Box                                                    71.0   \n",
       "The Broadway Melody                                              36.0   \n",
       "Hell's Angels                                                    35.0   \n",
       "A Farewell to Arms                                               42.0   \n",
       "42nd Street                                                      65.0   \n",
       "She Done Him Wrong                                               35.0   \n",
       "\n",
       "                                                   IMDB Score  Unnamed: 25  \n",
       "Title                                                                       \n",
       "Intolerance: Love's Struggle Throughout the Ages          8.0          NaN  \n",
       "Over the Hill to the Poorhouse                            4.8          NaN  \n",
       "The Big Parade                                            8.3          NaN  \n",
       "Metropolis                                                8.3          NaN  \n",
       "Pandora's Box                                             8.0          NaN  \n",
       "The Broadway Melody                                       6.3          NaN  \n",
       "Hell's Angels                                             7.8          NaN  \n",
       "A Farewell to Arms                                        6.6          NaN  \n",
       "42nd Street                                               7.7          NaN  \n",
       "She Done Him Wrong                                        6.5          NaN  \n",
       "\n",
       "[10 rows x 25 columns]"
      ]
     },
     "execution_count": 49,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "movies.head(10)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 50,
   "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>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>Director</th>\n",
       "      <th>...</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",
       "      <th>Unnamed: 25</th>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Title</th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>Wild Wild West</th>\n",
       "      <td>1999</td>\n",
       "      <td>Action|Comedy|Sci-Fi|Western</td>\n",
       "      <td>English</td>\n",
       "      <td>USA</td>\n",
       "      <td>PG-13</td>\n",
       "      <td>106</td>\n",
       "      <td>1.85</td>\n",
       "      <td>170000000.0</td>\n",
       "      <td>113745408.0</td>\n",
       "      <td>Barry Sonnenfeld</td>\n",
       "      <td>...</td>\n",
       "      <td>4000</td>\n",
       "      <td>582.0</td>\n",
       "      <td>15870</td>\n",
       "      <td>0</td>\n",
       "      <td>2</td>\n",
       "      <td>129601</td>\n",
       "      <td>648</td>\n",
       "      <td>85.0</td>\n",
       "      <td>4.8</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Wing Commander</th>\n",
       "      <td>1999</td>\n",
       "      <td>Action|Adventure|Sci-Fi</td>\n",
       "      <td>English</td>\n",
       "      <td>USA</td>\n",
       "      <td>PG-13</td>\n",
       "      <td>100</td>\n",
       "      <td>2.35</td>\n",
       "      <td>30000000.0</td>\n",
       "      <td>11576087.0</td>\n",
       "      <td>Chris Roberts</td>\n",
       "      <td>...</td>\n",
       "      <td>586</td>\n",
       "      <td>362.0</td>\n",
       "      <td>2497</td>\n",
       "      <td>858</td>\n",
       "      <td>3</td>\n",
       "      <td>14747</td>\n",
       "      <td>338</td>\n",
       "      <td>85.0</td>\n",
       "      <td>4.1</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "<p>2 rows × 25 columns</p>\n",
       "</div>"
      ],
      "text/plain": [
       "                 Year                        Genres Language Country  \\\n",
       "Title                                                                  \n",
       "Wild Wild West   1999  Action|Comedy|Sci-Fi|Western  English     USA   \n",
       "Wing Commander   1999       Action|Adventure|Sci-Fi  English     USA   \n",
       "\n",
       "                Content Rating  Duration  Aspect Ratio       Budget  \\\n",
       "Title                                                                 \n",
       "Wild Wild West           PG-13       106          1.85  170000000.0   \n",
       "Wing Commander           PG-13       100          2.35   30000000.0   \n",
       "\n",
       "                 Gross Earnings          Director  ...  \\\n",
       "Title                                              ...   \n",
       "Wild Wild West      113745408.0  Barry Sonnenfeld  ...   \n",
       "Wing Commander       11576087.0     Chris Roberts  ...   \n",
       "\n",
       "                Facebook Likes - Actor 2 Facebook Likes - Actor 3  \\\n",
       "Title                                                               \n",
       "Wild Wild West                      4000                    582.0   \n",
       "Wing Commander                       586                    362.0   \n",
       "\n",
       "                Facebook Likes - cast Total  Facebook likes - Movie  \\\n",
       "Title                                                                 \n",
       "Wild Wild West                        15870                       0   \n",
       "Wing Commander                         2497                     858   \n",
       "\n",
       "                 Facenumber in posters  User Votes  Reviews by Users  \\\n",
       "Title                                                                  \n",
       "Wild Wild West                       2      129601               648   \n",
       "Wing Commander                       3       14747               338   \n",
       "\n",
       "                 Reviews by Crtiics  IMDB Score  Unnamed: 25  \n",
       "Title                                                         \n",
       "Wild Wild West                 85.0         4.8          NaN  \n",
       "Wing Commander                 85.0         4.1          NaN  \n",
       "\n",
       "[2 rows x 25 columns]"
      ]
     },
     "execution_count": 50,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "movies.tail(2)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "And individual rows, which can be indexed by Name"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 51,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "Year                                                   1999\n",
       "Genres                         Action|Comedy|Sci-Fi|Western\n",
       "Language                                            English\n",
       "Country                                                 USA\n",
       "Content Rating                                        PG-13\n",
       "Duration                                                106\n",
       "Aspect Ratio                                           1.85\n",
       "Budget                                              1.7e+08\n",
       "Gross Earnings                                  1.13745e+08\n",
       "Director                                   Barry Sonnenfeld\n",
       "Actor 1                                          Will Smith\n",
       "Actor 2                                         Salma Hayek\n",
       "Actor 3                                            Bai Ling\n",
       "Facebook Likes - Director                               188\n",
       "Facebook Likes - Actor 1                              10000\n",
       "Facebook Likes - Actor 2                               4000\n",
       "Facebook Likes - Actor 3                                582\n",
       "Facebook Likes - cast Total                           15870\n",
       "Facebook likes - Movie                                    0\n",
       "Facenumber in posters                                     2\n",
       "User Votes                                           129601\n",
       "Reviews by Users                                        648\n",
       "Reviews by Crtiics                                       85\n",
       "IMDB Score                                              4.8\n",
       "Unnamed: 25                                             NaN\n",
       "Name: Wild Wild West , dtype: object"
      ]
     },
     "execution_count": 51,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "movies.loc[\"Wild Wild West\\xa0\"]"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Or by position"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 52,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "Year                                                   1999\n",
       "Genres                         Action|Comedy|Sci-Fi|Western\n",
       "Language                                            English\n",
       "Country                                                 USA\n",
       "Content Rating                                        PG-13\n",
       "Duration                                                106\n",
       "Aspect Ratio                                           1.85\n",
       "Budget                                              1.7e+08\n",
       "Gross Earnings                                  1.13745e+08\n",
       "Director                                   Barry Sonnenfeld\n",
       "Actor 1                                          Will Smith\n",
       "Actor 2                                         Salma Hayek\n",
       "Actor 3                                            Bai Ling\n",
       "Facebook Likes - Director                               188\n",
       "Facebook Likes - Actor 1                              10000\n",
       "Facebook Likes - Actor 2                               4000\n",
       "Facebook Likes - Actor 3                                582\n",
       "Facebook Likes - cast Total                           15870\n",
       "Facebook likes - Movie                                    0\n",
       "Facenumber in posters                                     2\n",
       "User Votes                                           129601\n",
       "Reviews by Users                                        648\n",
       "Reviews by Crtiics                                       85\n",
       "IMDB Score                                              4.8\n",
       "Unnamed: 25                                             NaN\n",
       "Name: Wild Wild West , dtype: object"
      ]
     },
     "execution_count": 52,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "movies.iloc[1336]"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Rows behave a named tuples, so you can access individual elements by position:"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 53,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "'Will Smith'"
      ]
     },
     "execution_count": 53,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "movies.iloc[1336, 10]"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Or by name"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 54,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "170000000.0"
      ]
     },
     "execution_count": 54,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "movies.iloc[1336].Budget"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 55,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "'Barry Sonnenfeld'"
      ]
     },
     "execution_count": 55,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "movies.loc['Wild Wild West\\xa0', 'Director']"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Ranges can also be used with iloc"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 56,
   "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>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>Director</th>\n",
       "      <th>...</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",
       "      <th>Unnamed: 25</th>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Title</th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>Over the Hill to the Poorhouse</th>\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>Harry F. Millarde</td>\n",
       "      <td>...</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",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>The Big Parade</th>\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>King Vidor</td>\n",
       "      <td>...</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",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Metropolis</th>\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>Fritz Lang</td>\n",
       "      <td>...</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",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "<p>3 rows × 25 columns</p>\n",
       "</div>"
      ],
      "text/plain": [
       "                                 Year             Genres Language  Country  \\\n",
       "Title                                                                        \n",
       "Over the Hill to the Poorhouse   1920        Crime|Drama      NaN      USA   \n",
       "The Big Parade                   1925  Drama|Romance|War      NaN      USA   \n",
       "Metropolis                       1927       Drama|Sci-Fi   German  Germany   \n",
       "\n",
       "                                Content Rating  Duration  Aspect Ratio  \\\n",
       "Title                                                                    \n",
       "Over the Hill to the Poorhouse             NaN       110          1.33   \n",
       "The Big Parade                       Not Rated       151          1.33   \n",
       "Metropolis                           Not Rated       145          1.33   \n",
       "\n",
       "                                    Budget  Gross Earnings           Director  \\\n",
       "Title                                                                           \n",
       "Over the Hill to the Poorhouse    100000.0       3000000.0  Harry F. Millarde   \n",
       "The Big Parade                    245000.0             NaN         King Vidor   \n",
       "Metropolis                       6000000.0         26435.0         Fritz Lang   \n",
       "\n",
       "                                 ... Facebook Likes - Actor 2  \\\n",
       "Title                            ...                            \n",
       "Over the Hill to the Poorhouse   ...                        2   \n",
       "The Big Parade                   ...                       12   \n",
       "Metropolis                       ...                       23   \n",
       "\n",
       "                                Facebook Likes - Actor 3  \\\n",
       "Title                                                      \n",
       "Over the Hill to the Poorhouse                       0.0   \n",
       "The Big Parade                                       6.0   \n",
       "Metropolis                                          18.0   \n",
       "\n",
       "                                Facebook Likes - cast Total  \\\n",
       "Title                                                         \n",
       "Over the Hill to the Poorhouse                            4   \n",
       "The Big Parade                                          108   \n",
       "Metropolis                                              203   \n",
       "\n",
       "                                 Facebook likes - Movie  \\\n",
       "Title                                                     \n",
       "Over the Hill to the Poorhouse                        0   \n",
       "The Big Parade                                      226   \n",
       "Metropolis                                        12000   \n",
       "\n",
       "                                 Facenumber in posters  User Votes  \\\n",
       "Title                                                                \n",
       "Over the Hill to the Poorhouse                       1           5   \n",
       "The Big Parade                                       0        4849   \n",
       "Metropolis                                           1      111841   \n",
       "\n",
       "                                 Reviews by Users  Reviews by Crtiics  \\\n",
       "Title                                                                   \n",
       "Over the Hill to the Poorhouse                  1                 1.0   \n",
       "The Big Parade                                 45                48.0   \n",
       "Metropolis                                    413               260.0   \n",
       "\n",
       "                                 IMDB Score  Unnamed: 25  \n",
       "Title                                                     \n",
       "Over the Hill to the Poorhouse          4.8          NaN  \n",
       "The Big Parade                          8.3          NaN  \n",
       "Metropolis                              8.3          NaN  \n",
       "\n",
       "[3 rows x 25 columns]"
      ]
     },
     "execution_count": 56,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "movies.iloc[1:4]"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "And __loc__ with the important difference that loc automatically __includes__ the last value of the range, while iloc does not"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 57,
   "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>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>Director</th>\n",
       "      <th>...</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",
       "      <th>Unnamed: 25</th>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Title</th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>Over the Hill to the Poorhouse</th>\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>Harry F. Millarde</td>\n",
       "      <td>...</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",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>The Big Parade</th>\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>King Vidor</td>\n",
       "      <td>...</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",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Metropolis</th>\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>Fritz Lang</td>\n",
       "      <td>...</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",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "<p>3 rows × 25 columns</p>\n",
       "</div>"
      ],
      "text/plain": [
       "                                 Year             Genres Language  Country  \\\n",
       "Title                                                                        \n",
       "Over the Hill to the Poorhouse   1920        Crime|Drama      NaN      USA   \n",
       "The Big Parade                   1925  Drama|Romance|War      NaN      USA   \n",
       "Metropolis                       1927       Drama|Sci-Fi   German  Germany   \n",
       "\n",
       "                                Content Rating  Duration  Aspect Ratio  \\\n",
       "Title                                                                    \n",
       "Over the Hill to the Poorhouse             NaN       110          1.33   \n",
       "The Big Parade                       Not Rated       151          1.33   \n",
       "Metropolis                           Not Rated       145          1.33   \n",
       "\n",
       "                                    Budget  Gross Earnings           Director  \\\n",
       "Title                                                                           \n",
       "Over the Hill to the Poorhouse    100000.0       3000000.0  Harry F. Millarde   \n",
       "The Big Parade                    245000.0             NaN         King Vidor   \n",
       "Metropolis                       6000000.0         26435.0         Fritz Lang   \n",
       "\n",
       "                                 ... Facebook Likes - Actor 2  \\\n",
       "Title                            ...                            \n",
       "Over the Hill to the Poorhouse   ...                        2   \n",
       "The Big Parade                   ...                       12   \n",
       "Metropolis                       ...                       23   \n",
       "\n",
       "                                Facebook Likes - Actor 3  \\\n",
       "Title                                                      \n",
       "Over the Hill to the Poorhouse                       0.0   \n",
       "The Big Parade                                       6.0   \n",
       "Metropolis                                          18.0   \n",
       "\n",
       "                                Facebook Likes - cast Total  \\\n",
       "Title                                                         \n",
       "Over the Hill to the Poorhouse                            4   \n",
       "The Big Parade                                          108   \n",
       "Metropolis                                              203   \n",
       "\n",
       "                                 Facebook likes - Movie  \\\n",
       "Title                                                     \n",
       "Over the Hill to the Poorhouse                        0   \n",
       "The Big Parade                                      226   \n",
       "Metropolis                                        12000   \n",
       "\n",
       "                                 Facenumber in posters  User Votes  \\\n",
       "Title                                                                \n",
       "Over the Hill to the Poorhouse                       1           5   \n",
       "The Big Parade                                       0        4849   \n",
       "Metropolis                                           1      111841   \n",
       "\n",
       "                                 Reviews by Users  Reviews by Crtiics  \\\n",
       "Title                                                                   \n",
       "Over the Hill to the Poorhouse                  1                 1.0   \n",
       "The Big Parade                                 45                48.0   \n",
       "Metropolis                                    413               260.0   \n",
       "\n",
       "                                 IMDB Score  Unnamed: 25  \n",
       "Title                                                     \n",
       "Over the Hill to the Poorhouse          4.8          NaN  \n",
       "The Big Parade                          8.3          NaN  \n",
       "Metropolis                              8.3          NaN  \n",
       "\n",
       "[3 rows x 25 columns]"
      ]
     },
     "execution_count": 57,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "movies.loc[\"Over the Hill to the Poorhouse\\xa0\":\"Metropolis\\xa0\"]"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Since each column is just a numpy array, we can easily manipulate the values and create new columns"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 58,
   "metadata": {},
   "outputs": [],
   "source": [
    "movies['Budget2'] = movies.Budget + 42"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 59,
   "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>Budget</th>\n",
       "      <th>Budget2</th>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Title</th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>Intolerance: Love's Struggle Throughout the Ages</th>\n",
       "      <td>385907.0</td>\n",
       "      <td>385949.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Over the Hill to the Poorhouse</th>\n",
       "      <td>100000.0</td>\n",
       "      <td>100042.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>The Big Parade</th>\n",
       "      <td>245000.0</td>\n",
       "      <td>245042.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Metropolis</th>\n",
       "      <td>6000000.0</td>\n",
       "      <td>6000042.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Pandora's Box</th>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>...</th>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Twin Falls Idaho</th>\n",
       "      <td>500000.0</td>\n",
       "      <td>500042.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Universal Soldier: The Return</th>\n",
       "      <td>24000000.0</td>\n",
       "      <td>24000042.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Varsity Blues</th>\n",
       "      <td>16000000.0</td>\n",
       "      <td>16000042.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Wild Wild West</th>\n",
       "      <td>170000000.0</td>\n",
       "      <td>170000042.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Wing Commander</th>\n",
       "      <td>30000000.0</td>\n",
       "      <td>30000042.0</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "<p>1338 rows × 2 columns</p>\n",
       "</div>"
      ],
      "text/plain": [
       "                                                        Budget      Budget2\n",
       "Title                                                                      \n",
       "Intolerance: Love's Struggle Throughout the Ages      385907.0     385949.0\n",
       "Over the Hill to the Poorhouse                        100000.0     100042.0\n",
       "The Big Parade                                        245000.0     245042.0\n",
       "Metropolis                                           6000000.0    6000042.0\n",
       "Pandora's Box                                              NaN          NaN\n",
       "...                                                        ...          ...\n",
       "Twin Falls Idaho                                      500000.0     500042.0\n",
       "Universal Soldier: The Return                       24000000.0   24000042.0\n",
       "Varsity Blues                                       16000000.0   16000042.0\n",
       "Wild Wild West                                     170000000.0  170000042.0\n",
       "Wing Commander                                      30000000.0   30000042.0\n",
       "\n",
       "[1338 rows x 2 columns]"
      ]
     },
     "execution_count": 59,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "movies[['Budget', 'Budget2']]"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "We can also append new rows to the dataframe. Since we have 3 sheets that all follow the same format, we can just stack them together using __concat__:"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 60,
   "metadata": {},
   "outputs": [],
   "source": [
    "df2 = pd.concat([movies, movies_2000])"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 61,
   "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>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>Director</th>\n",
       "      <th>...</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",
       "      <th>Unnamed: 25</th>\n",
       "      <th>Budget2</th>\n",
       "      <th>Title</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>Intolerance: Love's Struggle Throughout the Ages</th>\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.0</td>\n",
       "      <td>1.33</td>\n",
       "      <td>385907.0</td>\n",
       "      <td>NaN</td>\n",
       "      <td>D.W. Griffith</td>\n",
       "      <td>...</td>\n",
       "      <td>481</td>\n",
       "      <td>691</td>\n",
       "      <td>1</td>\n",
       "      <td>10718</td>\n",
       "      <td>88.0</td>\n",
       "      <td>69.0</td>\n",
       "      <td>8.0</td>\n",
       "      <td>NaN</td>\n",
       "      <td>385949.0</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Over the Hill to the Poorhouse</th>\n",
       "      <td>1920</td>\n",
       "      <td>Crime|Drama</td>\n",
       "      <td>NaN</td>\n",
       "      <td>USA</td>\n",
       "      <td>NaN</td>\n",
       "      <td>110.0</td>\n",
       "      <td>1.33</td>\n",
       "      <td>100000.0</td>\n",
       "      <td>3000000.0</td>\n",
       "      <td>Harry F. Millarde</td>\n",
       "      <td>...</td>\n",
       "      <td>4</td>\n",
       "      <td>0</td>\n",
       "      <td>1</td>\n",
       "      <td>5</td>\n",
       "      <td>1.0</td>\n",
       "      <td>1.0</td>\n",
       "      <td>4.8</td>\n",
       "      <td>NaN</td>\n",
       "      <td>100042.0</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>The Big Parade</th>\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.0</td>\n",
       "      <td>1.33</td>\n",
       "      <td>245000.0</td>\n",
       "      <td>NaN</td>\n",
       "      <td>King Vidor</td>\n",
       "      <td>...</td>\n",
       "      <td>108</td>\n",
       "      <td>226</td>\n",
       "      <td>0</td>\n",
       "      <td>4849</td>\n",
       "      <td>45.0</td>\n",
       "      <td>48.0</td>\n",
       "      <td>8.3</td>\n",
       "      <td>NaN</td>\n",
       "      <td>245042.0</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Metropolis</th>\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.0</td>\n",
       "      <td>1.33</td>\n",
       "      <td>6000000.0</td>\n",
       "      <td>26435.0</td>\n",
       "      <td>Fritz Lang</td>\n",
       "      <td>...</td>\n",
       "      <td>203</td>\n",
       "      <td>12000</td>\n",
       "      <td>1</td>\n",
       "      <td>111841</td>\n",
       "      <td>413.0</td>\n",
       "      <td>260.0</td>\n",
       "      <td>8.3</td>\n",
       "      <td>NaN</td>\n",
       "      <td>6000042.0</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Pandora's Box</th>\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.0</td>\n",
       "      <td>1.33</td>\n",
       "      <td>NaN</td>\n",
       "      <td>9950.0</td>\n",
       "      <td>Georg Wilhelm Pabst</td>\n",
       "      <td>...</td>\n",
       "      <td>455</td>\n",
       "      <td>926</td>\n",
       "      <td>1</td>\n",
       "      <td>7431</td>\n",
       "      <td>84.0</td>\n",
       "      <td>71.0</td>\n",
       "      <td>8.0</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</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",
       "      <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",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2095</th>\n",
       "      <td>2009</td>\n",
       "      <td>Action|Adventure|Fantasy|Sci-Fi|Thriller</td>\n",
       "      <td>English</td>\n",
       "      <td>USA</td>\n",
       "      <td>PG-13</td>\n",
       "      <td>119.0</td>\n",
       "      <td>2.35</td>\n",
       "      <td>150000000.0</td>\n",
       "      <td>179883016.0</td>\n",
       "      <td>Gavin Hood</td>\n",
       "      <td>...</td>\n",
       "      <td>40054</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>361924</td>\n",
       "      <td>641.0</td>\n",
       "      <td>350.0</td>\n",
       "      <td>6.7</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>X-Men Origins: Wolverine</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2096</th>\n",
       "      <td>2009</td>\n",
       "      <td>Adventure|Comedy</td>\n",
       "      <td>English</td>\n",
       "      <td>USA</td>\n",
       "      <td>PG-13</td>\n",
       "      <td>100.0</td>\n",
       "      <td>1.85</td>\n",
       "      <td>60000000.0</td>\n",
       "      <td>43337279.0</td>\n",
       "      <td>Harold Ramis</td>\n",
       "      <td>...</td>\n",
       "      <td>12258</td>\n",
       "      <td>0</td>\n",
       "      <td>2</td>\n",
       "      <td>76770</td>\n",
       "      <td>203.0</td>\n",
       "      <td>170.0</td>\n",
       "      <td>4.9</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>Year One</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2097</th>\n",
       "      <td>2009</td>\n",
       "      <td>Comedy|Drama|Romance</td>\n",
       "      <td>English</td>\n",
       "      <td>USA</td>\n",
       "      <td>R</td>\n",
       "      <td>90.0</td>\n",
       "      <td>1.85</td>\n",
       "      <td>18000000.0</td>\n",
       "      <td>15281286.0</td>\n",
       "      <td>Miguel Arteta</td>\n",
       "      <td>...</td>\n",
       "      <td>16004</td>\n",
       "      <td>0</td>\n",
       "      <td>2</td>\n",
       "      <td>64646</td>\n",
       "      <td>105.0</td>\n",
       "      <td>192.0</td>\n",
       "      <td>6.5</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>Youth in Revolt</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2098</th>\n",
       "      <td>2009</td>\n",
       "      <td>Comedy|Horror|Sci-Fi</td>\n",
       "      <td>English</td>\n",
       "      <td>USA</td>\n",
       "      <td>R</td>\n",
       "      <td>89.0</td>\n",
       "      <td>1.85</td>\n",
       "      <td>500000.0</td>\n",
       "      <td>NaN</td>\n",
       "      <td>Kevin Hamedani</td>\n",
       "      <td>...</td>\n",
       "      <td>292</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>3650</td>\n",
       "      <td>39.0</td>\n",
       "      <td>64.0</td>\n",
       "      <td>5.1</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>ZMD: Zombies of Mass Destruction</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2099</th>\n",
       "      <td>2009</td>\n",
       "      <td>Adventure|Comedy|Horror|Sci-Fi</td>\n",
       "      <td>English</td>\n",
       "      <td>USA</td>\n",
       "      <td>R</td>\n",
       "      <td>88.0</td>\n",
       "      <td>2.35</td>\n",
       "      <td>23600000.0</td>\n",
       "      <td>75590286.0</td>\n",
       "      <td>Ruben Fleischer</td>\n",
       "      <td>...</td>\n",
       "      <td>28011</td>\n",
       "      <td>26000</td>\n",
       "      <td>4</td>\n",
       "      <td>386217</td>\n",
       "      <td>553.0</td>\n",
       "      <td>445.0</td>\n",
       "      <td>7.7</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>Zombieland</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "<p>3438 rows × 27 columns</p>\n",
       "</div>"
      ],
      "text/plain": [
       "                                                   Year  \\\n",
       "Intolerance: Love's Struggle Throughout the Ages   1916   \n",
       "Over the Hill to the Poorhouse                     1920   \n",
       "The Big Parade                                     1925   \n",
       "Metropolis                                         1927   \n",
       "Pandora's Box                                      1929   \n",
       "...                                                 ...   \n",
       "2095                                               2009   \n",
       "2096                                               2009   \n",
       "2097                                               2009   \n",
       "2098                                               2009   \n",
       "2099                                               2009   \n",
       "\n",
       "                                                                                     Genres  \\\n",
       "Intolerance: Love's Struggle Throughout the Ages                          Drama|History|War   \n",
       "Over the Hill to the Poorhouse                                                  Crime|Drama   \n",
       "The Big Parade                                                            Drama|Romance|War   \n",
       "Metropolis                                                                     Drama|Sci-Fi   \n",
       "Pandora's Box                                                           Crime|Drama|Romance   \n",
       "...                                                                                     ...   \n",
       "2095                                               Action|Adventure|Fantasy|Sci-Fi|Thriller   \n",
       "2096                                                                       Adventure|Comedy   \n",
       "2097                                                                   Comedy|Drama|Romance   \n",
       "2098                                                                   Comedy|Horror|Sci-Fi   \n",
       "2099                                                         Adventure|Comedy|Horror|Sci-Fi   \n",
       "\n",
       "                                                  Language  Country  \\\n",
       "Intolerance: Love's Struggle Throughout the Ages       NaN      USA   \n",
       "Over the Hill to the Poorhouse                         NaN      USA   \n",
       "The Big Parade                                         NaN      USA   \n",
       "Metropolis                                          German  Germany   \n",
       "Pandora's Box                                       German  Germany   \n",
       "...                                                    ...      ...   \n",
       "2095                                               English      USA   \n",
       "2096                                               English      USA   \n",
       "2097                                               English      USA   \n",
       "2098                                               English      USA   \n",
       "2099                                               English      USA   \n",
       "\n",
       "                                                  Content Rating  Duration  \\\n",
       "Intolerance: Love's Struggle Throughout the Ages       Not Rated     123.0   \n",
       "Over the Hill to the Poorhouse                               NaN     110.0   \n",
       "The Big Parade                                         Not Rated     151.0   \n",
       "Metropolis                                             Not Rated     145.0   \n",
       "Pandora's Box                                          Not Rated     110.0   \n",
       "...                                                          ...       ...   \n",
       "2095                                                       PG-13     119.0   \n",
       "2096                                                       PG-13     100.0   \n",
       "2097                                                           R      90.0   \n",
       "2098                                                           R      89.0   \n",
       "2099                                                           R      88.0   \n",
       "\n",
       "                                                   Aspect Ratio       Budget  \\\n",
       "Intolerance: Love's Struggle Throughout the Ages           1.33     385907.0   \n",
       "Over the Hill to the Poorhouse                             1.33     100000.0   \n",
       "The Big Parade                                             1.33     245000.0   \n",
       "Metropolis                                                 1.33    6000000.0   \n",
       "Pandora's Box                                              1.33          NaN   \n",
       "...                                                         ...          ...   \n",
       "2095                                                       2.35  150000000.0   \n",
       "2096                                                       1.85   60000000.0   \n",
       "2097                                                       1.85   18000000.0   \n",
       "2098                                                       1.85     500000.0   \n",
       "2099                                                       2.35   23600000.0   \n",
       "\n",
       "                                                   Gross Earnings  \\\n",
       "Intolerance: Love's Struggle Throughout the Ages              NaN   \n",
       "Over the Hill to the Poorhouse                          3000000.0   \n",
       "The Big Parade                                                NaN   \n",
       "Metropolis                                                26435.0   \n",
       "Pandora's Box                                              9950.0   \n",
       "...                                                           ...   \n",
       "2095                                                  179883016.0   \n",
       "2096                                                   43337279.0   \n",
       "2097                                                   15281286.0   \n",
       "2098                                                          NaN   \n",
       "2099                                                   75590286.0   \n",
       "\n",
       "                                                              Director  ...  \\\n",
       "Intolerance: Love's Struggle Throughout the Ages         D.W. Griffith  ...   \n",
       "Over the Hill to the Poorhouse                       Harry F. Millarde  ...   \n",
       "The Big Parade                                              King Vidor  ...   \n",
       "Metropolis                                                  Fritz Lang  ...   \n",
       "Pandora's Box                                      Georg Wilhelm Pabst  ...   \n",
       "...                                                                ...  ...   \n",
       "2095                                                        Gavin Hood  ...   \n",
       "2096                                                      Harold Ramis  ...   \n",
       "2097                                                     Miguel Arteta  ...   \n",
       "2098                                                    Kevin Hamedani  ...   \n",
       "2099                                                   Ruben Fleischer  ...   \n",
       "\n",
       "                                                  Facebook Likes - cast Total  \\\n",
       "Intolerance: Love's Struggle Throughout the Ages                          481   \n",
       "Over the Hill to the Poorhouse                                              4   \n",
       "The Big Parade                                                            108   \n",
       "Metropolis                                                                203   \n",
       "Pandora's Box                                                             455   \n",
       "...                                                                       ...   \n",
       "2095                                                                    40054   \n",
       "2096                                                                    12258   \n",
       "2097                                                                    16004   \n",
       "2098                                                                      292   \n",
       "2099                                                                    28011   \n",
       "\n",
       "                                                  Facebook likes - Movie  \\\n",
       "Intolerance: Love's Struggle Throughout the Ages                     691   \n",
       "Over the Hill to the Poorhouse                                         0   \n",
       "The Big Parade                                                       226   \n",
       "Metropolis                                                         12000   \n",
       "Pandora's Box                                                        926   \n",
       "...                                                                  ...   \n",
       "2095                                                                   0   \n",
       "2096                                                                   0   \n",
       "2097                                                                   0   \n",
       "2098                                                                   0   \n",
       "2099                                                               26000   \n",
       "\n",
       "                                                  Facenumber in posters  \\\n",
       "Intolerance: Love's Struggle Throughout the Ages                      1   \n",
       "Over the Hill to the Poorhouse                                        1   \n",
       "The Big Parade                                                        0   \n",
       "Metropolis                                                            1   \n",
       "Pandora's Box                                                         1   \n",
       "...                                                                 ...   \n",
       "2095                                                                  0   \n",
       "2096                                                                  2   \n",
       "2097                                                                  2   \n",
       "2098                                                                  0   \n",
       "2099                                                                  4   \n",
       "\n",
       "                                                   User Votes  \\\n",
       "Intolerance: Love's Struggle Throughout the Ages        10718   \n",
       "Over the Hill to the Poorhouse                              5   \n",
       "The Big Parade                                           4849   \n",
       "Metropolis                                             111841   \n",
       "Pandora's Box                                            7431   \n",
       "...                                                       ...   \n",
       "2095                                                   361924   \n",
       "2096                                                    76770   \n",
       "2097                                                    64646   \n",
       "2098                                                     3650   \n",
       "2099                                                   386217   \n",
       "\n",
       "                                                   Reviews by Users  \\\n",
       "Intolerance: Love's Struggle Throughout the Ages               88.0   \n",
       "Over the Hill to the Poorhouse                                  1.0   \n",
       "The Big Parade                                                 45.0   \n",
       "Metropolis                                                    413.0   \n",
       "Pandora's Box                                                  84.0   \n",
       "...                                                             ...   \n",
       "2095                                                          641.0   \n",
       "2096                                                          203.0   \n",
       "2097                                                          105.0   \n",
       "2098                                                           39.0   \n",
       "2099                                                          553.0   \n",
       "\n",
       "                                                   Reviews by Crtiics  \\\n",
       "Intolerance: Love's Struggle Throughout the Ages                 69.0   \n",
       "Over the Hill to the Poorhouse                                    1.0   \n",
       "The Big Parade                                                   48.0   \n",
       "Metropolis                                                      260.0   \n",
       "Pandora's Box                                                    71.0   \n",
       "...                                                               ...   \n",
       "2095                                                            350.0   \n",
       "2096                                                            170.0   \n",
       "2097                                                            192.0   \n",
       "2098                                                             64.0   \n",
       "2099                                                            445.0   \n",
       "\n",
       "                                                   IMDB Score  Unnamed: 25  \\\n",
       "Intolerance: Love's Struggle Throughout the Ages          8.0          NaN   \n",
       "Over the Hill to the Poorhouse                            4.8          NaN   \n",
       "The Big Parade                                            8.3          NaN   \n",
       "Metropolis                                                8.3          NaN   \n",
       "Pandora's Box                                             8.0          NaN   \n",
       "...                                                       ...          ...   \n",
       "2095                                                      6.7          NaN   \n",
       "2096                                                      4.9          NaN   \n",
       "2097                                                      6.5          NaN   \n",
       "2098                                                      5.1          NaN   \n",
       "2099                                                      7.7          NaN   \n",
       "\n",
       "                                                     Budget2  \\\n",
       "Intolerance: Love's Struggle Throughout the Ages    385949.0   \n",
       "Over the Hill to the Poorhouse                      100042.0   \n",
       "The Big Parade                                      245042.0   \n",
       "Metropolis                                         6000042.0   \n",
       "Pandora's Box                                            NaN   \n",
       "...                                                      ...   \n",
       "2095                                                     NaN   \n",
       "2096                                                     NaN   \n",
       "2097                                                     NaN   \n",
       "2098                                                     NaN   \n",
       "2099                                                     NaN   \n",
       "\n",
       "                                                                               Title  \n",
       "Intolerance: Love's Struggle Throughout the Ages                                 NaN  \n",
       "Over the Hill to the Poorhouse                                                   NaN  \n",
       "The Big Parade                                                                   NaN  \n",
       "Metropolis                                                                       NaN  \n",
       "Pandora's Box                                                                    NaN  \n",
       "...                                                                              ...  \n",
       "2095                                                       X-Men Origins: Wolverine   \n",
       "2096                                                                       Year One   \n",
       "2097                                                                Youth in Revolt   \n",
       "2098                                               ZMD: Zombies of Mass Destruction   \n",
       "2099                                                                     Zombieland   \n",
       "\n",
       "[3438 rows x 27 columns]"
      ]
     },
     "execution_count": 61,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df2"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "__concat__ can also be used to place two DataFrames side by side"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 62,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "(1338, 26)"
      ]
     },
     "execution_count": 62,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "movies.shape"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 63,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "(1338, 52)"
      ]
     },
     "execution_count": 63,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "pd.concat([movies, movies], axis=1).shape"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## Time Series"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Apple stock information from https://finance.yahoo.com/quote/AAPL/history"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "We can automatically convert the Date column using __pd.read_csv__:"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 64,
   "metadata": {},
   "outputs": [],
   "source": [
    "data = pd.read_csv('data/AAPL.csv', parse_dates=['Date'])"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 65,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "Date         datetime64[ns]\n",
       "Open                float64\n",
       "High                float64\n",
       "Low                 float64\n",
       "Close               float64\n",
       "Adj Close           float64\n",
       "Volume              float64\n",
       "dtype: object"
      ]
     },
     "execution_count": 65,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "data.dtypes"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "If we now set the Date column to be the index, we effectively create our first Time Series"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 66,
   "metadata": {},
   "outputs": [],
   "source": [
    "data.set_index('Date', inplace=True)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "We see that pandas automatically generated a \"DatetimeIndex\" object that allos us to take advantage of the fact that we are dealing with dates"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 67,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "<class 'pandas.core.frame.DataFrame'>\n",
      "DatetimeIndex: 9887 entries, 1980-12-12 to 2020-02-28\n",
      "Data columns (total 6 columns):\n",
      " #   Column     Non-Null Count  Dtype  \n",
      "---  ------     --------------  -----  \n",
      " 0   Open       9886 non-null   float64\n",
      " 1   High       9886 non-null   float64\n",
      " 2   Low        9886 non-null   float64\n",
      " 3   Close      9886 non-null   float64\n",
      " 4   Adj Close  9886 non-null   float64\n",
      " 5   Volume     9886 non-null   float64\n",
      "dtypes: float64(6)\n",
      "memory usage: 540.7 KB\n"
     ]
    }
   ],
   "source": [
    "data.info()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "We can easily access parts of the date object"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 68,
   "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>Open</th>\n",
       "      <th>High</th>\n",
       "      <th>Low</th>\n",
       "      <th>Close</th>\n",
       "      <th>Adj Close</th>\n",
       "      <th>Volume</th>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Date</th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>1980-12-12</th>\n",
       "      <td>0.513393</td>\n",
       "      <td>0.515625</td>\n",
       "      <td>0.513393</td>\n",
       "      <td>0.513393</td>\n",
       "      <td>0.406782</td>\n",
       "      <td>117258400.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1980-12-15</th>\n",
       "      <td>0.488839</td>\n",
       "      <td>0.488839</td>\n",
       "      <td>0.486607</td>\n",
       "      <td>0.486607</td>\n",
       "      <td>0.385558</td>\n",
       "      <td>43971200.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1980-12-16</th>\n",
       "      <td>0.453125</td>\n",
       "      <td>0.453125</td>\n",
       "      <td>0.450893</td>\n",
       "      <td>0.450893</td>\n",
       "      <td>0.357260</td>\n",
       "      <td>26432000.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1980-12-17</th>\n",
       "      <td>0.462054</td>\n",
       "      <td>0.464286</td>\n",
       "      <td>0.462054</td>\n",
       "      <td>0.462054</td>\n",
       "      <td>0.366103</td>\n",
       "      <td>21610400.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1980-12-18</th>\n",
       "      <td>0.475446</td>\n",
       "      <td>0.477679</td>\n",
       "      <td>0.475446</td>\n",
       "      <td>0.475446</td>\n",
       "      <td>0.376715</td>\n",
       "      <td>18362400.0</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",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2020-02-24</th>\n",
       "      <td>297.260010</td>\n",
       "      <td>304.179993</td>\n",
       "      <td>289.230011</td>\n",
       "      <td>298.179993</td>\n",
       "      <td>298.179993</td>\n",
       "      <td>55548800.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2020-02-25</th>\n",
       "      <td>300.950012</td>\n",
       "      <td>302.529999</td>\n",
       "      <td>286.130005</td>\n",
       "      <td>288.079987</td>\n",
       "      <td>288.079987</td>\n",
       "      <td>57668400.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2020-02-26</th>\n",
       "      <td>286.529999</td>\n",
       "      <td>297.880005</td>\n",
       "      <td>286.500000</td>\n",
       "      <td>292.649994</td>\n",
       "      <td>292.649994</td>\n",
       "      <td>49513700.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2020-02-27</th>\n",
       "      <td>281.100006</td>\n",
       "      <td>286.000000</td>\n",
       "      <td>272.959991</td>\n",
       "      <td>273.519989</td>\n",
       "      <td>273.519989</td>\n",
       "      <td>80151400.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2020-02-28</th>\n",
       "      <td>257.260010</td>\n",
       "      <td>278.410004</td>\n",
       "      <td>256.369995</td>\n",
       "      <td>273.359985</td>\n",
       "      <td>273.359985</td>\n",
       "      <td>106627500.0</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "<p>9887 rows × 6 columns</p>\n",
       "</div>"
      ],
      "text/plain": [
       "                  Open        High         Low       Close   Adj Close  \\\n",
       "Date                                                                     \n",
       "1980-12-12    0.513393    0.515625    0.513393    0.513393    0.406782   \n",
       "1980-12-15    0.488839    0.488839    0.486607    0.486607    0.385558   \n",
       "1980-12-16    0.453125    0.453125    0.450893    0.450893    0.357260   \n",
       "1980-12-17    0.462054    0.464286    0.462054    0.462054    0.366103   \n",
       "1980-12-18    0.475446    0.477679    0.475446    0.475446    0.376715   \n",
       "...                ...         ...         ...         ...         ...   \n",
       "2020-02-24  297.260010  304.179993  289.230011  298.179993  298.179993   \n",
       "2020-02-25  300.950012  302.529999  286.130005  288.079987  288.079987   \n",
       "2020-02-26  286.529999  297.880005  286.500000  292.649994  292.649994   \n",
       "2020-02-27  281.100006  286.000000  272.959991  273.519989  273.519989   \n",
       "2020-02-28  257.260010  278.410004  256.369995  273.359985  273.359985   \n",
       "\n",
       "                 Volume  \n",
       "Date                     \n",
       "1980-12-12  117258400.0  \n",
       "1980-12-15   43971200.0  \n",
       "1980-12-16   26432000.0  \n",
       "1980-12-17   21610400.0  \n",
       "1980-12-18   18362400.0  \n",
       "...                 ...  \n",
       "2020-02-24   55548800.0  \n",
       "2020-02-25   57668400.0  \n",
       "2020-02-26   49513700.0  \n",
       "2020-02-27   80151400.0  \n",
       "2020-02-28  106627500.0  \n",
       "\n",
       "[9887 rows x 6 columns]"
      ]
     },
     "execution_count": 68,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "data"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 69,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "Int64Index([12, 12, 12, 12, 12, 12, 12, 12, 12, 12,\n",
       "            ...\n",
       "             2,  2,  2,  2,  2,  2,  2,  2,  2,  2],\n",
       "           dtype='int64', name='Date', length=9887)"
      ]
     },
     "execution_count": 69,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "data.index.month"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 70,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "Int64Index([1980, 1980, 1980, 1980, 1980, 1980, 1980, 1980, 1980, 1980,\n",
       "            ...\n",
       "            2020, 2020, 2020, 2020, 2020, 2020, 2020, 2020, 2020, 2020],\n",
       "           dtype='int64', name='Date', length=9887)"
      ]
     },
     "execution_count": 70,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "data.index.year"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 71,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "Int64Index([12, 15, 16, 17, 18, 19, 22, 23, 24, 26,\n",
       "            ...\n",
       "            14, 18, 19, 20, 21, 24, 25, 26, 27, 28],\n",
       "           dtype='int64', name='Date', length=9887)"
      ]
     },
     "execution_count": 71,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "data.index.day"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "And slice the DataFrame by date"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 72,
   "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>Open</th>\n",
       "      <th>High</th>\n",
       "      <th>Low</th>\n",
       "      <th>Close</th>\n",
       "      <th>Adj Close</th>\n",
       "      <th>Volume</th>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Date</th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>2010-01-04</th>\n",
       "      <td>30.49</td>\n",
       "      <td>30.64</td>\n",
       "      <td>30.34</td>\n",
       "      <td>30.57</td>\n",
       "      <td>26.54</td>\n",
       "      <td>123432400.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2010-01-05</th>\n",
       "      <td>30.66</td>\n",
       "      <td>30.80</td>\n",
       "      <td>30.46</td>\n",
       "      <td>30.63</td>\n",
       "      <td>26.58</td>\n",
       "      <td>150476200.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2010-01-06</th>\n",
       "      <td>30.63</td>\n",
       "      <td>30.75</td>\n",
       "      <td>30.11</td>\n",
       "      <td>30.14</td>\n",
       "      <td>26.16</td>\n",
       "      <td>138040000.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2010-01-07</th>\n",
       "      <td>30.25</td>\n",
       "      <td>30.29</td>\n",
       "      <td>29.86</td>\n",
       "      <td>30.08</td>\n",
       "      <td>26.11</td>\n",
       "      <td>119282800.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2010-01-08</th>\n",
       "      <td>30.04</td>\n",
       "      <td>30.29</td>\n",
       "      <td>29.87</td>\n",
       "      <td>30.28</td>\n",
       "      <td>26.29</td>\n",
       "      <td>111902700.0</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",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2010-06-04</th>\n",
       "      <td>36.89</td>\n",
       "      <td>37.41</td>\n",
       "      <td>36.38</td>\n",
       "      <td>36.57</td>\n",
       "      <td>31.74</td>\n",
       "      <td>189576100.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2010-06-07</th>\n",
       "      <td>36.90</td>\n",
       "      <td>37.02</td>\n",
       "      <td>35.79</td>\n",
       "      <td>35.85</td>\n",
       "      <td>31.12</td>\n",
       "      <td>221735500.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2010-06-08</th>\n",
       "      <td>36.18</td>\n",
       "      <td>36.26</td>\n",
       "      <td>35.09</td>\n",
       "      <td>35.62</td>\n",
       "      <td>30.92</td>\n",
       "      <td>250192600.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2010-06-09</th>\n",
       "      <td>35.92</td>\n",
       "      <td>35.99</td>\n",
       "      <td>34.64</td>\n",
       "      <td>34.74</td>\n",
       "      <td>30.16</td>\n",
       "      <td>213657500.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2010-06-10</th>\n",
       "      <td>34.98</td>\n",
       "      <td>35.85</td>\n",
       "      <td>34.60</td>\n",
       "      <td>35.79</td>\n",
       "      <td>31.06</td>\n",
       "      <td>194089000.0</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "<p>110 rows × 6 columns</p>\n",
       "</div>"
      ],
      "text/plain": [
       "             Open   High    Low  Close  Adj Close       Volume\n",
       "Date                                                          \n",
       "2010-01-04  30.49  30.64  30.34  30.57      26.54  123432400.0\n",
       "2010-01-05  30.66  30.80  30.46  30.63      26.58  150476200.0\n",
       "2010-01-06  30.63  30.75  30.11  30.14      26.16  138040000.0\n",
       "2010-01-07  30.25  30.29  29.86  30.08      26.11  119282800.0\n",
       "2010-01-08  30.04  30.29  29.87  30.28      26.29  111902700.0\n",
       "...           ...    ...    ...    ...        ...          ...\n",
       "2010-06-04  36.89  37.41  36.38  36.57      31.74  189576100.0\n",
       "2010-06-07  36.90  37.02  35.79  35.85      31.12  221735500.0\n",
       "2010-06-08  36.18  36.26  35.09  35.62      30.92  250192600.0\n",
       "2010-06-09  35.92  35.99  34.64  34.74      30.16  213657500.0\n",
       "2010-06-10  34.98  35.85  34.60  35.79      31.06  194089000.0\n",
       "\n",
       "[110 rows x 6 columns]"
      ]
     },
     "execution_count": 72,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "data.loc['2010':'2010-06-10'].round(2)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "As before, we note that the last value is also included"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## DataFrame Manipulations"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Map allows us to easily apply a function to the rows of a Series."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 73,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "Title\n",
       "Intolerance: Love's Struggle Throughout the Ages           D.W. Griffith\n",
       "Over the Hill to the Poorhouse                         Harry F. Millarde\n",
       "The Big Parade                                                King Vidor\n",
       "Metropolis                                                    Fritz Lang\n",
       "Pandora's Box                                        Georg Wilhelm Pabst\n",
       "Name: Director, dtype: object"
      ]
     },
     "execution_count": 73,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "movies['Director'].head()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 74,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "Title\n",
       "Intolerance: Love's Struggle Throughout the Ages           d.w. griffith\n",
       "Over the Hill to the Poorhouse                         harry f. millarde\n",
       "The Big Parade                                                king vidor\n",
       "Metropolis                                                    fritz lang\n",
       "Pandora's Box                                        georg wilhelm pabst\n",
       "                                                            ...         \n",
       "Twin Falls Idaho                                          michael polish\n",
       "Universal Soldier: The Return                                mic rodgers\n",
       "Varsity Blues                                              brian robbins\n",
       "Wild Wild West                                          barry sonnenfeld\n",
       "Wing Commander                                             chris roberts\n",
       "Name: Director, Length: 1338, dtype: object"
      ]
     },
     "execution_count": 74,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "movies['Director'].map(lambda x: x.lower())"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "For a dataframe we need to use transform"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 75,
   "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>Duration</th>\n",
       "      <th>Budget</th>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Title</th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>Intolerance: Love's Struggle Throughout the Ages</th>\n",
       "      <td>123</td>\n",
       "      <td>385907.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Over the Hill to the Poorhouse</th>\n",
       "      <td>110</td>\n",
       "      <td>100000.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>The Big Parade</th>\n",
       "      <td>151</td>\n",
       "      <td>245000.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Metropolis</th>\n",
       "      <td>145</td>\n",
       "      <td>6000000.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Pandora's Box</th>\n",
       "      <td>110</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "                                                   Duration     Budget\n",
       "Title                                                                 \n",
       "Intolerance: Love's Struggle Throughout the Ages        123   385907.0\n",
       "Over the Hill to the Poorhouse                          110   100000.0\n",
       "The Big Parade                                          151   245000.0\n",
       "Metropolis                                              145  6000000.0\n",
       "Pandora's Box                                           110        NaN"
      ]
     },
     "execution_count": 75,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "movies[['Duration', 'Budget']].head()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 76,
   "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>Duration</th>\n",
       "      <th>Budget</th>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Title</th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>Intolerance: Love's Struggle Throughout the Ages</th>\n",
       "      <td>246</td>\n",
       "      <td>771814.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Over the Hill to the Poorhouse</th>\n",
       "      <td>220</td>\n",
       "      <td>200000.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>The Big Parade</th>\n",
       "      <td>302</td>\n",
       "      <td>490000.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Metropolis</th>\n",
       "      <td>290</td>\n",
       "      <td>12000000.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Pandora's Box</th>\n",
       "      <td>220</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>...</th>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Twin Falls Idaho</th>\n",
       "      <td>222</td>\n",
       "      <td>1000000.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Universal Soldier: The Return</th>\n",
       "      <td>166</td>\n",
       "      <td>48000000.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Varsity Blues</th>\n",
       "      <td>212</td>\n",
       "      <td>32000000.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Wild Wild West</th>\n",
       "      <td>212</td>\n",
       "      <td>340000000.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Wing Commander</th>\n",
       "      <td>200</td>\n",
       "      <td>60000000.0</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "<p>1338 rows × 2 columns</p>\n",
       "</div>"
      ],
      "text/plain": [
       "                                                   Duration       Budget\n",
       "Title                                                                   \n",
       "Intolerance: Love's Struggle Throughout the Ages        246     771814.0\n",
       "Over the Hill to the Poorhouse                          220     200000.0\n",
       "The Big Parade                                          302     490000.0\n",
       "Metropolis                                              290   12000000.0\n",
       "Pandora's Box                                           220          NaN\n",
       "...                                                     ...          ...\n",
       "Twin Falls Idaho                                        222    1000000.0\n",
       "Universal Soldier: The Return                           166   48000000.0\n",
       "Varsity Blues                                           212   32000000.0\n",
       "Wild Wild West                                          212  340000000.0\n",
       "Wing Commander                                          200   60000000.0\n",
       "\n",
       "[1338 rows x 2 columns]"
      ]
     },
     "execution_count": 76,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "movies[['Duration', 'Budget']].transform(lambda x: x * 2)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Or apply"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 77,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "Duration    1.512890e+05\n",
       "Budget      3.474154e+10\n",
       "dtype: float64"
      ]
     },
     "execution_count": 77,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "movies[['Duration', 'Budget']].apply(np.sum, axis=0)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 78,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "Title\n",
       "Intolerance: Love's Struggle Throughout the Ages        386030.0\n",
       "Over the Hill to the Poorhouse                          100110.0\n",
       "The Big Parade                                          245151.0\n",
       "Metropolis                                             6000145.0\n",
       "Pandora's Box                                              110.0\n",
       "                                                        ...     \n",
       "Twin Falls Idaho                                        500111.0\n",
       "Universal Soldier: The Return                         24000083.0\n",
       "Varsity Blues                                         16000106.0\n",
       "Wild Wild West                                       170000106.0\n",
       "Wing Commander                                        30000100.0\n",
       "Length: 1338, dtype: float64"
      ]
     },
     "execution_count": 78,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "movies[['Duration', 'Budget']].apply(np.sum, axis=1)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## Merge and Join"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Define 2 toy DataFrames"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 79,
   "metadata": {},
   "outputs": [],
   "source": [
    "A = pd.DataFrame({\"lkey\":  [\"foo\", \"bar\", \"baz\", \"foo\"], \n",
    "                  \"value\": [1,     2,     3,     4]})\n",
    "\n",
    "B = pd.DataFrame({\"rkey\":  [\"foo\", \"bar\", \"qux\", \"bar\"], \n",
    "                  \"value\": [5,     6,     7,     8]})"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Merge allows us to join them by specifying an arbitrary column on each of them"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 80,
   "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>lkey</th>\n",
       "      <th>value_x</th>\n",
       "      <th>rkey</th>\n",
       "      <th>value_y</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>foo</td>\n",
       "      <td>1.0</td>\n",
       "      <td>foo</td>\n",
       "      <td>5</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>foo</td>\n",
       "      <td>4.0</td>\n",
       "      <td>foo</td>\n",
       "      <td>5</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>bar</td>\n",
       "      <td>2.0</td>\n",
       "      <td>bar</td>\n",
       "      <td>6</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>bar</td>\n",
       "      <td>2.0</td>\n",
       "      <td>bar</td>\n",
       "      <td>8</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>qux</td>\n",
       "      <td>7</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "  lkey  value_x rkey  value_y\n",
       "0  foo      1.0  foo        5\n",
       "1  foo      4.0  foo        5\n",
       "2  bar      2.0  bar        6\n",
       "3  bar      2.0  bar        8\n",
       "4  NaN      NaN  qux        7"
      ]
     },
     "execution_count": 80,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "A.merge(B, left_on=\"lkey\", right_on=\"rkey\", how=\"right\")"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "On the other hand, join performs the join using the respective Indices"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 81,
   "metadata": {},
   "outputs": [],
   "source": [
    "A.set_index('lkey', inplace=True)\n",
    "B.set_index('rkey', inplace=True)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 82,
   "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>value_l</th>\n",
       "      <th>value_r</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>bar</th>\n",
       "      <td>2</td>\n",
       "      <td>6</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>bar</th>\n",
       "      <td>2</td>\n",
       "      <td>8</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>foo</th>\n",
       "      <td>1</td>\n",
       "      <td>5</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>foo</th>\n",
       "      <td>4</td>\n",
       "      <td>5</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "     value_l  value_r\n",
       "bar        2        6\n",
       "bar        2        8\n",
       "foo        1        5\n",
       "foo        4        5"
      ]
     },
     "execution_count": 82,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "A.join(B, lsuffix=\"_l\", rsuffix=\"_r\", how=\"inner\")"
   ]
  },
  {
   "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
}