DS_Course0_Week1_Module5_DataPy.ipynb

{
 "cells": [
  {
   "cell_type": "markdown",
   "id": "375ee24e",
   "metadata": {},
   "source": [
    "# Data Analysis with Pandas - Lab"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "585bf8f9",
   "metadata": {},
   "source": [
    "## Introduction\n",
    "\n",
    "In this lab, you will make use of everything we have learned about pandas: data cleaning, reshaping, and joining. \n",
    "\n",
    "In order to complete this lab, you’ll have to import, clean, combine, reshape, and visualize data to answer questions provided, as well as your own questions!"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "08e4facc",
   "metadata": {
    "ExecuteTime": {
     "end_time": "2024-07-09T17:53:25.839571Z",
     "start_time": "2024-07-09T17:53:25.826201Z"
    }
   },
   "source": [
    "## Objectives\n",
    "\n",
    "You will be able to:\n",
    "- Open and inspect the contents of CSVs using pandas DataFrames\n",
    "- Identify and handle missing values\n",
    "- Identify and handle invalid values\n",
    "- Clean text data by removing whitespace and fix typos\n",
    "- Join multiple pandas DataFrames\n",
    "- Aggregate data by groups\n",
    "- Create visualizations\n",
    "- Use visualizations and summary statistics to answer business questions."
   ]
  },
  {
   "cell_type": "markdown",
   "id": "d2f82f62",
   "metadata": {},
   "source": [
    "### Data Understanding\n",
    "\n",
    "You will be working with a version of the comprehensive Superheroes Dataset, which can be found on Kaggle and was originally scrapped from SuperHeroDb. We have modified the structure and contents of the dataset somewhat for the purposes of this lab. \n",
    "\n",
    "The data is contained in two separate CSV files:\n",
    "1. `heroes_information.csv`: each record represents a superhero, with attributes of that superhero (e.g. eye\n",
    "color). Height is measured in centimeters, and weight is measured in pounds.\n",
    "2. `super_hero_powers.csv`: each record represents a superpower, then has True/False values representing\n",
    "whether each superhero has that power"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "b1e03397",
   "metadata": {},
   "source": [
    "### Business Understanding\n",
    "\n",
    "The business questions you have been provided are:\n",
    "1. What is the distribution of superheroes by publisher?\n",
    "2. What is the relationship between height and number of superpowers? And does this differ based on gender?\n",
    "3. What are the 5 most common superpowers in Marvel Comics vs. DC Comics?\n",
    "\n",
    "*This lab also simulates something you are likely to encounter at some point or another in your career in data science: someone has given you access to a dataset, as well as a few questions, and has told you to find something interesting.*"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "1bb9fc93",
   "metadata": {},
   "source": [
    "## Section 1"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "a58a098f",
   "metadata": {},
   "source": [
    "### Load the Data with Pandas"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "e2e25490",
   "metadata": {},
   "source": [
    "Create DataFrames `heroes_df` and `powers_df` that represent the two CSV files. Use pandas methods to inspect the shape and other attributes of these DataFrames."
   ]
  },
  {
   "cell_type": "markdown",
   "id": "9a131ab3",
   "metadata": {},
   "source": [
    "### Superheroes"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "65751e1b",
   "metadata": {
    "ExecuteTime": {
     "end_time": "2024-07-10T21:27:16.487286Z",
     "start_time": "2024-07-10T21:27:16.005168Z"
    }
   },
   "outputs": [],
   "source": [
    "# CodeGrade step0\n",
    "# Run this cell without any changes\n",
    "\n",
    "# Imports\n",
    "import pandas as pd\n",
    "import numpy as np\n",
    "import matplotlib.pyplot as plt"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "2af2143c",
   "metadata": {},
   "source": [
    "### Step 1\n",
    "\n",
    "Now you want to get familiar with the data. This step includes:\n",
    "- Import your dataset\n",
    "- Understanding the dimensionality of your dataset\n",
    "- Investigating what type of data it contains, and the data types used to store it\n",
    "- Discovering how missing values are encoded, and how many there are\n",
    "\n",
    "In the cell below, inspect the overall shape of the dataframe:\n",
    "- Set the `index_col=0` argument to use the first column as the index"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "7ae37660",
   "metadata": {
    "ExecuteTime": {
     "end_time": "2024-07-10T21:27:19.157157Z",
     "start_time": "2024-07-10T21:27:19.148656Z"
    }
   },
   "outputs": [],
   "source": [
    "# CodeGrade step1\n",
    "# Replace None with your code\n",
    "\n",
    "# Load the data as heroes_df\n",
    "heroes_df = None\n",
    "\n",
    "# Check the shape\n",
    "heroes_shape = None\n",
    "heroes_shape"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "40918a44",
   "metadata": {},
   "source": [
    "Now let's look at the info printout:"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "9060df39",
   "metadata": {},
   "outputs": [],
   "source": [
    "# Run this cell without any changes\n",
    "\n",
    "heroes_df.info()"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "be97aff7",
   "metadata": {},
   "source": [
    "Do the data types line up with what we expect? Are there any missing values? All good things to think about which we might need to handle in Section 2."
   ]
  },
  {
   "cell_type": "markdown",
   "id": "73c94750",
   "metadata": {},
   "source": [
    "## Superpowers"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "68134364",
   "metadata": {},
   "source": [
    "### Step 2\n",
    "Now, repeat the same process with `super_hero_powers.csv`. Name the DataFrame `powers_df`. This time, make sure you use `index_col=0` when opening the CSV because the index contains important information."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "c500e119",
   "metadata": {
    "ExecuteTime": {
     "end_time": "2024-07-10T21:27:22.835560Z",
     "start_time": "2024-07-10T21:27:22.828025Z"
    }
   },
   "outputs": [],
   "source": [
    "# CodeGrade step2\n",
    "# Replace None with appropriate code\n",
    "\n",
    "# load super_hero_powers.csv as powers_df\n",
    "powers_df = None\n",
    "\n",
    "# check the shape\n",
    "powers_shape = None\n",
    "powers_shape"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "513d82b2",
   "metadata": {},
   "outputs": [],
   "source": [
    "# Run this cell without changes\n",
    "# Check the info printout\n",
    "powers_df.info()"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "7d0aa863",
   "metadata": {},
   "source": [
    "## Section 2"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "fff6aae3",
   "metadata": {},
   "source": [
    "### Perform Data Cleaning Required to Answer First Question\n",
    "\n",
    "Recall that the first question is: *What is the distribution of superheroes by publisher?*\n",
    "\n",
    "To answer this question, we will only need to use `heroes_df`, which contains the Publisher column."
   ]
  },
  {
   "cell_type": "markdown",
   "id": "74e38ecc",
   "metadata": {},
   "source": [
    "### Identifying and Handling Missing Values\n",
    "\n",
    "As you likely noted above, the Publisher column is missing some values.\n",
    "\n",
    "Recall that there are two general strategies for dealing with missing values:\n",
    "1. Fill in missing values (either using another value from the column, e.g. the mean or mode, or using some other value like \"Unknown\")\n",
    "2. Drop rows with missing values"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "a86f2ab0",
   "metadata": {},
   "source": [
    "### Step 3\n",
    "Here, implement the strategy to drop rows with missing values using code. Only drop rows that are missing information from the `Publisher` column. Save over `heroes_df` with the new dataframe that has less rows."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "0d4e9be6",
   "metadata": {
    "ExecuteTime": {
     "end_time": "2024-07-10T21:27:25.178385Z",
     "start_time": "2024-07-10T21:27:25.171610Z"
    }
   },
   "outputs": [],
   "source": [
    "# CodeGrade step3\n",
    "# Replace None with appropriate code\n",
    "\n",
    "# Drop rows with missing values\n",
    "heroes_df = None"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "d24a7f96",
   "metadata": {},
   "source": [
    "Now there should be no missing values in the publisher column. Let's confirm by checking the info printout:"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "9c740c36",
   "metadata": {
    "ExecuteTime": {
     "end_time": "2024-07-10T21:27:27.649946Z",
     "start_time": "2024-07-10T21:27:27.646920Z"
    }
   },
   "outputs": [],
   "source": [
    "# Run this cell without changes\n",
    "# Check the info printout\n",
    "heroes_df.info()"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "7391e881",
   "metadata": {},
   "source": [
    "### Identifying and Handling Text Data Requiring Cleaning\n",
    "\n",
    "The overall field of natural language processing (NLP) is quite broad, and we're not going to get into any advanced text processing, but it's useful to be able to clean up minor issues in text data.\n",
    "\n",
    "### Step 4\n",
    "\n",
    "Let's take a look at the counts of heroes grouped by publisher by calling the value counts of the column:"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "8fcb2ca0",
   "metadata": {
    "ExecuteTime": {
     "end_time": "2024-07-10T21:27:28.715812Z",
     "start_time": "2024-07-10T21:27:28.710448Z"
    }
   },
   "outputs": [],
   "source": [
    "# CodeGrade step4\n",
    "# Replace None with appropriate code\n",
    "\n",
    "# Call the value counts of the Publisher column\n",
    "pub_counts = None\n",
    "pub_counts"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "04fab091",
   "metadata": {},
   "source": [
    "There are two cases where we appear to have data entry issues, and publishers that should be encoded the same have not been. In other words, there are four categories present that really should be counted as two categories and you do not need specific comic book knowledge to be able to identify them.\n",
    "\n",
    "HINT: It involves the two biggest Publishers"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "8d4450c7",
   "metadata": {},
   "source": [
    "### Step 5\n",
    "Identify those two cases and write some code to handle them. If you're not sure where to start, look at the pandas documentation for [replacing values](https://pandas.pydata.org/pandas-docs/stable/reference/api/pandas.Series.replace.html) and [stripping off whitespace](https://pandas.pydata.org/pandas-docs/stable/reference/api/pandas.Series.str.strip.html). You can accomplish this in either one or two lines of code, two have been provided, delete the second if you do it in one line.\n",
    "\n",
    "- Please make sure to keep \"Comics\" as a part of both names"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "b54d5e81",
   "metadata": {
    "ExecuteTime": {
     "end_time": "2024-07-10T21:27:30.408078Z",
     "start_time": "2024-07-10T21:27:30.400515Z"
    }
   },
   "outputs": [],
   "source": [
    "# CodeGrade step5\n",
    "# Replace None with appropriate code\n",
    "\n",
    "# Handle data entry issues\n",
    "heroes_df['Publisher'] = None\n",
    "heroes_df['Publisher'] = None\n",
    "\n",
    "#check the Publisher value counts\n",
    "correct_counts = None\n",
    "correct_counts"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "9f7d932d",
   "metadata": {
    "ExecuteTime": {
     "end_time": "2024-07-09T19:18:32.720202Z",
     "start_time": "2024-07-09T19:18:32.706665Z"
    }
   },
   "source": [
    "### Answering the Question\n",
    "\n",
    "Now we should be able to answer \"*What is the distribution of superheroes by publisher?*\"\n",
    "If your data cleaning was done correctly, this code should work without any further changes:\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "3f07ee00",
   "metadata": {
    "ExecuteTime": {
     "end_time": "2024-07-10T21:13:13.146579Z",
     "start_time": "2024-07-10T21:13:12.924613Z"
    }
   },
   "outputs": [],
   "source": [
    "# Run this cell with no changes\n",
    "# Set up plots\n",
    "fig, (ax1, ax2) = plt.subplots(ncols=2, figsize=(16, 5))\n",
    "\n",
    "# Create variables for easier reuse\n",
    "value_counts = heroes_df[\"Publisher\"].value_counts()\n",
    "top_5_counts = value_counts.iloc[:5]\n",
    "\n",
    "# Plot data\n",
    "ax1.bar(value_counts.index, value_counts.values)\n",
    "ax2.bar(top_5_counts.index, top_5_counts.values)\n",
    "\n",
    "# Customize appearance\n",
    "ax1.tick_params(axis=\"x\", labelrotation=90)\n",
    "ax2.tick_params(axis=\"x\", labelrotation=45)\n",
    "ax1.set_ylabel(\"Count of Superheroes\")\n",
    "ax2.set_ylabel(\"Count of Superheroes\")\n",
    "ax1.set_title(\"Distribution of Superheroes by Publisher\")\n",
    "ax2.set_title(\"Top 5 Publishers by Count of Superheroes\");"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "bf66add2",
   "metadata": {},
   "source": [
    "## Section 3"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "50b63bec",
   "metadata": {},
   "source": [
    "### Perform Data Aggregation and Cleaning Required to Answer Second Question\n",
    "\n",
    "Recall that the second question is: *What is the relationship between height and number of superpowers? And does this differ based on gender?*\n",
    "\n",
    "Unlike the previous question, we won't be able to answer this with just `heroes_df`, since information about height is contained in `heroes_df`, while information about superpowers is contained in `powers_df`.\n",
    "\n",
    "We will need to join these DataFrames to address these questions."
   ]
  },
  {
   "cell_type": "markdown",
   "id": "531a98b6",
   "metadata": {},
   "source": [
    "### Joining the DataFrames Together\n",
    "\n",
    "We need to reshape the `powers_df` DataFrame in order for it to be merged with the `heroes_df` DataFrame using a shared key. (Shared key meaning, the values you want to join on.) Let's look at them again.\n",
    "\n",
    "### Step 6\n",
    "\n",
    "First, get a transposed version of the powers DataFrame. This means\n",
    "that what used to be the row index is now the column name, and vice versa:"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "6b3f5893",
   "metadata": {
    "ExecuteTime": {
     "end_time": "2024-07-10T21:27:36.261852Z",
     "start_time": "2024-07-10T21:27:36.257148Z"
    }
   },
   "outputs": [],
   "source": [
    "# CodeGrade step6\n",
    "# Replace None with appropriate code\n",
    "\n",
    "# Transpose the powers_df DataFrame\n",
    "powers_df_transposed = None\n",
    "powers_df_transposed.head()"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "17429427",
   "metadata": {},
   "source": [
    "### Step 7\n",
    "\n",
    "Next name the index of `powers_df_transposed` \"name\" and then reset the index so that the DataFrame can be combined with `heroes_df`. This will make the \"name\" column (old index) appear as the first column in the dataframe."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "f7c4a8ea",
   "metadata": {
    "ExecuteTime": {
     "end_time": "2024-07-10T21:27:37.419055Z",
     "start_time": "2024-07-10T21:27:37.413619Z"
    }
   },
   "outputs": [],
   "source": [
    "# CodeGrade step7\n",
    "# Replace None with appropriate code\n",
    "\n",
    "# Change the name of the index to name\n",
    "None\n",
    "\n",
    "# Reset the powers_df_transposed index\n",
    "None\n",
    "\n",
    "# Call the new DataFrame to check work\n",
    "powers_df_transposed.head()"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "d958ee2f",
   "metadata": {},
   "source": [
    "### Step 8\n",
    "Now create the `heroes_and_powers_df` DataFrame by combining the `powers_df_transposed` and `heroes_df` DataFrames using either `.merge` or `.join`.\n",
    "\n",
    "**Hint**: Note that the `.join` method requires that the two DataFrames share an index whereas the `.merge` method can join using any columns. It is up to you which one you want to use."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "c0a1a575",
   "metadata": {
    "ExecuteTime": {
     "end_time": "2024-07-10T21:27:39.404763Z",
     "start_time": "2024-07-10T21:27:39.401311Z"
    }
   },
   "outputs": [],
   "source": [
    "# CodeGrade step8\n",
    "# Replace None with appropriate code\n",
    "\n",
    "# Merge the two DataFrames on name\n",
    "heroes_and_powers_df = None"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "3eb70736",
   "metadata": {},
   "source": [
    "Now that we have created a joined DataFrame, we can aggregate the number of superpowers by superhero. This code is written for you:"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "1fb964d8",
   "metadata": {
    "ExecuteTime": {
     "end_time": "2024-07-10T21:13:13.217237Z",
     "start_time": "2024-07-10T21:13:13.186369Z"
    }
   },
   "outputs": [],
   "source": [
    "# Run this cell with no changes\n",
    "# Note: we can use sum() with True and False values and they will automatically be cast to 1s and 0s\n",
    "heroes_and_powers_df[\"Power Count\"] = sum(\n",
    "    [heroes_and_powers_df[power_name] for power_name in powers_df.index]\n",
    ")\n",
    "# Check changes\n",
    "heroes_and_powers_df"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "e86e5b95",
   "metadata": {
    "ExecuteTime": {
     "end_time": "2024-07-10T15:08:26.493857Z",
     "start_time": "2024-07-10T15:08:26.491389Z"
    }
   },
   "source": [
    "### Answering the Question\n",
    "Now we can plot the height vs. the count of powers:"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "3a17319c",
   "metadata": {
    "ExecuteTime": {
     "end_time": "2024-07-10T21:13:13.339690Z",
     "start_time": "2024-07-10T21:13:13.218199Z"
    }
   },
   "outputs": [],
   "source": [
    "# Run this cell with no changes\n",
    "\n",
    "# Create figure and axis objects\n",
    "fig, ax = plt.subplots(figsize=(16, 8))\n",
    "\n",
    "# Create scatterplot\n",
    "ax.scatter(\n",
    "    x=heroes_and_powers_df[\"Height\"], y=heroes_and_powers_df[\"Power Count\"], alpha=0.3\n",
    ")\n",
    "\n",
    "# Set labels and Title for plot\n",
    "ax.set_xlabel(\"Height (cm)\")\n",
    "ax.set_ylabel(\"Number of Superpowers\")\n",
    "ax.set_title(\"Height vs. Power Count\");"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "293d1eb1",
   "metadata": {},
   "source": [
    "Hmm...what is that stack of values off below zero? What is a \"negative\" height?"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "f203b69c",
   "metadata": {},
   "source": [
    "### Identifying and Handling Invalid values\n",
    "\n",
    "One of the trickier tasks in data cleaning is identifying invalid or impossible values. In these cases, you have to apply your domain knowledge rather than any particular computational technique. For example, if you were looking at data containing dates of past home sales, and one of those dates was 100 years in the future, pandas wouldn't flag that as an issue, but you as a data scientist should be able to identify it.\n",
    "\n",
    "In this case, we are looking at heights, which are 1-dimensional, positive numbers. In theory we could have a very tiny height close to 0 cm because the hero is microscopic, but it does not make sense that we would have a height below zero.\n",
    "\n",
    "Let's take a look at a sample of those negative heights:"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "5832265d",
   "metadata": {
    "ExecuteTime": {
     "end_time": "2024-07-10T21:27:51.771474Z",
     "start_time": "2024-07-10T21:27:51.765013Z"
    }
   },
   "outputs": [],
   "source": [
    "# Run this cell without changes\n",
    "\n",
    "# View a sample of the negative heights\n",
    "heroes_and_powers_df[heroes_and_powers_df[\"Height\"] < 0].sample(5, random_state=1)"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "0641a749",
   "metadata": {},
   "source": [
    "It looks like not only are those heights negative, those weights are negative also, and all of them are set to exactly -99.0.\n",
    "\n",
    "It seems like this data source probably filled in -99.0 as the height or weight whenever it was unknown, instead of just leaving it as NaN.\n",
    "\n",
    "### Step 9\n",
    "\n",
    "Depending on the purpose of the analysis, maybe this would be a useful piece of information, but for our current question, let's go ahead and drop the records where the height is -99.0. We'll make a new temporary DataFrame to make sure we don't accidentally delete anything that will be needed in a future question."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "89c1da37",
   "metadata": {
    "ExecuteTime": {
     "end_time": "2024-07-10T21:27:54.534960Z",
     "start_time": "2024-07-10T21:27:54.529406Z"
    }
   },
   "outputs": [],
   "source": [
    "# CodeGrade step9\n",
    "# Replace None with appropriate code\n",
    "\n",
    "# Create a copy of heroes_and_power_df that has no height below 0\n",
    "question_2_df = None\n",
    "\n",
    "# Check new DataFrame copy\n",
    "question_2_df.head()"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "ea43dff8",
   "metadata": {},
   "source": [
    "### Answering the Question, Again"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "b1b3be06",
   "metadata": {},
   "source": [
    "Now we can redo that plot without those negative heights:"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "eaaeb5f7",
   "metadata": {
    "ExecuteTime": {
     "end_time": "2024-07-10T21:13:13.458200Z",
     "start_time": "2024-07-10T21:13:13.376405Z"
    }
   },
   "outputs": [],
   "source": [
    "# Run this cell with no changes\n",
    "\n",
    "# create figure and axis objects\n",
    "fig, ax = plt.subplots(figsize=(16, 8))\n",
    "\n",
    "# create scatterplot\n",
    "ax.scatter(x=question_2_df[\"Height\"], y=question_2_df[\"Power Count\"], alpha=0.3)\n",
    "\n",
    "# Set labels and Title for plot\n",
    "ax.set_xlabel(\"Height (cm)\")\n",
    "ax.set_ylabel(\"Number of Superpowers\")\n",
    "ax.set_title(\"Height vs. Power Count\");"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "6c6e7fa6",
   "metadata": {},
   "source": [
    "Ok, that makes more sense. It looks like there is not much of a relationship between height and number of superpowers.\n",
    "\n",
    "Now we can go on to answering the second half of question 2: *And does this differ based on gender?*\n",
    "\n",
    "To indicate multiple categories within a scatter plot, we can use color to add a third dimension:"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "06456954",
   "metadata": {
    "ExecuteTime": {
     "end_time": "2024-07-10T21:13:13.605741Z",
     "start_time": "2024-07-10T21:13:13.459332Z"
    }
   },
   "outputs": [],
   "source": [
    "# Run this cell with no changes\n",
    "fig, ax = plt.subplots(figsize=(16, 8))\n",
    "\n",
    "# Select subsets\n",
    "question_2_male = question_2_df[question_2_df[\"Gender\"] == \"Male\"]\n",
    "question_2_female = question_2_df[question_2_df[\"Gender\"] == \"Female\"]\n",
    "question_2_other = question_2_df[\n",
    "    (question_2_df[\"Gender\"] != \"Male\") & (question_2_df[\"Gender\"] != \"Female\")\n",
    "]\n",
    "\n",
    "# Plot data with different colors\n",
    "ax.scatter(\n",
    "    x=question_2_male[\"Height\"],\n",
    "    y=question_2_male[\"Power Count\"],\n",
    "    alpha=0.5,\n",
    "    color=\"cyan\",\n",
    "    label=\"Male\",\n",
    ")\n",
    "ax.scatter(\n",
    "    x=question_2_female[\"Height\"],\n",
    "    y=question_2_female[\"Power Count\"],\n",
    "    alpha=0.5,\n",
    "    color=\"gray\",\n",
    "    label=\"Female\",\n",
    ")\n",
    "ax.scatter(\n",
    "    x=question_2_other[\"Height\"],\n",
    "    y=question_2_other[\"Power Count\"],\n",
    "    alpha=0.5,\n",
    "    color=\"yellow\",\n",
    "    label=\"Other\",\n",
    ")\n",
    "\n",
    "# Customize appearance\n",
    "ax.set_xlabel(\"Height (cm)\")\n",
    "ax.set_ylabel(\"Number of Superpowers\")\n",
    "ax.set_title(\"Height vs. Power Count\")\n",
    "ax.legend();"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "7f3bf813",
   "metadata": {},
   "source": [
    "It appears that there is still no clear relationship between count of powers and height, regardless of gender. We do however note that \"Male\" is the most common gender, and that male superheroes tend to be taller, on average."
   ]
  },
  {
   "cell_type": "markdown",
   "id": "adaabded",
   "metadata": {},
   "source": [
    "## Section 4"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "a303a5d2",
   "metadata": {},
   "source": [
    "### Perform Data Aggregation Required to Answer Third Question\n",
    "\n",
    "Recall that the third question is: *What are the 5 most common superpowers in Marvel Comics vs. DC Comics?*\n",
    "\n",
    "We'll need to keep using `heroes_and_powers_df` since we require information from both `heroes_df and powers_df`.\n",
    "\n",
    "### Step 10\n",
    "\n",
    "Your resulting `question_3_df` should contain aggregated data, with columns `Superpower Name`, `Marvel Comics` (containing the count of occurrences in Marvel Comics), and `DC Comics` (containing the count of occurrences in DC Comics). Each row should represent a superpower.\n",
    "\n",
    "In other words, `question_3_df` should look like this:"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "272a25ef",
   "metadata": {
    "ExecuteTime": {
     "end_time": "2024-07-10T21:13:13.610148Z",
     "start_time": "2024-07-10T21:13:13.608757Z"
    }
   },
   "source": [
    "![view of correct dataframe](question_3.png)"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "40f67cb0",
   "metadata": {},
   "source": [
    "Don't worry if the rows or columns are in a different order, all that matters is that you have the right rows and columns with all the data.\n",
    "\n",
    "***Hint***: refer to the [documentation](https://pandas.pydata.org/pandas-docs/stable/reference/api/pandas.DataFrame.groupby.html) for .groupby and treat each publisher as a group."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "59b6709a",
   "metadata": {
    "ExecuteTime": {
     "end_time": "2024-07-10T21:28:01.503672Z",
     "start_time": "2024-07-10T21:28:01.494042Z"
    }
   },
   "outputs": [],
   "source": [
    "# CodeGrade step10\n",
    "# Replace None with appropriate code\n",
    "\n",
    "# Copy of the heroes_and_powers_df named question_3_df to use\n",
    "question_3_df = heroes_and_powers_df.copy()\n",
    "\n",
    "# Drop rows and columns that we know are irrelevant, leave only rows with the relevant publishers\n",
    "question_3_df = None\n",
    "\n",
    "# Only columns needed are the Publisher and Superpower columns\n",
    "# Get all superpower names as a list of strings\n",
    "revelant_columns = None\n",
    "\n",
    "# Add the Publisher column to the list of relevant columns\n",
    "revelant_columns.append('Publisher')\n",
    "\n",
    "# Subset the DataFrame to only the relevant columns\n",
    "question_3_df = None\n",
    "\n",
    "# Check changes by viewing the DataFrame\n",
    "question_3_df.head()"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "11d22ffa",
   "metadata": {},
   "source": [
    "### Step 11\n",
    "\n",
    "Now you would need to group by the Publisher column.\n",
    "\n",
    "Note that grouping requires an associated aggregation, similar to `COUNT` or `SUM` after `GROUP BY` in SQL."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "aad434e6",
   "metadata": {
    "ExecuteTime": {
     "end_time": "2024-07-10T21:28:03.944130Z",
     "start_time": "2024-07-10T21:28:03.936181Z"
    }
   },
   "outputs": [],
   "source": [
    "# CodeGrade step11\n",
    "# Replace None with appropriate code\n",
    "\n",
    "# Calculate the aggregate sum of powers by Publisher (True values are summed as 1, False as 0 by python)\n",
    "question_3_grouped = None\n",
    "\n",
    "# Check dataframe\n",
    "question_3_grouped"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "d87f3565",
   "metadata": {},
   "source": [
    "### Step 12\n",
    "\n",
    "Need to tranpose the result again to get each power as a single row."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "e356fc14",
   "metadata": {
    "ExecuteTime": {
     "end_time": "2024-07-10T21:28:05.384715Z",
     "start_time": "2024-07-10T21:28:05.375551Z"
    }
   },
   "outputs": [],
   "source": [
    "# CodeGrade step12\n",
    "# Replace None with appropriate code\n",
    "\n",
    "# Now transpose so that each record represents a superpower\n",
    "question_3_grouped = None\n",
    "\n",
    "# check changes\n",
    "question_3_grouped"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "5754bc88",
   "metadata": {},
   "source": [
    "### Step 13\n",
    "\n",
    "The last step to match the desired final result of the DataFrame is to change the index name from `Publisher` to `Superpower Name`."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "7c1b8779",
   "metadata": {
    "ExecuteTime": {
     "end_time": "2024-07-10T21:28:06.832135Z",
     "start_time": "2024-07-10T21:28:06.824663Z"
    }
   },
   "outputs": [],
   "source": [
    "# CodeGrade step13\n",
    "# Replace None with appropriate code\n",
    "\n",
    "# Change the name of index to be \"Superpower Name\"\n",
    "None\n",
    "\n",
    "# Reset the index so that \"Superpower Name\" becomes a column\n",
    "None\n",
    "\n",
    "# Check changes\n",
    "question_3_grouped"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "692a95a3",
   "metadata": {},
   "source": [
    "### Answering the Question"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "a4cc006c",
   "metadata": {},
   "source": [
    "The code below uses the DataFrame you created to find and plot the most common superpowers in Marvel Comics and DC Comics."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "2c2278ce",
   "metadata": {
    "ExecuteTime": {
     "end_time": "2024-07-10T21:13:13.656344Z",
     "start_time": "2024-07-10T21:13:13.650873Z"
    },
    "scrolled": true
   },
   "outputs": [],
   "source": [
    "# Run this cell with no changes\n",
    "\n",
    "# Drop DC Comics from DataFrame\n",
    "marvel_most_common = question_3_grouped.drop(\"DC Comics\", axis=1)\n",
    "# sort most commen values from greatest to least\n",
    "marvel_most_common = marvel_most_common.sort_values(\n",
    "    by=\"Marvel Comics\", ascending=False\n",
    ")[:5]\n",
    "\n",
    "# check values\n",
    "marvel_most_common"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "ee79699b",
   "metadata": {
    "ExecuteTime": {
     "end_time": "2024-07-10T21:13:13.663192Z",
     "start_time": "2024-07-10T21:13:13.657765Z"
    }
   },
   "outputs": [],
   "source": [
    "# Run this cell with no changes\n",
    "\n",
    "# drop Marvel Comics from DataFrame\n",
    "dc_most_common = question_3_grouped.drop(\"Marvel Comics\", axis=1)\n",
    "# sort most commen values from greatest to least\n",
    "dc_most_common = dc_most_common.sort_values(by=\"DC Comics\", ascending=False)[:5]\n",
    "\n",
    "# check values\n",
    "dc_most_common"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "55c3ddb9",
   "metadata": {
    "ExecuteTime": {
     "end_time": "2024-07-10T21:13:13.789934Z",
     "start_time": "2024-07-10T21:13:13.664230Z"
    }
   },
   "outputs": [],
   "source": [
    "# Run this cell with no changes\n",
    "\n",
    "# create figure and axis objects\n",
    "fig, (ax1, ax2) = plt.subplots(ncols=2, figsize=(15, 5))\n",
    "\n",
    "# create bar plot for marvel\n",
    "ax1.bar(\n",
    "    x=marvel_most_common[\"Superpower Name\"], height=marvel_most_common[\"Marvel Comics\"]\n",
    ")\n",
    "# creaste bar plot for DC\n",
    "ax2.bar(x=dc_most_common[\"Superpower Name\"], height=dc_most_common[\"DC Comics\"])\n",
    "\n",
    "# create labels for plots\n",
    "ax1.set_ylabel(\"Count of Superheroes\")\n",
    "ax2.set_ylabel(\"Count of Superheroes\")\n",
    "ax1.set_title(\"Frequency of Top Superpowers in Marvel Comics\")\n",
    "ax2.set_title(\"Frequency of Top Superpowers in DC Comics\");"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "fc9ba26a",
   "metadata": {},
   "source": [
    "It looks like super strength is the most popular power in both Marvel Comics and DC Comics. Overall, the top 5 powers are fairly similar — 4 out of 5 overlap, although Marvel contains agility whereas DC contains flight."
   ]
  },
  {
   "cell_type": "markdown",
   "id": "6b698f47",
   "metadata": {},
   "source": [
    "## Section 5"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "ce26ba00",
   "metadata": {
    "ExecuteTime": {
     "end_time": "2024-07-10T15:09:59.534237Z",
     "start_time": "2024-07-10T15:09:59.532722Z"
    }
   },
   "source": [
    "### Formulate and Answer Your Own Question"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "7e548125",
   "metadata": {
    "ExecuteTime": {
     "end_time": "2024-07-10T15:06:42.486699Z",
     "start_time": "2024-07-10T15:06:42.471909Z"
    }
   },
   "source": [
    "For the remainder of this lab (ungraded), you'll be focusing on coming up with and answering your own question, just like we did above. Your question should not be overly simple, and should require both descriptive statistics and data visualization to answer. In case you're unsure of what questions to ask, some sample questions have been provided below.\n",
    "Pick one of the following questions to investigate and answer, or come up with one of your own!\n",
    "\n",
    "- Which powers have the highest chance of co-occurring in a hero (e.g. super strength and flight)?\n",
    "- What is the distribution of skin colors amongst alien heroes?\n",
    "- How are eye color and hair color related in this dataset?\n",
    "\n",
    "Explain your question below:\n",
    "    \n",
    "Some sample cells have been provided to give you room to work. Feel free to create more cells as needed.\n",
    "\n",
    "Be sure to include thoughtful, well-labeled visualizations to back up your analysis!\n",
    "\n",
    "(There is no solution for this part, and feel free to move on to the next lesson if you have already spent more than 60 minutes.)"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "69c53a04",
   "metadata": {
    "ExecuteTime": {
     "end_time": "2024-07-10T15:09:59.536588Z",
     "start_time": "2024-07-10T15:09:59.535248Z"
    }
   },
   "source": [
    "### Summary"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "3235ee00",
   "metadata": {},
   "source": [
    "In this lab, you demonstrated your mastery of using pandas to clean and aggregate data in order to answer several business questions. This included identifying and handling missing values, text requiring preprocessing, and invalid values. You also performed aggregation and reshaping tasks such as transposing, joining, and grouping data. Great job, there was a lot here!"
   ]
  }
 ],
 "metadata": {
  "kernelspec": {
   "display_name": "Python (learn-env)",
   "language": "python",
   "name": "learn-env"
  },
  "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"
  },
  "toc": {
   "base_numbering": 1,
   "nav_menu": {},
   "number_sections": true,
   "sideBar": true,
   "skip_h1_title": false,
   "title_cell": "Table of Contents",
   "title_sidebar": "Contents",
   "toc_cell": false,
   "toc_position": {},
   "toc_section_display": true,
   "toc_window_display": false
  }
 },
 "nbformat": 4,
 "nbformat_minor": 5
}