Skip to content

Instantly share code, notes, and snippets.

@mde-2590

mde-2590/Lab5 SQLmagic-v3-py.ipynb

Created May 9, 2019 01:10
Show Gist options
  • Save mde-2590/402363633dcb56da6040c2e22ece14dd to your computer and use it in GitHub Desktop.
Save mde-2590/402363633dcb56da6040c2e22ece14dd to your computer and use it in GitHub Desktop.
Download ZIP
Created on Cognitive Class Labs
Display the source blob
Display the rendered blob
Raw
Lab5 SQLmagic-v3-py.ipynb
{
"cells": [
{
"cell_type": "code",
"execution_count": 1,
"metadata": {},
"outputs": [],
"source": [
"#Below, we'll use the load_ext magic to load the ipython-sql extension. \n",
"\n",
"%load_ext sql"
]
},
{
"cell_type": "code",
"execution_count": 2,
"metadata": {},
"outputs": [
{
"data": {
"text/plain": [
"'Connected: dfk30111@BLUDB'"
]
},
"execution_count": 2,
"metadata": {},
"output_type": "execute_result"
}
],
"source": [
"# Enter your Db2 credentials in the connection string below\n",
"# Recall you created Service Credentials in Part III of the first lab of the course in Week 1\n",
"# i.e. from the uri field in the Service Credentials copy everything after db2:// (but remove the double quote at the end)\n",
"# for example, if your credentials are as in the screenshot above, you would write:\n",
"# %sql ibm_db_sa://my-username:my-password@dashdb-txn-sbox-yp-dal09-03.services.dal.bluemix.net:50000/BLUDB\n",
"# Note the ibm_db_sa:// prefix instead of db2://\n",
"# This is because JupyterLab's ipython-sql extension uses sqlalchemy (a python SQL toolkit)\n",
"# which in turn uses IBM's sqlalchemy dialect: ibm_db_sa\n",
"\n",
"%sql ibm_db_sa://dfk30111:b6q01rtcjz%5Eq4sk8@dashdb-txn-sbox-yp-dal09-04.services.dal.bluemix.net:50000/BLUDB"
]
},
{
"cell_type": "code",
"execution_count": null,
"metadata": {},
"outputs": [],
"source": [
"#Use %%sql (two %'s instead of one) at the top of a cell to indicate we want the entire cell to be treated as SQL. Let's use this to create a table and fill it with some test data for experimenting"
]
},
{
"cell_type": "code",
"execution_count": 13,
"metadata": {},
"outputs": [
{
"name": "stdout",
"output_type": "stream",
"text": [
" * ibm_db_sa://dfk30111:***@dashdb-txn-sbox-yp-dal09-04.services.dal.bluemix.net:50000/BLUDB\n",
"Done.\n",
"99 rows affected.\n"
]
},
{
"data": {
"text/plain": [
"[]"
]
},
"execution_count": 13,
"metadata": {},
"output_type": "execute_result"
}
],
"source": [
"%%sql\n",
"\n",
"CREATE TABLE INTERNATIONAL_STUDENT_TEST_SCORES (\n",
"\tcountry VARCHAR(50),\n",
"\tfirst_name VARCHAR(50),\n",
"\tlast_name VARCHAR(50),\n",
"\ttest_score INT\n",
");\n",
"INSERT INTO INTERNATIONAL_STUDENT_TEST_SCORES (country, first_name, last_name, test_score)\n",
"VALUES\n",
"('United States', 'Marshall', 'Bernadot', 54),\n",
"('Ghana', 'Celinda', 'Malkin', 51),\n",
"('Ukraine', 'Guillermo', 'Furze', 53),\n",
"('Greece', 'Aharon', 'Tunnow', 48),\n",
"('Russia', 'Bail', 'Goodwin', 46),\n",
"('Poland', 'Cole', 'Winteringham', 49),\n",
"('Sweden', 'Emlyn', 'Erricker', 55),\n",
"('Russia', 'Cathee', 'Sivewright', 49),\n",
"('China', 'Barny', 'Ingerson', 57),\n",
"('Uganda', 'Sharla', 'Papaccio', 55),\n",
"('China', 'Stella', 'Youens', 51),\n",
"('Poland', 'Julio', 'Buesden', 48),\n",
"('United States', 'Tiffie', 'Cosely', 58),\n",
"('Poland', 'Auroora', 'Stiffell', 45),\n",
"('China', 'Clarita', 'Huet', 52),\n",
"('Poland', 'Shannon', 'Goulden', 45),\n",
"('Philippines', 'Emylee', 'Privost', 50),\n",
"('France', 'Madelina', 'Burk', 49),\n",
"('China', 'Saunderson', 'Root', 58),\n",
"('Indonesia', 'Bo', 'Waring', 55),\n",
"('China', 'Hollis', 'Domotor', 45),\n",
"('Russia', 'Robbie', 'Collip', 46),\n",
"('Philippines', 'Davon', 'Donisi', 46),\n",
"('China', 'Cristabel', 'Radeliffe', 48),\n",
"('China', 'Wallis', 'Bartleet', 58),\n",
"('Moldova', 'Arleen', 'Stailey', 38),\n",
"('Ireland', 'Mendel', 'Grumble', 58),\n",
"('China', 'Sallyann', 'Exley', 51),\n",
"('Mexico', 'Kain', 'Swaite', 46),\n",
"('Indonesia', 'Alonso', 'Bulteel', 45),\n",
"('Armenia', 'Anatol', 'Tankus', 51),\n",
"('Indonesia', 'Coralyn', 'Dawkins', 48),\n",
"('China', 'Deanne', 'Edwinson', 45),\n",
"('China', 'Georgiana', 'Epple', 51),\n",
"('Portugal', 'Bartlet', 'Breese', 56),\n",
"('Azerbaijan', 'Idalina', 'Lukash', 50),\n",
"('France', 'Livvie', 'Flory', 54),\n",
"('Malaysia', 'Nonie', 'Borit', 48),\n",
"('Indonesia', 'Clio', 'Mugg', 47),\n",
"('Brazil', 'Westley', 'Measor', 48),\n",
"('Philippines', 'Katrinka', 'Sibbert', 51),\n",
"('Poland', 'Valentia', 'Mounch', 50),\n",
"('Norway', 'Sheilah', 'Hedditch', 53),\n",
"('Papua New Guinea', 'Itch', 'Jubb', 50),\n",
"('Latvia', 'Stesha', 'Garnson', 53),\n",
"('Canada', 'Cristionna', 'Wadmore', 46),\n",
"('China', 'Lianna', 'Gatward', 43),\n",
"('Guatemala', 'Tanney', 'Vials', 48),\n",
"('France', 'Alma', 'Zavittieri', 44),\n",
"('China', 'Alvira', 'Tamas', 50),\n",
"('United States', 'Shanon', 'Peres', 45),\n",
"('Sweden', 'Maisey', 'Lynas', 53),\n",
"('Indonesia', 'Kip', 'Hothersall', 46),\n",
"('China', 'Cash', 'Landis', 48),\n",
"('Panama', 'Kennith', 'Digance', 45),\n",
"('China', 'Ulberto', 'Riggeard', 48),\n",
"('Switzerland', 'Judy', 'Gilligan', 49),\n",
"('Philippines', 'Tod', 'Trevaskus', 52),\n",
"('Brazil', 'Herold', 'Heggs', 44),\n",
"('Latvia', 'Verney', 'Note', 50),\n",
"('Poland', 'Temp', 'Ribey', 50),\n",
"('China', 'Conroy', 'Egdal', 48),\n",
"('Japan', 'Gabie', 'Alessandone', 47),\n",
"('Ukraine', 'Devlen', 'Chaperlin', 54),\n",
"('France', 'Babbette', 'Turner', 51),\n",
"('Czech Republic', 'Virgil', 'Scotney', 52),\n",
"('Tajikistan', 'Zorina', 'Bedow', 49),\n",
"('China', 'Aidan', 'Rudeyeard', 50),\n",
"('Ireland', 'Saunder', 'MacLice', 48),\n",
"('France', 'Waly', 'Brunstan', 53),\n",
"('China', 'Gisele', 'Enns', 52),\n",
"('Peru', 'Mina', 'Winchester', 48),\n",
"('Japan', 'Torie', 'MacShirrie', 50),\n",
"('Russia', 'Benjamen', 'Kenford', 51),\n",
"('China', 'Etan', 'Burn', 53),\n",
"('Russia', 'Merralee', 'Chaperlin', 38),\n",
"('Indonesia', 'Lanny', 'Malam', 49),\n",
"('Canada', 'Wilhelm', 'Deeprose', 54),\n",
"('Czech Republic', 'Lari', 'Hillhouse', 48),\n",
"('China', 'Ossie', 'Woodley', 52),\n",
"('Macedonia', 'April', 'Tyer', 50),\n",
"('Vietnam', 'Madelon', 'Dansey', 53),\n",
"('Ukraine', 'Korella', 'McNamee', 52),\n",
"('Jamaica', 'Linnea', 'Cannam', 43),\n",
"('China', 'Mart', 'Coling', 52),\n",
"('Indonesia', 'Marna', 'Causbey', 47),\n",
"('China', 'Berni', 'Daintier', 55),\n",
"('Poland', 'Cynthia', 'Hassell', 49),\n",
"('Canada', 'Carma', 'Schule', 49),\n",
"('Indonesia', 'Malia', 'Blight', 48),\n",
"('China', 'Paulo', 'Seivertsen', 47),\n",
"('Niger', 'Kaylee', 'Hearley', 54),\n",
"('Japan', 'Maure', 'Jandak', 46),\n",
"('Argentina', 'Foss', 'Feavers', 45),\n",
"('Venezuela', 'Ron', 'Leggitt', 60),\n",
"('Russia', 'Flint', 'Gokes', 40),\n",
"('China', 'Linet', 'Conelly', 52),\n",
"('Philippines', 'Nikolas', 'Birtwell', 57),\n",
"('Australia', 'Eduard', 'Leipelt', 53)\n"
]
},
{
"cell_type": "code",
"execution_count": null,
"metadata": {},
"outputs": [],
"source": [
"#Using Python Variables in your SQL Statements\n",
"#You can use python variables in your SQL statements by adding a \":\" prefix to your python variable names.\n",
"#For example, if I have a python variable country with a value of \"Canada\", I can use this variable in a SQL query to find all the rows of students from Canada."
]
},
{
"cell_type": "code",
"execution_count": 14,
"metadata": {},
"outputs": [
{
"name": "stdout",
"output_type": "stream",
"text": [
" * ibm_db_sa://dfk30111:***@dashdb-txn-sbox-yp-dal09-04.services.dal.bluemix.net:50000/BLUDB\n",
"Done.\n"
]
},
{
"data": {
"text/html": [
"<table>\n",
" <tr>\n",
" <th>country</th>\n",
" <th>first_name</th>\n",
" <th>last_name</th>\n",
" <th>test_score</th>\n",
" </tr>\n",
" <tr>\n",
" <td>Canada</td>\n",
" <td>Cristionna</td>\n",
" <td>Wadmore</td>\n",
" <td>46</td>\n",
" </tr>\n",
" <tr>\n",
" <td>Canada</td>\n",
" <td>Wilhelm</td>\n",
" <td>Deeprose</td>\n",
" <td>54</td>\n",
" </tr>\n",
" <tr>\n",
" <td>Canada</td>\n",
" <td>Carma</td>\n",
" <td>Schule</td>\n",
" <td>49</td>\n",
" </tr>\n",
"</table>"
],
"text/plain": [
"[('Canada', 'Cristionna', 'Wadmore', 46),\n",
" ('Canada', 'Wilhelm', 'Deeprose', 54),\n",
" ('Canada', 'Carma', 'Schule', 49)]"
]
},
"execution_count": 14,
"metadata": {},
"output_type": "execute_result"
}
],
"source": [
"country = \"Canada\"\n",
"%sql select * from INTERNATIONAL_STUDENT_TEST_SCORES where country = :country"
]
},
{
"cell_type": "code",
"execution_count": 15,
"metadata": {},
"outputs": [],
"source": [
"#Assigning the Results of Queries to Python Variables\n",
"#You can use the normal python assignment syntax to assign the results of your queries to python variables.\n",
"#For example, I have a SQL query to retrieve the distribution of test scores (i.e. how many students got each score). I can assign the result of this query to the variable test_score_distribution using the = operator"
]
},
{
"cell_type": "code",
"execution_count": 16,
"metadata": {},
"outputs": [
{
"name": "stdout",
"output_type": "stream",
"text": [
" * ibm_db_sa://dfk30111:***@dashdb-txn-sbox-yp-dal09-04.services.dal.bluemix.net:50000/BLUDB\n",
"Done.\n"
]
},
{
"data": {
"text/html": [
"<table>\n",
" <tr>\n",
" <th>Test Score</th>\n",
" <th>Frequency</th>\n",
" </tr>\n",
" <tr>\n",
" <td>38</td>\n",
" <td>2</td>\n",
" </tr>\n",
" <tr>\n",
" <td>40</td>\n",
" <td>1</td>\n",
" </tr>\n",
" <tr>\n",
" <td>43</td>\n",
" <td>2</td>\n",
" </tr>\n",
" <tr>\n",
" <td>44</td>\n",
" <td>2</td>\n",
" </tr>\n",
" <tr>\n",
" <td>45</td>\n",
" <td>8</td>\n",
" </tr>\n",
" <tr>\n",
" <td>46</td>\n",
" <td>7</td>\n",
" </tr>\n",
" <tr>\n",
" <td>47</td>\n",
" <td>4</td>\n",
" </tr>\n",
" <tr>\n",
" <td>48</td>\n",
" <td>14</td>\n",
" </tr>\n",
" <tr>\n",
" <td>49</td>\n",
" <td>8</td>\n",
" </tr>\n",
" <tr>\n",
" <td>50</td>\n",
" <td>10</td>\n",
" </tr>\n",
" <tr>\n",
" <td>51</td>\n",
" <td>8</td>\n",
" </tr>\n",
" <tr>\n",
" <td>52</td>\n",
" <td>8</td>\n",
" </tr>\n",
" <tr>\n",
" <td>53</td>\n",
" <td>8</td>\n",
" </tr>\n",
" <tr>\n",
" <td>54</td>\n",
" <td>5</td>\n",
" </tr>\n",
" <tr>\n",
" <td>55</td>\n",
" <td>4</td>\n",
" </tr>\n",
" <tr>\n",
" <td>56</td>\n",
" <td>1</td>\n",
" </tr>\n",
" <tr>\n",
" <td>57</td>\n",
" <td>2</td>\n",
" </tr>\n",
" <tr>\n",
" <td>58</td>\n",
" <td>4</td>\n",
" </tr>\n",
" <tr>\n",
" <td>60</td>\n",
" <td>1</td>\n",
" </tr>\n",
"</table>"
],
"text/plain": [
"[(38, Decimal('2')),\n",
" (40, Decimal('1')),\n",
" (43, Decimal('2')),\n",
" (44, Decimal('2')),\n",
" (45, Decimal('8')),\n",
" (46, Decimal('7')),\n",
" (47, Decimal('4')),\n",
" (48, Decimal('14')),\n",
" (49, Decimal('8')),\n",
" (50, Decimal('10')),\n",
" (51, Decimal('8')),\n",
" (52, Decimal('8')),\n",
" (53, Decimal('8')),\n",
" (54, Decimal('5')),\n",
" (55, Decimal('4')),\n",
" (56, Decimal('1')),\n",
" (57, Decimal('2')),\n",
" (58, Decimal('4')),\n",
" (60, Decimal('1'))]"
]
},
"execution_count": 16,
"metadata": {},
"output_type": "execute_result"
}
],
"source": [
"test_score_distribution = %sql SELECT test_score as \"Test Score\", count(*) as \"Frequency\" from INTERNATIONAL_STUDENT_TEST_SCORES GROUP BY test_score;\n",
"test_score_distribution"
]
},
{
"cell_type": "code",
"execution_count": 17,
"metadata": {},
"outputs": [],
"source": [
"#Converting Query Results to DataFrames\n",
"#You can easily convert a SQL query result to a pandas dataframe using the DataFrame() method. Dataframe objects are much more versatile than SQL query result objects. For example, we can easily graph our test score distribution after converting to a dataframe."
]
},
{
"cell_type": "code",
"execution_count": 18,
"metadata": {},
"outputs": [
{
"data": {
"image/png": "iVBORw0KGgoAAAANSUhEUgAAAYIAAAEKCAYAAAAfGVI8AAAABHNCSVQICAgIfAhkiAAAAAlwSFlzAAALEgAACxIB0t1+/AAAADl0RVh0U29mdHdhcmUAbWF0cGxvdGxpYiB2ZXJzaW9uIDMuMC4zLCBodHRwOi8vbWF0cGxvdGxpYi5vcmcvnQurowAAGApJREFUeJzt3XmYJHWd5/H3B1oUFESlOBTbVsfBa7y2ZcdxHRVEURlEAcX1YDjsGV3xGBnF0RGc0V1HGfGYVbdFDgEvQFwGBGlUPJ5VkKORU/FAQVTAC08Q+O4fES1J0VWVFVWZ2U28X89TT2VERnx/38yMqm/+4vhFqgpJUn9tMOkEJEmTZSGQpJ6zEEhSz1kIJKnnLASS1HMWAknqOQuBJPWchUCSes5CIEk9t2TSCQxjiy22qGXLlk06DUlar5x33nnXV9XUXMutF4Vg2bJlnHvuuZNOQ5LWK0l+MMxy7hqSpJ6zEEhSz1kIJKnnLASS1HMWAknquZEVgiRHJLk2ycVree7AJJVki1G1L0kazih7BEcBO0+fmeT+wE7AD0fYtiRpSCMrBFX1ZeDna3nqMOD1gPfIlKR1wFiPESTZFfhRVV04znYlSTMb25XFSTYB3gQ8fcjlVwArAJYuXTrCzLQueuZnDui03mm7vX+RM5Hu/MbZI3gw8EDgwiRXAtsC5yfZem0LV9XKqlpeVcunpuYcKkOS1NHYegRVdRGw5Zrpthgsr6rrx5WDJOmORnn66MeBrwHbJbk6yX6jakuS1N3IegRV9cI5nl82qrYlScPzymJJ6jkLgST1nIVAknrOQiBJPWchkKSesxBIUs9ZCCSp5ywEktRzFgJJ6jkLgST1nIVAknrOQiBJPWchkKSesxBIUs9ZCCSp5ywEktRzFgJJ6jkLgST1nIVAknrOQiBJPWchkKSeG1khSHJEkmuTXDww711JLk/yzSQnJdl8VO1LkoYzyh7BUcDO0+atAh5ZVY8Cvg28cYTtS5KGMLJCUFVfBn4+bd4ZVXVzO/l1YNtRtS9JGs4kjxHsC5w205NJViQ5N8m511133RjTkqR+mUghSPIm4GbguJmWqaqVVbW8qpZPTU2NLzlJ6pkl424wyd7ALsCOVVXjbl+SdHtjLQRJdgbeADy5qn43zrYlSWs3ytNHPw58DdguydVJ9gP+A9gUWJVkdZIPjap9SdJwRtYjqKoXrmX2R0bVniSpG68slqSesxBIUs9ZCCSp5ywEktRzFgJJ6jkLgST1nIVAknrOQiBJPWchkKSesxBIUs9ZCCSp5ywEktRzFgJJ6jkLgST1nIVAknrOQiBJPWchkKSesxBIUs9ZCCSp5ywEktRzIysESY5Icm2Siwfm3TvJqiRXtL/vNar2JUnDGWWP4Chg52nzDgI+X1UPAT7fTkuSJmhkhaCqvgz8fNrs5wBHt4+PBnYbVfuSpOGM+xjBVlX1Y4D295Zjbl+SNM2SSScwkyQrgBUAS5cunXA26rNdTjyi03qn7L7vImcijca4ewQ/TbINQPv72pkWrKqVVbW8qpZPTU2NLUFJ6ptxF4KTgb3bx3sD/3fM7UuSphnl6aMfB74GbJfk6iT7Ae8AdkpyBbBTOy1JmqCRHSOoqhfO8NSOo2pTkjR/XlksST1nIZCknrMQSFLPWQgkqecsBJLUcxYCSeo5C4Ek9ZyFQJJ6zkIgST1nIZCknhuqECR55KgTkSRNxrA9gg8lOSfJK5JsPtKMJEljNVQhqKr/BrwIuD9wbpKPJdlppJlJksZi6GMEVXUF8GbgDcCTgfcluTzJ80aVnCRp9IY9RvCoJIcBlwE7AH9TVQ9rHx82wvwkSSM27P0I/gP4MPBPVfX7NTOr6pokbx5JZpKksRi2EDwL+H1V3QKQZAPgblX1u6o6ZmTZSZJGbthjBGcCGw9Mb9LOkySt54YtBHerqt+smWgfbzKalCRJ4zRsIfhtksetmUjyX4Dfz7K8JGk9MewxgtcAxye5pp3eBnjBaFKSJI3TUIWgqr6R5KHAdkCAy6vqj10bTfJaYH+ggIuAfarqD13jSZK6m8+gc48HHgU8Fnhhkpd2aTDJ/YBXAcur6pHAhsBeXWJJkhZuqB5BkmOABwOrgVva2QV8dAHtbpzkjzQHna+ZY3lJ0ogMe4xgOfDwqqqFNlhVP0pyKPBDmgPOZ1TVGdOXS7ICWAGwdOnShTZ7p/aJI5/Rab299vncImey7nn2p/+903qnPu91i5wJ7HL8CZ3WO2XPPW43vdsJ3c7c/sweT/vT4z1P/GanGMfv/qhO62ndNuyuoYuBrRejwST3Ap4DPBC4L3D3JC+evlxVrayq5VW1fGpqajGaliStxbA9gi2AS5OcA9y4ZmZV7dqhzacB36+q6wCSfBr4K+DYDrEkSQs0bCE4ZBHb/CHwl0k2odk1tCNw7iLGlyTNw7Cnj34pyQOAh1TVme0/8Q27NFhVZyc5ATgfuBm4AFjZJZYkaeGGPWvoZTQHbu9Nc/bQ/YAP0Xybn7eqOhg4uMu6kqTFNezB4v8BPBG4Af50k5otR5WUJGl8hi0EN1bVTWsmkiyhuY5AkrSeG7YQfCnJP9FcBLYTcDzwn6NLS5I0LsMWgoOA62jGBfo74LM09y+WJK3nhj1r6FaaW1V+eLTpSJLGbdizhr7PWo4JVNWDFj0jSdJYzWesoTXuBuxJcyqpJGk9N9Qxgqr62cDPj6rqPcAOI85NkjQGw+4aetzA5AY0PYRNR5KRJGmsht01NDiW783AlcDzFz0bTdT7j+s2nPUBL7rzD2ct3ZkNe9bQU0ediCRpMobdNfQPsz1fVe9enHQkSeM2n7OGHg+c3E7/DfBl4KpRJCVJGp/53JjmcVX1a4AkhwDHV9X+o0pMkjQeww4xsRS4aWD6JmDZomcjSRq7YXsExwDnJDmJ5grj5wIfHVlWkqSxGfasobcnOQ14Ujtrn6q6YHRpSZLGZdhdQwCbADdU1XuBq5M8cEQ5SZLGaKhCkORg4A3AG9tZdwGOHVVSkqTxGbZH8FxgV+C3AFV1DQ4xIUl3CsMWgpuqqmiHok5y94U0mmTzJCckuTzJZUmesJB4kqTuhi0En0ryf4DNk7wMOJOF3aTmvcDpVfVQ4NHAZQuIJUlagGHPGjq0vVfxDcB2wFuqalWXBpNsBvw18Ldt7Ju4/TUKkqQxmrMQJNkQ+FxVPQ3o9M9/mgfR3P/4yCSPBs4DXl1Vv12E2JKkeZpz11BV3QL8Lsk9F6nNJcDjgA9W1WNpDkAfNH2hJCuSnJvk3Ouuu26RmpYkTTfslcV/AC5Ksor2zCGAqnpVhzavBq6uqrPb6RNYSyGoqpXASoDly5ff4X7JkqTFMWwhOLX9WbCq+kmSq5JsV1XfAnYELl2M2JKk+Zu1ECRZWlU/rKqjF7ndA4DjkmwEfA/YZ5HjS5KGNFeP4DM0+/NJcmJV7b4YjVbVapp7HEiSJmyug8UZePygUSYiSZqMuQpBzfBYknQnMdeuoUcnuYGmZ7Bx+5h2uqpqs5FmJ0kauVkLQVVtOK5EJEmTMZ/7EUiS7oQsBJLUcxYCSeo5C4Ek9ZyFQJJ6zkIgST037KBzknQHbz/px53We9Nzt1nkTLQQ9ggkqecsBJLUcxYCSeo5C4Ek9ZyFQJJ6zkIgST1nIZCknrMQSFLPWQgkqecsBJLUcxMrBEk2THJBklMmlYMkabI9glcDl02wfUkSEyoESbYFng0cPon2JUm3mVSP4D3A64FbJ9S+JKk19mGok+wCXFtV5yV5yizLrQBWACxdunRM2WmhDjxh587rHrrH6YuYidYXnzn++s7r7rbnFouYyeL56WGrO6231Wsfs8iZDGcSPYInArsmuRL4BLBDkmOnL1RVK6tqeVUtn5qaGneOktQbYy8EVfXGqtq2qpYBewFfqKoXjzsPSVLD6wgkqecmeqvKqjoLOGuSOUhS39kjkKSesxBIUs9ZCCSp5ywEktRzFgJJ6jkLgST1nIVAknrOQiBJPWchkKSesxBIUs9ZCCSp5ywEktRzFgJJ6jkLgST1nIVAknrOQiBJPWchkKSesxBIUs9ZCCSp5ywEktRzFgJJ6rmxF4Ik90/yxSSXJbkkyavHnYMk6TZLJtDmzcDrqur8JJsC5yVZVVWXTiAXSeq9sfcIqurHVXV++/jXwGXA/cadhySpMYkewZ8kWQY8Fjh7Lc+tAFYALF26FIDrPnhsp3amXv7ijhnO7JIP7NppvUe84uTbTX/+8Gd3irPj/qd2Wk/S7K4+9Ced1tv2wK0XOZPxmdjB4iT3AE4EXlNVN0x/vqpWVtXyqlo+NTU1/gQlqScmUgiS3IWmCBxXVZ+eRA6SpMYkzhoK8BHgsqp697jblyTd3iR6BE8EXgLskGR1+/OsCeQhSWICB4ur6qtAxt2uJGntvLJYknrOQiBJPWchkKSesxBIUs9ZCCSp5ywEktRzFgJJ6jkLgST1nIVAknpuosNQT8JPPvjWTutt/fKDFzkTSYvpnCOv7bTe9vtsuciZLJ5r37+q03pbHrDTvJa3RyBJPWchkKSesxBIUs9ZCCSp5ywEktRzFgJJ6jkLgST1nIVAknrOQiBJPWchkKSem0ghSLJzkm8l+U6SgyaRgySpMfZCkGRD4H8DzwQeDrwwycPHnYckqTGJHsH2wHeq6ntVdRPwCeA5E8hDksRkCsH9gKsGpq9u50mSJiBVNd4Gkz2BZ1TV/u30S4Dtq+qAacutAFa0k9sB35oj9BbA9QtMbzFirGtxzGW0cdalXBYrjrmMNs44c3lAVU3NFWgS9yO4Grj/wPS2wDXTF6qqlcDKYYMmObeqli8kscWIsa7FMZfRxlmXclmsOOYy2jjrUi5rTGLX0DeAhyR5YJKNgL2AkyeQhySJCfQIqurmJK8EPgdsCBxRVZeMOw9JUmMit6qsqs8Cn13ksEPvRhpxjHUtjrmMNs66lMtixTGX0cZZl3IBJnCwWJK0bnGICUnqufWuECS5W5JzklyY5JIkb23n75jk/CSrk3w1yZ8NGW/DJBckOaWdfmCSs5NckeST7QHt+cb4SJvfN5OckOQeXXIZmP/+JL/p+HqOSvL99n1ZneQxHeMkyduTfDvJZUle1THOVwZyuSbJZzrEWKzPeoc2zsVJjk4y567SJFcmuaht+9x23r2TrGq3mVVJ7tUhxp7t9nxrkqHOBJkhzruSXN5ueycl2bxjnH9tY6xOckaS+843xsBzByapJFt0zOWQJD8a2G6e1SVOO/+ANMPbXJLknR1y+eRAHlcmWd3xNT0mydfXzEuy/RwxNk/zv+Ty9u/vCfPd7mZVVevVDxDgHu3juwBnA38JfBt4WDv/FcBRQ8b7B+BjwCnt9KeAvdrHHwJe3iHGZgPPvRs4qEsu7bzlwDHAbzq+nqOAPTq8z9Pj7AN8FNignd6y62saeO5E4KUdclnwZ03zJegq4M/b5/4F2G+IGFcCW0yb9841nzFwEPBvHWI8jOZ6mbOA5UO+nrXFeTqwpH38b3PlMkucwW34VcCH5hujnX9/mhNDfrC254fM5RDgwHluv2uL81TgTOCuw2zDM72mgef/HXhLx1zOAJ7ZPn4WcNYcMY4G9m8fbwRsPt/tbraf9a5HUI01347v0v5U+7NZO/+erOXahOmSbAs8Gzi8nQ6wA3BCu8jRwG7zidHmeMNAvI3b3OaVSztvQ+BdwOvnWn+mGF3MEOflwL9U1a0AVXXtQvJJsinNez1rj2CGGAv+rIH7ADdW1bfb6VXA7nPFmcFzaLYVGGKbWZuquqyq5rpocpg4Z1TVze3k12mu0+kS54aBybszxDY8g8Nott914WDky4F3VNWNMNw2PJP2b/v5wMc7hhh6G06yGfDXwEcAquqmqvoli7DdrbHeFQL4Uxd/NXAtsKqqzgb2Bz6b5GrgJcA7hgj1HpqN9NZ2+j7ALwf+kIYZ/mJ6jDU5Hgn8BHgo8P4OuQC8Eji5qn48xPoz5gK8ve3mH5bkrh3jPBh4QduNPS3JQxaQD8Bzgc9P+4czbIzF+KyvB+4ysBtmD25/oeNMCjgjyXlprn4H2GrNZ9T+3rJDjC7mirMvcFrXOGl2BV4FvAh4y3xjJNkV+FFVXTjMi5ktF+CV7TZ8xJC7QNYW58+BJ6XZ9fulJI/vmAvAk4CfVtUVHXN5DfCu9v09FHjjLOs/CLgOODLNrs3Dk9yd+W93s2TYsSuxLvzQdI++CDwS+DTwX9v5/wgcPse6uwAfaB8/hWZ3wRTNgHiD3dqL5hNj2vMbAh8A9umQy32Br3JbN3/WXUMz5QJsQ7M77a403xpm7crOEuc3wOvax88DvtIlzsDzpwG7d8xlwZ91+/gJwFeAc4C3ARcMsc3dt/29JXAhzTe1X05b5hfzjTHw3FkMv2totjhvAk6iPTOwa5x2/huBt3Z4X84G7tnOv5Lhdg2tLc5WNH9LGwBvp7n2qEuci4H3tX8P2wPfn+39meP9/eCav4eOubxvzfZP07M4c5b1lwM3D2zz7wX+db7b3aw5dl1xXfkBDm7/GXx3YN5S4NI51vtfNN/4r6T55v474Diab4pr/vk+AfjcPGMcO22ZJ7OW/eNDxPlF+/jK9udWBopUx1ye0jGXY4HLgWXtMgF+1SVO+9x9gJ8Bd+sQ49RF+qynvzdPBz41z23vEOBAmnGwtmnnbQN8a74xBqbPYshCMFMcYG/ga8AmC4kzMO8BwMXzjPHPND32NdvvzcAPga0XmMuy+eQy7XM6HXjKwPzvAlMd3t8lwE+Bbbu+v8CvuO30/QA3zLLO1sCVA9NPav8OOm93d2ij64qT+qH51r55+3hjmm90u9D8A19z4G8/4MR5xHwKt31LPJ7bHyx+xXxitB/qnw18wIcCh3bJZdr8oQ4Wr+X1bDOQy3to9pF2ifMOYN+B+d/o+pqAvweOnufnvub9XbKIn/WW7e+7Ap8Hdphj3bsDmw48/n/AzjTHcQYP2r1zvjEGnj+LIQrBLLnsDFzK8P/gZorzkIFlDgBO6Pqa2vlXMkePYJZcthlY5rXAJzrG+Xua41zQ7Ca6ihl6BLO9pjbWlxb4/l5GW5SAHYHz5ojzFWC79vEh7TY39HY3189ErixeoG2Ao9sDqRvQfIs7JcnLgBOT3ErzbXrfjvHfAHwiyduAC2gP0MxD2vw2ax9fSHOQalKOSzLV5rKa5o+hi3e0sV5Ls5to/wXktBfD7de/g2qGKFmsz/ofk+xCsx19sKq+MMfyWwEnNccJWQJ8rKpOT/IN4FNJ9qP51rtnhxjPpTmWNAWcmmR1VT2jQ5zv0BS2Ve1zX6+q2T7zmeKcmGQ7mp7oD5h9u1lrjFmWn28ux6Q57bloCsrfdYyzEXBEkouBm4C9q/0vOs/XtBfDHySeKZffAO9Nc8ryH7htpOWZHEDz97cR8D2as/g2YPjtblZeWSxJPbdenjUkSVo8FgJJ6jkLgST1nIVAknrOQiBJPWch0J1SkvsMjBL5k9x+9Mo5R5QdiLNvkq1neO6J7XAFq9sRIf958V6BND6ePqo7vSSH0FyQd2iHdb8KvLKq7jDccHvO/m5VdXF7Xct2VXXpAnPdsKpuWUgMab7sEah3kuyd5p4Wq5N8IMkGSZa0Fy5dlOb+BK9K8gLgMcAnZ+hJTNEMWUFV3bKmCCTZNM39DS5qB0rbrZ3/4oH4/7OdtyTJL5O8Lck5wPZJHt8OinZeO8DfVmN7c9RL6+OVxVJnSR5JM/LpX7VXKa+kuVL0uzRDIPxFu9zmVfXLJAcwQ4+AZsiOK5J8kWYQvY9WM8TxIcB1VfUXaS4p3TzNMNhvoxlA7FfAme1VzafTDEN8flW9Oc3osF8Edq2q65O8iGaAsYWMUirNykKgvnka8Hjg3Pay/41pxpz5HLBdkvcCn6W5ccisqurgJMfQDFj3UuAFbfyn0Y4N3w5h8IskOwBfqKrrAZJ8jGYUytNphjs4qQ37MOARNIUCmlE3r17wq5ZmYSFQ34RmGOM7HNhN8ijgmTR35NqdIb6FV9V3gO8k+TDwsyT3bNuYfvAts4T5/cCYNwG+WVVPmvOVSIvEYwTqmzOB56e9f257dtHSNQPzVdXxNEObP65d/tfApmsLlOTZ7a4faEazvLFd/gyamwqtudfzvWjuFvbUtr0lNLujvrSWsJcC90t7D9skGyV5xIJftTQLewTqlaq6KMlbaXa9bAD8kWZkzVuAj7T/2ItmFFqAI4HDk/we2L6qbhoI97fAYUl+18b571V1axv/A+0ol7cA/1xVJyd5C80w0wH+s6pObYvCYH43JtkDeF+a23kuobk37iWL/25IDU8flaSec9eQJPWchUCSes5CIEk9ZyGQpJ6zEEhSz1kIJKnnLASS1HMWAknquf8Pi0XDHPzru+kAAAAASUVORK5CYII=\n",
"text/plain": [
"<Figure size 432x288 with 1 Axes>"
]
},
"metadata": {
"needs_background": "light"
},
"output_type": "display_data"
}
],
"source": [
"dataframe = test_score_distribution.DataFrame()\n",
"\n",
"%matplotlib inline\n",
"# uncomment the following line if you get an module error saying seaborn not found\n",
"# !pip install seaborn\n",
"import seaborn\n",
"\n",
"plot = seaborn.barplot(x='Test Score',y='Frequency', data=dataframe)"
]
},
{
"cell_type": "code",
"execution_count": 19,
"metadata": {},
"outputs": [],
"source": [
"#%%sql \n",
"\n",
"#-- Feel free to experiment with the data set provided in this notebook for practice:\n",
"#SELECT country, first_name, last_name, test_score FROM INTERNATIONAL_STUDENT_TEST_SCORES; "
]
},
{
"cell_type": "code",
"execution_count": null,
"metadata": {},
"outputs": [],
"source": []
}
],
"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.6.8"
}
},
"nbformat": 4,
"nbformat_minor": 2
}
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment