Lessons/Lesson17_Pandas-Subsetting-II.ipynb

{
 "cells": [
  {
   "cell_type": "markdown",
   "metadata": {
    "colab_type": "text",
    "id": "KSChitqaVpii"
   },
   "source": [
    "# Subsetting Pandas DataFrames II\n",
    "\n",
    "In the last lesson, you learned how to subset dataframes by entire rows or entire columns. Now we're going to learn how to do both at the same time!\n",
    "\n",
    "Let's read in the `tips` dataset again:"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "colab": {},
    "colab_type": "code",
    "id": "O7YFupf5YEUN"
   },
   "outputs": [],
   "source": [
    "# import the pandas package\n",
    "import pandas as pd\n",
    "# set the path\n",
    "path = 'https://raw.githubusercontent.com/GWC-DCMB/ClubCurriculum/master/'\n",
    "# load tips\n",
    "tips = pd.read_csv(path + 'SampleData/tips.csv')\n"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {
    "colab_type": "text",
    "id": "z9l9mAozXoR8"
   },
   "source": [
    "Take a look again at the beginning of the `tips` `DataFrame`:"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "colab": {
     "base_uri": "https://localhost:8080/",
     "height": 204
    },
    "colab_type": "code",
    "executionInfo": {
     "elapsed": 353,
     "status": "ok",
     "timestamp": 1563150749669,
     "user": {
      "displayName": "Kelly Sovacool",
      "photoUrl": "https://lh3.googleusercontent.com/-WQsOgn6cXFg/AAAAAAAAAAI/AAAAAAAAAVA/ol7X28qyOHc/s64/photo.jpg",
      "userId": "07259925140867310896"
     },
     "user_tz": 240
    },
    "id": "kB7LuRpvXtK9",
    "outputId": "c46f9245-7f00-4015-a2d0-9d3052be3c00"
   },
   "outputs": [],
   "source": [
    "# view the beginning of tips\n"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "First, let's recall how you would subset three columns, `total_bill`, `day`, and `time`. Let's save it to a variable called `subset1`."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "# subset the colums and save it to subset1\n",
    "\n"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Now, how would you subset rows 5 through 10 from the `subset1` dataframe? Let's save it to a variable called `subset2`."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "# subset rows 5 through 10 and save it to tips_subset_rows\n"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {
    "colab_type": "text",
    "id": "lmdNsRJIdSww"
   },
   "source": [
    "Now the `subset2` dataframe has just the rows 5 through 10 and three columns. We can even subset rows and columns in the same line of code, instead of doing it on multiple lines like we did above. Let's try combining both `iloc` and square brackets `[]` on one line:"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "colab": {
     "base_uri": "https://localhost:8080/",
     "height": 235
    },
    "colab_type": "code",
    "executionInfo": {
     "elapsed": 950,
     "status": "ok",
     "timestamp": 1563148693759,
     "user": {
      "displayName": "Kelly Sovacool",
      "photoUrl": "https://lh3.googleusercontent.com/-WQsOgn6cXFg/AAAAAAAAAAI/AAAAAAAAAVA/ol7X28qyOHc/s64/photo.jpg",
      "userId": "07259925140867310896"
     },
     "user_tz": 240
    },
    "id": "LmF5osHCdJ6w",
    "outputId": "e1b9103a-2f09-47c4-dc96-a351448e33c2"
   },
   "outputs": [],
   "source": [
    "# subset rows & columns at the same time\n"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "What do you notice about `subset3`? How does it compare to `subset2`?"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "# compare subset2 and subset3\n"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {
    "colab_type": "text",
    "id": "3bB-w1eOdoxD"
   },
   "source": [
    "Now you try! Subset rows 11 and 12 and columns `total_bill` and `tip` on one line of code:"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "colab": {
     "base_uri": "https://localhost:8080/",
     "height": 235
    },
    "colab_type": "code",
    "executionInfo": {
     "elapsed": 942,
     "status": "ok",
     "timestamp": 1563148693761,
     "user": {
      "displayName": "Kelly Sovacool",
      "photoUrl": "https://lh3.googleusercontent.com/-WQsOgn6cXFg/AAAAAAAAAAI/AAAAAAAAAVA/ol7X28qyOHc/s64/photo.jpg",
      "userId": "07259925140867310896"
     },
     "user_tz": 240
    },
    "id": "VFdqBEB7dU-f",
    "outputId": "25963cfe-8945-4c7f-9533-43098535906b"
   },
   "outputs": [],
   "source": [
    "# subset rows and columns\n"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {
    "colab_type": "text",
    "id": "ielBxuXYd_B6"
   },
   "source": [
    "Sometimes we don't know exactly which row(s) we want to subset ahead of time. What if we want to subset rows that have a certain value in the `time` column? We don't want to scroll through hundreds of rows to find them. The good news is: we don't have to! Let's use the `method` called `query`.  Inside the parentheses of `query` we'll enclose a statement in quotes with the name of the column and an expression."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "colab": {
     "base_uri": "https://localhost:8080/",
     "height": 1000
    },
    "colab_type": "code",
    "executionInfo": {
     "elapsed": 935,
     "status": "ok",
     "timestamp": 1563148693767,
     "user": {
      "displayName": "Kelly Sovacool",
      "photoUrl": "https://lh3.googleusercontent.com/-WQsOgn6cXFg/AAAAAAAAAAI/AAAAAAAAAVA/ol7X28qyOHc/s64/photo.jpg",
      "userId": "07259925140867310896"
     },
     "user_tz": 240
    },
    "id": "yi6NNYYrdztC",
    "outputId": "13049e5c-7a3e-4979-cd6c-c79c765cae9e"
   },
   "outputs": [],
   "source": []
  },
  {
   "cell_type": "markdown",
   "metadata": {
    "colab_type": "text",
    "id": "iSBA6Bx5fPN8"
   },
   "source": [
    "The above cell showed us all the rows where `time` is equal to \"Lunch\". We had to enclose \"Lunch\" in quotes above because it's not the name of a column, but a value within the `time` column.\n",
    "\n",
    "Now you try: subset the rows where the waitress is female and save it to a variable, `female`:"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "colab": {
     "base_uri": "https://localhost:8080/",
     "height": 204
    },
    "colab_type": "code",
    "executionInfo": {
     "elapsed": 1288,
     "status": "ok",
     "timestamp": 1563148694136,
     "user": {
      "displayName": "Kelly Sovacool",
      "photoUrl": "https://lh3.googleusercontent.com/-WQsOgn6cXFg/AAAAAAAAAAI/AAAAAAAAAVA/ol7X28qyOHc/s64/photo.jpg",
      "userId": "07259925140867310896"
     },
     "user_tz": 240
    },
    "id": "z56a4vGDecan",
    "outputId": "6480d6b4-544e-4feb-8a77-bf80eb2c2a7f"
   },
   "outputs": [],
   "source": [
    "# subset rows with a female waitress and save it to a variable\n",
    "\n",
    "\n",
    "# take a look at the beginning\n"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {
    "colab_type": "text",
    "id": "14Ne3-oifwkE"
   },
   "source": [
    "Now lets do the same for males. Subset the male waiter data and save it to a variable, `male`:"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "colab": {
     "base_uri": "https://localhost:8080/",
     "height": 204
    },
    "colab_type": "code",
    "executionInfo": {
     "elapsed": 1278,
     "status": "ok",
     "timestamp": 1563148694139,
     "user": {
      "displayName": "Kelly Sovacool",
      "photoUrl": "https://lh3.googleusercontent.com/-WQsOgn6cXFg/AAAAAAAAAAI/AAAAAAAAAVA/ol7X28qyOHc/s64/photo.jpg",
      "userId": "07259925140867310896"
     },
     "user_tz": 240
    },
    "id": "hdCwv4RJfvIO",
    "outputId": "49c47b00-28c6-45e8-b66d-b43b69ea56e4"
   },
   "outputs": [],
   "source": [
    "# subset the male waiters and save it\n",
    "\n",
    "\n",
    "# look at the beginning\n"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {
    "colab_type": "text",
    "id": "CLBfBmXSgoqX"
   },
   "source": [
    " How would you determine the number of male waiters in this `DataFrame`? Think back to the last lesson when we used the `len` function."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "colab": {
     "base_uri": "https://localhost:8080/",
     "height": 34
    },
    "colab_type": "code",
    "executionInfo": {
     "elapsed": 1266,
     "status": "ok",
     "timestamp": 1563148694140,
     "user": {
      "displayName": "Kelly Sovacool",
      "photoUrl": "https://lh3.googleusercontent.com/-WQsOgn6cXFg/AAAAAAAAAAI/AAAAAAAAAVA/ol7X28qyOHc/s64/photo.jpg",
      "userId": "07259925140867310896"
     },
     "user_tz": 240
    },
    "id": "vHeToUhgga_9",
    "outputId": "eeec74b8-13c5-4523-d547-ddf519a2879a"
   },
   "outputs": [],
   "source": [
    "# number of males\n"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {
    "colab_type": "text",
    "id": "J02LvYxqh4jb"
   },
   "source": [
    "How about the number of female waitreses?"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "colab": {
     "base_uri": "https://localhost:8080/",
     "height": 34
    },
    "colab_type": "code",
    "executionInfo": {
     "elapsed": 1254,
     "status": "ok",
     "timestamp": 1563148694142,
     "user": {
      "displayName": "Kelly Sovacool",
      "photoUrl": "https://lh3.googleusercontent.com/-WQsOgn6cXFg/AAAAAAAAAAI/AAAAAAAAAVA/ol7X28qyOHc/s64/photo.jpg",
      "userId": "07259925140867310896"
     },
     "user_tz": 240
    },
    "id": "2-yoiqYbhImF",
    "outputId": "777308df-4f61-47a6-e6fe-4c210149bc03"
   },
   "outputs": [],
   "source": [
    "# number of females\n"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {
    "colab_type": "text",
    "id": "53Tg-2phiKtS"
   },
   "source": [
    "We can use `query` on multiple columns at a time. Let's find out how many tables were served by a female waitress on a Sunday."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "colab": {
     "base_uri": "https://localhost:8080/",
     "height": 607
    },
    "colab_type": "code",
    "executionInfo": {
     "elapsed": 1250,
     "status": "ok",
     "timestamp": 1563148694149,
     "user": {
      "displayName": "Kelly Sovacool",
      "photoUrl": "https://lh3.googleusercontent.com/-WQsOgn6cXFg/AAAAAAAAAAI/AAAAAAAAAVA/ol7X28qyOHc/s64/photo.jpg",
      "userId": "07259925140867310896"
     },
     "user_tz": 240
    },
    "id": "hHv2O1R-h-Bw",
    "outputId": "3095be1e-2eee-4bce-c95b-6207bf8687b5"
   },
   "outputs": [],
   "source": []
  },
  {
   "cell_type": "markdown",
   "metadata": {
    "colab_type": "text",
    "id": "jnN9Z384isNT"
   },
   "source": [
    "We used the ampersand symbol (&) or the keyword `and` to chain together two statements inside the `query` function. Both statements have to be true for a row to be included.\n",
    "\n",
    "Besides checking whether values are equal using ==, we can also use greater than, less than, greater than or equal, etc. Try subsetting the rows where the bill is greater than $\\$15$ and the tip is less than $\\$2$:"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "colab": {
     "base_uri": "https://localhost:8080/",
     "height": 359
    },
    "colab_type": "code",
    "executionInfo": {
     "elapsed": 1248,
     "status": "ok",
     "timestamp": 1563148694158,
     "user": {
      "displayName": "Kelly Sovacool",
      "photoUrl": "https://lh3.googleusercontent.com/-WQsOgn6cXFg/AAAAAAAAAAI/AAAAAAAAAVA/ol7X28qyOHc/s64/photo.jpg",
      "userId": "07259925140867310896"
     },
     "user_tz": 240
    },
    "id": "G4nBn8bIi8q7",
    "outputId": "f813d993-80d1-45c4-da32-93304b41f2a4"
   },
   "outputs": [],
   "source": [
    "# subset by bill and tip\n"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {
    "colab_type": "text",
    "id": "zrUVL0jZmUeM"
   },
   "source": [
    "Instead of the ampersand (&) we can use the pipe (|) or the keyword `or` to represent a query where *one* of the two conditions must be fulfilled. Try subsetting where the bill is greater than $\\$15$ or the tip is greater than $\\$5$:"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "colab": {
     "base_uri": "https://localhost:8080/",
     "height": 1000
    },
    "colab_type": "code",
    "executionInfo": {
     "elapsed": 1244,
     "status": "ok",
     "timestamp": 1563148694164,
     "user": {
      "displayName": "Kelly Sovacool",
      "photoUrl": "https://lh3.googleusercontent.com/-WQsOgn6cXFg/AAAAAAAAAAI/AAAAAAAAAVA/ol7X28qyOHc/s64/photo.jpg",
      "userId": "07259925140867310896"
     },
     "user_tz": 240
    },
    "id": "EZknNe4tmkPA",
    "outputId": "cf9d1006-903c-4fb0-9892-201408840933"
   },
   "outputs": [],
   "source": [
    "# subset by bill or tip\n"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {
    "colab_type": "text",
    "id": "VXeb835BjQyi"
   },
   "source": [
    "In this lesson, you learned:\n",
    "\n",
    "- How to use `iloc` and square brackets `[]` at the same time.\n",
    "- How to use `query` to find rows where the column has a certain value."
   ]
  }
 ],
 "metadata": {
  "kernelspec": {
   "display_name": "Python 3",
   "language": "python",
   "name": "python3"
  },
  "language_info": {
   "codemirror_mode": {
    "name": "ipython",
    "version": 3
   },
   "file_extension": ".py",
   "mimetype": "text/x-python",
   "name": "python",
   "nbconvert_exporter": "python",
   "pygments_lexer": "ipython3",
   "version": "3.7.3"
  }
 },
 "nbformat": 4,
 "nbformat_minor": 4
}