4_Data_Visualization_Using_Python.ipynb

{
 "cells": [
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "<p style=\"text-align:center\">\n",
    "    <a href=\"https://skills.network/?utm_medium=Exinfluencer&utm_source=Exinfluencer&utm_content=000026UJ&utm_term=10006555&utm_id=NA-SkillsNetwork-Channel-SkillsNetworkCoursesIBMDA0321ENSkillsNetwork21426264-2022-01-01\" target=\"_blank\">\n",
    "    <img src=\"https://cf-courses-data.s3.us.cloud-object-storage.appdomain.cloud/assets/logos/SN_web_lightmode.png\" width=\"200\" alt=\"Skills Network Logo\"  />\n",
    "    </a>\n",
    "</p>\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": [
    "# !pip install wget"
   ]
  },
  {
   "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\n",
    "import seaborn as sns\n",
    "import matplotlib.pyplot as plt\n",
    "\n",
    "%matplotlib inline"
   ]
  },
  {
   "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",
    "QUERY = \"\"\"\n",
    "SELECT ConvertedComp\n",
    "FROM master\n",
    "\"\"\"\n",
    "convertedComp = pd.read_sql_query(QUERY,conn)\n",
    "\n",
    "plt.figure(figsize=(12, 6))\n",
    "sns.displot(convertedComp, bins=20)\n",
    "plt.show()"
   ]
  },
  {
   "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",
    "QUERY = \"\"\"\n",
    "SELECT Age\n",
    "FROM master\n",
    "\"\"\"\n",
    "age = pd.read_sql_query(QUERY,conn)\n",
    "\n",
    "plt.figure(figsize=(3, 8))\n",
    "sns.boxplot(data=age)\n",
    "plt.show()\n",
    "\n",
    "# age.plot(kind='box', figsize=(3,8))"
   ]
  },
  {
   "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",
    "QUERY = \"\"\"\n",
    "SELECT Age, WorkWeekHrs\n",
    "FROM master\n",
    "\"\"\"\n",
    "data = pd.read_sql_query(QUERY,conn)\n",
    "\n",
    "# plt.figure(figsize=(3, 8))\n",
    "sns.scatterplot(data=data, x=\"Age\", y=\"WorkWeekHrs\")\n",
    "plt.show()\n",
    "\n",
    "# data"
   ]
  },
  {
   "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",
    "QUERY = \"\"\"\n",
    "SELECT Age, WorkWeekHrs, CodeRevHrs\n",
    "FROM master\n",
    "\"\"\"\n",
    "data = pd.read_sql_query(QUERY,conn)\n",
    "\n",
    "# plt.figure(figsize=(3, 8))\n",
    "sns.scatterplot(data=data, x=\"WorkWeekHrs\", y=\"CodeRevHrs\", size=\"Age\")\n",
    "plt.show()\n",
    "\n",
    "# data\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",
    "QUERY = \"\"\"\n",
    "SELECT DatabaseDesireNextYear, COUNT(*) AS Total\n",
    "FROM DatabaseDesireNextYear\n",
    "GROUP BY DatabaseDesireNextYear\n",
    "ORDER BY Total desc\n",
    "\"\"\"\n",
    "\n",
    "top5databases = pd.read_sql_query(QUERY,conn)\n",
    "# top5 = pd.DataFrame(data=top5databases)\n",
    "# top5.set_index('DatabaseDesireNextYear', inplace=True)\n",
    "top5databases\n",
    "\n",
    "\n",
    "top5['Total'].plot(kind='pie',\n",
    "                            figsize=(5, 6),\n",
    "                            autopct='%1.1f%%', # add in percentages\n",
    "                            startangle=90,     # start angle 90° (Africa)\n",
    "                            shadow=True,       # add shadow      \n",
    "                            )\n",
    "\n",
    "plt.legend(labels=top5.index, loc='upper right')\n",
    "plt.title('Immigration to Canada by Continent [1980 - 2013]')\n",
    "plt.axis('equal') # Sets the pie chart to look like a circle.\n",
    "\n",
    "plt.show()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "# your code goes here\n",
    "# Quiz Question\n",
    "QUERY = \"\"\"\n",
    "SELECT LanguageDesireNextYear, COUNT(*) AS Total\n",
    "FROM LanguageDesireNextYear\n",
    "GROUP BY LanguageDesireNextYear\n",
    "ORDER BY Total desc\n",
    "\"\"\"\n",
    "\n",
    "top5lang = pd.read_sql_query(QUERY,conn)\n",
    "# top5 = pd.DataFrame(data=top5lang)\n",
    "# top5.set_index('LanguageDesireNextYear', inplace=True)\n",
    "top5lang\n",
    "\n",
    "\n",
    "top5['Total'].plot(kind='pie',\n",
    "                            figsize=(5, 6),\n",
    "                            autopct='%1.1f%%', # add in percentages\n",
    "                            startangle=90,     # start angle 90° (Africa)\n",
    "                            shadow=True,       # add shadow      \n",
    "                            )\n",
    "\n",
    "plt.legend(labels=top5.index, loc='upper right')\n",
    "plt.title('Immigration to Canada by Continent [1980 - 2013]')\n",
    "plt.axis('equal') # Sets the pie chart to look like a circle.\n",
    "\n",
    "plt.show()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "QUERY = \"\"\"\n",
    "SELECT DevType, COUNT(DevType)\n",
    "FROM master\n",
    "GROUP BY DevType\n",
    "\"\"\"\n",
    "data = pd.read_sql_query(QUERY,conn)\n",
    "data"
   ]
  },
  {
   "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",
    "QUERY = \"\"\"\n",
    "SELECT WorkWeekHrs, CodeRevHrs\n",
    "FROM master\n",
    "WHERE Age BETWEEN 30 AND 35\n",
    "GROUP BY Age\n",
    "\"\"\"\n",
    "data = pd.read_sql_query(QUERY,conn)\n",
    "\n",
    "data.plot(kind='bar', stacked=True, figsize=(10,6))\n",
    "plt.show()"
   ]
  },
  {
   "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",
    "QUERY = \"\"\"\n",
    "SELECT Age, ConvertedComp\n",
    "FROM master\n",
    "WHERE Age BETWEEN 45 AND 60\n",
    "\"\"\"\n",
    "\n",
    "data = pd.read_sql_query(QUERY,conn)\n",
    "median_data = data.groupby('Age').median()\n",
    "median_data\n",
    "\n",
    "sns.lineplot(data=median_data, x=\"Age\", y=\"ConvertedComp\")\n",
    "plt.show()"
   ]
  },
  {
   "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": {
    "scrolled": true
   },
   "outputs": [],
   "source": [
    "# your code goes here\n",
    "QUERY = \"\"\"\n",
    "SELECT MainBranch, COUNT(MainBranch) AS count\n",
    "FROM master\n",
    "GROUP BY MainBranch\n",
    "\"\"\"\n",
    "\n",
    "data = pd.read_sql_query(QUERY, conn)\n",
    "mbranch = pd.DataFrame(data=data)\n",
    "mbranch.set_index('MainBranch', inplace=True)\n",
    "mbranch\n",
    "\n",
    "mbranch.plot(kind=\"barh\", figsize=(12,6))\n",
    "plt.show()"
   ]
  },
  {
   "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?utm_medium=Exinfluencer&utm_source=Exinfluencer&utm_content=000026UJ&utm_term=10006555&utm_id=NA-SkillsNetwork-Channel-SkillsNetworkCoursesIBMDA0321ENSkillsNetwork21426264-2022-01-01&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 (ipykernel)",
   "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.9.7"
  }
 },
 "nbformat": 4,
 "nbformat_minor": 4
}