#NEWLINE# def load_world_bank_health_n_pop( ) : #NEWLINE# #INDENT# 'Loads the world bank health dataset, slices and a dashboard' #NEWLINE# tbl_name = 'wb_health_population' #NEWLINE# with gzip . open ( os . path . join ( DATA_FOLDER , 'countries.json.gz' ) ) as f : #NEWLINE# #INDENT# pdf = pd . read_json ( f ) #NEWLINE# #UNINDENT# pdf . columns = [ col . replace ( '.' , '_' ) for col in pdf . columns ] #NEWLINE# pdf . year = pd . to_datetime ( pdf . year ) #NEWLINE# pdf . to_sql ( tbl_name , db . engine , if_exists = 'replace' , chunksize = 500 , dtype = { #NEWLINE# #INDENT# 'year' : DateTime ( ) , #NEWLINE# 'country_code' : String ( 3 ) , #NEWLINE# 'country_name' : String ( 255 ) , #NEWLINE# 'region' : String ( 255 ) , #NEWLINE# #UNINDENT# } , index = False ) #NEWLINE# print ( 'Creating table [wb_health_population] reference' ) #NEWLINE# tbl = db . session . query ( TBL ) . filter_by ( table_name = tbl_name ) . first ( ) #NEWLINE# if ( not tbl ) : #NEWLINE# #INDENT# tbl = TBL ( table_name = tbl_name ) #NEWLINE# #UNINDENT# tbl . description = utils . readfile ( os . path . join ( DATA_FOLDER , 'countries.md' ) ) #NEWLINE# tbl . main_dttm_col = 'year' #NEWLINE# tbl . is_featured = True #NEWLINE# tbl . database = get_or_create_db ( db . session ) #NEWLINE# db . session . merge ( tbl ) #NEWLINE# db . session . commit ( ) #NEWLINE# tbl . fetch_metadata ( ) #NEWLINE# defaults = { #NEWLINE# #INDENT# 'compare_lag' : '10' , #NEWLINE# 'compare_suffix' : 'o10Y' , #NEWLINE# 'datasource_id' : '1' , #NEWLINE# 'datasource_name' : 'birth_names' , #NEWLINE# 'datasource_type' : 'table' , #NEWLINE# 'limit' : '25' , #NEWLINE# 'granularity' : 'year' , #NEWLINE# 'groupby' : [ ] , #NEWLINE# 'metric' : 'sum__SP_POP_TOTL' , #NEWLINE# 'metrics' : [ 'sum__SP_POP_TOTL' ] , #NEWLINE# 'row_limit' : config . get ( 'ROW_LIMIT' ) , #NEWLINE# 'since' : '2014-01-01' , #NEWLINE# 'until' : '2014-01-01' , #NEWLINE# 'where' : '' , #NEWLINE# 'markup_type' : 'markdown' , #NEWLINE# 'country_fieldtype' : 'cca3' , #NEWLINE# 'secondary_metric' : 'sum__SP_POP_TOTL' , #NEWLINE# 'entity' : 'country_code' , #NEWLINE# 'show_bubbles' : 'y' , #NEWLINE# #UNINDENT# } #NEWLINE# print ( 'Creating slices' ) #NEWLINE# slices = [ Slice ( slice_name = 'Region Filter' , viz_type = 'filter_box' , datasource_type = 'table' , table = tbl , params = get_slice_json ( defaults , viz_type = 'filter_box' , groupby = [ 'region' , 'country_name' ] ) ) , Slice ( slice_name = "World's Population" , viz_type = 'big_number' , datasource_type = 'table' , table = tbl , params = get_slice_json ( defaults , since = '2000' , viz_type = 'big_number' , compare_lag = '10' , metric = 'sum__SP_POP_TOTL' , compare_suffix = 'over 10Y' ) ) , Slice ( slice_name = 'Most Populated Countries' , viz_type = 'table' , datasource_type = 'table' , table = tbl , params = get_slice_json ( defaults , viz_type = 'table' , metrics = [ 'sum__SP_POP_TOTL' ] , groupby = [ 'country_name' ] ) ) , Slice ( slice_name = 'Growth Rate' , viz_type = 'line' , datasource_type = 'table' , table = tbl , params = get_slice_json ( defaults , viz_type = 'line' , since = '1960-01-01' , metrics = [ 'sum__SP_POP_TOTL' ] , num_period_compare = '10' , groupby = [ 'country_name' ] ) ) , Slice ( slice_name = '% Rural' , viz_type = 'world_map' , datasource_type = 'table' , table = tbl , params = get_slice_json ( defaults , viz_type = 'world_map' , metric = 'sum__SP_RUR_TOTL_ZS' , num_period_compare = '10' ) ) , Slice ( slice_name = 'Life Expexctancy VS Rural %' , viz_type = 'bubble' , datasource_type = 'table' , table = tbl , params = get_slice_json ( defaults , viz_type = 'bubble' , since = '2011-01-01' , until = '2011-01-01' , series = 'region' , limit = '0' , entity = 'country_name' , x = 'sum__SP_RUR_TOTL_ZS' , y = 'sum__SP_DYN_LE00_IN' , size = 'sum__SP_POP_TOTL' , max_bubble_size = '50' , flt_col_1 = 'country_code' , flt_op_1 = 'not in' , flt_eq_1 = 'TCA,MNP,DMA,MHL,MCO,SXM,CYM,TUV,IMY,KNA,ASM,ADO,AMA,PLW' , num_period_compare = '10' ) ) , Slice ( slice_name = 'Rural Breakdown' , viz_type = 'sunburst' , datasource_type = 'table' , table = tbl , params = get_slice_json ( defaults , viz_type = 'sunburst' , groupby = [ 'region' , 'country_name' ] , secondary_metric = 'sum__SP_RUR_TOTL' , since = '2011-01-01' , until = '2011-01-01' ) ) , Slice ( slice_name = "World's Pop Growth" , viz_type = 'area' , datasource_type = 'table' , table = tbl , params = get_slice_json ( defaults , since = '1960-01-01' , until = 'now' , viz_type = 'area' , groupby = [ 'region' ] ) ) , Slice ( slice_name = 'Box plot' , viz_type = 'box_plot' , datasource_type = 'table' , table = tbl , params = get_slice_json ( defaults , since = '1960-01-01' , until = 'now' , whisker_options = 'Tukey' , viz_type = 'box_plot' , groupby = [ 'region' ] ) ) , Slice ( slice_name = 'Treemap' , viz_type = 'treemap' , datasource_type = 'table' , table = tbl , params = get_slice_json ( slc , since = '1960-01-01' , until = 'now' , viz_type = 'treemap' , metrics = [ 'sum__SP_POP_TOTL' ] , groupby = [ 'region' , 'country_code' ] ) ) , Slice ( slice_name = 'Parallel Coordinates' , viz_type = 'para' , datasource_type = 'table' , table = tbl , params = get_slice_json ( defaults , since = '2011-01-01' , until = '2011-01-01' , viz_type = 'para' , limit = 100 , metrics = [ 'sum__SP_POP_TOTL' , 'sum__SP_RUR_TOTL_ZS' , 'sum__SH_DYN_AIDS' ] , secondary_metric = 'sum__SP_POP_TOTL' , series = [ 'country_name' ] ) ) ] #NEWLINE# for slc in slices : #NEWLINE# #INDENT# merge_slice ( slc ) #NEWLINE# #UNINDENT# print ( "Creating a World's Health Bank dashboard" ) #NEWLINE# dash_name = "World's Health Bank Dashboard" #NEWLINE# dash = db . session . query ( Dash ) . filter_by ( dashboard_title = dash_name ) . first ( ) #NEWLINE# if ( not dash ) : #NEWLINE# #INDENT# dash = Dash ( ) #NEWLINE# #UNINDENT# js = textwrap . dedent ( '    [\n        {\n            "size_y": 2,\n            "size_x": 3,\n            "col": 10,\n            "slice_id": "22",\n            "row": 1\n        },\n        {\n            "size_y": 3,\n            "size_x": 3,\n            "col": 10,\n            "slice_id": "23",\n            "row": 3\n        },\n        {\n            "size_y": 8,\n            "size_x": 3,\n            "col": 1,\n            "slice_id": "24",\n            "row": 1\n        },\n        {\n            "size_y": 3,\n            "size_x": 6,\n            "col": 4,\n            "slice_id": "25",\n            "row": 6\n        },\n        {\n            "size_y": 5,\n            "size_x": 6,\n            "col": 4,\n            "slice_id": "26",\n            "row": 1\n        },\n        {\n            "size_y": 4,\n            "size_x": 6,\n            "col": 7,\n            "slice_id": "27",\n            "row": 9\n        },\n        {\n            "size_y": 3,\n            "size_x": 3,\n            "col": 10,\n            "slice_id": "28",\n            "row": 6\n        },\n        {\n            "size_y": 4,\n            "size_x": 6,\n            "col": 1,\n            "slice_id": "29",\n            "row": 9\n        },\n        {\n            "size_y": 4,\n            "size_x": 5,\n            "col": 8,\n            "slice_id": "30",\n            "row": 13\n        },\n        {\n            "size_y": 4,\n            "size_x": 7,\n            "col": 1,\n            "slice_id": "31",\n            "row": 13\n        }\n    ]\n    ' ) #NEWLINE# l = json . loads ( js ) #NEWLINE# for ( i , pos ) in enumerate ( l ) : #NEWLINE# #INDENT# pos [ 'slice_id' ] = str ( slices [ i ] . id ) #NEWLINE# #UNINDENT# dash . dashboard_title = dash_name #NEWLINE# dash . position_json = json . dumps ( l , indent = 4 ) #NEWLINE# dash . slug = 'world_health' #NEWLINE# dash . slices = slices [ : ( - 1 ) ] #NEWLINE# db . session . merge ( dash ) #NEWLINE# db . session . commit ( )