sliderule_dsi_json_exercise_RH.ipynb

{
 "cells": [
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "# JSON examples and exercise\n",
    "****\n",
    "+ get familiar with packages for dealing with JSON\n",
    "+ study examples with JSON strings and files \n",
    "+ work on exercise to be completed and submitted \n",
    "****\n",
    "+ reference: http://pandas.pydata.org/pandas-docs/stable/io.html#io-json-reader\n",
    "+ data source: http://jsonstudio.com/resources/\n",
    "****"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 1,
   "metadata": {
    "collapsed": true
   },
   "outputs": [],
   "source": [
    "import pandas as pd\n",
    "import numpy as np"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## imports for Python, Pandas"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 2,
   "metadata": {
    "collapsed": true
   },
   "outputs": [],
   "source": [
    "import json\n",
    "from pandas.io.json import json_normalize"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "****\n",
    "## JSON exercise\n",
    "\n",
    "Using data in file 'data/world_bank_projects.json' and the techniques demonstrated above,\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": 3,
   "metadata": {
    "collapsed": true
   },
   "outputs": [],
   "source": [
    "# Exercise 1: Find the 10 countries with most projects\n",
    "\n",
    "# Read in the data to DataFrame\n",
    "df = pd.read_json('data/world_bank_projects.json')\n",
    "#df.head(3)   #Just to check"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 4,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "countryname\n",
       "People's Republic of China         19\n",
       "Republic of Indonesia              19\n",
       "Socialist Republic of Vietnam      17\n",
       "Republic of India                  16\n",
       "Republic of Yemen                  13\n",
       "Nepal                              12\n",
       "People's Republic of Bangladesh    12\n",
       "Kingdom of Morocco                 12\n",
       "Africa                             11\n",
       "Republic of Mozambique             11\n",
       "dtype: int64"
      ]
     },
     "execution_count": 4,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# Group by name and sort by number of projects, show top 10\n",
    "num_proj_by_country = df.groupby('countryname').size()\n",
    "num_proj_by_country.sort_values(ascending=False).head(10)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "collapsed": true
   },
   "outputs": [],
   "source": []
  },
  {
   "cell_type": "code",
   "execution_count": 5,
   "metadata": {
    "collapsed": true
   },
   "outputs": [],
   "source": [
    "# Exercise 2 : Find the top 10 major project themes (using column 'mjtheme_namecode')\n",
    "\n",
    "# Load the json data as str\n",
    "data = json.load((open('data/world_bank_projects.json')))"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 6,
   "metadata": {
    "collapsed": true,
    "scrolled": true
   },
   "outputs": [],
   "source": [
    "# Normalized with themes\n",
    "data_by_themes = json_normalize(data, 'mjtheme_namecode')\n",
    "#data_by_themes.head(5)     #just to check"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 7,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "name\n",
       "Environment and natural resources management    223\n",
       "Rural development                               202\n",
       "Human development                               197\n",
       "Public sector governance                        184\n",
       "Social protection and risk management           158\n",
       "Financial and private sector development        130\n",
       "                                                122\n",
       "Social dev/gender/inclusion                     119\n",
       "Trade and integration                            72\n",
       "Urban development                                47\n",
       "dtype: int64"
      ]
     },
     "execution_count": 7,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# Group by theme name and sort by number of projects, show top 10\n",
    "dbt = data_by_themes.groupby('name').size()\n",
    "dbt.sort_values(ascending=False).head(10)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "collapsed": true
   },
   "outputs": [],
   "source": []
  },
  {
   "cell_type": "code",
   "execution_count": 8,
   "metadata": {
    "collapsed": true
   },
   "outputs": [],
   "source": [
    "# Exercise3: In 2. above you will notice that some entries have only the code and the name is missing. \n",
    "#Create a dataframe with the missing names filled in."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 9,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "['Human development',\n",
       " '',\n",
       " 'Economic management',\n",
       " 'Social protection and risk management',\n",
       " 'Trade and integration']"
      ]
     },
     "execution_count": 9,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# Check how the missing values (empty names) are expressed in what dtype\n",
    "list(data_by_themes['name'].head())"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 10,
   "metadata": {
    "scrolled": false
   },
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style>\n",
       "    .dataframe thead tr:only-child th {\n",
       "        text-align: right;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: left;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\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",
       "    <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>7</th>\n",
       "      <td>6</td>\n",
       "      <td>Social protection and risk 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>9</th>\n",
       "      <td>7</td>\n",
       "      <td>Social dev/gender/inclusion</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>10</th>\n",
       "      <td>5</td>\n",
       "      <td>Trade and integration</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>12</th>\n",
       "      <td>6</td>\n",
       "      <td>Social protection and risk management</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>13</th>\n",
       "      <td>6</td>\n",
       "      <td></td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>14</th>\n",
       "      <td>2</td>\n",
       "      <td>Public sector governance</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\n",
       "5     2                      Public sector governance\n",
       "6    11  Environment and natural resources management\n",
       "7     6         Social protection and risk management\n",
       "8     7                   Social dev/gender/inclusion\n",
       "9     7                   Social dev/gender/inclusion\n",
       "10    5                         Trade and integration\n",
       "11    4      Financial and private sector development\n",
       "12    6         Social protection and risk management\n",
       "13    6                                              \n",
       "14    2                      Public sector governance"
      ]
     },
     "execution_count": 10,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# Now I know mising values are expressed as '' (str type)\n",
    "# Replace those names with \"NAME UKNOWN\"\n",
    "dbt2 = data_by_themes.copy()\n",
    "dbt3 = dbt2.copy()\n",
    "dbt3['name'] = dbt3.name.replace('', 'NAME UNKNOWN')\n",
    "\n",
    "# dbt2 still has missing values. See replaced version in the next cell\n",
    "dbt2.head(15)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 11,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style>\n",
       "    .dataframe thead tr:only-child th {\n",
       "        text-align: right;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: left;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\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>NAME UNKNOWN</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>7</th>\n",
       "      <td>6</td>\n",
       "      <td>Social protection and risk 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>9</th>\n",
       "      <td>7</td>\n",
       "      <td>Social dev/gender/inclusion</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>10</th>\n",
       "      <td>5</td>\n",
       "      <td>Trade and integration</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>12</th>\n",
       "      <td>6</td>\n",
       "      <td>Social protection and risk management</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>13</th>\n",
       "      <td>6</td>\n",
       "      <td>NAME UNKNOWN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>14</th>\n",
       "      <td>2</td>\n",
       "      <td>Public sector governance</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "   code                                          name\n",
       "0     8                             Human development\n",
       "1    11                                  NAME UNKNOWN\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",
       "7     6         Social protection and risk management\n",
       "8     7                   Social dev/gender/inclusion\n",
       "9     7                   Social dev/gender/inclusion\n",
       "10    5                         Trade and integration\n",
       "11    4      Financial and private sector development\n",
       "12    6         Social protection and risk management\n",
       "13    6                                  NAME UNKNOWN\n",
       "14    2                      Public sector governance"
      ]
     },
     "execution_count": 11,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# Now '' is replaced with 'NAME UKNOWN' in dbt3\n",
    "dbt3.head(15)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "collapsed": true
   },
   "outputs": [],
   "source": []
  }
 ],
 "metadata": {
  "kernelspec": {
   "display_name": "Python 3",
   "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.6.1"
  }
 },
 "nbformat": 4,
 "nbformat_minor": 1
}