<SYSTEM_TASK:>
Given the following text description, write Python code to implement the functionality described below step by step
<END_TASK>
<USER_TASK:>
Description:
 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 
reference
Step1:  imports for Python, Pandas
Step2:  JSON example, with string
demonstrates creation of normalized dataframes (tables) from nested json string
source
Step3:  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
Step4:  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.
Step5:  1. Find the 10 countries with most projects
Step6:  2. Find the top 10 major project themes (using column 'mjtheme_namecode')
Step7:  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.
<ASSISTANT_TASK:>
Python Code:
import pandas as pd
import numpy as np
Explanation: 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 
reference: http://pandas.pydata.org/pandas-docs/stable/io.html#io-json-reader
data source: http://jsonstudio.com/resources/
End of explanation
import json
from pandas.io.json import json_normalize
Explanation: imports for Python, Pandas
End of explanation
# 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}]}]
# use normalization to create tables from nested element
json_normalize(data, 'counties')
# further populate tables created from nested element
json_normalize(data, 'counties', ['state', 'shortname', ['info', 'governor']])
Explanation: JSON example, with string
demonstrates creation of normalized dataframes (tables) from nested json string
source: http://pandas.pydata.org/pandas-docs/stable/io.html#normalization
End of explanation
# load json as string
json.load((open('data/world_bank_projects_less.json')))
# load as Pandas dataframe
sample_json_df = pd.read_json('data/world_bank_projects_less.json')
sample_json_df
Explanation: 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/
End of explanation
bank = pd.read_json('data/world_bank_projects.json')
bank.head()
Explanation: 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.
End of explanation
bank.countryname.value_counts().head(10)
Explanation: 1. Find the 10 countries with most projects
End of explanation
names = []
for i in bank.index:
    namecode = bank.loc[i,'mjtheme_namecode']
    names.extend(list(json_normalize(namecode)['name']))
pd.Series(names).value_counts().head(10).drop('', axis=0)    
Explanation: 2. Find the top 10 major project themes (using column 'mjtheme_namecode')
End of explanation
codes_names = pd.DataFrame(columns=['code', 'name'])
for i in bank.index:
    namecode = bank.loc[i,'mjtheme_namecode']
    codes_names = pd.concat([codes_names, json_normalize(namecode)])
codes_names_dict = (codes_names[codes_names.name != '']
                                .drop_duplicates()
                                .to_dict())
for i in bank.index:
    namecode = bank.loc[i,'mjtheme_namecode']
    cell = json_normalize(namecode).replace('', np.nan)
    cell = cell.fillna(codes_names_dict)
    bank.set_value(i, 'mjtheme_namecode', cell.to_dict(orient='record'))
Explanation: 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.
End of explanation
<END_TASK>