eda.ipynb

{
 "cells": [
  {
   "cell_type": "code",
   "execution_count": 1,
   "metadata": {},
   "outputs": [],
   "source": [
    "import pandas as pd\n",
    "import numpy as np\n",
    "import re\n",
    "import json\n",
    "from data_models import Movie,User,Rating\n",
    "from connect_db import engine,base,Session\n",
    "import requests\n",
    "\n",
    "movies_df=pd.read_table('data/movies.dat','::',names=['title_year','genre'],index_col=0,engine='python')\n",
    "ratings_df=pd.read_table('data/ratings.dat','::',names=['UserID','MovieID','rating','timestamp'],engine='python')\n",
    "users_df=pd.read_table('data/users.dat','::',names=['gender','age','occupation','zipcode'],index_col=0,engine='python')"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 3,
   "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>genre</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <td>1</td>\n",
       "      <td>Toy Story (1995)</td>\n",
       "      <td>Animation|Children's|Comedy</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>2</td>\n",
       "      <td>Jumanji (1995)</td>\n",
       "      <td>Adventure|Children's|Fantasy</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>3</td>\n",
       "      <td>Grumpier Old Men (1995)</td>\n",
       "      <td>Comedy|Romance</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>4</td>\n",
       "      <td>Waiting to Exhale (1995)</td>\n",
       "      <td>Comedy|Drama</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>5</td>\n",
       "      <td>Father of the Bride Part II (1995)</td>\n",
       "      <td>Comedy</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>3948</td>\n",
       "      <td>Meet the Parents (2000)</td>\n",
       "      <td>Comedy</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>3949</td>\n",
       "      <td>Requiem for a Dream (2000)</td>\n",
       "      <td>Drama</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>3950</td>\n",
       "      <td>Tigerland (2000)</td>\n",
       "      <td>Drama</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>3951</td>\n",
       "      <td>Two Family House (2000)</td>\n",
       "      <td>Drama</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>3952</td>\n",
       "      <td>Contender, The (2000)</td>\n",
       "      <td>Drama|Thriller</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "<p>3883 rows × 2 columns</p>\n",
       "</div>"
      ],
      "text/plain": [
       "                                   title                         genre\n",
       "1                       Toy Story (1995)   Animation|Children's|Comedy\n",
       "2                         Jumanji (1995)  Adventure|Children's|Fantasy\n",
       "3                Grumpier Old Men (1995)                Comedy|Romance\n",
       "4               Waiting to Exhale (1995)                  Comedy|Drama\n",
       "5     Father of the Bride Part II (1995)                        Comedy\n",
       "...                                  ...                           ...\n",
       "3948             Meet the Parents (2000)                        Comedy\n",
       "3949          Requiem for a Dream (2000)                         Drama\n",
       "3950                    Tigerland (2000)                         Drama\n",
       "3951             Two Family House (2000)                         Drama\n",
       "3952               Contender, The (2000)                Drama|Thriller\n",
       "\n",
       "[3883 rows x 2 columns]"
      ]
     },
     "execution_count": 3,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "movies_df"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 2,
   "metadata": {},
   "outputs": [],
   "source": [
    "books_df=pd.read_csv('data/BX-Books.csv','\\\";\\\"',index_col=0,engine='python')\n",
    "\n",
    "cols=books_df.columns.tolist()\n",
    "\n",
    "cols[-1]='Image-URL-L'\n",
    "\n",
    "books_df.columns=cols\n",
    "\n",
    "books_df.index=[elem[1:] for elem in books_df.index]\n",
    "\n",
    "books_df['Image-URL-L']=[elem[:-1] for elem in books_df['Image-URL-L']]\n",
    "\n",
    "books_df.index.name='ISBN'\n",
    "\n",
    "book_ratings_df=pd.read_csv('data/BX-Book-Ratings.csv','\\\";\\\"',index_col=0,engine='python')\n",
    "\n",
    "book_ratings_df.columns=['ISBN','Book-Rating']\n",
    "\n",
    "book_ratings_df.index=[int(elem[1:]) for elem in book_ratings_df.index]\n",
    "\n",
    "book_ratings_df['Book-Rating']=[int(elem[:-1]) for elem in book_ratings_df['Book-Rating']]\n",
    "\n",
    "book_ratings_df.index.name='User-ID'\n",
    "\n",
    "book_users_df=pd.read_csv('data/BX-Users.csv','\\\";',index_col=0,engine='python')\n",
    "\n",
    "book_users_df.index=[int(elem[1:]) for elem in book_users_df.index]\n",
    "\n",
    "book_users_df.index.name='User-ID'\n",
    "\n",
    "book_users_df.columns=['Location','Age']\n",
    "\n",
    "book_users_df['Location']=[elem[1:] for elem in book_users_df['Location']]\n",
    "\n",
    "book_users_df['Age'].iloc[0] is np.nan\n",
    "\n",
    "book_users_df['Age']=[elem[1:-1] if elem is not np.nan else np.nan for elem in book_users_df['Age']]"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 137,
   "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>genre</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <td>count</td>\n",
       "      <td>3883</td>\n",
       "      <td>3883</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>unique</td>\n",
       "      <td>3883</td>\n",
       "      <td>301</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>top</td>\n",
       "      <td>Simple Twist of Fate, A (1994)</td>\n",
       "      <td>Drama</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>freq</td>\n",
       "      <td>1</td>\n",
       "      <td>843</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "                                 title  genre\n",
       "count                             3883   3883\n",
       "unique                            3883    301\n",
       "top     Simple Twist of Fate, A (1994)  Drama\n",
       "freq                                 1    843"
      ]
     },
     "execution_count": 137,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "movies_df.describe()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 146,
   "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>Book-Rating</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <td>count</td>\n",
       "      <td>1.149780e+06</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>mean</td>\n",
       "      <td>2.866950e+00</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>std</td>\n",
       "      <td>3.854184e+00</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>min</td>\n",
       "      <td>0.000000e+00</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>25%</td>\n",
       "      <td>0.000000e+00</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>50%</td>\n",
       "      <td>0.000000e+00</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>75%</td>\n",
       "      <td>7.000000e+00</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>max</td>\n",
       "      <td>1.000000e+01</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "        Book-Rating\n",
       "count  1.149780e+06\n",
       "mean   2.866950e+00\n",
       "std    3.854184e+00\n",
       "min    0.000000e+00\n",
       "25%    0.000000e+00\n",
       "50%    0.000000e+00\n",
       "75%    7.000000e+00\n",
       "max    1.000000e+01"
      ]
     },
     "execution_count": 146,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "book_ratings_df.describe()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 8,
   "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>gender</th>\n",
       "      <th>age</th>\n",
       "      <th>occupation</th>\n",
       "      <th>zip</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <td>count</td>\n",
       "      <td>6040</td>\n",
       "      <td>6040.000000</td>\n",
       "      <td>6040.000000</td>\n",
       "      <td>6040</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>unique</td>\n",
       "      <td>2</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>3439</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>top</td>\n",
       "      <td>M</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>48104</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>freq</td>\n",
       "      <td>4331</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>19</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>mean</td>\n",
       "      <td>NaN</td>\n",
       "      <td>30.639238</td>\n",
       "      <td>8.146854</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>std</td>\n",
       "      <td>NaN</td>\n",
       "      <td>12.895962</td>\n",
       "      <td>6.329511</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>min</td>\n",
       "      <td>NaN</td>\n",
       "      <td>1.000000</td>\n",
       "      <td>0.000000</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>25%</td>\n",
       "      <td>NaN</td>\n",
       "      <td>25.000000</td>\n",
       "      <td>3.000000</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>50%</td>\n",
       "      <td>NaN</td>\n",
       "      <td>25.000000</td>\n",
       "      <td>7.000000</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>75%</td>\n",
       "      <td>NaN</td>\n",
       "      <td>35.000000</td>\n",
       "      <td>14.000000</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>max</td>\n",
       "      <td>NaN</td>\n",
       "      <td>56.000000</td>\n",
       "      <td>20.000000</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "       gender          age   occupation    zip\n",
       "count    6040  6040.000000  6040.000000   6040\n",
       "unique      2          NaN          NaN   3439\n",
       "top         M          NaN          NaN  48104\n",
       "freq     4331          NaN          NaN     19\n",
       "mean      NaN    30.639238     8.146854    NaN\n",
       "std       NaN    12.895962     6.329511    NaN\n",
       "min       NaN     1.000000     0.000000    NaN\n",
       "25%       NaN    25.000000     3.000000    NaN\n",
       "50%       NaN    25.000000     7.000000    NaN\n",
       "75%       NaN    35.000000    14.000000    NaN\n",
       "max       NaN    56.000000    20.000000    NaN"
      ]
     },
     "execution_count": 8,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "users_df.describe(include='all')"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 3,
   "metadata": {},
   "outputs": [],
   "source": [
    "movies_df['year']=movies_df.title.str[-5:-1].astype(int)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 34,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "1       1995\n",
       "2       1995\n",
       "3       1995\n",
       "4       1995\n",
       "5       1995\n",
       "        ... \n",
       "3948    2000\n",
       "3949    2000\n",
       "3950    2000\n",
       "3951    2000\n",
       "3952    2000\n",
       "Name: year, Length: 3883, dtype: int64"
      ]
     },
     "execution_count": 34,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "movies_df.year"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 4,
   "metadata": {},
   "outputs": [],
   "source": [
    "movies_df['tt']=movies_df.title.str[:-7]"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 5,
   "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>genre</th>\n",
       "      <th>year</th>\n",
       "      <th>tt</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <td>1</td>\n",
       "      <td>Animation|Children's|Comedy</td>\n",
       "      <td>1995</td>\n",
       "      <td>Toy Story</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>2</td>\n",
       "      <td>Adventure|Children's|Fantasy</td>\n",
       "      <td>1995</td>\n",
       "      <td>Jumanji</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>3</td>\n",
       "      <td>Comedy|Romance</td>\n",
       "      <td>1995</td>\n",
       "      <td>Grumpier Old Men</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>4</td>\n",
       "      <td>Comedy|Drama</td>\n",
       "      <td>1995</td>\n",
       "      <td>Waiting to Exhale</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>5</td>\n",
       "      <td>Comedy</td>\n",
       "      <td>1995</td>\n",
       "      <td>Father of the Bride Part II</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>3948</td>\n",
       "      <td>Comedy</td>\n",
       "      <td>2000</td>\n",
       "      <td>Meet the Parents</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>3949</td>\n",
       "      <td>Drama</td>\n",
       "      <td>2000</td>\n",
       "      <td>Requiem for a Dream</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>3950</td>\n",
       "      <td>Drama</td>\n",
       "      <td>2000</td>\n",
       "      <td>Tigerland</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>3951</td>\n",
       "      <td>Drama</td>\n",
       "      <td>2000</td>\n",
       "      <td>Two Family House</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>3952</td>\n",
       "      <td>Drama|Thriller</td>\n",
       "      <td>2000</td>\n",
       "      <td>Contender, The</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "<p>3883 rows × 3 columns</p>\n",
       "</div>"
      ],
      "text/plain": [
       "                             genre  year                           tt\n",
       "1      Animation|Children's|Comedy  1995                    Toy Story\n",
       "2     Adventure|Children's|Fantasy  1995                      Jumanji\n",
       "3                   Comedy|Romance  1995             Grumpier Old Men\n",
       "4                     Comedy|Drama  1995            Waiting to Exhale\n",
       "5                           Comedy  1995  Father of the Bride Part II\n",
       "...                            ...   ...                          ...\n",
       "3948                        Comedy  2000             Meet the Parents\n",
       "3949                         Drama  2000          Requiem for a Dream\n",
       "3950                         Drama  2000                    Tigerland\n",
       "3951                         Drama  2000             Two Family House\n",
       "3952                Drama|Thriller  2000               Contender, The\n",
       "\n",
       "[3883 rows x 3 columns]"
      ]
     },
     "execution_count": 5,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "movies_df.drop('title',1)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 21,
   "metadata": {},
   "outputs": [],
   "source": [
    "from data_models import Movie"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 14,
   "metadata": {},
   "outputs": [],
   "source": [
    "import requests"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 15,
   "metadata": {},
   "outputs": [],
   "source": [
    "omdb='http://www.omdbapi.com'\n",
    "\n",
    "params={'i':'tt3896198','apikey':'f69c6afb','t':'Birthmarked','year':2018}\n",
    "\n",
    "result=requests.get(omdb,params)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 16,
   "metadata": {},
   "outputs": [],
   "source": [
    "data=result.json()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 17,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "{'Title': 'Birthmarked',\n",
       " 'Year': '2018',\n",
       " 'Rated': 'N/A',\n",
       " 'Released': '30 Mar 2018',\n",
       " 'Runtime': '90 min',\n",
       " 'Genre': 'Comedy',\n",
       " 'Director': 'Emanuel Hoss-Desmarais',\n",
       " 'Writer': 'Marc Tulin, Marc Tulin (story), Emanuel Hoss-Desmarais (story)',\n",
       " 'Actors': 'Matthew Goode, Toni Collette, Andreas Apergis, Jordan Poole',\n",
       " 'Plot': 'Two scientists raise 3 children contrarily to their genetic tendencies to prove the ultimate power of nurture over nature.',\n",
       " 'Language': 'English',\n",
       " 'Country': 'Canada',\n",
       " 'Awards': 'N/A',\n",
       " 'Poster': 'https://m.media-amazon.com/images/M/MV5BMTY3NzE5NzU2Nl5BMl5BanBnXkFtZTgwMTIzMzc4NDM@._V1_SX300.jpg',\n",
       " 'Ratings': [{'Source': 'Internet Movie Database', 'Value': '5.7/10'},\n",
       "  {'Source': 'Rotten Tomatoes', 'Value': '12%'},\n",
       "  {'Source': 'Metacritic', 'Value': '44/100'}],\n",
       " 'Metascore': '44',\n",
       " 'imdbRating': '5.7',\n",
       " 'imdbVotes': '2,273',\n",
       " 'imdbID': 'tt4702826',\n",
       " 'Type': 'movie',\n",
       " 'DVD': 'N/A',\n",
       " 'BoxOffice': 'N/A',\n",
       " 'Production': 'Vertical Entertainment',\n",
       " 'Website': 'N/A',\n",
       " 'Response': 'True'}"
      ]
     },
     "execution_count": 17,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "data"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 28,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "200"
      ]
     },
     "execution_count": 28,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "result.status_code"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 30,
   "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>genre</th>\n",
       "      <th>year</th>\n",
       "      <th>tt</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <td>1</td>\n",
       "      <td>Toy Story (1995)</td>\n",
       "      <td>Animation|Children's|Comedy</td>\n",
       "      <td>1995</td>\n",
       "      <td>Toy Story</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>2</td>\n",
       "      <td>Jumanji (1995)</td>\n",
       "      <td>Adventure|Children's|Fantasy</td>\n",
       "      <td>1995</td>\n",
       "      <td>Jumanji</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>3</td>\n",
       "      <td>Grumpier Old Men (1995)</td>\n",
       "      <td>Comedy|Romance</td>\n",
       "      <td>1995</td>\n",
       "      <td>Grumpier Old Men</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>4</td>\n",
       "      <td>Waiting to Exhale (1995)</td>\n",
       "      <td>Comedy|Drama</td>\n",
       "      <td>1995</td>\n",
       "      <td>Waiting to Exhale</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>5</td>\n",
       "      <td>Father of the Bride Part II (1995)</td>\n",
       "      <td>Comedy</td>\n",
       "      <td>1995</td>\n",
       "      <td>Father of the Bride Part II</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>3948</td>\n",
       "      <td>Meet the Parents (2000)</td>\n",
       "      <td>Comedy</td>\n",
       "      <td>2000</td>\n",
       "      <td>Meet the Parents</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>3949</td>\n",
       "      <td>Requiem for a Dream (2000)</td>\n",
       "      <td>Drama</td>\n",
       "      <td>2000</td>\n",
       "      <td>Requiem for a Dream</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>3950</td>\n",
       "      <td>Tigerland (2000)</td>\n",
       "      <td>Drama</td>\n",
       "      <td>2000</td>\n",
       "      <td>Tigerland</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>3951</td>\n",
       "      <td>Two Family House (2000)</td>\n",
       "      <td>Drama</td>\n",
       "      <td>2000</td>\n",
       "      <td>Two Family House</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>3952</td>\n",
       "      <td>Contender, The (2000)</td>\n",
       "      <td>Drama|Thriller</td>\n",
       "      <td>2000</td>\n",
       "      <td>Contender, The</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "<p>3883 rows × 4 columns</p>\n",
       "</div>"
      ],
      "text/plain": [
       "                                   title                         genre  year  \\\n",
       "1                       Toy Story (1995)   Animation|Children's|Comedy  1995   \n",
       "2                         Jumanji (1995)  Adventure|Children's|Fantasy  1995   \n",
       "3                Grumpier Old Men (1995)                Comedy|Romance  1995   \n",
       "4               Waiting to Exhale (1995)                  Comedy|Drama  1995   \n",
       "5     Father of the Bride Part II (1995)                        Comedy  1995   \n",
       "...                                  ...                           ...   ...   \n",
       "3948             Meet the Parents (2000)                        Comedy  2000   \n",
       "3949          Requiem for a Dream (2000)                         Drama  2000   \n",
       "3950                    Tigerland (2000)                         Drama  2000   \n",
       "3951             Two Family House (2000)                         Drama  2000   \n",
       "3952               Contender, The (2000)                Drama|Thriller  2000   \n",
       "\n",
       "                               tt  \n",
       "1                       Toy Story  \n",
       "2                         Jumanji  \n",
       "3                Grumpier Old Men  \n",
       "4               Waiting to Exhale  \n",
       "5     Father of the Bride Part II  \n",
       "...                           ...  \n",
       "3948             Meet the Parents  \n",
       "3949          Requiem for a Dream  \n",
       "3950                    Tigerland  \n",
       "3951             Two Family House  \n",
       "3952               Contender, The  \n",
       "\n",
       "[3883 rows x 4 columns]"
      ]
     },
     "execution_count": 30,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "movies_df"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 3,
   "metadata": {},
   "outputs": [],
   "source": [
    "from connect_db import engine"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 10,
   "metadata": {},
   "outputs": [],
   "source": [
    "users_df.to_sql('user',engine,if_exists='append',index_label='id',method='multi')"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 4,
   "metadata": {},
   "outputs": [],
   "source": [
    "conn=engine.connect()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 12,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "(6040, 4)"
      ]
     },
     "execution_count": 12,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "users_df.shape"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 18,
   "metadata": {},
   "outputs": [
    {
     "ename": "KeyError",
     "evalue": "0",
     "output_type": "error",
     "traceback": [
      "\u001b[0;31m---------------------------------------------------------------------------\u001b[0m",
      "\u001b[0;31mKeyError\u001b[0m                                  Traceback (most recent call last)",
      "\u001b[0;32m<ipython-input-18-80a4aab09c73>\u001b[0m in \u001b[0;36m<module>\u001b[0;34m\u001b[0m\n\u001b[0;32m----> 1\u001b[0;31m \u001b[0mdata\u001b[0m\u001b[0;34m[\u001b[0m\u001b[0;36m0\u001b[0m\u001b[0;34m]\u001b[0m\u001b[0;34m\u001b[0m\u001b[0;34m\u001b[0m\u001b[0m\n\u001b[0m",
      "\u001b[0;31mKeyError\u001b[0m: 0"
     ]
    }
   ],
   "source": [
    "data[0]"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 20,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "dict_keys(['Title', 'Year', 'Rated', 'Released', 'Runtime', 'Genre', 'Director', 'Writer', 'Actors', 'Plot', 'Language', 'Country', 'Awards', 'Poster', 'Ratings', 'Metascore', 'imdbRating', 'imdbVotes', 'imdbID', 'Type', 'DVD', 'BoxOffice', 'Production', 'Website', 'Response'])"
      ]
     },
     "execution_count": 20,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "data.keys()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 23,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "'Matthew'"
      ]
     },
     "execution_count": 23,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "data['Actors'].split()[0]"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 29,
   "metadata": {},
   "outputs": [],
   "source": [
    "movies_df_c=movies_df[900:1800].copy()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 28,
   "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>genre</th>\n",
       "      <th>year</th>\n",
       "      <th>title</th>\n",
       "      <th>title_len</th>\n",
       "      <th>genre_len</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <td>912</td>\n",
       "      <td>Drama|Romance|War</td>\n",
       "      <td>1942</td>\n",
       "      <td>Casablanca</td>\n",
       "      <td>10</td>\n",
       "      <td>17</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>913</td>\n",
       "      <td>Film-Noir|Mystery</td>\n",
       "      <td>1941</td>\n",
       "      <td>Maltese Falcon, The</td>\n",
       "      <td>19</td>\n",
       "      <td>17</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>914</td>\n",
       "      <td>Musical|Romance</td>\n",
       "      <td>1964</td>\n",
       "      <td>My Fair Lady</td>\n",
       "      <td>12</td>\n",
       "      <td>15</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>915</td>\n",
       "      <td>Comedy|Romance</td>\n",
       "      <td>1954</td>\n",
       "      <td>Sabrina</td>\n",
       "      <td>7</td>\n",
       "      <td>14</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>916</td>\n",
       "      <td>Comedy|Romance</td>\n",
       "      <td>1953</td>\n",
       "      <td>Roman Holiday</td>\n",
       "      <td>13</td>\n",
       "      <td>14</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>1864</td>\n",
       "      <td>Comedy</td>\n",
       "      <td>1998</td>\n",
       "      <td>Sour Grapes</td>\n",
       "      <td>11</td>\n",
       "      <td>6</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>1865</td>\n",
       "      <td>Documentary</td>\n",
       "      <td>1998</td>\n",
       "      <td>Wild Man Blues</td>\n",
       "      <td>14</td>\n",
       "      <td>11</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>1866</td>\n",
       "      <td>Action|Comedy</td>\n",
       "      <td>1998</td>\n",
       "      <td>Big Hit, The</td>\n",
       "      <td>12</td>\n",
       "      <td>13</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>1867</td>\n",
       "      <td>Action|Adventure</td>\n",
       "      <td>1998</td>\n",
       "      <td>Tarzan and the Lost City</td>\n",
       "      <td>24</td>\n",
       "      <td>16</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>1868</td>\n",
       "      <td>Drama|War</td>\n",
       "      <td>1996</td>\n",
       "      <td>Truce, The</td>\n",
       "      <td>10</td>\n",
       "      <td>9</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "<p>900 rows × 5 columns</p>\n",
       "</div>"
      ],
      "text/plain": [
       "                  genre  year                     title  title_len  genre_len\n",
       "912   Drama|Romance|War  1942                Casablanca         10         17\n",
       "913   Film-Noir|Mystery  1941       Maltese Falcon, The         19         17\n",
       "914     Musical|Romance  1964              My Fair Lady         12         15\n",
       "915      Comedy|Romance  1954                   Sabrina          7         14\n",
       "916      Comedy|Romance  1953             Roman Holiday         13         14\n",
       "...                 ...   ...                       ...        ...        ...\n",
       "1864             Comedy  1998               Sour Grapes         11          6\n",
       "1865        Documentary  1998            Wild Man Blues         14         11\n",
       "1866      Action|Comedy  1998              Big Hit, The         12         13\n",
       "1867   Action|Adventure  1998  Tarzan and the Lost City         24         16\n",
       "1868          Drama|War  1996                Truce, The         10          9\n",
       "\n",
       "[900 rows x 5 columns]"
      ]
     },
     "execution_count": 28,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "movies_df_c"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 7,
   "metadata": {},
   "outputs": [],
   "source": [
    "a=[9]*10"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 8,
   "metadata": {},
   "outputs": [],
   "source": [
    "movies_df_c['aaaa']=a"
   ]
  },
  {
   "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>title_year</th>\n",
       "      <th>genre</th>\n",
       "      <th>aaaa</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <td>1</td>\n",
       "      <td>Toy Story (1995)</td>\n",
       "      <td>Animation|Children's|Comedy</td>\n",
       "      <td>9</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>2</td>\n",
       "      <td>Jumanji (1995)</td>\n",
       "      <td>Adventure|Children's|Fantasy</td>\n",
       "      <td>9</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>3</td>\n",
       "      <td>Grumpier Old Men (1995)</td>\n",
       "      <td>Comedy|Romance</td>\n",
       "      <td>9</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>4</td>\n",
       "      <td>Waiting to Exhale (1995)</td>\n",
       "      <td>Comedy|Drama</td>\n",
       "      <td>9</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>5</td>\n",
       "      <td>Father of the Bride Part II (1995)</td>\n",
       "      <td>Comedy</td>\n",
       "      <td>9</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>6</td>\n",
       "      <td>Heat (1995)</td>\n",
       "      <td>Action|Crime|Thriller</td>\n",
       "      <td>9</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>7</td>\n",
       "      <td>Sabrina (1995)</td>\n",
       "      <td>Comedy|Romance</td>\n",
       "      <td>9</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>8</td>\n",
       "      <td>Tom and Huck (1995)</td>\n",
       "      <td>Adventure|Children's</td>\n",
       "      <td>9</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>9</td>\n",
       "      <td>Sudden Death (1995)</td>\n",
       "      <td>Action</td>\n",
       "      <td>9</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>10</td>\n",
       "      <td>GoldenEye (1995)</td>\n",
       "      <td>Action|Adventure|Thriller</td>\n",
       "      <td>9</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "                            title_year                         genre  aaaa\n",
       "1                     Toy Story (1995)   Animation|Children's|Comedy     9\n",
       "2                       Jumanji (1995)  Adventure|Children's|Fantasy     9\n",
       "3              Grumpier Old Men (1995)                Comedy|Romance     9\n",
       "4             Waiting to Exhale (1995)                  Comedy|Drama     9\n",
       "5   Father of the Bride Part II (1995)                        Comedy     9\n",
       "6                          Heat (1995)         Action|Crime|Thriller     9\n",
       "7                       Sabrina (1995)                Comedy|Romance     9\n",
       "8                  Tom and Huck (1995)          Adventure|Children's     9\n",
       "9                  Sudden Death (1995)                        Action     9\n",
       "10                    GoldenEye (1995)     Action|Adventure|Thriller     9"
      ]
     },
     "execution_count": 9,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "movies_df_c"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 10,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "(3883, 2)"
      ]
     },
     "execution_count": 10,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "movies_df.shape"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 11,
   "metadata": {},
   "outputs": [],
   "source": [
    "movies_df['year']=movies_df.title_year.str[-5:-1].astype(int)\n",
    "movies_df['title']=movies_df.title_year.str[:-7]\n",
    "movies_df.drop('title_year',1,inplace=True)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 17,
   "metadata": {},
   "outputs": [],
   "source": [
    "movies_df['title_len']=[len(title) for title in movies_df['title']]"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 18,
   "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>title_len</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <td>count</td>\n",
       "      <td>3883.000000</td>\n",
       "      <td>3883.000000</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>mean</td>\n",
       "      <td>1986.066959</td>\n",
       "      <td>17.202678</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>std</td>\n",
       "      <td>16.895690</td>\n",
       "      <td>9.951110</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>min</td>\n",
       "      <td>1919.000000</td>\n",
       "      <td>1.000000</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>25%</td>\n",
       "      <td>1982.000000</td>\n",
       "      <td>10.000000</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>50%</td>\n",
       "      <td>1994.000000</td>\n",
       "      <td>15.000000</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>75%</td>\n",
       "      <td>1997.000000</td>\n",
       "      <td>21.000000</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>max</td>\n",
       "      <td>2000.000000</td>\n",
       "      <td>75.000000</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "              year    title_len\n",
       "count  3883.000000  3883.000000\n",
       "mean   1986.066959    17.202678\n",
       "std      16.895690     9.951110\n",
       "min    1919.000000     1.000000\n",
       "25%    1982.000000    10.000000\n",
       "50%    1994.000000    15.000000\n",
       "75%    1997.000000    21.000000\n",
       "max    2000.000000    75.000000"
      ]
     },
     "execution_count": 18,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "movies_df.describe()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 21,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "11\n",
      "12\n",
      "13\n",
      "14\n",
      "15\n",
      "16\n",
      "17\n",
      "18\n",
      "19\n",
      "20\n"
     ]
    }
   ],
   "source": [
    "for row in movies_df_c.iterrows():\n",
    "    print(row[0])"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 22,
   "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>genre</th>\n",
       "      <th>year</th>\n",
       "      <th>title</th>\n",
       "      <th>title_len</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <td>11</td>\n",
       "      <td>Comedy|Drama|Romance</td>\n",
       "      <td>1995</td>\n",
       "      <td>American President, The</td>\n",
       "      <td>23</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>12</td>\n",
       "      <td>Comedy|Horror</td>\n",
       "      <td>1995</td>\n",
       "      <td>Dracula: Dead and Loving It</td>\n",
       "      <td>27</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>13</td>\n",
       "      <td>Animation|Children's</td>\n",
       "      <td>1995</td>\n",
       "      <td>Balto</td>\n",
       "      <td>5</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>14</td>\n",
       "      <td>Drama</td>\n",
       "      <td>1995</td>\n",
       "      <td>Nixon</td>\n",
       "      <td>5</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>15</td>\n",
       "      <td>Action|Adventure|Romance</td>\n",
       "      <td>1995</td>\n",
       "      <td>Cutthroat Island</td>\n",
       "      <td>16</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>16</td>\n",
       "      <td>Drama|Thriller</td>\n",
       "      <td>1995</td>\n",
       "      <td>Casino</td>\n",
       "      <td>6</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>17</td>\n",
       "      <td>Drama|Romance</td>\n",
       "      <td>1995</td>\n",
       "      <td>Sense and Sensibility</td>\n",
       "      <td>21</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>18</td>\n",
       "      <td>Thriller</td>\n",
       "      <td>1995</td>\n",
       "      <td>Four Rooms</td>\n",
       "      <td>10</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>19</td>\n",
       "      <td>Comedy</td>\n",
       "      <td>1995</td>\n",
       "      <td>Ace Ventura: When Nature Calls</td>\n",
       "      <td>30</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>20</td>\n",
       "      <td>Action</td>\n",
       "      <td>1995</td>\n",
       "      <td>Money Train</td>\n",
       "      <td>11</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "                       genre  year                           title  title_len\n",
       "11      Comedy|Drama|Romance  1995         American President, The         23\n",
       "12             Comedy|Horror  1995     Dracula: Dead and Loving It         27\n",
       "13      Animation|Children's  1995                           Balto          5\n",
       "14                     Drama  1995                           Nixon          5\n",
       "15  Action|Adventure|Romance  1995                Cutthroat Island         16\n",
       "16            Drama|Thriller  1995                          Casino          6\n",
       "17             Drama|Romance  1995           Sense and Sensibility         21\n",
       "18                  Thriller  1995                      Four Rooms         10\n",
       "19                    Comedy  1995  Ace Ventura: When Nature Calls         30\n",
       "20                    Action  1995                     Money Train         11"
      ]
     },
     "execution_count": 22,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "movies_df_c"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 23,
   "metadata": {},
   "outputs": [],
   "source": [
    "movies_df['genre_len']=[len(genre) for genre in movies_df['genre']]"
   ]
  },
  {
   "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>title_len</th>\n",
       "      <th>genre_len</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <td>count</td>\n",
       "      <td>3883.000000</td>\n",
       "      <td>3883.000000</td>\n",
       "      <td>3883.000000</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>mean</td>\n",
       "      <td>1986.066959</td>\n",
       "      <td>17.202678</td>\n",
       "      <td>11.196498</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>std</td>\n",
       "      <td>16.895690</td>\n",
       "      <td>9.951110</td>\n",
       "      <td>6.843339</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>min</td>\n",
       "      <td>1919.000000</td>\n",
       "      <td>1.000000</td>\n",
       "      <td>3.000000</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>25%</td>\n",
       "      <td>1982.000000</td>\n",
       "      <td>10.000000</td>\n",
       "      <td>6.000000</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>50%</td>\n",
       "      <td>1994.000000</td>\n",
       "      <td>15.000000</td>\n",
       "      <td>9.000000</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>75%</td>\n",
       "      <td>1997.000000</td>\n",
       "      <td>21.000000</td>\n",
       "      <td>14.000000</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>max</td>\n",
       "      <td>2000.000000</td>\n",
       "      <td>75.000000</td>\n",
       "      <td>47.000000</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "              year    title_len    genre_len\n",
       "count  3883.000000  3883.000000  3883.000000\n",
       "mean   1986.066959    17.202678    11.196498\n",
       "std      16.895690     9.951110     6.843339\n",
       "min    1919.000000     1.000000     3.000000\n",
       "25%    1982.000000    10.000000     6.000000\n",
       "50%    1994.000000    15.000000     9.000000\n",
       "75%    1997.000000    21.000000    14.000000\n",
       "max    2000.000000    75.000000    47.000000"
      ]
     },
     "execution_count": 24,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "movies_df.describe()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 2,
   "metadata": {},
   "outputs": [],
   "source": [
    "session=Session()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 3,
   "metadata": {},
   "outputs": [],
   "source": [
    "q=session.query(Movie.id)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 4,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "<sqlalchemy.orm.query.Query at 0x11e875210>"
      ]
     },
     "execution_count": 4,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "q"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 8,
   "metadata": {},
   "outputs": [],
   "source": [
    "allids=[elem[0] for elem in q.all()]"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 32,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "list"
      ]
     },
     "execution_count": 32,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "type(allids)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 33,
   "metadata": {},
   "outputs": [],
   "source": [
    "ne_ratinngs_df=ratings_df[ratings_df.MovieID.isin(allids)]"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 35,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "(799164, 4)"
      ]
     },
     "execution_count": 35,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "ne_ratinngs_df.shape"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 36,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "(1000209, 4)"
      ]
     },
     "execution_count": 36,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "ratings_df.shape"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 37,
   "metadata": {},
   "outputs": [],
   "source": [
    "allusers=[elem[0] for elem in session.query(User.id).all()]"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 38,
   "metadata": {},
   "outputs": [],
   "source": [
    "nene_ratings_dr=ne_ratinngs_df[ne_ratinngs_df.UserID.isin(allusers)]"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 39,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "(799164, 4)"
      ]
     },
     "execution_count": 39,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "nene_ratings_dr.shape"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 4,
   "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>Location</th>\n",
       "      <th>Age</th>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>User-ID</th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <td>1</td>\n",
       "      <td>nyc, new york, usa</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>2</td>\n",
       "      <td>stockton, california, usa</td>\n",
       "      <td>18</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>3</td>\n",
       "      <td>moscow, yukon territory, russia</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>4</td>\n",
       "      <td>porto, v.n.gaia, portugal</td>\n",
       "      <td>17</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>5</td>\n",
       "      <td>farnborough, hants, united kingdom</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>278854</td>\n",
       "      <td>portland, oregon, usa</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>278855</td>\n",
       "      <td>tacoma, washington, united kingdom</td>\n",
       "      <td>50</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>278856</td>\n",
       "      <td>brampton, ontario, canada</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>278857</td>\n",
       "      <td>knoxville, tennessee, usa</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>278858</td>\n",
       "      <td>dublin, n/a, ireland</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "<p>278858 rows × 2 columns</p>\n",
       "</div>"
      ],
      "text/plain": [
       "                                   Location  Age\n",
       "User-ID                                         \n",
       "1                        nyc, new york, usa  NaN\n",
       "2                 stockton, california, usa   18\n",
       "3           moscow, yukon territory, russia  NaN\n",
       "4                 porto, v.n.gaia, portugal   17\n",
       "5        farnborough, hants, united kingdom  NaN\n",
       "...                                     ...  ...\n",
       "278854                portland, oregon, usa  NaN\n",
       "278855   tacoma, washington, united kingdom   50\n",
       "278856            brampton, ontario, canada  NaN\n",
       "278857            knoxville, tennessee, usa  NaN\n",
       "278858                 dublin, n/a, ireland  NaN\n",
       "\n",
       "[278858 rows x 2 columns]"
      ]
     },
     "execution_count": 4,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "book_users_df"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 13,
   "metadata": {},
   "outputs": [],
   "source": [
    "authors=list(set(books_df['Book-Author'].tolist()))"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 12,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "Index(['Book-Title', 'Book-Author', 'Year-Of-Publication', 'Publisher',\n",
       "       'Image-URL-S', 'Image-URL-M', 'Image-URL-L'],\n",
       "      dtype='object')"
      ]
     },
     "execution_count": 12,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "books_df.columns"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 30,
   "metadata": {},
   "outputs": [],
   "source": [
    "publishers=list(set(books_df.Publisher))"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 32,
   "metadata": {},
   "outputs": [],
   "source": [
    "author_set=set(books_df['Book-Author'])"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 28,
   "metadata": {},
   "outputs": [],
   "source": [
    "author_set.remove(np.nan)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 4,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "count    1.000209e+06\n",
       "mean     3.581564e+00\n",
       "std      1.117102e+00\n",
       "min      1.000000e+00\n",
       "25%      3.000000e+00\n",
       "50%      4.000000e+00\n",
       "75%      4.000000e+00\n",
       "max      5.000000e+00\n",
       "Name: rating, dtype: float64"
      ]
     },
     "execution_count": 4,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "ratings_df.rating.describe()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 5,
   "metadata": {},
   "outputs": [],
   "source": [
    "book_ratings_df['Book-Rating'].describe()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 8,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "20"
      ]
     },
     "execution_count": 8,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "users_df.occupation.max()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 6,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "count      6040\n",
       "unique     3439\n",
       "top       48104\n",
       "freq         19\n",
       "Name: zipcode, dtype: object"
      ]
     },
     "execution_count": 6,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "users_df.zipcode.describe()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 11,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "[1, 18, 25, 35, 45, 50, 56]"
      ]
     },
     "execution_count": 11,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "sorted(users_df.age.unique())"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": []
  }
 ],
 "metadata": {
  "kernelspec": {
   "display_name": "Python 3",
   "language": "python",
   "name": "python3"
  },
  "language_info": {
   "codemirror_mode": {
    "name": "ipython",
    "version": 3
   },
   "file_extension": ".py",
   "mimetype": "text/x-python",
   "name": "python",
   "nbconvert_exporter": "python",
   "pygments_lexer": "ipython3",
   "version": "3.7.4"
  }
 },
 "nbformat": 4,
 "nbformat_minor": 4
}