anime_csv_to_sqlite_db.ipynb

{
 "cells": [
  {
   "attachments": {},
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "# Anime CSV to SQLite DB\n",
    "This notebook converts the anime.csv file into a SQLite database.\n",
    "\n",
    "## Note:\n",
    "This script was modified from the original `main.ipynb` notebook from the [Anime Database - Combining Crunchyroll and MyAnimeList\n",
    " repo](https://github.com/cckuqui/anime-db) to work with the [anime.csv](https://www.kaggle.com/datasets/shafiwalsher/myanimelist-dataset-2023-top-15000) file."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 308,
   "metadata": {},
   "outputs": [],
   "source": [
    "import pandas as pd\n",
    "import numpy as np"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 309,
   "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>aired</th>\n",
       "      <th>anime_id</th>\n",
       "      <th>duration</th>\n",
       "      <th>episodes</th>\n",
       "      <th>genres</th>\n",
       "      <th>members</th>\n",
       "      <th>popularity</th>\n",
       "      <th>premiered</th>\n",
       "      <th>ranked</th>\n",
       "      <th>rating</th>\n",
       "      <th>...</th>\n",
       "      <th>studios</th>\n",
       "      <th>synonyms</th>\n",
       "      <th>synopsis_x</th>\n",
       "      <th>title</th>\n",
       "      <th>titletype</th>\n",
       "      <th>anime_url</th>\n",
       "      <th>poster</th>\n",
       "      <th>trailer</th>\n",
       "      <th>trailer_thumbnail</th>\n",
       "      <th>synopsis_y</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>Apr 5, 2009 to Jul 4, 2010</td>\n",
       "      <td>5114.0</td>\n",
       "      <td>24 min. per ep.</td>\n",
       "      <td>64</td>\n",
       "      <td>Action, Adventure, Drama, Fantasy, Military, S...</td>\n",
       "      <td>3097493.0</td>\n",
       "      <td>3.0</td>\n",
       "      <td>Spring 2009</td>\n",
       "      <td>1.0</td>\n",
       "      <td>R - 17+ (violence &amp; profanity)</td>\n",
       "      <td>...</td>\n",
       "      <td>Bones</td>\n",
       "      <td>Hagane no Renkinjutsushi: Fullmetal Alchemist,...</td>\n",
       "      <td>After a horrific alchemy experiment goes wrong...</td>\n",
       "      <td>Fullmetal Alchemist: Brotherhood</td>\n",
       "      <td>TV</td>\n",
       "      <td>https://myanimelist.net/anime/5114/Fullmetal_A...</td>\n",
       "      <td>https://cdn.myanimelist.net/images/anime/1208/...</td>\n",
       "      <td>https://www.youtube.com/watch?v=--IcmZkvL0Q</td>\n",
       "      <td>https://img.youtube.com/vi/--IcmZkvL0Q/maxresd...</td>\n",
       "      <td>After a horrific alchemy experiment goes wrong...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>Oct 11, 2022 to Dec 27, 2022</td>\n",
       "      <td>41467.0</td>\n",
       "      <td>24 min. per ep.</td>\n",
       "      <td>13</td>\n",
       "      <td>Action, Adventure, Fantasy, Shounen</td>\n",
       "      <td>395558.0</td>\n",
       "      <td>518.0</td>\n",
       "      <td>Fall 2022</td>\n",
       "      <td>2.0</td>\n",
       "      <td>R - 17+ (violence &amp; profanity)</td>\n",
       "      <td>...</td>\n",
       "      <td>Pierrot</td>\n",
       "      <td>Bleach: Thousand-Year Blood War Arc</td>\n",
       "      <td>Substitute Soul Reaper Ichigo Kurosaki spends ...</td>\n",
       "      <td>Bleach: Thousand-Year Blood War</td>\n",
       "      <td>TV</td>\n",
       "      <td>https://myanimelist.net/anime/41467/Bleach__Se...</td>\n",
       "      <td>https://cdn.myanimelist.net/images/anime/1764/...</td>\n",
       "      <td>https://www.youtube.com/watch?v=e8YBesRKq_U</td>\n",
       "      <td>https://img.youtube.com/vi/e8YBesRKq_U/maxresd...</td>\n",
       "      <td>Substitute Soul Reaper Ichigo Kurosaki spends ...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>Apr 6, 2011 to Sep 14, 2011</td>\n",
       "      <td>9253.0</td>\n",
       "      <td>24 min. per ep.</td>\n",
       "      <td>24</td>\n",
       "      <td>Drama, Sci-Fi, Suspense, Psychological, Time T...</td>\n",
       "      <td>2385549.0</td>\n",
       "      <td>13.0</td>\n",
       "      <td>Spring 2011</td>\n",
       "      <td>3.0</td>\n",
       "      <td>PG-13 - Teens 13 or older</td>\n",
       "      <td>...</td>\n",
       "      <td>White Fox</td>\n",
       "      <td>NaN</td>\n",
       "      <td>Eccentric scientist Rintarou Okabe has a never...</td>\n",
       "      <td>Steins;Gate</td>\n",
       "      <td>TV</td>\n",
       "      <td>https://myanimelist.net/anime/9253/Steins_Gate</td>\n",
       "      <td>https://cdn.myanimelist.net/images/anime/1935/...</td>\n",
       "      <td>https://www.youtube.com/watch?v=27OZc-ku6is</td>\n",
       "      <td>https://img.youtube.com/vi/27OZc-ku6is/maxresd...</td>\n",
       "      <td>Eccentric scientist Rintarou Okabe has a never...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>Apr 8, 2015 to Mar 30, 2016</td>\n",
       "      <td>28977.0</td>\n",
       "      <td>24 min. per ep.</td>\n",
       "      <td>51</td>\n",
       "      <td>Action, Comedy, Sci-Fi, Gag Humor, Historical,...</td>\n",
       "      <td>579687.0</td>\n",
       "      <td>335.0</td>\n",
       "      <td>Spring 2015</td>\n",
       "      <td>4.0</td>\n",
       "      <td>PG-13 - Teens 13 or older</td>\n",
       "      <td>...</td>\n",
       "      <td>Bandai Namco Pictures</td>\n",
       "      <td>Gintama' (2015)</td>\n",
       "      <td>Gintoki, Shinpachi, and Kagura return as the f...</td>\n",
       "      <td>Gintama Season 4</td>\n",
       "      <td>TV</td>\n",
       "      <td>https://myanimelist.net/anime/28977/Gintama°</td>\n",
       "      <td>https://cdn.myanimelist.net/images/anime/3/720...</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>Gintoki, Shinpachi, and Kagura return as the f...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>Apr 9, 2022 to Jun 25, 2022</td>\n",
       "      <td>43608.0</td>\n",
       "      <td>23 min. per ep.</td>\n",
       "      <td>13</td>\n",
       "      <td>Comedy, Romance, School, Seinen</td>\n",
       "      <td>763040.0</td>\n",
       "      <td>224.0</td>\n",
       "      <td>Spring 2022</td>\n",
       "      <td>5.0</td>\n",
       "      <td>PG-13 - Teens 13 or older</td>\n",
       "      <td>...</td>\n",
       "      <td>A-1 Pictures</td>\n",
       "      <td>Kaguya-sama wa Kokurasetai: Tensai-tachi no Re...</td>\n",
       "      <td>The elite members of Shuchiin Academy's studen...</td>\n",
       "      <td>Kaguya-sama: Love is War - Ultra Romantic</td>\n",
       "      <td>TV</td>\n",
       "      <td>https://myanimelist.net/anime/43608/Kaguya-sam...</td>\n",
       "      <td>https://cdn.myanimelist.net/images/anime/1160/...</td>\n",
       "      <td>https://www.youtube.com/watch?v=vFN5K-iAyV0</td>\n",
       "      <td>https://img.youtube.com/vi/vFN5K-iAyV0/maxresd...</td>\n",
       "      <td>The elite members of Shuchiin Academy's studen...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>...</th>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>16015</th>\n",
       "      <td>NaN</td>\n",
       "      <td>29503.0</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>...</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>https://myanimelist.net/anime/29503/Gararin_to...</td>\n",
       "      <td>https://cdn.myanimelist.net/images/anime/11/78...</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>Catepillar-like Gararin and stone-like Gororin...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>16016</th>\n",
       "      <td>NaN</td>\n",
       "      <td>33217.0</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>...</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>https://myanimelist.net/anime/33217/Hikaru_Gen...</td>\n",
       "      <td>https://cdn.myanimelist.net/images/anime/2/800...</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>The official music video for Hikaru Genji's si...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>16017</th>\n",
       "      <td>NaN</td>\n",
       "      <td>31491.0</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>...</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>https://myanimelist.net/anime/31491/Hantsu_x_T...</td>\n",
       "      <td>https://cdn.myanimelist.net/images/anime/7/758...</td>\n",
       "      <td>https://www.youtube.com/watch?v=anC_jNoHjEs</td>\n",
       "      <td>https://img.youtube.com/vi/anC_jNoHjEs/maxresd...</td>\n",
       "      <td>Hamaji Youhei, a high school freshman, lives h...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>16018</th>\n",
       "      <td>NaN</td>\n",
       "      <td>41797.0</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>...</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>https://myanimelist.net/anime/41797/Kangaeru_J...</td>\n",
       "      <td>https://cdn.myanimelist.net/images/anime/1846/...</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>A short rotoscope film by Mika Seike.</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>16019</th>\n",
       "      <td>NaN</td>\n",
       "      <td>23267.0</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>...</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>https://myanimelist.net/anime/23267/Osaru_no_T...</td>\n",
       "      <td>https://cdn.myanimelist.net/images/anime/11/60...</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "<p>16020 rows × 22 columns</p>\n",
       "</div>"
      ],
      "text/plain": [
       "                              aired  anime_id         duration episodes  \\\n",
       "0        Apr 5, 2009 to Jul 4, 2010    5114.0  24 min. per ep.       64   \n",
       "1      Oct 11, 2022 to Dec 27, 2022   41467.0  24 min. per ep.       13   \n",
       "2       Apr 6, 2011 to Sep 14, 2011    9253.0  24 min. per ep.       24   \n",
       "3       Apr 8, 2015 to Mar 30, 2016   28977.0  24 min. per ep.       51   \n",
       "4       Apr 9, 2022 to Jun 25, 2022   43608.0  23 min. per ep.       13   \n",
       "...                             ...       ...              ...      ...   \n",
       "16015                           NaN   29503.0              NaN      NaN   \n",
       "16016                           NaN   33217.0              NaN      NaN   \n",
       "16017                           NaN   31491.0              NaN      NaN   \n",
       "16018                           NaN   41797.0              NaN      NaN   \n",
       "16019                           NaN   23267.0              NaN      NaN   \n",
       "\n",
       "                                                  genres    members  \\\n",
       "0      Action, Adventure, Drama, Fantasy, Military, S...  3097493.0   \n",
       "1                    Action, Adventure, Fantasy, Shounen   395558.0   \n",
       "2      Drama, Sci-Fi, Suspense, Psychological, Time T...  2385549.0   \n",
       "3      Action, Comedy, Sci-Fi, Gag Humor, Historical,...   579687.0   \n",
       "4                        Comedy, Romance, School, Seinen   763040.0   \n",
       "...                                                  ...        ...   \n",
       "16015                                                NaN        NaN   \n",
       "16016                                                NaN        NaN   \n",
       "16017                                                NaN        NaN   \n",
       "16018                                                NaN        NaN   \n",
       "16019                                                NaN        NaN   \n",
       "\n",
       "       popularity    premiered  ranked                          rating  ...  \\\n",
       "0             3.0  Spring 2009     1.0  R - 17+ (violence & profanity)  ...   \n",
       "1           518.0    Fall 2022     2.0  R - 17+ (violence & profanity)  ...   \n",
       "2            13.0  Spring 2011     3.0       PG-13 - Teens 13 or older  ...   \n",
       "3           335.0  Spring 2015     4.0       PG-13 - Teens 13 or older  ...   \n",
       "4           224.0  Spring 2022     5.0       PG-13 - Teens 13 or older  ...   \n",
       "...           ...          ...     ...                             ...  ...   \n",
       "16015         NaN          NaN     NaN                             NaN  ...   \n",
       "16016         NaN          NaN     NaN                             NaN  ...   \n",
       "16017         NaN          NaN     NaN                             NaN  ...   \n",
       "16018         NaN          NaN     NaN                             NaN  ...   \n",
       "16019         NaN          NaN     NaN                             NaN  ...   \n",
       "\n",
       "                     studios  \\\n",
       "0                      Bones   \n",
       "1                    Pierrot   \n",
       "2                  White Fox   \n",
       "3      Bandai Namco Pictures   \n",
       "4               A-1 Pictures   \n",
       "...                      ...   \n",
       "16015                    NaN   \n",
       "16016                    NaN   \n",
       "16017                    NaN   \n",
       "16018                    NaN   \n",
       "16019                    NaN   \n",
       "\n",
       "                                                synonyms  \\\n",
       "0      Hagane no Renkinjutsushi: Fullmetal Alchemist,...   \n",
       "1                    Bleach: Thousand-Year Blood War Arc   \n",
       "2                                                    NaN   \n",
       "3                                        Gintama' (2015)   \n",
       "4      Kaguya-sama wa Kokurasetai: Tensai-tachi no Re...   \n",
       "...                                                  ...   \n",
       "16015                                                NaN   \n",
       "16016                                                NaN   \n",
       "16017                                                NaN   \n",
       "16018                                                NaN   \n",
       "16019                                                NaN   \n",
       "\n",
       "                                              synopsis_x  \\\n",
       "0      After a horrific alchemy experiment goes wrong...   \n",
       "1      Substitute Soul Reaper Ichigo Kurosaki spends ...   \n",
       "2      Eccentric scientist Rintarou Okabe has a never...   \n",
       "3      Gintoki, Shinpachi, and Kagura return as the f...   \n",
       "4      The elite members of Shuchiin Academy's studen...   \n",
       "...                                                  ...   \n",
       "16015                                                NaN   \n",
       "16016                                                NaN   \n",
       "16017                                                NaN   \n",
       "16018                                                NaN   \n",
       "16019                                                NaN   \n",
       "\n",
       "                                           title titletype  \\\n",
       "0               Fullmetal Alchemist: Brotherhood        TV   \n",
       "1                Bleach: Thousand-Year Blood War        TV   \n",
       "2                                    Steins;Gate        TV   \n",
       "3                               Gintama Season 4        TV   \n",
       "4      Kaguya-sama: Love is War - Ultra Romantic        TV   \n",
       "...                                          ...       ...   \n",
       "16015                                        NaN       NaN   \n",
       "16016                                        NaN       NaN   \n",
       "16017                                        NaN       NaN   \n",
       "16018                                        NaN       NaN   \n",
       "16019                                        NaN       NaN   \n",
       "\n",
       "                                               anime_url  \\\n",
       "0      https://myanimelist.net/anime/5114/Fullmetal_A...   \n",
       "1      https://myanimelist.net/anime/41467/Bleach__Se...   \n",
       "2         https://myanimelist.net/anime/9253/Steins_Gate   \n",
       "3           https://myanimelist.net/anime/28977/Gintama°   \n",
       "4      https://myanimelist.net/anime/43608/Kaguya-sam...   \n",
       "...                                                  ...   \n",
       "16015  https://myanimelist.net/anime/29503/Gararin_to...   \n",
       "16016  https://myanimelist.net/anime/33217/Hikaru_Gen...   \n",
       "16017  https://myanimelist.net/anime/31491/Hantsu_x_T...   \n",
       "16018  https://myanimelist.net/anime/41797/Kangaeru_J...   \n",
       "16019  https://myanimelist.net/anime/23267/Osaru_no_T...   \n",
       "\n",
       "                                                  poster  \\\n",
       "0      https://cdn.myanimelist.net/images/anime/1208/...   \n",
       "1      https://cdn.myanimelist.net/images/anime/1764/...   \n",
       "2      https://cdn.myanimelist.net/images/anime/1935/...   \n",
       "3      https://cdn.myanimelist.net/images/anime/3/720...   \n",
       "4      https://cdn.myanimelist.net/images/anime/1160/...   \n",
       "...                                                  ...   \n",
       "16015  https://cdn.myanimelist.net/images/anime/11/78...   \n",
       "16016  https://cdn.myanimelist.net/images/anime/2/800...   \n",
       "16017  https://cdn.myanimelist.net/images/anime/7/758...   \n",
       "16018  https://cdn.myanimelist.net/images/anime/1846/...   \n",
       "16019  https://cdn.myanimelist.net/images/anime/11/60...   \n",
       "\n",
       "                                           trailer  \\\n",
       "0      https://www.youtube.com/watch?v=--IcmZkvL0Q   \n",
       "1      https://www.youtube.com/watch?v=e8YBesRKq_U   \n",
       "2      https://www.youtube.com/watch?v=27OZc-ku6is   \n",
       "3                                              NaN   \n",
       "4      https://www.youtube.com/watch?v=vFN5K-iAyV0   \n",
       "...                                            ...   \n",
       "16015                                          NaN   \n",
       "16016                                          NaN   \n",
       "16017  https://www.youtube.com/watch?v=anC_jNoHjEs   \n",
       "16018                                          NaN   \n",
       "16019                                          NaN   \n",
       "\n",
       "                                       trailer_thumbnail  \\\n",
       "0      https://img.youtube.com/vi/--IcmZkvL0Q/maxresd...   \n",
       "1      https://img.youtube.com/vi/e8YBesRKq_U/maxresd...   \n",
       "2      https://img.youtube.com/vi/27OZc-ku6is/maxresd...   \n",
       "3                                                    NaN   \n",
       "4      https://img.youtube.com/vi/vFN5K-iAyV0/maxresd...   \n",
       "...                                                  ...   \n",
       "16015                                                NaN   \n",
       "16016                                                NaN   \n",
       "16017  https://img.youtube.com/vi/anC_jNoHjEs/maxresd...   \n",
       "16018                                                NaN   \n",
       "16019                                                NaN   \n",
       "\n",
       "                                              synopsis_y  \n",
       "0      After a horrific alchemy experiment goes wrong...  \n",
       "1      Substitute Soul Reaper Ichigo Kurosaki spends ...  \n",
       "2      Eccentric scientist Rintarou Okabe has a never...  \n",
       "3      Gintoki, Shinpachi, and Kagura return as the f...  \n",
       "4      The elite members of Shuchiin Academy's studen...  \n",
       "...                                                  ...  \n",
       "16015  Catepillar-like Gararin and stone-like Gororin...  \n",
       "16016  The official music video for Hikaru Genji's si...  \n",
       "16017  Hamaji Youhei, a high school freshman, lives h...  \n",
       "16018              A short rotoscope film by Mika Seike.  \n",
       "16019                                                NaN  \n",
       "\n",
       "[16020 rows x 22 columns]"
      ]
     },
     "execution_count": 309,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df = pd.read_csv('animes.csv', encoding='utf-8')\n",
    "df"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 310,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "<class 'pandas.core.frame.DataFrame'>\n",
      "RangeIndex: 16020 entries, 0 to 16019\n",
      "Data columns (total 22 columns):\n",
      " #   Column             Non-Null Count  Dtype  \n",
      "---  ------             --------------  -----  \n",
      " 0   aired              16000 non-null  object \n",
      " 1   anime_id           16010 non-null  float64\n",
      " 2   duration           16000 non-null  object \n",
      " 3   episodes           16000 non-null  object \n",
      " 4   genres             15940 non-null  object \n",
      " 5   members            16000 non-null  float64\n",
      " 6   popularity         16000 non-null  float64\n",
      " 7   premiered          5132 non-null   object \n",
      " 8   ranked             16000 non-null  float64\n",
      " 9   rating             16000 non-null  object \n",
      " 10  score              14281 non-null  float64\n",
      " 11  status             16000 non-null  object \n",
      " 12  studios            16000 non-null  object \n",
      " 13  synonyms           9750 non-null   object \n",
      " 14  synopsis_x         15996 non-null  object \n",
      " 15  title              16000 non-null  object \n",
      " 16  titletype          16000 non-null  object \n",
      " 17  anime_url          15554 non-null  object \n",
      " 18  poster             15554 non-null  object \n",
      " 19  trailer            4237 non-null   object \n",
      " 20  trailer_thumbnail  4237 non-null   object \n",
      " 21  synopsis_y         14746 non-null  object \n",
      "dtypes: float64(5), object(17)\n",
      "memory usage: 2.7+ MB\n"
     ]
    }
   ],
   "source": [
    "df.info()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 311,
   "metadata": {},
   "outputs": [],
   "source": [
    "# drop trailer, trailer_thumbnail, premiered, synopsis_y\n",
    "df.drop(['trailer', 'trailer_thumbnail', 'premiered', 'synopsis_y'], axis=1, inplace=True)\n",
    "# drop null aired\n",
    "df.dropna(subset=['aired'], inplace=True)\n",
    "# drop null genres\n",
    "df.dropna(subset=['genres'], inplace=True)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 312,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "<class 'pandas.core.frame.DataFrame'>\n",
      "Int64Index: 15940 entries, 0 to 15999\n",
      "Data columns (total 18 columns):\n",
      " #   Column      Non-Null Count  Dtype  \n",
      "---  ------      --------------  -----  \n",
      " 0   aired       15940 non-null  object \n",
      " 1   anime_id    15940 non-null  float64\n",
      " 2   duration    15940 non-null  object \n",
      " 3   episodes    15940 non-null  object \n",
      " 4   genres      15940 non-null  object \n",
      " 5   members     15940 non-null  float64\n",
      " 6   popularity  15940 non-null  float64\n",
      " 7   ranked      15940 non-null  float64\n",
      " 8   rating      15940 non-null  object \n",
      " 9   score       14233 non-null  float64\n",
      " 10  status      15940 non-null  object \n",
      " 11  studios     15940 non-null  object \n",
      " 12  synonyms    9726 non-null   object \n",
      " 13  synopsis_x  15936 non-null  object \n",
      " 14  title       15940 non-null  object \n",
      " 15  titletype   15940 non-null  object \n",
      " 16  anime_url   15488 non-null  object \n",
      " 17  poster      15488 non-null  object \n",
      "dtypes: float64(5), object(13)\n",
      "memory usage: 2.3+ MB\n"
     ]
    }
   ],
   "source": [
    "df.info()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 313,
   "metadata": {},
   "outputs": [],
   "source": [
    "# print all nan data in genres\n",
    "# df[df['genres'].isnull()]"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 314,
   "metadata": {},
   "outputs": [],
   "source": [
    "title = []\n",
    "episodes = []\n",
    "score = []\n",
    "link = []\n",
    "genre = []\n",
    "synopsis = []\n",
    "duration = []\n",
    "aired = []\n",
    "members = []\n",
    "rating = []\n",
    "status = []\n",
    "studio = []\n",
    "titletype = []"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 315,
   "metadata": {},
   "outputs": [],
   "source": [
    "for index, data in df.iterrows():\n",
    "    data_genre = data['genres'] \n",
    "    data_genre = data_genre.split(',')\n",
    "    \n",
    "    for dg in data_genre:\n",
    "        genre.append(dg)\n",
    "        title.append(data['title'])\n",
    "        episodes.append(data['episodes'])\n",
    "        score.append(data['score'])\n",
    "        link.append(data['poster'])\n",
    "        synopsis.append(data['synopsis_x'])\n",
    "        duration.append(data['duration'])\n",
    "        aired.append(data['aired'])\n",
    "        members.append(data['members'])\n",
    "        rating.append(data['rating'])\n",
    "        status.append(data['status'])\n",
    "        studio.append(data['studios'])\n",
    "        titletype.append(data['titletype'])\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 316,
   "metadata": {},
   "outputs": [],
   "source": [
    "dict = {\n",
    "        'title': title,\n",
    "        'link': link,\n",
    "        'episodes': episodes,\n",
    "        'score': score,\n",
    "        'genre': genre,\n",
    "        'synopsis': synopsis,\n",
    "        'duration': duration,\n",
    "        'aired': aired,\n",
    "        'members': members,\n",
    "        'rating': rating,\n",
    "        'status': status,\n",
    "        'studio': studio,\n",
    "        'title_type': titletype\n",
    "        } \n",
    "myal = pd.DataFrame(dict)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 317,
   "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>link</th>\n",
       "      <th>episodes</th>\n",
       "      <th>score</th>\n",
       "      <th>genre</th>\n",
       "      <th>synopsis</th>\n",
       "      <th>duration</th>\n",
       "      <th>aired</th>\n",
       "      <th>members</th>\n",
       "      <th>rating</th>\n",
       "      <th>status</th>\n",
       "      <th>studio</th>\n",
       "      <th>title_type</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>Fullmetal Alchemist: Brotherhood</td>\n",
       "      <td>https://cdn.myanimelist.net/images/anime/1208/...</td>\n",
       "      <td>64</td>\n",
       "      <td>9.11</td>\n",
       "      <td>Action</td>\n",
       "      <td>After a horrific alchemy experiment goes wrong...</td>\n",
       "      <td>24 min. per ep.</td>\n",
       "      <td>Apr 5, 2009 to Jul 4, 2010</td>\n",
       "      <td>3097493.0</td>\n",
       "      <td>R - 17+ (violence &amp; profanity)</td>\n",
       "      <td>Finished Airing</td>\n",
       "      <td>Bones</td>\n",
       "      <td>TV</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>Fullmetal Alchemist: Brotherhood</td>\n",
       "      <td>https://cdn.myanimelist.net/images/anime/1208/...</td>\n",
       "      <td>64</td>\n",
       "      <td>9.11</td>\n",
       "      <td>Adventure</td>\n",
       "      <td>After a horrific alchemy experiment goes wrong...</td>\n",
       "      <td>24 min. per ep.</td>\n",
       "      <td>Apr 5, 2009 to Jul 4, 2010</td>\n",
       "      <td>3097493.0</td>\n",
       "      <td>R - 17+ (violence &amp; profanity)</td>\n",
       "      <td>Finished Airing</td>\n",
       "      <td>Bones</td>\n",
       "      <td>TV</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>Fullmetal Alchemist: Brotherhood</td>\n",
       "      <td>https://cdn.myanimelist.net/images/anime/1208/...</td>\n",
       "      <td>64</td>\n",
       "      <td>9.11</td>\n",
       "      <td>Drama</td>\n",
       "      <td>After a horrific alchemy experiment goes wrong...</td>\n",
       "      <td>24 min. per ep.</td>\n",
       "      <td>Apr 5, 2009 to Jul 4, 2010</td>\n",
       "      <td>3097493.0</td>\n",
       "      <td>R - 17+ (violence &amp; profanity)</td>\n",
       "      <td>Finished Airing</td>\n",
       "      <td>Bones</td>\n",
       "      <td>TV</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>Fullmetal Alchemist: Brotherhood</td>\n",
       "      <td>https://cdn.myanimelist.net/images/anime/1208/...</td>\n",
       "      <td>64</td>\n",
       "      <td>9.11</td>\n",
       "      <td>Fantasy</td>\n",
       "      <td>After a horrific alchemy experiment goes wrong...</td>\n",
       "      <td>24 min. per ep.</td>\n",
       "      <td>Apr 5, 2009 to Jul 4, 2010</td>\n",
       "      <td>3097493.0</td>\n",
       "      <td>R - 17+ (violence &amp; profanity)</td>\n",
       "      <td>Finished Airing</td>\n",
       "      <td>Bones</td>\n",
       "      <td>TV</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>Fullmetal Alchemist: Brotherhood</td>\n",
       "      <td>https://cdn.myanimelist.net/images/anime/1208/...</td>\n",
       "      <td>64</td>\n",
       "      <td>9.11</td>\n",
       "      <td>Military</td>\n",
       "      <td>After a horrific alchemy experiment goes wrong...</td>\n",
       "      <td>24 min. per ep.</td>\n",
       "      <td>Apr 5, 2009 to Jul 4, 2010</td>\n",
       "      <td>3097493.0</td>\n",
       "      <td>R - 17+ (violence &amp; profanity)</td>\n",
       "      <td>Finished Airing</td>\n",
       "      <td>Bones</td>\n",
       "      <td>TV</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "                              title  \\\n",
       "0  Fullmetal Alchemist: Brotherhood   \n",
       "1  Fullmetal Alchemist: Brotherhood   \n",
       "2  Fullmetal Alchemist: Brotherhood   \n",
       "3  Fullmetal Alchemist: Brotherhood   \n",
       "4  Fullmetal Alchemist: Brotherhood   \n",
       "\n",
       "                                                link episodes  score  \\\n",
       "0  https://cdn.myanimelist.net/images/anime/1208/...       64   9.11   \n",
       "1  https://cdn.myanimelist.net/images/anime/1208/...       64   9.11   \n",
       "2  https://cdn.myanimelist.net/images/anime/1208/...       64   9.11   \n",
       "3  https://cdn.myanimelist.net/images/anime/1208/...       64   9.11   \n",
       "4  https://cdn.myanimelist.net/images/anime/1208/...       64   9.11   \n",
       "\n",
       "        genre                                           synopsis  \\\n",
       "0      Action  After a horrific alchemy experiment goes wrong...   \n",
       "1   Adventure  After a horrific alchemy experiment goes wrong...   \n",
       "2       Drama  After a horrific alchemy experiment goes wrong...   \n",
       "3     Fantasy  After a horrific alchemy experiment goes wrong...   \n",
       "4    Military  After a horrific alchemy experiment goes wrong...   \n",
       "\n",
       "          duration                       aired    members  \\\n",
       "0  24 min. per ep.  Apr 5, 2009 to Jul 4, 2010  3097493.0   \n",
       "1  24 min. per ep.  Apr 5, 2009 to Jul 4, 2010  3097493.0   \n",
       "2  24 min. per ep.  Apr 5, 2009 to Jul 4, 2010  3097493.0   \n",
       "3  24 min. per ep.  Apr 5, 2009 to Jul 4, 2010  3097493.0   \n",
       "4  24 min. per ep.  Apr 5, 2009 to Jul 4, 2010  3097493.0   \n",
       "\n",
       "                           rating           status studio title_type  \n",
       "0  R - 17+ (violence & profanity)  Finished Airing  Bones         TV  \n",
       "1  R - 17+ (violence & profanity)  Finished Airing  Bones         TV  \n",
       "2  R - 17+ (violence & profanity)  Finished Airing  Bones         TV  \n",
       "3  R - 17+ (violence & profanity)  Finished Airing  Bones         TV  \n",
       "4  R - 17+ (violence & profanity)  Finished Airing  Bones         TV  "
      ]
     },
     "execution_count": 317,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "myal.head()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 318,
   "metadata": {},
   "outputs": [],
   "source": [
    "# Trim genre\n",
    "myal['genre'] = myal['genre'].str.strip()\n",
    "# Replace unknown episodes with null\n",
    "myal['episodes'] = myal['episodes'].replace('Unknown', np.nan)\n",
    "# Replace unknown duration with null\n",
    "myal['duration'] = myal['duration'].replace('Unknown', np.nan)\n",
    "# Replace 'None found,' studio with null\n",
    "myal['studio'] = myal['studio'].replace('None found,', np.nan)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 319,
   "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>link</th>\n",
       "      <th>episodes</th>\n",
       "      <th>score</th>\n",
       "      <th>genre</th>\n",
       "      <th>synopsis</th>\n",
       "      <th>duration</th>\n",
       "      <th>aired</th>\n",
       "      <th>members</th>\n",
       "      <th>rating</th>\n",
       "      <th>status</th>\n",
       "      <th>studio</th>\n",
       "      <th>title_type</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>Fullmetal Alchemist: Brotherhood</td>\n",
       "      <td>https://cdn.myanimelist.net/images/anime/1208/...</td>\n",
       "      <td>64</td>\n",
       "      <td>9.11</td>\n",
       "      <td>Action</td>\n",
       "      <td>After a horrific alchemy experiment goes wrong...</td>\n",
       "      <td>24 min. per ep.</td>\n",
       "      <td>Apr 5, 2009 to Jul 4, 2010</td>\n",
       "      <td>3097493.0</td>\n",
       "      <td>R - 17+ (violence &amp; profanity)</td>\n",
       "      <td>Finished Airing</td>\n",
       "      <td>Bones</td>\n",
       "      <td>TV</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>Fullmetal Alchemist: Brotherhood</td>\n",
       "      <td>https://cdn.myanimelist.net/images/anime/1208/...</td>\n",
       "      <td>64</td>\n",
       "      <td>9.11</td>\n",
       "      <td>Adventure</td>\n",
       "      <td>After a horrific alchemy experiment goes wrong...</td>\n",
       "      <td>24 min. per ep.</td>\n",
       "      <td>Apr 5, 2009 to Jul 4, 2010</td>\n",
       "      <td>3097493.0</td>\n",
       "      <td>R - 17+ (violence &amp; profanity)</td>\n",
       "      <td>Finished Airing</td>\n",
       "      <td>Bones</td>\n",
       "      <td>TV</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>Fullmetal Alchemist: Brotherhood</td>\n",
       "      <td>https://cdn.myanimelist.net/images/anime/1208/...</td>\n",
       "      <td>64</td>\n",
       "      <td>9.11</td>\n",
       "      <td>Drama</td>\n",
       "      <td>After a horrific alchemy experiment goes wrong...</td>\n",
       "      <td>24 min. per ep.</td>\n",
       "      <td>Apr 5, 2009 to Jul 4, 2010</td>\n",
       "      <td>3097493.0</td>\n",
       "      <td>R - 17+ (violence &amp; profanity)</td>\n",
       "      <td>Finished Airing</td>\n",
       "      <td>Bones</td>\n",
       "      <td>TV</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>Fullmetal Alchemist: Brotherhood</td>\n",
       "      <td>https://cdn.myanimelist.net/images/anime/1208/...</td>\n",
       "      <td>64</td>\n",
       "      <td>9.11</td>\n",
       "      <td>Fantasy</td>\n",
       "      <td>After a horrific alchemy experiment goes wrong...</td>\n",
       "      <td>24 min. per ep.</td>\n",
       "      <td>Apr 5, 2009 to Jul 4, 2010</td>\n",
       "      <td>3097493.0</td>\n",
       "      <td>R - 17+ (violence &amp; profanity)</td>\n",
       "      <td>Finished Airing</td>\n",
       "      <td>Bones</td>\n",
       "      <td>TV</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>Fullmetal Alchemist: Brotherhood</td>\n",
       "      <td>https://cdn.myanimelist.net/images/anime/1208/...</td>\n",
       "      <td>64</td>\n",
       "      <td>9.11</td>\n",
       "      <td>Military</td>\n",
       "      <td>After a horrific alchemy experiment goes wrong...</td>\n",
       "      <td>24 min. per ep.</td>\n",
       "      <td>Apr 5, 2009 to Jul 4, 2010</td>\n",
       "      <td>3097493.0</td>\n",
       "      <td>R - 17+ (violence &amp; profanity)</td>\n",
       "      <td>Finished Airing</td>\n",
       "      <td>Bones</td>\n",
       "      <td>TV</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "                              title  \\\n",
       "0  Fullmetal Alchemist: Brotherhood   \n",
       "1  Fullmetal Alchemist: Brotherhood   \n",
       "2  Fullmetal Alchemist: Brotherhood   \n",
       "3  Fullmetal Alchemist: Brotherhood   \n",
       "4  Fullmetal Alchemist: Brotherhood   \n",
       "\n",
       "                                                link episodes  score  \\\n",
       "0  https://cdn.myanimelist.net/images/anime/1208/...       64   9.11   \n",
       "1  https://cdn.myanimelist.net/images/anime/1208/...       64   9.11   \n",
       "2  https://cdn.myanimelist.net/images/anime/1208/...       64   9.11   \n",
       "3  https://cdn.myanimelist.net/images/anime/1208/...       64   9.11   \n",
       "4  https://cdn.myanimelist.net/images/anime/1208/...       64   9.11   \n",
       "\n",
       "       genre                                           synopsis  \\\n",
       "0     Action  After a horrific alchemy experiment goes wrong...   \n",
       "1  Adventure  After a horrific alchemy experiment goes wrong...   \n",
       "2      Drama  After a horrific alchemy experiment goes wrong...   \n",
       "3    Fantasy  After a horrific alchemy experiment goes wrong...   \n",
       "4   Military  After a horrific alchemy experiment goes wrong...   \n",
       "\n",
       "          duration                       aired    members  \\\n",
       "0  24 min. per ep.  Apr 5, 2009 to Jul 4, 2010  3097493.0   \n",
       "1  24 min. per ep.  Apr 5, 2009 to Jul 4, 2010  3097493.0   \n",
       "2  24 min. per ep.  Apr 5, 2009 to Jul 4, 2010  3097493.0   \n",
       "3  24 min. per ep.  Apr 5, 2009 to Jul 4, 2010  3097493.0   \n",
       "4  24 min. per ep.  Apr 5, 2009 to Jul 4, 2010  3097493.0   \n",
       "\n",
       "                           rating           status studio title_type  \n",
       "0  R - 17+ (violence & profanity)  Finished Airing  Bones         TV  \n",
       "1  R - 17+ (violence & profanity)  Finished Airing  Bones         TV  \n",
       "2  R - 17+ (violence & profanity)  Finished Airing  Bones         TV  \n",
       "3  R - 17+ (violence & profanity)  Finished Airing  Bones         TV  \n",
       "4  R - 17+ (violence & profanity)  Finished Airing  Bones         TV  "
      ]
     },
     "execution_count": 319,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "myal.head()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 320,
   "metadata": {},
   "outputs": [
    {
     "name": "stderr",
     "output_type": "stream",
     "text": [
      "C:\\Users\\User\\AppData\\Local\\Temp\\ipykernel_23780\\1323324221.py:2: SettingWithCopyWarning: \n",
      "A value is trying to be set on a copy of a slice from a DataFrame\n",
      "\n",
      "See the caveats in the documentation: https://pandas.pydata.org/pandas-docs/stable/user_guide/indexing.html#returning-a-view-versus-a-copy\n",
      "  anime.drop_duplicates('title', inplace = True)\n",
      "C:\\Users\\User\\AppData\\Local\\Temp\\ipykernel_23780\\1323324221.py:4: SettingWithCopyWarning: \n",
      "A value is trying to be set on a copy of a slice from a DataFrame.\n",
      "Try using .loc[row_indexer,col_indexer] = value instead\n",
      "\n",
      "See the caveats in the documentation: https://pandas.pydata.org/pandas-docs/stable/user_guide/indexing.html#returning-a-view-versus-a-copy\n",
      "  anime['id'] = anime.index + 1\n"
     ]
    },
    {
     "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>episodes</th>\n",
       "      <th>link</th>\n",
       "      <th>score</th>\n",
       "      <th>synopsis</th>\n",
       "      <th>duration</th>\n",
       "      <th>aired</th>\n",
       "      <th>members</th>\n",
       "      <th>rating</th>\n",
       "      <th>status</th>\n",
       "      <th>studio</th>\n",
       "      <th>title_type</th>\n",
       "      <th>id</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>Fullmetal Alchemist: Brotherhood</td>\n",
       "      <td>64</td>\n",
       "      <td>https://cdn.myanimelist.net/images/anime/1208/...</td>\n",
       "      <td>9.11</td>\n",
       "      <td>After a horrific alchemy experiment goes wrong...</td>\n",
       "      <td>24 min. per ep.</td>\n",
       "      <td>Apr 5, 2009 to Jul 4, 2010</td>\n",
       "      <td>3097493.0</td>\n",
       "      <td>R - 17+ (violence &amp; profanity)</td>\n",
       "      <td>Finished Airing</td>\n",
       "      <td>Bones</td>\n",
       "      <td>TV</td>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>Bleach: Thousand-Year Blood War</td>\n",
       "      <td>13</td>\n",
       "      <td>https://cdn.myanimelist.net/images/anime/1764/...</td>\n",
       "      <td>9.10</td>\n",
       "      <td>Substitute Soul Reaper Ichigo Kurosaki spends ...</td>\n",
       "      <td>24 min. per ep.</td>\n",
       "      <td>Oct 11, 2022 to Dec 27, 2022</td>\n",
       "      <td>395558.0</td>\n",
       "      <td>R - 17+ (violence &amp; profanity)</td>\n",
       "      <td>Finished Airing</td>\n",
       "      <td>Pierrot</td>\n",
       "      <td>TV</td>\n",
       "      <td>2</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>Steins;Gate</td>\n",
       "      <td>24</td>\n",
       "      <td>https://cdn.myanimelist.net/images/anime/1935/...</td>\n",
       "      <td>9.08</td>\n",
       "      <td>Eccentric scientist Rintarou Okabe has a never...</td>\n",
       "      <td>24 min. per ep.</td>\n",
       "      <td>Apr 6, 2011 to Sep 14, 2011</td>\n",
       "      <td>2385549.0</td>\n",
       "      <td>PG-13 - Teens 13 or older</td>\n",
       "      <td>Finished Airing</td>\n",
       "      <td>White Fox</td>\n",
       "      <td>TV</td>\n",
       "      <td>3</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>Gintama Season 4</td>\n",
       "      <td>51</td>\n",
       "      <td>https://cdn.myanimelist.net/images/anime/3/720...</td>\n",
       "      <td>9.07</td>\n",
       "      <td>Gintoki, Shinpachi, and Kagura return as the f...</td>\n",
       "      <td>24 min. per ep.</td>\n",
       "      <td>Apr 8, 2015 to Mar 30, 2016</td>\n",
       "      <td>579687.0</td>\n",
       "      <td>PG-13 - Teens 13 or older</td>\n",
       "      <td>Finished Airing</td>\n",
       "      <td>Bandai Namco Pictures</td>\n",
       "      <td>TV</td>\n",
       "      <td>4</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>Kaguya-sama: Love is War - Ultra Romantic</td>\n",
       "      <td>13</td>\n",
       "      <td>https://cdn.myanimelist.net/images/anime/1160/...</td>\n",
       "      <td>9.07</td>\n",
       "      <td>The elite members of Shuchiin Academy's studen...</td>\n",
       "      <td>23 min. per ep.</td>\n",
       "      <td>Apr 9, 2022 to Jun 25, 2022</td>\n",
       "      <td>763040.0</td>\n",
       "      <td>PG-13 - Teens 13 or older</td>\n",
       "      <td>Finished Airing</td>\n",
       "      <td>A-1 Pictures</td>\n",
       "      <td>TV</td>\n",
       "      <td>5</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>...</th>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>15270</th>\n",
       "      <td>Where the Sea Sleeps</td>\n",
       "      <td>1</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>Animated music video for the song  Where the S...</td>\n",
       "      <td>4 min.</td>\n",
       "      <td>Aug 31, 2020</td>\n",
       "      <td>144.0</td>\n",
       "      <td>G - All Ages</td>\n",
       "      <td>Finished Airing</td>\n",
       "      <td>NaN</td>\n",
       "      <td>Music</td>\n",
       "      <td>15271</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>15271</th>\n",
       "      <td>PalaMonki</td>\n",
       "      <td>26</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>No synopsis information has been added to this...</td>\n",
       "      <td>7 min. per ep.</td>\n",
       "      <td>Aug 2019 to 2019</td>\n",
       "      <td>66.0</td>\n",
       "      <td>PG - Children</td>\n",
       "      <td>Finished Airing</td>\n",
       "      <td>NaN</td>\n",
       "      <td>TV</td>\n",
       "      <td>15272</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>15272</th>\n",
       "      <td>PalaMonki 2</td>\n",
       "      <td>26</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>No synopsis information has been added to this...</td>\n",
       "      <td>7 min. per ep.</td>\n",
       "      <td>2020</td>\n",
       "      <td>64.0</td>\n",
       "      <td>PG - Children</td>\n",
       "      <td>Finished Airing</td>\n",
       "      <td>NaN</td>\n",
       "      <td>TV</td>\n",
       "      <td>15273</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>15273</th>\n",
       "      <td>Pai Le Qi Tong</td>\n",
       "      <td>20</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>No synopsis information has been added to this...</td>\n",
       "      <td>9 min. per ep.</td>\n",
       "      <td>Jul 6, 2009 to ?</td>\n",
       "      <td>33.0</td>\n",
       "      <td>PG - Children</td>\n",
       "      <td>Finished Airing</td>\n",
       "      <td>NaN</td>\n",
       "      <td>TV</td>\n",
       "      <td>15274</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>15274</th>\n",
       "      <td>Pakkororin</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>A minute long children's anime that follows 3 ...</td>\n",
       "      <td>1 min.</td>\n",
       "      <td>Mar 28, 2011 to ?</td>\n",
       "      <td>290.0</td>\n",
       "      <td>G - All Ages</td>\n",
       "      <td>Currently Airing</td>\n",
       "      <td>NaN</td>\n",
       "      <td>TV</td>\n",
       "      <td>15275</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "<p>15275 rows × 13 columns</p>\n",
       "</div>"
      ],
      "text/plain": [
       "                                           title episodes  \\\n",
       "0               Fullmetal Alchemist: Brotherhood       64   \n",
       "1                Bleach: Thousand-Year Blood War       13   \n",
       "2                                    Steins;Gate       24   \n",
       "3                               Gintama Season 4       51   \n",
       "4      Kaguya-sama: Love is War - Ultra Romantic       13   \n",
       "...                                          ...      ...   \n",
       "15270                       Where the Sea Sleeps        1   \n",
       "15271                                  PalaMonki       26   \n",
       "15272                                PalaMonki 2       26   \n",
       "15273                             Pai Le Qi Tong       20   \n",
       "15274                                 Pakkororin      NaN   \n",
       "\n",
       "                                                    link  score  \\\n",
       "0      https://cdn.myanimelist.net/images/anime/1208/...   9.11   \n",
       "1      https://cdn.myanimelist.net/images/anime/1764/...   9.10   \n",
       "2      https://cdn.myanimelist.net/images/anime/1935/...   9.08   \n",
       "3      https://cdn.myanimelist.net/images/anime/3/720...   9.07   \n",
       "4      https://cdn.myanimelist.net/images/anime/1160/...   9.07   \n",
       "...                                                  ...    ...   \n",
       "15270                                                NaN    NaN   \n",
       "15271                                                NaN    NaN   \n",
       "15272                                                NaN    NaN   \n",
       "15273                                                NaN    NaN   \n",
       "15274                                                NaN    NaN   \n",
       "\n",
       "                                                synopsis         duration  \\\n",
       "0      After a horrific alchemy experiment goes wrong...  24 min. per ep.   \n",
       "1      Substitute Soul Reaper Ichigo Kurosaki spends ...  24 min. per ep.   \n",
       "2      Eccentric scientist Rintarou Okabe has a never...  24 min. per ep.   \n",
       "3      Gintoki, Shinpachi, and Kagura return as the f...  24 min. per ep.   \n",
       "4      The elite members of Shuchiin Academy's studen...  23 min. per ep.   \n",
       "...                                                  ...              ...   \n",
       "15270  Animated music video for the song  Where the S...           4 min.   \n",
       "15271  No synopsis information has been added to this...   7 min. per ep.   \n",
       "15272  No synopsis information has been added to this...   7 min. per ep.   \n",
       "15273  No synopsis information has been added to this...   9 min. per ep.   \n",
       "15274  A minute long children's anime that follows 3 ...           1 min.   \n",
       "\n",
       "                              aired    members  \\\n",
       "0        Apr 5, 2009 to Jul 4, 2010  3097493.0   \n",
       "1      Oct 11, 2022 to Dec 27, 2022   395558.0   \n",
       "2       Apr 6, 2011 to Sep 14, 2011  2385549.0   \n",
       "3       Apr 8, 2015 to Mar 30, 2016   579687.0   \n",
       "4       Apr 9, 2022 to Jun 25, 2022   763040.0   \n",
       "...                             ...        ...   \n",
       "15270                  Aug 31, 2020      144.0   \n",
       "15271              Aug 2019 to 2019       66.0   \n",
       "15272                          2020       64.0   \n",
       "15273              Jul 6, 2009 to ?       33.0   \n",
       "15274             Mar 28, 2011 to ?      290.0   \n",
       "\n",
       "                               rating            status  \\\n",
       "0      R - 17+ (violence & profanity)   Finished Airing   \n",
       "1      R - 17+ (violence & profanity)   Finished Airing   \n",
       "2           PG-13 - Teens 13 or older   Finished Airing   \n",
       "3           PG-13 - Teens 13 or older   Finished Airing   \n",
       "4           PG-13 - Teens 13 or older   Finished Airing   \n",
       "...                               ...               ...   \n",
       "15270                    G - All Ages   Finished Airing   \n",
       "15271                   PG - Children   Finished Airing   \n",
       "15272                   PG - Children   Finished Airing   \n",
       "15273                   PG - Children   Finished Airing   \n",
       "15274                    G - All Ages  Currently Airing   \n",
       "\n",
       "                      studio title_type     id  \n",
       "0                      Bones         TV      1  \n",
       "1                    Pierrot         TV      2  \n",
       "2                  White Fox         TV      3  \n",
       "3      Bandai Namco Pictures         TV      4  \n",
       "4               A-1 Pictures         TV      5  \n",
       "...                      ...        ...    ...  \n",
       "15270                    NaN      Music  15271  \n",
       "15271                    NaN         TV  15272  \n",
       "15272                    NaN         TV  15273  \n",
       "15273                    NaN         TV  15274  \n",
       "15274                    NaN         TV  15275  \n",
       "\n",
       "[15275 rows x 13 columns]"
      ]
     },
     "execution_count": 320,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "anime = myal[['title','episodes','link','score','synopsis', 'duration', 'aired', 'members', 'rating', 'status', 'studio', 'title_type']]\n",
    "anime.drop_duplicates('title', inplace = True)\n",
    "anime.reset_index(drop=True, inplace=True)\n",
    "anime['id'] = anime.index + 1\n",
    "anime"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 321,
   "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>id</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>Action</td>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>Adventure</td>\n",
       "      <td>2</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>Drama</td>\n",
       "      <td>3</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>Fantasy</td>\n",
       "      <td>4</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>Military</td>\n",
       "      <td>5</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>...</th>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>69</th>\n",
       "      <td>Gourmet</td>\n",
       "      <td>70</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>70</th>\n",
       "      <td>Girls Love</td>\n",
       "      <td>71</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>71</th>\n",
       "      <td>Medical</td>\n",
       "      <td>72</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>72</th>\n",
       "      <td>Pets</td>\n",
       "      <td>73</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>73</th>\n",
       "      <td>Educational</td>\n",
       "      <td>74</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "<p>74 rows × 2 columns</p>\n",
       "</div>"
      ],
      "text/plain": [
       "          genre  id\n",
       "0        Action   1\n",
       "1     Adventure   2\n",
       "2         Drama   3\n",
       "3       Fantasy   4\n",
       "4      Military   5\n",
       "..          ...  ..\n",
       "69      Gourmet  70\n",
       "70   Girls Love  71\n",
       "71      Medical  72\n",
       "72         Pets  73\n",
       "73  Educational  74\n",
       "\n",
       "[74 rows x 2 columns]"
      ]
     },
     "execution_count": 321,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "genre = myal[['genre']]\n",
    "genre = genre.drop_duplicates('genre')\n",
    "genre = genre.reset_index(drop=True)\n",
    "genre['id'] = genre.index + 1\n",
    "genre"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 322,
   "metadata": {},
   "outputs": [],
   "source": [
    "import sqlite3"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 323,
   "metadata": {},
   "outputs": [],
   "source": [
    "create_table_query=\"\"\"\n",
    "BEGIN TRANSACTION;\n",
    "CREATE TABLE IF NOT EXISTS \"user\" (\n",
    "\t\"id\" INTEGER PRIMARY KEY,\n",
    "\t\"username\" TEXT NOT NULL UNIQUE,\n",
    "\t\"password\" TEXT NOT NULL,\n",
    "\t\"profile_picture_link\" TEXT,\n",
    "\t\"bio_description\" TEXT,\n",
    "\t\"created_at\" DATETIME DEFAULT CURRENT_TIMESTAMP,\n",
    "\t\"updated_at\" DATETIME DEFAULT CURRENT_TIMESTAMP,\n",
    "\t\"account_type\" TEXT NOT NULL DEFAULT 'USER'\n",
    ");\n",
    "CREATE TABLE IF NOT EXISTS \"list\" (\n",
    "\t\"id\" INTEGER PRIMARY KEY,\n",
    "\t\"user_id\" INTEGER NOT NULL,\n",
    "\t\"name\" TEXT NOT NULL,\n",
    "\t\"description\" TEXT,\n",
    "\t\"created_at\" TIMESTAMP DEFAULT CURRENT_TIMESTAMP,\n",
    "\t\"updated_at\" TIMESTAMP DEFAULT CURRENT_TIMESTAMP,\n",
    "    UNIQUE (\"user_id\", \"name\"),\n",
    "\tFOREIGN KEY(\"user_id\") REFERENCES \"user\"(\"id\") ON DELETE CASCADE\n",
    ");\n",
    "CREATE TABLE IF NOT EXISTS \"list_anime\" (\n",
    "\t\"list_id\" INTEGER NOT NULL,\n",
    "\t\"anime_id\" INTEGER NOT NULL,\n",
    "\t\"added_at\" TIMESTAMP DEFAULT CURRENT_TIMESTAMP,\n",
    "\tPRIMARY KEY(\"list_id\",\"anime_id\"),\n",
    "\tFOREIGN KEY(\"anime_id\") REFERENCES \"anime\"(\"id\") ON DELETE CASCADE,\n",
    "\tFOREIGN KEY(\"list_id\") REFERENCES \"list\"(\"id\") ON DELETE CASCADE\n",
    ");\n",
    "CREATE TABLE IF NOT EXISTS \"user_anime_status\" (\n",
    "\t\"status\" CHAR(1) PRIMARY KEY,\n",
    "\t\"seq\" INTEGER NOT NULL\n",
    ");\n",
    "CREATE TABLE IF NOT EXISTS \"anime\" (\n",
    "\t\"id\" INTEGER PRIMARY KEY,\n",
    "\t\"title\" TEXT,\n",
    "\t\"episodes\" TEXT,\n",
    "\t\"link\" TEXT,\n",
    "\t\"score\" REAL,\n",
    "\t\"synopsis\" TEXT,\n",
    "    \"duration\" TEXT,\n",
    "    \"aired\" TEXT,\n",
    "    \"members\" INTEGER,\n",
    "    \"rating\" TEXT,\n",
    "    \"status\" TEXT,\n",
    "    \"studio\" TEXT,\n",
    "    \"title_type\" TEXT,\n",
    "    \"created_at\" TIMESTAMP DEFAULT CURRENT_TIMESTAMP\n",
    ");\n",
    "CREATE TABLE IF NOT EXISTS \"genre\" (\n",
    "\t\"id\" INTEGER PRIMARY KEY,\n",
    "\t\"genre\" TEXT UNIQUE\n",
    ");\n",
    "CREATE TABLE IF NOT EXISTS \"anime_genre\" (\n",
    "\t\"id\" INTEGER PRIMARY KEY,\n",
    "\t\"anime_id\" INTEGER,\n",
    "\t\"genre_id\" INTEGER,\n",
    "\tFOREIGN KEY(\"anime_id\") REFERENCES \"anime\"(\"id\") ON DELETE CASCADE,\n",
    "\tFOREIGN KEY(\"genre_id\") REFERENCES \"genres\"(\"id\") ON DELETE CASCADE\n",
    ");\n",
    "CREATE TABLE IF NOT EXISTS \"user_anime\" (\n",
    "\t\"user_id\" INTEGER NOT NULL,\n",
    "\t\"anime_id\" INTEGER NOT NULL,\n",
    "\t\"review\" TEXT,\n",
    "\t\"score\" INTEGER,\n",
    "\t\"watched_episodes\" INTEGER,\n",
    "\t\"status\" CHAR(1) DEFAULT ('W'),\n",
    "\tPRIMARY KEY(\"user_id\",\"anime_id\"),\n",
    "\tFOREIGN KEY(\"anime_id\") REFERENCES \"anime\"(\"id\") ON DELETE CASCADE,\n",
    "\tFOREIGN KEY(\"user_id\") REFERENCES \"user\"(\"id\") ON DELETE CASCADE,\n",
    "\tFOREIGN KEY(\"status\") REFERENCES \"user_anime_status\"(\"status\")\n",
    ");\n",
    "\n",
    "INSERT OR IGNORE INTO \"user_anime_status\" VALUES ('W',1);\n",
    "INSERT OR IGNORE INTO \"user_anime_status\" VALUES ('C',2);\n",
    "INSERT OR IGNORE INTO \"user_anime_status\" VALUES ('O',3);\n",
    "INSERT OR IGNORE INTO \"user_anime_status\" VALUES ('D',4);\n",
    "INSERT OR IGNORE INTO \"user_anime_status\" VALUES ('P',5);\n",
    "\n",
    "COMMIT;\n",
    "\"\"\""
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 324,
   "metadata": {},
   "outputs": [],
   "source": [
    "conn = sqlite3.connect('anime.db')\n",
    "c = conn.cursor()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 325,
   "metadata": {},
   "outputs": [],
   "source": [
    "# conn.close()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 326,
   "metadata": {},
   "outputs": [],
   "source": [
    "c.executescript(create_table_query)\n",
    "conn.commit()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 327,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "74"
      ]
     },
     "execution_count": 327,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "anime.to_sql('anime', con=conn, if_exists='append', index=False)\n",
    "genre.to_sql('genre', con=conn, if_exists='append', index=False)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 328,
   "metadata": {},
   "outputs": [
    {
     "name": "stderr",
     "output_type": "stream",
     "text": [
      "C:\\Users\\User\\AppData\\Local\\Temp\\ipykernel_23780\\3674152784.py:2: SettingWithCopyWarning: \n",
      "A value is trying to be set on a copy of a slice from a DataFrame\n",
      "\n",
      "See the caveats in the documentation: https://pandas.pydata.org/pandas-docs/stable/user_guide/indexing.html#returning-a-view-versus-a-copy\n",
      "  merge_df_ag.drop_duplicates(inplace=True)\n"
     ]
    },
    {
     "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",
       "      <th>0</th>\n",
       "      <td>Fullmetal Alchemist: Brotherhood</td>\n",
       "      <td>Action</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>Fullmetal Alchemist: Brotherhood</td>\n",
       "      <td>Adventure</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>Fullmetal Alchemist: Brotherhood</td>\n",
       "      <td>Drama</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>Fullmetal Alchemist: Brotherhood</td>\n",
       "      <td>Fantasy</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>Fullmetal Alchemist: Brotherhood</td>\n",
       "      <td>Military</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>...</th>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>49953</th>\n",
       "      <td>PalaMonki 2</td>\n",
       "      <td>Kids</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>49954</th>\n",
       "      <td>Pai Le Qi Tong</td>\n",
       "      <td>Fantasy</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>49955</th>\n",
       "      <td>Pai Le Qi Tong</td>\n",
       "      <td>Anthropomorphic</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>49956</th>\n",
       "      <td>Pai Le Qi Tong</td>\n",
       "      <td>Kids</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>49957</th>\n",
       "      <td>Pakkororin</td>\n",
       "      <td>Kids</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "<p>47901 rows × 2 columns</p>\n",
       "</div>"
      ],
      "text/plain": [
       "                                  title            genre\n",
       "0      Fullmetal Alchemist: Brotherhood           Action\n",
       "1      Fullmetal Alchemist: Brotherhood        Adventure\n",
       "2      Fullmetal Alchemist: Brotherhood            Drama\n",
       "3      Fullmetal Alchemist: Brotherhood          Fantasy\n",
       "4      Fullmetal Alchemist: Brotherhood         Military\n",
       "...                                 ...              ...\n",
       "49953                       PalaMonki 2             Kids\n",
       "49954                    Pai Le Qi Tong          Fantasy\n",
       "49955                    Pai Le Qi Tong  Anthropomorphic\n",
       "49956                    Pai Le Qi Tong             Kids\n",
       "49957                        Pakkororin             Kids\n",
       "\n",
       "[47901 rows x 2 columns]"
      ]
     },
     "execution_count": 328,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "merge_df_ag = myal[['title', 'genre']]\n",
    "merge_df_ag.drop_duplicates(inplace=True)\n",
    "merge_df_ag"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 329,
   "metadata": {},
   "outputs": [],
   "source": [
    "# Get all rows with title starting with Bocchi\n",
    "# merge_df_ag[merge_df_ag['title'].str.startswith('Bocchi')]"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 330,
   "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>index</th>\n",
       "      <th>id</th>\n",
       "      <th>title</th>\n",
       "      <th>episodes</th>\n",
       "      <th>link</th>\n",
       "      <th>score</th>\n",
       "      <th>synopsis</th>\n",
       "      <th>duration</th>\n",
       "      <th>aired</th>\n",
       "      <th>members</th>\n",
       "      <th>rating</th>\n",
       "      <th>status</th>\n",
       "      <th>studio</th>\n",
       "      <th>title_type</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>0</td>\n",
       "      <td>1</td>\n",
       "      <td>Fullmetal Alchemist: Brotherhood</td>\n",
       "      <td>64</td>\n",
       "      <td>https://cdn.myanimelist.net/images/anime/1208/...</td>\n",
       "      <td>9.11</td>\n",
       "      <td>After a horrific alchemy experiment goes wrong...</td>\n",
       "      <td>24 min. per ep.</td>\n",
       "      <td>Apr 5, 2009 to Jul 4, 2010</td>\n",
       "      <td>3097493</td>\n",
       "      <td>R - 17+ (violence &amp; profanity)</td>\n",
       "      <td>Finished Airing</td>\n",
       "      <td>Bones</td>\n",
       "      <td>TV</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>1</td>\n",
       "      <td>2</td>\n",
       "      <td>Bleach: Thousand-Year Blood War</td>\n",
       "      <td>13</td>\n",
       "      <td>https://cdn.myanimelist.net/images/anime/1764/...</td>\n",
       "      <td>9.10</td>\n",
       "      <td>Substitute Soul Reaper Ichigo Kurosaki spends ...</td>\n",
       "      <td>24 min. per ep.</td>\n",
       "      <td>Oct 11, 2022 to Dec 27, 2022</td>\n",
       "      <td>395558</td>\n",
       "      <td>R - 17+ (violence &amp; profanity)</td>\n",
       "      <td>Finished Airing</td>\n",
       "      <td>Pierrot</td>\n",
       "      <td>TV</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>2</td>\n",
       "      <td>3</td>\n",
       "      <td>Steins;Gate</td>\n",
       "      <td>24</td>\n",
       "      <td>https://cdn.myanimelist.net/images/anime/1935/...</td>\n",
       "      <td>9.08</td>\n",
       "      <td>Eccentric scientist Rintarou Okabe has a never...</td>\n",
       "      <td>24 min. per ep.</td>\n",
       "      <td>Apr 6, 2011 to Sep 14, 2011</td>\n",
       "      <td>2385549</td>\n",
       "      <td>PG-13 - Teens 13 or older</td>\n",
       "      <td>Finished Airing</td>\n",
       "      <td>White Fox</td>\n",
       "      <td>TV</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>3</td>\n",
       "      <td>4</td>\n",
       "      <td>Gintama Season 4</td>\n",
       "      <td>51</td>\n",
       "      <td>https://cdn.myanimelist.net/images/anime/3/720...</td>\n",
       "      <td>9.07</td>\n",
       "      <td>Gintoki, Shinpachi, and Kagura return as the f...</td>\n",
       "      <td>24 min. per ep.</td>\n",
       "      <td>Apr 8, 2015 to Mar 30, 2016</td>\n",
       "      <td>579687</td>\n",
       "      <td>PG-13 - Teens 13 or older</td>\n",
       "      <td>Finished Airing</td>\n",
       "      <td>Bandai Namco Pictures</td>\n",
       "      <td>TV</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>4</td>\n",
       "      <td>5</td>\n",
       "      <td>Kaguya-sama: Love is War - Ultra Romantic</td>\n",
       "      <td>13</td>\n",
       "      <td>https://cdn.myanimelist.net/images/anime/1160/...</td>\n",
       "      <td>9.07</td>\n",
       "      <td>The elite members of Shuchiin Academy's studen...</td>\n",
       "      <td>23 min. per ep.</td>\n",
       "      <td>Apr 9, 2022 to Jun 25, 2022</td>\n",
       "      <td>763040</td>\n",
       "      <td>PG-13 - Teens 13 or older</td>\n",
       "      <td>Finished Airing</td>\n",
       "      <td>A-1 Pictures</td>\n",
       "      <td>TV</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>...</th>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>15270</th>\n",
       "      <td>15270</td>\n",
       "      <td>15271</td>\n",
       "      <td>Where the Sea Sleeps</td>\n",
       "      <td>1</td>\n",
       "      <td>None</td>\n",
       "      <td>NaN</td>\n",
       "      <td>Animated music video for the song  Where the S...</td>\n",
       "      <td>4 min.</td>\n",
       "      <td>Aug 31, 2020</td>\n",
       "      <td>144</td>\n",
       "      <td>G - All Ages</td>\n",
       "      <td>Finished Airing</td>\n",
       "      <td>None</td>\n",
       "      <td>Music</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>15271</th>\n",
       "      <td>15271</td>\n",
       "      <td>15272</td>\n",
       "      <td>PalaMonki</td>\n",
       "      <td>26</td>\n",
       "      <td>None</td>\n",
       "      <td>NaN</td>\n",
       "      <td>No synopsis information has been added to this...</td>\n",
       "      <td>7 min. per ep.</td>\n",
       "      <td>Aug 2019 to 2019</td>\n",
       "      <td>66</td>\n",
       "      <td>PG - Children</td>\n",
       "      <td>Finished Airing</td>\n",
       "      <td>None</td>\n",
       "      <td>TV</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>15272</th>\n",
       "      <td>15272</td>\n",
       "      <td>15273</td>\n",
       "      <td>PalaMonki 2</td>\n",
       "      <td>26</td>\n",
       "      <td>None</td>\n",
       "      <td>NaN</td>\n",
       "      <td>No synopsis information has been added to this...</td>\n",
       "      <td>7 min. per ep.</td>\n",
       "      <td>2020</td>\n",
       "      <td>64</td>\n",
       "      <td>PG - Children</td>\n",
       "      <td>Finished Airing</td>\n",
       "      <td>None</td>\n",
       "      <td>TV</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>15273</th>\n",
       "      <td>15273</td>\n",
       "      <td>15274</td>\n",
       "      <td>Pai Le Qi Tong</td>\n",
       "      <td>20</td>\n",
       "      <td>None</td>\n",
       "      <td>NaN</td>\n",
       "      <td>No synopsis information has been added to this...</td>\n",
       "      <td>9 min. per ep.</td>\n",
       "      <td>Jul 6, 2009 to ?</td>\n",
       "      <td>33</td>\n",
       "      <td>PG - Children</td>\n",
       "      <td>Finished Airing</td>\n",
       "      <td>None</td>\n",
       "      <td>TV</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>15274</th>\n",
       "      <td>15274</td>\n",
       "      <td>15275</td>\n",
       "      <td>Pakkororin</td>\n",
       "      <td>None</td>\n",
       "      <td>None</td>\n",
       "      <td>NaN</td>\n",
       "      <td>A minute long children's anime that follows 3 ...</td>\n",
       "      <td>1 min.</td>\n",
       "      <td>Mar 28, 2011 to ?</td>\n",
       "      <td>290</td>\n",
       "      <td>G - All Ages</td>\n",
       "      <td>Currently Airing</td>\n",
       "      <td>None</td>\n",
       "      <td>TV</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "<p>15275 rows × 14 columns</p>\n",
       "</div>"
      ],
      "text/plain": [
       "       index     id                                      title episodes  \\\n",
       "0          0      1           Fullmetal Alchemist: Brotherhood       64   \n",
       "1          1      2            Bleach: Thousand-Year Blood War       13   \n",
       "2          2      3                                Steins;Gate       24   \n",
       "3          3      4                           Gintama Season 4       51   \n",
       "4          4      5  Kaguya-sama: Love is War - Ultra Romantic       13   \n",
       "...      ...    ...                                        ...      ...   \n",
       "15270  15270  15271                       Where the Sea Sleeps        1   \n",
       "15271  15271  15272                                  PalaMonki       26   \n",
       "15272  15272  15273                                PalaMonki 2       26   \n",
       "15273  15273  15274                             Pai Le Qi Tong       20   \n",
       "15274  15274  15275                                 Pakkororin     None   \n",
       "\n",
       "                                                    link  score  \\\n",
       "0      https://cdn.myanimelist.net/images/anime/1208/...   9.11   \n",
       "1      https://cdn.myanimelist.net/images/anime/1764/...   9.10   \n",
       "2      https://cdn.myanimelist.net/images/anime/1935/...   9.08   \n",
       "3      https://cdn.myanimelist.net/images/anime/3/720...   9.07   \n",
       "4      https://cdn.myanimelist.net/images/anime/1160/...   9.07   \n",
       "...                                                  ...    ...   \n",
       "15270                                               None    NaN   \n",
       "15271                                               None    NaN   \n",
       "15272                                               None    NaN   \n",
       "15273                                               None    NaN   \n",
       "15274                                               None    NaN   \n",
       "\n",
       "                                                synopsis         duration  \\\n",
       "0      After a horrific alchemy experiment goes wrong...  24 min. per ep.   \n",
       "1      Substitute Soul Reaper Ichigo Kurosaki spends ...  24 min. per ep.   \n",
       "2      Eccentric scientist Rintarou Okabe has a never...  24 min. per ep.   \n",
       "3      Gintoki, Shinpachi, and Kagura return as the f...  24 min. per ep.   \n",
       "4      The elite members of Shuchiin Academy's studen...  23 min. per ep.   \n",
       "...                                                  ...              ...   \n",
       "15270  Animated music video for the song  Where the S...           4 min.   \n",
       "15271  No synopsis information has been added to this...   7 min. per ep.   \n",
       "15272  No synopsis information has been added to this...   7 min. per ep.   \n",
       "15273  No synopsis information has been added to this...   9 min. per ep.   \n",
       "15274  A minute long children's anime that follows 3 ...           1 min.   \n",
       "\n",
       "                              aired  members                          rating  \\\n",
       "0        Apr 5, 2009 to Jul 4, 2010  3097493  R - 17+ (violence & profanity)   \n",
       "1      Oct 11, 2022 to Dec 27, 2022   395558  R - 17+ (violence & profanity)   \n",
       "2       Apr 6, 2011 to Sep 14, 2011  2385549       PG-13 - Teens 13 or older   \n",
       "3       Apr 8, 2015 to Mar 30, 2016   579687       PG-13 - Teens 13 or older   \n",
       "4       Apr 9, 2022 to Jun 25, 2022   763040       PG-13 - Teens 13 or older   \n",
       "...                             ...      ...                             ...   \n",
       "15270                  Aug 31, 2020      144                    G - All Ages   \n",
       "15271              Aug 2019 to 2019       66                   PG - Children   \n",
       "15272                          2020       64                   PG - Children   \n",
       "15273              Jul 6, 2009 to ?       33                   PG - Children   \n",
       "15274             Mar 28, 2011 to ?      290                    G - All Ages   \n",
       "\n",
       "                 status                 studio title_type  \n",
       "0       Finished Airing                  Bones         TV  \n",
       "1       Finished Airing                Pierrot         TV  \n",
       "2       Finished Airing              White Fox         TV  \n",
       "3       Finished Airing  Bandai Namco Pictures         TV  \n",
       "4       Finished Airing           A-1 Pictures         TV  \n",
       "...                 ...                    ...        ...  \n",
       "15270   Finished Airing                   None      Music  \n",
       "15271   Finished Airing                   None         TV  \n",
       "15272   Finished Airing                   None         TV  \n",
       "15273   Finished Airing                   None         TV  \n",
       "15274  Currently Airing                   None         TV  \n",
       "\n",
       "[15275 rows x 14 columns]"
      ]
     },
     "execution_count": 330,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# get values from anime table and keep id as column\n",
    "\n",
    "table_anime = pd.read_sql_query('select * from anime', con=conn).reset_index()\n",
    "table_anime"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 331,
   "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>index</th>\n",
       "      <th>id</th>\n",
       "      <th>genre</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>0</td>\n",
       "      <td>1</td>\n",
       "      <td>Action</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>1</td>\n",
       "      <td>2</td>\n",
       "      <td>Adventure</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>2</td>\n",
       "      <td>3</td>\n",
       "      <td>Drama</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>3</td>\n",
       "      <td>4</td>\n",
       "      <td>Fantasy</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>4</td>\n",
       "      <td>5</td>\n",
       "      <td>Military</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "   index  id      genre\n",
       "0      0   1     Action\n",
       "1      1   2  Adventure\n",
       "2      2   3      Drama\n",
       "3      3   4    Fantasy\n",
       "4      4   5   Military"
      ]
     },
     "execution_count": 331,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "table_genre = pd.read_sql_query('select * from genre', conn).reset_index()\n",
    "table_genre.head()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 332,
   "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>anime_id</th>\n",
       "      <th>id_x</th>\n",
       "      <th>episodes</th>\n",
       "      <th>link</th>\n",
       "      <th>score</th>\n",
       "      <th>synopsis</th>\n",
       "      <th>duration</th>\n",
       "      <th>aired</th>\n",
       "      <th>members</th>\n",
       "      <th>rating</th>\n",
       "      <th>status</th>\n",
       "      <th>studio</th>\n",
       "      <th>title_type</th>\n",
       "      <th>genre_id</th>\n",
       "      <th>id_y</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>Fullmetal Alchemist: Brotherhood</td>\n",
       "      <td>Action</td>\n",
       "      <td>0</td>\n",
       "      <td>1</td>\n",
       "      <td>64</td>\n",
       "      <td>https://cdn.myanimelist.net/images/anime/1208/...</td>\n",
       "      <td>9.11</td>\n",
       "      <td>After a horrific alchemy experiment goes wrong...</td>\n",
       "      <td>24 min. per ep.</td>\n",
       "      <td>Apr 5, 2009 to Jul 4, 2010</td>\n",
       "      <td>3097493</td>\n",
       "      <td>R - 17+ (violence &amp; profanity)</td>\n",
       "      <td>Finished Airing</td>\n",
       "      <td>Bones</td>\n",
       "      <td>TV</td>\n",
       "      <td>0</td>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>Bleach: Thousand-Year Blood War</td>\n",
       "      <td>Action</td>\n",
       "      <td>1</td>\n",
       "      <td>2</td>\n",
       "      <td>13</td>\n",
       "      <td>https://cdn.myanimelist.net/images/anime/1764/...</td>\n",
       "      <td>9.10</td>\n",
       "      <td>Substitute Soul Reaper Ichigo Kurosaki spends ...</td>\n",
       "      <td>24 min. per ep.</td>\n",
       "      <td>Oct 11, 2022 to Dec 27, 2022</td>\n",
       "      <td>395558</td>\n",
       "      <td>R - 17+ (violence &amp; profanity)</td>\n",
       "      <td>Finished Airing</td>\n",
       "      <td>Pierrot</td>\n",
       "      <td>TV</td>\n",
       "      <td>0</td>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>Gintama Season 4</td>\n",
       "      <td>Action</td>\n",
       "      <td>3</td>\n",
       "      <td>4</td>\n",
       "      <td>51</td>\n",
       "      <td>https://cdn.myanimelist.net/images/anime/3/720...</td>\n",
       "      <td>9.07</td>\n",
       "      <td>Gintoki, Shinpachi, and Kagura return as the f...</td>\n",
       "      <td>24 min. per ep.</td>\n",
       "      <td>Apr 8, 2015 to Mar 30, 2016</td>\n",
       "      <td>579687</td>\n",
       "      <td>PG-13 - Teens 13 or older</td>\n",
       "      <td>Finished Airing</td>\n",
       "      <td>Bandai Namco Pictures</td>\n",
       "      <td>TV</td>\n",
       "      <td>0</td>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>Attack on Titan Season 3 Part 2</td>\n",
       "      <td>Action</td>\n",
       "      <td>5</td>\n",
       "      <td>6</td>\n",
       "      <td>10</td>\n",
       "      <td>https://cdn.myanimelist.net/images/anime/1517/...</td>\n",
       "      <td>9.06</td>\n",
       "      <td>Seeking to restore humanity's diminishing hope...</td>\n",
       "      <td>23 min. per ep.</td>\n",
       "      <td>Apr 29, 2019 to Jul 1, 2019</td>\n",
       "      <td>2031753</td>\n",
       "      <td>R - 17+ (violence &amp; profanity)</td>\n",
       "      <td>Finished Airing</td>\n",
       "      <td>Wit Studio</td>\n",
       "      <td>TV</td>\n",
       "      <td>0</td>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>Gintama: The Very Final</td>\n",
       "      <td>Action</td>\n",
       "      <td>6</td>\n",
       "      <td>7</td>\n",
       "      <td>1</td>\n",
       "      <td>https://cdn.myanimelist.net/images/anime/1988/...</td>\n",
       "      <td>9.05</td>\n",
       "      <td>Two years have passed following the Tendoshuu'...</td>\n",
       "      <td>1 hr. 44 min.</td>\n",
       "      <td>Jan 8, 2021</td>\n",
       "      <td>125152</td>\n",
       "      <td>PG-13 - Teens 13 or older</td>\n",
       "      <td>Finished Airing</td>\n",
       "      <td>Bandai Namco Pictures</td>\n",
       "      <td>Movie</td>\n",
       "      <td>0</td>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>...</th>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>47896</th>\n",
       "      <td>Ojarumaru no Koutsuu Anzen</td>\n",
       "      <td>Educational</td>\n",
       "      <td>15075</td>\n",
       "      <td>15076</td>\n",
       "      <td>1</td>\n",
       "      <td>None</td>\n",
       "      <td>NaN</td>\n",
       "      <td>Traffic safety video starring the cast of  Oja...</td>\n",
       "      <td>15 min.</td>\n",
       "      <td>2000</td>\n",
       "      <td>182</td>\n",
       "      <td>G - All Ages</td>\n",
       "      <td>Finished Airing</td>\n",
       "      <td>None</td>\n",
       "      <td>OVA</td>\n",
       "      <td>73</td>\n",
       "      <td>74</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>47897</th>\n",
       "      <td>Ojarumaru: Chitchai Mono no Ookina Chikara</td>\n",
       "      <td>Educational</td>\n",
       "      <td>15078</td>\n",
       "      <td>15079</td>\n",
       "      <td>1</td>\n",
       "      <td>None</td>\n",
       "      <td>NaN</td>\n",
       "      <td>An educational film about the importance of he...</td>\n",
       "      <td>11 min.</td>\n",
       "      <td>2000</td>\n",
       "      <td>210</td>\n",
       "      <td>G - All Ages</td>\n",
       "      <td>Finished Airing</td>\n",
       "      <td>None</td>\n",
       "      <td>OVA</td>\n",
       "      <td>73</td>\n",
       "      <td>74</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>47898</th>\n",
       "      <td>Ojiichan ga Nokoshite Kureta Mono: Moetemo Moe...</td>\n",
       "      <td>Educational</td>\n",
       "      <td>15083</td>\n",
       "      <td>15084</td>\n",
       "      <td>1</td>\n",
       "      <td>None</td>\n",
       "      <td>NaN</td>\n",
       "      <td>Japan Fire Retardant Association prevention video</td>\n",
       "      <td>10 min.</td>\n",
       "      <td>Jul 2014</td>\n",
       "      <td>219</td>\n",
       "      <td>G - All Ages</td>\n",
       "      <td>Finished Airing</td>\n",
       "      <td>None</td>\n",
       "      <td>OVA</td>\n",
       "      <td>73</td>\n",
       "      <td>74</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>47899</th>\n",
       "      <td>Oyako de Manabu: Kaji no Youjin - Jishin no Yo...</td>\n",
       "      <td>Educational</td>\n",
       "      <td>15251</td>\n",
       "      <td>15252</td>\n",
       "      <td>1</td>\n",
       "      <td>None</td>\n",
       "      <td>NaN</td>\n",
       "      <td>An educational fire safety and earthquake safe...</td>\n",
       "      <td>11 min.</td>\n",
       "      <td>1996</td>\n",
       "      <td>111</td>\n",
       "      <td>G - All Ages</td>\n",
       "      <td>Finished Airing</td>\n",
       "      <td>None</td>\n",
       "      <td>OVA</td>\n",
       "      <td>73</td>\n",
       "      <td>74</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>47900</th>\n",
       "      <td>Oz no Mahoutsukai no Koutsuu Anzen no Tabi</td>\n",
       "      <td>Educational</td>\n",
       "      <td>15262</td>\n",
       "      <td>15263</td>\n",
       "      <td>1</td>\n",
       "      <td>None</td>\n",
       "      <td>NaN</td>\n",
       "      <td>An educational film about traffic safety stari...</td>\n",
       "      <td>12 min.</td>\n",
       "      <td>2007</td>\n",
       "      <td>172</td>\n",
       "      <td>G - All Ages</td>\n",
       "      <td>Finished Airing</td>\n",
       "      <td>Toei Animation</td>\n",
       "      <td>OVA</td>\n",
       "      <td>73</td>\n",
       "      <td>74</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "<p>47901 rows × 17 columns</p>\n",
       "</div>"
      ],
      "text/plain": [
       "                                                   title        genre  \\\n",
       "0                       Fullmetal Alchemist: Brotherhood       Action   \n",
       "1                        Bleach: Thousand-Year Blood War       Action   \n",
       "2                                       Gintama Season 4       Action   \n",
       "3                        Attack on Titan Season 3 Part 2       Action   \n",
       "4                                Gintama: The Very Final       Action   \n",
       "...                                                  ...          ...   \n",
       "47896                         Ojarumaru no Koutsuu Anzen  Educational   \n",
       "47897         Ojarumaru: Chitchai Mono no Ookina Chikara  Educational   \n",
       "47898  Ojiichan ga Nokoshite Kureta Mono: Moetemo Moe...  Educational   \n",
       "47899  Oyako de Manabu: Kaji no Youjin - Jishin no Yo...  Educational   \n",
       "47900         Oz no Mahoutsukai no Koutsuu Anzen no Tabi  Educational   \n",
       "\n",
       "       anime_id   id_x episodes  \\\n",
       "0             0      1       64   \n",
       "1             1      2       13   \n",
       "2             3      4       51   \n",
       "3             5      6       10   \n",
       "4             6      7        1   \n",
       "...         ...    ...      ...   \n",
       "47896     15075  15076        1   \n",
       "47897     15078  15079        1   \n",
       "47898     15083  15084        1   \n",
       "47899     15251  15252        1   \n",
       "47900     15262  15263        1   \n",
       "\n",
       "                                                    link  score  \\\n",
       "0      https://cdn.myanimelist.net/images/anime/1208/...   9.11   \n",
       "1      https://cdn.myanimelist.net/images/anime/1764/...   9.10   \n",
       "2      https://cdn.myanimelist.net/images/anime/3/720...   9.07   \n",
       "3      https://cdn.myanimelist.net/images/anime/1517/...   9.06   \n",
       "4      https://cdn.myanimelist.net/images/anime/1988/...   9.05   \n",
       "...                                                  ...    ...   \n",
       "47896                                               None    NaN   \n",
       "47897                                               None    NaN   \n",
       "47898                                               None    NaN   \n",
       "47899                                               None    NaN   \n",
       "47900                                               None    NaN   \n",
       "\n",
       "                                                synopsis         duration  \\\n",
       "0      After a horrific alchemy experiment goes wrong...  24 min. per ep.   \n",
       "1      Substitute Soul Reaper Ichigo Kurosaki spends ...  24 min. per ep.   \n",
       "2      Gintoki, Shinpachi, and Kagura return as the f...  24 min. per ep.   \n",
       "3      Seeking to restore humanity's diminishing hope...  23 min. per ep.   \n",
       "4      Two years have passed following the Tendoshuu'...    1 hr. 44 min.   \n",
       "...                                                  ...              ...   \n",
       "47896  Traffic safety video starring the cast of  Oja...          15 min.   \n",
       "47897  An educational film about the importance of he...          11 min.   \n",
       "47898  Japan Fire Retardant Association prevention video          10 min.   \n",
       "47899  An educational fire safety and earthquake safe...          11 min.   \n",
       "47900  An educational film about traffic safety stari...          12 min.   \n",
       "\n",
       "                              aired  members                          rating  \\\n",
       "0        Apr 5, 2009 to Jul 4, 2010  3097493  R - 17+ (violence & profanity)   \n",
       "1      Oct 11, 2022 to Dec 27, 2022   395558  R - 17+ (violence & profanity)   \n",
       "2       Apr 8, 2015 to Mar 30, 2016   579687       PG-13 - Teens 13 or older   \n",
       "3       Apr 29, 2019 to Jul 1, 2019  2031753  R - 17+ (violence & profanity)   \n",
       "4                       Jan 8, 2021   125152       PG-13 - Teens 13 or older   \n",
       "...                             ...      ...                             ...   \n",
       "47896                          2000      182                    G - All Ages   \n",
       "47897                          2000      210                    G - All Ages   \n",
       "47898                      Jul 2014      219                    G - All Ages   \n",
       "47899                          1996      111                    G - All Ages   \n",
       "47900                          2007      172                    G - All Ages   \n",
       "\n",
       "                status                 studio title_type  genre_id  id_y  \n",
       "0      Finished Airing                  Bones         TV         0     1  \n",
       "1      Finished Airing                Pierrot         TV         0     1  \n",
       "2      Finished Airing  Bandai Namco Pictures         TV         0     1  \n",
       "3      Finished Airing             Wit Studio         TV         0     1  \n",
       "4      Finished Airing  Bandai Namco Pictures      Movie         0     1  \n",
       "...                ...                    ...        ...       ...   ...  \n",
       "47896  Finished Airing                   None        OVA        73    74  \n",
       "47897  Finished Airing                   None        OVA        73    74  \n",
       "47898  Finished Airing                   None        OVA        73    74  \n",
       "47899  Finished Airing                   None        OVA        73    74  \n",
       "47900  Finished Airing         Toei Animation        OVA        73    74  \n",
       "\n",
       "[47901 rows x 17 columns]"
      ]
     },
     "execution_count": 332,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "merge_df_ag = merge_df_ag.merge(table_anime, on='title')\n",
    "merge_df_ag.rename(columns={'index':'anime_id'}, inplace=True)\n",
    "merge_df_ag = merge_df_ag.merge(table_genre, left_on='genre', right_on='genre')\n",
    "merge_df_ag.rename(columns={'index':'genre_id'}, inplace=True)\n",
    "merge_df_ag"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 333,
   "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>anime_id</th>\n",
       "      <th>id_x</th>\n",
       "      <th>episodes</th>\n",
       "      <th>link</th>\n",
       "      <th>score</th>\n",
       "      <th>synopsis</th>\n",
       "      <th>duration</th>\n",
       "      <th>aired</th>\n",
       "      <th>members</th>\n",
       "      <th>rating</th>\n",
       "      <th>status</th>\n",
       "      <th>studio</th>\n",
       "      <th>title_type</th>\n",
       "      <th>genre_id</th>\n",
       "      <th>id_y</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>17683</th>\n",
       "      <td>Bocchi the Rock!</td>\n",
       "      <td>Comedy</td>\n",
       "      <td>18</td>\n",
       "      <td>19</td>\n",
       "      <td>12</td>\n",
       "      <td>https://cdn.myanimelist.net/images/anime/1448/...</td>\n",
       "      <td>8.92</td>\n",
       "      <td>Yearning to make friends and perform live with...</td>\n",
       "      <td>23 min. per ep.</td>\n",
       "      <td>Oct 9, 2022 to Dec 25, 2022</td>\n",
       "      <td>335537</td>\n",
       "      <td>PG-13 - Teens 13 or older</td>\n",
       "      <td>Finished Airing</td>\n",
       "      <td>CloverWorks</td>\n",
       "      <td>TV</td>\n",
       "      <td>10</td>\n",
       "      <td>11</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>34084</th>\n",
       "      <td>Bocchi the Rock!</td>\n",
       "      <td>CGDCT</td>\n",
       "      <td>18</td>\n",
       "      <td>19</td>\n",
       "      <td>12</td>\n",
       "      <td>https://cdn.myanimelist.net/images/anime/1448/...</td>\n",
       "      <td>8.92</td>\n",
       "      <td>Yearning to make friends and perform live with...</td>\n",
       "      <td>23 min. per ep.</td>\n",
       "      <td>Oct 9, 2022 to Dec 25, 2022</td>\n",
       "      <td>335537</td>\n",
       "      <td>PG-13 - Teens 13 or older</td>\n",
       "      <td>Finished Airing</td>\n",
       "      <td>CloverWorks</td>\n",
       "      <td>TV</td>\n",
       "      <td>30</td>\n",
       "      <td>31</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>34282</th>\n",
       "      <td>Bocchi the Rock!</td>\n",
       "      <td>Music</td>\n",
       "      <td>18</td>\n",
       "      <td>19</td>\n",
       "      <td>12</td>\n",
       "      <td>https://cdn.myanimelist.net/images/anime/1448/...</td>\n",
       "      <td>8.92</td>\n",
       "      <td>Yearning to make friends and perform live with...</td>\n",
       "      <td>23 min. per ep.</td>\n",
       "      <td>Oct 9, 2022 to Dec 25, 2022</td>\n",
       "      <td>335537</td>\n",
       "      <td>PG-13 - Teens 13 or older</td>\n",
       "      <td>Finished Airing</td>\n",
       "      <td>CloverWorks</td>\n",
       "      <td>TV</td>\n",
       "      <td>31</td>\n",
       "      <td>32</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "                  title   genre  anime_id  id_x episodes  \\\n",
       "17683  Bocchi the Rock!  Comedy        18    19       12   \n",
       "34084  Bocchi the Rock!   CGDCT        18    19       12   \n",
       "34282  Bocchi the Rock!   Music        18    19       12   \n",
       "\n",
       "                                                    link  score  \\\n",
       "17683  https://cdn.myanimelist.net/images/anime/1448/...   8.92   \n",
       "34084  https://cdn.myanimelist.net/images/anime/1448/...   8.92   \n",
       "34282  https://cdn.myanimelist.net/images/anime/1448/...   8.92   \n",
       "\n",
       "                                                synopsis         duration  \\\n",
       "17683  Yearning to make friends and perform live with...  23 min. per ep.   \n",
       "34084  Yearning to make friends and perform live with...  23 min. per ep.   \n",
       "34282  Yearning to make friends and perform live with...  23 min. per ep.   \n",
       "\n",
       "                             aired  members                     rating  \\\n",
       "17683  Oct 9, 2022 to Dec 25, 2022   335537  PG-13 - Teens 13 or older   \n",
       "34084  Oct 9, 2022 to Dec 25, 2022   335537  PG-13 - Teens 13 or older   \n",
       "34282  Oct 9, 2022 to Dec 25, 2022   335537  PG-13 - Teens 13 or older   \n",
       "\n",
       "                status       studio title_type  genre_id  id_y  \n",
       "17683  Finished Airing  CloverWorks         TV        10    11  \n",
       "34084  Finished Airing  CloverWorks         TV        30    31  \n",
       "34282  Finished Airing  CloverWorks         TV        31    32  "
      ]
     },
     "execution_count": 333,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# Get all rows with title starting with Bocchi\n",
    "merge_df_ag[merge_df_ag['title'].str.startswith('Bocchi')]"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 334,
   "metadata": {},
   "outputs": [
    {
     "name": "stderr",
     "output_type": "stream",
     "text": [
      "C:\\Users\\User\\AppData\\Local\\Temp\\ipykernel_23780\\2894379489.py:4: SettingWithCopyWarning: \n",
      "A value is trying to be set on a copy of a slice from a DataFrame.\n",
      "Try using .loc[row_indexer,col_indexer] = value instead\n",
      "\n",
      "See the caveats in the documentation: https://pandas.pydata.org/pandas-docs/stable/user_guide/indexing.html#returning-a-view-versus-a-copy\n",
      "  genre_anime['anime_id'] += 1\n",
      "C:\\Users\\User\\AppData\\Local\\Temp\\ipykernel_23780\\2894379489.py:5: SettingWithCopyWarning: \n",
      "A value is trying to be set on a copy of a slice from a DataFrame.\n",
      "Try using .loc[row_indexer,col_indexer] = value instead\n",
      "\n",
      "See the caveats in the documentation: https://pandas.pydata.org/pandas-docs/stable/user_guide/indexing.html#returning-a-view-versus-a-copy\n",
      "  genre_anime['genre_id'] += 1\n",
      "C:\\Users\\User\\AppData\\Local\\Temp\\ipykernel_23780\\2894379489.py:6: SettingWithCopyWarning: \n",
      "A value is trying to be set on a copy of a slice from a DataFrame.\n",
      "Try using .loc[row_indexer,col_indexer] = value instead\n",
      "\n",
      "See the caveats in the documentation: https://pandas.pydata.org/pandas-docs/stable/user_guide/indexing.html#returning-a-view-versus-a-copy\n",
      "  genre_anime['id'] = genre_anime.index + 1\n"
     ]
    },
    {
     "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>anime_id</th>\n",
       "      <th>genre_id</th>\n",
       "      <th>id</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>1</td>\n",
       "      <td>1</td>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>2</td>\n",
       "      <td>1</td>\n",
       "      <td>2</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>4</td>\n",
       "      <td>1</td>\n",
       "      <td>3</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>6</td>\n",
       "      <td>1</td>\n",
       "      <td>4</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>7</td>\n",
       "      <td>1</td>\n",
       "      <td>5</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "   anime_id  genre_id  id\n",
       "0         1         1   1\n",
       "1         2         1   2\n",
       "2         4         1   3\n",
       "3         6         1   4\n",
       "4         7         1   5"
      ]
     },
     "execution_count": 334,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "genre_anime = merge_df_ag[['anime_id','genre_id']]\n",
    "genre_anime.drop_duplicates()\n",
    "# Increment id_anime and id_genre by 1\n",
    "genre_anime['anime_id'] += 1\n",
    "genre_anime['genre_id'] += 1\n",
    "genre_anime['id'] = genre_anime.index + 1\n",
    "genre_anime.head()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 335,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "47901"
      ]
     },
     "execution_count": 335,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "genre_anime.to_sql(name='anime_genre', con=conn, if_exists='append', index=False)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 336,
   "metadata": {},
   "outputs": [],
   "source": [
    "conn.close()"
   ]
  }
 ],
 "metadata": {
  "kernelspec": {
   "display_name": ".venv",
   "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.10.11"
  },
  "orig_nbformat": 4,
  "vscode": {
   "interpreter": {
    "hash": "6b6f86ad23727067e3394c5acd256b1f2c99b0301e8f2bf8df17107f733281bf"
   }
  }
 },
 "nbformat": 4,
 "nbformat_minor": 2
}