Exemplar_ Dataframes with pandas.ipynb

{
 "cells": [
  {
   "cell_type": "markdown",
   "metadata": {
    "id": "W8orLNs0RRSa"
   },
   "source": [
    "<h1>Exemplar: Dataframes with pandas</h1>"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {
    "id": "ObyrlQucRbyr"
   },
   "source": [
    "## Introduction\n",
    "\n",
    "Your work as a data professional for the U.S. Environmental Protection Agency (EPA) requires you to analyze air quality index data collected from the United States and Mexico.\n",
    "\n",
    "The air quality index (AQI) is a number that runs from 0 to 500. The higher the AQI value, the greater the level of air pollution and the greater the health concern. For example, an AQI value of 50 or below represents good air quality, while an AQI value over 300 represents hazardous air quality. Refer to this guide from [AirNow.gov](https://www.airnow.gov/aqi/aqi-basics/) for more information.\n",
    "\n",
    "In this lab, you will practice working in pandas. You will load a dataframe, examine its metadata and summary statistics, and explore it using iloc indexing and sorting. You will also practice Boolean masking, grouping, and concatenating data."
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {
    "id": "1Qlq_5zHvzZV"
   },
   "source": [
    "## Tips for completing this lab\n",
    "\n",
    "As you navigate this lab, keep the following tips in mind:\n",
    "\n",
    "- `### YOUR CODE HERE ###` indicates where you should write code. Be sure to replace this with your own code before running the code cell.\n",
    "- Feel free to open the hints for additional guidance as you work on each task.\n",
    "- To enter your answer to a question, double-click the markdown cell to edit. Be sure to replace the \"[Double-click to enter your responses here.]\" with your own answer.\n",
    "- You can save your work manually by clicking File and then Save in the menu bar at the top of the notebook.\n",
    "- You can download your work locally by clicking File and then Download and then specifying your preferred file format in the menu bar at the top of the notebook."
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {
    "id": "zSl8JYIUv8qp"
   },
   "source": [
    "## Task 1: Read data from csv file into a pandas dataframe\n",
    "\n",
    "You are given two files of data. Begin with the first file, which contains the three states with the most observations (rows): California, Texas, and Pennsylvania."
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {
    "id": "FtHATF_qy2HY"
   },
   "source": [
    "### 1a: Import statements\n",
    "\n",
    "Import numpy and pandas. Use their standard aliases."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 1,
   "metadata": {
    "executionInfo": {
     "elapsed": 599,
     "status": "ok",
     "timestamp": 1680145357155,
     "user": {
      "displayName": "Matteo Riotto",
      "userId": "15211004813978485634"
     },
     "user_tz": 420
    },
    "id": "9v0Ws5Lma0Qp"
   },
   "outputs": [],
   "source": [
    "### YOUR CODE HERE ###\n",
    "import numpy as np\n",
    "import pandas as pd"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {
    "id": "qdCu_2SgzaP-"
   },
   "source": [
    "<details>\n",
    "  <summary><h4><strong>Hint 1</strong></h4></summary>\n",
    "\n",
    "Begin with the `import` keyword for each statement.\n",
    "\n",
    "</details>"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {
    "id": "v1vbXoc3zot4"
   },
   "source": [
    "<details>\n",
    "  <summary><h4><strong>Hint 2</strong></h4></summary>\n",
    "\n",
    "Use the `as` keyword to assign an alias.\n",
    "\n",
    "</details>"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {
    "id": "xlzyP3fTzxUE"
   },
   "source": [
    "<details>\n",
    "  <summary><h4><strong>Hint 3</strong></h4></summary>\n",
    "\n",
    "The conventional aliases are `np` for numpy and `pd` for pandas.\n",
    "\n",
    "</details>"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {
    "id": "HSlvZpkJ0B1P"
   },
   "source": [
    "### 1b: Read in the first file\n",
    "\n",
    "1. Use the `pd.read_csv()` function to read in the data from the three states with the most observations. The file is called `'epa_ca_tx_pa.csv'` and is already in your working directory. Assign the resulting dataframe to a variable named `top3`.\n",
    "\n",
    "2. Use the `head()` method on the `top3` dataframe to inspect the first five rows."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 2,
   "metadata": {
    "colab": {
     "base_uri": "https://localhost:8080/",
     "height": 206
    },
    "executionInfo": {
     "elapsed": 20,
     "status": "ok",
     "timestamp": 1679932927383,
     "user": {
      "displayName": "Matteo Riotto",
      "userId": "15211004813978485634"
     },
     "user_tz": 420
    },
    "id": "_prn5YzrSnkO",
    "outputId": "ac391295-ea57-4902-f828-cefcedc74cd6"
   },
   "outputs": [
    {
     "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>state_code</th>\n",
       "      <th>state_name</th>\n",
       "      <th>county_code</th>\n",
       "      <th>county_name</th>\n",
       "      <th>aqi</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>6</td>\n",
       "      <td>California</td>\n",
       "      <td>1</td>\n",
       "      <td>Alameda</td>\n",
       "      <td>11.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>6</td>\n",
       "      <td>California</td>\n",
       "      <td>7</td>\n",
       "      <td>Butte</td>\n",
       "      <td>6.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>6</td>\n",
       "      <td>California</td>\n",
       "      <td>19</td>\n",
       "      <td>Fresno</td>\n",
       "      <td>11.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>6</td>\n",
       "      <td>California</td>\n",
       "      <td>29</td>\n",
       "      <td>Kern</td>\n",
       "      <td>7.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>6</td>\n",
       "      <td>California</td>\n",
       "      <td>29</td>\n",
       "      <td>Kern</td>\n",
       "      <td>3.0</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "   state_code  state_name  county_code county_name   aqi\n",
       "0           6  California            1     Alameda  11.0\n",
       "1           6  California            7       Butte   6.0\n",
       "2           6  California           19      Fresno  11.0\n",
       "3           6  California           29        Kern   7.0\n",
       "4           6  California           29        Kern   3.0"
      ]
     },
     "execution_count": 2,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# 1. ### YOUR CODE HERE ###\n",
    "top3 = pd.read_csv('epa_ca_tx_pa.csv')\n",
    "\n",
    "# 2. ### YOUR CODE HERE ###\n",
    "top3.head()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {
    "id": "1Ed4yOjY2AeA"
   },
   "source": [
    "<details>\n",
    "  <summary><h4><strong>Hint</strong></h4></summary>\n",
    "\n",
    "Because the file is already in your working directory, you can simply pass the file name to the `pd.read_csv()` function as a string.\n",
    "\n",
    "</details>"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {
    "id": "mWlnbqgB2gVj"
   },
   "source": [
    "## Task 2: Summary information\n",
    "\n",
    "Now that you have a dataframe with the AQI data for California, Texas, and Pennsylvania, get some high-level summary information about it."
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {
    "id": "lAPAVCeg3Cqi"
   },
   "source": [
    "### 2a: Metadata\n",
    "\n",
    "Use a DataFrame method to examine the number of rows and columns, the column names, the data type contained in each column, the number of non-null values in each column, and the amount of memory the dataframe uses."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 3,
   "metadata": {
    "colab": {
     "base_uri": "https://localhost:8080/"
    },
    "executionInfo": {
     "elapsed": 20,
     "status": "ok",
     "timestamp": 1679932927384,
     "user": {
      "displayName": "Matteo Riotto",
      "userId": "15211004813978485634"
     },
     "user_tz": 420
    },
    "id": "KAlBReg6Swzs",
    "outputId": "eb2ad40a-74d6-4176-a156-8066f05f900a"
   },
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "<class 'pandas.core.frame.DataFrame'>\n",
      "RangeIndex: 546 entries, 0 to 545\n",
      "Data columns (total 5 columns):\n",
      " #   Column       Non-Null Count  Dtype  \n",
      "---  ------       --------------  -----  \n",
      " 0   state_code   546 non-null    int64  \n",
      " 1   state_name   546 non-null    object \n",
      " 2   county_code  546 non-null    int64  \n",
      " 3   county_name  546 non-null    object \n",
      " 4   aqi          546 non-null    float64\n",
      "dtypes: float64(1), int64(2), object(2)\n",
      "memory usage: 21.5+ KB\n"
     ]
    }
   ],
   "source": [
    "### YOUR CODE HERE ###\n",
    "top3.info()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {
    "id": "eaYhqTMH4cCl"
   },
   "source": [
    "<details>\n",
    "  <summary><h4><strong>Hint</strong></h4></summary>\n",
    "\n",
    "The `info()` method returns a dataframe's metadata.\n",
    "\n",
    "</details>"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {
    "id": "_MgYSGkX42m7"
   },
   "source": [
    "### 2b: Summary statistics\n",
    "\n",
    "Examine the summary statistics of the dataframe's numeric columns. The output should be a table that includes row count, mean, standard deviation, min, max, and quartile values."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 4,
   "metadata": {
    "colab": {
     "base_uri": "https://localhost:8080/",
     "height": 300
    },
    "executionInfo": {
     "elapsed": 17,
     "status": "ok",
     "timestamp": 1679932927385,
     "user": {
      "displayName": "Matteo Riotto",
      "userId": "15211004813978485634"
     },
     "user_tz": 420
    },
    "id": "FbPFaH6ZfPdz",
    "outputId": "b5a0e137-c207-4198-d32a-b1823d21a054"
   },
   "outputs": [
    {
     "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>state_code</th>\n",
       "      <th>county_code</th>\n",
       "      <th>aqi</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>count</th>\n",
       "      <td>546.000000</td>\n",
       "      <td>546.000000</td>\n",
       "      <td>546.000000</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>mean</th>\n",
       "      <td>20.593407</td>\n",
       "      <td>83.179487</td>\n",
       "      <td>8.906593</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>std</th>\n",
       "      <td>19.001484</td>\n",
       "      <td>92.240873</td>\n",
       "      <td>9.078479</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>min</th>\n",
       "      <td>6.000000</td>\n",
       "      <td>1.000000</td>\n",
       "      <td>0.000000</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>25%</th>\n",
       "      <td>6.000000</td>\n",
       "      <td>29.000000</td>\n",
       "      <td>3.000000</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>50%</th>\n",
       "      <td>6.000000</td>\n",
       "      <td>66.000000</td>\n",
       "      <td>6.000000</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>75%</th>\n",
       "      <td>42.000000</td>\n",
       "      <td>98.500000</td>\n",
       "      <td>11.000000</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>max</th>\n",
       "      <td>48.000000</td>\n",
       "      <td>479.000000</td>\n",
       "      <td>93.000000</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "       state_code  county_code         aqi\n",
       "count  546.000000   546.000000  546.000000\n",
       "mean    20.593407    83.179487    8.906593\n",
       "std     19.001484    92.240873    9.078479\n",
       "min      6.000000     1.000000    0.000000\n",
       "25%      6.000000    29.000000    3.000000\n",
       "50%      6.000000    66.000000    6.000000\n",
       "75%     42.000000    98.500000   11.000000\n",
       "max     48.000000   479.000000   93.000000"
      ]
     },
     "execution_count": 4,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "### YOUR CODE HERE ###\n",
    "top3.describe()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {
    "id": "SQ9n1u165XFd"
   },
   "source": [
    "<details>\n",
    "  <summary><h4><strong>Hint</strong></h4></summary>\n",
    "\n",
    "The `describe()` method returns a table of summary statistics for a dataframe's numeric columns.\n",
    "\n",
    "</details>"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {
    "id": "oLMPpAzN5wVH"
   },
   "source": [
    "## Task 3: Explore your data\n",
    "\n",
    "Practice exploring your data by completing the following exercises."
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {
    "id": "18cf9eYI6eY3"
   },
   "source": [
    "### 3a: Rows per state\n",
    "\n",
    "Select the `state_name` column and use the `value_counts()` method on it to check how many rows there are for each state in the dataframe."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 5,
   "metadata": {
    "colab": {
     "base_uri": "https://localhost:8080/"
    },
    "executionInfo": {
     "elapsed": 40,
     "status": "ok",
     "timestamp": 1679932927637,
     "user": {
      "displayName": "Matteo Riotto",
      "userId": "15211004813978485634"
     },
     "user_tz": 420
    },
    "id": "pkNTi5ZpfmLT",
    "outputId": "ed6454d5-a369-496d-e333-0af8872e48dc"
   },
   "outputs": [
    {
     "data": {
      "text/plain": [
       "California      342\n",
       "Texas           104\n",
       "Pennsylvania    100\n",
       "Name: state_name, dtype: int64"
      ]
     },
     "execution_count": 5,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "### YOUR CODE HERE ###\n",
    "top3['state_name'].value_counts()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {
    "id": "nzjx1vtx7L3-"
   },
   "source": [
    "<details>\n",
    "  <summary><h4><strong>Hint 1</strong></h4></summary>\n",
    "\n",
    "This code should all be on a single line. Begin by selecting the `top3` dataframe at the `state_name` column. You can use either selector brackets or dot notation.\n",
    "\n",
    "</details>"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {
    "id": "OSIHI_Hi7sQ3"
   },
   "source": [
    "<details>\n",
    "  <summary><h4><strong>Hint 2</strong></h4></summary>\n",
    "\n",
    "When using brackets to select a column, the column's name should be entered as a string.\n",
    "\n",
    "</details>"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {
    "id": "ZQyiudC974Ox"
   },
   "source": [
    "<details>\n",
    "  <summary><h4><strong>Hint 3</strong></h4></summary>\n",
    "\n",
    "The `value_counts()` method is added to the end of the selection statement using dot notation. Its parentheses are empty.\n",
    "\n",
    "</details>"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {
    "id": "pdtgkvCU8N96"
   },
   "source": [
    "### 3b: Sort by AQI\n",
    "\n",
    "1.  Create a new dataframe called `top3_sorted` by using the `sort_values()` method on the `top3` dataframe. Refer to the [sort_values pandas documentation](https://pandas.pydata.org/docs/reference/api/pandas.DataFrame.sort_values.html#) for more information about how to use this method.\n",
    "    *  The new dataframe should contain the data sorted by AQI, beginning with the rows with the highest AQI values.\n",
    "2.  Print the top 10 rows of `top3_sorted`."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 6,
   "metadata": {
    "colab": {
     "base_uri": "https://localhost:8080/",
     "height": 363
    },
    "executionInfo": {
     "elapsed": 39,
     "status": "ok",
     "timestamp": 1679932927638,
     "user": {
      "displayName": "Matteo Riotto",
      "userId": "15211004813978485634"
     },
     "user_tz": 420
    },
    "id": "IRryfyYOhOfm",
    "outputId": "da23ed8b-5181-43f4-8f58-8ce68aea9149"
   },
   "outputs": [
    {
     "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>state_code</th>\n",
       "      <th>state_name</th>\n",
       "      <th>county_code</th>\n",
       "      <th>county_name</th>\n",
       "      <th>aqi</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>76</th>\n",
       "      <td>6</td>\n",
       "      <td>California</td>\n",
       "      <td>37</td>\n",
       "      <td>Los Angeles</td>\n",
       "      <td>93.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>146</th>\n",
       "      <td>6</td>\n",
       "      <td>California</td>\n",
       "      <td>37</td>\n",
       "      <td>Los Angeles</td>\n",
       "      <td>59.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>41</th>\n",
       "      <td>6</td>\n",
       "      <td>California</td>\n",
       "      <td>83</td>\n",
       "      <td>Santa Barbara</td>\n",
       "      <td>47.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>122</th>\n",
       "      <td>6</td>\n",
       "      <td>California</td>\n",
       "      <td>59</td>\n",
       "      <td>Orange</td>\n",
       "      <td>47.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>184</th>\n",
       "      <td>6</td>\n",
       "      <td>California</td>\n",
       "      <td>59</td>\n",
       "      <td>Orange</td>\n",
       "      <td>47.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>51</th>\n",
       "      <td>48</td>\n",
       "      <td>Texas</td>\n",
       "      <td>141</td>\n",
       "      <td>El Paso</td>\n",
       "      <td>47.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>80</th>\n",
       "      <td>6</td>\n",
       "      <td>California</td>\n",
       "      <td>65</td>\n",
       "      <td>Riverside</td>\n",
       "      <td>43.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>136</th>\n",
       "      <td>48</td>\n",
       "      <td>Texas</td>\n",
       "      <td>141</td>\n",
       "      <td>El Paso</td>\n",
       "      <td>40.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>58</th>\n",
       "      <td>6</td>\n",
       "      <td>California</td>\n",
       "      <td>65</td>\n",
       "      <td>Riverside</td>\n",
       "      <td>40.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>91</th>\n",
       "      <td>48</td>\n",
       "      <td>Texas</td>\n",
       "      <td>141</td>\n",
       "      <td>El Paso</td>\n",
       "      <td>40.0</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "     state_code  state_name  county_code    county_name   aqi\n",
       "76            6  California           37    Los Angeles  93.0\n",
       "146           6  California           37    Los Angeles  59.0\n",
       "41            6  California           83  Santa Barbara  47.0\n",
       "122           6  California           59         Orange  47.0\n",
       "184           6  California           59         Orange  47.0\n",
       "51           48       Texas          141        El Paso  47.0\n",
       "80            6  California           65      Riverside  43.0\n",
       "136          48       Texas          141        El Paso  40.0\n",
       "58            6  California           65      Riverside  40.0\n",
       "91           48       Texas          141        El Paso  40.0"
      ]
     },
     "execution_count": 6,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# 1. ### YOUR CODE HERE ###\n",
    "top3_sorted = top3.sort_values(by='aqi', ascending=False)\n",
    "\n",
    "# 2. ### YOUR CODE HERE ###\n",
    "top3_sorted.head(10)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {
    "id": "vm2rJJLJ957E"
   },
   "source": [
    "<details>\n",
    "  <summary><h4><strong>Hint 1</strong></h4></summary>\n",
    "\n",
    "Attach the `sort_values()` method to the `top3` dataframe using dot notation.\n",
    "\n",
    "</details>"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {
    "id": "Y0WNm3s7-LLl"
   },
   "source": [
    "<details>\n",
    "  <summary><h4><strong>Hint 2</strong></h4></summary>\n",
    "\n",
    "*  The `by` argument of the `sort_values()` method should be a string of the column you want to sort by.\n",
    "\n",
    "*  The default behavior of the `sort_values()` method is to sort in ascending order. You want to sort in *descending* order. Which keyword argument modifies this behavior? Refer to the [sort_values() pandas documentation](https://pandas.pydata.org/docs/reference/api/pandas.DataFrame.sort_values.html#).\n",
    "\n",
    "</details>"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {
    "id": "CO7-6T3E_YQm"
   },
   "source": [
    "<details>\n",
    "  <summary><h4><strong>Hint 3</strong></h4></summary>\n",
    "\n",
    "*  Use the `head()` method on the `top3_sorted` dataframe.\n",
    "\n",
    "*  The default behavior of the `head()` method is to print the first five rows of a dataframe. You want to print the first 10 rows. Refer to the [head() pandas documentation](https://pandas.pydata.org/docs/reference/api/pandas.DataFrame.head.html) for more information on how to modify this behavior.\n",
    "\n",
    "\n",
    "</details>"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {
    "id": "MJW_H8ByCnp6"
   },
   "source": [
    "### 3c: Use `iloc` to select rows\n",
    "\n",
    "Use `iloc` to select the two rows at indices 10 and 11 of the `top3_sorted` dataframe."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 7,
   "metadata": {
    "colab": {
     "base_uri": "https://localhost:8080/",
     "height": 112
    },
    "executionInfo": {
     "elapsed": 39,
     "status": "ok",
     "timestamp": 1679932927639,
     "user": {
      "displayName": "Matteo Riotto",
      "userId": "15211004813978485634"
     },
     "user_tz": 420
    },
    "id": "lJf96gRyif8y",
    "outputId": "70bfc39d-0f15-4cd7-bba3-f499cf92a4c3"
   },
   "outputs": [
    {
     "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>state_code</th>\n",
       "      <th>state_name</th>\n",
       "      <th>county_code</th>\n",
       "      <th>county_name</th>\n",
       "      <th>aqi</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>186</th>\n",
       "      <td>6</td>\n",
       "      <td>California</td>\n",
       "      <td>73</td>\n",
       "      <td>San Diego</td>\n",
       "      <td>39.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>74</th>\n",
       "      <td>6</td>\n",
       "      <td>California</td>\n",
       "      <td>37</td>\n",
       "      <td>Los Angeles</td>\n",
       "      <td>38.0</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "     state_code  state_name  county_code  county_name   aqi\n",
       "186           6  California           73    San Diego  39.0\n",
       "74            6  California           37  Los Angeles  38.0"
      ]
     },
     "execution_count": 7,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "### YOUR CODE HERE ###\n",
    "top3_sorted.iloc[10:12]"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {
    "id": "HKGPz1WGD4PU"
   },
   "source": [
    "<details>\n",
    "  <summary><h4><strong>Hint 1</strong></h4></summary>\n",
    "\n",
    "To use `iloc` on the `top3_sorted` dataframe, use dot notation.\n",
    "\n",
    "</details>"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {
    "id": "-JIb7TeBElWU"
   },
   "source": [
    "<details>\n",
    "  <summary><h4><strong>Hint 2</strong></h4></summary>\n",
    "\n",
    "`iloc` uses brackets to index data.\n",
    "\n",
    "</details>"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {
    "id": "wdT9EWTME0xc"
   },
   "source": [
    "<details>\n",
    "  <summary><h4><strong>Hint 3</strong></h4></summary>\n",
    "\n",
    "`iloc` index ranges are separated by a colon. Remember, the end index is not included in the range of returned indices.\n",
    "\n",
    "</details>"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {
    "id": "vTCia-aGFbbs"
   },
   "source": [
    "## Task 4: Examine California data\n",
    "\n",
    "You notice that the rows with the highest AQI represent data from California, so you want to examine the data for just the state of California."
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {
    "id": "veJeGG1HGQLe"
   },
   "source": [
    "### 4a: Basic Boolean masking\n",
    "\n",
    "1. Create a Boolean mask that selects only the observations of the `top3_sorted` dataframe that are from California.\n",
    "2. Apply the Boolean mask to the `top3_sorted` dataframe and assign the result to a variable called `ca_df`.\n",
    "3. Print the first five rows of `ca_df`."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 8,
   "metadata": {
    "colab": {
     "base_uri": "https://localhost:8080/",
     "height": 206
    },
    "executionInfo": {
     "elapsed": 39,
     "status": "ok",
     "timestamp": 1679932927640,
     "user": {
      "displayName": "Matteo Riotto",
      "userId": "15211004813978485634"
     },
     "user_tz": 420
    },
    "id": "9Ysa4Pj-TEyk",
    "outputId": "a81d5861-4a9f-48c6-f66e-871731cd0ce9"
   },
   "outputs": [
    {
     "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>state_code</th>\n",
       "      <th>state_name</th>\n",
       "      <th>county_code</th>\n",
       "      <th>county_name</th>\n",
       "      <th>aqi</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>76</th>\n",
       "      <td>6</td>\n",
       "      <td>California</td>\n",
       "      <td>37</td>\n",
       "      <td>Los Angeles</td>\n",
       "      <td>93.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>146</th>\n",
       "      <td>6</td>\n",
       "      <td>California</td>\n",
       "      <td>37</td>\n",
       "      <td>Los Angeles</td>\n",
       "      <td>59.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>41</th>\n",
       "      <td>6</td>\n",
       "      <td>California</td>\n",
       "      <td>83</td>\n",
       "      <td>Santa Barbara</td>\n",
       "      <td>47.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>122</th>\n",
       "      <td>6</td>\n",
       "      <td>California</td>\n",
       "      <td>59</td>\n",
       "      <td>Orange</td>\n",
       "      <td>47.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>184</th>\n",
       "      <td>6</td>\n",
       "      <td>California</td>\n",
       "      <td>59</td>\n",
       "      <td>Orange</td>\n",
       "      <td>47.0</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "     state_code  state_name  county_code    county_name   aqi\n",
       "76            6  California           37    Los Angeles  93.0\n",
       "146           6  California           37    Los Angeles  59.0\n",
       "41            6  California           83  Santa Barbara  47.0\n",
       "122           6  California           59         Orange  47.0\n",
       "184           6  California           59         Orange  47.0"
      ]
     },
     "execution_count": 8,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# 1. ### YOUR CODE HERE ###\n",
    "mask = top3_sorted['state_name'] == 'California'\n",
    "\n",
    "# 2. ### YOUR CODE HERE ###\n",
    "ca_df = top3_sorted[mask]\n",
    "\n",
    "# 3. ### YOUR CODE HERE ###\n",
    "ca_df.head()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {
    "id": "PZo-yknRIZp6"
   },
   "source": [
    "<details>\n",
    "  <summary><h4><strong>Hint 1</strong></h4></summary>\n",
    "\n",
    "Refer to what you've learned about Boolean masking.\n",
    "\n",
    "</details>"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {
    "id": "2HfVOQEPIm3x"
   },
   "source": [
    "<details>\n",
    "  <summary><h4><strong>Hint 2</strong></h4></summary>\n",
    "\n",
    "Define a `mask` variable that is the `top3_sorted` dataframe selected where the `state_name` column is `California`.\n",
    "\n",
    "</details>"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {
    "id": "O4c0AXclJCf5"
   },
   "source": [
    "<details>\n",
    "  <summary><h4><strong>Hint 3</strong></h4></summary>\n",
    "\n",
    "*  Apply the `mask` variable to the `top3_sorted` dataframe and assign the result to a new dataframe called `ca_df`.\n",
    "\n",
    "*  Use the `head()` method on the new `ca_df` dataframe.\n",
    "</details>"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {
    "id": "QcP9jneZJoFt"
   },
   "source": [
    "### 4b: Validate CA data\n",
    "\n",
    "Inspect the shape of your new `ca_df` dataframe. Does its row count match the number of California rows determined in Task 3a?"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 9,
   "metadata": {
    "colab": {
     "base_uri": "https://localhost:8080/"
    },
    "executionInfo": {
     "elapsed": 39,
     "status": "ok",
     "timestamp": 1679932927641,
     "user": {
      "displayName": "Matteo Riotto",
      "userId": "15211004813978485634"
     },
     "user_tz": 420
    },
    "id": "ZdpJLggBgV5B",
    "outputId": "1ec24aea-3e02-45a5-f40a-54fff1670d77"
   },
   "outputs": [
    {
     "data": {
      "text/plain": [
       "(342, 5)"
      ]
     },
     "execution_count": 9,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "### YOUR CODE HERE ###\n",
    "ca_df.shape"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {
    "id": "KT4MTiqhK3Ml"
   },
   "source": [
    "<details>\n",
    "  <summary><h4><strong>Hint</strong></h4></summary>\n",
    "\n",
    "*   Use the `shape` attribute on the `ca_df` dataframe.\n",
    "\n",
    "*   Attributes don't use parentheses.\n",
    "\n",
    "\n",
    "</details>"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {
    "id": "yOBWRRiOL3AI"
   },
   "source": [
    "### 4c: Rows per CA county\n",
    "\n",
    "Examine a list of the number of times each county is represented in the California data."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 10,
   "metadata": {
    "colab": {
     "base_uri": "https://localhost:8080/"
    },
    "executionInfo": {
     "elapsed": 36,
     "status": "ok",
     "timestamp": 1679932927642,
     "user": {
      "displayName": "Matteo Riotto",
      "userId": "15211004813978485634"
     },
     "user_tz": 420
    },
    "id": "bJCi2FyhVCMI",
    "outputId": "c8167244-40b5-47b4-e1f7-613b8fa9dad6"
   },
   "outputs": [
    {
     "data": {
      "text/plain": [
       "Los Angeles        55\n",
       "Santa Barbara      26\n",
       "San Bernardino     21\n",
       "Orange             19\n",
       "San Diego          19\n",
       "Sacramento         17\n",
       "Alameda            17\n",
       "Fresno             16\n",
       "Riverside          14\n",
       "Contra Costa       13\n",
       "Imperial           13\n",
       "San Francisco       8\n",
       "Monterey            8\n",
       "Humboldt            8\n",
       "Santa Clara         7\n",
       "El Dorado           7\n",
       "Placer              6\n",
       "Butte               6\n",
       "Kern                6\n",
       "Mendocino           6\n",
       "Solano              5\n",
       "San Joaquin         5\n",
       "Tulare              5\n",
       "Ventura             5\n",
       "Sutter              4\n",
       "San Mateo           4\n",
       "Marin               3\n",
       "Sonoma              3\n",
       "Stanislaus          3\n",
       "San Luis Obispo     2\n",
       "Napa                2\n",
       "Santa Cruz          2\n",
       "Calaveras           2\n",
       "Shasta              1\n",
       "Tuolumne            1\n",
       "Inyo                1\n",
       "Yolo                1\n",
       "Mono                1\n",
       "Name: county_name, dtype: int64"
      ]
     },
     "execution_count": 10,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "### YOUR CODE HERE ###\n",
    "ca_df['county_name'].value_counts()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {
    "id": "MVyb4JVrMXX2"
   },
   "source": [
    "<details>\n",
    "  <summary><h4><strong>Hint</strong></h4></summary>\n",
    "\n",
    "Select the `county_name` column of `ca_df` and apply the `value_counts()` method to it using dot notation.\n",
    "\n",
    "</details>"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {
    "id": "25cj2ITKM2Z2"
   },
   "source": [
    "### 4d: Calculate mean AQI for Los Angeles county\n",
    "\n",
    "You notice that Los Angeles county has more than twice the number of rows of the next-most-represented county in California, and you want to learn more about it.\n",
    "\n",
    "*  Calculate the mean AQI for LA county."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 11,
   "metadata": {
    "colab": {
     "base_uri": "https://localhost:8080/"
    },
    "executionInfo": {
     "elapsed": 33,
     "status": "ok",
     "timestamp": 1679932927642,
     "user": {
      "displayName": "Matteo Riotto",
      "userId": "15211004813978485634"
     },
     "user_tz": 420
    },
    "id": "H4hTyZf5rC9E",
    "outputId": "1c329d79-0a95-40e1-d203-de37f514cb6c"
   },
   "outputs": [
    {
     "data": {
      "text/plain": [
       "13.4"
      ]
     },
     "execution_count": 11,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "### YOUR CODE HERE ###\n",
    "mask = ca_df['county_name'] == 'Los Angeles'\n",
    "ca_df[mask]['aqi'].mean()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {
    "id": "ss1C32s5NoIB"
   },
   "source": [
    "<details>\n",
    "  <summary><h4><strong>Hint 1</strong></h4></summary>\n",
    "\n",
    "Use Boolean masking to create a mask. Then apply the mask to the `ca_df` dataframe, select the `aqi` column of the result, and calculate its mean.\n",
    "\n",
    "</details>"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {
    "id": "6N5K3km7OFj4"
   },
   "source": [
    "<details>\n",
    "  <summary><h4><strong>Hint 2</strong></h4></summary>\n",
    "\n",
    "The Boolean mask is `ca_df` selected where `county_name` is `Los Angeles`.\n",
    "\n",
    "</details>"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {
    "id": "ptRizlhROkhC"
   },
   "source": [
    "<details>\n",
    "  <summary><h4><strong>Hint 3</strong></h4></summary>\n",
    "\n",
    "*  Apply the Boolean mask to `ca_df`.\n",
    "*  Then use selector brackets to select the `aqi` column.\n",
    "*  Then use dot notation to attach the `mean()` method to the end of the expression.\n",
    "\n",
    "</details>"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {
    "id": "YyMgRk1KPWYL"
   },
   "source": [
    "## Task 5: Groupby\n",
    "\n",
    "Group the original dataframe (`top3`) by state and calculate the mean AQI for each state."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 12,
   "metadata": {
    "colab": {
     "base_uri": "https://localhost:8080/",
     "height": 175
    },
    "executionInfo": {
     "elapsed": 31,
     "status": "ok",
     "timestamp": 1679932927643,
     "user": {
      "displayName": "Matteo Riotto",
      "userId": "15211004813978485634"
     },
     "user_tz": 420
    },
    "id": "w9MlkTjDk2jj",
    "outputId": "b88f2caa-2f40-406d-d9e4-2780d8f2f8f4"
   },
   "outputs": [
    {
     "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>aqi</th>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>state_name</th>\n",
       "      <th></th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>California</th>\n",
       "      <td>9.412281</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Pennsylvania</th>\n",
       "      <td>6.690000</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Texas</th>\n",
       "      <td>9.375000</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "                   aqi\n",
       "state_name            \n",
       "California    9.412281\n",
       "Pennsylvania  6.690000\n",
       "Texas         9.375000"
      ]
     },
     "execution_count": 12,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "### YOUR CODE HERE ###\n",
    "top3.groupby('state_name').mean()[['aqi']]"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {
    "id": "fpgs3ZqdQYJt"
   },
   "source": [
    "<details>\n",
    "  <summary><h4><strong>Hint 1</strong></h4></summary>\n",
    "\n",
    "Use the `groupby()` method on the `top3` dataframe.\n",
    "\n",
    "</details>"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {
    "id": "8wOezEbbQhs8"
   },
   "source": [
    "<details>\n",
    "  <summary><h4><strong>Hint 2</strong></h4></summary>\n",
    "\n",
    "Group by `state_name` and use dot notation to chain the `mean()` method to the expression.\n",
    "\n",
    "</details>"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {
    "id": "-IqJn1yBRUFX"
   },
   "source": [
    "<details>\n",
    "  <summary><h4><strong>Hint 3</strong></h4></summary>\n",
    "\n",
    "`top3.groupby('state_name').mean()` will produce a table of the mean values of every numeric column for each state. To filter the table on just the `aqi` column, add `['aqi']` to the end of the expression.\n",
    "\n",
    "</details>"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {
    "id": "e0Ls3OueR8LN"
   },
   "source": [
    "## Task 6: Add more data\n",
    "\n",
    "Now that you have performed a short examination of the file with AQI data for California, Texas, and Pennsylvania, you want to add more data from your second file."
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {
    "id": "Um7-XdnMSkHG"
   },
   "source": [
    "### 6a: Read in the second file\n",
    "\n",
    "1. Read in the data for the remaining territories. The file is called `'epa_others.csv'` and is already in your working directory. Assign the resulting dataframe to a variable named `other_states`.\n",
    "\n",
    "2. Use the `head()` method on the `other_states` dataframe to inspect the first five rows."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 13,
   "metadata": {
    "colab": {
     "base_uri": "https://localhost:8080/",
     "height": 206
    },
    "executionInfo": {
     "elapsed": 30,
     "status": "ok",
     "timestamp": 1679932927644,
     "user": {
      "displayName": "Matteo Riotto",
      "userId": "15211004813978485634"
     },
     "user_tz": 420
    },
    "id": "Qze2SW5Nl6rg",
    "outputId": "13b613fa-0fef-40db-8425-03cc743b7e6b"
   },
   "outputs": [
    {
     "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>state_code</th>\n",
       "      <th>state_name</th>\n",
       "      <th>county_code</th>\n",
       "      <th>county_name</th>\n",
       "      <th>aqi</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>4</td>\n",
       "      <td>Arizona</td>\n",
       "      <td>13</td>\n",
       "      <td>Maricopa</td>\n",
       "      <td>18.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>4</td>\n",
       "      <td>Arizona</td>\n",
       "      <td>13</td>\n",
       "      <td>Maricopa</td>\n",
       "      <td>9.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>4</td>\n",
       "      <td>Arizona</td>\n",
       "      <td>19</td>\n",
       "      <td>Pima</td>\n",
       "      <td>20.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>8</td>\n",
       "      <td>Colorado</td>\n",
       "      <td>41</td>\n",
       "      <td>El Paso</td>\n",
       "      <td>9.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>12</td>\n",
       "      <td>Florida</td>\n",
       "      <td>31</td>\n",
       "      <td>Duval</td>\n",
       "      <td>15.0</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "   state_code state_name  county_code county_name   aqi\n",
       "0           4    Arizona           13    Maricopa  18.0\n",
       "1           4    Arizona           13    Maricopa   9.0\n",
       "2           4    Arizona           19        Pima  20.0\n",
       "3           8   Colorado           41     El Paso   9.0\n",
       "4          12    Florida           31       Duval  15.0"
      ]
     },
     "execution_count": 13,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# 1. ### YOUR CODE HERE ###\n",
    "other_states = pd.read_csv('epa_others.csv')\n",
    "\n",
    "# 2. ### YOUR CODE HERE ###\n",
    "other_states.head()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {
    "id": "UWSSzw-4TNVv"
   },
   "source": [
    "### 6b: Concatenate the data\n",
    "\n",
    "The data from `other_states` is in the same format as the data from `top3`. It has the same columns in the same order.\n",
    "\n",
    "1. Add the data from `other_states` as new rows beneath the data from `top3`. Assign the result to a new dataframe called `combined_df`.\n",
    "\n",
    "2. Verify that the length of `combined_df` is equal to the sum of the lengths of `top3` and `other_states`."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 14,
   "metadata": {
    "colab": {
     "base_uri": "https://localhost:8080/"
    },
    "executionInfo": {
     "elapsed": 29,
     "status": "ok",
     "timestamp": 1679932927644,
     "user": {
      "displayName": "Matteo Riotto",
      "userId": "15211004813978485634"
     },
     "user_tz": 420
    },
    "id": "Jawta5WosDoW",
    "outputId": "f71829ad-acc1-46c0-9231-558fc1fc7e85"
   },
   "outputs": [
    {
     "data": {
      "text/plain": [
       "True"
      ]
     },
     "execution_count": 14,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# 1. ### YOUR CODE HERE ###\n",
    "combined_df = pd.concat([top3, other_states], axis=0)\n",
    "\n",
    "# 2. ### YOUR CODE HERE ###\n",
    "len(combined_df) == len(top3) + len(other_states)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {
    "id": "ZQhlUn1UUkro"
   },
   "source": [
    "<details>\n",
    "  <summary><h4><strong>Hint 1</strong></h4></summary>\n",
    "\n",
    "Use the `concat()` function. For more information on this function, refer to the [concat() pandas documentation](https://pandas.pydata.org/docs/reference/api/pandas.concat.html).\n",
    "\n",
    "</details>"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {
    "id": "iMrsBa9eU0yq"
   },
   "source": [
    "<details>\n",
    "  <summary><h4><strong>Hint 2</strong></h4></summary>\n",
    "\n",
    "*  Enter the two dataframes being joined as a list in the argument field of the `concat()` function.\n",
    "\n",
    "*  To add rows, concatenate along axis 0. To add columns, concatenate along axis 1.\n",
    "\n",
    "</details>"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {
    "id": "luRYFeOZV3Cq"
   },
   "source": [
    "<details>\n",
    "  <summary><h4><strong>Hint 3</strong></h4></summary>\n",
    "\n",
    "Use the `len()` function in a comparison statement to determine if the length of `combined_df` equals the length of `top3` plus the length of `other_states`.\n",
    "\n",
    "</details>"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {
    "id": "OHlADErxYoH4"
   },
   "source": [
    "## Task 7: Complex Boolean masking\n",
    "\n",
    "According to the EPA, AQI values of 51-100 are considered of \"Moderate\" concern. You've been tasked with examining some data for the state of Washington.\n",
    "\n",
    "*  Use Boolean masking to return the rows that represent data from the state of Washington with AQI values of 51+."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 15,
   "metadata": {
    "colab": {
     "base_uri": "https://localhost:8080/",
     "height": 175
    },
    "executionInfo": {
     "elapsed": 27,
     "status": "ok",
     "timestamp": 1679932927645,
     "user": {
      "displayName": "Matteo Riotto",
      "userId": "15211004813978485634"
     },
     "user_tz": 420
    },
    "id": "L_O-gFlBY6h7",
    "outputId": "e1830dcb-e1cf-4cb6-e8ec-45df5c63605a"
   },
   "outputs": [
    {
     "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>state_code</th>\n",
       "      <th>state_name</th>\n",
       "      <th>county_code</th>\n",
       "      <th>county_name</th>\n",
       "      <th>aqi</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>40</th>\n",
       "      <td>53</td>\n",
       "      <td>Washington</td>\n",
       "      <td>33</td>\n",
       "      <td>King</td>\n",
       "      <td>55.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>82</th>\n",
       "      <td>53</td>\n",
       "      <td>Washington</td>\n",
       "      <td>61</td>\n",
       "      <td>Snohomish</td>\n",
       "      <td>76.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>121</th>\n",
       "      <td>53</td>\n",
       "      <td>Washington</td>\n",
       "      <td>77</td>\n",
       "      <td>Yakima</td>\n",
       "      <td>58.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>122</th>\n",
       "      <td>53</td>\n",
       "      <td>Washington</td>\n",
       "      <td>77</td>\n",
       "      <td>Yakima</td>\n",
       "      <td>57.0</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "     state_code  state_name  county_code county_name   aqi\n",
       "40           53  Washington           33        King  55.0\n",
       "82           53  Washington           61   Snohomish  76.0\n",
       "121          53  Washington           77      Yakima  58.0\n",
       "122          53  Washington           77      Yakima  57.0"
      ]
     },
     "execution_count": 15,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "### YOUR CODE HERE ###\n",
    "mask = (combined_df['state_name'] == 'Washington') & (combined_df['aqi'] >= 51)\n",
    "combined_df[mask]"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {
    "id": "yCfp-RVwaYdn"
   },
   "source": [
    "<details>\n",
    "  <summary><h4><strong>Hint 1</strong></h4></summary>\n",
    "\n",
    "Create a Boolean mask for `combined_df` with two conditions:\n",
    "\n",
    "*   The state is Washington\n",
    "*   The AQI is greater than or equal to 51\n",
    "\n",
    "</details>"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {
    "id": "4eKEkghfbAQp"
   },
   "source": [
    "<details>\n",
    "  <summary><h4><strong>Hint 2</strong></h4></summary>\n",
    "\n",
    "Remember to enclose each condition in its own set of parentheses.\n",
    "\n",
    "</details>"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {
    "id": "gu7UQtCObGSS"
   },
   "source": [
    "<details>\n",
    "  <summary><h4><strong>Hint 3</strong></h4></summary>\n",
    "\n",
    "Separate the two conditions with the `&` operator, because both conditions need to evaluate as true for the row to be included in the filtered dataframe.\n",
    "\n",
    "</details>"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {
    "id": "O44oebM4PqaS"
   },
   "source": [
    "## Conclusion\n",
    "\n",
    "**What are your key takeaways from this lab?**"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {
    "id": "_7cqRMnyPz0A"
   },
   "source": [
    "pandas is a powerful tool for working with data in Python because:\n",
    "* It it comes with many built-in functions and tools specifically designed for use with tabular data to simplify common tasks such as:\n",
    "    * Reading and writing data to/from files\n",
    "    * Quickly computing summary statistics about your data\n",
    "    * Manipulating, selecting, and filtering data\n",
    "    * Grouping and aggregating data\n",
    "    * Adding new data to existing data\n",
    "\n",
    "* It's powered by NumPy, which uses the power of array operations to enhance performance.\n",
    "* Its interface makes working with tabular data easy because it allows you to visualize your data in rows and columns."
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "**Congratulations!** You've completed this lab. However, you may not notice a green check mark next to this item on Coursera's platform. Please continue your progress regardless of the check mark. Just click on the \"save\" icon at the top of this notebook to ensure your work has been logged. "
   ]
  }
 ],
 "metadata": {
  "colab": {
   "collapsed_sections": [
    "CtORKvT6aSqd"
   ],
   "provenance": [],
   "toc_visible": true
  },
  "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.6"
  }
 },
 "nbformat": 4,
 "nbformat_minor": 1
}