lecture_2.ipynb

{
 "cells": [
  {
   "cell_type": "markdown",
   "metadata": {
    "editable": true,
    "id": "Omm4n3FmPHlF",
    "slideshow": {
     "slide_type": "slide"
    },
    "tags": []
   },
   "source": [
    "# Lecture 2: Manipulating and combining data\n",
    "\n",
    "_Please sign attendance sheet_"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {
    "editable": true,
    "slideshow": {
     "slide_type": "slide"
    },
    "tags": []
   },
   "source": [
    "## Feeling overwhlemed?\n",
    "\n",
    "Reminder that learning to code is like learning a spoken language. It's not obvious, and people will pick it up at different speeds at different spots. Try:\n",
    "\n",
    "- Taking notes in the lecture notebooks\n",
    "- Using [another Python/pandas learning resource](https://python-public-policy.afeld.me/en/{{school_slug}}/resources.html)\n",
    "   - Hear things explained another way\n",
    "   - Ask in [Ed Discussions]({{discussions_url}}) if others have recommendations\n",
    "- [Comment-driven development](https://www.sitepoint.com/comment-driven-development/)\n",
    "   - Otherwise, trying to do two steps in your head:\n",
    "      1. Figuring out the logic\n",
    "      1. Figuring out the syntax"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {
    "slideshow": {
     "slide_type": "subslide"
    }
   },
   "source": [
    "Small example of comment-driven development:\n",
    "\n",
    "```python\n",
    "# find valid ZIP codes\n",
    "# filter the DataFrame to only invalid ZIP codes\n",
    "```"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {
    "editable": true,
    "slideshow": {
     "slide_type": "slide"
    },
    "tags": []
   },
   "source": [
    "## Data cleaning\n",
    "\n",
    "> Data Cleansing is a process of removing or fixing incorrect, malformed, incomplete, duplicate, or corrupted data\n",
    "\n",
    "https://hevodata.com/learn/data-cleansing-a-simplified-guide/"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {
    "editable": true,
    "slideshow": {
     "slide_type": "subslide"
    },
    "tags": []
   },
   "source": [
    "When have you needed to clean data?"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {
    "editable": true,
    "slideshow": {
     "slide_type": "fragment"
    },
    "tags": []
   },
   "source": [
    "What are continuous values?"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {
    "editable": true,
    "slideshow": {
     "slide_type": "fragment"
    },
    "tags": []
   },
   "source": [
    "What are categorical values?"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {
    "editable": true,
    "slideshow": {
     "slide_type": "subslide"
    },
    "tags": []
   },
   "source": [
    "### Things to check for\n",
    "\n",
    "From [my workshop on data cleaning](https://github.com/afeld/data-cleaning):\n",
    "\n",
    "- Missing data\n",
    "   - Empty values\n",
    "- Bad (junk) values\n",
    "   - Duplicates\n",
    "   - Mismatched types/formatting\n",
    "- Categorical values\n",
    "   - Uniqueness (cardinality)\n",
    "   - Value counts\n",
    "- Continuous values\n",
    "   - Ranges\n",
    "   - Spread (distribution)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {
    "editable": true,
    "slideshow": {
     "slide_type": "subslide"
    },
    "tags": []
   },
   "source": [
    "Notes:\n",
    "\n",
    "- \"Values\" in this case can be a single cell (in the spreadsheet sense) or a whole row\n",
    "- \"Missing\" or \"duplicates\" can be columns (Series), tables (DataFrames), rows, or cells"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {
    "slideshow": {
     "slide_type": "subslide"
    }
   },
   "source": [
    "### Data cleaning [mnemonic](https://literaryterms.net/mnemonic/)\n",
    "\n",
    "- Empty\n",
    "- Bad\n",
    "- Unique\n",
    "- Spread"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {
    "editable": true,
    "id": "9rvnMzjSMK36",
    "slideshow": {
     "slide_type": "slide"
    },
    "tags": []
   },
   "source": [
    "## Setup"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 1,
   "metadata": {
    "editable": true,
    "execution": {
     "iopub.execute_input": "2025-01-18T15:52:12.512357Z",
     "iopub.status.busy": "2025-01-18T15:52:12.512205Z",
     "iopub.status.idle": "2025-01-18T15:52:12.754388Z",
     "shell.execute_reply": "2025-01-18T15:52:12.754107Z"
    },
    "id": "Q4l2QosJLzP6",
    "slideshow": {
     "slide_type": "skip"
    },
    "tags": []
   },
   "outputs": [],
   "source": [
    "import pandas as pd"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 2,
   "metadata": {
    "editable": true,
    "execution": {
     "iopub.execute_input": "2025-01-18T15:52:12.756063Z",
     "iopub.status.busy": "2025-01-18T15:52:12.755866Z",
     "iopub.status.idle": "2025-01-18T15:52:12.757809Z",
     "shell.execute_reply": "2025-01-18T15:52:12.757571Z"
    },
    "id": "R4rtHV4Yu4jW",
    "slideshow": {
     "slide_type": "skip"
    },
    "tags": []
   },
   "outputs": [],
   "source": [
    "# Display more rows and columns in the DataFrames\n",
    "pd.options.display.max_columns = 100\n",
    "pd.options.display.max_rows = 100"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {
    "editable": true,
    "id": "R1G04BmMMFJb",
    "slideshow": {
     "slide_type": ""
    },
    "tags": []
   },
   "source": [
    "### Read our cleaned 311 Service Requests dataset"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 3,
   "metadata": {
    "editable": true,
    "execution": {
     "iopub.execute_input": "2025-01-18T15:52:12.759192Z",
     "iopub.status.busy": "2025-01-18T15:52:12.759059Z",
     "iopub.status.idle": "2025-01-18T15:52:16.896405Z",
     "shell.execute_reply": "2025-01-18T15:52:16.896073Z"
    },
    "id": "iQgE8qFAMbiF",
    "slideshow": {
     "slide_type": ""
    },
    "tags": []
   },
   "outputs": [
    {
     "name": "stderr",
     "output_type": "stream",
     "text": [
      "/var/folders/kr/nx0m1j811kz5vy8c87ffchzr0000gn/T/ipykernel_3268/610958175.py:2: DtypeWarning: Columns (8,20,31,34) have mixed types. Specify dtype option on import or set low_memory=False.\n",
      "  requests = pd.read_csv(url)\n"
     ]
    }
   ],
   "source": [
    "url = \"https://storage.googleapis.com/python-public-policy2/data/311_requests_2018-19_sample_clean.csv.zip\"\n",
    "requests = pd.read_csv(url)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {
    "editable": true,
    "slideshow": {
     "slide_type": "slide"
    },
    "tags": []
   },
   "source": [
    "## Dealing with dtypes\n",
    "\n",
    "More data cleaning!\n",
    "\n",
    "![Minion character vacuuming](https://impulsecreative.com/hs-fs/hubfs/cleaning-minion-gif.gif?width=490&name=cleaning-minion-gif.gif)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {
    "editable": true,
    "slideshow": {
     "slide_type": "subslide"
    },
    "tags": []
   },
   "source": [
    "```\n",
    "DtypeWarning: Columns (8,20,31,34) have mixed types.\n",
    "```"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 4,
   "metadata": {
    "execution": {
     "iopub.execute_input": "2025-01-18T15:52:16.914519Z",
     "iopub.status.busy": "2025-01-18T15:52:16.914392Z",
     "iopub.status.idle": "2025-01-18T15:52:17.200646Z",
     "shell.execute_reply": "2025-01-18T15:52:17.200372Z"
    },
    "scrolled": true,
    "slideshow": {
     "slide_type": "fragment"
    }
   },
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "<class 'pandas.core.frame.DataFrame'>\n",
      "RangeIndex: 499958 entries, 0 to 499957\n",
      "Data columns (total 41 columns):\n",
      " #   Column                          Non-Null Count   Dtype  \n",
      "---  ------                          --------------   -----  \n",
      " 0   Unique Key                      499958 non-null  int64  \n",
      " 1   Created Date                    499958 non-null  object \n",
      " 2   Closed Date                     476140 non-null  object \n",
      " 3   Agency                          499958 non-null  object \n",
      " 4   Agency Name                     499958 non-null  object \n",
      " 5   Complaint Type                  499958 non-null  object \n",
      " 6   Descriptor                      492496 non-null  object \n",
      " 7   Location Type                   392573 non-null  object \n",
      " 8   Incident Zip                    480394 non-null  object \n",
      " 9   Incident Address                434529 non-null  object \n",
      " 10  Street Name                     434504 non-null  object \n",
      " 11  Cross Street 1                  300825 non-null  object \n",
      " 12  Cross Street 2                  299624 non-null  object \n",
      " 13  Intersection Street 1           107377 non-null  object \n",
      " 14  Intersection Street 2           107042 non-null  object \n",
      " 15  Address Type                    451006 non-null  object \n",
      " 16  City                            476632 non-null  object \n",
      " 17  Landmark                        32516 non-null   object \n",
      " 18  Facility Type                   134918 non-null  object \n",
      " 19  Status                          499958 non-null  object \n",
      " 20  Due Date                        171534 non-null  object \n",
      " 21  Resolution Description          457354 non-null  object \n",
      " 22  Resolution Action Updated Date  488788 non-null  object \n",
      " 23  Community Board                 499958 non-null  object \n",
      " 24  BBL                             407338 non-null  float64\n",
      " 25  Borough                         499958 non-null  object \n",
      " 26  X Coordinate (State Plane)      470815 non-null  float64\n",
      " 27  Y Coordinate (State Plane)      470815 non-null  float64\n",
      " 28  Open Data Channel Type          499958 non-null  object \n",
      " 29  Park Facility Name              499931 non-null  object \n",
      " 30  Park Borough                    499958 non-null  object \n",
      " 31  Vehicle Type                    37 non-null      object \n",
      " 32  Taxi Company Borough            403 non-null     object \n",
      " 33  Taxi Pick Up Location           4474 non-null    object \n",
      " 34  Bridge Highway Name             696 non-null     object \n",
      " 35  Bridge Highway Direction        765 non-null     object \n",
      " 36  Road Ramp                       759 non-null     object \n",
      " 37  Bridge Highway Segment          1007 non-null    object \n",
      " 38  Latitude                        470815 non-null  float64\n",
      " 39  Longitude                       470815 non-null  float64\n",
      " 40  Location                        470815 non-null  object \n",
      "dtypes: float64(5), int64(1), object(35)\n",
      "memory usage: 156.4+ MB\n"
     ]
    }
   ],
   "source": [
    "requests.info()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 5,
   "metadata": {
    "editable": true,
    "execution": {
     "iopub.execute_input": "2025-01-18T15:52:17.202065Z",
     "iopub.status.busy": "2025-01-18T15:52:17.201980Z",
     "iopub.status.idle": "2025-01-18T15:52:17.214871Z",
     "shell.execute_reply": "2025-01-18T15:52:17.214622Z"
    },
    "scrolled": true,
    "slideshow": {
     "slide_type": "subslide"
    },
    "tags": []
   },
   "outputs": [
    {
     "data": {
      "text/plain": [
       "['11235',\n",
       " '11221',\n",
       " '11693',\n",
       " '11216',\n",
       " '10465',\n",
       " '11367',\n",
       " '10459',\n",
       " '11101',\n",
       " '11362',\n",
       " '10014',\n",
       " '11234',\n",
       " '11436',\n",
       " '10305',\n",
       " '10467',\n",
       " '11208',\n",
       " '10451',\n",
       " '11419',\n",
       " '11237',\n",
       " '11220',\n",
       " '10469',\n",
       " '11385',\n",
       " '10470',\n",
       " '11694',\n",
       " '10036',\n",
       " nan,\n",
       " '10473',\n",
       " '11435',\n",
       " '10040',\n",
       " '10472',\n",
       " '11225',\n",
       " '10019',\n",
       " '11434',\n",
       " '11226',\n",
       " '10010',\n",
       " '11211',\n",
       " '11421',\n",
       " '10026',\n",
       " '10013',\n",
       " '11423',\n",
       " '10002',\n",
       " '10453',\n",
       " '11213',\n",
       " '11104',\n",
       " '11249',\n",
       " '11361',\n",
       " '11233',\n",
       " '11224',\n",
       " '11374',\n",
       " '10025',\n",
       " '10022',\n",
       " '11214',\n",
       " '11209',\n",
       " '11366',\n",
       " '10304',\n",
       " '10027',\n",
       " '11378',\n",
       " '11206',\n",
       " '10021',\n",
       " '11364',\n",
       " '10065',\n",
       " '10456',\n",
       " '10314',\n",
       " '10312',\n",
       " '11212',\n",
       " '11379',\n",
       " '10462',\n",
       " '11231',\n",
       " '10460',\n",
       " '11416',\n",
       " '10001',\n",
       " '11357',\n",
       " '11413',\n",
       " '11210',\n",
       " '11217',\n",
       " '11223',\n",
       " '11417',\n",
       " '11418',\n",
       " '11218',\n",
       " '11230',\n",
       " '11207',\n",
       " '11691',\n",
       " '10468',\n",
       " '10007',\n",
       " '10310',\n",
       " '10306',\n",
       " '11103',\n",
       " '11105',\n",
       " '11433',\n",
       " '11203',\n",
       " '10307',\n",
       " '11229',\n",
       " '11372',\n",
       " '10032',\n",
       " '11420',\n",
       " '10017',\n",
       " '10301',\n",
       " '11368',\n",
       " '11201',\n",
       " '11365',\n",
       " '11422',\n",
       " '10452',\n",
       " '11377',\n",
       " '10029',\n",
       " '10003',\n",
       " '10075',\n",
       " '11222',\n",
       " '10128',\n",
       " '11415',\n",
       " '11204',\n",
       " '10030',\n",
       " '11432',\n",
       " '10308',\n",
       " '11102',\n",
       " '10016',\n",
       " '10463',\n",
       " '11412',\n",
       " '10011',\n",
       " '11106',\n",
       " '10457',\n",
       " '11375',\n",
       " '11356',\n",
       " '11228',\n",
       " '11369',\n",
       " '10458',\n",
       " '11411',\n",
       " '11215',\n",
       " '10309',\n",
       " '11358',\n",
       " '11355',\n",
       " '11219',\n",
       " '10031',\n",
       " '10303',\n",
       " '11232',\n",
       " '10302',\n",
       " '11238',\n",
       " '10005',\n",
       " '11429',\n",
       " '11205',\n",
       " '10023',\n",
       " '11373',\n",
       " '10033',\n",
       " '10039',\n",
       " '10028',\n",
       " '11363',\n",
       " '11354',\n",
       " '11692',\n",
       " '10455',\n",
       " '11370',\n",
       " '10035',\n",
       " '10012',\n",
       " '10024',\n",
       " '10009',\n",
       " '10034',\n",
       " '11001',\n",
       " '10466',\n",
       " '11427',\n",
       " '11004',\n",
       " '10454',\n",
       " '11414',\n",
       " '11360',\n",
       " '10461',\n",
       " '10018',\n",
       " '10006',\n",
       " '11236',\n",
       " '11428',\n",
       " '10474',\n",
       " '10471',\n",
       " '10037',\n",
       " '10475',\n",
       " '11430',\n",
       " '10119',\n",
       " '10038',\n",
       " '11426',\n",
       " '11239',\n",
       " '10151',\n",
       " '10120',\n",
       " '10112',\n",
       " '10168',\n",
       " '10004',\n",
       " '10464',\n",
       " '18773-9640',\n",
       " '10282',\n",
       " '11109',\n",
       " '10280',\n",
       " '07114',\n",
       " '11040',\n",
       " '10000',\n",
       " '07090',\n",
       " '10020',\n",
       " '00083',\n",
       " '10044',\n",
       " '10069',\n",
       " '10118',\n",
       " '07102',\n",
       " '11359',\n",
       " '11005',\n",
       " '10169',\n",
       " '11697',\n",
       " '10115',\n",
       " 11226.0,\n",
       " 11236.0,\n",
       " 10466.0,\n",
       " 11210.0,\n",
       " 10452.0,\n",
       " 10457.0,\n",
       " 10304.0,\n",
       " 11416.0,\n",
       " 11205.0,\n",
       " 11214.0,\n",
       " 11692.0,\n",
       " 11101.0,\n",
       " 11201.0,\n",
       " 10470.0,\n",
       " 11375.0,\n",
       " 10305.0,\n",
       " 11420.0,\n",
       " 11421.0,\n",
       " 11419.0,\n",
       " 11412.0,\n",
       " 10309.0,\n",
       " 11385.0,\n",
       " 11379.0,\n",
       " 11356.0,\n",
       " 10021.0,\n",
       " 11417.0,\n",
       " 11377.0,\n",
       " 11423.0,\n",
       " 10001.0,\n",
       " 10469.0,\n",
       " 10463.0,\n",
       " 11372.0,\n",
       " 11422.0,\n",
       " 10017.0,\n",
       " 11368.0,\n",
       " 11213.0,\n",
       " 11220.0,\n",
       " 10312.0,\n",
       " 10025.0,\n",
       " 11357.0,\n",
       " 11230.0,\n",
       " 10456.0,\n",
       " 11104.0,\n",
       " 11232.0,\n",
       " 11208.0,\n",
       " 11206.0,\n",
       " 11229.0,\n",
       " 10465.0,\n",
       " 10472.0,\n",
       " 10039.0,\n",
       " 10016.0,\n",
       " 10009.0,\n",
       " 11355.0,\n",
       " 11211.0,\n",
       " 10040.0,\n",
       " 11366.0,\n",
       " 11234.0,\n",
       " 10013.0,\n",
       " 11207.0,\n",
       " 10032.0,\n",
       " 10036.0,\n",
       " 10029.0,\n",
       " 11204.0,\n",
       " 11435.0,\n",
       " 11373.0,\n",
       " 10007.0,\n",
       " 11216.0,\n",
       " 10306.0,\n",
       " 11378.0,\n",
       " 11414.0,\n",
       " 10314.0,\n",
       " 10128.0,\n",
       " 10027.0,\n",
       " 11221.0,\n",
       " 10024.0,\n",
       " 11364.0,\n",
       " 10468.0,\n",
       " 11233.0,\n",
       " 10014.0,\n",
       " 11238.0,\n",
       " 11374.0,\n",
       " 11105.0,\n",
       " 11219.0,\n",
       " 11106.0,\n",
       " 10003.0,\n",
       " 10026.0,\n",
       " 11411.0,\n",
       " 11223.0,\n",
       " 11693.0,\n",
       " 11365.0,\n",
       " 11209.0,\n",
       " 11103.0,\n",
       " 11361.0,\n",
       " 11427.0,\n",
       " 10467.0,\n",
       " 11203.0,\n",
       " 11102.0,\n",
       " 10455.0,\n",
       " 10454.0,\n",
       " 11225.0,\n",
       " 11212.0,\n",
       " 11434.0,\n",
       " 10461.0,\n",
       " 11369.0,\n",
       " 11432.0,\n",
       " 10031.0,\n",
       " 11217.0,\n",
       " 11235.0,\n",
       " 10458.0,\n",
       " 10453.0,\n",
       " 11215.0,\n",
       " 11367.0,\n",
       " 11231.0,\n",
       " 11413.0,\n",
       " 11358.0,\n",
       " 11694.0,\n",
       " 10462.0,\n",
       " 11218.0,\n",
       " 11430.0,\n",
       " 10473.0,\n",
       " 10011.0,\n",
       " 10460.0,\n",
       " 10019.0,\n",
       " 10310.0,\n",
       " 10037.0,\n",
       " 10451.0,\n",
       " 10301.0,\n",
       " 10010.0,\n",
       " 11354.0,\n",
       " 11222.0,\n",
       " 11691.0,\n",
       " 10034.0,\n",
       " 10308.0,\n",
       " 10012.0,\n",
       " 11237.0,\n",
       " 10801.0,\n",
       " 10030.0,\n",
       " 11428.0,\n",
       " 11418.0,\n",
       " 10033.0,\n",
       " 10459.0,\n",
       " 11429.0,\n",
       " 10065.0,\n",
       " 10022.0,\n",
       " 10005.0,\n",
       " 10002.0,\n",
       " 10035.0,\n",
       " 11370.0,\n",
       " 10004.0,\n",
       " 11415.0,\n",
       " 11426.0,\n",
       " 11362.0,\n",
       " 10006.0,\n",
       " 11360.0,\n",
       " 10038.0,\n",
       " 11249.0,\n",
       " 11001.0,\n",
       " 11224.0,\n",
       " 10075.0,\n",
       " 10018.0,\n",
       " 10302.0,\n",
       " 11436.0,\n",
       " 11433.0,\n",
       " 10474.0,\n",
       " 10023.0,\n",
       " 11004.0,\n",
       " 10028.0,\n",
       " 11228.0,\n",
       " 10475.0,\n",
       " 10069.0,\n",
       " 11363.0,\n",
       " 10303.0,\n",
       " 10282.0,\n",
       " 10307.0,\n",
       " 10103.0,\n",
       " 10280.0,\n",
       " 10471.0,\n",
       " 10271.0,\n",
       " 10000.0,\n",
       " 10464.0,\n",
       " 10107.0,\n",
       " 7078.0,\n",
       " 10044.0,\n",
       " 10020.0,\n",
       " 56303.0,\n",
       " 10119.0,\n",
       " 11040.0,\n",
       " 11386.0,\n",
       " 10952.0,\n",
       " 6811.0,\n",
       " 11239.0,\n",
       " 10121.0,\n",
       " 37214.0,\n",
       " 10538.0,\n",
       " 10112.0,\n",
       " 10279.0,\n",
       " 11109.0,\n",
       " 11371.0,\n",
       " 18017.0,\n",
       " 7115.0,\n",
       " 77036.0,\n",
       " 7114.0,\n",
       " '10123',\n",
       " '11030',\n",
       " '10801',\n",
       " '92626-1902',\n",
       " '07302',\n",
       " '18773',\n",
       " '12222',\n",
       " '10710',\n",
       " '10103',\n",
       " '07057',\n",
       " '10162',\n",
       " '11582',\n",
       " '10281',\n",
       " '10271',\n",
       " '10107',\n",
       " 'HARRISBURG',\n",
       " '11735',\n",
       " '07305',\n",
       " 'N5X3A6',\n",
       " '11746',\n",
       " '11371',\n",
       " '23450',\n",
       " 11580.0,\n",
       " 10591.0,\n",
       " 11005.0,\n",
       " 19034.0,\n",
       " 11596.0,\n",
       " 11779.0,\n",
       " 7621.0,\n",
       " 11021.0,\n",
       " 11241.0,\n",
       " 10169.0,\n",
       " 100000.0,\n",
       " 11242.0,\n",
       " 10151.0,\n",
       " 11697.0,\n",
       " 7086.0,\n",
       " 10177.0,\n",
       " 10118.0,\n",
       " 10105.0,\n",
       " 10152.0,\n",
       " 10168.0,\n",
       " 7093.0,\n",
       " 10917.0,\n",
       " 10110.0,\n",
       " 10153.0,\n",
       " 10178.0,\n",
       " 11570.0,\n",
       " 10601.0,\n",
       " 10704.0,\n",
       " 7424.0,\n",
       " 10281.0,\n",
       " 10158.0,\n",
       " '10158',\n",
       " '10172',\n",
       " '10179',\n",
       " 'IDK',\n",
       " '11801',\n",
       " '10601',\n",
       " '11590',\n",
       " '10155',\n",
       " '11202',\n",
       " '1801',\n",
       " '11581',\n",
       " 11359.0,\n",
       " 11758.0,\n",
       " 10278.0,\n",
       " 43017.0,\n",
       " 10154.0,\n",
       " 11553.0,\n",
       " 10162.0,\n",
       " 11695.0,\n",
       " 10041.0,\n",
       " 11741.0,\n",
       " 98335.0,\n",
       " 14814.0,\n",
       " 10111.0,\n",
       " '12345',\n",
       " '11572',\n",
       " '11520',\n",
       " '14614-195',\n",
       " '10121',\n",
       " '10105',\n",
       " '10701',\n",
       " 0.0,\n",
       " 17106.0,\n",
       " 979113.0,\n",
       " 10120.0,\n",
       " 12345.0,\n",
       " 11030.0,\n",
       " 11797.0,\n",
       " 100.0,\n",
       " 11710.0,\n",
       " 33624.0,\n",
       " 8682.0,\n",
       " 11747.0,\n",
       " 1757.0,\n",
       " 11561.0,\n",
       " 7304.0,\n",
       " 6851.0,\n",
       " 11590.0,\n",
       " 94267.0,\n",
       " 10167.0,\n",
       " 11749.0,\n",
       " 11756.0,\n",
       " 10174.0,\n",
       " 10550.0,\n",
       " 89119.0,\n",
       " 14068.0,\n",
       " 11722.0,\n",
       " 11520.0,\n",
       " 6460.0,\n",
       " 32255.0,\n",
       " 10173.0,\n",
       " 10165.0,\n",
       " 11946.0,\n",
       " '29616-0759',\n",
       " '07032',\n",
       " '10278',\n",
       " '11575',\n",
       " 11963.0,\n",
       " 10106.0,\n",
       " 83.0,\n",
       " 11566.0,\n",
       " 6870.0,\n",
       " 7001.0,\n",
       " 10710.0,\n",
       " 11735.0,\n",
       " 11572.0,\n",
       " '10165',\n",
       " '10279',\n",
       " '11251',\n",
       " 'NJ 07114',\n",
       " '10106',\n",
       " '07666',\n",
       " '11516',\n",
       " '10177',\n",
       " '10170',\n",
       " '43215-1441',\n",
       " '00000',\n",
       " '08081',\n",
       " '10803',\n",
       " 11507.0,\n",
       " 11701.0,\n",
       " 11563.0,\n",
       " 7047.0,\n",
       " 3108.0,\n",
       " '11021',\n",
       " '07003',\n",
       " '10152',\n",
       " '07029',\n",
       " '10041',\n",
       " '31093',\n",
       " '11735-0230',\n",
       " 89118.0,\n",
       " 11803.0,\n",
       " 11559.0,\n",
       " 11565.0,\n",
       " 7080.0,\n",
       " 12601.0,\n",
       " 10155.0,\n",
       " 10171.0,\n",
       " 7208.0,\n",
       " 11757.0,\n",
       " 11042.0,\n",
       " '1101',\n",
       " '10111',\n",
       " '10173',\n",
       " '10096',\n",
       " '07087',\n",
       " 'DID N',\n",
       " '10956',\n",
       " 10048.0,\n",
       " 10123.0,\n",
       " 10122.0,\n",
       " 11251.0]"
      ]
     },
     "execution_count": 5,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "list(requests[\"Incident Zip\"].unique())"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {
    "editable": true,
    "slideshow": {
     "slide_type": "fragment"
    },
    "tags": []
   },
   "source": [
    "ZIP codes _look_ numeric, but aren't really."
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {
    "editable": true,
    "slideshow": {
     "slide_type": "subslide"
    },
    "tags": []
   },
   "source": [
    "[Read the ZIP codes in as strings.](https://pandas.pydata.org/pandas-docs/stable/user_guide/text.html#text-data-types)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 6,
   "metadata": {
    "editable": true,
    "execution": {
     "iopub.execute_input": "2025-01-18T15:52:17.216216Z",
     "iopub.status.busy": "2025-01-18T15:52:17.216133Z",
     "iopub.status.idle": "2025-01-18T15:52:21.599031Z",
     "shell.execute_reply": "2025-01-18T15:52:21.598734Z"
    },
    "slideshow": {
     "slide_type": "-"
    },
    "tags": []
   },
   "outputs": [
    {
     "name": "stderr",
     "output_type": "stream",
     "text": [
      "/var/folders/kr/nx0m1j811kz5vy8c87ffchzr0000gn/T/ipykernel_3268/1354368569.py:1: DtypeWarning: Columns (20,31,34) have mixed types. Specify dtype option on import or set low_memory=False.\n",
      "  requests2 = pd.read_csv(url, dtype={\"Incident Zip\": \"string\"})\n"
     ]
    }
   ],
   "source": [
    "requests2 = pd.read_csv(url, dtype={\"Incident Zip\": \"string\"})"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {
    "editable": true,
    "slideshow": {
     "slide_type": "fragment"
    },
    "tags": []
   },
   "source": [
    "We fixed the dtype warning for column 8 (`Incident Zip`)."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 7,
   "metadata": {
    "editable": true,
    "execution": {
     "iopub.execute_input": "2025-01-18T15:52:21.600408Z",
     "iopub.status.busy": "2025-01-18T15:52:21.600325Z",
     "iopub.status.idle": "2025-01-18T15:52:21.615047Z",
     "shell.execute_reply": "2025-01-18T15:52:21.614690Z"
    },
    "scrolled": true,
    "slideshow": {
     "slide_type": "subslide"
    },
    "tags": []
   },
   "outputs": [
    {
     "data": {
      "text/plain": [
       "['11235',\n",
       " '11221',\n",
       " '11693',\n",
       " '11216',\n",
       " '10465',\n",
       " '11367',\n",
       " '10459',\n",
       " '11101',\n",
       " '11362',\n",
       " '10014',\n",
       " '11234',\n",
       " '11436',\n",
       " '10305',\n",
       " '10467',\n",
       " '11208',\n",
       " '10451',\n",
       " '11419',\n",
       " '11237',\n",
       " '11220',\n",
       " '10469',\n",
       " '11385',\n",
       " '10470',\n",
       " '11694',\n",
       " '10036',\n",
       " <NA>,\n",
       " '10473',\n",
       " '11435',\n",
       " '10040',\n",
       " '10472',\n",
       " '11225',\n",
       " '10019',\n",
       " '11434',\n",
       " '11226',\n",
       " '10010',\n",
       " '11211',\n",
       " '11421',\n",
       " '10026',\n",
       " '10013',\n",
       " '11423',\n",
       " '10002',\n",
       " '10453',\n",
       " '11213',\n",
       " '11104',\n",
       " '11249',\n",
       " '11361',\n",
       " '11233',\n",
       " '11224',\n",
       " '11374',\n",
       " '10025',\n",
       " '10022',\n",
       " '11214',\n",
       " '11209',\n",
       " '11366',\n",
       " '10304',\n",
       " '10027',\n",
       " '11378',\n",
       " '11206',\n",
       " '10021',\n",
       " '11364',\n",
       " '10065',\n",
       " '10456',\n",
       " '10314',\n",
       " '10312',\n",
       " '11212',\n",
       " '11379',\n",
       " '10462',\n",
       " '11231',\n",
       " '10460',\n",
       " '11416',\n",
       " '10001',\n",
       " '11357',\n",
       " '11413',\n",
       " '11210',\n",
       " '11217',\n",
       " '11223',\n",
       " '11417',\n",
       " '11418',\n",
       " '11218',\n",
       " '11230',\n",
       " '11207',\n",
       " '11691',\n",
       " '10468',\n",
       " '10007',\n",
       " '10310',\n",
       " '10306',\n",
       " '11103',\n",
       " '11105',\n",
       " '11433',\n",
       " '11203',\n",
       " '10307',\n",
       " '11229',\n",
       " '11372',\n",
       " '10032',\n",
       " '11420',\n",
       " '10017',\n",
       " '10301',\n",
       " '11368',\n",
       " '11201',\n",
       " '11365',\n",
       " '11422',\n",
       " '10452',\n",
       " '11377',\n",
       " '10029',\n",
       " '10003',\n",
       " '10075',\n",
       " '11222',\n",
       " '10128',\n",
       " '11415',\n",
       " '11204',\n",
       " '10030',\n",
       " '11432',\n",
       " '10308',\n",
       " '11102',\n",
       " '10016',\n",
       " '10463',\n",
       " '11412',\n",
       " '10011',\n",
       " '11106',\n",
       " '10457',\n",
       " '11375',\n",
       " '11356',\n",
       " '11228',\n",
       " '11369',\n",
       " '10458',\n",
       " '11411',\n",
       " '11215',\n",
       " '10309',\n",
       " '11358',\n",
       " '11355',\n",
       " '11219',\n",
       " '10031',\n",
       " '10303',\n",
       " '11232',\n",
       " '10302',\n",
       " '11238',\n",
       " '10005',\n",
       " '11429',\n",
       " '11205',\n",
       " '10023',\n",
       " '11373',\n",
       " '10033',\n",
       " '10039',\n",
       " '10028',\n",
       " '11363',\n",
       " '11354',\n",
       " '11692',\n",
       " '10455',\n",
       " '11370',\n",
       " '10035',\n",
       " '10012',\n",
       " '10024',\n",
       " '10009',\n",
       " '10034',\n",
       " '11001',\n",
       " '10466',\n",
       " '11427',\n",
       " '11004',\n",
       " '10454',\n",
       " '11414',\n",
       " '11360',\n",
       " '10461',\n",
       " '10018',\n",
       " '10006',\n",
       " '11236',\n",
       " '11428',\n",
       " '10474',\n",
       " '10471',\n",
       " '10037',\n",
       " '10475',\n",
       " '11430',\n",
       " '10119',\n",
       " '10038',\n",
       " '11426',\n",
       " '11239',\n",
       " '10151',\n",
       " '10120',\n",
       " '10112',\n",
       " '10168',\n",
       " '10004',\n",
       " '10464',\n",
       " '18773-9640',\n",
       " '10282',\n",
       " '11109',\n",
       " '10280',\n",
       " '07114',\n",
       " '11040',\n",
       " '10000',\n",
       " '07090',\n",
       " '10020',\n",
       " '00083',\n",
       " '10044',\n",
       " '10069',\n",
       " '10118',\n",
       " '07102',\n",
       " '11359',\n",
       " '11005',\n",
       " '10169',\n",
       " '11697',\n",
       " '10115',\n",
       " '10801',\n",
       " '10103',\n",
       " '10271',\n",
       " '10107',\n",
       " '07078',\n",
       " '56303',\n",
       " '11386',\n",
       " '10952',\n",
       " '06811',\n",
       " '10121',\n",
       " '37214',\n",
       " '10538',\n",
       " '10279',\n",
       " '11371',\n",
       " '18017',\n",
       " '07115',\n",
       " '77036',\n",
       " '10123',\n",
       " '11030',\n",
       " '92626-1902',\n",
       " '07302',\n",
       " '18773',\n",
       " '12222',\n",
       " '10710',\n",
       " '07057',\n",
       " '10162',\n",
       " '11582',\n",
       " '10281',\n",
       " 'HARRISBURG',\n",
       " '11735',\n",
       " '07305',\n",
       " 'N5X3A6',\n",
       " '11746',\n",
       " '23450',\n",
       " '11580',\n",
       " '10591',\n",
       " '19034',\n",
       " '11596',\n",
       " '11779',\n",
       " '07621',\n",
       " '11021',\n",
       " '11241',\n",
       " '100000',\n",
       " '11242',\n",
       " '07086',\n",
       " '10177',\n",
       " '10105',\n",
       " '10152',\n",
       " '07093',\n",
       " '10917',\n",
       " '10110',\n",
       " '10153',\n",
       " '10178',\n",
       " '11570',\n",
       " '10601',\n",
       " '10704',\n",
       " '07424',\n",
       " '10158',\n",
       " '10172',\n",
       " '10179',\n",
       " 'IDK',\n",
       " '11801',\n",
       " '11590',\n",
       " '10155',\n",
       " '11202',\n",
       " '1801',\n",
       " '11581',\n",
       " '11758',\n",
       " '10278',\n",
       " '43017',\n",
       " '10154',\n",
       " '11553',\n",
       " '11695',\n",
       " '10041',\n",
       " '11741',\n",
       " '98335',\n",
       " '14814',\n",
       " '10111',\n",
       " '12345',\n",
       " '11572',\n",
       " '11520',\n",
       " '14614-195',\n",
       " '10701',\n",
       " '00000',\n",
       " '17106',\n",
       " '979113',\n",
       " '11797',\n",
       " '100',\n",
       " '11710',\n",
       " '33624',\n",
       " '8682',\n",
       " '11747',\n",
       " '01757',\n",
       " '11561',\n",
       " '07304',\n",
       " '000000',\n",
       " '06851',\n",
       " '94267',\n",
       " '10167',\n",
       " '11749',\n",
       " '11756',\n",
       " '10174',\n",
       " '10550',\n",
       " '89119',\n",
       " '14068',\n",
       " '11722',\n",
       " '06460',\n",
       " '32255',\n",
       " '10173',\n",
       " '10165',\n",
       " '11946',\n",
       " '29616-0759',\n",
       " '07032',\n",
       " '11575',\n",
       " '11963',\n",
       " '10106',\n",
       " '11566',\n",
       " '06870',\n",
       " '07001',\n",
       " '11251',\n",
       " 'NJ 07114',\n",
       " '07666',\n",
       " '11516',\n",
       " '10170',\n",
       " '43215-1441',\n",
       " '08081',\n",
       " '10803',\n",
       " '11507',\n",
       " '11701',\n",
       " '11563',\n",
       " '07047',\n",
       " '03108',\n",
       " '07003',\n",
       " '07029',\n",
       " '31093',\n",
       " '11735-0230',\n",
       " '89118',\n",
       " '11803',\n",
       " '11559',\n",
       " '11565',\n",
       " '07080',\n",
       " '12601',\n",
       " '10171',\n",
       " '07208',\n",
       " '11757',\n",
       " '11042',\n",
       " '1101',\n",
       " '10096',\n",
       " '07087',\n",
       " 'DID N',\n",
       " '10956',\n",
       " '10048',\n",
       " '10122']"
      ]
     },
     "execution_count": 7,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "list(requests2[\"Incident Zip\"].unique())"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {
    "editable": true,
    "slideshow": {
     "slide_type": "subslide"
    },
    "tags": []
   },
   "source": [
    "### Find invalid ZIP codes\n",
    "\n",
    "Use a [regular expression (regex)](https://regexone.com/) to [find strings that match a pattern](https://pandas.pydata.org/pandas-docs/stable/user_guide/text.html#testing-for-strings-that-match-or-contain-a-pattern):\n",
    "\n",
    "```\n",
    "^\\d{5}(?:-\\d{4})?$\n",
    "│ │ │  │        │└─ end of string\n",
    "│ │ │  │        └─ optional\n",
    "│ │ │  └─ capture group\n",
    "│ │ └─ count\n",
    "│ └─ numeric/digit character\n",
    "└─ start of string\n",
    "```\n",
    "\n",
    "[regex101](https://regex101.com/) is useful for testing them."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 8,
   "metadata": {
    "editable": true,
    "execution": {
     "iopub.execute_input": "2025-01-18T15:52:21.616516Z",
     "iopub.status.busy": "2025-01-18T15:52:21.616404Z",
     "iopub.status.idle": "2025-01-18T15:52:21.710926Z",
     "shell.execute_reply": "2025-01-18T15:52:21.710653Z"
    },
    "slideshow": {
     "slide_type": "subslide"
    },
    "tags": []
   },
   "outputs": [
    {
     "data": {
      "text/plain": [
       "55017     HARRISBURG\n",
       "58100         N5X3A6\n",
       "80798         100000\n",
       "120304           IDK\n",
       "123304          1801\n",
       "173518     14614-195\n",
       "192034        979113\n",
       "201463           100\n",
       "207158          8682\n",
       "216745        000000\n",
       "325071      NJ 07114\n",
       "425985          1101\n",
       "441166         DID N\n",
       "Name: Incident Zip, dtype: string"
      ]
     },
     "execution_count": 8,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# find valid ZIP codes\n",
    "valid_zips = requests2[\"Incident Zip\"].str.contains(r\"^\\d{5}(?:-\\d{4})?$\")\n",
    "\n",
    "# filter the DataFrame to only invalid ZIP codes\n",
    "invalid_zips = valid_zips == False\n",
    "requests_with_invalid_zips = requests2[invalid_zips]\n",
    "requests_with_invalid_zips[\"Incident Zip\"]"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {
    "editable": true,
    "slideshow": {
     "slide_type": "subslide"
    },
    "tags": []
   },
   "source": [
    "[Clear](https://pandas.pydata.org/pandas-docs/stable/user_guide/missing_data.html#inserting-missing-data) any invalid ZIP codes:"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 9,
   "metadata": {
    "editable": true,
    "execution": {
     "iopub.execute_input": "2025-01-18T15:52:21.712247Z",
     "iopub.status.busy": "2025-01-18T15:52:21.712163Z",
     "iopub.status.idle": "2025-01-18T15:52:21.739318Z",
     "shell.execute_reply": "2025-01-18T15:52:21.739027Z"
    },
    "slideshow": {
     "slide_type": "-"
    },
    "tags": []
   },
   "outputs": [],
   "source": [
    "requests2.loc[invalid_zips, \"Incident Zip\"] = None"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {
    "editable": true,
    "slideshow": {
     "slide_type": "subslide"
    },
    "tags": []
   },
   "source": [
    "Additonal data cleaning tips:\n",
    "\n",
    "- Hard part is finding what needs to be done\n",
    "- Will be specific to your use case\n",
    "- Document what you did, since it will affect your results"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {
    "editable": true,
    "slideshow": {
     "slide_type": "slide"
    },
    "tags": []
   },
   "source": [
    "## [In-class exercise](https://python-public-policy.afeld.me/en/{{school_slug}}/lecture_2_exercise.html)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {
    "editable": true,
    "slideshow": {
     "slide_type": "slide"
    },
    "tags": [
     "nyu-only"
    ]
   },
   "source": [
    "## [Concatenation](https://pandas.pydata.org/docs/user_guide/merging.html#concat)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 10,
   "metadata": {
    "editable": true,
    "execution": {
     "iopub.execute_input": "2025-01-18T15:52:21.741291Z",
     "iopub.status.busy": "2025-01-18T15:52:21.741183Z",
     "iopub.status.idle": "2025-01-18T15:52:21.746319Z",
     "shell.execute_reply": "2025-01-18T15:52:21.746105Z"
    },
    "slideshow": {
     "slide_type": "subslide"
    },
    "tags": [
     "nyu-only"
    ]
   },
   "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>month</th>\n",
       "      <th>revenue</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>Jan</td>\n",
       "      <td>175589</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>Feb</td>\n",
       "      <td>189645</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>Mar</td>\n",
       "      <td>163423</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "  month  revenue\n",
       "0   Jan   175589\n",
       "1   Feb   189645\n",
       "2   Mar   163423"
      ]
     },
     "execution_count": 10,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "revenue_q1 = pd.DataFrame(\n",
    "    {\n",
    "        \"month\": [\"Jan\", \"Feb\", \"Mar\"],\n",
    "        \"revenue\": [175589, 189645, 163423],\n",
    "    }\n",
    ")\n",
    "revenue_q1"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 11,
   "metadata": {
    "editable": true,
    "execution": {
     "iopub.execute_input": "2025-01-18T15:52:21.747499Z",
     "iopub.status.busy": "2025-01-18T15:52:21.747425Z",
     "iopub.status.idle": "2025-01-18T15:52:21.750746Z",
     "shell.execute_reply": "2025-01-18T15:52:21.750509Z"
    },
    "slideshow": {
     "slide_type": "subslide"
    },
    "tags": [
     "nyu-only"
    ]
   },
   "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>month</th>\n",
       "      <th>revenue</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>Apr</td>\n",
       "      <td>14567</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>May</td>\n",
       "      <td>15863</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>Jun</td>\n",
       "      <td>17999</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "  month  revenue\n",
       "0   Apr    14567\n",
       "1   May    15863\n",
       "2   Jun    17999"
      ]
     },
     "execution_count": 11,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "revenue_q2 = pd.DataFrame(\n",
    "    {\n",
    "        \"month\": [\"Apr\", \"May\", \"Jun\"],\n",
    "        \"revenue\": [14567, 15863, 17999],\n",
    "    }\n",
    ")\n",
    "revenue_q2"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 12,
   "metadata": {
    "editable": true,
    "execution": {
     "iopub.execute_input": "2025-01-18T15:52:21.751939Z",
     "iopub.status.busy": "2025-01-18T15:52:21.751856Z",
     "iopub.status.idle": "2025-01-18T15:52:21.755204Z",
     "shell.execute_reply": "2025-01-18T15:52:21.754955Z"
    },
    "scrolled": true,
    "slideshow": {
     "slide_type": "subslide"
    },
    "tags": [
     "nyu-only"
    ]
   },
   "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>month</th>\n",
       "      <th>revenue</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>Jan</td>\n",
       "      <td>175589</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>Feb</td>\n",
       "      <td>189645</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>Mar</td>\n",
       "      <td>163423</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>Apr</td>\n",
       "      <td>14567</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>May</td>\n",
       "      <td>15863</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>Jun</td>\n",
       "      <td>17999</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "  month  revenue\n",
       "0   Jan   175589\n",
       "1   Feb   189645\n",
       "2   Mar   163423\n",
       "0   Apr    14567\n",
       "1   May    15863\n",
       "2   Jun    17999"
      ]
     },
     "execution_count": 12,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "pd.concat([revenue_q1, revenue_q2])"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {
    "editable": true,
    "slideshow": {
     "slide_type": "slide"
    },
    "tags": []
   },
   "source": [
    "## Simple [merge](https://pandas.pydata.org/docs/user_guide/merging.html#merge)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {
    "editable": true,
    "slideshow": {
     "slide_type": "subslide"
    },
    "tags": []
   },
   "source": [
    "_I had [Copilot](https://code.visualstudio.com/docs/copilot/overview) generate the DataFrames, so no idea if the numbers are real._"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 13,
   "metadata": {
    "editable": true,
    "execution": {
     "iopub.execute_input": "2025-01-18T15:52:21.756818Z",
     "iopub.status.busy": "2025-01-18T15:52:21.756679Z",
     "iopub.status.idle": "2025-01-18T15:52:21.760337Z",
     "shell.execute_reply": "2025-01-18T15:52:21.760070Z"
    },
    "slideshow": {
     "slide_type": ""
    },
    "tags": []
   },
   "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>Country</th>\n",
       "      <th>Population</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>China</td>\n",
       "      <td>1444216107</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>India</td>\n",
       "      <td>1393409038</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>Pakistan</td>\n",
       "      <td>220892331</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "    Country  Population\n",
       "0     China  1444216107\n",
       "1     India  1393409038\n",
       "2  Pakistan   220892331"
      ]
     },
     "execution_count": 13,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "populations = pd.DataFrame(\n",
    "    {\n",
    "        \"Country\": [\"China\", \"India\", \"Pakistan\"],\n",
    "        \"Population\": [1444216107, 1393409038, 220892331],\n",
    "    }\n",
    ")\n",
    "\n",
    "populations"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 14,
   "metadata": {
    "editable": true,
    "execution": {
     "iopub.execute_input": "2025-01-18T15:52:21.761718Z",
     "iopub.status.busy": "2025-01-18T15:52:21.761641Z",
     "iopub.status.idle": "2025-01-18T15:52:21.765096Z",
     "shell.execute_reply": "2025-01-18T15:52:21.764862Z"
    },
    "slideshow": {
     "slide_type": "subslide"
    },
    "tags": []
   },
   "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>Country</th>\n",
       "      <th>GDP</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>China</td>\n",
       "      <td>14342903</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>India</td>\n",
       "      <td>2875142</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>United States</td>\n",
       "      <td>21433226</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>Indonesia</td>\n",
       "      <td>1058393</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>Pakistan</td>\n",
       "      <td>263687</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "         Country       GDP\n",
       "0          China  14342903\n",
       "1          India   2875142\n",
       "2  United States  21433226\n",
       "3      Indonesia   1058393\n",
       "4       Pakistan    263687"
      ]
     },
     "execution_count": 14,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "gdps = pd.DataFrame(\n",
    "    {\n",
    "        \"Country\": [\"China\", \"India\", \"United States\", \"Indonesia\", \"Pakistan\"],\n",
    "        \"GDP\": [14342903, 2875142, 21433226, 1058393, 263687],\n",
    "    }\n",
    ")\n",
    "gdps"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {
    "editable": true,
    "id": "Z83dJbJ81wNV",
    "slideshow": {
     "slide_type": "subslide"
    },
    "tags": []
   },
   "source": [
    "To join dataframes together, we will use the [pandas `.merge()` function](https://pandas.pydata.org/pandas-docs/stable/getting_started/intro_tutorials/08_combine_dataframes.html#join-tables-using-a-common-identifier).\n",
    "\n",
    "![merge diagram](https://pandas.pydata.org/pandas-docs/stable/_images/08_merge_left.svg)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {
    "editable": true,
    "slideshow": {
     "slide_type": "subslide"
    },
    "tags": []
   },
   "source": [
    "`.merge()` is comparable to:\n",
    "\n",
    "- [SQL `JOIN`](https://pandas.pydata.org/pandas-docs/stable/getting_started/comparison/comparison_with_sql.html#join)\n",
    "- [Spreadsheet `VLOOKUP`](https://pandas.pydata.org/pandas-docs/stable/getting_started/comparison/comparison_with_spreadsheets.html#merging)\n",
    "\n",
    "In general, called [\"record linkage\" or \"entity resolution\"](https://en.wikipedia.org/wiki/Record_linkage)."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 15,
   "metadata": {
    "editable": true,
    "execution": {
     "iopub.execute_input": "2025-01-18T15:52:21.766503Z",
     "iopub.status.busy": "2025-01-18T15:52:21.766415Z",
     "iopub.status.idle": "2025-01-18T15:52:21.770397Z",
     "shell.execute_reply": "2025-01-18T15:52:21.770039Z"
    },
    "slideshow": {
     "slide_type": "subslide"
    },
    "tags": []
   },
   "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>Country</th>\n",
       "      <th>Population</th>\n",
       "      <th>GDP</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>China</td>\n",
       "      <td>1444216107</td>\n",
       "      <td>14342903</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>India</td>\n",
       "      <td>1393409038</td>\n",
       "      <td>2875142</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>Pakistan</td>\n",
       "      <td>220892331</td>\n",
       "      <td>263687</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "    Country  Population       GDP\n",
       "0     China  1444216107  14342903\n",
       "1     India  1393409038   2875142\n",
       "2  Pakistan   220892331    263687"
      ]
     },
     "execution_count": 15,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "pd.merge(populations, gdps, on=\"Country\")"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 16,
   "metadata": {
    "editable": true,
    "execution": {
     "iopub.execute_input": "2025-01-18T15:52:21.771812Z",
     "iopub.status.busy": "2025-01-18T15:52:21.771723Z",
     "iopub.status.idle": "2025-01-18T15:52:21.776541Z",
     "shell.execute_reply": "2025-01-18T15:52:21.776293Z"
    },
    "scrolled": true,
    "slideshow": {
     "slide_type": "fragment"
    },
    "tags": []
   },
   "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>Country</th>\n",
       "      <th>Population</th>\n",
       "      <th>GDP</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>China</td>\n",
       "      <td>1.444216e+09</td>\n",
       "      <td>14342903</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>India</td>\n",
       "      <td>1.393409e+09</td>\n",
       "      <td>2875142</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>Indonesia</td>\n",
       "      <td>NaN</td>\n",
       "      <td>1058393</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>Pakistan</td>\n",
       "      <td>2.208923e+08</td>\n",
       "      <td>263687</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>United States</td>\n",
       "      <td>NaN</td>\n",
       "      <td>21433226</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "         Country    Population       GDP\n",
       "0          China  1.444216e+09  14342903\n",
       "1          India  1.393409e+09   2875142\n",
       "2      Indonesia           NaN   1058393\n",
       "3       Pakistan  2.208923e+08    263687\n",
       "4  United States           NaN  21433226"
      ]
     },
     "execution_count": 16,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "pd.merge(populations, gdps, on=\"Country\", how=\"outer\")"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {
    "editable": true,
    "slideshow": {
     "slide_type": "subslide"
    },
    "tags": []
   },
   "source": [
    "[Different types of merges](https://www.geeksforgeeks.org/different-types-of-joins-in-pandas/)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {
    "editable": true,
    "slideshow": {
     "slide_type": "slide"
    },
    "tags": [
     "columbia-only"
    ]
   },
   "source": [
    "## In-class exercise 2\n",
    "\n",
    "Let's compute the migrants as a percent of total population by country using [UN data](https://data.un.org/)."
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {
    "slideshow": {
     "slide_type": "slide"
    }
   },
   "source": [
    "## [Homework 2](https://python-public-policy.afeld.me/en/{{school_slug}}/hw_2.html)"
   ]
  }
 ],
 "metadata": {
  "celltoolbar": "Slideshow",
  "kernelspec": {
   "display_name": "Python [conda env:python-public-policy] *",
   "language": "python",
   "name": "conda-env-python-public-policy-py"
  },
  "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.11.11"
  },
  "rise": {
   "scroll": true
  },
  "toc": {
   "base_numbering": 1,
   "nav_menu": {},
   "number_sections": true,
   "sideBar": true,
   "skip_h1_title": false,
   "title_cell": "Table of Contents",
   "title_sidebar": "Contents",
   "toc_cell": false,
   "toc_position": {},
   "toc_section_display": true,
   "toc_window_display": false
  }
 },
 "nbformat": 4,
 "nbformat_minor": 4
}