JSON examples and exercise


  • get familiar with packages for dealing with JSON
  • study examples with JSON strings and files
  • work on exercise to be completed and submitted



In [1]:
import pandas as pd

imports for Python, Pandas


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

JSON example, with string


In [3]:
# define json string
data = [{'state': 'Florida', 
         'shortname': 'FL',
         'info': {'governor': 'Rick Scott'},
         'counties': [{'name': 'Dade', 'population': 12345},
                      {'name': 'Broward', 'population': 40000},
                      {'name': 'Palm Beach', 'population': 60000}]},
        {'state': 'Ohio',
         'shortname': 'OH',
         'info': {'governor': 'John Kasich'},
         'counties': [{'name': 'Summit', 'population': 1234},
                      {'name': 'Cuyahoga', 'population': 1337}]}]

In [4]:
# use normalization to create tables from nested element
json_normalize(data, 'counties')


Out[4]:
name population
0 Dade 12345
1 Broward 40000
2 Palm Beach 60000
3 Summit 1234
4 Cuyahoga 1337

In [5]:
# further populate tables created from nested element
json_normalize(data, 'counties', ['state', 'shortname', ['info', 'governor']])


Out[5]:
name population state shortname info.governor
0 Dade 12345 Florida FL Rick Scott
1 Broward 40000 Florida FL Rick Scott
2 Palm Beach 60000 Florida FL Rick Scott
3 Summit 1234 Ohio OH John Kasich
4 Cuyahoga 1337 Ohio OH John Kasich

JSON example, with file

  • demonstrates reading in a json file as a string and as a table
  • uses small sample file containing data about projects funded by the World Bank
  • data source: http://jsonstudio.com/resources/

In [6]:
# load json as string
json.load((open('data/world_bank_projects_less.json')))


Out[6]:
[{'_id': {'$oid': '52b213b38594d8a2be17c780'},
  'approvalfy': 1999,
  'board_approval_month': 'November',
  'boardapprovaldate': '2013-11-12T00:00:00Z',
  'borrower': 'FEDERAL DEMOCRATIC REPUBLIC OF ETHIOPIA',
  'closingdate': '2018-07-07T00:00:00Z',
  'country_namecode': 'Federal Democratic Republic of Ethiopia!$!ET',
  'countrycode': 'ET',
  'countryname': 'Federal Democratic Republic of Ethiopia',
  'countryshortname': 'Ethiopia',
  'docty': 'Project Information Document,Indigenous Peoples Plan,Project Information Document',
  'envassesmentcategorycode': 'C',
  'grantamt': 0,
  'ibrdcommamt': 0,
  'id': 'P129828',
  'idacommamt': 130000000,
  'impagency': 'MINISTRY OF EDUCATION',
  'lendinginstr': 'Investment Project Financing',
  'lendinginstrtype': 'IN',
  'lendprojectcost': 550000000,
  'majorsector_percent': [{'Name': 'Education', 'Percent': 46},
   {'Name': 'Education', 'Percent': 26},
   {'Name': 'Public Administration, Law, and Justice', 'Percent': 16},
   {'Name': 'Education', 'Percent': 12}],
  'mjsector_namecode': [{'code': 'EX', 'name': 'Education'},
   {'code': 'EX', 'name': 'Education'},
   {'code': 'BX', 'name': 'Public Administration, Law, and Justice'},
   {'code': 'EX', 'name': 'Education'}],
  'mjtheme': ['Human development'],
  'mjtheme_namecode': [{'code': '8', 'name': 'Human development'},
   {'code': '11', 'name': ''}],
  'mjthemecode': '8,11',
  'prodline': 'PE',
  'prodlinetext': 'IBRD/IDA',
  'productlinetype': 'L',
  'project_abstract': {'cdata': 'The development objective of the Second Phase of General Education Quality Improvement Project for Ethiopia is to improve learning conditions in primary and secondary schools and strengthen institutions at different levels of educational administration. The project has six components. The first component is curriculum, textbooks, assessment, examinations, and inspection. This component will support improvement of learning conditions in grades KG-12 by providing increased access to teaching and learning materials and through improvements to the curriculum by assessing the strengths and weaknesses of the current curriculum. This component has following four sub-components: (i) curriculum reform and implementation; (ii) teaching and learning materials; (iii) assessment and examinations; and (iv) inspection. The second component is teacher development program (TDP). This component will support improvements in learning conditions in both primary and secondary schools by advancing the quality of teaching in general education through: (a) enhancing the training of pre-service teachers in teacher education institutions; and (b) improving the quality of in-service teacher training. This component has following three sub-components: (i) pre-service teacher training; (ii) in-service teacher training; and (iii) licensing and relicensing of teachers and school leaders. The third component is school improvement plan. This component will support the strengthening of school planning in order to improve learning outcomes, and to partly fund the school improvement plans through school grants. It has following two sub-components: (i) school improvement plan; and (ii) school grants. The fourth component is management and capacity building, including education management information systems (EMIS). This component will support management and capacity building aspect of the project. This component has following three sub-components: (i) capacity building for education planning and management; (ii) capacity building for school planning and management; and (iii) EMIS. The fifth component is improving the quality of learning and teaching in secondary schools and universities through the use of information and communications technology (ICT). It has following five sub-components: (i) national policy and institution for ICT in general education; (ii) national ICT infrastructure improvement plan for general education; (iii) develop an integrated monitoring, evaluation, and learning system specifically for the ICT component; (iv) teacher professional development in the use of ICT; and (v) provision of limited number of e-Braille display readers with the possibility to scale up to all secondary education schools based on the successful implementation and usage of the readers. The sixth component is program coordination, monitoring and evaluation, and communication. It will support institutional strengthening by developing capacities in all aspects of program coordination, monitoring and evaluation; a new sub-component on communications will support information sharing for better management and accountability. It has following three sub-components: (i) program coordination; (ii) monitoring and evaluation (M and E); and (iii) communication.'},
  'project_name': 'Ethiopia General Education Quality Improvement Project II',
  'projectdocs': [{'DocDate': '28-AUG-2013',
    'DocType': 'PID',
    'DocTypeDesc': 'Project Information Document (PID),  Vol.',
    'DocURL': 'http://www-wds.worldbank.org/servlet/WDSServlet?pcont=details&eid=090224b081e545fb_1_0',
    'EntityID': '090224b081e545fb_1_0'},
   {'DocDate': '01-JUL-2013',
    'DocType': 'IP',
    'DocTypeDesc': 'Indigenous Peoples Plan (IP),  Vol.1 of 1',
    'DocURL': 'http://www-wds.worldbank.org/servlet/WDSServlet?pcont=details&eid=000442464_20130920111729',
    'EntityID': '000442464_20130920111729'},
   {'DocDate': '22-NOV-2012',
    'DocType': 'PID',
    'DocTypeDesc': 'Project Information Document (PID),  Vol.',
    'DocURL': 'http://www-wds.worldbank.org/servlet/WDSServlet?pcont=details&eid=090224b0817b19e2_1_0',
    'EntityID': '090224b0817b19e2_1_0'}],
  'projectfinancialtype': 'IDA',
  'projectstatusdisplay': 'Active',
  'regionname': 'Africa',
  'sector': [{'Name': 'Primary education'},
   {'Name': 'Secondary education'},
   {'Name': 'Public administration- Other social services'},
   {'Name': 'Tertiary education'}],
  'sector1': {'Name': 'Primary education', 'Percent': 46},
  'sector2': {'Name': 'Secondary education', 'Percent': 26},
  'sector3': {'Name': 'Public administration- Other social services',
   'Percent': 16},
  'sector4': {'Name': 'Tertiary education', 'Percent': 12},
  'sector_namecode': [{'code': 'EP', 'name': 'Primary education'},
   {'code': 'ES', 'name': 'Secondary education'},
   {'code': 'BS', 'name': 'Public administration- Other social services'},
   {'code': 'ET', 'name': 'Tertiary education'}],
  'sectorcode': 'ET,BS,ES,EP',
  'source': 'IBRD',
  'status': 'Active',
  'supplementprojectflg': 'N',
  'theme1': {'Name': 'Education for all', 'Percent': 100},
  'theme_namecode': [{'code': '65', 'name': 'Education for all'}],
  'themecode': '65',
  'totalamt': 130000000,
  'totalcommamt': 130000000,
  'url': 'http://www.worldbank.org/projects/P129828/ethiopia-general-education-quality-improvement-project-ii?lang=en'},
 {'_id': {'$oid': '52b213b38594d8a2be17c781'},
  'approvalfy': 2015,
  'board_approval_month': 'November',
  'boardapprovaldate': '2013-11-04T00:00:00Z',
  'borrower': 'GOVERNMENT OF TUNISIA',
  'country_namecode': 'Republic of Tunisia!$!TN',
  'countrycode': 'TN',
  'countryname': 'Republic of Tunisia',
  'countryshortname': 'Tunisia',
  'docty': 'Project Information Document,Integrated Safeguards Data Sheet,Integrated Safeguards Data Sheet,Project Information Document,Integrated Safeguards Data Sheet,Project Information Document',
  'envassesmentcategorycode': 'C',
  'grantamt': 4700000,
  'ibrdcommamt': 0,
  'id': 'P144674',
  'idacommamt': 0,
  'impagency': 'MINISTRY OF FINANCE',
  'lendinginstr': 'Specific Investment Loan',
  'lendinginstrtype': 'IN',
  'lendprojectcost': 5700000,
  'majorsector_percent': [{'Name': 'Public Administration, Law, and Justice',
    'Percent': 70},
   {'Name': 'Public Administration, Law, and Justice', 'Percent': 30}],
  'mjsector_namecode': [{'code': 'BX',
    'name': 'Public Administration, Law, and Justice'},
   {'code': 'BX', 'name': 'Public Administration, Law, and Justice'}],
  'mjtheme': ['Economic management', 'Social protection and risk management'],
  'mjtheme_namecode': [{'code': '1', 'name': 'Economic management'},
   {'code': '6', 'name': 'Social protection and risk management'}],
  'mjthemecode': '1,6',
  'prodline': 'RE',
  'prodlinetext': 'Recipient Executed Activities',
  'productlinetype': 'L',
  'project_name': 'TN: DTF Social Protection Reforms Support',
  'projectdocs': [{'DocDate': '29-MAR-2013',
    'DocType': 'PID',
    'DocTypeDesc': 'Project Information Document (PID),  Vol.1 of 1',
    'DocURL': 'http://www-wds.worldbank.org/servlet/WDSServlet?pcont=details&eid=000333037_20131024115616',
    'EntityID': '000333037_20131024115616'},
   {'DocDate': '29-MAR-2013',
    'DocType': 'ISDS',
    'DocTypeDesc': 'Integrated Safeguards Data Sheet (ISDS),  Vol.1 of 1',
    'DocURL': 'http://www-wds.worldbank.org/servlet/WDSServlet?pcont=details&eid=000356161_20131024151611',
    'EntityID': '000356161_20131024151611'},
   {'DocDate': '29-MAR-2013',
    'DocType': 'ISDS',
    'DocTypeDesc': 'Integrated Safeguards Data Sheet (ISDS),  Vol.1 of 1',
    'DocURL': 'http://www-wds.worldbank.org/servlet/WDSServlet?pcont=details&eid=000442464_20131031112136',
    'EntityID': '000442464_20131031112136'},
   {'DocDate': '29-MAR-2013',
    'DocType': 'PID',
    'DocTypeDesc': 'Project Information Document (PID),  Vol.1 of 1',
    'DocURL': 'http://www-wds.worldbank.org/servlet/WDSServlet?pcont=details&eid=000333037_20131031105716',
    'EntityID': '000333037_20131031105716'},
   {'DocDate': '16-JAN-2013',
    'DocType': 'ISDS',
    'DocTypeDesc': 'Integrated Safeguards Data Sheet (ISDS),  Vol.1 of 1',
    'DocURL': 'http://www-wds.worldbank.org/servlet/WDSServlet?pcont=details&eid=000356161_20130305113209',
    'EntityID': '000356161_20130305113209'},
   {'DocDate': '16-JAN-2013',
    'DocType': 'PID',
    'DocTypeDesc': 'Project Information Document (PID),  Vol.1 of 1',
    'DocURL': 'http://www-wds.worldbank.org/servlet/WDSServlet?pcont=details&eid=000356161_20130305113716',
    'EntityID': '000356161_20130305113716'}],
  'projectfinancialtype': 'OTHER',
  'projectstatusdisplay': 'Active',
  'regionname': 'Middle East and North Africa',
  'sector': [{'Name': 'Public administration- Other social services'},
   {'Name': 'General public administration sector'}],
  'sector1': {'Name': 'Public administration- Other social services',
   'Percent': 70},
  'sector2': {'Name': 'General public administration sector', 'Percent': 30},
  'sector_namecode': [{'code': 'BS',
    'name': 'Public administration- Other social services'},
   {'code': 'BZ', 'name': 'General public administration sector'}],
  'sectorcode': 'BZ,BS',
  'source': 'IBRD',
  'status': 'Active',
  'supplementprojectflg': 'N',
  'theme1': {'Name': 'Other economic management', 'Percent': 30},
  'theme_namecode': [{'code': '24', 'name': 'Other economic management'},
   {'code': '54', 'name': 'Social safety nets'}],
  'themecode': '54,24',
  'totalamt': 0,
  'totalcommamt': 4700000,
  'url': 'http://www.worldbank.org/projects/P144674?lang=en'}]

In [7]:
# load as Pandas dataframe
sample_json_df = pd.read_json('data/world_bank_projects_less.json')
sample_json_df


Out[7]:
_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'} [{'code': '65', 'name': 'Education for all'}] 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... [{'code': '24', 'name': 'Other economic manage... 54,24 0 4700000 http://www.worldbank.org/projects/P144674?lang=en

2 rows × 50 columns


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 [8]:
#Question 1: Find the 10 countries with most projects

#Read json file into a pandas dataframe 
json_df = pd.read_json((open('data/world_bank_projects.json')))

#Calculate the number of projects for each country and select the 10 coutries with most projects
json_df['countryname'].value_counts()[:10]


Out[8]:
Republic of Indonesia              19
People's Republic of China         19
Socialist Republic of Vietnam      17
Republic of India                  16
Republic of Yemen                  13
People's Republic of Bangladesh    12
Nepal                              12
Kingdom of Morocco                 12
Africa                             11
Republic of Mozambique             11
Name: countryname, dtype: int64

In [9]:
#Question 2: Find the top 10 major project themes
from numpy import nan

#Read json file into a json string, use normalization to create tables from nested element
json_str = json.load((open('data/world_bank_projects.json'))) 
project_data = json_normalize(json_str, 'mjtheme_namecode')

#Calculate the counts for each project code
project_count = project_data.groupby('code').agg('count')
project_count.columns = ['count']

#Get the project name for each project code
project_data_new = project_data.replace('',nan)        
project_name = project_data_new.groupby('code').first()

#merge project_name and project_count by index, select the 10 project name with most counts
pd.merge(project_name, project_count, right_index=True, left_index=True).sort_values(by='count',ascending=False).iloc[:10,]


Out[9]:
name count
code
11 Environment and natural resources management 250
10 Rural development 216
8 Human development 210
2 Public sector governance 199
6 Social protection and risk management 168
4 Financial and private sector development 146
7 Social dev/gender/inclusion 130
5 Trade and integration 77
9 Urban development 50
1 Economic management 38

In [10]:
#Question 3: Create a dataframe project_data with the missing names filled in.

#For replace name column with code column 
project_data['name'] = project_data['code']

#Replace each code with its corresponding project name
project_data['name'].replace(project_name.index.values, project_name['name'].values ,inplace=True )
project_data


Out[10]:
code name
0 8 Human development
1 11 Environment and natural resources management
2 1 Economic management
3 6 Social protection and risk management
4 5 Trade and integration
5 2 Public sector governance
6 11 Environment and natural resources management
7 6 Social protection and risk management
8 7 Social dev/gender/inclusion
9 7 Social dev/gender/inclusion
10 5 Trade and integration
11 4 Financial and private sector development
12 6 Social protection and risk management
13 6 Social protection and risk management
14 2 Public sector governance
15 4 Financial and private sector development
16 11 Environment and natural resources management
17 8 Human development
18 10 Rural development
19 7 Social dev/gender/inclusion
20 2 Public sector governance
21 2 Public sector governance
22 2 Public sector governance
23 10 Rural development
24 2 Public sector governance
25 10 Rural development
26 6 Social protection and risk management
27 6 Social protection and risk management
28 11 Environment and natural resources management
29 4 Financial and private sector development
... ... ...
1469 8 Human development
1470 9 Urban development
1471 6 Social protection and risk management
1472 6 Social protection and risk management
1473 9 Urban development
1474 2 Public sector governance
1475 2 Public sector governance
1476 10 Rural development
1477 11 Environment and natural resources management
1478 8 Human development
1479 7 Social dev/gender/inclusion
1480 11 Environment and natural resources management
1481 5 Trade and integration
1482 6 Social protection and risk management
1483 8 Human development
1484 4 Financial and private sector development
1485 7 Social dev/gender/inclusion
1486 8 Human development
1487 5 Trade and integration
1488 2 Public sector governance
1489 8 Human development
1490 10 Rural development
1491 6 Social protection and risk management
1492 10 Rural development
1493 10 Rural development
1494 10 Rural development
1495 9 Urban development
1496 8 Human development
1497 5 Trade and integration
1498 4 Financial and private sector development

1499 rows × 2 columns


In [ ]:


In [ ]: