manga.ipynb

{
 "cells": [
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## Manga Merge"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 1,
   "metadata": {},
   "outputs": [],
   "source": [
    "iguals = ['manga_id', 'title', 'score', 'volumes', 'chapters', 'start_date', 'end_date', 'members', 'favorites', 'sfw', 'approved', \\\n",
    "    'real_start_date', 'real_end_date', 'genres', 'themes', 'demographics', 'main_picture', 'title_english', 'title_japanese']\n",
    "\n",
    "mal_millor = ['scored_by', 'authors', 'synopsis', 'title_synonyms']\n",
    "\n",
    "mal_extra = ['created_at_before', 'updated_at']\n",
    "\n",
    "formatted_strings = ['type', 'status']\n",
    "\n",
    "jikan_extra = ['serializations', 'background', 'url']"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 2,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "(65941, 30)\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>manga_id</th>\n",
       "      <th>title</th>\n",
       "      <th>type</th>\n",
       "      <th>score</th>\n",
       "      <th>scored_by</th>\n",
       "      <th>status</th>\n",
       "      <th>volumes</th>\n",
       "      <th>chapters</th>\n",
       "      <th>start_date</th>\n",
       "      <th>end_date</th>\n",
       "      <th>members</th>\n",
       "      <th>favorites</th>\n",
       "      <th>sfw</th>\n",
       "      <th>approved</th>\n",
       "      <th>created_at_before</th>\n",
       "      <th>updated_at</th>\n",
       "      <th>real_start_date</th>\n",
       "      <th>real_end_date</th>\n",
       "      <th>genres</th>\n",
       "      <th>themes</th>\n",
       "      <th>demographics</th>\n",
       "      <th>authors</th>\n",
       "      <th>serializations</th>\n",
       "      <th>synopsis</th>\n",
       "      <th>background</th>\n",
       "      <th>main_picture</th>\n",
       "      <th>url</th>\n",
       "      <th>title_english</th>\n",
       "      <th>title_japanese</th>\n",
       "      <th>title_synonyms</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>2</td>\n",
       "      <td>Berserk</td>\n",
       "      <td>manga</td>\n",
       "      <td>9.45</td>\n",
       "      <td>268737</td>\n",
       "      <td>currently_publishing</td>\n",
       "      <td>&lt;NA&gt;</td>\n",
       "      <td>&lt;NA&gt;</td>\n",
       "      <td>1989-08-25</td>\n",
       "      <td>NaT</td>\n",
       "      <td>551266</td>\n",
       "      <td>103820</td>\n",
       "      <td>True</td>\n",
       "      <td>True</td>\n",
       "      <td>2007-07-17 20:14:45+00:00</td>\n",
       "      <td>2022-06-23 08:30:44+00:00</td>\n",
       "      <td>1989-08-25</td>\n",
       "      <td>NaN</td>\n",
       "      <td>[Action, Adventure, Award Winning, Drama, Fant...</td>\n",
       "      <td>[Gore, Military, Mythology, Psychological]</td>\n",
       "      <td>[Seinen]</td>\n",
       "      <td>[{'id': 1868, 'first_name': 'Kentarou', 'last_...</td>\n",
       "      <td>[Young Animal]</td>\n",
       "      <td>Guts, a former mercenary now known as the \"Bla...</td>\n",
       "      <td>Berserk won the Award for Excellence at the si...</td>\n",
       "      <td>https://cdn.myanimelist.net/images/manga/1/157...</td>\n",
       "      <td>https://myanimelist.net/manga/2/Berserk</td>\n",
       "      <td>Berserk</td>\n",
       "      <td>ベルセルク</td>\n",
       "      <td>[Berserk: The Prototype]</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "   manga_id    title   type  score  scored_by                status  volumes  \\\n",
       "0         2  Berserk  manga   9.45     268737  currently_publishing     <NA>   \n",
       "\n",
       "   chapters start_date end_date  members  favorites   sfw  approved  \\\n",
       "0      <NA> 1989-08-25      NaT   551266     103820  True      True   \n",
       "\n",
       "          created_at_before                updated_at real_start_date  \\\n",
       "0 2007-07-17 20:14:45+00:00 2022-06-23 08:30:44+00:00      1989-08-25   \n",
       "\n",
       "  real_end_date                                             genres  \\\n",
       "0           NaN  [Action, Adventure, Award Winning, Drama, Fant...   \n",
       "\n",
       "                                       themes demographics  \\\n",
       "0  [Gore, Military, Mythology, Psychological]     [Seinen]   \n",
       "\n",
       "                                             authors  serializations  \\\n",
       "0  [{'id': 1868, 'first_name': 'Kentarou', 'last_...  [Young Animal]   \n",
       "\n",
       "                                            synopsis  \\\n",
       "0  Guts, a former mercenary now known as the \"Bla...   \n",
       "\n",
       "                                          background  \\\n",
       "0  Berserk won the Award for Excellence at the si...   \n",
       "\n",
       "                                        main_picture  \\\n",
       "0  https://cdn.myanimelist.net/images/manga/1/157...   \n",
       "\n",
       "                                       url title_english title_japanese  \\\n",
       "0  https://myanimelist.net/manga/2/Berserk       Berserk          ベルセルク   \n",
       "\n",
       "             title_synonyms  \n",
       "0  [Berserk: The Prototype]  "
      ]
     },
     "execution_count": 2,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "import pandas as pd\n",
    "import ast\n",
    "\n",
    "mal = pd.read_csv('data/manga_mal.csv')\n",
    "jikan = pd.read_csv('data/manga_jikan.csv')\n",
    "\n",
    "manga = pd.merge(mal[iguals + mal_millor + mal_extra + formatted_strings], jikan[['manga_id'] + jikan_extra], on='manga_id')\n",
    "\n",
    "order = ['manga_id', 'title', 'type', 'score', 'scored_by', 'status', 'volumes', 'chapters', 'start_date', 'end_date',\n",
    "         'members', 'favorites', 'sfw', 'approved', 'created_at_before', 'updated_at', 'real_start_date', 'real_end_date',\n",
    "         'genres', 'themes', 'demographics', 'authors', 'serializations', 'synopsis', 'background', 'main_picture', 'url',\n",
    "         'title_english', 'title_japanese', 'title_synonyms']\n",
    "\n",
    "manga = manga[order]\n",
    "\n",
    "for col in ['start_date', 'end_date', 'created_at_before', 'updated_at']:\n",
    "    manga[col] = pd.to_datetime(manga[col])\n",
    "\n",
    "for col in ['volumes', 'chapters']:\n",
    "    manga[col] = manga[col].astype('Int64')\n",
    "\n",
    "for col in ['genres', 'themes', 'demographics', 'authors', 'serializations', 'title_synonyms']:\n",
    "    manga[col] = manga[col].apply(ast.literal_eval)\n",
    "\n",
    "print(manga.shape)\n",
    "\n",
    "manga.to_csv('data/manga.csv', index=False)\n",
    "\n",
    "pd.options.display.max_columns = None\n",
    "manga.head(1)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## Load Manga"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 3,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>manga_id</th>\n",
       "      <th>title</th>\n",
       "      <th>type</th>\n",
       "      <th>score</th>\n",
       "      <th>scored_by</th>\n",
       "      <th>status</th>\n",
       "      <th>volumes</th>\n",
       "      <th>chapters</th>\n",
       "      <th>start_date</th>\n",
       "      <th>end_date</th>\n",
       "      <th>members</th>\n",
       "      <th>favorites</th>\n",
       "      <th>sfw</th>\n",
       "      <th>approved</th>\n",
       "      <th>created_at_before</th>\n",
       "      <th>updated_at</th>\n",
       "      <th>real_start_date</th>\n",
       "      <th>real_end_date</th>\n",
       "      <th>genres</th>\n",
       "      <th>themes</th>\n",
       "      <th>demographics</th>\n",
       "      <th>authors</th>\n",
       "      <th>serializations</th>\n",
       "      <th>synopsis</th>\n",
       "      <th>background</th>\n",
       "      <th>main_picture</th>\n",
       "      <th>url</th>\n",
       "      <th>title_english</th>\n",
       "      <th>title_japanese</th>\n",
       "      <th>title_synonyms</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>2</td>\n",
       "      <td>Berserk</td>\n",
       "      <td>manga</td>\n",
       "      <td>9.45</td>\n",
       "      <td>268737</td>\n",
       "      <td>currently_publishing</td>\n",
       "      <td>&lt;NA&gt;</td>\n",
       "      <td>&lt;NA&gt;</td>\n",
       "      <td>1989-08-25</td>\n",
       "      <td>NaT</td>\n",
       "      <td>551266</td>\n",
       "      <td>103820</td>\n",
       "      <td>True</td>\n",
       "      <td>True</td>\n",
       "      <td>2007-07-17 20:14:45+00:00</td>\n",
       "      <td>2022-06-23 08:30:44+00:00</td>\n",
       "      <td>1989-08-25</td>\n",
       "      <td>NaN</td>\n",
       "      <td>[Action, Adventure, Award Winning, Drama, Fant...</td>\n",
       "      <td>[Gore, Military, Mythology, Psychological]</td>\n",
       "      <td>[Seinen]</td>\n",
       "      <td>[{'id': 1868, 'first_name': 'Kentarou', 'last_...</td>\n",
       "      <td>[Young Animal]</td>\n",
       "      <td>Guts, a former mercenary now known as the \"Bla...</td>\n",
       "      <td>Berserk won the Award for Excellence at the si...</td>\n",
       "      <td>https://cdn.myanimelist.net/images/manga/1/157...</td>\n",
       "      <td>https://myanimelist.net/manga/2/Berserk</td>\n",
       "      <td>Berserk</td>\n",
       "      <td>ベルセルク</td>\n",
       "      <td>[Berserk: The Prototype]</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "   manga_id    title   type  score  scored_by                status  volumes  \\\n",
       "0         2  Berserk  manga   9.45     268737  currently_publishing     <NA>   \n",
       "\n",
       "   chapters start_date end_date  members  favorites   sfw  approved  \\\n",
       "0      <NA> 1989-08-25      NaT   551266     103820  True      True   \n",
       "\n",
       "          created_at_before                updated_at real_start_date  \\\n",
       "0 2007-07-17 20:14:45+00:00 2022-06-23 08:30:44+00:00      1989-08-25   \n",
       "\n",
       "  real_end_date                                             genres  \\\n",
       "0           NaN  [Action, Adventure, Award Winning, Drama, Fant...   \n",
       "\n",
       "                                       themes demographics  \\\n",
       "0  [Gore, Military, Mythology, Psychological]     [Seinen]   \n",
       "\n",
       "                                             authors  serializations  \\\n",
       "0  [{'id': 1868, 'first_name': 'Kentarou', 'last_...  [Young Animal]   \n",
       "\n",
       "                                            synopsis  \\\n",
       "0  Guts, a former mercenary now known as the \"Bla...   \n",
       "\n",
       "                                          background  \\\n",
       "0  Berserk won the Award for Excellence at the si...   \n",
       "\n",
       "                                        main_picture  \\\n",
       "0  https://cdn.myanimelist.net/images/manga/1/157...   \n",
       "\n",
       "                                       url title_english title_japanese  \\\n",
       "0  https://myanimelist.net/manga/2/Berserk       Berserk          ベルセルク   \n",
       "\n",
       "             title_synonyms  \n",
       "0  [Berserk: The Prototype]  "
      ]
     },
     "execution_count": 3,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "import pandas as pd\n",
    "import ast\n",
    "\n",
    "manga = pd.read_csv('data/manga.csv')\n",
    "\n",
    "for col in ['start_date', 'end_date', 'created_at_before', 'updated_at']:\n",
    "    manga[col] = pd.to_datetime(manga[col])\n",
    "\n",
    "for col in ['volumes', 'chapters']:\n",
    "    manga[col] = manga[col].astype('Int64')\n",
    "\n",
    "for col in ['genres', 'themes', 'demographics', 'authors', 'serializations', 'title_synonyms']:\n",
    "    manga[col] = manga[col].apply(ast.literal_eval)\n",
    "\n",
    "pd.options.display.max_columns = None\n",
    "manga.head(1)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## Differences\n",
    "- There's quite a lot (1.332) of not cached Manga!\n",
    "- 268 of them apre approved\n",
    "- Some more than 2 months and not saved yet"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 4,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "both          65941\n",
       "left_only      1332\n",
       "right_only      447\n",
       "Name: _merge, dtype: int64"
      ]
     },
     "execution_count": 4,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "manga_all = pd.merge(mal, jikan, on='manga_id', how='outer', indicator=True)\n",
    "\n",
    "manga_all._merge.value_counts()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 5,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "1332"
      ]
     },
     "execution_count": 5,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# Not scraped on Jikan\n",
    "missing = mal[mal.manga_id.isin(manga_all[manga_all._merge=='left_only'].manga_id)].sort_values('updated_at')\n",
    "\n",
    "missing.shape[0]"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 6,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "447"
      ]
     },
     "execution_count": 6,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# Lost on MAL API (favorite around 3.500) or deleted from MyAnimeList \n",
    "deleted = jikan[jikan.manga_id.isin(manga_all[manga_all._merge=='right_only'].manga_id)].sort_values('favorites', ascending=False)\n",
    "\n",
    "deleted.shape[0]"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 7,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "24152"
      ]
     },
     "execution_count": 7,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# Completament iguals\n",
    "compl_iguals = pd.merge(mal[iguals], jikan[iguals])\n",
    "\n",
    "compl_iguals.shape[0]"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 8,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "64883"
      ]
     },
     "execution_count": 8,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# Iguals\n",
    "changing = ['score', 'scored_by', 'members', 'favorites']\n",
    "iguals_not_changing = [col for col in iguals if col not in changing]\n",
    "\n",
    "df_iguals = pd.merge(mal[iguals_not_changing], jikan[iguals_not_changing])\n",
    "df_iguals.shape[0]"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 9,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "1058"
      ]
     },
     "execution_count": 9,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# Changed\n",
    "\n",
    "manga_both = pd.merge(mal[iguals_not_changing], jikan[iguals_not_changing], how='outer', indicator=True)\n",
    "\n",
    "changed = manga_both[~manga_both.manga_id.isin(list(deleted.manga_id) + list(missing.manga_id)) & (manga_both['_merge']!='both')] \\\n",
    "    .sort_values(['manga_id', '_merge']).drop(columns=['_merge'])\n",
    "\n",
    "changed.shape[0] // 2"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 10,
   "metadata": {},
   "outputs": [],
   "source": [
    "import collections as c\n",
    "\n",
    "changes = c.Counter()\n",
    "\n",
    "for i in range(changed.shape[0] // 2):\n",
    "    changed_manga = changed[2*i:2*(i+1)]\n",
    "    first, second = changed_manga.iloc[0], changed_manga.iloc[1]\n",
    "    is_diff_cols = (first!=second) & ~(first.isna() & second.isna())\n",
    "    diff_cols = is_diff_cols[is_diff_cols].index\n",
    "    for col in diff_cols:\n",
    "        changes[col] += 1"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 11,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "[('title_japanese', 514),\n",
       " ('end_date', 405),\n",
       " ('real_end_date', 405),\n",
       " ('main_picture', 164),\n",
       " ('title_english', 144),\n",
       " ('start_date', 126),\n",
       " ('real_start_date', 126),\n",
       " ('approved', 84),\n",
       " ('genres', 52),\n",
       " ('title', 51),\n",
       " ('chapters', 43),\n",
       " ('themes', 37),\n",
       " ('volumes', 27),\n",
       " ('demographics', 26),\n",
       " ('sfw', 10)]"
      ]
     },
     "execution_count": 11,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "changes.most_common()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": []
  }
 ],
 "metadata": {
  "kernelspec": {
   "display_name": "Python 3.10.5 64-bit",
   "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.5"
  },
  "orig_nbformat": 4,
  "vscode": {
   "interpreter": {
    "hash": "ad2117d8da04c720903da65ba8862d63b2fb82849cd17efbeca69c80f28b43db"
   }
  }
 },
 "nbformat": 4,
 "nbformat_minor": 2
}