<jupyter_start><jupyter_text>**Data Visualization Lab** Estimated time needed: **45 to 60** minutes In this assignment you will be focusing on the visualization of data.The data set will be presented to you in the form of a RDBMS.You will have to use SQL queries to extract the data.  Objectives In this lab you will perform the following: *   Visualize the distribution of data.*   Visualize the relationship between two features.*   Visualize composition of data.*   Visualize comparison of data.   Demo: How to work with database Download database file.<jupyter_code>!wget https://cf-courses-data.s3.us.cloud-object-storage.appdomain.cloud/IBM-DA0321EN-SkillsNetwork/LargeData/m4_survey_data.sqlite<jupyter_output>--2021-07-12 18:22:50--  https://cf-courses-data.s3.us.cloud-object-storage.appdomain.cloud/IBM-DA0321EN-SkillsNetwork/LargeData/m4_survey_data.sqlite
Resolving cf-courses-data.s3.us.cloud-object-storage.appdomain.cloud (cf-courses-data.s3.us.cloud-object-storage.appdomain.cloud)... 192.168.3.11
Connecting to cf-courses-data.s3.us.cloud-object-storage.appdomain.cloud (cf-courses-data.s3.us.cloud-object-storage.appdomain.cloud)|192.168.3.11|:443... connected.
HTTP request sent, awaiting response... 200 OK
Length: 36679680 (35M) [application/octet-stream]
Saving to: ‘m4_survey_data.sqlite.1’

m4_survey_data.sqli 100%[===================>]  34.98M  33.3MB/s    in 1.1s    

2021-07-12 18:22:51 (33.3 MB/s) - ‘m4_survey_data.sqlite.1’ saved [36679680/36679680]<jupyter_text>Connect to the database.<jupyter_code>import sqlite3
conn = sqlite3.connect("m4_survey_data.sqlite") # open a database connection<jupyter_output><empty_output><jupyter_text>Import pandas module.<jupyter_code>import numpy as np
import pandas as pd
import matplotlib.pyplot as plt
import seaborn as sns

%matplotlib inline<jupyter_output><empty_output><jupyter_text>Demo: How to run an sql query<jupyter_code># print how many rows are there in the table named 'master'
QUERY = """
SELECT *
FROM master
"""

# the read_sql_query runs the sql query and returns the data as a dataframe
df = pd.read_sql_query(QUERY,conn)
df.head()<jupyter_output><empty_output><jupyter_text>Demo: How to list all tables<jupyter_code># print all the tables names in the database
QUERY = """
SELECT name as Table_Name FROM
sqlite_master WHERE
type = 'table'
"""
# the read_sql_query runs the sql query and returns the data as a dataframe
pd.read_sql_query(QUERY,conn)<jupyter_output><empty_output><jupyter_text>Demo: How to run a group by query<jupyter_code>QUERY = """
SELECT Age,COUNT(*) as count
FROM master
group by age
order by age
"""
pd.read_sql_query(QUERY,conn)<jupyter_output><empty_output><jupyter_text>Demo: How to describe a table<jupyter_code>table_name = 'master'  # the table you wish to describe

QUERY = """
SELECT sql FROM sqlite_master
WHERE name= '{}'
""".format(table_name)

df = pd.read_sql_query(QUERY,conn)
print(df.iat[0,0])<jupyter_output>CREATE TABLE "master" (
"index" INTEGER,
  "Respondent" INTEGER,
  "MainBranch" TEXT,
  "Hobbyist" TEXT,
  "OpenSourcer" TEXT,
  "OpenSource" TEXT,
  "Employment" TEXT,
  "Country" TEXT,
  "Student" TEXT,
  "EdLevel" TEXT,
  "UndergradMajor" TEXT,
  "OrgSize" TEXT,
  "YearsCode" TEXT,
  "Age1stCode" TEXT,
  "YearsCodePro" TEXT,
  "CareerSat" TEXT,
  "JobSat" TEXT,
  "MgrIdiot" TEXT,
  "MgrMoney" TEXT,
  "MgrWant" TEXT,
  "JobSeek" TEXT,
  "LastHireDate" TEXT,
  "FizzBuzz" TEXT,
  "ResumeUpdate" TEXT,
  "CurrencySymbol" TEXT,
  "CurrencyDesc" TEXT,
  "CompTotal" REAL,
  "CompFreq" TEXT,
  "ConvertedComp" REAL,
  "WorkWeekHrs" REAL,
  "WorkRemote" TEXT,
  "WorkLoc" TEXT,
  "ImpSyn" TEXT,
  "CodeRev" TEXT,
  "CodeRevHrs" REAL,
  "UnitTests" TEXT,
  "PurchaseHow" TEXT,
  "PurchaseWhat" TEXT,
  "OpSys" TEXT,
  "BlockchainOrg" TEXT,
  "BlockchainIs" TEXT,
  "BetterLife" TEXT,
  "ITperson" TEXT,
  "OffOn" TEXT,
  "SocialMedia" TEXT,
  "Extraversion" TEXT,
  "ScreenName" TEXT,
  "SOVisit1st" T[...]<jupyter_text>Hands-on Lab  Visualizing distribution of data  Histograms Plot a histogram of `ConvertedComp.`<jupyter_code># your code goes here
df["ConvertedComp"].plot(kind='hist', figsize=(8, 5))

plt.xlabel("CovertedComp")
plt.show()<jupyter_output><empty_output><jupyter_text>Box Plots Plot a box plot of `Age.`<jupyter_code># your code goes here

plt.figure(figsize=(25,5))
sns.boxplot(x=df.Age, data=df)
plt.show()<jupyter_output><empty_output><jupyter_text>Visualizing relationships in data  Scatter Plots Create a scatter plot of `Age` and `WorkWeekHrs.`<jupyter_code># your code goes here

df.plot(kind='scatter', x='Age', y='WorkWeekHrs', figsize=(20, 10), color='darkblue')

plt.title('WorkWeekHrs in different Age')
plt.xlabel('Age')
plt.ylabel('WorkWeekHrs')

plt.show()<jupyter_output><empty_output><jupyter_text>Bubble Plots Create a bubble plot of `WorkWeekHrs` and `CodeRevHrs`, use `Age` column as bubble size.<jupyter_code># your code goes here

ax0 = df.plot(kind='scatter',
                    x='WorkWeekHrs',
                    y='CodeRevHrs',
                    figsize=(20,10),
                    alpha=0.5,
                    color="red",
                    s=df.Age*5,
                    )

ax0.set_xlabel('WorkWeekHrs')
ax0.set_ylabel('CodeRevHrs')
ax0.set_title('CodeRevHrs VS WorkWeekHrs')<jupyter_output><empty_output><jupyter_text>Visualizing composition of data  Pie Charts 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.<jupyter_code># your code goes here

df_pie = pd.read_sql_query("SELECT * FROM DatabaseDesireNextYear",conn)
df_pie = df_pie.groupby("DatabaseDesireNextYear").count()
df_pie = df_pie.sort_values(by="Respondent", ascending=False)
df_pie = df_pie.head(5)

plt.figure(figsize=(10,10))
plt.pie(df_pie.Respondent, labels=['PostgreSQL','MongoDB','Redis','MySQL','Elasticsearch'], autopct='%.2f%%')
plt.title("Database Desire To Learn Next Year")

plt.show()
df_pie

#In the list of most popular languages respondents wish to learn next year, what is the rank of Python?

df_python = pd.read_sql_query("SELECT * FROM LanguageDesireNextYear",conn)
df_python = df_python.groupby("LanguageDesireNextYear").count()
df_python = df_python.sort_values(by="Respondent", ascending=False)
df_p = df_python.reset_index()
df_p2 = df_p[df_p['LanguageDesireNextYear']=="Python"].index.values.astype(int)
int(df_p2)+1
#How many respondents indicated that they currently work with 'SQL'? 


df_sql = pd.read_sql("SELECT * FROM LanguageWorkedWith", conn)
df_sql = df_sql[df_sql["LanguageWorkedWith"].str.contains("SQL")]
df_sql<jupyter_output><empty_output><jupyter_text>Stacked Charts<jupyter_code>#How many respondents indicated that they work on 'MySQL' only? 

import numpy as np


df_sql = pd.read_sql("SELECT * FROM DatabaseWorkedWith", conn)
df_mysql = df_sql[df_sql["DatabaseWorkedWith"].str.contains("MySQL")]
df_withoutmysql = df_sql[~df_sql["DatabaseWorkedWith"].str.contains("MySQL")]
list1 = df_withoutmysql["Respondent"].values.tolist()
set(list1)
list2 = df_mysql["Respondent"].values.tolist()
set(list2)

main_list = np.setdiff1d(list2,list1)
len(main_list)<jupyter_output><empty_output><jupyter_text>Create a stacked chart of median `WorkWeekHrs` and `CodeRevHrs` for the age group 30 to 35.<jupyter_code># your code goes here
QUERY1 = """
select WorkWeekHrs, CodeRevHrs, Age from master
where Age between 30 and 35
"""
df_stacked = pd.read_sql_query(QUERY1,conn)
df_stacked.groupby('Age').median().plot.bar(figsize=(20,10),stacked=True)<jupyter_output><empty_output><jupyter_text>Visualizing comparison of data  Line Chart Plot the median `ConvertedComp` for all ages from 45 to 60.<jupyter_code># your code goes here
QUERY2="""
Select ConvertedComp,Age from master
where age between 45 and 60
"""

df_line = pd.read_sql_query(QUERY2,conn)
df_line.groupby(['Age'])['ConvertedComp'].median().plot.line(figsize=(20,10))

#Based on the Line Chart of Age and median ConvertedComp for the age group 25-30, what kind of correlation do you see between Age and median ConvertedComp? 

QUERY3="""
Select ConvertedComp,Age from master
where age between 25 and 30
"""

df_line1 = pd.read_sql_query(QUERY3,conn)
df_line1.groupby(['Age'])['ConvertedComp'].median().plot.line(figsize=(20,10))<jupyter_output><empty_output><jupyter_text>Bar Chart Create a horizontal bar chart using column `MainBranch.`<jupyter_code># your code goes here

df_bar = pd.DataFrame(df.MainBranch.value_counts())
df_bar

plt.figure(figsize=(10,10))
sns.barplot(x=df_bar.MainBranch,y=df_bar.index, orient='h')
plt.show()
df_bar
#Majority of the survey responders are?

df_job = pd.read_sql_query("SELECT * FROM DevType",conn)
df_job = df_job.groupby("DevType").count()
df_job = df_job.sort_values(by="Respondent", ascending=False)
df_job.head()<jupyter_output><empty_output><jupyter_text>Close the database connection.<jupyter_code>conn.close()<jupyter_output><empty_output>
{'fraction_non_alphanumeric': 0.12569707784965425, 'fraction_numerical': 0.023421815748382778, 'mean_word_length': 5.696788648244959, 'pattern_counts': {'":': 0, '<': 67, '<?xml version=': 0, '>': 68, 'https://': 2, 'lorem ipsum': 0, 'www.': 0, 'xml': 0}, 'pii_count': 2, 'substrings_counts': 0, 'word_list_counts': {'cursed_substrings.json': 1, 'profanity_word_list.json': 0, 'sexual_word_list.json': 0, 'zh_pornsignals.json': 0}}
{'chain_length': '17', 'dir': 'jupyter-structured-clean-dedup', 'id': '182671', 'license': 'Apache-2.0', 'n_tokens_mistral': 3374, 'n_tokens_neox': 3175, 'n_words': 759, 'path': 'Step_4_DataVisualization.ipynb', 'repo_name': 'johnleung8888/IBM_Data_Analyst_Capstone_Project', 'size': '8970'}