JSON examples and exercise

In [69]:

    

import pandas as pd


    

In [70]:

    

import json
from pandas.io.json import json_normalize


    

In [71]:

    

# 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 [72]:

    

# use normalization to create tables from nested element
json_normalize(data, 'counties')


    

    
Out[72]:







  

    

      

      
name
      
population
    
  
  

    

      
0
      
Dade
      
12345
    
    

      
1
      
Broward
      
40000
    
    

      
2
      
Palm Beach
      
60000
    
    

      
3
      
Summit
      
1234
    
    

      
4
      
Cuyahoga
      
1337
    
  

In [73]:

    

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


    

    
Out[73]:







  

    

      

      
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
    
  

In [74]:

    

# load json as string
json.load((open('data/world_bank_projects_less.json')))


    

    




---------------------------------------------------------------------------
FileNotFoundError                         Traceback (most recent call last)
<ipython-input-74-3ca90de6a5ff> in <module>()
      1 # load json as string
----> 2 json.load((open('data/world_bank_projects_less.json')))

FileNotFoundError: [Errno 2] No such file or directory: 'data/world_bank_projects_less.json'

In [ ]:

    

# load as Pandas dataframe
sample_json_df = pd.read_json('data/world_bank_projects_less.json')
sample_json_df


    

In [127]:

    

#import packages
import pandas as pd
import json
from pandas.io.json import json_normalize

# Displays all DataFrame columns and rows
pd.set_option('display.max_columns', None)
pd.set_option('display.max_rows', None)

# Load json file as a string
world_bank_file = json.load((open('world_bank_projects.json')))

# Load json file into a Pandas DataFrame
world_bank_df = pd.read_json('world_bank_projects.json')

#print(world_bank_df.head(5))
#print(world_bank_df.columns)
print(world_bank_df.info())
#print(world_bank_df[['mjthemecode','mjtheme']])


    

    




<class 'pandas.core.frame.DataFrame'>
Int64Index: 500 entries, 0 to 499
Data columns (total 50 columns):
_id                         500 non-null object
approvalfy                  500 non-null int64
board_approval_month        500 non-null object
boardapprovaldate           500 non-null object
borrower                    485 non-null object
closingdate                 370 non-null object
country_namecode            500 non-null object
countrycode                 500 non-null object
countryname                 500 non-null object
countryshortname            500 non-null object
docty                       446 non-null object
envassesmentcategorycode    430 non-null object
grantamt                    500 non-null int64
ibrdcommamt                 500 non-null int64
id                          500 non-null object
idacommamt                  500 non-null int64
impagency                   472 non-null object
lendinginstr                495 non-null object
lendinginstrtype            495 non-null object
lendprojectcost             500 non-null int64
majorsector_percent         500 non-null object
mjsector_namecode           500 non-null object
mjtheme                     491 non-null object
mjtheme_namecode            500 non-null object
mjthemecode                 500 non-null object
prodline                    500 non-null object
prodlinetext                500 non-null object
productlinetype             500 non-null object
project_abstract            362 non-null object
project_name                500 non-null object
projectdocs                 446 non-null object
projectfinancialtype        500 non-null object
projectstatusdisplay        500 non-null object
regionname                  500 non-null object
sector                      500 non-null object
sector1                     500 non-null object
sector2                     380 non-null object
sector3                     265 non-null object
sector4                     174 non-null object
sector_namecode             500 non-null object
sectorcode                  500 non-null object
source                      500 non-null object
status                      500 non-null object
supplementprojectflg        498 non-null object
theme1                      500 non-null object
theme_namecode              491 non-null object
themecode                   491 non-null object
totalamt                    500 non-null int64
totalcommamt                500 non-null int64
url                         500 non-null object
dtypes: int64(7), object(43)
memory usage: 199.2+ KB
None

In [126]:

    

# 1. Find the 10 countries with the most projects

# Select the countryname and project_name columns from the Pandas DataFrame
most_projects = world_bank_df[['countryname', 'project_name']]

# Group by countryname, count and sort values from highest to lowest
most_projects = most_projects.groupby("countryname").size().sort_values(ascending = False)

# Print first 10 rows
print(most_projects.head(10))


    

    




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

In [124]:

    

# 2. Find the top 10 major project themes (using column ‘mjtheme_namecode’)

# Use normalization to create tables from nested element
major_themes = json_normalize(world_bank_file, 'mjtheme_namecode')

# Group by countryname, count and sort values from highest to lowest
major_themes = major_themes.groupby("name").size().sort_values(ascending = False)

# Print first 10 rows
print(major_themes)


    

    




name
Environment and natural resources management    223
Rural development                               202
Human development                               197
Public sector governance                        184
Social protection and risk management           158
Financial and private sector development        130
                                                122
Social dev/gender/inclusion                     119
Trade and integration                            72
Urban development                                47
Economic management                              33
Rule of law                                      12
dtype: int64

In [125]:

    

# 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.

import numpy as np

#  Use normalization to create table from 'mjtheme_namecode' column
missing_names = json_normalize(world_bank_file, 'mjtheme_namecode')

# Replacing empty entries with NaNs
missing_names = missing_names.apply(lambda x: x.str.strip()).replace('', np.nan)
#print(missing_names)

# Finding unique values
code_name_map = missing_names.loc[missing_names['name'] != '', :]

unique_names = code_name_map.drop_duplicates().dropna()

# Creating dictionary from unique_names
unique_names_dict_list = unique_names.set_index('code').T.to_dict(orient = 'records')

# Extracting dictionary from list
unique_names_dict = unique_names_dict_list[0] 

# Fills NaNs with values from dictionary
filled_values = missing_names['name'].fillna(missing_names['code'].map(unique_names_dict))
print(filled_values.value_counts())


    

    




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
Rule of law                                      15
Name: name, dtype: int64