teaching_materials/module_3/module_3_exercises.ipynb

{
 "cells": [
  {
   "cell_type": "markdown",
   "metadata": {
    "ExecuteTime": {
     "end_time": "2017-08-23T15:30:03.634114Z",
     "start_time": "2017-08-23T15:30:03.629294Z"
    }
   },
   "source": [
    "# Videos and Exercises for Session 3: Data Structuring in Pandas II\n",
    "\n",
    "In this combined teaching module and exercise set, you will continue working with structuring data. \n",
    "\n",
    "In the last session, you were working with making operations on relatively clean data. However, before it is meaningful to make such operations, you will (as a data scientist) often have to do some very preliminary cleaning, involving for instance dealing with missings and duplicates as well as combining and restructuring larger sets of data. These are among the topics that we will be focusing on today.\n",
    "\n",
    "The notebook is structured as follows:\n",
    "1. Missings and Duplicated Data:\n",
    "    - Handling Missings: Delete or Interpolate?\n",
    "    - Spotting and Interpreting Duplicates\n",
    "2. Combining Data Sets:\n",
    "    - Intro to `merge`, `concat` and `join`\n",
    "    - Horizontal and Vertical Merging\n",
    "3. Split-Apply-Combine\n",
    "    - Finding Means and Other Characteristics from Data Subsets (aggregation)\n",
    "4. Reshaping Data\n",
    "    - Wide and Long Data\n",
    "    - Intro to `stack`"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {
    "ExecuteTime": {
     "end_time": "2017-08-23T15:30:03.634114Z",
     "start_time": "2017-08-23T15:30:03.629294Z"
    }
   },
   "source": [
    "## Loading\n",
    "Before we get started.... load in the required modules and set up the plotting library!"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 1,
   "metadata": {},
   "outputs": [],
   "source": [
    "%matplotlib inline\n",
    "import numpy as np\n",
    "import pandas as pd\n",
    "import matplotlib.pyplot as plt\n",
    "from IPython.display import YouTubeVideo"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "#  Part 1: Duplicates and Missings\n",
    "\n",
    "In this section we will use [this dataset](https://archive.ics.uci.edu/ml/datasets/Adult) from the [UCI Machine Learning Repository](https://archive.ics.uci.edu/ml/datasets.html) to practice some basic operations on pandas dataframes. This is an extract from the US Census containing almost 50,000 rows of individual-level micro data from 1994.\n"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "> **Ex. 3.1.1:** This link `'https://archive.ics.uci.edu/ml/machine-learning-databases/adult/adult.data'` leads to a comma-separated file with income data from a US census. Load the data into a pandas dataframe and show the 25th to 35th row.\n",
    ">\n",
    "> _Hint 1:_ There are no column names in the dataset. Use the list `['age','workclass', 'fnlwgt', 'educ', 'educ_num', 'marital_status', 'occupation','relationship', 'race', 'sex','capital_gain', 'capital_loss', 'hours_per_week', 'native_country', 'wage']` as names. \n",
    ">\n",
    "> _Hint 2:_ When you read in the csv, you might find that pandas includes whitespace in all of the cells. To get around this include the argument `skipinitialspace = True` to `read_csv()`. "
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 2,
   "metadata": {
    "deletable": false,
    "nbgrader": {
     "cell_type": "code",
     "checksum": "85a3e6585253444281c7c88d89254794",
     "grade": false,
     "grade_id": "cell-5162c9a337cb8792",
     "locked": false,
     "schema_version": 3,
     "solution": true,
     "task": false
    }
   },
   "outputs": [
    {
     "ename": "NotImplementedError",
     "evalue": "",
     "output_type": "error",
     "traceback": [
      "\u001b[0;31m---------------------------------------------------------------------------\u001b[0m",
      "\u001b[0;31mNotImplementedError\u001b[0m                       Traceback (most recent call last)",
      "Cell \u001b[0;32mIn[2], line 2\u001b[0m\n\u001b[1;32m      1\u001b[0m \u001b[38;5;66;03m# YOUR CODE HERE\u001b[39;00m\n\u001b[0;32m----> 2\u001b[0m \u001b[38;5;28;01mraise\u001b[39;00m \u001b[38;5;167;01mNotImplementedError\u001b[39;00m()\n",
      "\u001b[0;31mNotImplementedError\u001b[0m: "
     ]
    }
   ],
   "source": [
    "# YOUR CODE HERE\n",
    "raise NotImplementedError()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "##  Missing data\n",
    "\n",
    "Often our data having information missing, e.g. one row lacks data on education for a specific person. Watch the video below about missing data type and get some simple tools to deal with the problem."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 3,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "image/jpeg": "/9j/4AAQSkZJRgABAQAAAQABAAD/2wCEABALDBgYFhwaGQ4cHRsfIyYmIiIiIycrJSgmNjI1PC0nMis1SFBCNTpLQjIwRWFFS1NbW11bM0hlbWVkbVBZalcBERISGBYXLRobL1c3NjZXV1dXV1dXV11XV1dXV1dXV1dXV1dXV1dXV2RXV1dkXVdXV1dXV1dXV1dXV1dXV11XV//AABEIAWgB4AMBIgACEQEDEQH/xAAbAAEAAgMBAQAAAAAAAAAAAAAAAQIDBgcEBf/EAEsQAQABAgMEBwYCAwwJBQEAAAABAhEDBBIXIVGSEzFBUlSR0gUiU2Fx0YGhBhQyBzNEcoKjscHC4eLwFiMlNEJDYmWDJGOyw/EV/8QAGAEBAQEBAQAAAAAAAAAAAAAAAAECAwT/xAAdEQEBAAICAwEAAAAAAAAAAAAAAQIRITEDEkET/9oADAMBAAIRAxEAPwDn4AAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAA3DZxnvE5bmxPSbOM94nLc2J6QaeNw2cZ7xOW5sT0mzjPeJy3NiekGnjcNnGe8TlubE9Js4z3ictzYnpBp43DZxnvE5bmxPSbOM94nLc2J6QaeNw2cZ7xOW5sT0mzjPeJy3NiekGnjcNnGe8TlubE9Js4z3ictzYnpBp43DZxnvE5bmxPSbOM94nLc2J6QaeNw2cZ7xOW5sT0mzjPeJy3NiekGnjcNnGe8TlubE9Js4z3ictzYnpBp43DZxnvE5bmxPSbOM94nLc2J6QaeNw2cZ7xOW5sT0mzjPeJy3NiekGnjcNnGe8TlubE9Js4z3ictzYnpBp43DZxnvE5bmxPSbOM94nLc2J6QaeNw2cZ7xOW5sT0mzjPeJy3NiekGnjcNnGe8TlubE9Js4z3ictzYnpBp43DZxnvE5bmxPSbOM94nLc2J6QaeNw2cZ7xOW5sT0mzjPeJy3NiekGnjcNnGe8TlubE9Js4z3ictzYnpBp43DZxnvE5bmxPSbOM94nLc2J6QaeNw2cZ7xOW5sT0mzjPeJy3NiekGnjcNnGe8TlubE9Js4z3ictzYnpBp43DZxnvE5bmxPSbOM94nLc2J6QaeNw2cZ7xOW5sT0mzjPeJy3NiekGnjcNnGe8TlubE9Js4z3ictzYnpBp43DZxnvE5bmxPS+P7f8A0bx/Z/R9NiYVXSatOiap/Zte94jvQD44AAAAAAAAEQDuftPO9BhxMUxNVVUU03va89s27GHIZ7FnGnAx6KIxNOqNF7Wva037XszeVpxqNNV+uJiYm0xMdUxPFhyPs2jBmaoxK66pi2qubzEXvaOEA9oAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAADn/7qn8D/wDN/YdAaD+6fh6qsnTExEz01r/yAc9GajLzVNomL6dXb1GHlqqtVpj3ZtPX8/tIMIyTgzoivsnd2rfq06qqddN6b8eyLz2fIFKcOZ39idNPCX18hl6aqadVVNvr1voz7LpirVpiI7eCyN6jWK8CYi/Z+bE2H2lkaJjXR2NfqptMxwKzZpD6fsrJU101YlfVTfTHGbdf4PmPveyq4nK1U3i8TV28Yv8A1MZXh08Uly5diAacgAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAABof7p+FVV+p6Y3xOL2xG/3LW8m+ND/AHUMaaYysRumZxZvwtp+4NDpnE34l4nVqiZm2/juWonFiImIi1c3/wCG02ibx5TO75qU5i2Ho0z1zPXum9uz8IThZqaYiIp3WtMT29dp+UxcEUxXNERERpqqt2b57I/NOrEnEqm0TXarVujqtv8AyMHNTRTERE7qtXX1/IozWmua4o3zVfs6r9XUD7vsvBnoKLVdl+qOL6mFFVVERq672fI9h5jVhzFt1MzEfTsfUy+LT7u6r83WdNzTHmsrM4c0Ti3mrdeYafiUzEzE9cTaW0+2M7ODaqmmJm9ov1dTVsSvVVM8ZmWMu0y0vgZeqv8AZj8ZZsOrFy9f8aLTwmGf2di06dN7TfzRn8WmdNMTeb3+jlu707/njPH7y8u3gNvMAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAANA/dSpv+qf+b/62/vifpD7CrzlWDVRnOinDiuP2NWqKtPzi37IOOTR8/6U9HP+Yl0uv9CcWqJifa1PZv6CL7v5S/8Aobjbv9rUbv8A2I9X+fxm4cx6OeP9KNP/AFR+bpeH+g+LTEUx7Xi0W3Tg36oiO98o8l/9DMa9/wD+tRPb/u8b54/tA0T2Lqpqmd2md34tgw8Si19932qP0Hqver2nEz8sK27mein9D7fw7+b/AL3TGyJy0T9IIm1Ez2zO58R0z2h+gk4+n/aemI7Oiv8A2nj2Z/8Adv5r/Em4rQLJpp3x70fm37Zp/wB2/mv8Rs0/7r/Nf4i+vwb+AwAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAIShIAAIAAEgCEgAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAIShIAAIABIAAAAAAMWax6cLDrxK592imqqr6RF5BlHzMP2lXRTqzOXjDidGiaJnEvNV/ctEX1Rbsi03izNHtXAmK7V1ToteIw65mbzMXpiIvVF4nfF43SD2j5VHt3CqxNFNMzT/q5iu1UUzFcTMTE2tO6OP9DJT7by006oxapjd/y8S83jVExTa8xbfeN24H0R8zO+14w/1aacOaqMxXTTq3xNMTG6q0x9PN5sj+kdGJOJrwtFNGJiUxN5maqaej01RERv1dJG6PlxB9weKfaeFfTE1TVo120VxaN9tW73b6Z67dTHR7by/VVjaZim9V4q0x7kVzGu1r6ZibcAfRHgw/bGXqmKYrr1TM06eixIqiYte8TF4iNVN5nd70MXtP2nVg16acOKopo113njVFNMR9fen+T8wfUHy8H2rNeHhTTgxrxMavDim8zEU0V1RXXM/wAWmZ+sxDy5H2/XjdDV0FNNFc4cVb5mYnEomabfjaPx+QPvAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAhKEgAAgAEgAAAAAMeNhU4lFVFdN6aommqOMTFphkRVNo6r/IHzsP2XVE0TXncTE0TRNN4iItTfriO2b75+UdTz0fo5h001U01xETVE/vdHvReZ017vfj3u3hHzv7MP2phzETVFVETRFcauE3nri8XtTO5l/XsK0TGJeJi8WiZ3Xt533W4g8GB7BpoiimMxVpoow6bTEb9EVRE3+lTze0PYtcRh9FqrimKYm0xFXu0TTH/FTunVN/e/Cez69Gfw5mfetabdU7/2ez61RFuKuH7SwqoidUxqta8TffETv4ftR18QefMey5zGBhU4sxh100TExh9VNVVE0zpvwvePpDHhfo/h0X04k2mKqdM00zTaaMOm1p3TFsKPOX0MPO4dU2prmZte1pi0fO/V+KKc7hzTq1Ta8U9Uz709kcfwB4qfYkRo/wDU1ToiqL6Y1WmZnTFXXFO+2nfFqYYM57AvgYlOHiTNU6piJta84MYVvKL/AFfTj2hhXiJrtM/Kbdnb1dsecKVe1cCN84to3b5iY3zebW6+qL/TeD52U9k4leHMYkdHbEmqmq0TizeI9+apqqmK90xExV1dj2e0PZk42JqjE001UaK+O6qKqJj6Tqj+V8n0aZv/APlgHz8h7KpwK5q6Wat1cUxMR7uvEqrr85mmP5EPLlvYPRdDTTj3w6Jw5qvG+ejomKIj8bTP0+b7YAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAACEoSAACAASAAAAAADFGJM11UxTG6Im/1v9gUnJYVojootEREb56oiYj/5SmvKYdWnVTM6eq9VW759fX8+tkvVwjzL1fLzBhjI4V4no+qb9c9e7fPH9mOvgiPZ+DFv9V1fOflb6/sx5M96vl5q657sArGUw4nVo3/WbdUR1fSLI/UsPTNOjdVa++rfEdUdfV8upk11d2DXV3YBj/UsL4f5z/0+mnyVo9n4VMRGibxb3tVWrdFr36+rd9NzNrq4Qa6u7AL0xaLJY9dXdg11d2AZBj11d2DXV3YBkGPVV3YNdXdgGQY9dXdg1Vd2AZBj11d2Ea6u7AMox66u7CNc8IBlHmwszrmqKaZvTNpvTVG/8Y3suuruwDIMeqruwaqu7AMgx6qu7Brq7sAyDHrq7sGuruwDIMeuruwa6u7AMgx66u7Brq7sAyDHrq7sGuruwDIMeuruwa6u7AMgx66u7Brq7sAyDHrq7sGuruwDIMeuruwa6u7AMgx66u7Brq7sAyDHrq7sGuruwC6WPXV3YNdXdgGQY9dXdg11d2AXSx66u7Brq7sAyDHrq7sLUVXvuBYAAAB58P8AfsT+LR/aeh58P9+xP4tH9pErNXRFUWnjE+U3Y5y8TERebRExx65j7L4s1RHuxEz82LpMT4VuHbw/vVV6cGKbzef83+6tVF72qtMxb6fP81sOquZ30/53/wB3mTi0RNprpifrAMEZWLWnGrnfe8zEyiMpG6+PiVRFpm9V7zFuvy/OWTAx4mn36sOKt+6KomPkjDx411xVVhxTFtE3i87t9wUqyl5/3rFj+Umcrf8A5+JHyiq3H7skY0TXERVRptxi9+HWz6Y4A8leWmqbzmK43W92bRbf9/yWowJpq1dNVN+Np3cHp0xwNMcAeWrL3mZ6av6X3RvvuRGWt/CcSd8T+18+D16Y4GmOAMFWFe/vzvmmd++ItMboj8Do7TVMV756u2I+kM+mOBpjgDBiYUVTEz2fn9fNNVF6Ziauu++N1mbTHBGmOAMFWDeYm9rfL5xP9StWXvMT09ccYvun8Hq0xwNMcAeOnK2/hGJ2dvC32/OVcTI01WviVbrdvB7tMcDTHANPnZbITRVXV0u+qrVFr7t1t8T1s/6v1/66uYmLdb1aY4GmOAmnkw8rpq1dNXP1ns4R/ntehfTHA0xwFUF9McDTHAFBfTHA0xwBQX0xwNMcAUF9McDTHAFBfTHA0xwBQX0xwNMcAUF9McDTHAFBfTHA0xwBQX0xwNMcAUF9McDTHAFBfTHA0xwBQX0xwNMcAUFtMcITpjgCgvpjgaY4AoYfb9V9McFaeufr/VALiEgAAPPh/v2J/Fo/tPQ8+H+/Yn0o/tIlehAlVQ8tUVdNeI36d0apiLX6+rr/ABep56sOZxO3TbrvPXfq6weeaZ6KIv7vHXN+v+KYFFVOHVVqmqJjfNVczO6992ll6KvRHu7774iqbWv8/kmnDq0b76rTu1Ta/YDDk8aJq004kVTO/wDanq5Yv2ecfJ9B5svh1ddUTE370vUAAAAAAAhKASAACASIASIASIASIASIASIASIASIASIASIASIASIASIASIASIASIASpT11fX+qFlaeur6/1QC4gBIACnRReZtvm17TK4CnRxxnzk6OOM+crgKdHHGfOTo44z5yuAp0ccZ85OjjjPnK4CnRxxnzk6OOM+crgKdHHGfOTo44z5yuAp0ccZ85OjjjPnK4CnRxxnzk6OOM+crgKdHHGfOTo44z5yuAp0ccZ85OjjjPnK4CnRxxnzk6OOM+crgKdHHGfOTo44z5yuAp0ccZ85OjjjPnK4CnRxxnzk6OOM+crgKdHHGfOTo44z5yuAp0ccZ85OjjjPnK4CnRxxnzk6OOM+crgKdHHGfOTo44z5yuAp0ccZ85OjjjPnK4CnRxxnzk6OOM+crgKdHHGfOTo44z5yuAp0ccZ85OjjjPnK4CnRxxnzk6OOM+crgKdHHGfOTo44z5yuAp0ccZ85OjjjPnK4CnRxxnzk6OOM+crgKdHHGfOTo44z5yuAp0ccZ85TTTZYBAkAEJAAAAABAAkBAkBAkBAkBAkBAkBAkBAkBAkBAkBAkBAkBAkBAkBAkBAkBAkBAkBAkBAkBAkBAkBAkBAkBCQAAAAAAAAAQkAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAFMXEiimaqr2iLzaJmfKGLKZunGiZpiqJpm1VNUTFUT84B6AAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAY8TEtaIi8z1R/TIMgrri9tUXefAzmrFrwa8PRiU+9EXvFVF91cT+Ux2T8rTIel58hlYwcOKN0zvmqYid8z277y5ntCz/dwOSfubQs/3cDkn7g6qOVbQs/3cDkn7m0LP93A5J+4OqjlW0LP93A5J+5tCz/dwOSfuDqo5VtCz/dwOSfubQs/3cDkn7g6qOVbQs/3cDkn7m0LP93A5J+4OqjlW0LP93A5J+5tCz/dwOSfuDqo5VtCz/dwOSfubQs/3cDkn7g6qOVbQs/3cDkn7m0LP93A5J+4OqjlW0LP93A5J+6+0XP8AwcvyV+oHUhy3aLnvg5fkr9RtFz3wcvyV+oHUhy3aLnvg5fkr9RtFz3wcvyV+oHUhy3aLnvg5fkr9RtFz3wcvyV+oHUhy3aLnvg5fkr9RtFz3wcvyV+oHUhy3aLnvg5fkr9RtFz3wcvyV+oHUhy3aLnvg5fkr9RtFz3wcvyV+oHUhy3aLnvg5fkr9RtFz3wcvyV+oHUhy3aLnvg5fkr9RtFz3wcvyV+oHUhy3aLnvg5fkr9RtFz3wcvyV+oHUhy3aLnvg5fkr9RtFz3wcvyV+oHUhy3aLnvg5fkr9RtFz3wcvyV+oHUhy3aLnvg5fkr9RtFz3wcvyV+oHUhy3aLnvg5fkr9RtFz3wcvyV+oHUhy3aLnvg5fkr9RtFz3wcvyV+oHUhy3aLnvg5fkr9RtFz3wcvyV+oHUhy3aLnvg5fkr9RtFz3wcvyV+oHUhy3aLnvg5fkr9RtFz3wcvyV+oHUhy3aLnvg5fkr9RtFz3wcvyV+oHUhy3aLnvg5fkr9RtFz3wcvyV+oHUlK6Iq6+zfE9sOYbRc98HL8lfqNoue+Dl+Sv1A6hpjgxYGVow6q64vNdc3qqmbz8o+UR2R/XMuabRc98HL8lfqNoue+Dl+Sv1A1EAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAH//Z\n",
      "text/html": [
       "\n",
       "        <iframe\n",
       "            width=\"640\"\n",
       "            height=\"360\"\n",
       "            src=\"https://www.youtube.com/embed/mX6uMt_1-Lk\"\n",
       "            frameborder=\"0\"\n",
       "            allowfullscreen\n",
       "            \n",
       "        ></iframe>\n",
       "        "
      ],
      "text/plain": [
       "<IPython.lib.display.YouTubeVideo at 0x11fd228e0>"
      ]
     },
     "execution_count": 3,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "YouTubeVideo('mX6uMt_1-Lk', width=640, height=360)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "> **Ex. 3.1.2:** What is the missing value sign in this dataset? Replace all missing values with NA's understood by pandas. Then proceed to drop all rows containing any missing values with the `dropna` method. Store this new dataframe as `df_census_new`. How many rows are removed in this operation?\n",
    "> \n",
    "> _Hint:_ NaN from NumPy might be useful. What you should discover here is that pandas indeed has native methods for dealing with missings."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "deletable": false,
    "nbgrader": {
     "cell_type": "code",
     "checksum": "c7938d5025bfb5bed9ec0e0077258cec",
     "grade": false,
     "grade_id": "cell-9fc9645db3d798e9",
     "locked": false,
     "schema_version": 3,
     "solution": true,
     "task": false
    }
   },
   "outputs": [],
   "source": [
    "from numpy import NaN\n",
    "\n",
    "# YOUR CODE HERE\n",
    "raise NotImplementedError()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "##  Duplicated Data\n",
    "\n",
    "Watch the video below about duplicated data and how to handle such observations."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 4,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "image/jpeg": "/9j/4AAQSkZJRgABAQAAAQABAAD/2wCEABALDBoYFhgYGRodHRweGiomIiIhISsnMR8oNCsyMC4qLS02QlBFQDpNOSstRWFFS1NWW1xbNUFlbWRYbFdZW1cBERISGRYZLxsaL2A2NTZfXF1XV1dXV1dXV1djV1dXY1lXV1dXXVdXV1dXV1dXV1dXV1dXV1ddV1dXV11dV1dXV//AABEIAWgB4AMBIgACEQEDEQH/xAAbAAEAAQUBAAAAAAAAAAAAAAAAAgEDBQYHBP/EAEkQAQABAgMFBQUDBwkGBwAAAAABAhEDBBIXITFRkkFTYZHSBRMiUtEycYEGBxRCocHwFjNUcoKDk7HiI2Jko8LDFSRDRGPh8f/EABgBAQEBAQEAAAAAAAAAAAAAAAABAgME/8QAHREBAQACAgMBAAAAAAAAAAAAAAECESExAxJhE//aAAwDAQACEQMRAD8A5+AAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAANw2cZ3vct1V+g2cZ3vct1V+gGnjcNnGd73LdVfoNnGd73LdVfoBp43DZxne9y3VX6DZxne9y3VX6AaeNw2cZ3vct1V+g2cZ3vct1V+gGnjcNnGd73LdVfoNnGd73LdVfoBp43DZxne9y3VX6DZxne9y3VX6AaeNw2cZ3vct1V+g2cZ3vct1V+gGnjcNnGd73LdVfoNnGd73LdVfoBp43DZxne9y3VX6DZxne9y3VX6AaeNw2cZ3vct1V+g2cZ3vct1V+gGnjcNnGd73LdVfoNnGd73LdVfoBp43DZxne9y3VX6DZxne9y3VX6AaeNw2cZ3vct1V+g2cZ3vct1V+gGnjcNnGd73LdVfoNnGd73LdVfoBp43DZxne9y3VX6DZxne9y3VX6AaeNw2cZ3vct1V+g2cZ3vct1V+gGnjcNnGd73LdVfoNnGd73LdVfoBp43DZxne9y3VX6DZxne9y3VX6AaeNw2cZ3vct1V+g2cZ3vct1V+gGnjcNnGd73LdVfoNnGd73LdVfoBp43DZxne9y3VX6DZxne9y3VX6AaeNw2cZ3vct1V+g2cZ3vct1V+gGnjcNnGd73LdVfoNnGd73LdVfoBp43DZxne9y3VX6DZxne9y3VX6AaeNw2cZ3vct1V+hh/b/5N43s/wB176rDq95q06Jqm2m173iPmgGHAAAAAAAAAiAdz9p533GHExETVVXFNN72iZ7Zt2LOQz2LONVgY9NEVxRqjRe1r2tN+3g9maytONRoqvxiYmJtNMxwmJ5rOR9m0YEzVFVddUxbVXN5iL3tHhcHtAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAc/wDzqf8Asv73/tugNB/Ofh6qshTExEz721/7sHPReoy81TaJi+nV2mHlqqtVpj4ZtPHlM/ukFkXJwZ0RX2TNkpy0xVVTem9N+fZF57PAFlLRNr2ezL5PVHwzeZ7VyfZVfGZXTXqxo9uPlJpteOMcbPHVTaZjlKJYoyfsrJU1014tfCmJ0xzm17/dDGM97KrirK10Xi8TO774v+5nK8OnikuXLsQDTkAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAND/OfhVVfoWnsnE7bb/gtbyb40P86GNNMZOI3TM4kxMdltEfvBodM178Td8WqJmbb+e78UsOcWLTERaub9lptExbymdyFOPbD0WnjM8eN7cY/CFcLNTTEREXjTaYmePG0+E7wUpiuaIiIjTVVbsi88Yj9ququcSqbRNVp1bo4WtP7LmDmpopppiJ3V6uPHw/YUZrTXNcRvmq/Pde9uAMt7HpmMKKvi+1PCzM1V3oiYiYnwYv2RjROBMcIi/Hs334/iy2Xrj5ondw3OmPTc0xfte8YMzbl9q3bu3tbrpmJmJ4xO9tPtHNU00165i80TFNM/rS1fEqvVM85mWKmXaeBl6q76eztlfw6sTL1/1otPKYXvZ2LTp03iJifNT2hi0zppibzqv9znu707/njPH7y8u3gNvMAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAANA/OlTf9C/vf8Att/YT8ofYVWcqwKqMf3U4UVx9jXqirT4xb7IOOTR/G893P8AES6ZX+ROLVExOcp323/o8X3f2k/5G427/wA5Run+jx6v487hzDR/FpNPjH7XS8P8h8WmmKYzkWi26cC/CIj5uUR5J/yMxuP6ZRO++/LxvnnPxA0b2JiW10z9me3xZuPd0ReJi/hDN0/kLVq1VZuJ3W3YNv8AqejD/I23/rx/h/6m8bDbnvtyJ95Tf5fLf/8AjGxHi6X7Q/IH39UT+labf/Ff/qePZn/xn/J/1JwVoOnxj9qtMb43xx8W/bNP+M/5P+oj82f/ABn/ACf9SX4N/AQAAAAAAAAAAAAUVAAAFFQAAAAAAAAAAAAAAAAAAAAAAAAAAAAAABQAAAAFQUVAAAAAAEcSuKaZqqm0RF5nlEAkLVGYoqqimKomZo1xHOnmugDz4uewqKtFeJTTVa9pm1otVN/Kiuf7Mr8TcFRbx8enDoqxK6opppi8zPYjls1h40TVh101xE2m0/ZnlMdk743AvCGLiU0U1V1TammJmZnsiN8yYeLTXF6aoqiJtNpvaeQJiGJiU0xE1TERMxEXm15mbRHmmALOZzVGFFM1zbVXFNO6ZvM+EeETPhETKFGfwasOvFjEpmiiL1VdlMaYrvP9mYn8QekeKv2rgRa+JG+qKbWndM1RTv5fFVTH4vaAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAACgAAAKgAAAAAAAPPnqJqwMammLzVh1REc5mJs9CkyDW6/Z2Ph2mn3uJfLYdE3qiJonXGqI0zTM2i82vv4XUyuSzdsOqr30V0xhRGrFmYj4q4r1RqmJ+Gab3v+LZYmJAafX7LzMzNVOFjRV7qmJmvEiqqcSMDM0zMTqn9bEo5b5vzerP4OPhU4uLqxYv7yn+dm2+aIw4iL7rzffFpi8tmKoiYmJi8T2SDXsLDnEyGZow5qxMT3lUTRNVUzTPw1RRE1z2UzHbYxcLMzjTmacHEpirEiJw4qp1TTGDiUxVVaq32qqd15+zE/dn8OmmmLUxERHZG6Epm3EGo04GYxKceiIxqsSmNFcziXptOUp+C01W1e8qib27b3ZDDyuNGLRNdGNVRrq0xRiadM6qbTVGqLxaJ57omLb9+e3ReeHMiYnhvBq2JkcxXViTXh42n3uFXFMYtXGMaZq0/HP6sx8sbotG6E8LJZrTje8qxrzXE2i9pnVVvj/a3mLTG6NG7Tu3TDZlKaonhMTvtu5gxGdormMlXXRaYmaa6b6tFdeFVTE6u34p03/wB54fZns7GiiMDEw5porxMOrEm8fZw8DCjTu5102t2xFTZgGq5/I4sTmPgmPexi4dExafixMTDiid33TPhZtQqAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAACgAAAKgAAAAAAALWZw5rw8SiJtNVExE8rxa66Axtfs+vVXpxZiKp3b5vT8NMdnHh+HYYuQxasKqj30zNUzebzG6Yq3bp7Kqr/AHREMiqDwTksSaao97VEzVxiZ4WmJtym09m6JiFKMnixrmcWZmqI7ZiOzVER2XtVaeMX8GQAYuMhi00/DiTM3vaJqjf8M7t/OKuPz772S/QMSaKaZxJmL/FE1VT8vb27oq3TuvV4MkAxM5DGq1U1Yk2miIvqnjp0zu7eETv5yuYmRxZiqIxbXmd+qqLRMTui3Krff8GSAYyfZ+J8VsSYve0RVVERM1zVM2/Hn4eL34OFp1c6qpmfHsj9kRH4LgAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAACgAAAKgAAAAACioALePTM0VxEzEzTMRNNrxu7L9oLgxmHh5jd+rwvaYn9b/emr9WZ7ZRpjM1VTEzNMUzG+1PHTRN4575rv2cAZUeCK8xNGFVptNUXqjd8F6qZtvtwp1R96mBGYmY1zMRomOFPHfvmY4Tw3REwD3l2Kows1TE2qq3xF76JmLU4cbuy+6vwevGmuKd0RNeid/CNVt14vwuD1XLsVhU5iIq3z2zEVaZmb1VbrxuvEaLdn7pYOLixVVOJE6I3fZvfdFvs3mZ3zfsBk7l2Krqx9c10UzNNp0xui94i02m03vxv+HjSJx6rzNNW7FptbTTuiudU8d8aYjjxuDLXgvHNjsP3urfFWn3lV5maZmY3W7fs8fHh4rNEZmbVTeJ01xpvRMXtTNM1THZfVG7fw3cQZe8F2Licxbhv93V8sWq36b2mb9nC0cfui/gRPvKbatPuovqvxv8PHttqv+APcMXNOb0/a32jsom26iZtw33muN/ZEK+6zMTeKpmqJq+1p0zeqi26N9ojV4/eDJjG0xmNdE1apoib2vREzuqvFVt3G1rbubJAAAAAAAAAAAAAAAAAAAAAAAAAAAAAoAAACoAAAAAAACirz0RE42Jff8FH/AFA9CiFcREXimJ3x2eK3VXFonRxiZtMb+MRbd94L6rz0VxN40W3cvv4+X7VZtv3cIif48gXh5aMWif1Zjxq3crf5mHi0VTupqj74mOyPqD1DxfpNFrzRXG+32f45wrXmKKZndMxEcYi8T4XB7B5Pf0b/AIZ3X7PGr0/thSMxR8lcf2fv+gPaosYdqr7uE/uif3paI5Aui1ojkaI5Aui1ojkrojkC6LWiORojkC6LWiORojkC6LWiORojkC6LWiORojkC6LWiORojkC6LWiORojkC6LWiORojkC6LWiORojkC6LWiORojkC6LWiORojkC6LWiORojkC6LWiORojkC6LWiORojkC6LWiORojkC6LWiORojkC4LeiORojkC4LeiORojkC6LWiORojkC6LWiOSuF2/eC4AAAA8+H/PYn9Sj/ADqeh58P+exP6lH+dSxKvzNuKk1RHGYUroiqLTwW4y1McL+O/jw+iKu3ibxEsfj+2crh1TRiZnBoqpnfTViREx+D20YMU8P44/WXJ/y5xaqs/iRNcVREzaIiPg32tP4RE/iDo/8AKDJf0vL/AOLSrPt/J9uawP8AEhx7K5aMSKr4tGHMWtrm0Txvv/COztZjBx8bXrnN5a97fFMTb4rzMRa3/wBRAOkfygyX9Ly/+LSr/wCP5L+l5f8AxKXL83i4k4eJfGy9UaYvFEcPsxutFtU8OPC6eDjV7tOZwI/2f61rz9i9NXHfu3TPy9gOnYftzKVVU005rAmaptERiRvmeEQyOmXJsvViV5nKxOJhYlszhTbCiN0ap3zblfl2w64CGmfA0z4JgIaZNM+CYCGmfAtPgmAhafAtPgmAhafAtPgmAhafAtPgmAhafAtPgmAhafAtPgmAhafAtPgmAhafAtPgmAhafAtPgmAhafAtPgmAhafAtPgmAhafAtPgmAhafAtPgmAhafAtPgmAhafAtPgmAhafAtPgmAhafAtPgkAjafAtPgkAjafAtPgmAhaSiOP3/uTRp41ff+6ASAAAAefD/nsT+pR/nU9C1GFauqqJ4xEcOV/qqVdEbTzjyLTzjyRUmje3vyTjNZqvE/SacO8zu91M77xxm++d8N3tPOPJSaZ8PIHOcH833vPsZymf7mY7L81ct+RUYdXvP0qirTePiwKpjfTPGNX3+To1p5x5KaZ8N3gDS6vyMpxaKqKcTBiqYi9XuZ4W0xbfum9KVP5C1Ra+LgVfDpnVgTv+zv8Atbp+Hs5zxbnaeceRaeceQMD7A/JycnjVYmrCmKqNNqMOae2Jvvqnl/k2BG0848i0848gSEbTzjyLTzjyBIRtPOPItPOPIEhG0848i0848gSEbTzjyLTzjyBIRtPOPItPOPIEhG0848i0848gSEbTzjyLTzjyBIRtPOPItPOPIEhG0848i0848gSEbTzjyLTzjyBIRtPOPItPOPIEhG0848i0848gSEbTzjyLTzjyBIRtPOPItPOPIEhG0848i0848gSEbTzjyLTzjyBIRtPOPItPOPIEhG0848i0848gVFLTzjyLTzjyBUUtPOPItPOPIEhG0848i0848gSRp41ff+6C0848imOPiCQAAoqAAAAAAAAAAAAApdV5sWmr3mqmnjERe/jO6wPSKKgAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAoAAACoAAAAAKCoAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAACgAAAKgAAAAAAAAAAAAAKKgKKgAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAACgAAAKgAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAoAAACoAAAAAAhi4kUU1Vze1MXm0TP7IWspm6caKppiqJpm1VNUWmmeO+AegAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAFAAAAVAABbxMS1oiLzPCP8AOQS1Re14vySWZw6N+/fe9774W8DOasWvBrp0V0/FEXvFdF7RVE/smOyfC0yHpefIZWMHDijdM3mapiPtTPbN7y5ntCz3LB6J+ptCz3LB6J+oOqjlW0LPcsHon6m0LPcsHon6g6qOVbQs9yweifqbQs9yweifqDqo5VtCz3LB6J+ptCz3LB6J+oOqjlW0LPcsHon6m0LPcsHon6g6qOVbQs9yweifqbQs9yweifqDqo5VtCz3LB6J+ptCz3LB6J+oOqjlW0LPcsHon6m0LPcsHon6g6qOVbQs9yweifqbQs9yweifqDqo5VtCz3LB6J+ptCz3LB6J+oOqjlW0LPcsHon6m0LPcsHon6g6qOVbQs9yweifqbQs9yweifqDqo5XH5w89ywJ/sT9Utoud+TL9FXqB1Ict2i535Mv0Veo2i535Mv0VeoHUhy3aLnfky/RV6jaLnfky/RV6gdSHLdoud+TL9FXqNoud+TL9FXqB1Ict2i535Mv0Veo2i535Mv0VeoHUhy3aLnfky/RV6jaLnfky/RV6gdSHLdoud+TL9FXqNoud+TL9FXqB1Ict2i535Mv0Veo2i535Mv0VeoHUhy3aLnfky/RV6jaLnfky/RV6gdSHLdoud+TL9FXqNoud+TL9FXqB1Ict2i535Mv0Veo2i535Mv0VeoHUhy3aLnfky/RV6jaLnfky/RV6gdSHLdoud+TL9FXqNoud+TL9FXqB1Ecu2i535Mv0Veo2i535Mv0VeoHURy7aLnfky/RV6jaLnfky/RV6gdSHLdoud+TL9FXqNoud+TL9FXqB1JCuiKrX7JvE8nMNoud+TL9FXqNoud+TL9FXqB1DTHG0XlawMrTh1YlcXmrEqvVVM3meUeER2R++Zc02i535Mv0Veo2i535Mv0VeoGogAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAA/9k=\n",
      "text/html": [
       "\n",
       "        <iframe\n",
       "            width=\"640\"\n",
       "            height=\"360\"\n",
       "            src=\"https://www.youtube.com/embed/aQrqhJnHIqQ\"\n",
       "            frameborder=\"0\"\n",
       "            allowfullscreen\n",
       "            \n",
       "        ></iframe>\n",
       "        "
      ],
      "text/plain": [
       "<IPython.lib.display.YouTubeVideo at 0x11fd22df0>"
      ]
     },
     "execution_count": 4,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "YouTubeVideo('aQrqhJnHIqQ', width=640, height=360)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "> **Ex. 3.1.3:** Determine whether or not duplicated rows is a problem in the NOAA weather data and the US census data. You should come up with arguments from the structure of the rows.\n",
    ">\n",
    "> *Hint:* Just use the `load_weather()` function from the last module (provided below) to load weather data from 1863 only."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "def load_weather(year):\n",
    "    \n",
    "    url = f\"ftp://ftp.ncdc.noaa.gov/pub/data/ghcn/daily/by_year/{year}.csv.gz\"\n",
    "\n",
    "    df_weather = pd.read_csv(url, \n",
    "                             header=None)        \n",
    "\n",
    "    df_weather = df_weather.iloc[:,:4] \n",
    "    \n",
    "    column_names = ['station', 'datetime', 'obs_type', 'obs_value']\n",
    "    df_weather.columns = column_names \n",
    "    \n",
    "    df_weather['obs_value'] = df_weather['obs_value'] / 10 \n",
    "    \n",
    "    selection_tmax = df_weather.obs_type == 'TMAX'\n",
    "    df_select = df_weather.loc[selection_tmax]\n",
    "    \n",
    "    df_sorted = df_select.sort_values(by=['station', 'datetime'])\n",
    "    df_reset = df_sorted.reset_index(drop=True)\n",
    "    df_out = df_reset.copy()\n",
    "            \n",
    "    return df_out"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "deletable": false,
    "nbgrader": {
     "cell_type": "code",
     "checksum": "6185914cc7355abcbfec0ebbd415ec7b",
     "grade": false,
     "grade_id": "cell-2ad5ad96bbccbae8",
     "locked": false,
     "schema_version": 3,
     "solution": true,
     "task": false
    }
   },
   "outputs": [],
   "source": [
    "# YOUR CODE HERE\n",
    "raise NotImplementedError()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "# Part 2: Combining Data Sets\n",
    "\n",
    "Below we hear about how different datasets can be combined into one, by merging on overlapping information that exists in both datasets.\n",
    "\n",
    "**Note:** In the video, we are only dealing with one-to-one joins. This is a type of merge where there is only one row with a given merge key in each data frame. However, you may encounter situations where there are multiple rows that share the value of a merge key. In this situation, you may perform a one-to-many join or a many-to-many join that forms a Cartesian product of your rows."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 5,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "image/jpeg": "/9j/4AAQSkZJRgABAQAAAQABAAD/2wCEABALDBoYFhsaGRoeHRwfIyomJCIiIzAoMSMnLio6MC0uLTFAPFBCNDhLPzMwRmFHS1NWW1xbN0dlcmRYbVBZW1cBERISGRUZLxsbMFc/OEJXXV5fXV1XWF1XV19cV1dXV2JjXldXV11XV1dXV11XV1dXV1ddV1dXWldXXWNXV1dXV//AABEIAWgB4AMBIgACEQEDEQH/xAAbAAEAAgMBAQAAAAAAAAAAAAAAAQYEBQcDAv/EAEcQAQABAgMFBAgBCQQJBQAAAAABAhEDBCESFzFRkkFT0dIFEyJSYXGBkaEGBxQyQoKxwfAjZKPiFRYzRGJyg8LhJDRjorL/xAAZAQEAAwEBAAAAAAAAAAAAAAAAAgMEAQX/xAAhEQEAAgIDAQEAAwEAAAAAAAAAAQIDEQQSITETFEFRBf/aAAwDAQACEQMRAD8A5+AAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAALfu5zve5fqr8id3Gd73L9VfkBTxcN3Gd73L9VfkN3Gd73L9VfkBTxcN3Gd73L9VfkN3Gd73L9VfkBTxcN3Gd73L9VfkN3Gd73L9VfkBTxcN3Gd73L9VfkN3Gd73L9VfkBTxcN3Gd73L9VfkN3Gd73L9VfkBTxcN3Gd73L9VfkN3Gd73L9VfkBTxcN3Gd73L9VfkN3Gd73L9VfkBTxcN3Gd73L9VfkN3Gd73L9VfkBTxcN3Gd73L9VfkN3Gd73L9VfkBTxcN3Gd73L9VfkN3Gd73L9VfkBTxcN3Gd73L9VfkN3Gd73L9VfkBTxcN3Gd73L9VfkN3Gd73L9VfkBTxcN3Gd73L9VfkN3Gd73L9VfkBTxcN3Gd73L9VfkN3Gd73L9VfkBTxcN3Gd73L9VfkN3Gd73L9VfkBTxcN3Gd73L9VfkN3Gd73L9VfkBTxcN3Gd73L9VfkN3Gd73L9VfkBTxcN3Gd73L9VfkN3Gd73L9VfkBTxcN3Gd73L9VfkN3Gd73L9VfkBTxcN3Gd73L9VfkN3Gd73L9VfkBTxcN3Gd73L9VfkN3Gd73L9VfkBTxcN3Gd73L9VfkN3Gd73L9VfkBTxcN3Gd73L9VfkN3Gd73L9VfkBTxcN3Gd73L9Vfkaf0/+TeN6P9X66rDq9ZtW2JmbbNr3vEc4BpwAAAAAAAAIgHc/Sed9RhxMRE1VVRTTfhee2fg8chnsWcacDHpoivZ242L2te1pv2szNZWnGo2ar8YmJibTTMcJiebxyPo2jAmaoqrrqmLbVc3mIve0couDNAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAc/wDzqf7n/wBX/sdAUH85+HtVZKmJiJn1tr/uA56PajLzVNomL7O12+HEw8tVVtWmPZm34TP8pB4j0nBnYivsnR9Tlp2qqdqm9N+fZF57AeJEPfDy01axMTF7aNjheiK6o0mw7EbaiqmY4xMfNDc4uUxMGmdrWI7YarHiIqnZ4fw5u6JjTzbP0Vkqa6asSvhTfZjnNr/aGsb70VXFWVqpvF4mfxi/8kLT4swxE29diASVAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAACh/nPwqqv0PZ7JxO22s7FrfZfFD/OhjTTGUiNJmcSb8rbMfzBQ6ZxNcTSdraiZm2vPR9UTixETERaub9lptE3j7TOj4pzFsPYtPGZ48b246fCE4WammIiI0taYmePG0/CdQRTFc0RERGzVVbs1njEfinaxJxKpteu030jha0/gYWamimKYidKtrjx+BRmtmuaojWar89L3twBlehqJmudPZ7Z+PZ/NYsGqaJ4VW+NrK/6JzkUzOHa0VVXp7bTyWGqu9NN5iJmU6pwZmuaptabcrafdTMSJ2pvxvquVeNEYczNUaTrPLmpuJVeqZ5zMuWLPvAy9Vf6vZ2y9sOrEy9f/ADRaeUw9/R2LTs7N7Tf7o9IYtM7NMTeb3+Src70u/OsY+8T67eAmzAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAACgfnSpv8Aof8A1f8AsX9pPyh9BVZyrBqox/VThxXH6m3tRVs/GLfqg45NHx/in1c/1E+Dpdf5E4tUTE5ynW2vqIvp+8+/9TcbT/1lGn93jz/19ZuHMfV/1aUbPxj8XS8P8h8WmIpjORaLaTg34REe/wDCPs+/9TMbj+mUT265eNZ5z7QOc5HDmcWi3ZN/pCz4dcTFqtLN7R+Q1W1VVVmomZiI0wrWiP3mRH5HW/3j/D/zJ11BtSvTePFOFs06xV2wr9nTM9+Qfropj9J2bTf/AGd7/wD2Ye7P++f4X+YmYN7c/wBn4vqmnWNY/Fft2n98/wAL/MR+bPX/AN5/hf5kZ1/Qv4hLgAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAhKEggSAgSAhIAgSAhKEgAxvSHrP0fG9V/tfV1bH/Nszs/jYGQlVMLNYWXmcXKbM4VGFRVmLXnXbiJmr/wCTZ25m+s2i/Y+6/TeapmrajDpqpwtuaKrXm+DNd6YvebVWp4W0qBaBWvSHpHMURXg1TFVU4FWLtxTNMbEYdW1EWm8TFex23tV8EVemsemrGn+z2MG9VVM61xRRVTebXvrRtzef+H4gsw0foz0pmMXDx666I2sKm2xEccS01W6Zw/rMsXBzNeYx8tfFoxaKcW8VU0+zVPqZqtxnWmfteO2AWYUb05iYlOJ6RppmvZxvZvHCj1OBRiz8tqmao+jZT6XzEVYlNM4UbERTFNURNVPtURTVMbV5idqZ7OMW+IWcVnH9MZinEx6aKqKvUxXeNmOyinZmfavxqmZ0iLRx7Zzqav0nJYk43q8S23NM02mJtfZnSZi/y7QbgVbDzmLlqKMLBooppnA9ffZ7KcKduJ11q2/V68qpTj+nMaIth4mDXF5ti6U0TVsRVFEztW4z2a2046gtA1eQz9VeYxMKuqmZi8xFERMUxFVoiqb3irWNJiL8Y0iW0AAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAABCUJAAAAAAAAAAAABFhICCyQECQEWLJAYdPozCjFjFiKtqKqq49qZiKqomKpiL2vMTP3ZdkgMWvIYdU4k1RMziU7FV6qp9m1pimL2p+lrsimmIiIjhD6AQkAAAAAAAAAB44uZoom1VURNr/AEfM53Ctfbi2sdvZMRP8Y+4MgfNNUTETE3idYlIJAAHnXjU0zaaoiXzVmcOLXriL27efAHsISAAAAAAAAAAAAAACEoSAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAA8K8WqappoteONU8Kfh8Z+H/i/uxsvNsLa4TN5nttMzr9v5AYmHVETVOJGka7VMbMR+E/ix8rjTjXi0YduzZvMx2VRfSO3S13nmMea8GYmq/t03nZmnTjEa8eFnllZtj4XxmqJ+WzM/xiGPLyZpnri19WVpusy2UxiURpO3EdkxEVfSeH0tHze2HXFURVGsSxMtmKqq7TVExyiiY/F65b9bFjsivT60xVP4zM/VsVshh5vO+rnZpjartf4RHOfBltFXXriVT79d/3ZmI/CIY+ZnnDj3X7KzHTtPr1qzeJOtXq5+dGn/6fEekJr0pwqKNP2o2rxzp4afP7c8P1m3TMTVFV6qY0iY0mYiePN7Y3Gie3a/jGvj9Hlfy89Y1NvrR+dWbh+kMSJ9qKa4+EWn+Np/BssLEiumKqZvE8JVzDx5mu21TblETdtvRM6YkdkVafC8RM+P1beFyclr/nk98VZaREbhsEJQ9RQJQkBCQBCQAAAAAAEJQkAAAAEJQkAAAAAAAAAAAAAAAAAAAAAAAAAABj64czpM0TN9IvszPHTlM6/VkAMTM4uFiUTR6ynXhszeYmNYmI7ZuxMjTsV7eN7ExFqYnSJvxqvw15cY+rY4mPTTNqptPyn5Pmc3h3iNrWdO3jx/nH3V2xVtaLzHsO9piNInNRP+z/ALSfhOn1nhH9cXpg4ezTaZvM6zPOZ4vsWODV53LVUVTXTEzRVN5tF5pnt05T/Xw2qFObDXNXrZKtprO4V2vFoqjZ26deUxe/iidqJirFp9XEcNrSKpnS8ctL6Trrw4LFNUROsxEo9ZT70fdhj/m1iJjst/af8aPDr25tR7c8qdfvPZ9W3yWX9XRaZvVM3qn4/wBWj6PeJ5DRxuJXB7HsoXyTZKEjYrQkAAAAAAAAAAAAAAAAAAAAAQAAkAAAAAAAAAAAAAAAAAAAAAQw83ntidiiImrtmeFPL5z8P/F8xX9rSqqeMzVM/O/8uH0YubnthpHX7K3FSLT696s3iR7U1Uac6dIj73/Fk5TMVY99IwpjsteZ+MT2dvZf5NRTibUREze9UX9mY049vyZ2VqmMfCt2zVE/LZmf4xDFxuXljJFLzvay+Outw2c+sp1vFccrWn6Twn5afN60VxVETE3ieDEy2ZqqxLTVExyiiqPxl7Zb9bFjsivT60xVP4zL2mZ7vLFxrTsxG1VPZyjnM9kPVjZfhXXNrzVVefhTMxH4QD4xcvVXMVVUYczEWj2p0/et/Jg15vDq9mjCidnjtTaIvyte8afLRkZnMzVgV/qxMzTT7FW1aKpiJ17J4tfiRaaJjnb6THD72+zz+ZyrYpilPsrseOLeyzsH0hsREVYcRTHbRMzb9238LtlRVExExMTE6xMdsK7h48zVaYpt8Kr3+ja+iavZrp7KatPheL2+9/uhw+VfJbpk+u5McVjcM8B6agAAAAAAAAAAAAABCQAAAAAAAAAAAAAAAAAAAAAAAAAAAAAABDVZzKzRVVVTEzRVN5trNMzx05TOrbIU5sNc1etkq2ms7hXa66ao2Yqi/ZbWbxw0ZmQjZq28f+zmItTFWka8Zvw15cY7eLbDPg4NMNu29ynfLNo08JzVM6Yf9pP/AAzp9Z4R/XF6YGHs02mbzM3mecy9BuVDFvGHM01fqVTMxM8ImeMT9b/e3zykSDwx8tTVh1Uxam8cYjhMaxP0lo6atuuImLbOvznhennTx18JbarDwqcSYmjDtp+zF/nHNlYuDTXFq6aao5VREsnJ4sZ/fkrKX6tFs0xMae1PCIjWfk2+Qy84dHtfrVTtT8OyI+kRD1wsvh0fqUU0392mIeiPF4cYPZncu3ydvEgNqpCQAAAAAAAAAAAEJAAAAAEJBCQAAAeOaxZowsSuLXppqmL/AAi72Aayv0pVTV7VGzFu2be1F9rWf2dLRPbP3euH6R2qqKdiY25qiLzHCmrZnTnfs5fZmgNdX6WiIvNGmltecxa/KNUz6UiNm9E+1No1i/Zf+PPsn4X2D4rwaapiaqYmY4TPZ2/yj7Awo9JTNGHXTRExVMxbai82pmqIp5zpw0RX6YpiLxTeJtbWNYnhVMdlNp4tjMXi0opoiOERHy+wMHF9KRTMexVrRFfGL630iP2p0tp2zHPTzq9LWibRTwvF6rRa0Txi/CJ46Q2gDDj0hTNGJVpeiZiY2o7LazP17L/fR4f6Yp7KJnWI4xHZM9vC9tL8bxwbKwDFnNTGJNNotE89f2fMUZ2Jo2rRps8Zt+tbWeT3nBombzTH2PUUe7T9gY8Z+Jt7Nr24zw+cfXTm88XP1U+rnZptVFU1a8LVU0x8taovPZaWX+j0e5T9n3TTERaItHwBh0+kYmiqvZtFM0061cZqtrf3fajV4YPpaaoo9mmJnY2pmq0RtRE/z05/a+0Aar/TF5iaYiYvr8Y2a6tJ/djs58WywcWK6YqpmJiYidH2kAAAAAAAAAAHzNETN5iLvoAAAAAAAAAAAAAAAAABCQAAAAAAAAAAAAAAAQCQQCRACRACRACRACQAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAQlCQAAAAAAAAAAAAAAEJQCQQAAAAAAAACQAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAQkAAAAAAAAAQJAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAB8YuJFFM1VXtEXm0X/B5ZTN040TNMVRNM2qpqi0xPHWAZAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAPPExLWiIvM8I/jIPQfO3HONGPgZzaxa8GunYrp9qIveK6L2iqJ/CY7J+FpkMlj5DKxg4cUaTOs1TEfrTPbN5mXM94We5YPRPibws9yweifEHVRyreFnuWD0T4m8LPcsHonxB1Ucq3hZ7lg9E+JvCz3LB6J8QdVHKt4We5YPRPibws9yweifEHVRyreFnuWD0T4m8LPcsHonxB1Ucq3hZ7lg9E+JvCz3LB6J8QdVHKt4We5YPRPibws9yweifEHVRyreFnuWD0T4m8LPcsHonxB1Ucq3hZ7lg9E+JvCz3LB6J8QdVHKt4We5YPRPibws9yweifEHVRyreFnuWD0T4m8LPcsHonxB1Ucq3hZ7lg9E+JvCz3LB6J8QdVHKt4We5YPRPibws9yweifEHVRyreFnuWD0T4m8LPcsHonxB1Ucq3hZ7lg9E+JvCz3LB6J8QdVHKt4We5YPRPibws9yweifEHVRyreFnuWD0T4m8LPcsHonxB1Ucq3hZ7lg9E+JvCz3LB6J8QdVHLY/OLnvcwJ/cq8xvFzvuZfoq8wOpDlu8XO+5l+irzG8XO+5l+irzA6kOW7xc77mX6KvMbxc77mX6KvMDqQ5bvFzvuZfoq8xvFzvuZfoq8wOpDlu8XO+5l+irzG8XO+5l+irzA6kOW7xc77mX6KvMbxc77mX6KvMDqQ5bvFzvuZfoq8xvFzvuZfoq8wOpDlu8XO+5l+irzG8XO+5l+irzA6kOW7xc77mX6KvMbxc77mX6KvMDqQ5bvFzvuZfoq8xvFzvuZfoq8wOpPiuiKrX7NYnk5hvFzvuZfoq8xvFzvuZfoq8wOobMXvaHlgZWnDqrri81VzeqqZvPwj4RHZH85lzTeLnfcy/RV5jeLnfcy/RV5gVEAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAH/2Q==\n",
      "text/html": [
       "\n",
       "        <iframe\n",
       "            width=\"640\"\n",
       "            height=\"360\"\n",
       "            src=\"https://www.youtube.com/embed/y2YL_cMp8v0\"\n",
       "            frameborder=\"0\"\n",
       "            allowfullscreen\n",
       "            \n",
       "        ></iframe>\n",
       "        "
      ],
      "text/plain": [
       "<IPython.lib.display.YouTubeVideo at 0x11fd22b50>"
      ]
     },
     "execution_count": 5,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "YouTubeVideo('y2YL_cMp8v0', width=640, height=360)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "We now continue with structuring weather data. Use the function `load_weather()` for fetching and structuring weather data that you also used above."
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "> **Ex. 3.2.1:** Get the processed data from years 1870-1875 as a list of DataFrames. Generate a variable that denotes the year that the observations belong to. Convert the list into a single DataFrame by concatenating vertically. "
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "deletable": false,
    "nbgrader": {
     "cell_type": "code",
     "checksum": "09f387f440027d3137c359e5872f963a",
     "grade": false,
     "grade_id": "cell-cff6fafb47a33d1c",
     "locked": false,
     "schema_version": 3,
     "solution": true,
     "task": false
    }
   },
   "outputs": [],
   "source": [
    "# YOUR CODE HERE\n",
    "raise NotImplementedError()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "# Part 3: Split-Apply-Combine\n",
    "\n",
    "Often we need to process information for a given individual, point in time etc. Instead of writing a loop over all the subsets of the data, we can use a more clever approach. Below we introduce the split-apply-combine framework and show how we can leverage it in pandas"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "YouTubeVideo('pZinSHCRgbA', width=640, height=360)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "*Note:* The Split-Apply-Combine method uses `.groupby()`. As indicated in the video, `.groupby()` is a method of pandas dataframes, meaning we can call it like so: `data.groupby('colname')`. The method groups your dataset by a specified column, and applies any following changes within each of these groups. For a more detailed explanation see [this link](https://www.tutorialspoint.com/python_pandas/python_pandas_groupby.htm). The [documentation](https://pandas.pydata.org/pandas-docs/stable/generated/pandas.DataFrame.groupby.html) might also be useful.\n",
    "\n",
    "> **Ex. 3.3.1:** Compute the mean and median maximum daily temperature for each month-year-station pair on the dataframe `df_weather_period` from last exercise by using the _split-apply-combine_ procedure. Store the results in new columns `tmax_mean` and `tmax_median`. "
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "deletable": false,
    "nbgrader": {
     "cell_type": "code",
     "checksum": "f21e38a07bebf830e4c3c85ed1127d85",
     "grade": false,
     "grade_id": "cell-90339eeae9752f90",
     "locked": false,
     "schema_version": 3,
     "solution": true,
     "task": false
    }
   },
   "outputs": [],
   "source": [
    "# INCLUDED IN ASSIGNMENT 1"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "> **Ex. 3.3.2:** Plot the monthly max, min, mean, first and third quartiles for maximum temperature for our station with the ID _'CA006110549'_ for the years 1870-1875. \n",
    "\n",
    "> *Hint*: the method `describe` computes all these measures."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "deletable": false,
    "nbgrader": {
     "cell_type": "code",
     "checksum": "6eb1be7117139270d256f62188cf0399",
     "grade": false,
     "grade_id": "cell-8e0b67c4d87ae877",
     "locked": false,
     "schema_version": 3,
     "solution": true,
     "task": false
    }
   },
   "outputs": [],
   "source": [
    "# INCLUDED IN ASSIGNMENT 1"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "> **Ex. 3.3.3:** Parse the station location data which you can find at https://www1.ncdc.noaa.gov/pub/data/ghcn/daily/ghcnd-stations.txt. Merge station locations onto the weather data spanning 1870-1875.  \n",
    "\n",
    "> _Hint:_ The location data have the folllowing format, \n",
    "\n",
    "```\n",
    "------------------------------\n",
    "Variable   Columns   Type\n",
    "------------------------------\n",
    "ID            1-11   Character\n",
    "LATITUDE     13-20   Real\n",
    "LONGITUDE    22-30   Real\n",
    "ELEVATION    32-37   Real\n",
    "STATE        39-40   Character\n",
    "NAME         42-71   Character\n",
    "GSN FLAG     73-75   Character\n",
    "HCN/CRN FLAG 77-79   Character\n",
    "WMO ID       81-85   Character\n",
    "------------------------------\n",
    "```\n",
    "\n",
    "> *Hint*: The station information has *fixed width format* - does there exist a pandas reader for that? Here Google might be helpful!"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "deletable": false,
    "nbgrader": {
     "cell_type": "code",
     "checksum": "9f9034d4e659e597d47b5e4cbad4b98c",
     "grade": false,
     "grade_id": "cell-10b2b71e38e06588",
     "locked": false,
     "schema_version": 3,
     "solution": true,
     "task": false
    }
   },
   "outputs": [],
   "source": [
    "# INCLUDED IN ASSIGNMENT 1"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "##  Bonus Exercises"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "If you want to practice *split-apply-combine* a bit more before proceeding, we have generated two additional exercises that use the US census data from Part 1. In order to solve this exercise, you should use `df_census_new` which you generated in a previous exercise.\n",
    "> **Ex. 3.3.4:** (_Bonus_) Is there any evidence of a gender-wage-gap in the data? Create a table showing the percentage of men and women earning more than 50K a year."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "deletable": false,
    "nbgrader": {
     "cell_type": "code",
     "checksum": "7890fcb19c0fe6ee81a94be4014a79f3",
     "grade": false,
     "grade_id": "cell-67b5c30978741779",
     "locked": false,
     "schema_version": 3,
     "solution": true,
     "task": false
    }
   },
   "outputs": [],
   "source": [
    "# YOUR CODE HERE\n",
    "raise NotImplementedError()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "> **Ex. 3.3.5:** (_Bonus_) Group the data by years of education (`educ_num`) and marital status. Now plot the share of individuals who earn more than 50K for the two groups 'Divorced' and 'Married-civ-spouse' (normal marriage). Your final result should look like this: \n",
    "\n",
    "![](examplefig.png)\n",
    "\n",
    "> _Hint:_ remember the `.query()` method is extremely useful for filtering data."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "deletable": false,
    "nbgrader": {
     "cell_type": "code",
     "checksum": "1232340258600774bd10822553abacd5",
     "grade": false,
     "grade_id": "cell-2f83377644343c11",
     "locked": false,
     "schema_version": 3,
     "solution": true,
     "task": false
    }
   },
   "outputs": [],
   "source": [
    "# YOUR CODE HERE\n",
    "raise NotImplementedError()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "# Part 4: Reshaping  Dataframes\n",
    "\n",
    "Often we have data that comes in a format that does not fit our purpose."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "YouTubeVideo('cJOo01dj5hs', width=640, height=360)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "In this exercise, we continue working with the NOAA data that you worked with in the main section of part 3.\n",
    "\n",
    "> **Ex. 3.4.1:** For which months was the temperature in general (i.e. use the mean) lower in 1870 vs. 1875?\n",
    ">\n",
    "> *Hint:* you may use `unstack`."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "deletable": false,
    "nbgrader": {
     "cell_type": "code",
     "checksum": "4925e38e10f2593052255355147dae7d",
     "grade": false,
     "grade_id": "cell-93d420b54bc3cff9",
     "locked": false,
     "schema_version": 3,
     "solution": true,
     "task": false
    },
    "tags": []
   },
   "outputs": [],
   "source": [
    "# YOUR CODE HERE\n",
    "raise NotImplementedError()"
   ]
  }
 ],
 "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.16"
  },
  "nav_menu": {},
  "toc": {
   "navigate_menu": true,
   "number_sections": false,
   "sideBar": true,
   "threshold": 6,
   "toc_cell": false,
   "toc_section_display": "block",
   "toc_window_display": true
  },
  "varInspector": {
   "cols": {
    "lenName": 16,
    "lenType": 16,
    "lenVar": 40
   },
   "kernels_config": {
    "python": {
     "delete_cmd_postfix": "",
     "delete_cmd_prefix": "del ",
     "library": "var_list.py",
     "varRefreshCmd": "print(var_dic_list())"
    },
    "r": {
     "delete_cmd_postfix": ") ",
     "delete_cmd_prefix": "rm(",
     "library": "var_list.r",
     "varRefreshCmd": "cat(var_dic_list()) "
    }
   },
   "types_to_exclude": [
    "module",
    "function",
    "builtin_function_or_method",
    "instance",
    "_Feature"
   ],
   "window_display": false
  }
 },
 "nbformat": 4,
 "nbformat_minor": 4
}