<jupyter_start><jupyter_text>**** JSON exerciseUsing data in file 'data/world_bank_projects.json'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.  1) Find the 10 countries with most projects<jupyter_code># Import required packages
import pandas as pd
import json
from pandas.io.json import json_normalize
# Read JSON file as Pandas DataFrame object
world_bank_df = pd.read_json('data/world_bank_projects.json')
world_bank_df
# Check DataFrame info
world_bank_df.info()
# List top 10 countries with the most projects
world_bank_df.groupby('countryname').size().sort_values(ascending=False)[:10]<jupyter_output><empty_output><jupyter_text>2) Find the top 10 major project themes (using column 'mjtheme_namecode')<jupyter_code># Load nested JSON column ('mjtheme_namecode') as Pandas DataFrame
data = json.load((open('data/world_bank_projects.json')))
project_themes_df = json_normalize(data, 'mjtheme_namecode')
project_themes_df
# Check DataFrame info
project_themes_df.info()
# List top 10 most occurring major project themes by name
project_themes_df.groupby('name').size().sort_values(ascending=False)[:10]
# Since there are missing values indicated by the missing name below this output is not accurate
# Convert code column to integers
# Setting errors='raise' will raise an alert if there are missing values
project_themes_df.code = pd.to_numeric(project_themes_df.code, errors='raise')
print(project_themes_df.code.dtype)
# List top 10 most occurring major project themes by code
project_themes_df.groupby('code').size().sort_values(ascending=False)[:10]
# There are no missing values so this output is more accurate than above<jupyter_output><empty_output><jupyter_text>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># Change empty strings in the name column to nulls
project_themes_df.loc[project_themes_df['name'] == "", 'name'] = None
project_themes_df.info()
# Create dictionary that maps project codes to names
themes_by_code = {}
for i in range(project_themes_df['code'].min(), project_themes_df['code'].max() + 1):
    code_filter = (project_themes_df.name.notnull()) & (project_themes_df.code == i)
    themes_by_code[i] = project_themes_df.name[code_filter].iloc[0]
themes_by_code
# Fill in missing name values in DataFrame using code values
project_themes_df.name = project_themes_df.code.map(themes_by_code)
project_themes_df.info()
# Rerun code above from number 2 to list top 10 most occurring major project themes by name
project_themes_df.groupby('name').size().sort_values(ascending=False)[:10]<jupyter_output><empty_output>
{'fraction_non_alphanumeric': 0.08913649025069638, 'fraction_numerical': 0.012186629526462395, 'mean_word_length': 6.661333333333333, 'pattern_counts': {'":': 0, '<': 13, '<?xml version=': 0, '>': 13, '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': '3', 'dir': 'jupyter-structured-clean-dedup', 'id': '575987', 'license': 'MIT', 'n_tokens_mistral': 898, 'n_tokens_neox': 844, 'n_words': 287, 'path': 'data_wrangling_json/json_exercise.ipynb', 'repo_name': 'llclave/Springboard-Mini-Projects', 'size': '2872'}