{"metadata":{"kernelspec":{"language":"python","display_name":"Python 3","name":"python3"},"language_info":{"pygments_lexer":"ipython3","nbconvert_exporter":"python","version":"3.6.4","file_extension":".py","codemirror_mode":{"name":"ipython","version":3},"name":"python","mimetype":"text/x-python"}},"nbformat_minor":4,"nbformat":4,"cells":[{"cell_type":"markdown","source":"# INTRODUCTION\n\nNelson Mandela believed education was the most powerful weapon to change the world. But not every student has equal opportunities to learn. Effective policies and plans need to be enacted in order to make education more equitable—and perhaps your innovative data analysis will help reveal the solution.\n\nCurrent research shows educational outcomes are far from equitable. The imbalance was exacerbated by the COVID-19 pandemic. There's an urgent need to better understand and measure the scope and impact of the pandemic on these inequities.\n\nEducation technology company LearnPlatform was founded in 2014 with a mission to expand equitable access to education technology for all students and teachers. LearnPlatform’s comprehensive edtech effectiveness system is used by districts and states to continuously improve the safety, equity, and effectiveness of their educational technology. LearnPlatform does so by generating an evidence basis for what’s working and enacting it to benefit students, teachers, and budgets.\n\nIn this analytics competition, you’ll work to uncover trends in digital learning. Accomplish this with data analysis about how engagement with digital learning relates to factors like district demographics, broadband access, and state/national level policies and events. Then, submit a Kaggle Notebook to propose your best solution to these educational inequities.\n\nYour submissions will inform policies and practices that close the digital divide. With a better understanding of digital learning trends, you may help reverse the long-term learning loss among America’s most vulnerable, making education more equitable.","metadata":{"_uuid":"8f2839f25d086af736a60e9eeb907d3b93b6e0e5","_cell_guid":"b1076dfc-b9ad-4769-8c92-a6c4dae69d19"}},{"cell_type":"markdown","source":"# PROBLEM STATEMENT\n\nThe COVID-19 Pandemic has disrupted learning for more than 56 million students in the United States. In the Spring of 2020, most states and local governments across the U.S. closed educational institutions to stop the spread of the virus. In response, schools and teachers have attempted to reach students remotely through distance learning tools and digital platforms. Until today, concerns of the exacaberting digital divide and long-term learning loss among America’s most vulnerable learners continue to grow.","metadata":{}},{"cell_type":"markdown","source":"# DATA PREPROCSSING\n\nWe are preparing packages and source data that will be used in the analysis process. Python packages that will be used in the analysis mainly are for data manipulation (numpy and pandas) and data visualization (matplotlib and seaborn). ","metadata":{}},{"cell_type":"code","source":"import os\nimport glob\nimport numpy as np\nimport pandas as pd\nimport warnings\n\nimport squarify\nimport matplotlib\nimport matplotlib.pyplot as plt\nimport matplotlib.dates as mdates\nfrom matplotlib import ticker\nimport seaborn as sns\n\n# setting up options\npd.set_option('display.max_rows', None)\npd.set_option('display.max_columns', None)\npd.set_option('float_format', '{:f}'.format)\nwarnings.filterwarnings('ignore')\n\n#loading dataset\ndistricts = pd.read_csv('/kaggle/input/learnplatform-covid19-impact-on-digital-learning/districts_info.csv')\nproducts = pd.read_csv('/kaggle/input/learnplatform-covid19-impact-on-digital-learning/products_info.csv')\n\nfor dirname, _, filenames in os.walk('/kaggle/input/learnplatform-covid19-impact-on-digital-learning/engagement_data'):\n    for filename in filenames:\n        engagement_files = list(glob.glob(os.path.join(dirname,'*.*')))\n\nengagement = pd.DataFrame()\nfor file in engagement_files:\n    district_id = file[79:83]\n    engagement_file = pd.read_csv(file)\n    engagement_file['id'] = district_id\n    engagement = pd.concat([engagement, engagement_file], axis=0).reset_index(drop=True)\n\n#mapping for districts dataset\nmapping_1 = {\n    '[0, 0.2[': '0%-20%',\n    '[0.2, 0.4[': '20%-40%',\n    '[0.4, 0.6[': '40%-60%',\n    '[0.6, 0.8[': '60%-80%',\n    '[0.8, 1[': '80%-100%'}\n\nmapping_2 = {\n    '[4000, 6000[': '4000-6000',\n    '[6000, 8000[': '6000-8000',\n    '[8000, 10000[': '8000-10000',\n    '[10000, 12000[': '10000-12000',\n    '[12000, 14000[': '12000-14000',\n    '[14000, 16000[': '14000-16000',\n    '[16000, 18000[': '16000-18000',\n    '[18000, 20000[': '18000-20000',\n    '[20000, 22000[': '20000-22000',\n    '[22000, 24000[': '22000-24000',\n    '[32000, 34000[': '32000-34000'}\n\nmapping_3 = {\n    '[0.18, 1[': '18%-100%',\n    '[1, 2[': '100%-200%'\n}\n\ndistricts['pct_black/hispanic'] = districts['pct_black/hispanic'].map(mapping_1)\ndistricts['pct_free/reduced'] = districts['pct_free/reduced'].map(mapping_1)\ndistricts['county_connections_ratio'] = districts['county_connections_ratio'].map(mapping_3)\ndistricts['pp_total_raw'] = districts['pp_total_raw'].map(mapping_2)\n\n#separating category\nproducts[['Category', 'Subcategory']] = products['Primary Essential Function'].str.split('-', n=1, expand=True,)\nproducts = products.drop('Primary Essential Function', axis=1)","metadata":{"_kg_hide-input":true,"execution":{"iopub.status.busy":"2022-07-11T06:53:50.469296Z","iopub.execute_input":"2022-07-11T06:53:50.470224Z","iopub.status.idle":"2022-07-11T07:02:05.922336Z","shell.execute_reply.started":"2022-07-11T06:53:50.470103Z","shell.execute_reply":"2022-07-11T07:02:05.919611Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"code","source":"import seaborn as sns\ncolors_blue = [\"#132C33\", \"#264D58\", '#17869E', '#51C4D3', '#B4DBE9']\ncolors_dark = [\"#1F1F1F\", \"#313131\", '#636363', '#AEAEAE', '#DADADA']\ncolors_red = [\"#331313\", \"#582626\", '#9E1717', '#D35151', '#E9B4B4']\ncolors_mix = [\"#17869E\", '#264D58', '#179E66', '#D35151', '#E9DAB4', '#E9B4B4', '#D3B651', '#6351D3']\ncolors_div = [\"#132C33\", '#17869E', '#DADADA', '#D35151', '#331313']\n\nsns.palplot(colors_blue)\nsns.palplot(colors_dark)\nsns.palplot(colors_red)\nsns.palplot(colors_mix)\nsns.palplot(colors_div)","metadata":{"_kg_hide-input":true,"execution":{"iopub.status.busy":"2022-07-11T07:02:05.925898Z","iopub.execute_input":"2022-07-11T07:02:05.926337Z","iopub.status.idle":"2022-07-11T07:02:06.237623Z","shell.execute_reply.started":"2022-07-11T07:02:05.926284Z","shell.execute_reply":"2022-07-11T07:02:06.236868Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"markdown","source":"# DATA SET OVERVIEW\n\nThe overview is prepared to get the feel on data structure. It will also include a quick analysis on missing values, basic statistics and data manipulation. In general there will 3 datasets: engagement, districts and products","metadata":{}},{"cell_type":"markdown","source":"# ENGAGEMENT\n\nThe engagement data are aggregated at school district level, and each file represents data from one school district. The 4-digit file name represents district_id which can be used to link to district information in district_info. The lp_id can be used to link to product information in product_info.\n\nThis dataset consists of below information:\n\n--> **time:** date in \"YYYY-MM-DD\"\n\n--> **lp_id:** The unique identifier of the product\n\n--> **pct_access:** Percentage of students in the district have at least one page-load event of a given product and on a given day\n\n--> **engagement_index:** Total page-load events per one thousand students of a given product and on a given day\n\n**Observations:**\n\n--> There are 22,324,190 rows with 5 columns as mentioned above.\n\n--> This dataset contain missing value of 5,392,397 which come from lp_id of 541, pct_access of 13,447 and engagement_index 5,378,409. Missing value in the engagement_index can be considered big as it consist of 24.15% from total observation.","metadata":{}},{"cell_type":"code","source":"engagement.head()","metadata":{"_kg_hide-input":true,"execution":{"iopub.status.busy":"2022-07-11T07:02:06.239221Z","iopub.execute_input":"2022-07-11T07:02:06.239622Z","iopub.status.idle":"2022-07-11T07:02:06.257062Z","shell.execute_reply.started":"2022-07-11T07:02:06.239584Z","shell.execute_reply":"2022-07-11T07:02:06.256145Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"code","source":"print(f'Number of rows: {engagement.shape[0]};  Number of columns: {engagement.shape[1]}; No of missing values: {sum(engagement.isna().sum())}')","metadata":{"_kg_hide-input":true,"execution":{"iopub.status.busy":"2022-07-11T07:02:06.260224Z","iopub.execute_input":"2022-07-11T07:02:06.260766Z","iopub.status.idle":"2022-07-11T07:02:08.261084Z","shell.execute_reply.started":"2022-07-11T07:02:06.260736Z","shell.execute_reply":"2022-07-11T07:02:08.259922Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"code","source":"print('Number of missing Values in every column:')\nprint(engagement.isna().sum())","metadata":{"execution":{"iopub.status.busy":"2022-07-11T07:02:08.262467Z","iopub.execute_input":"2022-07-11T07:02:08.262802Z","iopub.status.idle":"2022-07-11T07:02:10.269907Z","shell.execute_reply.started":"2022-07-11T07:02:08.262773Z","shell.execute_reply":"2022-07-11T07:02:10.268082Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"markdown","source":"# BASIC STATISTICS\n\nBelow is the basic statistics for each variables which contain information on count, mean, standard deviation, minimum, 1st quartile, median, 3rd quartile and maximum.","metadata":{}},{"cell_type":"code","source":"engagement.describe()","metadata":{"execution":{"iopub.status.busy":"2022-07-11T07:02:10.271830Z","iopub.execute_input":"2022-07-11T07:02:10.272473Z","iopub.status.idle":"2022-07-11T07:02:15.345333Z","shell.execute_reply.started":"2022-07-11T07:02:10.272428Z","shell.execute_reply":"2022-07-11T07:02:15.344299Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"markdown","source":"# DISTRICTS\n\nThe district file includes information about the characteristics of school districts, including data from NCES (2018-19), FCC (Dec 2018), and Edunomics Lab. In this data set, LearnPlatform removed the identifiable information about the school districts. LearnPlatform also used an open source tool ARX (Prasser et al. 2020) to transform several data fields and reduce the risks of re-identification. For data generalization purposes some data points are released with a range where the actual value falls under. Additionally, there are many missing data marked as 'NaN' indicating that the data was suppressed to maximize anonymization of the dataset.\n\nThis dataset consists of below information:\n\n-->**** district_id:** The unique identifier of the school district\n\n--> **state:** The state where the district resides in\n\n--> **locale:** NCES locale classification that categorizes U.S. territory into four types of areas: City, Suburban, Town, and Rural.\n\n--> **pct_black/hispanic:** Percentage of students in the districts identified as Black or Hispanic based on 2018-19 NCES data.\n\n\n--> **pct_free/reduced:** Percentage of students in the districts eligible for free or reduced-price lunch based on 2018-19 NCES data\n\n--> **countyconnectionsratio:** ratio (residential fixed high-speed connections over 200 kbps in at least one direction/households) based on the county level data from FCC From 477 (December 2018 version).\n\n--> **pptotalraw:** Per-pupil total expenditure (sum of local and federal expenditure) from Edunomics Lab's National Education Resource - Database on Schools (NERD$) project.\nThe expenditure data are school-by-school, and we use the median value to represent the expenditure of a given school district.\n\n**OBSERVATIONS:**\n\nThere are 223 rows with 7 columns as mentioned above.\nThis dataset contain missing value of 442 which mainly come from pp_total_raw of 115, pct_free/reduced of 85 and county_connections_ratio of 71.","metadata":{}},{"cell_type":"code","source":"districts.head()","metadata":{"execution":{"iopub.status.busy":"2022-07-11T07:02:15.346657Z","iopub.execute_input":"2022-07-11T07:02:15.346973Z","iopub.status.idle":"2022-07-11T07:02:15.359580Z","shell.execute_reply.started":"2022-07-11T07:02:15.346946Z","shell.execute_reply":"2022-07-11T07:02:15.358457Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"code","source":"print(f'Number of rows: {districts.shape[0]};  Number of columns: {districts.shape[1]}; No of missing values: {sum(districts.isna().sum())}')","metadata":{"execution":{"iopub.status.busy":"2022-07-11T07:02:15.361294Z","iopub.execute_input":"2022-07-11T07:02:15.361591Z","iopub.status.idle":"2022-07-11T07:02:15.373645Z","shell.execute_reply.started":"2022-07-11T07:02:15.361564Z","shell.execute_reply":"2022-07-11T07:02:15.372852Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"code","source":"print('Number of missing Values in every column:')\nprint(districts.isna().sum())","metadata":{"execution":{"iopub.status.busy":"2022-07-11T07:02:15.375019Z","iopub.execute_input":"2022-07-11T07:02:15.375602Z","iopub.status.idle":"2022-07-11T07:02:15.385854Z","shell.execute_reply.started":"2022-07-11T07:02:15.375572Z","shell.execute_reply":"2022-07-11T07:02:15.385112Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"code","source":"plt.figure(figsize=(16, 10))\n\nsns.countplot(y=\"state\",data=districts,order=districts.state.value_counts().index,palette=\"pastel\",linewidth=3)\nplt.title(\"State Distribution\",size=18)\n\nsns.despine()\nplt.show()","metadata":{"_kg_hide-input":true,"execution":{"iopub.status.busy":"2022-07-11T07:02:15.389491Z","iopub.execute_input":"2022-07-11T07:02:15.390041Z","iopub.status.idle":"2022-07-11T07:02:15.656529Z","shell.execute_reply.started":"2022-07-11T07:02:15.390008Z","shell.execute_reply":"2022-07-11T07:02:15.655455Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"markdown","source":"As seen in the graph above, we know that Connecticut has the Most district representation followed by Utah.","metadata":{}},{"cell_type":"code","source":"fig, ax  = plt.subplots(figsize=(8, 5))\nfig.suptitle('Locale Type Distribution', size = 5)\n\nlabels = list(districts.locale.value_counts().index)\nsizes = districts.locale.value_counts().values\nexplode = (0, 0, 0, 0.1)\n\nax.pie(sizes, explode=explode,startangle=60, labels=labels,autopct='%1.0f%%', pctdistance=0.7, colors=[\"#FFFF33\",\"#ff9100\",\"#eaaa00\",\"#6d6875\"])\nax.add_artist(plt.Circle((0,0),0.3,fc='white'))\nplt.show()","metadata":{"_kg_hide-input":true,"execution":{"iopub.status.busy":"2022-07-11T07:02:15.657912Z","iopub.execute_input":"2022-07-11T07:02:15.658542Z","iopub.status.idle":"2022-07-11T07:02:15.768411Z","shell.execute_reply.started":"2022-07-11T07:02:15.658510Z","shell.execute_reply":"2022-07-11T07:02:15.767174Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"markdown","source":"While making the analysis of the Locale from the District data set \nWe came to know that the suburb(an outlying district of a city, especially a residential one) is most locale representation while town is least locale representation","metadata":{}},{"cell_type":"markdown","source":"# PRODUCTS\n\nThe product file includes information about the characteristics of the top 372 products with most users in 2020. The categories listed in this file are part of LearnPlatform's product taxonomy. Data were labeled by LearnPlatform team. Some products may not have labels due to being duplicate, lack of accurate url or other reasons.\n\nThis dataset consists of below information:\n\n--> **LP ID:** The unique identifier of the product\n\n--> **URL:** Web Link to the specific product\n\n--> **Product Name:** Name of the specific product\n\n--> **Provider/Company Name:** Name of the product provider\n\n--> **Sector(s):** Sector of education where the product is used\n\n--> **Category:** The basic function of the product. Products are first labeled as one of these three categories: LC = ----Learning & Curriculum, CM = Classroom Management, and SDO = School & District Operations.\nSubcategory: Each of these categories have multiple sub-categories with which the products were labeled\n\n**Observations:**\n\nThere are 372 rows with 7 columns as mentioned above.\nThis dataset contain missing value of 61 which mainly come from Sectors(s), Category, Subcategory with each of them has 20 missing values and 1 missing value on Provider/Company Name.","metadata":{}},{"cell_type":"code","source":"products.head()","metadata":{"execution":{"iopub.status.busy":"2022-07-11T07:02:15.770340Z","iopub.execute_input":"2022-07-11T07:02:15.770750Z","iopub.status.idle":"2022-07-11T07:02:15.784657Z","shell.execute_reply.started":"2022-07-11T07:02:15.770711Z","shell.execute_reply":"2022-07-11T07:02:15.783326Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"code","source":"print(f'Number of rows: {products.shape[0]};  Number of columns: {products.shape[1]}; No of missing values: {sum(products.isna().sum())}')","metadata":{"execution":{"iopub.status.busy":"2022-07-11T07:02:15.786527Z","iopub.execute_input":"2022-07-11T07:02:15.787387Z","iopub.status.idle":"2022-07-11T07:02:15.801700Z","shell.execute_reply.started":"2022-07-11T07:02:15.787341Z","shell.execute_reply":"2022-07-11T07:02:15.798399Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"code","source":"print('Number of missing Values in every column:')\nprint(products.isna().sum())","metadata":{"execution":{"iopub.status.busy":"2022-07-11T07:02:15.803496Z","iopub.execute_input":"2022-07-11T07:02:15.804842Z","iopub.status.idle":"2022-07-11T07:02:15.820298Z","shell.execute_reply.started":"2022-07-11T07:02:15.804796Z","shell.execute_reply":"2022-07-11T07:02:15.818207Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"markdown","source":"# COUNT OF PRODUCTS BY ITS FUNCTIONS\n\nFrom the visualization below, we can see that the dataset consist of many type of products specifically product by its functions. Most of the product that is in this datasets belongs to Digital Learning Platforms, well almost all the product in the top spots belongs to digital learning services.\n\nSince the documentation said that the product data is collected from top 372 products with most users in 2020, by that alone we can conclude that online learning services is plentiful","metadata":{}},{"cell_type":"code","source":"df = products.groupby('Subcategory').count()[['LP ID']].sort_values(by=\"LP ID\", ascending=True)\n\nfig, ax = plt.subplots(figsize=(8, 14))\n\nbars0 = ax.barh(df.index, df['LP ID'], color=\"#179E66\", alpha=0.8, edgecolor=colors_dark[0])\n\nax.grid(axis='x', alpha=0.3)\nax.set_axisbelow(True)\nax.set_xlabel(\"Total Products\", fontsize=14, labelpad=10, fontweight='bold', color=colors_dark[0])\nax.set_ylabel(\"Subcategory\", fontsize=14, labelpad=10, fontweight='bold', color=colors_dark[0])\nxmin, xmax = ax.get_xlim()\nymin, ymax = ax.get_ylim()\n\nplt.text(s=\"About The Data | Products\", ha='left', x=xmin, y=ymax*1.08, fontsize=24, color=colors_dark[0])\nplt.text(s=\"Count of products by its functions\", ha='left', x=xmin, y=ymax*1.04, fontsize=24, fontweight='bold', color=colors_dark[0])\nplt.title(\"Most of products that is in this dataset belongs to Digital Learning Platforms\", loc='left', fontsize=13, color=colors_dark[1]) \n\nplt.show()\n","metadata":{"_kg_hide-input":true,"execution":{"iopub.status.busy":"2022-07-11T07:02:15.822300Z","iopub.execute_input":"2022-07-11T07:02:15.823640Z","iopub.status.idle":"2022-07-11T07:02:16.331906Z","shell.execute_reply.started":"2022-07-11T07:02:15.823590Z","shell.execute_reply":"2022-07-11T07:02:16.330791Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"markdown","source":"# COUNT OF PRODUCTS BY IT'S SECTOR\n\nFrom the visualization below we can see that most of the products that is listed on this dataset is used for PreK-12 (meant for 1st grade to 12th grade students) with the number of 170 products.","metadata":{}},{"cell_type":"code","source":"df = products.groupby('Sector(s)').count()[['LP ID']].sort_values(by=\"LP ID\", ascending=False)\n\nfig, ax = plt.subplots(figsize=(14, 8))\n\nbars0 = ax.bar(df.index, df['LP ID'], color=\"#9E1717\", alpha=0.8, edgecolor=\"#9E1717\")\n\nax.grid(axis='y', alpha=0.3)\nax.set_axisbelow(True)\nax.set_xlabel(\"Total Products\", fontsize=14, labelpad=10, fontweight='bold', color=colors_dark[0])\nax.set_ylabel(\"Sector(s)\", fontsize=14, labelpad=10, fontweight='bold', color=colors_dark[0])\nxmin, xmax = ax.get_xlim()\nymin, ymax = ax.get_ylim()\n\n\nfor i, bar in enumerate(bars0) : \n    x=bar.get_x()\n    y=bar.get_height()\n    if i < 3 : \n        ax.text(\n        s=f\"{df.iloc[i].values[0]}\\nProducts\",\n        va='center', ha='center', \n        x=x+0.38, y=y/2,\n        color='white',\n        fontsize=18,\n        fontweight='bold')\n    else : \n        ax.text(\n        s=f\"{df.iloc[i].values[0]}\",\n        va='center', ha='center', \n        x=x+0.38, y=y+5,\n        color=colors_dark[0],\n        fontsize=14)\n        \n\nplt.text(s=\"About The Data | Products\", ha='left', x=xmin, y=ymax*1.16, fontsize=24, color=colors_dark[0])\nplt.text(s=\"Count of products by its Sector(s)\", ha='left', x=xmin, y=ymax*1.1, fontsize=24, fontweight='bold', color=colors_dark[0])\nplt.title(\"Most of products that is in this dataset belongs to PreK-12 sector\\nmeaning that most of the education services that exists in this dataset is for kindergarten to 12th grade students\", loc='left', fontsize=13, color=colors_dark[2]) \n\nplt.show()","metadata":{"_kg_hide-input":true,"execution":{"iopub.status.busy":"2022-07-11T07:02:16.333560Z","iopub.execute_input":"2022-07-11T07:02:16.334585Z","iopub.status.idle":"2022-07-11T07:02:16.588821Z","shell.execute_reply.started":"2022-07-11T07:02:16.334544Z","shell.execute_reply":"2022-07-11T07:02:16.587782Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"markdown","source":"# MERGING DATA SETS\n\nWe will merge engagement, districts and products datasets into 1 big dataset called combine that consist all of the information from all dataset and we will delete existing dataset to free up some memory","metadata":{}},{"cell_type":"code","source":"merged = engagement.copy()\nmerged['id'] = merged['id'].astype('int64') \nmerged = merged.merge(districts, left_on='id', right_on='district_id', how='left')\nmerged = merged.merge(products, left_on='lp_id', right_on='LP ID', how='left')\nmerged = merged.drop('district_id', axis=1)\nmerged = merged.drop('LP ID', axis=1)\nmerged['time'] = pd.to_datetime(merged['time'])","metadata":{"_kg_hide-input":true,"execution":{"iopub.status.busy":"2022-07-11T07:02:16.590539Z","iopub.execute_input":"2022-07-11T07:02:16.591222Z","iopub.status.idle":"2022-07-11T07:03:01.719476Z","shell.execute_reply.started":"2022-07-11T07:02:16.591190Z","shell.execute_reply":"2022-07-11T07:03:01.718577Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"code","source":"merged.head()","metadata":{"execution":{"iopub.status.busy":"2022-07-11T07:03:01.720936Z","iopub.execute_input":"2022-07-11T07:03:01.721486Z","iopub.status.idle":"2022-07-11T07:03:01.739868Z","shell.execute_reply.started":"2022-07-11T07:03:01.721455Z","shell.execute_reply":"2022-07-11T07:03:01.738949Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"code","source":"print(f'Number of rows: {merged.shape[0]};  Number of columns: {merged.shape[1]}; No of missing values: {sum(merged.isna().sum())}')","metadata":{"execution":{"iopub.status.busy":"2022-07-11T07:03:01.741208Z","iopub.execute_input":"2022-07-11T07:03:01.741540Z","iopub.status.idle":"2022-07-11T07:03:12.873165Z","shell.execute_reply.started":"2022-07-11T07:03:01.741512Z","shell.execute_reply":"2022-07-11T07:03:12.872013Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"code","source":"print('Number of missing Values in every column:')\nprint(merged.isna().sum())","metadata":{"execution":{"iopub.status.busy":"2022-07-11T07:03:12.874936Z","iopub.execute_input":"2022-07-11T07:03:12.875371Z","iopub.status.idle":"2022-07-11T07:03:23.709465Z","shell.execute_reply.started":"2022-07-11T07:03:12.875328Z","shell.execute_reply":"2022-07-11T07:03:23.708255Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"code","source":"merged.describe()","metadata":{"execution":{"iopub.status.busy":"2022-07-11T07:03:23.710793Z","iopub.execute_input":"2022-07-11T07:03:23.711129Z","iopub.status.idle":"2022-07-11T07:03:29.431336Z","shell.execute_reply.started":"2022-07-11T07:03:23.711101Z","shell.execute_reply":"2022-07-11T07:03:29.430293Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"code","source":"merged['Provider/Company Name'].value_counts(dropna=False).shape","metadata":{"execution":{"iopub.status.busy":"2022-07-11T07:03:29.432498Z","iopub.execute_input":"2022-07-11T07:03:29.432767Z","iopub.status.idle":"2022-07-11T07:03:30.367900Z","shell.execute_reply.started":"2022-07-11T07:03:29.432743Z","shell.execute_reply":"2022-07-11T07:03:30.366903Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"markdown","source":"# ANALYSIS","metadata":{}},{"cell_type":"markdown","source":"Engagement dataset represents on how many products (in a school district) that have been accessed by students in a daily basis for year 2020 with the total of 22 million product accessed in 2020. There are 8,646 products but only 368 products that have been successfully mapped using the products_info dataset, unmapped products are categorized as Unknown.\n\nTo make a little bit clearer:\n\n--> The dataset is presented in a daily basis.\n\n--> A product will only one product per school district if there is an accessed to the product.\n\nIn this part we will also find some analysis related to trend:\n\n--> We will look into the mean accessed products.\n\n--> How many products that have been used in a daily basis.","metadata":{}},{"cell_type":"markdown","source":"# ACCESSED PRODUCTS\n\nThe analysis will focus on mean accessed products. What and how will we calculate the mean of accessed products? Every observations in the engagement dataset represent an accessed product in a school district. We can calculate how many products have been used in a school district and take the average from them. We will see the average of total products that has been used per school district.\n\n# OBSERVATIONS:\n\n* Mean daily accessed products is around 300 - 500 per school districts in 2020. There is an increased in the daily mean of accessed products after the summer holiday. Are the students in every school districts using a more diversify products or there are new products to support digital learning?\n* In every month there are volatility in the trend, that follow an order of 5-2 which are 5 days of school and 2 days of weekend.\n* Though the number of accessed products decreased in the weekend but we still see accessed products in the weekend, number of accessed products in the weekend are about 25%-50% from the weekday. Does it mean there are still many students that studying in the weekend?\n* In the mid-February, there is an temporary school closures followed by WHO that characterized COVID-19 as a pandemic on March 11th 2020. We can see there is an increased on accessed products starting from mid-February, though the increased are marginal.\n* Summer holiday in the United States are differ between schools districts, usually start in late May / early June and end in late August / early September, this consistent with lower accessed products on those dates but it can be considered high compared to regular date. Once again, does it mean there are many students that still study in the holiday? It seems weird.","metadata":{}},{"cell_type":"code","source":"import pandas as pd\ntemp = pd.DataFrame(merged.groupby(['time', 'id'])['time'].count())\ntemp = temp.rename(columns={\"time\":\"amount\"})\ntemp = temp.reset_index(drop=False)\ntemp = temp.groupby('time')['amount'].mean()\n\nbackground_color = \"#B4DBE9\"\nsns.set_palette(['dimgray']*400)\n\nplt.rcParams['figure.dpi'] = 600\nfig = plt.figure(figsize=(9, 1), facecolor='#B4DBE9')\ngs = fig.add_gridspec(1, 1)\ngs.update(wspace=0, hspace=0)\nax0 = fig.add_subplot(gs[0, 0])\nax0.set_facecolor(background_color)\n\n#graph\nax0 = sns.barplot(ax=ax0, x=temp.index, y=temp, zorder=2, linewidth=0.8, saturation=1)\nsummer = np.arange(np.datetime64(\"1970-06-01\"), np.datetime64(\"1970-08-24\"))\nax0.fill_between(summer, np.max(temp), color='#ffd514', alpha=0.5, zorder=2, linewidth=0)\nplt.axvline(np.datetime64(\"1970-02-12\"), color='#ffd514', alpha=0.5)\nplt.axvline(np.datetime64(\"1970-03-11\"), color='#ffd514', alpha=0.5)\nfor s in [\"right\", \"top\"]:\n    ax0.spines[s].set_visible(False)\nax0.grid(which='major', axis='x', zorder=0, color='#EEEEEE', lw=0.3)\nax0.grid(which='major', axis='y', zorder=0, color='#EEEEEE', lw=0.3)\n\n#title\nx0, x1 = ax0.get_xlim()\ny0, y1 = ax0.get_ylim() \nax0.text(x0, y1*1.11, 'Mean Daily Accessed Products', color='black', fontsize=7, ha='left', va='bottom', weight='bold')\nax0.text(x0, y1*1.1, 'After the summer holiday, there are an increased in accessed products', \n        color='#292929', fontsize=5, ha='left', va='top')\nax0.annotate(\"temporary\\nschool closures\", \n             xy=(np.datetime64(\"1970-02-12\"), 430), \n             xytext=(np.datetime64(\"1970-01-09\"), 380), \n             arrowprops=dict(arrowstyle=\"->\"), fontsize=5)\nax0.annotate(\"COVID-19\\nPandemic\", \n             xy=(np.datetime64(\"1970-03-11\"), 430), \n             xytext=(np.datetime64(\"1970-03-18\"), 350), \n             arrowprops=dict(arrowstyle=\"->\"), fontsize=5)\nax0.annotate(\"Summer Holiday\", \n             xy=(np.datetime64(\"1970-06-27\"), 350), \n             xytext=(np.datetime64(\"1970-06-27\"), 350), \n             fontsize=5)\n\n#format axis\nax0.set_xlabel(\"date\",fontsize=5, weight='bold')\nax0.set_ylabel(\"products\",fontsize=5, weight='bold')\n\n#format the ticks\nax0.tick_params('both', length=2, which='major', labelsize=5)\nmonths = mdates.MonthLocator()\nax0.xaxis.set_major_locator(months)\nax0.set_xticklabels(['Jan 2020', 'Feb 2020', 'Mar 2020', 'Apr 2020', 'May 2020', 'Jun 2020', 'Jul 2020', \n                     'Aug 2020', 'Sep 2020', 'Oct 2020', 'Nov 2010', 'Dec 2020'])\ny_format = ticker.FuncFormatter(lambda x, p: format(int(x), ','))\nax0.yaxis.set_major_formatter(y_format)\n\nplt.show()","metadata":{"_kg_hide-input":true,"execution":{"iopub.status.busy":"2022-07-11T07:03:30.369454Z","iopub.execute_input":"2022-07-11T07:03:30.369735Z","iopub.status.idle":"2022-07-11T07:03:34.189231Z","shell.execute_reply.started":"2022-07-11T07:03:30.369709Z","shell.execute_reply":"2022-07-11T07:03:34.188263Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"markdown","source":"# TOP PRODUCTS AND PROVIDERS\nAs stated before, there are 369 products (including Unknown) that have been successfully mapped in 2020. This mapped products will be used as the basis of the analysis. In this section we are trying to see:\n\nMost used products by students in 2020 using page-load as the basis.\nTop providers in 2020 based on the page-load.\n\n# OBSERVATIONS:\n\n* Products\n1. Four of top 5 products (excluding Unknown) are managed by Google LLC, they are Google Docs, Google Classroom, Youtube and Meet. Google Docs is the most used products in 2020, it had 769.9 million page-load,Google Classroom, is in the 3rd place with373.6 millionpage-load.YoutubeandMeetare in positionfourandfive` respectively.\n1. There is 1 product in the top 5 products that doesn't belong to Google LLC, the name of the product is Canvas. This product is owned by Instructure, Inc. and is in the 3rd position.\n1. Unknown is in the second place with 417.1 million page-load. We can assume it was coming from many products.\n* Providers\n1. As expected, Google LLC has the highest page load outperforming any others providers.\n1. Instructure, Inc., a company that made Canvas is in the third place with 138 million page-load in 2020.\n1. In 4th place, we can see there is Kahoot! AS with 87 million page-load","metadata":{}},{"cell_type":"code","source":"plt.rcParams['figure.dpi'] = 600\nfig = plt.figure(figsize=(4, 3), facecolor='#DADADA')\ngs = fig.add_gridspec(1, 2)\ngs.update(wspace=1.1, hspace=1.5)\n\n##########PRODUCT##########\ntemp = pd.DataFrame(merged.groupby('Product Name', dropna=False)['engagement_index'].sum()/1000000).reset_index()\ntemp.columns = ['product', 'amount']\ntemp = temp.fillna('Unknown')\ntemp = temp.sort_values('amount', ascending=False)\ntemp = temp[0:10]\n\nbackground_color = \"#f6f5f5\"\ncolor_map = [\"blue\" for _ in range(75)]\ncolor_map[0] = \"#ffd514\"\nsns.set_palette(sns.color_palette(color_map))\n\nax0 = fig.add_subplot(gs[0, 0])\nax0.set_facecolor(background_color)\nfor s in [\"right\", \"top\"]:\n    ax0.spines[s].set_visible(False)\n\n#graph\nax0_sns = sns.barplot(ax=ax0, y=temp['product'], x=temp['amount'], \n                      zorder=2, linewidth=0, orient='h', saturation=1, alpha=1)\nax0_sns.grid(which='major', axis='x', zorder=0, color='#EEEEEE', linewidth=0.3)\nax0_sns.grid(which='major', axis='y', zorder=0, color='#EEEEEE', linewidth=0.3)\n\n#format axis\nax0_sns.set_xlabel(\"page-load (million)\",fontsize=3, weight='bold')\nax0_sns.set_ylabel(\"products\",fontsize=3, weight='bold')\nax0_sns.tick_params(labelsize=3, width=0.5, length=1.5)\n\n#title\nx0, x1 = ax0.get_xlim()\ny0, y1 = ax0.get_ylim()\nax0.text(x0, y1-0.45, 'Top 10 Products', fontsize=4, ha='left', va='top', weight='bold')\nax0.text(x0, y1-0.2, 'Top 10 products are controlled by Google LLC', fontsize=3, ha='left', va='top')\n\n# data label\nfor p in ax0.patches:\n    value = f'{p.get_width():,.0f}'\n    x = p.get_x() + p.get_width() + 55\n    y = p.get_y() + p.get_height() / 2 \n    ax0.text(x, y, value, ha='center', va='center', fontsize=2.7, \n            bbox=dict(facecolor='none', edgecolor='black', boxstyle='round', linewidth=0.3))\nx_format = ticker.FuncFormatter(lambda x, p: format(int(x), ','))\nax0.xaxis.set_major_formatter(x_format)\n\n##########PROVIDER##########\ntemp = pd.DataFrame(merged.groupby('Provider/Company Name', dropna=False)['engagement_index'].sum()/1000000).reset_index()\ntemp.columns = ['product', 'amount']\ntemp = temp.fillna('Unknown')\ntemp = temp.sort_values('amount', ascending=False)\ntemp = temp[0:10]\n\nbackground_color = \"#f6f5f5\"\ncolor_map = [\"blue\" for _ in range(75)]\ncolor_map[0] = \"#ffd514\"\nsns.set_palette(sns.color_palette(color_map))\n\nax0 = fig.add_subplot(gs[0, 1])\nax0.set_facecolor(background_color)\nfor s in [\"right\", \"top\"]:\n    ax0.spines[s].set_visible(False)\n\n#graph\nax0_sns = sns.barplot(ax=ax0, y=temp['product'], x=temp['amount'], \n                      zorder=2, linewidth=0, orient='h', saturation=1, alpha=1)\nax0_sns.grid(which='major', axis='x', zorder=0, color='#EEEEEE', linewidth=0.3)\nax0_sns.grid(which='major', axis='y', zorder=0, color='#EEEEEE', linewidth=0.3)\n\n#format axis\nax0_sns.set_xlabel(\"page-load (million)\",fontsize=3, weight='bold')\nax0_sns.set_ylabel(\"providers\",fontsize=3, weight='bold')\nax0_sns.tick_params(labelsize=3, width=0.5, length=1.5)\n\n#title\nx0, x1 = ax0.get_xlim()\ny0, y1 = ax0.get_ylim()\nax0.text(x0, y1-0.47, 'Top 10 Providers', fontsize=4, ha='left', va='top', weight='bold')\nax0.text(x0, y1-0.2, 'Google LLC is the top provider for digital learning', fontsize=3, ha='left', va='top')\n\n# data label\nfor p in ax0.patches:\n    value = f'{p.get_width():,.0f}'\n    x = p.get_x() + p.get_width() + 130\n    y = p.get_y() + p.get_height() / 2 \n    ax0.text(x, y, value, ha='center', va='center', fontsize=2.7, \n            bbox=dict(facecolor='none', edgecolor='black', boxstyle='round', linewidth=0.3))\nx_format = ticker.FuncFormatter(lambda x, p: format(int(x), ','))\nax0.xaxis.set_major_formatter(x_format)\n","metadata":{"_kg_hide-input":true,"execution":{"iopub.status.busy":"2022-07-11T07:03:34.190709Z","iopub.execute_input":"2022-07-11T07:03:34.191048Z","iopub.status.idle":"2022-07-11T07:03:38.391187Z","shell.execute_reply.started":"2022-07-11T07:03:34.191019Z","shell.execute_reply":"2022-07-11T07:03:38.390071Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"markdown","source":"# CATEGORY & SECTORS\nIn the first part we would like to see how category and sectors relates to the page-load. In the perspective of sector, a product can be classified into more than 1 sector, there are 5 sectors excluding the unknown. There are 3 categories in the dataset that are described below:\n\nLC = Learning & Curriculum\nCM = Classroom Management\nSDO = School & District Operations\n\n# OBSERVATIONS:\n\n* Category\n1. Learning & Curriculum has the highest page-load reaching 1.6 billion in 2020, this is a good sign as products are used for students to study.\n1. School & District Operations is in the second place with 494.9 million page-load.\n1. Classroom Management is in the third place (excluding Unknown) with 187.4 million of page-load.\n* Sectors\n1. Almost 2 billion page-load are coming from products that can be categorize into 3 sector: PreK-12; Higher Ed; Corporate.\n1. Unknown sectors is in the second place with 424.6 million page-load.\n1. In the 3rd place there is PreK-12 with 391.9 million page-load.","metadata":{}},{"cell_type":"code","source":"plt.rcParams['figure.dpi'] = 600\nfig = plt.figure(figsize=(4, 4), facecolor='#f6f5f5')\ngs = fig.add_gridspec(2, 2)\ngs.update(wspace=0.2, hspace=1.5)\n\n##########CATEGORY##########\ntemp = pd.DataFrame(merged.groupby(['Category'], dropna=False)['engagement_index'].sum()).reset_index()\ntemp.columns = ['description', 'amount']\ntemp = temp.fillna('Unknown')\ntemp = temp.sort_values('amount', ascending=False)\n\nbackground_color = \"#331313\"\ncolor_map = [\"blue\" for _ in range(75)]\ncolor_map[0] = \"#ffd514\"\nsns.set_palette(sns.color_palette(color_map))\n\nax0 = fig.add_subplot(gs[0, 0])\nax0.set_facecolor(background_color)\nfor s in [\"right\", \"top\"]:\n    ax0.spines[s].set_visible(False)\n\n#graph\nax0.plot(temp['description'], temp['amount']/1000000, 'o--', color=\"#ffd514\", markersize=3, markeredgewidth=0, linewidth=0.5, zorder=4)\nax0.fill_between(temp['description'], temp['amount']/1000000, color=\"#d3d3d3\", zorder=3, alpha=0.5, linewidth=0)\nax0.grid(which='major', axis='x', zorder=0, color='#EEEEEE', linewidth=0.2)\nax0.grid(which='major', axis='y', zorder=0, color='#EEEEEE', linewidth=0.2)\n\n#format axis\nax0.set_xlabel(\"category\",fontsize=3, weight='bold')\nax0.set_ylabel(\"page-load (million)\",fontsize=3, weight='bold')\nax0.tick_params(labelsize=3, width=0.5, length=1.5)\nax0.yaxis.set_major_formatter(y_format)\n\n#title\nx0, x1 = ax0.get_xlim()\ny0, y1 = ax0.get_ylim()\nax0.text(x0, y1+285, 'Category & Page Load', fontsize=4, ha='left', va='top', weight='bold')\nax0.text(x0, y1+160, 'Most of the page-load come from Learning & Curriculum', fontsize=3, ha='left', va='top')\n\n##########SECTORS##########\ntemp = pd.DataFrame(merged.groupby(['Sector(s)'], dropna=False)['engagement_index'].sum()).reset_index()\ntemp.columns = ['description', 'amount']\ntemp = temp.fillna('Unknown')\ntemp = temp.sort_values('amount', ascending=False)\n\nbackground_color = \"#331313\"\ncolor_map = [\"dimgray\" for _ in range(75)]\ncolor_map[0] = \"#ffd514\"\nsns.set_palette(sns.color_palette(color_map))\n\nax0 = fig.add_subplot(gs[0, 1])\nax0.set_facecolor(background_color)\nfor s in [\"right\", \"top\"]:\n    ax0.spines[s].set_visible(False)\n\n#graph\nax0.plot(temp['description'], temp['amount']/1000000, 'o--', color=\"#ffd514\", markersize=3, markeredgewidth=0, linewidth=0.5, zorder=4)\nax0.fill_between(temp['description'], temp['amount']/1000000, color=\"#d3d3d3\", zorder=3, alpha=0.5, linewidth=0)\nax0.grid(which='major', axis='x', zorder=0, color='#EEEEEE', linewidth=0.2)\nax0.grid(which='major', axis='y', zorder=0, color='#EEEEEE', linewidth=0.2)\n\n#format axis\nax0.set_xlabel(\"sector\",fontsize=3, weight='bold')\nax0.set_ylabel(\"page-load (million)\",fontsize=3, weight='bold')\nax0.tick_params(labelsize=3, width=0.5, length=1.5)\nax0.yaxis.set_major_formatter(y_format)\nax0.set_xticklabels(['PreK-12;\\nHigher Ed;\\nCorporate', 'Unknown', 'PreK-12', \n                     'PreK-12;\\nHigher Ed', 'Corporate', 'Higher Ed;\\nCorporate'])\n\n#title\nx0, x1 = ax0.get_xlim()\ny0, y1 = ax0.get_ylim()\nax0.text(x0, y1+300, 'Sectors & Page Load', fontsize=4, ha='left', va='top', weight='bold')\nax0.text(x0, y1+150, 'PreK-12; Higher Ed; Corporate is dominating', fontsize=3, ha='left', va='top')\n\nplt.show()","metadata":{"_kg_hide-input":true,"execution":{"iopub.status.busy":"2022-07-11T07:03:38.392869Z","iopub.execute_input":"2022-07-11T07:03:38.393236Z","iopub.status.idle":"2022-07-11T07:03:42.366473Z","shell.execute_reply.started":"2022-07-11T07:03:38.393204Z","shell.execute_reply":"2022-07-11T07:03:42.365386Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"code","source":"merged['time'] = pd.to_datetime(merged['time'], errors='coerce')\nmerged['month'] = merged['time'].dt.month","metadata":{"_kg_hide-input":true,"execution":{"iopub.status.busy":"2022-07-11T07:03:42.368274Z","iopub.execute_input":"2022-07-11T07:03:42.368696Z","iopub.status.idle":"2022-07-11T07:03:44.961767Z","shell.execute_reply.started":"2022-07-11T07:03:42.368657Z","shell.execute_reply":"2022-07-11T07:03:44.960928Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"code","source":"engagement_per_month=merged.groupby(['month'], as_index=False)['engagement_index'].mean()\nengagement_per_month=engagement_per_month.sort_values(by=['month'],ascending=True)","metadata":{"_kg_hide-input":true,"execution":{"iopub.status.busy":"2022-07-11T07:03:44.963223Z","iopub.execute_input":"2022-07-11T07:03:44.964180Z","iopub.status.idle":"2022-07-11T07:03:45.796238Z","shell.execute_reply.started":"2022-07-11T07:03:44.964137Z","shell.execute_reply":"2022-07-11T07:03:45.795120Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"markdown","source":"# how's the online platform engagement trend in 2020 ?\n\nThe plot below tells us about average engagement index monthly. We know that there is significant drop from April to July, but the other way around from July to August.\nSince march, where WHO declare about COVID-19 Pandemic, there is slightly increase engagement index. I think summer holiday is related to why engagement index is dropping.\n\n","metadata":{}},{"cell_type":"code","source":"plt.figure(figsize = (7,4))\n\nsns.lineplot(data=engagement_per_month, x=\"month\", y= \"engagement_index\", color='g')\nplt.title('Monthly Average Engagement in 2020 (All District)', size=10)\nplt.xlabel('Month',size=12)\n\nsns.despine()\nplt.show()","metadata":{"_kg_hide-input":true,"execution":{"iopub.status.busy":"2022-07-11T07:03:45.802226Z","iopub.execute_input":"2022-07-11T07:03:45.802689Z","iopub.status.idle":"2022-07-11T07:03:46.590789Z","shell.execute_reply.started":"2022-07-11T07:03:45.802655Z","shell.execute_reply":"2022-07-11T07:03:46.589768Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"markdown","source":"# Which Platform Category with the most engagement index ?","metadata":{}},{"cell_type":"code","source":"#get the category data base on average engagement index and sort it\ntop_category_platform=merged.groupby(['Category'], as_index=False)['engagement_index'].mean()\ntop_category_platform=top_category_platform.sort_values(by=['engagement_index'],ascending=False)","metadata":{"execution":{"iopub.status.busy":"2022-07-11T07:03:46.591982Z","iopub.execute_input":"2022-07-11T07:03:46.592272Z","iopub.status.idle":"2022-07-11T07:03:48.424342Z","shell.execute_reply.started":"2022-07-11T07:03:46.592245Z","shell.execute_reply":"2022-07-11T07:03:48.423431Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"code","source":"top_category_platform.head()","metadata":{"execution":{"iopub.status.busy":"2022-07-11T07:03:48.425434Z","iopub.execute_input":"2022-07-11T07:03:48.425705Z","iopub.status.idle":"2022-07-11T07:03:48.436045Z","shell.execute_reply.started":"2022-07-11T07:03:48.425680Z","shell.execute_reply":"2022-07-11T07:03:48.434883Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"markdown","source":"SDO - Learning Management Systems (LMS) is the category with most used in 2020 and followed by LC Category\n\n","metadata":{}},{"cell_type":"code","source":"plt.figure(figsize = (10,4))\n\nsns.barplot(data=top_category_platform[:10], y=\"Category\", x= \"engagement_index\")\nplt.title('Top 10 Category Platform with the Most Average Daily Engagement in 2020 (All District)', size=18)\nsns.despine()\nplt.show()","metadata":{"execution":{"iopub.status.busy":"2022-07-11T07:03:48.437874Z","iopub.execute_input":"2022-07-11T07:03:48.438210Z","iopub.status.idle":"2022-07-11T07:03:49.500844Z","shell.execute_reply.started":"2022-07-11T07:03:48.438182Z","shell.execute_reply":"2022-07-11T07:03:49.500122Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"markdown","source":"# VISUALIZATION","metadata":{}},{"cell_type":"markdown","source":"# GEOGRAPHIC AND PAGELOAD CORRELATION\nWe will try to see below correlation:\n\nCorrelation between its own state and its own locale in terms of page-load. At first, we will look into the correlation between state followed by correlation between `locale.\nCorrelation between the locale. There are 5 locale including the Unknown, the others are City, Rural, Suburb and Town.\n# OBSERVATIONS:\n\n* Correlation between state\n1. The lower correlation between state is betwen Minnesota and North Dakota which is -0.121. This meaning they have an inverse relationship, though it's near zero.\n1. Both of New York and North Dakota almost don't have any relationship with correlation of -0.033.\n1. The highest postive correlation are between Unknown state and Missouri with a correlation of 0.932.\n* Correlation between locale\n1. Most of the locale have a high positive correlation above 0.8.\n1. Suburb and Rural have the highest correlation of 0.98 which is almost 1.","metadata":{}},{"cell_type":"code","source":"plt.rcParams['figure.dpi'] = 600\nfig = plt.figure(figsize=(4, 4), facecolor='#DADADA')\ngs = fig.add_gridspec(1, 1)\ngs.update(wspace=0.7, hspace=0.1)\n\n##########CORRELATION STATE##########\ntemp = merged[['time', 'state', 'engagement_index']]\ntemp['state'].fillna('Unknown', inplace=True)\ntemp = pd.DataFrame(temp.pivot_table(index='time', columns='state', values='engagement_index', \n                                     aggfunc='sum', dropna=False)).reset_index(drop=False)\n\nbackground_color = \"#f6f5f5\"\ncolors = [\"black\", \"#51C4D3\"]\ncolormap = matplotlib.colors.LinearSegmentedColormap.from_list(\"\", colors)\n\nax0 = fig.add_subplot(gs[0, 0])\nax0.set_facecolor(background_color)\n\n#graph\nax0_sns = sns.heatmap(temp.corr(), ax=ax0, annot=True, square=True, xticklabels=True, yticklabels=True,\n            annot_kws={\"size\": 3}, cbar=False, cmap=colormap, linewidths=0.3, \n            linecolor='black', fmt='.1g')\n\n#title\nx0, x1 = ax0.get_xlim()\ny0, y1 = ax0.get_ylim()\nax0.text(x0, y1-1.1, \"Correlation Between State\", fontsize=5, ha='left', va='top', weight='bold')\nax0.text(x0, y1-0.5, 'BLUE indicates a high positive correlation', fontsize=3, ha='left', va='top')\n\n#axis\nax0_sns.set_xlabel(\"\")\nax0_sns.set_ylabel(\"\")\nax0_sns.tick_params(length=0, labelsize=3)","metadata":{"_kg_hide-input":true,"execution":{"iopub.status.busy":"2022-07-11T07:03:49.502129Z","iopub.execute_input":"2022-07-11T07:03:49.502618Z","iopub.status.idle":"2022-07-11T07:03:56.123150Z","shell.execute_reply.started":"2022-07-11T07:03:49.502585Z","shell.execute_reply":"2022-07-11T07:03:56.122062Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"code","source":"plt.rcParams['figure.dpi'] = 600\nfig = plt.figure(figsize=(3, 3), facecolor='#f6f5f5')\ngs = fig.add_gridspec(1, 1)\ngs.update(wspace=0.7, hspace=0.1)\n\n##########CORRELATION LOCALE##########\ntemp = merged[['time', 'locale', 'engagement_index']]\ntemp['locale'].fillna('Unknown', inplace=True)\ntemp = pd.DataFrame(temp.pivot_table(index='time', columns='locale', values='engagement_index', \n                                     aggfunc='sum', dropna=False)).reset_index(drop=False)\n\nbackground_color = \"#f6f5f5\"\ncolors = [\"black\", \"#6351D3\"]\ncolormap = matplotlib.colors.LinearSegmentedColormap.from_list(\"\", colors)\n\nax0 = fig.add_subplot(gs[0, 0])\nax0.set_facecolor(background_color)\n\n#graph\n#matrix = np.triu(temp.corr())\nax0_sns = sns.heatmap(temp.corr(), ax=ax0, annot=True, square=True, xticklabels=True, yticklabels=True,\n            annot_kws={\"size\": 4}, cbar=False, cmap=colormap, linewidths=0.3, \n            linecolor='black', fmt='.1g')\n\n#title\nx0, x1 = ax0.get_xlim()\ny0, y1 = ax0.get_ylim()\nax0.text(x0, y1-0.38, \"Correlation Between Locale\", fontsize=6, ha='left', va='top', weight='bold')\nax0.text(x0, y1-0.2, 'Byzantine Night Blue indicates a high positive correlation', fontsize=4, ha='left', va='top')\n\n#axis\nax0_sns.set_xlabel(\"\")\nax0_sns.set_ylabel(\"\")\nax0_sns.tick_params(length=0, labelsize=3)","metadata":{"_kg_hide-output":false,"_kg_hide-input":true,"execution":{"iopub.status.busy":"2022-07-11T07:03:56.124593Z","iopub.execute_input":"2022-07-11T07:03:56.125377Z","iopub.status.idle":"2022-07-11T07:04:00.646231Z","shell.execute_reply.started":"2022-07-11T07:03:56.125339Z","shell.execute_reply":"2022-07-11T07:04:00.645467Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"markdown","source":"# VISUALIZING THE ENGAGEMENT INDEX USING FOLIUM ND HEATMAP","metadata":{}},{"cell_type":"code","source":"from geopy.geocoders import Nominatim\nfrom folium.plugins import HeatMap","metadata":{"execution":{"iopub.status.busy":"2022-07-11T07:04:00.647204Z","iopub.execute_input":"2022-07-11T07:04:00.648141Z","iopub.status.idle":"2022-07-11T07:04:01.286729Z","shell.execute_reply.started":"2022-07-11T07:04:00.648106Z","shell.execute_reply":"2022-07-11T07:04:01.285955Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"code","source":"locations=pd.DataFrame({\"state\":districts['state'].unique()})\n\ngeolocator=Nominatim(user_agent=\"app\")\n\n#we need to get the latitude and longitude data\nlat=[]\nlon=[]\nfor location in locations['state']:\n    location = geolocator.geocode(location)    \n    if location is None:\n        lat.append(np.nan)\n        lon.append(np.nan)\n    else:\n        lat.append(location.latitude)\n        lon.append(location.longitude)\n        \nlocations['lat']=lat\nlocations['lon']=lon","metadata":{"_kg_hide-input":true,"execution":{"iopub.status.busy":"2022-07-11T07:04:01.287715Z","iopub.execute_input":"2022-07-11T07:04:01.288815Z","iopub.status.idle":"2022-07-11T07:04:13.093946Z","shell.execute_reply.started":"2022-07-11T07:04:01.288782Z","shell.execute_reply":"2022-07-11T07:04:13.093057Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"code","source":"state_engagement = merged.groupby(['state'], as_index=False)['engagement_index'].mean()\n\n#merge the state engagement data with latidude and longitude\nfinal_loc = state_engagement.merge(locations,on='state',how=\"left\").dropna()\nfinal_loc","metadata":{"execution":{"iopub.status.busy":"2022-07-11T07:04:13.095226Z","iopub.execute_input":"2022-07-11T07:04:13.095546Z","iopub.status.idle":"2022-07-11T07:04:14.488756Z","shell.execute_reply.started":"2022-07-11T07:04:13.095518Z","shell.execute_reply":"2022-07-11T07:04:14.487707Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"code","source":"import folium\nfrom folium import plugins\n\nus_map = folium.Map(location=[38,-97],zoom_start =5, tiles='Stamen Terrain')\n\nHeatMap(final_loc[['lat','lon','engagement_index']],zoom=20,radius=20).add_to(us_map)\naverage_engagement = plugins.MarkerCluster().add_to(us_map)\n\nfor lat, long, label, in zip(final_loc.lat, final_loc.lon, final_loc.engagement_index):\n    folium.Marker(\n        location=[lat,long],\n        icon=None,\n        popup=label,\n    ).add_to(average_engagement)\n\nus_map","metadata":{"_kg_hide-input":true,"execution":{"iopub.status.busy":"2022-07-11T07:04:14.490336Z","iopub.execute_input":"2022-07-11T07:04:14.490980Z","iopub.status.idle":"2022-07-11T07:04:14.544154Z","shell.execute_reply.started":"2022-07-11T07:04:14.490941Z","shell.execute_reply":"2022-07-11T07:04:14.542959Z"},"trusted":true},"execution_count":null,"outputs":[]}]}