5 - Data Wrangling with Pandas.ipynb

{
 "cells": [
  {
   "cell_type": "markdown",
   "id": "e0450c47-4cc6-4009-980c-cdac1fc5372b",
   "metadata": {},
   "source": [
    "# Data Wrangling with Pandas\n",
    "\n",
    "This lesson introduces the topics and techniques for merging and reshaping your DataFrames. \n",
    "\n",
    "While mostly complete, parts of this lesson are ![under construction](http://textfiles.com/underconstruction/EnEnchantedForestMountain6559Construction_3.gif)\n",
    "\n",
    "## Topics\n",
    " \n",
    " - concatenating datasets vertically - community center attendance (split by year)\n",
    " - Pivot from long to wide data - community center \n",
    " - Merging Data\n",
    "     - One to one - 2015 and 2010 neighborhood populations (note, there is a neighborhood missing in the 2010 data)\n",
    "         - inner, left, right, outer - play with these options to see what neighborhood is missing\n",
    "     - One to many - counts of 311 requests per neighborhood then join with a dataset with ACS 2015 population by neighborhood. calculate 311 requests per population rankings\n",
    "     \n",
    "     \n",
    "## Learning Objectives\n",
    "\n",
    "- ![under construction gif](http://textfiles.com/underconstruction/mamagnolia_acresunderconstruction.gif)\n",
    "\n",
    "     \n",
    "     \n",
    "## Datasets used in this lesson\n",
    "\n",
    "- [Daily Community Center Attendance](https://data.wprdc.org/dataset/daily-community-center-attendance) - This datasets contains information about the number of people who visited a community center in the city of Pittsburgh per day. Each row represents a day, community center, and count. Note, not all community centers report their attendance each day. The dataset spans a temporal period from 2011 to 2023 and has around TKTKTK entries.\n",
    "    - This dataset has been broken into individual CSV files. Each file represents a year. The data files are located in the `community center attendance by year` directory. Each file has a name with a pattern of `community-center-attendance-YYYY.csv`.\n",
    "- [311 Service Requests](https://data.wprdc.org/dataset/311-data) - This dataset contains information about the 311 information and service requests made to the city of Pittsburgh. Each row represents an individual service request with a bunch of meta-information about the request include source, time, neighborhood origin, and request type. The dataset spans a temporal period from TKTKTK to TKTKTK and has around 680 thousand entries.\n",
    "    - The dataset is stored as a CSV file with the name `311-data.csv`\n",
    "- [American Community Survey 2015 Total Population](https://data.wprdc.org/dataset/pittsburgh-american-community-survey-2015-miscellaneous-data/resource/82f29015-6905-4b1c-8300-afe9bb2231b3) - This dataset contains information about the total population for each neighborhood in the city of Pittsburgh based upon data from the 2015 American Community Survey. Each row represents the total population estimates for a neighborhood. The dataset spans the 2015 community survey and has 91 entries. \n",
    "    - The dataset is stored as a CSV file with the name `ACS-2015-total-population.csv`\n",
    "- [Pittsburgh Neighborhood Data](https://ucsur.pitt.edu/files/census/UCSUR_SF1_NeighborhoodProfiles_July2011.pdf) - This dataset was extracted from a 2010 Pittsburgh Neighborhood Profiles PDF document. This dataset includes rankings of each neighborhood across a variety of metrics including population, age, race, and housing. The dataset represents information from 2010. Each row is a neighborhood and there are 90 entries.\n",
    "    - The datset is stored as a CSV file with the name `pgh-neighborhood-data-2010.csv`\n",
    "- [311 Issue and Category Codebook](https://data.wprdc.org/dataset/311-data/resource/7794b313-33be-4a8b-bf80-41751a59b84a) - This dataset contains information describing the 311 Request Types and mapping them to higher level categories. Each row represents a request type and includes categorical columns with information about the category, the definition, and the responsible department. The dataset contains 231 entries.\n",
    "    - The dataset is stored as an Excel file with the name `311-codebook-request-types.xlsx`\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "79aa9479-b2c3-46c9-9a8f-f726123da0cc",
   "metadata": {},
   "outputs": [],
   "source": [
    "# Load Pandas\n",
    "import pandas as pd"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "43972c3d-32f0-4d31-88ae-5142d542c6d2",
   "metadata": {},
   "source": [
    "## Concatenating Datasets\n",
    "\n",
    "The community center attendance datasets are broken up by year, but they all share the same shape. Each dataset has three columns that represent the same information. This means you can easily \n",
    "\n",
    "Pandas provides a top level function [concat](https://pandas.pydata.org/docs/reference/api/pandas.concat.html) that can merge dataframes together.\n",
    "\n",
    "#### Task - Review the Concat documentation\n",
    "\n",
    "1. Look at the documentation for [concat](https://pandas.pydata.org/docs/reference/api/pandas.concat.html). What Python or pandas data structure should you use to store the dataframes we need to concatenate?"
   ]
  },
  {
   "cell_type": "raw",
   "id": "c9cc3c3d-974c-4fe3-82d6-6152e1f0946d",
   "metadata": {},
   "source": [
    "# your answer here"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "c03e5656-0800-422c-9ed1-3d9860b1d507",
   "metadata": {},
   "source": [
    "#### Answer - Review the Concat Documentation\n",
    "Click on the ellipses (...) below to see the answers."
   ]
  },
  {
   "cell_type": "markdown",
   "id": "db5f4a0a-2e53-444b-a678-2e57f74a5c24",
   "metadata": {
    "jupyter": {
     "source_hidden": true
    },
    "tags": []
   },
   "source": [
    "Python list or Pandas series"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "bf4defc6-8a9e-4e54-944d-717b6ce6c71f",
   "metadata": {},
   "source": [
    "#### Task - Load three Community Center Data Files\n",
    "\n",
    "1. Load the daily community center attendance for 2023, 2022, and 2021 into a pandas dataframe.\n",
    "2. Create the Python data structure you identified in the previoud task to contain each of the three dataframes. Set each item to be one loaded dataframes."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "8fd2fabe-1467-4924-a73f-d932a0de6f1a",
   "metadata": {},
   "outputs": [],
   "source": [
    "# your code here\n"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "c0594b8c-7777-4da0-894c-8abcd4e6c250",
   "metadata": {},
   "source": [
    "#### Answer - Load three Community Center Data Files\n",
    "\n",
    "Click on the ellipses (...) below to see the answers."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "e6a6ca5f-00fd-40f0-b47c-a8af259c760d",
   "metadata": {
    "jupyter": {
     "source_hidden": true
    },
    "tags": []
   },
   "outputs": [],
   "source": [
    "# answer 1\n",
    "cca_2023 = pd.read_csv(\"datasets/community-attendance/community-center-attendance-2023.csv\")\n",
    "cca_2022 = pd.read_csv(\"datasets/community-attendance/community-center-attendance-2022.csv\")\n",
    "cca_2021 = pd.read_csv(\"datasets/community-attendance/community-center-attendance-2021.csv\")\n",
    "\n",
    "# answer 2\n",
    "cca_list = [cca_2023, cca_2022, cca_2021]"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "dab32bca-0ec8-4f69-ac32-082e157555df",
   "metadata": {},
   "source": [
    "#### Task - Join community center datasets into one\n",
    "\n",
    "1. Use the Pandas Concat function to merge the three datasets into a single dataframe"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "5cf93719-b695-4180-8209-c8d8de901ad6",
   "metadata": {},
   "outputs": [],
   "source": [
    "# your code here\n"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "bafaaa94-f0ac-439f-bfb4-3f1ad73549f6",
   "metadata": {},
   "source": [
    "#### Answer - Join Community Center datasets into one\n",
    "\n",
    "Click on the ellipses (...) below to see the answers."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "63297d8b-57aa-471d-8553-a4bf45bd79e4",
   "metadata": {
    "jupyter": {
     "source_hidden": true
    },
    "tags": []
   },
   "outputs": [],
   "source": [
    "# answer 1\n",
    "\n",
    "cca = pd.concat(cca_list)\n",
    "cca"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "604d3a61-60d2-40d9-9c66-729d521c5ca5",
   "metadata": {},
   "source": [
    "## Loading all of the Community Center Attendance datasets\n",
    "\n",
    "The daily community center attendance datasets are broken up by year. Each year is stored as a separate file. We have included 13 years worth of data, this means there are 13 separate dataframes you need to load if you want to merge them together. While you could manually load each dataframe into a separate variable, you can also automate this process with Python\n",
    "\n",
    "- Generate a list of community center attendance data file paths\n",
    "- Loop over the list of file paths\n",
    "    - load the csv file into a dataframe\n",
    "    - add that dataframe to a list\n",
    "- Concatenate the list of dataframes into a single dataframe \n",
    "\n",
    "\n",
    "#### Task - Create a list of Community Center Filenames\n",
    "\n",
    "1. Create a Python list of 13 strings, each string should represent the path to one of the community center attendance data files. So for example, the string representing the 2023 data file would be: `\"community-attendance/community-center-attendance-2023.csv\"`"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "d5905940-5a95-4be6-97bc-0289329fce31",
   "metadata": {},
   "outputs": [],
   "source": [
    "# your code here\n"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "f825bfd8-fc89-43db-b8b2-70d323ddd2ac",
   "metadata": {},
   "source": [
    "#### Answer - Create Community Center Filenames\n",
    "\n",
    "Click on the ellipses (...) below to see the answers."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "9224bd19-cfc8-4ba1-8947-013d31dac2c1",
   "metadata": {
    "jupyter": {
     "source_hidden": true
    },
    "tags": []
   },
   "outputs": [],
   "source": [
    "# answer 1\n",
    "cca_filepaths = [f\"datasets/community-attendance/community-center-attendance-{year}.csv\" for year in range(2011,2024)]\n",
    "cca_filepaths"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "73cbff6a-aaf5-433e-8604-540c488f0b4a",
   "metadata": {},
   "source": [
    "#### Task - Loading each datafile\n",
    "\n",
    "1. Use a Python loop or list comprehension to loop over the list of filepaths and load each dataframe as an item in a Python list"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "88617c77-2a46-45dd-b23b-7a75bef8e0a0",
   "metadata": {},
   "outputs": [],
   "source": [
    "# your code here\n"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "3213b385-d8ea-4637-89a9-7d06eb880dd9",
   "metadata": {},
   "source": [
    "#### Answer - Loading Each Datafiles\n",
    "\n",
    "Click on the ellipses (...) below to see the answers."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "342b838d-22a2-49e8-8ef3-1514677d4d49",
   "metadata": {
    "jupyter": {
     "source_hidden": true
    },
    "tags": []
   },
   "outputs": [],
   "source": [
    "# answer 1\n",
    "cca_dataframes = []\n",
    "\n",
    "for filepath in cca_filepaths:\n",
    "    cca_dataframes.append(pd.read_csv(filepath))\n",
    "\n",
    "cca_dataframes[0:2]"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "9e877cd4-235a-4730-9492-72fd936bbff3",
   "metadata": {},
   "source": [
    "#### Task - Concatenate all the dataframes\n",
    "\n",
    "1. Use the Pandas concat function to merge all of the community center attendance data into a single dataframe"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "7b2f5789-87d8-4a10-8920-6ac799f4c85a",
   "metadata": {},
   "outputs": [],
   "source": [
    "# your code here"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "e95927d5-97e9-4d7a-b00a-403b343e5a85",
   "metadata": {},
   "source": [
    "#### Answer - Concatenate all the dataframes\n",
    "\n",
    "Click on the ellipses (...) below to see the answers."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "826c1711-0333-4521-9b64-3895426aa94c",
   "metadata": {
    "jupyter": {
     "source_hidden": true
    },
    "tags": []
   },
   "outputs": [],
   "source": [
    "# answer 1\n",
    "\n",
    "cca_data = pd.concat(cca_dataframes)\n",
    "cca_data"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "62499d47-649c-42af-ba40-80326c6f047b",
   "metadata": {},
   "source": [
    "## Pivoting Dataframes\n",
    "\n",
    "The Community Center Attendance is structured as [long, narrow or stacked](https://en.wikipedia.org/wiki/Wide_and_narrow_data)\n",
    "\n",
    "|date      |center_name                |attendance_count|\n",
    "|----------|---------------------------|----------------|\n",
    "|2023-03-12|Warrington Community Center|3               |\n",
    "|2023-03-11|Warrington Community Center|1               |\n",
    "|2023-03-11|Ormsby Community Center    |5               |\n",
    "|2023-03-11|Paulson Community Center   |12              |\n",
    "|2023-03-11|Phillips Community Center  |10              |\n",
    "|2023-03-11|West Penn Community Center |104             |\n",
    "|2023-03-10|Warrington Community Center|32              |\n",
    "|2023-03-10|Ammon Community Center     |8               |\n",
    "|2023-03-10|Arlington Community Center |13              |\n",
    "|2023-03-10|Brookline Community Center |40              |\n",
    "\n",
    "A different way to represent these data in a \"wider\" form would be the following: \n",
    "\n",
    "|date      |Warrington Community Center|Ormsby Community Center|Phillips Community Center|West Penn Community Center|Ammon Community Center|Arlington Community Center|Brookline Community Center|\n",
    "|----------|---------------------------|-----------------------|-------------------------|--------------------------|----------------------|--------------------------|--------------------------|\n",
    "|2023-03-08|69.0                       |35.0                   |54.0                     |48.0                      |5.0                   |84.0                      |36.0                      |\n",
    "|2023-03-09|63.0                       |11.0                   |74.0                     |135.0                     |32.0                  |15.0                      |58.0                      |\n",
    "|2023-03-10|32.0                       |11.0                   |39.0                     |73.0                      |8.0                   |13.0                      |40.0                      |\n",
    "|2023-03-11|1.0                        |5.0                    |10.0                     |104.0                     |                      |                          |                          |\n",
    "|2023-03-12|3.0                        |                       |                         |                          |                      |                          |                          |\n",
    "\n",
    "\n"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "09483725-a255-40f8-a5e3-66e65e9f76c2",
   "metadata": {
    "tags": []
   },
   "source": [
    "#### Task - Pivoting narrow data into wide data \n",
    "\n",
    "Before \n",
    "\n",
    "\n",
    "1. What does each row represent? \n",
    "2. What does each column represent?\n",
    "3. What does each value in the rows/columns represent?\n"
   ]
  },
  {
   "cell_type": "raw",
   "id": "5988f6c2-f892-40e8-a0fd-bffed52a284d",
   "metadata": {},
   "source": [
    "# you answer here\n"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "53011d81-45dc-44d2-af48-7862f506f5e8",
   "metadata": {},
   "source": [
    "#### Answer - Pivoting narrow data into wide data\n",
    "\n",
    "Click on the ellipses (...) below to see the answers."
   ]
  },
  {
   "cell_type": "markdown",
   "id": "ed41dd61-8601-498d-a6a8-a7bd2c93a33b",
   "metadata": {
    "jupyter": {
     "source_hidden": true
    },
    "tags": []
   },
   "source": [
    "\n",
    "1. a day\n",
    "2. a community center\n",
    "3. the attendance at a community center on a particular day"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "9b94a850-5e8f-49be-b5c2-ddd714e8081e",
   "metadata": {},
   "source": [
    "#### Task - Pivoting with Pandas\n",
    "\n",
    "The Pandas pivot function\n",
    "\n",
    "\n",
    "\n",
    "1. Use the Pandas Dataframe [pivot](https://pandas.pydata.org/docs/reference/api/pandas.DataFrame.pivot.html) function to transform the narrowly structured data into a wide format. Use the answers from the previous task to determine the three parameters for the pivot function."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "4bd4d41d-3e1c-4646-907f-f49742fc7db0",
   "metadata": {},
   "outputs": [],
   "source": [
    "# your code here\n"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "f80e1805-d9b5-4fff-9b2c-8f420735ddcf",
   "metadata": {},
   "source": [
    "#### Answer - Pivoting with Pandas\n",
    "\n",
    "Click on the ellipses (...) below to see the answers."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "f87f204f-f06f-4192-9a6c-72db1fd7a697",
   "metadata": {
    "jupyter": {
     "source_hidden": true
    },
    "tags": []
   },
   "outputs": [],
   "source": [
    "# answer 1\n",
    "cca_data_wide = cca_data.pivot(index=\"date\", # these values will be rows\n",
    "                          columns=\"center_name\", # these values will be columns\n",
    "                          values=\"attendance_count\" # these values will populate the table\n",
    "                         )\n",
    "cca_data_wide"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "bc256951-cafe-46fa-8314-52575cd3e947",
   "metadata": {},
   "source": [
    "#### Task - Why all the NaNs?\n",
    "\n",
    "1. After pivoting the data you will notice a lot of `Nan` values that you didn't see before. Why do you think there are all these missing values? Why don't we see all these missing values in the long format?"
   ]
  },
  {
   "cell_type": "raw",
   "id": "6368716c-7500-4d22-a250-4d8c1f848add",
   "metadata": {},
   "source": [
    "# your answer here"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "b8c5fe93-8955-422f-878a-7a76b5c54a5d",
   "metadata": {},
   "source": [
    "#### Answer - Why all the NaNs\n",
    "\n",
    "Click on the ellipses (...) below to see the answers."
   ]
  },
  {
   "cell_type": "markdown",
   "id": "b853e071-e130-4c0b-a7ed-2a1ae61ae5f6",
   "metadata": {
    "jupyter": {
     "source_hidden": true
    },
    "tags": []
   },
   "source": [
    "Many of the community centers do not report their attendance every day. The long format hides this."
   ]
  },
  {
   "cell_type": "markdown",
   "id": "206d41d2-0f17-4227-b65f-7cc93e087c09",
   "metadata": {},
   "source": [
    "#### Task - Dropping NaNs\n",
    "\n",
    "1. Use the `dropna()` function to remove all rows from the `cca_data_wide` Dataframe.\n",
    "2. What do the results mean? "
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "b622aa79-10b0-4725-a4b5-46eb8b800bdd",
   "metadata": {},
   "outputs": [],
   "source": [
    "# your code here\n"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "0e80123c-0ae8-43d4-955f-3f38e77e3a03",
   "metadata": {},
   "source": [
    "#### Answer - Dropping NaNs\n",
    "\n",
    "Click on the ellipses (...) below to see the answers."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "18042d78-4858-4d7e-8160-364d58c40701",
   "metadata": {
    "jupyter": {
     "source_hidden": true
    },
    "tags": []
   },
   "outputs": [],
   "source": [
    "# drop all rows with a missing value\n",
    "cca_data_wide.dropna()"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "d008aa67-6c6a-432f-bedb-2c9f5cb8152f",
   "metadata": {
    "jupyter": {
     "source_hidden": true
    },
    "tags": []
   },
   "source": [
    "The fact we see zero rows means there has never been a day when every community center has reported their attendance."
   ]
  },
  {
   "cell_type": "markdown",
   "id": "b8cfcb1c-e6ab-4840-ace4-9441f94829d6",
   "metadata": {},
   "source": [
    "## Merging and Joining Datasets\n",
    "\n",
    "In the previous sections of this lesson the data had shared column names, same order, or similar shapes. What if the data you want to connect together have shared values but not shared structure?"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "f438ebe0-5154-49e3-9a2a-51d5ad2aace3",
   "metadata": {},
   "source": [
    "#### Task - Load Population datasets\n",
    "\n",
    "1. Load the `ACS-2015-total-population.csv` and `pgh-neighborhood-data-2010.csv` data files into pandas dataframes and display their contents. Note, you will need to do this in two separate code cells."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "a56b6188-d519-489e-a8d0-55f65ce5355c",
   "metadata": {},
   "outputs": [],
   "source": [
    "# your answer here\n"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "7e2858b7-959e-47fe-971b-902c3cc60a1f",
   "metadata": {},
   "source": [
    "#### Answer - Load Population Datasets\n",
    "\n",
    "Click on the ellipses (...) below to see the answers."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "1222508d-5303-4fb8-81c3-d435b73e4a59",
   "metadata": {
    "jupyter": {
     "source_hidden": true
    },
    "tags": []
   },
   "outputs": [],
   "source": [
    "# answer 1\n",
    "acs_2015_total_population = pd.read_csv(\"ACS-2015-total-population.csv\")\n",
    "acs_2015_total_population"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "4fa9fc6c-2360-4211-b19b-ecfe5b4f180b",
   "metadata": {
    "jupyter": {
     "source_hidden": true
    },
    "tags": []
   },
   "outputs": [],
   "source": [
    "# answer 2 \n",
    "pgh_neighborhood_data_2010 = pd.read_csv(\"pgh-neighborhood-data-2010.csv\")\n",
    "pgh_neighborhood_data_2010"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "d6b865b1-c5f3-47b6-acc9-16e05460577f",
   "metadata": {},
   "source": [
    "#### Task - Shared Information\n",
    "\n",
    "Look at the contents of both of the datasets you loaded in the previous task.\n",
    "\n",
    "1. Is there a column that has the same values for both datasets shared across both? \n",
    "1. What does each row represent? Is this the same for both datasets?\n",
    "1. Does each dataset have the same number of rows? What does it mean if they don't have the same number of rows?\n",
    "1. Why can't we just copy the columns from one of the datasets into the other?"
   ]
  },
  {
   "cell_type": "raw",
   "id": "17023472-3019-459f-9bfe-29eba75abc6a",
   "metadata": {},
   "source": [
    "# your answer here\n"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "f4786e21-ef50-464d-a69a-8c20eba3d4d3",
   "metadata": {},
   "source": [
    "#### Answer - Shared Information\n",
    "\n",
    "Click on the ellipses (...) below to see the answers."
   ]
  },
  {
   "cell_type": "markdown",
   "id": "1f2afb61-19b0-40af-ae3e-ce98283e8f38",
   "metadata": {
    "jupyter": {
     "source_hidden": true
    },
    "tags": []
   },
   "source": [
    "1. Yes the neighborhood column.\n",
    "2. Each row represents the population of a neighborhood.\n",
    "3. No, one dataset has 90 rows the other has 91. It means there is a neighborhood missing in one of the datasets.\n",
    "2. Several reasons. First, the data are not in the same order. Second, they don't have the same number of rows. "
   ]
  },
  {
   "cell_type": "markdown",
   "id": "333e0931-56e4-4f12-80c8-0f2ea0113b72",
   "metadata": {
    "tags": []
   },
   "source": [
    "### Pandas Merge\n",
    "\n",
    "When you want to join a datasets together based upon shared column values, then you need to use Pandas [Merge](https://pandas.pydata.org/docs/reference/api/pandas.merge.html) function to perform a \"database style join.\" A database style join will connect two datasets together based upon the shared values of a column or columns. This style of merging is smarter than simply concatenating datasets together because the merge or join operations will join rows together based on shared column value rather than order or structure. So in our case, we can use the *Neighborhood* column in both of the datasets (although as we will see, it is not seamless)."
   ]
  },
  {
   "cell_type": "markdown",
   "id": "72e48a91-f09d-4d2a-bfee-db04da5a75c3",
   "metadata": {
    "tags": []
   },
   "source": [
    "#### Task - What got merged?\n",
    "\n",
    "Run the code cell below and look at the output. \n",
    "\n",
    "1. How many rows are there? Is this what you expected?\n",
    "2. Do you think there are rows/data missing?\n",
    "3. Why do you think there is that particular number of rows in the results?"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "bc6d2e84-9f73-456d-b770-3d290fd94a27",
   "metadata": {},
   "outputs": [],
   "source": [
    "# Merge the 2015 and 2010 datasets\n",
    "\n",
    "pd.merge(acs_2015_total_population, pgh_neighborhood_data_2010)"
   ]
  },
  {
   "cell_type": "raw",
   "id": "d3366c81-274a-4883-8ec3-4ebf84936a1d",
   "metadata": {},
   "source": [
    "# your answer here\n"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "93615eb7-74a5-4f9b-b064-c8c6fc3b1f07",
   "metadata": {},
   "source": [
    "#### Answer - What Got Merged?\n",
    "\n",
    "Click on the ellipses (...) below to see the answers."
   ]
  },
  {
   "cell_type": "markdown",
   "id": "6d31ea41-d70c-41e6-9572-3f6db77379e2",
   "metadata": {
    "jupyter": {
     "source_hidden": true
    },
    "tags": []
   },
   "source": [
    "1. there are 88 rows, but there should be 90 since there are 90 neighborhoods inPittsburgh.\n",
    "2. yes.\n",
    "3. there are values in the neighborhood column that are not present in the other dataset. dirty data."
   ]
  },
  {
   "cell_type": "markdown",
   "id": "256823bd-0d34-4fec-a339-ade4b298c9b4",
   "metadata": {},
   "source": [
    "### How to merge\n",
    "\n",
    "When performing this kind of merge operation it is often the case that there are data values in one of the dataframes that don't exist in the other dataframe. You need to tell the Pandas merge function how you \n",
    "\n",
    "- inner: use intersection of values from both dataframes, similar to a SQL inner join; preserve the order of the left keys.\n",
    "- Left: use only keys from left dataframe, preserve key order.\n",
    "- Right: use only keys from right dataframe, preserve key order.\n",
    "- Outer: use union of keys from both dataframes, sort keys lexicographically.\n",
    "\n",
    "![Inner join venn diagram](https://www.w3schools.com/sql/img_innerjoin.gif)\n",
    "![Left Join](https://www.w3schools.com/sql/img_leftjoin.gif)\n",
    "![Right join venn diagram](https://www.w3schools.com/sql/img_rightjoin.gif)\n",
    "![Outer Join venn diamgram](https://www.w3schools.com/sql/img_fulljoin.gif)\n",
    "\n",
    "*Image credit: [W3C Schools](https://www.w3schools.com/sql/sql_join.asp)"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "90d60885-c3ec-4e37-88b4-ca88e51351f8",
   "metadata": {
    "tags": []
   },
   "source": [
    "#### Task - What kind of join produced the results above\n",
    "\n",
    "1. Considering the results produced by the merge operation above, what kind of join do you think was performed?"
   ]
  },
  {
   "cell_type": "raw",
   "id": "522826df-1675-4899-81fd-c13bc9188d73",
   "metadata": {},
   "source": [
    "# your answer here\n"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "39856191-60ad-4eaa-9ef9-79edc50763f3",
   "metadata": {},
   "source": [
    "#### Answer - What kind of join produced the result above\n",
    "\n",
    "Click on the ellipses (...) below to see the answers."
   ]
  },
  {
   "cell_type": "markdown",
   "id": "05457625-8ff8-47d2-ba9f-1fcd1152da75",
   "metadata": {
    "jupyter": {
     "source_hidden": true
    },
    "tags": []
   },
   "source": [
    "1. Inner join. Only 88 neighborhood values are shared across the two datasets. "
   ]
  },
  {
   "cell_type": "markdown",
   "id": "a8f01af9-0721-46f2-8600-4be9a9889232",
   "metadata": {},
   "source": [
    "#### Task - Review the Pandas Merge documentation\n",
    "\n",
    "1. Review the Pandas [documentation for the merge function](https://pandas.pydata.org/docs/reference/api/pandas.merge.html). What parameter lets to specify the type of merge operation and what is the format of argument?\n",
    "2. What is the default?"
   ]
  },
  {
   "cell_type": "raw",
   "id": "534f5cf5-3162-4923-8d57-e3f6d61fe953",
   "metadata": {},
   "source": [
    "# your answer here\n"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "392d761b-aa44-41ea-aac7-8a24d9984542",
   "metadata": {},
   "source": [
    "#### Answer - Review the Pandas Merge Documentation\n",
    "\n",
    "Click on the ellipses (...) below to see the answers."
   ]
  },
  {
   "cell_type": "markdown",
   "id": "3552684a-5053-4ea6-9b9a-e5a26076a185",
   "metadata": {
    "jupyter": {
     "source_hidden": true
    },
    "tags": []
   },
   "source": [
    "1. The `how` parameter. Takes a string \n",
    "2. Inner join."
   ]
  },
  {
   "cell_type": "markdown",
   "id": "a5e7c098-d629-48ca-b291-b838c044b288",
   "metadata": {},
   "source": [
    "#### Task - Left Join\n",
    "\n",
    "1. Copy the merge code from above and modify the arguments to perform a *left* join.\n",
    "2. Look at the output and consider the number of rows. Do you think there is data is missing or added?"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "8441047e-69be-4b8c-9ed2-3beffb4a867f",
   "metadata": {},
   "outputs": [],
   "source": [
    "# your code here\n"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "ea9a4666-469f-497c-9a6a-0a04d01acd3b",
   "metadata": {},
   "source": [
    "#### Answer - Left Join\n",
    "\n",
    "Click on the ellipses (...) below to see the answers."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "c2953b02-64ec-4e37-9313-2f01f47b8b36",
   "metadata": {
    "jupyter": {
     "source_hidden": true
    },
    "tags": []
   },
   "outputs": [],
   "source": [
    "# answer 1\n",
    "\n",
    "pd.merge(acs_2015_total_population, pgh_neighborhood_data_2010, how=\"left\")"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "6ebafcdb-302d-49d9-a80d-a153b92afcb1",
   "metadata": {},
   "source": [
    "#### Task - Right Join\n",
    "\n",
    "1. Copy the merge code above and modify the arguments to perform a *right* join.\n",
    "2. Look at the output and consider the number of rows. Do you think there is data is missing or added?"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "5b97a3e8-eda0-4fa3-b5c6-753fa4c40d29",
   "metadata": {},
   "outputs": [],
   "source": [
    "# your answer here\n"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "a6cc2194-232b-42b8-a5a2-08cf5ae7422e",
   "metadata": {
    "tags": []
   },
   "source": [
    "#### Answer - Right Join\n",
    "\n",
    "Click on the ellipses (...) below to see the answers."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "12c59f78-8b88-4ebb-865d-6a8493a71012",
   "metadata": {
    "jupyter": {
     "source_hidden": true
    },
    "tags": []
   },
   "outputs": [],
   "source": [
    "# answer 1\n",
    "pd.merge(acs_2015_total_population, pgh_neighborhood_data_2010, how=\"right\")"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "74775efc-0f92-4c45-8407-63123347cd44",
   "metadata": {},
   "source": [
    "#### Task - Outer Join\n",
    "\n",
    "1. Copy and modify the merge code to perform an *outer* join.\n",
    "2. Look at the output and consider the number of rows. Do you think there is data is missing or added?"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "1d843bfb-0553-482c-972c-c0f5ec2bd577",
   "metadata": {},
   "outputs": [],
   "source": [
    "# your code here\n"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "e818d793-ddfd-424a-a392-a276c46294e4",
   "metadata": {},
   "source": [
    "#### Answer - Outer Join\n",
    "\n",
    "Click on the ellipses (...) below to see the answers."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "75b69d4d-13ae-4dea-949a-ab0fc8fdc8da",
   "metadata": {
    "jupyter": {
     "source_hidden": true
    },
    "tags": []
   },
   "outputs": [],
   "source": [
    "# answer 1 \n",
    "pd.merge(acs_2015_total_population, pgh_neighborhood_data_2010, how=\"outer\")"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "b9fb9db1-c2f3-4918-b6d7-4bdf2ae876f9",
   "metadata": {},
   "source": [
    "#### Task - Look at the results of an Outer Join\n",
    "\n",
    "1. How many rows are in the results of the outer join operation?\n",
    "2. How is it possible to have *more* rows than existed in either of the two merged datasets\n",
    "3. Look at the last two rows (91 and 91), what value do you see for the \"Id\", \"Estimate; Total\", and \"Margin of Error; Total\" columns? \n",
    "4. Why do you think those rows have that value for those columns? "
   ]
  },
  {
   "cell_type": "raw",
   "id": "d23dac71-d364-4585-adc1-434b98d92563",
   "metadata": {},
   "source": [
    "# your answer here\n"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "fbceab5e-6eab-4208-9224-2d4c80e09642",
   "metadata": {},
   "source": [
    "#### Answer - Look at the results of an Outer Join\n",
    "\n",
    "Click on the ellipses (...) below to see the answers."
   ]
  },
  {
   "cell_type": "markdown",
   "id": "17198edd-c7ac-473f-bf2e-04136b8c1a6d",
   "metadata": {
    "jupyter": {
     "source_hidden": true
    },
    "tags": []
   },
   "source": [
    "1. 93\n",
    "2. There were rows that could not be merged, so the outer join operation just added them\n",
    "3. NaN\n",
    "4. When the outer join adds the rows it fills in missing values with NaN because it doesn't know what to put there."
   ]
  },
  {
   "cell_type": "markdown",
   "id": "c94ef5db-6c22-4e83-94f2-eae6c34f483e",
   "metadata": {},
   "source": [
    "### Finding Missing Values\n",
    "\n",
    "The output of the various merge operations seem to indicate that the two datasets, which should theoretically contain the same rows (the number of neighborhoods in Pittsburgh hasn't changed). So what gives? Clearly there are some dirty data values that are causing errors. \n",
    "\n",
    "\n",
    "What we can see in the results of the outer join are some rows that didn't merge data from both datasets. To see what rows are not shared across both datasets we need to filter for just the rows with missing values. To do this with Pandas we need to do three things:\n",
    "1. Create a new dataframe from the results of the outer join.\n",
    "2. Create a mask that filters out all of the rows that contain\n",
    "\n",
    "\n",
    "The code below performs an outer merge of the two datasets and then filters for rows that have missing values.\n",
    "\n",
    "```python\n",
    "merged_pgh_data[missing_mask]\n",
    "```"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "bfe54bb1-6f7c-4587-a941-9bed6245b103",
   "metadata": {},
   "source": [
    "#### Task - Create a new dataframe\n",
    "\n",
    "1. Perform an outer merge of the two datasets and save the results into a new variable called `merged_pgh_data`\n",
    "2. Display the new dataframe"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "e0952dcb-c07b-4383-b246-3a243b9f8930",
   "metadata": {},
   "outputs": [],
   "source": [
    "# your code here\n"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "b554086b-33bf-481e-ad16-176fbfca99ef",
   "metadata": {},
   "source": [
    "#### Answer - Create a new dataframe\n",
    "\n",
    "Click on the ellipses (...) below to see the answers."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "6d2b8a5c-ae6b-4d7b-9d09-e8d22e51a4f6",
   "metadata": {
    "jupyter": {
     "source_hidden": true
    },
    "tags": []
   },
   "outputs": [],
   "source": [
    "# answer\n",
    "merged_pgh_data = pd.merge(acs_2015_total_population, pgh_neighborhood_data_2010, how=\"outer\")\n",
    "merged_pgh_data"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "d951aea0-5e6e-4744-9ad6-1ba41ea28876",
   "metadata": {},
   "source": [
    "#### Task - Identify missing values in the dataframe\n",
    "\n",
    "1. Use the [isna()](https://pandas.pydata.org/docs/reference/api/pandas.DataFrame.isna.html#pandas.DataFrame.isna) function on your dataframe to output boolean values indicating if a value is missing. Don't save the output to va variable yet, just look at the output.\n",
    "2. Do you see `True` values where the results where Nan in the previous task?"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "e03bf830-a219-4e21-ada7-85c6f6026187",
   "metadata": {},
   "outputs": [],
   "source": [
    "# your code here\n"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "819aa2a9-2ad1-4045-8e7f-d4ed5e019a9d",
   "metadata": {},
   "source": [
    "#### Answer - Identify missing values in the dataframe\n",
    "\n",
    "Click on the ellipses (...) below to see the answers."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "af7f96be-f9a2-4f85-aa2c-cce70fdf6dfc",
   "metadata": {
    "jupyter": {
     "source_hidden": true
    },
    "tags": []
   },
   "outputs": [],
   "source": [
    "# answer \n",
    "missing_mask = merged_pgh_data.isna()\n",
    "missing_mask"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "9863d290-da3a-43fd-9cac-76502272efbb",
   "metadata": {},
   "source": [
    "#### Task - Make a 1-dimensional Mask\n",
    "\n",
    "1. Copy your answer from the previous task and add `.any(axis=1)` after the `isna()` function. \n",
    "2. Save this to a variable called `missing_mask` and display it. What do the results mean?\n",
    "2. Look at the Pandas documentation for [any()](https://pandas.pydata.org/docs/reference/api/pandas.DataFrame.any.html). What do you think the `any()` function is doing here and why does it have the `axis=1` parameter?"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "145bcbd8-8705-448f-a63d-367739069a4b",
   "metadata": {},
   "outputs": [],
   "source": [
    "# your code here\n"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "cf8846db-0d94-4ae5-b3fa-c89013a1ca6b",
   "metadata": {},
   "source": [
    "#### Answer - Make a 1-dimensional Mask\n",
    "\n",
    "Click on the ellipses (...) below to see the answers."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "636f47c6-3093-4195-9bcc-0f6774142b0b",
   "metadata": {
    "jupyter": {
     "source_hidden": true
    },
    "tags": []
   },
   "outputs": [],
   "source": [
    "# answer \n",
    "missing_mask = merged_pgh_data.isna().any(axis=1)\n",
    "missing_mask\n",
    "# answer 3 - Any is looking at the the row (as specified by the axis=1 parameter) and returning True if there are any True values"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "42b14be9-3dd2-4036-a014-97d1c57dc2ad",
   "metadata": {},
   "source": [
    "#### Task - Apply the mask\n",
    "\n",
    "1. Apply the mask you saved into the variable `missing_mask` to the dataframe `merged_data_pgh`"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "aa965be1-a0c7-4cd6-beac-64504c92c825",
   "metadata": {},
   "outputs": [],
   "source": [
    "# your code here\n"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "b68ee522-31c3-4cc4-b9b0-86e07b28ba2c",
   "metadata": {},
   "source": [
    "#### Answer - Apply the mask\n",
    "\n",
    "Click on the ellipses (...) below to see the answers."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "41971761-9ce6-4540-89e4-5a722093ce8e",
   "metadata": {},
   "outputs": [],
   "source": [
    "# answer 1\n",
    "merged_pgh_data[missing_mask]"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "c1d82a5c-96cf-4636-a685-82e3136e10af",
   "metadata": {},
   "source": [
    "#### Task - Analyze the results\n",
    "\n",
    "The results of the previous task indicate what data values are not shared across the two datasets.\n",
    "\n",
    "1. What rows are similar but not exact? What is the difference? \n",
    "2. What is the *correct* value? Which dataset has the incorrect data neighborhood name?\n",
    "3. What value appears in one of the datasets but not in the other? In which dataset does it appear? \n",
    "4. Is that value actually a neighborhood?\n",
    "\n"
   ]
  },
  {
   "cell_type": "raw",
   "id": "e04cd3b0-4a42-406e-a3d6-a84851c3ef69",
   "metadata": {},
   "source": [
    "# your answer here\n"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "42f48c70-88ee-4b23-a5e4-a5d14e91e79e",
   "metadata": {},
   "source": [
    "#### Answer - Displaying Specific Rows of a Dataframe\n",
    "\n",
    "Click on the ellipses (...) below to see the answers."
   ]
  },
  {
   "cell_type": "markdown",
   "id": "e474b53d-2f17-420e-b9d1-cb5b30ff5a91",
   "metadata": {
    "jupyter": {
     "source_hidden": true
    },
    "tags": []
   },
   "source": [
    "1. One dataset says \"South Side\" and the other dataset says \"Southside\" for the Flats and Slopes.\n",
    "2. The correct answer is \"South Side\" with a space. The ACS data is correct.\n",
    "3. The value \"Mount Oliver Borough\" appears in the ACS data, but not in the 2010 neighborhood profile data.\n",
    "4. No."
   ]
  },
  {
   "cell_type": "markdown",
   "id": "5020871c-6c62-432f-9e8c-189f1400a8ec",
   "metadata": {},
   "source": [
    "#### Task - Fix the misspelled neighborhoods\n",
    "\n",
    "1. Use the Pandas `replace()` function to fix the 2010 neighborhood profiles dataset and replace the incorrect neighborhood names with the correct names"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "92e77542-4477-44c2-a6d0-6b4aa774bb2a",
   "metadata": {},
   "outputs": [],
   "source": [
    "# your answer here\n"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "5cda0a1e-8839-459c-aae4-45e551e2de6e",
   "metadata": {},
   "source": [
    "#### Answer - Fix the misspelled neighborhoods\n",
    "\n",
    "Click on the ellipses (...) below to see the answers."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "013d0061-2137-4f13-93d2-c1671b4278d3",
   "metadata": {
    "jupyter": {
     "source_hidden": true
    },
    "tags": []
   },
   "outputs": [],
   "source": [
    "# answer 1\n",
    "pgh_neighborhood_data_2010_fixed = pgh_neighborhood_data_2010.replace({\"Southside Flats\":\"South Side Flats\",\n",
    "                                    \"Southside Slopes\":\"South Side Slopes\"})"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "a5850a96-7c02-46b2-90c7-61791465d87b",
   "metadata": {},
   "source": [
    "#### Task - Find the index of row that is not a neighborhood\n",
    "\n",
    "1. Find the row identifying the non-neighborhood\n",
    "2. What is the row index?"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "fba778bd-8a54-4f81-b07e-e45a3064d7b2",
   "metadata": {},
   "outputs": [],
   "source": [
    "# your code here\n"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "12014c88-b646-452b-bb9d-deb4db3c3281",
   "metadata": {},
   "source": [
    "#### Answer - Find the index of row that is not a neighborhood\n",
    "\n",
    "Click on the ellipses (...) below to see the answers."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "16bda75e-9ba7-4e72-aff0-9894864f0e55",
   "metadata": {
    "jupyter": {
     "source_hidden": true
    },
    "tags": []
   },
   "outputs": [],
   "source": [
    "# answer 1\n",
    "acs_2015_total_population[acs_2015_total_population['Neighborhood'] == \"Mount Oliver Borough\"]"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "00a3c9f3-04d6-49b3-aace-0119ea0e167a",
   "metadata": {},
   "source": [
    "#### Task - Drop the bad row\n",
    "\n",
    "1. Use the pandas [drop()](https://pandas.pydata.org/docs/reference/api/pandas.DataFrame.drop.html?highlight=drop#pandas.DataFrame.drop) function to remove the row with the index position of the incorrect neighborhood)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "689de05f-a3e1-4125-bf7e-2b676aff4453",
   "metadata": {},
   "outputs": [],
   "source": [
    "# your code here\n"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "e7010762-1ca1-4680-ac77-28f49ba7f876",
   "metadata": {},
   "source": [
    "#### Answer - Drop the bad row\n",
    "\n",
    "Click on the ellipses (...) below to see the answers."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "7b2794cd-0ddc-4763-ad6b-4a0ab3959d13",
   "metadata": {
    "jupyter": {
     "source_hidden": true
    },
    "tags": []
   },
   "outputs": [],
   "source": [
    "# answer\n",
    "acs_2015_total_population_fixed = acs_2015_total_population.drop(52)"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "dd96e88b-e7a4-4b76-91eb-01d7914cac8b",
   "metadata": {},
   "source": [
    "#### Task - Merge the fixed dataframes\n",
    "\n",
    "1. Run the Pandas merge operation again on the fixed dataframes.\n",
    "2. Do you get the correct number of rows?"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "aea47771-c65e-4e7a-9f92-6bc8eb99c13d",
   "metadata": {},
   "outputs": [],
   "source": [
    "# your code here\n"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "4df8af06-21e0-4dbe-8491-287791808366",
   "metadata": {},
   "source": [
    "#### Answer - Merge the Fixed Dataframes\n",
    "\n",
    "Click on the ellipses (...) below to see the answers."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "ecc6dd6d-53d2-4bd5-8714-efb51a3cb64d",
   "metadata": {
    "jupyter": {
     "source_hidden": true
    },
    "tags": []
   },
   "outputs": [],
   "source": [
    "# answer\n",
    "pd.merge(acs_2015_total_population_fixed, pgh_neighborhood_data_2010_fixed, how=\"outer\")"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "68b2e2d7-38ce-4ec9-a16b-9117c0687c68",
   "metadata": {},
   "source": [
    "### Merging Many to Many\n",
    "\n",
    "\n",
    "![under develpment gif](http://textfiles.com/underconstruction/Dimension4933picsA-Hconstruction.gif)\n",
    "\n",
    "\n",
    "- overall goal is to show how merge the 311 dataset with teh 311 categories. This is a many to one relationship, there are many request types that map to a single category. Why is this useful, becuase this can help aggregate the data into a more comprehensible format\n",
    "\n",
    "Steps\n",
    "- introduce the 311 data. These are data collected by the city's [311 response center](https://pittsburghpa.gov/311/) which handles non emergency comments, concerns, or questions. \n",
    "- Task - load the 311 data into a dataframe\n",
    "- There is a lot of data here. We are specifically interested knowing more about the types of requests that are submitted. \n",
    "- the distribution of request types. So we count the values in the request type column. \n",
    "- task - select the request_type column and then\n",
    "- The results are busy. Look at teh results\n",
    "\n",
    "use value counts to aggregate\n",
    "3. perhaps "
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "680e9912-5c6c-48ee-be82-bd3bc2d0794d",
   "metadata": {},
   "outputs": [],
   "source": []
  },
  {
   "cell_type": "markdown",
   "id": "08d0247d-bc8b-4841-aa8e-ff7d7a29a15e",
   "metadata": {},
   "source": [
    "#### Task - Load the 311 Service Request Data\n",
    "\n",
    "1. Use the `read_csv` method to load the data file `311-data.csv` into a Pandas Dataframe called `service_requests`\n",
    "2. Display the first 20 rows of the service request data."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "789316e7",
   "metadata": {
    "collapsed": false,
    "jupyter": {
     "outputs_hidden": false
    }
   },
   "outputs": [],
   "source": [
    "# your code here\n"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "754cf2c9-20b7-4bec-9ff3-b36a2252da18",
   "metadata": {
    "tags": []
   },
   "source": [
    "#### Answer - Load the 311 Service Request Data\n",
    "\n",
    "Click on the ellipses (...) below to see the answers."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "bcad600b",
   "metadata": {
    "jupyter": {
     "source_hidden": true
    },
    "tags": []
   },
   "outputs": [],
   "source": [
    "# answer 1\n",
    "service_requests = pd.read_csv(\"311-data.csv\")\n",
    "service_requests.head(20)"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "164000ab-79c1-48d9-a392-177110660331",
   "metadata": {},
   "source": [
    "#### Task - Categorical data types\n",
    "1. Look at the results from loading the service request data. There are 17 columns, which ones have categorical data that might be suitable for grouping. What column contains information about the types of question, comments, and concerns?"
   ]
  },
  {
   "cell_type": "raw",
   "id": "7d3b4158-6035-4b46-928a-3c830fca2bbf",
   "metadata": {},
   "source": [
    "# your answer here\n"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "f3a6c330-58d0-410b-8f01-9e0e06c6b4cc",
   "metadata": {},
   "source": [
    "#### Answer - Categorical data types\n",
    "\n",
    "Click on the ellipses (...) below to see the answers."
   ]
  },
  {
   "cell_type": "markdown",
   "id": "e218a2ed-d0e6-419f-be2d-5b7d81ed8f23",
   "metadata": {
    "jupyter": {
     "source_hidden": true
    },
    "tags": []
   },
   "source": [
    "There are lots of columns to choose from. Request_type, Neighborhood, Department..."
   ]
  },
  {
   "cell_type": "markdown",
   "id": "bace2c20-bfe6-4897-ae35-af81bb8fc80b",
   "metadata": {},
   "source": [
    "#### Task - Count the kinds of requests\n",
    "\n",
    "1. Select column you identified in the previous task to  and then use the `value_counts()` function to aggregate teh data. What does the result tell you?"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "96a5c4f8-7153-4ca0-ab8b-981aab095a93",
   "metadata": {},
   "outputs": [],
   "source": [
    "# your code here\n"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "18391672-1229-4fe7-b83e-362d9b5a47be",
   "metadata": {},
   "source": [
    "#### Answer - Count the kinds of requets\n",
    "\n",
    "Click on the ellipses (...) below to see the answers."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "2b53737f-f5a0-49cf-969f-0d4450da5f0e",
   "metadata": {
    "jupyter": {
     "source_hidden": true
    },
    "tags": []
   },
   "outputs": [],
   "source": [
    "# answer 1\n",
    "service_requests[\"REQUEST_TYPE\"].value_counts()"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "f3dd6435-8c29-4cbe-93e9-c55017ddabc0",
   "metadata": {
    "jupyter": {
     "outputs_hidden": false
    }
   },
   "source": [
    "#### Task - How many request types\n",
    "\n",
    "1. Look at the results from the previous task. How many different kinds of requests are there in the dataset?"
   ]
  },
  {
   "cell_type": "raw",
   "id": "87861daa-f24d-41aa-b08c-4c4dce5b8498",
   "metadata": {},
   "source": [
    "# your answer here"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "36859cfc-1e0a-4ff2-ad06-42203e5e62e6",
   "metadata": {},
   "source": [
    "#### Answer - How Many request types\n",
    "\n",
    "Click on the ellipses (...) below to see the answers."
   ]
  },
  {
   "cell_type": "raw",
   "id": "515dc72c-9ef1-49ef-98de-a28efd747197",
   "metadata": {
    "jupyter": {
     "source_hidden": true
    },
    "tags": []
   },
   "source": [
    "There are 378, which can be determined by the length of the series."
   ]
  },
  {
   "cell_type": "markdown",
   "id": "5086f3ef-9827-4aae-b044-f2b7f0ca15b3",
   "metadata": {},
   "source": [
    "#### Task - Make a bar chart\n",
    "\n",
    "While we will focus more on visualizaiton in the next lesson, sometimes it is easier to use a visualization to explore your data than as a table.\n",
    "\n",
    "1. Add the two functions `.plot().bar()` to the end of the code in the previous task. Look at the resulting bar chart, is it any easier to understand the data? "
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "bb04c89d-f528-4fc1-a242-bd263a22fa2d",
   "metadata": {},
   "outputs": [],
   "source": [
    "# your code here\n"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "1ec1d0ed-5130-4df8-a548-210487885bc5",
   "metadata": {},
   "source": [
    "#### Answer - Make a bar chart\n",
    "\n",
    "Click on the ellipses (...) below to see the answers."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "50fbe8ae-5741-48be-9187-9024ac5d659e",
   "metadata": {
    "jupyter": {
     "source_hidden": true
    },
    "tags": []
   },
   "outputs": [],
   "source": [
    "# answer 1\n",
    "service_requests[\"REQUEST_TYPE\"].value_counts().plot.bar()\n",
    "\n",
    "# yes, we can see the distribution, but there are too many request types"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "f73d81e0-e838-4ef5-99fc-ee946d297547",
   "metadata": {},
   "source": [
    "### Mapping to Higher Level Categories\n",
    "\n",
    "The City has provided a companion dataset, the [311 Issue and Category Codebook](https://data.wprdc.org/dataset/311-data/resource/7794b313-33be-4a8b-bf80-41751a59b84a), that provides more information about the various kinds of request types. We can use this dataset to make our aggregation of request types more comprehensible."
   ]
  },
  {
   "cell_type": "markdown",
   "id": "6bd017bb-0612-423c-909a-f7e5fe9c6335",
   "metadata": {},
   "source": [
    "#### Task - Load the 311 Issue and Category Dataset\n",
    "\n",
    "1. Load the data file `311-codebook-request-types.xlsx` dataset into a pandas dataframe called `service_request_codebook`. Pay attention to the file extension!\n",
    "2. Display the results"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "6486f02c-bd50-4222-886d-7d4d2699362a",
   "metadata": {
    "tags": []
   },
   "outputs": [],
   "source": [
    "# your code here\n"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "939cbb98-fead-45b1-943f-a3ad1b6ad4d4",
   "metadata": {},
   "source": [
    "#### Answer - Load the 311 Issue and Category Dataset\n",
    "\n",
    "Click on the ellipses (...) below to see the answers."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "4d815d68-3f4b-4197-8987-41009ccbfd34",
   "metadata": {
    "jupyter": {
     "source_hidden": true
    },
    "tags": []
   },
   "outputs": [],
   "source": [
    "# answer 1\n",
    "service_request_codebook = pd.read_excel(\"311-codebook-request-types.xlsx\")\n",
    "service_request_codebook"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "c3c9188f-42e0-4585-9f1f-da86eb17fab8",
   "metadata": {},
   "source": [
    "#### Task - Exploring the Codebook data\n",
    "\n",
    "Look at the display of the codebook dataset and consider the following questions.\n",
    "\n",
    "1. What does each row  of the codebook data represent?\n",
    "1. What columns/data values are shared with the 311 service request dataset?\n",
    "1. What columns/data values are not shared?\n",
    "1. What specific column of the codebook has values that are shared with the `REQUEST_TYPE` column of the 311 service requests?\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "976e2f51-02e5-4967-92e4-aadc4161632f",
   "metadata": {},
   "outputs": [],
   "source": [
    "# your answer here\n"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "31e04e1d-40a7-40bc-bcb0-39da629da111",
   "metadata": {},
   "source": [
    "#### Answer - Exploring the Codebook data\n",
    "\n",
    "Click on the ellipses (...) below to see the answers."
   ]
  },
  {
   "cell_type": "markdown",
   "id": "8b7eacaf-f070-4208-880a-eb16c791b0ae",
   "metadata": {
    "jupyter": {
     "source_hidden": true
    },
    "tags": []
   },
   "source": [
    "\n",
    "1. a request type\n",
    "2. \n",
    "\n",
    "2. \n",
    "Shared\n",
    "- Issue\n",
    "- Department\n",
    "\n",
    "Not shared\n",
    "- Category\n",
    "- Definition\n",
    "- Public Access Level\n",
    "- Suggested access level\n",
    "\n",
    "2. Issue\n",
    "3. Category"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "b65e833b-5762-4afc-98cd-51937fd944c1",
   "metadata": {},
   "source": [
    "#### Task - How is the relationship between these two datasets many to one?\n",
    "\n",
    "1. Consider what each row in the service request and codebook datasets represent. How are they different? Why would the relationship between these two datasets be considered a \"many to one\" or \"one to many\" relationship?"
   ]
  },
  {
   "cell_type": "raw",
   "id": "5a4a9fbd-e836-49bb-bbad-1d96946742ce",
   "metadata": {},
   "source": [
    "# your answer here\n"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "93e88053-dc43-4726-8791-84fe9b824668",
   "metadata": {},
   "source": [
    "#### Answer - How is the relationship between these two datasets many to one?\n",
    "\n",
    "Click on the ellipses (...) below to see the answers."
   ]
  },
  {
   "cell_type": "markdown",
   "id": "c96974f2-8b42-4510-887e-a4e248573581",
   "metadata": {
    "jupyter": {
     "source_hidden": true
    },
    "tags": []
   },
   "source": [
    "The 311 service request dataset represents information individual requests made to the city, including an assigned request type category. The codebook represents information about types of request. There are many requests of all the different types in the 311 service request dataset and all of those rows of the request type map to a single row in the codebook.  "
   ]
  },
  {
   "cell_type": "markdown",
   "id": "ee0bea48-2c53-4003-b5ca-07ff2b775bcc",
   "metadata": {},
   "source": [
    "#### Task - Merge the Service requests with the code book\n",
    "\n",
    "1. Use the Pandas `merge` function to combine the `service_requests` dataset with the `service_request_codebook`. You will need to use the `left_on` and `right_on` parameters to tell pandas which columns have the shared values you wish to merge on. Refer to the [Pandas `merge` documentation](https://pandas.pydata.org/docs/reference/api/pandas.merge.html?highlight=merge#pandas.merge)\n",
    "2. Display the results."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "50db8c0f-2efe-49c3-82ac-b1201f2a9dae",
   "metadata": {},
   "outputs": [],
   "source": [
    "# your code here\n"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "1c05e74b-c2ae-4bb0-9c5d-4e7f9268ef20",
   "metadata": {},
   "source": [
    "#### Answer - Merge the Service requests with the code book\n",
    "\n",
    "Click on the ellipses (...) below to see the answers."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "1149879b-2370-443d-aed5-0045bda9b1c5",
   "metadata": {
    "jupyter": {
     "source_hidden": true
    },
    "tags": []
   },
   "outputs": [],
   "source": [
    "#answer 1\n",
    "merged_service_requests = pd.merge(service_requests, service_request_codebook,\n",
    "                                   left_on=\"REQUEST_TYPE\", right_on=\"Issue\")\n",
    "merged_service_requests"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "d2fd9744-a10a-43b0-bca7-6a48b367c98a",
   "metadata": {},
   "source": [
    "#### Task - Exploring new columns\n",
    "\n",
    "Look at the new dataframe that was created as a result of the merge operation. Find the new columns that were added (scroll all the way to the right of the dataframe display to see them). \n",
    "\n",
    "1. What does each row represent?\n",
    "1. Of the new columns that have been added to the dataset, which would be most useful for aggregating the requests to learn about the kinds of requests?"
   ]
  },
  {
   "cell_type": "raw",
   "id": "c57cda4d-2682-49ca-807d-ce6e7a9c7d34",
   "metadata": {},
   "source": [
    "# your answer here"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "f9e7f25b-568e-4255-ba80-fb89b31f36c2",
   "metadata": {},
   "source": [
    "#### Answer - Exploring new columns\n",
    "\n",
    "Click on the ellipses (...) below to see the answers."
   ]
  },
  {
   "cell_type": "markdown",
   "id": "8ff2ce29-f1b4-40c6-872a-68940b33ecd0",
   "metadata": {
    "jupyter": {
     "source_hidden": true
    },
    "tags": []
   },
   "source": [
    "1. a service request\n",
    "2. Category"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "d9eadbe9-5d8e-4098-80fa-26c26662a07f",
   "metadata": {},
   "source": [
    "#### Task - Count the categories\n",
    "\n",
    "\n",
    "1. Select column you identified in the previous task and then use the `value_counts()` function to aggregate the data. What does the result tell you? \n",
    "2. What type of request is most frequent in the data?\n",
    "3. What type of request is least frequent?"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "31164eb9-1686-4202-8cda-a9ca1550151a",
   "metadata": {},
   "outputs": [],
   "source": [
    "# your code here\n"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "65bc197e-31a1-41d7-bb4b-a3060c93d82c",
   "metadata": {},
   "source": [
    "#### Answer - Count the categories\n",
    "\n",
    "Click on the ellipses (...) below to see the answers."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "d7e00b5a-7de4-4ed4-aa69-eabeb958b74c",
   "metadata": {
    "jupyter": {
     "source_hidden": true
    },
    "tags": []
   },
   "outputs": [],
   "source": [
    "# answer 1\n",
    "merged_service_requests[\"Category\"].value_counts()"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "0be66639-f3bf-452a-9dfd-63f9573dde46",
   "metadata": {
    "jupyter": {
     "source_hidden": true
    },
    "tags": []
   },
   "source": [
    "1. We can see the total number of requests submitted based on their categorical value\n",
    "2. Road/Street Issues is the most frequent type of request\n",
    "3. Communications (Cable, Website) is the least frequent"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "6e98bcef-d878-41c7-a0be-b2b9ba3832f4",
   "metadata": {},
   "source": [
    "#### Task - Make a bar chart, again\n",
    "\n",
    "While we will focus more on visualization in the next lesson, sometimes it is easier to use a visualization to explore your data than as a table.\n",
    "\n",
    "1. Add the two functions `.plot().bar()` to the end of the code in the previous task. Look at the resulting bar chart, is it any easier to understand the data? "
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "45504612-d746-43d9-bac4-65e01146fb33",
   "metadata": {},
   "outputs": [],
   "source": [
    "# your code here\n"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "6bac21ba-85d4-4550-b46c-60b44130dc92",
   "metadata": {},
   "source": [
    "#### Answer - Make a bar char, again\n",
    "\n",
    "Click on the ellipses (...) below to see the answers."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "ea13fbd5-cb51-4659-b9ba-911d79cedc0e",
   "metadata": {
    "jupyter": {
     "source_hidden": true
    },
    "tags": []
   },
   "outputs": [],
   "source": [
    "# answer 1\n",
    "merged_service_requests[\"Category\"].value_counts().plot.bar()\n",
    "# yes, we can see the distribution, but there are too many request types"
   ]
  }
 ],
 "metadata": {
  "kernelspec": {
   "display_name": "Python 3 (ipykernel)",
   "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.9.13"
  },
  "toc-autonumbering": false,
  "toc-showcode": false,
  "toc-showmarkdowntxt": false,
  "toc-showtags": false
 },
 "nbformat": 4,
 "nbformat_minor": 5
}