PandasTraining_Unsolved.ipynb

{
  "nbformat": 4,
  "nbformat_minor": 0,
  "metadata": {
    "colab": {
      "name": "PandasTraining_Unsolved.ipynb",
      "version": "0.3.2",
      "provenance": [],
      "collapsed_sections": [
        "3nXNkBzsxSfm",
        "XT7ZVuUpxSfq"
      ],
      "include_colab_link": true
    },
    "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.6.4"
    },
    "kernelspec": {
      "display_name": "Python 3",
      "language": "python",
      "name": "python3"
    }
  },
  "cells": [
    {
      "cell_type": "markdown",
      "metadata": {
        "id": "view-in-github",
        "colab_type": "text"
      },
      "source": [
        "<a href=\"https://colab.research.google.com/github/alitarraf/Data-Science-Training/blob/master/PandasTraining_Unsolved.ipynb\" target=\"_parent\"><img src=\"https://colab.research.google.com/assets/colab-badge.svg\" alt=\"Open In Colab\"/></a>"
      ]
    },
    {
      "cell_type": "markdown",
      "metadata": {
        "id": "h4iI2-i6xSfB",
        "colab_type": "text"
      },
      "source": [
        "# Python Pandas Tutorial: A Complete Introduction for Beginners"
      ]
    },
    {
      "cell_type": "markdown",
      "metadata": {
        "id": "4oTG2HOdxSfC",
        "colab_type": "text"
      },
      "source": [
        "![alt text](https://github.com/alitarraf/Data-Science-Training/blob/master/assets/panda%20(2).jpg?raw=true)\n",
        "\n",
        "The *pandas* package is the most important tool at the disposal of Data Scientists and Analysts working in Python today. The powerful machine learning and glamorous visualization tools may get all the attention, but pandas is the backbone of most data projects. \n",
        "\n",
        ">\\[*pandas*\\] is derived from the term \"**pan**el **da**ta\", an econometrics term for data sets that include observations over multiple time periods for the same individuals. — [Wikipedia](https://en.wikipedia.org/wiki/Pandas_%28software%29)\n",
        "\n",
        "If you're thinking about data science as a career, then it is imperative that one of the first things you do is learn pandas. In this post, we will go over the essential bits of information about pandas, including how to install it, its uses, and how it works with other common Python data analysis packages such as **matplotlib** and **sci-kit learn**."
      ]
    },
    {
      "cell_type": "markdown",
      "metadata": {
        "id": "pO6Fkk8uxSfD",
        "colab_type": "text"
      },
      "source": [
        "## What's Pandas for?\n",
        "\n",
        "Pandas has so many uses that it might make sense to list the things it can't do instead of what it can do. \n",
        "\n",
        "This tool is essentially your data’s home. Through pandas, you get acquainted with your data by cleaning, transforming, and analyzing it. \n",
        "\n",
        "For example, say you want to explore a dataset stored in a CSV on your computer. Pandas will extract the data from that CSV into a DataFrame — a table, basically — then let you do things like:\n",
        "\n",
        "- Calculate statistics and answer questions about the data, like\n",
        "\n",
        "\n",
        "    - What's the average, median, max, or min of each column? \n",
        "    - Does column A correlate with column B?\n",
        "    - What does the distribution of data in column C look like?\n",
        "\n",
        "\n",
        "- Clean the data by doing things like removing missing values and filtering rows or columns by some criteria\n",
        "\n",
        "\n",
        "- Visualize the data with help from Matplotlib. Plot bars, lines, histograms, bubbles, and more. \n",
        "\n",
        "\n",
        "- Store the cleaned, transformed data back into a CSV, other file or database\n",
        "\n",
        "\n",
        "Before you jump into the modeling or the complex visualizations you need to have a good understanding of the nature of your dataset and pandas is the best avenue through which to do that.\n",
        "\n"
      ]
    },
    {
      "cell_type": "markdown",
      "metadata": {
        "id": "6TEVlnlqxSfE",
        "colab_type": "text"
      },
      "source": [
        "## How does pandas fit into the data science toolkit?\n",
        "\n",
        "Not only is the pandas library a central component of the data science toolkit but it is used in conjunction with other libraries in that collection. \n",
        "\n",
        "Pandas is built on top of the **NumPy** package, meaning a lot of the structure of NumPy is used or replicated in Pandas. Data in pandas is often used to feed statistical analysis in **SciPy**, plotting functions from **Matplotlib**, and machine learning algorithms in **Scikit-learn**.\n",
        "\n",
        "Jupyter Notebooks offer a good environment for using pandas to do data exploration and modeling, but pandas can also be used in text editors just as easily.\n",
        "\n",
        "Jupyter Notebooks give us the ability to execute code in a particular cell as opposed to running the entire file. This saves a lot of time when working with large datasets and complex transformations. Notebooks also provide an easy way to visualize pandas’ DataFrames and plots. As a matter of fact, this article was created entirely in a Jupyter Notebook.\n",
        "\n"
      ]
    },
    {
      "cell_type": "markdown",
      "metadata": {
        "id": "WmpZUVovw1jW",
        "colab_type": "text"
      },
      "source": [
        "## When should you start using pandas?\n",
        "\n",
        "If you do not have any experience coding in Python, then you should stay away from learning pandas until you do. You don’t have to be at the level of the software engineer, but you should be adept at the basics, such as lists, tuples, dictionaries, functions, and iterations. Also, I’d also recommend familiarizing yourself with **NumPy** due to the similarities mentioned above.\n",
        "\n",
        "If you're looking for a good place to learn Python, [Python for Everybody](https://www.learndatasci.com/out/coursera-programming-everybody-getting-started-python/) on Coursera is great (and Free).\n",
        "\n",
        "Moreover, for those of you looking to do a [data science bootcamp](https://www.learndatasci.com/articles/thinkful-data-science-online-bootcamp-review/) or some other accelerated data science education program, it's highly recommended you start learning pandas on your own before you start the program. \n",
        "\n",
        "Even though accelerated programs teach you pandas, better skills beforehand means you'll be able to maximize time for learning and mastering the more complicated material."
      ]
    },
    {
      "cell_type": "markdown",
      "metadata": {
        "id": "4JVnZ58z1Rii",
        "colab_type": "text"
      },
      "source": [
        "## Downloading Files\n",
        "\n",
        "For simplicity and gaining time, let's run the code to downlaod files:\n",
        "\n"
      ]
    },
    {
      "cell_type": "code",
      "metadata": {
        "id": "IfMOfRSj1Jbx",
        "colab_type": "code",
        "colab": {}
      },
      "source": [
        "!git clone https://github.com/alitarraf/Data-Science-Training.git  # download data from Github\n",
        "# change directory into the downloaded folder\n",
        "%cd \"Data-Science-Training/data\"\n",
        "!ls -lah  # list files in current directory"
      ],
      "execution_count": 0,
      "outputs": []
    },
    {
      "cell_type": "markdown",
      "metadata": {
        "id": "f_doOs_KxSfE",
        "colab_type": "text"
      },
      "source": [
        "## Pandas First Steps\n",
        "\n"
      ]
    },
    {
      "cell_type": "markdown",
      "metadata": {
        "id": "orw3maICwsmt",
        "colab_type": "text"
      },
      "source": [
        "### Install and import\n",
        "Pandas is an easy package to install. Open up your terminal program (for Mac users) or command line (for PC users) and install it using either of the following commands:"
      ]
    },
    {
      "cell_type": "markdown",
      "metadata": {
        "id": "WiPV8gu7xSfF",
        "colab_type": "text"
      },
      "source": [
        "`conda install pandas`\n",
        "\n",
        "OR \n",
        "\n",
        "`pip install pandas`"
      ]
    },
    {
      "cell_type": "markdown",
      "metadata": {
        "id": "-NfV44HQxSfG",
        "colab_type": "text"
      },
      "source": [
        "Alternatively, if you're currently viewing this article in a Jupyter notebook you can run this cell:"
      ]
    },
    {
      "cell_type": "code",
      "metadata": {
        "id": "Ncj6EL7fxSfG",
        "colab_type": "code",
        "colab": {}
      },
      "source": [
        "!pip install pandas"
      ],
      "execution_count": 0,
      "outputs": []
    },
    {
      "cell_type": "markdown",
      "metadata": {
        "id": "NuXqkU4JxSfJ",
        "colab_type": "text"
      },
      "source": [
        "The `!` at the beginning runs cells as if they were in a terminal.\n",
        "\n",
        "To import pandas we usually import it with a shorter name since it's used so much:"
      ]
    },
    {
      "cell_type": "code",
      "metadata": {
        "id": "8ZycyHuSxSfK",
        "colab_type": "code",
        "colab": {}
      },
      "source": [
        "import pandas as pd"
      ],
      "execution_count": 0,
      "outputs": []
    },
    {
      "cell_type": "markdown",
      "metadata": {
        "id": "gA0CXBPjxSfM",
        "colab_type": "text"
      },
      "source": [
        "Now to the basic components of pandas."
      ]
    },
    {
      "cell_type": "markdown",
      "metadata": {
        "id": "cT9OVC0jxSfN",
        "colab_type": "text"
      },
      "source": [
        "## Core components of pandas: Series and DataFrames\n",
        "\n",
        "\n",
        "A `Series` is essentially a column.\n",
        "\n",
        "A `DataFrame` is a multi-dimensional table made up of a collection of Series. \n",
        "\n",
        "\n",
        "DataFrames and Series are quite similar in that many operations that you can do with one you can do with the other, such as filling in null values and calculating the mean.\n",
        "\n",
        "You'll see how these components work when we start working with data below. "
      ]
    },
    {
      "cell_type": "markdown",
      "metadata": {
        "id": "6UNispZhxSfO",
        "colab_type": "text"
      },
      "source": [
        "### Creating DataFrames from scratch\n",
        "Creating DataFrames right in Python is good to know and quite useful when testing new methods and functions you find in the pandas docs.\n",
        "\n",
        "There are *many* ways to create a DataFrame from scratch, but a great option is to just use a simple `dict`. \n",
        "\n",
        "Let's say we have a fruit stand that sells apples and oranges. We want to have a column for each fruit and a row for each customer purchase. To organize this as a dictionary for pandas we could do something like:"
      ]
    },
    {
      "cell_type": "code",
      "metadata": {
        "id": "-z9O863oxSfO",
        "colab_type": "code",
        "colab": {}
      },
      "source": [
        "data = {\n",
        "    'apples': [3, 2, 0, 1], \n",
        "    'oranges': [0, 3, 7, 2]\n",
        "}"
      ],
      "execution_count": 0,
      "outputs": []
    },
    {
      "cell_type": "markdown",
      "metadata": {
        "id": "JQ6GS-O3xSfQ",
        "colab_type": "text"
      },
      "source": [
        "And then pass it to the pandas DataFrame constructor:"
      ]
    },
    {
      "cell_type": "code",
      "metadata": {
        "id": "U_hRc6KIxSfR",
        "colab_type": "code",
        "colab": {}
      },
      "source": [
        "purchases = pd.DataFrame(data)\n",
        "\n",
        "purchases"
      ],
      "execution_count": 0,
      "outputs": []
    },
    {
      "cell_type": "markdown",
      "metadata": {
        "id": "ZkQbptFrxSfX",
        "colab_type": "text"
      },
      "source": [
        "**How did that work?**\n",
        "\n",
        "Each *(key, value)* item in `data` corresponds to a *column* in the resulting DataFrame.\n",
        "\n",
        "The **Index** of this DataFrame was given to us on creation as the numbers 0-3, but we could also create our own when we initialize the DataFrame. \n",
        "\n",
        "Let's have customer names as our index: "
      ]
    },
    {
      "cell_type": "code",
      "metadata": {
        "id": "oR5Q3BgjxSfY",
        "colab_type": "code",
        "colab": {}
      },
      "source": [
        "purchases = pd.DataFrame(data, index=['June', 'Robert', 'Lily', 'David'])\n",
        "\n",
        "purchases"
      ],
      "execution_count": 0,
      "outputs": []
    },
    {
      "cell_type": "markdown",
      "metadata": {
        "id": "NlsO3eURxSfb",
        "colab_type": "text"
      },
      "source": [
        "So now we could **loc**ate a customer's order by using their name as we will see later."
      ]
    },
    {
      "cell_type": "markdown",
      "metadata": {
        "id": "GHFifK_DxSff",
        "colab_type": "text"
      },
      "source": [
        "## How to read in data\n",
        "\n",
        "It’s quite simple to load data from various file formats into a DataFrame. In the following examples we'll keep using our apples and oranges data, but this time it's coming from various files."
      ]
    },
    {
      "cell_type": "markdown",
      "metadata": {
        "id": "SHUcCdAliyqv",
        "colab_type": "text"
      },
      "source": [
        "### Reading data from Excel\n",
        "With Excel files all you need is a single line to load in the data:"
      ]
    },
    {
      "cell_type": "code",
      "metadata": {
        "id": "LYiF3TzQjCVm",
        "colab_type": "code",
        "colab": {}
      },
      "source": [
        "df = pd.read_excel('purchases.xlsx')\n",
        "df"
      ],
      "execution_count": 0,
      "outputs": []
    },
    {
      "cell_type": "markdown",
      "metadata": {
        "id": "tBwgx8gLjZlv",
        "colab_type": "text"
      },
      "source": [
        "XLSXs don't have indexes like our DataFrames, so all we need to do is just designate the index_col when reading:"
      ]
    },
    {
      "cell_type": "code",
      "metadata": {
        "id": "CT6hX8_314L_",
        "colab_type": "code",
        "colab": {}
      },
      "source": [
        "df = pd.read_excel('purchases.xlsx', index_col=0)\n",
        "\n",
        "df"
      ],
      "execution_count": 0,
      "outputs": []
    },
    {
      "cell_type": "markdown",
      "metadata": {
        "id": "SIwZATy0xSfg",
        "colab_type": "text"
      },
      "source": [
        "### Reading data from CSVs\n",
        "\n",
        "With CSV files all you need is a single line to load in the data:\n",
        "\n",
        "Read documentation of \"read csv\" [here](https://pandas.pydata.org/pandas-docs/stable/reference/api/pandas.read_csv.html)."
      ]
    },
    {
      "cell_type": "code",
      "metadata": {
        "id": "18FmBXUAxSfh",
        "colab_type": "code",
        "colab": {}
      },
      "source": [
        "# Read file 'purchases.csv'\n",
        "\n",
        "### START CODE HERE ### (aprox. 1 line of code)\n",
        "\n",
        "### END CODE HERE ###"
      ],
      "execution_count": 0,
      "outputs": []
    },
    {
      "cell_type": "code",
      "metadata": {
        "id": "w2ptt0zj3dW4",
        "colab_type": "code",
        "colab": {}
      },
      "source": [
        "df"
      ],
      "execution_count": 0,
      "outputs": []
    },
    {
      "cell_type": "markdown",
      "metadata": {
        "id": "U7InU7kWZpMy",
        "colab_type": "text"
      },
      "source": [
        "Expected Output:\n",
        "\n",
        "![alt text](https://github.com/alitarraf/Data-Science-Training/blob/master/assets/Ex1.png?raw=true)"
      ]
    },
    {
      "cell_type": "markdown",
      "metadata": {
        "id": "opNh6QMFxSfj",
        "colab_type": "text"
      },
      "source": [
        "CSVs don't have indexes like our DataFrames, so all we need to do is just designate the `index_col` when reading:"
      ]
    },
    {
      "cell_type": "code",
      "metadata": {
        "id": "1rOtkMsdxSfk",
        "colab_type": "code",
        "colab": {}
      },
      "source": [
        "# Set column 0 as index\n",
        "\n",
        "### START CODE HERE ### (aprox. 1 line of code)\n",
        "\n",
        "### END CODE HERE ###"
      ],
      "execution_count": 0,
      "outputs": []
    },
    {
      "cell_type": "code",
      "metadata": {
        "id": "I63hTrB34x7J",
        "colab_type": "code",
        "colab": {}
      },
      "source": [
        "df"
      ],
      "execution_count": 0,
      "outputs": []
    },
    {
      "cell_type": "markdown",
      "metadata": {
        "id": "CFMXF8w5Z-LX",
        "colab_type": "text"
      },
      "source": [
        "Expected Output:\n",
        "\n",
        "![alt text](https://github.com/alitarraf/Data-Science-Training/blob/master/assets/Ex2.png?raw=true)"
      ]
    },
    {
      "cell_type": "markdown",
      "metadata": {
        "id": "26egH61vrHVh",
        "colab_type": "text"
      },
      "source": [
        "Here we're setting the index to be column zero.\n",
        "\n",
        "You'll find that most CSVs won't ever have an index column and so usually you don't have to worry about this step.\n"
      ]
    },
    {
      "cell_type": "markdown",
      "metadata": {
        "id": "3nXNkBzsxSfm",
        "colab_type": "text"
      },
      "source": [
        "### Reading data from JSON\n",
        "\n",
        "If you have a JSON file — which is essentially a stored Python `dict` — pandas can read this just as easily:"
      ]
    },
    {
      "cell_type": "code",
      "metadata": {
        "colab_type": "code",
        "outputId": "c4caa9f7-15ef-48ea-e96f-c887d14a5057",
        "id": "e_VuHXjDqAy-",
        "colab": {}
      },
      "source": [
        "df = pd.read_json('purchases.json')\n",
        "\n",
        "df"
      ],
      "execution_count": 0,
      "outputs": [
        {
          "output_type": "execute_result",
          "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>apples</th>\n",
              "      <th>oranges</th>\n",
              "    </tr>\n",
              "  </thead>\n",
              "  <tbody>\n",
              "    <tr>\n",
              "      <th>David</th>\n",
              "      <td>1</td>\n",
              "      <td>2</td>\n",
              "    </tr>\n",
              "    <tr>\n",
              "      <th>June</th>\n",
              "      <td>3</td>\n",
              "      <td>0</td>\n",
              "    </tr>\n",
              "    <tr>\n",
              "      <th>Lily</th>\n",
              "      <td>0</td>\n",
              "      <td>7</td>\n",
              "    </tr>\n",
              "    <tr>\n",
              "      <th>Robert</th>\n",
              "      <td>2</td>\n",
              "      <td>3</td>\n",
              "    </tr>\n",
              "  </tbody>\n",
              "</table>\n",
              "</div>"
            ],
            "text/plain": [
              "        apples  oranges\n",
              "David        1        2\n",
              "June         3        0\n",
              "Lily         0        7\n",
              "Robert       2        3"
            ]
          },
          "metadata": {
            "tags": []
          },
          "execution_count": 55
        }
      ]
    },
    {
      "cell_type": "markdown",
      "metadata": {
        "id": "c4BiBKkarXUH",
        "colab_type": "text"
      },
      "source": [
        "Notice this time our index came with us correctly since using JSON allowed indexes to work through nesting. Feel free to open `data_file.json` in a notepad so you can see how it works.\n",
        "\n",
        "Pandas will try to figure out how to create a DataFrame by analyzing structure of your JSON, and sometimes it doesn't get it right. Often you'll need to set the `orient` keyword argument depending on the structure, so check out [read_json docs](https://pandas.pydata.org/pandas-docs/stable/generated/pandas.read_json.html) about that argument to see which orientation you're using.\n"
      ]
    },
    {
      "cell_type": "markdown",
      "metadata": {
        "id": "XT7ZVuUpxSfq",
        "colab_type": "text"
      },
      "source": [
        "### Reading data from a SQL database\n",
        "\n",
        "If you’re working with data from a SQL database you need to first establish a connection using an appropriate Python library, then pass a query to pandas. Here we'll use SQLite to demonstrate. \n",
        "\n",
        "First, we need `pysqlite3` installed, so run this command in your terminal:"
      ]
    },
    {
      "cell_type": "markdown",
      "metadata": {
        "id": "pXlUbo_lxSfq",
        "colab_type": "text"
      },
      "source": [
        "`pip install pysqlite3`"
      ]
    },
    {
      "cell_type": "markdown",
      "metadata": {
        "id": "CI8e247xxSfr",
        "colab_type": "text"
      },
      "source": [
        "Or run this cell if you're in a notebook:"
      ]
    },
    {
      "cell_type": "code",
      "metadata": {
        "id": "4qz_hl8FxSfs",
        "colab_type": "code",
        "colab": {}
      },
      "source": [
        "!pip install pysqlite3"
      ],
      "execution_count": 0,
      "outputs": []
    },
    {
      "cell_type": "markdown",
      "metadata": {
        "id": "ZjP_3WC3xSfu",
        "colab_type": "text"
      },
      "source": [
        "`sqlite3` is used to create a connection to a database which we can then use to generate a DataFrame through a `SELECT` query.\n",
        "\n",
        "So first we'll make a connection to a SQLite database file:"
      ]
    },
    {
      "cell_type": "code",
      "metadata": {
        "id": "amXbtKr8xSfw",
        "colab_type": "code",
        "colab": {}
      },
      "source": [
        "import sqlite3\n",
        "\n",
        "con = sqlite3.connect(\"database.db\")"
      ],
      "execution_count": 0,
      "outputs": []
    },
    {
      "cell_type": "markdown",
      "metadata": {
        "id": "yPZUv6lIxSf0",
        "colab_type": "text"
      },
      "source": [
        "**Note**: If you have data in PostgreSQL, MySQL, or some other SQL server, you'll need to obtain the right Python library to make a connection. For example, `psycopg2` ([link](http://initd.org/psycopg/download/)) is a commonly used library for making connections to PostgreSQL. Furthermore, you would make a connection to a database URI instead of a file like we did here with SQLite. For a great course on SQL check out [The Complete SQL Bootcamp](https://learndatasci.com/out/udemy-the-complete-sql-bootcamp/) on Udemy\n",
        "\n",
        "\n",
        "In this SQLite database we have a table called *purchases*, and our index is in a column called \"index\". \n",
        "\n",
        "By passing a SELECT query and our `con`, we can read from the *purchases* table:"
      ]
    },
    {
      "cell_type": "code",
      "metadata": {
        "id": "IksMhiIGxSf0",
        "colab_type": "code",
        "outputId": "5128539b-46ce-47b2-ab31-04a00e9ab7f0",
        "colab": {}
      },
      "source": [
        "df = pd.read_sql_query(\"SELECT * FROM purchases\", con)\n",
        "\n",
        "df"
      ],
      "execution_count": 0,
      "outputs": [
        {
          "output_type": "execute_result",
          "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>index</th>\n",
              "      <th>apples</th>\n",
              "      <th>oranges</th>\n",
              "    </tr>\n",
              "  </thead>\n",
              "  <tbody>\n",
              "    <tr>\n",
              "      <th>0</th>\n",
              "      <td>June</td>\n",
              "      <td>3</td>\n",
              "      <td>0</td>\n",
              "    </tr>\n",
              "    <tr>\n",
              "      <th>1</th>\n",
              "      <td>Robert</td>\n",
              "      <td>2</td>\n",
              "      <td>3</td>\n",
              "    </tr>\n",
              "    <tr>\n",
              "      <th>2</th>\n",
              "      <td>Lily</td>\n",
              "      <td>0</td>\n",
              "      <td>7</td>\n",
              "    </tr>\n",
              "    <tr>\n",
              "      <th>3</th>\n",
              "      <td>David</td>\n",
              "      <td>1</td>\n",
              "      <td>2</td>\n",
              "    </tr>\n",
              "  </tbody>\n",
              "</table>\n",
              "</div>"
            ],
            "text/plain": [
              "    index  apples  oranges\n",
              "0    June       3        0\n",
              "1  Robert       2        3\n",
              "2    Lily       0        7\n",
              "3   David       1        2"
            ]
          },
          "metadata": {
            "tags": []
          },
          "execution_count": 62
        }
      ]
    },
    {
      "cell_type": "markdown",
      "metadata": {
        "id": "0HHOSaEsxSf4",
        "colab_type": "text"
      },
      "source": [
        "Just like with CSVs, we could pass `index_col='index'`, but we can also set an index after-the-fact:"
      ]
    },
    {
      "cell_type": "code",
      "metadata": {
        "id": "EWr3RNYWxSf5",
        "colab_type": "code",
        "outputId": "2d04972f-923f-4c81-a2fd-4ba089812f66",
        "colab": {}
      },
      "source": [
        "df = df.set_index('index')\n",
        "\n",
        "df"
      ],
      "execution_count": 0,
      "outputs": [
        {
          "output_type": "execute_result",
          "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>apples</th>\n",
              "      <th>oranges</th>\n",
              "    </tr>\n",
              "    <tr>\n",
              "      <th>index</th>\n",
              "      <th></th>\n",
              "      <th></th>\n",
              "    </tr>\n",
              "  </thead>\n",
              "  <tbody>\n",
              "    <tr>\n",
              "      <th>June</th>\n",
              "      <td>3</td>\n",
              "      <td>0</td>\n",
              "    </tr>\n",
              "    <tr>\n",
              "      <th>Robert</th>\n",
              "      <td>2</td>\n",
              "      <td>3</td>\n",
              "    </tr>\n",
              "    <tr>\n",
              "      <th>Lily</th>\n",
              "      <td>0</td>\n",
              "      <td>7</td>\n",
              "    </tr>\n",
              "    <tr>\n",
              "      <th>David</th>\n",
              "      <td>1</td>\n",
              "      <td>2</td>\n",
              "    </tr>\n",
              "  </tbody>\n",
              "</table>\n",
              "</div>"
            ],
            "text/plain": [
              "        apples  oranges\n",
              "index                  \n",
              "June         3        0\n",
              "Robert       2        3\n",
              "Lily         0        7\n",
              "David        1        2"
            ]
          },
          "metadata": {
            "tags": []
          },
          "execution_count": 64
        }
      ]
    },
    {
      "cell_type": "markdown",
      "metadata": {
        "id": "GQxdLvb3xSf9",
        "colab_type": "text"
      },
      "source": [
        "In fact, we could use `set_index()` on *any* DataFrame using *any* column at *any* time. Indexing Series and DataFrames is a very common task, and the different ways of doing it is worth remembering."
      ]
    },
    {
      "cell_type": "markdown",
      "metadata": {
        "id": "h0_068D-xSf-",
        "colab_type": "text"
      },
      "source": [
        "### Converting back to a XLSX, CSV, JSON, or SQL\n",
        "\n",
        "So after extensive work on cleaning your data, you’re now ready to save it as a file of your choice. Similar to the ways we read in data, pandas provides intuitive commands to save it:"
      ]
    },
    {
      "cell_type": "code",
      "metadata": {
        "id": "izlBd-MbxSf-",
        "colab_type": "code",
        "colab": {}
      },
      "source": [
        "df.to_excel('new_purchases.xlsx')\n",
        "\n",
        "df.to_csv('new_purchases.csv')\n",
        "\n",
        "# df.to_json('new_purchases.json')\n",
        "\n",
        "# df.to_sql('new_purchases', con)"
      ],
      "execution_count": 0,
      "outputs": []
    },
    {
      "cell_type": "markdown",
      "metadata": {
        "id": "qJff9vzxrq3-",
        "colab_type": "text"
      },
      "source": [
        "When we save XLSX, JSON and CSV files, all we have to input into those functions is our desired filename with the appropriate file extension. With SQL, we’re not creating a new file but instead inserting a new table into the database using our `con` variable from before.\n",
        "\n",
        "Let's move on to importing some real-world data and detailing a few of the operations you'll be using a lot."
      ]
    },
    {
      "cell_type": "markdown",
      "metadata": {
        "id": "jiL4L3NjxSgB",
        "colab_type": "text"
      },
      "source": [
        "## Most important DataFrame operations\n",
        "\n",
        "DataFrames possess hundreds of methods and other operations that are crucial to any analysis. As a beginner, you should know the operations that perform simple transformations of your data and those that provide fundamental statistical analysis.\n",
        "\n",
        "Let's load in the IMDB movies dataset to begin:"
      ]
    },
    {
      "cell_type": "code",
      "metadata": {
        "id": "xqsMq7abxSgC",
        "colab_type": "code",
        "colab": {}
      },
      "source": [
        "# Read IMDB movies CSV file, save it in \"movies_df\", and set Title as column index\n",
        "#To see output, refer to next section.\n",
        "\n",
        "### START CODE HERE ### (aprox. 1 line of code)\n",
        "\n",
        "### END CODE HERE ###"
      ],
      "execution_count": 0,
      "outputs": []
    },
    {
      "cell_type": "markdown",
      "metadata": {
        "id": "d801Sa_8xSgE",
        "colab_type": "text"
      },
      "source": [
        "We're loading this dataset from a CSV and designating the movie titles to be our index.\n"
      ]
    },
    {
      "cell_type": "markdown",
      "metadata": {
        "id": "aRBDBIjPxSgE",
        "colab_type": "text"
      },
      "source": [
        "### Viewing your data\n",
        "\n",
        "The first thing to do when opening a new dataset is print out a few rows to keep as a visual reference. We accomplish this with `.head()`:"
      ]
    },
    {
      "cell_type": "code",
      "metadata": {
        "id": "pjjwCW1fxSgF",
        "colab_type": "code",
        "colab": {}
      },
      "source": [
        "movies_df.head()"
      ],
      "execution_count": 0,
      "outputs": []
    },
    {
      "cell_type": "markdown",
      "metadata": {
        "id": "HBA-BIUOxSgI",
        "colab_type": "text"
      },
      "source": [
        "Expected Output:\n",
        "\n",
        "![alt text](https://github.com/alitarraf/Data-Science-Training/blob/master/assets/exx.jpg?raw=true)\n",
        "\n",
        "\n",
        "`.head()` outputs the **first** five rows of your DataFrame by default, but we could also pass a number as well: `movies_df.head(10)` would output the top ten rows, for example. \n",
        "\n",
        "To see the **last** five rows use `.tail()`. \n",
        "\n",
        "`tail()` also accepts a number, and in this case we printing the bottom two rows.:"
      ]
    },
    {
      "cell_type": "code",
      "metadata": {
        "id": "R8k5RwaXxSgI",
        "colab_type": "code",
        "colab": {}
      },
      "source": [
        "# Show the last 2 rows of movies_df\n",
        "\n",
        "### START CODE HERE ### (approx. 1 line of code)\n",
        "\n",
        "### END CODE HERE ###"
      ],
      "execution_count": 0,
      "outputs": []
    },
    {
      "cell_type": "markdown",
      "metadata": {
        "id": "FF_ydN1qayvz",
        "colab_type": "text"
      },
      "source": [
        "Expected Output:\n",
        "\n",
        "![alt text](https://github.com/alitarraf/Data-Science-Training/blob/master/assets/Ex3.png?raw=true)"
      ]
    },
    {
      "cell_type": "markdown",
      "metadata": {
        "id": "MP1ETk7RxSgM",
        "colab_type": "text"
      },
      "source": [
        "Typically when we load in a dataset, we like to view the first five or so rows to see what's under the hood. Here we can see the names of each column, the index, and examples of values in each row.\n",
        "\n",
        "You'll notice that the index in our DataFrame is the *Title* column, which you can tell by how the word *Title* is slightly lower than the rest of the columns."
      ]
    },
    {
      "cell_type": "markdown",
      "metadata": {
        "id": "R2SfgaNexSgN",
        "colab_type": "text"
      },
      "source": [
        "### Getting info about your data\n",
        "\n",
        "`.info()` should be one of the very first commands you run after loading your data:"
      ]
    },
    {
      "cell_type": "code",
      "metadata": {
        "id": "zG2rS9MLxSgO",
        "colab_type": "code",
        "colab": {}
      },
      "source": [
        "movies_df.info()\n",
        "\n",
        "type(movies_df['Actors'][3])"
      ],
      "execution_count": 0,
      "outputs": []
    },
    {
      "cell_type": "markdown",
      "metadata": {
        "id": "cNh79XlvxSgR",
        "colab_type": "text"
      },
      "source": [
        "`.info()` provides the essential details about your dataset, such as the number of rows and columns, the number of non-null values, what type of data is in each column, and how much memory your DataFrame is using. \n",
        "\n",
        "Notice in our movies dataset we have some obvious missing values in the `Revenue` and `Metascore` columns. We'll look at how to handle those in a bit.\n",
        "\n",
        "Seeing the datatype quickly is actually quite useful. Imagine you just imported some JSON and the integers were recorded as strings. You go to do some arithmetic and find an \"unsupported operand\" Exception because you can't do math with strings. Calling `.info()` will quickly point out that your column you thought was all integers are actually string objects.\n",
        "\n",
        "Another fast and useful attribute is `.shape`, which outputs just a tuple of (rows, columns):"
      ]
    },
    {
      "cell_type": "code",
      "metadata": {
        "id": "1JtZrIvzxSgR",
        "colab_type": "code",
        "colab": {}
      },
      "source": [
        "movies_df.shape"
      ],
      "execution_count": 0,
      "outputs": []
    },
    {
      "cell_type": "markdown",
      "metadata": {
        "id": "GEnrnPPMxSgU",
        "colab_type": "text"
      },
      "source": [
        "Note that `.shape` has no parentheses and is a simple tuple of format (rows, columns). So we have **1000 rows** and **11 columns** in our movies DataFrame.\n",
        "\n",
        "You'll be going to `.shape` a lot when cleaning and transforming data. For example, you might filter some rows based on some criteria and then want to know quickly how many rows were removed."
      ]
    },
    {
      "cell_type": "markdown",
      "metadata": {
        "id": "5QEp5TDoxSgU",
        "colab_type": "text"
      },
      "source": [
        "### Handling duplicates"
      ]
    },
    {
      "cell_type": "markdown",
      "metadata": {
        "id": "NrKFauefxSgV",
        "colab_type": "text"
      },
      "source": [
        "This dataset does not have duplicate rows, but it is always important to verify you aren't aggregating duplicate rows. \n",
        "\n",
        "To demonstrate, let's simply just double up our movies DataFrame by appending it to itself:"
      ]
    },
    {
      "cell_type": "code",
      "metadata": {
        "id": "B86erALbxSgV",
        "colab_type": "code",
        "colab": {}
      },
      "source": [
        "temp_df = movies_df.append(movies_df)\n",
        "\n",
        "temp_df.shape"
      ],
      "execution_count": 0,
      "outputs": []
    },
    {
      "cell_type": "markdown",
      "metadata": {
        "id": "ciWThC8RxSgY",
        "colab_type": "text"
      },
      "source": [
        "Using `append()` will return a copy without affecting the original DataFrame. We are capturing this copy in `temp` so we aren't working with the real data.\n",
        "\n",
        "Notice call `.shape` quickly proves our DataFrame rows have doubled.\n",
        "\n",
        "Now we can try dropping duplicates:"
      ]
    },
    {
      "cell_type": "code",
      "metadata": {
        "id": "acqtsSKAxSgZ",
        "colab_type": "code",
        "colab": {}
      },
      "source": [
        "temp_df = temp_df.drop_duplicates()\n",
        "\n",
        "temp_df.shape"
      ],
      "execution_count": 0,
      "outputs": []
    },
    {
      "cell_type": "markdown",
      "metadata": {
        "id": "V02fY3o9xSgc",
        "colab_type": "text"
      },
      "source": [
        "Just like `append()`, the `drop_duplicates()` method will also return a copy of your DataFrame, but this time with duplicates removed. Calling `.shape` confirms we're back to the 1000 rows of our original dataset.\n",
        "\n",
        "It's a little verbose to keep assigning DataFrames to the same variable like in this example. For this reason, pandas has the `inplace` keyword argument on many of its methods. Using `inplace=True` will modify the DataFrame object in place:"
      ]
    },
    {
      "cell_type": "code",
      "metadata": {
        "id": "9yuAZLeXxSge",
        "colab_type": "code",
        "colab": {}
      },
      "source": [
        "temp_df.drop_duplicates(inplace=True)"
      ],
      "execution_count": 0,
      "outputs": []
    },
    {
      "cell_type": "markdown",
      "metadata": {
        "id": "FNHnmFfexSgg",
        "colab_type": "text"
      },
      "source": [
        "Now our `temp_df` *will* have the transformed data automatically. \n",
        "\n",
        "Another important argument for `drop_duplicates()` is `keep`, which has three possible options:\n",
        "\n",
        "* `first`: (default) Drop duplicates except for the first occurrence.\n",
        "* `last`: Drop duplicates except for the last occurrence.\n",
        "* `False`: Drop all duplicates.\n",
        "\n",
        "Since we didn't define the `keep` arugment in the previous example it was defaulted to `first`. This means that if two rows are the same pandas will drop the second row and keep the first row. Using `last` has the opposite effect: the first row is dropped.\n",
        "\n",
        "`False`, on the other hand, will drop all duplicates. If two rows are the same then both will be dropped. Watch what happens to `temp_df`:"
      ]
    },
    {
      "cell_type": "code",
      "metadata": {
        "id": "-l48hZwBxSgg",
        "colab_type": "code",
        "colab": {}
      },
      "source": [
        "# Duplicate 'movies_df' and save the new dataframe as 'temp_df'\n",
        "# Drop ALL duplicates of 'temp_df' and overwrite it with the new dataframe\n",
        "# Output shape of 'temp_df'\n",
        "\n",
        "### START CODE HERE ### (approx. 3 lines of code)\n",
        "\n",
        "\n",
        "\n",
        "### END CODE HERE ###"
      ],
      "execution_count": 0,
      "outputs": []
    },
    {
      "cell_type": "markdown",
      "metadata": {
        "id": "z3paAIqDa-vg",
        "colab_type": "text"
      },
      "source": [
        "Expected Output:\n",
        "\n",
        "(0, 11)"
      ]
    },
    {
      "cell_type": "markdown",
      "metadata": {
        "id": "cnRaB5QfxSgj",
        "colab_type": "text"
      },
      "source": [
        "Since all rows were duplicates, `keep=False` dropped them all resulting in zero rows being left over. If you're wondering why you would want to do this, one reason is that it allows you to locate all duplicates in your dataset. When conditional selections are shown below you'll see how to do that."
      ]
    },
    {
      "cell_type": "markdown",
      "metadata": {
        "id": "Gt16aoYCxSgj",
        "colab_type": "text"
      },
      "source": [
        "### Column cleanup\n",
        "\n",
        "Many times datasets will have verbose column names with symbols, upper and lowercase words, spaces, and typos. To make selecting data by column name easier we can spend a little time cleaning up their names.\n",
        "\n",
        "Here's how to print the column names of our dataset:"
      ]
    },
    {
      "cell_type": "code",
      "metadata": {
        "id": "gQ8_w8htxSgk",
        "colab_type": "code",
        "colab": {}
      },
      "source": [
        "movies_df.columns"
      ],
      "execution_count": 0,
      "outputs": []
    },
    {
      "cell_type": "markdown",
      "metadata": {
        "id": "fllUEl1hxSgn",
        "colab_type": "text"
      },
      "source": [
        "Not only does `.columns` come in handy if you want to rename columns by allowing for simple copy and paste, it's also useful if you need to understand why you are receiving a `Key Error` when selecting data by column.\n",
        "\n",
        "We can use the `.rename()` method to rename certain or all columns via a `dict`. We don't want parentheses, so let's rename those:"
      ]
    },
    {
      "cell_type": "code",
      "metadata": {
        "id": "Xqkb_QevxSgn",
        "colab_type": "code",
        "colab": {}
      },
      "source": [
        "movies_df.rename(columns={\n",
        "        'Runtime (Minutes)': 'Runtime', \n",
        "        'Revenue (Millions)': 'Revenue_millions'\n",
        "    }, inplace=True)\n",
        "\n",
        "\n",
        "movies_df.columns"
      ],
      "execution_count": 0,
      "outputs": []
    },
    {
      "cell_type": "markdown",
      "metadata": {
        "id": "xT8kQCu_xSgr",
        "colab_type": "text"
      },
      "source": [
        "Excellent. But what if we want to lowercase all names? Instead of using `.rename()` we could also set a list of names to the columns like so:"
      ]
    },
    {
      "cell_type": "code",
      "metadata": {
        "id": "gQlYFx_wxSgr",
        "colab_type": "code",
        "colab": {}
      },
      "source": [
        "movies_df.columns = ['rank', 'genre', 'description', 'director', 'actors', 'year', 'runtime', \n",
        "                     'rating', 'votes', 'revenue_millions', 'metascore']\n",
        "\n",
        "\n",
        "movies_df.columns"
      ],
      "execution_count": 0,
      "outputs": []
    },
    {
      "cell_type": "markdown",
      "metadata": {
        "id": "zfvEwHi5xSgw",
        "colab_type": "text"
      },
      "source": [
        "But that's too much work. Instead of just renaming each column manually we can do a list comprehension:"
      ]
    },
    {
      "cell_type": "code",
      "metadata": {
        "id": "kp7UDFV3xSgw",
        "colab_type": "code",
        "colab": {}
      },
      "source": [
        "movies_df.columns = [col.lower() for col in movies_df]\n",
        "#can change \"col\" ==> [x.lower() for x in movies_df]\n",
        "\n",
        "movies_df.columns"
      ],
      "execution_count": 0,
      "outputs": []
    },
    {
      "cell_type": "markdown",
      "metadata": {
        "id": "bb_PPPw_xSgy",
        "colab_type": "text"
      },
      "source": [
        "`list` (and `dict`) comprehensions come in handy a lot when working with pandas and data in general.\n",
        "\n",
        "It's a good idea to lowercase, remove special characters, and replace spaces with underscores if you'll be working with a dataset for some time."
      ]
    },
    {
      "cell_type": "markdown",
      "metadata": {
        "id": "IdwWU1aCxSgz",
        "colab_type": "text"
      },
      "source": [
        "### How to work with missing values\n",
        "\n",
        "When exploring data, you’ll most likely encounter missing or null values, which are essentially placeholders for non-existent values. Most commonly you'll see Python's `None` or NumPy's `np.nan`, each of which are handled differently in some situations.\n",
        "\n",
        "There are two options in dealing with nulls: \n",
        "\n",
        "1. Get rid of rows or columns with nulls\n",
        "2. Replace nulls with non-null values, a technique known as **imputation**\n",
        "\n",
        "Let's calculate to total number of nulls in each column of our dataset. The first step is to check which cells in our DataFrame are null:"
      ]
    },
    {
      "cell_type": "code",
      "metadata": {
        "id": "It--gEMexSg0",
        "colab_type": "code",
        "colab": {}
      },
      "source": [
        "movies_df.isnull()"
      ],
      "execution_count": 0,
      "outputs": []
    },
    {
      "cell_type": "markdown",
      "metadata": {
        "id": "fGhtH5olxSg3",
        "colab_type": "text"
      },
      "source": [
        "Notice `isnull()` returns a DataFrame where each cell is either True or False depending on that cell's null status.\n",
        "\n",
        "To count the number of nulls in each column we use an aggregate function for summing: "
      ]
    },
    {
      "cell_type": "code",
      "metadata": {
        "id": "wMr7IoSGxSg3",
        "colab_type": "code",
        "colab": {}
      },
      "source": [
        "movies_df.isnull().sum()"
      ],
      "execution_count": 0,
      "outputs": []
    },
    {
      "cell_type": "markdown",
      "metadata": {
        "id": "wV67eEONxSg6",
        "colab_type": "text"
      },
      "source": [
        "`.isnull()` just by iteself isn't very useful, and is usually used in conjunction with other methods, like `sum()`.\n",
        "\n",
        "We can see now that our data has **128** missing values for `revenue_millions` and **64** missing values for `metascore`."
      ]
    },
    {
      "cell_type": "markdown",
      "metadata": {
        "id": "-YY9ZxrwxSg6",
        "colab_type": "text"
      },
      "source": [
        "#### Removing null values\n",
        "\n",
        "Data Scientists and Analysts regularly face the dilemma of dropping or imputing null values, and is a decision that requires intimate knowledge of your data and its context. Overall, removing null data is only suggested if you have a small amount of missing data.\n",
        "\n",
        "Remove nulls is pretty simple:"
      ]
    },
    {
      "cell_type": "code",
      "metadata": {
        "id": "YyBO9UOUxSg7",
        "colab_type": "code",
        "colab": {}
      },
      "source": [
        "movies_df.dropna()"
      ],
      "execution_count": 0,
      "outputs": []
    },
    {
      "cell_type": "markdown",
      "metadata": {
        "id": "VSh6MiEhxSg-",
        "colab_type": "text"
      },
      "source": [
        "This operation will delete any **row** with at least a single null value, but it will return a new DataFrame without altering the original one. You could specify `inplace=True` in this method as well.\n",
        "\n",
        "So in the case of our dataset, this operation would remove 128 rows where `revenue_millions` is null and 64 rows where `metascore` is null. This obviously seems like a waste since there's perfectly good data in the other columns of those dropped rows. That's why we'll look at imputation next.\n",
        "\n",
        "Other than just dropping rows, you can also drop columns with null values by setting `axis=1`:"
      ]
    },
    {
      "cell_type": "code",
      "metadata": {
        "id": "dgrPPHNAxSg_",
        "colab_type": "code",
        "colab": {}
      },
      "source": [
        "movies_df.dropna(axis=1)"
      ],
      "execution_count": 0,
      "outputs": []
    },
    {
      "cell_type": "markdown",
      "metadata": {
        "id": "Y_ka9bAQxShB",
        "colab_type": "text"
      },
      "source": [
        "In our dataset, this operation would drop the `revenue_millions` and `metascore` columns.\n",
        "\n",
        "**Intuition side note**: What's with this `axis=1` parameter?\n",
        "\n",
        "It's not immediately obvious where `axis` comes from and why you need it to be 1 for it to affect columns. To see why, just look at the `.shape` output:"
      ]
    },
    {
      "cell_type": "code",
      "metadata": {
        "id": "oUgQWxhSxShB",
        "colab_type": "code",
        "colab": {}
      },
      "source": [
        "movies_df.shape"
      ],
      "execution_count": 0,
      "outputs": []
    },
    {
      "cell_type": "markdown",
      "metadata": {
        "id": "-oVOXgfQxShG",
        "colab_type": "text"
      },
      "source": [
        "As we learned above, this is a tuple that represents the shape of the DataFrame, i.e. 1000 rows and 11 columns. Note that the *rows* are at index zero of this tuple and *columns* are at **index one** of this tuple. This is why `axis=1` affects columns. This comes from NumPy, and is a great example of why learning NumPy is worth your time."
      ]
    },
    {
      "cell_type": "markdown",
      "metadata": {
        "id": "Iy3s8sykxShG",
        "colab_type": "text"
      },
      "source": [
        "#### Imputation\n",
        "\n",
        "Imputation is a conventional feature engineering technique used to keep valuable data that have null values. \n",
        "\n",
        "There may be instances where dropping every row with a null value removes too big a chunk from your dataset, so instead we can impute that null with another value, usually the **mean** or the **median** of that column. \n",
        "\n",
        "Let's look at imputing the missing values in the `revenue_millions` column. First we'll extract that column into its own variable:"
      ]
    },
    {
      "cell_type": "code",
      "metadata": {
        "id": "yrP__iomxShH",
        "colab_type": "code",
        "colab": {}
      },
      "source": [
        "revenue = movies_df['revenue_millions']"
      ],
      "execution_count": 0,
      "outputs": []
    },
    {
      "cell_type": "markdown",
      "metadata": {
        "id": "k2AycxyKxShI",
        "colab_type": "text"
      },
      "source": [
        "Using square brackets is the general way we select columns in a DataFrame. \n",
        "\n",
        "If you remember back to when we created DataFrames from scratch, the keys of the `dict` ended up as column names. Now when we select columns of a DataFrame, we use brackets just like if we were accessing a Python dictionary. \n",
        "\n",
        "`revenue` now contains a Series:"
      ]
    },
    {
      "cell_type": "code",
      "metadata": {
        "id": "qZXCBG4ZxShJ",
        "colab_type": "code",
        "colab": {}
      },
      "source": [
        "revenue.head()"
      ],
      "execution_count": 0,
      "outputs": []
    },
    {
      "cell_type": "markdown",
      "metadata": {
        "id": "ljvhOZRrxShL",
        "colab_type": "text"
      },
      "source": [
        "Slightly different formatting than a DataFrame, but we still have our `Title` index. \n",
        "\n",
        "We'll impute the missing values of revenue using the mean. With the mean, let's fill the nulls using fillna(). By using `inplace=True` we can actually affect the original `movies_df`"
      ]
    },
    {
      "cell_type": "code",
      "metadata": {
        "id": "hTM1kBmkb9c_",
        "colab_type": "code",
        "colab": {}
      },
      "source": [
        "# Fill null values of revenue with its mean and use inplace to ovewrite revenue\n",
        "# Note htat the mean of revenue is \"revenue.mean\"\n",
        "# Check if the sum of null values for each column is 0\n",
        "\n",
        "### START CODE HERE ### (aprox. 2 lines of code)\n",
        "\n",
        "\n",
        "### END CODE HERE ###"
      ],
      "execution_count": 0,
      "outputs": []
    },
    {
      "cell_type": "markdown",
      "metadata": {
        "id": "kjZ70DLebaf0",
        "colab_type": "text"
      },
      "source": [
        "Expected Output:\n",
        "\n",
        "![alt text](https://github.com/alitarraf/Data-Science-Training/blob/master/assets/Ex4.png?raw=true)"
      ]
    },
    {
      "cell_type": "markdown",
      "metadata": {
        "id": "yxevktP-xShU",
        "colab_type": "text"
      },
      "source": [
        "Imputing an entire column with the same value like this is a basic example. It would be a better idea to try a more granular imputation by Genre or Director. \n",
        "\n",
        "For example, you would find the mean of the revenue generated in each genre individually and impute the nulls in each genre with that genre's mean.\n",
        "\n",
        "Let's now look at more ways to examine and understand the dataset."
      ]
    },
    {
      "cell_type": "markdown",
      "metadata": {
        "id": "kvU2yy5AxShU",
        "colab_type": "text"
      },
      "source": [
        "### Understanding your variables"
      ]
    },
    {
      "cell_type": "markdown",
      "metadata": {
        "id": "NLnQrFmwxShV",
        "colab_type": "text"
      },
      "source": [
        "Using `describe()` on an entire DataFrame we can get a summary of the distribution of continuous variables:"
      ]
    },
    {
      "cell_type": "code",
      "metadata": {
        "id": "mW5UpAG_xShV",
        "colab_type": "code",
        "colab": {}
      },
      "source": [
        "movies_df.describe()"
      ],
      "execution_count": 0,
      "outputs": []
    },
    {
      "cell_type": "markdown",
      "metadata": {
        "id": "EEYvHzswxShY",
        "colab_type": "text"
      },
      "source": [
        "Understanding which numbers are continuous also comes in handy when thinking about the type of plot to use to represent your data visually. \n",
        "\n",
        "`.describe()` can also be used on a categorical variable to get the count of rows, unique count of categories, top category, and freq of top category:"
      ]
    },
    {
      "cell_type": "code",
      "metadata": {
        "id": "YqtgADn_xShY",
        "colab_type": "code",
        "colab": {}
      },
      "source": [
        "movies_df['genre'].describe()"
      ],
      "execution_count": 0,
      "outputs": []
    },
    {
      "cell_type": "markdown",
      "metadata": {
        "id": "HX6oceWhxShb",
        "colab_type": "text"
      },
      "source": [
        "This tells us that the genre column has 207 unique values, the top value is Action/Adventure/Sci-Fi, which shows up 50 times (freq).\n",
        "\n",
        "`.value_counts()` can tell us the frequency of all values in a column:"
      ]
    },
    {
      "cell_type": "code",
      "metadata": {
        "id": "ALvhTIbNxShd",
        "colab_type": "code",
        "colab": {}
      },
      "source": [
        "movies_df['genre'].value_counts().head(10)"
      ],
      "execution_count": 0,
      "outputs": []
    },
    {
      "cell_type": "markdown",
      "metadata": {
        "id": "FK9LJ1fqxShf",
        "colab_type": "text"
      },
      "source": [
        "#### Relationships between continuous variables"
      ]
    },
    {
      "cell_type": "markdown",
      "metadata": {
        "id": "3hJ7tLoIxShf",
        "colab_type": "text"
      },
      "source": [
        "By using the correlation method `.corr()` we can generate the relationship between each continuous variable:"
      ]
    },
    {
      "cell_type": "code",
      "metadata": {
        "id": "gdcVybXwxShg",
        "colab_type": "code",
        "colab": {}
      },
      "source": [
        "movies_df.corr()"
      ],
      "execution_count": 0,
      "outputs": []
    },
    {
      "cell_type": "markdown",
      "metadata": {
        "id": "d7KuzBbjxShj",
        "colab_type": "text"
      },
      "source": [
        "Correlation tables are a numerical representation of the bivariate relationships in the dataset. \n",
        "\n",
        "Positive numbers indicate a positive correlation — one goes up the other goes up — and negative numbers represent an inverse correlation — one goes up the other goes down. 1.0 indicates a perfect correlation. \n",
        "\n",
        "So looking in the first row, first column we see `rank` has a perfect correlation with itself, which is obvious. On the other hand, the correlation between `votes` and `revenue_millions` is 0.6. A little more interesting.\n",
        "\n",
        "Examining bivariate relationships comes in handy when you have an outcome or dependent variable in mind and would like to see the features most correlated to the increase or decrease of the outcome. You can visually represent bivariate relationships with scatterplots (seen below in the plotting section). \n",
        "\n",
        "For a deeper look into data summarizations check out [Essential Statistics for Data Science](https://www.learndatasci.com/tutorials/data-science-statistics-using-python/).\n",
        "\n",
        "Let's now look more at manipulating DataFrames."
      ]
    },
    {
      "cell_type": "markdown",
      "metadata": {
        "id": "l93cYp7bxShj",
        "colab_type": "text"
      },
      "source": [
        "### DataFrame slicing, selecting, extracting\n",
        "\n",
        "Up until now we've focused on some basic summaries of our data. We've learned about simple column extraction using single brackets, and we imputed null values in a column using `fillna()`. Below are the other methods of slicing, selecting, and extracting you'll need to use constantly.\n",
        "\n",
        "It's important to note that, although many methods are the same, DataFrames and Series have different attributes, so you'll need be sure to know which type you are working with or else you will receive attribute errors. \n",
        "\n",
        "Let's look at working with columns first."
      ]
    },
    {
      "cell_type": "markdown",
      "metadata": {
        "id": "lLW628TfxShk",
        "colab_type": "text"
      },
      "source": [
        "#### By column\n",
        "\n",
        "You already saw how to extract a column using square brackets like this:"
      ]
    },
    {
      "cell_type": "code",
      "metadata": {
        "id": "03T5IIbXxShk",
        "colab_type": "code",
        "colab": {}
      },
      "source": [
        "genre_col = movies_df['genre']\n",
        "\n",
        "type(genre_col)"
      ],
      "execution_count": 0,
      "outputs": []
    },
    {
      "cell_type": "markdown",
      "metadata": {
        "id": "Q9Gv4FW5xShm",
        "colab_type": "text"
      },
      "source": [
        "This will return a *Series*. To extract a column as a *DataFrame*, you need to pass a list of column names. In our case that's just a single column:"
      ]
    },
    {
      "cell_type": "code",
      "metadata": {
        "id": "a_VRBtMNxShm",
        "colab_type": "code",
        "colab": {}
      },
      "source": [
        "genre_col = movies_df[['genre']]\n",
        "\n",
        "type(genre_col)"
      ],
      "execution_count": 0,
      "outputs": []
    },
    {
      "cell_type": "markdown",
      "metadata": {
        "id": "3199NxXKxSho",
        "colab_type": "text"
      },
      "source": [
        "Since it's just a list, adding another column name is easy:"
      ]
    },
    {
      "cell_type": "code",
      "metadata": {
        "id": "FCOHW2NqxShp",
        "colab_type": "code",
        "colab": {}
      },
      "source": [
        "subset = movies_df[['genre', 'rating']]\n",
        "\n",
        "subset.head()"
      ],
      "execution_count": 0,
      "outputs": []
    },
    {
      "cell_type": "markdown",
      "metadata": {
        "id": "8k6dJO5gxShr",
        "colab_type": "text"
      },
      "source": [
        "Now we'll look at getting data by rows."
      ]
    },
    {
      "cell_type": "markdown",
      "metadata": {
        "id": "6wXhk2VFxShs",
        "colab_type": "text"
      },
      "source": [
        "#### By rows"
      ]
    },
    {
      "cell_type": "markdown",
      "metadata": {
        "id": "Fnzz8wpnxShs",
        "colab_type": "text"
      },
      "source": [
        "For rows, we have two options: \n",
        "\n",
        "- `.loc` - **loc**ates by name\n",
        "- `.iloc`- **loc**ates by numerical **i**ndex\n",
        "\n",
        "Remember that we are still indexed by movie Title, so to use `.loc` we give it the Title of a movie:"
      ]
    },
    {
      "cell_type": "code",
      "metadata": {
        "id": "xsOqEX5LxShs",
        "colab_type": "code",
        "colab": {}
      },
      "source": [
        "prom = movies_df.loc[\"Prometheus\"]\n",
        "\n",
        "prom"
      ],
      "execution_count": 0,
      "outputs": []
    },
    {
      "cell_type": "markdown",
      "metadata": {
        "id": "CnzrVqaKxShv",
        "colab_type": "text"
      },
      "source": [
        "On the other hand, with `iloc` we give it the numerical index of Prometheus:"
      ]
    },
    {
      "cell_type": "code",
      "metadata": {
        "id": "e3TxGHUUxShw",
        "colab_type": "code",
        "colab": {}
      },
      "source": [
        "prom = movies_df.iloc[1]"
      ],
      "execution_count": 0,
      "outputs": []
    },
    {
      "cell_type": "markdown",
      "metadata": {
        "id": "C4Utwh9sxShy",
        "colab_type": "text"
      },
      "source": [
        "`loc` and `iloc` can be thought of as similar to Python `list` slicing. To show this even further, let's select multiple rows.\n",
        "\n",
        "How would you do it with a list? In Python, just slice with brackets like `example_list[1:4]`. It's works the same way in pandas:"
      ]
    },
    {
      "cell_type": "code",
      "metadata": {
        "id": "WgWEOycTxShy",
        "colab_type": "code",
        "colab": {}
      },
      "source": [
        "movie_subset = movies_df.loc['Prometheus':'Sing']\n",
        "\n",
        "movie_subset = movies_df.iloc[1:4]\n",
        "\n",
        "movie_subset"
      ],
      "execution_count": 0,
      "outputs": []
    },
    {
      "cell_type": "markdown",
      "metadata": {
        "id": "CNfi2eX0sdB-",
        "colab_type": "text"
      },
      "source": [
        "One important distinction between using `.loc` and `.iloc` to select multiple rows is that `.loc` includes the movie *Sing* in the result, but when using `.iloc` we're getting rows 1:4 but the movie at index 4 (*Suicide Squad*) is not included. \n",
        "\n",
        "Slicing with `.iloc` follows the same rules as slicing with lists, the object at the index at the end is not included."
      ]
    },
    {
      "cell_type": "markdown",
      "metadata": {
        "id": "905evl_xxSh0",
        "colab_type": "text"
      },
      "source": [
        "#### Conditional selections\n",
        "We’ve gone over how to select columns and rows, but what if we want to make a conditional selection? \n",
        "\n",
        "For example, what if we want to filter our movies DataFrame to show only films directed by Ridley Scott or films with a rating greater than or equal to 8.0?\n",
        "\n",
        "To do that, we take a column from the DataFrame and apply a Boolean condition to it. "
      ]
    },
    {
      "cell_type": "code",
      "metadata": {
        "id": "xgvL5q_7xSh1",
        "colab_type": "code",
        "colab": {}
      },
      "source": [
        "# We want to know if the director is Ridley Scott or not.\n",
        "#Show the result of the first 5 movie titles, name it \"condition\"\n",
        "\n",
        "### START CODE HERE ### (aprox. 1 lines of code)\n",
        "\n",
        "### END CODE HERE ###\n"
      ],
      "execution_count": 0,
      "outputs": []
    },
    {
      "cell_type": "markdown",
      "metadata": {
        "id": "CwVqvQhkb80R",
        "colab_type": "text"
      },
      "source": [
        "Expected Output:\n",
        "\n",
        "![alt text](https://github.com/alitarraf/Data-Science-Training/blob/master/assets/Ex5.png?raw=true)"
      ]
    },
    {
      "cell_type": "markdown",
      "metadata": {
        "id": "qF4mNSYrxSh2",
        "colab_type": "text"
      },
      "source": [
        "Similar to `isnull()`, this returns a Series of True and False values: True for films directed by Ridley Scott and False for ones not directed by him. \n"
      ]
    },
    {
      "cell_type": "code",
      "metadata": {
        "id": "gWzDewltxSh3",
        "colab_type": "code",
        "colab": {}
      },
      "source": [
        "#We want to filter out all movies not directed by Ridley Scott, in other words,\n",
        "#we don’t want the False films.\n",
        "#Show the result of the first 5 movie titles\n",
        "\n",
        "### START CODE HERE ### (aprox. 1 lines of code)\n",
        "\n",
        "### END CODE HERE ###\n"
      ],
      "execution_count": 0,
      "outputs": []
    },
    {
      "cell_type": "markdown",
      "metadata": {
        "id": "72niGJQ1cX3Y",
        "colab_type": "text"
      },
      "source": [
        "Expected Output:\n",
        "\n",
        "![alt text](https://github.com/alitarraf/Data-Science-Training/blob/master/assets/Ex6.png?raw=true)"
      ]
    },
    {
      "cell_type": "markdown",
      "metadata": {
        "id": "nGOx9dATxSh5",
        "colab_type": "text"
      },
      "source": [
        "You can get used to looking at these conditionals by reading it like: \n",
        "\n",
        "> Select movies_df where movies_df director equals Ridley Scott\n",
        "\n",
        "Let's look at conditional selections using numerical values by filtering the DataFrame by ratings:"
      ]
    },
    {
      "cell_type": "code",
      "metadata": {
        "id": "e0nepv7YxSh6",
        "colab_type": "code",
        "colab": {}
      },
      "source": [
        "movies_df[movies_df['rating'] >= 8.6].head(3)"
      ],
      "execution_count": 0,
      "outputs": []
    },
    {
      "cell_type": "markdown",
      "metadata": {
        "id": "VACF7JJBxSh8",
        "colab_type": "text"
      },
      "source": [
        "We can make some richer conditionals by using logical operators `|` for \"or\" and `&` for \"and\".\n",
        "\n",
        "Let's filter the the DataFrame to show only movies by Christopher Nolan OR Ridley Scott:"
      ]
    },
    {
      "cell_type": "code",
      "metadata": {
        "id": "wvg73pxgxSh9",
        "colab_type": "code",
        "colab": {}
      },
      "source": [
        "movies_df[(movies_df['director'] == 'Christopher Nolan') | (movies_df['director'] == 'Ridley Scott')].head()"
      ],
      "execution_count": 0,
      "outputs": []
    },
    {
      "cell_type": "markdown",
      "metadata": {
        "id": "3pGRESaGxSh_",
        "colab_type": "text"
      },
      "source": [
        "We need to make sure to group evaluations with parentheses so Python knows how to evaluate the conditional.\n",
        "\n",
        "Using the `isin()` method we could make this more concise though:"
      ]
    },
    {
      "cell_type": "code",
      "metadata": {
        "id": "GYGL8mzBxSh_",
        "colab_type": "code",
        "colab": {}
      },
      "source": [
        "movies_df[movies_df['director'].isin(['Christopher Nolan', 'Ridley Scott'])].head()"
      ],
      "execution_count": 0,
      "outputs": []
    },
    {
      "cell_type": "markdown",
      "metadata": {
        "id": "KMJmKDP6xSiD",
        "colab_type": "text"
      },
      "source": [
        "Let's say we want all movies that were released between 2005 and 2010, have a rating above 8.0, but made below the 25th percentile in revenue.\n",
        "\n",
        "Here's how we could do all of that:"
      ]
    },
    {
      "cell_type": "markdown",
      "metadata": {
        "id": "C3cv4IqIxSiH",
        "colab_type": "text"
      },
      "source": [
        "## Applying functions\n",
        "\n",
        "It is possible to iterate over a DataFrame or Series as you would with a list, but doing so — especially on large datasets — is very slow.\n",
        "\n",
        "An efficient alternative is to `apply()` a function to the dataset. For example, we could use a function to convert movies with an 8.0 or greater to a string value of \"good\" and the rest to \"bad\" and use this transformed values to create a new column.\n",
        "\n",
        "First we would create a function that, when given a rating, determines if it's good or bad:"
      ]
    },
    {
      "cell_type": "code",
      "metadata": {
        "id": "NhAT_kKqxSiH",
        "colab_type": "code",
        "colab": {}
      },
      "source": [
        "def rating_function(x):\n",
        "    if x >= 8.0:\n",
        "        return \"good\"\n",
        "    else:\n",
        "        return \"bad\""
      ],
      "execution_count": 0,
      "outputs": []
    },
    {
      "cell_type": "markdown",
      "metadata": {
        "id": "wfE8n1MRxSiI",
        "colab_type": "text"
      },
      "source": [
        "Now we want to send the entire rating column through this function, which is what `apply()` does:"
      ]
    },
    {
      "cell_type": "code",
      "metadata": {
        "id": "2la4r2ZKxSiI",
        "colab_type": "code",
        "colab": {}
      },
      "source": [
        "movies_df[\"rating_category\"] = movies_df[\"rating\"].apply(rating_function)\n",
        "\n",
        "movies_df.head(2)"
      ],
      "execution_count": 0,
      "outputs": []
    },
    {
      "cell_type": "markdown",
      "metadata": {
        "id": "m3OI6LqxxSiK",
        "colab_type": "text"
      },
      "source": [
        "The `.apply()` method passes every value in the `rating` column through the `rating_function` and then returns a new Series. This Series is then assigned to a new column called `rating_category`.\n",
        "\n"
      ]
    },
    {
      "cell_type": "code",
      "metadata": {
        "id": "sEQMXPB72fi7",
        "colab_type": "code",
        "colab": {}
      },
      "source": [
        "# Let's see who will get it all right!\n",
        "# Using what you just learned, create a function named \"acceptance_function\" that takes rating category as an input\n",
        "# This function should return \"Accepted\" if the rating category is good and \"Rejected\" otherwise\n",
        "# Save the results in a new column with title \"decision\"\n",
        "# Show ONLY rows of rank 991 to 993 without using column \"rank\" ;)\n",
        "\n",
        "### START CODE HERE ### (aprox. 7 lines of code)\n",
        "\n",
        "\n",
        "\n",
        "\n",
        "\n",
        "\n",
        "\n",
        "\n",
        "### END CODE HERE ###"
      ],
      "execution_count": 0,
      "outputs": []
    },
    {
      "cell_type": "markdown",
      "metadata": {
        "id": "Q4YMVAK08qpw",
        "colab_type": "text"
      },
      "source": [
        "Expected Output:\n",
        "\n",
        "![alt text](https://github.com/alitarraf/Data-Science-Training/blob/master/assets/Ex7.PNG?raw=true)"
      ]
    },
    {
      "cell_type": "markdown",
      "metadata": {
        "id": "HMGPCyIS2bZF",
        "colab_type": "text"
      },
      "source": [
        "You can also use anonymous functions as well. This lambda function achieves the same result as `rating_function`:"
      ]
    },
    {
      "cell_type": "code",
      "metadata": {
        "id": "NYSa4HBQxSiK",
        "colab_type": "code",
        "colab": {}
      },
      "source": [
        "movies_df[\"rating_category\"] = movies_df[\"rating\"].apply(lambda x: 'good' if x >= 8.0 else 'bad')\n",
        "\n",
        "movies_df.head(2)"
      ],
      "execution_count": 0,
      "outputs": []
    },
    {
      "cell_type": "markdown",
      "metadata": {
        "id": "4MdK2J3dxSiM",
        "colab_type": "text"
      },
      "source": [
        "Overall, using `apply()` will be much faster than iterating manually over rows because pandas is utilizing vectorization.\n",
        "\n",
        "> Vectorization: a style of computer programming where operations are applied to whole arrays instead of individual elements —[Wikipedia](https://en.wikipedia.org/wiki/Vectorization)\n",
        "\n",
        "A good example of high usage of `apply()` is during natural language processing (NLP) work. You'll need to apply all sorts of text cleaning functions to strings to prepare for machine learning."
      ]
    },
    {
      "cell_type": "markdown",
      "metadata": {
        "id": "lf7IjfncxSid",
        "colab_type": "text"
      },
      "source": [
        "## Wrapping up\n",
        "\n",
        "Exploring, cleaning, transforming, and visualization data with pandas in Python is an essential skill in data science. Just cleaning wrangling data is 80% of your job as a Data Scientist. After a few projects and some practice, you should be very comfortable with most of the basics.\n",
        "\n",
        "To keep improving, view the [extensive tutorials](https://pandas.pydata.org/pandas-docs/stable/tutorials.html) offered by the official pandas docs, follow along with a few [Kaggle kernels](https://www.kaggle.com/kernels), and keep working on your own projects!"
      ]
    },
    {
      "cell_type": "markdown",
      "metadata": {
        "id": "37J26aUWxJ5U",
        "colab_type": "text"
      },
      "source": [
        "## References\n",
        "\n",
        "[Python Pandas Tutorial: A Complete Introduction for Beginners](https://www.learndatasci.com/tutorials/python-pandas-tutorial-complete-introduction-for-beginners/)"
      ]
    },
    {
      "cell_type": "code",
      "metadata": {
        "id": "1FDMpNL5o6Zx",
        "colab_type": "code",
        "colab": {}
      },
      "source": [
        ""
      ],
      "execution_count": 0,
      "outputs": []
    }
  ]
}