5_spreadsheets_and_pandas.ipynb

{
 "cells": [
  {
   "cell_type": "markdown",
   "metadata": {
    "colab_type": "text",
    "id": "aGisXFdYMo0k"
   },
   "source": [
    "\n",
    "![alt text](https://s3-ap-south-1.amazonaws.com/av-blog-media/wp-content/uploads/2018/03/pandas.jpg)\n",
    "\n",
    "# Spreadsheets and Pandas\n",
    "\n",
    "- Python works well with speadsheets\n",
    " - using a library called Pandas\n",
    "- lots of built-in methods for doing many common tasks\n",
    " - data manipulation\n",
    " - data visualization\n",
    " - statistics\n",
    " - and much much more...\n",
    "\n",
    "- in Pandas a spreadsheet is called a DataFrame"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {
    "colab_type": "text",
    "id": "H6hx1OgtoQrl"
   },
   "source": [
    "## Getting some data\n",
    " - fake EQAO data\n",
    " - columns include:\n",
    "  - reading, writing, math, gender, IEP, school name"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "colab": {},
    "colab_type": "code",
    "id": "giFs3PWAn9U1"
   },
   "outputs": [],
   "source": [
    "import pandas as pd\n",
    "import numpy as np"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {
    "colab_type": "text",
    "id": "r40mBgy4pqf1"
   },
   "source": [
    "## Reading data into Python\n",
    "- In Python a spreadsheet is called a \"DataFrame\" as in other languages\n",
    "- we use \"df\" for short as a convention"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "colab": {},
    "colab_type": "code",
    "id": "GZ3icsmqpjJu"
   },
   "outputs": [],
   "source": [
    "df=pd.read_csv('sample_EQAO_data.csv')\n",
    "df.head()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {
    "colab_type": "text",
    "id": "k9HfqIgigrO-"
   },
   "source": [
    "## The number of rows in the data"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "colab": {
     "base_uri": "https://localhost:8080/",
     "height": 35
    },
    "colab_type": "code",
    "executionInfo": {
     "elapsed": 282,
     "status": "ok",
     "timestamp": 1533318928357,
     "user": {
      "displayName": "Allan Campopiano",
      "photoUrl": "//lh6.googleusercontent.com/-jajm12wFRU4/AAAAAAAAAAI/AAAAAAAAAws/b_aS-B759i4/s50-c-k-no/photo.jpg",
      "userId": "113962595821525423496"
     },
     "user_tz": 240
    },
    "id": "XL8cmQYBqzfW",
    "outputId": "74260a37-896e-4ce5-ebf1-68aa43191dba"
   },
   "outputs": [
    {
     "data": {
      "text/plain": [
       "2133"
      ]
     },
     "execution_count": 3,
     "metadata": {
      "tags": []
     },
     "output_type": "execute_result"
    }
   ],
   "source": [
    "len(df)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {
    "colab_type": "text",
    "id": "AEoOSaZBgyS-"
   },
   "source": [
    "## The top or bottom *N* rows"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "colab": {},
    "colab_type": "code",
    "id": "S76867eMSQIv"
   },
   "outputs": [],
   "source": [
    "df.head(5)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "colab": {},
    "colab_type": "code",
    "id": "AiVq2XnzqZHf"
   },
   "outputs": [],
   "source": [
    "df.head(10)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "colab": {},
    "colab_type": "code",
    "id": "nEjlMcahsoHJ"
   },
   "outputs": [],
   "source": [
    "df.tail(2)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {
    "colab_type": "text",
    "id": "3CJ606wUg86m"
   },
   "source": [
    "## Selecting columns"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "colab": {
     "base_uri": "https://localhost:8080/",
     "height": 92
    },
    "colab_type": "code",
    "executionInfo": {
     "elapsed": 379,
     "status": "ok",
     "timestamp": 1533319266122,
     "user": {
      "displayName": "Allan Campopiano",
      "photoUrl": "//lh6.googleusercontent.com/-jajm12wFRU4/AAAAAAAAAAI/AAAAAAAAAws/b_aS-B759i4/s50-c-k-no/photo.jpg",
      "userId": "113962595821525423496"
     },
     "user_tz": 240
    },
    "id": "Dc4W9Sw4k7gO",
    "outputId": "71cfb717-0e4f-485d-e215-41813565ee96"
   },
   "outputs": [
    {
     "data": {
      "text/plain": [
       "Index(['Student_number', 'SchoolName', 'reading_level', 'reading_raw_score',\n",
       "       'writing_level', 'writing_raw_score', 'math_level', 'meath_raw_score',\n",
       "       'Gender', 'IEP', 'Region', 'StudentName'],\n",
       "      dtype='object')"
      ]
     },
     "execution_count": 11,
     "metadata": {
      "tags": []
     },
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df.columns"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "colab": {
     "base_uri": "https://localhost:8080/",
     "height": 73
    },
    "colab_type": "code",
    "executionInfo": {
     "elapsed": 332,
     "status": "ok",
     "timestamp": 1533319301842,
     "user": {
      "displayName": "Allan Campopiano",
      "photoUrl": "//lh6.googleusercontent.com/-jajm12wFRU4/AAAAAAAAAAI/AAAAAAAAAws/b_aS-B759i4/s50-c-k-no/photo.jpg",
      "userId": "113962595821525423496"
     },
     "user_tz": 240
    },
    "id": "by8qM55G15EM",
    "outputId": "c797f3d7-f98e-4c94-a57d-51b22c0f67f6"
   },
   "outputs": [
    {
     "data": {
      "text/plain": [
       "2131    Oakville\n",
       "2132    Oakville\n",
       "Name: Region, dtype: object"
      ]
     },
     "execution_count": 12,
     "metadata": {
      "tags": []
     },
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df['Region'].tail(2)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "colab": {},
    "colab_type": "code",
    "id": "_tgaRIjBhFI-"
   },
   "outputs": [],
   "source": [
    "df[['Student_number', 'Region']]"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "colab": {},
    "colab_type": "code",
    "id": "faq0icWInb_A"
   },
   "outputs": [],
   "source": [
    "df['SchoolName'].unique()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "colab": {},
    "colab_type": "code",
    "id": "Sry2bqJTUImD"
   },
   "outputs": [],
   "source": [
    "len(df['SchoolName'].unique())"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "colab": {},
    "colab_type": "code",
    "id": "DowtahdNUnKj"
   },
   "outputs": [],
   "source": [
    "df['Gender'].unique()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {
    "colab_type": "text",
    "id": "ze7vToKPzWO7"
   },
   "source": [
    "## <font color=blue>How would you select reading_level, writing_level, and math_level from the DataFrame?</font>\n",
    "  \n",
    "```python\n",
    "A. df['reading_level'] + df['writing_level'] + df['math_level']\n",
    "B. df['reading', 'writing', 'math']\n",
    "C. df['reading_level', 'writing_level', 'math_level']\n",
    "D. df[['reading_level', 'writing_level', 'math_level']]\n",
    "```"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {
    "colab_type": "text",
    "id": "A9nT0_GfhjJA"
   },
   "source": [
    "## Basic aggregates"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "colab": {
     "base_uri": "https://localhost:8080/",
     "height": 35
    },
    "colab_type": "code",
    "executionInfo": {
     "elapsed": 247,
     "status": "ok",
     "timestamp": 1533319763764,
     "user": {
      "displayName": "Allan Campopiano",
      "photoUrl": "//lh6.googleusercontent.com/-jajm12wFRU4/AAAAAAAAAAI/AAAAAAAAAws/b_aS-B759i4/s50-c-k-no/photo.jpg",
      "userId": "113962595821525423496"
     },
     "user_tz": 240
    },
    "id": "EfO6YjnGR73g",
    "outputId": "d2cf0568-1126-4923-91fb-e4c9952cdbf0"
   },
   "outputs": [
    {
     "data": {
      "text/plain": [
       "201"
      ]
     },
     "execution_count": 19,
     "metadata": {
      "tags": []
     },
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df['IEP'].sum()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "colab": {},
    "colab_type": "code",
    "id": "eRQxa-AXq_Cm"
   },
   "outputs": [],
   "source": [
    "df['IEP'].sum()/df.count()*100"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "colab": {
     "base_uri": "https://localhost:8080/",
     "height": 35
    },
    "colab_type": "code",
    "executionInfo": {
     "elapsed": 452,
     "status": "ok",
     "timestamp": 1533319887047,
     "user": {
      "displayName": "Allan Campopiano",
      "photoUrl": "//lh6.googleusercontent.com/-jajm12wFRU4/AAAAAAAAAAI/AAAAAAAAAws/b_aS-B759i4/s50-c-k-no/photo.jpg",
      "userId": "113962595821525423496"
     },
     "user_tz": 240
    },
    "id": "3_VDgs98lVuw",
    "outputId": "d716d35c-d046-43da-edf5-9a92b8322c3f"
   },
   "outputs": [
    {
     "data": {
      "text/plain": [
       "1.8188466947960618"
      ]
     },
     "execution_count": 22,
     "metadata": {
      "tags": []
     },
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df['reading_raw_score'].mean()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "colab": {
     "base_uri": "https://localhost:8080/",
     "height": 35
    },
    "colab_type": "code",
    "executionInfo": {
     "elapsed": 265,
     "status": "ok",
     "timestamp": 1533319898284,
     "user": {
      "displayName": "Allan Campopiano",
      "photoUrl": "//lh6.googleusercontent.com/-jajm12wFRU4/AAAAAAAAAAI/AAAAAAAAAws/b_aS-B759i4/s50-c-k-no/photo.jpg",
      "userId": "113962595821525423496"
     },
     "user_tz": 240
    },
    "id": "ALXSgh1G3XlE",
    "outputId": "bf86bd46-3e12-45d0-db40-7932672a9345"
   },
   "outputs": [
    {
     "data": {
      "text/plain": [
       "-99.0"
      ]
     },
     "execution_count": 23,
     "metadata": {
      "tags": []
     },
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df['reading_raw_score'].min()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "colab": {
     "base_uri": "https://localhost:8080/",
     "height": 35
    },
    "colab_type": "code",
    "executionInfo": {
     "elapsed": 237,
     "status": "ok",
     "timestamp": 1533319934787,
     "user": {
      "displayName": "Allan Campopiano",
      "photoUrl": "//lh6.googleusercontent.com/-jajm12wFRU4/AAAAAAAAAAI/AAAAAAAAAws/b_aS-B759i4/s50-c-k-no/photo.jpg",
      "userId": "113962595821525423496"
     },
     "user_tz": 240
    },
    "id": "Am-cj5K23Zy3",
    "outputId": "d5e7ba68-1731-4ca1-c259-eaf9a000bb4a"
   },
   "outputs": [
    {
     "data": {
      "text/plain": [
       "4.9"
      ]
     },
     "execution_count": 24,
     "metadata": {
      "tags": []
     },
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df['reading_raw_score'].max()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "colab": {},
    "colab_type": "code",
    "id": "xhNbCRGGlcRX"
   },
   "outputs": [],
   "source": [
    "df.mean()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "colab": {
     "base_uri": "https://localhost:8080/",
     "height": 185
    },
    "colab_type": "code",
    "executionInfo": {
     "elapsed": 264,
     "status": "ok",
     "timestamp": 1533319982895,
     "user": {
      "displayName": "Allan Campopiano",
      "photoUrl": "//lh6.googleusercontent.com/-jajm12wFRU4/AAAAAAAAAAI/AAAAAAAAAws/b_aS-B759i4/s50-c-k-no/photo.jpg",
      "userId": "113962595821525423496"
     },
     "user_tz": 240
    },
    "id": "mrgu066Zlw94",
    "outputId": "841ebc5a-786d-44d3-8151-05237a09c302"
   },
   "outputs": [
    {
     "data": {
      "text/plain": [
       "count    2133.000000\n",
       "mean        1.477731\n",
       "std         0.499621\n",
       "min         1.000000\n",
       "25%         1.000000\n",
       "50%         1.000000\n",
       "75%         2.000000\n",
       "max         2.000000\n",
       "Name: Gender, dtype: float64"
      ]
     },
     "execution_count": 26,
     "metadata": {
      "tags": []
     },
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df['Gender'].describe()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "colab": {},
    "colab_type": "code",
    "id": "cb_lMyAi_phO"
   },
   "outputs": [],
   "source": [
    "df.describe()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {
    "colab_type": "text",
    "id": "0mZR52953ooH"
   },
   "source": [
    "## <font color=blue>How would you use find the average (i.e., mean) for only the reading_level and writing_level columns?</font>\n",
    "  \n",
    "```python\n",
    "A. mean(df['reading_level'] + df['writing_level'])\n",
    "B. df['reading', 'writing'].mean\n",
    "C. df[['reading_level', 'writing_level']].mean()\n",
    "D. df['reading_level', 'writing_level'].mean()\n",
    "```"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {
    "colab_type": "text",
    "id": "1lXevk9mmRXa"
   },
   "source": [
    "## Finding and replacing"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "colab": {},
    "colab_type": "code",
    "id": "E7r3ySBXrw8P"
   },
   "outputs": [],
   "source": [
    "df=df.replace(-99,np.nan)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "colab": {},
    "colab_type": "code",
    "id": "AudwoQh_l7Z3"
   },
   "outputs": [],
   "source": [
    "df.describe()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "colab": {},
    "colab_type": "code",
    "id": "xWysp2goxuMb"
   },
   "outputs": [],
   "source": [
    "df['SchoolName'].unique()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "colab": {},
    "colab_type": "code",
    "id": "0Mun5NQJ1V_9"
   },
   "outputs": [],
   "source": [
    "df['SchoolName'].replace('School','')\n",
    "df['SchoolName'].unique()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "colab": {},
    "colab_type": "code",
    "id": "dojyFeETyhLV"
   },
   "outputs": [],
   "source": [
    "df=df.replace(' Catholic Elementary School','', regex=True)\n",
    "df['SchoolName'].unique()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {
    "colab_type": "text",
    "id": "PFK1C_Ret7sx"
   },
   "source": [
    "## <font color=blue>What command would remove \"Elementary School\" from the \"SchoolName\" column?</font>\n",
    "\n",
    "```python\n",
    "get_rid='Elementary School'\n",
    "\n",
    "A. df.remove(get_rid, regex=True)\n",
    "B. df.replace(get_rid, '')\n",
    "C. df.replace(get_rid, '', regex=True)\n",
    "D. df.replace(get_rid, regex=True)\n",
    "\n",
    "```"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {
    "colab_type": "text",
    "id": "wDAr5bpMmIZQ"
   },
   "source": [
    "## Adding and removing columns"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "colab": {
     "base_uri": "https://localhost:8080/",
     "height": 87
    },
    "colab_type": "code",
    "executionInfo": {
     "elapsed": 431,
     "status": "ok",
     "timestamp": 1533320497839,
     "user": {
      "displayName": "Allan Campopiano",
      "photoUrl": "//lh6.googleusercontent.com/-jajm12wFRU4/AAAAAAAAAAI/AAAAAAAAAws/b_aS-B759i4/s50-c-k-no/photo.jpg",
      "userId": "113962595821525423496"
     },
     "user_tz": 240
    },
    "id": "LQRQcQXVqjEz",
    "outputId": "a90d687e-d0d3-4634-b125-895b052aee00"
   },
   "outputs": [
    {
     "data": {
      "text/plain": [
       "Index(['Student_number', 'SchoolName', 'reading_level', 'reading_raw_score',\n",
       "       'writing_level', 'writing_raw_score', 'math_level', 'meath_raw_score',\n",
       "       'Gender', 'IEP', 'Region', 'StudentName'],\n",
       "      dtype='object')"
      ]
     },
     "execution_count": 34,
     "metadata": {
      "tags": []
     },
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df.columns"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "colab": {},
    "colab_type": "code",
    "id": "Cf9vKhBVsM0H"
   },
   "outputs": [],
   "source": [
    "df['math_raw_score']=df['meath_raw_score']\n",
    "df=df.drop(columns=['meath_raw_score'])"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "colab": {
     "base_uri": "https://localhost:8080/",
     "height": 87
    },
    "colab_type": "code",
    "executionInfo": {
     "elapsed": 266,
     "status": "ok",
     "timestamp": 1533320569132,
     "user": {
      "displayName": "Allan Campopiano",
      "photoUrl": "//lh6.googleusercontent.com/-jajm12wFRU4/AAAAAAAAAAI/AAAAAAAAAws/b_aS-B759i4/s50-c-k-no/photo.jpg",
      "userId": "113962595821525423496"
     },
     "user_tz": 240
    },
    "id": "JK87B3E2tbSy",
    "outputId": "ba2adb16-a578-43e5-8b9a-3067708cd029"
   },
   "outputs": [
    {
     "data": {
      "text/plain": [
       "Index(['Student_number', 'SchoolName', 'reading_level', 'reading_raw_score',\n",
       "       'writing_level', 'writing_raw_score', 'math_level', 'Gender', 'IEP',\n",
       "       'Region', 'StudentName', 'math_raw_score'],\n",
       "      dtype='object')"
      ]
     },
     "execution_count": 36,
     "metadata": {
      "tags": []
     },
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df.rename({'meath_raw_score': 'math_raw_score'})"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "colab": {},
    "colab_type": "code",
    "id": "Dx5J6bbs5akW"
   },
   "outputs": [],
   "source": [
    "df['reading_minus_math']=df['reading_raw_score']-df['math_raw_score']"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "colab": {},
    "colab_type": "code",
    "id": "fYmWCM8FajBl"
   },
   "outputs": [],
   "source": [
    "df[['reading_raw_score', 'math_raw_score', 'reading_minus_math']]"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {
    "colab_type": "text",
    "id": "4bNjwz2u6XM7"
   },
   "source": [
    "## <font color=blue>How would we remove the 'reading_minus_math' column?</font>\n",
    "\n",
    "```python\n",
    "A. df.remove('reading_minus_math')\n",
    "B. df.drop(columns=['reading_minus_math'])\n",
    "C. df.drop('reading_minus_math')\n",
    "D. df.replace('reading_minus_math', '')\n",
    "\n",
    "```"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {
    "colab_type": "text",
    "id": "cVXFcb5LlmMu"
   },
   "source": [
    "## Selecting rows\n",
    "\n",
    "- boolean filters\n",
    "\n",
    "```python\n",
    "df[True, True, False, False, True]\n",
    "\n",
    "```"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "colab": {},
    "colab_type": "code",
    "id": "mnguCeKIdgUl"
   },
   "outputs": [],
   "source": [
    "df['Gender']==2"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "colab": {},
    "colab_type": "code",
    "id": "BDBmn8fEok73"
   },
   "outputs": [],
   "source": [
    "df[df['Gender']==2]"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {
    "colab_type": "text",
    "id": "Flfx8OSf7x6B"
   },
   "source": [
    "## <font color=blue>How would we select only females?</font>\n",
    "\n",
    "```python\n",
    "A. df[df['Female']==2]\n",
    "B. df[df['Gender']==1]\n",
    "C. df[df['Male']!=2]\n",
    "D. df[df['Gender']==2]\n",
    "\n",
    "```"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {
    "colab_type": "text",
    "id": "aKhFLsNzpVLx"
   },
   "source": [
    "## Selecting rows and then aggregating"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "colab": {},
    "colab_type": "code",
    "id": "kCAltbBdqEC3"
   },
   "outputs": [],
   "source": [
    "df[df['Gender']==2].count()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "colab": {
     "base_uri": "https://localhost:8080/",
     "height": 34
    },
    "colab_type": "code",
    "executionInfo": {
     "elapsed": 255,
     "status": "ok",
     "timestamp": 1533321126827,
     "user": {
      "displayName": "Allan Campopiano",
      "photoUrl": "//lh6.googleusercontent.com/-jajm12wFRU4/AAAAAAAAAAI/AAAAAAAAAws/b_aS-B759i4/s50-c-k-no/photo.jpg",
      "userId": "113962595821525423496"
     },
     "user_tz": 240
    },
    "id": "d7Yql-iAu04w",
    "outputId": "d7dbc7f1-b5b5-46a0-fc01-f2bf5124d267"
   },
   "outputs": [
    {
     "data": {
      "text/plain": [
       "1019"
      ]
     },
     "execution_count": 49,
     "metadata": {
      "tags": []
     },
     "output_type": "execute_result"
    }
   ],
   "source": [
    "males=df['Gender']==2\n",
    "males.sum()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "colab": {
     "base_uri": "https://localhost:8080/",
     "height": 34
    },
    "colab_type": "code",
    "executionInfo": {
     "elapsed": 365,
     "status": "ok",
     "timestamp": 1533321246748,
     "user": {
      "displayName": "Allan Campopiano",
      "photoUrl": "//lh6.googleusercontent.com/-jajm12wFRU4/AAAAAAAAAAI/AAAAAAAAAws/b_aS-B759i4/s50-c-k-no/photo.jpg",
      "userId": "113962595821525423496"
     },
     "user_tz": 240
    },
    "id": "XjEONMBtpUhC",
    "outputId": "8a79187d-ce56-444c-9be2-d4d8ebe49c24"
   },
   "outputs": [
    {
     "data": {
      "text/plain": [
       "1089"
      ]
     },
     "execution_count": 51,
     "metadata": {
      "tags": []
     },
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df['reading_raw_score'][df['Gender']==1].count()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "colab": {},
    "colab_type": "code",
    "id": "BOunkXGoxY7b"
   },
   "outputs": [],
   "source": [
    "df['reading_raw_score'][df['Gender']==2].min()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "colab": {},
    "colab_type": "code",
    "id": "71588D-PqHkQ"
   },
   "outputs": [],
   "source": [
    "df['reading_raw_score'][df['Gender']==2].max()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {
    "colab_type": "text",
    "id": "h9BePIF3uJeJ"
   },
   "source": [
    "## <font color=blue>What command would calculate the percentage of males?</font>\n",
    "- males are coded as 2\n",
    "-  to calculate percentage:\n",
    " - number of things you want, divided by total number of things, times 100\n",
    " \n",
    " \n",
    " \n",
    " ```python\n",
    "\n",
    "A. all_males=df['Gender']==2 \n",
    "   all_males.sum()/len(df)*100\n",
    "  \n",
    "B. df['Gender'].sum()/len(df)*100\n",
    "C. df['Males']/df['Total']*100\n",
    "\n",
    "\n",
    "\n",
    "```"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {
    "colab_type": "text",
    "id": "rE_lHruQx5sP"
   },
   "source": [
    "## Advanced selection\n",
    "- using AND and OR\n",
    "\n",
    " - AKA \"&\" and \"|\"\n",
    " \n",
    "- mind your parentheses"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "colab": {},
    "colab_type": "code",
    "id": "urHW_4-3_CN3"
   },
   "outputs": [],
   "source": [
    "df[df['Gender'] == 1]"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "colab": {},
    "colab_type": "code",
    "id": "lVVYnXzr_Whi"
   },
   "outputs": [],
   "source": [
    "df[df['IEP'] == 1]"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "colab": {},
    "colab_type": "code",
    "id": "F-SonwBQ_bzT"
   },
   "outputs": [],
   "source": [
    "df[(df['Gender'] == 1) & (df['IEP']==1)]"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "colab": {
     "base_uri": "https://localhost:8080/",
     "height": 70
    },
    "colab_type": "code",
    "executionInfo": {
     "elapsed": 259,
     "status": "ok",
     "timestamp": 1533321692255,
     "user": {
      "displayName": "Allan Campopiano",
      "photoUrl": "//lh6.googleusercontent.com/-jajm12wFRU4/AAAAAAAAAAI/AAAAAAAAAws/b_aS-B759i4/s50-c-k-no/photo.jpg",
      "userId": "113962595821525423496"
     },
     "user_tz": 240
    },
    "id": "0PQ_p10h_nSe",
    "outputId": "5e54aaa8-fc6e-4512-ec0d-5783615533dc"
   },
   "outputs": [
    {
     "data": {
      "text/plain": [
       "Gender            289\n",
       "math_raw_score    282\n",
       "dtype: int64"
      ]
     },
     "execution_count": 55,
     "metadata": {
      "tags": []
     },
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df[(df['Gender'] == 1) & (df['Region']=='Burlington')].count()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "colab": {},
    "colab_type": "code",
    "id": "ftW7fysa_n4m"
   },
   "outputs": [],
   "source": [
    "df[(df['Gender'] == 1) & (df['IEP']==1) & (df['Region']=='Burlington')]"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "colab": {},
    "colab_type": "code",
    "id": "RThUMc5m_7Qn"
   },
   "outputs": [],
   "source": [
    "df[(df['Region']=='Oakville') | (df['Region']=='Burlington')]"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "colab": {},
    "colab_type": "code",
    "id": "5_cDJ-wcA6nw"
   },
   "outputs": [],
   "source": [
    "df[(my_filter) & (another_filter)]"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "colab": {},
    "colab_type": "code",
    "id": "jKBrpIHsA8Rn"
   },
   "outputs": [],
   "source": [
    "df[(df['Gender'] == 2) & (df['IEP']==1) & (df['reading_raw_score']>3)]"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "colab": {},
    "colab_type": "code",
    "id": "lE19daGfBH9f"
   },
   "outputs": [],
   "source": [
    "df[(df['Gender'] == 2) & (df['IEP']==1) & (df['reading_raw_score']>3) & ((df['Region']=='Burlington') | (df['Region']=='Oakville'))].describe()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "colab": {
     "base_uri": "https://localhost:8080/",
     "height": 34
    },
    "colab_type": "code",
    "executionInfo": {
     "elapsed": 287,
     "status": "ok",
     "timestamp": 1533322098855,
     "user": {
      "displayName": "Allan Campopiano",
      "photoUrl": "//lh6.googleusercontent.com/-jajm12wFRU4/AAAAAAAAAAI/AAAAAAAAAws/b_aS-B759i4/s50-c-k-no/photo.jpg",
      "userId": "113962595821525423496"
     },
     "user_tz": 240
    },
    "id": "PGZyew4HjSDD",
    "outputId": "327ca304-11d3-4ded-a8ed-0d9d661c858e"
   },
   "outputs": [
    {
     "data": {
      "text/plain": [
       "0.6931144761427454"
      ]
     },
     "execution_count": 64,
     "metadata": {
      "tags": []
     },
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df['reading_raw_score'].corr(df['writing_raw_score'])"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "colab": {},
    "colab_type": "code",
    "id": "AhWCWJ-sHMMW"
   },
   "outputs": [],
   "source": [
    "df['Region'].isin(['Oakville', 'Burlington'])"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {
    "colab_type": "text",
    "id": "1gJ5o7B0CZgd"
   },
   "source": [
    "## <font color=blue>What would give us males with a math score above 3?</font>\n",
    "- males are coded as 2\n",
    " \n",
    "```python\n",
    "A. df[(df['Gender'] == 2) & (df['math_level']>3)]\n",
    "B. df[(df['Gender'] == 2) & (df['math_level']>=3)]\n",
    "C. df[(df['Gender'] == 1) & (df['math_level']>3)]\n",
    "```"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {
    "colab_type": "text",
    "id": "jsEj-eK2yHmE"
   },
   "source": [
    "## Advanced selection and aggregation"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "colab": {
     "base_uri": "https://localhost:8080/",
     "height": 34
    },
    "colab_type": "code",
    "executionInfo": {
     "elapsed": 296,
     "status": "ok",
     "timestamp": 1533322393942,
     "user": {
      "displayName": "Allan Campopiano",
      "photoUrl": "//lh6.googleusercontent.com/-jajm12wFRU4/AAAAAAAAAAI/AAAAAAAAAws/b_aS-B759i4/s50-c-k-no/photo.jpg",
      "userId": "113962595821525423496"
     },
     "user_tz": 240
    },
    "id": "XQLyXiVJB9e7",
    "outputId": "5d03d356-5b38-47d8-a50c-fc8f4468b64e"
   },
   "outputs": [
    {
     "data": {
      "text/plain": [
       "3.098360655737705"
      ]
     },
     "execution_count": 66,
     "metadata": {
      "tags": []
     },
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df['reading_raw_score'][(df['Gender'] == 1) & (df['IEP']==1)].mean()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "colab": {},
    "colab_type": "code",
    "id": "iKzQKYg1B9e-"
   },
   "outputs": [],
   "source": [
    "df['reading_raw_score'][(df['Gender'] == 1) & (df['Region']=='Burlington')].mean()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "colab": {},
    "colab_type": "code",
    "id": "SdJyzyalB9fC"
   },
   "outputs": [],
   "source": [
    "df['reading_raw_score'][(df['Gender'] == 1) & (df['IEP']==1) & (df['Region']=='Burlington')].mean()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "colab": {},
    "colab_type": "code",
    "id": "Z7w9sgEzB9fF"
   },
   "outputs": [],
   "source": [
    "df['reading_raw_score'][(df['Region']=='Oakville') | (df['Region']=='Burlington')].mean()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "colab": {},
    "colab_type": "code",
    "id": "Oc70FBnrB9fH"
   },
   "outputs": [],
   "source": [
    "df['reading_raw_score'][(df['Region']=='Oakville') & (df['Region']=='Burlington')].mean()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "colab": {},
    "colab_type": "code",
    "id": "ze5L0qt1B9fJ"
   },
   "outputs": [],
   "source": [
    "df[['reading_raw_score', 'writing_raw_score']][(df['Gender'] == 2) & (df['IEP']==1) & (df['reading_raw_score']>3)].mean()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "colab": {},
    "colab_type": "code",
    "id": "Rc6FEJRlB9fM"
   },
   "outputs": [],
   "source": [
    "df['reading_raw_score'][(df['Gender'] == 2) & (df['IEP']==1) & (df['reading_raw_score']>3) & ((df['Region']=='Burlington') | (df['Region']=='Oakville'))].mean()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "colab": {
     "base_uri": "https://localhost:8080/",
     "height": 175
    },
    "colab_type": "code",
    "executionInfo": {
     "elapsed": 261,
     "status": "ok",
     "timestamp": 1533322633918,
     "user": {
      "displayName": "Allan Campopiano",
      "photoUrl": "//lh6.googleusercontent.com/-jajm12wFRU4/AAAAAAAAAAI/AAAAAAAAAws/b_aS-B759i4/s50-c-k-no/photo.jpg",
      "userId": "113962595821525423496"
     },
     "user_tz": 240
    },
    "id": "xGQQPMBHCMlg",
    "outputId": "25e15675-7586-4f1c-d40f-b3a4e1eaf0f1"
   },
   "outputs": [
    {
     "data": {
      "text/plain": [
       "count    21.000000\n",
       "mean      3.576190\n",
       "std       0.376702\n",
       "min       3.100000\n",
       "25%       3.300000\n",
       "50%       3.500000\n",
       "75%       3.700000\n",
       "max       4.300000\n",
       "Name: reading_raw_score, dtype: float64"
      ]
     },
     "execution_count": 67,
     "metadata": {
      "tags": []
     },
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df['reading_raw_score'][(df['Gender'] == 2) & (df['IEP']==1) & (df['reading_raw_score']>3) & ((df['Region']=='Burlington') | (df['Region']=='Oakville'))].describe()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {
    "colab_type": "text",
    "id": "zO4eEz2K5lK6"
   },
   "source": [
    "## <font color=blue>What would give us the number people in Milton with a \"math_level\" less than 3?</font>\n",
    "\n",
    "```python\n",
    "A. df['math_level'][(df['math_level']<2) & (df['Region']=='Milton')].count()\n",
    "B. df['math_level'][(df['math_level']<2) & (df['Region']=='Milton')].size()\n",
    "C. df['math_level'][(df['math_level']<2) & (df['Region']=='Milton')].sum()\n",
    "```"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {
    "colab_type": "text",
    "id": "wIsmn95vXs94"
   },
   "source": [
    "## Selection shortcuts\n",
    "\n",
    "- isin"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "colab": {},
    "colab_type": "code",
    "id": "vHSQQ_yoYTgU"
   },
   "outputs": [],
   "source": [
    "df['reading_level'].isin([2]).head(8)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "colab": {},
    "colab_type": "code",
    "id": "HE3cBm08YQlA"
   },
   "outputs": [],
   "source": [
    "df.head(8)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "colab": {},
    "colab_type": "code",
    "id": "QzhTt6zxZAsz"
   },
   "outputs": [],
   "source": [
    "df['reading_level'].isin([3,2])"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "colab": {},
    "colab_type": "code",
    "id": "y_rQ1CePZHgD"
   },
   "outputs": [],
   "source": [
    "~df['reading_level'].isin([2]).head(8)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "colab": {},
    "colab_type": "code",
    "id": "pEwHW5oaZrWR"
   },
   "outputs": [],
   "source": [
    "df[df['reading_level'].isin([2])].head(8)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "colab": {},
    "colab_type": "code",
    "id": "3_kmy0u2Z07e"
   },
   "outputs": [],
   "source": [
    "df[(df['reading_level'].isin([1,2])) & (df['Region'].isin(['Oakville', 'Milton']))].head(8) "
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "colab": {},
    "colab_type": "code",
    "id": "3o7LxU14aVId"
   },
   "outputs": [],
   "source": [
    "df[(df['reading_level'].isin([1, 2])) & (df['Region'].isin(['Oakville', 'Milton']))]['Region'].unique()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "colab": {},
    "colab_type": "code",
    "id": "iM2N0IoIahsl"
   },
   "outputs": [],
   "source": [
    "df[((df['reading_level'] == 1) | (df['reading_level']==2)) & ((df['Region']=='Oakville') | (df['Region']=='Milton'))].head(8)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "colab": {},
    "colab_type": "code",
    "id": "hunhGuFQfQDY"
   },
   "outputs": [],
   "source": [
    "df.isin({'reading_level': [1,2], 'Region': ['Oakville', 'Milton']})"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "colab": {},
    "colab_type": "code",
    "id": "1_-xokERfWuz"
   },
   "outputs": [],
   "source": [
    "df[df.isin({'reading_level': [1,2], 'Region': ['Oakville', 'Milton']})] "
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "colab": {},
    "colab_type": "code",
    "id": "jYzPmpqedV7b"
   },
   "outputs": [],
   "source": [
    "df[df.isin({'reading_level': [1,2], 'Region': ['Oakville', 'Milton']})] [['reading_level', 'Region']].dropna()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "colab": {},
    "colab_type": "code",
    "id": "p-7KpAeBh6yu"
   },
   "outputs": [],
   "source": [
    "df.isin({'reading_level': [1,2], 'Region': ['Oakville', 'Milton']}).sum()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "colab": {},
    "colab_type": "code",
    "id": "2R8CQt8qkO2z"
   },
   "outputs": [],
   "source": [
    "df.isin([1,2,'Oakville', 'Milton']).sum()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "colab": {},
    "colab_type": "code",
    "id": "qQSIdIz9LoT6"
   },
   "outputs": [],
   "source": [
    "df.query(\"Region == 'Burlington'\")"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {
    "colab_type": "text",
    "id": "eqKj1Ityi_VQ"
   },
   "source": [
    "## <font color=blue>Which two expressions will produce the same result?</font>\n",
    "<font color= red>Very Difficult</font>\n",
    "\n",
    "\n",
    "```python\n",
    "1. (df['math_level'].isin([1, 2])) & (df['Gender'].isin([2]))\n",
    "2. df.isin([1,2])\n",
    "3. (df['math_level']==1) | (df['math_level']==2) & (df['Gender']==2)\n",
    "4. ((df['math_level']==1) | (df['math_level']==2)) & (df['Gender']==2)\n",
    "\n",
    "```"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {
    "colab_type": "text",
    "id": "QAsN4-O-yMf6"
   },
   "source": [
    "## Grouping\n",
    "\n",
    "- perhaps the most useful operation when dealing with data\n",
    "- organize results by a categorical variable \n",
    " - eg., school, region, scores above, below, or equal to some value)\n",
    "- almost always used in conjunction with aggregation\n",
    "\n",
    "\n",
    "![alt text](http://pbpython.com/images/groupby-example.png)\n",
    "\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "colab": {},
    "colab_type": "code",
    "id": "c5I01tKrE4ho"
   },
   "outputs": [],
   "source": [
    "df.groupby('SchoolName').mean()['reading_level']"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "colab": {},
    "colab_type": "code",
    "id": "KrWEB62RGDRZ"
   },
   "outputs": [],
   "source": [
    "df.groupby('Region').mean()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "colab": {},
    "colab_type": "code",
    "id": "UzzsEvzOGJ-J"
   },
   "outputs": [],
   "source": [
    "df.groupby(['SchoolName', 'IEP']).mean()['reading_level']"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "colab": {},
    "colab_type": "code",
    "id": "UxQk-7uOGiIN"
   },
   "outputs": [],
   "source": [
    "df.groupby(['SchoolName', 'IEP', 'Gender']).mean()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "colab": {},
    "colab_type": "code",
    "id": "_EX3ZqEhGpF-"
   },
   "outputs": [],
   "source": [
    "df.groupby('Region').mean()['reading_raw_score']"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {
    "colab_type": "text",
    "id": "Exu0gd_a-hjs"
   },
   "source": [
    "## <font color=blue>What would happen if you grouped by \"StudentName\" and calculated the mean?</font>\n",
    "<font color= red>Tricky</font>\n",
    "\n",
    "```\n",
    "A. you'd get an error\n",
    "B. you'd just get the original data back\n",
    "C. you'd get the mean of everything but the region or school column\n",
    "D. you'd get original data and duplicate names would be grouped\n",
    "E. you'd get the original data or duplicate names would be grouped, and C\n",
    "```"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {
    "colab_type": "text",
    "id": "vRh-tFEcHNOw"
   },
   "source": [
    "## Advanced grouping with aggregation\n",
    "- remember how to select data?\n",
    "- how would you use those techniques to filter the groups?\n",
    "- defining our own little function for aggregation"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "colab": {},
    "colab_type": "code",
    "id": "OplUnTIkG998"
   },
   "outputs": [],
   "source": [
    "df['reading_level'][df['reading_level']==3].count()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "colab": {},
    "colab_type": "code",
    "id": "hxvgs6mWJNUe"
   },
   "outputs": [],
   "source": [
    "df.groupby('SchoolName')['reading_level'].count()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "colab": {},
    "colab_type": "code",
    "id": "TP3O2l4TJ2ZS"
   },
   "outputs": [],
   "source": [
    "df.groupby('SchoolName')['reading_level'].aggregate(['count', 'mean'])"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "colab": {},
    "colab_type": "code",
    "id": "C4pk_kYgLG-l"
   },
   "outputs": [],
   "source": [
    "df.groupby('SchoolName')['reading_level'].aggregate('max')"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "colab": {},
    "colab_type": "code",
    "id": "fBLk_z1gLJO7"
   },
   "outputs": [],
   "source": [
    "df.groupby('SchoolName')['reading_level'].aggregate('min')"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "colab": {},
    "colab_type": "code",
    "id": "xJRXyPmv5Fx2"
   },
   "outputs": [],
   "source": [
    "def my_function(x):\n",
    "  my_count=x[(x>=2) & (x<4)].count()\n",
    "  return my_count\n",
    "\n",
    "df.groupby('SchoolName')['reading_level'].aggregate(my_function)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "colab": {},
    "colab_type": "code",
    "id": "SecZnWFf0BCY"
   },
   "outputs": [],
   "source": [
    "def my_function(x):\n",
    "  my_count=x[(x['reading_level']==3) & (x['writing_level']==4)].count()\n",
    "  return my_count\n",
    "\n",
    "df.groupby('SchoolName').aggregate(my_function)['reading_level']"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "colab": {},
    "colab_type": "code",
    "id": "kyDFZ77T5mA5"
   },
   "outputs": [],
   "source": [
    "def my_function(x):\n",
    "  my_vals=x['reading_raw_score'][(x['Gender'] == 2) & (x['IEP']==1)].mean()\n",
    "  return my_vals\n",
    "\n",
    "df.groupby('Region').aggregate(my_function)['reading_level']"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "colab": {},
    "colab_type": "code",
    "id": "3VyWFqEkH9H7"
   },
   "outputs": [],
   "source": [
    "my_function=lambda x: x[x == 3].count()\n",
    "df.groupby('SchoolName')['reading_level'].aggregate(my_function)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "colab": {},
    "colab_type": "code",
    "id": "mRB0BqJxLtCt"
   },
   "outputs": [],
   "source": [
    "df.groupby('SchoolName')['reading_level'].aggregate(lambda x: x[x == 3].count())"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "colab": {},
    "colab_type": "code",
    "id": "Xp28LQ3PIi-R"
   },
   "outputs": [],
   "source": [
    "my_function=lambda x: x[x > 3].count() / x.count() * 100\n",
    "df.groupby('SchoolName')['reading_raw_score'].aggregate(my_function)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "colab": {},
    "colab_type": "code",
    "id": "t_oWUpdvMVWr"
   },
   "outputs": [],
   "source": [
    "my_function=lambda x: x[x > 3].count() / x.count() * 100\n",
    "df.groupby('SchoolName')['reading_raw_score', 'writing_raw_score'].aggregate(my_function)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {
    "colab_type": "text",
    "id": "JZIAA9PnUIaQ"
   },
   "source": [
    "## <font color=blue>If you want to aggregate AND use groups, but none of the built-in aggregations are of interest to you...</font>\n",
    "\n",
    "\n",
    "```\n",
    "A. you can write your own aggregation function\n",
    "B. you are out of luck\n",
    "C. you have to write many lines of code\n",
    "```"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {
    "colab_type": "text",
    "id": "7hWPYY8FbY0R"
   },
   "source": [
    "## Transforming\n",
    "<font color= red>Tricky</font>\n",
    "- often used with groups\n",
    "- taking every row of the dataFrame and changing its value \n",
    " - usually the new values are *based on each group*\n",
    " - however, you can also transform the data irrespective of the group (e.g., reverse code)\n",
    " \n",
    " \n",
    "- baseline correction\n",
    "- normalization\n",
    "- imputing missing data\n",
    "\n",
    "![alt text](http://pbpython.com/images/transform-example.png)\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "colab": {},
    "colab_type": "code",
    "id": "Re29tSDsbY0V"
   },
   "outputs": [],
   "source": [
    "df.head()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "colab": {},
    "colab_type": "code",
    "id": "wkYLN6-tbY0Y"
   },
   "outputs": [],
   "source": [
    "my_new_num=6-my_old_num"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "colab": {},
    "colab_type": "code",
    "id": "Tgi4XmP5bY0c"
   },
   "outputs": [],
   "source": [
    "def revcode(x):\n",
    "  lim=5\n",
    "  x=lim-x\n",
    "  return x\n",
    "\n",
    "df['junk']=df['math_level'].transform(revcode)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "colab": {},
    "colab_type": "code",
    "id": "82ei5RNFbY0e"
   },
   "outputs": [],
   "source": [
    "df[['junk', 'math_level']].head()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "colab": {},
    "colab_type": "code",
    "id": "Dak6Xb3lbY0g"
   },
   "outputs": [],
   "source": [
    "def impute_mean(x):\n",
    "  x=x.fillna(x.mean())\n",
    "  return x\n",
    "\n",
    "df['junk']=df.groupby('Region')['reading_level'].transform(impute_mean)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "colab": {},
    "colab_type": "code",
    "id": "aU1nHfx2vxqe"
   },
   "outputs": [],
   "source": [
    "df.groupby('Region')['reading_level'].mean()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "colab": {},
    "colab_type": "code",
    "id": "F5S5EQ4f1MEX"
   },
   "outputs": [],
   "source": [
    "df[(df['Region']=='Oakville') & (df['junk'].round(6)==3.001776)][['junk', 'reading_level']]"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "colab": {},
    "colab_type": "code",
    "id": "5TL78MOfbY0j"
   },
   "outputs": [],
   "source": [
    "def subtract_mean(x):\n",
    "  x=x-x.mean()\n",
    "  return x\n",
    "\n",
    "df['junk']=df.groupby('Region')['reading_level'].transform(subtract_mean)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "colab": {},
    "colab_type": "code",
    "id": "GJQlKGPN4-yE"
   },
   "outputs": [],
   "source": [
    "df.groupby('Region')['junk'].sum()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "colab": {},
    "colab_type": "code",
    "id": "LuSLZsa6bY0n"
   },
   "outputs": [],
   "source": [
    "def normalize(x):\n",
    "  x=x/x.max()\n",
    "  return x\n",
    "\n",
    "df['junk']=df.groupby('Region')['reading_level'].transform(normalize)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "colab": {},
    "colab_type": "code",
    "id": "S5DquzLX5g_l"
   },
   "outputs": [],
   "source": [
    "df.groupby('Region')[['junk', 'reading_level']].aggregate(['min', 'max'])"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {
    "colab_type": "text",
    "id": "HMDb_h9Pn8cR"
   },
   "source": [
    "## <font color=blue>How does the *transform* method differ from the *aggregate* method?</font>\n",
    "\n",
    "\n",
    "```\n",
    "A. They are actually the same\n",
    "B. aggregate returns a smaller dataset than transform\n",
    "C. transform returns a smaller dataset than aggregate\n",
    "D. only aggregate allows custom functions to be passed in\n",
    "```"
   ]
  }
 ],
 "metadata": {
  "colab": {
   "collapsed_sections": [],
   "name": "5_spreadsheets_and_pandas",
   "provenance": [
    {
     "file_id": "1B2kdhnm0KDZpANC2WT4lirXOxNHQ5FF2",
     "timestamp": 1530232572724
    }
   ],
   "version": "0.3.2"
  },
  "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.1"
  }
 },
 "nbformat": 4,
 "nbformat_minor": 4
}