process_etl.ipynb

{
 "cells": [
  {
   "cell_type": "markdown",
   "id": "8e542d2c",
   "metadata": {},
   "source": [
    "# Process ETL"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "c18c5b6a",
   "metadata": {},
   "source": [
    "### Import libraries"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 20,
   "id": "7fb317a8",
   "metadata": {},
   "outputs": [],
   "source": [
    "#import libraries\n",
    "import pandas\n",
    "import requests\n",
    "from sqlalchemy import create_engine\n",
    "#from access_server import user_n_password1, user_n_password2 "
   ]
  },
  {
   "cell_type": "markdown",
   "id": "3a1ba2f2",
   "metadata": {},
   "source": [
    "### Make request"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 21,
   "id": "d034e071",
   "metadata": {},
   "outputs": [],
   "source": [
    "url = \"https://api.jikan.moe/v3/search/anime?q=berserk\"\n",
    "response = requests.get(url)"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "6364e71b",
   "metadata": {},
   "source": [
    "### Status code"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 22,
   "id": "e8a22ad5",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "<Response [200]>"
      ]
     },
     "execution_count": 22,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "response"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "11fdc938",
   "metadata": {},
   "source": [
    "### Convert response to json format"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 23,
   "id": "586ceeea",
   "metadata": {},
   "outputs": [],
   "source": [
    "#convert response to json\n",
    "data_json = response.json()"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "df6f2ca5",
   "metadata": {},
   "source": [
    "### Get top 10 results on the list"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 24,
   "id": "8af8fe7a",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "[{'mal_id': 32379,\n",
       "  'url': 'https://myanimelist.net/anime/32379/Berserk',\n",
       "  'image_url': 'https://cdn.myanimelist.net/images/anime/10/79352.jpg?s=bd668bd7511c8aa906e65697b7cfe151',\n",
       "  'title': 'Berserk',\n",
       "  'airing': False,\n",
       "  'synopsis': 'Now branded for death and destined to be hunted by demons until the day he dies, Guts embarks on a journey to defy such a gruesome fate, as waves of beasts relentlessly pursue him. Steeling his resolv...',\n",
       "  'type': 'TV',\n",
       "  'episodes': 12,\n",
       "  'score': 6.33,\n",
       "  'start_date': '2016-07-01T00:00:00+00:00',\n",
       "  'end_date': '2016-09-16T00:00:00+00:00',\n",
       "  'members': 278821,\n",
       "  'rated': 'R+'},\n",
       " {'mal_id': 33,\n",
       "  'url': 'https://myanimelist.net/anime/33/Kenpuu_Denki_Berserk',\n",
       "  'image_url': 'https://cdn.myanimelist.net/images/anime/12/18520.jpg?s=0bb887542ea5385eee775e7d1f0f0d0f',\n",
       "  'title': 'Kenpuu Denki Berserk',\n",
       "  'airing': False,\n",
       "  'synopsis': 'Born from the corpse of his mother, a young mercenary known only as Guts, embraces the battlefield as his only means of survival. Day in and day out, putting his life on the line just to make enough t...',\n",
       "  'type': 'TV',\n",
       "  'episodes': 25,\n",
       "  'score': 8.5,\n",
       "  'start_date': '1997-10-08T00:00:00+00:00',\n",
       "  'end_date': '1998-04-01T00:00:00+00:00',\n",
       "  'members': 467356,\n",
       "  'rated': 'R+'},\n",
       " {'mal_id': 34055,\n",
       "  'url': 'https://myanimelist.net/anime/34055/Berserk_2nd_Season',\n",
       "  'image_url': 'https://cdn.myanimelist.net/images/anime/12/85296.jpg?s=4bbe3e8bd450681ff80e96c86df4c104',\n",
       "  'title': 'Berserk 2nd Season',\n",
       "  'airing': False,\n",
       "  'synopsis': 'Demons have now become commonplace around the kingdom of Midland, which has fallen into chaos. The swordsman Guts still cannot stay in one place for long due to his demonic brand. He could always mana...',\n",
       "  'type': 'TV',\n",
       "  'episodes': 12,\n",
       "  'score': 6.62,\n",
       "  'start_date': '2017-04-07T00:00:00+00:00',\n",
       "  'end_date': '2017-06-23T00:00:00+00:00',\n",
       "  'members': 134905,\n",
       "  'rated': 'R'},\n",
       " {'mal_id': 35009,\n",
       "  'url': 'https://myanimelist.net/anime/35009/Berserk_Recap',\n",
       "  'image_url': 'https://cdn.myanimelist.net/images/anime/9/84535.jpg?s=13260746ed00215d81c3f01d8bf3ea47',\n",
       "  'title': 'Berserk Recap',\n",
       "  'airing': False,\n",
       "  'synopsis': 'Recap episode of Berserk.',\n",
       "  'type': 'Special',\n",
       "  'episodes': 1,\n",
       "  'score': 6.04,\n",
       "  'start_date': '2017-03-03T00:00:00+00:00',\n",
       "  'end_date': '2017-03-03T00:00:00+00:00',\n",
       "  'members': 10888,\n",
       "  'rated': 'R'},\n",
       " {'mal_id': 12115,\n",
       "  'url': 'https://myanimelist.net/anime/12115/Berserk__Ougon_Jidai-hen_III_-_Kourin',\n",
       "  'image_url': 'https://cdn.myanimelist.net/images/anime/12/41305.jpg?s=ecff5c27f482f61b6261ab3dc83f95c3',\n",
       "  'title': 'Berserk: Ougon Jidai-hen III - Kourin',\n",
       "  'airing': False,\n",
       "  'synopsis': 'The Band of the Hawk has dwindled in the year since Guts left them on his journey to forge his own destiny. Unaware of their fate, Guts returns to the Hawks—now being led by his former ally Casca—afte...',\n",
       "  'type': 'Movie',\n",
       "  'episodes': 1,\n",
       "  'score': 8.2,\n",
       "  'start_date': '2013-02-01T00:00:00+00:00',\n",
       "  'end_date': '2013-02-01T00:00:00+00:00',\n",
       "  'members': 170117,\n",
       "  'rated': 'R+'},\n",
       " {'mal_id': 35676,\n",
       "  'url': 'https://myanimelist.net/anime/35676/Berserk__Majo_no_Tsuisou',\n",
       "  'image_url': 'https://cdn.myanimelist.net/images/anime/13/86045.jpg?s=5f65fe6ca97efffb904cd1ebdce430c6',\n",
       "  'title': 'Berserk: Majo no Tsuisou',\n",
       "  'airing': False,\n",
       "  'synopsis': 'Recap episode of Berserk 2nd Season.',\n",
       "  'type': 'Special',\n",
       "  'episodes': 1,\n",
       "  'score': 5.81,\n",
       "  'start_date': '2017-06-02T00:00:00+00:00',\n",
       "  'end_date': '2017-06-02T00:00:00+00:00',\n",
       "  'members': 12969,\n",
       "  'rated': 'R'},\n",
       " {'mal_id': 10218,\n",
       "  'url': 'https://myanimelist.net/anime/10218/Berserk__Ougon_Jidai-hen_I_-_Haou_no_Tamago',\n",
       "  'image_url': 'https://cdn.myanimelist.net/images/anime/12/62179.jpg?s=bf545ad02b5c37672916209060a2248e',\n",
       "  'title': 'Berserk: Ougon Jidai-hen I - Haou no Tamago',\n",
       "  'airing': False,\n",
       "  'synopsis': 'In the Kingdom of Midland, a mercenary named Guts wanders the land, preferring a life of conflict over a life of peace. Despite the odds never being in his favor, he is an unstoppable force that overc...',\n",
       "  'type': 'Movie',\n",
       "  'episodes': 1,\n",
       "  'score': 7.74,\n",
       "  'start_date': '2012-02-04T00:00:00+00:00',\n",
       "  'end_date': '2012-02-04T00:00:00+00:00',\n",
       "  'members': 176643,\n",
       "  'rated': 'R+'},\n",
       " {'mal_id': 12113,\n",
       "  'url': 'https://myanimelist.net/anime/12113/Berserk__Ougon_Jidai-hen_II_-_Doldrey_Kouryaku',\n",
       "  'image_url': 'https://cdn.myanimelist.net/images/anime/12/37193.jpg?s=c2f550a1ee984cec1677389e3cfc4023',\n",
       "  'title': 'Berserk: Ougon Jidai-hen II - Doldrey Kouryaku',\n",
       "  'airing': False,\n",
       "  'synopsis': 'The Band of the Hawk and their enigmatic leader Griffith continue winning battle after battle as their prestige throughout the kingdom of Midland grows. But their latest task is one that has seen fail...',\n",
       "  'type': 'Movie',\n",
       "  'episodes': 1,\n",
       "  'score': 7.9,\n",
       "  'start_date': '2012-06-23T00:00:00+00:00',\n",
       "  'end_date': '2012-06-23T00:00:00+00:00',\n",
       "  'members': 162444,\n",
       "  'rated': 'R+'},\n",
       " {'mal_id': 8311,\n",
       "  'url': 'https://myanimelist.net/anime/8311/Tegamibachi_Reverse',\n",
       "  'image_url': 'https://cdn.myanimelist.net/images/anime/4/26472.jpg?s=2e25f43284e5c9d775b8e6f7662a698e',\n",
       "  'title': 'Tegamibachi Reverse',\n",
       "  'airing': False,\n",
       "  'synopsis': \"After Niche carries the wounded and stunned Lag back to the Bee Hive, the Letter Bee finally begins to piece the puzzle together. Now he knows what's happened to Gauche, why the Marauders are so focus...\",\n",
       "  'type': 'TV',\n",
       "  'episodes': 25,\n",
       "  'score': 7.77,\n",
       "  'start_date': '2010-10-03T00:00:00+00:00',\n",
       "  'end_date': '2011-03-26T00:00:00+00:00',\n",
       "  'members': 56095,\n",
       "  'rated': 'PG-13'},\n",
       " {'mal_id': 36976,\n",
       "  'url': 'https://myanimelist.net/anime/36976/Cutie_Honey_Universe',\n",
       "  'image_url': 'https://cdn.myanimelist.net/images/anime/1879/91014.jpg?s=416e4ee552129901c536262dc70b61a2',\n",
       "  'title': 'Cutie Honey Universe',\n",
       "  'airing': False,\n",
       "  'synopsis': 'The forces of evil are on the rise. When the evil mastermind Sister Jill transforms one of her girls into the bestial Breast Claw and sends her minions out on a mission involving the group Panther Cla...',\n",
       "  'type': 'TV',\n",
       "  'episodes': 12,\n",
       "  'score': 5.41,\n",
       "  'start_date': '2018-04-08T00:00:00+00:00',\n",
       "  'end_date': '2018-06-24T00:00:00+00:00',\n",
       "  'members': 16361,\n",
       "  'rated': 'R'}]"
      ]
     },
     "execution_count": 24,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "#get first 10 results on the list\n",
    "data_anime = data_json[\"results\"][:10]\n",
    "data_anime"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "ae653a71",
   "metadata": {},
   "source": [
    "### Convert list to dataframe  "
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 25,
   "id": "d1125a31",
   "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>mal_id</th>\n",
       "      <th>url</th>\n",
       "      <th>image_url</th>\n",
       "      <th>title</th>\n",
       "      <th>airing</th>\n",
       "      <th>synopsis</th>\n",
       "      <th>type</th>\n",
       "      <th>episodes</th>\n",
       "      <th>score</th>\n",
       "      <th>start_date</th>\n",
       "      <th>end_date</th>\n",
       "      <th>members</th>\n",
       "      <th>rated</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>32379</td>\n",
       "      <td>https://myanimelist.net/anime/32379/Berserk</td>\n",
       "      <td>https://cdn.myanimelist.net/images/anime/10/79...</td>\n",
       "      <td>Berserk</td>\n",
       "      <td>False</td>\n",
       "      <td>Now branded for death and destined to be hunte...</td>\n",
       "      <td>TV</td>\n",
       "      <td>12</td>\n",
       "      <td>6.33</td>\n",
       "      <td>2016-07-01T00:00:00+00:00</td>\n",
       "      <td>2016-09-16T00:00:00+00:00</td>\n",
       "      <td>278821</td>\n",
       "      <td>R+</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>33</td>\n",
       "      <td>https://myanimelist.net/anime/33/Kenpuu_Denki_...</td>\n",
       "      <td>https://cdn.myanimelist.net/images/anime/12/18...</td>\n",
       "      <td>Kenpuu Denki Berserk</td>\n",
       "      <td>False</td>\n",
       "      <td>Born from the corpse of his mother, a young me...</td>\n",
       "      <td>TV</td>\n",
       "      <td>25</td>\n",
       "      <td>8.50</td>\n",
       "      <td>1997-10-08T00:00:00+00:00</td>\n",
       "      <td>1998-04-01T00:00:00+00:00</td>\n",
       "      <td>467356</td>\n",
       "      <td>R+</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>34055</td>\n",
       "      <td>https://myanimelist.net/anime/34055/Berserk_2n...</td>\n",
       "      <td>https://cdn.myanimelist.net/images/anime/12/85...</td>\n",
       "      <td>Berserk 2nd Season</td>\n",
       "      <td>False</td>\n",
       "      <td>Demons have now become commonplace around the ...</td>\n",
       "      <td>TV</td>\n",
       "      <td>12</td>\n",
       "      <td>6.62</td>\n",
       "      <td>2017-04-07T00:00:00+00:00</td>\n",
       "      <td>2017-06-23T00:00:00+00:00</td>\n",
       "      <td>134905</td>\n",
       "      <td>R</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>35009</td>\n",
       "      <td>https://myanimelist.net/anime/35009/Berserk_Recap</td>\n",
       "      <td>https://cdn.myanimelist.net/images/anime/9/845...</td>\n",
       "      <td>Berserk Recap</td>\n",
       "      <td>False</td>\n",
       "      <td>Recap episode of Berserk.</td>\n",
       "      <td>Special</td>\n",
       "      <td>1</td>\n",
       "      <td>6.04</td>\n",
       "      <td>2017-03-03T00:00:00+00:00</td>\n",
       "      <td>2017-03-03T00:00:00+00:00</td>\n",
       "      <td>10888</td>\n",
       "      <td>R</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>12115</td>\n",
       "      <td>https://myanimelist.net/anime/12115/Berserk__O...</td>\n",
       "      <td>https://cdn.myanimelist.net/images/anime/12/41...</td>\n",
       "      <td>Berserk: Ougon Jidai-hen III - Kourin</td>\n",
       "      <td>False</td>\n",
       "      <td>The Band of the Hawk has dwindled in the year ...</td>\n",
       "      <td>Movie</td>\n",
       "      <td>1</td>\n",
       "      <td>8.20</td>\n",
       "      <td>2013-02-01T00:00:00+00:00</td>\n",
       "      <td>2013-02-01T00:00:00+00:00</td>\n",
       "      <td>170117</td>\n",
       "      <td>R+</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>5</th>\n",
       "      <td>35676</td>\n",
       "      <td>https://myanimelist.net/anime/35676/Berserk__M...</td>\n",
       "      <td>https://cdn.myanimelist.net/images/anime/13/86...</td>\n",
       "      <td>Berserk: Majo no Tsuisou</td>\n",
       "      <td>False</td>\n",
       "      <td>Recap episode of Berserk 2nd Season.</td>\n",
       "      <td>Special</td>\n",
       "      <td>1</td>\n",
       "      <td>5.81</td>\n",
       "      <td>2017-06-02T00:00:00+00:00</td>\n",
       "      <td>2017-06-02T00:00:00+00:00</td>\n",
       "      <td>12969</td>\n",
       "      <td>R</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>6</th>\n",
       "      <td>10218</td>\n",
       "      <td>https://myanimelist.net/anime/10218/Berserk__O...</td>\n",
       "      <td>https://cdn.myanimelist.net/images/anime/12/62...</td>\n",
       "      <td>Berserk: Ougon Jidai-hen I - Haou no Tamago</td>\n",
       "      <td>False</td>\n",
       "      <td>In the Kingdom of Midland, a mercenary named G...</td>\n",
       "      <td>Movie</td>\n",
       "      <td>1</td>\n",
       "      <td>7.74</td>\n",
       "      <td>2012-02-04T00:00:00+00:00</td>\n",
       "      <td>2012-02-04T00:00:00+00:00</td>\n",
       "      <td>176643</td>\n",
       "      <td>R+</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>7</th>\n",
       "      <td>12113</td>\n",
       "      <td>https://myanimelist.net/anime/12113/Berserk__O...</td>\n",
       "      <td>https://cdn.myanimelist.net/images/anime/12/37...</td>\n",
       "      <td>Berserk: Ougon Jidai-hen II - Doldrey Kouryaku</td>\n",
       "      <td>False</td>\n",
       "      <td>The Band of the Hawk and their enigmatic leade...</td>\n",
       "      <td>Movie</td>\n",
       "      <td>1</td>\n",
       "      <td>7.90</td>\n",
       "      <td>2012-06-23T00:00:00+00:00</td>\n",
       "      <td>2012-06-23T00:00:00+00:00</td>\n",
       "      <td>162444</td>\n",
       "      <td>R+</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>8</th>\n",
       "      <td>8311</td>\n",
       "      <td>https://myanimelist.net/anime/8311/Tegamibachi...</td>\n",
       "      <td>https://cdn.myanimelist.net/images/anime/4/264...</td>\n",
       "      <td>Tegamibachi Reverse</td>\n",
       "      <td>False</td>\n",
       "      <td>After Niche carries the wounded and stunned La...</td>\n",
       "      <td>TV</td>\n",
       "      <td>25</td>\n",
       "      <td>7.77</td>\n",
       "      <td>2010-10-03T00:00:00+00:00</td>\n",
       "      <td>2011-03-26T00:00:00+00:00</td>\n",
       "      <td>56095</td>\n",
       "      <td>PG-13</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>9</th>\n",
       "      <td>36976</td>\n",
       "      <td>https://myanimelist.net/anime/36976/Cutie_Hone...</td>\n",
       "      <td>https://cdn.myanimelist.net/images/anime/1879/...</td>\n",
       "      <td>Cutie Honey Universe</td>\n",
       "      <td>False</td>\n",
       "      <td>The forces of evil are on the rise. When the e...</td>\n",
       "      <td>TV</td>\n",
       "      <td>12</td>\n",
       "      <td>5.41</td>\n",
       "      <td>2018-04-08T00:00:00+00:00</td>\n",
       "      <td>2018-06-24T00:00:00+00:00</td>\n",
       "      <td>16361</td>\n",
       "      <td>R</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "   mal_id                                                url  \\\n",
       "0   32379        https://myanimelist.net/anime/32379/Berserk   \n",
       "1      33  https://myanimelist.net/anime/33/Kenpuu_Denki_...   \n",
       "2   34055  https://myanimelist.net/anime/34055/Berserk_2n...   \n",
       "3   35009  https://myanimelist.net/anime/35009/Berserk_Recap   \n",
       "4   12115  https://myanimelist.net/anime/12115/Berserk__O...   \n",
       "5   35676  https://myanimelist.net/anime/35676/Berserk__M...   \n",
       "6   10218  https://myanimelist.net/anime/10218/Berserk__O...   \n",
       "7   12113  https://myanimelist.net/anime/12113/Berserk__O...   \n",
       "8    8311  https://myanimelist.net/anime/8311/Tegamibachi...   \n",
       "9   36976  https://myanimelist.net/anime/36976/Cutie_Hone...   \n",
       "\n",
       "                                           image_url  \\\n",
       "0  https://cdn.myanimelist.net/images/anime/10/79...   \n",
       "1  https://cdn.myanimelist.net/images/anime/12/18...   \n",
       "2  https://cdn.myanimelist.net/images/anime/12/85...   \n",
       "3  https://cdn.myanimelist.net/images/anime/9/845...   \n",
       "4  https://cdn.myanimelist.net/images/anime/12/41...   \n",
       "5  https://cdn.myanimelist.net/images/anime/13/86...   \n",
       "6  https://cdn.myanimelist.net/images/anime/12/62...   \n",
       "7  https://cdn.myanimelist.net/images/anime/12/37...   \n",
       "8  https://cdn.myanimelist.net/images/anime/4/264...   \n",
       "9  https://cdn.myanimelist.net/images/anime/1879/...   \n",
       "\n",
       "                                            title  airing  \\\n",
       "0                                         Berserk   False   \n",
       "1                            Kenpuu Denki Berserk   False   \n",
       "2                              Berserk 2nd Season   False   \n",
       "3                                   Berserk Recap   False   \n",
       "4           Berserk: Ougon Jidai-hen III - Kourin   False   \n",
       "5                        Berserk: Majo no Tsuisou   False   \n",
       "6     Berserk: Ougon Jidai-hen I - Haou no Tamago   False   \n",
       "7  Berserk: Ougon Jidai-hen II - Doldrey Kouryaku   False   \n",
       "8                             Tegamibachi Reverse   False   \n",
       "9                            Cutie Honey Universe   False   \n",
       "\n",
       "                                            synopsis     type  episodes  \\\n",
       "0  Now branded for death and destined to be hunte...       TV        12   \n",
       "1  Born from the corpse of his mother, a young me...       TV        25   \n",
       "2  Demons have now become commonplace around the ...       TV        12   \n",
       "3                          Recap episode of Berserk.  Special         1   \n",
       "4  The Band of the Hawk has dwindled in the year ...    Movie         1   \n",
       "5               Recap episode of Berserk 2nd Season.  Special         1   \n",
       "6  In the Kingdom of Midland, a mercenary named G...    Movie         1   \n",
       "7  The Band of the Hawk and their enigmatic leade...    Movie         1   \n",
       "8  After Niche carries the wounded and stunned La...       TV        25   \n",
       "9  The forces of evil are on the rise. When the e...       TV        12   \n",
       "\n",
       "   score                 start_date                   end_date  members  rated  \n",
       "0   6.33  2016-07-01T00:00:00+00:00  2016-09-16T00:00:00+00:00   278821     R+  \n",
       "1   8.50  1997-10-08T00:00:00+00:00  1998-04-01T00:00:00+00:00   467356     R+  \n",
       "2   6.62  2017-04-07T00:00:00+00:00  2017-06-23T00:00:00+00:00   134905      R  \n",
       "3   6.04  2017-03-03T00:00:00+00:00  2017-03-03T00:00:00+00:00    10888      R  \n",
       "4   8.20  2013-02-01T00:00:00+00:00  2013-02-01T00:00:00+00:00   170117     R+  \n",
       "5   5.81  2017-06-02T00:00:00+00:00  2017-06-02T00:00:00+00:00    12969      R  \n",
       "6   7.74  2012-02-04T00:00:00+00:00  2012-02-04T00:00:00+00:00   176643     R+  \n",
       "7   7.90  2012-06-23T00:00:00+00:00  2012-06-23T00:00:00+00:00   162444     R+  \n",
       "8   7.77  2010-10-03T00:00:00+00:00  2011-03-26T00:00:00+00:00    56095  PG-13  \n",
       "9   5.41  2018-04-08T00:00:00+00:00  2018-06-24T00:00:00+00:00    16361      R  "
      ]
     },
     "execution_count": 25,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "dataframe = pandas.DataFrame(data_anime)\n",
    "dataframe"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "1541c31f",
   "metadata": {},
   "source": [
    "### Drop columns"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 26,
   "id": "2486639d",
   "metadata": {},
   "outputs": [],
   "source": [
    "#delete columns \n",
    "dataframe.drop(columns=[\"mal_id\", \"url\", \"image_url\", \"airing\", \"members\"], inplace=bool(1))"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "e58894ef",
   "metadata": {},
   "source": [
    "### Create a new column: id_anime"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 27,
   "id": "40898e6d",
   "metadata": {},
   "outputs": [],
   "source": [
    "#create index column\n",
    "dataframe[\"id_anime\"] = dataframe.index"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "a6803162",
   "metadata": {},
   "source": [
    "### Rename columns"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 28,
   "id": "afa37d85",
   "metadata": {},
   "outputs": [],
   "source": [
    "dataframe.rename(columns={\"start_date\":\"release_date\", \"end_date\":\"finish_date\"}, inplace=1)"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "480cee80",
   "metadata": {},
   "source": [
    "### Change format date"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 29,
   "id": "b082b00d",
   "metadata": {},
   "outputs": [],
   "source": [
    "dataframe[\"release_date\"] = pandas.to_datetime(dataframe[\"release_date\"], format=\"%Y-%m-%d\")\n",
    "dataframe[\"finish_date\"] = pandas.to_datetime(dataframe[\"finish_date\"], format=\"%Y-%m-%d\")"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 30,
   "id": "35cf3b09",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "title                        object\n",
       "synopsis                     object\n",
       "type                         object\n",
       "episodes                      int64\n",
       "score                       float64\n",
       "release_date    datetime64[ns, UTC]\n",
       "finish_date     datetime64[ns, UTC]\n",
       "rated                        object\n",
       "id_anime                      int64\n",
       "dtype: object"
      ]
     },
     "execution_count": 30,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "dataframe.dtypes"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "80a5a38e",
   "metadata": {},
   "source": [
    "### View"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 31,
   "id": "d817aace",
   "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>synopsis</th>\n",
       "      <th>type</th>\n",
       "      <th>episodes</th>\n",
       "      <th>score</th>\n",
       "      <th>release_date</th>\n",
       "      <th>finish_date</th>\n",
       "      <th>rated</th>\n",
       "      <th>id_anime</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>Berserk</td>\n",
       "      <td>Now branded for death and destined to be hunte...</td>\n",
       "      <td>TV</td>\n",
       "      <td>12</td>\n",
       "      <td>6.33</td>\n",
       "      <td>2016-07-01 00:00:00+00:00</td>\n",
       "      <td>2016-09-16 00:00:00+00:00</td>\n",
       "      <td>R+</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>Kenpuu Denki Berserk</td>\n",
       "      <td>Born from the corpse of his mother, a young me...</td>\n",
       "      <td>TV</td>\n",
       "      <td>25</td>\n",
       "      <td>8.50</td>\n",
       "      <td>1997-10-08 00:00:00+00:00</td>\n",
       "      <td>1998-04-01 00:00:00+00:00</td>\n",
       "      <td>R+</td>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>Berserk 2nd Season</td>\n",
       "      <td>Demons have now become commonplace around the ...</td>\n",
       "      <td>TV</td>\n",
       "      <td>12</td>\n",
       "      <td>6.62</td>\n",
       "      <td>2017-04-07 00:00:00+00:00</td>\n",
       "      <td>2017-06-23 00:00:00+00:00</td>\n",
       "      <td>R</td>\n",
       "      <td>2</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>Berserk Recap</td>\n",
       "      <td>Recap episode of Berserk.</td>\n",
       "      <td>Special</td>\n",
       "      <td>1</td>\n",
       "      <td>6.04</td>\n",
       "      <td>2017-03-03 00:00:00+00:00</td>\n",
       "      <td>2017-03-03 00:00:00+00:00</td>\n",
       "      <td>R</td>\n",
       "      <td>3</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>Berserk: Ougon Jidai-hen III - Kourin</td>\n",
       "      <td>The Band of the Hawk has dwindled in the year ...</td>\n",
       "      <td>Movie</td>\n",
       "      <td>1</td>\n",
       "      <td>8.20</td>\n",
       "      <td>2013-02-01 00:00:00+00:00</td>\n",
       "      <td>2013-02-01 00:00:00+00:00</td>\n",
       "      <td>R+</td>\n",
       "      <td>4</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>5</th>\n",
       "      <td>Berserk: Majo no Tsuisou</td>\n",
       "      <td>Recap episode of Berserk 2nd Season.</td>\n",
       "      <td>Special</td>\n",
       "      <td>1</td>\n",
       "      <td>5.81</td>\n",
       "      <td>2017-06-02 00:00:00+00:00</td>\n",
       "      <td>2017-06-02 00:00:00+00:00</td>\n",
       "      <td>R</td>\n",
       "      <td>5</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>6</th>\n",
       "      <td>Berserk: Ougon Jidai-hen I - Haou no Tamago</td>\n",
       "      <td>In the Kingdom of Midland, a mercenary named G...</td>\n",
       "      <td>Movie</td>\n",
       "      <td>1</td>\n",
       "      <td>7.74</td>\n",
       "      <td>2012-02-04 00:00:00+00:00</td>\n",
       "      <td>2012-02-04 00:00:00+00:00</td>\n",
       "      <td>R+</td>\n",
       "      <td>6</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>7</th>\n",
       "      <td>Berserk: Ougon Jidai-hen II - Doldrey Kouryaku</td>\n",
       "      <td>The Band of the Hawk and their enigmatic leade...</td>\n",
       "      <td>Movie</td>\n",
       "      <td>1</td>\n",
       "      <td>7.90</td>\n",
       "      <td>2012-06-23 00:00:00+00:00</td>\n",
       "      <td>2012-06-23 00:00:00+00:00</td>\n",
       "      <td>R+</td>\n",
       "      <td>7</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>8</th>\n",
       "      <td>Tegamibachi Reverse</td>\n",
       "      <td>After Niche carries the wounded and stunned La...</td>\n",
       "      <td>TV</td>\n",
       "      <td>25</td>\n",
       "      <td>7.77</td>\n",
       "      <td>2010-10-03 00:00:00+00:00</td>\n",
       "      <td>2011-03-26 00:00:00+00:00</td>\n",
       "      <td>PG-13</td>\n",
       "      <td>8</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>9</th>\n",
       "      <td>Cutie Honey Universe</td>\n",
       "      <td>The forces of evil are on the rise. When the e...</td>\n",
       "      <td>TV</td>\n",
       "      <td>12</td>\n",
       "      <td>5.41</td>\n",
       "      <td>2018-04-08 00:00:00+00:00</td>\n",
       "      <td>2018-06-24 00:00:00+00:00</td>\n",
       "      <td>R</td>\n",
       "      <td>9</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "                                            title  \\\n",
       "0                                         Berserk   \n",
       "1                            Kenpuu Denki Berserk   \n",
       "2                              Berserk 2nd Season   \n",
       "3                                   Berserk Recap   \n",
       "4           Berserk: Ougon Jidai-hen III - Kourin   \n",
       "5                        Berserk: Majo no Tsuisou   \n",
       "6     Berserk: Ougon Jidai-hen I - Haou no Tamago   \n",
       "7  Berserk: Ougon Jidai-hen II - Doldrey Kouryaku   \n",
       "8                             Tegamibachi Reverse   \n",
       "9                            Cutie Honey Universe   \n",
       "\n",
       "                                            synopsis     type  episodes  \\\n",
       "0  Now branded for death and destined to be hunte...       TV        12   \n",
       "1  Born from the corpse of his mother, a young me...       TV        25   \n",
       "2  Demons have now become commonplace around the ...       TV        12   \n",
       "3                          Recap episode of Berserk.  Special         1   \n",
       "4  The Band of the Hawk has dwindled in the year ...    Movie         1   \n",
       "5               Recap episode of Berserk 2nd Season.  Special         1   \n",
       "6  In the Kingdom of Midland, a mercenary named G...    Movie         1   \n",
       "7  The Band of the Hawk and their enigmatic leade...    Movie         1   \n",
       "8  After Niche carries the wounded and stunned La...       TV        25   \n",
       "9  The forces of evil are on the rise. When the e...       TV        12   \n",
       "\n",
       "   score              release_date               finish_date  rated  id_anime  \n",
       "0   6.33 2016-07-01 00:00:00+00:00 2016-09-16 00:00:00+00:00     R+         0  \n",
       "1   8.50 1997-10-08 00:00:00+00:00 1998-04-01 00:00:00+00:00     R+         1  \n",
       "2   6.62 2017-04-07 00:00:00+00:00 2017-06-23 00:00:00+00:00      R         2  \n",
       "3   6.04 2017-03-03 00:00:00+00:00 2017-03-03 00:00:00+00:00      R         3  \n",
       "4   8.20 2013-02-01 00:00:00+00:00 2013-02-01 00:00:00+00:00     R+         4  \n",
       "5   5.81 2017-06-02 00:00:00+00:00 2017-06-02 00:00:00+00:00      R         5  \n",
       "6   7.74 2012-02-04 00:00:00+00:00 2012-02-04 00:00:00+00:00     R+         6  \n",
       "7   7.90 2012-06-23 00:00:00+00:00 2012-06-23 00:00:00+00:00     R+         7  \n",
       "8   7.77 2010-10-03 00:00:00+00:00 2011-03-26 00:00:00+00:00  PG-13         8  \n",
       "9   5.41 2018-04-08 00:00:00+00:00 2018-06-24 00:00:00+00:00      R         9  "
      ]
     },
     "execution_count": 31,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "dataframe"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "93641fea",
   "metadata": {},
   "source": [
    "### Filter animes with the word Berserk in the title"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 32,
   "id": "7a2f35a5",
   "metadata": {},
   "outputs": [],
   "source": [
    "#filter just animes what contains berserk in the title\n",
    "titles_with_the_word_berserk = dataframe[\"title\"].str.contains(\"Berserk\")\n",
    "dataframe_berserk = dataframe.loc[titles_with_the_word_berserk]"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "cb9223b3",
   "metadata": {},
   "source": [
    "### View"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 33,
   "id": "99889903",
   "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>synopsis</th>\n",
       "      <th>type</th>\n",
       "      <th>episodes</th>\n",
       "      <th>score</th>\n",
       "      <th>release_date</th>\n",
       "      <th>finish_date</th>\n",
       "      <th>rated</th>\n",
       "      <th>id_anime</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>Berserk</td>\n",
       "      <td>Now branded for death and destined to be hunte...</td>\n",
       "      <td>TV</td>\n",
       "      <td>12</td>\n",
       "      <td>6.33</td>\n",
       "      <td>2016-07-01 00:00:00+00:00</td>\n",
       "      <td>2016-09-16 00:00:00+00:00</td>\n",
       "      <td>R+</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>Kenpuu Denki Berserk</td>\n",
       "      <td>Born from the corpse of his mother, a young me...</td>\n",
       "      <td>TV</td>\n",
       "      <td>25</td>\n",
       "      <td>8.50</td>\n",
       "      <td>1997-10-08 00:00:00+00:00</td>\n",
       "      <td>1998-04-01 00:00:00+00:00</td>\n",
       "      <td>R+</td>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>Berserk 2nd Season</td>\n",
       "      <td>Demons have now become commonplace around the ...</td>\n",
       "      <td>TV</td>\n",
       "      <td>12</td>\n",
       "      <td>6.62</td>\n",
       "      <td>2017-04-07 00:00:00+00:00</td>\n",
       "      <td>2017-06-23 00:00:00+00:00</td>\n",
       "      <td>R</td>\n",
       "      <td>2</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>Berserk Recap</td>\n",
       "      <td>Recap episode of Berserk.</td>\n",
       "      <td>Special</td>\n",
       "      <td>1</td>\n",
       "      <td>6.04</td>\n",
       "      <td>2017-03-03 00:00:00+00:00</td>\n",
       "      <td>2017-03-03 00:00:00+00:00</td>\n",
       "      <td>R</td>\n",
       "      <td>3</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>Berserk: Ougon Jidai-hen III - Kourin</td>\n",
       "      <td>The Band of the Hawk has dwindled in the year ...</td>\n",
       "      <td>Movie</td>\n",
       "      <td>1</td>\n",
       "      <td>8.20</td>\n",
       "      <td>2013-02-01 00:00:00+00:00</td>\n",
       "      <td>2013-02-01 00:00:00+00:00</td>\n",
       "      <td>R+</td>\n",
       "      <td>4</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>5</th>\n",
       "      <td>Berserk: Majo no Tsuisou</td>\n",
       "      <td>Recap episode of Berserk 2nd Season.</td>\n",
       "      <td>Special</td>\n",
       "      <td>1</td>\n",
       "      <td>5.81</td>\n",
       "      <td>2017-06-02 00:00:00+00:00</td>\n",
       "      <td>2017-06-02 00:00:00+00:00</td>\n",
       "      <td>R</td>\n",
       "      <td>5</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>6</th>\n",
       "      <td>Berserk: Ougon Jidai-hen I - Haou no Tamago</td>\n",
       "      <td>In the Kingdom of Midland, a mercenary named G...</td>\n",
       "      <td>Movie</td>\n",
       "      <td>1</td>\n",
       "      <td>7.74</td>\n",
       "      <td>2012-02-04 00:00:00+00:00</td>\n",
       "      <td>2012-02-04 00:00:00+00:00</td>\n",
       "      <td>R+</td>\n",
       "      <td>6</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>7</th>\n",
       "      <td>Berserk: Ougon Jidai-hen II - Doldrey Kouryaku</td>\n",
       "      <td>The Band of the Hawk and their enigmatic leade...</td>\n",
       "      <td>Movie</td>\n",
       "      <td>1</td>\n",
       "      <td>7.90</td>\n",
       "      <td>2012-06-23 00:00:00+00:00</td>\n",
       "      <td>2012-06-23 00:00:00+00:00</td>\n",
       "      <td>R+</td>\n",
       "      <td>7</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "                                            title  \\\n",
       "0                                         Berserk   \n",
       "1                            Kenpuu Denki Berserk   \n",
       "2                              Berserk 2nd Season   \n",
       "3                                   Berserk Recap   \n",
       "4           Berserk: Ougon Jidai-hen III - Kourin   \n",
       "5                        Berserk: Majo no Tsuisou   \n",
       "6     Berserk: Ougon Jidai-hen I - Haou no Tamago   \n",
       "7  Berserk: Ougon Jidai-hen II - Doldrey Kouryaku   \n",
       "\n",
       "                                            synopsis     type  episodes  \\\n",
       "0  Now branded for death and destined to be hunte...       TV        12   \n",
       "1  Born from the corpse of his mother, a young me...       TV        25   \n",
       "2  Demons have now become commonplace around the ...       TV        12   \n",
       "3                          Recap episode of Berserk.  Special         1   \n",
       "4  The Band of the Hawk has dwindled in the year ...    Movie         1   \n",
       "5               Recap episode of Berserk 2nd Season.  Special         1   \n",
       "6  In the Kingdom of Midland, a mercenary named G...    Movie         1   \n",
       "7  The Band of the Hawk and their enigmatic leade...    Movie         1   \n",
       "\n",
       "   score              release_date               finish_date rated  id_anime  \n",
       "0   6.33 2016-07-01 00:00:00+00:00 2016-09-16 00:00:00+00:00    R+         0  \n",
       "1   8.50 1997-10-08 00:00:00+00:00 1998-04-01 00:00:00+00:00    R+         1  \n",
       "2   6.62 2017-04-07 00:00:00+00:00 2017-06-23 00:00:00+00:00     R         2  \n",
       "3   6.04 2017-03-03 00:00:00+00:00 2017-03-03 00:00:00+00:00     R         3  \n",
       "4   8.20 2013-02-01 00:00:00+00:00 2013-02-01 00:00:00+00:00    R+         4  \n",
       "5   5.81 2017-06-02 00:00:00+00:00 2017-06-02 00:00:00+00:00     R         5  \n",
       "6   7.74 2012-02-04 00:00:00+00:00 2012-02-04 00:00:00+00:00    R+         6  \n",
       "7   7.90 2012-06-23 00:00:00+00:00 2012-06-23 00:00:00+00:00    R+         7  "
      ]
     },
     "execution_count": 33,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "dataframe_berserk"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "4f3e1520",
   "metadata": {},
   "source": [
    "### MariaDB: transfer to a table"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 34,
   "id": "6c9abd9e",
   "metadata": {},
   "outputs": [],
   "source": [
    "#create engine and connection, user:pass\n",
    "#mariadb = create_engine(\"mysql+mysqlconnector://\" + user_n_password1 + \"@localhost/animes\")\n",
    "#convert dataframe to sql and transfer to a table \n",
    "#dataframe_berserk.to_sql(name=\"berserk\", con=mariadb, index=0)"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "61e0bd91",
   "metadata": {},
   "source": [
    "### PostgreSQL: transfer to a table "
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 35,
   "id": "2b175f2b",
   "metadata": {},
   "outputs": [],
   "source": [
    "#create engine and connection, user:pass\n",
    "#postgres = create_engine(\"postgresql+psycopg2://\" + user_n_password2 + \"@localhost/animes\")\n",
    "#convert dataframe to sql and transfer to a table\n",
    "#dataframe_berserk.to_sql(name=\"berserk\", con=postgres, index=0)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "b29b7914",
   "metadata": {},
   "outputs": [],
   "source": []
  }
 ],
 "metadata": {
  "kernelspec": {
   "display_name": "Python 3",
   "language": "python",
   "name": "python3"
  },
  "language_info": {
   "codemirror_mode": {
    "name": "ipython",
    "version": 3
   },
   "file_extension": ".py",
   "mimetype": "text/x-python",
   "name": "python",
   "nbconvert_exporter": "python",
   "pygments_lexer": "ipython3",
   "version": "3.8.5"
  }
 },
 "nbformat": 4,
 "nbformat_minor": 5
}