Lesson 2 - Programmatic data wrangling with Python and Pandas.ipynb

{
 "cells": [
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "# _Lesson 2_: Programmatic data wrangling with Python and Pandas\n",
    "\n",
    "<div class=\"alert alert-block alert-warning\">\n",
    "    <b>Learning outcomes:</b>\n",
    "    <br>\n",
    "    <ul>\n",
    "        <li>Understand, and have practical experience with, the structure and design of machine-readable data files.</li>\n",
    "        <li>Integrate and apply methods from the core data analysis libraries of Numpy, Pandas and Matplotlib to investigate and manipulate source data.</li>\n",
    "    </ul>\n",
    "</div>\n",
    "\n",
    "---"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## 2.1 Introduction\n",
    "\n",
    "The term _Open Data_ is generally understood to be data that are made available to the public free of charge, without registration or restrictive licenses, for any purpose whatsoever (including commercial purposes), in electronic, machine-readable formats that ensure data are easy to find, download and use. \n",
    " \n",
    "Open data initiatives by public institutions, such as governments and intergovernmental organisations, recognise that such data is produced with public funds and so, with few exceptions, should be treated as public goods.\n",
    " \n",
    "Data reuse, both by data experts and the public at large, is key to creating new opportunities and benefits from government data. Open data reuse requires two basic criteria:\n",
    " \n",
    "1. __Data must be legally open__, meaning that it is placed in the public domain or under liberal terms of use with minimal restrictions. This ensures that government policies do not create barriers or ambiguities concerning how the data may be used.\n",
    "2. __Data must be technically open__, meaning that it is published in electronic formats that are machine-readable and non-proprietary. This ensures that ordinary citizens can access and use the data with little or no cost using common software tools.\n",
    "\n",
    "The purpose of this syllabus in _Data Wrangling and Validation for Open Data_ is to guide learners to confidence in delivering technically open data: well-structured, machine-readable data, validated to a defined and standard metadata schema.\n",
    "\n",
    "---"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## 2.2 The data management lifecycle\n",
    "\n",
    "While the process for creating, maintaining and inspiring new data are often presented as a cycle, it is more like a spiral. Each cycle spiralling upwards, and resulting in more information. However, the effectiveness of each step is defined by the needs of its users, and its relevance in terms of the process or events it reflects.\n",
    "\n",
    "![Data lifecycle](images/data-lifecycle-en.jpg)\n",
    "\n",
    "### 2.2.3 Collection and creation\n",
    "\n",
    "Before data can be collected, there are a range of things which must be known:\n",
    "\n",
    "- why are we collecting the data?\n",
    "- what purpose does it serve?\n",
    "- do we have consent and / or the legal authority to collect that data?\n",
    "- is there an existing data series which this data extends or compliments?\n",
    "- how will the data be collected?\n",
    "- who will be responsible for data quality, and how will quality be measured?\n",
    "- who will have access to the data, and how sensitive is this data (e.g. personally-identifying)?\n",
    "- are we using a standardised and agreed-upon classification or metadata format?\n",
    "\n",
    "### 2.2.4 Classification and processing\n",
    "\n",
    "The creator of the data would best know what the data are about and should assign keywords as descriptors. This data about the data is called metadata. The term is ambiguous, as it is used for two fundamentally different concepts:\n",
    " \n",
    "- __Structural metadata__ correspond to internal metadata (i.e. metadata about the structure of database objects such as tables, columns, keys and indexes);\n",
    "- __Descriptive metadata__ correspond to external metadata. (i.e. metadata typically used for discovery and identification, as information used to search and locate an object such as title, author, subjects, keywords, publisher);\n",
    " \n",
    "Descriptive metadata permits discovery of the object. Structural metadata permits the data to be applied, interpreted, analysed, restructured, and linked to other, similar, datasets.\n",
    " \n",
    "Metadata can permit interoperability between different systems. An agreed-upon structure for querying the ‘aboutness’ of a data series can permit unrelated software systems to find and use remote data.\n",
    " \n",
    "Beyond metadata, there are also mechanisms for the structuring of relationships between hierarchies of keywords. These are known as ontologies and, along with metadata, can be used to accurately define and permit discovery of data.\n",
    " \n",
    "Adding metadata to existing data resources can be a labour-intensive and expensive process. This may become a barrier to implementing a comprehensive knowledge management system.\n",
    "\n",
    "The types of descriptive metadata we should include (and the terms we should use for them):\n",
    "\n",
    "| REQUIRED        | RECOMMENDED   | OPTIONAL              |\n",
    "|:----------------|:--------------|:----------------------|\n",
    "| Title           | Tag(s)        | Last update           |\n",
    "| Description     | Terms of use  | Update frequency      |\n",
    "| Theme(s)        | Contact email | Geographical coverage |\n",
    "| Publishing body |               | Temporal coverage     |\n",
    "|                 |               | Validity              |\n",
    "|                 |               | Related resources     |\n",
    "|                 |               | Regulations           |\n",
    "\n",
    "### 2.2.5 Manipulation, conversion or alteration\n",
    "\n",
    "This part of the process is where the data are transcribed, translated, checked, validated, cleaned and managed.\n",
    "\n",
    "This presents the greatest risk for data consistency. Any format change or manipulation, or even copying a file from one system to another, introduces the potential for data corruption. Similarly, it also increases the potential for data - whether erroneous or not - to be accidentally released to users or the public before it is ready.\n",
    "\n",
    "This is also known as __data wrangling__.\n",
    "\n",
    "### 2.2.6 Analysis and presentation\n",
    "\n",
    "Analysis may not always be part of the data manager's role, but it is certainly the reason that data are collected.\n",
    "\n",
    "It is here where the data are interpreted, combined with other datasets to produce meta-analysis, and where analysis becomes the story you wish to tell derived from the data.\n",
    "\n",
    "The story is only as good as your data and research is only considered valid if the data which informs it are also released.\n",
    "\n",
    "The purpose of analysis is to inform collective or individual behaviour, influence policy, or support economic activity, amongst many others. Trust can be achieved by  \"showing your workings \", which includes data release.\n",
    "\n",
    "### 2.2.7 Preserving and storing\n",
    "\n",
    "Data needs to be preserved from corruption, as well as being available for later use once the initial analysis is complete. It is also necessary to preserve the data in case of queries regarding the analysis.\n",
    "\n",
    "Long-term storage requires that the __metadata__ be well-defined and exceptionally useful to ensure that understanding what the data describe is still possible long after its initial collection.\n",
    "\n",
    "Data may end up being stored in multiple formats or across multiple systems. It is essential that primacy is established (i.e. which dataset has priority over the others) and that the various formats are kept in alignment.\n",
    "\n",
    "### 2.2.8 Release and access\n",
    "\n",
    "Even where data is only released within government - and not for the public - there will always be others who will want to use your data, or would benefit from it if they know it exists. The greatest inefficiency in data management arises when research is repeated because a different department needs the same data but did not know it already existed.\n",
    "\n",
    "Release is not simply about making data available to the public, it is also about creating a predictable process for that release.\n",
    "\n",
    "Regularly collected data (such as inflation rates) need a predictable release cycle since many companies base their investment decisions on the availability of such information.\n",
    "\n",
    "Publishing a release calendar for your stakeholders (and sticking to it) permits them to plan their own analysis, or response to your analysis.\n",
    "\n",
    "Access implies that you need a centralised database which is accessible to your stakeholders. In the case of open data, how will data be moved from internal servers to a public repository?\n",
    "\n",
    "Responsibility for this process needs to be assigned and measured.\n",
    "\n",
    "### 2.2.9 Retention and reuse\n",
    "\n",
    "Once data are released, the question arises as to how long it will be available? Research data should, ordinarily, be available in perpetuity.\n",
    "\n",
    "Time-series data become more useful the longer it has been collected. Suddenly removing data can cause tremendous disruption.\n",
    "\n",
    "If appropriate systems and support have not been put in place then retention can become a very expensive problem.\n",
    "\n",
    "Importantly, in order to support reuse, clear copyright and licensing which permit data to be freely reused for any purpose is essential.\n",
    "\n",
    "### 2.2.10 Archival\n",
    "\n",
    "Datasets can become very large and may only be accessed infrequently. This can become problematic for long-term storage.\n",
    "\n",
    "A process of archival - where data can be stored more cheaply but still be accessible - may need to be considered.\n",
    "\n",
    "<div class=\"alert alert-block alert-danger\">\n",
    "    <b>Actions:</b>\n",
    "    <br>\n",
    "    <ul>\n",
    "        <li>Ensure there is a data owner who is responsible for the data lifecycle, including publication and responding to feedback or queries;</li>\n",
    "        <li>Prepare a data-collection plan, ensuring that definitions and data structure conform to international standards;</li>\n",
    "        <li>Ensure metadata are agreed with stakeholders and are useful and standardised;</li>\n",
    "        <li>Ensure that data are appropriately licensed to ensure release and reuse;</li>\n",
    "    </ul>\n",
    "</div>\n",
    "\n",
    "<div class=\"alert alert-block alert-info\">\n",
    "    <b>References:</b>\n",
    "    <br>\n",
    "    <ul>\n",
    "        <li><a href=\"http://www.bu.edu/datamanagement/background/data-life-cycle/\">Boston University Data Life Cycle</a></li>\n",
    "        <li><a href=\"http://searchsecurity.techtarget.com/magazineContent/Data-Lifecycle-Management-Model-Shows-Risks-and-Integrated-Data-Flow\">Tech Target Data Lifecycle Management</a></li>\n",
    "        <li><a href=\"http://en.wikipedia.org/wiki/Dublin_Core\">Dublin Core Metadata Standard</a></li>\n",
    "        <li><a href=\"http://www.w3.org/TR/vocab-dcat/\">DCAT Metadata Standard</a></li>\n",
    "        <li><a href=\"http://en.wikipedia.org/wiki/Seven_Basic_Tools_of_Quality\">Seven Basic Tools of Quality</a></li>\n",
    "    </ul>\n",
    "</div>\n",
    "\n",
    "---"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## 2.3 Data wrangling and preparation for publication\n",
    "\n",
    "There are a number of common formats for data distribution. Some of these are considered \"open\" (such as CSV, XML, text and others) and some proprietary (SAS, STATA, SPSS, etc.). XLS and XLSX, associated with Microsoft Excel, are relatively open formats and a number of software systems can interpret the data.\n",
    " \n",
    "Proprietary formats are legitimate even in an open data initiative since these are the software systems used by many professional data users. However, since these formats are often not interoperable, the potential for data re-use is limited unless open formats are also supported. Data dissemination in proprietary formats does not preclude dissemination in open formats and vice versa.\n",
    "\n",
    "Spreadsheets and distributed data systems often lack an agreed data structure. A researcher who wishes to combine this with other data first needs to normalise it and then decide on standardised terms to define the columns and data-types in those columns.\n",
    "\n",
    "![A typical \"human-readable\" structured table of data](images/01-01-human-readable-data.jpg)\n",
    "\n",
    "Converting semi-structured tabular data into a typical machine-readable format results in the comma-separated-value (CSV) file. These are tabular files with a header row which defines each of the data in the columns and rows below.\n",
    "\n",
    "![A CSV-structured table of data](images/01-01-machine-readable-data.jpg)\n",
    "\n",
    "Ignoring any further standards compliance, CSV files can be so arranged that they are \"joined\" on a common column. For example, a set of geospatial coordinates can be used to connect a number of similar files covering different data series.\n",
    "\n",
    "### 2.3.1 Considerations for machine-readable spreadsheets\n",
    "\n",
    "Data.gov has a useful [Primer on Machine Readability for Online Documents and Data](https://www.data.gov/developers/blog/primer-machine-readability-online-documents-and-data) and a comment thread at Data.gov.uk offers the following guidance:\n",
    "\n",
    "- A requester can open the data file in freely-available and widely accessible software packages - this means that formats such as CSV should be preferred over, or offered in addition to formats like Excel, and proprietary formats which can only be opened with commercial or specialist software should be avoided. \n",
    "- It is possible to process the data directly, carrying out any appropriate operations on it such as sorting columns, filtering rows, running aggregates of values - this requires well structured data. Where possible, the meaning of the data should not be contained in the layout. \n",
    "- Common elements in the dataset are expressed in uniform ways - for example, dates are always in the same format, codes or names are always in the same case, and numbers are expressed consistently (e.g. 1,000 or 1000 but not a mixture of the two). \n",
    "- The meaning of fields and values is clearly documented - either through clear naming of fields, or through accompanying descriptions provided along with the data.  \n",
    "\n",
    "In addition, where possible, machine readability is enhanced if:\n",
    "\n",
    "- The dataset uses common standards where they exist - including standard identifiers and standard field names. These might be standards like the public spending vocabulary developed for government, or third-party standards such as KML for indicating 'points of interest'\n",
    "\n",
    "It is important to also note that it may be possible to provide data in a variety of machine readable formats, and where possible the authority should correspond with the requester to identify the best format. For example, points of interest could be provided in a CSV spreadsheet form, or KML. Ideally both would be provided: though depending on the context and the re-user one may be more appropriate than the other. \n",
    "  \n",
    "### 2.3.2 _Long_ vs _wide_ data\n",
    "\n",
    "Any data series consists of numerical values (usually) described by standardised metadata terms (time, area, a specific description, etc). There are two main ways of presenting these machine-readable data, which can be summarised as _wide_ or _long_. You need to make a deliberate choice as to which format you will choose, and each has its own particular strengths and weaknesses:\n",
    "\n",
    "- __Wide data__ present numerical data in multiple columns. Either as categories (e.g. each country is presented in its own column) or by date (e.g. each annual update results in a new column). New data go across the screen from left to right:\n",
    "\n",
    "![Wide data](images/01-01-wide-data.jpg)\n",
    "\n",
    "__Wide data__ are often used for data visualisation and processing since the data can easily be grouped into the necessary axes for chart libraries. However, it's a difficult archival format since updating such a dataseries requires the equivalent of creating a new field (the _year_ in the fields above) and then updating every row with appropriate information. That can be an expensive operation in a large database, and also means that writing a programmatic method for querying your data is more challenging.\n",
    "\n",
    "- __Long data__ present numerical data in multiple rows with only one column for values. New data go down the screen from top to bottom:\n",
    "\n",
    "![Long data](images/01-01-narrow-data.jpg)\n",
    "\n",
    "__Long data__ are best for archival and for representing the structure you will ordinarily find in a database. Each row in a _long_ dataseries represents a row in a database. Adding new information is relatively straightforward since you only need update a single row at a time. In database terms, you'd be creating a single database entry.\n",
    "\n",
    "<div class=\"alert alert-block alert-warning\">\n",
    "    <p>The preference in open data publication is for the <b>long</b> format, and this will be the method usually recommended for release. That said, conversion between them - as long as data are machine-readable with well-defined metadata - is straightforward.</p>\n",
    "</div>\n",
    "\n",
    "### 2.3.3 Defining your destination metadata and schema\n",
    "\n",
    "Creating a `schema` is the first part of the wrangling process. Your schema defines the structural\n",
    "metadata target for your wrangling process. This is not the format your input data arrive in, but\n",
    "it is what you require it to look like when you're done.\n",
    "\n",
    "Your schema sets the requirements, constraints and sensible defaults available for wrangling input data into the fields defined by the schema. Once complete, you will need to perform further cleaning and validation.\n",
    "\n",
    "In simple terms, the columns in an input CSV or Excel-file will be restructured into new columns\n",
    "defined by the fields in your schema. These target fields are likely to be those in a database. Until your input data conform to this structure, your data should not be released as open data.\n",
    "\n",
    "We'll be using [Frictionless Data's](https://specs.frictionlessdata.io/table-schema/#field-descriptors) definitions and schema to define structural metadata fields. \n",
    "\n",
    "#### ___`type` and `format`___\n",
    "\n",
    "`type` defines the data-type of the field, while `format` further refines the specific `type` properties. These are the core types you're likely to use, with indents for formats:\n",
    "\n",
    "- `string`: any text-based string.\n",
    "  - `default`: any string\n",
    "  - `email`: an email address\n",
    "  - `uri`: any web address / URI\n",
    "- `number`: any number-based value, including integers and floats.\n",
    "- `integer`: any integer-based value.\n",
    "- `boolean`: a boolean [`true`, `false`] value. Can set category constraints to fix term used.\n",
    "- `object`: any valid JSON data.\n",
    "- `array`: any valid array-based data.\n",
    "- `date`: any date without a time. Must be in ISO8601 format, `YYYY-MM-DD`.\n",
    "- `datetime`: any date with a time. Must be in ISO8601 format, with UTC time specified (optionally) as `YYYY-MM-DD hh:mm:ss Zz`.\n",
    "- `year`: any year, formatted as `YYYY`.\n",
    "\n",
    "#### ___`schema` design___\n",
    "\n",
    "Have a look at the open data presented at [Données hospitalières relatives à l'épidémie de COVID-19](https://www.data.gouv.fr/fr/datasets/donnees-hospitalieres-relatives-a-lepidemie-de-covid-19/). Preview the metadata schema file for hospitalisation:\n",
    "\n",
    "![Hospitalisation structural metadata](images/01-01-structural-metadata-covid-hospitalisation.jpg)\n",
    "\n",
    "The first term in each row defines the destination column name (the structural metadata terms), along with its data type, descriptions in English and French, and an example of an expected value for that field.\n",
    "\n",
    "Before you start restructuring messy data files, create such a structural metadata definition for your data. And the first thing to do when you begin is simply to look at your data and understand how it is currently structured. Your objective in moving from messy to structured data is simple:\n",
    "\n",
    " ___Preserve everything that is known from your source data when you restructure it.___\n",
    "\n",
    "Here is a summary of these schema design requirements:\n",
    "\n",
    "- _Establish a standard convention for naming column fields, and stick to it_; the convention refers to the physical structure of the word, such as `startDate` or `place_name`. `camelCase` is one convention, `underscore_separation` is another. Don't mix these as it is frustrating for users.\n",
    "- _Every value of every column of every table should represent only a single thing_; if you look at the `region` column below, you'll see that it should be split into separate `region` and `district` columns for Côte d'Ivoire:\n",
    "\n",
    "| description | region                   | year | value     |\n",
    "|:------------|:-------------------------|:-----|:----------|\n",
    "| Population  | Gbôklé, Bas-Sassandra    | 2014 | 400,798   |\n",
    "| Population  | Nawa, Bas-Sassandra      | 2014 | 1,053,084 |\n",
    "| Population  | San-Pédro, Bas-Sassandra | 2014 | 826,666   |\n",
    "| Population  | Indénié-Djuablin, Comoé  | 2014 | 560,432   |\n",
    "| Population  | Sud-Comoé, Comoé         | 2014 | 642,620   |\n",
    "\n",
    "- _Define the `type`, `format` and `definition` for each column name_; the below schema definition is the __long__ form. If each `year` had its own column, you would then - instead of `year` - have a row for `2014`, then the next release might include `2016` and so on. You can see that updating the data and the schema simultaneously is more work for you, and your users, than just updating the data:\n",
    "\n",
    "| column      | type             | description                                      | example    |\n",
    "|:------------|:-----------------|:-------------------------------------------------|:-----------|\n",
    "| description | string           | Definition of the demographic data series        | Population |\n",
    "| region      | string           | Name of one of the 31 regions in Côte d'Ivoire   | Sud-Comoé  |\n",
    "| district    | string           | Name of one of the 14 districts in Côte d'Ivoire | Comoé      |\n",
    "| year        | date             | The year of the data series                      | 2014       |\n",
    "| value       | integer          | The value for the data series                    | 560432     |\n",
    "\n",
    "There are additional requirements for validating your data - such as noting that the commas between digits in the numbers must be removed - and we'll go through these below.\n",
    "\n",
    "#### ___`descriptive metadata` design___\n",
    "\n",
    "Before we start wrangling our data, first we should review the following table of formal terms which describe the data:\n",
    "\n",
    "| REQUIRED        | RECOMMENDED   | OPTIONAL              |\n",
    "|:----------------|:--------------|:----------------------|\n",
    "| Title           | Tag(s)        | Last update           |\n",
    "| Description     | Terms of use  | Update frequency      |\n",
    "| Theme(s)        | Contact email | Geographical coverage |\n",
    "| Publishing body |               | Temporal coverage     |\n",
    "|                 |               | Validity              |\n",
    "|                 |               | Related resources     |\n",
    "|                 |               | Regulations           |\n",
    "\n",
    "These are the high-level definitions and terms to describe what the data your are restructuring are about. A person opening a spreadsheet has very little context as to what they are reading, and these metadata provide much-needed context.\n",
    "\n",
    "Your next step is to begin restructuring your messy data to conform to this schema you've just created.\n",
    "\n",
    "### 2.3.4 Habits which make spreadsheets unusable\n",
    "\n",
    "In his excellent essay [The Art of Spreadsheets](http://john.raffensperger.org/ArtOfTheSpreadsheet/Chapter09_ShowAllTheInformation.html) John Raffensperger lists 37 ways that you can hide data in a spreadsheet. Here are 10 of them:\n",
    "\n",
    "- Do not share the file. This is the most common way of hiding information, and the most effective.\n",
    "- Hide the sheet. You need at least two sheets first, then: Format, Sheet, Hide.\n",
    "- Hide the row: Format, Row, Hide.\n",
    "- Hide the column: Format, Column, Hide.\n",
    "- Hide the cell and protect the sheet: Format, Cells, Protection, Hidden, then Tools, Protection. This shows a display, but hides the formula: =if(1, \"Peace!\", \"Attack at dawn.\").\n",
    "- Make the column too narrow: Format, Column, Width, 0.\n",
    "- For formulas that are likely to be zero, use Tools, Options, View, and clear the Zero values box. For example: =IF(1, 0, \"Attack at dawn.\").\n",
    "- Use a formula that returns a blank: =IF(1, \" \", \"Attack at dawn.\").\n",
    "- Create a complicated formula that displays the information, but format it as text (with Format, Cells, Number, Text, or just start the cell with a single quotation mark), so the formula is displayed rather than the output.\n",
    "- Format the font with Wingdings: Format, Cells, Font, Wingdings. This displays unintelligible characters.\n",
    "\n",
    "<div class=\"alert alert-block alert-success\">\n",
    "    <b>Exercise:</b>\n",
    "    <br>\n",
    "    <p><a href=\"https://www.data.gouv.fr/fr/datasets/donnees-hospitalieres-relatives-a-lepidemie-de-covid-19/\">Santé publique France</a> produce daily updates on France's COVID-19 hospitalisation data. Using John Raffensperger's list as inspiration, your task is to mess up the <a href=\"https://www.data.gouv.fr/fr/datasets/r/63352e38-d353-4b54-bfd1-f1b3ee1cabd7\">donnees-hospitalieres-covid19.csv</a> data as much as possible.</p>\n",
    "    <p>Marks will be awarded for:</p>\n",
    "    <ul>\n",
    "        <li>making the presentation just bad enough that someone using the data might be tempted to think they can still use it!</li>\n",
    "        <li>the use of colour and font effects in ways that really offend the eye</li>\n",
    "        <li>ingenuity in hiding bits of data in plain sight.</li>\n",
    "    </ul>\n",
    "</div>\n",
    "\n",
    "<div class=\"alert alert-block alert-info\">\n",
    "    <b>Tutorials & references:</b>\n",
    "    <br>\n",
    "    <ul>\n",
    "        <li><a href=\"http://blog.ouseful.info/2012/11/27/when-machine-readable-data-still-causes-issues-wrangling-dates/\">When Machine Readable Data Still Causes \"Issues\" – Wrangling Dates…</a></li>\n",
    "        <li><a href=\"http://www.opendataimpacts.net/2012/11/more-than-csv/\">Digital landscapes: effective open data takes more than a single CSV</a></li>\n",
    "        <li><a href=\"https://www.mikealche.com/software-development/a-humble-guide-to-database-schema-design\">A humble guide to database schema design</a></li>\n",
    "    </ul>\n",
    "</div>\n",
    "\n",
    "---"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## 2.4 Investigating and manipulating data with Pandas\n",
    "\n",
    "It would be nice if you could import messy data and have a program automatically restructure it for you into your chosen metadata schema. There is no current mechanism to do this, and programmatic data wrangling only reduces the complexity of the process in small ways. What it does do is reduce repetitive tasks, like copying-and-pasting, and massively limit the risk of introducing copy-related errors.\n",
    "\n",
    "### Setting up your development environment, and installing third-party packages\n",
    "\n",
    "Python is a programming language. At its core are the functions and definitions you learned in Lesson 1.2.1. However, Python is also a community of passionate developers - just like you - who each bring to the programming language their particular interest. It could be solving complex maths, or drawing striking charts and visualisations, creating computer games, or even working with structured data. When they do this, they create new functions which they can package up as libraries to be used by others. The Python community has produced tens of thousands of these packages, and we're going to install some of them now.\n",
    "\n",
    "If you haven't already, start your Anaconda Navigator: \n",
    "\n",
    "![Jupyter menu (base)](images/jupyter-home-base.jpg)\n",
    "\n",
    "Select Environments from the menu on the left:\n",
    "\n",
    "![Jupyter menu](images/jupyter-menu.jpg)\n",
    "\n",
    "Anaconda allows you to create isolated development environments. The important thing to understand about these is that it keeps your programming software contained to its own \"play-area\", making it easy to extend and delete. Create a new environment by clicking on the \"Create\" button at the bottom of the screen: \n",
    "\n",
    "![Jupyter environments](images/jupyter-environments.JPG \"Jupyter environments\")\n",
    "\n",
    "Name your environment anything you like, but you can call it `datascience` to make it easy to find. Now you need to install the packages we need during this course. There are two ways of doing this, one for packages that Conda already knows about, and one from the operating system terminal. We're going to do the second one now which will install everything we need.\n",
    "\n",
    "Left-click on the \"arrow\" next to \"datascience\" and choose `Open Terminal`. Make sure you click on the right environment; not `base` or `root`, but the name of the environment you just created: \n",
    "\n",
    "![Jupyter terminal](images/jupyter-terminal.JPG \"Jupyter terminal\")\n",
    "\n",
    "A new window will open with a command prompt. Type in the following and enter.\n",
    "\n",
    "    pip install whyqd\n",
    "    \n",
    "![Jupyter terminal window](images/jupyter-terminal-window.JPG \"Jupyter terminal window\")\n",
    "  \n",
    "[Whyqd](https://github.com/whythawk/whyqd/) has dependencies we will use during future lessons, one of which is [Pandas](https://pandas.pydata.org/pandas-docs/stable/), and these will be installed automatically as well. We won't be using `whyqd` until the next lesson, but this is a good time to make sure we have it. Now do the same for `matplotlib`, a charting library:\n",
    "\n",
    "    pip install matplotlib\n",
    "    \n",
    "Once it has finished installing (and you may need to answer \"yes\" when it prompts), return to Home and click to install Jupyter Notebook.\n",
    "\n",
    "![Jupyter module launcher](images/jupyter-datascience-home.JPG \"Jupyter Module Launcher\")\n",
    "\n",
    "Once installed, launch it: \n",
    "\n",
    "![Jupyter launch](images/jupyter-launch.JPG \"Launch Jupyter Notebook\")\n",
    "\n",
    "You will need to launch Jupyter Notebook every time you want to do a tutorial or edit your notebooks. Fortunately, Anaconda creates a Windows shortcut so you can easily find it:\n",
    "\n",
    "![Jupyter Windows launcher](images/jupyter-windows-launcher.JPG \"Jupyter Windows launcher\")\n",
    "\n",
    "If you get stuck, [read the docs](https://docs.anaconda.com/) or search for solutions on [Stack Overflow](https://stackoverflow.com/).\n",
    "\n",
    "There are a host of online tutorials and resources that will deepen your knowledge of the topics discussed in this lesson, and you can probably find these yourself. The documentation for the libraries we use are also a critical resource and familiarising yourself with how to read and use these will repay itself massively:\n",
    " \n",
    "<div class=\"alert alert-block alert-info\">\n",
    "    <b>References to the libraries installed for this course:</b>\n",
    "    <br>\n",
    "    <ul>\n",
    "        <li><a href=\"https://docs.anaconda.com/\">Anaconda</a></li>\n",
    "        <li><a href=\"http://jupyter.org/documentation.html\">Jupyter Notebook</a></li>\n",
    "        <li><a href=\"https://pandas.pydata.org/pandas-docs/stable/\">Pandas</a></li>\n",
    "        <li><a href=\"https://docs.scipy.org/doc/numpy-1.13.0/user/\">Numpy</a></li>\n",
    "        <li><a href=\"https://matplotlib.org/\">Matplotlib</a></li>\n",
    "        <li><a href=\"https://python-poetry.org/docs/\">Poetry</a></li>\n",
    "        <li><a href=\"https://whyqd.readthedocs.io/en/latest/\">Whyqd</a></li>\n",
    "    </ul>\n",
    "</div>\n",
    "\n",
    "---"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### Worked data wrangling example\n",
    "\n",
    "Working with data programmatically means working symbolically. Most of the time you won't be looking at columns and rows of data the way you will in a spreadsheet. The advantage is that you won't imagine patterns that aren't in the data, and that you can work with very large datasets. The disadvantage is that it's not always clear when something goes wrong.\n",
    "\n",
    "Your first step remains the need for data to be machine-readable. That means you need to be good at the skills presented in the first lesson. The software we're going to use cannot open files that are not prepared appropriately.\n",
    "\n",
    "Before you can explore data, you must ensure that there is a _header row_ and that there are _no hidden columns or fields_. Please review Lesson 1.1 if you're not absolutely sure of what this means.\n",
    "\n",
    "Let's start working with the module you'll be using and learning about for the rest of this course: `pandas`.\n",
    "\n",
    "<div class=\"alert alert-block alert-info\">\n",
    "    <b>Syntax</b>\n",
    "    <br>\n",
    "    <ul>\n",
    "        <li>After you've imported a module, <code>dir(module)</code> lets you see a list of all the functions implemented in that library.</li>\n",
    "        <li>You can also read the help from the module docstrings with <code>help(module)</code></li>\n",
    "    </ul>\n",
    "</div>"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 1,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "Help on package pandas:\n",
      "\n",
      "NAME\n",
      "    pandas\n",
      "\n",
      "DESCRIPTION\n",
      "    pandas - a powerful data analysis and manipulation library for Python\n",
      "    =====================================================================\n",
      "    \n",
      "    **pandas** is a Python package providing fast, flexible, and expressive data\n",
      "    structures designed to make working with \"relational\" or \"labeled\" data both\n",
      "    easy and intuitive. It aims to be the fundamental high-level building block for\n",
      "    doing practical, **real world** data analysis in Python. Additionally, it has\n",
      "    the broader goal of becoming **the most powerful and flexible open source data\n",
      "    analysis / manipulation tool available in any language**. It is already well on\n",
      "    its way toward this goal.\n",
      "    \n",
      "    Main Features\n",
      "    -------------\n",
      "    Here are just a few of the things that pandas does well:\n",
      "    \n",
      "      - Easy handling of missing data in floating point as well as non-floating\n",
      "        point data.\n",
      "      - Size mutability: columns can be inserted and deleted from DataFrame and\n",
      "        higher dimensional objects\n",
      "      - Automatic and explicit data alignment: objects can be explicitly aligned\n",
      "        to a set of labels, or the user can simply ignore the labels and let\n",
      "        `Series`, `DataFrame`, etc. automatically align the data for you in\n",
      "        computations.\n",
      "      - Powerful, flexible group by functionality to perform split-apply-combine\n",
      "        operations on data sets, for both aggregating and transforming data.\n",
      "      - Make it easy to convert ragged, differently-indexed data in other Python\n",
      "        and NumPy data structures into DataFrame objects.\n",
      "      - Intelligent label-based slicing, fancy indexing, and subsetting of large\n",
      "        data sets.\n",
      "      - Intuitive merging and joining data sets.\n",
      "      - Flexible reshaping and pivoting of data sets.\n",
      "      - Hierarchical labeling of axes (possible to have multiple labels per tick).\n",
      "      - Robust IO tools for loading data from flat files (CSV and delimited),\n",
      "        Excel files, databases, and saving/loading data from the ultrafast HDF5\n",
      "        format.\n",
      "      - Time series-specific functionality: date range generation and frequency\n",
      "        conversion, moving window statistics, date shifting and lagging.\n",
      "\n",
      "PACKAGE CONTENTS\n",
      "    _config (package)\n",
      "    _libs (package)\n",
      "    _testing\n",
      "    _typing\n",
      "    _version\n",
      "    api (package)\n",
      "    arrays (package)\n",
      "    compat (package)\n",
      "    conftest\n",
      "    core (package)\n",
      "    errors (package)\n",
      "    io (package)\n",
      "    plotting (package)\n",
      "    testing\n",
      "    tests (package)\n",
      "    tseries (package)\n",
      "    util (package)\n",
      "\n",
      "SUBMODULES\n",
      "    _hashtable\n",
      "    _lib\n",
      "    _tslib\n",
      "    offsets\n",
      "\n",
      "FUNCTIONS\n",
      "    __getattr__(name)\n",
      "\n",
      "DATA\n",
      "    IndexSlice = <pandas.core.indexing._IndexSlice object>\n",
      "    NA = <NA>\n",
      "    NaT = NaT\n",
      "    __docformat__ = 'restructuredtext'\n",
      "    __git_version__ = '3adf3340453d6704d4a2cb47058214cc697a7d29'\n",
      "    describe_option = <pandas._config.config.CallableDynamicDoc object>\n",
      "    get_option = <pandas._config.config.CallableDynamicDoc object>\n",
      "    options = <pandas._config.config.DictWrapper object>\n",
      "    reset_option = <pandas._config.config.CallableDynamicDoc object>\n",
      "    set_option = <pandas._config.config.CallableDynamicDoc object>\n",
      "\n",
      "VERSION\n",
      "    1.0.3\n",
      "\n",
      "FILE\n",
      "    c:\\users\\turukawa\\.conda\\envs\\datascience\\lib\\site-packages\\pandas\\__init__.py\n",
      "\n",
      "\n"
     ]
    }
   ],
   "source": [
    "import pandas as pd\n",
    "import numpy as np\n",
    "\n",
    "help(pd)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "We `import` a module and can rename it using the term `as`:\n",
    "\n",
    "    import pandas as pd\n",
    "    import numpy as np\n",
    "    \n",
    "There's nothing special about `pd` or `np`, it's just a convention, but we will be using these modules a ___lot___ so shortening pandas to pd and numpy to np makes typing slightly easier. We are now going to import the same Excel file we used in Lesson 1.1 and do exactly the same tutorial as before, only this time using `pandas` and `numpy`.\n",
    "\n",
    "We start by importing the Excel file. In pandas, a spreadsheet is called a `dataframe`, or `df` for short."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 2,
   "metadata": {},
   "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>Data Source</th>\n",
       "      <th>World Development Indicators</th>\n",
       "      <th>Unnamed: 2</th>\n",
       "      <th>Unnamed: 3</th>\n",
       "      <th>Unnamed: 4</th>\n",
       "      <th>Unnamed: 5</th>\n",
       "      <th>Unnamed: 6</th>\n",
       "      <th>Unnamed: 7</th>\n",
       "      <th>Unnamed: 8</th>\n",
       "      <th>Unnamed: 9</th>\n",
       "      <th>...</th>\n",
       "      <th>Unnamed: 54</th>\n",
       "      <th>Unnamed: 55</th>\n",
       "      <th>Unnamed: 56</th>\n",
       "      <th>Unnamed: 57</th>\n",
       "      <th>Unnamed: 58</th>\n",
       "      <th>Unnamed: 59</th>\n",
       "      <th>Unnamed: 60</th>\n",
       "      <th>Unnamed: 61</th>\n",
       "      <th>Unnamed: 62</th>\n",
       "      <th>Unnamed: 63</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>Last Updated Date</td>\n",
       "      <td>2020-04-09 00:00:00</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>...</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>...</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>Country Name</td>\n",
       "      <td>Country Code</td>\n",
       "      <td>Indicator Name</td>\n",
       "      <td>Indicator Code</td>\n",
       "      <td>1960.0</td>\n",
       "      <td>1961.0</td>\n",
       "      <td>1962.0</td>\n",
       "      <td>1963.0</td>\n",
       "      <td>1964.0</td>\n",
       "      <td>1965.0</td>\n",
       "      <td>...</td>\n",
       "      <td>2010.0</td>\n",
       "      <td>2011.0</td>\n",
       "      <td>2012.0</td>\n",
       "      <td>2013.0</td>\n",
       "      <td>2014.0</td>\n",
       "      <td>2015.0</td>\n",
       "      <td>2016.0</td>\n",
       "      <td>2017.0</td>\n",
       "      <td>2018.0</td>\n",
       "      <td>2019.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>Aruba</td>\n",
       "      <td>ABW</td>\n",
       "      <td>Urban population</td>\n",
       "      <td>SP.URB.TOTL</td>\n",
       "      <td>27526.0</td>\n",
       "      <td>28141.0</td>\n",
       "      <td>28532.0</td>\n",
       "      <td>28761.0</td>\n",
       "      <td>28924.0</td>\n",
       "      <td>29082.0</td>\n",
       "      <td>...</td>\n",
       "      <td>43778.0</td>\n",
       "      <td>43819.0</td>\n",
       "      <td>44057.0</td>\n",
       "      <td>44348.0</td>\n",
       "      <td>44665.0</td>\n",
       "      <td>44979.0</td>\n",
       "      <td>45296.0</td>\n",
       "      <td>45616.0</td>\n",
       "      <td>45948.0</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>Afghanistan</td>\n",
       "      <td>AFG</td>\n",
       "      <td>Urban population</td>\n",
       "      <td>SP.URB.TOTL</td>\n",
       "      <td>755836.0</td>\n",
       "      <td>796272.0</td>\n",
       "      <td>839385.0</td>\n",
       "      <td>885228.0</td>\n",
       "      <td>934135.0</td>\n",
       "      <td>986074.0</td>\n",
       "      <td>...</td>\n",
       "      <td>6927764.0</td>\n",
       "      <td>7212518.0</td>\n",
       "      <td>7528588.0</td>\n",
       "      <td>7865067.0</td>\n",
       "      <td>8204877.0</td>\n",
       "      <td>8535606.0</td>\n",
       "      <td>8852859.0</td>\n",
       "      <td>9164841.0</td>\n",
       "      <td>9477100.0</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "<p>5 rows × 64 columns</p>\n",
       "</div>"
      ],
      "text/plain": [
       "         Data Source World Development Indicators        Unnamed: 2  \\\n",
       "0  Last Updated Date          2020-04-09 00:00:00               NaN   \n",
       "1                NaN                          NaN               NaN   \n",
       "2       Country Name                 Country Code    Indicator Name   \n",
       "3              Aruba                          ABW  Urban population   \n",
       "4        Afghanistan                          AFG  Urban population   \n",
       "\n",
       "       Unnamed: 3  Unnamed: 4  Unnamed: 5  Unnamed: 6  Unnamed: 7  Unnamed: 8  \\\n",
       "0             NaN         NaN         NaN         NaN         NaN         NaN   \n",
       "1             NaN         NaN         NaN         NaN         NaN         NaN   \n",
       "2  Indicator Code      1960.0      1961.0      1962.0      1963.0      1964.0   \n",
       "3     SP.URB.TOTL     27526.0     28141.0     28532.0     28761.0     28924.0   \n",
       "4     SP.URB.TOTL    755836.0    796272.0    839385.0    885228.0    934135.0   \n",
       "\n",
       "   Unnamed: 9  ...  Unnamed: 54  Unnamed: 55  Unnamed: 56  Unnamed: 57  \\\n",
       "0         NaN  ...          NaN          NaN          NaN          NaN   \n",
       "1         NaN  ...          NaN          NaN          NaN          NaN   \n",
       "2      1965.0  ...       2010.0       2011.0       2012.0       2013.0   \n",
       "3     29082.0  ...      43778.0      43819.0      44057.0      44348.0   \n",
       "4    986074.0  ...    6927764.0    7212518.0    7528588.0    7865067.0   \n",
       "\n",
       "   Unnamed: 58  Unnamed: 59  Unnamed: 60  Unnamed: 61  Unnamed: 62  \\\n",
       "0          NaN          NaN          NaN          NaN          NaN   \n",
       "1          NaN          NaN          NaN          NaN          NaN   \n",
       "2       2014.0       2015.0       2016.0       2017.0       2018.0   \n",
       "3      44665.0      44979.0      45296.0      45616.0      45948.0   \n",
       "4    8204877.0    8535606.0    8852859.0    9164841.0    9477100.0   \n",
       "\n",
       "   Unnamed: 63  \n",
       "0          NaN  \n",
       "1          NaN  \n",
       "2       2019.0  \n",
       "3          NaN  \n",
       "4          NaN  \n",
       "\n",
       "[5 rows x 64 columns]"
      ]
     },
     "execution_count": 2,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "source = \"http://api.worldbank.org/v2/en/indicator/SP.URB.TOTL?downloadformat=excel\"\n",
    "# read_excel is a pandas function which will open an Excel file for you\n",
    "# the `source` can be anywhere, either in a local file, or a remote online resource\n",
    "df = pd.read_excel(source)\n",
    "# head will print the first x rows of the dataframe so we can see what it looks like\n",
    "df.head(5) "
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Well, that's a problem, but the header row isn't in the first row, it's actually in the fourth. Let's reimport our file and make sure we ignore the first two rows:"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 3,
   "metadata": {},
   "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 Name</th>\n",
       "      <th>Country Code</th>\n",
       "      <th>Indicator Name</th>\n",
       "      <th>Indicator Code</th>\n",
       "      <th>1960</th>\n",
       "      <th>1961</th>\n",
       "      <th>1962</th>\n",
       "      <th>1963</th>\n",
       "      <th>1964</th>\n",
       "      <th>1965</th>\n",
       "      <th>...</th>\n",
       "      <th>2010</th>\n",
       "      <th>2011</th>\n",
       "      <th>2012</th>\n",
       "      <th>2013</th>\n",
       "      <th>2014</th>\n",
       "      <th>2015</th>\n",
       "      <th>2016</th>\n",
       "      <th>2017</th>\n",
       "      <th>2018</th>\n",
       "      <th>2019</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>Aruba</td>\n",
       "      <td>ABW</td>\n",
       "      <td>Urban population</td>\n",
       "      <td>SP.URB.TOTL</td>\n",
       "      <td>27526.0</td>\n",
       "      <td>28141.0</td>\n",
       "      <td>28532.0</td>\n",
       "      <td>28761.0</td>\n",
       "      <td>28924.0</td>\n",
       "      <td>29082.0</td>\n",
       "      <td>...</td>\n",
       "      <td>43778.0</td>\n",
       "      <td>43819.0</td>\n",
       "      <td>44057.0</td>\n",
       "      <td>44348.0</td>\n",
       "      <td>44665.0</td>\n",
       "      <td>44979.0</td>\n",
       "      <td>45296.0</td>\n",
       "      <td>45616.0</td>\n",
       "      <td>45948.0</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>Afghanistan</td>\n",
       "      <td>AFG</td>\n",
       "      <td>Urban population</td>\n",
       "      <td>SP.URB.TOTL</td>\n",
       "      <td>755836.0</td>\n",
       "      <td>796272.0</td>\n",
       "      <td>839385.0</td>\n",
       "      <td>885228.0</td>\n",
       "      <td>934135.0</td>\n",
       "      <td>986074.0</td>\n",
       "      <td>...</td>\n",
       "      <td>6927764.0</td>\n",
       "      <td>7212518.0</td>\n",
       "      <td>7528588.0</td>\n",
       "      <td>7865067.0</td>\n",
       "      <td>8204877.0</td>\n",
       "      <td>8535606.0</td>\n",
       "      <td>8852859.0</td>\n",
       "      <td>9164841.0</td>\n",
       "      <td>9477100.0</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>Angola</td>\n",
       "      <td>AGO</td>\n",
       "      <td>Urban population</td>\n",
       "      <td>SP.URB.TOTL</td>\n",
       "      <td>569222.0</td>\n",
       "      <td>597288.0</td>\n",
       "      <td>628381.0</td>\n",
       "      <td>660180.0</td>\n",
       "      <td>691532.0</td>\n",
       "      <td>721552.0</td>\n",
       "      <td>...</td>\n",
       "      <td>13963065.0</td>\n",
       "      <td>14660282.0</td>\n",
       "      <td>15383127.0</td>\n",
       "      <td>16130304.0</td>\n",
       "      <td>16900847.0</td>\n",
       "      <td>17691524.0</td>\n",
       "      <td>18502165.0</td>\n",
       "      <td>19332881.0</td>\n",
       "      <td>20184707.0</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>Albania</td>\n",
       "      <td>ALB</td>\n",
       "      <td>Urban population</td>\n",
       "      <td>SP.URB.TOTL</td>\n",
       "      <td>493982.0</td>\n",
       "      <td>513592.0</td>\n",
       "      <td>530766.0</td>\n",
       "      <td>547928.0</td>\n",
       "      <td>565248.0</td>\n",
       "      <td>582374.0</td>\n",
       "      <td>...</td>\n",
       "      <td>1519519.0</td>\n",
       "      <td>1546929.0</td>\n",
       "      <td>1575788.0</td>\n",
       "      <td>1603505.0</td>\n",
       "      <td>1630119.0</td>\n",
       "      <td>1654503.0</td>\n",
       "      <td>1680247.0</td>\n",
       "      <td>1706345.0</td>\n",
       "      <td>1728969.0</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>Andorra</td>\n",
       "      <td>AND</td>\n",
       "      <td>Urban population</td>\n",
       "      <td>SP.URB.TOTL</td>\n",
       "      <td>7839.0</td>\n",
       "      <td>8766.0</td>\n",
       "      <td>9754.0</td>\n",
       "      <td>10811.0</td>\n",
       "      <td>11915.0</td>\n",
       "      <td>13067.0</td>\n",
       "      <td>...</td>\n",
       "      <td>75007.0</td>\n",
       "      <td>74305.0</td>\n",
       "      <td>73056.0</td>\n",
       "      <td>71515.0</td>\n",
       "      <td>70057.0</td>\n",
       "      <td>68919.0</td>\n",
       "      <td>68213.0</td>\n",
       "      <td>67876.0</td>\n",
       "      <td>67813.0</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "<p>5 rows × 64 columns</p>\n",
       "</div>"
      ],
      "text/plain": [
       "  Country Name Country Code    Indicator Name Indicator Code      1960  \\\n",
       "0        Aruba          ABW  Urban population    SP.URB.TOTL   27526.0   \n",
       "1  Afghanistan          AFG  Urban population    SP.URB.TOTL  755836.0   \n",
       "2       Angola          AGO  Urban population    SP.URB.TOTL  569222.0   \n",
       "3      Albania          ALB  Urban population    SP.URB.TOTL  493982.0   \n",
       "4      Andorra          AND  Urban population    SP.URB.TOTL    7839.0   \n",
       "\n",
       "       1961      1962      1963      1964      1965  ...        2010  \\\n",
       "0   28141.0   28532.0   28761.0   28924.0   29082.0  ...     43778.0   \n",
       "1  796272.0  839385.0  885228.0  934135.0  986074.0  ...   6927764.0   \n",
       "2  597288.0  628381.0  660180.0  691532.0  721552.0  ...  13963065.0   \n",
       "3  513592.0  530766.0  547928.0  565248.0  582374.0  ...   1519519.0   \n",
       "4    8766.0    9754.0   10811.0   11915.0   13067.0  ...     75007.0   \n",
       "\n",
       "         2011        2012        2013        2014        2015        2016  \\\n",
       "0     43819.0     44057.0     44348.0     44665.0     44979.0     45296.0   \n",
       "1   7212518.0   7528588.0   7865067.0   8204877.0   8535606.0   8852859.0   \n",
       "2  14660282.0  15383127.0  16130304.0  16900847.0  17691524.0  18502165.0   \n",
       "3   1546929.0   1575788.0   1603505.0   1630119.0   1654503.0   1680247.0   \n",
       "4     74305.0     73056.0     71515.0     70057.0     68919.0     68213.0   \n",
       "\n",
       "         2017        2018  2019  \n",
       "0     45616.0     45948.0   NaN  \n",
       "1   9164841.0   9477100.0   NaN  \n",
       "2  19332881.0  20184707.0   NaN  \n",
       "3   1706345.0   1728969.0   NaN  \n",
       "4     67876.0     67813.0   NaN  \n",
       "\n",
       "[5 rows x 64 columns]"
      ]
     },
     "execution_count": 3,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df = pd.read_excel(source, header=3)\n",
    "df.head(5)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Notice the `NaN` in the `2019` column? `NaN` is short for Not-a-Number, and is the way that pandas (and `numpy` the software that pandas runs on) keeps track of undefined numbers. In this case it means that there are no numbers available for some of the countries for that year. Pandas will try and guess the format of the columns for you, but sometimes it gets it wrong. Let's check and see what it thinks:"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 4,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "<class 'pandas.core.frame.DataFrame'>\n",
      "RangeIndex: 264 entries, 0 to 263\n",
      "Data columns (total 64 columns):\n",
      " #   Column          Non-Null Count  Dtype  \n",
      "---  ------          --------------  -----  \n",
      " 0   Country Name    264 non-null    object \n",
      " 1   Country Code    264 non-null    object \n",
      " 2   Indicator Name  264 non-null    object \n",
      " 3   Indicator Code  264 non-null    object \n",
      " 4   1960            258 non-null    float64\n",
      " 5   1961            258 non-null    float64\n",
      " 6   1962            258 non-null    float64\n",
      " 7   1963            258 non-null    float64\n",
      " 8   1964            258 non-null    float64\n",
      " 9   1965            258 non-null    float64\n",
      " 10  1966            258 non-null    float64\n",
      " 11  1967            258 non-null    float64\n",
      " 12  1968            258 non-null    float64\n",
      " 13  1969            258 non-null    float64\n",
      " 14  1970            258 non-null    float64\n",
      " 15  1971            258 non-null    float64\n",
      " 16  1972            258 non-null    float64\n",
      " 17  1973            258 non-null    float64\n",
      " 18  1974            258 non-null    float64\n",
      " 19  1975            258 non-null    float64\n",
      " 20  1976            258 non-null    float64\n",
      " 21  1977            258 non-null    float64\n",
      " 22  1978            258 non-null    float64\n",
      " 23  1979            258 non-null    float64\n",
      " 24  1980            258 non-null    float64\n",
      " 25  1981            258 non-null    float64\n",
      " 26  1982            258 non-null    float64\n",
      " 27  1983            258 non-null    float64\n",
      " 28  1984            258 non-null    float64\n",
      " 29  1985            258 non-null    float64\n",
      " 30  1986            258 non-null    float64\n",
      " 31  1987            258 non-null    float64\n",
      " 32  1988            258 non-null    float64\n",
      " 33  1989            258 non-null    float64\n",
      " 34  1990            260 non-null    float64\n",
      " 35  1991            260 non-null    float64\n",
      " 36  1992            259 non-null    float64\n",
      " 37  1993            259 non-null    float64\n",
      " 38  1994            259 non-null    float64\n",
      " 39  1995            260 non-null    float64\n",
      " 40  1996            260 non-null    float64\n",
      " 41  1997            260 non-null    float64\n",
      " 42  1998            261 non-null    float64\n",
      " 43  1999            261 non-null    float64\n",
      " 44  2000            261 non-null    float64\n",
      " 45  2001            261 non-null    float64\n",
      " 46  2002            261 non-null    float64\n",
      " 47  2003            261 non-null    float64\n",
      " 48  2004            261 non-null    float64\n",
      " 49  2005            261 non-null    float64\n",
      " 50  2006            261 non-null    float64\n",
      " 51  2007            261 non-null    float64\n",
      " 52  2008            261 non-null    float64\n",
      " 53  2009            261 non-null    float64\n",
      " 54  2010            261 non-null    float64\n",
      " 55  2011            261 non-null    float64\n",
      " 56  2012            260 non-null    float64\n",
      " 57  2013            260 non-null    float64\n",
      " 58  2014            260 non-null    float64\n",
      " 59  2015            260 non-null    float64\n",
      " 60  2016            260 non-null    float64\n",
      " 61  2017            260 non-null    float64\n",
      " 62  2018            260 non-null    float64\n",
      " 63  2019            0 non-null      float64\n",
      "dtypes: float64(60), object(4)\n",
      "memory usage: 132.1+ KB\n"
     ]
    }
   ],
   "source": [
    "df.info()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "`dtypes` tells us the data-types for each of the columns. `object` is any sort of `string` or data-type that doesn't fit into the core numerical, categorical or date types. `float64` means that these values are considered to be 64-bit floating-point numbers.\n",
    "\n",
    "<div class=\"alert alert-block alert-warning\">\n",
    "    <p>From here on, I will only reference the documentation for each new function I introduce as a comment in the code, with a link to an online resource where you can find extra information. This is a useful coding habit to get into as you may sometimes forget exactly how you discovered this solution or reference.</p>\n",
    "    <p>As an example, <code># https://stackoverflow.com/a/51794989</code> is a reference to <a href=\"https://stackoverflow.com\">Stackoverflow</a> - a popular question and answer site amongst developers. If you visit this link, you'll get a host of information on the line of code, or technical solution, that follows. Some references, e.g. <code># Reference for np.where https://docs.scipy.org/doc/numpy/reference/generated/numpy.where.html</code>, will leed you to the package documentation.</p>\n",
    "    <p>Get into the habit of reading reference material. Software development requires patience and you will always be looking things up. Doing your own research makes you a good coder.</p>\n",
    "</div>\n",
    "\n",
    "The complete list of columns can be reached as a list:"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 5,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "Index(['Country Name', 'Country Code', 'Indicator Name', 'Indicator Code',\n",
       "       '1960', '1961', '1962', '1963', '1964', '1965', '1966', '1967', '1968',\n",
       "       '1969', '1970', '1971', '1972', '1973', '1974', '1975', '1976', '1977',\n",
       "       '1978', '1979', '1980', '1981', '1982', '1983', '1984', '1985', '1986',\n",
       "       '1987', '1988', '1989', '1990', '1991', '1992', '1993', '1994', '1995',\n",
       "       '1996', '1997', '1998', '1999', '2000', '2001', '2002', '2003', '2004',\n",
       "       '2005', '2006', '2007', '2008', '2009', '2010', '2011', '2012', '2013',\n",
       "       '2014', '2015', '2016', '2017', '2018', '2019'],\n",
       "      dtype='object')"
      ]
     },
     "execution_count": 5,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df.columns"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "The file is currently in the `wide` format and we would prefer it to be `long`, with all the years running down the dataframe. To do that, we need to `pivot` our dataframe and create a new `year` column. Pandas has a function called `melt` to do this, but first we need to get the columns we want to use:"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 6,
   "metadata": {},
   "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 Name</th>\n",
       "      <th>Country Code</th>\n",
       "      <th>Indicator Name</th>\n",
       "      <th>Indicator Code</th>\n",
       "      <th>Year</th>\n",
       "      <th>Value</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>Aruba</td>\n",
       "      <td>ABW</td>\n",
       "      <td>Urban population</td>\n",
       "      <td>SP.URB.TOTL</td>\n",
       "      <td>1971</td>\n",
       "      <td>30082.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>Afghanistan</td>\n",
       "      <td>AFG</td>\n",
       "      <td>Urban population</td>\n",
       "      <td>SP.URB.TOTL</td>\n",
       "      <td>1971</td>\n",
       "      <td>1379463.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>Angola</td>\n",
       "      <td>AGO</td>\n",
       "      <td>Urban population</td>\n",
       "      <td>SP.URB.TOTL</td>\n",
       "      <td>1971</td>\n",
       "      <td>944294.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>Albania</td>\n",
       "      <td>ALB</td>\n",
       "      <td>Urban population</td>\n",
       "      <td>SP.URB.TOTL</td>\n",
       "      <td>1971</td>\n",
       "      <td>698647.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>Andorra</td>\n",
       "      <td>AND</td>\n",
       "      <td>Urban population</td>\n",
       "      <td>SP.URB.TOTL</td>\n",
       "      <td>1971</td>\n",
       "      <td>20902.0</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "  Country Name Country Code    Indicator Name Indicator Code  Year      Value\n",
       "0        Aruba          ABW  Urban population    SP.URB.TOTL  1971    30082.0\n",
       "1  Afghanistan          AFG  Urban population    SP.URB.TOTL  1971  1379463.0\n",
       "2       Angola          AGO  Urban population    SP.URB.TOTL  1971   944294.0\n",
       "3      Albania          ALB  Urban population    SP.URB.TOTL  1971   698647.0\n",
       "4      Andorra          AND  Urban population    SP.URB.TOTL  1971    20902.0"
      ]
     },
     "execution_count": 6,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "id_columns = ['Country Name', 'Country Code', 'Indicator Name', 'Indicator Code']\n",
    "year_columns = set(df.columns).difference(set(id_columns))\n",
    "\n",
    "# https://pandas.pydata.org/docs/reference/api/pandas.melt.html\n",
    "df = pd.melt(df, id_vars=id_columns, value_vars=year_columns,\n",
    "             var_name='Year', value_name='Value')\n",
    "df.head(5)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 7,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "<class 'pandas.core.frame.DataFrame'>\n",
      "RangeIndex: 15840 entries, 0 to 15839\n",
      "Data columns (total 6 columns):\n",
      " #   Column          Non-Null Count  Dtype  \n",
      "---  ------          --------------  -----  \n",
      " 0   Country Name    15840 non-null  object \n",
      " 1   Country Code    15840 non-null  object \n",
      " 2   Indicator Name  15840 non-null  object \n",
      " 3   Indicator Code  15840 non-null  object \n",
      " 4   Year            15840 non-null  object \n",
      " 5   Value           15291 non-null  float64\n",
      "dtypes: float64(1), object(5)\n",
      "memory usage: 742.6+ KB\n"
     ]
    }
   ],
   "source": [
    "df.info()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "`Year` is of type `object`. If it is important to convert that into a date-type, then we would need to do that. However, the file is fine for our purposes, so we can save this as a CSV:"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 8,
   "metadata": {},
   "outputs": [],
   "source": [
    "destination = \"data/lesson-programmatic/world-urban-population-data.csv\"\n",
    "# `index=False` means that we're not going to keep that index column - it will be recreated when we open the file in future\n",
    "df.to_csv(destination, index=False)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Pandas can do a lot more than just move data around. You can filter and sort and draw charts too:"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 9,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "<matplotlib.axes._subplots.AxesSubplot at 0x159148e9bc8>"
      ]
     },
     "execution_count": 9,
     "metadata": {},
     "output_type": "execute_result"
    },
    {
     "data": {
      "image/png": "iVBORw0KGgoAAAANSUhEUgAAAXQAAAERCAYAAABrWly6AAAABHNCSVQICAgIfAhkiAAAAAlwSFlzAAALEgAACxIB0t1+/AAAADh0RVh0U29mdHdhcmUAbWF0cGxvdGxpYiB2ZXJzaW9uMy4xLjMsIGh0dHA6Ly9tYXRwbG90bGliLm9yZy+AADFEAAAgAElEQVR4nOy9eZQkZ30ler+MLdfaq3pfpNaGhKTWgozQ2MhgAYMBGZBtdPAKb3g2Y2MGe/xsPGZsZjweM/NsxvYYDtuTF8boDCBkY8BmsGQBAoG61doBdbd6qeru2rfcIjIiv/dHxBcZGRlrZkRWVtZ3z9FRd2VWZXRW5C9u3N/93R+hlIKDg4ODY/sjs9UHwMHBwcGRDHhB5+Dg4BgS8ILOwcHBMSTgBZ2Dg4NjSMALOgcHB8eQgBd0Dg4OjiHBlhZ0QsinCCELhJBnIjz3ECHka4SQpwghDxNC9vfjGDk4ODi2C7aaod8H4HURn/vfAfwVpfQGAB8E8IdpHRQHBwfHdsSWFnRK6SMAVpxfI4QcIYR8hRByjBDydULINdZD1wL4mvXnhwDc3cdD5eDg4Bh4bDVD98LHAPwqpfQWAL8B4C+srz8J4K3Wn98MoEQImdyC4+Pg4OAYSIhbfQBOEEKKAF4B4H8TQtiXFev/vwHgzwkhvwDgEQBzAPR+HyMHBwfHoGKgCjrMO4Y1SulR9wOU0gsA3gLYhf+tlNL1Ph8fBwcHx8BioCQXSukGgBcJIT8JAMTEjdafpwgh7Hh/G8CntugwOTg4OAYSW21b/FsA3wJwNSFklhDyTgBvB/BOQsiTAJ5Fq/l5J4DvE0J+AGAXgD/YgkPm4ODgGFgQHp/LwcHBMRwYKMmFg4ODg6N7bFlTdGpqih4+fHirXp6Dg4NjW+LYsWNLlNJpr8e2rKAfPnwYjz/++Fa9PAcHB8e2BCHkrN9jXHLh4ODgGBLwgs7BwcExJOAFnYODg2NIMFCToo1GA7Ozs6jX61t9KFuCbDaL/fv3Q5KkrT4UDg6ObYiBKuizs7MolUo4fPgwHFkuOwKUUiwvL2N2dhaXXXbZVh8OBwfHNsRASS71eh2Tk5M7rpgDACEEk5OTO/buhIODo3cMVEEHsCOLOcNO/rdzcHD0joEr6BzA2eUKHvnB4lYfBgcHxzYDL+gO3HnnnfjHf/zHtq99+MMfxrvf/W7f7ykWi4kfx8e/fhr/7v4Tif9cDg6O4QYv6A7ce++9+MxnPtP2tc985jO49957+3ocVdXAZp3v7uDg4IgHXtAduOeee/DFL34RqqoCAM6cOYMLFy7g6NGjePWrX42bb74Z119/PR588MGO73344Yfxhje8wf77r/zKr+C+++4DABw7dgyvfOUrccstt+C1r30tLl68GHgcdd2AZjTRMJrJ/eM4ODiGHgNlW3Ti9//+WTx3YSPRn3nt3hH8xzde5/v45OQkbrvtNnzlK1/B3Xffjc985jP46Z/+aeRyOTzwwAMYGRnB0tISXv7yl+NNb3pTpCZmo9HAr/7qr+LBBx/E9PQ07r//fvzO7/wOPvUp//0c9YZZyKuagdEcv+ZycHBEw8AW9K0Ck11YQf/Upz4FSine//7345FHHkEmk8Hc3Bzm5+exe/fu0J/3/e9/H8888wzuuusuAIBhGNizZ0/g99QbBgCgqukYzfEhI454oJTibx47h584uhelLD9/dhIGtqAHMek08RM/8RN43/veh+PHj6NWq+Hmm2/Gfffdh8XFRRw7dgySJOHw4cMdfnFRFNFstiQS9jilFNdddx2+9a1vRT4GVtArqpHAv4hjp+GFhTJ+9wvPoKgIePNN+7f6cDj6CH4/70KxWMSdd96Jd7zjHXYzdH19HTMzM5AkCQ899BDOnu1Mrzx06BCee+45qKqK9fV1fO1rXwMAXH311VhcXLQLeqPRwLPPPht4DKpuXhhqGi/oHPGxtGn2gJh0x7FzMLAMfStx77334i1veYvteHn729+ON77xjbj11ltx9OhRXHPNNR3fc+DAAfzUT/0UbrjhBlx55ZW46aabAACyLOOzn/0s3vOe92B9fR26ruO9730vrrvO/w7EZugad7pwxMdSRQPQOo84dg54QffAm9/8Zjh3rU5NTflKJuVy2f7zhz70IXzoQx/qeM7Ro0fxyCOPRH79VlOUF3SO+Fgpmwyd3elx7BxEllwIIQIh5AlCyBc9HlMIIfcTQk4SQh4jhBxO8iB3GlSdNUU5w+KIjxWLoatcckkF8xt13P/dc1t9GJ6Io6H/GoDnfR57J4BVSukVAP4EwB/1emA7GTZD501Rji7AJBdGDDiSxReemMP/87mnsWTdCQ0SIhV0Qsh+AD8O4BM+T7kbwF9af/4sgFeTLpOmnFLHTgP7t3MNnaMXrJSZhs4ZehqoqObncn5j8JJRozL0DwP4TQB+Z8g+AOcBgFKqA1gHMOl+EiHkXYSQxwkhjy8udoZPZbNZLC8v78iizvLQFUWB3jT//Vxy4egGyxWmofPzJw2wz+XC5uAx9NCmKCHkDQAWKKXHCCF3+j3N42sdVZlS+jEAHwOAW2+9tePx/fv3Y3Z2Fl7Fficgm81ictceMGWLN0U5usGyLblwhp4GKqygDyBDj+JyuQPAmwghrweQBTBCCPkbSunPOJ4zC+AAgFlCiAhgFMBK3IORJGnHb+tZduhyfLCIoxssl4e/oC9s1PGFE3N4xx2XQRT6O05Ts4jWwsbgMfTQd4JS+tuU0v2U0sMA3gbgn13FHAD+DsDPW3++x3rOztNNEkDd8SHkDJ0jLhpGE+u1BgBAHWIf+j89N4//8qXv4fNPzPX9tZnkMr85eAy960sbIeSDhJA3WX/9JIBJQshJAO8D8FtJHNxOhHMYhGvoHHGxasktQDs5GDawz8mHv/qDvg9Q2Rr6ADL0WINFlNKHATxs/fkDjq/XAfxkkge2U8ELOkcvWHYU9GFm6CwW48J6HZ9+7Bze+a/6J9WyO+dBbIryLJcBg9NqxuxRHBxRwfTzrJQZag29rhsQMgQ/fOUU/udDJ7FZb/TttasD3BTlBX3AwKxmOUkYaIa+VtXwn7/4HLQhLhrbEcyyuHc0N9wFvdFEVszg37/2aqxUNHzi6y/27bVr1p3PYllFszlYrUJe0AcMbFx7oiAPdFP0myeX8YlvvIhnL6xv9aFwOMAY+t6x3FD70OsNA1lJwA37x/CvX7obn/j66TaHWJpg7rOGQbFa1UKe3V/wgj5gYBr6ZFEeaIbOigXffTpYWKloyBBgZkQZ6iyXmlXQAeDXX3M1ag0D//OhU/15bU3H3tEsgMHT0XlBHzDUrUI5npcHWkNnUgsv6IOF5YqKiYKMnCQMNUNXG01kJbN8XTFTxD237MfffPss5tZqqb4upRTVhoFDkwUAvKBzhKDukFxqA+xS0AxW0PvXjOIIx3JZw2RBgSIKQ83Q6w6GDgC/9mNXAcS0Mab7uk1QChyeMgv6oOW58II+YGCSy0RBRsOgA9t0ZMVigxf0gcJyRcNEQYayA1wuzoK+byyHn335IXzu+CzOLldSe13W1zo8mQcALHKGzhEE9iGcKMgABndatMXQB/P4dipWKhomizKyogDNaA6cCyMp1B2SC8ObbtyLJgV+MF/2+a7ewfpa4wUZI1mRM3SOYDgZOtAKAho0sKGVjRpn6IOEpbKKSYuhA8Ob51LTDGRFoe1rY3kJAOzog1Re1zrv87KAXSPZgZsW5QV9wFBvNCELGRQVc4i3NqAMXeUMfeCg6U1s1nVMFhUoIivog0kIekVdN5CV2wv6aC79gs6MCnlZwMyIgoUBy3PhBX3AUG8YUKQM8tbJOqiJiy0NnRf0QQHzRE8UZCgWex1Whq42mh0MvZSNXtAppV3tXWCRA3lZxK5SFvOcoXMEQdUNKKKAvGwy9EHdWsQ0dN4UHRywlWhTRbnF0IfU6WK6XNrLl5AhKGXFSDLgj//pN/AXD8f3rVe1luQyPaJgcVMdqIU8vKAPGFizp6CY7KM2oBo696EPHthy6ImCYjtA6sMqubhsiwyjOQlrIdOblFJ8f34Tz13YiP26jGDlZQEzpSw0o4m16uCQGl7QBwyqZcdqMfTB/ECqOvehDxrY2P/kkDN0Sqk1KdpZvkZzUqjkUlZ1GE3aleWQEaycLGLXiAJgsIaLeEEfMDCGzjT06oBOi2o6d7kMGlh0brvLZTAJQS9oGBRNagbYuRGloDNG3U1Dk0kuBYuhd/tz0gIv6AOGesO0YxW2CUMvq/rQep23EhfXa3hmLl7w2XJZhZAhGMlKQ90UZTKSn+QStaB3w9DZXEhOFjBTMhn6IDVGeUEfMDBtMCczDX1QGbpZKJp0cBu32xl/+rWTePenj8f6nhVrSjSTIUNtW2SzGopHQR/LS1ivBZ+PazXzTqaiGbHzkqqamcMuCxnM2JILZ+gcPqg3mlDEDGQxA0kgA8/QAd4YTQMb9UZsB9FSWcOkNZBmN0WHUENnfYGs2Fm+RnISNmqNQOeJs4kZl6VXNQN5WQAhBHlZREkRB2q4iBf0AYMzoyIviwOsofOCnibURjP2rsyViorJolnQh5mhs2lNP8lFM5qBF7I1hyQTt6FZswo6w6ANF/GCPmBQG027oVWQB3drkaY3UbBObO5FTx6qbljJftH7E2YwlykD2E3RIWTo7ELn1xQFgoeL1h22xrgMvaLptgMNAGZKgzX+zwv6gEF1MnRFTLWgf+ThU/j0Y2e7+l5VNzBlNYW4dTF5sEIcp6m54pBchropyiSXLgv6apvkEo9dezH0ec7QOfxQd4w052Uh1YbjX3/rDL7wxFxX36vpTUwVzYK+EdKE4ogP5uSIyrBV3cCmqjs09OGVXOq25OLtQweCC/patYE9o1kIGRJbcqm6CjoL6BqUadHQgk4IyRJCvkMIeZIQ8iwh5Pc9nvMLhJBFQsgJ67//K53DHX44R5rzsoBqSlku9YaBC+t1LJW724mo6k27eHCGnjxYIY866cmmRCeti6wsmOfQMDZF6yEaOhAiudQ0jOdlTBXlLpqiOnJtkosCVW8OTKaRGP4UqABeRSktE0IkAN8ghHyZUvpt1/Pup5T+SvKHuHOgG03oTWrfLhdkMbXbufMrVQDdB/RretOWXAblZB4msEIetTHKpkRZ7LIoZCBmyFAy9FoEhh40/r9WbWAsL3XN0PeOOSUXa7hoo26/9lYilKFTEywxXrL+G4z7iyFDXWfaoPlryaXI0M8smwW9rOpd5cWoehMjWQmykOFN0RRgM/SIDJtNiU5ZLhfAdLoMY1NU7VlDNxn6dEnpyraYc2ropfjj/6sVDUZKw3iRNHRCiEAIOQFgAcBXKaWPeTztrYSQpwghnyWEHEj0KHcI3LeSBVlMTUM/s9Ra08VS+qKCUgrNaEIWMxjJidy2mALiM3Tzd8gYOmAO3gxlUzRgUpRF6AZFUqzXGhjNS5gpKViMee7XGq6mqD0tGv1O+hX/9Z/xh196PtbrRkWkgk4pNSilRwHsB3AbIeSlrqf8PYDDlNIbAPwfAH/p9XMIIe8ihDxOCHl8cXGxl+MeSqguhp5X0rMtnnHsXYx728micxUxg1JW4nkuKYCx0KiLwt0aOmAO3gyj5BKkoZvRB6IvQ6eUmpJLTsJ0ScFyWY3Fliuqy7bIJJeIn6GaZqDWMDDhuJNKErFcLpTSNQAPA3id6+vLlFL2L/o4gFt8vv9jlNJbKaW3Tk9Pd3G4ww0vhl7VjFQ66GeXq/aFIy5DZxces6Bzhp40KKWxGfpSWYMkmMWMQZGEoWyK1jT/SVEAGM3757lUNAN6k2Isbxb0JgWWK9HOf6NJoerNNoZeVEQUZCGyF91eQpLfooJOCJkmhIxZf84B+DEA33M9Z4/jr28CkM79xJDDzqiwmqI5WbBPoqRxZrmCowfGAMRvjLIpUVnMYCQrcZdLwmgYFOwaHrUgr1RUTBRkEELsrynDytB1A5JAIAo+BT0goIs1S8fyckv/jliMnftEnZgZyUY2L7A7qfHC1jH0PQAeIoQ8BeC7MDX0LxJCPkgIeZP1nPdYlsYnAbwHwC+kcrTbHGeXK1gPCMNvDUy0JkUBJC67qLqBC2s13HJoHITEL+huhs5dLsnCaVWMWpBXHFOiDGZBHz6GzhJJ/RBc0M2vM8kFQGQdncVwOG2LgKmjL8Zl6CkV9FDbIqX0KQA3eXz9A44//zaA30720IYPP/PJx3DXS3bjA2+81vNx1cXQ89ai6KqmJ3oCnF+poUmBK2aKmMjLsSUXztDThdOZEkdymXSdI4ooDKXLpd5oeiYtMozmJFxa92bMdkHPy3aeeVRC48xCd2JmJIunZtci/QyboW+V5MKRHJY2tcDi2eretwaLgOQZ+lmrIXposoCpYnzrFmONiihYOxw5Q08SziIeXXLR7GAuBkXKDOUKOtVnWxGDydC9z0kWncs0dCB+QXdLLrtKSuRp0daaQF7QtzWMprk2qxyQnuj219pLLhJOXGQe9MOTBUyXlO4ZumC6XGoNAw1j+JjgVsEpk8SxLbqLxLAy9JrPPlGGoAjdFkOXkJVMQhK9oPtILiNK6GebYbWigRCkNoTEC3qfwE6GoF+621+bt5dcJM/QS1kR43nJHH/usqArkulDB4Ay19ETQ1yGXm8YqGiGna3DoEhD2hRtGJ5JiwwsQtfL8smaoqygTpeix9/6Si6WdBNlc9FKVcNYzpxSTQO8oPcJ7GQIYtsdTVElnTV0Z5arODxZACHEnpaLY41UXQwd4BG6UXH/d8+FbptvY+gRCvKyz2388DZFm6GSC+A9LbpWbSAvC3afajqG5Fi1F0S7NfTom4tWK43UHC4AL+h9A2PmwQXdYugO2yLQYvdJ4cxSBYcm8wBMhlJvNGNdNNqbouZFh3vRw9FsUvyHLzyD+797LvB5ahtDD/+9rJRby6GdUMThnRQNklwCC3rNHCpimBnJRh8KapjneL7D5cLyXCIw9Epn8zpJ8ILeJ7BMlnJANgtj6K0FF0xDT46ha3oTs6tVXDZVAAD7Nj1OY7S9KRo+as1hYqmsomHQ0OnPdg09vCAvWYMx7qZoVsrE3nq0HWCuafQv6GM5833wsgivVRsYdThM4jB09jnsdLnEYOhWjkxa4AW9T2CZLHEYel5JnqHPrZmWxUOTZkFnnf44jVHVwdBLFkPnXvRwzK3VAAC1kCLtLMJqLIbu9qEPKUOP4HIBvBm6GZ3rZOgKqhGXRdd8JJeSIiInRZsWZYu80wIv6H0CK8q1huGbHaHqTchCBhmrYZKXkrctsgyXw5bk0h1Dbw0WsQ8P96KH46LljQ5rcrP3V8yQiBq6Fczlti2KGWh6vDV22wFRmqKAd0FftaJzGaaL0dMSW7bFdsmFEGJtLgr+GZRSk6Hzgr794ZRa/BIU6w3DllsAM9NaFjOJJi6eXWp50AHE9uICDpfLNmXob/3Io/izr73Q99e9YDH0MOcJY+hjeSmSw2m5opkNaqW90Nh7RYeMpddDbIthTdHRXKugMrkkyvlf1XTIYsbTobKrlMVCSOJiWdXRMGhqOS4AL+h9Q9VxS+d3e6d6NHsKspCobfHMchUFWbBzs8fzMoQMiSW5tAq6gKLCmqLbg6HXGwaOn1vFCwvl8CcnjLgMfSQnRdLQl8taR44LMLx7RcNcLqWsCEI6+zqUUqzXtHaGXoquf1c1o0M/t3/OiBLK8tPOcQF4Qe8bnC4Sv4LudaLmZTHRpuiZ5QoOWZZFwIwbnSjEW8Xl1NBFIYOCLGybadEXlyqgNHnnUBRcXGcaekSGnpMiSS5eU6KAY6/oEDVGWRJlEEPPZAhKSmeEblUz0DBom4Y+HUNyNPeJeqelzJSUUIbemhJNb7MRL+h9grOI+zld6g2jo3ufl4VEi8/Z5SoOT+Xbvhan0w+02xYBc6nAdmHopxdNySnJi2RUXFgzP/BhzhN2wRyNzNA7p0SB4WToqt4Epd5Z6E54ReiyYKwxh+QynpchZkik87/W0Dsaogy7RrKoaMHTouz1uctlCODUwf0Zemf3Pq+IiQ0W6UYT51fMoSInpmKO/6u6ATFDbC1xO20tOrVoSi1bydDDinS9Yb6/eUWMxK6XK1rHlChg9jiA6ImN2wFB6+ecGM1JWHMVdDb2P+pg6JkMwVQxXC4BTBLgJ7m0onj9WfpKxXx97nIZAjh3g/pdxVW92RELamroyRSfC2t16E3aUdC7YeiyY7lAKSttm0nR03ZB72+RaxhNu2hE8aFnJQFZUYjkI2cauhusoA/Tkgt3gJ0fvCJ02d/HXDkqMyPRzv+aa5+oE7uszUWXAgr6KtfQhwcVVQfrWQUz9E7JJSl54IydsuiSXEoKlspaZHubqjftYgEAI9toa9EpS3Lpd0Gf36iDUvO9CivSpvSWQU7O2IvD/VDVdHOlmUeRYOfSMDF096yGH8ZyckdBd0bnOjEdkaFXG7qvhr57lOW5BDD0qrlVyu1GShK8oPcJFU23R37jNkWTkgdsD/qUS3IpytCMZuTG5nZl6JRSm6GntXzbD8zhcvl0EbVG8FrBOAx92RoqmvJoitqSyzAx9IiSC0tcdIJF5zqbokB0hl5VjY7oXIbdFkO/6JPDDpgMfTzf6UZKEryg9wlVzcC0lfng2xTVjY7g/kKCi6LPLJl7RJnex9Da3BItdU4z2kevt8te0fkNFRXNQCkrtklg/QDzoF8+VQClwY1KxtCzklnQg4p/yznhoaFLw9cUZXJVVMnF+d4xhj7iklymiwqWKyr0kAho0+XiXdALioiRrOi7WANIf0oU4AW9b6ioJkPPkAAfeqNdygAYQ0+m+Jxdrtgpi060rFtapJ+j6kYbQx/JmS6XQZ9IZOz8pXtHoRlN263TDzCHy+XT5t1REGtWdXMjT1bKoEnNHaN+CFqYMIxNUXbHEjQpCpgF3Z2bs1bVkJOEDnY/XVJAaeu99ENV85dcAGDPaC6Yoaec4wLwgt43VFQDBUVAQRF9m6K+GrqmJ1Isz1gF3Y24uxU1K6KAoZQV0TDowDffTllTsjfsHwWQfM58EC6u1zCSFW0mHdQYdTJ0IDhCd9M6l1jqpROtgj7Yv5c4sBepRyjoQPu06Jpr7J+B3TmH6ehBDB0wdfQghr7MGfrwoKLpKCgiiooY3BTt8KGLobfoUWA0Kc6v1HDI5UEHHAFdEZ0uJoN0NkW3R57LqYUyCrJgJ032U0e/sFbH3rEccrL5vgUVdFNDz9hFK0hHZw6ovEejLRvh+7cb3DsD/OBZ0GuNjoYoEC3+QtOb0Js0sKDvGc2Ga+gpDhUBvKD3DebYsIiCIvoWEvZBdqJgJS72uobuwloNmtH0ZOijOQmSQCIzdNWDoQODn+dyeqmCy6YL9uKQfnrRL67XsGc0a0sFQUVWtQbMssx2qPlfzP0iXYHhZOiqa6uXH+yCXnUydK3Dsgi0PORBBb2VtOgvuewezWKprHpKeUaTYq3WSDXHBeAFvW8oqzrytuTS+WHWDZMBuE/UXEKJi2etPaJuyyJgpsXFWRat6e1b10e2ydaiUwtlHJkuOi6S/ZRc6tgzlrPftygMPYrk0tpz6VHQWVO0j1JYWdXxR1/5nr3qLWmwwhq5oEeSXMLzXKr2cotghg54WxfNBm26HnSAF/S+oGE14IqyiKIieLJt5jfuZOhsDV1vbLIVm9vJ0AEzRjfqtKibobO9ooPsdKlpBi6s13D5VNFubPVLcqk3DKxUNOx1MvSACzSLgIgimVQ1c6rU+ftg2Iqm6Ie/+gN85OFTePTUcio/P05TFPCSXDoLelYSMBKyLJpd/IM19BwA7+GioOZ1kggt6ISQLCHkO4SQJwkhzxJCft/jOQoh5H5CyElCyGOEkMNpHOx2hZ2jrIgoyN4auj0w4dEUdf6MbnF2uQJFzNh+WTfYbtEo0PT2mN/tsLWIhXIdmSm03tM+MXSmq+4ZzbUKekCRbTH08ElP1qjz8jaLGYIM6Z/k8v1Lm/j/Hj1jH1ca8CM+brgLOqUU667oXCemS0qg5FjzyUJ3gjF0Lx2d5bhseUEHoAJ4FaX0RgBHAbyOEPJy13PeCWCVUnoFgD8B8EfJHub2BivgBdmMm/Vyudjde9GbofdafM4sV3FoMm8vz3BjOiZDVwSvpujgMvTTS6ZlcSsY+kXLg753LGdfsGsBungchl5R/a10hBDby542KKX43Qefse8UkoqrcCPqpCiL0GUFvdYwoBnNjqEihumSErhxiElbYS4XALhkZfY4YUfnbrWGTk2w8GjJ+s/tobsbwF9af/4sgFeTNMehthmqDieCn23RbwKOMbpei89ZKzbXD1MlGUtlDU2fbUpOdE6KDn4mOktZvGyqYGvo/Rr/v2Axtr1jLcklkoYuRpBcGoa9qtALipjpC0N/8MQFfOfFFfzGa68GgMQC5dyoNYy2rV5+yGQIRrKtPJdVe+zfu6DPlLKBDL21rcj/vS4pIgqy4M3QB0VyAQBCiEAIOQFgAcBXKaWPuZ6yD8B5AKCU6gDWAUx6/Jx3EUIeJ4Q8vri42NuRbyMw/a1oNUW9JBfn4mUnGEPv1TN9fqWGA+OdDVGG6aICo0ntW8MguLNc8rIAIUMGuil6arGMfWM55GShxdB7dA5FBZsS3T2aRVZmMor375NSajN0ZnEMynOpqrq9TNwLiiik3hTdrDfwB196HjfuH8XP337IPK6UCrraaLfMBsEZ0MWatEGSy8KG6jvv4bd+zglCiK8XfaUP0blAxIJOKTUopUcB7AdwGyHkpa6neF0uO94ZSunHKKW3UkpvnZ6ejn+02xQV+3bNbIo2DNrRqPLz1zI7Wi8MXdUNK8DJ3wM7ZS+LDi/oboZOCBn48f/TixV7SjOpvkRUXFyvYaooR5JR9CZFk5rngRKBoVcCEgABcw1d2k3RD/+fF7BUVvHBu18KUcggn2BCqBth+0SdcBb09VCGrqDWMHzvLKJILoD/tOhqxZxSDfpdJYFYLhdK6RqAhwG8zvXQLIADAEAIEQGMAlhJ4PiGAi2vsNhyrbg0cdWvKZqAhs5O6lEPDy5DnM0t7iwXwJRdBrUpykK5jkwXAQBSCrtag3BhrY49lgPCllx8Ckerl9Iq/kGZ6IiEOJcAACAASURBVLWAtWjmz0lXcvnepQ3c9+gZvO1lB3HjgTEAbLo5paZoyD5RJ9oYuvV/P4Y8HZJnHkVyAfynRfsxJQpEc7lME0LGrD/nAPwYgO+5nvZ3AH7e+vM9AP6ZDnqwRx/Bru4FpbWD0327X/cZmEhCQ1/3CSVywp4WDWmM6kYTRpO2MXTAbIwOKkNnoVxHpls9hKR3tQaBDRUB5sVEzBBfl4vqcHFEcblUNN1zSpQhzaYopRQf+MKzKGVF/KalnQOmJz6t9zZsn6gTzoJubysK0NABf0ITRXIBTKfLwma9I+irH1OiQDSGvgfAQ4SQpwB8F6aG/kVCyAcJIW+ynvNJAJOEkJMA3gfgt9I53O0Jm6Fbo/9A55IL9qF1u1yEDEFWyvT0AYnC0KciTMsBJjv3Os5BllzYlqLLLYYOJL+rNQgXrbF/hqwk+LpcvBh6YFNUNZAPYKxpMvSvv7CE75xZwW++9pq2gZlCgpHPbtRiMHRnhK69rcjnMxCWZ1TTzH0GYReT3aNZNGnnz1mpNlLXzwEgNGmdUvoUgJs8vv4Bx5/rAH4y2UMbHjA2npcFh+TiLuj+E3AF2T8uIAqiFPSSIkIRM6Hj/6zB5mbopayE8yvVro8xTbCUxSOOgm7GEqd/AdqsN7Cp6jZDB6yC7lOkWfFVpAwkIQMhgM0DrYwgPyii/2v1CtbsvfPq9n5YTk4u8tkNr7wjPzgjdNdrjbbpWzdakos/Q89L3n5/J5xedCazASZDP+wxpZ00+KRoH+BsihZ8GLoaMDCRk4XUNXQ2/h8W0MUY+naSXE4tVlCQBewaaWWG5+XkdrUGwR4qcjD0nJzx1cWdDB0AsmLGV3KhlKIWkgBoMvR0/p3sHHZfUPJpFnS9iWzExqIzQtfMcfFnyGMheUZm8zl809DuEWta1KWjrw6Khs7RO6qa2ZkXMsShobtdLv4DE4UeM9GjFHQgfFoOaDH07dQUPbVYxmXT7TnwBUVAtQ+2RcZi9zoZegBrdl/Yg9i8ZuX/BDJ0yf+C0Cv8gsGSzPB3Q20YdmhZGNj5vlZt+Oa4MNjLon0Yek3TQxuiQIuhOwu6pjexqeqpB3MBvKD3BWVVt4dZ/NITg1Zr5RUhEcklqCkKRBv/1wzzg9rB0HMSypoeaTCp3zi9WGmTW4CtZuj+RbqDoQc0NdldW5CNLysKqTH0qqZDETMQXTkyJkNPz7YYVUNnBXy9Fl7QAdO66BfQFZaF7nxNRcy05bkwD3zawVwAL+h9QdUxnu3fFPUe/QeSYehFRYTkEeDkRJSALlvj7ZBczNz2cp93dYahphmYWzNDuZwopFh0nLi4VkOGALsca/+CirSboStSxncwqNpgzfYQH3pKDL2s6vb57ESakovZFI3H0NdrDazVgiUXwGxo+uWZRy3ohJCOXPSVPuW4ALyg9wUVzbBvi32borr/SHNO9k5ojIr1WiNUbgFMhr5c0QJ3K7KC09kUtTLRB0x2edHaUnRkpj32IK/0x+Uyt1bHrpFsG4s1ZRTv91jt0NCDGHqrN+MHRRRSc7lUVO+GbE4SU7YtRtfQgegMfd9YHhfWap7TomHr55wwveitPJd+5bgAvKD3BRVVt3VGNtTiZrJBI82FgFt0ZskLwkatESq3AMB0UTZ3KwaM/2u+DH0wA7psy6KLoeelPjF0hwedISdlfONzOzX0jK/LpRJh2CXdpqjhWdCZgyiNUZS4k6IAY+je24qc2DuWRVUzbIujE9WQiVwn3NOi/YrOBXhB7wsqmtE2/OG1hk7V/bVBPzb56MklvPr//Rc8M7ce+PomQw9nF1FWcflJLoMaoesM5XIir5gyVtqaP1ts4UROEnyLtHtnZk4WfJuaVS0CQ5fM70+juFY13XNKNScLaCawNtENSqm9QDsKGImZX69D05uhDH3/uPl7mlvrTEusNYIncp3YPZrF/EbdPrdYMNegDBZx9IiqqqPo0DkLiuDhcvGfgPNjk989swrA+wR0Io7kAoTvVgQAWWg/uQd1ycXppVYolxPsw5mWRxswC9CFtVqbwwVgg0UhDN26YAZLLhE0dOvnaAEyWrfwk1zYoFPSOnqQtdcLJcWM0D1rzUd4rZ9zgg1/eX2eKmo02yJgOl0aBsWyVchXKsGxA0mCF/Q+wGyotE6GgtwZoRs0MJFXRNQanWzy6bk1AK1bOj9ELehTxfCALjsVUvJm6JvqYDH0U4tlO5TLCXbHlGaey2q1AVVvtg2YAMFWRDdDD2qgViIERqW5V9S3KZrSzlZ7/VzEwSIWoXvW2tYVrqGbv6cLXgw9om0RgL1EhlkXV6saStlwU0IS4AW9Dyir7bemXpJLkB2rIAugtHPLzVOzptSSdEGPxtD9mqKDw9DNUK5OyyLQYuhpbi2yPehjLg1dDnC5NNoZepCPPMoWnTT3ivo5P9JKs/TLOwrCaE6y9+n6RecyTBRkZKUM5lbbCzqlFNUYkgu7gF+0GqMrfRoqAnhB7wuqrvFsr0z0eqPpaVkEHGzSUXzmN+pYsArvakBBV3UD9UYzUkEvKCLyshBoXdQco+lODOKSi0sbdVRdoVwM/dha5Fw950RWNCOUvdxEdd2AkCG2KyYr+fvIWVM0OA89vb2iZT/JJa2Cbl2UWE58FIzmJPtzEqZhE0KwdyyHC66NQ2YPApElF3tz0UaLofOCPiTQ9CYaRvs0n9caunpQU9TWJFvfw9g5EOxKiTolyhA2XKT6MHRFFKCIGWwMkIZ+csHKcJnxYOh92FrEGNqeDobuv7hCbTTbJiFNDd2nKWqdQ0Hui1bAV/INyoqvDz0dySXq+jknnOd9mA8dMGUXN0OPmoXOMFmQIQnEvqCvVLS+TIkCvKCnDmcwF0PcpqhX8Xl6bh0ZAhyZLgRKLhsRp0QZpovBBb3F0DtP7pGcNFAMnRX0KzwKeqvopCm51CEJBFMFpe3rQZnodd1oe2+zUiZAQzcgCaRjJsCJtBi6qjfRpPBcf5fWEu6gADs/jDp08zANHbAK+lr7cFHULHSGTIZg10grF92MzuUFfSjAbumdt8Vekovq+iA74cV4np5dw5UzJewbzwdKLmGxoW5MFYPzXFhhcDN0wMpzGTCGPpIV7eUdTtgXyRTzXC6u17B7NNsxLKbYrLmz4HUwdEmA3qRoeMgztQjDLmk1Rdkdpt+kKNCaZE0KtYZ3Qz4I7LxXRP+kRSf2juWwVFbbfjdRs9CdMKdFLQ2dSy7DA3YyuCWXimvwwvwg+xV0lv9i/ixKKZ6eW8f1+0cxWZATlVxGHRnSXmAMXRI6J1pL2eDv7TdOLpRxZKboGXlasDX0dJuie136OdBi6F4Fve7yWbeWXHQ+txKyrQhoTZwm3RRlhMRLv2eFL+k1dGpA3pEf2Hkf1TLInC7OwaC4kgsA7B7N4dJ6HVVNR73R7ItlEeAFPXUwJpNv86GLaNJ2D3Q9IKPCLQ9cXK9jqazhhv2jGM/LWK34F1FW0MOm5JzHFiRDsAXRXkVyZMCWXJxarOAKD4cL4GzcpXe8F1yLLRhsycWToRttzfEgDbyq6aHTi4zNJi25VAI88G4CkhTYRS3qpCjQKuhR5BbA4UV36OhMGouzD5TluSyX2ZRo+kNFAC/oqaPq2CfK4JWJHmhbVNqLD2uIvnTfKCYKEsqq7vuBjcvQi1ayo99koepaEO3ESFbCxoBo6OvVBpbKqqd+DjgzddJh6EaTYn6j3jH2DwQX6Q6GHrAouqp5j963vZbo/1q9wJYSvbJcUhra6ta26Px/GNi0qNOLXo3gJnJj90gWqt60s4Q4Qx8StE781klYYgXdwWbreoBt0SUPPD23BiFDcO2eEbvZ4sfS7ejcbLSTsaCYqYl+LN1rQTTDSG5wGPrJxU0A3g1RwNRUMyQ9hr5UVqE3acfYP9ByufgxdKeGHsSwq2p4AmBaDN1vuQVg9lfEDElhsCjepCgQn6HvGsmCEGDWUdDZZzguQweA5y5uAOhPjgvAC3rqqPo0RYEWO2xYi5d9bYu2a6DF0K/aVUJWEmw7lJ/ThUXnujOr/eCXBsmgBvjlS9nBcbkEOVwA03NcSHGv6Kx1y77fo6BnA10u7Qw9F8DmK1vYFPW682QghFgJoSm5XLqwLUaxLAJmiuiuUraNoddiulyAlhf9uQtmQeculyFB2Tqp864sF/Mxs2iGZVTkbB+6AUopnplbxw37RgG0rvyrPo3RqFOiDH557QwmQ/cp6IqIeqNpN063EqcWK5DFDPaP++9xzKe4V3R21ZxOZLfwTrCC7sWa3Qw9aFF02Po5wNEUTfh3YjdFfXJk8rJ/Xk236EVyGYuhYe8dy7Zp6N1ILmyYjDH0SV7QhwNVD3tX0cWCw/y1mQyxt8DMrtawWm3g+v3tBd2PoUeNzmUIa2ipDcNfQ8+xCN2tZ+knF8q4fKoAwSNfniHNrUUs4GmfR0EP8qG70wSD9PaKpocWGVtySVjPDrItAtZSlqQ1dHv9YReSS0SGDgD7xvNt06LVLiSX6ZICIUNwerGMDGnFS6cNXtBTRkUzQEj7baIta2iugh5wK5mXBVQ0A09bUbk3WAV9PBJDj84siq5jc0Mz/Juidp7LAOjozLIYhLyc3l7R2dUaJgqypyQS5nJpZ+j+tsWqGp7RnU2JoYdF9+ZSeG+ZA8hrCYwfdo1k8SNXTeOHLp+I/D17x7K4uNaKv61qBsRM8ACXG0KGYKakoEnNhmicY+4FoUdICDlACHmIEPI8IeRZQsiveTznTkLIOiHkhPXfB9I53O2HiqojLwltv1C3rGEzj4BmT142t8A8NbsOSSC4encJQCsSdNknITGu5NKLht5acrG1DL3eMHB+teprWWQoyGJqWS5zqzXb0+xGuMvFQ3JxyTN2YFRAdC5gzgsQkgZDNzds+RW5NNbQ1WLsE2WQxQz+6h234eaD45G/Z/9YDprRtDONoq6fc4Pp6P3SzwEgCnXTAfw6pfQ4IaQE4Bgh5KuU0udcz/s6pfQNyR/i9kZV09uWWwCdRdO9GNgLeWsN3dNza7h6d8l+rihkMJqTEtPQvSyVTmiGf0RBK6Braxn66cUKKPVviDLkFSE0qbJbzK5WcdWukudj7ILoz9C9bIvtxV/VzUZ6WFOUEGJtLUpeQw+6mORk0XZYJYWgWY0kwbzos2s1zIxkrV5F9Ltchj2jWTwB9C3HBYjA0CmlFymlx60/bwJ4HsC+tA9sWFBRjQ6dkYVtsYapajd7/H8dBWu69KnZdVy/b6ztscmCHOhy6aYp6qeha3qQbXEwthaxtXNesblOmC6X5C8+lFLMrfkz9EyG+Ga0dDJ0b8ml1agLZ45p7BUNc9gUUpBc4uwT7QX7XF70SowsdCd2j5g/px+bihhiXe4IIYcB3ATgMY+HbyeEPEkI+TIh5Dqf738XIeRxQsjji4uLsQ92O6Kidp4MmQxBwbH4OcpIc14W8L2Lm9is67Z+zjBekD0ZepzoXPt1FNYU9ZFcrGXWXmBe31WPnYz9xMmFMgiB52ILJ9LaTr9c0VBvND0dLgxeiyt0Zl91XDD9cl9aoW/hzFER/QO+uoVf0iJDLoX3Ns4+0V7gnhataYZnCFkYmBe9Xx50IEZBJ4QUAXwOwHsppRuuh48DOEQpvRHAnwH4gtfPoJR+jFJ6K6X01unp6W6PeVvBz4ngDOiKYsfKy4K90ur6fa6Cnpc9NfS4U6KAM+PER3LR/ZdZs2k4P/mnXzi5WMaB8Xwom/MKSUsCrBDsC7BM5jzW0NU9sub9GDqTa6IUGjNTPWnJJVi/zwcsNu8Wbo9+WhjJSihlRZuhVzUDeSm+5GJr6IMkuQAAIUSCWcw/TSn9vPtxSukGpbRs/flLACRCyFSiR7pNYY5nd56Ezkz0eiN8Ao4VWlnMdGizEwVvDZ1JH6MxTighQ5CThACG3vRl6FlJQEFOT5eOilML5VD9HEiPodtDRQEMPeexhk71sK/KQgaEdGroQeFYbpgaesIMXfNebsGQhpxV19odQGnCjNFlBT08M8cLA8nQiZnC9EkAz1NK/9jnObut54EQcpv1c5eTPNDtiora2RQFXAw9gm2RnVAv2TPS4SwYL5gBXe78lW4YOju2coCGHmTfGg/Q8/sBo0lxeqniuaXIjYIiQm/SxAeh5tbMoSIvDzqDInUurrAZuuP9JYR4LoquxgiMUqRMKmmLQReTnCzYjdukELQEJmk4c9G7dblcNlVAVspEIhdJIcp9xB0AfhbA04SQE9bX3g/gIABQSj8K4B4Av0wI0QHUALyN+qU77TBUVO+IU+eSiyi2RcaGbnDJLYDZFNWMJipaewM2bhY6Q1EJZuhBbpyJLS7os6tVaHozMkMHTAYmi8mxqNnVGkayYuAwSc6jKerF0M2/Zzpsi3GmF1NpiqrBwWDsva01Ok0B3aJfGjpg6ujfPbMCoHPJe1RMFhU88buv6YszhyH0KCml3wAQ6IqnlP45gD9P6qCGCX5ugKIi4oLFAKIOFgGd+jng0K4rWtuHpxeG7lfQwxj6Vhf0sAwXJ5yZ6GP+cndszK3WAvVzwGSwbsnFbxIy58Hm7cGeSBp6Ck1RTUcxUEO3Ip9DmqdxELTVK2nsG89ho65js94wrcddMHQg3nRpEuCToimCUoqq5s1QmA0RcGa5+P/yWfG5fn9nQWca3bKrkHZd0OXOnaeA+e8JynIBTM+t35BTP2AX9GlvD7gT+ZS2Fs2u1gL1cwCeMgrTud2NPy9HDLu7i1Jo0mHo3lIiQxqLooMippMGc7pcWKt3LblsBZK5dHJ4wh7+8GAyXhp6UKF89UtmsFRWPYdVWhG63gU9anRu69gELHkUZXtBdIiGvpUul5MLZUwVlbZdkn5IY2sRpRSzq1XcfmQy8HnZGAzdS28PG71v+/6Em6KqbqBh0EDmnUZB72ZStFuwGYLzK1WoerMryWUrwBl6ighyIrS5XHQz8Coo7+Hy6SJ++/Uv8Qyb8ovQjRudy+AnuWhGeDjSREFGVTMSv8WPilOL5UgNUaAzljgJrNcaqGhGKEPPSQLqmjdD99LQ3QU5zuLipCdF7W1FAa/ttQe3V6gNf8ts0mAF/aQ1pLZdGDov6ADOLlfszSJJwmufKENBNqNmdaPZsRg4LiaK3v7vuFOiDM6LjRNqhLS7sPTHNEEpxcmIlkWgc3FIEohiWQRYo9PlcvF5f73kmYqmQxYykCJcrBVRSNTlYg819ZGhG01T7utXU3SmpEASCF6YNwt6v7XwbrGjC3qzSfHxR07jrj9+BG//+LftdLWkYG918XG5AGYxqTeMngYmSooIMUM6NPS40bmtYwtm6IGSS8jCjTSxWFaxUdejF3TXar8k0CroIU1Rj8GiIIbullziTC96uWR6Aev9hE2KAskVdL/3Ji1kMgS7R7M2Qw8LQRsU7NiCPrtaxb0f/zb+4EvP47KpAi6s1/Ht08la51tOBG/JBTDZTq+hQ4QQy4vuxdDja39mw9bouMCpttbvf3JP+twt9ANxHC6AQ0NPcLMOW2zhl+PCwAaLnO5evzsgv6Zo1IULipQOQw8bLAKSu1jaw3d9GiwCzN/hyXlzlWGui0nRrcCOK+iUUnz22Cxe9+Gv49kLG/hv99yAB3/lDpQUEZ9/Yi7R12KFwsveVcw6C3oz1lotL0zkO+2C3UouBYeH2IlBZ+inFk3ZbCsZ+txaDQVZCN1hye7InNq2P0MXPHzo0a10rCma1GhINA09WYZe8/Hop4m9YzlbjuMa+oDi9/7uWfzG/34S1+4ZwZd/7Yfxk7ceQFYS8Prr9+DLT19MdG1WkBPBGVOrJjABN+HhLum6oPtkomsek4xexwFsUUFfKKMgC9g9ko30fJZ6mSxDr2HfeA7W4LQvWrtCW6/tq6FLGXtBMkMcK50iZtCkgJ6QpBiFoTPJJanPU9hWrzTg3AfLJZcBxQNPzOF11+3G377r5Tgw0dI533zzPlQ0A//03KXEXqscsEjXGVObxMCE10DPeq2BsS6Cgfz2ikaxLY7mJGRIp4WyH2BbisKKKYMoZKCImWQZ+motVD8HHAXPUdD9GLrZ1PRi6BElF7Hz4tELwtbPAc6Gc1KSy9YwdAYuuQwgNuoNbNR13HRwrMP+d9vhCewby+Fzx5OTXYKm+ViRL6t6IhkV4wWpraB3E51rH5tPJjpj6H7hXIAZ7jWWlzsatP3AyYVy6JYiN5wDXklgdrUaqp8DzhTFFvOuN5rIEEB0nZtekktY2qHXayVlXYximRQy5mKN5Bh6eIBd0nBm8XDJZQAxF+BAyGQI3nzTPnzjhUUsbNQTeb2Whu7P0MuWhh7UaIyCibyMtVrDDkOyh4q6KuhsAYeboXtPMroxnvffoJQWNusNXNqoh+4RdcPcK5pM0WGEIcyyCHgvimbSm/sOIytl0DBoW9CVmQAYj6EnVdDLESQXINk0S7+cmzThZOi8oA8gwjzCb755H5oUePDEhURer6LqyBBvzbngWCShNoyeBybGCzIobRXyjS7H/gGn+8NbQw9i6AAwWVD6rqGfWTLdJWFbitxIcq9oKwc9CkPvlFzqPvtavfT2quYd+uYFdm4ltVe0ouoQLQYehHyC7+1WNEWdd1pBnvtBwg4r6OaH3q+gH5ku4sYDY4m5XdhyCy9Nt9DG0I3eXS6uZmS3OS7OY3N/GFWPBQxecMs//cDFdauYRpA7nMgrybHIoDtAN1hhUhudDN3vue6CHl1DT15yycuddxJu5OVOr3232ArJJSsJmLQ+V/0aaOoVO6yg15CThMDA+bfevA/PX9zA8xfdS5nioxoQMaqIGYgZYjJ0PZmmKNAq6N1G5wL+e0XViAzdbND2dw3dvCWT7RpRYn1fQRYTK+hRPeiAQ3KJwNBtvd16/ymlsfZcptEUjZKgmKTkwo6934V133gOipjxjNwYROywgl7F/hBL2Rtu2AsxQ/BAAiy9rOm+03yEEHsiM4kUObf/uzeG7r1XVIvI0JmFsp+R+Jc26hAyBJPFeAU9L/tnv8fF3FoNipjBVDHcWeTnconC0FW9CUqjRecCDsklIYZeUYO3FTGYe0UTcrn0eVKUYe9oLtK/dVCwwwp6eKzpREHGj14zgy88MdfztpVqyFaXorUZqJ4gQ2fNyJ4KuhxsW1SEsKaoDKNJsVFPfl+nH+Y3VMyUlNhMqqAkydCjedCBVva92+XixdDdDDvO+jnn9ydW0DUjkqac5N0Pk256lSbj4q237MfP/NDBvr5mL9hxBT1Kw+otN+3DwqaKb55c6un1Kj77RBkKioB1y5mSloYeNzoXMB0/XsxVi+BD9zqWfmB+o46ZiANFTuQTZJFza9E86ACQlc330M3QvRxEbotjnPVzgENDT7ApGrTcgiGXoIYetX+TNO66dhfe95qr+/qavWDHFPSNegPrtUakD9yrXjKDkayIzx+f7ek1q1owQy8oIpYrKoDeT9SsJCAvC/ZAT7fRuQymQ8E7PGoQC/ql9Tp2x9TPASa5JMjQIzZlbeeKFkVDb2+gxlk/Z35/CpJLhNdOWkMnPo4xjhZ2zLszFzHWFDBvUd9w417847PzPemrFTX41rSoiPZ2nyS0wXFHnku3Y/+tY/Nm6GKGhMoaEz4LN9LE/EY98si/E3lZRK1h9C6vaTpWKlqk8wvwdq6YzfEADd26oFZirJ8Dkm+KVrRoGnqStkXmBIs6BbxTsX3U/h4RNdaU4d/88OV428sO9DRQYDKZ4LVyy2WToSehDU4UZKxYGnq30bn2sXlE6JoLosM5QL8DumqagY263pXkwiSxXpcZxyEMACAJpsupTXJpGIEuF5bnUg2IlPBC8k3RaFOqSdsW+2lZ3K7YQQU92IPuxmVT0bbeBKGqBW9GZzG1QDLaoDNCd73WwFiPBd3dFA1bEM3AInRX+jQtesmyLHbL0IHelxnPrsUr6EArQpfBl6G7GHYr9C0eQ09yUjQaQxegN2nk8yYI/Vw/t52xYy55s6s1ZKWMPSiQNphXOIihOxtLSZysE3nJLqK9Sy6dt8uaHi2iICcJUMRM3yQX5kHfPdo9Q+91axG7A9w3Fu0OEOjcFVr3Zejtkkuc9XOAc7Cod7bcMJrQ9CaKkTT04Ez0P/zy83jwRDR7cD8XRG9nhBZ0QsgBQshDhJDnCSHPEkJ+zeM5hBDyp4SQk4SQpwghN6dzuN3D9KDn+6bBmcsLgkeGnSwnkYJeULBSTqagFxSxI+NEtXafhoEQgolC/wK6uh0qAhypgD160edWa5AEgplS9GPIyZmIGnq7y4VdaKP6o1sul94ZOjsnotgWwzLR/9dj5/APT12M9Lp+DWOOdkQ5I3QAv04pPU4IKQE4Rgj5KqX0Ocdz/jWAK63/fgjAR6z/DwyieNCThL0EIGJBT+JknShI9kq79Voj0uZ732OThU7JxYh+6zzhsUEpLVxaZwW9C4Zus8heGXoVe8dygYu+3XCvoQtl6ExyUePZFgkxc1eSWENXttfPRbMtAt7vbb1hYLOuR77oq7qxbfZ6biVCP52U0ouU0uPWnzcBPA9gn+tpdwP4K2ri2wDGCCF7Ej9aHzSbFP/rsXOBDZj+F3T/faIMxYQZ+rglJy1sqF1H5zJ4NkVjsCRngzZtzG+oKMgCStn4/968Lbn0yNDX4p9fWYeGrhtN6E3qeR4oYgaEdNoW8zHOGUXMJMTQo98dBK2hW9w0zQBLlikgDEnkHe0ExKKFhJDDAG4C8JjroX0Azjv+PovOog9CyLsIIY8TQh5fXFyMd6QBODG7hvc/8LTvuP5mDA96UqgEbCtiaJdcEmDolrvkxWVzFVvPLhfXXtE4DH3cYyVeWpjfqHfFzgFH0enRix7Hg87g3BWqBmyDajFsNlikm1lAMWYMFElIpClajjGlGiS5LLCCvhmtoNd63Lu7UxD5HSKEFAF8DsB7KaXu5Cqv+8wOYy+l9GOU0lsppbdOT0/HO9IAN0pllAAAIABJREFUnFs2HSwnzq96Pj7XhQOhV7CTOMg50cbQE2AfjKGfWTILeq8+dACottnqYjL0fkkuPRR0VnR6Yej1hoHFTTU2Ych5FHS/OzVn8Y8TzMXA9or2iihSIkPQGrrFTVMmq2hGJGujaVvkDD0MkT6dhBAJZjH/NKX08x5PmQVwwPH3/QCSCRWPgLN2QV/zfHx2JZ4HPQkwuSJo+CNpyYU5eF5MoKB77RVVjSbkiBeeiYKMzbqOhpHctnk/mAw9fkMUaP07qz00RS+sdRfdm5UytuTCirXfBTMrCg7bYvToXIakJJfWcosoPnT/NXSLDmYeRXbhLpdoiOJyIQA+CeB5Sukf+zzt7wD8nOV2eTmAdUpptPZ1Aji7YhawFxbK2Kx3xrbG9aAngUqE4Y9Cm20xGR86kFBB93B/aBEHi5zHknZjlFKKhQ0Vu7qwLAJOht4de/3WqWX83399DABwzZ5SrO/NOWyL4Qw908pyibF+rvX9QiIMnenhSUkuQNSCzhl6FES5zN8B4GcBPE0IOWF97f0ADgIApfSjAL4E4PUATgKoAvjF5A/VH+dXqpCFDDSjiadn1/GKK6baHu+3Bx1waujRGHqvK+gA2INEyTL09vCoqBo6e69XqlpXE5xRsVLRoBnNroaKANhZ13EnGhc26viDLz2PB09cwP7xHD7xc7fiur2jsX5GThaiM/QOyaULhp6Ahl6J0RTNB0ouzoIeftFXuYYeCaG/FUrpN+CtkTufQwH826QOKi7OLlfxyqun8dXn5vHE+TXPgt5PDzrQuoUP0tCTti2KQgajOcm+I+mtoHfuFdX0JpSIjbh+jf/Pb5iFoVsNnRArWTKihm40Ke579Az+5Ks/gKY38Z5XXYF3/+gVXbHHrCTY4VxhDF2RBLspWrM2BsWBIgoJSS7hvSGGIMllYVO1ds82IjF0PikaDdv+klfTDCxsqrhh3ygumyrgSQ8dfXat2le5BWjdwgdp6Kygy2Imln85CBMFGcyY0k10LkPRS0PXm5EjCvqVuNgaKur+LiDOougHnpjDf/ric7jl0Dj+6d/9CN73mqu7LjRZSbC94eEaesbB0LvQ0KVkmqJVzdyTG4UtZyXTbunH0K/ZPQIg3OliWzq5bTEU276gn7fY6MHJPI4eGMOJ82sdm3K6sZT1CrZIN2hdG/OoZxOcgGOFtNRDdC7gvVdU05uh6+fcx5G2hn6ph7F/hjiLop+ZW0dBFnDfL74Mh3vM+8lJAhoGRcNoOvK+w10uVU2PraErYqYtZqBblFX/PbluEEKQl7wjdBc269g/nkMpK4YOF9Xtu5dtX65Sx7Z/h5jD5dBkAUcPjGFhU8VFa3IQMD3oa9X+etCBVjBX0IkvChlkpUyit5JM6ujFgw547xX1W8DghTFrSjXt3aKMoU/HXD3nRJxF0acWy7hsupCIfJdzTICGa+gZl8slvaaopjd9o3ajrp9jyMlix2BRs0mxVNYwXVIwXVSwGCK52PtE+aRoKIagoJsNwIMTJkMH2u2LW+FBBxiTCT8Bi4qYaEGfKJiFtBf9HHC4P9TuGLokZDCSFbFSiTY40i3mN+qYKso9pfnl5c6pWD+8uFTB5VPFrl/LCWdGSzQfOnO5pNsU/b2/fxY/+0n37KAJc/1c9PO14HGxXKlqMJoUMyUFU0UlVHJhBZ1LLuHY9gX9/EoVJUXEeF7CNXtKkIVMW0FvedD7W9Crmh5t76IiJnoryeyCvRZ0915R3WiiSeM1byeLClaq6TL0S+vdDxUxFCJu1qk3DMyt1XD5dO/RykB7RksYQ2dDSM0mRbVhRCILTihi9EnRZ+bW8dTsetuUMEMlZsxwzkNyYQ6X6VIWUyU5tClqvzdccgnFtn+Hzq5UcXAyb41HC7h27whOnHMUdNuD3l/JxVwCEC3vIgnLIsNkQgXdvVeUFYM4THg8L6Wuoc9vqD0X9LxHVLAXzixXQClw+XQyDN2epGwYkSdF67qZ4pnrarAomuRybqUKVW+2ecUZoq6fY/Da2cp+7syIgsmCEq6hN4LfG44Wtn1BP7dSxcGJVrE+emAMT8+tQ7cmFGdXa1DEDKaK/fOgA+HbihjGCxJKPbhROn5ePpmCDrA8F/PDGHVBtBP9iNDtJceFoRDR5XJ60ZT3Lk9g+QnQvrhCDWGhimRmudj7ROM2RaVWFkwQNqyeE2BewNyIuq2IoaCI/gy9aEoua9VG4ESxLbnwgh6KbV3QjSbF7EoNBydbBf2mg2OoNQz8YL4MoJWy2O9dhFGtZb/3xuvw+2+6LrHXZe6SXqJzGYqKaPuOW+FR0T9UaUfoqrqB5YrW9VARQ9Tdl6cXzXMqiW1WQHvWic3Qfd7frChA05so18ND3/y+32hSm+j44fxK1f4zy0hyIuo+UQZ3RDBgOlwAYLqkYKpknq/LAcNFjKHneEEPxbYu6Jc26tCMZgdDB1qNUdOD3l+5BYhuLbtyVwlX7oo3Mh6EpDR0gPmzu2fo41aErttGmhQY0+s2x4WBNe7CjvP0YgW7R7KxCloQGOOsWRo6IYAkeBMP9txVK5I49mBRxL2izoLuzdDjFXSvoa3FTTPuuKCImLLcSUE6es1m6Nu6XPUF2/odYgzi0ESLMR2cyGOiINvJi/3OQWeIqqEnjamC+QEZS4ChO/eKakZw084LE3kZmt7seb2bH+yhoh486IDJdo0mDS12p5cqiTVEgU6XS9BWe/ZcNqjVzaQoEF7Qz1kFfbIg4+yKB0NX4zVk84rowdBVOw6CSaFBBZ1LLtGxvQu6Fcp1yCG5EEJw4/5RnDi/hrKqb4kHHYiuoSeNAxM5/NFbr8cbrt/b889y7hVlt71xNXQgveGiS+tmEehVcikEhEgxUEpxerGcaEF3+9CDXBysmLGeRFyyEHWv6PmVGkZzEq7bN2pbghmMJkWtEY+oeA0WLW6qmLZW9bUYepDkwm2LUbGtC/rZ5SrEDMEeF0M7emAcLyyU8f1LZmx7vxk6O/Hj6pxJgBCCn37ZwUQ0dHNrkflh0gz/BQx+SHv8P4mxf6C1HzPIi75c0bBR1xPzoAMul0ujGViwGENf7Zahu/aS+oGZDA5N5HF2udomQ1W08HwiN/KWJdRpgfQu6AEMnU+KRsa2fofOrVSxbzzXMeJ+44FRUAp86elLAPpf0C+um973JN0rW4Gi0torqnbB0Mf7UNBlMYPxHi9eUfaK2g6XJCUXh8ulrocwdOu5LcklflMUiMLQrYI+mcdmXbcdL0Brq1Mshm4917nPdHFTtZdpFxQROUkIHC5iDqAsnxQNxbYv6M6GKANrjH7paTOSvZ+SC6UUv//3z0ERM7jr2l19e900UJDFVlO0C4Y+mXJBv2QttujVwRRlryhzuBxJyIMOxGXo5mNMmog9WMSaogEMvdmkZs9pIodDk+aFy9kYZRf3OHcH7kz0qqajrOo2QweAyWKwvZVp8FxyCce2Luhnl70L+lhexmVTBVxcr/fdg/4PT1/EV5+bx6+/5ir7Q7FdkXfsFbV90jE+VPaSi5SWRc9v1LGr1HvWOlu2HJSJfnqpAlnMYG+CIW/s4ljXwhk6e8x2ucTW0MObovObLdcY60udczRGKxEiod1gfQLG7pkzacbxe5sqKiGSi4FMgAOIo4VtW9DXq+biZ2dD1AnG0vvpQV+paPiPDz6LG/aP4h13XNaX10wTzr2ijKHHkVxKighJIKkNF833sKnICa91e26cXizj8GQeQkIxx4DZ78haAz9RGTp7L+N6su2LR8C0KHONHZzI20TpzJKjoGvRl1swMGmo2jC/d8Ee+28x9Kmi0rbwwo2qZiAn+TuAOFrYtgWdMYeDE94suFXQ+ye3/KcvPof1WgMfuueGnqJrBwXOQqfp8SUXQgjG8+kMF1FKcWm93rPDBQhelcZwOsFQLifY4E0YQ2cFfKWiIitlYl9YojD086tm7+fAeB5ZScDukay93hGItlbRDSZnsfe2xdBbBX26JAe6XM6vbM0syXbEtq067ETzklyAdobeDzz0vQU88MQc3v2jV9jB/dsd7Na6rOpdZbkAptMlDQ19U9VRaxg9DxUB3tnvTjSMJs4tVxNtiDLkJMHW0IPkLHuwqNKIVVBb3x9uWzy3UkWGwJaVDk3m7XhqwLl+LoaG7pJcFjZaU6IMkwUFKxUVhkcYGACcWkzW/z/M2LYF3WboPpLLS/aMYN9YDjcdHE/9WDbrDbz/gadx1a4i/u2PHkn99foF56JorYvRf8DMlkmjoM+vJ2NZBBwM3SfP5fxKFXqTJhbK5YQzdCvYh24+Vlb1rnLBbYYe0BQ9v1LFntGcfdHuKOhd2RaZg8j83sWyCiFDMJFv9bWmiuaWrTWPXoumN3FupZpoM3qYsW19deeWq5gsyL4nlyxm8M3felVfjuW/fvl7mN+o4y/e/opEkxO3Gs5F0YzZdcPQn7fmAZKEvakokYIezNDTsCwysIIeqqE7HuuGoUcZ/T+/UsWBidYd7aHJApbKsyhbkbmMocdpyDLJhY3vL2yomCrKbSsXp0qt4aJJ16KScysVGE2KIzOcoUfBtmXoZ5ervuy8n/jOiyv49GPn8I47LuvL3UA/wW6tnQw96oILhrQCunpdDu2EkDGbk34a+ukl07KYVMqiEznZklxCGXqroMdZMMEQqSnqsgHbTheLpbOgtnyMhmxrUYqloZfVNocLEDxcdHLBvJhyhh4N27agn1up4pCPft5PXLt3BO959ZV432uu2upDSRxObVnVm4HhUX4YL8hYqzV89dFuMZ/ALlEnCgFbi04vVjBRkDGWT97+aq6WC3e5OJvR3WnowU3ResNctu4s6Ict2y2LAGBxFnEWmueldsllYUNt08+B4DyXUwknXA47Qgs6IeRThJAFQsgzPo/fSQhZJ4ScsP77QPKH2Q5Nb+Lies23IdpPFBUR77vrqi0Z808bzqYoWz8X1zo2kZdAffTRXnBpvY7RnJRYYFPQXtHTi5VU2DkQ3eWSyRBb7upGQ2d3Vn4MnS2COeD4TLE7YBbSFXULlxPOiGCAMXR3QffPczm1WMauEQWlbO9RFjsBURj6fQBeF/Kcr1NKj1r/fbD3wwrG3FoNTQoc3OaDO4MOp21R1ZuxLIsME9aHNenhonlrSjQpBDL0hFMWnchK5kafhkFDJyGz1vvfTehbJkOweySLkxbjdYOZDJwFfSQrYaIg2wy9rBqxGqKA2XORBIKKZsBoUiyXOxn6aE6CJBBPhn56scLllhgI/YRSSh8BsNKHY4kMdoL5DRVxJAOmlZpN0SbkLhq+zM0QtMCgGySxqcgJFiLlxka9gaWymorDBTAL+lrNzEsJ25nJ7kbismSGVxyZxLdPLXvuCmU6+QGX3/vgRMvpYmahxz8HzLsQHcsVFU2KDoZOCMFkoXNZNKUUpxbLvKDHQFIa+u2EkCcJIV8mhPiu3yGEvIsQ8jgh5PHFxcWuX6w1VMQLeppw7hXVumTo4wXzVjlphn5pI5mhIoZdI1k8d3EDm/X2pdZJr51zIycJWLcKejbk/bULepcy0+1HJrFc0fD9+c2Ox86v1pCThI6YjMOT7QW9G2mRraFb2Ggth3bDa1n0YlnFZl3nHvQYSKKgHwdwiFJ6I4A/A/AFvydSSj9GKb2VUnrr9PR01y94brmKrJTpuNJzJA+2V1TVja4K+qS1cGOl0gh5ZnQYTYrFzd6XQzvxS688gpWKho88fKrt6yyUKy2GnpMFsIRaJaRQMy96twz9jiumAADfPLnU8RhzuLh7JAcnC7iwXoOqG6hoemzJBTD/jdWGgcVy59g/g9eyaHYx5Qw9Onou6JTSDUpp2frzlwBIhJCpno8sAGd9Tj6O5MH2imp6M7YHHWgxdBYpnASWyuatexI5Lgw3HhjDm2/ah09840W7QQiYRUXIkNTuBp2sPCzvmzH0bhen7B3L4bKpAh49tdzxmNuDznB4Mg9KzcUX3W7hYqsMFzc6x/4ZpoqdkgtzuByZ4QU9Knou6ISQ3cSqrISQ26yf2XnGJIhzPimLHMmjoAg9NUUVUcBtl03gs8dmbS97r7i0ntxQkRP//rVXgwD40Fe+b3/t9FIZB8ZzXV3MosCZ8R02lGZLLj3kgr/iyCQeO72MhmNZNKXUKuidn6lW6mKl6y1cedmUXIIY+pSV5+JcqHFqoYKslMGehH/Pw4wotsW/BfAtAFcTQmYJIe8khPwSIeSXrKfcA+AZQsiTAP4UwNtoWluBYZ585u0h19X6gYIstmyLXRa1X77zCC6u1/HgiblEjqm1qShZyW3vWA7v+pHL8XdPXsAT58ydtKcXK6nJLUB7amJUht6LRfaOK6ZQ0Qw8Nbtmf22loqGiGZ4kyc5FX6rGXhDNkLeGpxY26hjJip5W0+miAs1oYqPechqdXirj8qliLN/7TkcUl8u9lNI9lFKJUrqfUvpJSulHKaUftR7/c0rpdZTSGymlL6eUPprmAS+WVdQaBg563B5yJI+iIqKq6dCM4PCoINx51TSu2V3CR//llKfDIi5eWDBvxQ+lcFH/pVcewXRJwX/+h+dhNCnOLKfnQQfaJ0BDGTqzLXbhNGG4/fJJAMCjJ1s30c6URTcmCzIKsoCzyxVUtO4ll4qqY9HDsmi/TpG5oVqyy6nFMpdbYmLbTYoye9V2Xx6xXZC39oqqutE1QyeE4JfvPIJTixV89fn5no/p2NlVXDFTTGRvqhsFRcRvvOYqHDu7ik9+4zTqjebAMfRcDwx9vCDj2j0j+OapVmM0KOiOEIJDkwU8f8l0xnQjueQkETXL5eIe+2dwDxfVGwZmV2s4wh0usbD9CnpIyiJHsmB7Rbu1LTL8+PV7cGAih488fAq9KHKUUhw/t4qbD451/TPCcM8tB3DN7hL+2z+aWnqatrlYDF3qfrDIiTuumMTxs2v29Ob5FW8POsOhyTyev2AGrHXD0AtKy+Xix9DdeS4vLlVAaXruomHFtivoP3F0H77z/lcPRI7LTgCboFR70NABQBQyeNePHMGJ82t47MXu59ROL1WwVm3glkPpBaEJGYL/8OPXomGYF550C3p8l0uvMROvuGIKmtHE42fN38P5lSqmiopvpMChyQI2u1g/x5CTBVRVxtCjFfSWZZEz9DjYdgU9kyGYGckOxUag7QA2FFJvGLGTFt34yVv2Y6ood3i94+D4WbNZeXPKyZb/6sop/NhLZjBZkDFdTG/eIReLoVu2xR40dAC47fAExAzBNy0d3TQZ+PeknBPZXWnokgjNaKLWMHwZ+nheAiGwrYvMspjGlqhhBq+KHIFgjGy12ggdTQ9DVhLwi3dchn/5wSKevbDe1c84fm4VI1mxL8Mm/+NtN+GBd9+R6ryDkxWHjv73EM7lREERcdPBMTxq6eju2Fw32gp6F6/tvADN+DiTRCGDibyMJWu46NRiGfvGcj3/W3caeEHnCATL3jbTFnv/cP3Myw+hqIj46L+c7ur7j59dw00Hx/tiZSsoYuq9mjga+khOgpAhXckebtx+ZApPz61juazi4nrd04PO4DQgdMPQnUV5uujvKXcOF53ma+e6Ai/oHIFwFo9eGTpgJuu9/eUH8Q9PXbBD1qJio97ADxY2U9XP+404LpefetkB/M07fyiRqOY7jkyCUuCBJ+ZgNGlgQd8zkrX7J93aFhn8GDrQynPhoVzdgxd0jkA4lyn0qqEzvPOOyyBmMvj41+Ox9BPn1kBp+vp5P8EYOiHh7+9IVsLtRyYTed2bDo4jJwn4zHfPAwgOustkCA5Yy9a70e+dF6CgfsRkQcFSWcOljTqqmsE96F2AF3SOQBQSZugAMDOSxd1H9+Lzx+ewUY8e2nXs7CoyBLjxwGgixzEIYKxcEeMvD+kFspjByy6bwElrSCuIoQOt7UW9MHRJIBgLmB2YKipYLqsthwvfUhQbvKBzBMIpuSTF0AHg524/jKpm4HPHZiN/z/Fzq7hqV2mottcwhr4Vy8XvsNi+JJDQXJxDkwUQ0t36O1bQp4tK4EVrqiSjohl4Zs5smHOGHh+8oHMEwnmL3ctgkRvX7x/FTQfH8NffOhspDqDZpDhxbg03D5F+DgCSYG70CdPP0wCL090/nocQ0mT+hVccxn+/58bQ53mBSS7TIRcN5kV/7MUVFBWRx2N3AV7QOQLRJrkkzCJ/7vZDOL1UaRtD98MLC2VsqjpuGSL9nCErClvC0F+yZwSjOQn7x8NzkQ5O5vHWW/Z39TqMoYcVaLZc47tnVnD5dIHHY3cBXtA5AuEs6ElHyL7++j2YLMj4y0fPhj73uJV+OGwMHTAjdLeCoQsZgj/56RvxvruuSvV1mG3Rb6iIgTH0zbrOHS5dghd0jkDkpXQkF/PnCXjbbQfwz9+bt/NE/HDs7ComCjIOD2GGT07aGoYOAK+6ZhduSvmuh/VhdvkEczFMORwwfOS/O/CCzhGITIbY04FpLHl4+w8dAgB8+rFzgc9jgVzDeBuelTJbwtD7hbws4n+87Sjuve1A4PMmHftMOUPvDsN7FnEkBia7pMEi947lcNe1u3D/d8+h3jA8n7Na0XB6sTKUcgsAlLJSV3bA7YS7j+7DTEhTVBEFlLLm+8BTFrsDL+gcoWC3zGmtYfv52w9jtdrAF5+66Pn4E+f7E8i1Vfjg3dfhd17/kq0+jIHAdFFBhrTnx3BEBy/oHKFgeS5pFfTbj0ziipki/upbZzwfP3Z2FUKG4Mb96WWgbyWu2zuKK3eVtvowBgJTRQUHJvKea+o4wsELOkco2DBJ0k1RBkIIfvblh/DU7DpOnF/rePz42TVcu2eEJ+/tAPybH7k8ddfNMIMXdI5QpC25AMBbbt6HgizgQ1/5Hl6Y37S/rhtNnDi/NlSBXBz+uOvaXbj76L6tPoxti+HuxHAkglZTNL2CXspKeO+PXYU/+sr/397dx8hVlXEc//4KRWBLw8tSbStr24AaAsG2K6AUaIwoRbSiEShEUGqICSb4gkmNEsVKoo0vYEiAJhbbKFBBUEyUSA1NI1Cl1JZCCy2Qhl1oXA2pLcWUlj7+cc6YYbuzO7udnTtz9/dJbubuvXfOnGd65+mdc8+c8xwX/GwNM7uO5bLuk5je2cF/973FzFGccs6sLJzQbUgdTbhCh/R1+5JZU/ndP15h5ZM9LHpg0//3+QrdbGhDJnRJy4CLgb6IOG2A/QJuBS4C3gC+EBHrG11RK06lH3ozfvzSOeEdfOncGSycM50NPTv5zboe9u47wNRjh/55utlYV88V+i+B24AVNfbPA07Jy1nA7fnRSqIZTS79SWJm13Gj/itGszIZMqFHxBpJ0wY5ZD6wIiICWCvpWEmTI2LgTsXWdj55xhSOHH+Yu5KZtbhGtKFPBXqq/u7N2w5K6JKuBa4F6OrqasBLWzOcPGkCJ3tsarOW14jv0AMNrjHgANcRsTQiuiOi+8QTT2zAS5uZWUUjEnovUD3qzruBVxtQrpmZDUMjEvpDwFVKzgb+4/ZzM7Pmq6fb4j3AXKBTUi/wXWA8QETcAfyR1GXxBVK3xS+OVmXNzKy2enq5LBhifwDXNaxGZmY2Ih7LxcysJJzQzcxKwgndzKwklJrAC3hh6V/A0NO9D6wT+HcDq1M0x9O6yhQLlCueMsUC9cfznogY8Ic8hSX0QyFpXUR0F12PRnE8ratMsUC54ilTLNCYeNzkYmZWEk7oZmYl0a4JfWnRFWgwx9O6yhQLlCueMsUCDYinLdvQzczsYO16hW5mZv04oZuZlUTLJHRJyyT1SXqmatsZkp6QtEnSHyRNzNuvlLShajkg6QN5382SeiS93iaxjJe0PG/fIulbg5VThGHGc4Sku/L2jZLmDlDeQ0XG1MBzbXY+/gVJP8/z67ZrLKslPV+1b1IBsZwk6dH8OXhW0vV5+/GSHpG0LT8el7e/P8e5V9IN/cranuPfIGlds2MpTES0xAKcB8wCnqna9iRwfl6/Blg8wPNOB16q+vtsYDLwejvEAlwB3JvXjwa2A9NqldMG8VwH3JXXJwFPAeOqnvcZ4O4iY2rgufZ34EOkSV7+BMxr41hWA90Fn2eTgVl5/RhgK3AqsARYlLcvAn5UdX59ELgZuKFfWduBziLjKWJpmSv0iFgDvNZv8/uANXn9EeCzAzx1AXBPVTlro+Dx2IcZSwAdkg4HjgLeBHYNUk7TDTOeU4G/5Of1ATuBbgBJE4CvAz8Y5SoPqhHnmqTJwMSIeCJSBlkBfHp0alxboz43rSAidkTE+ry+G9hCms5yPrA8H7ac/D5HRF9EPAnsK6C6LallEnoNzwCfyuuf4+0zI1VcRoudmDXUiuV+YA9pDtaXgR9HROFJvA614tkIzJd0uKTpwOyqfYuBn5DGzW81wz3XppJm66qozKXbCkb6ubkrN1HcWETzUTWlielnAn8D3lm5SMuP9TQHBfBnSU8pzWU8JrR6Qr8GuE7SU6SvYG9W75R0FvBGRBTaxlynWrGcCbwFTAGmA9+QNKOYKg5LrXiWkZLbOuAW4HFgf26rPTkiHiyisnUY7rlW91y6BRjJ5+bKiDgdODcvn29WZfvL3+R+C3w1InaNsJhzImIWMI/0XpzXsAq2sCEnuChSRDwHfAxA0nuBT/Q75HLa4+p8sFiuAB6OiH1An6THSE0ULxVS0TrViici9gNfqxwn6XFgG3A+MFvSdtJ5N0nS6oiY29yaD2wE51ovaf7cipaZS3ckn5uIeCU/7pZ0N+lCY8Xo1/btJI0nJfNfR8QDefM/JU2OiB25qatvqHIi4tX82CfpQVI8awZ/Vvtr6Sv0yp12SeOA7wB3VO0bR/o6eW8xtRueQWJ5GfiIkg7STd3niqll/WrFI+noHAeSLgD2R8TmiLg9IqZExDRgDrC1VZI5DP9cy1/9d0s6OzdPXAX8vqmVrmG4seTmsc68Ph64mNRs01T5ffwFsCUiflq16yHg6rx+NUMTisieAAACNUlEQVS8z5I6JB1TWSf959YO3+IPXdF3ZSsL6YphB+kGRy+wELiedKd7K/BD8i9b8/FzgbUDlLMkP/9AfvxeK8cCTADuA54FNgPfHKycVv+3AaYBz5NuaK0iDfXZv7xpFNvLpVHnWjcpUbwI3Fb9nHaKBegg9UZ6Op+HtwKHFRDLHFKz1dPAhrxcBJxAutG+LT8en49/V455F+nmey8wEZhBupezMcfz7aLOtWYv/um/mVlJtHSTi5mZ1c8J3cysJJzQzcxKwgndzKwknNDNzErCCd3GhNzP/6+S5lVtu1TSw0XWy6yR3G3RxgxJp5H6/M8EDiP1c74wIl48hDIPj/TrWLPCOaHbmCJpCWkwtA5gd0QslnQ1adjfI0hjz3wlIg5IWkoamvYoYGVEfD+X0QvcCVwI3BIR9xUQitlBWnosF7NRcBOwnjRgVXe+ar8E+HBE7M9J/HLSmO2LIuK1PLTxo5Luj4jNuZw9EXFOEQGY1eKEbmNKROyRtJI0AcpeSR8lTZKwLo8YexTQkw9fIGkh6XMyhTTWeyWhr2xuzc2G5oRuY9GBvEAaBndZRNxYfYCkU0hjopwZETsl/Qo4suqQPU2pqdkwuJeLjXWrgEurRhs8QVIXaZCn3cCuPGTrxwuso1ldfIVuY1pEbJJ0E7AqDy27D/gyaYKOzaTRFF8CHiuulmb1cS8XM7OScJOLmVlJOKGbmZWEE7qZWUk4oZuZlYQTuplZSTihm5mVhBO6mVlJ/A+3+41cBu0KgAAAAABJRU5ErkJggg==\n",
      "text/plain": [
       "<Figure size 432x288 with 1 Axes>"
      ]
     },
     "metadata": {
      "needs_background": "light"
     },
     "output_type": "display_data"
    }
   ],
   "source": [
    "# import the charting library we need and set it to draw inline with the notebook\n",
    "%matplotlib inline\n",
    "# First, sort by Year to make sure we get everything in order\n",
    "# https://pandas.pydata.org/pandas-docs/stable/reference/api/pandas.DataFrame.sort_values.html\n",
    "# inplace = True means it will sort and save to the df variable\n",
    "df\n",
    "# Here we filter the df[\"Country Name\"] column only by values == \"World\"\n",
    "# https://pandas.pydata.org/pandas-docs/stable/reference/api/pandas.DataFrame.plot.line.html\n",
    "df[df[\"Country Name\"] == \"World\"].plot(x=\"Year\", y=\"Value\")"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## 2.5 Tutorial - cleaning complex data\n",
    "\n",
    "<div class=\"alert alert-block alert-success\">\n",
    "    <p>Pick a spreadsheet from <a href=\"data/lesson-spreadsheet/\">training data</a> and restructure it according to the techniques and requirements presented in this lesson.</p>\n",
    "    <p>The data in this folder are also sourced from the World Bank, but from a long time ago, before the World Bank knew what open or machine-readable data was. It contains some of the worst examples of data-mangling you will ever see.</p>\n",
    "</div>\n",
    "\n",
    "![Tutorial spreadsheet](images/tutorial-1-2.jpg)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 26,
   "metadata": {},
   "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>Unnamed: 0</th>\n",
       "      <th>Unnamed: 1</th>\n",
       "      <th>Unnamed: 2</th>\n",
       "      <th>Monitoring human development: enlarging people's choices …</th>\n",
       "      <th>Unnamed: 4</th>\n",
       "      <th>Unnamed: 5</th>\n",
       "      <th>Unnamed: 6</th>\n",
       "      <th>Unnamed: 7</th>\n",
       "      <th>Unnamed: 8</th>\n",
       "      <th>Unnamed: 9</th>\n",
       "      <th>...</th>\n",
       "      <th>Unnamed: 21</th>\n",
       "      <th>Unnamed: 22</th>\n",
       "      <th>Unnamed: 23</th>\n",
       "      <th>Unnamed: 24</th>\n",
       "      <th>Unnamed: 25</th>\n",
       "      <th>Unnamed: 26</th>\n",
       "      <th>Unnamed: 27</th>\n",
       "      <th>Unnamed: 28</th>\n",
       "      <th>Unnamed: 29</th>\n",
       "      <th>Unnamed: 30</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>3 Human and income poverty Developing countries</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>...</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>...</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>...</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>...</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>...</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>5</th>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>...</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>6</th>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>...</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>7</th>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>Probability at birth of not surviving to age 4...</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>...</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>8</th>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>Adult illiteracy rateb,†\\n(% aged 15 and older...</td>\n",
       "      <td>...</td>\n",
       "      <td>HPI-1 rank minus income poverty rankc</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>9</th>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>Human poverty index                         (H...</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>...</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>10</th>\n",
       "      <td>HDI rank</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>...</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>11</th>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>Rank</td>\n",
       "      <td>NaN</td>\n",
       "      <td>Value \\n (%)</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>...</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "<p>12 rows × 31 columns</p>\n",
       "</div>"
      ],
      "text/plain": [
       "                                          Unnamed: 0 Unnamed: 1  Unnamed: 2  \\\n",
       "0   3 Human and income poverty Developing countries         NaN         NaN   \n",
       "1                                                NaN        NaN         NaN   \n",
       "2                                                NaN        NaN         NaN   \n",
       "3                                                NaN        NaN         NaN   \n",
       "4                                                NaN        NaN         NaN   \n",
       "5                                                NaN        NaN         NaN   \n",
       "6                                                NaN        NaN         NaN   \n",
       "7                                                NaN        NaN         NaN   \n",
       "8                                                NaN        NaN         NaN   \n",
       "9                                                NaN        NaN         NaN   \n",
       "10                                          HDI rank        NaN         NaN   \n",
       "11                                               NaN        NaN         NaN   \n",
       "\n",
       "   Monitoring human development: enlarging people's choices … Unnamed: 4  \\\n",
       "0                                                 NaN                NaN   \n",
       "1                                                 NaN                NaN   \n",
       "2                                                 NaN                NaN   \n",
       "3                                                 NaN                NaN   \n",
       "4                                                 NaN                NaN   \n",
       "5                                                 NaN                NaN   \n",
       "6                                                 NaN                NaN   \n",
       "7                                                 NaN                NaN   \n",
       "8                                                 NaN                NaN   \n",
       "9   Human poverty index                         (H...                NaN   \n",
       "10                                                NaN                NaN   \n",
       "11                                               Rank                NaN   \n",
       "\n",
       "      Unnamed: 5  Unnamed: 6  \\\n",
       "0            NaN         NaN   \n",
       "1            NaN         NaN   \n",
       "2            NaN         NaN   \n",
       "3            NaN         NaN   \n",
       "4            NaN         NaN   \n",
       "5            NaN         NaN   \n",
       "6            NaN         NaN   \n",
       "7            NaN         NaN   \n",
       "8            NaN         NaN   \n",
       "9            NaN         NaN   \n",
       "10           NaN         NaN   \n",
       "11  Value \\n (%)         NaN   \n",
       "\n",
       "                                           Unnamed: 7 Unnamed: 8  \\\n",
       "0                                                 NaN        NaN   \n",
       "1                                                 NaN        NaN   \n",
       "2                                                 NaN        NaN   \n",
       "3                                                 NaN        NaN   \n",
       "4                                                 NaN        NaN   \n",
       "5                                                 NaN        NaN   \n",
       "6                                                 NaN        NaN   \n",
       "7   Probability at birth of not surviving to age 4...        NaN   \n",
       "8                                                 NaN        NaN   \n",
       "9                                                 NaN        NaN   \n",
       "10                                                NaN        NaN   \n",
       "11                                                NaN        NaN   \n",
       "\n",
       "                                           Unnamed: 9  ...  \\\n",
       "0                                                 NaN  ...   \n",
       "1                                                 NaN  ...   \n",
       "2                                                 NaN  ...   \n",
       "3                                                 NaN  ...   \n",
       "4                                                 NaN  ...   \n",
       "5                                                 NaN  ...   \n",
       "6                                                 NaN  ...   \n",
       "7                                                 NaN  ...   \n",
       "8   Adult illiteracy rateb,†\\n(% aged 15 and older...  ...   \n",
       "9                                                 NaN  ...   \n",
       "10                                                NaN  ...   \n",
       "11                                                NaN  ...   \n",
       "\n",
       "                              Unnamed: 21 Unnamed: 22 Unnamed: 23 Unnamed: 24  \\\n",
       "0                                     NaN         NaN         NaN         NaN   \n",
       "1                                     NaN         NaN         NaN         NaN   \n",
       "2                                     NaN         NaN         NaN         NaN   \n",
       "3                                     NaN         NaN         NaN         NaN   \n",
       "4                                     NaN         NaN         NaN         NaN   \n",
       "5                                     NaN         NaN         NaN         NaN   \n",
       "6                                     NaN         NaN         NaN         NaN   \n",
       "7                                     NaN         NaN         NaN         NaN   \n",
       "8   HPI-1 rank minus income poverty rankc         NaN         NaN         NaN   \n",
       "9                                     NaN         NaN         NaN         NaN   \n",
       "10                                    NaN         NaN         NaN         NaN   \n",
       "11                                    NaN         NaN         NaN         NaN   \n",
       "\n",
       "   Unnamed: 25 Unnamed: 26 Unnamed: 27 Unnamed: 28 Unnamed: 29 Unnamed: 30  \n",
       "0          NaN         NaN         NaN         NaN         NaN         NaN  \n",
       "1          NaN         NaN         NaN         NaN         NaN         NaN  \n",
       "2          NaN         NaN         NaN         NaN         NaN         NaN  \n",
       "3          NaN         NaN         NaN         NaN         NaN         NaN  \n",
       "4          NaN         NaN         NaN         NaN         NaN         NaN  \n",
       "5          NaN         NaN         NaN         NaN         NaN         NaN  \n",
       "6          NaN         NaN         NaN         NaN         NaN         NaN  \n",
       "7          NaN         NaN         NaN         NaN         NaN         NaN  \n",
       "8          NaN         NaN         NaN         NaN         NaN         NaN  \n",
       "9          NaN         NaN         NaN         NaN         NaN         NaN  \n",
       "10         NaN         NaN         NaN         NaN         NaN         NaN  \n",
       "11         NaN         NaN         NaN         NaN         NaN         NaN  \n",
       "\n",
       "[12 rows x 31 columns]"
      ]
     },
     "execution_count": 26,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "import numpy as np\n",
    "import pandas as pd\n",
    "\n",
    "source = \"data/lesson-spreadsheet/HDR 2007-2008 Table 03.xlsx\"\n",
    "\n",
    "df = pd.read_excel(source)\n",
    "df.head(12)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "As with the previous example, you can see that the \"real\" header row doesn't begin until around row 7, and even then it appears spread out over multiple rows. Plus there are a range of different problems. See if you can spot all of them:\n",
    "\n",
    "- Seemingly empty columns and rows (filled with nothing but null value `NaNs`)\n",
    "- Special printing characters (like `\\n`)\n",
    "- References that probably don't belong in this spreadsheet\n",
    "\n",
    "There will be more, but lets have a go at fixing the immediate problems."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 27,
   "metadata": {},
   "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>Unnamed: 0</th>\n",
       "      <th>Unnamed: 1</th>\n",
       "      <th>Unnamed: 3</th>\n",
       "      <th>Unnamed: 4</th>\n",
       "      <th>Unnamed: 5</th>\n",
       "      <th>Unnamed: 7</th>\n",
       "      <th>Unnamed: 8</th>\n",
       "      <th>Unnamed: 9</th>\n",
       "      <th>Unnamed: 10</th>\n",
       "      <th>Unnamed: 11</th>\n",
       "      <th>Unnamed: 12</th>\n",
       "      <th>Unnamed: 13</th>\n",
       "      <th>Unnamed: 14</th>\n",
       "      <th>Unnamed: 15</th>\n",
       "      <th>Unnamed: 16</th>\n",
       "      <th>Unnamed: 17</th>\n",
       "      <th>Unnamed: 18</th>\n",
       "      <th>Unnamed: 19</th>\n",
       "      <th>Unnamed: 20</th>\n",
       "      <th>Unnamed: 21</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>Probability at birth of not surviving to age 4...</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>MDG</td>\n",
       "      <td>NaN</td>\n",
       "      <td>MDG</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>Adult illiteracy rateb,†\\n(% aged 15 and older...</td>\n",
       "      <td>NaN</td>\n",
       "      <td>Population not using an improved water source†...</td>\n",
       "      <td>NaN</td>\n",
       "      <td>Children under weight for age†\\n(% under age 5...</td>\n",
       "      <td>NaN</td>\n",
       "      <td>Population below\\n income poverty line\\n(%)</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>HPI-1 rank minus income poverty rankc</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>Human poverty index                         (H...</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>HDI rank</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>$1 a day \\n1990-2005d</td>\n",
       "      <td>NaN</td>\n",
       "      <td>$2 a day \\n1990-2005d</td>\n",
       "      <td>NaN</td>\n",
       "      <td>National poverty line           1990-2004d</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>Rank</td>\n",
       "      <td>NaN</td>\n",
       "      <td>Value \\n (%)</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>5</th>\n",
       "      <td>HIGH HUMAN DEVELOPMENT</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "               Unnamed: 0 Unnamed: 1  \\\n",
       "0                     NaN        NaN   \n",
       "1                     NaN        NaN   \n",
       "2                     NaN        NaN   \n",
       "3                HDI rank        NaN   \n",
       "4                     NaN        NaN   \n",
       "5  HIGH HUMAN DEVELOPMENT        NaN   \n",
       "\n",
       "                                          Unnamed: 3 Unnamed: 4    Unnamed: 5  \\\n",
       "0                                                NaN        NaN           NaN   \n",
       "1                                                NaN        NaN           NaN   \n",
       "2  Human poverty index                         (H...        NaN           NaN   \n",
       "3                                                NaN        NaN           NaN   \n",
       "4                                               Rank        NaN  Value \\n (%)   \n",
       "5                                                NaN        NaN           NaN   \n",
       "\n",
       "                                          Unnamed: 7 Unnamed: 8  \\\n",
       "0  Probability at birth of not surviving to age 4...        NaN   \n",
       "1                                                NaN        NaN   \n",
       "2                                                NaN        NaN   \n",
       "3                                                NaN        NaN   \n",
       "4                                                NaN        NaN   \n",
       "5                                                NaN        NaN   \n",
       "\n",
       "                                          Unnamed: 9 Unnamed: 10  \\\n",
       "0                                                NaN         NaN   \n",
       "1  Adult illiteracy rateb,†\\n(% aged 15 and older...         NaN   \n",
       "2                                                NaN         NaN   \n",
       "3                                                NaN         NaN   \n",
       "4                                                NaN         NaN   \n",
       "5                                                NaN         NaN   \n",
       "\n",
       "                                         Unnamed: 11 Unnamed: 12  \\\n",
       "0                                                NaN         NaN   \n",
       "1  Population not using an improved water source†...         NaN   \n",
       "2                                                NaN         NaN   \n",
       "3                                                NaN         NaN   \n",
       "4                                                NaN         NaN   \n",
       "5                                                NaN         NaN   \n",
       "\n",
       "                                         Unnamed: 13 Unnamed: 14  \\\n",
       "0                                                MDG         NaN   \n",
       "1  Children under weight for age†\\n(% under age 5...         NaN   \n",
       "2                                                NaN         NaN   \n",
       "3                                                NaN         NaN   \n",
       "4                                                NaN         NaN   \n",
       "5                                                NaN         NaN   \n",
       "\n",
       "                                   Unnamed: 15 Unnamed: 16  \\\n",
       "0                                          MDG         NaN   \n",
       "1  Population below\\n income poverty line\\n(%)         NaN   \n",
       "2                                          NaN         NaN   \n",
       "3                        $1 a day \\n1990-2005d         NaN   \n",
       "4                                          NaN         NaN   \n",
       "5                                          NaN         NaN   \n",
       "\n",
       "              Unnamed: 17 Unnamed: 18  \\\n",
       "0                     NaN         NaN   \n",
       "1                     NaN         NaN   \n",
       "2                     NaN         NaN   \n",
       "3  $2 a day \\n1990-2005d          NaN   \n",
       "4                     NaN         NaN   \n",
       "5                     NaN         NaN   \n",
       "\n",
       "                                  Unnamed: 19 Unnamed: 20  \\\n",
       "0                                         NaN         NaN   \n",
       "1                                         NaN         NaN   \n",
       "2                                         NaN         NaN   \n",
       "3  National poverty line           1990-2004d         NaN   \n",
       "4                                         NaN         NaN   \n",
       "5                                         NaN         NaN   \n",
       "\n",
       "                             Unnamed: 21  \n",
       "0                                    NaN  \n",
       "1  HPI-1 rank minus income poverty rankc  \n",
       "2                                    NaN  \n",
       "3                                    NaN  \n",
       "4                                    NaN  \n",
       "5                                    NaN  "
      ]
     },
     "execution_count": 27,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# Rebase the header row\n",
    "df = pd.read_excel(source, header=7)\n",
    "# https://stackoverflow.com/a/51794989\n",
    "# First remove all columns (axis=1) with no values\n",
    "df = df.dropna(how=\"all\", axis=1)\n",
    "# Remove all rows (axis=0) with no values\n",
    "df = df.dropna(how=\"all\", axis=0).reset_index(drop=True)\n",
    "df.head(6)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "We could simply fix the columns, but lets get a list of the information that's there already and use that to guide the new names we need to include."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 28,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "[['HDI rank'],\n",
       " [],\n",
       " ['Human poverty index                         (HPI-1)', 'Rank'],\n",
       " [],\n",
       " ['Value \\n (%)'],\n",
       " ['Probability at birth of not surviving to age 40a,†\\n(% of cohort)\\n2000-05'],\n",
       " [],\n",
       " ['Adult illiteracy rateb,†\\n(% aged 15 and older)\\n1995-2005 '],\n",
       " [],\n",
       " ['Population not using an improved water source†\\n(%)\\n2004'],\n",
       " [],\n",
       " ['MDG', 'Children under weight for age†\\n(% under age 5)\\n1996-2005d '],\n",
       " [],\n",
       " ['MDG',\n",
       "  'Population below\\n income poverty line\\n(%)',\n",
       "  '$1 a day \\n1990-2005d'],\n",
       " [],\n",
       " ['$2 a day \\n1990-2005d '],\n",
       " [],\n",
       " ['National poverty line           1990-2004d'],\n",
       " [],\n",
       " ['HPI-1 rank minus income poverty rankc']]"
      ]
     },
     "execution_count": 28,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "columns = []\n",
    "for c in df.columns:\n",
    "    # For each column, get the values in the first 5 rows\n",
    "    # then keep all the strings (i.e. exclude if null) and append as a column\n",
    "    columns.append([str(v) for v in df[c][:5] if pd.notnull(v)])\n",
    "columns"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "There's a few columns that appear to have no names. But those columns weren't deleted when we removed empty columns and rows, so there is information in them. When in doubt, open the source file and have a look. When you do, you'll notice that the first \"empty\" column actually contains the country names, and the remaining empty columns contain the individual references. This allows us to come up with a list of new column names:"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 29,
   "metadata": {},
   "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>HDI rank</th>\n",
       "      <th>Country</th>\n",
       "      <th>Human poverty index (HPI-1) - Rank</th>\n",
       "      <th>Reference 1</th>\n",
       "      <th>Human poverty index (HPI-1) - Value (%)</th>\n",
       "      <th>Probability at birth of not surviving to age 40 (% of cohort) 2000-05</th>\n",
       "      <th>Reference 2</th>\n",
       "      <th>Adult illiteracy rate (% aged 15 and older) 1995-2005</th>\n",
       "      <th>Reference 3</th>\n",
       "      <th>Population not using an improved water source (%) 2004</th>\n",
       "      <th>Reference 4</th>\n",
       "      <th>Children under weight for age (% under age 5) 1996-2005</th>\n",
       "      <th>Reference 5</th>\n",
       "      <th>Population below income poverty line (%) - $1 a day 1990-2005</th>\n",
       "      <th>Reference 6</th>\n",
       "      <th>Population below income poverty line (%) - $2 a day 1990-2005</th>\n",
       "      <th>Reference 7</th>\n",
       "      <th>Population below income poverty line (%) - National poverty line 1990-2004</th>\n",
       "      <th>Reference 8</th>\n",
       "      <th>HPI-1 rank minus income poverty rank</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>HIGH HUMAN DEVELOPMENT</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>21</td>\n",
       "      <td>Hong Kong, China (SAR)</td>\n",
       "      <td>..</td>\n",
       "      <td>NaN</td>\n",
       "      <td>..</td>\n",
       "      <td>1.5</td>\n",
       "      <td>e</td>\n",
       "      <td>..</td>\n",
       "      <td>NaN</td>\n",
       "      <td>..</td>\n",
       "      <td>NaN</td>\n",
       "      <td>..</td>\n",
       "      <td>NaN</td>\n",
       "      <td>..</td>\n",
       "      <td>NaN</td>\n",
       "      <td>..</td>\n",
       "      <td>NaN</td>\n",
       "      <td>..</td>\n",
       "      <td>NaN</td>\n",
       "      <td>..</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>25</td>\n",
       "      <td>Singapore</td>\n",
       "      <td>7</td>\n",
       "      <td>NaN</td>\n",
       "      <td>5.2</td>\n",
       "      <td>1.8</td>\n",
       "      <td>NaN</td>\n",
       "      <td>7.5</td>\n",
       "      <td>NaN</td>\n",
       "      <td>0</td>\n",
       "      <td>NaN</td>\n",
       "      <td>3</td>\n",
       "      <td>NaN</td>\n",
       "      <td>..</td>\n",
       "      <td>NaN</td>\n",
       "      <td>..</td>\n",
       "      <td>NaN</td>\n",
       "      <td>..</td>\n",
       "      <td>NaN</td>\n",
       "      <td>..</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>26</td>\n",
       "      <td>Korea (Republic of)</td>\n",
       "      <td>..</td>\n",
       "      <td>NaN</td>\n",
       "      <td>..</td>\n",
       "      <td>2.5</td>\n",
       "      <td>NaN</td>\n",
       "      <td>1.0</td>\n",
       "      <td>NaN</td>\n",
       "      <td>8</td>\n",
       "      <td>NaN</td>\n",
       "      <td>..</td>\n",
       "      <td>NaN</td>\n",
       "      <td>&lt;2</td>\n",
       "      <td>NaN</td>\n",
       "      <td>&lt;2</td>\n",
       "      <td>NaN</td>\n",
       "      <td>..</td>\n",
       "      <td>NaN</td>\n",
       "      <td>..</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>28</td>\n",
       "      <td>Cyprus</td>\n",
       "      <td>..</td>\n",
       "      <td>NaN</td>\n",
       "      <td>..</td>\n",
       "      <td>2.4</td>\n",
       "      <td>NaN</td>\n",
       "      <td>3.2</td>\n",
       "      <td>NaN</td>\n",
       "      <td>0</td>\n",
       "      <td>NaN</td>\n",
       "      <td>..</td>\n",
       "      <td>NaN</td>\n",
       "      <td>..</td>\n",
       "      <td>NaN</td>\n",
       "      <td>..</td>\n",
       "      <td>NaN</td>\n",
       "      <td>..</td>\n",
       "      <td>NaN</td>\n",
       "      <td>..</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "                 HDI rank                 Country  \\\n",
       "0  HIGH HUMAN DEVELOPMENT                     NaN   \n",
       "1                      21  Hong Kong, China (SAR)   \n",
       "2                      25               Singapore   \n",
       "3                      26     Korea (Republic of)   \n",
       "4                      28                  Cyprus   \n",
       "\n",
       "  Human poverty index (HPI-1) - Rank Reference 1  \\\n",
       "0                                NaN         NaN   \n",
       "1                                 ..         NaN   \n",
       "2                                  7         NaN   \n",
       "3                                 ..         NaN   \n",
       "4                                 ..         NaN   \n",
       "\n",
       "  Human poverty index (HPI-1) - Value (%)  \\\n",
       "0                                     NaN   \n",
       "1                                      ..   \n",
       "2                                     5.2   \n",
       "3                                      ..   \n",
       "4                                      ..   \n",
       "\n",
       "  Probability at birth of not surviving to age 40 (% of cohort) 2000-05  \\\n",
       "0                                                NaN                      \n",
       "1                                                1.5                      \n",
       "2                                                1.8                      \n",
       "3                                                2.5                      \n",
       "4                                                2.4                      \n",
       "\n",
       "  Reference 2 Adult illiteracy rate (% aged 15 and older) 1995-2005  \\\n",
       "0         NaN                                                NaN      \n",
       "1           e                                                 ..      \n",
       "2         NaN                                                7.5      \n",
       "3         NaN                                                1.0      \n",
       "4         NaN                                                3.2      \n",
       "\n",
       "  Reference 3 Population not using an improved water source (%) 2004  \\\n",
       "0         NaN                                                NaN       \n",
       "1         NaN                                                 ..       \n",
       "2         NaN                                                  0       \n",
       "3         NaN                                                  8       \n",
       "4         NaN                                                  0       \n",
       "\n",
       "  Reference 4 Children under weight for age (% under age 5) 1996-2005  \\\n",
       "0         NaN                                                NaN        \n",
       "1         NaN                                                 ..        \n",
       "2         NaN                                                  3        \n",
       "3         NaN                                                 ..        \n",
       "4         NaN                                                 ..        \n",
       "\n",
       "  Reference 5 Population below income poverty line (%) - $1 a day 1990-2005  \\\n",
       "0         NaN                                                NaN              \n",
       "1         NaN                                                 ..              \n",
       "2         NaN                                                 ..              \n",
       "3         NaN                                                 <2              \n",
       "4         NaN                                                 ..              \n",
       "\n",
       "  Reference 6 Population below income poverty line (%) - $2 a day 1990-2005  \\\n",
       "0         NaN                                                NaN              \n",
       "1         NaN                                                 ..              \n",
       "2         NaN                                                 ..              \n",
       "3         NaN                                                 <2              \n",
       "4         NaN                                                 ..              \n",
       "\n",
       "  Reference 7  \\\n",
       "0         NaN   \n",
       "1         NaN   \n",
       "2         NaN   \n",
       "3         NaN   \n",
       "4         NaN   \n",
       "\n",
       "  Population below income poverty line (%) - National poverty line 1990-2004  \\\n",
       "0                                                NaN                           \n",
       "1                                                 ..                           \n",
       "2                                                 ..                           \n",
       "3                                                 ..                           \n",
       "4                                                 ..                           \n",
       "\n",
       "  Reference 8 HPI-1 rank minus income poverty rank  \n",
       "0         NaN                                  NaN  \n",
       "1         NaN                                   ..  \n",
       "2         NaN                                   ..  \n",
       "3         NaN                                   ..  \n",
       "4         NaN                                   ..  "
      ]
     },
     "execution_count": 29,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "columns = [\n",
    "    \"HDI rank\",\n",
    "    \"Country\",\n",
    "    \"Human poverty index (HPI-1) - Rank\",\n",
    "    \"Reference 1\",\n",
    "    \"Human poverty index (HPI-1) - Value (%)\",\n",
    "    \"Probability at birth of not surviving to age 40 (% of cohort) 2000-05\",\n",
    "    \"Reference 2\",\n",
    "    \"Adult illiteracy rate (% aged 15 and older) 1995-2005\",\n",
    "    \"Reference 3\",\n",
    "    \"Population not using an improved water source (%) 2004\",\n",
    "    \"Reference 4\",\n",
    "    \"Children under weight for age (% under age 5) 1996-2005\",\n",
    "    \"Reference 5\",\n",
    "    \"Population below income poverty line (%) - $1 a day 1990-2005\",\n",
    "    \"Reference 6\",\n",
    "    \"Population below income poverty line (%) - $2 a day 1990-2005\",   \n",
    "    \"Reference 7\",\n",
    "    \"Population below income poverty line (%) - National poverty line 1990-2004\",   \n",
    "    \"Reference 8\",\n",
    "    \"HPI-1 rank minus income poverty rank\"\n",
    "]\n",
    "# Reset the dataframe columns\n",
    "df.columns = columns\n",
    "# Remove the rows that contained the information we no longer need\n",
    "# and reset the index\n",
    "# np.arange(5) creates an array = [0, 1, 2, 3, 4, 5]\n",
    "df = df.drop(np.arange(5), axis=0).reset_index(drop=True)\n",
    "df.head()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "This file doesn't have a separate metadata file to accompany it. All the references and footnotes are literally at the bottom of the file. Have a look:"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 30,
   "metadata": {},
   "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>HDI rank</th>\n",
       "      <th>Country</th>\n",
       "      <th>Human poverty index (HPI-1) - Rank</th>\n",
       "      <th>Reference 1</th>\n",
       "      <th>Human poverty index (HPI-1) - Value (%)</th>\n",
       "      <th>Probability at birth of not surviving to age 40 (% of cohort) 2000-05</th>\n",
       "      <th>Reference 2</th>\n",
       "      <th>Adult illiteracy rate (% aged 15 and older) 1995-2005</th>\n",
       "      <th>Reference 3</th>\n",
       "      <th>Population not using an improved water source (%) 2004</th>\n",
       "      <th>Reference 4</th>\n",
       "      <th>Children under weight for age (% under age 5) 1996-2005</th>\n",
       "      <th>Reference 5</th>\n",
       "      <th>Population below income poverty line (%) - $1 a day 1990-2005</th>\n",
       "      <th>Reference 6</th>\n",
       "      <th>Population below income poverty line (%) - $2 a day 1990-2005</th>\n",
       "      <th>Reference 7</th>\n",
       "      <th>Population below income poverty line (%) - National poverty line 1990-2004</th>\n",
       "      <th>Reference 8</th>\n",
       "      <th>HPI-1 rank minus income poverty rank</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>127</th>\n",
       "      <td>176</td>\n",
       "      <td>Burkina Faso</td>\n",
       "      <td>106</td>\n",
       "      <td>NaN</td>\n",
       "      <td>55.8</td>\n",
       "      <td>26.5</td>\n",
       "      <td>NaN</td>\n",
       "      <td>76.4</td>\n",
       "      <td>NaN</td>\n",
       "      <td>39</td>\n",
       "      <td>NaN</td>\n",
       "      <td>38</td>\n",
       "      <td>NaN</td>\n",
       "      <td>27.2</td>\n",
       "      <td>NaN</td>\n",
       "      <td>71.8</td>\n",
       "      <td>NaN</td>\n",
       "      <td>46.4</td>\n",
       "      <td>NaN</td>\n",
       "      <td>23</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>128</th>\n",
       "      <td>177</td>\n",
       "      <td>Sierra Leone</td>\n",
       "      <td>102</td>\n",
       "      <td>NaN</td>\n",
       "      <td>51.7</td>\n",
       "      <td>45.6</td>\n",
       "      <td>NaN</td>\n",
       "      <td>65.2</td>\n",
       "      <td>NaN</td>\n",
       "      <td>43</td>\n",
       "      <td>NaN</td>\n",
       "      <td>27</td>\n",
       "      <td>NaN</td>\n",
       "      <td>57.0</td>\n",
       "      <td>e</td>\n",
       "      <td>74.5</td>\n",
       "      <td>e</td>\n",
       "      <td>70.2</td>\n",
       "      <td>NaN</td>\n",
       "      <td>4</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>129</th>\n",
       "      <td>NOTES</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>130</th>\n",
       "      <td>† Denotes indicators used to calculate the hum...</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>131</th>\n",
       "      <td>a. Data refer to the probability at birth of n...</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>132</th>\n",
       "      <td>SOURCES\\nColumn 1: Determined on the basis of ...</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>133</th>\n",
       "      <td>HPI-1 ranks for 108 developing countries and a...</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>134</th>\n",
       "      <td>1</td>\n",
       "      <td>Barbados</td>\n",
       "      <td>22</td>\n",
       "      <td>Turkey</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>45</td>\n",
       "      <td>Tunisia</td>\n",
       "      <td>NaN</td>\n",
       "      <td>68</td>\n",
       "      <td>Morocco</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>89</td>\n",
       "      <td>Angola</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>135</th>\n",
       "      <td>2</td>\n",
       "      <td>Uruguay</td>\n",
       "      <td>23</td>\n",
       "      <td>Brazil</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>46</td>\n",
       "      <td>Nicaragua</td>\n",
       "      <td>NaN</td>\n",
       "      <td>69</td>\n",
       "      <td>Sudan</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>90</td>\n",
       "      <td>Papua New Guinea</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>136</th>\n",
       "      <td>3</td>\n",
       "      <td>Chile</td>\n",
       "      <td>24</td>\n",
       "      <td>Thailand</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>47</td>\n",
       "      <td>Indonesia</td>\n",
       "      <td>NaN</td>\n",
       "      <td>70</td>\n",
       "      <td>Lao People's Democratic Republic</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>91</td>\n",
       "      <td>Zimbabwe</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>137</th>\n",
       "      <td>4</td>\n",
       "      <td>Argentina</td>\n",
       "      <td>25</td>\n",
       "      <td>Suriname</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>48</td>\n",
       "      <td>Egypt</td>\n",
       "      <td>NaN</td>\n",
       "      <td>71</td>\n",
       "      <td>Lesotho</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>92</td>\n",
       "      <td>Côte d'Ivoire</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>138</th>\n",
       "      <td>5</td>\n",
       "      <td>Costa Rica</td>\n",
       "      <td>26</td>\n",
       "      <td>Dominican Republic</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>49</td>\n",
       "      <td>Gabon</td>\n",
       "      <td>NaN</td>\n",
       "      <td>72</td>\n",
       "      <td>Uganda</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>93</td>\n",
       "      <td>Bangladesh</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>139</th>\n",
       "      <td>6</td>\n",
       "      <td>Cuba</td>\n",
       "      <td>27</td>\n",
       "      <td>Mauritius</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>50</td>\n",
       "      <td>Fiji</td>\n",
       "      <td>NaN</td>\n",
       "      <td>73</td>\n",
       "      <td>Swaziland</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>94</td>\n",
       "      <td>Gambia</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>140</th>\n",
       "      <td>7</td>\n",
       "      <td>Singapore</td>\n",
       "      <td>28</td>\n",
       "      <td>Peru</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>51</td>\n",
       "      <td>Algeria</td>\n",
       "      <td>NaN</td>\n",
       "      <td>74</td>\n",
       "      <td>Haiti</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>95</td>\n",
       "      <td>Timor-Leste</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>141</th>\n",
       "      <td>8</td>\n",
       "      <td>Saint Lucia</td>\n",
       "      <td>29</td>\n",
       "      <td>China</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>52</td>\n",
       "      <td>Myanmar</td>\n",
       "      <td>NaN</td>\n",
       "      <td>75</td>\n",
       "      <td>Madagascar</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>96</td>\n",
       "      <td>Zambia</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>142</th>\n",
       "      <td>9</td>\n",
       "      <td>Occupied Palestinian Territories</td>\n",
       "      <td>30</td>\n",
       "      <td>Iran (Islamic Republic of)</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>53</td>\n",
       "      <td>Solomon Islands</td>\n",
       "      <td>NaN</td>\n",
       "      <td>76</td>\n",
       "      <td>Eritrea</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>97</td>\n",
       "      <td>Senegal</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>143</th>\n",
       "      <td>10</td>\n",
       "      <td>Mexico</td>\n",
       "      <td>31</td>\n",
       "      <td>Syrian Arab Republic</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>54</td>\n",
       "      <td>Guatemala</td>\n",
       "      <td>NaN</td>\n",
       "      <td>77</td>\n",
       "      <td>Pakistan</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>98</td>\n",
       "      <td>Central African Republic</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>144</th>\n",
       "      <td>11</td>\n",
       "      <td>Jordan</td>\n",
       "      <td>32</td>\n",
       "      <td>Bolivia</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>55</td>\n",
       "      <td>South Africa</td>\n",
       "      <td>NaN</td>\n",
       "      <td>78</td>\n",
       "      <td>Rwanda</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>99</td>\n",
       "      <td>Guinea-Bissau</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>145</th>\n",
       "      <td>12</td>\n",
       "      <td>Trinidad and Tobago</td>\n",
       "      <td>33</td>\n",
       "      <td>Guyana</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>56</td>\n",
       "      <td>Vanuatu</td>\n",
       "      <td>NaN</td>\n",
       "      <td>79</td>\n",
       "      <td>Malawi</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>100</td>\n",
       "      <td>Benin</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>146</th>\n",
       "      <td>13</td>\n",
       "      <td>Qatar</td>\n",
       "      <td>34</td>\n",
       "      <td>Jamaica</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>57</td>\n",
       "      <td>Congo</td>\n",
       "      <td>NaN</td>\n",
       "      <td>80</td>\n",
       "      <td>Nigeria</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>101</td>\n",
       "      <td>Mozambique</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>147</th>\n",
       "      <td>14</td>\n",
       "      <td>Colombia</td>\n",
       "      <td>35</td>\n",
       "      <td>El Salvador</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>58</td>\n",
       "      <td>Namibia</td>\n",
       "      <td>NaN</td>\n",
       "      <td>81</td>\n",
       "      <td>Burundi</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>102</td>\n",
       "      <td>Sierra Leone</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>148</th>\n",
       "      <td>15</td>\n",
       "      <td>Panama</td>\n",
       "      <td>36</td>\n",
       "      <td>Viet Nam</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>59</td>\n",
       "      <td>Djibouti</td>\n",
       "      <td>NaN</td>\n",
       "      <td>82</td>\n",
       "      <td>Yemen</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>103</td>\n",
       "      <td>Guinea</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>149</th>\n",
       "      <td>16</td>\n",
       "      <td>Malaysia</td>\n",
       "      <td>37</td>\n",
       "      <td>Philippines</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>60</td>\n",
       "      <td>Kenya</td>\n",
       "      <td>NaN</td>\n",
       "      <td>83</td>\n",
       "      <td>Togo</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>104</td>\n",
       "      <td>Niger</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>150</th>\n",
       "      <td>17</td>\n",
       "      <td>United Arab Emirates</td>\n",
       "      <td>38</td>\n",
       "      <td>Cape Verde</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>61</td>\n",
       "      <td>Comoros</td>\n",
       "      <td>NaN</td>\n",
       "      <td>84</td>\n",
       "      <td>Nepal</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>105</td>\n",
       "      <td>Ethiopia</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>151</th>\n",
       "      <td>18</td>\n",
       "      <td>Lebanon</td>\n",
       "      <td>39</td>\n",
       "      <td>Sao Tome and Principe</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>62</td>\n",
       "      <td>India</td>\n",
       "      <td>NaN</td>\n",
       "      <td>85</td>\n",
       "      <td>Cambodia</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>106</td>\n",
       "      <td>Burkina Faso</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>152</th>\n",
       "      <td>19</td>\n",
       "      <td>Ecuador</td>\n",
       "      <td>40</td>\n",
       "      <td>Mongolia</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>63</td>\n",
       "      <td>Botswana</td>\n",
       "      <td>NaN</td>\n",
       "      <td>86</td>\n",
       "      <td>Bhutan</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>107</td>\n",
       "      <td>Mali</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>153</th>\n",
       "      <td>20</td>\n",
       "      <td>Paraguay</td>\n",
       "      <td>41</td>\n",
       "      <td>Honduras</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>64</td>\n",
       "      <td>Cameroon</td>\n",
       "      <td>NaN</td>\n",
       "      <td>87</td>\n",
       "      <td>Mauritania</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>108</td>\n",
       "      <td>Chad</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>154</th>\n",
       "      <td>21</td>\n",
       "      <td>Venezuela (Bolivarian Republic of)</td>\n",
       "      <td>42</td>\n",
       "      <td>Maldives</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>65</td>\n",
       "      <td>Ghana</td>\n",
       "      <td>NaN</td>\n",
       "      <td>88</td>\n",
       "      <td>Congo (Democratic Republic of the)</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>155</th>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>43</td>\n",
       "      <td>Belize</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>66</td>\n",
       "      <td>Equatorial Guinea</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>156</th>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>44</td>\n",
       "      <td>Sri Lanka</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>67</td>\n",
       "      <td>Tanzania (United Republic of)</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "                                              HDI rank  \\\n",
       "127                                                176   \n",
       "128                                                177   \n",
       "129                                              NOTES   \n",
       "130  † Denotes indicators used to calculate the hum...   \n",
       "131  a. Data refer to the probability at birth of n...   \n",
       "132  SOURCES\\nColumn 1: Determined on the basis of ...   \n",
       "133  HPI-1 ranks for 108 developing countries and a...   \n",
       "134                                                  1   \n",
       "135                                                  2   \n",
       "136                                                  3   \n",
       "137                                                  4   \n",
       "138                                                  5   \n",
       "139                                                  6   \n",
       "140                                                  7   \n",
       "141                                                  8   \n",
       "142                                                  9   \n",
       "143                                                 10   \n",
       "144                                                 11   \n",
       "145                                                 12   \n",
       "146                                                 13   \n",
       "147                                                 14   \n",
       "148                                                 15   \n",
       "149                                                 16   \n",
       "150                                                 17   \n",
       "151                                                 18   \n",
       "152                                                 19   \n",
       "153                                                 20   \n",
       "154                                                 21   \n",
       "155                                                NaN   \n",
       "156                                                NaN   \n",
       "\n",
       "                                Country Human poverty index (HPI-1) - Rank  \\\n",
       "127                        Burkina Faso                                106   \n",
       "128                        Sierra Leone                                102   \n",
       "129                                 NaN                                NaN   \n",
       "130                                 NaN                                NaN   \n",
       "131                                 NaN                                NaN   \n",
       "132                                 NaN                                NaN   \n",
       "133                                 NaN                                NaN   \n",
       "134                            Barbados                                 22   \n",
       "135                             Uruguay                                 23   \n",
       "136                               Chile                                 24   \n",
       "137                           Argentina                                 25   \n",
       "138                          Costa Rica                                 26   \n",
       "139                                Cuba                                 27   \n",
       "140                           Singapore                                 28   \n",
       "141                         Saint Lucia                                 29   \n",
       "142    Occupied Palestinian Territories                                 30   \n",
       "143                              Mexico                                 31   \n",
       "144                              Jordan                                 32   \n",
       "145                 Trinidad and Tobago                                 33   \n",
       "146                               Qatar                                 34   \n",
       "147                            Colombia                                 35   \n",
       "148                              Panama                                 36   \n",
       "149                            Malaysia                                 37   \n",
       "150                United Arab Emirates                                 38   \n",
       "151                             Lebanon                                 39   \n",
       "152                             Ecuador                                 40   \n",
       "153                            Paraguay                                 41   \n",
       "154  Venezuela (Bolivarian Republic of)                                 42   \n",
       "155                                 NaN                                 43   \n",
       "156                                 NaN                                 44   \n",
       "\n",
       "                    Reference 1 Human poverty index (HPI-1) - Value (%)  \\\n",
       "127                         NaN                                    55.8   \n",
       "128                         NaN                                    51.7   \n",
       "129                         NaN                                     NaN   \n",
       "130                         NaN                                     NaN   \n",
       "131                         NaN                                     NaN   \n",
       "132                         NaN                                     NaN   \n",
       "133                         NaN                                     NaN   \n",
       "134                      Turkey                                     NaN   \n",
       "135                      Brazil                                     NaN   \n",
       "136                    Thailand                                     NaN   \n",
       "137                    Suriname                                     NaN   \n",
       "138          Dominican Republic                                     NaN   \n",
       "139                   Mauritius                                     NaN   \n",
       "140                        Peru                                     NaN   \n",
       "141                       China                                     NaN   \n",
       "142  Iran (Islamic Republic of)                                     NaN   \n",
       "143        Syrian Arab Republic                                     NaN   \n",
       "144                     Bolivia                                     NaN   \n",
       "145                      Guyana                                     NaN   \n",
       "146                     Jamaica                                     NaN   \n",
       "147                 El Salvador                                     NaN   \n",
       "148                    Viet Nam                                     NaN   \n",
       "149                 Philippines                                     NaN   \n",
       "150                  Cape Verde                                     NaN   \n",
       "151       Sao Tome and Principe                                     NaN   \n",
       "152                    Mongolia                                     NaN   \n",
       "153                    Honduras                                     NaN   \n",
       "154                    Maldives                                     NaN   \n",
       "155                      Belize                                     NaN   \n",
       "156                   Sri Lanka                                     NaN   \n",
       "\n",
       "    Probability at birth of not surviving to age 40 (% of cohort) 2000-05  \\\n",
       "127                                               26.5                      \n",
       "128                                               45.6                      \n",
       "129                                                NaN                      \n",
       "130                                                NaN                      \n",
       "131                                                NaN                      \n",
       "132                                                NaN                      \n",
       "133                                                NaN                      \n",
       "134                                                NaN                      \n",
       "135                                                NaN                      \n",
       "136                                                NaN                      \n",
       "137                                                NaN                      \n",
       "138                                                NaN                      \n",
       "139                                                NaN                      \n",
       "140                                                NaN                      \n",
       "141                                                NaN                      \n",
       "142                                                NaN                      \n",
       "143                                                NaN                      \n",
       "144                                                NaN                      \n",
       "145                                                NaN                      \n",
       "146                                                NaN                      \n",
       "147                                                NaN                      \n",
       "148                                                NaN                      \n",
       "149                                                NaN                      \n",
       "150                                                NaN                      \n",
       "151                                                NaN                      \n",
       "152                                                NaN                      \n",
       "153                                                NaN                      \n",
       "154                                                NaN                      \n",
       "155                                                NaN                      \n",
       "156                                                NaN                      \n",
       "\n",
       "    Reference 2 Adult illiteracy rate (% aged 15 and older) 1995-2005  \\\n",
       "127         NaN                                               76.4      \n",
       "128         NaN                                               65.2      \n",
       "129         NaN                                                NaN      \n",
       "130         NaN                                                NaN      \n",
       "131         NaN                                                NaN      \n",
       "132         NaN                                                NaN      \n",
       "133         NaN                                                NaN      \n",
       "134          45                                            Tunisia      \n",
       "135          46                                          Nicaragua      \n",
       "136          47                                          Indonesia      \n",
       "137          48                                              Egypt      \n",
       "138          49                                              Gabon      \n",
       "139          50                                               Fiji      \n",
       "140          51                                            Algeria      \n",
       "141          52                                            Myanmar      \n",
       "142          53                                    Solomon Islands      \n",
       "143          54                                          Guatemala      \n",
       "144          55                                       South Africa      \n",
       "145          56                                            Vanuatu      \n",
       "146          57                                              Congo      \n",
       "147          58                                            Namibia      \n",
       "148          59                                           Djibouti      \n",
       "149          60                                              Kenya      \n",
       "150          61                                            Comoros      \n",
       "151          62                                              India      \n",
       "152          63                                           Botswana      \n",
       "153          64                                           Cameroon      \n",
       "154          65                                              Ghana      \n",
       "155          66                                  Equatorial Guinea      \n",
       "156          67                      Tanzania (United Republic of)      \n",
       "\n",
       "    Reference 3 Population not using an improved water source (%) 2004  \\\n",
       "127         NaN                                                 39       \n",
       "128         NaN                                                 43       \n",
       "129         NaN                                                NaN       \n",
       "130         NaN                                                NaN       \n",
       "131         NaN                                                NaN       \n",
       "132         NaN                                                NaN       \n",
       "133         NaN                                                NaN       \n",
       "134         NaN                                                 68       \n",
       "135         NaN                                                 69       \n",
       "136         NaN                                                 70       \n",
       "137         NaN                                                 71       \n",
       "138         NaN                                                 72       \n",
       "139         NaN                                                 73       \n",
       "140         NaN                                                 74       \n",
       "141         NaN                                                 75       \n",
       "142         NaN                                                 76       \n",
       "143         NaN                                                 77       \n",
       "144         NaN                                                 78       \n",
       "145         NaN                                                 79       \n",
       "146         NaN                                                 80       \n",
       "147         NaN                                                 81       \n",
       "148         NaN                                                 82       \n",
       "149         NaN                                                 83       \n",
       "150         NaN                                                 84       \n",
       "151         NaN                                                 85       \n",
       "152         NaN                                                 86       \n",
       "153         NaN                                                 87       \n",
       "154         NaN                                                 88       \n",
       "155         NaN                                                NaN       \n",
       "156         NaN                                                NaN       \n",
       "\n",
       "                            Reference 4  \\\n",
       "127                                 NaN   \n",
       "128                                 NaN   \n",
       "129                                 NaN   \n",
       "130                                 NaN   \n",
       "131                                 NaN   \n",
       "132                                 NaN   \n",
       "133                                 NaN   \n",
       "134                             Morocco   \n",
       "135                               Sudan   \n",
       "136    Lao People's Democratic Republic   \n",
       "137                             Lesotho   \n",
       "138                              Uganda   \n",
       "139                           Swaziland   \n",
       "140                               Haiti   \n",
       "141                          Madagascar   \n",
       "142                             Eritrea   \n",
       "143                            Pakistan   \n",
       "144                              Rwanda   \n",
       "145                              Malawi   \n",
       "146                             Nigeria   \n",
       "147                             Burundi   \n",
       "148                               Yemen   \n",
       "149                                Togo   \n",
       "150                               Nepal   \n",
       "151                            Cambodia   \n",
       "152                              Bhutan   \n",
       "153                          Mauritania   \n",
       "154  Congo (Democratic Republic of the)   \n",
       "155                                 NaN   \n",
       "156                                 NaN   \n",
       "\n",
       "    Children under weight for age (% under age 5) 1996-2005 Reference 5  \\\n",
       "127                                                 38              NaN   \n",
       "128                                                 27              NaN   \n",
       "129                                                NaN              NaN   \n",
       "130                                                NaN              NaN   \n",
       "131                                                NaN              NaN   \n",
       "132                                                NaN              NaN   \n",
       "133                                                NaN              NaN   \n",
       "134                                                NaN              NaN   \n",
       "135                                                NaN              NaN   \n",
       "136                                                NaN              NaN   \n",
       "137                                                NaN              NaN   \n",
       "138                                                NaN              NaN   \n",
       "139                                                NaN              NaN   \n",
       "140                                                NaN              NaN   \n",
       "141                                                NaN              NaN   \n",
       "142                                                NaN              NaN   \n",
       "143                                                NaN              NaN   \n",
       "144                                                NaN              NaN   \n",
       "145                                                NaN              NaN   \n",
       "146                                                NaN              NaN   \n",
       "147                                                NaN              NaN   \n",
       "148                                                NaN              NaN   \n",
       "149                                                NaN              NaN   \n",
       "150                                                NaN              NaN   \n",
       "151                                                NaN              NaN   \n",
       "152                                                NaN              NaN   \n",
       "153                                                NaN              NaN   \n",
       "154                                                NaN              NaN   \n",
       "155                                                NaN              NaN   \n",
       "156                                                NaN              NaN   \n",
       "\n",
       "    Population below income poverty line (%) - $1 a day 1990-2005  \\\n",
       "127                                               27.2              \n",
       "128                                               57.0              \n",
       "129                                                NaN              \n",
       "130                                                NaN              \n",
       "131                                                NaN              \n",
       "132                                                NaN              \n",
       "133                                                NaN              \n",
       "134                                                 89              \n",
       "135                                                 90              \n",
       "136                                                 91              \n",
       "137                                                 92              \n",
       "138                                                 93              \n",
       "139                                                 94              \n",
       "140                                                 95              \n",
       "141                                                 96              \n",
       "142                                                 97              \n",
       "143                                                 98              \n",
       "144                                                 99              \n",
       "145                                                100              \n",
       "146                                                101              \n",
       "147                                                102              \n",
       "148                                                103              \n",
       "149                                                104              \n",
       "150                                                105              \n",
       "151                                                106              \n",
       "152                                                107              \n",
       "153                                                108              \n",
       "154                                                NaN              \n",
       "155                                                NaN              \n",
       "156                                                NaN              \n",
       "\n",
       "                  Reference 6  \\\n",
       "127                       NaN   \n",
       "128                         e   \n",
       "129                       NaN   \n",
       "130                       NaN   \n",
       "131                       NaN   \n",
       "132                       NaN   \n",
       "133                       NaN   \n",
       "134                    Angola   \n",
       "135          Papua New Guinea   \n",
       "136                  Zimbabwe   \n",
       "137             Côte d'Ivoire   \n",
       "138                Bangladesh   \n",
       "139                    Gambia   \n",
       "140               Timor-Leste   \n",
       "141                    Zambia   \n",
       "142                   Senegal   \n",
       "143  Central African Republic   \n",
       "144             Guinea-Bissau   \n",
       "145                     Benin   \n",
       "146                Mozambique   \n",
       "147              Sierra Leone   \n",
       "148                    Guinea   \n",
       "149                     Niger   \n",
       "150                  Ethiopia   \n",
       "151              Burkina Faso   \n",
       "152                      Mali   \n",
       "153                      Chad   \n",
       "154                       NaN   \n",
       "155                       NaN   \n",
       "156                       NaN   \n",
       "\n",
       "    Population below income poverty line (%) - $2 a day 1990-2005 Reference 7  \\\n",
       "127                                               71.8                    NaN   \n",
       "128                                               74.5                      e   \n",
       "129                                                NaN                    NaN   \n",
       "130                                                NaN                    NaN   \n",
       "131                                                NaN                    NaN   \n",
       "132                                                NaN                    NaN   \n",
       "133                                                NaN                    NaN   \n",
       "134                                                NaN                    NaN   \n",
       "135                                                NaN                    NaN   \n",
       "136                                                NaN                    NaN   \n",
       "137                                                NaN                    NaN   \n",
       "138                                                NaN                    NaN   \n",
       "139                                                NaN                    NaN   \n",
       "140                                                NaN                    NaN   \n",
       "141                                                NaN                    NaN   \n",
       "142                                                NaN                    NaN   \n",
       "143                                                NaN                    NaN   \n",
       "144                                                NaN                    NaN   \n",
       "145                                                NaN                    NaN   \n",
       "146                                                NaN                    NaN   \n",
       "147                                                NaN                    NaN   \n",
       "148                                                NaN                    NaN   \n",
       "149                                                NaN                    NaN   \n",
       "150                                                NaN                    NaN   \n",
       "151                                                NaN                    NaN   \n",
       "152                                                NaN                    NaN   \n",
       "153                                                NaN                    NaN   \n",
       "154                                                NaN                    NaN   \n",
       "155                                                NaN                    NaN   \n",
       "156                                                NaN                    NaN   \n",
       "\n",
       "    Population below income poverty line (%) - National poverty line 1990-2004  \\\n",
       "127                                               46.4                           \n",
       "128                                               70.2                           \n",
       "129                                                NaN                           \n",
       "130                                                NaN                           \n",
       "131                                                NaN                           \n",
       "132                                                NaN                           \n",
       "133                                                NaN                           \n",
       "134                                                NaN                           \n",
       "135                                                NaN                           \n",
       "136                                                NaN                           \n",
       "137                                                NaN                           \n",
       "138                                                NaN                           \n",
       "139                                                NaN                           \n",
       "140                                                NaN                           \n",
       "141                                                NaN                           \n",
       "142                                                NaN                           \n",
       "143                                                NaN                           \n",
       "144                                                NaN                           \n",
       "145                                                NaN                           \n",
       "146                                                NaN                           \n",
       "147                                                NaN                           \n",
       "148                                                NaN                           \n",
       "149                                                NaN                           \n",
       "150                                                NaN                           \n",
       "151                                                NaN                           \n",
       "152                                                NaN                           \n",
       "153                                                NaN                           \n",
       "154                                                NaN                           \n",
       "155                                                NaN                           \n",
       "156                                                NaN                           \n",
       "\n",
       "    Reference 8 HPI-1 rank minus income poverty rank  \n",
       "127         NaN                                   23  \n",
       "128         NaN                                    4  \n",
       "129         NaN                                  NaN  \n",
       "130         NaN                                  NaN  \n",
       "131         NaN                                  NaN  \n",
       "132         NaN                                  NaN  \n",
       "133         NaN                                  NaN  \n",
       "134         NaN                                  NaN  \n",
       "135         NaN                                  NaN  \n",
       "136         NaN                                  NaN  \n",
       "137         NaN                                  NaN  \n",
       "138         NaN                                  NaN  \n",
       "139         NaN                                  NaN  \n",
       "140         NaN                                  NaN  \n",
       "141         NaN                                  NaN  \n",
       "142         NaN                                  NaN  \n",
       "143         NaN                                  NaN  \n",
       "144         NaN                                  NaN  \n",
       "145         NaN                                  NaN  \n",
       "146         NaN                                  NaN  \n",
       "147         NaN                                  NaN  \n",
       "148         NaN                                  NaN  \n",
       "149         NaN                                  NaN  \n",
       "150         NaN                                  NaN  \n",
       "151         NaN                                  NaN  \n",
       "152         NaN                                  NaN  \n",
       "153         NaN                                  NaN  \n",
       "154         NaN                                  NaN  \n",
       "155         NaN                                  NaN  \n",
       "156         NaN                                  NaN  "
      ]
     },
     "execution_count": 30,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df.tail(30)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "We need to delete this from the dataframe ___but___ we cannot lose this information. You should also create a metadata file containing this information to keep with your restructured data file, this way you have all the guidance a user may need, along with machine-readable data."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 31,
   "metadata": {},
   "outputs": [],
   "source": [
    "# Drop the rows from the bottom of the data we want to keep, to the end (defined as len(df))\n",
    "df = df.drop(np.arange(129,len(df)), axis=0).reset_index(drop=True)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "We have one other issue as well. There are three Human Development Index categories, arbitrarily defined as:\n",
    "\n",
    "- `LOW HUMAN DEVELOPMENT` for `HDI ranks > 155`\n",
    "- `MEDIUM HUMAN DEVELOPMENT` for `HDI ranks > 70`\n",
    "- `HIGH HUMAN DEVELOPMENT` for everything else\n",
    "\n",
    "We need to remove these rows (since they get in the way of the core data) and reassign them to columns."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 32,
   "metadata": {},
   "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>HDI rank</th>\n",
       "      <th>Country</th>\n",
       "      <th>Human poverty index (HPI-1) - Rank</th>\n",
       "      <th>Reference 1</th>\n",
       "      <th>Human poverty index (HPI-1) - Value (%)</th>\n",
       "      <th>Probability at birth of not surviving to age 40 (% of cohort) 2000-05</th>\n",
       "      <th>Reference 2</th>\n",
       "      <th>Adult illiteracy rate (% aged 15 and older) 1995-2005</th>\n",
       "      <th>Reference 3</th>\n",
       "      <th>Population not using an improved water source (%) 2004</th>\n",
       "      <th>...</th>\n",
       "      <th>Children under weight for age (% under age 5) 1996-2005</th>\n",
       "      <th>Reference 5</th>\n",
       "      <th>Population below income poverty line (%) - $1 a day 1990-2005</th>\n",
       "      <th>Reference 6</th>\n",
       "      <th>Population below income poverty line (%) - $2 a day 1990-2005</th>\n",
       "      <th>Reference 7</th>\n",
       "      <th>Population below income poverty line (%) - National poverty line 1990-2004</th>\n",
       "      <th>Reference 8</th>\n",
       "      <th>HPI-1 rank minus income poverty rank</th>\n",
       "      <th>HDI category</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>21</td>\n",
       "      <td>Hong Kong, China (SAR)</td>\n",
       "      <td>..</td>\n",
       "      <td>NaN</td>\n",
       "      <td>..</td>\n",
       "      <td>1.5</td>\n",
       "      <td>e</td>\n",
       "      <td>..</td>\n",
       "      <td>NaN</td>\n",
       "      <td>..</td>\n",
       "      <td>...</td>\n",
       "      <td>..</td>\n",
       "      <td>NaN</td>\n",
       "      <td>..</td>\n",
       "      <td>NaN</td>\n",
       "      <td>..</td>\n",
       "      <td>NaN</td>\n",
       "      <td>..</td>\n",
       "      <td>NaN</td>\n",
       "      <td>..</td>\n",
       "      <td>HIGH HUMAN DEVELOPMENT</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>25</td>\n",
       "      <td>Singapore</td>\n",
       "      <td>7</td>\n",
       "      <td>NaN</td>\n",
       "      <td>5.2</td>\n",
       "      <td>1.8</td>\n",
       "      <td>NaN</td>\n",
       "      <td>7.5</td>\n",
       "      <td>NaN</td>\n",
       "      <td>0</td>\n",
       "      <td>...</td>\n",
       "      <td>3</td>\n",
       "      <td>NaN</td>\n",
       "      <td>..</td>\n",
       "      <td>NaN</td>\n",
       "      <td>..</td>\n",
       "      <td>NaN</td>\n",
       "      <td>..</td>\n",
       "      <td>NaN</td>\n",
       "      <td>..</td>\n",
       "      <td>HIGH HUMAN DEVELOPMENT</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>26</td>\n",
       "      <td>Korea (Republic of)</td>\n",
       "      <td>..</td>\n",
       "      <td>NaN</td>\n",
       "      <td>..</td>\n",
       "      <td>2.5</td>\n",
       "      <td>NaN</td>\n",
       "      <td>1.0</td>\n",
       "      <td>NaN</td>\n",
       "      <td>8</td>\n",
       "      <td>...</td>\n",
       "      <td>..</td>\n",
       "      <td>NaN</td>\n",
       "      <td>&lt;2</td>\n",
       "      <td>NaN</td>\n",
       "      <td>&lt;2</td>\n",
       "      <td>NaN</td>\n",
       "      <td>..</td>\n",
       "      <td>NaN</td>\n",
       "      <td>..</td>\n",
       "      <td>HIGH HUMAN DEVELOPMENT</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>28</td>\n",
       "      <td>Cyprus</td>\n",
       "      <td>..</td>\n",
       "      <td>NaN</td>\n",
       "      <td>..</td>\n",
       "      <td>2.4</td>\n",
       "      <td>NaN</td>\n",
       "      <td>3.2</td>\n",
       "      <td>NaN</td>\n",
       "      <td>0</td>\n",
       "      <td>...</td>\n",
       "      <td>..</td>\n",
       "      <td>NaN</td>\n",
       "      <td>..</td>\n",
       "      <td>NaN</td>\n",
       "      <td>..</td>\n",
       "      <td>NaN</td>\n",
       "      <td>..</td>\n",
       "      <td>NaN</td>\n",
       "      <td>..</td>\n",
       "      <td>HIGH HUMAN DEVELOPMENT</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>30</td>\n",
       "      <td>Brunei Darussalam</td>\n",
       "      <td>..</td>\n",
       "      <td>NaN</td>\n",
       "      <td>..</td>\n",
       "      <td>3.0</td>\n",
       "      <td>NaN</td>\n",
       "      <td>7.3</td>\n",
       "      <td>NaN</td>\n",
       "      <td>..</td>\n",
       "      <td>...</td>\n",
       "      <td>..</td>\n",
       "      <td>NaN</td>\n",
       "      <td>..</td>\n",
       "      <td>NaN</td>\n",
       "      <td>..</td>\n",
       "      <td>NaN</td>\n",
       "      <td>..</td>\n",
       "      <td>NaN</td>\n",
       "      <td>..</td>\n",
       "      <td>HIGH HUMAN DEVELOPMENT</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "<p>5 rows × 21 columns</p>\n",
       "</div>"
      ],
      "text/plain": [
       "  HDI rank                 Country Human poverty index (HPI-1) - Rank  \\\n",
       "0       21  Hong Kong, China (SAR)                                 ..   \n",
       "1       25               Singapore                                  7   \n",
       "2       26     Korea (Republic of)                                 ..   \n",
       "3       28                  Cyprus                                 ..   \n",
       "4       30       Brunei Darussalam                                 ..   \n",
       "\n",
       "  Reference 1 Human poverty index (HPI-1) - Value (%)  \\\n",
       "0         NaN                                      ..   \n",
       "1         NaN                                     5.2   \n",
       "2         NaN                                      ..   \n",
       "3         NaN                                      ..   \n",
       "4         NaN                                      ..   \n",
       "\n",
       "  Probability at birth of not surviving to age 40 (% of cohort) 2000-05  \\\n",
       "0                                                1.5                      \n",
       "1                                                1.8                      \n",
       "2                                                2.5                      \n",
       "3                                                2.4                      \n",
       "4                                                3.0                      \n",
       "\n",
       "  Reference 2 Adult illiteracy rate (% aged 15 and older) 1995-2005  \\\n",
       "0           e                                                 ..      \n",
       "1         NaN                                                7.5      \n",
       "2         NaN                                                1.0      \n",
       "3         NaN                                                3.2      \n",
       "4         NaN                                                7.3      \n",
       "\n",
       "  Reference 3 Population not using an improved water source (%) 2004  ...  \\\n",
       "0         NaN                                                 ..      ...   \n",
       "1         NaN                                                  0      ...   \n",
       "2         NaN                                                  8      ...   \n",
       "3         NaN                                                  0      ...   \n",
       "4         NaN                                                 ..      ...   \n",
       "\n",
       "  Children under weight for age (% under age 5) 1996-2005 Reference 5  \\\n",
       "0                                                 ..              NaN   \n",
       "1                                                  3              NaN   \n",
       "2                                                 ..              NaN   \n",
       "3                                                 ..              NaN   \n",
       "4                                                 ..              NaN   \n",
       "\n",
       "  Population below income poverty line (%) - $1 a day 1990-2005 Reference 6  \\\n",
       "0                                                 ..                    NaN   \n",
       "1                                                 ..                    NaN   \n",
       "2                                                 <2                    NaN   \n",
       "3                                                 ..                    NaN   \n",
       "4                                                 ..                    NaN   \n",
       "\n",
       "  Population below income poverty line (%) - $2 a day 1990-2005 Reference 7  \\\n",
       "0                                                 ..                    NaN   \n",
       "1                                                 ..                    NaN   \n",
       "2                                                 <2                    NaN   \n",
       "3                                                 ..                    NaN   \n",
       "4                                                 ..                    NaN   \n",
       "\n",
       "  Population below income poverty line (%) - National poverty line 1990-2004  \\\n",
       "0                                                 ..                           \n",
       "1                                                 ..                           \n",
       "2                                                 ..                           \n",
       "3                                                 ..                           \n",
       "4                                                 ..                           \n",
       "\n",
       "  Reference 8 HPI-1 rank minus income poverty rank            HDI category  \n",
       "0         NaN                                   ..  HIGH HUMAN DEVELOPMENT  \n",
       "1         NaN                                   ..  HIGH HUMAN DEVELOPMENT  \n",
       "2         NaN                                   ..  HIGH HUMAN DEVELOPMENT  \n",
       "3         NaN                                   ..  HIGH HUMAN DEVELOPMENT  \n",
       "4         NaN                                   ..  HIGH HUMAN DEVELOPMENT  \n",
       "\n",
       "[5 rows x 21 columns]"
      ]
     },
     "execution_count": 32,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# Categories\n",
    "hdi_categories = [\"HIGH HUMAN DEVELOPMENT\", \"MEDIUM HUMAN DEVELOPMENT\", \"LOW HUMAN DEVELOPMENT\"]\n",
    "# Get rid of rows with the HDI categories\n",
    "# https://stackoverflow.com/a/27360130 & https://stackoverflow.com/a/12098586\n",
    "df = df.drop(df[df[\"HDI rank\"].isin(hdi_categories)].index, axis=0).reset_index(drop=True)\n",
    "# Set \"HDI rank\" to integers\n",
    "df[\"HDI rank\"] = pd.to_numeric(df[\"HDI rank\"])\n",
    "# Reference for np.where https://docs.scipy.org/doc/numpy/reference/generated/numpy.where.html\n",
    "# Think of this as being similar to an if-then statement : where(test is true, true-thing, false-thing)\n",
    "# this can also be nested: where(test is true, true-thing, where(new-test is true, true-thing, false-thing))\n",
    "df.loc[:, \"HDI category\"] = np.where(df[\"HDI rank\"] > 155,\n",
    "                                     \"LOW HUMAN DEVELOPMENT\",\n",
    "                                     np.where(df[\"HDI rank\"] > 70,\n",
    "                                              \"MEDIUM HUMAN DEVELOPMENT\",\n",
    "                                              \"HIGH HUMAN DEVELOPMENT\"\n",
    "                                             )\n",
    "                                    )\n",
    "df.sort_values(by=\"HDI rank\", inplace=True)\n",
    "df.head()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "There is a lot more we can do to clean this spreadsheet, but this is a good start and teaches you a great deal about what you can do with pandas. When you're done, remember to save your dataframe as a `CSV` using `.to_csv` as you did in the earlier exercise.\n",
    "\n",
    "---\n",
    "\n",
    "## 2.6 Lesson tutorial\n",
    "\n",
    "<div class=\"alert alert-block alert-success\">\n",
    "    <p><b>Tutorial:</b></p>\n",
    "    <p>Pick a spreadsheet from <a href=\"data/lesson-spreadsheet/\">training data</a> and restructure it according to the techniques and requirements presented in this lesson.</p>\n",
    "    <p>Remember to create a metadata file along with your data file.</p>\n",
    "</div>\n",
    "\n",
    "Please complete this tutorial before beginning the next lesson."
   ]
  }
 ],
 "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.8.10"
  },
  "latex_envs": {
   "LaTeX_envs_menu_present": true,
   "autoclose": false,
   "autocomplete": true,
   "bibliofile": "biblio.bib",
   "cite_by": "apalike",
   "current_citInitial": 1,
   "eqLabelWithNumbers": true,
   "eqNumInitial": 1,
   "hotkeys": {
    "equation": "Ctrl-E",
    "itemize": "Ctrl-I"
   },
   "labels_anchors": false,
   "latex_user_defs": false,
   "report_style_numbering": false,
   "user_envs_cfg": false
  }
 },
 "nbformat": 4,
 "nbformat_minor": 4
}