Hands-on Lab 17 Bubble Plots.ipynb

{"cells":[{"cell_type":"markdown","metadata":{},"source":["<center>\n","    <img src=\"https://gitlab.com/ibm/skills-network/courses/placeholder101/-/raw/master/labs/module%201/images/IDSNlogo.png\" width=\"300\" alt=\"cognitiveclass.ai logo\"  />\n","</center>\n"]},{"cell_type":"markdown","metadata":{},"source":["# **Data Visualization Lab**\n"]},{"cell_type":"markdown","metadata":{},"source":["Estimated time needed: **45 to 60** minutes\n"]},{"cell_type":"markdown","metadata":{},"source":["In this assignment you will be focusing on the visualization of data.\n","\n","The data set will be presented to you in the form of a RDBMS.\n","\n","You will have to use SQL queries to extract the data.\n"]},{"cell_type":"markdown","metadata":{},"source":["## Objectives\n"]},{"cell_type":"markdown","metadata":{},"source":["In this lab you will perform the following:\n"]},{"cell_type":"markdown","metadata":{},"source":["-   Visualize the distribution of data.\n","\n","-   Visualize the relationship between two features.\n","\n","-   Visualize composition of data.\n","\n","-   Visualize comparison of data.\n"]},{"cell_type":"markdown","metadata":{},"source":["<hr>\n"]},{"cell_type":"markdown","metadata":{},"source":["## Demo: How to work with database\n"]},{"cell_type":"markdown","metadata":{},"source":["Download database file.\n"]},{"cell_type":"code","execution_count":null,"metadata":{},"outputs":[],"source":["!wget https://cf-courses-data.s3.us.cloud-object-storage.appdomain.cloud/IBM-DA0321EN-SkillsNetwork/LargeData/m4_survey_data.sqlite"]},{"cell_type":"markdown","metadata":{},"source":["Connect to the database.\n"]},{"cell_type":"code","execution_count":null,"metadata":{},"outputs":[],"source":["import sqlite3\n","conn = sqlite3.connect(\"m4_survey_data.sqlite\") # open a database connection"]},{"cell_type":"markdown","metadata":{},"source":["Import pandas module.\n"]},{"cell_type":"code","execution_count":null,"metadata":{},"outputs":[],"source":["import pandas as pd"]},{"cell_type":"markdown","metadata":{},"source":["## Demo: How to run an sql query\n"]},{"cell_type":"code","execution_count":null,"metadata":{},"outputs":[],"source":["# print how many rows are there in the table named 'master'\n","QUERY = \"\"\"\n","SELECT COUNT(*)\n","FROM master\n","\"\"\"\n","\n","# the read_sql_query runs the sql query and returns the data as a dataframe\n","df = pd.read_sql_query(QUERY,conn)\n","df.head()"]},{"cell_type":"markdown","metadata":{},"source":["## Demo: How to list all tables\n"]},{"cell_type":"code","execution_count":null,"metadata":{"scrolled":true},"outputs":[],"source":["# print all the tables names in the database\n","QUERY = \"\"\"\n","SELECT name as Table_Name FROM\n","sqlite_master WHERE\n","type = 'table'\n","\"\"\"\n","# the read_sql_query runs the sql query and returns the data as a dataframe\n","pd.read_sql_query(QUERY,conn)\n"]},{"cell_type":"markdown","metadata":{},"source":["## Demo: How to run a group by query\n"]},{"cell_type":"code","execution_count":null,"metadata":{},"outputs":[],"source":["QUERY = \"\"\"\n","SELECT Age,COUNT(*) as count\n","FROM master\n","group by age\n","order by age\n","\"\"\"\n","pd.read_sql_query(QUERY,conn)"]},{"cell_type":"markdown","metadata":{},"source":["## Demo: How to describe a table\n"]},{"cell_type":"code","execution_count":null,"metadata":{},"outputs":[],"source":["table_name = 'master'  # the table you wish to describe\n","\n","QUERY = \"\"\"\n","SELECT sql FROM sqlite_master\n","WHERE name= '{}'\n","\"\"\".format(table_name)\n","\n","df = pd.read_sql_query(QUERY,conn)\n","print(df.iat[0,0])"]},{"cell_type":"markdown","metadata":{},"source":["# Hands-on Lab\n"]},{"cell_type":"markdown","metadata":{},"source":["## Visualizing distribution of data\n"]},{"cell_type":"markdown","metadata":{},"source":["### Histograms\n"]},{"cell_type":"markdown","metadata":{},"source":["Plot a histogram of `ConvertedComp.`\n"]},{"cell_type":"code","execution_count":null,"metadata":{},"outputs":[],"source":["# your code goes here\n"]},{"cell_type":"markdown","metadata":{},"source":["### Box Plots\n"]},{"cell_type":"markdown","metadata":{},"source":["Plot a box plot of `Age.`\n"]},{"cell_type":"code","execution_count":null,"metadata":{},"outputs":[],"source":["# your code goes here\n","\n"]},{"cell_type":"markdown","metadata":{},"source":["## Visualizing relationships in data\n"]},{"cell_type":"markdown","metadata":{},"source":["### Scatter Plots\n"]},{"cell_type":"markdown","metadata":{},"source":["Create a scatter plot of `Age` and `WorkWeekHrs.`\n"]},{"cell_type":"code","execution_count":null,"metadata":{},"outputs":[],"source":["# your code goes here\n","\n"]},{"cell_type":"markdown","metadata":{},"source":["### Bubble Plots\n"]},{"cell_type":"markdown","metadata":{},"source":["Create a bubble plot of `WorkWeekHrs` and `CodeRevHrs`, use `Age` column as bubble size.\n"]},{"cell_type":"code","execution_count":null,"metadata":{},"outputs":[],"source":["# your code goes here\n","\n"]},{"cell_type":"markdown","metadata":{},"source":["## Visualizing composition of data\n"]},{"cell_type":"markdown","metadata":{},"source":["### Pie Charts\n"]},{"cell_type":"markdown","metadata":{},"source":["Create a pie chart of the top 5 databases that respondents wish to learn next year. Label the pie chart with database names. Display percentages of each database on the pie chart.\n"]},{"cell_type":"code","execution_count":null,"metadata":{},"outputs":[],"source":["# your code goes here\n","\n"]},{"cell_type":"markdown","metadata":{},"source":["### Stacked Charts\n"]},{"cell_type":"markdown","metadata":{},"source":["Create a stacked chart of median `WorkWeekHrs` and `CodeRevHrs` for the age group 30 to 35.\n"]},{"cell_type":"code","execution_count":null,"metadata":{},"outputs":[],"source":["# your code goes here\n","\n"]},{"cell_type":"markdown","metadata":{},"source":["## Visualizing comparison of data\n"]},{"cell_type":"markdown","metadata":{},"source":["### Line Chart\n"]},{"cell_type":"markdown","metadata":{},"source":["Plot the median `ConvertedComp` for all ages from 45 to 60.\n"]},{"cell_type":"code","execution_count":null,"metadata":{},"outputs":[],"source":["# your code goes here\n","\n"]},{"cell_type":"markdown","metadata":{},"source":["### Bar Chart\n"]},{"cell_type":"markdown","metadata":{},"source":["Create a horizontal bar chart using column `MainBranch.`\n"]},{"cell_type":"code","execution_count":null,"metadata":{},"outputs":[],"source":["# your code goes here\n","\n"]},{"cell_type":"markdown","metadata":{},"source":["Close the database connection.\n"]},{"cell_type":"code","execution_count":null,"metadata":{},"outputs":[],"source":["conn.close()"]},{"cell_type":"markdown","metadata":{},"source":["## Authors\n"]},{"cell_type":"markdown","metadata":{},"source":["Ramesh Sannareddy\n"]},{"cell_type":"markdown","metadata":{},"source":["### Other Contributors\n"]},{"cell_type":"markdown","metadata":{},"source":["Rav Ahuja\n"]},{"cell_type":"markdown","metadata":{},"source":["## Change Log\n"]},{"cell_type":"markdown","metadata":{},"source":["| Date (YYYY-MM-DD) | Version | Changed By        | Change Description                 |\n","| ----------------- | ------- | ----------------- | ---------------------------------- |\n","| 2020-10-17        | 0.1     | Ramesh Sannareddy | Created initial version of the lab |\n"]},{"cell_type":"markdown","metadata":{},"source":[" Copyright © 2020 IBM Corporation. This notebook and its source code are released under the terms of the [MIT License](https://cognitiveclass.ai/mit-license?cm_mmc=Email_Newsletter-_-Developer_Ed%2BTech-_-WW_WW-_-SkillsNetwork-Courses-IBM-DA0321EN-SkillsNetwork-21426264&cm_mmca1=000026UJ&cm_mmca2=10006555&cm_mmca3=M12345678&cvosrc=email.Newsletter.M12345678&cvo_campaign=000026UJ&cm_mmc=Email_Newsletter-_-Developer_Ed%2BTech-_-WW_WW-_-SkillsNetwork-Courses-IBM-DA0321EN-SkillsNetwork-21426264&cm_mmca1=000026UJ&cm_mmca2=10006555&cm_mmca3=M12345678&cvosrc=email.Newsletter.M12345678&cvo_campaign=000026UJ&cm_mmc=Email_Newsletter-_-Developer_Ed%2BTech-_-WW_WW-_-SkillsNetwork-Courses-IBM-DA0321EN-SkillsNetwork-21426264&cm_mmca1=000026UJ&cm_mmca2=10006555&cm_mmca3=M12345678&cvosrc=email.Newsletter.M12345678&cvo_campaign=000026UJ&cm_mmc=Email_Newsletter-_-Developer_Ed%2BTech-_-WW_WW-_-SkillsNetwork-Courses-IBM-DA0321EN-SkillsNetwork-21426264&cm_mmca1=000026UJ&cm_mmca2=10006555&cm_mmca3=M12345678&cvosrc=email.Newsletter.M12345678&cvo_campaign=000026UJ&cm_mmc=Email_Newsletter-_-Developer_Ed%2BTech-_-WW_WW-_-SkillsNetwork-Courses-IBM-DA0321EN-SkillsNetwork-21426264&cm_mmca1=000026UJ&cm_mmca2=10006555&cm_mmca3=M12345678&cvosrc=email.Newsletter.M12345678&cvo_campaign=000026UJ&cm_mmc=Email_Newsletter-_-Developer_Ed%2BTech-_-WW_WW-_-SkillsNetwork-Courses-IBM-DA0321EN-SkillsNetwork-21426264&cm_mmca1=000026UJ&cm_mmca2=10006555&cm_mmca3=M12345678&cvosrc=email.Newsletter.M12345678&cvo_campaign=000026UJ).\n"]}],"metadata":{"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.6"}},"nbformat":4,"nbformat_minor":4}