1.Preparing_Data(Web_Scrapping & Cleaning).ipynb

{
 "cells": [
  {
   "cell_type": "code",
   "execution_count": 2,
   "metadata": {},
   "outputs": [],
   "source": [
    "from bs4 import BeautifulSoup\n",
    "import requests \n",
    "import pandas as pd\n",
    "import numpy as np"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 7,
   "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>Movie_ID</th>\n",
       "      <th>Title</th>\n",
       "      <th>Year</th>\n",
       "      <th>Genre</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "Empty DataFrame\n",
       "Columns: [Movie_ID, Title, Year, Genre]\n",
       "Index: []"
      ]
     },
     "execution_count": 7,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "columns=['Movie_ID','Title','Year','Genre']\n",
    "df = pd.DataFrame(columns=columns)\n",
    "df"
   ]
  },
  {
   "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>Movie_ID</th>\n",
       "      <th>Title</th>\n",
       "      <th>Year</th>\n",
       "      <th>Genre</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>34759</td>\n",
       "      <td>Aaghaaz</td>\n",
       "      <td>2000</td>\n",
       "      <td>Thriller</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>34760</td>\n",
       "      <td>Aaj Ka Ravan</td>\n",
       "      <td>2000</td>\n",
       "      <td>Drama</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>34761</td>\n",
       "      <td>Anjaane</td>\n",
       "      <td>2000</td>\n",
       "      <td>Romance</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>34762</td>\n",
       "      <td>Anokha Moti</td>\n",
       "      <td>2000</td>\n",
       "      <td>Family</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>34763</td>\n",
       "      <td>Apradhi Kaun</td>\n",
       "      <td>2000</td>\n",
       "      <td>Thriller</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>...</th>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>734</th>\n",
       "      <td>35493</td>\n",
       "      <td>The Train</td>\n",
       "      <td>2007</td>\n",
       "      <td>Drama Thriller\\n</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>735</th>\n",
       "      <td>35494</td>\n",
       "      <td>Victoria No. 203</td>\n",
       "      <td>2007</td>\n",
       "      <td>Comedy\\n</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>736</th>\n",
       "      <td>35495</td>\n",
       "      <td>Welcome</td>\n",
       "      <td>2007</td>\n",
       "      <td>Comedy\\n</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>737</th>\n",
       "      <td>35496</td>\n",
       "      <td>Yatra</td>\n",
       "      <td>2007</td>\n",
       "      <td>Drama\\n</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>738</th>\n",
       "      <td>35497</td>\n",
       "      <td>Zamaanat</td>\n",
       "      <td>2007</td>\n",
       "      <td>Drama\\n</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "<p>739 rows × 4 columns</p>\n",
       "</div>"
      ],
      "text/plain": [
       "     Movie_ID             Title  Year             Genre\n",
       "0       34759          Aaghaaz   2000          Thriller\n",
       "1       34760      Aaj Ka Ravan  2000             Drama\n",
       "2       34761           Anjaane  2000           Romance\n",
       "3       34762       Anokha Moti  2000            Family\n",
       "4       34763      Apradhi Kaun  2000          Thriller\n",
       "..        ...               ...   ...               ...\n",
       "734     35493         The Train  2007  Drama Thriller\\n\n",
       "735     35494  Victoria No. 203  2007          Comedy\\n\n",
       "736     35495           Welcome  2007          Comedy\\n\n",
       "737     35496             Yatra  2007           Drama\\n\n",
       "738     35497          Zamaanat  2007           Drama\\n\n",
       "\n",
       "[739 rows x 4 columns]"
      ]
     },
     "execution_count": 8,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "URL = {2000:'https://en.wikipedia.org/wiki/List_of_Bollywood_films_of_2000',\n",
    "       2001:'https://en.wikipedia.org/wiki/List_of_Bollywood_films_of_2001',\n",
    "       2002:'https://en.wikipedia.org/wiki/List_of_Bollywood_films_of_2002',    \n",
    "       2003:'https://en.wikipedia.org/wiki/List_of_Bollywood_films_of_2003',\n",
    "       2004:'https://en.wikipedia.org/wiki/List_of_Bollywood_films_of_2004',\n",
    "       2005:'https://en.wikipedia.org/wiki/List_of_Bollywood_films_of_2005',\n",
    "       2006:'https://en.wikipedia.org/wiki/List_of_Bollywood_films_of_2006',\n",
    "       2007:'https://en.wikipedia.org/wiki/List_of_Bollywood_films_of_2007'\n",
    "      }\n",
    "id = 34759 \n",
    "for key, url in URL.items():\n",
    "    response = requests.get(url)\n",
    "    soup = BeautifulSoup(response.text, 'html.parser')\n",
    "    table = soup.findAll('table', {'class':'wikitable'})[1].tbody\n",
    "    rows = table.find_all('tr')\n",
    "    for i in range(1, len(rows)):\n",
    "        tds = rows[i].find_all('td')\n",
    "        values = [id,tds[0].text, key, tds[3].text]\n",
    "        id+=1\n",
    "        df.loc[len(df)] = values\n",
    "\n",
    "df"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 9,
   "metadata": {},
   "outputs": [],
   "source": [
    "URL = {2008:'https://en.wikipedia.org/wiki/List_of_Bollywood_films_of_2008',\n",
    "       2009:'https://en.wikipedia.org/wiki/List_of_Bollywood_films_of_2009',\n",
    "       2010:'https://en.wikipedia.org/wiki/List_of_Bollywood_films_of_2010'\n",
    "      }\n",
    "\n",
    "for key, url in URL.items():\n",
    "    response = requests.get(url)\n",
    "    soup = BeautifulSoup(response.text, 'html.parser')\n",
    "    tbl = soup.findAll('table', {'class':'wikitable'})\n",
    "    for t in tbl:\n",
    "        table=t.tbody\n",
    "        rows = table.find_all('tr')\n",
    "        for i in range(1, len(rows)):\n",
    "            tds = rows[i].find_all('td')\n",
    "            if len(tds)==5:\n",
    "                values = [id, tds[1].text, key, tds[4].text.replace('\\n','')]\n",
    "\n",
    "            elif len(tds)==4:\n",
    "                values = [id, tds[0].text, key, tds[3].text.replace('\\n','')]\n",
    "\n",
    "            elif len(tds)==6:\n",
    "                values = [id, tds[2].text, key, tds[5].text.replace('\\n','')]\n",
    "\n",
    "            id+=1\n",
    "            df.loc[len(df)] = values"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 10,
   "metadata": {},
   "outputs": [],
   "source": [
    "URL = {\n",
    "       2011:'https://en.wikipedia.org/wiki/List_of_Bollywood_films_of_2011'\n",
    "      }\n",
    "\n",
    "for key, url in URL.items():\n",
    "    response = requests.get(url)\n",
    "    soup = BeautifulSoup(response.text, 'html.parser')\n",
    "    tbl = soup.findAll('table', {'class':'wikitable'})\n",
    "    for t in tbl:\n",
    "        table=t.tbody\n",
    "        rows = table.find_all('tr')\n",
    "        for i in range(1, len(rows)):\n",
    "            tds = rows[i].find_all('td')\n",
    "            if len(tds)==7:\n",
    "                values = [id, tds[2].text, key, tds[3].text.replace('\\n','')]\n",
    "\n",
    "            elif len(tds)==5:\n",
    "                values = [id, tds[0].text, key, tds[1].text.replace('\\n','')]\n",
    "\n",
    "            elif len(tds)==6:\n",
    "                values = [id, tds[1].text, key, tds[2].text.replace('\\n','')]\n",
    "\n",
    "            id+=1\n",
    "            df.loc[len(df)] = values"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 11,
   "metadata": {},
   "outputs": [],
   "source": [
    "URL = {\n",
    "       2012:'https://en.wikipedia.org/wiki/List_of_Bollywood_films_of_2012'\n",
    "      }\n",
    "\n",
    "for key, url in URL.items():\n",
    "    response = requests.get(url)\n",
    "    soup = BeautifulSoup(response.text, 'html.parser')\n",
    "    tbl = soup.findAll('table', {'class':'wikitable'})\n",
    "    for t in tbl:\n",
    "        table=t.tbody\n",
    "        rows = table.find_all('tr')\n",
    "        for i in range(1, len(rows)):\n",
    "            tds = rows[i].find_all('td')\n",
    "\n",
    "            if len(tds)==5:\n",
    "                values = [id, tds[1].text, key, tds[2].text.replace('\\n','')]\n",
    "\n",
    "            elif len(tds)==6:\n",
    "                values = [id, tds[2].text, key, tds[3].text.replace('\\n','')]\n",
    "            \n",
    "            elif len(tds)==4:\n",
    "                values = [id, tds[0].text, key, tds[1].text.replace('\\n','')]\n",
    "\n",
    "            id+=1\n",
    "            df.loc[len(df)] = values\n",
    " "
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 12,
   "metadata": {},
   "outputs": [],
   "source": [
    "URL = {\n",
    "       2013:'https://en.wikipedia.org/wiki/List_of_Bollywood_films_of_2013',\n",
    "       2014:'https://en.wikipedia.org/wiki/List_of_Bollywood_films_of_2014',\n",
    "       2015:'https://en.wikipedia.org/wiki/List_of_Bollywood_films_of_2015'\n",
    "      }\n",
    "\n",
    "for key, url in URL.items():\n",
    "    response = requests.get(url)\n",
    "    soup = BeautifulSoup(response.text, 'html.parser')\n",
    "    tbl = soup.findAll('table', {'class':'wikitable'})\n",
    "    for t in tbl:\n",
    "        table=t.tbody\n",
    "        rows = table.find_all('tr')\n",
    "        for i in range(1, len(rows)):\n",
    "            tds = rows[i].find_all('td')\n",
    "\n",
    "            if len(tds)==7:\n",
    "                values = [id, tds[2].text, key, tds[3].text.replace('\\n','')]\n",
    "\n",
    "            elif len(tds)==5:\n",
    "                values = [id, tds[0].text, key, tds[1].text.replace('\\n','')]\n",
    "            \n",
    "            elif len(tds)==6:\n",
    "                values = [id, tds[1].text, key, tds[2].text.replace('\\n','')]\n",
    "\n",
    "            id+=1\n",
    "            df.loc[len(df)] = values\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 13,
   "metadata": {},
   "outputs": [],
   "source": [
    "URL = {\n",
    "       2016:'https://en.wikipedia.org/wiki/List_of_Bollywood_films_of_2016'\n",
    "      }\n",
    "\n",
    "for key, url in URL.items():\n",
    "    response = requests.get(url)\n",
    "    soup = BeautifulSoup(response.text, 'html.parser')\n",
    "    table = soup.findAll('table', {'class':'wikitable'})[1].tbody\n",
    "    rows = table.find_all('tr')\n",
    "    for i in range(1, len(rows)):\n",
    "        tds = rows[i].find_all('td')\n",
    "        if len(tds)==8:\n",
    "                values = [id, tds[2].text, key, tds[3].text.replace('\\n','')]\n",
    "\n",
    "        elif len(tds)==7:\n",
    "            values = [id, tds[1].text, key, tds[2].text.replace('\\n','')]\n",
    "\n",
    "        elif len(tds)==6:\n",
    "            values = [id, tds[0].text, key, tds[1].text.replace('\\n','')]\n",
    "        id+=1\n",
    "        df.loc[len(df)] = values\n",
    "\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 14,
   "metadata": {},
   "outputs": [],
   "source": [
    "URL = {\n",
    "       2016:'https://en.wikipedia.org/wiki/List_of_Bollywood_films_of_2016'\n",
    "      }\n",
    "\n",
    "for key, url in URL.items():\n",
    "    response = requests.get(url)\n",
    "    soup = BeautifulSoup(response.text, 'html.parser')\n",
    "    tbl = soup.findAll('table', {'class':'wikitable'})[1:]\n",
    "    for t in tbl:\n",
    "        table=t.tbody\n",
    "        rows = table.find_all('tr')\n",
    "        for i in range(1, len(rows)):\n",
    "            tds = rows[i].find_all('td')\n",
    "            if len(tds)==8:\n",
    "                    values = [id, tds[2].text, key, tds[5].text.replace('\\n','')]\n",
    "\n",
    "            elif len(tds)==7:\n",
    "                values = [id, tds[1].text, key, tds[4].text.replace('\\n','')]\n",
    "\n",
    "            elif len(tds)==6:\n",
    "                values = [id, tds[0].text, key, tds[3].text.replace('\\n','')]\n",
    "            id+=1\n",
    "            df.loc[len(df)] = values\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 15,
   "metadata": {
    "scrolled": true
   },
   "outputs": [],
   "source": [
    "URL = {\n",
    "       2017:'https://en.wikipedia.org/wiki/List_of_Bollywood_films_of_2017'\n",
    "      }\n",
    "\n",
    "for key, url in URL.items():\n",
    "    response = requests.get(url)\n",
    "    soup = BeautifulSoup(response.text, 'html.parser')\n",
    "    tbl = soup.findAll('table', {'class':'wikitable'})[1:]\n",
    "    for t in tbl:\n",
    "        table=t.tbody\n",
    "        rows = table.find_all('tr')\n",
    "        for i in range(1, len(rows)):\n",
    "            tds = rows[i].find_all('td')\n",
    "            if len(tds)==8:\n",
    "                    values = [id, tds[2].text, key, tds[5].text.replace('\\n','')]\n",
    "\n",
    "            elif len(tds)==7:\n",
    "                values = [id, tds[1].text, key, tds[4].text.replace('\\n','')]\n",
    "\n",
    "            elif len(tds)==6:\n",
    "                values = [id, tds[0].text, key, tds[3].text.replace('\\n','')]\n",
    "            id+=1\n",
    "            df.loc[len(df)] = values\n",
    "\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 16,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "(2002, 4)"
      ]
     },
     "execution_count": 16,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df.shape"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 12,
   "metadata": {},
   "outputs": [],
   "source": [
    "import openpyxl"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 13,
   "metadata": {},
   "outputs": [],
   "source": [
    "wb = openpyxl.load_workbook('kaggleList.xlsx')\n",
    "sheet = wb.active\n",
    "sheet = wb['Sheet1']"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 14,
   "metadata": {},
   "outputs": [],
   "source": [
    "for i in range(9481, 9684):\n",
    "    values = [id, str(sheet['B'+str(i)].value), sheet['A'+str(i)].value, sheet['E'+str(i)].value]\n",
    "    id+=1\n",
    "    df = df.append(pd.Series(values, index=columns), ignore_index=True)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 15,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "(2218, 4)"
      ]
     },
     "execution_count": 15,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df.shape"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 16,
   "metadata": {},
   "outputs": [],
   "source": [
    "df = df.apply(lambda x : x.lower().strip() if isinstance(x, str) else x)\n",
    "df.drop_duplicates(inplace=True, subset='Title')\n",
    "df.to_csv('Movie_List.csv', index=False)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 17,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "(2089, 4)"
      ]
     },
     "execution_count": 17,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df.shape"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": []
  }
 ],
 "metadata": {
  "kernelspec": {
   "display_name": "Python 3 (ipykernel)",
   "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.11.5"
  }
 },
 "nbformat": 4,
 "nbformat_minor": 4
}