Marvel Super Heroes 69bcf7.ipynb

{
 "cells": [
  {
   "cell_type": "code",
   "execution_count": 2,
   "metadata": {
    "_cell_guid": "b1076dfc-b9ad-4769-8c92-a6c4dae69d19",
    "_uuid": "8f2839f25d086af736a60e9eeb907d3b93b6e0e5"
   },
   "outputs": [],
   "source": [
    "# This Python 3 environment comes with many helpful analytics libraries installed\n",
    "# It is defined by the kaggle/python docker image: https://github.com/kaggle/docker-python\n",
    "# For example, here's several helpful packages to load in \n",
    "\n",
    "import numpy as np # linear algebra\n",
    "import pandas as pd # data processing, CSV file I/O (e.g. pd.read_csv)\n",
    "import seaborn as sns\n",
    "import matplotlib.pyplot as plt\n",
    "%matplotlib inline\n",
    "\n",
    "# Input data files are available in the \"../input/\" directory.\n",
    "# For example, running this (by clicking run or pressing Shift+Enter) will list the files in the input directory\n",
    "\n",
    "# import os\n",
    "# print(os.listdir(\"../input\"))\n",
    "\n",
    "# Any results you write to the current directory are saved as output."
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {
    "_uuid": "72544db6933972547ae69858d41c11f7f4ff8043"
   },
   "source": [
    "**Reading Data**\n",
    "\n",
    "In this part, you read data from csv files and create dataframes for each.\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 3,
   "metadata": {
    "_cell_guid": "79c7e3d0-c299-4dcb-8224-4455121ee9b0",
    "_uuid": "d629ff2d2480ee46fbb7e2d37f6b5fab8052498a"
   },
   "outputs": [
    {
     "ename": "FileNotFoundError",
     "evalue": "[Errno 2] File b'../input/characters.csv' does not exist: b'../input/characters.csv'",
     "output_type": "error",
     "traceback": [
      "\u001b[1;31m---------------------------------------------------------------------------\u001b[0m",
      "\u001b[1;31mFileNotFoundError\u001b[0m                         Traceback (most recent call last)",
      "\u001b[1;32m<ipython-input-3-734b113f50a4>\u001b[0m in \u001b[0;36m<module>\u001b[1;34m\u001b[0m\n\u001b[1;32m----> 1\u001b[1;33m \u001b[0mcharacters\u001b[0m\u001b[1;33m=\u001b[0m\u001b[0mpd\u001b[0m\u001b[1;33m.\u001b[0m\u001b[0mread_csv\u001b[0m\u001b[1;33m(\u001b[0m\u001b[1;34m\"../input/characters.csv\"\u001b[0m\u001b[1;33m)\u001b[0m\u001b[1;33m\u001b[0m\u001b[1;33m\u001b[0m\u001b[0m\n\u001b[0m\u001b[0;32m      2\u001b[0m \u001b[0mcomics\u001b[0m\u001b[1;33m=\u001b[0m\u001b[0mpd\u001b[0m\u001b[1;33m.\u001b[0m\u001b[0mread_csv\u001b[0m\u001b[1;33m(\u001b[0m\u001b[1;34m\"../input/comics.csv\"\u001b[0m\u001b[1;33m)\u001b[0m\u001b[1;33m\u001b[0m\u001b[1;33m\u001b[0m\u001b[0m\n\u001b[0;32m      3\u001b[0m \u001b[0msuperheroes_power_matrix\u001b[0m\u001b[1;33m=\u001b[0m\u001b[0mpd\u001b[0m\u001b[1;33m.\u001b[0m\u001b[0mread_csv\u001b[0m\u001b[1;33m(\u001b[0m\u001b[1;34m\"../input/superheroes_power_matrix.csv\"\u001b[0m\u001b[1;33m)\u001b[0m\u001b[1;33m\u001b[0m\u001b[1;33m\u001b[0m\u001b[0m\n\u001b[0;32m      4\u001b[0m \u001b[0mcharcters_stats\u001b[0m\u001b[1;33m=\u001b[0m\u001b[0mpd\u001b[0m\u001b[1;33m.\u001b[0m\u001b[0mread_csv\u001b[0m\u001b[1;33m(\u001b[0m\u001b[1;34m\"../input/charcters_stats.csv\"\u001b[0m\u001b[1;33m)\u001b[0m\u001b[1;33m\u001b[0m\u001b[1;33m\u001b[0m\u001b[0m\n\u001b[0;32m      5\u001b[0m \u001b[0mmarvel_dc_characters\u001b[0m\u001b[1;33m=\u001b[0m\u001b[0mpd\u001b[0m\u001b[1;33m.\u001b[0m\u001b[0mread_excel\u001b[0m\u001b[1;33m(\u001b[0m\u001b[1;34m\"../input/marvel_dc_characters.xlsx\"\u001b[0m\u001b[1;33m)\u001b[0m\u001b[1;33m\u001b[0m\u001b[1;33m\u001b[0m\u001b[0m\n",
      "\u001b[1;32mC:\\ProgramData\\Anaconda3\\lib\\site-packages\\pandas\\io\\parsers.py\u001b[0m in \u001b[0;36mparser_f\u001b[1;34m(filepath_or_buffer, sep, delimiter, header, names, index_col, usecols, squeeze, prefix, mangle_dupe_cols, dtype, engine, converters, true_values, false_values, skipinitialspace, skiprows, skipfooter, nrows, na_values, keep_default_na, na_filter, verbose, skip_blank_lines, parse_dates, infer_datetime_format, keep_date_col, date_parser, dayfirst, cache_dates, iterator, chunksize, compression, thousands, decimal, lineterminator, quotechar, quoting, doublequote, escapechar, comment, encoding, dialect, error_bad_lines, warn_bad_lines, delim_whitespace, low_memory, memory_map, float_precision)\u001b[0m\n\u001b[0;32m    683\u001b[0m         )\n\u001b[0;32m    684\u001b[0m \u001b[1;33m\u001b[0m\u001b[0m\n\u001b[1;32m--> 685\u001b[1;33m         \u001b[1;32mreturn\u001b[0m \u001b[0m_read\u001b[0m\u001b[1;33m(\u001b[0m\u001b[0mfilepath_or_buffer\u001b[0m\u001b[1;33m,\u001b[0m \u001b[0mkwds\u001b[0m\u001b[1;33m)\u001b[0m\u001b[1;33m\u001b[0m\u001b[1;33m\u001b[0m\u001b[0m\n\u001b[0m\u001b[0;32m    686\u001b[0m \u001b[1;33m\u001b[0m\u001b[0m\n\u001b[0;32m    687\u001b[0m     \u001b[0mparser_f\u001b[0m\u001b[1;33m.\u001b[0m\u001b[0m__name__\u001b[0m \u001b[1;33m=\u001b[0m \u001b[0mname\u001b[0m\u001b[1;33m\u001b[0m\u001b[1;33m\u001b[0m\u001b[0m\n",
      "\u001b[1;32mC:\\ProgramData\\Anaconda3\\lib\\site-packages\\pandas\\io\\parsers.py\u001b[0m in \u001b[0;36m_read\u001b[1;34m(filepath_or_buffer, kwds)\u001b[0m\n\u001b[0;32m    455\u001b[0m \u001b[1;33m\u001b[0m\u001b[0m\n\u001b[0;32m    456\u001b[0m     \u001b[1;31m# Create the parser.\u001b[0m\u001b[1;33m\u001b[0m\u001b[1;33m\u001b[0m\u001b[1;33m\u001b[0m\u001b[0m\n\u001b[1;32m--> 457\u001b[1;33m     \u001b[0mparser\u001b[0m \u001b[1;33m=\u001b[0m \u001b[0mTextFileReader\u001b[0m\u001b[1;33m(\u001b[0m\u001b[0mfp_or_buf\u001b[0m\u001b[1;33m,\u001b[0m \u001b[1;33m**\u001b[0m\u001b[0mkwds\u001b[0m\u001b[1;33m)\u001b[0m\u001b[1;33m\u001b[0m\u001b[1;33m\u001b[0m\u001b[0m\n\u001b[0m\u001b[0;32m    458\u001b[0m \u001b[1;33m\u001b[0m\u001b[0m\n\u001b[0;32m    459\u001b[0m     \u001b[1;32mif\u001b[0m \u001b[0mchunksize\u001b[0m \u001b[1;32mor\u001b[0m \u001b[0miterator\u001b[0m\u001b[1;33m:\u001b[0m\u001b[1;33m\u001b[0m\u001b[1;33m\u001b[0m\u001b[0m\n",
      "\u001b[1;32mC:\\ProgramData\\Anaconda3\\lib\\site-packages\\pandas\\io\\parsers.py\u001b[0m in \u001b[0;36m__init__\u001b[1;34m(self, f, engine, **kwds)\u001b[0m\n\u001b[0;32m    893\u001b[0m             \u001b[0mself\u001b[0m\u001b[1;33m.\u001b[0m\u001b[0moptions\u001b[0m\u001b[1;33m[\u001b[0m\u001b[1;34m\"has_index_names\"\u001b[0m\u001b[1;33m]\u001b[0m \u001b[1;33m=\u001b[0m \u001b[0mkwds\u001b[0m\u001b[1;33m[\u001b[0m\u001b[1;34m\"has_index_names\"\u001b[0m\u001b[1;33m]\u001b[0m\u001b[1;33m\u001b[0m\u001b[1;33m\u001b[0m\u001b[0m\n\u001b[0;32m    894\u001b[0m \u001b[1;33m\u001b[0m\u001b[0m\n\u001b[1;32m--> 895\u001b[1;33m         \u001b[0mself\u001b[0m\u001b[1;33m.\u001b[0m\u001b[0m_make_engine\u001b[0m\u001b[1;33m(\u001b[0m\u001b[0mself\u001b[0m\u001b[1;33m.\u001b[0m\u001b[0mengine\u001b[0m\u001b[1;33m)\u001b[0m\u001b[1;33m\u001b[0m\u001b[1;33m\u001b[0m\u001b[0m\n\u001b[0m\u001b[0;32m    896\u001b[0m \u001b[1;33m\u001b[0m\u001b[0m\n\u001b[0;32m    897\u001b[0m     \u001b[1;32mdef\u001b[0m \u001b[0mclose\u001b[0m\u001b[1;33m(\u001b[0m\u001b[0mself\u001b[0m\u001b[1;33m)\u001b[0m\u001b[1;33m:\u001b[0m\u001b[1;33m\u001b[0m\u001b[1;33m\u001b[0m\u001b[0m\n",
      "\u001b[1;32mC:\\ProgramData\\Anaconda3\\lib\\site-packages\\pandas\\io\\parsers.py\u001b[0m in \u001b[0;36m_make_engine\u001b[1;34m(self, engine)\u001b[0m\n\u001b[0;32m   1133\u001b[0m     \u001b[1;32mdef\u001b[0m \u001b[0m_make_engine\u001b[0m\u001b[1;33m(\u001b[0m\u001b[0mself\u001b[0m\u001b[1;33m,\u001b[0m \u001b[0mengine\u001b[0m\u001b[1;33m=\u001b[0m\u001b[1;34m\"c\"\u001b[0m\u001b[1;33m)\u001b[0m\u001b[1;33m:\u001b[0m\u001b[1;33m\u001b[0m\u001b[1;33m\u001b[0m\u001b[0m\n\u001b[0;32m   1134\u001b[0m         \u001b[1;32mif\u001b[0m \u001b[0mengine\u001b[0m \u001b[1;33m==\u001b[0m \u001b[1;34m\"c\"\u001b[0m\u001b[1;33m:\u001b[0m\u001b[1;33m\u001b[0m\u001b[1;33m\u001b[0m\u001b[0m\n\u001b[1;32m-> 1135\u001b[1;33m             \u001b[0mself\u001b[0m\u001b[1;33m.\u001b[0m\u001b[0m_engine\u001b[0m \u001b[1;33m=\u001b[0m \u001b[0mCParserWrapper\u001b[0m\u001b[1;33m(\u001b[0m\u001b[0mself\u001b[0m\u001b[1;33m.\u001b[0m\u001b[0mf\u001b[0m\u001b[1;33m,\u001b[0m \u001b[1;33m**\u001b[0m\u001b[0mself\u001b[0m\u001b[1;33m.\u001b[0m\u001b[0moptions\u001b[0m\u001b[1;33m)\u001b[0m\u001b[1;33m\u001b[0m\u001b[1;33m\u001b[0m\u001b[0m\n\u001b[0m\u001b[0;32m   1136\u001b[0m         \u001b[1;32melse\u001b[0m\u001b[1;33m:\u001b[0m\u001b[1;33m\u001b[0m\u001b[1;33m\u001b[0m\u001b[0m\n\u001b[0;32m   1137\u001b[0m             \u001b[1;32mif\u001b[0m \u001b[0mengine\u001b[0m \u001b[1;33m==\u001b[0m \u001b[1;34m\"python\"\u001b[0m\u001b[1;33m:\u001b[0m\u001b[1;33m\u001b[0m\u001b[1;33m\u001b[0m\u001b[0m\n",
      "\u001b[1;32mC:\\ProgramData\\Anaconda3\\lib\\site-packages\\pandas\\io\\parsers.py\u001b[0m in \u001b[0;36m__init__\u001b[1;34m(self, src, **kwds)\u001b[0m\n\u001b[0;32m   1915\u001b[0m         \u001b[0mkwds\u001b[0m\u001b[1;33m[\u001b[0m\u001b[1;34m\"usecols\"\u001b[0m\u001b[1;33m]\u001b[0m \u001b[1;33m=\u001b[0m \u001b[0mself\u001b[0m\u001b[1;33m.\u001b[0m\u001b[0musecols\u001b[0m\u001b[1;33m\u001b[0m\u001b[1;33m\u001b[0m\u001b[0m\n\u001b[0;32m   1916\u001b[0m \u001b[1;33m\u001b[0m\u001b[0m\n\u001b[1;32m-> 1917\u001b[1;33m         \u001b[0mself\u001b[0m\u001b[1;33m.\u001b[0m\u001b[0m_reader\u001b[0m \u001b[1;33m=\u001b[0m \u001b[0mparsers\u001b[0m\u001b[1;33m.\u001b[0m\u001b[0mTextReader\u001b[0m\u001b[1;33m(\u001b[0m\u001b[0msrc\u001b[0m\u001b[1;33m,\u001b[0m \u001b[1;33m**\u001b[0m\u001b[0mkwds\u001b[0m\u001b[1;33m)\u001b[0m\u001b[1;33m\u001b[0m\u001b[1;33m\u001b[0m\u001b[0m\n\u001b[0m\u001b[0;32m   1918\u001b[0m         \u001b[0mself\u001b[0m\u001b[1;33m.\u001b[0m\u001b[0munnamed_cols\u001b[0m \u001b[1;33m=\u001b[0m \u001b[0mself\u001b[0m\u001b[1;33m.\u001b[0m\u001b[0m_reader\u001b[0m\u001b[1;33m.\u001b[0m\u001b[0munnamed_cols\u001b[0m\u001b[1;33m\u001b[0m\u001b[1;33m\u001b[0m\u001b[0m\n\u001b[0;32m   1919\u001b[0m \u001b[1;33m\u001b[0m\u001b[0m\n",
      "\u001b[1;32mpandas\\_libs\\parsers.pyx\u001b[0m in \u001b[0;36mpandas._libs.parsers.TextReader.__cinit__\u001b[1;34m()\u001b[0m\n",
      "\u001b[1;32mpandas\\_libs\\parsers.pyx\u001b[0m in \u001b[0;36mpandas._libs.parsers.TextReader._setup_parser_source\u001b[1;34m()\u001b[0m\n",
      "\u001b[1;31mFileNotFoundError\u001b[0m: [Errno 2] File b'../input/characters.csv' does not exist: b'../input/characters.csv'"
     ]
    }
   ],
   "source": [
    "characters=pd.read_csv(\"../input/characters.csv\")\n",
    "comics=pd.read_csv(\"../input/comics.csv\")\n",
    "superheroes_power_matrix=pd.read_csv(\"../input/superheroes_power_matrix.csv\")\n",
    "charcters_stats=pd.read_csv(\"../input/charcters_stats.csv\")\n",
    "marvel_dc_characters=pd.read_excel(\"../input/marvel_dc_characters.xlsx\")\n",
    "marvel_characters_info=pd.read_csv(\"../input/marvel_characters_info.csv\")\n",
    "charactersToComics=pd.read_csv(\"../input/charactersToComics.csv\")"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {
    "_uuid": "e9101642dc124197c4705a0d38af11f777fc25fe"
   },
   "source": [
    "**Finding Answers**\n",
    "\n",
    "In this part you try to answer the questions. Questions are categorized in 3 group up to their difficulties. If you don't want to get bored at the beginning, you can pass to medium category.\n",
    "\n",
    "It's better to use plots to visualize the answer, if possible."
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {
    "_uuid": "e82e10da3c0b57e3ab9bfd00d7a6aa8ef654bd6b"
   },
   "source": [
    "**Easy Questions**"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "_uuid": "7612a7f03839bbe5efc6df1d74816de49360e220"
   },
   "outputs": [],
   "source": [
    "# e-01\n",
    "# question: Good versus Evil - Which group has more combined power?\n",
    "# difficulty: easy\n",
    "# datasets: charcters_stats.csv\n",
    "\n",
    "##Group by alignment and aggregate total powers.\n",
    "print(charcters_stats.groupby(by='Alignment').agg(['count','mean','sum'])['Total'])\n",
    "print('Total power of good ones are greater than the bad ones')\n",
    "print('But if we do this comparison by average power of good and bad ones; an average bad character has more power than a average good one.')\n",
    "\n",
    "sns.boxplot(x='Alignment',\n",
    "           y='Total',\n",
    "           data=charcters_stats);"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "_uuid": "1b9d2902832158fc2c5b0d88441dd1a2f9d5e319"
   },
   "outputs": [],
   "source": [
    "# e-02\n",
    "# question:   Which alignment (good\\bad) has higher avg speed?\n",
    "# difficulty: easy\n",
    "# datasets: charcters_stats\n",
    "\n",
    "##Create a filter to find only good and bad ones\n",
    "filterGoodAndBad=charcters_stats['Alignment'].isin(['good','bad'])\n",
    "\n",
    "##Calculate average speed and find the fastest group.\n",
    "SpeedChampionGroup=charcters_stats[filterGoodAndBad].groupby(by='Alignment').mean()['Speed'].idxmax()\n",
    "print('Average speed is higher in {} ones'.format(SpeedChampionGroup))\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "_uuid": "7ec14b1517e3cdc00e8d6138d59f8f8bd00224a9"
   },
   "outputs": [],
   "source": [
    "# e-03\n",
    "# question: How many superheros have more Intelligence then strength?\n",
    "# difficulty: easy\n",
    "# datasets: charcters_stats\n",
    "\n",
    "## Create a filter to find the smarter heroes.\n",
    "SmarterHeroesFilter=charcters_stats['Intelligence']>charcters_stats['Strength']\n",
    "SmarterHeroes=charcters_stats[SmarterHeroesFilter]['Name']\n",
    "print('There are {} heroes have more Intelligence then strength'.format(SmarterHeroes.size))\n",
    "print('Here are some of them:')\n",
    "SmarterHeroes.head()\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "_uuid": "2ba42883d4b6d877a6ccc4e5ba85309771ea1bfc"
   },
   "outputs": [],
   "source": [
    "# e-04\n",
    "# question: Show the distribution of Total (all the powers combined) for the good and the evil.\n",
    "# difficulty: easy\n",
    "# datasets: charcters_stats\n",
    "\n",
    "#Creating two filters to find good and bad characters.\n",
    "filterGood=charcters_stats['Alignment'].isin(['good'])\n",
    "filterBad=charcters_stats['Alignment'].isin(['bad'])\n",
    "\n",
    "#Creating a figure for 2 plots\n",
    "f,(ax1,ax2)=plt.subplots(2,1,figsize=(7,7), sharex=True, sharey=True)\n",
    "\n",
    "#Plot the data and show the distribution\n",
    "sns.distplot(charcters_stats[filterGood]['Total'],ax=ax1)\n",
    "ax1.set_ylabel(\"Good\")\n",
    "sns.distplot(charcters_stats[filterBad]['Total'],ax=ax2)\n",
    "ax2.set_ylabel(\"Bad\")"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "_uuid": "8f98c020ce05efb6f6e11c0b73f1af2052535754"
   },
   "outputs": [],
   "source": [
    "# e-05\n",
    "# question: How many comics with 7 or more character published each year? (show on graph)\n",
    "# difficulty: easy\n",
    "\n",
    "## Creating a filter to find the comics with least 8 characters in \n",
    "comicsWith7orMoreCharacterFilter=charactersToComics.groupby(by='comicID').count()['characterID']>7\n",
    "## Applying the filter and find comics with 7+ characters in it\n",
    "comicsWith7orMoreCharacterData=charactersToComics.groupby(by='comicID').count()[comicsWith7orMoreCharacterFilter]\n",
    "\n",
    "##Finding year of the comics\n",
    "### First, creating a function to extract the year from comics title column\n",
    "def extractYear(txt):\n",
    "    startChr=txt.find('(')+1\n",
    "    endChr=txt.find(')')\n",
    "    try:\n",
    "        result=int(txt[startChr:endChr])\n",
    "        return result\n",
    "    except:\n",
    "        pass\n",
    "    \n",
    "###Creating a copy of comics data frame\n",
    "comicsWyear=comics.copy()\n",
    "\n",
    "###applying extractYear function and creating a new column that contains year of the comics\n",
    "comicsWyear['year']=comics['title'].apply(extractYear)\n",
    "\n",
    "### creating a bar chart\n",
    "comicsWyear.groupby(by='year').count()['comicID'].plot(kind='bar',figsize=(18,12));\n",
    "\n",
    "del comicsWyear"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {
    "_uuid": "a08db645e81623174cceb1dc0c8d461fa38b5ac9"
   },
   "source": [
    "The rest of the easy questions are at the bottom with other type of questions.\n",
    "You skip them and move to medium ones."
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {
    "_uuid": "0351cffef63ef3a60f7d8bc25914784a199c8b3c"
   },
   "source": [
    "**Medium Questions**"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "_uuid": "f901b39db3c3e68541ef94fb19291f4a89dac3f4"
   },
   "outputs": [],
   "source": [
    "# m-01\n",
    "# question: Show 5 top comics with top participants on a plot bar.\n",
    "# difficulty: medium\n",
    "# datasets: comics,characters,comics_to_characters\n",
    "\n",
    "\n",
    "## First, find number of participants in comics\n",
    "comicsAndNumberOfParticipants=charactersToComics.groupby(by='comicID').count().sort_values(by='characterID',ascending=False)\n",
    "##Find top 5 comics with most participants\n",
    "comicsWithMostParticipants=pd.DataFrame(comicsAndNumberOfParticipants.head(5))\n",
    "comicsWithMostParticipants.columns=['NumberOfParticipants']\n",
    "\n",
    "##Use comics dataframe to find comics' names\n",
    "comicsWithMostParticipantsAndNames=pd.merge(left=comicsWithMostParticipants,\n",
    "                                    right=comics,\n",
    "                                    on='comicID'\n",
    "                                   );\n",
    "##plot it\n",
    "sns.barplot(y='title',\n",
    "           x='NumberOfParticipants',\n",
    "           data=comicsWithMostParticipantsAndNames\n",
    "           );\n",
    "\n",
    "# delete temporary objects\n",
    "del [comicsAndNumberOfParticipants,comicsWithMostParticipants,comicsWithMostParticipantsAndNames]"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "_uuid": "c67905012c14c196855729a68839cdd0e1fa13ac"
   },
   "outputs": [],
   "source": [
    "# m-02\n",
    "# question: Unmatched rivals - show for each super hero the number of vilans that stronger then him/her\n",
    "# difficulty: medium-hard\n",
    "# datasets: charcters_stats.csv\n",
    "\n",
    "#First create hero and villain dataframe by using Alignment feature\n",
    "heroFilter=charcters_stats['Alignment'].isin(['good'])\n",
    "heroesData=charcters_stats[heroFilter][['Name','Total']]\n",
    "\n",
    "villainFilter=charcters_stats['Alignment'].isin(['bad'])\n",
    "villainData=charcters_stats[villainFilter][['Name','Total']]\n",
    "\n",
    "#You will need a dummy column for merging dataframes\n",
    "heroesData['key']=0\n",
    "villainData['key']=0\n",
    "\n",
    "#You merge dataframes on dummy columns, key. It is not an efficient way.\n",
    "heroesVillainDataXJoined=heroesData.merge(villainData,how='outer',left_on=['key'],right_on=['key'])\n",
    "\n",
    "#You create a filter to find stronger ones\n",
    "findStrongerOnesFilter=heroesVillainDataXJoined['Total_y']>heroesVillainDataXJoined['Total_x']\n",
    "findStrongerOnes=heroesVillainDataXJoined[findStrongerOnesFilter]\n",
    "\n",
    "#You count and list the number of villains that are stronger than a hero.\n",
    "NumberStrongerOnes=findStrongerOnes.groupby('Name_x').count()['Name_y']\n",
    "print(NumberStrongerOnes.head(15))\n",
    "\n",
    "# delete temporary objects\n",
    "del [heroFilter,heroesData,villainFilter,villainData,heroesVillainDataXJoined,findStrongerOnesFilter,findStrongerOnes]\n"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {
    "_uuid": "9137b2ac0e1884145846dac3704901ed7c73179c",
    "collapsed": true
   },
   "source": [
    "There are more questions in medium difficulty at the bottom."
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {
    "_uuid": "d0722af015a5d40eb9af4c8fe12a5368f525c238"
   },
   "source": [
    "**Hard Questions**"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "_uuid": "7e7828de1240bf66f8d12a91d0e6a2dec646d6bd"
   },
   "outputs": [],
   "source": [
    "# h-01\n",
    "# question: Show pairs of characters that always appear together. rank them by number of appearances\n",
    "# difficulty: hard - very hard\n",
    "# datasets: comics,characters,comics_to_characters\n",
    "\n",
    "#Characters to comics data's used.\n",
    "#You merge characters to comics data so you can calculate how many times are pair of characters together.\n",
    "\n",
    "dataTemp1=pd.merge(left=charactersToComics,\n",
    "                    right=charactersToComics,\n",
    "                    how='inner',\n",
    "                    on='comicID')\n",
    "\n",
    "#After merging, characters are matched with other characters and their own. You delete rows which characters matched with themselves.\n",
    "eliminateEqualOnesFilter=dataTemp1['characterID_x']!=dataTemp1['characterID_y']\n",
    "dataTemp2=dataTemp1[eliminateEqualOnesFilter]\n",
    "\n",
    "#Grouping the data by characters and counting that how many comics that they are together in\n",
    "dataTemp3=dataTemp2.groupby(by=['characterID_x','characterID_y']).count()\n",
    "\n",
    "#Finding characters that are together the most\n",
    "dataTemp5=dataTemp3.idxmax().get_values()\n",
    "numberOfTimesTheyAreTogether=dataTemp3['comicID'].max()\n",
    "\n",
    "# Using characters data for character's name\n",
    "characterId_1=dataTemp5[0][0]\n",
    "characterName_1=characters['name'][characters['characterID'].isin([characterId_1])].get_values()[0]\n",
    "\n",
    "characterId_2=dataTemp5[0][1]\n",
    "characterName_2=characters[characters['characterID'].isin([characterId_2])]['name'].get_values()[0]\n",
    "print('Most frequent pair of characters are {} and {}. There are in same comics {} of times.'.format(characterName_1,characterName_2,numberOfTimesTheyAreTogether))\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "_uuid": "1007c8f3fc754bc69cc8e792c587ff226a1b7f45"
   },
   "outputs": [],
   "source": [
    "# h-02\n",
    "# question: Unmatched rivals - show for each super hero , all the names of the  vilans that stronger then him/her\n",
    "# difficulty: hard\n",
    "# datasets: charcters_stats.csv\n",
    "\n",
    "# BONUS\n",
    "# question: Unmatched rivals - find an informative way to visualize the results you got\n",
    "\n",
    "# This question is same as m-02. But this time you try to visualize the result.\n",
    "#First creating 2 dataframes, hero and villain, by using Alignment feature, like you did in m-02.\n",
    "heroFilter=charcters_stats['Alignment'].isin(['good'])\n",
    "heroesData=charcters_stats[heroFilter][['Name','Total']]\n",
    "villainFilter=charcters_stats['Alignment'].isin(['bad'])\n",
    "villainData=charcters_stats[villainFilter][['Name','Total']]\n",
    "\n",
    "#You need a dummy column for merging dataframes\n",
    "heroesData['key']=0\n",
    "villainData['key']=0\n",
    "\n",
    "#Merging dataframes on dummy columns, key. Maybe it is not the efficient way.\n",
    "heroesVillainDataXJoined=heroesData.merge(villainData,how='outer',left_on=['key'],right_on=['key'])\n",
    "\n",
    "#Creating two columns that represents the difference of the powers if the difference is greater than 0.\n",
    "heroesVillainDataXJoined['PowerDifference']=heroesVillainDataXJoined['Total_y']-heroesVillainDataXJoined['Total_x']\n",
    "\n",
    "#Keep only columns you need\n",
    "heroesVillainDataXJoined=heroesVillainDataXJoined[['Name_x','Name_y','PowerDifference']]\n",
    "#Checking if there is any duplicate value.If any,the first one is kept and rest is dropped. You get an error while you visualize them If duplicate value exists. \n",
    "heroesVillainDataXJoined=heroesVillainDataXJoined.drop_duplicates(subset=['Name_x','Name_y'],keep='first')\n",
    "heroesVillainDataXJoined['IsVillainStronger']=heroesVillainDataXJoined['PowerDifference']<0\n",
    "#Creating 2 heat maps that represents how much stronger that villains are than each heroes.\n",
    "##First, heat map without any filter\n",
    "heatmap_data=heroesVillainDataXJoined.pivot('Name_x','Name_y','PowerDifference')\n",
    "fig=plt.figure()\n",
    "fig.set_figheight(40)\n",
    "fig.set_figwidth(30)\n",
    "\n",
    "ax1=fig.add_subplot(2,1,1)\n",
    "sns.heatmap(heatmap_data,\n",
    "            ax=ax1,\n",
    "           cmap='bwr')\n",
    "ax1.set_title('Heatmap without any filter')\n",
    "\n",
    "##Heat map with the stronger filter\n",
    "heatmap_filter=heroesVillainDataXJoined.pivot('Name_x','Name_y','IsVillainStronger')\n",
    "\n",
    "ax2=fig.add_subplot(2,1,2)\n",
    "sns.heatmap(heatmap_data,\n",
    "            mask=heatmap_filter,\n",
    "            vmin=0,\n",
    "            cmap='Reds',\n",
    "            ax=ax2)\n",
    "ax2.set_title('Heatmap with the filter')"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {
    "_uuid": "513726d544e1cf3f0760ed8be96407530b02e621"
   },
   "source": [
    "There are more hard questions at the bottom with other questions.\n",
    "Thank you..."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "_uuid": "86b3ccf8dca16ed504afe8b987454c7a8d391321"
   },
   "outputs": [],
   "source": [
    "# e-06\n",
    "# question: How has more characters DC or Marvel?\n",
    "# difficulty: easy\n",
    "# datasets: comics,characters,comics_to_characters\n",
    "# e-07\n",
    "# question: Who has higher representation of female heros DC or Marvel?\n",
    "# difficulty: easy\n",
    "# datasets: marvel_dc_characters\n",
    "# e-08\n",
    "# question: Who has higher representation of black skined heros DC or Marvel?\n",
    "# difficulty: easy\n",
    "# datasets: marvel_dc_characters\n",
    "# e-09\n",
    "# question: Show how common is each trait in 'superheroes_power_matrix.csv'.\n",
    "# difficulty: easy\n",
    "# datasets: superheroes_power_matrix\n",
    "# e-10\n",
    "# question: Show the hight distrebution for the characters of 'Marvel Comics' (from 'marvel_characters_info.csv').\n",
    "# difficulty: easy\n",
    "# datasets: \n",
    "# e-11\n",
    "# question: Show the distrebution of apperences.\n",
    "# difficulty: easy\n",
    "# datasets: marvel_dc_characters.csv\n",
    "# e-12\n",
    "# question: Show the distrebution of eye colors.\n",
    "# difficulty: easy\n",
    "# datasets: marvel_dc_characters.csv\n",
    "# e-13\n",
    "# question: How many characters apperred only once?\n",
    "# difficulty: easy\n",
    "# datasets: marvel_dc_characters.csv\n",
    "# e-14\n",
    "# question: How many characters died in thair first apperance (have one apperance and are deceased)?\n",
    "# difficulty: easy\n",
    "# datasets: marvel_dc_characters.csv\n",
    "# e-15\n",
    "# question:   Display a pie chart of the 10 most common hair styles\n",
    "# difficulty: easy\n",
    "# datasets: marvel_characters_info\n",
    "\n",
    "\n",
    "# e-16\n",
    "# question: Display the average height\n",
    "# difficulty: easy\n",
    "# datasets: marvel_characters_info\n",
    "\n",
    "# e-17\n",
    "# find the comic with most characters. display the comics name and the name of the characters\n",
    "# difficulty: easy\n",
    "\n",
    "# e-18\n",
    "# the oldest character of both universes\n",
    "# difficulty: easy\n",
    "\n",
    "# e-19\n",
    "# we want to build the master group to fight evil, kind of an avengers 2.0, but only better,\n",
    "# lets select the captain, the one with the most total stats  (obviously his Alignment must be good to fight evil)\n",
    "# level: easy\n",
    "\n",
    "# e-20\n",
    "# People will pay big money for original vintage comic books, retrive all first issue comic books\n",
    "# level: easy\n",
    "# datasets: comics_characters\n",
    "\n",
    "# e-21\n",
    "# On the other hand, long lasting series are great as well :), retrive the comic book with the biggest issue number\n",
    "#level: easy\n",
    "# datasets: comics_characters\n",
    "\n",
    "\n",
    "# e-22\n",
    "# It's the holiday season, and to celebrate marvel usually comes out with holiday special comic books, \n",
    "# retrive all  holiday special comic books (the word 'Holiday' will appeer in the title)\n",
    "# level: easy\n",
    "\n",
    "# e-23\n",
    "# What's the mean intelligence of the superheroes who have a 'True' value in the power matrix and the same for the superheroes who have a 'False' value?\n",
    "# difficulty: easy\n",
    "\n",
    "\n",
    "#Medium Questions\n",
    "\n",
    "# m-03\n",
    "# question: Weak point - for each vilan, show his weakest characteristic.\n",
    "# difficulty: medium\n",
    "# datasets: charcters_stats\n",
    "# m-04\n",
    "# question: Who can beat me? - for each vilan, show how many superheros can defeat them (compare by total score)\n",
    "# difficulty: medium\n",
    "# datasets: charcters_stats\n",
    "# m-05\n",
    "# question: Display box plot summarizing the next statistics:\n",
    "# Height, Weight, Intelligence, Strength, Speed, Durability, Power, Combat\n",
    "# difficulty: medium\n",
    "# datasets: marvel_characters_info, characters_stats\n",
    "# m-06\n",
    "# after you found it display the characters the participate it in\n",
    "# difficulty: medium\n",
    "\n",
    "# m-07\n",
    "# A great team needs great diversity, and to be great at everything, get the best hero at each statistical category\n",
    "# level: easy - medium\n",
    "\n",
    "# m-08\n",
    "# Is your strngth and intelligence related?.\n",
    "# Show a scatter chart where the x axis is stength, and the y axis is intelligence, scatter heros and villans as two different color dots\n",
    "# level: easy - medium\n",
    "\n",
    "\n",
    "# m-09\n",
    "# To truly be a great superhero, you can't be a one trick pony, you need to posess multipule abilities. Create a series of every superhero and how many different abilities they posess, in descending order\n",
    "# level: medium\n",
    "\n",
    "# m-10\n",
    "# Create a serires that counts the number of comic book appeerences for each hero\n",
    "# Bonus: show the top 10 heros in a pie chart\n",
    "\n",
    "#level: easy - medium\n",
    "\n",
    "\n",
    "# m-11\n",
    "# Pick any hero from the previous question and list all the comic book titles that he appeared in\n",
    "#level: medium\n",
    "\n",
    "\n",
    "# m-12\n",
    "# It's the holiday season once again, since we already have a list of all holiday comics, \n",
    "# retrive all heros who have participated in a holiday comic book\n",
    "\n",
    "# level: easy - medium\n",
    "\n",
    "\n",
    "# h-03\n",
    "#find the heroes that has the maximum amount of abilities(more True values than anyone)\n",
    "# difficulty: hard (not that hard)\n",
    "\n",
    "# h-04\n",
    "# Two of the most iconic marvel superheros, Iron Man and Captain America, appeer together quite offten. \n",
    "# see if you can get the ammount of comic books they both appear in\n",
    "\n",
    "# level: medium - hard\n",
    "\n",
    "\n",
    "# h-05\n",
    "# Now that we know how many comic books both of those guys have appeared together at, are they the best power duo in the marvel universe?.\n",
    "# craete a series with a multi index of 2 superheros(name1,name2) and count for each of them the ammount of comic books they have been in together in, order by that ammount in a descending order\n",
    "\n",
    "# level: really hard :)\n"
   ]
  }
 ],
 "metadata": {
  "kernelspec": {
   "display_name": "Python 3",
   "language": "python",
   "name": "python3"
  },
  "language_info": {
   "codemirror_mode": {
    "name": "ipython",
    "version": 3
   },
   "file_extension": ".py",
   "mimetype": "text/x-python",
   "name": "python",
   "nbconvert_exporter": "python",
   "pygments_lexer": "ipython3",
   "version": "3.7.4"
  }
 },
 "nbformat": 4,
 "nbformat_minor": 4
}