import pandas as pd
import numpy as np

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

sample_json_df.info()

wb_projects['country_namecode'].str.contains()

"""
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 as Pandas dataframe
wb_projects = pd.read_json('data/world_bank_projects.json')


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


topcountries=wb_projects['countrycode'].value_counts()[:10]

topcountries.name='count'


countrydict=wb_projects['country_namecode'].str.split(pat='!\$!',expand=True).set_index(1).groupby(level=0).last()
countrydict.columns=['name']

pd.concat([countrydict,topcountries],axis=1,join='inner').sort('count',ascending=False)

"""
Explanation: Part 1
End of explanation
"""


codecounts=(wb_projects['mjthemecode'].str.split(pat=',',expand=True)).apply(lambda x: x.value_counts()).sum(axis=1)
codecounts

c=wb_projects['mjtheme_namecode'].map(lambda x: pd.DataFrame(x).set_index('code').groupby(level=0).last())
#sample_json_df['mjtheme_namecode']=c
#codedict=c[0]
#for i in c[1:20]:
#    codedict=codedict.merge(i,how='outer')
#codedict
    

codedict=pd.concat(c.values, axis=1, join='outer').transpose().replace(to_replace='',value=np.nan).fillna(method='bfill').iloc[0]

a=pd.DataFrame([codedict,codecounts])
a.columns=a.columns.astype('int64')
a.index=pd.Index([u'name', u'count'], dtype='object')
a=a.transpose()
a.sort(columns='count',ascending=False).iloc[:10]

"""
Explanation: Part 2
End of explanation
"""


wb_projects['mjtheme_namecode']=c.map(lambda x: pd.concat([x,codedict],axis=1,join='inner').iloc[:,1])

"""
Explanation: Part 3
End of explanation
"""


codedict

pd.concat([c[5],codedict],axis=1,join='inner').iloc[:,1]

codes.apply(lambda x: x.value_counts()).sum(axis=1).order(ascending=False)

codes = sample_json_df['mjthemecode'].str.split(pat=',',expand=True)
tots=codes[0].value_counts()
codes.columns
for i in codes.columns[1:]:
    tots += codes[i].value_counts()
tots

codes[0].value_counts()

sample_json_df['mjtheme_namecode'].map(len).min()

pd.DataFrame(sample_json_df['mjtheme_namecode'][4])

pd.DataFrame(sample_json_df['mjtheme_namecode'][2])

pd.DataFrame(sample_json_df['mjtheme_namecode'][2]).merge(pd.DataFrame(sample_json_df['mjtheme_namecode'][4]),how='outer')

c.values[3].groupby(level=0).last()

codecounts.index

codedict.index=codedict.index.astype('float64')

pd.concat((sample_json_df['mjtheme_namecode'].map(lambda x: pd.DataFrame(x).set_index('code'))).values)[11]

"""
Explanation: Scrap
End of explanation
"""


