Json_exercise.ipynb

{
 "cells": [
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "****\n",
    "# JSON exercise\n",
    "\n",
    "Using data in file 'data/world_bank_projects.json',\n",
    "1. Find the 10 countries with most projects\n",
    "2. Find the top 10 major project themes (using column 'mjtheme_namecode')\n",
    "3. In 2. above you will notice that some entries have only the code and the name is missing. Create a dataframe with the missing names filled in."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 2,
   "metadata": {},
   "outputs": [],
   "source": [
    "import pandas as pd\n",
    "import json\n",
    "from pandas.io.json import json_normalize\n",
    "import numpy as np"
   ]
  },
  {
   "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>sector</th>\n",
       "      <th>supplementprojectflg</th>\n",
       "      <th>projectfinancialtype</th>\n",
       "      <th>prodline</th>\n",
       "      <th>mjtheme</th>\n",
       "      <th>idacommamt</th>\n",
       "      <th>impagency</th>\n",
       "      <th>project_name</th>\n",
       "      <th>mjthemecode</th>\n",
       "      <th>closingdate</th>\n",
       "      <th>...</th>\n",
       "      <th>majorsector_percent</th>\n",
       "      <th>board_approval_month</th>\n",
       "      <th>theme_namecode</th>\n",
       "      <th>countryname</th>\n",
       "      <th>url</th>\n",
       "      <th>source</th>\n",
       "      <th>projectstatusdisplay</th>\n",
       "      <th>ibrdcommamt</th>\n",
       "      <th>sector_namecode</th>\n",
       "      <th>_id</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>[{'Name': 'Primary education'}, {'Name': 'Seco...</td>\n",
       "      <td>N</td>\n",
       "      <td>IDA</td>\n",
       "      <td>PE</td>\n",
       "      <td>[Human development]</td>\n",
       "      <td>130000000</td>\n",
       "      <td>MINISTRY OF EDUCATION</td>\n",
       "      <td>Ethiopia General Education Quality Improvement...</td>\n",
       "      <td>8,11</td>\n",
       "      <td>2018-07-07T00:00:00Z</td>\n",
       "      <td>...</td>\n",
       "      <td>[{'Percent': 46, 'Name': 'Education'}, {'Perce...</td>\n",
       "      <td>November</td>\n",
       "      <td>[{'code': '65', 'name': 'Education for all'}]</td>\n",
       "      <td>Federal Democratic Republic of Ethiopia</td>\n",
       "      <td>http://www.worldbank.org/projects/P129828/ethi...</td>\n",
       "      <td>IBRD</td>\n",
       "      <td>Active</td>\n",
       "      <td>0</td>\n",
       "      <td>[{'code': 'EP', 'name': 'Primary education'}, ...</td>\n",
       "      <td>{'$oid': '52b213b38594d8a2be17c780'}</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>[{'Name': 'Public administration- Other social...</td>\n",
       "      <td>N</td>\n",
       "      <td>OTHER</td>\n",
       "      <td>RE</td>\n",
       "      <td>[Economic management, Social protection and ri...</td>\n",
       "      <td>0</td>\n",
       "      <td>MINISTRY OF FINANCE</td>\n",
       "      <td>TN: DTF Social Protection Reforms Support</td>\n",
       "      <td>1,6</td>\n",
       "      <td>NaN</td>\n",
       "      <td>...</td>\n",
       "      <td>[{'Percent': 70, 'Name': 'Public Administratio...</td>\n",
       "      <td>November</td>\n",
       "      <td>[{'code': '24', 'name': 'Other economic manage...</td>\n",
       "      <td>Republic of Tunisia</td>\n",
       "      <td>http://www.worldbank.org/projects/P144674?lang=en</td>\n",
       "      <td>IBRD</td>\n",
       "      <td>Active</td>\n",
       "      <td>0</td>\n",
       "      <td>[{'code': 'BS', 'name': 'Public administration...</td>\n",
       "      <td>{'$oid': '52b213b38594d8a2be17c781'}</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "<p>2 rows × 50 columns</p>\n",
       "</div>"
      ],
      "text/plain": [
       "                                              sector supplementprojectflg  \\\n",
       "0  [{'Name': 'Primary education'}, {'Name': 'Seco...                    N   \n",
       "1  [{'Name': 'Public administration- Other social...                    N   \n",
       "\n",
       "  projectfinancialtype prodline  \\\n",
       "0                  IDA       PE   \n",
       "1                OTHER       RE   \n",
       "\n",
       "                                             mjtheme  idacommamt  \\\n",
       "0                                [Human development]   130000000   \n",
       "1  [Economic management, Social protection and ri...           0   \n",
       "\n",
       "               impagency                                       project_name  \\\n",
       "0  MINISTRY OF EDUCATION  Ethiopia General Education Quality Improvement...   \n",
       "1    MINISTRY OF FINANCE          TN: DTF Social Protection Reforms Support   \n",
       "\n",
       "  mjthemecode           closingdate  ...  \\\n",
       "0        8,11  2018-07-07T00:00:00Z  ...   \n",
       "1         1,6                   NaN  ...   \n",
       "\n",
       "                                 majorsector_percent board_approval_month  \\\n",
       "0  [{'Percent': 46, 'Name': 'Education'}, {'Perce...             November   \n",
       "1  [{'Percent': 70, 'Name': 'Public Administratio...             November   \n",
       "\n",
       "                                      theme_namecode  \\\n",
       "0      [{'code': '65', 'name': 'Education for all'}]   \n",
       "1  [{'code': '24', 'name': 'Other economic manage...   \n",
       "\n",
       "                               countryname  \\\n",
       "0  Federal Democratic Republic of Ethiopia   \n",
       "1                      Republic of Tunisia   \n",
       "\n",
       "                                                 url source  \\\n",
       "0  http://www.worldbank.org/projects/P129828/ethi...   IBRD   \n",
       "1  http://www.worldbank.org/projects/P144674?lang=en   IBRD   \n",
       "\n",
       "  projectstatusdisplay ibrdcommamt  \\\n",
       "0               Active           0   \n",
       "1               Active           0   \n",
       "\n",
       "                                     sector_namecode  \\\n",
       "0  [{'code': 'EP', 'name': 'Primary education'}, ...   \n",
       "1  [{'code': 'BS', 'name': 'Public administration...   \n",
       "\n",
       "                                    _id  \n",
       "0  {'$oid': '52b213b38594d8a2be17c780'}  \n",
       "1  {'$oid': '52b213b38594d8a2be17c781'}  \n",
       "\n",
       "[2 rows x 50 columns]"
      ]
     },
     "execution_count": 3,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# Create a DataFrame from a JSON file\n",
    "data_file = 'data/world_bank_projects.json'\n",
    "df = pd.read_json(data_file)\n",
    "df.head(2)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 4,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "<class 'pandas.core.frame.DataFrame'>\n",
      "RangeIndex: 500 entries, 0 to 499\n",
      "Data columns (total 50 columns):\n",
      "sector                      500 non-null object\n",
      "supplementprojectflg        498 non-null object\n",
      "projectfinancialtype        500 non-null object\n",
      "prodline                    500 non-null object\n",
      "mjtheme                     491 non-null object\n",
      "idacommamt                  500 non-null int64\n",
      "impagency                   472 non-null object\n",
      "project_name                500 non-null object\n",
      "mjthemecode                 500 non-null object\n",
      "closingdate                 370 non-null object\n",
      "totalcommamt                500 non-null int64\n",
      "id                          500 non-null object\n",
      "mjsector_namecode           500 non-null object\n",
      "docty                       446 non-null object\n",
      "sector1                     500 non-null object\n",
      "lendinginstr                495 non-null object\n",
      "countrycode                 500 non-null object\n",
      "sector2                     380 non-null object\n",
      "totalamt                    500 non-null int64\n",
      "mjtheme_namecode            500 non-null object\n",
      "boardapprovaldate           500 non-null object\n",
      "countryshortname            500 non-null object\n",
      "sector4                     174 non-null object\n",
      "prodlinetext                500 non-null object\n",
      "productlinetype             500 non-null object\n",
      "regionname                  500 non-null object\n",
      "status                      500 non-null object\n",
      "country_namecode            500 non-null object\n",
      "envassesmentcategorycode    430 non-null object\n",
      "project_abstract            362 non-null object\n",
      "approvalfy                  500 non-null int64\n",
      "projectdocs                 446 non-null object\n",
      "lendprojectcost             500 non-null int64\n",
      "lendinginstrtype            495 non-null object\n",
      "theme1                      500 non-null object\n",
      "grantamt                    500 non-null int64\n",
      "themecode                   491 non-null object\n",
      "borrower                    485 non-null object\n",
      "sectorcode                  500 non-null object\n",
      "sector3                     265 non-null object\n",
      "majorsector_percent         500 non-null object\n",
      "board_approval_month        500 non-null object\n",
      "theme_namecode              491 non-null object\n",
      "countryname                 500 non-null object\n",
      "url                         500 non-null object\n",
      "source                      500 non-null object\n",
      "projectstatusdisplay        500 non-null object\n",
      "ibrdcommamt                 500 non-null int64\n",
      "sector_namecode             500 non-null object\n",
      "_id                         500 non-null object\n",
      "dtypes: int64(7), object(43)\n",
      "memory usage: 195.4+ KB\n"
     ]
    }
   ],
   "source": [
    "# Check the data information\n",
    "df.info()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## Find the 10 countries with most projects"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 5,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "Republic of Indonesia              19\n",
       "People's Republic of China         19\n",
       "Socialist Republic of Vietnam      17\n",
       "Republic of India                  16\n",
       "Republic of Yemen                  13\n",
       "Kingdom of Morocco                 12\n",
       "Nepal                              12\n",
       "People's Republic of Bangladesh    12\n",
       "Africa                             11\n",
       "Republic of Mozambique             11\n",
       "Name: countryname, dtype: int64"
      ]
     },
     "execution_count": 5,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# Top 10 countries with most projects in 'countryname' column\n",
    "countryname_values = df['countryname'].value_counts()\n",
    "countryname_values.head(10)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 6,
   "metadata": {
    "scrolled": true
   },
   "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>countrycode</th>\n",
       "      <th>countryshortname</th>\n",
       "      <th>country_namecode</th>\n",
       "      <th>countryname</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>45</th>\n",
       "      <td>3A</td>\n",
       "      <td>Africa</td>\n",
       "      <td>Africa!$!3A</td>\n",
       "      <td>Africa</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>46</th>\n",
       "      <td>3A</td>\n",
       "      <td>Africa</td>\n",
       "      <td>Africa!$!3A</td>\n",
       "      <td>Africa</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>51</th>\n",
       "      <td>3A</td>\n",
       "      <td>Africa</td>\n",
       "      <td>Africa!$!3A</td>\n",
       "      <td>Africa</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>58</th>\n",
       "      <td>3A</td>\n",
       "      <td>Africa</td>\n",
       "      <td>Africa!$!3A</td>\n",
       "      <td>Africa</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>65</th>\n",
       "      <td>3A</td>\n",
       "      <td>Africa</td>\n",
       "      <td>Africa!$!3A</td>\n",
       "      <td>Africa</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>99</th>\n",
       "      <td>3A</td>\n",
       "      <td>Africa</td>\n",
       "      <td>Africa!$!3A</td>\n",
       "      <td>Africa</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>167</th>\n",
       "      <td>3A</td>\n",
       "      <td>Africa</td>\n",
       "      <td>Africa!$!3A</td>\n",
       "      <td>Africa</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>184</th>\n",
       "      <td>3A</td>\n",
       "      <td>Africa</td>\n",
       "      <td>Africa!$!3A</td>\n",
       "      <td>Africa</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>287</th>\n",
       "      <td>3A</td>\n",
       "      <td>Africa</td>\n",
       "      <td>Africa!$!3A</td>\n",
       "      <td>Africa</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>353</th>\n",
       "      <td>3A</td>\n",
       "      <td>Africa</td>\n",
       "      <td>Africa!$!3A</td>\n",
       "      <td>Africa</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>449</th>\n",
       "      <td>3A</td>\n",
       "      <td>Africa</td>\n",
       "      <td>Africa!$!3A</td>\n",
       "      <td>Africa</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "    countrycode countryshortname country_namecode countryname\n",
       "45           3A           Africa      Africa!$!3A      Africa\n",
       "46           3A           Africa      Africa!$!3A      Africa\n",
       "51           3A           Africa      Africa!$!3A      Africa\n",
       "58           3A           Africa      Africa!$!3A      Africa\n",
       "65           3A           Africa      Africa!$!3A      Africa\n",
       "99           3A           Africa      Africa!$!3A      Africa\n",
       "167          3A           Africa      Africa!$!3A      Africa\n",
       "184          3A           Africa      Africa!$!3A      Africa\n",
       "287          3A           Africa      Africa!$!3A      Africa\n",
       "353          3A           Africa      Africa!$!3A      Africa\n",
       "449          3A           Africa      Africa!$!3A      Africa"
      ]
     },
     "execution_count": 6,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# 'Africa' is not a country name.\n",
    "\n",
    "# Check the other coutry-related columns\n",
    "africa = df[df['countryname'] == 'Africa']\n",
    "africa[['countrycode', 'countryshortname', 'country_namecode', 'countryname']]"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 7,
   "metadata": {},
   "outputs": [],
   "source": [
    "# No hint in the other country-related columns, either\n",
    "\n",
    "# Remove the rows which have 'Africa' in 'countryname' column\n",
    "df_removed = df[df['countryname'] != 'Africa']"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 8,
   "metadata": {},
   "outputs": [],
   "source": [
    "# Count the number of projects by country\n",
    "df_removed_values = df_removed['countryname'].value_counts()\n",
    "\n",
    "# Reset the index to start from 1\n",
    "df_removed_values = df_removed_values.reset_index()\n",
    "df_removed_values.index = df_removed_values.index + 1\n",
    "\n",
    "# Change the index name and column labels\n",
    "df_removed_values.index.name = 'Ranking'\n",
    "df_removed_values.columns = ['Country', '#_of_projects']"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "**TOP10 countries with most projects**"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 9,
   "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</th>\n",
       "      <th>#_of_projects</th>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Ranking</th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>Republic of Indonesia</td>\n",
       "      <td>19</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>People's Republic of China</td>\n",
       "      <td>19</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>Socialist Republic of Vietnam</td>\n",
       "      <td>17</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>Republic of India</td>\n",
       "      <td>16</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>5</th>\n",
       "      <td>Republic of Yemen</td>\n",
       "      <td>13</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>6</th>\n",
       "      <td>Kingdom of Morocco</td>\n",
       "      <td>12</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>7</th>\n",
       "      <td>People's Republic of Bangladesh</td>\n",
       "      <td>12</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>8</th>\n",
       "      <td>Nepal</td>\n",
       "      <td>12</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>9</th>\n",
       "      <td>Republic of Mozambique</td>\n",
       "      <td>11</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>10</th>\n",
       "      <td>Burkina Faso</td>\n",
       "      <td>9</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "                                 Country  #_of_projects\n",
       "Ranking                                                \n",
       "1                  Republic of Indonesia             19\n",
       "2             People's Republic of China             19\n",
       "3          Socialist Republic of Vietnam             17\n",
       "4                      Republic of India             16\n",
       "5                      Republic of Yemen             13\n",
       "6                     Kingdom of Morocco             12\n",
       "7        People's Republic of Bangladesh             12\n",
       "8                                  Nepal             12\n",
       "9                 Republic of Mozambique             11\n",
       "10                          Burkina Faso              9"
      ]
     },
     "execution_count": 9,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# Top 10 ranking\n",
    "df_removed_values.head(10)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## Find the top 10 major project themes"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 10,
   "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>code</th>\n",
       "      <th>name</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>8</td>\n",
       "      <td>Human development</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>11</td>\n",
       "      <td></td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>1</td>\n",
       "      <td>Economic management</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>6</td>\n",
       "      <td>Social protection and risk management</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>5</td>\n",
       "      <td>Trade and integration</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "  code                                   name\n",
       "0    8                      Human development\n",
       "1   11                                       \n",
       "2    1                    Economic management\n",
       "3    6  Social protection and risk management\n",
       "4    5                  Trade and integration"
      ]
     },
     "execution_count": 10,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# Load JSON: json_data\n",
    "with open(data_file) as json_file:\n",
    "    json_data = json.load(json_file)\n",
    "    \n",
    "# Create a table from nested dictionaries\n",
    "theme = json_normalize(json_data, 'mjtheme_namecode')\n",
    "theme.head()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 11,
   "metadata": {
    "scrolled": true
   },
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "<class 'pandas.core.frame.DataFrame'>\n",
      "RangeIndex: 1499 entries, 0 to 1498\n",
      "Data columns (total 2 columns):\n",
      "code    1499 non-null object\n",
      "name    1499 non-null object\n",
      "dtypes: object(2)\n",
      "memory usage: 23.5+ KB\n"
     ]
    }
   ],
   "source": [
    "# Check the data information: no NaN\n",
    "theme.info()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 12,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "code    122\n",
       "name    122\n",
       "dtype: int64"
      ]
     },
     "execution_count": 12,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# Find missing data in 'name' column: 122 missing data\n",
    "theme[theme['name'] == ''].count()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 13,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "code    0\n",
       "name    0\n",
       "dtype: int64"
      ]
     },
     "execution_count": 13,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# Find missing data in 'code' column: no missing data\n",
    "theme[theme['code'] == ''].count()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 14,
   "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>code</th>\n",
       "      <th>name</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>8</td>\n",
       "      <td>Human development</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>1</td>\n",
       "      <td>Economic management</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>6</td>\n",
       "      <td>Social protection and risk management</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>5</td>\n",
       "      <td>Trade and integration</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>5</th>\n",
       "      <td>2</td>\n",
       "      <td>Public sector governance</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>6</th>\n",
       "      <td>11</td>\n",
       "      <td>Environment and natural resources management</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>8</th>\n",
       "      <td>7</td>\n",
       "      <td>Social dev/gender/inclusion</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>11</th>\n",
       "      <td>4</td>\n",
       "      <td>Financial and private sector development</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>18</th>\n",
       "      <td>10</td>\n",
       "      <td>Rural development</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>53</th>\n",
       "      <td>9</td>\n",
       "      <td>Urban development</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>252</th>\n",
       "      <td>3</td>\n",
       "      <td>Rule of law</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "    code                                          name\n",
       "0      8                             Human development\n",
       "2      1                           Economic management\n",
       "3      6         Social protection and risk management\n",
       "4      5                         Trade and integration\n",
       "5      2                      Public sector governance\n",
       "6     11  Environment and natural resources management\n",
       "8      7                   Social dev/gender/inclusion\n",
       "11     4      Financial and private sector development\n",
       "18    10                             Rural development\n",
       "53     9                             Urban development\n",
       "252    3                                   Rule of law"
      ]
     },
     "execution_count": 14,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# Remove rows with missing data\n",
    "theme_removed = theme[theme['name'] != '']\n",
    "\n",
    "# Drop duplocates to make a code-name contrastive table\n",
    "unique = theme_removed.drop_duplicates()\n",
    "unique"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 15,
   "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>code</th>\n",
       "      <th>#_of_projects</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>11</td>\n",
       "      <td>250</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>10</td>\n",
       "      <td>216</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>8</td>\n",
       "      <td>210</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>2</td>\n",
       "      <td>199</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>6</td>\n",
       "      <td>168</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>5</th>\n",
       "      <td>4</td>\n",
       "      <td>146</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>6</th>\n",
       "      <td>7</td>\n",
       "      <td>130</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>7</th>\n",
       "      <td>5</td>\n",
       "      <td>77</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>8</th>\n",
       "      <td>9</td>\n",
       "      <td>50</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>9</th>\n",
       "      <td>1</td>\n",
       "      <td>38</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "  code  #_of_projects\n",
       "0   11            250\n",
       "1   10            216\n",
       "2    8            210\n",
       "3    2            199\n",
       "4    6            168\n",
       "5    4            146\n",
       "6    7            130\n",
       "7    5             77\n",
       "8    9             50\n",
       "9    1             38"
      ]
     },
     "execution_count": 15,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# Count the number of projects by project code\n",
    "theme_counts = theme['code'].value_counts()\n",
    "\n",
    "# Reset the index\n",
    "theme_counts = theme_counts.reset_index()\n",
    "\n",
    "# Change the column labels\n",
    "theme_counts.columns = ['code', '#_of_projects']\n",
    "\n",
    "# Print the Top 10 ranking\n",
    "theme_counts.head(10)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 16,
   "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>code</th>\n",
       "      <th>#_of_projects</th>\n",
       "      <th>name</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>11</td>\n",
       "      <td>250</td>\n",
       "      <td>Environment and natural resources management</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>10</td>\n",
       "      <td>216</td>\n",
       "      <td>Rural development</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>8</td>\n",
       "      <td>210</td>\n",
       "      <td>Human development</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>2</td>\n",
       "      <td>199</td>\n",
       "      <td>Public sector governance</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>6</td>\n",
       "      <td>168</td>\n",
       "      <td>Social protection and risk management</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>5</th>\n",
       "      <td>4</td>\n",
       "      <td>146</td>\n",
       "      <td>Financial and private sector development</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>6</th>\n",
       "      <td>7</td>\n",
       "      <td>130</td>\n",
       "      <td>Social dev/gender/inclusion</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>7</th>\n",
       "      <td>5</td>\n",
       "      <td>77</td>\n",
       "      <td>Trade and integration</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>8</th>\n",
       "      <td>9</td>\n",
       "      <td>50</td>\n",
       "      <td>Urban development</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>9</th>\n",
       "      <td>1</td>\n",
       "      <td>38</td>\n",
       "      <td>Economic management</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>10</th>\n",
       "      <td>3</td>\n",
       "      <td>15</td>\n",
       "      <td>Rule of law</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "   code  #_of_projects                                          name\n",
       "0    11            250  Environment and natural resources management\n",
       "1    10            216                             Rural development\n",
       "2     8            210                             Human development\n",
       "3     2            199                      Public sector governance\n",
       "4     6            168         Social protection and risk management\n",
       "5     4            146      Financial and private sector development\n",
       "6     7            130                   Social dev/gender/inclusion\n",
       "7     5             77                         Trade and integration\n",
       "8     9             50                             Urban development\n",
       "9     1             38                           Economic management\n",
       "10    3             15                                   Rule of law"
      ]
     },
     "execution_count": 16,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# Merge theme_counts and unique\n",
    "ranking = pd.merge(theme_counts, unique)\n",
    "ranking"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 17,
   "metadata": {},
   "outputs": [],
   "source": [
    "# Remove 'code' column and change the column order\n",
    "ranking = ranking[['name', '#_of_projects']]\n",
    "\n",
    "# Change the index name and column labels\n",
    "ranking.index.name = 'Ranking'\n",
    "ranking.columns = ['Theme_name', '#_of_projects']\n",
    "\n",
    "# Make the index start from 1\n",
    "ranking.index = ranking.index + 1"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "**TOP10 major project themes**"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 18,
   "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>Theme_name</th>\n",
       "      <th>#_of_projects</th>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Ranking</th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>Environment and natural resources management</td>\n",
       "      <td>250</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>Rural development</td>\n",
       "      <td>216</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>Human development</td>\n",
       "      <td>210</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>Public sector governance</td>\n",
       "      <td>199</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>5</th>\n",
       "      <td>Social protection and risk management</td>\n",
       "      <td>168</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>6</th>\n",
       "      <td>Financial and private sector development</td>\n",
       "      <td>146</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>7</th>\n",
       "      <td>Social dev/gender/inclusion</td>\n",
       "      <td>130</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>8</th>\n",
       "      <td>Trade and integration</td>\n",
       "      <td>77</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>9</th>\n",
       "      <td>Urban development</td>\n",
       "      <td>50</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>10</th>\n",
       "      <td>Economic management</td>\n",
       "      <td>38</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "                                           Theme_name  #_of_projects\n",
       "Ranking                                                             \n",
       "1        Environment and natural resources management            250\n",
       "2                                   Rural development            216\n",
       "3                                   Human development            210\n",
       "4                            Public sector governance            199\n",
       "5               Social protection and risk management            168\n",
       "6            Financial and private sector development            146\n",
       "7                         Social dev/gender/inclusion            130\n",
       "8                               Trade and integration             77\n",
       "9                                   Urban development             50\n",
       "10                                Economic management             38"
      ]
     },
     "execution_count": 18,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# Show Top 10 ranking\n",
    "ranking.iloc[0:10]"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## Create a dataframe with the missing names filled in."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 19,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "<class 'pandas.core.frame.DataFrame'>\n",
      "RangeIndex: 1499 entries, 0 to 1498\n",
      "Data columns (total 2 columns):\n",
      "code    1499 non-null object\n",
      "name    1499 non-null object\n",
      "dtypes: object(2)\n",
      "memory usage: 23.5+ KB\n"
     ]
    }
   ],
   "source": [
    "# Check the target columns' information again\n",
    "theme.info()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 20,
   "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>code</th>\n",
       "      <th>name_x</th>\n",
       "      <th>name_y</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>8</td>\n",
       "      <td>Human development</td>\n",
       "      <td>Human development</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>8</td>\n",
       "      <td></td>\n",
       "      <td>Human development</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>8</td>\n",
       "      <td>Human development</td>\n",
       "      <td>Human development</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>8</td>\n",
       "      <td>Human development</td>\n",
       "      <td>Human development</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>8</td>\n",
       "      <td>Human development</td>\n",
       "      <td>Human development</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>...</th>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1494</th>\n",
       "      <td>3</td>\n",
       "      <td>Rule of law</td>\n",
       "      <td>Rule of law</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1495</th>\n",
       "      <td>3</td>\n",
       "      <td>Rule of law</td>\n",
       "      <td>Rule of law</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1496</th>\n",
       "      <td>3</td>\n",
       "      <td>Rule of law</td>\n",
       "      <td>Rule of law</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1497</th>\n",
       "      <td>3</td>\n",
       "      <td>Rule of law</td>\n",
       "      <td>Rule of law</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1498</th>\n",
       "      <td>3</td>\n",
       "      <td>Rule of law</td>\n",
       "      <td>Rule of law</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "<p>1499 rows × 3 columns</p>\n",
       "</div>"
      ],
      "text/plain": [
       "     code             name_x             name_y\n",
       "0       8  Human development  Human development\n",
       "1       8                     Human development\n",
       "2       8  Human development  Human development\n",
       "3       8  Human development  Human development\n",
       "4       8  Human development  Human development\n",
       "...   ...                ...                ...\n",
       "1494    3        Rule of law        Rule of law\n",
       "1495    3        Rule of law        Rule of law\n",
       "1496    3        Rule of law        Rule of law\n",
       "1497    3        Rule of law        Rule of law\n",
       "1498    3        Rule of law        Rule of law\n",
       "\n",
       "[1499 rows x 3 columns]"
      ]
     },
     "execution_count": 20,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# Merge theme and unique\n",
    "filled = pd.merge(theme, unique, on='code')\n",
    "filled"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 21,
   "metadata": {},
   "outputs": [],
   "source": [
    "# Remove 'name_x' column\n",
    "filled = filled[['code', 'name_y']]\n",
    "\n",
    "# Change the column labels\n",
    "filled.columns = ['code', 'name']"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "**The Filled dataframe**"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 22,
   "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>code</th>\n",
       "      <th>name</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>8</td>\n",
       "      <td>Human development</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>8</td>\n",
       "      <td>Human development</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>8</td>\n",
       "      <td>Human development</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>8</td>\n",
       "      <td>Human development</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>8</td>\n",
       "      <td>Human development</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>...</th>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1494</th>\n",
       "      <td>3</td>\n",
       "      <td>Rule of law</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1495</th>\n",
       "      <td>3</td>\n",
       "      <td>Rule of law</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1496</th>\n",
       "      <td>3</td>\n",
       "      <td>Rule of law</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1497</th>\n",
       "      <td>3</td>\n",
       "      <td>Rule of law</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1498</th>\n",
       "      <td>3</td>\n",
       "      <td>Rule of law</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "<p>1499 rows × 2 columns</p>\n",
       "</div>"
      ],
      "text/plain": [
       "     code               name\n",
       "0       8  Human development\n",
       "1       8  Human development\n",
       "2       8  Human development\n",
       "3       8  Human development\n",
       "4       8  Human development\n",
       "...   ...                ...\n",
       "1494    3        Rule of law\n",
       "1495    3        Rule of law\n",
       "1496    3        Rule of law\n",
       "1497    3        Rule of law\n",
       "1498    3        Rule of law\n",
       "\n",
       "[1499 rows x 2 columns]"
      ]
     },
     "execution_count": 22,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# Show filled\n",
    "filled"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 24,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "code    0\n",
       "name    0\n",
       "dtype: int64"
      ]
     },
     "execution_count": 24,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# Confirm there is no missing data\n",
    "filled[filled['name'] == ''].count()"
   ]
  }
 ],
 "metadata": {
  "kernelspec": {
   "display_name": "springboard1",
   "language": "python",
   "name": "springboard1"
  },
  "language_info": {
   "codemirror_mode": {
    "name": "ipython",
    "version": 3
   },
   "file_extension": ".py",
   "mimetype": "text/x-python",
   "name": "python",
   "nbconvert_exporter": "python",
   "pygments_lexer": "ipython3",
   "version": "3.7.5"
  },
  "toc": {
   "base_numbering": 1,
   "nav_menu": {},
   "number_sections": true,
   "sideBar": true,
   "skip_h1_title": false,
   "title_cell": "Table of Contents",
   "title_sidebar": "Contents",
   "toc_cell": false,
   "toc_position": {},
   "toc_section_display": true,
   "toc_window_display": false
  }
 },
 "nbformat": 4,
 "nbformat_minor": 1
}