<jupyter_start><jupyter_text>JSON exerciseDownload the data from [**here**](https://drive.google.com/file/d/1DGaX5AVfYhmWeb15lI-MzUbSKTYSz9fQ/view?usp=sharing) and answer following questions:1. Find the 10 countries with most projects2. What are top 10 sectors with projects?3. Find the top 10 major project themes (using column 'mjtheme_namecode')4. In 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>import pandas as pd
import json
from pprint import pprint
from pandas import json_normalize
world_ = pd.read_json("world_bank_projects.json")
world_.head(2) # the world dataframe<jupyter_output><empty_output><jupyter_text>keys<jupyter_code>for keys in world_.keys():
    print(keys)<jupyter_output>sector
supplementprojectflg
projectfinancialtype
prodline
mjtheme
idacommamt
impagency
project_name
mjthemecode
closingdate
totalcommamt
id
mjsector_namecode
docty
sector1
lendinginstr
countrycode
sector2
totalamt
mjtheme_namecode
boardapprovaldate
countryshortname
sector4
prodlinetext
productlinetype
regionname
status
country_namecode
envassesmentcategorycode
project_abstract
approvalfy
projectdocs
lendprojectcost
lendinginstrtype
theme1
grantamt
themecode
borrower
sectorcode
sector3
majorsector_percent
board_approval_month
theme_namecode
countryname
url
source
projectstatusdisplay
ibrdcommamt
sector_namecode
_id<jupyter_text>1) 10 countries : most projects<jupyter_code>active_only = world_[world_["projectstatusdisplay"]=="Active"] # wrong, only counting active.
active_countries = active_only[["countryname","projectstatusdisplay"]].groupby("countryname")
active_countries.count().sort_values(by=["projectstatusdisplay"], ascending=False).head(10)
a=world_['countryshortname'].value_counts()
country=pd.DataFrame(a)
country.head(10)<jupyter_output><empty_output><jupyter_text>2) What are top 10 sectors with projects?<jupyter_code>a=world_['sector'].value_counts()
sector=pd.DataFrame(a)
sector=pd.DataFrame(sector)
sector.head(10)
pd.DataFrame(json_normalize(world_["sector"]).stack())<jupyter_output><empty_output><jupyter_text>3) Find the top 10 major project themes ["mjtheme_namecode"]<jupyter_code># json_normalize(data=data, record_path='counties', meta=['state', 'shortname', ['info', 'governor']])
with open('world_bank_projects.json') as f:
    nested_json = json.load(f)
print(type(nested_json))
tehjson = json_normalize(data=nested_json, record_path="mjtheme_namecode")
tehjson.groupby("name").count().sort_values(by=["code"], ascending=False) # not 4 n00bs<jupyter_output><empty_output><jupyter_text>4) Create a dataframe with the missing names filled in.<jupyter_code>empty_df = json_normalize(data=nested_json, record_path="mjtheme_namecode")
empty_df
dict_for_empty_df = {1:"Economic management", 2:"Economic management", 3:"Rule of law", 4:"Financial and private sector development",
          5:"Trade and integration", 6:"Social protection and risk management", 7: "Social dev/gender/inclusion", 8: "Human development",
          9:"Urban development",10:"Rural development", 11:"Environment and natural resources management"
         }
empty_df['name'] = empty_df['code'].apply(lambda x:dict_for_empty_df.get(int(x)))
empty_df.groupby("name").count()
final1 = world_[["countryname", "project_name"]].groupby("countryname").count()
final1.sort_values(by=["project_name"], ascending=False).head(10)<jupyter_output><empty_output><jupyter_text>2) Top 10 sectors with projects<jupyter_code># projectstatusdisplay = active
# groupby sector_namecode
active_projects = world_[world_["projectstatusdisplay"]=="Active"]
active_projects.head(2)
# active_projects[["sector_namecode","projectstatusdisplay"]]
active_projects["sector_dict"] = json_normalize(active_projects["majorsector_percent"])[0] # split the list
active_projects["sector_dict"]
def get_name(x):
    try:
        return x["Name"]
    except:
        return None
active_projects["sector_name"] = active_projects["sector_dict"].apply(get_name)
active_projects
unsorted_active = active_projects[["sector_name", "projectstatusdisplay"]].groupby("sector_name").count()
unsorted_active.sort_values(by="projectstatusdisplay", ascending=False)
# Homework: replicate using collections module default_dict<jupyter_output><empty_output><jupyter_text>2) second attempt<jupyter_code>sectors = json_normalize(world_['sector']) #sector is nested json lists...
sectors.head(4)
sectors["status"] = world_["projectstatusdisplay"]
sectors_active = sectors[sectors["status"]=="Active"]
sectors_active
# this doesn't work because it groups subsectors.
# we need subsectors also
# stuck here. How do I split to count subsectors? wrong list?<jupyter_output><empty_output><jupyter_text>3) top 10 major project themes<jupyter_code>stacked = json_normalize(world_["mjtheme_namecode"]).stack()
stacked
json_normalize(stacked).groupby("name").count()<jupyter_output><empty_output>
{'fraction_non_alphanumeric': 0.11365015166835186, 'fraction_numerical': 0.013751263902932255, 'mean_word_length': 9.197938144329896, 'pattern_counts': {'":': 0, '<': 36, '<?xml version=': 0, '>': 36, 'https://': 1, 'lorem ipsum': 0, 'www.': 0, 'xml': 0}, 'pii_count': 0, 'substrings_counts': 0, 'word_list_counts': {'cursed_substrings.json': 1, 'profanity_word_list.json': 0, 'sexual_word_list.json': 0, 'zh_pornsignals.json': 0}}
{'chain_length': '9', 'dir': 'jupyter-structured-clean-dedup', 'id': '294837', 'license': 'MIT', 'n_tokens_mistral': 1662, 'n_tokens_neox': 1574, 'n_words': 330, 'path': 'w2/w2-Day_3/JSON_exercise_CLONED.ipynb', 'repo_name': 'bmskarate/lighthouseMain', 'size': '4945'}