In [204]:
import pandas as pd


---------------------------------------------------------------------------
FileNotFoundError                         Traceback (most recent call last)
<ipython-input-204-298d014a26f1> in <module>()
      1 import pandas as pd
      2 from IPython.core.display import HTML
----> 3 css = open('style-table.css').read() + open('style-notebook.css').read()
      4 HTML('<style>{}</style>'.format(css))

FileNotFoundError: [Errno 2] No such file or directory: 'style-table.css'

imports for Python, Pandas


In [2]:
import json
from pandas.io.json import json_normalize

JSON exercise

Using data in file 'data/world_bank_projects.json' and the techniques demonstrated above,

  1. Find the 10 countries with most projects
  2. Find the top 10 major project themes (using column 'mjtheme_namecode')
  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.

In [227]:
df = pd.read_json('data/world_bank_projects.json')
df.head(3)


Out[227]:
_id approvalfy board_approval_month boardapprovaldate borrower closingdate country_namecode countrycode countryname countryshortname ... sectorcode source status supplementprojectflg theme1 theme_namecode themecode totalamt totalcommamt url
0 {'$oid': '52b213b38594d8a2be17c780'} 1999 November 2013-11-12T00:00:00Z FEDERAL DEMOCRATIC REPUBLIC OF ETHIOPIA 2018-07-07T00:00:00Z Federal Democratic Republic of Ethiopia!$!ET ET Federal Democratic Republic of Ethiopia Ethiopia ... ET,BS,ES,EP IBRD Active N {'Percent': 100, 'Name': 'Education for all'} [{'name': 'Education for all', 'code': '65'}] 65 130000000 130000000 http://www.worldbank.org/projects/P129828/ethi...
1 {'$oid': '52b213b38594d8a2be17c781'} 2015 November 2013-11-04T00:00:00Z GOVERNMENT OF TUNISIA NaN Republic of Tunisia!$!TN TN Republic of Tunisia Tunisia ... BZ,BS IBRD Active N {'Percent': 30, 'Name': 'Other economic manage... [{'name': 'Other economic management', 'code':... 54,24 0 4700000 http://www.worldbank.org/projects/P144674?lang=en
2 {'$oid': '52b213b38594d8a2be17c782'} 2014 November 2013-11-01T00:00:00Z MINISTRY OF FINANCE AND ECONOMIC DEVEL NaN Tuvalu!$!TV TV Tuvalu Tuvalu ... TI IBRD Active Y {'Percent': 46, 'Name': 'Regional integration'} [{'name': 'Regional integration', 'code': '47'... 52,81,25,47 6060000 6060000 http://www.worldbank.org/projects/P145310?lang=en

3 rows × 50 columns


In [228]:
df.countryshortname.value_counts().sort_values(ascending=False).head(10)


Out[228]:
China                 19
Indonesia             19
Vietnam               17
India                 16
Yemen, Republic of    13
Nepal                 12
Morocco               12
Bangladesh            12
Mozambique            11
Africa                11
Name: countryshortname, dtype: int64

Thus we have the ten countries with the most projects.


In [229]:
# load json as string
json_string = json.load((open('data/world_bank_projects.json')))

In [230]:
unstacked_mjthemes = json_normalize(json_string, 'mjtheme_namecode','_id')

In [231]:
unstacked_mjthemes.head(15)


Out[231]:
code name _id
0 8 Human development {'$oid': '52b213b38594d8a2be17c780'}
1 11 {'$oid': '52b213b38594d8a2be17c780'}
2 1 Economic management {'$oid': '52b213b38594d8a2be17c781'}
3 6 Social protection and risk management {'$oid': '52b213b38594d8a2be17c781'}
4 5 Trade and integration {'$oid': '52b213b38594d8a2be17c782'}
5 2 Public sector governance {'$oid': '52b213b38594d8a2be17c782'}
6 11 Environment and natural resources management {'$oid': '52b213b38594d8a2be17c782'}
7 6 Social protection and risk management {'$oid': '52b213b38594d8a2be17c782'}
8 7 Social dev/gender/inclusion {'$oid': '52b213b38594d8a2be17c783'}
9 7 Social dev/gender/inclusion {'$oid': '52b213b38594d8a2be17c783'}
10 5 Trade and integration {'$oid': '52b213b38594d8a2be17c784'}
11 4 Financial and private sector development {'$oid': '52b213b38594d8a2be17c784'}
12 6 Social protection and risk management {'$oid': '52b213b38594d8a2be17c785'}
13 6 {'$oid': '52b213b38594d8a2be17c785'}
14 2 Public sector governance {'$oid': '52b213b38594d8a2be17c786'}

In [232]:
unstacked_mjthemes.code.value_counts().head(10)


Out[232]:
11    250
10    216
8     210
2     199
6     168
4     146
7     130
5      77
9      50
1      38
Name: code, dtype: int64

Now we have the top 10 project themes (out of 11 total). However these give us only the code, so unless you have the corresponding string names memorized, it's not too useful. Using $value\_counts()$ on the name column of our new dataframe won't quite do, since there will be missing entries counted as blanks. So let's fill these in using the map method.


In [233]:
themes = unstacked_mjthemes[unstacked_mjthemes.name != '']
paired_themes = themes.name.groupby(themes.code).max()
paired_themes = paired_themes.to_dict()
paired_themes


Out[233]:
{'1': 'Economic management',
 '10': 'Rural development',
 '11': 'Environment and natural resources management',
 '2': 'Public sector governance',
 '3': 'Rule of law',
 '4': 'Financial and private sector development',
 '5': 'Trade and integration',
 '6': 'Social protection and risk management',
 '7': 'Social dev/gender/inclusion',
 '8': 'Human development',
 '9': 'Urban development'}

In [234]:
unstacked_mjthemes['name'] = unstacked_mjthemes.code.map(paired_themes)
unstacked_mjthemes.head(15)


Out[234]:
code name _id
0 8 Human development {'$oid': '52b213b38594d8a2be17c780'}
1 11 Environment and natural resources management {'$oid': '52b213b38594d8a2be17c780'}
2 1 Economic management {'$oid': '52b213b38594d8a2be17c781'}
3 6 Social protection and risk management {'$oid': '52b213b38594d8a2be17c781'}
4 5 Trade and integration {'$oid': '52b213b38594d8a2be17c782'}
5 2 Public sector governance {'$oid': '52b213b38594d8a2be17c782'}
6 11 Environment and natural resources management {'$oid': '52b213b38594d8a2be17c782'}
7 6 Social protection and risk management {'$oid': '52b213b38594d8a2be17c782'}
8 7 Social dev/gender/inclusion {'$oid': '52b213b38594d8a2be17c783'}
9 7 Social dev/gender/inclusion {'$oid': '52b213b38594d8a2be17c783'}
10 5 Trade and integration {'$oid': '52b213b38594d8a2be17c784'}
11 4 Financial and private sector development {'$oid': '52b213b38594d8a2be17c784'}
12 6 Social protection and risk management {'$oid': '52b213b38594d8a2be17c785'}
13 6 Social protection and risk management {'$oid': '52b213b38594d8a2be17c785'}
14 2 Public sector governance {'$oid': '52b213b38594d8a2be17c786'}

In [235]:
any(unstacked_mjthemes.name == '')


Out[235]:
False

So we see we have used the dict of paired codes and names to them map the blank entries correctly, and that there are no empty strings left in the name column. We can thus now use value counts to correctly see the ten top themes by name and not just code.


In [236]:
unstacked_mjthemes.name.value_counts().head(10)


Out[236]:
Environment and natural resources management    250
Rural development                               216
Human development                               210
Public sector governance                        199
Social protection and risk management           168
Financial and private sector development        146
Social dev/gender/inclusion                     130
Trade and integration                            77
Urban development                                50
Economic management                              38
Name: name, dtype: int64

Reindex our new dataframe to use the project id as index. Could then use this to reinsert corrected major themes into large dataframe if required.


In [257]:
unstacked = unstacked_mjthemes[['_id','code','name']]
unstacked.set_index('_id')


Out[257]:
code name
_id
{'$oid': '52b213b38594d8a2be17c780'} 8 Human development
{'$oid': '52b213b38594d8a2be17c780'} 11 Environment and natural resources management
{'$oid': '52b213b38594d8a2be17c781'} 1 Economic management
{'$oid': '52b213b38594d8a2be17c781'} 6 Social protection and risk management
{'$oid': '52b213b38594d8a2be17c782'} 5 Trade and integration
{'$oid': '52b213b38594d8a2be17c782'} 2 Public sector governance
{'$oid': '52b213b38594d8a2be17c782'} 11 Environment and natural resources management
{'$oid': '52b213b38594d8a2be17c782'} 6 Social protection and risk management
{'$oid': '52b213b38594d8a2be17c783'} 7 Social dev/gender/inclusion
{'$oid': '52b213b38594d8a2be17c783'} 7 Social dev/gender/inclusion
{'$oid': '52b213b38594d8a2be17c784'} 5 Trade and integration
{'$oid': '52b213b38594d8a2be17c784'} 4 Financial and private sector development
{'$oid': '52b213b38594d8a2be17c785'} 6 Social protection and risk management
{'$oid': '52b213b38594d8a2be17c785'} 6 Social protection and risk management
{'$oid': '52b213b38594d8a2be17c786'} 2 Public sector governance
{'$oid': '52b213b38594d8a2be17c786'} 4 Financial and private sector development
{'$oid': '52b213b38594d8a2be17c787'} 11 Environment and natural resources management
{'$oid': '52b213b38594d8a2be17c787'} 8 Human development
{'$oid': '52b213b38594d8a2be17c788'} 10 Rural development
{'$oid': '52b213b38594d8a2be17c788'} 7 Social dev/gender/inclusion
{'$oid': '52b213b38594d8a2be17c789'} 2 Public sector governance
{'$oid': '52b213b38594d8a2be17c789'} 2 Public sector governance
{'$oid': '52b213b38594d8a2be17c789'} 2 Public sector governance
{'$oid': '52b213b38594d8a2be17c78a'} 10 Rural development
{'$oid': '52b213b38594d8a2be17c78a'} 2 Public sector governance
{'$oid': '52b213b38594d8a2be17c78b'} 10 Rural development
{'$oid': '52b213b38594d8a2be17c78b'} 6 Social protection and risk management
{'$oid': '52b213b38594d8a2be17c78b'} 6 Social protection and risk management
{'$oid': '52b213b38594d8a2be17c78b'} 11 Environment and natural resources management
{'$oid': '52b213b38594d8a2be17c78c'} 4 Financial and private sector development
... ... ...
{'$oid': '52b213b38594d8a2be17c968'} 8 Human development
{'$oid': '52b213b38594d8a2be17c969'} 9 Urban development
{'$oid': '52b213b38594d8a2be17c969'} 6 Social protection and risk management
{'$oid': '52b213b38594d8a2be17c969'} 6 Social protection and risk management
{'$oid': '52b213b38594d8a2be17c969'} 9 Urban development
{'$oid': '52b213b38594d8a2be17c96a'} 2 Public sector governance
{'$oid': '52b213b38594d8a2be17c96a'} 2 Public sector governance
{'$oid': '52b213b38594d8a2be17c96b'} 10 Rural development
{'$oid': '52b213b38594d8a2be17c96b'} 11 Environment and natural resources management
{'$oid': '52b213b38594d8a2be17c96c'} 8 Human development
{'$oid': '52b213b38594d8a2be17c96c'} 7 Social dev/gender/inclusion
{'$oid': '52b213b38594d8a2be17c96d'} 11 Environment and natural resources management
{'$oid': '52b213b38594d8a2be17c96d'} 5 Trade and integration
{'$oid': '52b213b38594d8a2be17c96e'} 6 Social protection and risk management
{'$oid': '52b213b38594d8a2be17c96e'} 8 Human development
{'$oid': '52b213b38594d8a2be17c96f'} 4 Financial and private sector development
{'$oid': '52b213b38594d8a2be17c96f'} 7 Social dev/gender/inclusion
{'$oid': '52b213b38594d8a2be17c970'} 8 Human development
{'$oid': '52b213b38594d8a2be17c970'} 5 Trade and integration
{'$oid': '52b213b38594d8a2be17c970'} 2 Public sector governance
{'$oid': '52b213b38594d8a2be17c970'} 8 Human development
{'$oid': '52b213b38594d8a2be17c971'} 10 Rural development
{'$oid': '52b213b38594d8a2be17c971'} 6 Social protection and risk management
{'$oid': '52b213b38594d8a2be17c972'} 10 Rural development
{'$oid': '52b213b38594d8a2be17c972'} 10 Rural development
{'$oid': '52b213b38594d8a2be17c972'} 10 Rural development
{'$oid': '52b213b38594d8a2be17c973'} 9 Urban development
{'$oid': '52b213b38594d8a2be17c973'} 8 Human development
{'$oid': '52b213b38594d8a2be17c973'} 5 Trade and integration
{'$oid': '52b213b38594d8a2be17c973'} 4 Financial and private sector development

1499 rows × 2 columns