<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-docs.github.io/pandas-docs-travis/io.htmljson+ 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-docs.github.io/pandas-docs-travis/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']])<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># load json data frame
dataFrame = pd.read_json('data/world_bank_projects.json')
dataFrame
dataFrame.info()
dataFrame.columns<jupyter_output><empty_output><jupyter_text>Top 10 Countries with most projects<jupyter_code>dataFrame.groupby(dataFrame.countryshortname).count().sort('_id', ascending=False).head(10)<jupyter_output><empty_output><jupyter_text>Top 10 major project themes<jupyter_code>themeNameCode = [] 

for codes in dataFrame.mjtheme_namecode:
    themeNameCode += codes
themeNameCode = json_normalize(themeNameCode)
themeNameCode['count']=themeNameCode.groupby('code').transform('count')
themeNameCode.sort('count', ascending=False).drop_duplicates().head(10)
#Create dictionary Code:Name to replace empty names. 
codeNameDict = {}
for codes in dataFrame.mjtheme_namecode:
    for code in codes:
        if code['name']!='':
            codeNameDict[code['code']]=code['name']

index=0
for codes in dataFrame.mjtheme_namecode:
    innerIndex=0
    for code in codes:
        if code['name']=='':
            dataFrame.mjtheme_namecode[index][innerIndex]['name']=codeNameDict[code['code']]
        innerIndex += 1
    index += 1

themeNameCode = [] 
for codes in dataFrame.mjtheme_namecode:
    themeNameCode += codes<jupyter_output><empty_output><jupyter_text>Dataframe with the missing names filled in Top 10 themes<jupyter_code>themeNameCode = json_normalize(themeNameCode)
themeNameCode['count']=themeNameCode.groupby('code').transform('count')
themeNameCode.sort('count', ascending=False).drop_duplicates().head(10)<jupyter_output><empty_output>
{'fraction_non_alphanumeric': 0.12734177215189874, 'fraction_numerical': 0.01189873417721519, 'mean_word_length': 5.509060955518946, 'pattern_counts': {'":': 0, '<': 36, '<?xml version=': 0, '>': 36, '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': '343642', 'license': 'Apache-2.0', 'n_tokens_mistral': 1250, 'n_tokens_neox': 1186, 'n_words': 307, 'path': 'Week_1/DATA_WRANGLING/WORKING_WITH_DATA_IN_FILES/data_wrangling_json/sliderule_dsi_json_exercise.ipynb', 'repo_name': 'abhipr1/DATA_SCIENCE_INTENSIVE', 'size': '3963'}