{"cells": [{"cell_type": "markdown", "metadata": {}, "source": ["# 1A.soft - Notions de SQL - correction\n", "\n", "Correction des exercices du premier notebooks reli\u00e9 au SQL."]}, {"cell_type": "code", "execution_count": 1, "metadata": {}, "outputs": [{"data": {"text/html": ["
\n", ""], "text/plain": [""]}, "execution_count": 2, "metadata": {}, "output_type": "execute_result"}], "source": ["from jyquickhelper import add_notebook_menu\n", "add_notebook_menu()"]}, {"cell_type": "code", "execution_count": 2, "metadata": {}, "outputs": [], "source": ["%matplotlib inline"]}, {"cell_type": "markdown", "metadata": {}, "source": ["## Recup\u00e9rer les donn\u00e9es"]}, {"cell_type": "code", "execution_count": 3, "metadata": {}, "outputs": [{"name": "stdout", "output_type": "stream", "text": [" TextFile: opening file td8_velib.txt\n", " TextFile.guess_columns: processing file td8_velib.txt\n", " TextFile: opening file td8_velib.txt\n", " TextFile.guess_columns: using 101 lines\n", " TextFile: closing file td8_velib.txt\n", " TextFile.guess_columns: sep '\\t' nb cols 7 bestnb 101 more {('\\t', 6): 101, (' ', 2): 100}\n", " TextFile.guess_columns: header True columns {0: ('collect_date', ), 1: ('last_update', ), 2: ('available_bike_stands', ), 3: ('available_bikes', ), 4: ('number', ), 5: ('heure', ), 6: ('minute', )}\n", " compiling ^(?P.*)\\t(?P.*)\\t(?P([-]?[1-9][0-9]*?)|(0?))\\t(?P([-]?[1-9][0-9]*?)|(0?))\\t(?P([-]?[1-9][0-9]*?)|(0?))\\t(?P([-]?[1-9][0-9]*?)|(0?))\\t(?P([-]?[1-9][0-9]*?)|(0?))$\n", " TextFile.guess_columns: regex ^(?P.*)\\t(?P.*)\\t(?P([-]?[1-9][0-9]*?)|(0?))\\t(?P([-]?[1-9][0-9]*?)|(0?))\\t(?P([-]?[1-9][0-9]*?)|(0?))\\t(?P([-]?[1-9][0-9]*?)|(0?))\\t(?P([-]?[1-9][0-9]*?)|(0?))$\n", " TextFile.guess_columns: header True columns {0: ('collect_date', (, 52)), 1: ('last_update', (, 38)), 2: ('available_bike_stands', ), 3: ('available_bikes', ), 4: ('number', ), 5: ('heure', ), 6: ('minute', )}\n", " [_guess_columns] sep=['\\t']\n", " TextFile: closing file td8_velib.txt\n", " [_guess_columns] columns_name=None\n", " guess with 1001 lines\n", " count_types {0: {: 1000}, 1: {: 1000}, 2: {: 1000}, 3: {: 1000}, 4: {: 1000}, 5: {: 1000}, 6: {: 1000}}\n", " columns {0: ('collect_date', ), 1: ('last_update', ), 2: ('available_bike_stands', ), 3: ('available_bikes', ), 4: ('number', ), 5: ('heure', ), 6: ('minute', )}\n", " guess {0: ('collect_date', (, 52)), 1: ('last_update', (, 38)), 2: ('available_bike_stands', ), 3: ('available_bikes', ), 4: ('number', ), 5: ('heure', ), 6: ('minute', )}\n", "SQL 'CREATE TABLE td8_velib(collect_date TEXT,'\n", "' last_update TEXT,'\n", "' available_bike_stands INTEGER,'\n", "' available_bikes INTEGER,'\n", "' number INTEGER,'\n", "' heure INTEGER,'\n", "' minute INTEGER);'\n", " column_has_space False ['collect_date', 'last_update', 'available_bike_stands', 'available_bikes', 'number', 'heure', 'minute']\n", " changes {}\n", " TextFileColumns (2): regex: {0: ('collect_date', (, 52)), 1: ('last_update', (, 38)), 2: ('available_bike_stands', ), 3: ('available_bikes', ), 4: ('number', ), 5: ('heure', ), 6: ('minute', )}\n", " TextFile.guess_columns: processing file td8_velib.txt\n", " TextFile: opening file td8_velib.txt\n", " TextFile.guess_columns: using 101 lines\n", " TextFile: closing file td8_velib.txt\n", " TextFile.guess_columns: sep '\\t' nb cols 7 bestnb 101 more {('\\t', 6): 101, (' ', 2): 100}\n", " TextFile.guess_columns: header True columns {0: ('collect_date', ), 1: ('last_update', ), 2: ('available_bike_stands', ), 3: ('available_bikes', ), 4: ('number', ), 5: ('heure', ), 6: ('minute', )}\n", " compiling ^(?P.*)\\t(?P.*)\\t(?P([-]?[1-9][0-9]*?)|(0?))\\t(?P([-]?[1-9][0-9]*?)|(0?))\\t(?P([-]?[1-9][0-9]*?)|(0?))\\t(?P([-]?[1-9][0-9]*?)|(0?))\\t(?P([-]?[1-9][0-9]*?)|(0?))$\n", " TextFile.guess_columns: regex ^(?P.*)\\t(?P.*)\\t(?P([-]?[1-9][0-9]*?)|(0?))\\t(?P([-]?[1-9][0-9]*?)|(0?))\\t(?P([-]?[1-9][0-9]*?)|(0?))\\t(?P([-]?[1-9][0-9]*?)|(0?))\\t(?P([-]?[1-9][0-9]*?)|(0?))$\n", " TextFile.guess_columns: header True columns {0: ('collect_date', (, 52)), 1: ('last_update', (, 38)), 2: ('available_bike_stands', ), 3: ('available_bikes', ), 4: ('number', ), 5: ('heure', ), 6: ('minute', )}\n", " TextFile: opening file td8_velib.txt\n", "adding 100000 lines into table td8_velib\n", "adding 200000 lines into table td8_velib\n", "adding 300000 lines into table td8_velib\n", "adding 400000 lines into table td8_velib\n", "adding 500000 lines into table td8_velib\n", "adding 600000 lines into table td8_velib\n", "adding 700000 lines into table td8_velib\n", "adding 800000 lines into table td8_velib\n", "adding 900000 lines into table td8_velib\n", "adding 1000000 lines into table td8_velib\n", "adding 1100000 lines into table td8_velib\n", "^(?P.*)\\t(?P.*)\\t(?P([-]?[1-9][0-9]*?)|(0?))\\t(?P([-]?[1-9][0-9]*?)|(0?))\\t(?P([-]?[1-9][0-9]*?)|(0?))\\t(?P([-]?[1-9][0-9]*?)|(0?))\\t(?P([-]?[1-9][0-9]*?)|(0?))$\n", "error regex 0 unable to interpret line 1103788 : ''\n", " TextFile: closing file td8_velib.txt\n", "1103787 lines imported\n", " TextFile: opening file stations.txt\n", " TextFile.guess_columns: processing file stations.txt\n", " TextFile: opening file stations.txt\n", " TextFile.guess_columns: using 101 lines\n", " TextFile: closing file stations.txt\n", " TextFile.guess_columns: sep '\\t' nb cols 6 bestnb 101 more {('\\t', 5): 101, (' ', 13): 3, (' ', 21): 1, (' ', 14): 2, (' ', 12): 11, (' ', 16): 2, (' ', 9): 24, (' ', 10): 21, (' ', 8): 13, (' ', 11): 10, (' ', 7): 12, (' ', 6): 1}\n", " TextFile.guess_columns: header True columns {0: ('address', ), 1: ('contract_name', ), 2: ('lat', ), 3: ('lng', ), 4: ('name', ), 5: ('number', )}\n", " compiling ^(?P.*)\\t(?P.*)\\t(?P[-]?[0-9]*?([.][0-9]*?)?([eE][-]?[0-9]{0,4})?)\\t(?P[-]?[0-9]*?([.][0-9]*?)?([eE][-]?[0-9]{0,4})?)\\t(?P.*)\\t(?P([-]?[1-9][0-9]*?)|(0?))$\n", " TextFile.guess_columns: regex ^(?P.*)\\t(?P.*)\\t(?P[-]?[0-9]*?([.][0-9]*?)?([eE][-]?[0-9]{0,4})?)\\t(?P[-]?[0-9]*?([.][0-9]*?)?([eE][-]?[0-9]{0,4})?)\\t(?P.*)\\t(?P([-]?[1-9][0-9]*?)|(0?))$\n", " TextFile.guess_columns: header True columns {0: ('address', (, 134)), 1: ('contract_name', (, 10)), 2: ('lat', ), 3: ('lng', ), 4: ('name', (, 98)), 5: ('number', )}\n", " [_guess_columns] sep=['\\t']\n", " TextFile: closing file stations.txt\n", " [_guess_columns] columns_name=None\n", " guess with 935 lines\n", " count_types {0: {: 934}, 1: {: 934}, 2: {: 934}, 3: {: 934}, 4: {: 934}, 5: {: 934}}\n", " columns {0: ('address', ), 1: ('contract_name', ), 2: ('lat', ), 3: ('lng', ), 4: ('name', ), 5: ('number', )}\n", " guess {0: ('address', (, 152)), 1: ('contract_name', (, 10)), 2: ('lat', ), 3: ('lng', ), 4: ('name', (, 98)), 5: ('number', )}\n", "SQL 'CREATE TABLE stations(address TEXT,'\n", "' contract_name TEXT,'\n", "' lat FLOAT,'\n", "' lng FLOAT,'\n", "' name TEXT,'\n", "' number INTEGER);'\n", " column_has_space False ['address', 'contract_name', 'lat', 'lng', 'name', 'number']\n", " changes {}\n", " TextFileColumns (2): regex: {0: ('address', (, 152)), 1: ('contract_name', (, 10)), 2: ('lat', ), 3: ('lng', ), 4: ('name', (, 98)), 5: ('number', )}\n", " TextFile.guess_columns: processing file stations.txt\n", " TextFile: opening file stations.txt\n", " TextFile.guess_columns: using 101 lines\n", " TextFile: closing file stations.txt\n", " TextFile.guess_columns: sep '\\t' nb cols 6 bestnb 101 more {('\\t', 5): 101, (' ', 13): 3, (' ', 21): 1, (' ', 14): 2, (' ', 12): 11, (' ', 16): 2, (' ', 9): 24, (' ', 10): 21, (' ', 8): 13, (' ', 11): 10, (' ', 7): 12, (' ', 6): 1}\n", " TextFile.guess_columns: header True columns {0: ('address', ), 1: ('contract_name', ), 2: ('lat', ), 3: ('lng', ), 4: ('name', ), 5: ('number', )}\n", " compiling ^(?P.*)\\t(?P.*)\\t(?P[-]?[0-9]*?([.][0-9]*?)?([eE][-]?[0-9]{0,4})?)\\t(?P[-]?[0-9]*?([.][0-9]*?)?([eE][-]?[0-9]{0,4})?)\\t(?P.*)\\t(?P([-]?[1-9][0-9]*?)|(0?))$\n", " TextFile.guess_columns: regex ^(?P.*)\\t(?P.*)\\t(?P[-]?[0-9]*?([.][0-9]*?)?([eE][-]?[0-9]{0,4})?)\\t(?P[-]?[0-9]*?([.][0-9]*?)?([eE][-]?[0-9]{0,4})?)\\t(?P.*)\\t(?P([-]?[1-9][0-9]*?)|(0?))$\n", " TextFile.guess_columns: header True columns {0: ('address', (, 134)), 1: ('contract_name', (, 10)), 2: ('lat', ), 3: ('lng', ), 4: ('name', (, 98)), 5: ('number', )}\n", " TextFile: opening file stations.txt\n", "^(?P.*)\\t(?P.*)\\t(?P[-]?[0-9]*?([.][0-9]*?)?([eE][-]?[0-9]{0,4})?)\\t(?P[-]?[0-9]*?([.][0-9]*?)?([eE][-]?[0-9]{0,4})?)\\t(?P.*)\\t(?P([-]?[1-9][0-9]*?)|(0?))$\n", "error regex 0 unable to interpret line 1232 : ''\n", " TextFile: closing file stations.txt\n", "1231 lines imported\n"]}], "source": ["import os\n", "if not os.path.exists(\"td8_velib.db3\"):\n", " from pyensae.datasource import download_data\n", " download_data(\"td8_velib.zip\", website = 'xd')\n", " from pyensae.sql import import_flatfile_into_database\n", " dbf = \"td8_velib.db3\"\n", " import_flatfile_into_database(dbf, \"td8_velib.txt\") # 2 secondes \n", " import_flatfile_into_database(dbf, \"stations.txt\", table=\"stations\") # 2 minutes"]}, {"cell_type": "code", "execution_count": 4, "metadata": {}, "outputs": [], "source": ["%load_ext pyensae"]}, {"cell_type": "code", "execution_count": 5, "metadata": {}, "outputs": [{"data": {"text/plain": [""]}, "execution_count": 6, "metadata": {}, "output_type": "execute_result"}], "source": ["%SQL_connect td8_velib.db3"]}, {"cell_type": "markdown", "metadata": {}, "source": ["## Exercice 1"]}, {"cell_type": "code", "execution_count": 6, "metadata": {}, "outputs": [{"data": {"text/html": ["\n", "\n", "
\n", " \n", " \n", " \n", " COUNT(*) \n", " \n", " \n", " \n", " \n", " 0 \n", " 15294 \n", " \n", " \n", "
\n", "
"], "text/plain": [" COUNT(*)\n", "0 15294"]}, "execution_count": 7, "metadata": {}, "output_type": "execute_result"}], "source": ["%%SQL\n", "SELECT COUNT(*) FROM (\n", " SELECT DISTINCT last_update FROM td8_velib\n", ") ;"]}, {"cell_type": "code", "execution_count": 7, "metadata": {}, "outputs": [{"data": {"text/html": ["\n", "\n", "
\n", " \n", " \n", " \n", " MIN(last_update) \n", " MAX(last_update) \n", " \n", " \n", " \n", " \n", " 0 \n", " 2013-07-22 09:00:19 \n", " 2013-09-13 11:25:19 \n", " \n", " \n", "
\n", "
"], "text/plain": [" MIN(last_update) MAX(last_update)\n", "0 2013-07-22 09:00:19 2013-09-13 11:25:19"]}, "execution_count": 8, "metadata": {}, "output_type": "execute_result"}], "source": ["%%SQL\n", "SELECT MIN(last_update), MAX(last_update) FROM td8_velib ;"]}, {"cell_type": "markdown", "metadata": {}, "source": ["## Exercice 2"]}, {"cell_type": "code", "execution_count": 8, "metadata": {}, "outputs": [{"data": {"text/html": ["\n", "\n", "
\n", " \n", " \n", " \n", " number \n", " nb \n", " \n", " \n", " \n", " \n", " 0 \n", " 42704 \n", " 864 \n", " \n", " \n", " 1 \n", " 41101 \n", " 864 \n", " \n", " \n", " 2 \n", " 35008 \n", " 864 \n", " \n", " \n", " 3 \n", " 35005 \n", " 864 \n", " \n", " \n", " 4 \n", " 35003 \n", " 864 \n", " \n", " \n", " 5 \n", " 33012 \n", " 864 \n", " \n", " \n", " 6 \n", " 33011 \n", " 864 \n", " \n", " \n", " 7 \n", " 33006 \n", " 864 \n", " \n", " \n", " 8 \n", " 33005 \n", " 864 \n", " \n", " \n", " 9 \n", " 32603 \n", " 864 \n", " \n", " \n", "
\n", "
"], "text/plain": [" number nb\n", "0 42704 864\n", "1 41101 864\n", "2 35008 864\n", "3 35005 864\n", "4 35003 864\n", "5 33012 864\n", "6 33011 864\n", "7 33006 864\n", "8 33005 864\n", "9 32603 864"]}, "execution_count": 9, "metadata": {}, "output_type": "execute_result"}], "source": ["%%SQL\n", "SELECT number, COUNT(*) AS nb \n", "FROM td8_velib\n", "WHERE available_bikes==0 AND last_update >= '2013-09-10 11:30:19'\n", "GROUP BY number\n", "ORDER BY nb DESC"]}, {"cell_type": "markdown", "metadata": {}, "source": ["## Exercice 3 : plage horaires de cinq minutes o\u00f9 il n'y a aucun v\u00e9lo disponible"]}, {"cell_type": "code", "execution_count": 9, "metadata": {}, "outputs": [{"data": {"text/html": ["\n", "\n", "
\n", " \n", " \n", " \n", " nb \n", " nb_station \n", " \n", " \n", " \n", " \n", " 0 \n", " 1 \n", " 7 \n", " \n", " \n", " 1 \n", " 2 \n", " 7 \n", " \n", " \n", " 2 \n", " 3 \n", " 5 \n", " \n", " \n", " 3 \n", " 4 \n", " 6 \n", " \n", " \n", " 4 \n", " 5 \n", " 6 \n", " \n", " \n", " 5 \n", " 6 \n", " 5 \n", " \n", " \n", " 6 \n", " 7 \n", " 8 \n", " \n", " \n", " 7 \n", " 8 \n", " 9 \n", " \n", " \n", " 8 \n", " 9 \n", " 1 \n", " \n", " \n", " 9 \n", " 10 \n", " 7 \n", " \n", " \n", "
\n", "
"], "text/plain": [" nb nb_station\n", "0 1 7\n", "1 2 7\n", "2 3 5\n", "3 4 6\n", "4 5 6\n", "5 6 5\n", "6 7 8\n", "7 8 9\n", "8 9 1\n", "9 10 7"]}, "execution_count": 10, "metadata": {}, "output_type": "execute_result"}], "source": ["%%SQL\n", "SELECT nb, COUNT(*) AS nb_station\n", "FROM (\n", " -- requ\u00eate de l'exercice pr\u00e9c\u00e9dent\n", " SELECT number, COUNT(*) AS nb \n", " FROM td8_velib\n", " WHERE available_bikes==0 AND last_update >= '2013-09-10 11:30:19'\n", " GROUP BY number\n", ")\n", "GROUP BY nb"]}, {"cell_type": "markdown", "metadata": {}, "source": ["## Exercice 4 : distribution horaire par station et par tranche de 5 minutes"]}, {"cell_type": "code", "execution_count": 10, "metadata": {}, "outputs": [{"data": {"text/html": ["\n", "\n", "
\n", " \n", " \n", " \n", " number \n", " heure \n", " minute \n", " distribution_temporelle \n", " \n", " \n", " \n", " \n", " 0 \n", " 901 \n", " 0 \n", " 0 \n", " 0.001104 \n", " \n", " \n", " 1 \n", " 901 \n", " 0 \n", " 5 \n", " 0.001104 \n", " \n", " \n", " 2 \n", " 901 \n", " 0 \n", " 10 \n", " 0.001104 \n", " \n", " \n", " 3 \n", " 901 \n", " 0 \n", " 15 \n", " 0.001104 \n", " \n", " \n", " 4 \n", " 901 \n", " 0 \n", " 20 \n", " 0.001104 \n", " \n", " \n", " 5 \n", " 901 \n", " 0 \n", " 25 \n", " 0.000946 \n", " \n", " \n", " 6 \n", " 901 \n", " 0 \n", " 30 \n", " 0.000946 \n", " \n", " \n", " 7 \n", " 901 \n", " 0 \n", " 35 \n", " 0.000946 \n", " \n", " \n", " 8 \n", " 901 \n", " 0 \n", " 40 \n", " 0.000946 \n", " \n", " \n", " 9 \n", " 901 \n", " 0 \n", " 45 \n", " 0.000946 \n", " \n", " \n", "
\n", "
"], "text/plain": [" number heure minute distribution_temporelle\n", "0 901 0 0 0.001104\n", "1 901 0 5 0.001104\n", "2 901 0 10 0.001104\n", "3 901 0 15 0.001104\n", "4 901 0 20 0.001104\n", "5 901 0 25 0.000946\n", "6 901 0 30 0.000946\n", "7 901 0 35 0.000946\n", "8 901 0 40 0.000946\n", "9 901 0 45 0.000946"]}, "execution_count": 11, "metadata": {}, "output_type": "execute_result"}], "source": ["%%SQL\n", "SELECT A.number, A.heure, A.minute, 1.0 * A.nb_velo / B.nb_velo_tot AS distribution_temporelle\n", "FROM (\n", " SELECT number, heure, minute, SUM(available_bikes) AS nb_velo\n", " FROM td8_velib\n", " WHERE last_update >= '2013-09-10 11:30:19'\n", " GROUP BY heure, minute, number\n", ") AS A\n", "JOIN (\n", " SELECT number, heure, minute, SUM(available_bikes) AS nb_velo_tot\n", " FROM td8_velib\n", " WHERE last_update >= '2013-09-10 11:30:19'\n", " GROUP BY number\n", ") AS B\n", "ON A.number == B.number\n", "--WHERE A.number in (8001, 8003, 15024, 15031) -- pour n'afficher que quelques stations\n", "ORDER BY A.number, A.heure, A.minute"]}, {"cell_type": "markdown", "metadata": {}, "source": ["## Zones de travail et zones de r\u00e9sidence"]}, {"cell_type": "code", "execution_count": 11, "metadata": {}, "outputs": [{"data": {"text/html": ["\n", "\n", "
\n", " \n", " \n", " \n", " number \n", " velo_jour \n", " \n", " \n", " \n", " \n", " 0 \n", " 901 \n", " 0.555188 \n", " \n", " \n", " 1 \n", " 903 \n", " 0.473137 \n", " \n", " \n", " 2 \n", " 904 \n", " 0.357640 \n", " \n", " \n", " 3 \n", " 905 \n", " 0.323395 \n", " \n", " \n", " 4 \n", " 906 \n", " 0.117566 \n", " \n", " \n", " 5 \n", " 908 \n", " 0.501727 \n", " \n", " \n", " 6 \n", " 1001 \n", " 0.486796 \n", " \n", " \n", " 7 \n", " 1002 \n", " 0.355434 \n", " \n", " \n", " 8 \n", " 1003 \n", " 0.364749 \n", " \n", " \n", " 9 \n", " 1004 \n", " 0.440549 \n", " \n", " \n", "
\n", "
"], "text/plain": [" number velo_jour\n", "0 901 0.555188\n", "1 903 0.473137\n", "2 904 0.357640\n", "3 905 0.323395\n", "4 906 0.117566\n", "5 908 0.501727\n", "6 1001 0.486796\n", "7 1002 0.355434\n", "8 1003 0.364749\n", "9 1004 0.440549"]}, "execution_count": 12, "metadata": {}, "output_type": "execute_result"}], "source": ["%%SQL --df=df\n", "SELECT number, SUM(distribution_temporelle) AS velo_jour\n", "FROM (\n", " -- requ\u00eate de l'exercice 4\n", " SELECT A.number, A.heure, A.minute, 1.0 * A.nb_velo / B.nb_velo_tot AS distribution_temporelle\n", " FROM (\n", " SELECT number, heure, minute, SUM(available_bikes) AS nb_velo\n", " FROM td8_velib\n", " WHERE last_update >= '2013-09-10 11:30:19'\n", " GROUP BY heure, minute, number\n", " ) AS A\n", " JOIN (\n", " SELECT number, heure, minute, SUM(available_bikes) AS nb_velo_tot\n", " FROM td8_velib\n", " WHERE last_update >= '2013-09-10 11:30:19'\n", " GROUP BY number\n", " ) AS B\n", " ON A.number == B.number\n", ")\n", "WHERE heure >= 10 AND heure <= 16 \n", "GROUP BY number"]}, {"cell_type": "code", "execution_count": 12, "metadata": {}, "outputs": [{"data": {"text/html": ["\n", "\n", "
\n", " \n", " \n", " \n", " index \n", " number \n", " velo_jour \n", " \n", " \n", " \n", " \n", " 0 \n", " 0 \n", " 18113 \n", " 0.000000 \n", " \n", " \n", " 1 \n", " 1 \n", " 35013 \n", " 0.000000 \n", " \n", " \n", " 2 \n", " 2 \n", " 10010 \n", " 0.000000 \n", " \n", " \n", " 3 \n", " 3 \n", " 20037 \n", " 0.007382 \n", " \n", " \n", " 4 \n", " 4 \n", " 20119 \n", " 0.010623 \n", " \n", " \n", "
\n", "
"], "text/plain": [" index number velo_jour\n", "0 0 18113 0.000000\n", "1 1 35013 0.000000\n", "2 2 10010 0.000000\n", "3 3 20037 0.007382\n", "4 4 20119 0.010623"]}, "execution_count": 13, "metadata": {}, "output_type": "execute_result"}], "source": ["df = df.sort_values(\"velo_jour\").reset_index()\n", "df[\"index\"] = range(0, df.shape[0])\n", "df.head()"]}, {"cell_type": "code", "execution_count": 13, "metadata": {}, "outputs": [{"data": {"text/plain": [""]}, "execution_count": 14, "metadata": {}, "output_type": "execute_result"}, {"data": {"image/png": "iVBORw0KGgoAAAANSUhEUgAAAXQAAAEGCAYAAAB1iW6ZAAAAOXRFWHRTb2Z0d2FyZQBNYXRwbG90bGliIHZlcnNpb24zLjMuMywgaHR0cHM6Ly9tYXRwbG90bGliLm9yZy/Il7ecAAAACXBIWXMAAAsTAAALEwEAmpwYAAAp0klEQVR4nO3deXxU9b3/8deHBBLZtwCBEAj7IggYQUVU3BeUWrWCXqutiteti0uvXr22tb33Vtuft/YW91K7uFGKFpWKS3HBBRKUfQ1rAgFCwhrI/vn9MSN3TKMZwkwmM3k/H488mHPOd+Z8Tk7yZvKdc75fc3dERCT+tYh1ASIiEhkKdBGRBKFAFxFJEAp0EZEEoUAXEUkQybHacdeuXb1v376x2r2ISFxavHjxbndPq2tbzAK9b9++5Obmxmr3IiJxycy2fNU2dbmIiCQIBbqISIJQoIuIJIiY9aHXpbKykoKCAsrKymJdSlxKTU0lIyODli1bxroUEYmBsALdzC4AHgOSgGfd/Re1tmcCfwA6Btvc6+5zj7aYgoIC2rVrR9++fTGzo316s+buFBcXU1BQQFZWVqzLEZEYqLfLxcySgOnAhcAwYKqZDavV7AFgpruPBqYAjzekmLKyMrp06aIwbwAzo0uXLvrrRqQZC6cPfSyQ5+4b3b0CeAmYXKuNA+2DjzsA2xtakMK84fS9E2newgn0XkB+yHJBcF2onwD/YmYFwFzgjrpeyMymmVmumeUWFRU1oFwRkfh1uKKaX85bw9L8vVF5/Uhd5TIVeM7dM4CLgD+Z2T+9trs/7e7Z7p6dllbnjU4iIglr7+EKps/fwKrC/VF5/XACfRvQO2Q5I7gu1A3ATAB3/wRIBbpGosCmrG3btkf9nO3bt3PFFVdEoRoRaepKy6sBaJMSnQsMwwn0HGCgmWWZWSsCH3rOqdVmK3A2gJkNJRDo6lOpQ8+ePZk1a1ZEXqu6ujoiryMijaO0vAqANq2SovL69f434e5VZnY7MI/AJYkz3H2lmT0E5Lr7HOAu4Bkz+yGBD0iv92Oc2+6nr61k1fbI/lkyrGd7fnzJ8K/cfu+999K7d29uu+02AH7yk5/Qtm1b3J2ZM2dSXl7OZZddxk9/+tMvPc/d+dGPfsTf//53zIwHHniAq666qs59bN68mUmTJrFixQrKysq45ZZbyM3NJTk5mUcffZSJEyfy3HPPkZuby29/+1sAJk2axN13382ZZ55J27Ztufnmm3nnnXeYPn06p512WoS+OyISbaUVgUBv3Sp279Bx97nuPsjd+7v7fwbXPRgMc9x9lbuPd/cT3H2Uu78VlWqj7KqrrmLmzJlHlmfOnElaWhrr169n0aJFLFmyhMWLF/PBBx986XmzZ89myZIlLF26lHfeeYd77rmHwsLCevc3ffp0zIzly5fz4osvct1119V72WFpaSnjxo1j6dKlCnOROHMo2OXSNkpdLk3qTtFQX/dOOlpGjx7Nrl272L59O0VFRXTq1Inly5fz1ltvMXr0aAAOHjzI+vXrOf300488b8GCBUydOpWkpCS6d+/OGWecQU5ODpdeeunX7m/BggXccUfggqAhQ4bQp08f1q1b97XPSUpK4vLLLz/GIxWRWDhQXglAm5QYdbk0N1deeSWzZs1ix44dXHXVVWzZsoX77ruPm2++udFqSE5Opqam5shy6Lv21NRUkpKi88MgItG1c385AN3ap0bl9TU4Vy1XXXUVL730ErNmzeLKK6/k/PPPZ8aMGRw8eBCAbdu2sWvXri89Z8KECbz88stUV1dTVFTEBx98wNixY+vd14QJE3j++ecBWLduHVu3bmXw4MH07duXJUuWUFNTQ35+PosWLYr8gYpIo9uxr4y2KcnNr8slVoYPH86BAwfo1asX6enppKens3r1ak455RQgcKnin//8Z7p163bkOZdddhmffPIJJ5xwAmbGI488Qo8ePb5yH1/c0Xnrrbdyyy23MGLECJKTk3nuuedISUlh/PjxZGVlMWzYMIYOHcqYMWOie9Ai0ii2FJfSu3PrqL2+HePFKA2WnZ3ttWcsWr16NUOHDo1JPY1l8eLF3Hnnnbz//vtRef3m8D0UiVcTf/UeQ9Pb8fg1Jzb4Ncxssbtn17VNXS6NKDc3l6lTp/L9738/1qWISCOrrK5ha8khsrq2ido+1OUSJcuXL+faa6/90rqUlJR6r2IRkcT0/toiqmucoent62/cQE0u0N09IUYNHDFiBEuWLGnUfcaq+0xE6rd46x6SWxjnDusetX00qS6X1NRUiouLFUwN8MUEF6mp0bkcSkSOzbwVOxiR0YGU5Ohddtyk3qFnZGRQUFCAhtZtmC+moBORpqWquoYtJYe4dWT/qO6nSQV6y5YtNX2aiCScwn1lVNc4PTseF9X9NKkuFxGRRLQyONDgoO5HP+T20VCgi4hEUUVVDY++vZbu7VMY3rNDVPelQBcRiaLf/mM963Ye5MFJw0ltGd1xmBToIiJRsjR/L7/5Rx4Du7XlnGHd6n/CMVKgi4hEyfy1gYH8Xpx2clQvV/xCWIFuZheY2VozyzOze+vY/j9mtiT4tc7M9ka8UhGROPPBuiJO6N2Rrm1TGmV/9V62aGZJwHTgXKAAyDGzOe6+6os27v7DkPZ3AKOjUKuISNz49Tvr+GzrXu45f3Cj7TOcd+hjgTx33+juFcBLwOSvaT8VeDESxYmIxKOZufn8+p31TBqZzg2nNd69NeEEei8gP2S5ILjun5hZHyAL+MdXbJ9mZrlmlqu7QUUkES3N38t/zV3NsPT2/L9vnRD1K1tCRfpD0SnALHevrmujuz/t7tnunp2WlhbhXYuIxNaKbfu47PGPSDLjV1ee0CgfhIYK59b/bUDvkOWM4Lq6TAFuO9aiRETi0azFBZgZ8354eqN9EBoqnHfoOcBAM8sys1YEQntO7UZmNgToBHwS2RJFRJo+d+ftVTs5Z2i3mIQ5hBHo7l4F3A7MA1YDM919pZk9ZGaXhjSdArzkGvtWRJqZyuoaHnp9Fdv2HubsIdEb77w+YY226O5zgbm11j1Ya/knkStLRCQ+HCir5NbnP+PD9bu5/tS+XJkduyGsm9TwuSIi8aS6xrlz5lI+2VDMI1eM5FvZvet/UhQp0EVEGuC1pdv51Vtr2VJ8iNsm9o95mIMCXUTkqO0preCuvywlrW0KD00ezjXj+sS6JECBLiJyVMoqq7n5T4upqKrhmW9nM6xn+1iXdIQCXUQkTHtKK7j+uRyW5u/lx5cMa1JhDho+V0QkbP/999VHwvw745ve/McKdBGRMKzYto+ZuQV8c3SvJhnmoEAXEalX4b7D/GjWMpJbGPddNDTW5Xwl9aGLiHyNN1cU8r2XllBZXcODk4aR1i42t/WHQ4EuIlKH3M0lzFu5g2c+3ERauxR+f/1JHN+rQ6zL+loKdBGRWv66uIC7/rIUMxiT2ZGnrs1u0u/Mv6BAFxEJMX/NLv7tr8s4tX8XnrjmRDq0bhnrksKmQBcRCfHMhxvp3j6Vp649kXap8RPmoKtcRESO+ChvNws3lTB5VM+4C3NQoIuIAPD2qp1c+7uFDEhry00T+sW6nAZRoItIs/fioq3c9MdcurVL5YWbxtGpTatYl9QgYQW6mV1gZmvNLM/M7v2KNt8ys1VmttLMXohsmSIi0fHa0u3cN3s5w3u255dXjqRLjKaPi4R6PxQ1syRgOnAuUADkmNkcd18V0mYgcB8w3t33mFm3aBUsIhIJ5VXVPD5/A9Pn5zG4ezteuXU8rZLju9MinKtcxgJ57r4RwMxeAiYDq0La3ARMd/c9AO6+K9KFiohESk2Nc+Mfcvlw/W4uOaEnP598fNyHOYTX5dILyA9ZLgiuCzUIGGRmH5nZp2Z2QV0vZGbTzCzXzHKLiooaVrGIyDF6dck2Ply/m9sm9ud/p46Oq2vNv06k/ktKBgYCZwJTgWfMrGPtRu7+tLtnu3t2WlpahHYtIhK+iqoannx/A61bJXH3eYNjXU5EhRPo24DQyfIygutCFQBz3L3S3TcB6wgEvIhIk1FVXcOVT37Mup0HmTikG2YW65IiKpxAzwEGmlmWmbUCpgBzarV5lcC7c8ysK4EumI2RK1NE5Ni4O798ay1LC/bx4KRhTL96TKxLirh6A93dq4DbgXnAamCmu680s4fM7NJgs3lAsZmtAuYD97h7cbSKFhE5Wi8s2spT72/k4hHpfGd831iXExXm7jHZcXZ2tufm5sZk3yLSvLg7Zz/6Pm1TkvnbbePjuqvFzBa7e3Zd2+L/Oh0RkXo8/OZaNhaVMuWkzLgO8/oo0EUkoW3aXcqzH27kihMzmDq2d/1PiGMKdBFJWK8t3c4VT3xMSnIL7jl/cEK/OwcFuogkqE27S7lv9nLM4MFLhtG9fWqsS4o6TXAhIgln4cZivvtcDocrq/nfq09i4uDmMbyUAl1EEsrPXl/F7xZsom+X1jz3nbH07dom1iU1GgW6iCSMBet387sFm7hsdC9+fMkwOraOz3HNG0qBLiIJ4ZE31/D4exvo3fk4/vOy42ndqvnFW/M7YhFJOM9+uJHH39vA5WMy+I9JQ5tlmIMCXUTi3N+WbOPnb6xmwsCu/Pc3RyTEuOYNpUAXkbg1Y8EmHnp9FZ1at+R/p45u1mEOCnQRiUNV1TXM+GgT/zV3DWcP6cbDV4xsdh+A1kWBLiJxpWDPIX40axkfbyjmrCHd+J8po2ifmhgzDh0rBbqIxI3qGucb0z9m76EKHrh4KDeclpXwt/Mfjebd4SQicaX4YDm7D5Zz/al9uXFCP4V5LQp0EYkbO/aXATA2q3OMK2mawgp0M7vAzNaaWZ6Z3VvH9uvNrMjMlgS/box8qSLSXLk7W4pLeXz+BgD6d2sb44qapnr70M0sCZgOnEtgMugcM5vj7qtqNX3Z3W+PQo0i0ozNWlzAb95dz9aSQwBce3If+qcp0OsSzoeiY4E8d98IYGYvAZOB2oEuIhIxhyuquf+V5cz+fBtDerTj3guHcM7Q7gzQu/OvFE6g9wLyQ5YLgHF1tLvczE4H1gE/dPf82g3MbBowDSAzM/PoqxWRZsHduW7GInK2lHDHWQO4beIAUlsmxbqsJi9SH4q+BvR195HA28Af6mrk7k+7e7a7Z6elpUVo1yKSSIoOlHPlk5+waHMJP710OHedN1hhHqZwAn0bEDoRX0Zw3RHuXuzu5cHFZ4ETI1OeiDQnn23dw+TfLmBpwV5+cM5Arj25T6xLiivhdLnkAAPNLItAkE8Brg5tYGbp7l4YXLwUWB3RKkUk4VXXON/5fQ7JLYzZt4xnREaHWJcUd+oNdHevMrPbgXlAEjDD3Vea2UNArrvPAb5nZpcCVUAJcH0UaxaRBPTXxQXsO1zJI1eMVJg3kLl7THacnZ3tubm5Mdm3iDQt7k72z9+hdUoS835werMdzzwcZrbY3bPr2qY7RUUkptydb89YRHFpBd8Y1UthfgwU6CISU899vJkP1+/mlH5d+ME5g2JdTlxToItIzMxdXshPX1vFmMyOPHtdNkktNNjWsdDfNiISE4crqvm3WcsY1L0tL998Ci2T9P7yWOk7KCIx8cdPNnOgvIrvnz1IYR4h+i6KSKP7bOse/vvvawA4e2i3GFeTONTlIiKNprS8is+27uGBV1fQMsl4+PKRuq0/ghToIhJ1RQfKefjNNcxbsYMD5VUktTCmXz2GC47vEevSEooCXUSiqrS8ihv/mMvS/L1ceHwPpozNZFRGRzq01sTOkaZAF5GocXe+9dQnrNy+n0tO6Mn/Th0d65ISmgJdRKKisrqGJ9/bwMrt+7n7vEFMO71/rEtKeAp0EYk4d+fa3y3k040ljM7syA2n9aNVsi6qizYFuohEVGV1DbM/K+DTjSXccmZ/7jp3EMm6zrxRKNBFJGIOV1RzzbOf8tnWvYzJ7MhtEwcozBuRAl1Ejll1jTP7swJmfLSZ1YX7uffCIdx4WpbCvJGFFehmdgHwGIEJLp519198RbvLgVnASe6uwc5FElzRgXLmr93F8wu3sjR/L+1Tk/mvy0Zw9ThNAh8L9Qa6mSUB04FzgQIgx8zmuPuqWu3aAd8HFkajUBFpWqqqazj/1x9QUlpBWrsUHrl8JFdmZ2CmERNjJZx36GOBPHffCGBmLwGTgVW12v0MeBi4J6IVikiTk7frALf8+TNKSiv44TmDuHVifw2w1QSEcwZ6AfkhywXBdUeY2Rigt7u/EcHaRKQJyi85xLee+pTCfWU8NmUUt581QGHeRBzzh6Jm1gJ4lDAmhjazacA0gMxM9bGJxJO1Ow7wxHt5vL6skOQk45lvZzNhYFqsy5IQ4QT6NqB3yHJGcN0X2gHHA+8F+856AHPM7NLaH4y6+9PA0xCYJPoY6haRRlRd40x95lP2H67kstG9uPmM/gzo1jbWZUkt4QR6DjDQzLIIBPkU4OovNrr7PqDrF8tm9h5wt65yEUkMZZXV/PhvKykpreCxKaOYPKpX/U+SmKg30N29ysxuB+YRuGxxhruvNLOHgFx3nxPtIkWk8bk7CzeV8KNZy9hacojbJw7gkpE9Y12WfI2w+tDdfS4wt9a6B7+i7ZnHXpaIxNLK7ft46LVVLNxUQrd2Kcy4PpuzhnSPdVlSD90pKiJHVNc4f8nN597ZyzmuZRJ3nDWAW88cwHGtNKtQPFCgiwgAqwv38/Cba3hvbRGZnVvzx++OpW/XNrEuS46CAl2kmaupcV5fXshdM5dQ4+hGoTimQBdppnbuL+OJ9zYw+7MC9pdVkd2nE09deyJd2qbEujRpIAW6SDO071AlFz32IcWlFVw2uhen9u/CpJE91Vce5xToIs3I9r2Huf+V5by/rogah99dl83ZQ3X1SqJQoIs0E4u37OHW5xdzqLyab5/Sl3OHdWf8gK71P1HihgJdpBlYsH433/1DDl3atOL5m8YxMqNjrEuSKFCgiySw6hrn043F3PbCZxzXMokZ15/E0PT2sS5LokSBLpKAyquqef7TrTzz4UYK95WR1i6F6VePUZgnOAW6SIL5/UebmD5/A7sPlnNyv878+0VDOXtoN1q30q97otMZFkkAZZXV/M8763j1823s3F/Oqf278NiUUZzav4umhGtGFOgicWx/WSWLN+/hyfc3sHBTCecP786Zg7vxrezeJLVQkDc3CnSROFNVXcOCvN28trSQ15Ztp6KqhvapyfzkkmFcPz4r1uVJDCnQReLI4i17+I9XV7CqcD+tWyXxrewMLjo+ndGZnXSXpyjQReLBu6t3MuOjTXyUV0yn1i35+TeO54oTM0htqRCX/xNWoJvZBcBjBGYsetbdf1Fr+78CtwHVwEFgmruvinCtIs3KgbJK3l9XxOtLC3lz5Q46t2nF7RMHcMuZ/WmTovdi8s/q/akwsyRgOnAuUADkmNmcWoH9grs/GWx/KfAocEEU6hVJaDv2lfHG8kI+ytvNpxuLOVRRTetWSdw0IYu7zhusd+TytcL5b34skOfuGwHM7CVgMnAk0N19f0j7NoBHskiRRFZT47y1agdvLN/B68u24w790toweVRPzh/eg1P6dyElWUEu9Qsn0HsB+SHLBcC42o3M7DbgTqAVcFZdL2Rm04BpAJmZmUdbq0hCKS2v4t01u3h8fh5rdhygXWoyk0b25HtnDWBg93axLk/iUMQ64tx9OjDdzK4GHgCuq6PN08DTANnZ2XoXL81OVXUNb6/aSe6WPczMyedAeRX90trw66tGceGIHnonLscknEDfBvQOWc4IrvsqLwFPHEtRIonog3VFPDJvDSu27ScluQUjenXg+vF9ufD4dN0EJBERTqDnAAPNLItAkE8Brg5tYGYD3X19cPFiYD0iwuGKapYV7OXl3Hxmf7aN9qnJ/PKKkXxzTIZCXCKu3kB39yozux2YR+CyxRnuvtLMHgJy3X0OcLuZnQNUAnuoo7tFpDk5VFHFz15fzaufb+NwZTUA14zL5MeXDKdVsiZflugIqw/d3ecCc2utezDk8fcjXJdI3HF38nYd5OWcfN5ds4stxaVceWJvxvXrzOmD0uiqyZclynR3gkgE5O06yEOvr+KDdUUAnNKvC/92wRAuOL5HjCuT5kSBLtIAhyuq+Tx/D59sKGbW4gIK95WR2rIFd507iG+emEGvjsfFukRphhToIkfho7zd/Ocbq1m78wDVNYErb3t2SOUnlwzjohHpdGufGuMKpTlToIvUo6bGmb92F698vo25ywvp3j6VW87oz4l9OjEyowOd27TSJBLSJCjQRb5CSWkFsz8r4NUl21ixbT+dWrfkihMzuPmM/vRPaxvr8kT+iQJdJERldQ1zlxfy1sqdLMjbzb7DlQzp0Y77LxrKdaf21SWH0qQp0KXZK6us5v11RcxZup0P1hZxoLyKHu1TGZfVmRsn9GNsVudYlygSFgW6NEuV1TUsK9jLCwvz+ceanew5VEnXtilcOKIH5w3rwVlDutFCd3JKnFGgS7NSXeN8lLebn7y2ko1FpbQwOHtod64Zl8lpA7qSnKQuFYlfCnRpFrYUl/JyTj5vrdpJ3q6DtEsJTKp88ciepLXTHZySGBToktD2lFZw7YyFrNgWmIPlhIwOPDZlFGcN6Ua71JYxrk4kshTokpDW7jjAb/6xnvfXFnGoooobTsvi6nGZutxQEpoCXRLKzv1l3DNrGR+sK6JNqyTOP74HV5yYwan9u8a6NJGoU6BL3CurrOaNZYXMWlzAJxuLMYMbT8vi2lP60KdLm1iXJ9JoFOgSt7bvPcxDr63iH2t3UVFVQ+c2rfj2KX245ISenNRX145L86NAl7iy73Alf/pkM/NW7mT5tn0AXDa6FxePSOeMwWm01GWH0oyFFehmdgHwGIEZi55191/U2n4ncCNQBRQB33X3LRGuVZq5ZQV7mfL0pxyqqGZ0ZkfuvXAI5wztxoBu7WJdmkiTUG+gm1kSMB04FygAcsxsjruvCmn2OZDt7ofM7BbgEeCqaBQszcuOfWW8t3YXry8r5JONxSS3MF64cRynDtCHnCK1hfMOfSyQ5+4bAczsJWAycCTQ3X1+SPtPgX+JZJHSPL27eid3vPg5hyqqSWuXwk0T+nH12Ewyu7SOdWkiTVI4gd4LyA9ZLgDGfU37G4C/17XBzKYB0wAyMzPDLFGam/ySQzzx/gZm5uTTo0Mqf70lm8Hd22lsFZF6RPRDUTP7FyAbOKOu7e7+NPA0QHZ2tkdy3xL/Kqtr+MXf1/CHjzfTooVx8ch0Hrh4mG7NFwlTOIG+DegdspwRXPclZnYOcD9whruXR6Y8aQ72HqrguY83MzMnn+37yrh8TAb3nD+YHh00nZvI0Qgn0HOAgWaWRSDIpwBXhzYws9HAU8AF7r4r4lVKQvp4w25mLNjExxuKOVRRzan9u/Cfl43gzMFpmtJNpAHqDXR3rzKz24F5BC5bnOHuK83sISDX3ecAvwTaAn8J/iJudfdLo1i3xLG8XQf4+Rur+WBdEa1bJTN5VE+mnJTJCb07xro0kbgWVh+6u88F5tZa92DI43MiXJckoE83FrOsYC/T52/ADG6bOIAbJ/Sjw3Ea9VAkEnSnqETV9r2Hee7jzSzN38vCTSUADO/ZnieuOVGXH4pEmAJdIi6/5BB//GQz768rYt3Og7QwGNyjPdee3IdbzuxPeodU9ZGLRIECXSKi+GA5f/xkC/NW7mDNjgO0MDi1f1cuH5PBecN7kNVVox6KRJsCXY5JWWU1v5q3lj8v3EJZZQ2n9OvCPecP5qIR6QpxkUamQJcGKSmt4LF31jH7820cKKti8qie3HHWAA2UJRJDCnQJW1llNX/Jzef1ZYUs2lyCO3xjVE++ld1bg2WJNAEKdKlXeVU1uZv38PM3VrO6cD9ZXdtw25kDOH1QGmOzNJGESFOhQJevVFldw+PzN/C7BRvZX1ZF61ZJPH3tiZw3vEesSxOROijQ5Z+UVVazYP1unl2wkU83lnDO0O58Y3RPJgxIo0Nr3QQk0lQp0OVLlubv5eY/LWbH/jLapSZz93mDuP2sgbEuS0TCoECXI363YBM/e30VrVsl8ey3szl9UBqtkjVHp0i8UKAL7s6/v7KcFxflM6h7W/7w3bGkdzgu1mWJyFFSoDdze0orePjNNbyUk8/FI9P55RUjad1KPxYi8Ui/uc1Q0YFyPlhXxPy1u/h4QzElpRXcNCGLf79oqMZYEYljCvRmYse+Mt5ds5Pff7SZvF0HAejUuiVjMjtxw2lZujFIJAEo0BPcu6t38qu31rG6cD8AWV3bcP9FQxmR0YExmZ30oadIAgkr0M3sAuAxAjMWPevuv6i1/XTg18BIYIq7z4pwnXIUyquqeXPFDp7/dCuLNpcwqHtb7r1wCGcN6cbAbm3VrSKSoOoNdDNLAqYD5wIFQI6ZzXH3VSHNtgLXA3dHo0gJX/HBcm78Yy6fb91Lr47Hcfd5g7jhtH4c1yop1qWJSJSF8w59LJDn7hsBzOwlYDJwJNDdfXNwW00UapR65Jcc4rVl21m74wCfbiym+GAFt57Znx+cM0hdKiLNSDiB3gvID1kuAMY1ZGdmNg2YBpCZmdmQl5AQebsO8MR7G3lt6XYqqmvo2SGVYentuePsgYzJ7BTr8kSkkTXqh6Lu/jTwNEB2drY35r4TRVllNZ9v3cvbq3bywqItVFTVcNVJvblxQj/6p7WNdXkiEkPhBPo2oHfIckZwnTSS6hrnuY83M2/FDj7P30NltdPC4Oyh3Xng4qH06aKZgUQkvEDPAQaaWRaBIJ8CXB3VqgSA+Wt38dmWPcxfu4sV2/YzpEc7vjs+i7FZncnu01kjH4rIl9Qb6O5eZWa3A/MIXLY4w91XmtlDQK67zzGzk4BXgE7AJWb2U3cfHtXKE5C7U3SwnHdX72L2ZwXkbN4DQL+0Nnzv7IHcee6gGFcoIk1ZWH3o7j4XmFtr3YMhj3MIdMXIUdpYdJAP1+/m/XVFLNxYTGlFNQCZnVvzvbMH8q9n9NPYKiISFiVFjBwoq+T5hVt59K11R65QmTSyJwO7t2X8gK4M7t6OFi10A5CIhE+B3siKD5bzycZiHvzbSkpKKzilXxfuv3gow3u21x2cInJMFOhRtvtgOcsK9rJ59yHeXLGDRZtLAGifmqz5OUUkohToEVZSWsGH64t4Z3XgCpVtew8f2da783Hcee4gxg/owoheHXUXp4hElAI9AtydVz7fxoyPNrFy+37coVu7FE7K6sz1p/blhN4dyerahq5tW6lbRUSiRoHeQIX7DvP+2sAkETmb91BSWsGw9Pbcec4gxg/syqiMjvpQU0QalQI9DHtKK1hduJ9VhftZu+MAa3YcYPm2fQD06ngcEwd344zBaUwaka4QF5GYUaDXYd/hSmYtLiB3cwlL8vdSuK/syLaubVPo26U1t08cwKQT0hncvZ26UUSkSVCgA5XVNby/tojff7yJ5QX72F9WBUBGp+M4qW9nhvdsz9D0wFdau5QYVysiUrdmGehV1TXk7zlMzqYSPszbzbyVO6ioqqFtSjKXnJBORqfWjMzowISBabEuVUQkbM0i0N2ddTsPsn7XAT7ZUMzc5YXsOVQJQNe2rbj0hJ6cO6w7ZwxKI7WlZvYRkfiUsIFeVV3Dh3m7eWNZIe+t3cXugxUAtG6VxMn9ujBxcBon9unM0HT1gYtIYkiYQN+0u5S3V+1g1fb9LN66h+17y6iucTq2bkl2n85MHJLGqN4dGdCtLSnJehcuIoknrgN936FKXvm8gDeWFx4ZarZbuxSy+3biG6N60bdLGyadkK4AF5FmIW4DfWZOPg+9voqD5VUMTW/Pzaf34xujezE0vX2sSxMRiYm4DPQ1O/bzb7OXcXJWYKTC43t1iHVJIiIxF9boUGZ2gZmtNbM8M7u3ju0pZvZycPtCM+sb8UpDvPr5dpLMmH7NGIW5iEhQvYFuZknAdOBCYBgw1cyG1Wp2A7DH3QcA/wM8HOlCQ322ZQ+jMzvSuU2raO5GRCSuhPMOfSyQ5+4b3b0CeAmYXKvNZOAPwcezgLMtStcCzszJZ9HmEvpqpnsRkS8JJ9B7AfkhywXBdXW2cfcqYB/QpfYLmdk0M8s1s9yioqIGFdyxdUsuHpHOlLGZDXq+iEiiatQPRd39aeBpgOzsbG/Ia5w3vIdm+RERqUM479C3Ab1DljOC6+psY2bJQAegOBIFiohIeMIJ9BxgoJllmVkrYAowp1abOcB1wcdXAP9w9wa9AxcRkYapt8vF3avM7HZgHpAEzHD3lWb2EJDr7nOA3wF/MrM8oIRA6IuISCMKqw/d3ecCc2utezDkcRlwZWRLExGRo6Fp50VEEoQCXUQkQSjQRUQShAJdRCRBWKyuLjSzImBLA5/eFdgdwXJiScfSNOlYmiYdC/Rx9zonPI5ZoB8LM8t19+xY1xEJOpamScfSNOlYvp66XEREEoQCXUQkQcRroD8d6wIiSMfSNOlYmiYdy9eIyz50ERH5Z/H6Dl1ERGpRoIuIJIi4C/T6Jqxuasyst5nNN7NVZrbSzL4fXN/ZzN42s/XBfzsF15uZ/SZ4fMvMbExsj+DLzCzJzD43s9eDy1nBicHzghOFtwqub9SJwxvCzDqa2SwzW2Nmq83slHg8L2b2w+DP1goze9HMUuPpvJjZDDPbZWYrQtYd9Xkws+uC7deb2XV17StGx/LL4M/YMjN7xcw6hmy7L3gsa83s/JD1Dcs5d4+bLwLD924A+gGtgKXAsFjXVU/N6cCY4ON2wDoCk20/AtwbXH8v8HDw8UXA3wEDTgYWxvoYah3PncALwOvB5ZnAlODjJ4Fbgo9vBZ4MPp4CvBzr2us4lj8ANwYftwI6xtt5ITD94ybguJDzcX08nRfgdGAMsCJk3VGdB6AzsDH4b6fg405N5FjOA5KDjx8OOZZhwQxLAbKC2ZZ0LDkX8x/Io/xmnQLMC1m+D7gv1nUd5TH8DTgXWAukB9elA2uDj58Cpoa0P9Iu1l8EZqt6FzgLeD34S7U75If1yPkhMH7+KcHHycF2FutjCDmWDsEgtFrr4+q88H/z+XYOfp9fB86Pt/MC9K0Vgkd1HoCpwFMh67/ULpbHUmvbZcDzwcdfyq8vzs2x5Fy8dbmEM2F1kxX883Y0sBDo7u6FwU07gO7Bx035GH8N/AioCS53AfZ6YGJw+HKtYU0cHkNZQBHw+2AX0rNm1oY4Oy/uvg34FbAVKCTwfV5M/J6XLxzteWiS56cO3yXwFwZE4VjiLdDjlpm1Bf4K/MDd94du88B/w036+lEzmwTscvfFsa4lQpIJ/Gn8hLuPBkoJ/Gl/RJycl07AZAL/QfUE2gAXxLSoCIuH8xAOM7sfqAKej9Y+4i3Qw5mwuskxs5YEwvx5d58dXL3TzNKD29OBXcH1TfUYxwOXmtlm4CUC3S6PAR0tMDE4fLnWpj5xeAFQ4O4Lg8uzCAR8vJ2Xc4BN7l7k7pXAbALnKl7PyxeO9jw01fMDgJldD0wCrgn+BwVROJZ4C/RwJqxuUszMCMy5utrdHw3ZFDqx9nUE+ta/WP/t4Kf5JwP7Qv70jBl3v8/dM9y9L4Hv+z/c/RpgPoGJweGfj6PJThzu7juAfDMbHFx1NrCKODsvBLpaTjaz1sGftS+OIy7PS4ijPQ/zgPPMrFPwr5bzgutizswuINBVeam7HwrZNAeYErzyKAsYCCziWHIu1h+GNOADh4sIXCmyAbg/1vWEUe9pBP5cXAYsCX5dRKDf8l1gPfAO0DnY3oDpweNbDmTH+hjqOKYz+b+rXPoFfwjzgL8AKcH1qcHlvOD2frGuu47jGAXkBs/NqwSujoi78wL8FFgDrAD+ROCqibg5L8CLBPr/Kwn85XRDQ84Dgf7pvODXd5rQseQR6BP/4vf/yZD29wePZS1wYcj6BuWcbv0XEUkQ8dblIiIiX0GBLiKSIBToIiIJQoEuIpIgFOgiIglCgS4Jz8w+Psr2Z1pwNEmReKJAl4Tn7qfGugaRxqBAl4RnZgeD/55pZu/Z/42B/nzw7sovxp9eY2afAd8MeW6b4BjXi4KDeE0Orn/MzB4MPj7fzD4wM/0+SUwl199EJKGMBoYD24GPgPFmlgs8Q2B8mjzg5ZD29xO4Pf67wYkJFpnZOwSGNM0xsw+B3wAXuXsNIjGkdxTS3Cxy94Jg+C4hMHb1EAIDXK33wK3Tfw5pfx5wr5ktAd4jcOt8pgfG5LgJeBv4rbtvaLQjEPkKeocuzU15yONq6v8dMOByd19bx7YRBEYq7Bmh2kSOid6hiwQGtuprZv2Dy1NDts0D7gjpax8d/LcPcBeBLpwLzWxcI9YrUicFujR77l4GTAPeCH4ouitk88+AlsAyM1sJ/CxkSOS73X07gRH1njWz1EYuXeRLNNqiiEiC0Dt0EZEEoUAXEUkQCnQRkQShQBcRSRAKdBGRBKFAFxFJEAp0EZEE8f8BXvXgC44u6iQAAAAASUVORK5CYII=\n", "text/plain": [""]}, "metadata": {"needs_background": "light"}, "output_type": "display_data"}], "source": ["df.plot(x=\"index\", y=\"velo_jour\")"]}, {"cell_type": "markdown", "metadata": {}, "source": ["## JOIN avec la table stations et les stations \"travail\"\n", "\n", "On trouve les arrondissements o\u00f9 les stations de v\u00e9lib sont les plus remplies en journ\u00e9e au centre de Paris."]}, {"cell_type": "code", "execution_count": 14, "metadata": {}, "outputs": [{"data": {"text/html": ["\n", "\n", "
\n", " \n", " \n", " \n", " number \n", " name \n", " lat \n", " lng \n", " velo_jour \n", " \n", " \n", " \n", " \n", " 0 \n", " 901 \n", " 00901 - PORT SOLF\u00c9RINO (STATION MOBILE) \n", " 48.861380 \n", " 2.324420 \n", " 0.555188 \n", " \n", " \n", " 1 \n", " 903 \n", " 00903 - QUAI MAURIAC / PONT DE BERCY \n", " 48.837134 \n", " 2.374341 \n", " 0.473137 \n", " \n", " \n", " 2 \n", " 904 \n", " 00904 - PLACE JOFFRE / ECOLE MILITAIRE \n", " 48.852136 \n", " 2.301961 \n", " 0.357640 \n", " \n", " \n", " 3 \n", " 905 \n", " 00905 - CONCORDE/BERGES DE SEINE (STATION MOBILE) \n", " 48.863140 \n", " 2.316690 \n", " 0.323395 \n", " \n", " \n", " 4 \n", " 906 \n", " 00906 - GARE DE L'EST \n", " 48.876420 \n", " 2.358630 \n", " 0.117566 \n", " \n", " \n", " 5 \n", " 908 \n", " 00908 - PORT DU GROS CAILLOU (STATION MOBILE) \n", " 48.862880 \n", " 2.306520 \n", " 0.501727 \n", " \n", " \n", " 6 \n", " 1001 \n", " 01001 - ILE DE LA CITE PONT NEUF \n", " 48.857092 \n", " 2.341748 \n", " 0.486796 \n", " \n", " \n", " 7 \n", " 1002 \n", " 01002 - PLACE DU CHATELET \n", " 48.857940 \n", " 2.347010 \n", " 0.355434 \n", " \n", " \n", " 8 \n", " 1003 \n", " 01003 - RIVOLI SAINT DENIS \n", " 48.859150 \n", " 2.347620 \n", " 0.364749 \n", " \n", " \n", " 9 \n", " 1004 \n", " 01004 - MARGUERITE DE NAVARRE \n", " 48.859896 \n", " 2.346757 \n", " 0.440549 \n", " \n", " \n", "
\n", "
"], "text/plain": [" number name lat \\\n", "0 901 00901 - PORT SOLF\u00c9RINO (STATION MOBILE) 48.861380 \n", "1 903 00903 - QUAI MAURIAC / PONT DE BERCY 48.837134 \n", "2 904 00904 - PLACE JOFFRE / ECOLE MILITAIRE 48.852136 \n", "3 905 00905 - CONCORDE/BERGES DE SEINE (STATION MOBILE) 48.863140 \n", "4 906 00906 - GARE DE L'EST 48.876420 \n", "5 908 00908 - PORT DU GROS CAILLOU (STATION MOBILE) 48.862880 \n", "6 1001 01001 - ILE DE LA CITE PONT NEUF 48.857092 \n", "7 1002 01002 - PLACE DU CHATELET 48.857940 \n", "8 1003 01003 - RIVOLI SAINT DENIS 48.859150 \n", "9 1004 01004 - MARGUERITE DE NAVARRE 48.859896 \n", "\n", " lng velo_jour \n", "0 2.324420 0.555188 \n", "1 2.374341 0.473137 \n", "2 2.301961 0.357640 \n", "3 2.316690 0.323395 \n", "4 2.358630 0.117566 \n", "5 2.306520 0.501727 \n", "6 2.341748 0.486796 \n", "7 2.347010 0.355434 \n", "8 2.347620 0.364749 \n", "9 2.346757 0.440549 "]}, "execution_count": 15, "metadata": {}, "output_type": "execute_result"}], "source": ["%%SQL\n", "SELECT C.number, name, lat, lng, velo_jour FROM \n", "(\n", " -- requ\u00eate de la partie pr\u00e9c\u00e9dente\n", " SELECT number, SUM(distribution_temporelle) AS velo_jour\n", " FROM (\n", " -- requ\u00eate de l'exercice 4\n", " SELECT A.number, A.heure, A.minute, 1.0 * A.nb_velo / B.nb_velo_tot AS distribution_temporelle\n", " FROM (\n", " SELECT number, heure, minute, SUM(available_bikes) AS nb_velo\n", " FROM td8_velib\n", " WHERE last_update >= '2013-09-10 11:30:19'\n", " GROUP BY heure, minute, number\n", " ) AS A\n", " JOIN (\n", " SELECT number, heure, minute, SUM(available_bikes) AS nb_velo_tot\n", " FROM td8_velib\n", " WHERE last_update >= '2013-09-10 11:30:19'\n", " GROUP BY number\n", " ) AS B\n", " ON A.number == B.number\n", " )\n", " WHERE heure >= 10 AND heure <= 16 \n", " GROUP BY number\n", ") AS C\n", "INNER JOIN stations\n", "ON C.number == stations.number "]}, {"cell_type": "code", "execution_count": 15, "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.8.7"}}, "nbformat": 4, "nbformat_minor": 2}