CLUSTER-DATA-PROCESSING-Homework-Cloud.ipynb.bak

<<<<<<< HEAD
{"nbformat":4,"nbformat_minor":0,"metadata":{"colab":{"name":" Homework_no_Sol_Remote_New_Data.ipynb","provenance":[{"file_id":"1V4kiujaAnm3hHMomSK1eDz5jQjdKxmWU","timestamp":1571083042642}],"collapsed_sections":[],"toc_visible":true,"machine_shape":"hm"},"kernelspec":{"name":"python3","display_name":"Python 3"},"widgets":{"application/vnd.jupyter.widget-state+json":{"0ec3bf246d6241e38d2b68f478bccb36":{"model_module":"@jupyter-widgets/controls","model_name":"FloatProgressModel","state":{"_view_name":"ProgressView","style":"IPY_MODEL_f848b820820f462f9bd7788a206c6197","_dom_classes":[],"description":"Progress:","_model_name":"FloatProgressModel","bar_style":"info","max":1,"_view_module":"@jupyter-widgets/controls","_model_module_version":"1.5.0","value":0,"_view_count":null,"_view_module_version":"1.5.0","orientation":"horizontal","min":0,"description_tooltip":null,"_model_module":"@jupyter-widgets/controls","layout":"IPY_MODEL_c81f5783e51f450f8335631023d58bec"}},"f848b820820f462f9bd7788a206c6197":{"model_module":"@jupyter-widgets/controls","model_name":"ProgressStyleModel","state":{"_view_name":"StyleView","_model_name":"ProgressStyleModel","description_width":"","_view_module":"@jupyter-widgets/base","_model_module_version":"1.5.0","_view_count":null,"_view_module_version":"1.2.0","bar_color":null,"_model_module":"@jupyter-widgets/controls"}},"c81f5783e51f450f8335631023d58bec":{"model_module":"@jupyter-widgets/base","model_name":"LayoutModel","state":{"_view_name":"LayoutView","grid_template_rows":null,"right":null,"justify_content":null,"_view_module":"@jupyter-widgets/base","overflow":null,"_model_module_version":"1.2.0","_view_count":null,"flex_flow":null,"width":"50%","min_width":null,"border":null,"align_items":null,"bottom":null,"_model_module":"@jupyter-widgets/base","top":null,"grid_column":null,"overflow_y":null,"overflow_x":null,"grid_auto_flow":null,"grid_area":null,"grid_template_columns":null,"flex":null,"_model_name":"LayoutModel","justify_items":null,"grid_row":null,"max_height":null,"align_content":null,"visibility":null,"align_self":null,"height":"25px","min_height":null,"padding":null,"grid_auto_rows":null,"grid_gap":null,"max_width":null,"order":null,"_view_module_version":"1.2.0","grid_template_areas":null,"object_position":null,"object_fit":null,"grid_auto_columns":null,"margin":null,"display":null,"left":null}},"8b3b2f26485c413881059c5affd2055e":{"model_module":"@jupyter-widgets/controls","model_name":"FloatProgressModel","state":{"_view_name":"ProgressView","style":"IPY_MODEL_0f816c10fa484f01b4a14190ade37768","_dom_classes":[],"description":"Progress:","_model_name":"FloatProgressModel","bar_style":"info","max":1,"_view_module":"@jupyter-widgets/controls","_model_module_version":"1.5.0","value":0,"_view_count":null,"_view_module_version":"1.5.0","orientation":"horizontal","min":0,"description_tooltip":null,"_model_module":"@jupyter-widgets/controls","layout":"IPY_MODEL_b2af543a4da04a53a7080b43eea45486"}},"0f816c10fa484f01b4a14190ade37768":{"model_module":"@jupyter-widgets/controls","model_name":"ProgressStyleModel","state":{"_view_name":"StyleView","_model_name":"ProgressStyleModel","description_width":"","_view_module":"@jupyter-widgets/base","_model_module_version":"1.5.0","_view_count":null,"_view_module_version":"1.2.0","bar_color":null,"_model_module":"@jupyter-widgets/controls"}},"b2af543a4da04a53a7080b43eea45486":{"model_module":"@jupyter-widgets/base","model_name":"LayoutModel","state":{"_view_name":"LayoutView","grid_template_rows":null,"right":null,"justify_content":null,"_view_module":"@jupyter-widgets/base","overflow":null,"_model_module_version":"1.2.0","_view_count":null,"flex_flow":null,"width":"50%","min_width":null,"border":null,"align_items":null,"bottom":null,"_model_module":"@jupyter-widgets/base","top":null,"grid_column":null,"overflow_y":null,"overflow_x":null,"grid_auto_flow":null,"grid_area":null,"grid_template_columns":null,"flex":null,"_model_name":"LayoutModel","justify_items":null,"grid_row":null,"max_height":null,"align_content":null,"visibility":null,"align_self":null,"height":"25px","min_height":null,"padding":null,"grid_auto_rows":null,"grid_gap":null,"max_width":null,"order":null,"_view_module_version":"1.2.0","grid_template_areas":null,"object_position":null,"object_fit":null,"grid_auto_columns":null,"margin":null,"display":null,"left":null}},"10e9bda7da7e48d0845b749ab8e9e9ee":{"model_module":"@jupyter-widgets/controls","model_name":"FloatProgressModel","state":{"_view_name":"ProgressView","style":"IPY_MODEL_d1a222beb845430abec998d9c6f175ea","_dom_classes":[],"description":"Progress:","_model_name":"FloatProgressModel","bar_style":"info","max":1,"_view_module":"@jupyter-widgets/controls","_model_module_version":"1.5.0","value":0,"_view_count":null,"_view_module_version":"1.5.0","orientation":"horizontal","min":0,"description_tooltip":null,"_model_module":"@jupyter-widgets/controls","layout":"IPY_MODEL_3747c90befbe4e7094faa212b6512361"}},"d1a222beb845430abec998d9c6f175ea":{"model_module":"@jupyter-widgets/controls","model_name":"ProgressStyleModel","state":{"_view_name":"StyleView","_model_name":"ProgressStyleModel","description_width":"","_view_module":"@jupyter-widgets/base","_model_module_version":"1.5.0","_view_count":null,"_view_module_version":"1.2.0","bar_color":null,"_model_module":"@jupyter-widgets/controls"}},"3747c90befbe4e7094faa212b6512361":{"model_module":"@jupyter-widgets/base","model_name":"LayoutModel","state":{"_view_name":"LayoutView","grid_template_rows":null,"right":null,"justify_content":null,"_view_module":"@jupyter-widgets/base","overflow":null,"_model_module_version":"1.2.0","_view_count":null,"flex_flow":null,"width":"50%","min_width":null,"border":null,"align_items":null,"bottom":null,"_model_module":"@jupyter-widgets/base","top":null,"grid_column":null,"overflow_y":null,"overflow_x":null,"grid_auto_flow":null,"grid_area":null,"grid_template_columns":null,"flex":null,"_model_name":"LayoutModel","justify_items":null,"grid_row":null,"max_height":null,"align_content":null,"visibility":null,"align_self":null,"height":"25px","min_height":null,"padding":null,"grid_auto_rows":null,"grid_gap":null,"max_width":null,"order":null,"_view_module_version":"1.2.0","grid_template_areas":null,"object_position":null,"object_fit":null,"grid_auto_columns":null,"margin":null,"display":null,"left":null}},"fc9a5ea9104f4def9166d3a62dff697a":{"model_module":"@jupyter-widgets/controls","model_name":"FloatProgressModel","state":{"_view_name":"ProgressView","style":"IPY_MODEL_8d520961230c4509820ffc9e698c6cdc","_dom_classes":[],"description":"Progress:","_model_name":"FloatProgressModel","bar_style":"info","max":1,"_view_module":"@jupyter-widgets/controls","_model_module_version":"1.5.0","value":0,"_view_count":null,"_view_module_version":"1.5.0","orientation":"horizontal","min":0,"description_tooltip":null,"_model_module":"@jupyter-widgets/controls","layout":"IPY_MODEL_59e9ab8b8888436da6023bff00ce7098"}},"8d520961230c4509820ffc9e698c6cdc":{"model_module":"@jupyter-widgets/controls","model_name":"ProgressStyleModel","state":{"_view_name":"StyleView","_model_name":"ProgressStyleModel","description_width":"","_view_module":"@jupyter-widgets/base","_model_module_version":"1.5.0","_view_count":null,"_view_module_version":"1.2.0","bar_color":null,"_model_module":"@jupyter-widgets/controls"}},"59e9ab8b8888436da6023bff00ce7098":{"model_module":"@jupyter-widgets/base","model_name":"LayoutModel","state":{"_view_name":"LayoutView","grid_template_rows":null,"right":null,"justify_content":null,"_view_module":"@jupyter-widgets/base","overflow":null,"_model_module_version":"1.2.0","_view_count":null,"flex_flow":null,"width":"50%","min_width":null,"border":null,"align_items":null,"bottom":null,"_model_module":"@jupyter-widgets/base","top":null,"grid_column":null,"overflow_y":null,"overflow_x":null,"grid_auto_flow":null,"grid_area":null,"grid_template_columns":null,"flex":null,"_model_name":"LayoutModel","justify_items":null,"grid_row":null,"max_height":null,"align_content":null,"visibility":null,"align_self":null,"height":"25px","min_height":null,"padding":null,"grid_auto_rows":null,"grid_gap":null,"max_width":null,"order":null,"_view_module_version":"1.2.0","grid_template_areas":null,"object_position":null,"object_fit":null,"grid_auto_columns":null,"margin":null,"display":null,"left":null}},"cfecaa14eb514313a5f15ed03ef6789d":{"model_module":"@jupyter-widgets/controls","model_name":"FloatProgressModel","state":{"_view_name":"ProgressView","style":"IPY_MODEL_5bea45fa7fc54ebebad0a57be6310c65","_dom_classes":[],"description":"Progress:","_model_name":"FloatProgressModel","bar_style":"info","max":1,"_view_module":"@jupyter-widgets/controls","_model_module_version":"1.5.0","value":0,"_view_count":null,"_view_module_version":"1.5.0","orientation":"horizontal","min":0,"description_tooltip":null,"_model_module":"@jupyter-widgets/controls","layout":"IPY_MODEL_472c58675e1e4d2eab714bd7ce9f0df3"}},"5bea45fa7fc54ebebad0a57be6310c65":{"model_module":"@jupyter-widgets/controls","model_name":"ProgressStyleModel","state":{"_view_name":"StyleView","_model_name":"ProgressStyleModel","description_width":"","_view_module":"@jupyter-widgets/base","_model_module_version":"1.5.0","_view_count":null,"_view_module_version":"1.2.0","bar_color":null,"_model_module":"@jupyter-widgets/controls"}},"472c58675e1e4d2eab714bd7ce9f0df3":{"model_module":"@jupyter-widgets/base","model_name":"LayoutModel","state":{"_view_name":"LayoutView","grid_template_rows":null,"right":null,"justify_content":null,"_view_module":"@jupyter-widgets/base","overflow":null,"_model_module_version":"1.2.0","_view_count":null,"flex_flow":null,"width":"50%","min_width":null,"border":null,"align_items":null,"bottom":null,"_model_module":"@jupyter-widgets/base","top":null,"grid_column":null,"overflow_y":null,"overflow_x":null,"grid_auto_flow":null,"grid_area":null,"grid_template_columns":null,"flex":null,"_model_name":"LayoutModel","justify_items":null,"grid_row":null,"max_height":null,"align_content":null,"visibility":null,"align_self":null,"height":"25px","min_height":null,"padding":null,"grid_auto_rows":null,"grid_gap":null,"max_width":null,"order":null,"_view_module_version":"1.2.0","grid_template_areas":null,"object_position":null,"object_fit":null,"grid_auto_columns":null,"margin":null,"display":null,"left":null}},"dc7c0513d85b4643bdbfc7bf8702e856":{"model_module":"@jupyter-widgets/controls","model_name":"FloatProgressModel","state":{"_view_name":"ProgressView","style":"IPY_MODEL_2056868703734a26b1db0e3ffec7699d","_dom_classes":[],"description":"Progress:","_model_name":"FloatProgressModel","bar_style":"info","max":1,"_view_module":"@jupyter-widgets/controls","_model_module_version":"1.5.0","value":0.9798657718120806,"_view_count":null,"_view_module_version":"1.5.0","orientation":"horizontal","min":0,"description_tooltip":null,"_model_module":"@jupyter-widgets/controls","layout":"IPY_MODEL_66aaa398ce5e401b9e1a1d87988e5487"}},"2056868703734a26b1db0e3ffec7699d":{"model_module":"@jupyter-widgets/controls","model_name":"ProgressStyleModel","state":{"_view_name":"StyleView","_model_name":"ProgressStyleModel","description_width":"","_view_module":"@jupyter-widgets/base","_model_module_version":"1.5.0","_view_count":null,"_view_module_version":"1.2.0","bar_color":null,"_model_module":"@jupyter-widgets/controls"}},"66aaa398ce5e401b9e1a1d87988e5487":{"model_module":"@jupyter-widgets/base","model_name":"LayoutModel","state":{"_view_name":"LayoutView","grid_template_rows":null,"right":null,"justify_content":null,"_view_module":"@jupyter-widgets/base","overflow":null,"_model_module_version":"1.2.0","_view_count":null,"flex_flow":null,"width":"50%","min_width":null,"border":null,"align_items":null,"bottom":null,"_model_module":"@jupyter-widgets/base","top":null,"grid_column":null,"overflow_y":null,"overflow_x":null,"grid_auto_flow":null,"grid_area":null,"grid_template_columns":null,"flex":null,"_model_name":"LayoutModel","justify_items":null,"grid_row":null,"max_height":null,"align_content":null,"visibility":null,"align_self":null,"height":"25px","min_height":null,"padding":null,"grid_auto_rows":null,"grid_gap":null,"max_width":null,"order":null,"_view_module_version":"1.2.0","grid_template_areas":null,"object_position":null,"object_fit":null,"grid_auto_columns":null,"margin":null,"display":null,"left":null}},"52815494440f4161b01b6eebb60b999d":{"model_module":"@jupyter-widgets/controls","model_name":"FloatProgressModel","state":{"_view_name":"ProgressView","style":"IPY_MODEL_91880f7960e5464da7893d80df72d483","_dom_classes":[],"description":"Progress:","_model_name":"FloatProgressModel","bar_style":"info","max":1,"_view_module":"@jupyter-widgets/controls","_model_module_version":"1.5.0","value":0,"_view_count":null,"_view_module_version":"1.5.0","orientation":"horizontal","min":0,"description_tooltip":null,"_model_module":"@jupyter-widgets/controls","layout":"IPY_MODEL_8ece5c44cc2c4a25b4fcc641882667a4"}},"91880f7960e5464da7893d80df72d483":{"model_module":"@jupyter-widgets/controls","model_name":"ProgressStyleModel","state":{"_view_name":"StyleView","_model_name":"ProgressStyleModel","description_width":"","_view_module":"@jupyter-widgets/base","_model_module_version":"1.5.0","_view_count":null,"_view_module_version":"1.2.0","bar_color":null,"_model_module":"@jupyter-widgets/controls"}},"8ece5c44cc2c4a25b4fcc641882667a4":{"model_module":"@jupyter-widgets/base","model_name":"LayoutModel","state":{"_view_name":"LayoutView","grid_template_rows":null,"right":null,"justify_content":null,"_view_module":"@jupyter-widgets/base","overflow":null,"_model_module_version":"1.2.0","_view_count":null,"flex_flow":null,"width":"50%","min_width":null,"border":null,"align_items":null,"bottom":null,"_model_module":"@jupyter-widgets/base","top":null,"grid_column":null,"overflow_y":null,"overflow_x":null,"grid_auto_flow":null,"grid_area":null,"grid_template_columns":null,"flex":null,"_model_name":"LayoutModel","justify_items":null,"grid_row":null,"max_height":null,"align_content":null,"visibility":null,"align_self":null,"height":"25px","min_height":null,"padding":null,"grid_auto_rows":null,"grid_gap":null,"max_width":null,"order":null,"_view_module_version":"1.2.0","grid_template_areas":null,"object_position":null,"object_fit":null,"grid_auto_columns":null,"margin":null,"display":null,"left":null}},"f52ae0051fc24086995d05461b02229d":{"model_module":"@jupyter-widgets/controls","model_name":"FloatProgressModel","state":{"_view_name":"ProgressView","style":"IPY_MODEL_defbbe69eeb44fcbba983d981a4521fe","_dom_classes":[],"description":"Progress:","_model_name":"FloatProgressModel","bar_style":"info","max":1,"_view_module":"@jupyter-widgets/controls","_model_module_version":"1.5.0","value":0,"_view_count":null,"_view_module_version":"1.5.0","orientation":"horizontal","min":0,"description_tooltip":null,"_model_module":"@jupyter-widgets/controls","layout":"IPY_MODEL_5647e8443a5a4d7a9d775c8a2bea87c6"}},"defbbe69eeb44fcbba983d981a4521fe":{"model_module":"@jupyter-widgets/controls","model_name":"ProgressStyleModel","state":{"_view_name":"StyleView","_model_name":"ProgressStyleModel","description_width":"","_view_module":"@jupyter-widgets/base","_model_module_version":"1.5.0","_view_count":null,"_view_module_version":"1.2.0","bar_color":null,"_model_module":"@jupyter-widgets/controls"}},"5647e8443a5a4d7a9d775c8a2bea87c6":{"model_module":"@jupyter-widgets/base","model_name":"LayoutModel","state":{"_view_name":"LayoutView","grid_template_rows":null,"right":null,"justify_content":null,"_view_module":"@jupyter-widgets/base","overflow":null,"_model_module_version":"1.2.0","_view_count":null,"flex_flow":null,"width":"50%","min_width":null,"border":null,"align_items":null,"bottom":null,"_model_module":"@jupyter-widgets/base","top":null,"grid_column":null,"overflow_y":null,"overflow_x":null,"grid_auto_flow":null,"grid_area":null,"grid_template_columns":null,"flex":null,"_model_name":"LayoutModel","justify_items":null,"grid_row":null,"max_height":null,"align_content":null,"visibility":null,"align_self":null,"height":"25px","min_height":null,"padding":null,"grid_auto_rows":null,"grid_gap":null,"max_width":null,"order":null,"_view_module_version":"1.2.0","grid_template_areas":null,"object_position":null,"object_fit":null,"grid_auto_columns":null,"margin":null,"display":null,"left":null}},"00c647594bdf4e86aebd273fffcbcc85":{"model_module":"@jupyter-widgets/controls","model_name":"FloatProgressModel","state":{"_view_name":"ProgressView","style":"IPY_MODEL_8de371ef2a1749558e1a7fe53363d060","_dom_classes":[],"description":"Progress:","_model_name":"FloatProgressModel","bar_style":"info","max":1,"_view_module":"@jupyter-widgets/controls","_model_module_version":"1.5.0","value":0,"_view_count":null,"_view_module_version":"1.5.0","orientation":"horizontal","min":0,"description_tooltip":null,"_model_module":"@jupyter-widgets/controls","layout":"IPY_MODEL_3e73d2733d814879bfb767f462570743"}},"8de371ef2a1749558e1a7fe53363d060":{"model_module":"@jupyter-widgets/controls","model_name":"ProgressStyleModel","state":{"_view_name":"StyleView","_model_name":"ProgressStyleModel","description_width":"","_view_module":"@jupyter-widgets/base","_model_module_version":"1.5.0","_view_count":null,"_view_module_version":"1.2.0","bar_color":null,"_model_module":"@jupyter-widgets/controls"}},"3e73d2733d814879bfb767f462570743":{"model_module":"@jupyter-widgets/base","model_name":"LayoutModel","state":{"_view_name":"LayoutView","grid_template_rows":null,"right":null,"justify_content":null,"_view_module":"@jupyter-widgets/base","overflow":null,"_model_module_version":"1.2.0","_view_count":null,"flex_flow":null,"width":"50%","min_width":null,"border":null,"align_items":null,"bottom":null,"_model_module":"@jupyter-widgets/base","top":null,"grid_column":null,"overflow_y":null,"overflow_x":null,"grid_auto_flow":null,"grid_area":null,"grid_template_columns":null,"flex":null,"_model_name":"LayoutModel","justify_items":null,"grid_row":null,"max_height":null,"align_content":null,"visibility":null,"align_self":null,"height":"25px","min_height":null,"padding":null,"grid_auto_rows":null,"grid_gap":null,"max_width":null,"order":null,"_view_module_version":"1.2.0","grid_template_areas":null,"object_position":null,"object_fit":null,"grid_auto_columns":null,"margin":null,"display":null,"left":null}},"6bd5845756c84761a22c4fca0c27e091":{"model_module":"@jupyter-widgets/controls","model_name":"FloatProgressModel","state":{"_view_name":"ProgressView","style":"IPY_MODEL_18c3f006e61b466abea135b15d4fe624","_dom_classes":[],"description":"Progress:","_model_name":"FloatProgressModel","bar_style":"info","max":1,"_view_module":"@jupyter-widgets/controls","_model_module_version":"1.5.0","value":0,"_view_count":null,"_view_module_version":"1.5.0","orientation":"horizontal","min":0,"description_tooltip":null,"_model_module":"@jupyter-widgets/controls","layout":"IPY_MODEL_637bd902d20544569334d56244bca5d3"}},"18c3f006e61b466abea135b15d4fe624":{"model_module":"@jupyter-widgets/controls","model_name":"ProgressStyleModel","state":{"_view_name":"StyleView","_model_name":"ProgressStyleModel","description_width":"","_view_module":"@jupyter-widgets/base","_model_module_version":"1.5.0","_view_count":null,"_view_module_version":"1.2.0","bar_color":null,"_model_module":"@jupyter-widgets/controls"}},"637bd902d20544569334d56244bca5d3":{"model_module":"@jupyter-widgets/base","model_name":"LayoutModel","state":{"_view_name":"LayoutView","grid_template_rows":null,"right":null,"justify_content":null,"_view_module":"@jupyter-widgets/base","overflow":null,"_model_module_version":"1.2.0","_view_count":null,"flex_flow":null,"width":"50%","min_width":null,"border":null,"align_items":null,"bottom":null,"_model_module":"@jupyter-widgets/base","top":null,"grid_column":null,"overflow_y":null,"overflow_x":null,"grid_auto_flow":null,"grid_area":null,"grid_template_columns":null,"flex":null,"_model_name":"LayoutModel","justify_items":null,"grid_row":null,"max_height":null,"align_content":null,"visibility":null,"align_self":null,"height":"25px","min_height":null,"padding":null,"grid_auto_rows":null,"grid_gap":null,"max_width":null,"order":null,"_view_module_version":"1.2.0","grid_template_areas":null,"object_position":null,"object_fit":null,"grid_auto_columns":null,"margin":null,"display":null,"left":null}},"277806ae13be4bd2a7e17019f477a79e":{"model_module":"@jupyter-widgets/controls","model_name":"FloatProgressModel","state":{"_view_name":"ProgressView","style":"IPY_MODEL_0f19d766cf624efc9a417c72f607368e","_dom_classes":[],"description":"Progress:","_model_name":"FloatProgressModel","bar_style":"info","max":1,"_view_module":"@jupyter-widgets/controls","_model_module_version":"1.5.0","value":0,"_view_count":null,"_view_module_version":"1.5.0","orientation":"horizontal","min":0,"description_tooltip":null,"_model_module":"@jupyter-widgets/controls","layout":"IPY_MODEL_cc5799297d594e738a34ed4b83a16fec"}},"0f19d766cf624efc9a417c72f607368e":{"model_module":"@jupyter-widgets/controls","model_name":"ProgressStyleModel","state":{"_view_name":"StyleView","_model_name":"ProgressStyleModel","description_width":"","_view_module":"@jupyter-widgets/base","_model_module_version":"1.5.0","_view_count":null,"_view_module_version":"1.2.0","bar_color":null,"_model_module":"@jupyter-widgets/controls"}},"cc5799297d594e738a34ed4b83a16fec":{"model_module":"@jupyter-widgets/base","model_name":"LayoutModel","state":{"_view_name":"LayoutView","grid_template_rows":null,"right":null,"justify_content":null,"_view_module":"@jupyter-widgets/base","overflow":null,"_model_module_version":"1.2.0","_view_count":null,"flex_flow":null,"width":"50%","min_width":null,"border":null,"align_items":null,"bottom":null,"_model_module":"@jupyter-widgets/base","top":null,"grid_column":null,"overflow_y":null,"overflow_x":null,"grid_auto_flow":null,"grid_area":null,"grid_template_columns":null,"flex":null,"_model_name":"LayoutModel","justify_items":null,"grid_row":null,"max_height":null,"align_content":null,"visibility":null,"align_self":null,"height":"25px","min_height":null,"padding":null,"grid_auto_rows":null,"grid_gap":null,"max_width":null,"order":null,"_view_module_version":"1.2.0","grid_template_areas":null,"object_position":null,"object_fit":null,"grid_auto_columns":null,"margin":null,"display":null,"left":null}},"0b0cf2113c5d46079b10ee9291d81ca2":{"model_module":"@jupyter-widgets/controls","model_name":"FloatProgressModel","state":{"_view_name":"ProgressView","style":"IPY_MODEL_e904164016e649b39ae675c5316f3718","_dom_classes":[],"description":"Progress:","_model_name":"FloatProgressModel","bar_style":"info","max":1,"_view_module":"@jupyter-widgets/controls","_model_module_version":"1.5.0","value":0,"_view_count":null,"_view_module_version":"1.5.0","orientation":"horizontal","min":0,"description_tooltip":null,"_model_module":"@jupyter-widgets/controls","layout":"IPY_MODEL_b2deaafff8174b3cab34294d58e1e27e"}},"e904164016e649b39ae675c5316f3718":{"model_module":"@jupyter-widgets/controls","model_name":"ProgressStyleModel","state":{"_view_name":"StyleView","_model_name":"ProgressStyleModel","description_width":"","_view_module":"@jupyter-widgets/base","_model_module_version":"1.5.0","_view_count":null,"_view_module_version":"1.2.0","bar_color":null,"_model_module":"@jupyter-widgets/controls"}},"b2deaafff8174b3cab34294d58e1e27e":{"model_module":"@jupyter-widgets/base","model_name":"LayoutModel","state":{"_view_name":"LayoutView","grid_template_rows":null,"right":null,"justify_content":null,"_view_module":"@jupyter-widgets/base","overflow":null,"_model_module_version":"1.2.0","_view_count":null,"flex_flow":null,"width":"50%","min_width":null,"border":null,"align_items":null,"bottom":null,"_model_module":"@jupyter-widgets/base","top":null,"grid_column":null,"overflow_y":null,"overflow_x":null,"grid_auto_flow":null,"grid_area":null,"grid_template_columns":null,"flex":null,"_model_name":"LayoutModel","justify_items":null,"grid_row":null,"max_height":null,"align_content":null,"visibility":null,"align_self":null,"height":"25px","min_height":null,"padding":null,"grid_auto_rows":null,"grid_gap":null,"max_width":null,"order":null,"_view_module_version":"1.2.0","grid_template_areas":null,"object_position":null,"object_fit":null,"grid_auto_columns":null,"margin":null,"display":null,"left":null}},"25c499f20e694a60bd3fc36ab3287402":{"model_module":"@jupyter-widgets/controls","model_name":"FloatProgressModel","state":{"_view_name":"ProgressView","style":"IPY_MODEL_a27a5af9ab1e47449f2cb01f298ab010","_dom_classes":[],"description":"Progress:","_model_name":"FloatProgressModel","bar_style":"info","max":1,"_view_module":"@jupyter-widgets/controls","_model_module_version":"1.5.0","value":0,"_view_count":null,"_view_module_version":"1.5.0","orientation":"horizontal","min":0,"description_tooltip":null,"_model_module":"@jupyter-widgets/controls","layout":"IPY_MODEL_e2efaeed796f491dac1e80de28d71575"}},"a27a5af9ab1e47449f2cb01f298ab010":{"model_module":"@jupyter-widgets/controls","model_name":"ProgressStyleModel","state":{"_view_name":"StyleView","_model_name":"ProgressStyleModel","description_width":"","_view_module":"@jupyter-widgets/base","_model_module_version":"1.5.0","_view_count":null,"_view_module_version":"1.2.0","bar_color":null,"_model_module":"@jupyter-widgets/controls"}},"e2efaeed796f491dac1e80de28d71575":{"model_module":"@jupyter-widgets/base","model_name":"LayoutModel","state":{"_view_name":"LayoutView","grid_template_rows":null,"right":null,"justify_content":null,"_view_module":"@jupyter-widgets/base","overflow":null,"_model_module_version":"1.2.0","_view_count":null,"flex_flow":null,"width":"50%","min_width":null,"border":null,"align_items":null,"bottom":null,"_model_module":"@jupyter-widgets/base","top":null,"grid_column":null,"overflow_y":null,"overflow_x":null,"grid_auto_flow":null,"grid_area":null,"grid_template_columns":null,"flex":null,"_model_name":"LayoutModel","justify_items":null,"grid_row":null,"max_height":null,"align_content":null,"visibility":null,"align_self":null,"height":"25px","min_height":null,"padding":null,"grid_auto_rows":null,"grid_gap":null,"max_width":null,"order":null,"_view_module_version":"1.2.0","grid_template_areas":null,"object_position":null,"object_fit":null,"grid_auto_columns":null,"margin":null,"display":null,"left":null}},"664d2f8b41e8447ba797bbd586cf7442":{"model_module":"@jupyter-widgets/controls","model_name":"FloatProgressModel","state":{"_view_name":"ProgressView","style":"IPY_MODEL_14f008c6d70a41de96a66a9255d28c91","_dom_classes":[],"description":"Progress:","_model_name":"FloatProgressModel","bar_style":"info","max":1,"_view_module":"@jupyter-widgets/controls","_model_module_version":"1.5.0","value":0,"_view_count":null,"_view_module_version":"1.5.0","orientation":"horizontal","min":0,"description_tooltip":null,"_model_module":"@jupyter-widgets/controls","layout":"IPY_MODEL_cac27272dfa64b55b8ac984c8f2e83e7"}},"14f008c6d70a41de96a66a9255d28c91":{"model_module":"@jupyter-widgets/controls","model_name":"ProgressStyleModel","state":{"_view_name":"StyleView","_model_name":"ProgressStyleModel","description_width":"","_view_module":"@jupyter-widgets/base","_model_module_version":"1.5.0","_view_count":null,"_view_module_version":"1.2.0","bar_color":null,"_model_module":"@jupyter-widgets/controls"}},"cac27272dfa64b55b8ac984c8f2e83e7":{"model_module":"@jupyter-widgets/base","model_name":"LayoutModel","state":{"_view_name":"LayoutView","grid_template_rows":null,"right":null,"justify_content":null,"_view_module":"@jupyter-widgets/base","overflow":null,"_model_module_version":"1.2.0","_view_count":null,"flex_flow":null,"width":"50%","min_width":null,"border":null,"align_items":null,"bottom":null,"_model_module":"@jupyter-widgets/base","top":null,"grid_column":null,"overflow_y":null,"overflow_x":null,"grid_auto_flow":null,"grid_area":null,"grid_template_columns":null,"flex":null,"_model_name":"LayoutModel","justify_items":null,"grid_row":null,"max_height":null,"align_content":null,"visibility":null,"align_self":null,"height":"25px","min_height":null,"padding":null,"grid_auto_rows":null,"grid_gap":null,"max_width":null,"order":null,"_view_module_version":"1.2.0","grid_template_areas":null,"object_position":null,"object_fit":null,"grid_auto_columns":null,"margin":null,"display":null,"left":null}},"90d37ecc6c6e42d8a7867fcce3980831":{"model_module":"@jupyter-widgets/controls","model_name":"FloatProgressModel","state":{"_view_name":"ProgressView","style":"IPY_MODEL_5b6f6a0b7f8a45949f82869fcae374cb","_dom_classes":[],"description":"Progress:","_model_name":"FloatProgressModel","bar_style":"info","max":1,"_view_module":"@jupyter-widgets/controls","_model_module_version":"1.5.0","value":0,"_view_count":null,"_view_module_version":"1.5.0","orientation":"horizontal","min":0,"description_tooltip":null,"_model_module":"@jupyter-widgets/controls","layout":"IPY_MODEL_3e9b9186606b4aa28ddeee3a5112ab20"}},"5b6f6a0b7f8a45949f82869fcae374cb":{"model_module":"@jupyter-widgets/controls","model_name":"ProgressStyleModel","state":{"_view_name":"StyleView","_model_name":"ProgressStyleModel","description_width":"","_view_module":"@jupyter-widgets/base","_model_module_version":"1.5.0","_view_count":null,"_view_module_version":"1.2.0","bar_color":null,"_model_module":"@jupyter-widgets/controls"}},"3e9b9186606b4aa28ddeee3a5112ab20":{"model_module":"@jupyter-widgets/base","model_name":"LayoutModel","state":{"_view_name":"LayoutView","grid_template_rows":null,"right":null,"justify_content":null,"_view_module":"@jupyter-widgets/base","overflow":null,"_model_module_version":"1.2.0","_view_count":null,"flex_flow":null,"width":"50%","min_width":null,"border":null,"align_items":null,"bottom":null,"_model_module":"@jupyter-widgets/base","top":null,"grid_column":null,"overflow_y":null,"overflow_x":null,"grid_auto_flow":null,"grid_area":null,"grid_template_columns":null,"flex":null,"_model_name":"LayoutModel","justify_items":null,"grid_row":null,"max_height":null,"align_content":null,"visibility":null,"align_self":null,"height":"25px","min_height":null,"padding":null,"grid_auto_rows":null,"grid_gap":null,"max_width":null,"order":null,"_view_module_version":"1.2.0","grid_template_areas":null,"object_position":null,"object_fit":null,"grid_auto_columns":null,"margin":null,"display":null,"left":null}},"9b53a3b6b43e46b48004eab3188f52cc":{"model_module":"@jupyter-widgets/controls","model_name":"FloatProgressModel","state":{"_view_name":"ProgressView","style":"IPY_MODEL_375176dbd9eb42a29fd34152142f70b3","_dom_classes":[],"description":"Progress:","_model_name":"FloatProgressModel","bar_style":"info","max":1,"_view_module":"@jupyter-widgets/controls","_model_module_version":"1.5.0","value":0,"_view_count":null,"_view_module_version":"1.5.0","orientation":"horizontal","min":0,"description_tooltip":null,"_model_module":"@jupyter-widgets/controls","layout":"IPY_MODEL_72ed66a0fe4d476ab6c55a255ae0cad0"}},"375176dbd9eb42a29fd34152142f70b3":{"model_module":"@jupyter-widgets/controls","model_name":"ProgressStyleModel","state":{"_view_name":"StyleView","_model_name":"ProgressStyleModel","description_width":"","_view_module":"@jupyter-widgets/base","_model_module_version":"1.5.0","_view_count":null,"_view_module_version":"1.2.0","bar_color":null,"_model_module":"@jupyter-widgets/controls"}},"72ed66a0fe4d476ab6c55a255ae0cad0":{"model_module":"@jupyter-widgets/base","model_name":"LayoutModel","state":{"_view_name":"LayoutView","grid_template_rows":null,"right":null,"justify_content":null,"_view_module":"@jupyter-widgets/base","overflow":null,"_model_module_version":"1.2.0","_view_count":null,"flex_flow":null,"width":"50%","min_width":null,"border":null,"align_items":null,"bottom":null,"_model_module":"@jupyter-widgets/base","top":null,"grid_column":null,"overflow_y":null,"overflow_x":null,"grid_auto_flow":null,"grid_area":null,"grid_template_columns":null,"flex":null,"_model_name":"LayoutModel","justify_items":null,"grid_row":null,"max_height":null,"align_content":null,"visibility":null,"align_self":null,"height":"25px","min_height":null,"padding":null,"grid_auto_rows":null,"grid_gap":null,"max_width":null,"order":null,"_view_module_version":"1.2.0","grid_template_areas":null,"object_position":null,"object_fit":null,"grid_auto_columns":null,"margin":null,"display":null,"left":null}},"06d34ff02a2f40acb420053db30d1ce6":{"model_module":"@jupyter-widgets/controls","model_name":"FloatProgressModel","state":{"_view_name":"ProgressView","style":"IPY_MODEL_8f3ed02d295449deb1a410e319457366","_dom_classes":[],"description":"Progress:","_model_name":"FloatProgressModel","bar_style":"info","max":1,"_view_module":"@jupyter-widgets/controls","_model_module_version":"1.5.0","value":0,"_view_count":null,"_view_module_version":"1.5.0","orientation":"horizontal","min":0,"description_tooltip":null,"_model_module":"@jupyter-widgets/controls","layout":"IPY_MODEL_da5370dcfa9e484ebec2232b753f9126"}},"8f3ed02d295449deb1a410e319457366":{"model_module":"@jupyter-widgets/controls","model_name":"ProgressStyleModel","state":{"_view_name":"StyleView","_model_name":"ProgressStyleModel","description_width":"","_view_module":"@jupyter-widgets/base","_model_module_version":"1.5.0","_view_count":null,"_view_module_version":"1.2.0","bar_color":null,"_model_module":"@jupyter-widgets/controls"}},"da5370dcfa9e484ebec2232b753f9126":{"model_module":"@jupyter-widgets/base","model_name":"LayoutModel","state":{"_view_name":"LayoutView","grid_template_rows":null,"right":null,"justify_content":null,"_view_module":"@jupyter-widgets/base","overflow":null,"_model_module_version":"1.2.0","_view_count":null,"flex_flow":null,"width":"50%","min_width":null,"border":null,"align_items":null,"bottom":null,"_model_module":"@jupyter-widgets/base","top":null,"grid_column":null,"overflow_y":null,"overflow_x":null,"grid_auto_flow":null,"grid_area":null,"grid_template_columns":null,"flex":null,"_model_name":"LayoutModel","justify_items":null,"grid_row":null,"max_height":null,"align_content":null,"visibility":null,"align_self":null,"height":"25px","min_height":null,"padding":null,"grid_auto_rows":null,"grid_gap":null,"max_width":null,"order":null,"_view_module_version":"1.2.0","grid_template_areas":null,"object_position":null,"object_fit":null,"grid_auto_columns":null,"margin":null,"display":null,"left":null}},"09bfe3c82da54132bc0b1de3698f5041":{"model_module":"@jupyter-widgets/controls","model_name":"FloatProgressModel","state":{"_view_name":"ProgressView","style":"IPY_MODEL_bdf094cb30f741778998082741862013","_dom_classes":[],"description":"Progress:","_model_name":"FloatProgressModel","bar_style":"info","max":1,"_view_module":"@jupyter-widgets/controls","_model_module_version":"1.5.0","value":0,"_view_count":null,"_view_module_version":"1.5.0","orientation":"horizontal","min":0,"description_tooltip":null,"_model_module":"@jupyter-widgets/controls","layout":"IPY_MODEL_c6e2b881ee2e4982bb3781c81e46fda5"}},"bdf094cb30f741778998082741862013":{"model_module":"@jupyter-widgets/controls","model_name":"ProgressStyleModel","state":{"_view_name":"StyleView","_model_name":"ProgressStyleModel","description_width":"","_view_module":"@jupyter-widgets/base","_model_module_version":"1.5.0","_view_count":null,"_view_module_version":"1.2.0","bar_color":null,"_model_module":"@jupyter-widgets/controls"}},"c6e2b881ee2e4982bb3781c81e46fda5":{"model_module":"@jupyter-widgets/base","model_name":"LayoutModel","state":{"_view_name":"LayoutView","grid_template_rows":null,"right":null,"justify_content":null,"_view_module":"@jupyter-widgets/base","overflow":null,"_model_module_version":"1.2.0","_view_count":null,"flex_flow":null,"width":"50%","min_width":null,"border":null,"align_items":null,"bottom":null,"_model_module":"@jupyter-widgets/base","top":null,"grid_column":null,"overflow_y":null,"overflow_x":null,"grid_auto_flow":null,"grid_area":null,"grid_template_columns":null,"flex":null,"_model_name":"LayoutModel","justify_items":null,"grid_row":null,"max_height":null,"align_content":null,"visibility":null,"align_self":null,"height":"25px","min_height":null,"padding":null,"grid_auto_rows":null,"grid_gap":null,"max_width":null,"order":null,"_view_module_version":"1.2.0","grid_template_areas":null,"object_position":null,"object_fit":null,"grid_auto_columns":null,"margin":null,"display":null,"left":null}},"3f7baced569340eb9e13bcce2d6e7834":{"model_module":"@jupyter-widgets/controls","model_name":"FloatProgressModel","state":{"_view_name":"ProgressView","style":"IPY_MODEL_661ffb43a0b24ce99e3a3bd560dc7f3c","_dom_classes":[],"description":"Progress:","_model_name":"FloatProgressModel","bar_style":"info","max":1,"_view_module":"@jupyter-widgets/controls","_model_module_version":"1.5.0","value":0,"_view_count":null,"_view_module_version":"1.5.0","orientation":"horizontal","min":0,"description_tooltip":null,"_model_module":"@jupyter-widgets/controls","layout":"IPY_MODEL_293e6a8149074fa297336901798a3c5a"}},"661ffb43a0b24ce99e3a3bd560dc7f3c":{"model_module":"@jupyter-widgets/controls","model_name":"ProgressStyleModel","state":{"_view_name":"StyleView","_model_name":"ProgressStyleModel","description_width":"","_view_module":"@jupyter-widgets/base","_model_module_version":"1.5.0","_view_count":null,"_view_module_version":"1.2.0","bar_color":null,"_model_module":"@jupyter-widgets/controls"}},"293e6a8149074fa297336901798a3c5a":{"model_module":"@jupyter-widgets/base","model_name":"LayoutModel","state":{"_view_name":"LayoutView","grid_template_rows":null,"right":null,"justify_content":null,"_view_module":"@jupyter-widgets/base","overflow":null,"_model_module_version":"1.2.0","_view_count":null,"flex_flow":null,"width":"50%","min_width":null,"border":null,"align_items":null,"bottom":null,"_model_module":"@jupyter-widgets/base","top":null,"grid_column":null,"overflow_y":null,"overflow_x":null,"grid_auto_flow":null,"grid_area":null,"grid_template_columns":null,"flex":null,"_model_name":"LayoutModel","justify_items":null,"grid_row":null,"max_height":null,"align_content":null,"visibility":null,"align_self":null,"height":"25px","min_height":null,"padding":null,"grid_auto_rows":null,"grid_gap":null,"max_width":null,"order":null,"_view_module_version":"1.2.0","grid_template_areas":null,"object_position":null,"object_fit":null,"grid_auto_columns":null,"margin":null,"display":null,"left":null}},"60381de0cb0f4bcbb03a3f077896433a":{"model_module":"@jupyter-widgets/controls","model_name":"FloatProgressModel","state":{"_view_name":"ProgressView","style":"IPY_MODEL_183ce4cf310845d29208c2565450ead1","_dom_classes":[],"description":"Progress:","_model_name":"FloatProgressModel","bar_style":"info","max":1,"_view_module":"@jupyter-widgets/controls","_model_module_version":"1.5.0","value":0,"_view_count":null,"_view_module_version":"1.5.0","orientation":"horizontal","min":0,"description_tooltip":null,"_model_module":"@jupyter-widgets/controls","layout":"IPY_MODEL_745dc77db2ac4dd6838be8dab0a5f66c"}},"183ce4cf310845d29208c2565450ead1":{"model_module":"@jupyter-widgets/controls","model_name":"ProgressStyleModel","state":{"_view_name":"StyleView","_model_name":"ProgressStyleModel","description_width":"","_view_module":"@jupyter-widgets/base","_model_module_version":"1.5.0","_view_count":null,"_view_module_version":"1.2.0","bar_color":null,"_model_module":"@jupyter-widgets/controls"}},"745dc77db2ac4dd6838be8dab0a5f66c":{"model_module":"@jupyter-widgets/base","model_name":"LayoutModel","state":{"_view_name":"LayoutView","grid_template_rows":null,"right":null,"justify_content":null,"_view_module":"@jupyter-widgets/base","overflow":null,"_model_module_version":"1.2.0","_view_count":null,"flex_flow":null,"width":"50%","min_width":null,"border":null,"align_items":null,"bottom":null,"_model_module":"@jupyter-widgets/base","top":null,"grid_column":null,"overflow_y":null,"overflow_x":null,"grid_auto_flow":null,"grid_area":null,"grid_template_columns":null,"flex":null,"_model_name":"LayoutModel","justify_items":null,"grid_row":null,"max_height":null,"align_content":null,"visibility":null,"align_self":null,"height":"25px","min_height":null,"padding":null,"grid_auto_rows":null,"grid_gap":null,"max_width":null,"order":null,"_view_module_version":"1.2.0","grid_template_areas":null,"object_position":null,"object_fit":null,"grid_auto_columns":null,"margin":null,"display":null,"left":null}}}}},"cells":[{"cell_type":"markdown","metadata":{"id":"qVMCjEV1IEC7","colab_type":"text"},"source":["# Homework: Spark SQL\n","\n","In this homework you will gain a mastery of using Spark SQL. By the end, you'll be a star (not that you aren't already one). Over the next few days you will be using an EMR cluster to use Spark to manipulate the entire `linkedin.json` dataset as well as a new data set, `stock_prices.csv`.\n","\n","The goal of the homework will be to create a training dataset for a Random Forest Machine learning model. The training data set will contain the monthly number of employees hired by companies in `linkedin.json` and their corresponding closing stock prices over a 10 year period (2000-2011). We will try and predict, based on this data, if the company will have a positive or negative growth in stock in the first quarter of the next year. Who's ready to make some money?\n","\n","## The Noteworthy Notes\n","Before we begin here are some important notes to keep in mind,\n","\n","\n","1.   **IMPORTANT!** I said it twice, it's really important. In this homework, we will be using AWS resources. You are given a quota ($150) to use for the entirety of the homework. There is a small chance you will use all this money, however it is important that at the end of every session, you **shut down your EMR cluster**.\n","2.   **You can only use Google Colab for this Homework** since we must connect to the EMR cluster and Jupyter doesn't do that. Using a Google Colab Notebook with an EMR cluster has two important abnormalities:\n","    * The first line of any cell in which you will use the spark session must be `%%spark`. Notice that all cells below have this.\n","    * You will, unfortunately, not be able to stop a cell while it is running. If you wish to do so, you will need to restart your cluster. See the Setup EMR Document for reference.\n","3.   You are **required** to use Spark SQL queries to handle the data in the assignment. Mastering SQL is more beneficial than being able to use Spark commands (functions) as it will show up in more areas of programming and data science/analytics than just Spark. Use the following [function list](https://spark.apache.org/docs/latest/api/sql/index.html#) to see all the SQL functions avaliable in Spark.\n","4. There are portions of this homework that are _very_ challenging. \n","\n","With that said, let's dive in.\n","\n","\n"]},{"cell_type":"markdown","metadata":{"id":"7XEqGpEGBWs5","colab_type":"text"},"source":["## Step 0: Set up EMR\n","\n","Your first task is to create an EMR cluster your AWS Educate Accounts. Please see the [attached document](https://drive.google.com/open?id=1_8NB_3QXfQKm5Vyu7XyxU2mnH5HnGM-F) for detailed instructions. Move on to Step 0.1 after you have completed all the steps in the document."]},{"cell_type":"markdown","metadata":{"id":"5iBPXxgAdXkv","colab_type":"text"},"source":["### Step 0.1: The Superfluous Setup\n","\n","Run the following two cells. These will allow your colab notebook to connect to an use your EMR."]},{"cell_type":"code","metadata":{"id":"pvkEbVaaAQ1e","colab_type":"code","colab":{}},"source":["%%capture\n","!apt update\n","!apt install gcc python-dev libkrb5-dev\n","!pip install sparkmagic\n","!pip install pyspark"],"execution_count":0,"outputs":[]},{"cell_type":"code","metadata":{"id":"6WAJmQ8IAbRs","colab_type":"code","colab":{}},"source":["%%capture\n","%load_ext sparkmagic.magics "],"execution_count":0,"outputs":[]},{"cell_type":"code","metadata":{"id":"IAvEMpzqqHeY","colab_type":"code","colab":{}},"source":["from pyspark.sql.types import *\n","import json\n","import urllib.request\n","\n","from datetime import datetime"],"execution_count":0,"outputs":[]},{"cell_type":"markdown","metadata":{"id":"CL6n768EPt9E","colab_type":"text"},"source":["### Step 0.2: The Succulent Spark\n","\n","Now, connect your notebook to the EMR cluster you created. In the first cell, copy the link to the Master Public DNS specified in the setup document. You will need to add `http://` to the beginning of the address and the port to the end. The final format should be,\n","\n","`http://<your-DNS-link>:8998`\n","\n","For example, if my DNS (directly from the AWS EMR console) is `ec2-3-15-237-211.us-east-2.compute.amazonaws.com` my address would be,\n","\n","`http://ec2-3-15-237-211.us-east-2.compute.amazonaws.com:8998`\n","\n","Insert this in the `# TODO # below`. For our example, the cell would read,\n","\n","\n","\n","```\n","%spark add -s spark_session -l python -u http://ec2-3-15-237-211.us-east-2.compute.amazonaws.com:8998\n","```"]},{"cell_type":"code","metadata":{"id":"R9XWNkCEAeWP","colab_type":"code","outputId":"63ed2476-09a2-4242-9c13-327feedcf230","executionInfo":{"status":"ok","timestamp":1575582618079,"user_tz":300,"elapsed":27719,"user":{"displayName":"Leshang Chen","photoUrl":"","userId":"00573172153387237137"}},"colab":{"base_uri":"https://localhost:8080/","height":92,"referenced_widgets":["0ec3bf246d6241e38d2b68f478bccb36","f848b820820f462f9bd7788a206c6197","c81f5783e51f450f8335631023d58bec"]}},"source":["# TODO: Enter your Master Public DNS with the proper formatting and host\n","\n","%spark add -s spark_session -l python -u http://ec2-34-205-85-50.compute-1.amazonaws.com:8998 "],"execution_count":0,"outputs":[{"output_type":"stream","text":["Starting Spark application\n"],"name":"stdout"},{"output_type":"display_data","data":{"text/html":["<table>\n","<tr><th>ID</th><th>YARN Application ID</th><th>Kind</th><th>State</th><th>Spark UI</th><th>Driver log</th><th>Current session?</th></tr><tr><td>8</td><td>application_1575571283203_0011</td><td>pyspark</td><td>idle</td><td><a target=\"_blank\" href=\"http://ip-172-31-73-160.ec2.internal:20888/proxy/application_1575571283203_0011/\">Link</a></td><td><a target=\"_blank\" href=\"http://ip-172-31-69-18.ec2.internal:8042/node/containerlogs/container_1575571283203_0011_01_000001/livy\">Link</a></td><td>✔</td></tr></table>"],"text/plain":["<IPython.core.display.HTML object>"]},"metadata":{"tags":[]}},{"output_type":"display_data","data":{"application/vnd.jupyter.widget-view+json":{"model_id":"0ec3bf246d6241e38d2b68f478bccb36","version_minor":0,"version_major":2},"text/plain":["FloatProgress(value=0.0, bar_style='info', description='Progress:', layout=Layout(height='25px', width='50%'),…"]},"metadata":{"tags":[]}},{"output_type":"stream","text":["SparkSession available as 'spark'.\n"],"name":"stdout"}]},{"cell_type":"code","metadata":{"id":"DSALdzFWgKuV","colab_type":"code","colab":{}},"source":["# To delte a session. \n","# %spark delete -s spark_session_name"],"execution_count":0,"outputs":[]},{"cell_type":"markdown","metadata":{"id":"Bfs-EQZUzF4j","colab_type":"text"},"source":["_Note_: Since we are using an EMR cluster we will not have access to the several modules that exist for python, e.g. `pandas`, `numpy`, etc. We have written the entire homework such that the solution does not require any of these. Don't try to import them. It won't work."]},{"cell_type":"markdown","metadata":{"id":"Nf_ADEXnIK0b","colab_type":"text"},"source":["## Step 1: Data Cleaning and Shaping\n","\n","The data you will use is stored in an S3 bucket, a cloud storage service. You now need to download it onto the nodes of your [EMR cluster](https://docs.aws.amazon.com/emr/latest/ManagementGuide/emr-what-is-emr.html). \n","\n","### Step 1.1: The Stupendous Schema\n","\n","When loading data, Spark will try to infer it's structure on it's own. This process is faulty because it will sometimes infer the type incorrectly. JSON documents, like the one we will use, can have nested types, such as: arrays, arrays of dictionaries, dictionaries of dictionaries, etc. Spark's ability to determine these nested types is not reliable, thus you will define a schema for `linkedin.json`.\n","\n","A schema is a description of the structure of data. You will be defining an explicit schema for `linkedin.json`. In Spark, schema's are defined using a `StructType` object. This is a collection of data types, termed `StructField`'s, that specify the structure and variable type of each component of the dataset. For example, suppose we have the following simple JSON object,\n","\n","\n","```\n","{\n"," \"student_name\": \"Leonardo Murri\",\n"," \"GPA\": 1.4,\n"," \"courses\": [\n","    {\"department\": \"Computer and Information Science\",\n","     \"course_id\": \"CIS 545\",\n","     \"semester\": \"Fall 2018\"},\n","    {\"department\": \"Computer and Information Science\",\n","     \"course_id\": \"CIS 520\",\n","     \"semester\": \"Fall 2018\"},\n","    {\"department\": \"Electrical and Systems Engineering\",\n","     \"course_id\": \"ESE 650\",\n","     \"semester\": \"Spring 2018\"}\n"," ],\n"," \"grad_year\": 2019\n"," }\n","```\n","\n","We would define it's schema as follows,\n","\n","```       \n","schema = StructType([\n","           StructField(\"student_name\", StringType(), nullable=True),\n","           StructField(\"GPA\", FloatType(), nullable=True),\n","           StructField(\"courses\", ArrayType(\n","                StructType([\n","                  StructField(\"department\", StringType(), nullable=True),\n","                  StructField(\"course_id\", StringType(), nullable=True),\n","                  StructField(\"semester\", StringType(), nullable=True)\n","                ])\n","           ), nullable=True),\n","           StructField(\"grad_year\", IntegerType(), nullable=True)\n","         ])\n","```\n","\n","\n","Each `StructField` has the following structure: `(name, type, nullable)`. The `nullable` flag defines that the specified field may be empty. Your first task is to define the `schema` of `linkedin.json`. A sample JSON object for the dataset can be found [here](http://oneclickpaste.com/194048/).\n","\n","_Note_: In `linkedin.json` the field `specilities` is spelled incorrectly. This is **not** a typo. \n","\n"]},{"cell_type":"code","metadata":{"id":"pL-Ps4KWIJ9e","colab_type":"code","outputId":"452c5671-7852-4905-ee6d-c26f8ad23ea0","executionInfo":{"status":"ok","timestamp":1575583353855,"user_tz":300,"elapsed":402,"user":{"displayName":"Leshang Chen","photoUrl":"","userId":"00573172153387237137"}},"colab":{"base_uri":"https://localhost:8080/","height":17,"referenced_widgets":["8b3b2f26485c413881059c5affd2055e","0f816c10fa484f01b4a14190ade37768","b2af543a4da04a53a7080b43eea45486"]}},"source":["%%spark\n","\n","# TODO: Define [linkedin.json] schema\n","# YOUR CODE HERE\n"],"execution_count":0,"outputs":[{"output_type":"display_data","data":{"application/vnd.jupyter.widget-view+json":{"model_id":"8b3b2f26485c413881059c5affd2055e","version_minor":0,"version_major":2},"text/plain":["FloatProgress(value=0.0, bar_style='info', description='Progress:', layout=Layout(height='25px', width='50%'),…"]},"metadata":{"tags":[]}}]},{"cell_type":"markdown","metadata":{"id":"2Su604X9ggc2","colab_type":"text"},"source":["### Step 1.2: The Laudable Loading\n","\n","Load the `linkedin.json` dataset from your S3 bucket into a Spark dataframe (sdf) called `raw_data_sdf`. If you have constructed `schema` correctly `spark.read.json()` will read in the dataset. ***You do not need to edit this cell***."]},{"cell_type":"code","metadata":{"id":"ji-KW2sAiB6r","colab_type":"code","outputId":"e58668c0-67a8-4e2c-d602-18d70266da27","executionInfo":{"status":"ok","timestamp":1575584115692,"user_tz":300,"elapsed":2733,"user":{"displayName":"Leshang Chen","photoUrl":"","userId":"00573172153387237137"}},"colab":{"base_uri":"https://localhost:8080/","height":17,"referenced_widgets":["10e9bda7da7e48d0845b749ab8e9e9ee","d1a222beb845430abec998d9c6f175ea","3747c90befbe4e7094faa212b6512361"]}},"source":["%%spark\n","# TODO: modify the location to the place where you have the data. \n","raw_data_sdf = spark.read.json(\"s3a://opends4allhw3/linkedin.json\", schema=schema)\n","# raw_data_sdf = spark.read.json('linkedin.json', schema=schema)"],"execution_count":0,"outputs":[{"output_type":"display_data","data":{"application/vnd.jupyter.widget-view+json":{"model_id":"10e9bda7da7e48d0845b749ab8e9e9ee","version_minor":0,"version_major":2},"text/plain":["FloatProgress(value=0.0, bar_style='info', description='Progress:', layout=Layout(height='25px', width='50%'),…"]},"metadata":{"tags":[]}}]},{"cell_type":"markdown","metadata":{"id":"jMVCVotcE1wv","colab_type":"text"},"source":["The cell below shows how to run SQL commands on Spark tables. Use this as a template for all your SQL queries in this notebook. ***You do not need to edit this cell***."]},{"cell_type":"code","metadata":{"id":"NJSVWeGiEO5c","colab_type":"code","outputId":"ec70a7ff-43ae-42e6-93b2-917f2fe11de2","executionInfo":{"status":"ok","timestamp":1575584235649,"user_tz":300,"elapsed":12066,"user":{"displayName":"Leshang Chen","photoUrl":"","userId":"00573172153387237137"}},"colab":{"base_uri":"https://localhost:8080/","height":289,"referenced_widgets":["fc9a5ea9104f4def9166d3a62dff697a","8d520961230c4509820ffc9e698c6cdc","59e9ab8b8888436da6023bff00ce7098"]}},"source":["%%spark\n","\n","# Create SQL-accesible table\n","raw_data_sdf.createOrReplaceTempView(\"raw_data\")\n","\n","# Declare SQL query to be excecuted\n","query = '''SELECT * \n","           FROM raw_data'''\n","\n","# Save the output sdf of spark.sql() as answer_sdf\n","answer_sdf = spark.sql(query)\n","\n","# Display the first 10 rows\n","answer_sdf.show(10)"],"execution_count":0,"outputs":[{"output_type":"display_data","data":{"application/vnd.jupyter.widget-view+json":{"model_id":"fc9a5ea9104f4def9166d3a62dff697a","version_minor":0,"version_major":2},"text/plain":["FloatProgress(value=0.0, bar_style='info', description='Progress:', layout=Layout(height='25px', width='50%'),…"]},"metadata":{"tags":[]}},{"output_type":"stream","text":["+------------------+--------------------+--------------------+--------------------+--------------------+--------------------+--------------------+--------+--------------------+--------------------+--------------------+--------------------+--------------------+--------+--------------------+--------------------+--------------------+--------------------+\n","|               _id|           education|               group|                name|            locality|              skills|            industry|interval|          experience|             summary|           interests|       overview_html|         specilities|homepage|              honors|                 url|           also_view|              events|\n","+------------------+--------------------+--------------------+--------------------+--------------------+--------------------+--------------------+--------+--------------------+--------------------+--------------------+--------------------+--------------------+--------+--------------------+--------------------+--------------------+--------------------+\n","|       in-00000001|                null|                null|[Mazalu MBA, Dr C...|       United States|[Key Account Deve...|     Medical Devices|    null|                null|SALES MANAGEMENT ...|                null|                null|                null|    null|                null|http://www.linked...|[[pub-krisa-drost...|                null|\n","|          in-00001|[[2008, Economics...|[[ASMALLWORLD.net...|     [Forslund, Ann]|Antwerp Area, Bel...|[Molecular Biolog...|     Pharmaceuticals|      20|[[Johnson and Joh...|Ph.D. scientist w...|                null|<dl id=\"overview\"...|Biomarkers in Onc...|    null|                null|http://be.linkedi...|[[pub-peter-king-...|[[Sahlgrenska Uni...|\n","|              null|                null|                null|                null|                null|                null|                null|    null|                null|                null|                null|                null|                null|    null|                null|                null|                null|                null|\n","|  in-000montgomery|                null|[[Big Data, Low L...|   [Kilimann, Edric]|San Francisco Bay...|                null|Information Techn...|       5|[[<Online Recruit...|OBJECTIVE<Primary...|                null|                null|                null|    null|                null|http://www.linked...|[[pub-david-brigh...|[[<Employee Benef...|\n","|in-000vijaychauhan|[[1988,, 1989, Ec...|[[AeSI Alumni Ass...|[Chauhan, PMP, Vi...| Chennai Area, India|[Program Manageme...|Aviation & Aerospace|    null|                null|Experience in Avi...|Literature, Philo...|                null|                null|    null|                null|http://in.linkedi...|[[in-sandeepraghu...|                null|\n","|  in-001adambutler|[[1991, Product D...|                null|     [Adam, Butler,]|Brighton, United ...|[Digital Strategy...|Marketing and Adv...|      16|[[Brand New Music...|Integrating creat...|travelling,the se...|                null|A passion for Bra...|    null|                null|http://uk.linkedi...|[[in-paulbeier, h...|[[Tigerprint, WHS...|\n","|      in-001monica|[[2000, Economics...|[[Canadian Market...|    [Andrus, Monica]|Toronto, Canada Area|                null|Nonprofit Organiz...|      20|[[Canadian MedicA...|                null|                null|                null|                null|    null|                null|http://ca.linkedi...|                  []|[[CMAF, CMAF, Bus...|\n","| in-001neilpeacock|                null|                null|     [Peacock, Neil]|      United Kingdom|[DLP, Managed Ser...|Computer & Networ...|      31|[[Complete IT Sys...|Currently Trainin...|                null|                null|                null|    null|                null|http://uk.linkedi...|[[pub-dilan-hindo...|[[Dawson Rentals ...|\n","|          in-00666|                null|                null|[BOLUKBAS, PMP, M...|              Turkey|[Project Manageme...|    Telekomünikasyon|    null|                null|                null|                null|                null|                null|    null|                null|http://tr.linkedi...|[[pub-dilek-karap...|                null|\n","|       in-00789123|[[2009, Business ...|[[CFA Institute C...| [Hoffmann, Lynette]|        South Africa|[Predictive Analy...|  Telecommunications|      12|[[Ericsson Region...|A Marketing profe...|Marketing and sta...|                null|                null|    null|[Recognition Awar...|http://za.linkedi...|[[pub-mashudu-tho...|[[Eskom, ESKOM Di...|\n","+------------------+--------------------+--------------------+--------------------+--------------------+--------------------+--------------------+--------+--------------------+--------------------+--------------------+--------------------+--------------------+--------+--------------------+--------------------+--------------------+--------------------+\n","only showing top 10 rows"],"name":"stdout"}]},{"cell_type":"markdown","metadata":{"id":"svOO4iLPist4","colab_type":"text"},"source":["### Step 1.3: The Extravagent Extraction\n","\n","In our training model, we are interested in when individuals began working at a company.  From creating the schema, you should notice that the collection of companies inviduals worked at are contained in the `experience` field as an array of dictionaries. You should use the `org` for the company name and `start` for the start date. Here is an example of an `experience` field,\n","\n","```\n","{\n","   \"experience\": [\n","     {\n","        \"org\": \"The Walt Disney Company\", \n","        \"title\" : \"Mickey Mouse\",\n","        \"end\" : \"Present\",\n","        \"start\": \"November 1928\",\n","        \"desc\": \"Sailed a boat.\"\n","     },\n","     {\n","        \"org\": \"Walt Disney World Resort\",\n","        \"title\": \"Mickey Mouse Mascot\",\n","        \"start\": \"January 2005\",\n","        \"desc\": \"Took pictures with kids.\"\n","     }\n","   ]\n","}\n","```\n","\n","Your task is to extract each pair of company and start date from these arrays. In Spark, this is known as \"exploding\" a row. An explode will seperate the elements of an array into multiple rows.\n","\n","Create an sdf called `raw_start_dates_sdf` that contains the company and start date for every experience of every individual in `raw_data_sdf`. Drop any row that contains a `null` in either column with `dropna()`. You can sort the elements however you wish (you don't need to if you don't want to). The sdf should look as follows:\n","\n","```\n","+--------------------------+---------------+\n","|org                       |start_date     |\n","+--------------------------+---------------+\n","|Walt Disney World Resort  |January 2005   | \n","|The Walt Disney Company   |November 1928  |\n","|...                       |...            |\n","+--------------------------+---------------+\n","```\n","\n","_Hint_: You may want to do two seperate explodes for `org` and `start`. In an explode, the position of the element in the array can be extracted as well, and used to merge two seperate explodes. Reference the [function list](https://spark.apache.org/docs/2.3.0/api/sql/index.html).\n","\n","_Note_: Some of the entires in `org` are \"weird\", i.e. made up of non-english letters and characters. Keep them. **DO NOT** edit any name in the original dataframe unless we specify. **DO NOT** drop any row unless there is a `null` value as stated before. This goes for the rest of the homework as well, unless otherwise specified."]},{"cell_type":"code","metadata":{"id":"Kt16tyP0klQX","colab_type":"code","outputId":"4bb1d270-0215-44b6-d3b0-4cfcf2a3027b","executionInfo":{"status":"ok","timestamp":1575584587756,"user_tz":300,"elapsed":36859,"user":{"displayName":"Leshang Chen","photoUrl":"","userId":"00573172153387237137"}},"colab":{"base_uri":"https://localhost:8080/","height":17,"referenced_widgets":["cfecaa14eb514313a5f15ed03ef6789d","5bea45fa7fc54ebebad0a57be6310c65","472c58675e1e4d2eab714bd7ce9f0df3"]}},"source":["%%spark\n","\n","# TODO: Create [raw_start_dates_sdf]\n","\n","# YOUR CODE HERE\n"],"execution_count":0,"outputs":[{"output_type":"display_data","data":{"application/vnd.jupyter.widget-view+json":{"model_id":"cfecaa14eb514313a5f15ed03ef6789d","version_minor":0,"version_major":2},"text/plain":["FloatProgress(value=0.0, bar_style='info', description='Progress:', layout=Layout(height='25px', width='50%'),…"]},"metadata":{"tags":[]}}]},{"cell_type":"code","metadata":{"id":"-a3xLDEYCcwl","colab_type":"code","outputId":"6735732d-912e-4823-c8a2-59daf4f41d58","executionInfo":{"status":"ok","timestamp":1575584659737,"user_tz":300,"elapsed":108829,"user":{"displayName":"Leshang Chen","photoUrl":"","userId":"00573172153387237137"}},"colab":{"base_uri":"https://localhost:8080/","height":168,"referenced_widgets":["dc7c0513d85b4643bdbfc7bf8702e856","2056868703734a26b1db0e3ffec7699d","66aaa398ce5e401b9e1a1d87988e5487"]}},"source":["%%spark\n","raw_start_dates_sdf.show(4)"],"execution_count":0,"outputs":[{"output_type":"display_data","data":{"application/vnd.jupyter.widget-view+json":{"model_id":"dc7c0513d85b4643bdbfc7bf8702e856","version_minor":0,"version_major":2},"text/plain":["FloatProgress(value=0.0, bar_style='info', description='Progress:', layout=Layout(height='25px', width='50%'),…"]},"metadata":{"tags":[]}},{"output_type":"stream","text":["+--------------------+--------------+\n","|                 org|    start_date|\n","+--------------------+--------------+\n","|             Spot.us|September 2009|\n","|Pfizer Ltd. (WPO ...|          2005|\n","|              ALSTOM|          2008|\n","|DSM i-Nutrition B...|  January 2009|\n","+--------------------+--------------+\n","only showing top 4 rows"],"name":"stdout"}]},{"cell_type":"markdown","metadata":{"id":"zSbb0t-d-VFt","colab_type":"text"},"source":["### Step 1.4: The Fortuitous Formatting\n","\n","There are two issues with the values in our `date` column. First, the values are saved as strings, not datetime types. This halts us from running functions such as `ORDER BY` or `GROUP BY` on common months or years. Second, some values do not have both month and year information or are in other languages. Your task is to filter out and clean the `date` column. We are interested in only those rows that have date in the following format \"(month_name) (year)\", e.g. \"October 2010\".\n","\n","Create an sdf called `filtered_start_dates_sdf` from `raw_start_dates_sdf` with the `date` column filtered in the manner above. Keep only those rows with a start date between January 2000 to December 2011, inclusive. Ensure that any dates that are not in our desired format are ommitted. Drop any row that contains a `null` in either column. The format of the sdf is shown below:\n","```\n","+--------------------------+---------------+\n","|org                       |start_date     |\n","+--------------------------+---------------+\n","|Walt Disney World Resort  |2005-01-01     | \n","|...                       |...            |\n","+--------------------------+---------------+\n","```\n","_Hint_: Refer to the [function list](https://spark.apache.org/docs/2.3.0/api/sql/index.html) to format the `date` column. In Spark SQL the date format we are interested in is `\"MMM y\"`.\n","\n","_Note_: Spark will return the date in the format above, with the day as `01`. This is ok, since we are interested in the month and year each individual began working and all dates will have `01` as their day."]},{"cell_type":"code","metadata":{"id":"eelgTtOc_MBM","colab_type":"code","outputId":"49854f5c-074a-4611-fc23-0acb2df64963","executionInfo":{"status":"ok","timestamp":1575584734608,"user_tz":300,"elapsed":183678,"user":{"displayName":"Leshang Chen","photoUrl":"","userId":"00573172153387237137"}},"colab":{"base_uri":"https://localhost:8080/","height":17,"referenced_widgets":["52815494440f4161b01b6eebb60b999d","91880f7960e5464da7893d80df72d483","8ece5c44cc2c4a25b4fcc641882667a4"]}},"source":["%%spark\n","\n","# TODO: Create [filtered_start_dates_sdf]\n"],"execution_count":0,"outputs":[{"output_type":"display_data","data":{"application/vnd.jupyter.widget-view+json":{"model_id":"52815494440f4161b01b6eebb60b999d","version_minor":0,"version_major":2},"text/plain":["FloatProgress(value=0.0, bar_style='info', description='Progress:', layout=Layout(height='25px', width='50%'),…"]},"metadata":{"tags":[]}}]},{"cell_type":"markdown","metadata":{"id":"LXYYQn2_GYwZ","colab_type":"text"},"source":["### Step 1.5 The Gregarious Grouping\n","\n","We now want to collect the number of individuals that started in the same month and year for each company. Create an sdf called `start_dates_sdf` that has the total number of employees who began working at the same company on the same start date. The format of the sdf is shown below:\n","\n","```\n","+--------------------------+---------------+---------------+\n","|org                       |start_date     |num_employees  |\n","+--------------------------+---------------+---------------+\n","|Walt Disney World Resort  |2005-01-01     |1              |\n","|...                       |...            |...            |\n","+--------------------------+---------------+---------------+\n","```"]},{"cell_type":"code","metadata":{"id":"CxVIyc1CHooV","colab_type":"code","outputId":"0a2c79d4-0749-403a-e007-0173cffe006e","executionInfo":{"status":"ok","timestamp":1575584854127,"user_tz":300,"elapsed":303177,"user":{"displayName":"Leshang Chen","photoUrl":"","userId":"00573172153387237137"}},"colab":{"base_uri":"https://localhost:8080/","height":17,"referenced_widgets":["f52ae0051fc24086995d05461b02229d","defbbe69eeb44fcbba983d981a4521fe","5647e8443a5a4d7a9d775c8a2bea87c6"]}},"source":["%%spark\n","\n","# TODO: Create [start_dates_sdf]\n","# YOUR CODE HERE\n"],"execution_count":0,"outputs":[{"output_type":"display_data","data":{"application/vnd.jupyter.widget-view+json":{"model_id":"f52ae0051fc24086995d05461b02229d","version_minor":0,"version_major":2},"text/plain":["FloatProgress(value=0.0, bar_style='info', description='Progress:', layout=Layout(height='25px', width='50%'),…"]},"metadata":{"tags":[]}}]},{"cell_type":"markdown","metadata":{"id":"QYScM3FwJnUz","colab_type":"text"},"source":["## Step 2: Hiring Trends Analysis\n","\n","Now we will analyze `start_dates_sdf` to find monthly and annual hiring trends.\n","\n","### Step 2.1: The Marvelous Months\n","\n","Your task is to answer the question: \"On average, what month do most employees start working?\" Create an sdf called `monthly_hires_sdf` which contains the total number of employees that started working on a specific month, at any company and on any year. The `month` column should be of type `int`, i.e. 1-12. The format of the sdf is shown below:\n","\n","```\n","+---------------+---------------+\n","|month          |num_employees  |\n","+---------------+---------------+\n","|1              |...            |\n","|2              |...            |\n","|3              |...            |\n","|...            |...            |\n","+---------------+---------------+\n","```\n","\n","Find the month in which the most employees start working and save it's number as an integer to the variable `most_common_month`.\n","\n","_Hint_: Be careful. The starts dates we have right now have both month and year. We only want the common months. See if you can find something in the [function list](https://spark.apache.org/docs/2.3.0/api/sql/index.html) that will help you do this."]},{"cell_type":"code","metadata":{"id":"vLTmvD9WNQH3","colab_type":"code","outputId":"4463575e-90d3-4050-ef97-b8d1155f5a4d","executionInfo":{"status":"ok","timestamp":1575584971691,"user_tz":300,"elapsed":420722,"user":{"displayName":"Leshang Chen","photoUrl":"","userId":"00573172153387237137"}},"colab":{"base_uri":"https://localhost:8080/","height":17,"referenced_widgets":["00c647594bdf4e86aebd273fffcbcc85","8de371ef2a1749558e1a7fe53363d060","3e73d2733d814879bfb767f462570743"]}},"source":["%%spark\n","\n","# TODO: Create [monthly_hire_sdf] and find the most common month people were\n","# hired. Save its number as an integer to [most_common_month]\n"],"execution_count":0,"outputs":[{"output_type":"display_data","data":{"application/vnd.jupyter.widget-view+json":{"model_id":"00c647594bdf4e86aebd273fffcbcc85","version_minor":0,"version_major":2},"text/plain":["FloatProgress(value=0.0, bar_style='info', description='Progress:', layout=Layout(height='25px', width='50%'),…"]},"metadata":{"tags":[]}}]},{"cell_type":"markdown","metadata":{"id":"CUtVHRcMUoWp","colab_type":"text"},"source":["### Step 2.2: The Preposterous Percentages\n","\n","The next question we will answer is \"What is the percentage change in hires between 2010 and 2011 for each company?\" Create an sdf called `percentage_change_sdf` that has the percentage change between 2010 and 2011 for each company. The sdf should look as follows:\n","\n","```\n","+---------------------------+--------------------+\n","|org                        |percentage_change   |\n","+---------------------------+--------------------+\n","|Walt Disney World Resort   |12.3                |\n","|...                        |...                 |\n","+---------------------------+--------------------+\n","```\n","\n","_Note_: A percentage change can be positive or negative depending \n","on the difference between the two years.The formula for percent change is given below,\n","\n","$$\\text{% change} = \\frac{P_f-P_i}{P_f} \\times 100$$\n","\n","Here, $P_f$ is the final element (in this case the number of hires in 2011) and $P_i$ is initial element (the number of hires in 2010).\n","\n","_Hint_: This is a **difficult** question. We recommend using a combination of `GROUP BY` and `JOIN`. Keep in mind that operations between columns in SQL dataframes are often easier than those between rows. "]},{"cell_type":"code","metadata":{"id":"_AhhfLXpWq7y","colab_type":"code","outputId":"e2e04149-cade-4ffc-d4bb-4e655228b109","executionInfo":{"status":"ok","timestamp":1575585089273,"user_tz":300,"elapsed":538283,"user":{"displayName":"Leshang Chen","photoUrl":"","userId":"00573172153387237137"}},"colab":{"base_uri":"https://localhost:8080/","height":17,"referenced_widgets":["6bd5845756c84761a22c4fca0c27e091","18c3f006e61b466abea135b15d4fe624","637bd902d20544569334d56244bca5d3"]}},"source":["%%spark\n","\n","# TODO: Create [percentage_change_sdf]\n","# YOUR CODE HERE\n"],"execution_count":0,"outputs":[{"output_type":"display_data","data":{"application/vnd.jupyter.widget-view+json":{"model_id":"6bd5845756c84761a22c4fca0c27e091","version_minor":0,"version_major":2},"text/plain":["FloatProgress(value=0.0, bar_style='info', description='Progress:', layout=Layout(height='25px', width='50%'),…"]},"metadata":{"tags":[]}}]},{"cell_type":"markdown","metadata":{"id":"QkF2RfLSXO0u","colab_type":"text"},"source":["## Step 3: Formatting the Training Data\n","\n","\n","Our overaching goal is to train a machine learning (ML) model that will use the monthly hiring trends of a company to predict a positive or negative gain in the company's stock in the first quarter of the following year. A ML model is trained on a set of observations. Each observation contains a set of features, `X`, and a label, `y`. The goal of the ML model is to create a function that takes any `X` as an input and outputs a predicted `y`. \n","\n","The machine learning model we will use is a [Random Forest Classifier](https://builtin.com/data-science/random-forest-algorithm). Each observation we will pass in will have 24 features (columns). These are the number of people hired from Jan to Dec and the company stock price on the last day of each month. The label will be the direction of the company's stock percentage change (positive, `1`, or negative, `-1`) in the first quarter of the following year. Each observation will correspond to a specified company's trends on a specified year. The format of our final training sdf is shown below. The first 26 columns define our observations, `X`, and the last column the label, `y`.\n","```\n","+----+-----+----------+---------+----------+----------+---------+----------+-------------+\n","|org |year |jan_hired |   ...   |dec_hired |jan_stock |   ...   |dec_stock |stock_result |\n","+----+-----+----------+---------+----------+----------+---------+----------+-------------+\n","|IBM |2008 |...       |   ...   |...       |...       |   ...   |...       |1            |\n","|IBM |2009 |...       |   ...   |...       |...       |   ...   |...       |-1           |\n","|... |...  |...       |   ...   |...       |...       |   ...   |...       |...          |\n","+----+-----+----------+---------+----------+----------+---------+----------+-------------+\n","```\n","\n","_Note_: We will use the first three letters of each month in naming, i.e. `jan, feb, mar, apr, may, jun, jul, aug, sep, oct, nov, dec`\n","\n","\n","\n","### Step 3.1: The Harmonious Hires\n","\n","Your first task is to create the first half of the training table, i.e. the `jan_hired` through `dec_hired` columns. This will involve reshaping `start_dates_sdf`. Currently, `start_dates_sdf` has columns `org`, `start_date`, and `num_employees`. We want to group the rows together based on common `org` and years and create new columns for the number of employees that started working in each month of that year.\n","\n","Create an sdf called `raw_hirings_for_training_sdf` that has for a single company and a single year, the number of hires in Jan through Dec, and the total number of hires that year. Note that for each company you will have several rows corresponding to years between 2000 and 2011. It is ok if for a given company you don't have a given year. However, ensure that for a given company and given year, each month column has an entry, i.e. if no one was hired the value should be `0`. The format of the sdf is shown below: \n","```\n","+----+-----+----------+---------+----------+----------+\n","|org |year |jan_hired |   ...   |dec_hired |total_num |\n","+----+-----+----------+---------+----------+----------+\n","|IBM |2008 |...       |   ...   |...       |...       |\n","|IBM |2009 |...       |   ...   |...       |...       |\n","|... |...  |...       |   ...   |...       |...       |\n","+----+-----+----------+---------+----------+----------+\n","```\n","_Hint_: This is a **difficult** question. The tricky part is creating the additional columns of monthly hires, specifically when there are missing dates. In our dataset, if a company did not hire anybody in a given date, it will not appear in `start_dates_sdf`. We suggest you look into `CASE` and `WHEN` statements in the [function list](https://spark.apache.org/docs/2.3.0/api/sql/index.html)."]},{"cell_type":"code","metadata":{"id":"btp2wboHqg2J","colab_type":"code","outputId":"f3561d40-c00d-497f-c487-3c58747b08dc","executionInfo":{"status":"ok","timestamp":1575585305365,"user_tz":300,"elapsed":754324,"user":{"displayName":"Leshang Chen","photoUrl":"","userId":"00573172153387237137"}},"colab":{"base_uri":"https://localhost:8080/","height":17,"referenced_widgets":["277806ae13be4bd2a7e17019f477a79e","0f19d766cf624efc9a417c72f607368e","cc5799297d594e738a34ed4b83a16fec"]}},"source":["%%spark\n","\n","# TODO: Create [raw_hire_train_sdf]\n","# YOUR CODE HERE\n"],"execution_count":0,"outputs":[{"output_type":"display_data","data":{"application/vnd.jupyter.widget-view+json":{"model_id":"277806ae13be4bd2a7e17019f477a79e","version_minor":0,"version_major":2},"text/plain":["FloatProgress(value=0.0, bar_style='info', description='Progress:', layout=Layout(height='25px', width='50%'),…"]},"metadata":{"tags":[]}}]},{"cell_type":"markdown","metadata":{"id":"IkXyet6rrczK","colab_type":"text"},"source":["### Step 3.2: The Formidable Filters\n","\n","Create an sdf called `hire_train_sdf` that contains all the observations in `raw_hire_train_sdf` with `total_num` greater than or equal to 10. The format of the sdf is shown below:\n","\n","```\n","+----+-----+----------+---------+----------+----------+\n","|org |year |jan_hired |   ...   |dec_hired |total_num |\n","+----+-----+----------+---------+----------+----------+\n","|IBM |2008 |...       |   ...   |...       |...       |\n","|IBM |2009 |...       |   ...   |...       |...       |\n","|... |...  |...       |   ...   |...       |...       |\n","+----+-----+----------+---------+----------+----------+\n","```\n"]},{"cell_type":"code","metadata":{"id":"dCH4mbNcshq9","colab_type":"code","outputId":"2a546db3-3a2e-4e16-e598-3cfd828c9e2f","executionInfo":{"status":"ok","timestamp":1575585416723,"user_tz":300,"elapsed":865658,"user":{"displayName":"Leshang Chen","photoUrl":"","userId":"00573172153387237137"}},"colab":{"base_uri":"https://localhost:8080/","height":17,"referenced_widgets":["0b0cf2113c5d46079b10ee9291d81ca2","e904164016e649b39ae675c5316f3718","b2deaafff8174b3cab34294d58e1e27e"]}},"source":["%%spark\n","\n","# TODO: Create [hire_train_sdf]\n","# YOUR CODE HERE\n"],"execution_count":0,"outputs":[{"output_type":"display_data","data":{"application/vnd.jupyter.widget-view+json":{"model_id":"0b0cf2113c5d46079b10ee9291d81ca2","version_minor":0,"version_major":2},"text/plain":["FloatProgress(value=0.0, bar_style='info', description='Progress:', layout=Layout(height='25px', width='50%'),…"]},"metadata":{"tags":[]}}]},{"cell_type":"markdown","metadata":{"id":"MN4ik70Hta01","colab_type":"text"},"source":["### Step 3.3: The Stupendous Stocks\n","\n","Now we are ready for the stock data. The stock data we will use is saved in the same S3 bucket as `linkedin.json`. Load the data into the EMR cluster. Run the cell below. ***You do not need to edit this cell, except changing dataset url***."]},{"cell_type":"code","metadata":{"id":"APv4BxKw643q","colab_type":"code","outputId":"1de29e91-11be-40ca-9c02-5491aee6ff10","executionInfo":{"status":"ok","timestamp":1575585524960,"user_tz":300,"elapsed":973879,"user":{"displayName":"Leshang Chen","photoUrl":"","userId":"00573172153387237137"}},"colab":{"base_uri":"https://localhost:8080/","height":269,"referenced_widgets":["25c499f20e694a60bd3fc36ab3287402","a27a5af9ab1e47449f2cb01f298ab010","e2efaeed796f491dac1e80de28d71575"]}},"source":["%%spark\n","# TODO: modify the location to the place that you have the data. \n","# Load stock data\n","raw_stocks_sdf = spark.read.format(\"csv\") \\\n","              .option(\"header\", \"true\") \\\n","              .load(\"s3a://xxx/stock_prices.csv\")\n","\n","# Creates SQL-accesible table\n","raw_stocks_sdf.createOrReplaceTempView('raw_stocks')\n","\n","# Display the first 10 rows\n","query = '''SELECT *\n","           FROM raw_stocks'''\n","spark.sql(query).show(10)\n"],"execution_count":0,"outputs":[{"output_type":"display_data","data":{"application/vnd.jupyter.widget-view+json":{"model_id":"25c499f20e694a60bd3fc36ab3287402","version_minor":0,"version_major":2},"text/plain":["FloatProgress(value=0.0, bar_style='info', description='Progress:', layout=Layout(height='25px', width='50%'),…"]},"metadata":{"tags":[]}},{"output_type":"stream","text":["+------+-----------------+----------+\n","|ticker|    closing_price|      date|\n","+------+-----------------+----------+\n","|   AHH| 8.49315452575684|2013-05-08|\n","|   AHH| 8.47115135192871|2013-05-09|\n","|   AHH| 8.50782203674316|2013-05-10|\n","|   AHH| 8.54449367523193|2013-05-13|\n","|   AHH|8.456483840942381|2013-05-14|\n","|   AHH| 8.50782203674316|2013-05-15|\n","|   AHH| 8.61050128936768|2013-05-16|\n","|   AHH|8.625171661376951|2013-05-17|\n","|   AHH| 8.60316944122314|2013-05-20|\n","|   AHH|8.676511764526369|2013-05-21|\n","+------+-----------------+----------+\n","only showing top 10 rows"],"name":"stdout"}]},{"cell_type":"markdown","metadata":{"id":"JUCdr3zDUAFH","colab_type":"text"},"source":["Run the cell below to see the types of the columns in our data frame. These are not correct. We could have defined a schema when reading in data but we will handle this issue in another manner. You will do this in Step 3.4.2."]},{"cell_type":"code","metadata":{"id":"oNTGEfxsisqs","colab_type":"code","outputId":"c62fe3de-796e-4c7a-d030-fe67f6710e81","executionInfo":{"status":"ok","timestamp":1575585524961,"user_tz":300,"elapsed":973871,"user":{"displayName":"Leshang Chen","photoUrl":"","userId":"00573172153387237137"}},"colab":{"base_uri":"https://localhost:8080/","height":34,"referenced_widgets":["664d2f8b41e8447ba797bbd586cf7442","14f008c6d70a41de96a66a9255d28c91","cac27272dfa64b55b8ac984c8f2e83e7"]}},"source":["%%spark \n","\n","# Print types of SDF\n","raw_stocks_sdf.dtypes"],"execution_count":0,"outputs":[{"output_type":"display_data","data":{"application/vnd.jupyter.widget-view+json":{"model_id":"664d2f8b41e8447ba797bbd586cf7442","version_minor":0,"version_major":2},"text/plain":["FloatProgress(value=0.0, bar_style='info', description='Progress:', layout=Layout(height='25px', width='50%'),…"]},"metadata":{"tags":[]}},{"output_type":"stream","text":["[('ticker', 'string'), ('closing_price', 'string'), ('date', 'string')]"],"name":"stdout"}]},{"cell_type":"markdown","metadata":{"id":"0DdnoFkP7mxz","colab_type":"text"},"source":["### Step 3.4 The Clairvoyant Cleaning\n","\n","We now want to format the stock data set into the second half of the training table. We will then merge it with `hire_train` based off the common `org` and `year` fields. The formatting will consist of 4 steps. Actually, it is 5.\n","\n","#### Step 3.4.1 The Ubiquitous UDF\n","\n","The companies in our stock dataset are defined by their stock tickers. Thus, we would not be able to merge it with the `org` field in `hire_train_sdf`. We must convert them to that format. Often times when using Spark, there may not be a built-in SQL function that can do the operation we desired. Instead, we can create one on our own with a user-defined function (udf).\n","\n","A udf is defined as a normal Python function and then registered to be used as a Spark SQL function. Your task is to create a udf, `TICKER_TO_NAME()` that will convert the ticker field in `raw_stocks` to the company's name. This will be done using the provided `ticker_to_name_dict` dictionary. We are only interested in the companies in that dictionary.\n","\n","Fill out the function `ticker_to_name()` below. Then use `spark.udf.register()` to register it as a SQL function. The command is provided. ***You do not need to edit it***. Note, we have defined the udf as returning `StringType()`. Ensure that your function returns this. You must also deal with any potential `null` cases."]},{"cell_type":"code","metadata":{"id":"P4cJWZsr8iNC","colab_type":"code","outputId":"101ca7f2-f6a8-443b-e138-365d49241567","executionInfo":{"status":"ok","timestamp":1575585525098,"user_tz":300,"elapsed":974000,"user":{"displayName":"Leshang Chen","photoUrl":"","userId":"00573172153387237137"}},"colab":{"base_uri":"https://localhost:8080/","height":34,"referenced_widgets":["90d37ecc6c6e42d8a7867fcce3980831","5b6f6a0b7f8a45949f82869fcae374cb","3e9b9186606b4aa28ddeee3a5112ab20"]}},"source":["%%spark\n","\n","# TODO: Fill out [ticker_to_name()] and register it as a udf.\n","# YOUR CODE HERE\n","\n","def ticker_to_name(ticker):\n","\n","# Register udf as a SQL function. DO NOT EDIT\n","spark.udf.register(\"TICKER_TO_NAME\", ticker_to_name, StringType())\n"],"execution_count":0,"outputs":[{"output_type":"display_data","data":{"application/vnd.jupyter.widget-view+json":{"model_id":"90d37ecc6c6e42d8a7867fcce3980831","version_minor":0,"version_major":2},"text/plain":["FloatProgress(value=0.0, bar_style='info', description='Progress:', layout=Layout(height='25px', width='50%'),…"]},"metadata":{"tags":[]}},{"output_type":"stream","text":["<function ticker_to_name at 0x7f34267198c8>"],"name":"stdout"}]},{"cell_type":"markdown","metadata":{"id":"u9YOYO9L-_GS","colab_type":"text"},"source":["#### Step 3.4.2: The Fastidious Filters\n","\n","With our new `TICKER_TO_NAME()` function we will begin to wrangle `raw_stocks_sdf`.\n","\n","Create an sdf called `filter_1_stocks_sdf` as follows. Convert all the ticker names in `raw_stocks_sdf` to the company names and save it as `org`. Next, convert the `date` field to a datetime type. As explained before this will help order and group the rows in future steps. Then, convert the type of the values in `closing_price` to `float`. This will take care of the `dtypes` issue we saw in Step 3.3.\n","\n","Drop any company names that do not appear in `ticker_to_name_dict`. Keep any date between January 1st 2001 and December 4th 2012 inclusive, in the format shown below (note this is a datetime object not a string):\n","\n","```\n","+----+------------+--------------+\n","|org |date        |closing_price |\n","+----+------------+--------------+\n","|IBM |2000-01-03  |...           |\n","|... |...         |...           |\n","+----+------------+--------------+\n","```\n","_Hint_: You will use a similar function to filter the dates as in Step 1.4. In Spark SQL the format for the `date` field in `raw_stocks_sdf` is `\"yyyy-MM-dd\"`."]},{"cell_type":"code","metadata":{"id":"RuiitnWlBYJ7","colab_type":"code","outputId":"dc6a7b77-34e5-4634-84b8-ca9114fa4c2f","executionInfo":{"status":"ok","timestamp":1575585525577,"user_tz":300,"elapsed":974459,"user":{"displayName":"Leshang Chen","photoUrl":"","userId":"00573172153387237137"}},"colab":{"base_uri":"https://localhost:8080/","height":17,"referenced_widgets":["9b53a3b6b43e46b48004eab3188f52cc","375176dbd9eb42a29fd34152142f70b3","72ed66a0fe4d476ab6c55a255ae0cad0"]}},"source":["%%spark\n","\n","# TODO: Create [filter_1_stocks_sdf]\n","# YOUR CODE HERE\n"],"execution_count":0,"outputs":[{"output_type":"display_data","data":{"application/vnd.jupyter.widget-view+json":{"model_id":"9b53a3b6b43e46b48004eab3188f52cc","version_minor":0,"version_major":2},"text/plain":["FloatProgress(value=0.0, bar_style='info', description='Progress:', layout=Layout(height='25px', width='50%'),…"]},"metadata":{"tags":[]}}]},{"cell_type":"markdown","metadata":{"id":"Ne5NaT-6CLns","colab_type":"text"},"source":["#### Step 3.4.3: The Magnanimous Months\n","\n","The data in `filter_1_stocks_sdf` gives closing prices on a daily basis. Since we are interested in monthly trends, we will only keep the closing price on the **last trading day of each month**.\n","\n","Create an sdf `filter_2_stocks_sdf` that contains only the closing prices for the last trading day of each month. Note that a trading day is not simply the last day of each month, as this could be on a weekend when the market is closed . The format of the sdf is shown below:\n","\n","```\n","+----+------------+--------------+\n","|org |date        |closing_price |\n","+----+------------+--------------+\n","|IBM |2000-01-31  |...           |\n","|... |...         |...           |\n","+----+------------+--------------+\n","```\n","\n","  _Hint_: This is a **difficult** question. But if you made it this far, you're a star by now. It may be helpful to create an intermediate dataframe that will help you filter out the specific dates you desire."]},{"cell_type":"code","metadata":{"id":"AIx5LUuDD4q_","colab_type":"code","outputId":"3c69ac55-3b2e-4724-b5b8-783e0a4e737c","executionInfo":{"status":"ok","timestamp":1575585560088,"user_tz":300,"elapsed":1008953,"user":{"displayName":"Leshang Chen","photoUrl":"","userId":"00573172153387237137"}},"colab":{"base_uri":"https://localhost:8080/","height":17,"referenced_widgets":["06d34ff02a2f40acb420053db30d1ce6","8f3ed02d295449deb1a410e319457366","da5370dcfa9e484ebec2232b753f9126"]}},"source":["%%spark\n","\n","# TODO: Create [filter_2_stocks_sdf]\n","# YOUR CODE HERE\n"],"execution_count":0,"outputs":[{"output_type":"display_data","data":{"application/vnd.jupyter.widget-view+json":{"model_id":"06d34ff02a2f40acb420053db30d1ce6","version_minor":0,"version_major":2},"text/plain":["FloatProgress(value=0.0, bar_style='info', description='Progress:', layout=Layout(height='25px', width='50%'),…"]},"metadata":{"tags":[]}}]},{"cell_type":"markdown","metadata":{"id":"AG4bACKKEQNl","colab_type":"text"},"source":["#### Step 3.4.4: The Rambunctious Reshape\n","\n","Now, we will begin to shape our dataframe into the format of the final training sdf.\n","\n","Create an sdf `filter_3_stocks_sdf` that has for a single company and a single year, the closing stock price for the last trading day of each month in that year. This is similar to the table you created in Step 3.1. In this case since we cannot make a proxy for the closing price if the data is not avaliable, drop any rows containing any `null` values, in any column. The format of the sdf is shown below:\n","\n","```\n","+----+-----+----------+---------+----------+\n","|org |year |jan_stock |   ...   |dec_stock |\n","+----+-----+----------+---------+----------+\n","|IBM |2008 |...       |   ...   |...       |\n","|IBM |2009 |...       |   ...   |...       |\n","|... |...  |...       |   ...   |...       |\n","+----+-----+----------+---------+----------+\n","```\n"]},{"cell_type":"code","metadata":{"id":"AucLEgvwIr_0","colab_type":"code","outputId":"d40ca836-d8d6-4b6e-d6e6-e2a6eb604cd9","executionInfo":{"status":"ok","timestamp":1575585622883,"user_tz":300,"elapsed":1071728,"user":{"displayName":"Leshang Chen","photoUrl":"","userId":"00573172153387237137"}},"colab":{"base_uri":"https://localhost:8080/","height":17,"referenced_widgets":["09bfe3c82da54132bc0b1de3698f5041","bdf094cb30f741778998082741862013","c6e2b881ee2e4982bb3781c81e46fda5"]}},"source":["%%spark\n","\n","# TODO: Create [filter_3_stocks_sdf]\n","# YOUR CODE HERE\n","\n"],"execution_count":0,"outputs":[{"output_type":"display_data","data":{"application/vnd.jupyter.widget-view+json":{"model_id":"09bfe3c82da54132bc0b1de3698f5041","version_minor":0,"version_major":2},"text/plain":["FloatProgress(value=0.0, bar_style='info', description='Progress:', layout=Layout(height='25px', width='50%'),…"]},"metadata":{"tags":[]}}]},{"cell_type":"markdown","metadata":{"id":"82OQKp-nIulq","colab_type":"text"},"source":["#### Step 3.4.5: The Decisive Direction\n","\n","The final element in our training set is the binary output for each case, i.e. the `y` label. \n","\n","Create an sdf `stocks_train_sdf` from `filter_3_stocks_sdf` with an additional column `direction`. This should be the direction of percentage change in the closing stock price, i.e. `1` for positive or `-1` for negative, in the first quarter of a given year. The quarter of a year begins in January and ends in April, inclusive. We want to know the percent change between these two months. Reference Step 2.2 for the percent change formula. The format of the sdf is shown below:\n","\n","```\n","+----+-----+----------+---------+----------+-------------+\n","|org |year |jan_stock |   ...   |dec_stock |direction    |\n","+----+-----+----------+---------+----------+-------------+\n","|IBM |2008 |...       |   ...   |...       |1.0          |\n","|IBM |2009 |...       |   ...   |...       |-1.0         |\n","|... |...  |...       |   ...   |...       |...          |\n","+----+-----+----------+---------+----------+-------------+\n","```"]},{"cell_type":"code","metadata":{"id":"yEFJIfyZKf7B","colab_type":"code","outputId":"f24e6eae-c8f3-4336-a5ea-088b5f275dd3","executionInfo":{"status":"ok","timestamp":1575585685404,"user_tz":300,"elapsed":1134227,"user":{"displayName":"Leshang Chen","photoUrl":"","userId":"00573172153387237137"}},"colab":{"base_uri":"https://localhost:8080/","height":17,"referenced_widgets":["3f7baced569340eb9e13bcce2d6e7834","661ffb43a0b24ce99e3a3bd560dc7f3c","293e6a8149074fa297336901798a3c5a"]}},"source":["%%spark\n","\n","# TODO: Create [stocks_train_sdf]\n","# YOUR CODE HERE\n"],"execution_count":0,"outputs":[{"output_type":"display_data","data":{"application/vnd.jupyter.widget-view+json":{"model_id":"3f7baced569340eb9e13bcce2d6e7834","version_minor":0,"version_major":2},"text/plain":["FloatProgress(value=0.0, bar_style='info', description='Progress:', layout=Layout(height='25px', width='50%'),…"]},"metadata":{"tags":[]}}]},{"cell_type":"markdown","metadata":{"id":"Fd2nviNpM2dF","colab_type":"text"},"source":["### Step 3.5: The Capricious Combination\n","\n","Now that we have individually created the two halfs of our training data we will merge them together to create the final training sdf we showed in the beginning of Step 3.\n","\n","Create an sdf called `training_sdf` in the format of the one shown at the beginning of Step 3. Note that in our definition for the `stock_result` column, the `stock_result` value for a particular year corresponds to the direction of the stock percentage change in the **following** year. For example, the stock_result in the `2008` row for `IBM` will contain the direction of IBM's stock in the first quarter of 2009. The format of the sdf is shown below:\n","```\n","+----+-----+----------+---------+----------+----------+---------+----------+-------------+\n","|org |year |jan_hired |   ...   |dec_hired |jan_stock |   ...   |dec_stock |stock_result |\n","+----+-----+----------+---------+----------+----------+---------+----------+-------------+\n","|IBM |2008 |...       |   ...   |...       |...       |   ...   |...       |-1.0         |\n","|IBM |2009 |...       |   ...   |...       |...       |   ...   |...       |1.0          |\n","|... |...  |...       |   ...   |...       |...       |   ...   |...       |...          |\n","+----+-----+----------+---------+----------+----------+---------+----------+-------------+\n","```"]},{"cell_type":"code","metadata":{"id":"8ZIb6QkcO5RB","colab_type":"code","outputId":"824edeb5-3b1f-4282-fc90-3709e6f214cd","executionInfo":{"status":"ok","timestamp":1575585746267,"user_tz":300,"elapsed":1195073,"user":{"displayName":"Leshang Chen","photoUrl":"","userId":"00573172153387237137"}},"colab":{"base_uri":"https://localhost:8080/","height":17,"referenced_widgets":["60381de0cb0f4bcbb03a3f077896433a","183ce4cf310845d29208c2565450ead1","745dc77db2ac4dd6838be8dab0a5f66c"]}},"source":["%%spark\n","\n","# TODO: Create [training_sdf]\n","# YOUR CODE HERE\n"],"execution_count":0,"outputs":[{"output_type":"display_data","data":{"application/vnd.jupyter.widget-view+json":{"model_id":"60381de0cb0f4bcbb03a3f077896433a","version_minor":0,"version_major":2},"text/plain":["FloatProgress(value=0.0, bar_style='info', description='Progress:', layout=Layout(height='25px', width='50%'),…"]},"metadata":{"tags":[]}}]}]}
=======
{
 "cells": [
  {
   "cell_type": "markdown",
   "metadata": {
    "colab_type": "text",
    "id": "qVMCjEV1IEC7"
   },
   "source": [
    "# Homework: Spark SQL\n",
    "\n",
    "In this homework you will gain a mastery of using Spark SQL. By the end, you'll be a star (not that you aren't already one). Over the next few days you will be using an EMR cluster to use Spark to manipulate the entire `linkedin.json` dataset as well as a new data set, `stock_prices.csv`.\n",
    "\n",
    "The goal of the homework will be to create a training dataset for a Random Forest Machine learning model. The training data set will contain the monthly number of employees hired by companies in `linkedin.json` and their corresponding closing stock prices over a 10 year period (2000-2011). We will try and predict, based on this data, if the company will have a positive or negative growth in stock in the first quarter of the next year. Who's ready to make some money?\n",
    "\n",
    "## The Noteworthy Notes\n",
    "Before we begin here are some important notes to keep in mind,\n",
    "\n",
    "\n",
    "1.   **IMPORTANT!** I said it twice, it's really important. In this homework, we will be using AWS resources. You are given a quota ($150) to use for the entirety of the homework. There is a small chance you will use all this money, however it is important that at the end of every session, you **shut down your EMR cluster**.\n",
    "2.   **You can only use Google Colab for this Homework** since we must connect to the EMR cluster and Jupyter doesn't do that. Using a Google Colab Notebook with an EMR cluster has two important abnormalities:\n",
    "    * The first line of any cell in which you will use the spark session must be `%%spark`. Notice that all cells below have this.\n",
    "    * You will, unfortunately, not be able to stop a cell while it is running. If you wish to do so, you will need to restart your cluster. See the Setup EMR Document for reference.\n",
    "3.   You are **required** to use Spark SQL queries to handle the data in the assignment. Mastering SQL is more beneficial than being able to use Spark commands (functions) as it will show up in more areas of programming and data science/analytics than just Spark. Use the following [function list](https://spark.apache.org/docs/latest/api/sql/index.html#) to see all the SQL functions avaliable in Spark.\n",
    "4. There are portions of this homework that are _very_ challenging. \n",
    "\n",
    "With that said, let's dive in.\n",
    "\n",
    "\n"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {
    "colab_type": "text",
    "id": "7XEqGpEGBWs5"
   },
   "source": [
    "## Step 0: Set up EMR\n",
    "\n",
    "Your first task is to create an EMR cluster your AWS Educate Accounts. Please see the [attached document](https://drive.google.com/open?id=1_8NB_3QXfQKm5Vyu7XyxU2mnH5HnGM-F) for detailed instructions. Move on to Step 0.1 after you have completed all the steps in the document."
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {
    "colab_type": "text",
    "id": "5iBPXxgAdXkv"
   },
   "source": [
    "### Step 0.1: The Superfluous Setup\n",
    "\n",
    "Run the following two cells. These will allow your colab notebook to connect to an use your EMR."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "colab": {},
    "colab_type": "code",
    "id": "pvkEbVaaAQ1e"
   },
   "outputs": [],
   "source": [
    "%%capture\n",
    "!apt update\n",
    "!apt install gcc python-dev libkrb5-dev\n",
    "!pip install sparkmagic\n",
    "!pip install pyspark"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "colab": {},
    "colab_type": "code",
    "id": "6WAJmQ8IAbRs"
   },
   "outputs": [],
   "source": [
    "%%capture\n",
    "%load_ext sparkmagic.magics "
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "colab": {},
    "colab_type": "code",
    "id": "IAvEMpzqqHeY"
   },
   "outputs": [],
   "source": [
    "from pyspark.sql.types import *\n",
    "import json\n",
    "import urllib.request\n",
    "\n",
    "from datetime import datetime"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {
    "colab_type": "text",
    "id": "CL6n768EPt9E"
   },
   "source": [
    "### Step 0.2: The Succulent Spark\n",
    "\n",
    "Now, connect your notebook to the EMR cluster you created. In the first cell, copy the link to the Master Public DNS specified in the setup document. You will need to add `http://` to the beginning of the address and the port to the end. The final format should be,\n",
    "\n",
    "`http://<your-DNS-link>:8998`\n",
    "\n",
    "For example, if my DNS (directly from the AWS EMR console) is `ec2-3-15-237-211.us-east-2.compute.amazonaws.com` my address would be,\n",
    "\n",
    "`http://ec2-3-15-237-211.us-east-2.compute.amazonaws.com:8998`\n",
    "\n",
    "Insert this in the `# TODO # below`. For our example, the cell would read,\n",
    "\n",
    "\n",
    "\n",
    "```\n",
    "%spark add -s spark_session -l python -u http://ec2-3-15-237-211.us-east-2.compute.amazonaws.com:8998\n",
    "```"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "colab": {
     "base_uri": "https://localhost:8080/",
     "height": 92,
     "referenced_widgets": [
      "0ec3bf246d6241e38d2b68f478bccb36",
      "f848b820820f462f9bd7788a206c6197",
      "c81f5783e51f450f8335631023d58bec"
     ]
    },
    "colab_type": "code",
    "executionInfo": {
     "elapsed": 27719,
     "status": "ok",
     "timestamp": 1575582618079,
     "user": {
      "displayName": "Leshang Chen",
      "photoUrl": "",
      "userId": "00573172153387237137"
     },
     "user_tz": 300
    },
    "id": "R9XWNkCEAeWP",
    "outputId": "63ed2476-09a2-4242-9c13-327feedcf230"
   },
   "outputs": [],
   "source": [
    "# TODO: Enter your Master Public DNS with the proper formatting and host\n",
    "\n",
    "%spark add -s spark_session -l python -u http://ec2-34-205-85-50.compute-1.amazonaws.com:8998 "
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "colab": {},
    "colab_type": "code",
    "id": "DSALdzFWgKuV"
   },
   "outputs": [],
   "source": [
    "# To delte a session. \n",
    "# %spark delete -s spark_session_name"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {
    "colab_type": "text",
    "id": "Bfs-EQZUzF4j"
   },
   "source": [
    "_Note_: Since we are using an EMR cluster we will not have access to the several modules that exist for python, e.g. `pandas`, `numpy`, etc. We have written the entire homework such that the solution does not require any of these. Don't try to import them. It won't work."
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {
    "colab_type": "text",
    "id": "Nf_ADEXnIK0b"
   },
   "source": [
    "## Step 1: Data Cleaning and Shaping\n",
    "\n",
    "The data you will use is stored in an S3 bucket, a cloud storage service. You now need to download it onto the nodes of your [EMR cluster](https://docs.aws.amazon.com/emr/latest/ManagementGuide/emr-what-is-emr.html). \n",
    "\n",
    "### Step 1.1: The Stupendous Schema\n",
    "\n",
    "When loading data, Spark will try to infer it's structure on it's own. This process is faulty because it will sometimes infer the type incorrectly. JSON documents, like the one we will use, can have nested types, such as: arrays, arrays of dictionaries, dictionaries of dictionaries, etc. Spark's ability to determine these nested types is not reliable, thus you will define a schema for `linkedin.json`.\n",
    "\n",
    "A schema is a description of the structure of data. You will be defining an explicit schema for `linkedin.json`. In Spark, schema's are defined using a `StructType` object. This is a collection of data types, termed `StructField`'s, that specify the structure and variable type of each component of the dataset. For example, suppose we have the following simple JSON object,\n",
    "\n",
    "\n",
    "```\n",
    "{\n",
    " \"student_name\": \"Leonardo Murri\",\n",
    " \"GPA\": 1.4,\n",
    " \"courses\": [\n",
    "    {\"department\": \"Computer and Information Science\",\n",
    "     \"course_id\": \"CIS 545\",\n",
    "     \"semester\": \"Fall 2018\"},\n",
    "    {\"department\": \"Computer and Information Science\",\n",
    "     \"course_id\": \"CIS 520\",\n",
    "     \"semester\": \"Fall 2018\"},\n",
    "    {\"department\": \"Electrical and Systems Engineering\",\n",
    "     \"course_id\": \"ESE 650\",\n",
    "     \"semester\": \"Spring 2018\"}\n",
    " ],\n",
    " \"grad_year\": 2019\n",
    " }\n",
    "```\n",
    "\n",
    "We would define it's schema as follows,\n",
    "\n",
    "```       \n",
    "schema = StructType([\n",
    "           StructField(\"student_name\", StringType(), nullable=True),\n",
    "           StructField(\"GPA\", FloatType(), nullable=True),\n",
    "           StructField(\"courses\", ArrayType(\n",
    "                StructType([\n",
    "                  StructField(\"department\", StringType(), nullable=True),\n",
    "                  StructField(\"course_id\", StringType(), nullable=True),\n",
    "                  StructField(\"semester\", StringType(), nullable=True)\n",
    "                ])\n",
    "           ), nullable=True),\n",
    "           StructField(\"grad_year\", IntegerType(), nullable=True)\n",
    "         ])\n",
    "```\n",
    "\n",
    "\n",
    "Each `StructField` has the following structure: `(name, type, nullable)`. The `nullable` flag defines that the specified field may be empty. Your first task is to define the `schema` of `linkedin.json`. A sample JSON object for the dataset can be found [here](http://oneclickpaste.com/194048/).\n",
    "\n",
    "_Note_: In `linkedin.json` the field `specilities` is spelled incorrectly. This is **not** a typo. \n",
    "\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "colab": {
     "base_uri": "https://localhost:8080/",
     "height": 17,
     "referenced_widgets": [
      "8b3b2f26485c413881059c5affd2055e",
      "0f816c10fa484f01b4a14190ade37768",
      "b2af543a4da04a53a7080b43eea45486"
     ]
    },
    "colab_type": "code",
    "executionInfo": {
     "elapsed": 402,
     "status": "ok",
     "timestamp": 1575583353855,
     "user": {
      "displayName": "Leshang Chen",
      "photoUrl": "",
      "userId": "00573172153387237137"
     },
     "user_tz": 300
    },
    "id": "pL-Ps4KWIJ9e",
    "outputId": "452c5671-7852-4905-ee6d-c26f8ad23ea0"
   },
   "outputs": [],
   "source": [
    "%%spark\n",
    "\n",
    "# TODO: Define [linkedin.json] schema\n",
    "# YOUR CODE HERE\n"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {
    "colab_type": "text",
    "id": "2Su604X9ggc2"
   },
   "source": [
    "### Step 1.2: The Laudable Loading\n",
    "\n",
    "Load the `linkedin.json` dataset from your S3 bucket into a Spark dataframe (sdf) called `raw_data_sdf`. If you have constructed `schema` correctly `spark.read.json()` will read in the dataset. ***You do not need to edit this cell***."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "colab": {
     "base_uri": "https://localhost:8080/",
     "height": 17,
     "referenced_widgets": [
      "10e9bda7da7e48d0845b749ab8e9e9ee",
      "d1a222beb845430abec998d9c6f175ea",
      "3747c90befbe4e7094faa212b6512361"
     ]
    },
    "colab_type": "code",
    "executionInfo": {
     "elapsed": 2733,
     "status": "ok",
     "timestamp": 1575584115692,
     "user": {
      "displayName": "Leshang Chen",
      "photoUrl": "",
      "userId": "00573172153387237137"
     },
     "user_tz": 300
    },
    "id": "ji-KW2sAiB6r",
    "outputId": "e58668c0-67a8-4e2c-d602-18d70266da27"
   },
   "outputs": [],
   "source": [
    "%%spark\n",
    "# TODO: modify the location to the place where you have the data. \n",
    "raw_data_sdf = spark.read.json(\"s3a://opends4allhw3/linkedin.json\", schema=schema)\n",
    "# raw_data_sdf = spark.read.json('linkedin.json', schema=schema)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {
    "colab_type": "text",
    "id": "jMVCVotcE1wv"
   },
   "source": [
    "The cell below shows how to run SQL commands on Spark tables. Use this as a template for all your SQL queries in this notebook. ***You do not need to edit this cell***."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "colab": {
     "base_uri": "https://localhost:8080/",
     "height": 289,
     "referenced_widgets": [
      "fc9a5ea9104f4def9166d3a62dff697a",
      "8d520961230c4509820ffc9e698c6cdc",
      "59e9ab8b8888436da6023bff00ce7098"
     ]
    },
    "colab_type": "code",
    "executionInfo": {
     "elapsed": 12066,
     "status": "ok",
     "timestamp": 1575584235649,
     "user": {
      "displayName": "Leshang Chen",
      "photoUrl": "",
      "userId": "00573172153387237137"
     },
     "user_tz": 300
    },
    "id": "NJSVWeGiEO5c",
    "outputId": "ec70a7ff-43ae-42e6-93b2-917f2fe11de2"
   },
   "outputs": [],
   "source": [
    "%%spark\n",
    "\n",
    "# Create SQL-accesible table\n",
    "raw_data_sdf.createOrReplaceTempView(\"raw_data\")\n",
    "\n",
    "# Declare SQL query to be excecuted\n",
    "query = '''SELECT * \n",
    "           FROM raw_data'''\n",
    "\n",
    "# Save the output sdf of spark.sql() as answer_sdf\n",
    "answer_sdf = spark.sql(query)\n",
    "\n",
    "# Display the first 10 rows\n",
    "answer_sdf.show(10)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {
    "colab_type": "text",
    "id": "svOO4iLPist4"
   },
   "source": [
    "### Step 1.3: The Extravagent Extraction\n",
    "\n",
    "In our training model, we are interested in when individuals began working at a company.  From creating the schema, you should notice that the collection of companies inviduals worked at are contained in the `experience` field as an array of dictionaries. You should use the `org` for the company name and `start` for the start date. Here is an example of an `experience` field,\n",
    "\n",
    "```\n",
    "{\n",
    "   \"experience\": [\n",
    "     {\n",
    "        \"org\": \"The Walt Disney Company\", \n",
    "        \"title\" : \"Mickey Mouse\",\n",
    "        \"end\" : \"Present\",\n",
    "        \"start\": \"November 1928\",\n",
    "        \"desc\": \"Sailed a boat.\"\n",
    "     },\n",
    "     {\n",
    "        \"org\": \"Walt Disney World Resort\",\n",
    "        \"title\": \"Mickey Mouse Mascot\",\n",
    "        \"start\": \"January 2005\",\n",
    "        \"desc\": \"Took pictures with kids.\"\n",
    "     }\n",
    "   ]\n",
    "}\n",
    "```\n",
    "\n",
    "Your task is to extract each pair of company and start date from these arrays. In Spark, this is known as \"exploding\" a row. An explode will seperate the elements of an array into multiple rows.\n",
    "\n",
    "Create an sdf called `raw_start_dates_sdf` that contains the company and start date for every experience of every individual in `raw_data_sdf`. Drop any row that contains a `null` in either column with `dropna()`. You can sort the elements however you wish (you don't need to if you don't want to). The sdf should look as follows:\n",
    "\n",
    "```\n",
    "+--------------------------+---------------+\n",
    "|org                       |start_date     |\n",
    "+--------------------------+---------------+\n",
    "|Walt Disney World Resort  |January 2005   | \n",
    "|The Walt Disney Company   |November 1928  |\n",
    "|...                       |...            |\n",
    "+--------------------------+---------------+\n",
    "```\n",
    "\n",
    "_Hint_: You may want to do two seperate explodes for `org` and `start`. In an explode, the position of the element in the array can be extracted as well, and used to merge two seperate explodes. Reference the [function list](https://spark.apache.org/docs/2.3.0/api/sql/index.html).\n",
    "\n",
    "_Note_: Some of the entires in `org` are \"weird\", i.e. made up of non-english letters and characters. Keep them. **DO NOT** edit any name in the original dataframe unless we specify. **DO NOT** drop any row unless there is a `null` value as stated before. This goes for the rest of the homework as well, unless otherwise specified."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "colab": {
     "base_uri": "https://localhost:8080/",
     "height": 17,
     "referenced_widgets": [
      "cfecaa14eb514313a5f15ed03ef6789d",
      "5bea45fa7fc54ebebad0a57be6310c65",
      "472c58675e1e4d2eab714bd7ce9f0df3"
     ]
    },
    "colab_type": "code",
    "executionInfo": {
     "elapsed": 36859,
     "status": "ok",
     "timestamp": 1575584587756,
     "user": {
      "displayName": "Leshang Chen",
      "photoUrl": "",
      "userId": "00573172153387237137"
     },
     "user_tz": 300
    },
    "id": "Kt16tyP0klQX",
    "outputId": "4bb1d270-0215-44b6-d3b0-4cfcf2a3027b"
   },
   "outputs": [],
   "source": [
    "%%spark\n",
    "\n",
    "# TODO: Create [raw_start_dates_sdf]\n",
    "\n",
    "# YOUR CODE HERE\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "colab": {
     "base_uri": "https://localhost:8080/",
     "height": 168,
     "referenced_widgets": [
      "dc7c0513d85b4643bdbfc7bf8702e856",
      "2056868703734a26b1db0e3ffec7699d",
      "66aaa398ce5e401b9e1a1d87988e5487"
     ]
    },
    "colab_type": "code",
    "executionInfo": {
     "elapsed": 108829,
     "status": "ok",
     "timestamp": 1575584659737,
     "user": {
      "displayName": "Leshang Chen",
      "photoUrl": "",
      "userId": "00573172153387237137"
     },
     "user_tz": 300
    },
    "id": "-a3xLDEYCcwl",
    "outputId": "6735732d-912e-4823-c8a2-59daf4f41d58"
   },
   "outputs": [],
   "source": [
    "%%spark\n",
    "raw_start_dates_sdf.show(4)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {
    "colab_type": "text",
    "id": "zSbb0t-d-VFt"
   },
   "source": [
    "### Step 1.4: The Fortuitous Formatting\n",
    "\n",
    "There are two issues with the values in our `date` column. First, the values are saved as strings, not datetime types. This halts us from running functions such as `ORDER BY` or `GROUP BY` on common months or years. Second, some values do not have both month and year information or are in other languages. Your task is to filter out and clean the `date` column. We are interested in only those rows that have date in the following format \"(month_name) (year)\", e.g. \"October 2010\".\n",
    "\n",
    "Create an sdf called `filtered_start_dates_sdf` from `raw_start_dates_sdf` with the `date` column filtered in the manner above. Keep only those rows with a start date between January 2000 to December 2011, inclusive. Ensure that any dates that are not in our desired format are ommitted. Drop any row that contains a `null` in either column. The format of the sdf is shown below:\n",
    "```\n",
    "+--------------------------+---------------+\n",
    "|org                       |start_date     |\n",
    "+--------------------------+---------------+\n",
    "|Walt Disney World Resort  |2005-01-01     | \n",
    "|...                       |...            |\n",
    "+--------------------------+---------------+\n",
    "```\n",
    "_Hint_: Refer to the [function list](https://spark.apache.org/docs/2.3.0/api/sql/index.html) to format the `date` column. In Spark SQL the date format we are interested in is `\"MMM y\"`.\n",
    "\n",
    "_Note_: Spark will return the date in the format above, with the day as `01`. This is ok, since we are interested in the month and year each individual began working and all dates will have `01` as their day."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "colab": {
     "base_uri": "https://localhost:8080/",
     "height": 17,
     "referenced_widgets": [
      "52815494440f4161b01b6eebb60b999d",
      "91880f7960e5464da7893d80df72d483",
      "8ece5c44cc2c4a25b4fcc641882667a4"
     ]
    },
    "colab_type": "code",
    "executionInfo": {
     "elapsed": 183678,
     "status": "ok",
     "timestamp": 1575584734608,
     "user": {
      "displayName": "Leshang Chen",
      "photoUrl": "",
      "userId": "00573172153387237137"
     },
     "user_tz": 300
    },
    "id": "eelgTtOc_MBM",
    "outputId": "49854f5c-074a-4611-fc23-0acb2df64963"
   },
   "outputs": [],
   "source": [
    "%%spark\n",
    "\n",
    "# TODO: Create [filtered_start_dates_sdf]\n"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {
    "colab_type": "text",
    "id": "LXYYQn2_GYwZ"
   },
   "source": [
    "### Step 1.5 The Gregarious Grouping\n",
    "\n",
    "We now want to collect the number of individuals that started in the same month and year for each company. Create an sdf called `start_dates_sdf` that has the total number of employees who began working at the same company on the same start date. The format of the sdf is shown below:\n",
    "\n",
    "```\n",
    "+--------------------------+---------------+---------------+\n",
    "|org                       |start_date     |num_employees  |\n",
    "+--------------------------+---------------+---------------+\n",
    "|Walt Disney World Resort  |2005-01-01     |1              |\n",
    "|...                       |...            |...            |\n",
    "+--------------------------+---------------+---------------+\n",
    "```"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "colab": {
     "base_uri": "https://localhost:8080/",
     "height": 17,
     "referenced_widgets": [
      "f52ae0051fc24086995d05461b02229d",
      "defbbe69eeb44fcbba983d981a4521fe",
      "5647e8443a5a4d7a9d775c8a2bea87c6"
     ]
    },
    "colab_type": "code",
    "executionInfo": {
     "elapsed": 303177,
     "status": "ok",
     "timestamp": 1575584854127,
     "user": {
      "displayName": "Leshang Chen",
      "photoUrl": "",
      "userId": "00573172153387237137"
     },
     "user_tz": 300
    },
    "id": "CxVIyc1CHooV",
    "outputId": "0a2c79d4-0749-403a-e007-0173cffe006e"
   },
   "outputs": [],
   "source": [
    "%%spark\n",
    "\n",
    "# TODO: Create [start_dates_sdf]\n",
    "# YOUR CODE HERE\n"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {
    "colab_type": "text",
    "id": "QYScM3FwJnUz"
   },
   "source": [
    "## Step 2: Hiring Trends Analysis\n",
    "\n",
    "Now we will analyze `start_dates_sdf` to find monthly and annual hiring trends.\n",
    "\n",
    "### Step 2.1: The Marvelous Months\n",
    "\n",
    "Your task is to answer the question: \"On average, what month do most employees start working?\" Create an sdf called `monthly_hires_sdf` which contains the total number of employees that started working on a specific month, at any company and on any year. The `month` column should be of type `int`, i.e. 1-12. The format of the sdf is shown below:\n",
    "\n",
    "```\n",
    "+---------------+---------------+\n",
    "|month          |num_employees  |\n",
    "+---------------+---------------+\n",
    "|1              |...            |\n",
    "|2              |...            |\n",
    "|3              |...            |\n",
    "|...            |...            |\n",
    "+---------------+---------------+\n",
    "```\n",
    "\n",
    "Find the month in which the most employees start working and save it's number as an integer to the variable `most_common_month`.\n",
    "\n",
    "_Hint_: Be careful. The starts dates we have right now have both month and year. We only want the common months. See if you can find something in the [function list](https://spark.apache.org/docs/2.3.0/api/sql/index.html) that will help you do this."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "colab": {
     "base_uri": "https://localhost:8080/",
     "height": 17,
     "referenced_widgets": [
      "00c647594bdf4e86aebd273fffcbcc85",
      "8de371ef2a1749558e1a7fe53363d060",
      "3e73d2733d814879bfb767f462570743"
     ]
    },
    "colab_type": "code",
    "executionInfo": {
     "elapsed": 420722,
     "status": "ok",
     "timestamp": 1575584971691,
     "user": {
      "displayName": "Leshang Chen",
      "photoUrl": "",
      "userId": "00573172153387237137"
     },
     "user_tz": 300
    },
    "id": "vLTmvD9WNQH3",
    "outputId": "4463575e-90d3-4050-ef97-b8d1155f5a4d"
   },
   "outputs": [],
   "source": [
    "%%spark\n",
    "\n",
    "# TODO: Create [monthly_hire_sdf] and find the most common month people were\n",
    "# hired. Save its number as an integer to [most_common_month]\n"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {
    "colab_type": "text",
    "id": "CUtVHRcMUoWp"
   },
   "source": [
    "### Step 2.2: The Preposterous Percentages\n",
    "\n",
    "The next question we will answer is \"What is the percentage change in hires between 2010 and 2011 for each company?\" Create an sdf called `percentage_change_sdf` that has the percentage change between 2010 and 2011 for each company. The sdf should look as follows:\n",
    "\n",
    "```\n",
    "+---------------------------+--------------------+\n",
    "|org                        |percentage_change   |\n",
    "+---------------------------+--------------------+\n",
    "|Walt Disney World Resort   |12.3                |\n",
    "|...                        |...                 |\n",
    "+---------------------------+--------------------+\n",
    "```\n",
    "\n",
    "_Note_: A percentage change can be positive or negative depending \n",
    "on the difference between the two years.The formula for percent change is given below,\n",
    "\n",
    "$$\\text{% change} = \\frac{P_f-P_i}{P_f} \\times 100$$\n",
    "\n",
    "Here, $P_f$ is the final element (in this case the number of hires in 2011) and $P_i$ is initial element (the number of hires in 2010).\n",
    "\n",
    "_Hint_: This is a **difficult** question. We recommend using a combination of `GROUP BY` and `JOIN`. Keep in mind that operations between columns in SQL dataframes are often easier than those between rows. "
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "colab": {
     "base_uri": "https://localhost:8080/",
     "height": 17,
     "referenced_widgets": [
      "6bd5845756c84761a22c4fca0c27e091",
      "18c3f006e61b466abea135b15d4fe624",
      "637bd902d20544569334d56244bca5d3"
     ]
    },
    "colab_type": "code",
    "executionInfo": {
     "elapsed": 538283,
     "status": "ok",
     "timestamp": 1575585089273,
     "user": {
      "displayName": "Leshang Chen",
      "photoUrl": "",
      "userId": "00573172153387237137"
     },
     "user_tz": 300
    },
    "id": "_AhhfLXpWq7y",
    "outputId": "e2e04149-cade-4ffc-d4bb-4e655228b109"
   },
   "outputs": [],
   "source": [
    "%%spark\n",
    "\n",
    "# TODO: Create [percentage_change_sdf]\n",
    "# YOUR CODE HERE\n"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {
    "colab_type": "text",
    "id": "QkF2RfLSXO0u"
   },
   "source": [
    "## Step 3: Formatting the Training Data\n",
    "\n",
    "\n",
    "Our overaching goal is to train a machine learning (ML) model that will use the monthly hiring trends of a company to predict a positive or negative gain in the company's stock in the first quarter of the following year. A ML model is trained on a set of observations. Each observation contains a set of features, `X`, and a label, `y`. The goal of the ML model is to create a function that takes any `X` as an input and outputs a predicted `y`. \n",
    "\n",
    "The machine learning model we will use is a [Random Forest Classifier](https://builtin.com/data-science/random-forest-algorithm). Each observation we will pass in will have 24 features (columns). These are the number of people hired from Jan to Dec and the company stock price on the last day of each month. The label will be the direction of the company's stock percentage change (positive, `1`, or negative, `-1`) in the first quarter of the following year. Each observation will correspond to a specified company's trends on a specified year. The format of our final training sdf is shown below. The first 26 columns define our observations, `X`, and the last column the label, `y`.\n",
    "```\n",
    "+----+-----+----------+---------+----------+----------+---------+----------+-------------+\n",
    "|org |year |jan_hired |   ...   |dec_hired |jan_stock |   ...   |dec_stock |stock_result |\n",
    "+----+-----+----------+---------+----------+----------+---------+----------+-------------+\n",
    "|IBM |2008 |...       |   ...   |...       |...       |   ...   |...       |1            |\n",
    "|IBM |2009 |...       |   ...   |...       |...       |   ...   |...       |-1           |\n",
    "|... |...  |...       |   ...   |...       |...       |   ...   |...       |...          |\n",
    "+----+-----+----------+---------+----------+----------+---------+----------+-------------+\n",
    "```\n",
    "\n",
    "_Note_: We will use the first three letters of each month in naming, i.e. `jan, feb, mar, apr, may, jun, jul, aug, sep, oct, nov, dec`\n",
    "\n",
    "\n",
    "\n",
    "### Step 3.1: The Harmonious Hires\n",
    "\n",
    "Your first task is to create the first half of the training table, i.e. the `jan_hired` through `dec_hired` columns. This will involve reshaping `start_dates_sdf`. Currently, `start_dates_sdf` has columns `org`, `start_date`, and `num_employees`. We want to group the rows together based on common `org` and years and create new columns for the number of employees that started working in each month of that year.\n",
    "\n",
    "Create an sdf called `raw_hirings_for_training_sdf` that has for a single company and a single year, the number of hires in Jan through Dec, and the total number of hires that year. Note that for each company you will have several rows corresponding to years between 2000 and 2011. It is ok if for a given company you don't have a given year. However, ensure that for a given company and given year, each month column has an entry, i.e. if no one was hired the value should be `0`. The format of the sdf is shown below: \n",
    "```\n",
    "+----+-----+----------+---------+----------+----------+\n",
    "|org |year |jan_hired |   ...   |dec_hired |total_num |\n",
    "+----+-----+----------+---------+----------+----------+\n",
    "|IBM |2008 |...       |   ...   |...       |...       |\n",
    "|IBM |2009 |...       |   ...   |...       |...       |\n",
    "|... |...  |...       |   ...   |...       |...       |\n",
    "+----+-----+----------+---------+----------+----------+\n",
    "```\n",
    "_Hint_: This is a **difficult** question. The tricky part is creating the additional columns of monthly hires, specifically when there are missing dates. In our dataset, if a company did not hire anybody in a given date, it will not appear in `start_dates_sdf`. We suggest you look into `CASE` and `WHEN` statements in the [function list](https://spark.apache.org/docs/2.3.0/api/sql/index.html)."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "colab": {
     "base_uri": "https://localhost:8080/",
     "height": 17,
     "referenced_widgets": [
      "277806ae13be4bd2a7e17019f477a79e",
      "0f19d766cf624efc9a417c72f607368e",
      "cc5799297d594e738a34ed4b83a16fec"
     ]
    },
    "colab_type": "code",
    "executionInfo": {
     "elapsed": 754324,
     "status": "ok",
     "timestamp": 1575585305365,
     "user": {
      "displayName": "Leshang Chen",
      "photoUrl": "",
      "userId": "00573172153387237137"
     },
     "user_tz": 300
    },
    "id": "btp2wboHqg2J",
    "outputId": "f3561d40-c00d-497f-c487-3c58747b08dc"
   },
   "outputs": [],
   "source": [
    "%%spark\n",
    "\n",
    "# TODO: Create [raw_hire_train_sdf]\n",
    "# YOUR CODE HERE\n"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {
    "colab_type": "text",
    "id": "IkXyet6rrczK"
   },
   "source": [
    "### Step 3.2: The Formidable Filters\n",
    "\n",
    "Create an sdf called `hire_train_sdf` that contains all the observations in `raw_hire_train_sdf` with `total_num` greater than or equal to 10. The format of the sdf is shown below:\n",
    "\n",
    "```\n",
    "+----+-----+----------+---------+----------+----------+\n",
    "|org |year |jan_hired |   ...   |dec_hired |total_num |\n",
    "+----+-----+----------+---------+----------+----------+\n",
    "|IBM |2008 |...       |   ...   |...       |...       |\n",
    "|IBM |2009 |...       |   ...   |...       |...       |\n",
    "|... |...  |...       |   ...   |...       |...       |\n",
    "+----+-----+----------+---------+----------+----------+\n",
    "```\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "colab": {
     "base_uri": "https://localhost:8080/",
     "height": 17,
     "referenced_widgets": [
      "0b0cf2113c5d46079b10ee9291d81ca2",
      "e904164016e649b39ae675c5316f3718",
      "b2deaafff8174b3cab34294d58e1e27e"
     ]
    },
    "colab_type": "code",
    "executionInfo": {
     "elapsed": 865658,
     "status": "ok",
     "timestamp": 1575585416723,
     "user": {
      "displayName": "Leshang Chen",
      "photoUrl": "",
      "userId": "00573172153387237137"
     },
     "user_tz": 300
    },
    "id": "dCH4mbNcshq9",
    "outputId": "2a546db3-3a2e-4e16-e598-3cfd828c9e2f"
   },
   "outputs": [],
   "source": [
    "%%spark\n",
    "\n",
    "# TODO: Create [hire_train_sdf]\n",
    "# YOUR CODE HERE\n"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {
    "colab_type": "text",
    "id": "MN4ik70Hta01"
   },
   "source": [
    "### Step 3.3: The Stupendous Stocks\n",
    "\n",
    "Now we are ready for the stock data. The stock data we will use is saved in the same S3 bucket as `linkedin.json`. Load the data into the EMR cluster. Run the cell below. ***You do not need to edit this cell, except changing dataset url***."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "colab": {
     "base_uri": "https://localhost:8080/",
     "height": 269,
     "referenced_widgets": [
      "25c499f20e694a60bd3fc36ab3287402",
      "a27a5af9ab1e47449f2cb01f298ab010",
      "e2efaeed796f491dac1e80de28d71575"
     ]
    },
    "colab_type": "code",
    "executionInfo": {
     "elapsed": 973879,
     "status": "ok",
     "timestamp": 1575585524960,
     "user": {
      "displayName": "Leshang Chen",
      "photoUrl": "",
      "userId": "00573172153387237137"
     },
     "user_tz": 300
    },
    "id": "APv4BxKw643q",
    "outputId": "1de29e91-11be-40ca-9c02-5491aee6ff10"
   },
   "outputs": [],
   "source": [
    "%%spark\n",
    "# TODO: modify the location to the place that you have the data. \n",
    "# Load stock data\n",
    "raw_stocks_sdf = spark.read.format(\"csv\") \\\n",
    "              .option(\"header\", \"true\") \\\n",
    "              .load(\"s3a://xxx/stock_prices.csv\")\n",
    "\n",
    "# Creates SQL-accesible table\n",
    "raw_stocks_sdf.createOrReplaceTempView('raw_stocks')\n",
    "\n",
    "# Display the first 10 rows\n",
    "query = '''SELECT *\n",
    "           FROM raw_stocks'''\n",
    "spark.sql(query).show(10)\n"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {
    "colab_type": "text",
    "id": "JUCdr3zDUAFH"
   },
   "source": [
    "Run the cell below to see the types of the columns in our data frame. These are not correct. We could have defined a schema when reading in data but we will handle this issue in another manner. You will do this in Step 3.4.2."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "colab": {
     "base_uri": "https://localhost:8080/",
     "height": 34,
     "referenced_widgets": [
      "664d2f8b41e8447ba797bbd586cf7442",
      "14f008c6d70a41de96a66a9255d28c91",
      "cac27272dfa64b55b8ac984c8f2e83e7"
     ]
    },
    "colab_type": "code",
    "executionInfo": {
     "elapsed": 973871,
     "status": "ok",
     "timestamp": 1575585524961,
     "user": {
      "displayName": "Leshang Chen",
      "photoUrl": "",
      "userId": "00573172153387237137"
     },
     "user_tz": 300
    },
    "id": "oNTGEfxsisqs",
    "outputId": "c62fe3de-796e-4c7a-d030-fe67f6710e81"
   },
   "outputs": [],
   "source": [
    "%%spark \n",
    "\n",
    "# Print types of SDF\n",
    "raw_stocks_sdf.dtypes"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {
    "colab_type": "text",
    "id": "0DdnoFkP7mxz"
   },
   "source": [
    "### Step 3.4 The Clairvoyant Cleaning\n",
    "\n",
    "We now want to format the stock data set into the second half of the training table. We will then merge it with `hire_train` based off the common `org` and `year` fields. The formatting will consist of 4 steps. Actually, it is 5.\n",
    "\n",
    "#### Step 3.4.1 The Ubiquitous UDF\n",
    "\n",
    "The companies in our stock dataset are defined by their stock tickers. Thus, we would not be able to merge it with the `org` field in `hire_train_sdf`. We must convert them to that format. Often times when using Spark, there may not be a built-in SQL function that can do the operation we desired. Instead, we can create one on our own with a user-defined function (udf).\n",
    "\n",
    "A udf is defined as a normal Python function and then registered to be used as a Spark SQL function. Your task is to create a udf, `TICKER_TO_NAME()` that will convert the ticker field in `raw_stocks` to the company's name. This will be done using the provided `ticker_to_name_dict` dictionary. We are only interested in the companies in that dictionary.\n",
    "\n",
    "Fill out the function `ticker_to_name()` below. Then use `spark.udf.register()` to register it as a SQL function. The command is provided. ***You do not need to edit it***. Note, we have defined the udf as returning `StringType()`. Ensure that your function returns this. You must also deal with any potential `null` cases."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "colab": {
     "base_uri": "https://localhost:8080/",
     "height": 34,
     "referenced_widgets": [
      "90d37ecc6c6e42d8a7867fcce3980831",
      "5b6f6a0b7f8a45949f82869fcae374cb",
      "3e9b9186606b4aa28ddeee3a5112ab20"
     ]
    },
    "colab_type": "code",
    "executionInfo": {
     "elapsed": 974000,
     "status": "ok",
     "timestamp": 1575585525098,
     "user": {
      "displayName": "Leshang Chen",
      "photoUrl": "",
      "userId": "00573172153387237137"
     },
     "user_tz": 300
    },
    "id": "P4cJWZsr8iNC",
    "outputId": "101ca7f2-f6a8-443b-e138-365d49241567"
   },
   "outputs": [],
   "source": [
    "%%spark\n",
    "\n",
    "# TODO: Fill out [ticker_to_name()] and register it as a udf.\n",
    "# YOUR CODE HERE\n",
    "\n",
    "def ticker_to_name(ticker):\n",
    "\n",
    "# Register udf as a SQL function. DO NOT EDIT\n",
    "spark.udf.register(\"TICKER_TO_NAME\", ticker_to_name, StringType())\n"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {
    "colab_type": "text",
    "id": "u9YOYO9L-_GS"
   },
   "source": [
    "#### Step 3.4.2: The Fastidious Filters\n",
    "\n",
    "With our new `TICKER_TO_NAME()` function we will begin to wrangle `raw_stocks_sdf`.\n",
    "\n",
    "Create an sdf called `filter_1_stocks_sdf` as follows. Convert all the ticker names in `raw_stocks_sdf` to the company names and save it as `org`. Next, convert the `date` field to a datetime type. As explained before this will help order and group the rows in future steps. Then, convert the type of the values in `closing_price` to `float`. This will take care of the `dtypes` issue we saw in Step 3.3.\n",
    "\n",
    "Drop any company names that do not appear in `ticker_to_name_dict`. Keep any date between January 1st 2001 and December 4th 2012 inclusive, in the format shown below (note this is a datetime object not a string):\n",
    "\n",
    "```\n",
    "+----+------------+--------------+\n",
    "|org |date        |closing_price |\n",
    "+----+------------+--------------+\n",
    "|IBM |2000-01-03  |...           |\n",
    "|... |...         |...           |\n",
    "+----+------------+--------------+\n",
    "```\n",
    "_Hint_: You will use a similar function to filter the dates as in Step 1.4. In Spark SQL the format for the `date` field in `raw_stocks_sdf` is `\"yyyy-MM-dd\"`."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "colab": {
     "base_uri": "https://localhost:8080/",
     "height": 17,
     "referenced_widgets": [
      "9b53a3b6b43e46b48004eab3188f52cc",
      "375176dbd9eb42a29fd34152142f70b3",
      "72ed66a0fe4d476ab6c55a255ae0cad0"
     ]
    },
    "colab_type": "code",
    "executionInfo": {
     "elapsed": 974459,
     "status": "ok",
     "timestamp": 1575585525577,
     "user": {
      "displayName": "Leshang Chen",
      "photoUrl": "",
      "userId": "00573172153387237137"
     },
     "user_tz": 300
    },
    "id": "RuiitnWlBYJ7",
    "outputId": "dc6a7b77-34e5-4634-84b8-ca9114fa4c2f"
   },
   "outputs": [],
   "source": [
    "%%spark\n",
    "\n",
    "# TODO: Create [filter_1_stocks_sdf]\n",
    "# YOUR CODE HERE\n"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {
    "colab_type": "text",
    "id": "Ne5NaT-6CLns"
   },
   "source": [
    "#### Step 3.4.3: The Magnanimous Months\n",
    "\n",
    "The data in `filter_1_stocks_sdf` gives closing prices on a daily basis. Since we are interested in monthly trends, we will only keep the closing price on the **last trading day of each month**.\n",
    "\n",
    "Create an sdf `filter_2_stocks_sdf` that contains only the closing prices for the last trading day of each month. Note that a trading day is not simply the last day of each month, as this could be on a weekend when the market is closed . The format of the sdf is shown below:\n",
    "\n",
    "```\n",
    "+----+------------+--------------+\n",
    "|org |date        |closing_price |\n",
    "+----+------------+--------------+\n",
    "|IBM |2000-01-31  |...           |\n",
    "|... |...         |...           |\n",
    "+----+------------+--------------+\n",
    "```\n",
    "\n",
    "  _Hint_: This is a **difficult** question. But if you made it this far, you're a star by now. It may be helpful to create an intermediate dataframe that will help you filter out the specific dates you desire."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "colab": {
     "base_uri": "https://localhost:8080/",
     "height": 17,
     "referenced_widgets": [
      "06d34ff02a2f40acb420053db30d1ce6",
      "8f3ed02d295449deb1a410e319457366",
      "da5370dcfa9e484ebec2232b753f9126"
     ]
    },
    "colab_type": "code",
    "executionInfo": {
     "elapsed": 1008953,
     "status": "ok",
     "timestamp": 1575585560088,
     "user": {
      "displayName": "Leshang Chen",
      "photoUrl": "",
      "userId": "00573172153387237137"
     },
     "user_tz": 300
    },
    "id": "AIx5LUuDD4q_",
    "outputId": "3c69ac55-3b2e-4724-b5b8-783e0a4e737c"
   },
   "outputs": [],
   "source": [
    "%%spark\n",
    "\n",
    "# TODO: Create [filter_2_stocks_sdf]\n",
    "# YOUR CODE HERE\n"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {
    "colab_type": "text",
    "id": "AG4bACKKEQNl"
   },
   "source": [
    "#### Step 3.4.4: The Rambunctious Reshape\n",
    "\n",
    "Now, we will begin to shape our dataframe into the format of the final training sdf.\n",
    "\n",
    "Create an sdf `filter_3_stocks_sdf` that has for a single company and a single year, the closing stock price for the last trading day of each month in that year. This is similar to the table you created in Step 3.1. In this case since we cannot make a proxy for the closing price if the data is not avaliable, drop any rows containing any `null` values, in any column. The format of the sdf is shown below:\n",
    "\n",
    "```\n",
    "+----+-----+----------+---------+----------+\n",
    "|org |year |jan_stock |   ...   |dec_stock |\n",
    "+----+-----+----------+---------+----------+\n",
    "|IBM |2008 |...       |   ...   |...       |\n",
    "|IBM |2009 |...       |   ...   |...       |\n",
    "|... |...  |...       |   ...   |...       |\n",
    "+----+-----+----------+---------+----------+\n",
    "```\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "colab": {
     "base_uri": "https://localhost:8080/",
     "height": 17,
     "referenced_widgets": [
      "09bfe3c82da54132bc0b1de3698f5041",
      "bdf094cb30f741778998082741862013",
      "c6e2b881ee2e4982bb3781c81e46fda5"
     ]
    },
    "colab_type": "code",
    "executionInfo": {
     "elapsed": 1071728,
     "status": "ok",
     "timestamp": 1575585622883,
     "user": {
      "displayName": "Leshang Chen",
      "photoUrl": "",
      "userId": "00573172153387237137"
     },
     "user_tz": 300
    },
    "id": "AucLEgvwIr_0",
    "outputId": "d40ca836-d8d6-4b6e-d6e6-e2a6eb604cd9"
   },
   "outputs": [],
   "source": [
    "%%spark\n",
    "\n",
    "# TODO: Create [filter_3_stocks_sdf]\n",
    "# YOUR CODE HERE\n",
    "\n"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {
    "colab_type": "text",
    "id": "82OQKp-nIulq"
   },
   "source": [
    "#### Step 3.4.5: The Decisive Direction\n",
    "\n",
    "The final element in our training set is the binary output for each case, i.e. the `y` label. \n",
    "\n",
    "Create an sdf `stocks_train_sdf` from `filter_3_stocks_sdf` with an additional column `direction`. This should be the direction of percentage change in the closing stock price, i.e. `1` for positive or `-1` for negative, in the first quarter of a given year. The quarter of a year begins in January and ends in April, inclusive. We want to know the percent change between these two months. Reference Step 2.2 for the percent change formula. The format of the sdf is shown below:\n",
    "\n",
    "```\n",
    "+----+-----+----------+---------+----------+-------------+\n",
    "|org |year |jan_stock |   ...   |dec_stock |direction    |\n",
    "+----+-----+----------+---------+----------+-------------+\n",
    "|IBM |2008 |...       |   ...   |...       |1.0          |\n",
    "|IBM |2009 |...       |   ...   |...       |-1.0         |\n",
    "|... |...  |...       |   ...   |...       |...          |\n",
    "+----+-----+----------+---------+----------+-------------+\n",
    "```"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "colab": {
     "base_uri": "https://localhost:8080/",
     "height": 17,
     "referenced_widgets": [
      "3f7baced569340eb9e13bcce2d6e7834",
      "661ffb43a0b24ce99e3a3bd560dc7f3c",
      "293e6a8149074fa297336901798a3c5a"
     ]
    },
    "colab_type": "code",
    "executionInfo": {
     "elapsed": 1134227,
     "status": "ok",
     "timestamp": 1575585685404,
     "user": {
      "displayName": "Leshang Chen",
      "photoUrl": "",
      "userId": "00573172153387237137"
     },
     "user_tz": 300
    },
    "id": "yEFJIfyZKf7B",
    "outputId": "f24e6eae-c8f3-4336-a5ea-088b5f275dd3"
   },
   "outputs": [],
   "source": [
    "%%spark\n",
    "\n",
    "# TODO: Create [stocks_train_sdf]\n",
    "# YOUR CODE HERE\n"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {
    "colab_type": "text",
    "id": "Fd2nviNpM2dF"
   },
   "source": [
    "### Step 3.5: The Capricious Combination\n",
    "\n",
    "Now that we have individually created the two halfs of our training data we will merge them together to create the final training sdf we showed in the beginning of Step 3.\n",
    "\n",
    "Create an sdf called `training_sdf` in the format of the one shown at the beginning of Step 3. Note that in our definition for the `stock_result` column, the `stock_result` value for a particular year corresponds to the direction of the stock percentage change in the **following** year. For example, the stock_result in the `2008` row for `IBM` will contain the direction of IBM's stock in the first quarter of 2009. The format of the sdf is shown below:\n",
    "```\n",
    "+----+-----+----------+---------+----------+----------+---------+----------+-------------+\n",
    "|org |year |jan_hired |   ...   |dec_hired |jan_stock |   ...   |dec_stock |stock_result |\n",
    "+----+-----+----------+---------+----------+----------+---------+----------+-------------+\n",
    "|IBM |2008 |...       |   ...   |...       |...       |   ...   |...       |-1.0         |\n",
    "|IBM |2009 |...       |   ...   |...       |...       |   ...   |...       |1.0          |\n",
    "|... |...  |...       |   ...   |...       |...       |   ...   |...       |...          |\n",
    "+----+-----+----------+---------+----------+----------+---------+----------+-------------+\n",
    "```"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "colab": {
     "base_uri": "https://localhost:8080/",
     "height": 17,
     "referenced_widgets": [
      "60381de0cb0f4bcbb03a3f077896433a",
      "183ce4cf310845d29208c2565450ead1",
      "745dc77db2ac4dd6838be8dab0a5f66c"
     ]
    },
    "colab_type": "code",
    "executionInfo": {
     "elapsed": 1195073,
     "status": "ok",
     "timestamp": 1575585746267,
     "user": {
      "displayName": "Leshang Chen",
      "photoUrl": "",
      "userId": "00573172153387237137"
     },
     "user_tz": 300
    },
    "id": "8ZIb6QkcO5RB",
    "outputId": "824edeb5-3b1f-4282-fc90-3709e6f214cd"
   },
   "outputs": [],
   "source": [
    "%%spark\n",
    "\n",
    "# TODO: Create [training_sdf]\n",
    "# YOUR CODE HERE\n"
   ]
  }
 ],
 "metadata": {
  "colab": {
   "collapsed_sections": [],
   "machine_shape": "hm",
   "name": " Homework_no_Sol_Remote_New_Data.ipynb",
   "provenance": [
    {
     "file_id": "1V4kiujaAnm3hHMomSK1eDz5jQjdKxmWU",
     "timestamp": 1571083042642
    }
   ],
   "toc_visible": true
  },
  "kernelspec": {
   "display_name": "Python 3",
   "language": "python",
   "name": "python3"
  },
  "language_info": {
   "codemirror_mode": {
    "name": "ipython",
    "version": 3
   },
   "file_extension": ".py",
   "mimetype": "text/x-python",
   "name": "python",
   "nbconvert_exporter": "python",
   "pygments_lexer": "ipython3",
   "version": "3.7.2"
  },
  "widgets": {
   "application/vnd.jupyter.widget-state+json": {
    "00c647594bdf4e86aebd273fffcbcc85": {
     "model_module": "@jupyter-widgets/controls",
     "model_name": "FloatProgressModel",
     "state": {
      "_dom_classes": [],
      "_model_module": "@jupyter-widgets/controls",
      "_model_module_version": "1.5.0",
      "_model_name": "FloatProgressModel",
      "_view_count": null,
      "_view_module": "@jupyter-widgets/controls",
      "_view_module_version": "1.5.0",
      "_view_name": "ProgressView",
      "bar_style": "info",
      "description": "Progress:",
      "description_tooltip": null,
      "layout": "IPY_MODEL_3e73d2733d814879bfb767f462570743",
      "max": 1,
      "min": 0,
      "orientation": "horizontal",
      "style": "IPY_MODEL_8de371ef2a1749558e1a7fe53363d060",
      "value": 0
     }
    },
    "06d34ff02a2f40acb420053db30d1ce6": {
     "model_module": "@jupyter-widgets/controls",
     "model_name": "FloatProgressModel",
     "state": {
      "_dom_classes": [],
      "_model_module": "@jupyter-widgets/controls",
      "_model_module_version": "1.5.0",
      "_model_name": "FloatProgressModel",
      "_view_count": null,
      "_view_module": "@jupyter-widgets/controls",
      "_view_module_version": "1.5.0",
      "_view_name": "ProgressView",
      "bar_style": "info",
      "description": "Progress:",
      "description_tooltip": null,
      "layout": "IPY_MODEL_da5370dcfa9e484ebec2232b753f9126",
      "max": 1,
      "min": 0,
      "orientation": "horizontal",
      "style": "IPY_MODEL_8f3ed02d295449deb1a410e319457366",
      "value": 0
     }
    },
    "09bfe3c82da54132bc0b1de3698f5041": {
     "model_module": "@jupyter-widgets/controls",
     "model_name": "FloatProgressModel",
     "state": {
      "_dom_classes": [],
      "_model_module": "@jupyter-widgets/controls",
      "_model_module_version": "1.5.0",
      "_model_name": "FloatProgressModel",
      "_view_count": null,
      "_view_module": "@jupyter-widgets/controls",
      "_view_module_version": "1.5.0",
      "_view_name": "ProgressView",
      "bar_style": "info",
      "description": "Progress:",
      "description_tooltip": null,
      "layout": "IPY_MODEL_c6e2b881ee2e4982bb3781c81e46fda5",
      "max": 1,
      "min": 0,
      "orientation": "horizontal",
      "style": "IPY_MODEL_bdf094cb30f741778998082741862013",
      "value": 0
     }
    },
    "0b0cf2113c5d46079b10ee9291d81ca2": {
     "model_module": "@jupyter-widgets/controls",
     "model_name": "FloatProgressModel",
     "state": {
      "_dom_classes": [],
      "_model_module": "@jupyter-widgets/controls",
      "_model_module_version": "1.5.0",
      "_model_name": "FloatProgressModel",
      "_view_count": null,
      "_view_module": "@jupyter-widgets/controls",
      "_view_module_version": "1.5.0",
      "_view_name": "ProgressView",
      "bar_style": "info",
      "description": "Progress:",
      "description_tooltip": null,
      "layout": "IPY_MODEL_b2deaafff8174b3cab34294d58e1e27e",
      "max": 1,
      "min": 0,
      "orientation": "horizontal",
      "style": "IPY_MODEL_e904164016e649b39ae675c5316f3718",
      "value": 0
     }
    },
    "0ec3bf246d6241e38d2b68f478bccb36": {
     "model_module": "@jupyter-widgets/controls",
     "model_name": "FloatProgressModel",
     "state": {
      "_dom_classes": [],
      "_model_module": "@jupyter-widgets/controls",
      "_model_module_version": "1.5.0",
      "_model_name": "FloatProgressModel",
      "_view_count": null,
      "_view_module": "@jupyter-widgets/controls",
      "_view_module_version": "1.5.0",
      "_view_name": "ProgressView",
      "bar_style": "info",
      "description": "Progress:",
      "description_tooltip": null,
      "layout": "IPY_MODEL_c81f5783e51f450f8335631023d58bec",
      "max": 1,
      "min": 0,
      "orientation": "horizontal",
      "style": "IPY_MODEL_f848b820820f462f9bd7788a206c6197",
      "value": 0
     }
    },
    "0f19d766cf624efc9a417c72f607368e": {
     "model_module": "@jupyter-widgets/controls",
     "model_name": "ProgressStyleModel",
     "state": {
      "_model_module": "@jupyter-widgets/controls",
      "_model_module_version": "1.5.0",
      "_model_name": "ProgressStyleModel",
      "_view_count": null,
      "_view_module": "@jupyter-widgets/base",
      "_view_module_version": "1.2.0",
      "_view_name": "StyleView",
      "bar_color": null,
      "description_width": ""
     }
    },
    "0f816c10fa484f01b4a14190ade37768": {
     "model_module": "@jupyter-widgets/controls",
     "model_name": "ProgressStyleModel",
     "state": {
      "_model_module": "@jupyter-widgets/controls",
      "_model_module_version": "1.5.0",
      "_model_name": "ProgressStyleModel",
      "_view_count": null,
      "_view_module": "@jupyter-widgets/base",
      "_view_module_version": "1.2.0",
      "_view_name": "StyleView",
      "bar_color": null,
      "description_width": ""
     }
    },
    "10e9bda7da7e48d0845b749ab8e9e9ee": {
     "model_module": "@jupyter-widgets/controls",
     "model_name": "FloatProgressModel",
     "state": {
      "_dom_classes": [],
      "_model_module": "@jupyter-widgets/controls",
      "_model_module_version": "1.5.0",
      "_model_name": "FloatProgressModel",
      "_view_count": null,
      "_view_module": "@jupyter-widgets/controls",
      "_view_module_version": "1.5.0",
      "_view_name": "ProgressView",
      "bar_style": "info",
      "description": "Progress:",
      "description_tooltip": null,
      "layout": "IPY_MODEL_3747c90befbe4e7094faa212b6512361",
      "max": 1,
      "min": 0,
      "orientation": "horizontal",
      "style": "IPY_MODEL_d1a222beb845430abec998d9c6f175ea",
      "value": 0
     }
    },
    "14f008c6d70a41de96a66a9255d28c91": {
     "model_module": "@jupyter-widgets/controls",
     "model_name": "ProgressStyleModel",
     "state": {
      "_model_module": "@jupyter-widgets/controls",
      "_model_module_version": "1.5.0",
      "_model_name": "ProgressStyleModel",
      "_view_count": null,
      "_view_module": "@jupyter-widgets/base",
      "_view_module_version": "1.2.0",
      "_view_name": "StyleView",
      "bar_color": null,
      "description_width": ""
     }
    },
    "183ce4cf310845d29208c2565450ead1": {
     "model_module": "@jupyter-widgets/controls",
     "model_name": "ProgressStyleModel",
     "state": {
      "_model_module": "@jupyter-widgets/controls",
      "_model_module_version": "1.5.0",
      "_model_name": "ProgressStyleModel",
      "_view_count": null,
      "_view_module": "@jupyter-widgets/base",
      "_view_module_version": "1.2.0",
      "_view_name": "StyleView",
      "bar_color": null,
      "description_width": ""
     }
    },
    "18c3f006e61b466abea135b15d4fe624": {
     "model_module": "@jupyter-widgets/controls",
     "model_name": "ProgressStyleModel",
     "state": {
      "_model_module": "@jupyter-widgets/controls",
      "_model_module_version": "1.5.0",
      "_model_name": "ProgressStyleModel",
      "_view_count": null,
      "_view_module": "@jupyter-widgets/base",
      "_view_module_version": "1.2.0",
      "_view_name": "StyleView",
      "bar_color": null,
      "description_width": ""
     }
    },
    "2056868703734a26b1db0e3ffec7699d": {
     "model_module": "@jupyter-widgets/controls",
     "model_name": "ProgressStyleModel",
     "state": {
      "_model_module": "@jupyter-widgets/controls",
      "_model_module_version": "1.5.0",
      "_model_name": "ProgressStyleModel",
      "_view_count": null,
      "_view_module": "@jupyter-widgets/base",
      "_view_module_version": "1.2.0",
      "_view_name": "StyleView",
      "bar_color": null,
      "description_width": ""
     }
    },
    "25c499f20e694a60bd3fc36ab3287402": {
     "model_module": "@jupyter-widgets/controls",
     "model_name": "FloatProgressModel",
     "state": {
      "_dom_classes": [],
      "_model_module": "@jupyter-widgets/controls",
      "_model_module_version": "1.5.0",
      "_model_name": "FloatProgressModel",
      "_view_count": null,
      "_view_module": "@jupyter-widgets/controls",
      "_view_module_version": "1.5.0",
      "_view_name": "ProgressView",
      "bar_style": "info",
      "description": "Progress:",
      "description_tooltip": null,
      "layout": "IPY_MODEL_e2efaeed796f491dac1e80de28d71575",
      "max": 1,
      "min": 0,
      "orientation": "horizontal",
      "style": "IPY_MODEL_a27a5af9ab1e47449f2cb01f298ab010",
      "value": 0
     }
    },
    "277806ae13be4bd2a7e17019f477a79e": {
     "model_module": "@jupyter-widgets/controls",
     "model_name": "FloatProgressModel",
     "state": {
      "_dom_classes": [],
      "_model_module": "@jupyter-widgets/controls",
      "_model_module_version": "1.5.0",
      "_model_name": "FloatProgressModel",
      "_view_count": null,
      "_view_module": "@jupyter-widgets/controls",
      "_view_module_version": "1.5.0",
      "_view_name": "ProgressView",
      "bar_style": "info",
      "description": "Progress:",
      "description_tooltip": null,
      "layout": "IPY_MODEL_cc5799297d594e738a34ed4b83a16fec",
      "max": 1,
      "min": 0,
      "orientation": "horizontal",
      "style": "IPY_MODEL_0f19d766cf624efc9a417c72f607368e",
      "value": 0
     }
    },
    "293e6a8149074fa297336901798a3c5a": {
     "model_module": "@jupyter-widgets/base",
     "model_name": "LayoutModel",
     "state": {
      "_model_module": "@jupyter-widgets/base",
      "_model_module_version": "1.2.0",
      "_model_name": "LayoutModel",
      "_view_count": null,
      "_view_module": "@jupyter-widgets/base",
      "_view_module_version": "1.2.0",
      "_view_name": "LayoutView",
      "align_content": null,
      "align_items": null,
      "align_self": null,
      "border": null,
      "bottom": null,
      "display": null,
      "flex": null,
      "flex_flow": null,
      "grid_area": null,
      "grid_auto_columns": null,
      "grid_auto_flow": null,
      "grid_auto_rows": null,
      "grid_column": null,
      "grid_gap": null,
      "grid_row": null,
      "grid_template_areas": null,
      "grid_template_columns": null,
      "grid_template_rows": null,
      "height": "25px",
      "justify_content": null,
      "justify_items": null,
      "left": null,
      "margin": null,
      "max_height": null,
      "max_width": null,
      "min_height": null,
      "min_width": null,
      "object_fit": null,
      "object_position": null,
      "order": null,
      "overflow": null,
      "overflow_x": null,
      "overflow_y": null,
      "padding": null,
      "right": null,
      "top": null,
      "visibility": null,
      "width": "50%"
     }
    },
    "3747c90befbe4e7094faa212b6512361": {
     "model_module": "@jupyter-widgets/base",
     "model_name": "LayoutModel",
     "state": {
      "_model_module": "@jupyter-widgets/base",
      "_model_module_version": "1.2.0",
      "_model_name": "LayoutModel",
      "_view_count": null,
      "_view_module": "@jupyter-widgets/base",
      "_view_module_version": "1.2.0",
      "_view_name": "LayoutView",
      "align_content": null,
      "align_items": null,
      "align_self": null,
      "border": null,
      "bottom": null,
      "display": null,
      "flex": null,
      "flex_flow": null,
      "grid_area": null,
      "grid_auto_columns": null,
      "grid_auto_flow": null,
      "grid_auto_rows": null,
      "grid_column": null,
      "grid_gap": null,
      "grid_row": null,
      "grid_template_areas": null,
      "grid_template_columns": null,
      "grid_template_rows": null,
      "height": "25px",
      "justify_content": null,
      "justify_items": null,
      "left": null,
      "margin": null,
      "max_height": null,
      "max_width": null,
      "min_height": null,
      "min_width": null,
      "object_fit": null,
      "object_position": null,
      "order": null,
      "overflow": null,
      "overflow_x": null,
      "overflow_y": null,
      "padding": null,
      "right": null,
      "top": null,
      "visibility": null,
      "width": "50%"
     }
    },
    "375176dbd9eb42a29fd34152142f70b3": {
     "model_module": "@jupyter-widgets/controls",
     "model_name": "ProgressStyleModel",
     "state": {
      "_model_module": "@jupyter-widgets/controls",
      "_model_module_version": "1.5.0",
      "_model_name": "ProgressStyleModel",
      "_view_count": null,
      "_view_module": "@jupyter-widgets/base",
      "_view_module_version": "1.2.0",
      "_view_name": "StyleView",
      "bar_color": null,
      "description_width": ""
     }
    },
    "3e73d2733d814879bfb767f462570743": {
     "model_module": "@jupyter-widgets/base",
     "model_name": "LayoutModel",
     "state": {
      "_model_module": "@jupyter-widgets/base",
      "_model_module_version": "1.2.0",
      "_model_name": "LayoutModel",
      "_view_count": null,
      "_view_module": "@jupyter-widgets/base",
      "_view_module_version": "1.2.0",
      "_view_name": "LayoutView",
      "align_content": null,
      "align_items": null,
      "align_self": null,
      "border": null,
      "bottom": null,
      "display": null,
      "flex": null,
      "flex_flow": null,
      "grid_area": null,
      "grid_auto_columns": null,
      "grid_auto_flow": null,
      "grid_auto_rows": null,
      "grid_column": null,
      "grid_gap": null,
      "grid_row": null,
      "grid_template_areas": null,
      "grid_template_columns": null,
      "grid_template_rows": null,
      "height": "25px",
      "justify_content": null,
      "justify_items": null,
      "left": null,
      "margin": null,
      "max_height": null,
      "max_width": null,
      "min_height": null,
      "min_width": null,
      "object_fit": null,
      "object_position": null,
      "order": null,
      "overflow": null,
      "overflow_x": null,
      "overflow_y": null,
      "padding": null,
      "right": null,
      "top": null,
      "visibility": null,
      "width": "50%"
     }
    },
    "3e9b9186606b4aa28ddeee3a5112ab20": {
     "model_module": "@jupyter-widgets/base",
     "model_name": "LayoutModel",
     "state": {
      "_model_module": "@jupyter-widgets/base",
      "_model_module_version": "1.2.0",
      "_model_name": "LayoutModel",
      "_view_count": null,
      "_view_module": "@jupyter-widgets/base",
      "_view_module_version": "1.2.0",
      "_view_name": "LayoutView",
      "align_content": null,
      "align_items": null,
      "align_self": null,
      "border": null,
      "bottom": null,
      "display": null,
      "flex": null,
      "flex_flow": null,
      "grid_area": null,
      "grid_auto_columns": null,
      "grid_auto_flow": null,
      "grid_auto_rows": null,
      "grid_column": null,
      "grid_gap": null,
      "grid_row": null,
      "grid_template_areas": null,
      "grid_template_columns": null,
      "grid_template_rows": null,
      "height": "25px",
      "justify_content": null,
      "justify_items": null,
      "left": null,
      "margin": null,
      "max_height": null,
      "max_width": null,
      "min_height": null,
      "min_width": null,
      "object_fit": null,
      "object_position": null,
      "order": null,
      "overflow": null,
      "overflow_x": null,
      "overflow_y": null,
      "padding": null,
      "right": null,
      "top": null,
      "visibility": null,
      "width": "50%"
     }
    },
    "3f7baced569340eb9e13bcce2d6e7834": {
     "model_module": "@jupyter-widgets/controls",
     "model_name": "FloatProgressModel",
     "state": {
      "_dom_classes": [],
      "_model_module": "@jupyter-widgets/controls",
      "_model_module_version": "1.5.0",
      "_model_name": "FloatProgressModel",
      "_view_count": null,
      "_view_module": "@jupyter-widgets/controls",
      "_view_module_version": "1.5.0",
      "_view_name": "ProgressView",
      "bar_style": "info",
      "description": "Progress:",
      "description_tooltip": null,
      "layout": "IPY_MODEL_293e6a8149074fa297336901798a3c5a",
      "max": 1,
      "min": 0,
      "orientation": "horizontal",
      "style": "IPY_MODEL_661ffb43a0b24ce99e3a3bd560dc7f3c",
      "value": 0
     }
    },
    "472c58675e1e4d2eab714bd7ce9f0df3": {
     "model_module": "@jupyter-widgets/base",
     "model_name": "LayoutModel",
     "state": {
      "_model_module": "@jupyter-widgets/base",
      "_model_module_version": "1.2.0",
      "_model_name": "LayoutModel",
      "_view_count": null,
      "_view_module": "@jupyter-widgets/base",
      "_view_module_version": "1.2.0",
      "_view_name": "LayoutView",
      "align_content": null,
      "align_items": null,
      "align_self": null,
      "border": null,
      "bottom": null,
      "display": null,
      "flex": null,
      "flex_flow": null,
      "grid_area": null,
      "grid_auto_columns": null,
      "grid_auto_flow": null,
      "grid_auto_rows": null,
      "grid_column": null,
      "grid_gap": null,
      "grid_row": null,
      "grid_template_areas": null,
      "grid_template_columns": null,
      "grid_template_rows": null,
      "height": "25px",
      "justify_content": null,
      "justify_items": null,
      "left": null,
      "margin": null,
      "max_height": null,
      "max_width": null,
      "min_height": null,
      "min_width": null,
      "object_fit": null,
      "object_position": null,
      "order": null,
      "overflow": null,
      "overflow_x": null,
      "overflow_y": null,
      "padding": null,
      "right": null,
      "top": null,
      "visibility": null,
      "width": "50%"
     }
    },
    "52815494440f4161b01b6eebb60b999d": {
     "model_module": "@jupyter-widgets/controls",
     "model_name": "FloatProgressModel",
     "state": {
      "_dom_classes": [],
      "_model_module": "@jupyter-widgets/controls",
      "_model_module_version": "1.5.0",
      "_model_name": "FloatProgressModel",
      "_view_count": null,
      "_view_module": "@jupyter-widgets/controls",
      "_view_module_version": "1.5.0",
      "_view_name": "ProgressView",
      "bar_style": "info",
      "description": "Progress:",
      "description_tooltip": null,
      "layout": "IPY_MODEL_8ece5c44cc2c4a25b4fcc641882667a4",
      "max": 1,
      "min": 0,
      "orientation": "horizontal",
      "style": "IPY_MODEL_91880f7960e5464da7893d80df72d483",
      "value": 0
     }
    },
    "5647e8443a5a4d7a9d775c8a2bea87c6": {
     "model_module": "@jupyter-widgets/base",
     "model_name": "LayoutModel",
     "state": {
      "_model_module": "@jupyter-widgets/base",
      "_model_module_version": "1.2.0",
      "_model_name": "LayoutModel",
      "_view_count": null,
      "_view_module": "@jupyter-widgets/base",
      "_view_module_version": "1.2.0",
      "_view_name": "LayoutView",
      "align_content": null,
      "align_items": null,
      "align_self": null,
      "border": null,
      "bottom": null,
      "display": null,
      "flex": null,
      "flex_flow": null,
      "grid_area": null,
      "grid_auto_columns": null,
      "grid_auto_flow": null,
      "grid_auto_rows": null,
      "grid_column": null,
      "grid_gap": null,
      "grid_row": null,
      "grid_template_areas": null,
      "grid_template_columns": null,
      "grid_template_rows": null,
      "height": "25px",
      "justify_content": null,
      "justify_items": null,
      "left": null,
      "margin": null,
      "max_height": null,
      "max_width": null,
      "min_height": null,
      "min_width": null,
      "object_fit": null,
      "object_position": null,
      "order": null,
      "overflow": null,
      "overflow_x": null,
      "overflow_y": null,
      "padding": null,
      "right": null,
      "top": null,
      "visibility": null,
      "width": "50%"
     }
    },
    "59e9ab8b8888436da6023bff00ce7098": {
     "model_module": "@jupyter-widgets/base",
     "model_name": "LayoutModel",
     "state": {
      "_model_module": "@jupyter-widgets/base",
      "_model_module_version": "1.2.0",
      "_model_name": "LayoutModel",
      "_view_count": null,
      "_view_module": "@jupyter-widgets/base",
      "_view_module_version": "1.2.0",
      "_view_name": "LayoutView",
      "align_content": null,
      "align_items": null,
      "align_self": null,
      "border": null,
      "bottom": null,
      "display": null,
      "flex": null,
      "flex_flow": null,
      "grid_area": null,
      "grid_auto_columns": null,
      "grid_auto_flow": null,
      "grid_auto_rows": null,
      "grid_column": null,
      "grid_gap": null,
      "grid_row": null,
      "grid_template_areas": null,
      "grid_template_columns": null,
      "grid_template_rows": null,
      "height": "25px",
      "justify_content": null,
      "justify_items": null,
      "left": null,
      "margin": null,
      "max_height": null,
      "max_width": null,
      "min_height": null,
      "min_width": null,
      "object_fit": null,
      "object_position": null,
      "order": null,
      "overflow": null,
      "overflow_x": null,
      "overflow_y": null,
      "padding": null,
      "right": null,
      "top": null,
      "visibility": null,
      "width": "50%"
     }
    },
    "5b6f6a0b7f8a45949f82869fcae374cb": {
     "model_module": "@jupyter-widgets/controls",
     "model_name": "ProgressStyleModel",
     "state": {
      "_model_module": "@jupyter-widgets/controls",
      "_model_module_version": "1.5.0",
      "_model_name": "ProgressStyleModel",
      "_view_count": null,
      "_view_module": "@jupyter-widgets/base",
      "_view_module_version": "1.2.0",
      "_view_name": "StyleView",
      "bar_color": null,
      "description_width": ""
     }
    },
    "5bea45fa7fc54ebebad0a57be6310c65": {
     "model_module": "@jupyter-widgets/controls",
     "model_name": "ProgressStyleModel",
     "state": {
      "_model_module": "@jupyter-widgets/controls",
      "_model_module_version": "1.5.0",
      "_model_name": "ProgressStyleModel",
      "_view_count": null,
      "_view_module": "@jupyter-widgets/base",
      "_view_module_version": "1.2.0",
      "_view_name": "StyleView",
      "bar_color": null,
      "description_width": ""
     }
    },
    "60381de0cb0f4bcbb03a3f077896433a": {
     "model_module": "@jupyter-widgets/controls",
     "model_name": "FloatProgressModel",
     "state": {
      "_dom_classes": [],
      "_model_module": "@jupyter-widgets/controls",
      "_model_module_version": "1.5.0",
      "_model_name": "FloatProgressModel",
      "_view_count": null,
      "_view_module": "@jupyter-widgets/controls",
      "_view_module_version": "1.5.0",
      "_view_name": "ProgressView",
      "bar_style": "info",
      "description": "Progress:",
      "description_tooltip": null,
      "layout": "IPY_MODEL_745dc77db2ac4dd6838be8dab0a5f66c",
      "max": 1,
      "min": 0,
      "orientation": "horizontal",
      "style": "IPY_MODEL_183ce4cf310845d29208c2565450ead1",
      "value": 0
     }
    },
    "637bd902d20544569334d56244bca5d3": {
     "model_module": "@jupyter-widgets/base",
     "model_name": "LayoutModel",
     "state": {
      "_model_module": "@jupyter-widgets/base",
      "_model_module_version": "1.2.0",
      "_model_name": "LayoutModel",
      "_view_count": null,
      "_view_module": "@jupyter-widgets/base",
      "_view_module_version": "1.2.0",
      "_view_name": "LayoutView",
      "align_content": null,
      "align_items": null,
      "align_self": null,
      "border": null,
      "bottom": null,
      "display": null,
      "flex": null,
      "flex_flow": null,
      "grid_area": null,
      "grid_auto_columns": null,
      "grid_auto_flow": null,
      "grid_auto_rows": null,
      "grid_column": null,
      "grid_gap": null,
      "grid_row": null,
      "grid_template_areas": null,
      "grid_template_columns": null,
      "grid_template_rows": null,
      "height": "25px",
      "justify_content": null,
      "justify_items": null,
      "left": null,
      "margin": null,
      "max_height": null,
      "max_width": null,
      "min_height": null,
      "min_width": null,
      "object_fit": null,
      "object_position": null,
      "order": null,
      "overflow": null,
      "overflow_x": null,
      "overflow_y": null,
      "padding": null,
      "right": null,
      "top": null,
      "visibility": null,
      "width": "50%"
     }
    },
    "661ffb43a0b24ce99e3a3bd560dc7f3c": {
     "model_module": "@jupyter-widgets/controls",
     "model_name": "ProgressStyleModel",
     "state": {
      "_model_module": "@jupyter-widgets/controls",
      "_model_module_version": "1.5.0",
      "_model_name": "ProgressStyleModel",
      "_view_count": null,
      "_view_module": "@jupyter-widgets/base",
      "_view_module_version": "1.2.0",
      "_view_name": "StyleView",
      "bar_color": null,
      "description_width": ""
     }
    },
    "664d2f8b41e8447ba797bbd586cf7442": {
     "model_module": "@jupyter-widgets/controls",
     "model_name": "FloatProgressModel",
     "state": {
      "_dom_classes": [],
      "_model_module": "@jupyter-widgets/controls",
      "_model_module_version": "1.5.0",
      "_model_name": "FloatProgressModel",
      "_view_count": null,
      "_view_module": "@jupyter-widgets/controls",
      "_view_module_version": "1.5.0",
      "_view_name": "ProgressView",
      "bar_style": "info",
      "description": "Progress:",
      "description_tooltip": null,
      "layout": "IPY_MODEL_cac27272dfa64b55b8ac984c8f2e83e7",
      "max": 1,
      "min": 0,
      "orientation": "horizontal",
      "style": "IPY_MODEL_14f008c6d70a41de96a66a9255d28c91",
      "value": 0
     }
    },
    "66aaa398ce5e401b9e1a1d87988e5487": {
     "model_module": "@jupyter-widgets/base",
     "model_name": "LayoutModel",
     "state": {
      "_model_module": "@jupyter-widgets/base",
      "_model_module_version": "1.2.0",
      "_model_name": "LayoutModel",
      "_view_count": null,
      "_view_module": "@jupyter-widgets/base",
      "_view_module_version": "1.2.0",
      "_view_name": "LayoutView",
      "align_content": null,
      "align_items": null,
      "align_self": null,
      "border": null,
      "bottom": null,
      "display": null,
      "flex": null,
      "flex_flow": null,
      "grid_area": null,
      "grid_auto_columns": null,
      "grid_auto_flow": null,
      "grid_auto_rows": null,
      "grid_column": null,
      "grid_gap": null,
      "grid_row": null,
      "grid_template_areas": null,
      "grid_template_columns": null,
      "grid_template_rows": null,
      "height": "25px",
      "justify_content": null,
      "justify_items": null,
      "left": null,
      "margin": null,
      "max_height": null,
      "max_width": null,
      "min_height": null,
      "min_width": null,
      "object_fit": null,
      "object_position": null,
      "order": null,
      "overflow": null,
      "overflow_x": null,
      "overflow_y": null,
      "padding": null,
      "right": null,
      "top": null,
      "visibility": null,
      "width": "50%"
     }
    },
    "6bd5845756c84761a22c4fca0c27e091": {
     "model_module": "@jupyter-widgets/controls",
     "model_name": "FloatProgressModel",
     "state": {
      "_dom_classes": [],
      "_model_module": "@jupyter-widgets/controls",
      "_model_module_version": "1.5.0",
      "_model_name": "FloatProgressModel",
      "_view_count": null,
      "_view_module": "@jupyter-widgets/controls",
      "_view_module_version": "1.5.0",
      "_view_name": "ProgressView",
      "bar_style": "info",
      "description": "Progress:",
      "description_tooltip": null,
      "layout": "IPY_MODEL_637bd902d20544569334d56244bca5d3",
      "max": 1,
      "min": 0,
      "orientation": "horizontal",
      "style": "IPY_MODEL_18c3f006e61b466abea135b15d4fe624",
      "value": 0
     }
    },
    "72ed66a0fe4d476ab6c55a255ae0cad0": {
     "model_module": "@jupyter-widgets/base",
     "model_name": "LayoutModel",
     "state": {
      "_model_module": "@jupyter-widgets/base",
      "_model_module_version": "1.2.0",
      "_model_name": "LayoutModel",
      "_view_count": null,
      "_view_module": "@jupyter-widgets/base",
      "_view_module_version": "1.2.0",
      "_view_name": "LayoutView",
      "align_content": null,
      "align_items": null,
      "align_self": null,
      "border": null,
      "bottom": null,
      "display": null,
      "flex": null,
      "flex_flow": null,
      "grid_area": null,
      "grid_auto_columns": null,
      "grid_auto_flow": null,
      "grid_auto_rows": null,
      "grid_column": null,
      "grid_gap": null,
      "grid_row": null,
      "grid_template_areas": null,
      "grid_template_columns": null,
      "grid_template_rows": null,
      "height": "25px",
      "justify_content": null,
      "justify_items": null,
      "left": null,
      "margin": null,
      "max_height": null,
      "max_width": null,
      "min_height": null,
      "min_width": null,
      "object_fit": null,
      "object_position": null,
      "order": null,
      "overflow": null,
      "overflow_x": null,
      "overflow_y": null,
      "padding": null,
      "right": null,
      "top": null,
      "visibility": null,
      "width": "50%"
     }
    },
    "745dc77db2ac4dd6838be8dab0a5f66c": {
     "model_module": "@jupyter-widgets/base",
     "model_name": "LayoutModel",
     "state": {
      "_model_module": "@jupyter-widgets/base",
      "_model_module_version": "1.2.0",
      "_model_name": "LayoutModel",
      "_view_count": null,
      "_view_module": "@jupyter-widgets/base",
      "_view_module_version": "1.2.0",
      "_view_name": "LayoutView",
      "align_content": null,
      "align_items": null,
      "align_self": null,
      "border": null,
      "bottom": null,
      "display": null,
      "flex": null,
      "flex_flow": null,
      "grid_area": null,
      "grid_auto_columns": null,
      "grid_auto_flow": null,
      "grid_auto_rows": null,
      "grid_column": null,
      "grid_gap": null,
      "grid_row": null,
      "grid_template_areas": null,
      "grid_template_columns": null,
      "grid_template_rows": null,
      "height": "25px",
      "justify_content": null,
      "justify_items": null,
      "left": null,
      "margin": null,
      "max_height": null,
      "max_width": null,
      "min_height": null,
      "min_width": null,
      "object_fit": null,
      "object_position": null,
      "order": null,
      "overflow": null,
      "overflow_x": null,
      "overflow_y": null,
      "padding": null,
      "right": null,
      "top": null,
      "visibility": null,
      "width": "50%"
     }
    },
    "8b3b2f26485c413881059c5affd2055e": {
     "model_module": "@jupyter-widgets/controls",
     "model_name": "FloatProgressModel",
     "state": {
      "_dom_classes": [],
      "_model_module": "@jupyter-widgets/controls",
      "_model_module_version": "1.5.0",
      "_model_name": "FloatProgressModel",
      "_view_count": null,
      "_view_module": "@jupyter-widgets/controls",
      "_view_module_version": "1.5.0",
      "_view_name": "ProgressView",
      "bar_style": "info",
      "description": "Progress:",
      "description_tooltip": null,
      "layout": "IPY_MODEL_b2af543a4da04a53a7080b43eea45486",
      "max": 1,
      "min": 0,
      "orientation": "horizontal",
      "style": "IPY_MODEL_0f816c10fa484f01b4a14190ade37768",
      "value": 0
     }
    },
    "8d520961230c4509820ffc9e698c6cdc": {
     "model_module": "@jupyter-widgets/controls",
     "model_name": "ProgressStyleModel",
     "state": {
      "_model_module": "@jupyter-widgets/controls",
      "_model_module_version": "1.5.0",
      "_model_name": "ProgressStyleModel",
      "_view_count": null,
      "_view_module": "@jupyter-widgets/base",
      "_view_module_version": "1.2.0",
      "_view_name": "StyleView",
      "bar_color": null,
      "description_width": ""
     }
    },
    "8de371ef2a1749558e1a7fe53363d060": {
     "model_module": "@jupyter-widgets/controls",
     "model_name": "ProgressStyleModel",
     "state": {
      "_model_module": "@jupyter-widgets/controls",
      "_model_module_version": "1.5.0",
      "_model_name": "ProgressStyleModel",
      "_view_count": null,
      "_view_module": "@jupyter-widgets/base",
      "_view_module_version": "1.2.0",
      "_view_name": "StyleView",
      "bar_color": null,
      "description_width": ""
     }
    },
    "8ece5c44cc2c4a25b4fcc641882667a4": {
     "model_module": "@jupyter-widgets/base",
     "model_name": "LayoutModel",
     "state": {
      "_model_module": "@jupyter-widgets/base",
      "_model_module_version": "1.2.0",
      "_model_name": "LayoutModel",
      "_view_count": null,
      "_view_module": "@jupyter-widgets/base",
      "_view_module_version": "1.2.0",
      "_view_name": "LayoutView",
      "align_content": null,
      "align_items": null,
      "align_self": null,
      "border": null,
      "bottom": null,
      "display": null,
      "flex": null,
      "flex_flow": null,
      "grid_area": null,
      "grid_auto_columns": null,
      "grid_auto_flow": null,
      "grid_auto_rows": null,
      "grid_column": null,
      "grid_gap": null,
      "grid_row": null,
      "grid_template_areas": null,
      "grid_template_columns": null,
      "grid_template_rows": null,
      "height": "25px",
      "justify_content": null,
      "justify_items": null,
      "left": null,
      "margin": null,
      "max_height": null,
      "max_width": null,
      "min_height": null,
      "min_width": null,
      "object_fit": null,
      "object_position": null,
      "order": null,
      "overflow": null,
      "overflow_x": null,
      "overflow_y": null,
      "padding": null,
      "right": null,
      "top": null,
      "visibility": null,
      "width": "50%"
     }
    },
    "8f3ed02d295449deb1a410e319457366": {
     "model_module": "@jupyter-widgets/controls",
     "model_name": "ProgressStyleModel",
     "state": {
      "_model_module": "@jupyter-widgets/controls",
      "_model_module_version": "1.5.0",
      "_model_name": "ProgressStyleModel",
      "_view_count": null,
      "_view_module": "@jupyter-widgets/base",
      "_view_module_version": "1.2.0",
      "_view_name": "StyleView",
      "bar_color": null,
      "description_width": ""
     }
    },
    "90d37ecc6c6e42d8a7867fcce3980831": {
     "model_module": "@jupyter-widgets/controls",
     "model_name": "FloatProgressModel",
     "state": {
      "_dom_classes": [],
      "_model_module": "@jupyter-widgets/controls",
      "_model_module_version": "1.5.0",
      "_model_name": "FloatProgressModel",
      "_view_count": null,
      "_view_module": "@jupyter-widgets/controls",
      "_view_module_version": "1.5.0",
      "_view_name": "ProgressView",
      "bar_style": "info",
      "description": "Progress:",
      "description_tooltip": null,
      "layout": "IPY_MODEL_3e9b9186606b4aa28ddeee3a5112ab20",
      "max": 1,
      "min": 0,
      "orientation": "horizontal",
      "style": "IPY_MODEL_5b6f6a0b7f8a45949f82869fcae374cb",
      "value": 0
     }
    },
    "91880f7960e5464da7893d80df72d483": {
     "model_module": "@jupyter-widgets/controls",
     "model_name": "ProgressStyleModel",
     "state": {
      "_model_module": "@jupyter-widgets/controls",
      "_model_module_version": "1.5.0",
      "_model_name": "ProgressStyleModel",
      "_view_count": null,
      "_view_module": "@jupyter-widgets/base",
      "_view_module_version": "1.2.0",
      "_view_name": "StyleView",
      "bar_color": null,
      "description_width": ""
     }
    },
    "9b53a3b6b43e46b48004eab3188f52cc": {
     "model_module": "@jupyter-widgets/controls",
     "model_name": "FloatProgressModel",
     "state": {
      "_dom_classes": [],
      "_model_module": "@jupyter-widgets/controls",
      "_model_module_version": "1.5.0",
      "_model_name": "FloatProgressModel",
      "_view_count": null,
      "_view_module": "@jupyter-widgets/controls",
      "_view_module_version": "1.5.0",
      "_view_name": "ProgressView",
      "bar_style": "info",
      "description": "Progress:",
      "description_tooltip": null,
      "layout": "IPY_MODEL_72ed66a0fe4d476ab6c55a255ae0cad0",
      "max": 1,
      "min": 0,
      "orientation": "horizontal",
      "style": "IPY_MODEL_375176dbd9eb42a29fd34152142f70b3",
      "value": 0
     }
    },
    "a27a5af9ab1e47449f2cb01f298ab010": {
     "model_module": "@jupyter-widgets/controls",
     "model_name": "ProgressStyleModel",
     "state": {
      "_model_module": "@jupyter-widgets/controls",
      "_model_module_version": "1.5.0",
      "_model_name": "ProgressStyleModel",
      "_view_count": null,
      "_view_module": "@jupyter-widgets/base",
      "_view_module_version": "1.2.0",
      "_view_name": "StyleView",
      "bar_color": null,
      "description_width": ""
     }
    },
    "b2af543a4da04a53a7080b43eea45486": {
     "model_module": "@jupyter-widgets/base",
     "model_name": "LayoutModel",
     "state": {
      "_model_module": "@jupyter-widgets/base",
      "_model_module_version": "1.2.0",
      "_model_name": "LayoutModel",
      "_view_count": null,
      "_view_module": "@jupyter-widgets/base",
      "_view_module_version": "1.2.0",
      "_view_name": "LayoutView",
      "align_content": null,
      "align_items": null,
      "align_self": null,
      "border": null,
      "bottom": null,
      "display": null,
      "flex": null,
      "flex_flow": null,
      "grid_area": null,
      "grid_auto_columns": null,
      "grid_auto_flow": null,
      "grid_auto_rows": null,
      "grid_column": null,
      "grid_gap": null,
      "grid_row": null,
      "grid_template_areas": null,
      "grid_template_columns": null,
      "grid_template_rows": null,
      "height": "25px",
      "justify_content": null,
      "justify_items": null,
      "left": null,
      "margin": null,
      "max_height": null,
      "max_width": null,
      "min_height": null,
      "min_width": null,
      "object_fit": null,
      "object_position": null,
      "order": null,
      "overflow": null,
      "overflow_x": null,
      "overflow_y": null,
      "padding": null,
      "right": null,
      "top": null,
      "visibility": null,
      "width": "50%"
     }
    },
    "b2deaafff8174b3cab34294d58e1e27e": {
     "model_module": "@jupyter-widgets/base",
     "model_name": "LayoutModel",
     "state": {
      "_model_module": "@jupyter-widgets/base",
      "_model_module_version": "1.2.0",
      "_model_name": "LayoutModel",
      "_view_count": null,
      "_view_module": "@jupyter-widgets/base",
      "_view_module_version": "1.2.0",
      "_view_name": "LayoutView",
      "align_content": null,
      "align_items": null,
      "align_self": null,
      "border": null,
      "bottom": null,
      "display": null,
      "flex": null,
      "flex_flow": null,
      "grid_area": null,
      "grid_auto_columns": null,
      "grid_auto_flow": null,
      "grid_auto_rows": null,
      "grid_column": null,
      "grid_gap": null,
      "grid_row": null,
      "grid_template_areas": null,
      "grid_template_columns": null,
      "grid_template_rows": null,
      "height": "25px",
      "justify_content": null,
      "justify_items": null,
      "left": null,
      "margin": null,
      "max_height": null,
      "max_width": null,
      "min_height": null,
      "min_width": null,
      "object_fit": null,
      "object_position": null,
      "order": null,
      "overflow": null,
      "overflow_x": null,
      "overflow_y": null,
      "padding": null,
      "right": null,
      "top": null,
      "visibility": null,
      "width": "50%"
     }
    },
    "bdf094cb30f741778998082741862013": {
     "model_module": "@jupyter-widgets/controls",
     "model_name": "ProgressStyleModel",
     "state": {
      "_model_module": "@jupyter-widgets/controls",
      "_model_module_version": "1.5.0",
      "_model_name": "ProgressStyleModel",
      "_view_count": null,
      "_view_module": "@jupyter-widgets/base",
      "_view_module_version": "1.2.0",
      "_view_name": "StyleView",
      "bar_color": null,
      "description_width": ""
     }
    },
    "c6e2b881ee2e4982bb3781c81e46fda5": {
     "model_module": "@jupyter-widgets/base",
     "model_name": "LayoutModel",
     "state": {
      "_model_module": "@jupyter-widgets/base",
      "_model_module_version": "1.2.0",
      "_model_name": "LayoutModel",
      "_view_count": null,
      "_view_module": "@jupyter-widgets/base",
      "_view_module_version": "1.2.0",
      "_view_name": "LayoutView",
      "align_content": null,
      "align_items": null,
      "align_self": null,
      "border": null,
      "bottom": null,
      "display": null,
      "flex": null,
      "flex_flow": null,
      "grid_area": null,
      "grid_auto_columns": null,
      "grid_auto_flow": null,
      "grid_auto_rows": null,
      "grid_column": null,
      "grid_gap": null,
      "grid_row": null,
      "grid_template_areas": null,
      "grid_template_columns": null,
      "grid_template_rows": null,
      "height": "25px",
      "justify_content": null,
      "justify_items": null,
      "left": null,
      "margin": null,
      "max_height": null,
      "max_width": null,
      "min_height": null,
      "min_width": null,
      "object_fit": null,
      "object_position": null,
      "order": null,
      "overflow": null,
      "overflow_x": null,
      "overflow_y": null,
      "padding": null,
      "right": null,
      "top": null,
      "visibility": null,
      "width": "50%"
     }
    },
    "c81f5783e51f450f8335631023d58bec": {
     "model_module": "@jupyter-widgets/base",
     "model_name": "LayoutModel",
     "state": {
      "_model_module": "@jupyter-widgets/base",
      "_model_module_version": "1.2.0",
      "_model_name": "LayoutModel",
      "_view_count": null,
      "_view_module": "@jupyter-widgets/base",
      "_view_module_version": "1.2.0",
      "_view_name": "LayoutView",
      "align_content": null,
      "align_items": null,
      "align_self": null,
      "border": null,
      "bottom": null,
      "display": null,
      "flex": null,
      "flex_flow": null,
      "grid_area": null,
      "grid_auto_columns": null,
      "grid_auto_flow": null,
      "grid_auto_rows": null,
      "grid_column": null,
      "grid_gap": null,
      "grid_row": null,
      "grid_template_areas": null,
      "grid_template_columns": null,
      "grid_template_rows": null,
      "height": "25px",
      "justify_content": null,
      "justify_items": null,
      "left": null,
      "margin": null,
      "max_height": null,
      "max_width": null,
      "min_height": null,
      "min_width": null,
      "object_fit": null,
      "object_position": null,
      "order": null,
      "overflow": null,
      "overflow_x": null,
      "overflow_y": null,
      "padding": null,
      "right": null,
      "top": null,
      "visibility": null,
      "width": "50%"
     }
    },
    "cac27272dfa64b55b8ac984c8f2e83e7": {
     "model_module": "@jupyter-widgets/base",
     "model_name": "LayoutModel",
     "state": {
      "_model_module": "@jupyter-widgets/base",
      "_model_module_version": "1.2.0",
      "_model_name": "LayoutModel",
      "_view_count": null,
      "_view_module": "@jupyter-widgets/base",
      "_view_module_version": "1.2.0",
      "_view_name": "LayoutView",
      "align_content": null,
      "align_items": null,
      "align_self": null,
      "border": null,
      "bottom": null,
      "display": null,
      "flex": null,
      "flex_flow": null,
      "grid_area": null,
      "grid_auto_columns": null,
      "grid_auto_flow": null,
      "grid_auto_rows": null,
      "grid_column": null,
      "grid_gap": null,
      "grid_row": null,
      "grid_template_areas": null,
      "grid_template_columns": null,
      "grid_template_rows": null,
      "height": "25px",
      "justify_content": null,
      "justify_items": null,
      "left": null,
      "margin": null,
      "max_height": null,
      "max_width": null,
      "min_height": null,
      "min_width": null,
      "object_fit": null,
      "object_position": null,
      "order": null,
      "overflow": null,
      "overflow_x": null,
      "overflow_y": null,
      "padding": null,
      "right": null,
      "top": null,
      "visibility": null,
      "width": "50%"
     }
    },
    "cc5799297d594e738a34ed4b83a16fec": {
     "model_module": "@jupyter-widgets/base",
     "model_name": "LayoutModel",
     "state": {
      "_model_module": "@jupyter-widgets/base",
      "_model_module_version": "1.2.0",
      "_model_name": "LayoutModel",
      "_view_count": null,
      "_view_module": "@jupyter-widgets/base",
      "_view_module_version": "1.2.0",
      "_view_name": "LayoutView",
      "align_content": null,
      "align_items": null,
      "align_self": null,
      "border": null,
      "bottom": null,
      "display": null,
      "flex": null,
      "flex_flow": null,
      "grid_area": null,
      "grid_auto_columns": null,
      "grid_auto_flow": null,
      "grid_auto_rows": null,
      "grid_column": null,
      "grid_gap": null,
      "grid_row": null,
      "grid_template_areas": null,
      "grid_template_columns": null,
      "grid_template_rows": null,
      "height": "25px",
      "justify_content": null,
      "justify_items": null,
      "left": null,
      "margin": null,
      "max_height": null,
      "max_width": null,
      "min_height": null,
      "min_width": null,
      "object_fit": null,
      "object_position": null,
      "order": null,
      "overflow": null,
      "overflow_x": null,
      "overflow_y": null,
      "padding": null,
      "right": null,
      "top": null,
      "visibility": null,
      "width": "50%"
     }
    },
    "cfecaa14eb514313a5f15ed03ef6789d": {
     "model_module": "@jupyter-widgets/controls",
     "model_name": "FloatProgressModel",
     "state": {
      "_dom_classes": [],
      "_model_module": "@jupyter-widgets/controls",
      "_model_module_version": "1.5.0",
      "_model_name": "FloatProgressModel",
      "_view_count": null,
      "_view_module": "@jupyter-widgets/controls",
      "_view_module_version": "1.5.0",
      "_view_name": "ProgressView",
      "bar_style": "info",
      "description": "Progress:",
      "description_tooltip": null,
      "layout": "IPY_MODEL_472c58675e1e4d2eab714bd7ce9f0df3",
      "max": 1,
      "min": 0,
      "orientation": "horizontal",
      "style": "IPY_MODEL_5bea45fa7fc54ebebad0a57be6310c65",
      "value": 0
     }
    },
    "d1a222beb845430abec998d9c6f175ea": {
     "model_module": "@jupyter-widgets/controls",
     "model_name": "ProgressStyleModel",
     "state": {
      "_model_module": "@jupyter-widgets/controls",
      "_model_module_version": "1.5.0",
      "_model_name": "ProgressStyleModel",
      "_view_count": null,
      "_view_module": "@jupyter-widgets/base",
      "_view_module_version": "1.2.0",
      "_view_name": "StyleView",
      "bar_color": null,
      "description_width": ""
     }
    },
    "da5370dcfa9e484ebec2232b753f9126": {
     "model_module": "@jupyter-widgets/base",
     "model_name": "LayoutModel",
     "state": {
      "_model_module": "@jupyter-widgets/base",
      "_model_module_version": "1.2.0",
      "_model_name": "LayoutModel",
      "_view_count": null,
      "_view_module": "@jupyter-widgets/base",
      "_view_module_version": "1.2.0",
      "_view_name": "LayoutView",
      "align_content": null,
      "align_items": null,
      "align_self": null,
      "border": null,
      "bottom": null,
      "display": null,
      "flex": null,
      "flex_flow": null,
      "grid_area": null,
      "grid_auto_columns": null,
      "grid_auto_flow": null,
      "grid_auto_rows": null,
      "grid_column": null,
      "grid_gap": null,
      "grid_row": null,
      "grid_template_areas": null,
      "grid_template_columns": null,
      "grid_template_rows": null,
      "height": "25px",
      "justify_content": null,
      "justify_items": null,
      "left": null,
      "margin": null,
      "max_height": null,
      "max_width": null,
      "min_height": null,
      "min_width": null,
      "object_fit": null,
      "object_position": null,
      "order": null,
      "overflow": null,
      "overflow_x": null,
      "overflow_y": null,
      "padding": null,
      "right": null,
      "top": null,
      "visibility": null,
      "width": "50%"
     }
    },
    "dc7c0513d85b4643bdbfc7bf8702e856": {
     "model_module": "@jupyter-widgets/controls",
     "model_name": "FloatProgressModel",
     "state": {
      "_dom_classes": [],
      "_model_module": "@jupyter-widgets/controls",
      "_model_module_version": "1.5.0",
      "_model_name": "FloatProgressModel",
      "_view_count": null,
      "_view_module": "@jupyter-widgets/controls",
      "_view_module_version": "1.5.0",
      "_view_name": "ProgressView",
      "bar_style": "info",
      "description": "Progress:",
      "description_tooltip": null,
      "layout": "IPY_MODEL_66aaa398ce5e401b9e1a1d87988e5487",
      "max": 1,
      "min": 0,
      "orientation": "horizontal",
      "style": "IPY_MODEL_2056868703734a26b1db0e3ffec7699d",
      "value": 0.9798657718120806
     }
    },
    "defbbe69eeb44fcbba983d981a4521fe": {
     "model_module": "@jupyter-widgets/controls",
     "model_name": "ProgressStyleModel",
     "state": {
      "_model_module": "@jupyter-widgets/controls",
      "_model_module_version": "1.5.0",
      "_model_name": "ProgressStyleModel",
      "_view_count": null,
      "_view_module": "@jupyter-widgets/base",
      "_view_module_version": "1.2.0",
      "_view_name": "StyleView",
      "bar_color": null,
      "description_width": ""
     }
    },
    "e2efaeed796f491dac1e80de28d71575": {
     "model_module": "@jupyter-widgets/base",
     "model_name": "LayoutModel",
     "state": {
      "_model_module": "@jupyter-widgets/base",
      "_model_module_version": "1.2.0",
      "_model_name": "LayoutModel",
      "_view_count": null,
      "_view_module": "@jupyter-widgets/base",
      "_view_module_version": "1.2.0",
      "_view_name": "LayoutView",
      "align_content": null,
      "align_items": null,
      "align_self": null,
      "border": null,
      "bottom": null,
      "display": null,
      "flex": null,
      "flex_flow": null,
      "grid_area": null,
      "grid_auto_columns": null,
      "grid_auto_flow": null,
      "grid_auto_rows": null,
      "grid_column": null,
      "grid_gap": null,
      "grid_row": null,
      "grid_template_areas": null,
      "grid_template_columns": null,
      "grid_template_rows": null,
      "height": "25px",
      "justify_content": null,
      "justify_items": null,
      "left": null,
      "margin": null,
      "max_height": null,
      "max_width": null,
      "min_height": null,
      "min_width": null,
      "object_fit": null,
      "object_position": null,
      "order": null,
      "overflow": null,
      "overflow_x": null,
      "overflow_y": null,
      "padding": null,
      "right": null,
      "top": null,
      "visibility": null,
      "width": "50%"
     }
    },
    "e904164016e649b39ae675c5316f3718": {
     "model_module": "@jupyter-widgets/controls",
     "model_name": "ProgressStyleModel",
     "state": {
      "_model_module": "@jupyter-widgets/controls",
      "_model_module_version": "1.5.0",
      "_model_name": "ProgressStyleModel",
      "_view_count": null,
      "_view_module": "@jupyter-widgets/base",
      "_view_module_version": "1.2.0",
      "_view_name": "StyleView",
      "bar_color": null,
      "description_width": ""
     }
    },
    "f52ae0051fc24086995d05461b02229d": {
     "model_module": "@jupyter-widgets/controls",
     "model_name": "FloatProgressModel",
     "state": {
      "_dom_classes": [],
      "_model_module": "@jupyter-widgets/controls",
      "_model_module_version": "1.5.0",
      "_model_name": "FloatProgressModel",
      "_view_count": null,
      "_view_module": "@jupyter-widgets/controls",
      "_view_module_version": "1.5.0",
      "_view_name": "ProgressView",
      "bar_style": "info",
      "description": "Progress:",
      "description_tooltip": null,
      "layout": "IPY_MODEL_5647e8443a5a4d7a9d775c8a2bea87c6",
      "max": 1,
      "min": 0,
      "orientation": "horizontal",
      "style": "IPY_MODEL_defbbe69eeb44fcbba983d981a4521fe",
      "value": 0
     }
    },
    "f848b820820f462f9bd7788a206c6197": {
     "model_module": "@jupyter-widgets/controls",
     "model_name": "ProgressStyleModel",
     "state": {
      "_model_module": "@jupyter-widgets/controls",
      "_model_module_version": "1.5.0",
      "_model_name": "ProgressStyleModel",
      "_view_count": null,
      "_view_module": "@jupyter-widgets/base",
      "_view_module_version": "1.2.0",
      "_view_name": "StyleView",
      "bar_color": null,
      "description_width": ""
     }
    },
    "fc9a5ea9104f4def9166d3a62dff697a": {
     "model_module": "@jupyter-widgets/controls",
     "model_name": "FloatProgressModel",
     "state": {
      "_dom_classes": [],
      "_model_module": "@jupyter-widgets/controls",
      "_model_module_version": "1.5.0",
      "_model_name": "FloatProgressModel",
      "_view_count": null,
      "_view_module": "@jupyter-widgets/controls",
      "_view_module_version": "1.5.0",
      "_view_name": "ProgressView",
      "bar_style": "info",
      "description": "Progress:",
      "description_tooltip": null,
      "layout": "IPY_MODEL_59e9ab8b8888436da6023bff00ce7098",
      "max": 1,
      "min": 0,
      "orientation": "horizontal",
      "style": "IPY_MODEL_8d520961230c4509820ffc9e698c6cdc",
      "value": 0
     }
    }
   }
  }
 },
 "nbformat": 4,
 "nbformat_minor": 1
}
>>>>>>> eb2722c820aa9f38027d69767629ac9ff02010f2