import pandas as pd

"""
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-docs.github.io/pandas-docs-travis/io.html#json
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-docs.github.io/pandas-docs-travis/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
"""


# load json data frame
dataFrame = pd.read_json('data/world_bank_projects.json')

dataFrame

dataFrame.info()

dataFrame.columns

"""
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
"""


dataFrame.groupby(dataFrame.countryshortname).count().sort('_id', ascending=False).head(10)

"""
Explanation: Top 10 Countries with most projects
End of explanation
"""


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

"""
Explanation: Top 10 major project themes
End of explanation
"""


themeNameCode = json_normalize(themeNameCode)
themeNameCode['count']=themeNameCode.groupby('code').transform('count')
themeNameCode.sort('count', ascending=False).drop_duplicates().head(10)

"""
Explanation: Dataframe with the missing names filled in
Top 10 themes
End of explanation
"""


