AcademyAwards.ipynb

{
 "cells": [
  {
   "cell_type": "code",
   "execution_count": 1,
   "metadata": {
    "collapsed": true
   },
   "outputs": [],
   "source": [
    "import pandas as pd\n",
    "import numpy as np\n",
    "import sqlite3"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 2,
   "metadata": {
    "collapsed": false
   },
   "outputs": [],
   "source": [
    "df = pd.read_csv('academy_awards.csv',encoding='ISO-8859-1')"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 3,
   "metadata": {
    "collapsed": false
   },
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "111507\n"
     ]
    },
    {
     "data": {
      "text/plain": [
       "Index(['Year', 'Category', 'Nominee', 'Additional Info', 'Won?', 'Unnamed: 5',\n",
       "       'Unnamed: 6', 'Unnamed: 7', 'Unnamed: 8', 'Unnamed: 9', 'Unnamed: 10'],\n",
       "      dtype='object')"
      ]
     },
     "execution_count": 3,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "print(df.size)\n",
    "df.columns"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 4,
   "metadata": {
    "collapsed": false
   },
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>Year</th>\n",
       "      <th>Category</th>\n",
       "      <th>Nominee</th>\n",
       "      <th>Additional Info</th>\n",
       "      <th>Won?</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>2010 (83rd)</td>\n",
       "      <td>Actor -- Leading Role</td>\n",
       "      <td>Javier Bardem</td>\n",
       "      <td>Biutiful {'Uxbal'}</td>\n",
       "      <td>NO</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>2010 (83rd)</td>\n",
       "      <td>Actor -- Leading Role</td>\n",
       "      <td>Jeff Bridges</td>\n",
       "      <td>True Grit {'Rooster Cogburn'}</td>\n",
       "      <td>NO</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>2010 (83rd)</td>\n",
       "      <td>Actor -- Leading Role</td>\n",
       "      <td>Jesse Eisenberg</td>\n",
       "      <td>The Social Network {'Mark Zuckerberg'}</td>\n",
       "      <td>NO</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>2010 (83rd)</td>\n",
       "      <td>Actor -- Leading Role</td>\n",
       "      <td>Colin Firth</td>\n",
       "      <td>The King's Speech {'King George VI'}</td>\n",
       "      <td>YES</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>2010 (83rd)</td>\n",
       "      <td>Actor -- Leading Role</td>\n",
       "      <td>James Franco</td>\n",
       "      <td>127 Hours {'Aron Ralston'}</td>\n",
       "      <td>NO</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>5</th>\n",
       "      <td>2010 (83rd)</td>\n",
       "      <td>Actor -- Supporting Role</td>\n",
       "      <td>Christian Bale</td>\n",
       "      <td>The Fighter {'Dicky Eklund'}</td>\n",
       "      <td>YES</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "          Year                  Category          Nominee  \\\n",
       "0  2010 (83rd)     Actor -- Leading Role    Javier Bardem   \n",
       "1  2010 (83rd)     Actor -- Leading Role     Jeff Bridges   \n",
       "2  2010 (83rd)     Actor -- Leading Role  Jesse Eisenberg   \n",
       "3  2010 (83rd)     Actor -- Leading Role      Colin Firth   \n",
       "4  2010 (83rd)     Actor -- Leading Role     James Franco   \n",
       "5  2010 (83rd)  Actor -- Supporting Role   Christian Bale   \n",
       "\n",
       "                          Additional Info Won?  \n",
       "0                      Biutiful {'Uxbal'}   NO  \n",
       "1           True Grit {'Rooster Cogburn'}   NO  \n",
       "2  The Social Network {'Mark Zuckerberg'}   NO  \n",
       "3    The King's Speech {'King George VI'}  YES  \n",
       "4              127 Hours {'Aron Ralston'}   NO  \n",
       "5            The Fighter {'Dicky Eklund'}  YES  "
      ]
     },
     "execution_count": 4,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df.iloc[:,:5].head(6)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 5,
   "metadata": {
    "collapsed": false
   },
   "outputs": [
    {
     "data": {
      "text/plain": [
       "*                                                                                                               0.636364\n",
       " error-prone measurements on sets. [Digital Imaging Technology]\"                                                0.090909\n",
       " D.B. \"Don\" Keele and Mark E. Engebretson has resulted in the over 20-year dominance of constant-directivity    0.090909\n",
       " discoverer of stars                                                                                            0.090909\n",
       " resilience                                                                                                     0.090909\n",
       "Name: Unnamed: 5, dtype: float64"
      ]
     },
     "execution_count": 5,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df.iloc[:,5].value_counts('NaN')"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 6,
   "metadata": {
    "collapsed": false
   },
   "outputs": [
    {
     "data": {
      "text/plain": [
       "*                                                                                                               7\n",
       " error-prone measurements on sets. [Digital Imaging Technology]\"                                                1\n",
       " D.B. \"Don\" Keele and Mark E. Engebretson has resulted in the over 20-year dominance of constant-directivity    1\n",
       " discoverer of stars                                                                                            1\n",
       " resilience                                                                                                     1\n",
       "Name: Unnamed: 5, dtype: int64"
      ]
     },
     "execution_count": 6,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df.iloc[:,5].value_counts()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 7,
   "metadata": {
    "collapsed": false
   },
   "outputs": [],
   "source": [
    "#Converting the Year column to int64 type after filtering out string exclosed by brackets.\n",
    "\n",
    "df['Year'] = df[\"Year\"].str[:4].astype(np.int64)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 8,
   "metadata": {
    "collapsed": false
   },
   "outputs": [],
   "source": [
    "#Selecting dates later than 2000\n",
    "later_than_2000  = df[df['Year']>2000]"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 9,
   "metadata": {
    "collapsed": false
   },
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "['Actor -- Leading Role' 'Actor -- Supporting Role'\n",
      " 'Actress -- Leading Role' 'Actress -- Supporting Role'\n",
      " 'Animated Feature Film' 'Art Direction' 'Cinematography' 'Costume Design'\n",
      " 'Directing' 'Documentary (Feature)' 'Documentary (Short Subject)'\n",
      " 'Film Editing' 'Foreign Language Film' 'Makeup' 'Music (Scoring)'\n",
      " 'Music (Song)' 'Best Picture' 'Short Film (Animated)'\n",
      " 'Short Film (Live Action)' 'Sound' 'Sound Editing' 'Visual Effects'\n",
      " 'Writing' 'Honorary Award' 'Irving G. Thalberg Memorial Award'\n",
      " 'Scientific and Technical (Scientific and Engineering Award)'\n",
      " 'Scientific and Technical (Technical Achievement Award)'\n",
      " 'Scientific and Technical (Bonner Medal)'\n",
      " 'Jean Hersholt Humanitarian Award'\n",
      " 'Scientific and Technical (Gordon E. Sawyer Award)'\n",
      " 'Scientific and Technical (Academy Award of Merit)'\n",
      " 'Scientific and Technical (Special Awards)']\n"
     ]
    },
    {
     "data": {
      "text/plain": [
       "array(['Actor -- Leading Role', 'Actor -- Supporting Role',\n",
       "       'Actress -- Leading Role', 'Actress -- Supporting Role'], \n",
       "      dtype='<U26')"
      ]
     },
     "execution_count": 9,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "#Looking up list of award categories and selecting the first four(most hyped) awards\n",
    "print(later_than_2000['Category'].unique())\n",
    "award_categories = later_than_2000['Category'].unique()[:4].astype(np.str)\n",
    "award_categories"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 10,
   "metadata": {
    "collapsed": false
   },
   "outputs": [],
   "source": [
    "#Selecting all rows from the dataframe that pertain to items in the award categories list.\n",
    "\n",
    "nominations = later_than_2000[later_than_2000['Category'].isin(award_categories)]"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 11,
   "metadata": {
    "collapsed": false
   },
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>Year</th>\n",
       "      <th>Category</th>\n",
       "      <th>Nominee</th>\n",
       "      <th>Additional Info</th>\n",
       "      <th>Won</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>2010</td>\n",
       "      <td>Actor -- Leading Role</td>\n",
       "      <td>Javier Bardem</td>\n",
       "      <td>Biutiful {'Uxbal'}</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>2010</td>\n",
       "      <td>Actor -- Leading Role</td>\n",
       "      <td>Jeff Bridges</td>\n",
       "      <td>True Grit {'Rooster Cogburn'}</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>2010</td>\n",
       "      <td>Actor -- Leading Role</td>\n",
       "      <td>Jesse Eisenberg</td>\n",
       "      <td>The Social Network {'Mark Zuckerberg'}</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>2010</td>\n",
       "      <td>Actor -- Leading Role</td>\n",
       "      <td>Colin Firth</td>\n",
       "      <td>The King's Speech {'King George VI'}</td>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>2010</td>\n",
       "      <td>Actor -- Leading Role</td>\n",
       "      <td>James Franco</td>\n",
       "      <td>127 Hours {'Aron Ralston'}</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "   Year               Category          Nominee  \\\n",
       "0  2010  Actor -- Leading Role    Javier Bardem   \n",
       "1  2010  Actor -- Leading Role     Jeff Bridges   \n",
       "2  2010  Actor -- Leading Role  Jesse Eisenberg   \n",
       "3  2010  Actor -- Leading Role      Colin Firth   \n",
       "4  2010  Actor -- Leading Role     James Franco   \n",
       "\n",
       "                          Additional Info  Won  \n",
       "0                      Biutiful {'Uxbal'}    0  \n",
       "1           True Grit {'Rooster Cogburn'}    0  \n",
       "2  The Social Network {'Mark Zuckerberg'}    0  \n",
       "3    The King's Speech {'King George VI'}    1  \n",
       "4              127 Hours {'Aron Ralston'}    0  "
      ]
     },
     "execution_count": 11,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "#Selecting first five columns\n",
    "\n",
    "nominations = nominations.iloc[:,:5]\n",
    "#Renaming the Won? column\n",
    "nominations.rename(columns={'Won?':'Won'},inplace=True)\n",
    "#Replacing the values in Won column with boolean values\n",
    "nominations.replace({\"YES\":1,\"NO\":0},inplace=True)\n",
    "\n",
    "final_nominations = nominations\n",
    "final_nominations.head(5)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 12,
   "metadata": {
    "collapsed": false
   },
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "<class 'pandas.core.series.Series'>\n"
     ]
    },
    {
     "data": {
      "text/plain": [
       "0               [Biutiful , 'Uxbal']\n",
       "1    [True Grit , 'Rooster Cogburn']\n",
       "Name: Additional Info, dtype: object"
      ]
     },
     "execution_count": 12,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "#Splitting the Additional Info column into movie names and charachters using vectorized string operations\n",
    "\n",
    "#Splitting on last }\n",
    "a = final_nominations['Additional Info'].str.rstrip('}')\n",
    "#Splitting on first {\n",
    "a = a.str.split('{')\n",
    "print(type(a))\n",
    "a.head(2)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 13,
   "metadata": {
    "collapsed": false
   },
   "outputs": [],
   "source": [
    "#Selecting the first and second element of each row.\n",
    "\n",
    "movie_names = a.str[0]\n",
    "charachters = a.str[1]"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 14,
   "metadata": {
    "collapsed": false
   },
   "outputs": [],
   "source": [
    "#Assigning the pd.series to a new column in the Dataframe\n",
    "final_nominations['Movies'] = movie_names\n",
    "final_nominations['Charachter'] = charachters\n",
    "\n",
    "#Dropping the old Additional info column\n",
    "final_nominations.drop('Additional Info',axis=1,inplace=True)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 16,
   "metadata": {
    "collapsed": false
   },
   "outputs": [],
   "source": [
    "#Exporting the data to a SQL database\n",
    "\n",
    "#Creating a new database file\n",
    "#!rm nominations.db\n",
    "conn = sqlite3.connect('nominations.db')\n",
    "\n",
    "final_nominations.to_sql('nominations',conn,index=False) #Not writing the dataframe index as a column"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 17,
   "metadata": {
    "collapsed": false
   },
   "outputs": [
    {
     "data": {
      "text/plain": [
       "[(0, 'id', 'integer', 0, None, 1),\n",
       " (1, 'category', 'text', 0, None, 0),\n",
       " (2, 'nominee', 'text', 0, None, 0),\n",
       " (3, 'movie', 'text', 0, None, 0),\n",
       " (4, 'charachter', 'text', 0, None, 0),\n",
       " (5, 'Won', 'integer', 0, None, 0),\n",
       " (6, 'ceremony_id', 'integer', 0, None, 0)]"
      ]
     },
     "execution_count": 17,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "f = conn.execute(\"pragma table_info(nominations)\")\n",
    "f.fetchall()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 18,
   "metadata": {
    "collapsed": false
   },
   "outputs": [
    {
     "data": {
      "text/plain": [
       "[(1, 'Actor -- Leading Role', 'Javier Bardem', 'Biutiful ', \"'Uxbal'\", 0, 1),\n",
       " (2,\n",
       "  'Actor -- Leading Role',\n",
       "  'Jeff Bridges',\n",
       "  'True Grit ',\n",
       "  \"'Rooster Cogburn'\",\n",
       "  0,\n",
       "  1),\n",
       " (3,\n",
       "  'Actor -- Leading Role',\n",
       "  'Jesse Eisenberg',\n",
       "  'The Social Network ',\n",
       "  \"'Mark Zuckerberg'\",\n",
       "  0,\n",
       "  1),\n",
       " (4,\n",
       "  'Actor -- Leading Role',\n",
       "  'Colin Firth',\n",
       "  \"The King's Speech \",\n",
       "  \"'King George VI'\",\n",
       "  1,\n",
       "  1),\n",
       " (5,\n",
       "  'Actor -- Leading Role',\n",
       "  'James Franco',\n",
       "  '127 Hours ',\n",
       "  \"'Aron Ralston'\",\n",
       "  0,\n",
       "  1)]"
      ]
     },
     "execution_count": 18,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "f = conn.execute(\"select * from nominations limit 5\")\n",
    "f.fetchall()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "collapsed": true
   },
   "outputs": [],
   "source": []
  }
 ],
 "metadata": {
  "anaconda-cloud": {},
  "kernelspec": {
   "display_name": "Python [py3]",
   "language": "python",
   "name": "Python [py3]"
  },
  "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.5.2"
  }
 },
 "nbformat": 4,
 "nbformat_minor": 0
}