Assignment 2.ipynb

{
 "cells": [
  {
   "cell_type": "code",
   "execution_count": 1,
   "metadata": {},
   "outputs": [],
   "source": [
    "import pandas as pd\n",
    "import numpy as np"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 2,
   "metadata": {},
   "outputs": [],
   "source": [
    "df = pd.read_csv('vgsales2019.csv', header= 0)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 3,
   "metadata": {
    "scrolled": true
   },
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>ID</th>\n",
       "      <th>Name</th>\n",
       "      <th>Genre</th>\n",
       "      <th>PLATFORM</th>\n",
       "      <th>PUBLISHER</th>\n",
       "      <th>DEVELOPER</th>\n",
       "      <th>CRITIC_SCORE</th>\n",
       "      <th>USER_SCORE</th>\n",
       "      <th>Total_TOTAL_SHIPPED</th>\n",
       "      <th>GLOBAL_SALES</th>\n",
       "      <th>NA_SALES</th>\n",
       "      <th>PAL_SALES</th>\n",
       "      <th>JP_SALES</th>\n",
       "      <th>OTHER_SALES</th>\n",
       "      <th>YRS</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <td>0</td>\n",
       "      <td>ga1</td>\n",
       "      <td>Wii Sports</td>\n",
       "      <td>Sports</td>\n",
       "      <td>Wii</td>\n",
       "      <td>Nintendo</td>\n",
       "      <td>Nintendo EAD</td>\n",
       "      <td>7.7</td>\n",
       "      <td>NaN</td>\n",
       "      <td>82.86</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>2006.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>1</td>\n",
       "      <td>ga2</td>\n",
       "      <td>Super Mario Bros.</td>\n",
       "      <td>Platform</td>\n",
       "      <td>NES</td>\n",
       "      <td>Nintendo</td>\n",
       "      <td>Nintendo EAD</td>\n",
       "      <td>10.0</td>\n",
       "      <td>NaN</td>\n",
       "      <td>40.24</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>1985.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>2</td>\n",
       "      <td>ga3</td>\n",
       "      <td>Mario Kart Wii</td>\n",
       "      <td>Racing</td>\n",
       "      <td>Wii</td>\n",
       "      <td>Nintendo</td>\n",
       "      <td>Nintendo EAD</td>\n",
       "      <td>8.2</td>\n",
       "      <td>9.1</td>\n",
       "      <td>37.14</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>2008.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>3</td>\n",
       "      <td>ga4</td>\n",
       "      <td>PlayerUnknown's Battlegrounds</td>\n",
       "      <td>Shooter</td>\n",
       "      <td>PC</td>\n",
       "      <td>PUBG Corporation</td>\n",
       "      <td>PUBG Corporation</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>36.60</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>2017.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>4</td>\n",
       "      <td>ga5</td>\n",
       "      <td>Wii Sports Resort</td>\n",
       "      <td>Sports</td>\n",
       "      <td>Wii</td>\n",
       "      <td>Nintendo</td>\n",
       "      <td>Nintendo EAD</td>\n",
       "      <td>8.0</td>\n",
       "      <td>8.8</td>\n",
       "      <td>33.09</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>2009.0</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "    ID                           Name     Genre PLATFORM         PUBLISHER  \\\n",
       "0  ga1                     Wii Sports    Sports      Wii          Nintendo   \n",
       "1  ga2              Super Mario Bros.  Platform      NES          Nintendo   \n",
       "2  ga3                 Mario Kart Wii    Racing      Wii          Nintendo   \n",
       "3  ga4  PlayerUnknown's Battlegrounds   Shooter       PC  PUBG Corporation   \n",
       "4  ga5              Wii Sports Resort    Sports      Wii          Nintendo   \n",
       "\n",
       "          DEVELOPER  CRITIC_SCORE  USER_SCORE  Total_TOTAL_SHIPPED  \\\n",
       "0      Nintendo EAD           7.7         NaN                82.86   \n",
       "1      Nintendo EAD          10.0         NaN                40.24   \n",
       "2      Nintendo EAD           8.2         9.1                37.14   \n",
       "3  PUBG Corporation           NaN         NaN                36.60   \n",
       "4      Nintendo EAD           8.0         8.8                33.09   \n",
       "\n",
       "   GLOBAL_SALES  NA_SALES  PAL_SALES  JP_SALES  OTHER_SALES     YRS  \n",
       "0           NaN       NaN        NaN       NaN          NaN  2006.0  \n",
       "1           NaN       NaN        NaN       NaN          NaN  1985.0  \n",
       "2           NaN       NaN        NaN       NaN          NaN  2008.0  \n",
       "3           NaN       NaN        NaN       NaN          NaN  2017.0  \n",
       "4           NaN       NaN        NaN       NaN          NaN  2009.0  "
      ]
     },
     "execution_count": 3,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df.head()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### We can see from the dataset that there is a lot of missing values in SALES columns. Since this will affect the quality of the tables, we will be dropping these tables."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 4,
   "metadata": {},
   "outputs": [],
   "source": [
    "df = df.drop(['GLOBAL_SALES','NA_SALES','PAL_SALES','JP_SALES','OTHER_SALES'],axis = 1)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### To increase the quality of the data, we will be dropping the records having any null values"
   ]
  },
  {
   "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>ID</th>\n",
       "      <th>Name</th>\n",
       "      <th>Genre</th>\n",
       "      <th>PLATFORM</th>\n",
       "      <th>PUBLISHER</th>\n",
       "      <th>DEVELOPER</th>\n",
       "      <th>CRITIC_SCORE</th>\n",
       "      <th>USER_SCORE</th>\n",
       "      <th>Total_TOTAL_SHIPPED</th>\n",
       "      <th>YRS</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <td>2</td>\n",
       "      <td>ga3</td>\n",
       "      <td>Mario Kart Wii</td>\n",
       "      <td>Racing</td>\n",
       "      <td>Wii</td>\n",
       "      <td>Nintendo</td>\n",
       "      <td>Nintendo EAD</td>\n",
       "      <td>8.2</td>\n",
       "      <td>9.1</td>\n",
       "      <td>37.14</td>\n",
       "      <td>2008.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>4</td>\n",
       "      <td>ga5</td>\n",
       "      <td>Wii Sports Resort</td>\n",
       "      <td>Sports</td>\n",
       "      <td>Wii</td>\n",
       "      <td>Nintendo</td>\n",
       "      <td>Nintendo EAD</td>\n",
       "      <td>8.0</td>\n",
       "      <td>8.8</td>\n",
       "      <td>33.09</td>\n",
       "      <td>2009.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>6</td>\n",
       "      <td>ga7</td>\n",
       "      <td>New Super Mario Bros.</td>\n",
       "      <td>Platform</td>\n",
       "      <td>DS</td>\n",
       "      <td>Nintendo</td>\n",
       "      <td>Nintendo EAD</td>\n",
       "      <td>9.1</td>\n",
       "      <td>8.1</td>\n",
       "      <td>30.80</td>\n",
       "      <td>2006.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>8</td>\n",
       "      <td>ga9</td>\n",
       "      <td>New Super Mario Bros. Wii</td>\n",
       "      <td>Platform</td>\n",
       "      <td>Wii</td>\n",
       "      <td>Nintendo</td>\n",
       "      <td>Nintendo EAD</td>\n",
       "      <td>8.6</td>\n",
       "      <td>9.2</td>\n",
       "      <td>30.22</td>\n",
       "      <td>2009.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>11</td>\n",
       "      <td>ga12</td>\n",
       "      <td>Wii Play</td>\n",
       "      <td>Misc</td>\n",
       "      <td>Wii</td>\n",
       "      <td>Nintendo</td>\n",
       "      <td>Nintendo EAD</td>\n",
       "      <td>5.9</td>\n",
       "      <td>4.5</td>\n",
       "      <td>28.02</td>\n",
       "      <td>2007.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>2546</td>\n",
       "      <td>ga2547</td>\n",
       "      <td>Red Steel</td>\n",
       "      <td>Shooter</td>\n",
       "      <td>Wii</td>\n",
       "      <td>Ubisoft</td>\n",
       "      <td>Ubisoft Paris</td>\n",
       "      <td>5.9</td>\n",
       "      <td>7.8</td>\n",
       "      <td>0.95</td>\n",
       "      <td>2006.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>3633</td>\n",
       "      <td>ga3634</td>\n",
       "      <td>Phantasy Star Portable</td>\n",
       "      <td>Role-Playing</td>\n",
       "      <td>PSP</td>\n",
       "      <td>Sega</td>\n",
       "      <td>Alfa System</td>\n",
       "      <td>7.1</td>\n",
       "      <td>9.1</td>\n",
       "      <td>0.64</td>\n",
       "      <td>2009.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>5781</td>\n",
       "      <td>ga5782</td>\n",
       "      <td>Bionic Commando</td>\n",
       "      <td>Adventure</td>\n",
       "      <td>X360</td>\n",
       "      <td>Capcom</td>\n",
       "      <td>GRIN</td>\n",
       "      <td>7.1</td>\n",
       "      <td>8.0</td>\n",
       "      <td>0.38</td>\n",
       "      <td>2009.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>7278</td>\n",
       "      <td>ga7279</td>\n",
       "      <td>The Conduit</td>\n",
       "      <td>Shooter</td>\n",
       "      <td>Wii</td>\n",
       "      <td>Sega</td>\n",
       "      <td>High Voltage Software</td>\n",
       "      <td>6.6</td>\n",
       "      <td>8.2</td>\n",
       "      <td>0.27</td>\n",
       "      <td>2009.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>16763</td>\n",
       "      <td>ga16764</td>\n",
       "      <td>Submarine Titans</td>\n",
       "      <td>Strategy</td>\n",
       "      <td>PC</td>\n",
       "      <td>Strategy First</td>\n",
       "      <td>Ellipse Studios</td>\n",
       "      <td>6.1</td>\n",
       "      <td>9.0</td>\n",
       "      <td>0.03</td>\n",
       "      <td>2000.0</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "<p>64 rows × 10 columns</p>\n",
       "</div>"
      ],
      "text/plain": [
       "            ID                       Name         Genre PLATFORM  \\\n",
       "2          ga3             Mario Kart Wii        Racing      Wii   \n",
       "4          ga5          Wii Sports Resort        Sports      Wii   \n",
       "6          ga7      New Super Mario Bros.      Platform       DS   \n",
       "8          ga9  New Super Mario Bros. Wii      Platform      Wii   \n",
       "11        ga12                   Wii Play          Misc      Wii   \n",
       "...        ...                        ...           ...      ...   \n",
       "2546    ga2547                  Red Steel       Shooter      Wii   \n",
       "3633    ga3634     Phantasy Star Portable  Role-Playing      PSP   \n",
       "5781    ga5782            Bionic Commando     Adventure     X360   \n",
       "7278    ga7279                The Conduit       Shooter      Wii   \n",
       "16763  ga16764           Submarine Titans      Strategy       PC   \n",
       "\n",
       "            PUBLISHER              DEVELOPER  CRITIC_SCORE  USER_SCORE  \\\n",
       "2            Nintendo           Nintendo EAD           8.2         9.1   \n",
       "4            Nintendo           Nintendo EAD           8.0         8.8   \n",
       "6            Nintendo           Nintendo EAD           9.1         8.1   \n",
       "8            Nintendo           Nintendo EAD           8.6         9.2   \n",
       "11           Nintendo           Nintendo EAD           5.9         4.5   \n",
       "...               ...                    ...           ...         ...   \n",
       "2546          Ubisoft          Ubisoft Paris           5.9         7.8   \n",
       "3633             Sega            Alfa System           7.1         9.1   \n",
       "5781           Capcom                   GRIN           7.1         8.0   \n",
       "7278             Sega  High Voltage Software           6.6         8.2   \n",
       "16763  Strategy First        Ellipse Studios           6.1         9.0   \n",
       "\n",
       "       Total_TOTAL_SHIPPED     YRS  \n",
       "2                    37.14  2008.0  \n",
       "4                    33.09  2009.0  \n",
       "6                    30.80  2006.0  \n",
       "8                    30.22  2009.0  \n",
       "11                   28.02  2007.0  \n",
       "...                    ...     ...  \n",
       "2546                  0.95  2006.0  \n",
       "3633                  0.64  2009.0  \n",
       "5781                  0.38  2009.0  \n",
       "7278                  0.27  2009.0  \n",
       "16763                 0.03  2000.0  \n",
       "\n",
       "[64 rows x 10 columns]"
      ]
     },
     "execution_count": 5,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df = df.dropna()\n",
    "df"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### Creating the tables as DataFrames required "
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 6,
   "metadata": {},
   "outputs": [],
   "source": [
    "game_inventory = pd.DataFrame(columns = ['G_ID', 'G_NAME','GENRE','DEVELOPER', 'PLATFORM','TOTAL_SHIPPED','CRITIC_SCORE'\n",
    "                                         ,'USER_SCORE','YEAR'])"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### Storing the appropriate values to proposed Tables"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 7,
   "metadata": {},
   "outputs": [],
   "source": [
    "game_inventory['G_NAME'] = df['Name']\n",
    "game_inventory['PLATFORM'] = df['PLATFORM']\n",
    "years = []\n",
    "for i in df['YRS']:\n",
    "    years.append(int(i)) # Converting to integer\n",
    "game_inventory['YEAR'] = years\n",
    "game_inventory['TOTAL_SHIPPED'] = df['Total_TOTAL_SHIPPED']\n",
    "game_inventory['CRITIC_SCORE'] = df['CRITIC_SCORE']\n",
    "game_inventory['USER_SCORE'] = df['USER_SCORE']\n",
    "game_inventory['GENRE'] = df['Genre']\n",
    "game_inventory['DEVELOPER'] = df['DEVELOPER']"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "# 1 NF Normalization: \n",
    "#                            Now we shall try to achive 1NF from on the Game Inventory by adding Primary Key. The same shall be done for all the tables to achieve the 1NF across the tables in the database that we are building."
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## Generating Primary Keys for Game_Inventory"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 8,
   "metadata": {},
   "outputs": [],
   "source": [
    "j=1\n",
    "g_id = []\n",
    "for i in game_inventory['G_ID']:\n",
    "    g_id.append(j)\n",
    "    j+=1\n",
    "game_inventory['G_ID']=g_id"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 9,
   "metadata": {},
   "outputs": [],
   "source": [
    "game_inventory['G_ID']=g_id"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 10,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>G_ID</th>\n",
       "      <th>G_NAME</th>\n",
       "      <th>GENRE</th>\n",
       "      <th>DEVELOPER</th>\n",
       "      <th>PLATFORM</th>\n",
       "      <th>TOTAL_SHIPPED</th>\n",
       "      <th>CRITIC_SCORE</th>\n",
       "      <th>USER_SCORE</th>\n",
       "      <th>YEAR</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <td>2</td>\n",
       "      <td>1</td>\n",
       "      <td>Mario Kart Wii</td>\n",
       "      <td>Racing</td>\n",
       "      <td>Nintendo EAD</td>\n",
       "      <td>Wii</td>\n",
       "      <td>37.14</td>\n",
       "      <td>8.2</td>\n",
       "      <td>9.1</td>\n",
       "      <td>2008</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>4</td>\n",
       "      <td>2</td>\n",
       "      <td>Wii Sports Resort</td>\n",
       "      <td>Sports</td>\n",
       "      <td>Nintendo EAD</td>\n",
       "      <td>Wii</td>\n",
       "      <td>33.09</td>\n",
       "      <td>8.0</td>\n",
       "      <td>8.8</td>\n",
       "      <td>2009</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>6</td>\n",
       "      <td>3</td>\n",
       "      <td>New Super Mario Bros.</td>\n",
       "      <td>Platform</td>\n",
       "      <td>Nintendo EAD</td>\n",
       "      <td>DS</td>\n",
       "      <td>30.80</td>\n",
       "      <td>9.1</td>\n",
       "      <td>8.1</td>\n",
       "      <td>2006</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>8</td>\n",
       "      <td>4</td>\n",
       "      <td>New Super Mario Bros. Wii</td>\n",
       "      <td>Platform</td>\n",
       "      <td>Nintendo EAD</td>\n",
       "      <td>Wii</td>\n",
       "      <td>30.22</td>\n",
       "      <td>8.6</td>\n",
       "      <td>9.2</td>\n",
       "      <td>2009</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>11</td>\n",
       "      <td>5</td>\n",
       "      <td>Wii Play</td>\n",
       "      <td>Misc</td>\n",
       "      <td>Nintendo EAD</td>\n",
       "      <td>Wii</td>\n",
       "      <td>28.02</td>\n",
       "      <td>5.9</td>\n",
       "      <td>4.5</td>\n",
       "      <td>2007</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>14</td>\n",
       "      <td>6</td>\n",
       "      <td>Mario Kart DS</td>\n",
       "      <td>Racing</td>\n",
       "      <td>Nintendo EAD</td>\n",
       "      <td>DS</td>\n",
       "      <td>23.60</td>\n",
       "      <td>9.1</td>\n",
       "      <td>9.4</td>\n",
       "      <td>2005</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>28</td>\n",
       "      <td>7</td>\n",
       "      <td>Pokemon X/Y</td>\n",
       "      <td>Role-Playing</td>\n",
       "      <td>Game Freak</td>\n",
       "      <td>3DS</td>\n",
       "      <td>16.37</td>\n",
       "      <td>8.9</td>\n",
       "      <td>9.7</td>\n",
       "      <td>2013</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>33</td>\n",
       "      <td>8</td>\n",
       "      <td>Pokemon Black / White Version</td>\n",
       "      <td>Role-Playing</td>\n",
       "      <td>Game Freak</td>\n",
       "      <td>DS</td>\n",
       "      <td>15.64</td>\n",
       "      <td>8.6</td>\n",
       "      <td>9.0</td>\n",
       "      <td>2011</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>43</td>\n",
       "      <td>9</td>\n",
       "      <td>Halo 3</td>\n",
       "      <td>Shooter</td>\n",
       "      <td>Bungie Studios</td>\n",
       "      <td>X360</td>\n",
       "      <td>14.50</td>\n",
       "      <td>9.6</td>\n",
       "      <td>9.5</td>\n",
       "      <td>2007</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>52</td>\n",
       "      <td>10</td>\n",
       "      <td>Super Smash Bros. Brawl</td>\n",
       "      <td>Fighting</td>\n",
       "      <td>Project Sora</td>\n",
       "      <td>Wii</td>\n",
       "      <td>13.29</td>\n",
       "      <td>9.2</td>\n",
       "      <td>9.7</td>\n",
       "      <td>2008</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "    G_ID                         G_NAME         GENRE       DEVELOPER  \\\n",
       "2      1                 Mario Kart Wii        Racing    Nintendo EAD   \n",
       "4      2              Wii Sports Resort        Sports    Nintendo EAD   \n",
       "6      3          New Super Mario Bros.      Platform    Nintendo EAD   \n",
       "8      4      New Super Mario Bros. Wii      Platform    Nintendo EAD   \n",
       "11     5                       Wii Play          Misc    Nintendo EAD   \n",
       "14     6                  Mario Kart DS        Racing    Nintendo EAD   \n",
       "28     7                    Pokemon X/Y  Role-Playing      Game Freak   \n",
       "33     8  Pokemon Black / White Version  Role-Playing      Game Freak   \n",
       "43     9                         Halo 3       Shooter  Bungie Studios   \n",
       "52    10        Super Smash Bros. Brawl      Fighting    Project Sora   \n",
       "\n",
       "   PLATFORM  TOTAL_SHIPPED  CRITIC_SCORE  USER_SCORE  YEAR  \n",
       "2       Wii          37.14           8.2         9.1  2008  \n",
       "4       Wii          33.09           8.0         8.8  2009  \n",
       "6        DS          30.80           9.1         8.1  2006  \n",
       "8       Wii          30.22           8.6         9.2  2009  \n",
       "11      Wii          28.02           5.9         4.5  2007  \n",
       "14       DS          23.60           9.1         9.4  2005  \n",
       "28      3DS          16.37           8.9         9.7  2013  \n",
       "33       DS          15.64           8.6         9.0  2011  \n",
       "43     X360          14.50           9.6         9.5  2007  \n",
       "52      Wii          13.29           9.2         9.7  2008  "
      ]
     },
     "execution_count": 10,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "game_inventory.head(10)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## Creating separate tables for Developers, Publishers and Platforms"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 17,
   "metadata": {},
   "outputs": [],
   "source": [
    "developer = pd.DataFrame(columns = ['DEV_ID', 'DEV_NAME', 'PUB_ID'])\n",
    "publisher = pd.DataFrame(columns = ['PUB_ID','PUB_NAME'])\n",
    "platform = pd.DataFrame(columns = ['PLAT_ID','PLTF_NAME'])\n",
    "genre = pd.DataFrame(columns = ['GENRE_ID','GEN_NAME'])\n",
    "developer['DEV_NAME'] = df.DEVELOPER.unique()\n",
    "publisher['PUB_NAME'] = df.PUBLISHER.unique()\n",
    "platform.PLTF_NAME = game_inventory['PLATFORM'].unique()\n",
    "genre.GEN_NAME = game_inventory['GENRE'].unique()\n"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "# 2 Normalization:\n",
    "#                             Since there is no calculated columns in the tables and no partial dependencies we have achieved the 2NF.\n",
    "\n",
    "# 3 Normalization:\n",
    "#                            To achieve the 3NF each column in the table should have direct relation to the PK. We have ID columns of other tables which are kept to establish a relationship to another table by adding the foreign key constraints to the table."
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## Generating Foreign Keys for  Developers, Publishers and Platforms"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 18,
   "metadata": {},
   "outputs": [],
   "source": [
    "game_inventory.rename(columns = {'DEVELOPER':'DEV_ID'}, inplace = True) # Renaming to reflect the meaningful changes\n",
    "j = 101\n",
    "temp_pubid = []\n",
    "for i in publisher['PUB_NAME']:\n",
    "    temp_pubid.append(j)\n",
    "    j+=1\n",
    "j= 201\n",
    "temp_devid = []\n",
    "for i in developer['DEV_NAME']:\n",
    "    temp_devid.append(j)\n",
    "    j+=1\n",
    "j= 301\n",
    "temp_platid = []\n",
    "for i in platform['PLTF_NAME']:\n",
    "    temp_platid.append(j)\n",
    "    j+=1\n",
    "j=401\n",
    "temp_genid=[]\n",
    "for i in genre['GEN_NAME']:\n",
    "    temp_genid.append(j)\n",
    "    j+=1\n",
    "publisher['PUB_ID'] = temp_pubid\n",
    "developer['DEV_ID'] = temp_devid\n",
    "platform.PLAT_ID = temp_platid\n",
    "genre.GENRE_ID = temp_genid"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 19,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>PUB_ID</th>\n",
       "      <th>PUB_NAME</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <td>0</td>\n",
       "      <td>101</td>\n",
       "      <td>Nintendo</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>1</td>\n",
       "      <td>102</td>\n",
       "      <td>Microsoft Game Studios</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>2</td>\n",
       "      <td>103</td>\n",
       "      <td>Sony Interactive Entertainment</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>3</td>\n",
       "      <td>104</td>\n",
       "      <td>Sony Computer Entertainment</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>4</td>\n",
       "      <td>105</td>\n",
       "      <td>Square</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>5</td>\n",
       "      <td>106</td>\n",
       "      <td>Square Enix</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>6</td>\n",
       "      <td>107</td>\n",
       "      <td>Blizzard Entertainment</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>7</td>\n",
       "      <td>108</td>\n",
       "      <td>Capcom</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>8</td>\n",
       "      <td>109</td>\n",
       "      <td>Electronic Arts</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>9</td>\n",
       "      <td>110</td>\n",
       "      <td>Sega</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "   PUB_ID                        PUB_NAME\n",
       "0     101                        Nintendo\n",
       "1     102          Microsoft Game Studios\n",
       "2     103  Sony Interactive Entertainment\n",
       "3     104     Sony Computer Entertainment\n",
       "4     105                          Square\n",
       "5     106                     Square Enix\n",
       "6     107          Blizzard Entertainment\n",
       "7     108                          Capcom\n",
       "8     109                 Electronic Arts\n",
       "9     110                            Sega"
      ]
     },
     "execution_count": 19,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "publisher.head(10)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 20,
   "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_ID</th>\n",
       "      <th>GEN_NAME</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <td>0</td>\n",
       "      <td>401</td>\n",
       "      <td>Racing</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>1</td>\n",
       "      <td>402</td>\n",
       "      <td>Sports</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>2</td>\n",
       "      <td>403</td>\n",
       "      <td>Platform</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>3</td>\n",
       "      <td>404</td>\n",
       "      <td>Misc</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>4</td>\n",
       "      <td>405</td>\n",
       "      <td>Role-Playing</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>5</td>\n",
       "      <td>406</td>\n",
       "      <td>Shooter</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>6</td>\n",
       "      <td>407</td>\n",
       "      <td>Fighting</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>7</td>\n",
       "      <td>408</td>\n",
       "      <td>Action-Adventure</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>8</td>\n",
       "      <td>409</td>\n",
       "      <td>Action</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>9</td>\n",
       "      <td>410</td>\n",
       "      <td>Adventure</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "   GENRE_ID          GEN_NAME\n",
       "0       401            Racing\n",
       "1       402            Sports\n",
       "2       403          Platform\n",
       "3       404              Misc\n",
       "4       405      Role-Playing\n",
       "5       406           Shooter\n",
       "6       407          Fighting\n",
       "7       408  Action-Adventure\n",
       "8       409            Action\n",
       "9       410         Adventure"
      ]
     },
     "execution_count": 20,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "genre.head(10)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### Now, we will be adding the corresponding PUB_ID from the Publisher table to the Developer Table"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 21,
   "metadata": {},
   "outputs": [],
   "source": [
    "df2 = df.drop_duplicates('DEVELOPER', keep='first', inplace = False)\n",
    "pubtemp = []\n",
    "for i in developer['DEV_NAME']:\n",
    "    value = df2.loc[(df2.DEVELOPER == i),'PUBLISHER'].tolist()[0]\n",
    "    pubtemp.append(value)\n",
    "pub_id = []\n",
    "for i in pubtemp:\n",
    "    pub_id.append(publisher.loc[publisher.PUB_NAME == i,'PUB_ID'].tolist()[0])\n",
    "developer.PUB_ID = pub_id"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### Now, we will be adding the corresponding PLAT_ID and DEV_ID from the Platform table and Developer Table to the Game_Inventory Table"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 22,
   "metadata": {},
   "outputs": [],
   "source": [
    "df2 = df.drop_duplicates('DEVELOPER', keep='first', inplace = False)\n",
    "pubtemp = []\n",
    "for i in game_inventory['PLATFORM']:\n",
    "    value = df.loc[(game_inventory.PLATFORM == i),'PLATFORM'].tolist()[0]\n",
    "    pubtemp.append(value)\n",
    "pub_id = []\n",
    "for i in pubtemp:\n",
    "    pub_id.append(platform.loc[platform.PLTF_NAME == i,'PLAT_ID'].tolist()[0])\n",
    "game_inventory['PLATFORM']=pub_id"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 23,
   "metadata": {},
   "outputs": [],
   "source": [
    "devtemp = []\n",
    "for i in df['Name']:\n",
    "    value = df.loc[(df.Name == i),'DEVELOPER'].tolist()[0]\n",
    "    devtemp.append(value)\n",
    "dev_id = []\n",
    "for i in devtemp:\n",
    "    dev_id.append(developer.loc[developer.DEV_NAME == i,'DEV_ID'].tolist()[0])\n",
    "game_inventory['DEV_ID'] = dev_id"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 24,
   "metadata": {},
   "outputs": [],
   "source": [
    "genret = []\n",
    "for i in df['Genre']:\n",
    "    value = df.loc[(df.Genre == i),'Genre'].tolist()[0]\n",
    "    genret.append(value)\n",
    "gen_id = []\n",
    "for i in genret:\n",
    "    gen_id.append(genre.loc[genre.GEN_NAME == i,'GENRE_ID'].tolist()[0])\n",
    "game_inventory['GENRE'] = gen_id\n",
    "game_inventory.rename(columns = {'GENRE':'GENRE_ID'}, inplace = True)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## Sample Table Representation"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 25,
   "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>G_ID</th>\n",
       "      <th>G_NAME</th>\n",
       "      <th>GENRE_ID</th>\n",
       "      <th>DEV_ID</th>\n",
       "      <th>PLATFORM</th>\n",
       "      <th>TOTAL_SHIPPED</th>\n",
       "      <th>CRITIC_SCORE</th>\n",
       "      <th>USER_SCORE</th>\n",
       "      <th>YEAR</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <td>2</td>\n",
       "      <td>1</td>\n",
       "      <td>Mario Kart Wii</td>\n",
       "      <td>401</td>\n",
       "      <td>201</td>\n",
       "      <td>301</td>\n",
       "      <td>37.14</td>\n",
       "      <td>8.2</td>\n",
       "      <td>9.1</td>\n",
       "      <td>2008</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>4</td>\n",
       "      <td>2</td>\n",
       "      <td>Wii Sports Resort</td>\n",
       "      <td>402</td>\n",
       "      <td>201</td>\n",
       "      <td>301</td>\n",
       "      <td>33.09</td>\n",
       "      <td>8.0</td>\n",
       "      <td>8.8</td>\n",
       "      <td>2009</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>6</td>\n",
       "      <td>3</td>\n",
       "      <td>New Super Mario Bros.</td>\n",
       "      <td>403</td>\n",
       "      <td>201</td>\n",
       "      <td>302</td>\n",
       "      <td>30.80</td>\n",
       "      <td>9.1</td>\n",
       "      <td>8.1</td>\n",
       "      <td>2006</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>8</td>\n",
       "      <td>4</td>\n",
       "      <td>New Super Mario Bros. Wii</td>\n",
       "      <td>403</td>\n",
       "      <td>201</td>\n",
       "      <td>301</td>\n",
       "      <td>30.22</td>\n",
       "      <td>8.6</td>\n",
       "      <td>9.2</td>\n",
       "      <td>2009</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>11</td>\n",
       "      <td>5</td>\n",
       "      <td>Wii Play</td>\n",
       "      <td>404</td>\n",
       "      <td>201</td>\n",
       "      <td>301</td>\n",
       "      <td>28.02</td>\n",
       "      <td>5.9</td>\n",
       "      <td>4.5</td>\n",
       "      <td>2007</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>14</td>\n",
       "      <td>6</td>\n",
       "      <td>Mario Kart DS</td>\n",
       "      <td>401</td>\n",
       "      <td>201</td>\n",
       "      <td>302</td>\n",
       "      <td>23.60</td>\n",
       "      <td>9.1</td>\n",
       "      <td>9.4</td>\n",
       "      <td>2005</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>28</td>\n",
       "      <td>7</td>\n",
       "      <td>Pokemon X/Y</td>\n",
       "      <td>405</td>\n",
       "      <td>202</td>\n",
       "      <td>303</td>\n",
       "      <td>16.37</td>\n",
       "      <td>8.9</td>\n",
       "      <td>9.7</td>\n",
       "      <td>2013</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>33</td>\n",
       "      <td>8</td>\n",
       "      <td>Pokemon Black / White Version</td>\n",
       "      <td>405</td>\n",
       "      <td>202</td>\n",
       "      <td>302</td>\n",
       "      <td>15.64</td>\n",
       "      <td>8.6</td>\n",
       "      <td>9.0</td>\n",
       "      <td>2011</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>43</td>\n",
       "      <td>9</td>\n",
       "      <td>Halo 3</td>\n",
       "      <td>406</td>\n",
       "      <td>203</td>\n",
       "      <td>304</td>\n",
       "      <td>14.50</td>\n",
       "      <td>9.6</td>\n",
       "      <td>9.5</td>\n",
       "      <td>2007</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>52</td>\n",
       "      <td>10</td>\n",
       "      <td>Super Smash Bros. Brawl</td>\n",
       "      <td>407</td>\n",
       "      <td>204</td>\n",
       "      <td>301</td>\n",
       "      <td>13.29</td>\n",
       "      <td>9.2</td>\n",
       "      <td>9.7</td>\n",
       "      <td>2008</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "    G_ID                         G_NAME  GENRE_ID  DEV_ID  PLATFORM  \\\n",
       "2      1                 Mario Kart Wii       401     201       301   \n",
       "4      2              Wii Sports Resort       402     201       301   \n",
       "6      3          New Super Mario Bros.       403     201       302   \n",
       "8      4      New Super Mario Bros. Wii       403     201       301   \n",
       "11     5                       Wii Play       404     201       301   \n",
       "14     6                  Mario Kart DS       401     201       302   \n",
       "28     7                    Pokemon X/Y       405     202       303   \n",
       "33     8  Pokemon Black / White Version       405     202       302   \n",
       "43     9                         Halo 3       406     203       304   \n",
       "52    10        Super Smash Bros. Brawl       407     204       301   \n",
       "\n",
       "    TOTAL_SHIPPED  CRITIC_SCORE  USER_SCORE  YEAR  \n",
       "2           37.14           8.2         9.1  2008  \n",
       "4           33.09           8.0         8.8  2009  \n",
       "6           30.80           9.1         8.1  2006  \n",
       "8           30.22           8.6         9.2  2009  \n",
       "11          28.02           5.9         4.5  2007  \n",
       "14          23.60           9.1         9.4  2005  \n",
       "28          16.37           8.9         9.7  2013  \n",
       "33          15.64           8.6         9.0  2011  \n",
       "43          14.50           9.6         9.5  2007  \n",
       "52          13.29           9.2         9.7  2008  "
      ]
     },
     "execution_count": 25,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "game_inventory.head(10)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 26,
   "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>PLAT_ID</th>\n",
       "      <th>PLTF_NAME</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <td>0</td>\n",
       "      <td>301</td>\n",
       "      <td>Wii</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>1</td>\n",
       "      <td>302</td>\n",
       "      <td>DS</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>2</td>\n",
       "      <td>303</td>\n",
       "      <td>3DS</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>3</td>\n",
       "      <td>304</td>\n",
       "      <td>X360</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>4</td>\n",
       "      <td>305</td>\n",
       "      <td>NS</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>5</td>\n",
       "      <td>306</td>\n",
       "      <td>PS4</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>6</td>\n",
       "      <td>307</td>\n",
       "      <td>PS</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>7</td>\n",
       "      <td>308</td>\n",
       "      <td>PS2</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>8</td>\n",
       "      <td>309</td>\n",
       "      <td>N64</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>9</td>\n",
       "      <td>310</td>\n",
       "      <td>PS3</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "   PLAT_ID PLTF_NAME\n",
       "0      301       Wii\n",
       "1      302        DS\n",
       "2      303       3DS\n",
       "3      304      X360\n",
       "4      305        NS\n",
       "5      306       PS4\n",
       "6      307        PS\n",
       "7      308       PS2\n",
       "8      309       N64\n",
       "9      310       PS3"
      ]
     },
     "execution_count": 26,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "platform.head(10)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 27,
   "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>DEV_ID</th>\n",
       "      <th>DEV_NAME</th>\n",
       "      <th>PUB_ID</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <td>0</td>\n",
       "      <td>201</td>\n",
       "      <td>Nintendo EAD</td>\n",
       "      <td>101</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>1</td>\n",
       "      <td>202</td>\n",
       "      <td>Game Freak</td>\n",
       "      <td>101</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>2</td>\n",
       "      <td>203</td>\n",
       "      <td>Bungie Studios</td>\n",
       "      <td>102</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>3</td>\n",
       "      <td>204</td>\n",
       "      <td>Project Sora</td>\n",
       "      <td>101</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>4</td>\n",
       "      <td>205</td>\n",
       "      <td>Nintendo EAD Tokyo</td>\n",
       "      <td>101</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>5</td>\n",
       "      <td>206</td>\n",
       "      <td>Bandai Namco Games</td>\n",
       "      <td>101</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>6</td>\n",
       "      <td>207</td>\n",
       "      <td>Nintendo EPD</td>\n",
       "      <td>101</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>7</td>\n",
       "      <td>208</td>\n",
       "      <td>Guerrilla Games</td>\n",
       "      <td>103</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>8</td>\n",
       "      <td>209</td>\n",
       "      <td>SquareSoft</td>\n",
       "      <td>104</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>9</td>\n",
       "      <td>210</td>\n",
       "      <td>Square Enix</td>\n",
       "      <td>106</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "   DEV_ID            DEV_NAME  PUB_ID\n",
       "0     201        Nintendo EAD     101\n",
       "1     202          Game Freak     101\n",
       "2     203      Bungie Studios     102\n",
       "3     204        Project Sora     101\n",
       "4     205  Nintendo EAD Tokyo     101\n",
       "5     206  Bandai Namco Games     101\n",
       "6     207        Nintendo EPD     101\n",
       "7     208     Guerrilla Games     103\n",
       "8     209          SquareSoft     104\n",
       "9     210         Square Enix     106"
      ]
     },
     "execution_count": 27,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "developer.head(10)"
   ]
  },
  {
   "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>PUB_ID</th>\n",
       "      <th>PUB_NAME</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <td>0</td>\n",
       "      <td>101</td>\n",
       "      <td>Nintendo</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>1</td>\n",
       "      <td>102</td>\n",
       "      <td>Microsoft Game Studios</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>2</td>\n",
       "      <td>103</td>\n",
       "      <td>Sony Interactive Entertainment</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>3</td>\n",
       "      <td>104</td>\n",
       "      <td>Sony Computer Entertainment</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>4</td>\n",
       "      <td>105</td>\n",
       "      <td>Square</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>5</td>\n",
       "      <td>106</td>\n",
       "      <td>Square Enix</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>6</td>\n",
       "      <td>107</td>\n",
       "      <td>Blizzard Entertainment</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>7</td>\n",
       "      <td>108</td>\n",
       "      <td>Capcom</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>8</td>\n",
       "      <td>109</td>\n",
       "      <td>Electronic Arts</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>9</td>\n",
       "      <td>110</td>\n",
       "      <td>Sega</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "   PUB_ID                        PUB_NAME\n",
       "0     101                        Nintendo\n",
       "1     102          Microsoft Game Studios\n",
       "2     103  Sony Interactive Entertainment\n",
       "3     104     Sony Computer Entertainment\n",
       "4     105                          Square\n",
       "5     106                     Square Enix\n",
       "6     107          Blizzard Entertainment\n",
       "7     108                          Capcom\n",
       "8     109                 Electronic Arts\n",
       "9     110                            Sega"
      ]
     },
     "execution_count": 28,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "publisher.head(10)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 29,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>GENRE_ID</th>\n",
       "      <th>GEN_NAME</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <td>0</td>\n",
       "      <td>401</td>\n",
       "      <td>Racing</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>1</td>\n",
       "      <td>402</td>\n",
       "      <td>Sports</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>2</td>\n",
       "      <td>403</td>\n",
       "      <td>Platform</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>3</td>\n",
       "      <td>404</td>\n",
       "      <td>Misc</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>4</td>\n",
       "      <td>405</td>\n",
       "      <td>Role-Playing</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>5</td>\n",
       "      <td>406</td>\n",
       "      <td>Shooter</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>6</td>\n",
       "      <td>407</td>\n",
       "      <td>Fighting</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>7</td>\n",
       "      <td>408</td>\n",
       "      <td>Action-Adventure</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>8</td>\n",
       "      <td>409</td>\n",
       "      <td>Action</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>9</td>\n",
       "      <td>410</td>\n",
       "      <td>Adventure</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "   GENRE_ID          GEN_NAME\n",
       "0       401            Racing\n",
       "1       402            Sports\n",
       "2       403          Platform\n",
       "3       404              Misc\n",
       "4       405      Role-Playing\n",
       "5       406           Shooter\n",
       "6       407          Fighting\n",
       "7       408  Action-Adventure\n",
       "8       409            Action\n",
       "9       410         Adventure"
      ]
     },
     "execution_count": 29,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "genre.head(10)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## Social Media Account"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "import twitter"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "CONSUMER_KEY = 'Ulg0aOEDEy09m90eQ1WjgY8ax'\n",
    "CONSUMER_SECRET = ''\n",
    "OAUTH_TOKEN = '715063474020159488-TWPJ4GzyfYJ94Iy4ItvIu4c7Qd065nD'\n",
    "OAUTH_TOKEN_SECRET = ''\n",
    "auth = twitter.oauth.OAuth(OAUTH_TOKEN, OAUTH_TOKEN_SECRET,\n",
    "                           CONSUMER_KEY, CONSUMER_SECRET)\n",
    "\n",
    "twitter_api = twitter.Twitter(auth=auth)\n",
    "# For security purposes we won't be sharing the secret ID and password."
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### CONTRIBUTION\n",
    "###### Your contribution towards project. How much code did you write and how much you took from other site or some other source.                                                                            \n",
    "Our Own : 50%\n",
    "\n",
    "By External source: 50%                                                                                        \n",
    "\n",
    "\n",
    "\n",
    "\n"
   ]
  }
 ],
 "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": 2
}