basics/challenges/Data_Manipulation_Challenge.ipynb

{
  "nbformat": 4,
  "nbformat_minor": 0,
  "metadata": {
    "colab": {
      "provenance": [],
      "include_colab_link": true
    },
    "kernelspec": {
      "name": "python3",
      "display_name": "Python 3"
    },
    "language_info": {
      "name": "python"
    }
  },
  "cells": [
    {
      "cell_type": "markdown",
      "metadata": {
        "id": "view-in-github",
        "colab_type": "text"
      },
      "source": [
        "<a href=\"https://colab.research.google.com/github/BreakoutMentors/Data-Science-and-Machine-Learning/blob/main/basics/challenges/Data_Manipulation_Challenge.ipynb\" target=\"_parent\"><img src=\"https://colab.research.google.com/assets/colab-badge.svg\" alt=\"Open In Colab\"/></a>"
      ]
    },
    {
      "cell_type": "code",
      "source": [
        "import pandas as pd\n",
        "import numpy as np"
      ],
      "metadata": {
        "id": "d3cxkwCTXgtc"
      },
      "execution_count": null,
      "outputs": []
    },
    {
      "cell_type": "markdown",
      "source": [
        "# Data Structures"
      ],
      "metadata": {
        "id": "HKVi80LoLP-v"
      }
    },
    {
      "cell_type": "markdown",
      "source": [
        "## Series"
      ],
      "metadata": {
        "id": "aTxQ_1ZrO3zx"
      }
    },
    {
      "cell_type": "markdown",
      "source": [
        "### Initializing from lists\n",
        "Let's create a series object to store 5 students' names and their respective ages. Store the series into name_age and make sure to set the index to names."
      ],
      "metadata": {
        "id": "PZ2I30EYO50s"
      }
    },
    {
      "cell_type": "code",
      "execution_count": null,
      "metadata": {
        "id": "5Weh8gcpLJE0"
      },
      "outputs": [],
      "source": [
        "names = ['John', 'Ethan', 'Fiona', 'Iman', 'Ben']\n",
        "ages  = [14, 15, 17, 18, 15]\n",
        "name_age = None # Replace None with your code\n",
        "name_age"
      ]
    },
    {
      "cell_type": "markdown",
      "source": [
        "### Initializing from dictionary\n",
        "Let's create a series object to store 5 students' names and their respective favorite colors. Store the series into name_color."
      ],
      "metadata": {
        "id": "bjx0xA7vQbai"
      }
    },
    {
      "cell_type": "code",
      "source": [
        "name_color_dict = {\n",
        "    \"Name\": \"Favorite Color\",\n",
        "} # Think of 5 random names and colors\n",
        "name_color = None # Replace None with your code\n",
        "name_color"
      ],
      "metadata": {
        "id": "FDd13zqzRB4i"
      },
      "execution_count": null,
      "outputs": []
    },
    {
      "cell_type": "markdown",
      "source": [
        "## DataFrame"
      ],
      "metadata": {
        "id": "7YeTw0sJTyEk"
      }
    },
    {
      "cell_type": "markdown",
      "source": [
        "### Initializing from lists and dictionary"
      ],
      "metadata": {
        "id": "f7Pt6J9FU4Lj"
      }
    },
    {
      "cell_type": "markdown",
      "source": [
        "Create a pandas DataFrame from the following lists:"
      ],
      "metadata": {
        "id": "ptHFmthxW6UK"
      }
    },
    {
      "cell_type": "code",
      "source": [
        "names = ['Alice', 'Bob', 'Charlie']\n",
        "ages = [15, 17, 14]\n",
        "emails = ['alice@example.com', 'bob@example.com', 'charlie@example.com']\n",
        "name_emails_dict = {\n",
        "    'name': names,\n",
        "    # age here\n",
        "    # email here\n",
        "}\n",
        "name_emails = None # Replace None with your code\n",
        "name_emails"
      ],
      "metadata": {
        "id": "geTE_tQBU5Vp"
      },
      "execution_count": null,
      "outputs": []
    },
    {
      "cell_type": "markdown",
      "source": [
        "### Initializing from numpy array and lists"
      ],
      "metadata": {
        "id": "FytWh-4wZtf3"
      }
    },
    {
      "cell_type": "markdown",
      "source": [
        "Create a pandas DataFrame from the following NumPy array and lists:"
      ],
      "metadata": {
        "id": "sS7GJCb_UvSA"
      }
    },
    {
      "cell_type": "code",
      "source": [
        "data = np.array([[1, 2, 3], [4, 5, 6], [7, 8, 9]])\n",
        "data_index=['Row 1', 'Row 2', 'Row 3']\n",
        "data_columns=['A', 'B', 'C']\n",
        "data_df = # Replace with pd.DataFrame(?, index=?, columns=?)\n",
        "data_df"
      ],
      "metadata": {
        "id": "B5_n3gz4TvbM"
      },
      "execution_count": null,
      "outputs": []
    },
    {
      "cell_type": "markdown",
      "source": [
        "# Importing Datasets"
      ],
      "metadata": {
        "id": "14E9f9dJaOvo"
      }
    },
    {
      "cell_type": "markdown",
      "source": [
        "Let's load in all the datasets.\n",
        "\n",
        "For students, set the index column to be `'id'`.\n",
        "\n",
        "For athletes/academics, set the index column to be 0."
      ],
      "metadata": {
        "id": "pqu_8SYBa7nS"
      }
    },
    {
      "cell_type": "code",
      "source": [
        "students_url = 'https://raw.githubusercontent.com/BreakoutMentors/Data-Science-and-Machine-Learning/main/datasets/students.csv'\n",
        "athletes_url = 'https://raw.githubusercontent.com/BreakoutMentors/Data-Science-and-Machine-Learning/main/datasets/student_athletes.csv'\n",
        "academics_url = 'https://raw.githubusercontent.com/BreakoutMentors/Data-Science-and-Machine-Learning/main/datasets/student_academics.csv'\n",
        "\n",
        "# students =\n",
        "# athletes = pd.read_csv(athletes_url, index_col=0)\n",
        "# academics ="
      ],
      "metadata": {
        "id": "Bbev4NpqZiMB"
      },
      "execution_count": null,
      "outputs": []
    },
    {
      "cell_type": "markdown",
      "source": [
        "Print the first 5 elements of students\n",
        "(use `.head()`)"
      ],
      "metadata": {
        "id": "oUSeJwSaeKMS"
      }
    },
    {
      "cell_type": "code",
      "source": [],
      "metadata": {
        "id": "1S5a6zKZci5R"
      },
      "execution_count": null,
      "outputs": []
    },
    {
      "cell_type": "markdown",
      "source": [
        "Print the first 5 elements of athletes\n",
        "(use `.head()`)"
      ],
      "metadata": {
        "id": "O6JExCndd82O"
      }
    },
    {
      "cell_type": "code",
      "source": [],
      "metadata": {
        "id": "Tyv4rMp_ckbm"
      },
      "execution_count": null,
      "outputs": []
    },
    {
      "cell_type": "markdown",
      "source": [
        "Print the first 10 elements of academics\n",
        "(use `.head()`)"
      ],
      "metadata": {
        "id": "HtY65Yd8d_4O"
      }
    },
    {
      "cell_type": "code",
      "source": [],
      "metadata": {
        "id": "2kVlwYWkbTUl"
      },
      "execution_count": null,
      "outputs": []
    },
    {
      "cell_type": "markdown",
      "source": [
        "# Exploring the Data"
      ],
      "metadata": {
        "id": "Ird4NHq2htIg"
      }
    },
    {
      "cell_type": "markdown",
      "source": [
        "Use `describe` to get an overview of `athletes`.\n",
        "\n",
        "Answer:\n",
        "- How many student records are there?\n",
        "- What's the average grade level of the athletes?"
      ],
      "metadata": {
        "id": "Lsq9HKSUiGl7"
      }
    },
    {
      "cell_type": "code",
      "source": [],
      "metadata": {
        "id": "n45aiHIsir0V"
      },
      "execution_count": null,
      "outputs": []
    },
    {
      "cell_type": "markdown",
      "source": [
        "Print the `'sport'` column in `student_athletes` using `[ ]`."
      ],
      "metadata": {
        "id": "25NmUzTyjcl_"
      }
    },
    {
      "cell_type": "code",
      "source": [],
      "metadata": {
        "id": "pmquFo-niSWB"
      },
      "execution_count": null,
      "outputs": []
    },
    {
      "cell_type": "markdown",
      "source": [
        "Print the number of players in each sport. (Hint: use `value_counts`)"
      ],
      "metadata": {
        "id": "Io8IlqjJj_ek"
      }
    },
    {
      "cell_type": "code",
      "source": [],
      "metadata": {
        "id": "fFHPAdvEkNfu"
      },
      "execution_count": null,
      "outputs": []
    },
    {
      "cell_type": "markdown",
      "source": [
        "# Manipulating Data: Add, Rename, and Drop Columns"
      ],
      "metadata": {
        "id": "ubMBHZW2l10K"
      }
    },
    {
      "cell_type": "markdown",
      "source": [
        "Create a new column `'ScoreIsA'` which tells whether a student scored above a 90.\n",
        "\n",
        "Hint: Use the `>` operator."
      ],
      "metadata": {
        "id": "_sRWTiYyl4Ue"
      }
    },
    {
      "cell_type": "code",
      "source": [
        "# academics['ScoreIsA'] =\n",
        "# academics"
      ],
      "metadata": {
        "id": "m1G_VBYqlaKP"
      },
      "execution_count": null,
      "outputs": []
    },
    {
      "cell_type": "markdown",
      "source": [
        "Rename the column to be `'A'`."
      ],
      "metadata": {
        "id": "v2XWhAdxm8mY"
      }
    },
    {
      "cell_type": "code",
      "source": [
        "# academics =\n",
        "# academics"
      ],
      "metadata": {
        "id": "QxvaMHbJnB1s"
      },
      "execution_count": null,
      "outputs": []
    },
    {
      "cell_type": "markdown",
      "source": [
        "Drop the `'A'` column."
      ],
      "metadata": {
        "id": "drjG5Vl_oQsN"
      }
    },
    {
      "cell_type": "code",
      "source": [
        "# academics =\n",
        "# academics"
      ],
      "metadata": {
        "id": "sirff00voUCX"
      },
      "execution_count": null,
      "outputs": []
    },
    {
      "cell_type": "markdown",
      "source": [
        "# Filtering Data"
      ],
      "metadata": {
        "id": "Y-hoStWgofWK"
      }
    },
    {
      "cell_type": "markdown",
      "source": [
        "Filter for `'Swimming'` players in `athletes['sport']`."
      ],
      "metadata": {
        "id": "lKlbn3wtokoR"
      }
    },
    {
      "cell_type": "code",
      "source": [
        "# isSwimmer ="
      ],
      "metadata": {
        "id": "LfefLtwsoZTC"
      },
      "execution_count": null,
      "outputs": []
    },
    {
      "cell_type": "markdown",
      "source": [
        "Use isSwimmer to grab all the swimmers in athletes.\n",
        "\n",
        "Hint: Use `isSwimmer`."
      ],
      "metadata": {
        "id": "63FcmGS9p02Z"
      }
    },
    {
      "cell_type": "code",
      "source": [
        "# swimmers =\n",
        "# swimmers"
      ],
      "metadata": {
        "id": "npGcXDnbosPt"
      },
      "execution_count": null,
      "outputs": []
    },
    {
      "cell_type": "markdown",
      "source": [
        "# Sorting Data"
      ],
      "metadata": {
        "id": "8koAfE38qKY7"
      }
    },
    {
      "cell_type": "markdown",
      "source": [
        "Sort the athletes by their `'hours training'` from highest-to-lowest (descending) using `'id'` as a tie-breaker in increasing (ascending) order.\n",
        "\n",
        "Hints:\n",
        "- Use `sort_values`\n",
        "- the parameter `by` can take a list of column names and `ascending` can take a list of booleans."
      ],
      "metadata": {
        "id": "_SpQAJlKricb"
      }
    },
    {
      "cell_type": "code",
      "source": [
        "# sorted_athletes =\n",
        "# sorted_athletes"
      ],
      "metadata": {
        "id": "L-wUBSSCqJwd"
      },
      "execution_count": null,
      "outputs": []
    },
    {
      "cell_type": "markdown",
      "source": [
        "# Grouping and Aggregating Data"
      ],
      "metadata": {
        "id": "SfJV0FIltVG5"
      }
    },
    {
      "cell_type": "markdown",
      "source": [
        "Use `groupby` to group the student athletes by their `'id'`."
      ],
      "metadata": {
        "id": "uzEh_i4ItnEb"
      }
    },
    {
      "cell_type": "code",
      "source": [
        "# athlete_ids ="
      ],
      "metadata": {
        "id": "-E5Rw8Msswnx"
      },
      "execution_count": null,
      "outputs": []
    },
    {
      "cell_type": "markdown",
      "source": [
        "Now, call `apply(list)` on the `'sport'` column of athlete_ids.\n",
        "\n",
        "The apply() function is used to apply the list() function to the \"sport\" column within each group. This converts the individual sports values in each group into a list of sports."
      ],
      "metadata": {
        "id": "2QRq38SJPx5M"
      }
    },
    {
      "cell_type": "code",
      "source": [
        "# athlete_ids =\n",
        "\n",
        "# athlete_ids = athlete_ids.reset_index(name='sports').set_index('id')\n",
        "# athlete_ids"
      ],
      "metadata": {
        "id": "L7PBI384O1TE"
      },
      "execution_count": null,
      "outputs": []
    },
    {
      "cell_type": "markdown",
      "source": [
        "# Merging Two Dataframes"
      ],
      "metadata": {
        "id": "HYFRn3PWvHqu"
      }
    },
    {
      "cell_type": "markdown",
      "source": [
        "The DataSets are great on their own but they can be give us more information by merging them together!\n",
        "\n",
        "First, let's merge together `academics` and `athlete_ids` on the `'id'` column.\n",
        "\n",
        "Hint: `pd.merge` has an argument `on` which accepts column names.\n",
        "\n",
        "What do you expect the merged dataframe to look like?"
      ],
      "metadata": {
        "id": "HykJGwlSzNq5"
      }
    },
    {
      "cell_type": "code",
      "source": [
        "# merged_df =\n",
        "# merged_df.head(20)"
      ],
      "metadata": {
        "id": "J6PQBzrRDyTz"
      },
      "execution_count": null,
      "outputs": []
    },
    {
      "cell_type": "markdown",
      "source": [
        "We're left with only student-athletes and their corresponding sport/academic information!\n",
        "Now let's see what happens when we merge with `students`."
      ],
      "metadata": {
        "id": "c4NhYm5tGXHA"
      }
    },
    {
      "cell_type": "code",
      "source": [
        "# This code is already completed for you, just uncomment it\n",
        "# merged_df = pd.merge(students, merged_df, left_on=['id', 'name', 'grade'], right_on=['id', 'name', 'grade level']).drop(columns='grade level')\n",
        "# merged_df.head(20)"
      ],
      "metadata": {
        "id": "EoX5BGJ8S79y"
      },
      "execution_count": null,
      "outputs": []
    },
    {
      "cell_type": "markdown",
      "source": [
        "Now we can only see the most recent school year! This is because the `'grade'` column in `students` only stores the student's most recent school year, whereas in `academics` we store every academic year of the students."
      ],
      "metadata": {
        "id": "JrgwH7myUsVx"
      }
    },
    {
      "cell_type": "markdown",
      "source": [
        "# Congratulations!\n",
        "That's all for now. Good work!"
      ],
      "metadata": {
        "id": "L8A7urnMIolt"
      }
    }
  ]
}