<jupyter_start><jupyter_text>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.htmlio-json-reader+ data source: http://jsonstudio.com/resources/****<jupyter_code>import pandas as pd<jupyter_output><empty_output><jupyter_text>imports for Python, Pandas<jupyter_code>import json
from pandas.io.json import json_normalize<jupyter_output><empty_output><jupyter_text>JSON example, with string+ demonstrates creation of normalized dataframes (tables) from nested json string+ source: http://pandas.pydata.org/pandas-docs/stable/io.htmlnormalization<jupyter_code># define json string
data = [{'state': 'Florida', 
         'shortname': 'FL',
         'info': {'governor': '<NAME>'},
         'counties': [{'name': 'Dade', 'population': 12345},
                      {'name': 'Broward', 'population': 40000},
                      {'name': '<NAME>', 'population': 60000}]},
        {'state': 'Ohio',
         'shortname': 'OH',
         'info': {'governor': '<NAME>'},
         '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']])
json_normalize(data, 'counties', ['state', 'shortname', ['info', 'governor']])<jupyter_output><empty_output><jupyter_text>**** 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/<jupyter_code># 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<jupyter_output><empty_output><jupyter_text>**** JSON exerciseUsing data in file 'data/world_bank_projects.json' and the techniques demonstrated above,1. Find the 10 countries with most projects2. 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.<jupyter_code>json_df = pd.read_json('data/world_bank_projects.json')<jupyter_output><empty_output><jupyter_text>Task 1: Find the 10 countries with most projects<jupyter_code>top_10_countries=list(json_df.groupby('countryname').size().sort_values(ascending=False).index[:10])

print ("the 10 countries with most projects are:\n")

for c in top_10_countries:
    print (c)<jupyter_output>the 10 countries with most projects are:

People's Republic of China
Republic of Indonesia
Socialist Republic of Vietnam
Republic of India
Republic of Yemen
Nepal
People's Republic of Bangladesh
Kingdom of Morocco
Africa
Republic of Mozambique<jupyter_text>Task 2: Find the top 10 major project themes (using column 'mjtheme_namecode')<jupyter_code>#Find the top 10 major project themes (using column 'mjtheme_namecode')

##first create a dataframe with only the column of interested: 'mjtheme_namecode'
sub_df=json_df[['mjtheme_namecode']].dropna()
#then normalize each row as a dataframe.
#and append the normalized dataframe of each row to a list for concantenation
nested_dfs=list()
for i in sub_df.index:
    df=json_normalize(sub_df.loc[i]['mjtheme_namecode'])
    print (df)
    nested_dfs.append(df[['code','name']])
#concatenate all normalized dataframes (column wise) as a whole
theme_df=pd.concat(nested_dfs, axis=0)
#group the rows by theme names (column name), 
#get the frequency of each theme name and sort it in descending order,
#the top 10 major project themes is the first 10 names
top_10_themes=list(theme_df.groupby('name').size().sort_values(ascending=False).index[:10])

print ("The top 10 major project themes are:\n")

for t in top_10_themes:
    print (t)
#it seems like one theme name is missing<jupyter_output><empty_output><jupyter_text>Task 3: 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.<jupyter_code>#122 names are missing
sum(theme_df['name'].apply(lambda x:x==''))
#replace the '' with j
theme_df=theme_df.replace([''], [None])
code_name_df=theme_df.dropna().drop_duplicates().set_index('code')
#write the function to fill in the missing names based on the code
def Fill_in_missing_names(row_list, code_name_df):
    for i in range(len(row_list)):
        if row_list[i]['name']=='':
            row_list[i]['name']=code_name_df.loc[row_list[i]['code']]['name']
    return row_list
json_df['mjtheme_namecode']=json_df['mjtheme_namecode'].apply(lambda x: Fill_in_missing_names(x, code_name_df))
#repeat steps in task 2 and the full list of names

sub_df=json_df[['mjtheme_namecode']].dropna()
nested_dfs=list()
for i in sub_df.index:
    df=json_normalize(sub_df.loc[i]['mjtheme_namecode'])
    nested_dfs.append(df[['code','name']])

theme_df=pd.concat(nested_dfs, axis=0)

top_10_themes=list(theme_df.groupby('name').size().sort_values(ascending=False).index[:10])

print ("The top 10 major project themes are:\n")

for t in top_10_themes:
    print (t)<jupyter_output>The top 10 major project themes are:

Environment and natural resources management
Rural development
Human development
Public sector governance
Social protection and risk management
Financial and private sector development
Social dev/gender/inclusion
Trade and integration
Urban development
Economic management
{'fraction_non_alphanumeric': 0.11638830897703549, 'fraction_numerical': 0.013569937369519834, 'mean_word_length': 5.763529411764706, 'pattern_counts': {'":': 0, '<': 34, '<?xml version=': 0, '>': 34, 'https://': 0, 'lorem ipsum': 0, 'www.': 0, 'xml': 0}, 'pii_count': 0, 'substrings_counts': 0, 'word_list_counts': {'cursed_substrings.json': 0, 'profanity_word_list.json': 0, 'sexual_word_list.json': 0, 'zh_pornsignals.json': 0}}
{'chain_length': '8', 'dir': 'jupyter-structured-clean-dedup', 'id': '481792', 'license': 'MIT', 'n_tokens_mistral': 1868, 'n_tokens_neox': 1755, 'n_words': 552, 'path': 'data_wrangling_json/data_wrangling_json/sliderule_dsi_json_exercise_xinyu.ipynb', 'repo_name': 'cathyxinxyz/mini_projects', 'size': '5761'}