{"cells": [{"cell_type": "markdown", "metadata": {}, "source": ["# 1A.soft - Notions de SQL\n", "\n", "Premiers pas avec le langage [SQL](https://fr.wikipedia.org/wiki/Structured_Query_Language)."]}, {"cell_type": "code", "execution_count": 1, "metadata": {}, "outputs": [{"data": {"text/html": ["<div id=\"my_id_menu_nb\">run previous cell, wait for 2 seconds</div>\n", "<script>\n", "function repeat_indent_string(n){\n", "    var a = \"\" ;\n", "    for ( ; n > 0 ; --n)\n", "        a += \"    \";\n", "    return a;\n", "}\n", "// look up into all sections and builds an automated menu //\n", "var update_menu_string = function(begin, lfirst, llast, sformat, send, keep_item, begin_format, end_format) {\n", "    var anchors = document.getElementsByClassName(\"section\");\n", "    if (anchors.length == 0) {\n", "        anchors = document.getElementsByClassName(\"text_cell_render rendered_html\");\n", "    }\n", "    var i,t;\n", "    var text_menu = begin;\n", "    var text_memo = \"<pre>\\nlength:\" + anchors.length + \"\\n\";\n", "    var ind = \"\";\n", "    var memo_level = 1;\n", "    var href;\n", "    var tags = [];\n", "    var main_item = 0;\n", "    var format_open = 0;\n", "    for (i = 0; i <= llast; i++)\n", "        tags.push(\"h\" + i);\n", "\n", "    for (i = 0; i < anchors.length; i++) {\n", "        text_memo += \"**\" + anchors[i].id + \"--\\n\";\n", "\n", "        var child = null;\n", "        for(t = 0; t < tags.length; t++) {\n", "            var r = anchors[i].getElementsByTagName(tags[t]);\n", "            if (r.length > 0) {\n", "child = r[0];\n", "break;\n", "            }\n", "        }\n", "        if (child == null) {\n", "            text_memo += \"null\\n\";\n", "            continue;\n", "        }\n", "        if (anchors[i].hasAttribute(\"id\")) {\n", "            // when converted in RST\n", "            href = anchors[i].id;\n", "            text_memo += \"#1-\" + href;\n", "            // passer \u00e0 child suivant (le chercher)\n", "        }\n", "        else if (child.hasAttribute(\"id\")) {\n", "            // in a notebook\n", "            href = child.id;\n", "            text_memo += \"#2-\" + href;\n", "        }\n", "        else {\n", "            text_memo += \"#3-\" + \"*\" + \"\\n\";\n", "            continue;\n", "        }\n", "        var title = child.textContent;\n", "        var level = parseInt(child.tagName.substring(1,2));\n", "\n", "        text_memo += \"--\" + level + \"?\" + lfirst + \"--\" + title + \"\\n\";\n", "\n", "        if ((level < lfirst) || (level > llast)) {\n", "            continue ;\n", "        }\n", "        if (title.endsWith('\u00b6')) {\n", "            title = title.substring(0,title.length-1).replace(\"<\", \"&lt;\")\n", "         .replace(\">\", \"&gt;\").replace(\"&\", \"&amp;\");\n", "        }\n", "        if (title.length == 0) {\n", "            continue;\n", "        }\n", "\n", "        while (level < memo_level) {\n", "            text_menu += end_format + \"</ul>\\n\";\n", "            format_open -= 1;\n", "            memo_level -= 1;\n", "        }\n", "        if (level == lfirst) {\n", "            main_item += 1;\n", "        }\n", "        if (keep_item != -1 && main_item != keep_item + 1) {\n", "            // alert(main_item + \" - \" + level + \" - \" + keep_item);\n", "            continue;\n", "        }\n", "        while (level > memo_level) {\n", "            text_menu += \"<ul>\\n\";\n", "            memo_level += 1;\n", "        }\n", "        text_menu += repeat_indent_string(level-2);\n", "        text_menu += begin_format + sformat.replace(\"__HREF__\", href).replace(\"__TITLE__\", title);\n", "        format_open += 1;\n", "    }\n", "    while (1 < memo_level) {\n", "        text_menu += end_format + \"</ul>\\n\";\n", "        memo_level -= 1;\n", "        format_open -= 1;\n", "    }\n", "    text_menu += send;\n", "    //text_menu += \"\\n\" + text_memo;\n", "\n", "    while (format_open > 0) {\n", "        text_menu += end_format;\n", "        format_open -= 1;\n", "    }\n", "    return text_menu;\n", "};\n", "var update_menu = function() {\n", "    var sbegin = \"\";\n", "    var sformat = '<a href=\"#__HREF__\">__TITLE__</a>';\n", "    var send = \"\";\n", "    var begin_format = '<li>';\n", "    var end_format = '</li>';\n", "    var keep_item = -1;\n", "    var text_menu = update_menu_string(sbegin, 2, 4, sformat, send, keep_item,\n", "       begin_format, end_format);\n", "    var menu = document.getElementById(\"my_id_menu_nb\");\n", "    menu.innerHTML=text_menu;\n", "};\n", "window.setTimeout(update_menu,2000);\n", "            </script>"], "text/plain": ["<IPython.core.display.HTML object>"]}, "execution_count": 2, "metadata": {}, "output_type": "execute_result"}], "source": ["from jyquickhelper import add_notebook_menu\n", "add_notebook_menu()"]}, {"cell_type": "markdown", "metadata": {}, "source": ["Le langage SQL est utilis\u00e9 pour manipuler des [bases de donn\u00e9es](https://fr.wikipedia.org/wiki/Base_de_donn%C3%A9es). Pour faire simple, on utilise les bases de donn\u00e9es pour acc\u00e9der rapidement \u00e0 une information dans des donn\u00e9es qui font parfois plusieurs milliards de lignes.\n", "\n", "* Le tableau dont on se sert est trop grand (comme trier 50000 lignes).\n", "* On souhaite faire des op\u00e9rations sur deux feuilles Excel (associer les lignes de l'une avec celles de l'autre).\n", "\n", "Lorsque le volume de donn\u00e9es est important, il est impossible de les voir dans leur ensemble. On peut en voir soit une partie soit une aggr\u00e9gation. Par exemple, la soci\u00e9t\u00e9 qui g\u00e8re les v\u00e9lib a ouvert l'acc\u00e8s \u00e0 ses donn\u00e9es. Il est possible de t\u00e9l\u00e9charger aussi souvent qu'on veut (toutes les minutes par exemple) un \u00e9tat complet des v\u00e9los et places disponibles pour toutes les stations de Paris : c'est une table qui s'enrichit de 1300 lignes toutes les minutes."]}, {"cell_type": "markdown", "metadata": {}, "source": ["## R\u00e9cup\u00e9rer les donn\u00e9e"]}, {"cell_type": "code", "execution_count": 2, "metadata": {}, "outputs": [{"data": {"text/plain": ["['stations.txt', 'td8_velib.txt']"]}, "execution_count": 3, "metadata": {}, "output_type": "execute_result"}], "source": ["from pyensae.datasource import download_data\n", "download_data(\"td8_velib.zip\", website = 'xd')"]}, {"cell_type": "markdown", "metadata": {}, "source": ["On cr\u00e9e une base de donn\u00e9es [sqlite3](https://www.sqlite.org/). On peut la consulter avec un outil tel que [SqliteSpy](https://www.yunqa.de/delphi/products/sqlitespy/index) sous Windows, [sqlite_bro](https://pypi.python.org/pypi/sqlite_bro) sur tous les OS."]}, {"cell_type": "code", "execution_count": 3, "metadata": {"scrolled": false}, "outputs": [{"name": "stdout", "output_type": "stream", "text": ["remove  td8_velib\n", "SQL  'DROP TABLE td8_velib'\n", "  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', <class 'str'>), 1: ('last_update', <class 'str'>), 2: ('available_bike_stands', <class 'int'>), 3: ('available_bikes', <class 'int'>), 4: ('number', <class 'int'>), 5: ('heure', <class 'int'>), 6: ('minute', <class 'int'>)}\n", "  compiling ^(?P<collect_date>.*)\\t(?P<last_update>.*)\\t(?P<available_bike_stands>([-]?[1-9][0-9]*?)|(0?))\\t(?P<available_bikes>([-]?[1-9][0-9]*?)|(0?))\\t(?P<number>([-]?[1-9][0-9]*?)|(0?))\\t(?P<heure>([-]?[1-9][0-9]*?)|(0?))\\t(?P<minute>([-]?[1-9][0-9]*?)|(0?))$\n", "  TextFile.guess_columns: regex  ^(?P<collect_date>.*)\\t(?P<last_update>.*)\\t(?P<available_bike_stands>([-]?[1-9][0-9]*?)|(0?))\\t(?P<available_bikes>([-]?[1-9][0-9]*?)|(0?))\\t(?P<number>([-]?[1-9][0-9]*?)|(0?))\\t(?P<heure>([-]?[1-9][0-9]*?)|(0?))\\t(?P<minute>([-]?[1-9][0-9]*?)|(0?))$\n", "  TextFile.guess_columns: header  True  columns  {0: ('collect_date', (<class 'str'>, 52)), 1: ('last_update', (<class 'str'>, 38)), 2: ('available_bike_stands', <class 'int'>), 3: ('available_bikes', <class 'int'>), 4: ('number', <class 'int'>), 5: ('heure', <class 'int'>), 6: ('minute', <class 'int'>)}\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: {<class 'str'>: 1000}, 1: {<class 'str'>: 1000}, 2: {<class 'int'>: 1000}, 3: {<class 'int'>: 1000}, 4: {<class 'int'>: 1000}, 5: {<class 'int'>: 1000}, 6: {<class 'int'>: 1000}}\n", "   columns  {0: ('collect_date', <class 'str'>), 1: ('last_update', <class 'str'>), 2: ('available_bike_stands', <class 'int'>), 3: ('available_bikes', <class 'int'>), 4: ('number', <class 'int'>), 5: ('heure', <class 'int'>), 6: ('minute', <class 'int'>)}\n", "   guess {0: ('collect_date', (<class 'str'>, 52)), 1: ('last_update', (<class 'str'>, 38)), 2: ('available_bike_stands', <class 'int'>), 3: ('available_bikes', <class 'int'>), 4: ('number', <class 'int'>), 5: ('heure', <class 'int'>), 6: ('minute', <class 'int'>)}\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', (<class 'str'>, 52)), 1: ('last_update', (<class 'str'>, 38)), 2: ('available_bike_stands', <class 'int'>), 3: ('available_bikes', <class 'int'>), 4: ('number', <class 'int'>), 5: ('heure', <class 'int'>), 6: ('minute', <class 'int'>)}\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', <class 'str'>), 1: ('last_update', <class 'str'>), 2: ('available_bike_stands', <class 'int'>), 3: ('available_bikes', <class 'int'>), 4: ('number', <class 'int'>), 5: ('heure', <class 'int'>), 6: ('minute', <class 'int'>)}\n", "  compiling ^(?P<collect_date>.*)\\t(?P<last_update>.*)\\t(?P<available_bike_stands>([-]?[1-9][0-9]*?)|(0?))\\t(?P<available_bikes>([-]?[1-9][0-9]*?)|(0?))\\t(?P<number>([-]?[1-9][0-9]*?)|(0?))\\t(?P<heure>([-]?[1-9][0-9]*?)|(0?))\\t(?P<minute>([-]?[1-9][0-9]*?)|(0?))$\n", "  TextFile.guess_columns: regex  ^(?P<collect_date>.*)\\t(?P<last_update>.*)\\t(?P<available_bike_stands>([-]?[1-9][0-9]*?)|(0?))\\t(?P<available_bikes>([-]?[1-9][0-9]*?)|(0?))\\t(?P<number>([-]?[1-9][0-9]*?)|(0?))\\t(?P<heure>([-]?[1-9][0-9]*?)|(0?))\\t(?P<minute>([-]?[1-9][0-9]*?)|(0?))$\n", "  TextFile.guess_columns: header  True  columns  {0: ('collect_date', (<class 'str'>, 52)), 1: ('last_update', (<class 'str'>, 38)), 2: ('available_bike_stands', <class 'int'>), 3: ('available_bikes', <class 'int'>), 4: ('number', <class 'int'>), 5: ('heure', <class 'int'>), 6: ('minute', <class 'int'>)}\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<collect_date>.*)\\t(?P<last_update>.*)\\t(?P<available_bike_stands>([-]?[1-9][0-9]*?)|(0?))\\t(?P<available_bikes>([-]?[1-9][0-9]*?)|(0?))\\t(?P<number>([-]?[1-9][0-9]*?)|(0?))\\t(?P<heure>([-]?[1-9][0-9]*?)|(0?))\\t(?P<minute>([-]?[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", "remove  stations\n", "SQL  'DROP TABLE stations'\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', <class 'str'>), 1: ('contract_name', <class 'str'>), 2: ('lat', <class 'float'>), 3: ('lng', <class 'float'>), 4: ('name', <class 'str'>), 5: ('number', <class 'int'>)}\n", "  compiling ^(?P<address>.*)\\t(?P<contract_name>.*)\\t(?P<lat>[-]?[0-9]*?([.][0-9]*?)?([eE][-]?[0-9]{0,4})?)\\t(?P<lng>[-]?[0-9]*?([.][0-9]*?)?([eE][-]?[0-9]{0,4})?)\\t(?P<name>.*)\\t(?P<number>([-]?[1-9][0-9]*?)|(0?))$\n", "  TextFile.guess_columns: regex  ^(?P<address>.*)\\t(?P<contract_name>.*)\\t(?P<lat>[-]?[0-9]*?([.][0-9]*?)?([eE][-]?[0-9]{0,4})?)\\t(?P<lng>[-]?[0-9]*?([.][0-9]*?)?([eE][-]?[0-9]{0,4})?)\\t(?P<name>.*)\\t(?P<number>([-]?[1-9][0-9]*?)|(0?))$\n", "  TextFile.guess_columns: header  True  columns  {0: ('address', (<class 'str'>, 134)), 1: ('contract_name', (<class 'str'>, 10)), 2: ('lat', <class 'float'>), 3: ('lng', <class 'float'>), 4: ('name', (<class 'str'>, 98)), 5: ('number', <class 'int'>)}\n", "  [_guess_columns] sep=['\\t']\n", "  TextFile: closing file  stations.txt\n", "  [_guess_columns] columns_name=None\n", "   guess with  932 lines\n", "   count_types  {0: {<class 'str'>: 931}, 1: {<class 'str'>: 931}, 2: {<class 'float'>: 931}, 3: {<class 'float'>: 931}, 4: {<class 'str'>: 931}, 5: {<class 'int'>: 931}}\n", "   columns  {0: ('address', <class 'str'>), 1: ('contract_name', <class 'str'>), 2: ('lat', <class 'float'>), 3: ('lng', <class 'float'>), 4: ('name', <class 'str'>), 5: ('number', <class 'int'>)}\n", "   guess {0: ('address', (<class 'str'>, 188)), 1: ('contract_name', (<class 'str'>, 10)), 2: ('lat', <class 'float'>), 3: ('lng', <class 'float'>), 4: ('name', (<class 'str'>, 98)), 5: ('number', <class 'int'>)}\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', (<class 'str'>, 188)), 1: ('contract_name', (<class 'str'>, 10)), 2: ('lat', <class 'float'>), 3: ('lng', <class 'float'>), 4: ('name', (<class 'str'>, 98)), 5: ('number', <class 'int'>)}\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', <class 'str'>), 1: ('contract_name', <class 'str'>), 2: ('lat', <class 'float'>), 3: ('lng', <class 'float'>), 4: ('name', <class 'str'>), 5: ('number', <class 'int'>)}\n", "  compiling ^(?P<address>.*)\\t(?P<contract_name>.*)\\t(?P<lat>[-]?[0-9]*?([.][0-9]*?)?([eE][-]?[0-9]{0,4})?)\\t(?P<lng>[-]?[0-9]*?([.][0-9]*?)?([eE][-]?[0-9]{0,4})?)\\t(?P<name>.*)\\t(?P<number>([-]?[1-9][0-9]*?)|(0?))$\n", "  TextFile.guess_columns: regex  ^(?P<address>.*)\\t(?P<contract_name>.*)\\t(?P<lat>[-]?[0-9]*?([.][0-9]*?)?([eE][-]?[0-9]{0,4})?)\\t(?P<lng>[-]?[0-9]*?([.][0-9]*?)?([eE][-]?[0-9]{0,4})?)\\t(?P<name>.*)\\t(?P<number>([-]?[1-9][0-9]*?)|(0?))$\n", "  TextFile.guess_columns: header  True  columns  {0: ('address', (<class 'str'>, 134)), 1: ('contract_name', (<class 'str'>, 10)), 2: ('lat', <class 'float'>), 3: ('lng', <class 'float'>), 4: ('name', (<class 'str'>, 98)), 5: ('number', <class 'int'>)}\n", "  TextFile: opening file  stations.txt\n", "^(?P<address>.*)\\t(?P<contract_name>.*)\\t(?P<lat>[-]?[0-9]*?([.][0-9]*?)?([eE][-]?[0-9]{0,4})?)\\t(?P<lng>[-]?[0-9]*?([.][0-9]*?)?([eE][-]?[0-9]{0,4})?)\\t(?P<name>.*)\\t(?P<number>([-]?[1-9][0-9]*?)|(0?))$\n", "error regex 0 unable to interpret line  1232 :  ''\n"]}, {"name": "stdout", "output_type": "stream", "text": ["  TextFile: closing file  stations.txt\n", "1231  lines imported\n"]}, {"data": {"text/plain": ["'stations'"]}, "execution_count": 4, "metadata": {}, "output_type": "execute_result"}], "source": ["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": "markdown", "metadata": {}, "source": ["Vous devriez voir un fichier .db3."]}, {"cell_type": "code", "execution_count": 4, "metadata": {}, "outputs": [{"data": {"text/plain": ["['td8_velib.db3']"]}, "execution_count": 5, "metadata": {}, "output_type": "execute_result"}], "source": ["import os\n", "[ _ for _ in os.listdir(\".\") if \".db3\" in _]"]}, {"cell_type": "markdown", "metadata": {}, "source": [" ## Premi\u00e8res requ\u00eates SQL\n", " \n", " Dans notre cas, on va faire cela depuis le notebook."]}, {"cell_type": "code", "execution_count": 5, "metadata": {}, "outputs": [], "source": ["%load_ext pyensae"]}, {"cell_type": "code", "execution_count": 6, "metadata": {}, "outputs": [{"data": {"text/plain": ["<pyensae.sql.sql_interface_database.InterfaceSQLDatabase at 0x15ee7322d30>"]}, "execution_count": 7, "metadata": {}, "output_type": "execute_result"}], "source": ["%SQL_connect td8_velib.db3"]}, {"cell_type": "markdown", "metadata": {}, "source": ["On regarde les tables de la base de donn\u00e9es."]}, {"cell_type": "code", "execution_count": 7, "metadata": {}, "outputs": [{"data": {"text/plain": ["['stations', 'td8_velib']"]}, "execution_count": 8, "metadata": {}, "output_type": "execute_result"}], "source": ["%SQL_tables"]}, {"cell_type": "markdown", "metadata": {}, "source": ["On regarde les colonnes de chaque table."]}, {"cell_type": "code", "execution_count": 8, "metadata": {}, "outputs": [{"data": {"text/plain": ["{0: ('address', str),\n", " 1: ('contract_name', str),\n", " 2: ('lat', float),\n", " 3: ('lng', float),\n", " 4: ('name', str),\n", " 5: ('number', int)}"]}, "execution_count": 9, "metadata": {}, "output_type": "execute_result"}], "source": ["%SQL_schema stations"]}, {"cell_type": "code", "execution_count": 9, "metadata": {}, "outputs": [{"data": {"text/plain": ["{0: ('collect_date', str),\n", " 1: ('last_update', str),\n", " 2: ('available_bike_stands', int),\n", " 3: ('available_bikes', int),\n", " 4: ('number', int),\n", " 5: ('heure', int),\n", " 6: ('minute', int)}"]}, "execution_count": 10, "metadata": {}, "output_type": "execute_result"}], "source": ["%SQL_schema td8_velib"]}, {"cell_type": "markdown", "metadata": {}, "source": ["Et enfin on regarde les premi\u00e8res lignes."]}, {"cell_type": "code", "execution_count": 10, "metadata": {}, "outputs": [{"data": {"text/html": ["<div>\n", "<style scoped>\n", "    .dataframe tbody tr th:only-of-type {\n", "        vertical-align: middle;\n", "    }\n", "\n", "    .dataframe tbody tr th {\n", "        vertical-align: top;\n", "    }\n", "\n", "    .dataframe thead th {\n", "        text-align: right;\n", "    }\n", "</style>\n", "<table border=\"1\" class=\"dataframe\">\n", "  <thead>\n", "    <tr style=\"text-align: right;\">\n", "      <th></th>\n", "      <th>collect_date</th>\n", "      <th>last_update</th>\n", "      <th>available_bike_stands</th>\n", "      <th>available_bikes</th>\n", "      <th>number</th>\n", "      <th>heure</th>\n", "      <th>minute</th>\n", "    </tr>\n", "  </thead>\n", "  <tbody>\n", "    <tr>\n", "      <th>0</th>\n", "      <td>2013-09-13 11:26:37.738913</td>\n", "      <td>2013-07-22 09:00:19</td>\n", "      <td>0</td>\n", "      <td>0</td>\n", "      <td>15025</td>\n", "      <td>9</td>\n", "      <td>0</td>\n", "    </tr>\n", "    <tr>\n", "      <th>1</th>\n", "      <td>2013-09-13 11:26:37.738913</td>\n", "      <td>2013-07-22 09:05:19</td>\n", "      <td>0</td>\n", "      <td>0</td>\n", "      <td>15025</td>\n", "      <td>9</td>\n", "      <td>5</td>\n", "    </tr>\n", "    <tr>\n", "      <th>2</th>\n", "      <td>2013-09-13 11:26:37.738913</td>\n", "      <td>2013-07-22 09:10:19</td>\n", "      <td>0</td>\n", "      <td>0</td>\n", "      <td>15025</td>\n", "      <td>9</td>\n", "      <td>10</td>\n", "    </tr>\n", "    <tr>\n", "      <th>3</th>\n", "      <td>2013-09-13 11:26:37.738913</td>\n", "      <td>2013-07-22 09:15:19</td>\n", "      <td>0</td>\n", "      <td>0</td>\n", "      <td>15025</td>\n", "      <td>9</td>\n", "      <td>15</td>\n", "    </tr>\n", "    <tr>\n", "      <th>4</th>\n", "      <td>2013-09-13 11:26:37.738913</td>\n", "      <td>2013-07-22 09:20:19</td>\n", "      <td>0</td>\n", "      <td>0</td>\n", "      <td>15025</td>\n", "      <td>9</td>\n", "      <td>20</td>\n", "    </tr>\n", "    <tr>\n", "      <th>5</th>\n", "      <td>2013-09-13 11:26:37.738913</td>\n", "      <td>2013-07-22 09:25:19</td>\n", "      <td>0</td>\n", "      <td>0</td>\n", "      <td>15025</td>\n", "      <td>9</td>\n", "      <td>25</td>\n", "    </tr>\n", "    <tr>\n", "      <th>6</th>\n", "      <td>2013-09-13 11:26:37.738913</td>\n", "      <td>2013-07-22 09:30:19</td>\n", "      <td>0</td>\n", "      <td>0</td>\n", "      <td>15025</td>\n", "      <td>9</td>\n", "      <td>30</td>\n", "    </tr>\n", "    <tr>\n", "      <th>7</th>\n", "      <td>2013-09-13 11:26:37.738913</td>\n", "      <td>2013-07-22 09:35:19</td>\n", "      <td>0</td>\n", "      <td>0</td>\n", "      <td>15025</td>\n", "      <td>9</td>\n", "      <td>35</td>\n", "    </tr>\n", "    <tr>\n", "      <th>8</th>\n", "      <td>2013-09-13 11:26:37.738913</td>\n", "      <td>2013-07-22 09:40:19</td>\n", "      <td>0</td>\n", "      <td>0</td>\n", "      <td>15025</td>\n", "      <td>9</td>\n", "      <td>40</td>\n", "    </tr>\n", "    <tr>\n", "      <th>9</th>\n", "      <td>2013-09-13 11:26:37.738913</td>\n", "      <td>2013-07-22 09:45:19</td>\n", "      <td>0</td>\n", "      <td>0</td>\n", "      <td>15025</td>\n", "      <td>9</td>\n", "      <td>45</td>\n", "    </tr>\n", "  </tbody>\n", "</table>\n", "</div>"], "text/plain": ["                 collect_date          last_update  available_bike_stands  \\\n", "0  2013-09-13 11:26:37.738913  2013-07-22 09:00:19                      0   \n", "1  2013-09-13 11:26:37.738913  2013-07-22 09:05:19                      0   \n", "2  2013-09-13 11:26:37.738913  2013-07-22 09:10:19                      0   \n", "3  2013-09-13 11:26:37.738913  2013-07-22 09:15:19                      0   \n", "4  2013-09-13 11:26:37.738913  2013-07-22 09:20:19                      0   \n", "5  2013-09-13 11:26:37.738913  2013-07-22 09:25:19                      0   \n", "6  2013-09-13 11:26:37.738913  2013-07-22 09:30:19                      0   \n", "7  2013-09-13 11:26:37.738913  2013-07-22 09:35:19                      0   \n", "8  2013-09-13 11:26:37.738913  2013-07-22 09:40:19                      0   \n", "9  2013-09-13 11:26:37.738913  2013-07-22 09:45:19                      0   \n", "\n", "   available_bikes  number  heure  minute  \n", "0                0   15025      9       0  \n", "1                0   15025      9       5  \n", "2                0   15025      9      10  \n", "3                0   15025      9      15  \n", "4                0   15025      9      20  \n", "5                0   15025      9      25  \n", "6                0   15025      9      30  \n", "7                0   15025      9      35  \n", "8                0   15025      9      40  \n", "9                0   15025      9      45  "]}, "execution_count": 11, "metadata": {}, "output_type": "execute_result"}], "source": ["%%SQL\n", "SELECT * FROM td8_velib LIMIT 10"]}, {"cell_type": "markdown", "metadata": {}, "source": [" On s\u00e9lectionne les donn\u00e9es sur une plage horaire donn\u00e9e."]}, {"cell_type": "code", "execution_count": 11, "metadata": {}, "outputs": [{"data": {"text/html": ["<div>\n", "<style scoped>\n", "    .dataframe tbody tr th:only-of-type {\n", "        vertical-align: middle;\n", "    }\n", "\n", "    .dataframe tbody tr th {\n", "        vertical-align: top;\n", "    }\n", "\n", "    .dataframe thead th {\n", "        text-align: right;\n", "    }\n", "</style>\n", "<table border=\"1\" class=\"dataframe\">\n", "  <thead>\n", "    <tr style=\"text-align: right;\">\n", "      <th></th>\n", "      <th>collect_date</th>\n", "      <th>last_update</th>\n", "      <th>available_bike_stands</th>\n", "      <th>available_bikes</th>\n", "      <th>number</th>\n", "      <th>heure</th>\n", "      <th>minute</th>\n", "    </tr>\n", "  </thead>\n", "  <tbody>\n", "    <tr>\n", "      <th>0</th>\n", "      <td>2013-09-13 10:01:38.208021</td>\n", "      <td>2013-09-13 10:00:19</td>\n", "      <td>15</td>\n", "      <td>12</td>\n", "      <td>19115</td>\n", "      <td>10</td>\n", "      <td>0</td>\n", "    </tr>\n", "    <tr>\n", "      <th>1</th>\n", "      <td>2013-09-13 10:01:38.215022</td>\n", "      <td>2013-09-13 10:00:19</td>\n", "      <td>8</td>\n", "      <td>11</td>\n", "      <td>4101</td>\n", "      <td>10</td>\n", "      <td>0</td>\n", "    </tr>\n", "    <tr>\n", "      <th>2</th>\n", "      <td>2013-09-13 10:00:39.333106</td>\n", "      <td>2013-09-13 10:00:19</td>\n", "      <td>14</td>\n", "      <td>11</td>\n", "      <td>4103</td>\n", "      <td>10</td>\n", "      <td>0</td>\n", "    </tr>\n", "    <tr>\n", "      <th>3</th>\n", "      <td>2013-09-13 10:00:39.340107</td>\n", "      <td>2013-09-13 10:00:19</td>\n", "      <td>9</td>\n", "      <td>7</td>\n", "      <td>4104</td>\n", "      <td>10</td>\n", "      <td>0</td>\n", "    </tr>\n", "    <tr>\n", "      <th>4</th>\n", "      <td>2013-09-13 09:57:39.683954</td>\n", "      <td>2013-09-13 10:00:19</td>\n", "      <td>31</td>\n", "      <td>1</td>\n", "      <td>15020</td>\n", "      <td>10</td>\n", "      <td>0</td>\n", "    </tr>\n", "    <tr>\n", "      <th>5</th>\n", "      <td>2013-09-13 09:59:38.068877</td>\n", "      <td>2013-09-13 10:00:19</td>\n", "      <td>5</td>\n", "      <td>13</td>\n", "      <td>4107</td>\n", "      <td>10</td>\n", "      <td>0</td>\n", "    </tr>\n", "    <tr>\n", "      <th>6</th>\n", "      <td>2013-09-13 10:00:39.336106</td>\n", "      <td>2013-09-13 10:00:19</td>\n", "      <td>47</td>\n", "      <td>16</td>\n", "      <td>15021</td>\n", "      <td>10</td>\n", "      <td>0</td>\n", "    </tr>\n", "    <tr>\n", "      <th>7</th>\n", "      <td>2013-09-13 09:53:38.839575</td>\n", "      <td>2013-09-13 10:00:19</td>\n", "      <td>19</td>\n", "      <td>0</td>\n", "      <td>19118</td>\n", "      <td>10</td>\n", "      <td>0</td>\n", "    </tr>\n", "    <tr>\n", "      <th>8</th>\n", "      <td>2013-09-13 10:00:39.343107</td>\n", "      <td>2013-09-13 10:00:19</td>\n", "      <td>19</td>\n", "      <td>1</td>\n", "      <td>20503</td>\n", "      <td>10</td>\n", "      <td>0</td>\n", "    </tr>\n", "    <tr>\n", "      <th>9</th>\n", "      <td>2013-09-13 10:00:39.339106</td>\n", "      <td>2013-09-13 10:00:19</td>\n", "      <td>23</td>\n", "      <td>11</td>\n", "      <td>15023</td>\n", "      <td>10</td>\n", "      <td>0</td>\n", "    </tr>\n", "  </tbody>\n", "</table>\n", "</div>"], "text/plain": ["                 collect_date          last_update  available_bike_stands  \\\n", "0  2013-09-13 10:01:38.208021  2013-09-13 10:00:19                     15   \n", "1  2013-09-13 10:01:38.215022  2013-09-13 10:00:19                      8   \n", "2  2013-09-13 10:00:39.333106  2013-09-13 10:00:19                     14   \n", "3  2013-09-13 10:00:39.340107  2013-09-13 10:00:19                      9   \n", "4  2013-09-13 09:57:39.683954  2013-09-13 10:00:19                     31   \n", "5  2013-09-13 09:59:38.068877  2013-09-13 10:00:19                      5   \n", "6  2013-09-13 10:00:39.336106  2013-09-13 10:00:19                     47   \n", "7  2013-09-13 09:53:38.839575  2013-09-13 10:00:19                     19   \n", "8  2013-09-13 10:00:39.343107  2013-09-13 10:00:19                     19   \n", "9  2013-09-13 10:00:39.339106  2013-09-13 10:00:19                     23   \n", "\n", "   available_bikes  number  heure  minute  \n", "0               12   19115     10       0  \n", "1               11    4101     10       0  \n", "2               11    4103     10       0  \n", "3                7    4104     10       0  \n", "4                1   15020     10       0  \n", "5               13    4107     10       0  \n", "6               16   15021     10       0  \n", "7                0   19118     10       0  \n", "8                1   20503     10       0  \n", "9               11   15023     10       0  "]}, "execution_count": 12, "metadata": {}, "output_type": "execute_result"}], "source": ["%%SQL\n", "SELECT * FROM td8_velib WHERE last_update >= '2013-09-13 10:00:00' AND last_update <= '2013-09-13 11:00:00'"]}, {"cell_type": "markdown", "metadata": {}, "source": ["S\u00e9lectionner certaines colonnes et ordonner les valeurs."]}, {"cell_type": "code", "execution_count": 12, "metadata": {}, "outputs": [{"data": {"text/html": ["<div>\n", "<style scoped>\n", "    .dataframe tbody tr th:only-of-type {\n", "        vertical-align: middle;\n", "    }\n", "\n", "    .dataframe tbody tr th {\n", "        vertical-align: top;\n", "    }\n", "\n", "    .dataframe thead th {\n", "        text-align: right;\n", "    }\n", "</style>\n", "<table border=\"1\" class=\"dataframe\">\n", "  <thead>\n", "    <tr style=\"text-align: right;\">\n", "      <th></th>\n", "      <th>available_bike_stands</th>\n", "      <th>available_bikes</th>\n", "    </tr>\n", "  </thead>\n", "  <tbody>\n", "    <tr>\n", "      <th>0</th>\n", "      <td>68</td>\n", "      <td>2</td>\n", "    </tr>\n", "    <tr>\n", "      <th>1</th>\n", "      <td>68</td>\n", "      <td>2</td>\n", "    </tr>\n", "    <tr>\n", "      <th>2</th>\n", "      <td>68</td>\n", "      <td>2</td>\n", "    </tr>\n", "    <tr>\n", "      <th>3</th>\n", "      <td>68</td>\n", "      <td>2</td>\n", "    </tr>\n", "    <tr>\n", "      <th>4</th>\n", "      <td>68</td>\n", "      <td>2</td>\n", "    </tr>\n", "    <tr>\n", "      <th>5</th>\n", "      <td>68</td>\n", "      <td>2</td>\n", "    </tr>\n", "    <tr>\n", "      <th>6</th>\n", "      <td>67</td>\n", "      <td>3</td>\n", "    </tr>\n", "    <tr>\n", "      <th>7</th>\n", "      <td>67</td>\n", "      <td>3</td>\n", "    </tr>\n", "    <tr>\n", "      <th>8</th>\n", "      <td>67</td>\n", "      <td>3</td>\n", "    </tr>\n", "    <tr>\n", "      <th>9</th>\n", "      <td>67</td>\n", "      <td>3</td>\n", "    </tr>\n", "  </tbody>\n", "</table>\n", "</div>"], "text/plain": ["   available_bike_stands  available_bikes\n", "0                     68                2\n", "1                     68                2\n", "2                     68                2\n", "3                     68                2\n", "4                     68                2\n", "5                     68                2\n", "6                     67                3\n", "7                     67                3\n", "8                     67                3\n", "9                     67                3"]}, "execution_count": 13, "metadata": {}, "output_type": "execute_result"}], "source": ["%%SQL\n", "SELECT available_bike_stands, available_bikes FROM td8_velib \n", "WHERE last_update >= '2013-09-13 10:00:00' AND last_update <= '2013-09-13 11:00:00'\n", "ORDER BY available_bike_stands DESC ;"]}, {"cell_type": "markdown", "metadata": {}, "source": ["Compter le nombre d'emplacements de chaque station."]}, {"cell_type": "code", "execution_count": 13, "metadata": {}, "outputs": [{"data": {"text/html": ["<div>\n", "<style scoped>\n", "    .dataframe tbody tr th:only-of-type {\n", "        vertical-align: middle;\n", "    }\n", "\n", "    .dataframe tbody tr th {\n", "        vertical-align: top;\n", "    }\n", "\n", "    .dataframe thead th {\n", "        text-align: right;\n", "    }\n", "</style>\n", "<table border=\"1\" class=\"dataframe\">\n", "  <thead>\n", "    <tr style=\"text-align: right;\">\n", "      <th></th>\n", "      <th>last_update</th>\n", "      <th>place</th>\n", "      <th>number</th>\n", "    </tr>\n", "  </thead>\n", "  <tbody>\n", "    <tr>\n", "      <th>0</th>\n", "      <td>2013-09-13 10:00:19</td>\n", "      <td>70</td>\n", "      <td>16004</td>\n", "    </tr>\n", "    <tr>\n", "      <th>1</th>\n", "      <td>2013-09-13 10:05:19</td>\n", "      <td>70</td>\n", "      <td>16004</td>\n", "    </tr>\n", "    <tr>\n", "      <th>2</th>\n", "      <td>2013-09-13 10:10:19</td>\n", "      <td>70</td>\n", "      <td>16004</td>\n", "    </tr>\n", "    <tr>\n", "      <th>3</th>\n", "      <td>2013-09-13 10:15:19</td>\n", "      <td>70</td>\n", "      <td>16004</td>\n", "    </tr>\n", "    <tr>\n", "      <th>4</th>\n", "      <td>2013-09-13 10:20:19</td>\n", "      <td>70</td>\n", "      <td>16004</td>\n", "    </tr>\n", "    <tr>\n", "      <th>5</th>\n", "      <td>2013-09-13 10:25:19</td>\n", "      <td>70</td>\n", "      <td>16004</td>\n", "    </tr>\n", "    <tr>\n", "      <th>6</th>\n", "      <td>2013-09-13 10:30:19</td>\n", "      <td>70</td>\n", "      <td>16004</td>\n", "    </tr>\n", "    <tr>\n", "      <th>7</th>\n", "      <td>2013-09-13 10:35:19</td>\n", "      <td>70</td>\n", "      <td>16004</td>\n", "    </tr>\n", "    <tr>\n", "      <th>8</th>\n", "      <td>2013-09-13 10:40:19</td>\n", "      <td>70</td>\n", "      <td>16004</td>\n", "    </tr>\n", "    <tr>\n", "      <th>9</th>\n", "      <td>2013-09-13 10:45:19</td>\n", "      <td>70</td>\n", "      <td>16004</td>\n", "    </tr>\n", "  </tbody>\n", "</table>\n", "</div>"], "text/plain": ["           last_update  place  number\n", "0  2013-09-13 10:00:19     70   16004\n", "1  2013-09-13 10:05:19     70   16004\n", "2  2013-09-13 10:10:19     70   16004\n", "3  2013-09-13 10:15:19     70   16004\n", "4  2013-09-13 10:20:19     70   16004\n", "5  2013-09-13 10:25:19     70   16004\n", "6  2013-09-13 10:30:19     70   16004\n", "7  2013-09-13 10:35:19     70   16004\n", "8  2013-09-13 10:40:19     70   16004\n", "9  2013-09-13 10:45:19     70   16004"]}, "execution_count": 14, "metadata": {}, "output_type": "execute_result"}], "source": ["%%SQL\n", "SELECT last_update, available_bike_stands + available_bikes AS place, number FROM td8_velib \n", "WHERE last_update >= '2013-09-13 10:00:00' AND last_update <= '2013-09-13 11:00:00'\n", "ORDER BY place DESC ;"]}, {"cell_type": "markdown", "metadata": {}, "source": ["Par d\u00e9faut la commande [%%SQL](http://www.xavierdupre.fr/app/pyensae/helpsphinx/i_nb.html#sql) n'affiche que les dix premi\u00e8res lignes."]}, {"cell_type": "code", "execution_count": 14, "metadata": {}, "outputs": [{"name": "stdout", "output_type": "stream", "text": ["usage: SQL [-h] [--df DF] [-n N] [-q QUERY] [-v VARIABLE]\n", "\n", "query the database\n", "\n", "optional arguments:\n", "  -h, --help            show this help message and exit\n", "  --df DF               output dataframe\n", "  -n N, --n N           number of first lines to display\n", "  -q QUERY, --query QUERY\n", "                        when used in a single line (no cell), query is the SQL\n", "                        query, the command returns the full dataframe\n", "  -v VARIABLE, --variable VARIABLE\n", "                        variable name used to store the database object\n", "usage: SQL [-h] [--df DF] [-n N] [-q QUERY] [-v VARIABLE]\n", "\n"]}], "source": ["%%SQL --help\n", "\n", "."]}, {"cell_type": "markdown", "metadata": {}, "source": ["On affiche 5 lignes et on stocke le r\u00e9sultat dans un dataframe."]}, {"cell_type": "code", "execution_count": 15, "metadata": {}, "outputs": [{"data": {"text/html": ["<div>\n", "<style scoped>\n", "    .dataframe tbody tr th:only-of-type {\n", "        vertical-align: middle;\n", "    }\n", "\n", "    .dataframe tbody tr th {\n", "        vertical-align: top;\n", "    }\n", "\n", "    .dataframe thead th {\n", "        text-align: right;\n", "    }\n", "</style>\n", "<table border=\"1\" class=\"dataframe\">\n", "  <thead>\n", "    <tr style=\"text-align: right;\">\n", "      <th></th>\n", "      <th>last_update</th>\n", "      <th>place</th>\n", "      <th>number</th>\n", "    </tr>\n", "  </thead>\n", "  <tbody>\n", "    <tr>\n", "      <th>0</th>\n", "      <td>2013-09-13 10:00:19</td>\n", "      <td>70</td>\n", "      <td>16004</td>\n", "    </tr>\n", "    <tr>\n", "      <th>1</th>\n", "      <td>2013-09-13 10:05:19</td>\n", "      <td>70</td>\n", "      <td>16004</td>\n", "    </tr>\n", "    <tr>\n", "      <th>2</th>\n", "      <td>2013-09-13 10:10:19</td>\n", "      <td>70</td>\n", "      <td>16004</td>\n", "    </tr>\n", "    <tr>\n", "      <th>3</th>\n", "      <td>2013-09-13 10:15:19</td>\n", "      <td>70</td>\n", "      <td>16004</td>\n", "    </tr>\n", "    <tr>\n", "      <th>4</th>\n", "      <td>2013-09-13 10:20:19</td>\n", "      <td>70</td>\n", "      <td>16004</td>\n", "    </tr>\n", "  </tbody>\n", "</table>\n", "</div>"], "text/plain": ["           last_update  place  number\n", "0  2013-09-13 10:00:19     70   16004\n", "1  2013-09-13 10:05:19     70   16004\n", "2  2013-09-13 10:10:19     70   16004\n", "3  2013-09-13 10:15:19     70   16004\n", "4  2013-09-13 10:20:19     70   16004"]}, "execution_count": 16, "metadata": {}, "output_type": "execute_result"}], "source": ["%%SQL -n 5 --df=df\n", "SELECT last_update, available_bike_stands + available_bikes AS place, number FROM td8_velib \n", "WHERE last_update >= '2013-09-13 10:00:00' AND last_update <= '2013-09-13 11:00:00'\n", "ORDER BY place DESC ;"]}, {"cell_type": "code", "execution_count": 16, "metadata": {}, "outputs": [{"data": {"text/html": ["<div>\n", "<style scoped>\n", "    .dataframe tbody tr th:only-of-type {\n", "        vertical-align: middle;\n", "    }\n", "\n", "    .dataframe tbody tr th {\n", "        vertical-align: top;\n", "    }\n", "\n", "    .dataframe thead th {\n", "        text-align: right;\n", "    }\n", "</style>\n", "<table border=\"1\" class=\"dataframe\">\n", "  <thead>\n", "    <tr style=\"text-align: right;\">\n", "      <th></th>\n", "      <th>last_update</th>\n", "      <th>place</th>\n", "      <th>number</th>\n", "    </tr>\n", "  </thead>\n", "  <tbody>\n", "    <tr>\n", "      <th>14755</th>\n", "      <td>2013-09-13 10:55:19</td>\n", "      <td>0</td>\n", "      <td>7025</td>\n", "    </tr>\n", "    <tr>\n", "      <th>14756</th>\n", "      <td>2013-09-13 10:55:19</td>\n", "      <td>0</td>\n", "      <td>32004</td>\n", "    </tr>\n", "    <tr>\n", "      <th>14757</th>\n", "      <td>2013-09-13 10:55:19</td>\n", "      <td>0</td>\n", "      <td>32006</td>\n", "    </tr>\n", "    <tr>\n", "      <th>14758</th>\n", "      <td>2013-09-13 10:55:19</td>\n", "      <td>0</td>\n", "      <td>20122</td>\n", "    </tr>\n", "    <tr>\n", "      <th>14759</th>\n", "      <td>2013-09-13 10:55:19</td>\n", "      <td>0</td>\n", "      <td>16135</td>\n", "    </tr>\n", "  </tbody>\n", "</table>\n", "</div>"], "text/plain": ["               last_update  place  number\n", "14755  2013-09-13 10:55:19      0    7025\n", "14756  2013-09-13 10:55:19      0   32004\n", "14757  2013-09-13 10:55:19      0   32006\n", "14758  2013-09-13 10:55:19      0   20122\n", "14759  2013-09-13 10:55:19      0   16135"]}, "execution_count": 17, "metadata": {}, "output_type": "execute_result"}], "source": ["df.tail()"]}, {"cell_type": "markdown", "metadata": {}, "source": ["Maximum de v\u00e9los disponibles \u00e0 une station."]}, {"cell_type": "code", "execution_count": 17, "metadata": {}, "outputs": [{"data": {"text/html": ["<div>\n", "<style scoped>\n", "    .dataframe tbody tr th:only-of-type {\n", "        vertical-align: middle;\n", "    }\n", "\n", "    .dataframe tbody tr th {\n", "        vertical-align: top;\n", "    }\n", "\n", "    .dataframe thead th {\n", "        text-align: right;\n", "    }\n", "</style>\n", "<table border=\"1\" class=\"dataframe\">\n", "  <thead>\n", "    <tr style=\"text-align: right;\">\n", "      <th></th>\n", "      <th>MAX(available_bike_stands)</th>\n", "    </tr>\n", "  </thead>\n", "  <tbody>\n", "    <tr>\n", "      <th>0</th>\n", "      <td>70</td>\n", "    </tr>\n", "  </tbody>\n", "</table>\n", "</div>"], "text/plain": ["   MAX(available_bike_stands)\n", "0                          70"]}, "execution_count": 18, "metadata": {}, "output_type": "execute_result"}], "source": ["%%SQL\n", "SELECT MAX(available_bike_stands) FROM td8_velib"]}, {"cell_type": "markdown", "metadata": {}, "source": ["Et le minimum."]}, {"cell_type": "code", "execution_count": 18, "metadata": {}, "outputs": [{"data": {"text/html": ["<div>\n", "<style scoped>\n", "    .dataframe tbody tr th:only-of-type {\n", "        vertical-align: middle;\n", "    }\n", "\n", "    .dataframe tbody tr th {\n", "        vertical-align: top;\n", "    }\n", "\n", "    .dataframe thead th {\n", "        text-align: right;\n", "    }\n", "</style>\n", "<table border=\"1\" class=\"dataframe\">\n", "  <thead>\n", "    <tr style=\"text-align: right;\">\n", "      <th></th>\n", "      <th>label</th>\n", "      <th>MIN(available_bike_stands)</th>\n", "    </tr>\n", "  </thead>\n", "  <tbody>\n", "    <tr>\n", "      <th>0</th>\n", "      <td>min</td>\n", "      <td>0</td>\n", "    </tr>\n", "    <tr>\n", "      <th>1</th>\n", "      <td>max</td>\n", "      <td>70</td>\n", "    </tr>\n", "  </tbody>\n", "</table>\n", "</div>"], "text/plain": ["  label  MIN(available_bike_stands)\n", "0   min                           0\n", "1   max                          70"]}, "execution_count": 19, "metadata": {}, "output_type": "execute_result"}], "source": ["%%SQL\n", "SELECT \"min\" AS label, MIN(available_bike_stands) FROM td8_velib \n", "UNION ALL \n", "SELECT \"max\" AS label, MAX(available_bike_stands) FROM td8_velib "]}, {"cell_type": "markdown", "metadata": {}, "source": ["Tous les num\u00e9ros de stations de fa\u00e7on unique."]}, {"cell_type": "code", "execution_count": 19, "metadata": {}, "outputs": [{"data": {"text/html": ["<div>\n", "<style scoped>\n", "    .dataframe tbody tr th:only-of-type {\n", "        vertical-align: middle;\n", "    }\n", "\n", "    .dataframe tbody tr th {\n", "        vertical-align: top;\n", "    }\n", "\n", "    .dataframe thead th {\n", "        text-align: right;\n", "    }\n", "</style>\n", "<table border=\"1\" class=\"dataframe\">\n", "  <thead>\n", "    <tr style=\"text-align: right;\">\n", "      <th></th>\n", "      <th>number</th>\n", "    </tr>\n", "  </thead>\n", "  <tbody>\n", "    <tr>\n", "      <th>0</th>\n", "      <td>15025</td>\n", "    </tr>\n", "    <tr>\n", "      <th>1</th>\n", "      <td>17014</td>\n", "    </tr>\n", "    <tr>\n", "      <th>2</th>\n", "      <td>15108</td>\n", "    </tr>\n", "    <tr>\n", "      <th>3</th>\n", "      <td>20122</td>\n", "    </tr>\n", "    <tr>\n", "      <th>4</th>\n", "      <td>34010</td>\n", "    </tr>\n", "    <tr>\n", "      <th>5</th>\n", "      <td>43003</td>\n", "    </tr>\n", "    <tr>\n", "      <th>6</th>\n", "      <td>20023</td>\n", "    </tr>\n", "    <tr>\n", "      <th>7</th>\n", "      <td>12151</td>\n", "    </tr>\n", "    <tr>\n", "      <th>8</th>\n", "      <td>18041</td>\n", "    </tr>\n", "    <tr>\n", "      <th>9</th>\n", "      <td>43008</td>\n", "    </tr>\n", "  </tbody>\n", "</table>\n", "</div>"], "text/plain": ["   number\n", "0   15025\n", "1   17014\n", "2   15108\n", "3   20122\n", "4   34010\n", "5   43003\n", "6   20023\n", "7   12151\n", "8   18041\n", "9   43008"]}, "execution_count": 20, "metadata": {}, "output_type": "execute_result"}], "source": ["%%SQL\n", "SELECT DISTINCT number FROM td8_velib"]}, {"cell_type": "markdown", "metadata": {}, "source": ["Compter le nombre de stations (1230)."]}, {"cell_type": "code", "execution_count": 20, "metadata": {}, "outputs": [{"data": {"text/html": ["<div>\n", "<style scoped>\n", "    .dataframe tbody tr th:only-of-type {\n", "        vertical-align: middle;\n", "    }\n", "\n", "    .dataframe tbody tr th {\n", "        vertical-align: top;\n", "    }\n", "\n", "    .dataframe thead th {\n", "        text-align: right;\n", "    }\n", "</style>\n", "<table border=\"1\" class=\"dataframe\">\n", "  <thead>\n", "    <tr style=\"text-align: right;\">\n", "      <th></th>\n", "      <th>COUNT(*)</th>\n", "    </tr>\n", "  </thead>\n", "  <tbody>\n", "    <tr>\n", "      <th>0</th>\n", "      <td>1230</td>\n", "    </tr>\n", "  </tbody>\n", "</table>\n", "</div>"], "text/plain": ["   COUNT(*)\n", "0      1230"]}, "execution_count": 21, "metadata": {}, "output_type": "execute_result"}], "source": ["%%SQL\n", "SELECT COUNT(*) FROM (\n", "    SELECT DISTINCT number FROM td8_velib\n", ")"]}, {"cell_type": "markdown", "metadata": {}, "source": ["## Exercice 1\n", "\n", "* D\u00e9terminer le nombre de valeur distinctes pour la colonne ``last_update``.\n", "* D\u00e9terminer la premi\u00e8re et derni\u00e8re date."]}, {"cell_type": "markdown", "metadata": {}, "source": ["## GROUP BY\n", "\n", "L'instruction ``GROUP BY`` permet d'aggr\u00e9ger des valeurs (min, max, sum) sur un ensemble de ligne partageant le m\u00eame ensemble de valeurs (ou cl\u00e9)."]}, {"cell_type": "code", "execution_count": 21, "metadata": {}, "outputs": [{"data": {"text/html": ["<div>\n", "<style scoped>\n", "    .dataframe tbody tr th:only-of-type {\n", "        vertical-align: middle;\n", "    }\n", "\n", "    .dataframe tbody tr th {\n", "        vertical-align: top;\n", "    }\n", "\n", "    .dataframe thead th {\n", "        text-align: right;\n", "    }\n", "</style>\n", "<table border=\"1\" class=\"dataframe\">\n", "  <thead>\n", "    <tr style=\"text-align: right;\">\n", "      <th></th>\n", "      <th>last_update</th>\n", "      <th>velo_disponible</th>\n", "    </tr>\n", "  </thead>\n", "  <tbody>\n", "    <tr>\n", "      <th>0</th>\n", "      <td>2013-07-22 09:00:19</td>\n", "      <td>0</td>\n", "    </tr>\n", "    <tr>\n", "      <th>1</th>\n", "      <td>2013-07-22 09:05:19</td>\n", "      <td>0</td>\n", "    </tr>\n", "    <tr>\n", "      <th>2</th>\n", "      <td>2013-07-22 09:10:19</td>\n", "      <td>0</td>\n", "    </tr>\n", "    <tr>\n", "      <th>3</th>\n", "      <td>2013-07-22 09:15:19</td>\n", "      <td>0</td>\n", "    </tr>\n", "    <tr>\n", "      <th>4</th>\n", "      <td>2013-07-22 09:20:19</td>\n", "      <td>0</td>\n", "    </tr>\n", "    <tr>\n", "      <th>5</th>\n", "      <td>2013-07-22 09:25:19</td>\n", "      <td>0</td>\n", "    </tr>\n", "    <tr>\n", "      <th>6</th>\n", "      <td>2013-07-22 09:30:19</td>\n", "      <td>0</td>\n", "    </tr>\n", "    <tr>\n", "      <th>7</th>\n", "      <td>2013-07-22 09:35:19</td>\n", "      <td>0</td>\n", "    </tr>\n", "    <tr>\n", "      <th>8</th>\n", "      <td>2013-07-22 09:40:19</td>\n", "      <td>0</td>\n", "    </tr>\n", "    <tr>\n", "      <th>9</th>\n", "      <td>2013-07-22 09:45:19</td>\n", "      <td>0</td>\n", "    </tr>\n", "  </tbody>\n", "</table>\n", "</div>"], "text/plain": ["           last_update  velo_disponible\n", "0  2013-07-22 09:00:19                0\n", "1  2013-07-22 09:05:19                0\n", "2  2013-07-22 09:10:19                0\n", "3  2013-07-22 09:15:19                0\n", "4  2013-07-22 09:20:19                0\n", "5  2013-07-22 09:25:19                0\n", "6  2013-07-22 09:30:19                0\n", "7  2013-07-22 09:35:19                0\n", "8  2013-07-22 09:40:19                0\n", "9  2013-07-22 09:45:19                0"]}, "execution_count": 22, "metadata": {}, "output_type": "execute_result"}], "source": ["%%SQL --df=df\n", "SELECT last_update, SUM(available_bikes) AS velo_disponible\n", "FROM td8_velib\n", "GROUP BY last_update\n", "ORDER BY last_update"]}, {"cell_type": "markdown", "metadata": {}, "source": ["Le r\u00e9sultat est un tableau avec de petites valeurs au d\u00e9but et de grandes vers la fin. Cela est d\u00fb au processus de cr\u00e9ation de la base de donn\u00e9es. Certaines stations sont hors service et la derni\u00e8re arriv\u00e9e ou le dernier d\u00e9part remonte \u00e0 plusieurs jours. A chaque fois qu'on r\u00e9cup\u00e8re les donn\u00e9es velib, on dispose pour chaque station de la derni\u00e8re arriv\u00e9e ou du dernier d\u00e9part de v\u00e9lo. Le champ *last_update* correspond \u00e0 cette date. Il ne faudra consid\u00e9rer que les dates au-del\u00e0 de ``2013-09-10 11:30:19``."]}, {"cell_type": "code", "execution_count": 22, "metadata": {}, "outputs": [{"data": {"text/html": ["<div>\n", "<style scoped>\n", "    .dataframe tbody tr th:only-of-type {\n", "        vertical-align: middle;\n", "    }\n", "\n", "    .dataframe tbody tr th {\n", "        vertical-align: top;\n", "    }\n", "\n", "    .dataframe thead th {\n", "        text-align: right;\n", "    }\n", "</style>\n", "<table border=\"1\" class=\"dataframe\">\n", "  <thead>\n", "    <tr style=\"text-align: right;\">\n", "      <th></th>\n", "      <th>last_update</th>\n", "      <th>velo_disponible</th>\n", "    </tr>\n", "  </thead>\n", "  <tbody>\n", "    <tr>\n", "      <th>15289</th>\n", "      <td>2013-09-13 11:05:19</td>\n", "      <td>13498</td>\n", "    </tr>\n", "    <tr>\n", "      <th>15290</th>\n", "      <td>2013-09-13 11:10:19</td>\n", "      <td>13524</td>\n", "    </tr>\n", "    <tr>\n", "      <th>15291</th>\n", "      <td>2013-09-13 11:15:19</td>\n", "      <td>13519</td>\n", "    </tr>\n", "    <tr>\n", "      <th>15292</th>\n", "      <td>2013-09-13 11:20:19</td>\n", "      <td>13546</td>\n", "    </tr>\n", "    <tr>\n", "      <th>15293</th>\n", "      <td>2013-09-13 11:25:19</td>\n", "      <td>13560</td>\n", "    </tr>\n", "  </tbody>\n", "</table>\n", "</div>"], "text/plain": ["               last_update  velo_disponible\n", "15289  2013-09-13 11:05:19            13498\n", "15290  2013-09-13 11:10:19            13524\n", "15291  2013-09-13 11:15:19            13519\n", "15292  2013-09-13 11:20:19            13546\n", "15293  2013-09-13 11:25:19            13560"]}, "execution_count": 23, "metadata": {}, "output_type": "execute_result"}], "source": ["df.tail()"]}, {"cell_type": "markdown", "metadata": {}, "source": ["## Exercice 1b\n", "\n", "Que fait la requ\u00eate suivante ? Que se passe-t-il si vous enlevez les symboles ``--`` (on *d\u00e9commente* la condition ``WHERE``) ?"]}, {"cell_type": "code", "execution_count": 23, "metadata": {}, "outputs": [{"data": {"text/html": ["<div>\n", "<style scoped>\n", "    .dataframe tbody tr th:only-of-type {\n", "        vertical-align: middle;\n", "    }\n", "\n", "    .dataframe tbody tr th {\n", "        vertical-align: top;\n", "    }\n", "\n", "    .dataframe thead th {\n", "        text-align: right;\n", "    }\n", "</style>\n", "<table border=\"1\" class=\"dataframe\">\n", "  <thead>\n", "    <tr style=\"text-align: right;\">\n", "      <th></th>\n", "      <th>last_update</th>\n", "      <th>velo_disponible</th>\n", "      <th>stations</th>\n", "    </tr>\n", "  </thead>\n", "  <tbody>\n", "    <tr>\n", "      <th>0</th>\n", "      <td>2013-07-22 09:00:19</td>\n", "      <td>0</td>\n", "      <td>1</td>\n", "    </tr>\n", "    <tr>\n", "      <th>1</th>\n", "      <td>2013-07-22 09:05:19</td>\n", "      <td>0</td>\n", "      <td>1</td>\n", "    </tr>\n", "    <tr>\n", "      <th>2</th>\n", "      <td>2013-07-22 09:10:19</td>\n", "      <td>0</td>\n", "      <td>1</td>\n", "    </tr>\n", "    <tr>\n", "      <th>3</th>\n", "      <td>2013-07-22 09:15:19</td>\n", "      <td>0</td>\n", "      <td>1</td>\n", "    </tr>\n", "    <tr>\n", "      <th>4</th>\n", "      <td>2013-07-22 09:20:19</td>\n", "      <td>0</td>\n", "      <td>1</td>\n", "    </tr>\n", "    <tr>\n", "      <th>5</th>\n", "      <td>2013-07-22 09:25:19</td>\n", "      <td>0</td>\n", "      <td>1</td>\n", "    </tr>\n", "    <tr>\n", "      <th>6</th>\n", "      <td>2013-07-22 09:30:19</td>\n", "      <td>0</td>\n", "      <td>1</td>\n", "    </tr>\n", "    <tr>\n", "      <th>7</th>\n", "      <td>2013-07-22 09:35:19</td>\n", "      <td>0</td>\n", "      <td>1</td>\n", "    </tr>\n", "    <tr>\n", "      <th>8</th>\n", "      <td>2013-07-22 09:40:19</td>\n", "      <td>0</td>\n", "      <td>1</td>\n", "    </tr>\n", "    <tr>\n", "      <th>9</th>\n", "      <td>2013-07-22 09:45:19</td>\n", "      <td>0</td>\n", "      <td>1</td>\n", "    </tr>\n", "  </tbody>\n", "</table>\n", "</div>"], "text/plain": ["           last_update  velo_disponible  stations\n", "0  2013-07-22 09:00:19                0         1\n", "1  2013-07-22 09:05:19                0         1\n", "2  2013-07-22 09:10:19                0         1\n", "3  2013-07-22 09:15:19                0         1\n", "4  2013-07-22 09:20:19                0         1\n", "5  2013-07-22 09:25:19                0         1\n", "6  2013-07-22 09:30:19                0         1\n", "7  2013-07-22 09:35:19                0         1\n", "8  2013-07-22 09:40:19                0         1\n", "9  2013-07-22 09:45:19                0         1"]}, "execution_count": 24, "metadata": {}, "output_type": "execute_result"}], "source": ["%%SQL --df=df\n", "SELECT last_update, SUM(available_bikes) AS velo_disponible, COUNT(DISTINCT number) AS stations\n", "FROM td8_velib\n", "--WHERE last_update >= \"2013-09-10 11:30:19\"\n", "GROUP BY last_update\n", "ORDER BY last_update"]}, {"cell_type": "markdown", "metadata": {}, "source": ["et celle-ci ?"]}, {"cell_type": "code", "execution_count": 24, "metadata": {}, "outputs": [{"data": {"text/html": ["<div>\n", "<style scoped>\n", "    .dataframe tbody tr th:only-of-type {\n", "        vertical-align: middle;\n", "    }\n", "\n", "    .dataframe tbody tr th {\n", "        vertical-align: top;\n", "    }\n", "\n", "    .dataframe thead th {\n", "        text-align: right;\n", "    }\n", "</style>\n", "<table border=\"1\" class=\"dataframe\">\n", "  <thead>\n", "    <tr style=\"text-align: right;\">\n", "      <th></th>\n", "      <th>last_update</th>\n", "      <th>vide</th>\n", "      <th>nb</th>\n", "    </tr>\n", "  </thead>\n", "  <tbody>\n", "    <tr>\n", "      <th>0</th>\n", "      <td>2013-09-10 11:30:19</td>\n", "      <td>0</td>\n", "      <td>233</td>\n", "    </tr>\n", "    <tr>\n", "      <th>1</th>\n", "      <td>2013-09-10 11:30:19</td>\n", "      <td>1</td>\n", "      <td>997</td>\n", "    </tr>\n", "    <tr>\n", "      <th>2</th>\n", "      <td>2013-09-10 11:35:19</td>\n", "      <td>0</td>\n", "      <td>232</td>\n", "    </tr>\n", "    <tr>\n", "      <th>3</th>\n", "      <td>2013-09-10 11:35:19</td>\n", "      <td>1</td>\n", "      <td>998</td>\n", "    </tr>\n", "    <tr>\n", "      <th>4</th>\n", "      <td>2013-09-10 11:40:19</td>\n", "      <td>0</td>\n", "      <td>241</td>\n", "    </tr>\n", "    <tr>\n", "      <th>5</th>\n", "      <td>2013-09-10 11:40:19</td>\n", "      <td>1</td>\n", "      <td>989</td>\n", "    </tr>\n", "    <tr>\n", "      <th>6</th>\n", "      <td>2013-09-10 11:45:19</td>\n", "      <td>0</td>\n", "      <td>244</td>\n", "    </tr>\n", "    <tr>\n", "      <th>7</th>\n", "      <td>2013-09-10 11:45:19</td>\n", "      <td>1</td>\n", "      <td>986</td>\n", "    </tr>\n", "    <tr>\n", "      <th>8</th>\n", "      <td>2013-09-10 11:50:19</td>\n", "      <td>0</td>\n", "      <td>246</td>\n", "    </tr>\n", "    <tr>\n", "      <th>9</th>\n", "      <td>2013-09-10 11:50:19</td>\n", "      <td>1</td>\n", "      <td>984</td>\n", "    </tr>\n", "  </tbody>\n", "</table>\n", "</div>"], "text/plain": ["           last_update  vide   nb\n", "0  2013-09-10 11:30:19     0  233\n", "1  2013-09-10 11:30:19     1  997\n", "2  2013-09-10 11:35:19     0  232\n", "3  2013-09-10 11:35:19     1  998\n", "4  2013-09-10 11:40:19     0  241\n", "5  2013-09-10 11:40:19     1  989\n", "6  2013-09-10 11:45:19     0  244\n", "7  2013-09-10 11:45:19     1  986\n", "8  2013-09-10 11:50:19     0  246\n", "9  2013-09-10 11:50:19     1  984"]}, "execution_count": 25, "metadata": {}, "output_type": "execute_result"}], "source": ["%%SQL --df=df\n", "SELECT last_update, \n", "       CASE  WHEN available_bikes>0 THEN 1 ELSE 0 END AS vide, \n", "       COUNT(*) AS nb \n", "FROM td8_velib\n", "WHERE last_update >= \"2013-09-10 11:30:19\"\n", "GROUP BY last_update, vide\n", "ORDER BY last_update"]}, {"cell_type": "markdown", "metadata": {}, "source": ["## Exerice 2\n", "\n", "Pour chaque station, compter le nombre de plages horaires de cinq minutes o\u00f9 il n'y a aucun v\u00e9lo disponible."]}, {"cell_type": "code", "execution_count": 25, "metadata": {}, "outputs": [], "source": []}, {"cell_type": "markdown", "metadata": {}, "source": ["## Exercice 3\n", "\n", "Si on note $X(s)$ le nombre de plages horaires de cinq minutes o\u00f9 il n'y a aucun v\u00e9lo disponible, construire le tableau suivant : $k \\rightarrow card\\{ s | X(s) = k \\}$."]}, {"cell_type": "code", "execution_count": 26, "metadata": {}, "outputs": [], "source": []}, {"cell_type": "markdown", "metadata": {}, "source": ["## JOIN\n", "\n", "L'instruction ``JOIN`` sert \u00e0 associer des lignes d'une table avec les lignes d'une autre table \u00e0 partir du moment o\u00f9 elles partagent une information commune."]}, {"cell_type": "code", "execution_count": 27, "metadata": {}, "outputs": [{"data": {"text/html": ["<div>\n", "<style scoped>\n", "    .dataframe tbody tr th:only-of-type {\n", "        vertical-align: middle;\n", "    }\n", "\n", "    .dataframe tbody tr th {\n", "        vertical-align: top;\n", "    }\n", "\n", "    .dataframe thead th {\n", "        text-align: right;\n", "    }\n", "</style>\n", "<table border=\"1\" class=\"dataframe\">\n", "  <thead>\n", "    <tr style=\"text-align: right;\">\n", "      <th></th>\n", "      <th>collect_date</th>\n", "      <th>last_update</th>\n", "      <th>available_bike_stands</th>\n", "      <th>available_bikes</th>\n", "      <th>number</th>\n", "      <th>heure</th>\n", "      <th>minute</th>\n", "      <th>name</th>\n", "    </tr>\n", "  </thead>\n", "  <tbody>\n", "    <tr>\n", "      <th>0</th>\n", "      <td>2013-09-13 11:26:37.738913</td>\n", "      <td>2013-07-22 09:00:19</td>\n", "      <td>0</td>\n", "      <td>0</td>\n", "      <td>15025</td>\n", "      <td>9</td>\n", "      <td>0</td>\n", "      <td>15025 - AMETTE</td>\n", "    </tr>\n", "    <tr>\n", "      <th>1</th>\n", "      <td>2013-09-13 11:26:37.738913</td>\n", "      <td>2013-07-22 09:05:19</td>\n", "      <td>0</td>\n", "      <td>0</td>\n", "      <td>15025</td>\n", "      <td>9</td>\n", "      <td>5</td>\n", "      <td>15025 - AMETTE</td>\n", "    </tr>\n", "    <tr>\n", "      <th>2</th>\n", "      <td>2013-09-13 11:26:37.738913</td>\n", "      <td>2013-07-22 09:10:19</td>\n", "      <td>0</td>\n", "      <td>0</td>\n", "      <td>15025</td>\n", "      <td>9</td>\n", "      <td>10</td>\n", "      <td>15025 - AMETTE</td>\n", "    </tr>\n", "    <tr>\n", "      <th>3</th>\n", "      <td>2013-09-13 11:26:37.738913</td>\n", "      <td>2013-07-22 09:15:19</td>\n", "      <td>0</td>\n", "      <td>0</td>\n", "      <td>15025</td>\n", "      <td>9</td>\n", "      <td>15</td>\n", "      <td>15025 - AMETTE</td>\n", "    </tr>\n", "    <tr>\n", "      <th>4</th>\n", "      <td>2013-09-13 11:26:37.738913</td>\n", "      <td>2013-07-22 09:20:19</td>\n", "      <td>0</td>\n", "      <td>0</td>\n", "      <td>15025</td>\n", "      <td>9</td>\n", "      <td>20</td>\n", "      <td>15025 - AMETTE</td>\n", "    </tr>\n", "    <tr>\n", "      <th>5</th>\n", "      <td>2013-09-13 11:26:37.738913</td>\n", "      <td>2013-07-22 09:25:19</td>\n", "      <td>0</td>\n", "      <td>0</td>\n", "      <td>15025</td>\n", "      <td>9</td>\n", "      <td>25</td>\n", "      <td>15025 - AMETTE</td>\n", "    </tr>\n", "    <tr>\n", "      <th>6</th>\n", "      <td>2013-09-13 11:26:37.738913</td>\n", "      <td>2013-07-22 09:30:19</td>\n", "      <td>0</td>\n", "      <td>0</td>\n", "      <td>15025</td>\n", "      <td>9</td>\n", "      <td>30</td>\n", "      <td>15025 - AMETTE</td>\n", "    </tr>\n", "    <tr>\n", "      <th>7</th>\n", "      <td>2013-09-13 11:26:37.738913</td>\n", "      <td>2013-07-22 09:35:19</td>\n", "      <td>0</td>\n", "      <td>0</td>\n", "      <td>15025</td>\n", "      <td>9</td>\n", "      <td>35</td>\n", "      <td>15025 - AMETTE</td>\n", "    </tr>\n", "    <tr>\n", "      <th>8</th>\n", "      <td>2013-09-13 11:26:37.738913</td>\n", "      <td>2013-07-22 09:40:19</td>\n", "      <td>0</td>\n", "      <td>0</td>\n", "      <td>15025</td>\n", "      <td>9</td>\n", "      <td>40</td>\n", "      <td>15025 - AMETTE</td>\n", "    </tr>\n", "    <tr>\n", "      <th>9</th>\n", "      <td>2013-09-13 11:26:37.738913</td>\n", "      <td>2013-07-22 09:45:19</td>\n", "      <td>0</td>\n", "      <td>0</td>\n", "      <td>15025</td>\n", "      <td>9</td>\n", "      <td>45</td>\n", "      <td>15025 - AMETTE</td>\n", "    </tr>\n", "  </tbody>\n", "</table>\n", "</div>"], "text/plain": ["                 collect_date          last_update  available_bike_stands  \\\n", "0  2013-09-13 11:26:37.738913  2013-07-22 09:00:19                      0   \n", "1  2013-09-13 11:26:37.738913  2013-07-22 09:05:19                      0   \n", "2  2013-09-13 11:26:37.738913  2013-07-22 09:10:19                      0   \n", "3  2013-09-13 11:26:37.738913  2013-07-22 09:15:19                      0   \n", "4  2013-09-13 11:26:37.738913  2013-07-22 09:20:19                      0   \n", "5  2013-09-13 11:26:37.738913  2013-07-22 09:25:19                      0   \n", "6  2013-09-13 11:26:37.738913  2013-07-22 09:30:19                      0   \n", "7  2013-09-13 11:26:37.738913  2013-07-22 09:35:19                      0   \n", "8  2013-09-13 11:26:37.738913  2013-07-22 09:40:19                      0   \n", "9  2013-09-13 11:26:37.738913  2013-07-22 09:45:19                      0   \n", "\n", "   available_bikes  number  heure  minute            name  \n", "0                0   15025      9       0  15025 - AMETTE  \n", "1                0   15025      9       5  15025 - AMETTE  \n", "2                0   15025      9      10  15025 - AMETTE  \n", "3                0   15025      9      15  15025 - AMETTE  \n", "4                0   15025      9      20  15025 - AMETTE  \n", "5                0   15025      9      25  15025 - AMETTE  \n", "6                0   15025      9      30  15025 - AMETTE  \n", "7                0   15025      9      35  15025 - AMETTE  \n", "8                0   15025      9      40  15025 - AMETTE  \n", "9                0   15025      9      45  15025 - AMETTE  "]}, "execution_count": 28, "metadata": {}, "output_type": "execute_result"}], "source": ["%%SQL\n", "SELECT A.*, B.name  -- ajout du nom au bout de chaque ligne\n", "FROM td8_velib AS A\n", "JOIN stations AS B\n", "ON A.number == B.number"]}, {"cell_type": "markdown", "metadata": {}, "source": ["On peut s'en servir pour calculer un ratio en associant les deux instructions ``GROUP BY`` et ``JOIN``. L'instruction suivante permet d'obtenir la distribution des v\u00e9los disponibles sur la p\u00e9riode d'\u00e9tude pour chaque station."]}, {"cell_type": "code", "execution_count": 28, "metadata": {}, "outputs": [{"data": {"text/html": ["<div>\n", "<style scoped>\n", "    .dataframe tbody tr th:only-of-type {\n", "        vertical-align: middle;\n", "    }\n", "\n", "    .dataframe tbody tr th {\n", "        vertical-align: top;\n", "    }\n", "\n", "    .dataframe thead th {\n", "        text-align: right;\n", "    }\n", "</style>\n", "<table border=\"1\" class=\"dataframe\">\n", "  <thead>\n", "    <tr style=\"text-align: right;\">\n", "      <th></th>\n", "      <th>collect_date</th>\n", "      <th>last_update</th>\n", "      <th>available_bike_stands</th>\n", "      <th>available_bikes</th>\n", "      <th>number</th>\n", "      <th>heure</th>\n", "      <th>minute</th>\n", "      <th>distribution_temporelle</th>\n", "    </tr>\n", "  </thead>\n", "  <tbody>\n", "    <tr>\n", "      <th>0</th>\n", "      <td>2013-09-10 11:27:43.394054</td>\n", "      <td>2013-09-10 11:30:19</td>\n", "      <td>24</td>\n", "      <td>3</td>\n", "      <td>19115</td>\n", "      <td>11</td>\n", "      <td>30</td>\n", "      <td>0.000289</td>\n", "    </tr>\n", "    <tr>\n", "      <th>1</th>\n", "      <td>2013-09-10 11:28:44.115980</td>\n", "      <td>2013-09-10 11:30:19</td>\n", "      <td>16</td>\n", "      <td>2</td>\n", "      <td>4101</td>\n", "      <td>11</td>\n", "      <td>30</td>\n", "      <td>0.000288</td>\n", "    </tr>\n", "    <tr>\n", "      <th>2</th>\n", "      <td>2013-09-10 11:30:43.257442</td>\n", "      <td>2013-09-10 11:30:19</td>\n", "      <td>16</td>\n", "      <td>8</td>\n", "      <td>4103</td>\n", "      <td>11</td>\n", "      <td>30</td>\n", "      <td>0.002110</td>\n", "    </tr>\n", "    <tr>\n", "      <th>3</th>\n", "      <td>2013-09-10 11:27:43.400056</td>\n", "      <td>2013-09-10 11:30:19</td>\n", "      <td>13</td>\n", "      <td>1</td>\n", "      <td>4104</td>\n", "      <td>11</td>\n", "      <td>30</td>\n", "      <td>0.000194</td>\n", "    </tr>\n", "    <tr>\n", "      <th>4</th>\n", "      <td>2013-09-10 11:30:43.262502</td>\n", "      <td>2013-09-10 11:30:19</td>\n", "      <td>28</td>\n", "      <td>4</td>\n", "      <td>15020</td>\n", "      <td>11</td>\n", "      <td>30</td>\n", "      <td>0.000311</td>\n", "    </tr>\n", "    <tr>\n", "      <th>5</th>\n", "      <td>2013-09-10 11:27:43.391054</td>\n", "      <td>2013-09-10 11:30:19</td>\n", "      <td>12</td>\n", "      <td>6</td>\n", "      <td>4107</td>\n", "      <td>11</td>\n", "      <td>30</td>\n", "      <td>0.000798</td>\n", "    </tr>\n", "    <tr>\n", "      <th>6</th>\n", "      <td>2013-09-10 11:27:43.396054</td>\n", "      <td>2013-09-10 11:30:19</td>\n", "      <td>63</td>\n", "      <td>0</td>\n", "      <td>15021</td>\n", "      <td>11</td>\n", "      <td>30</td>\n", "      <td>0.000000</td>\n", "    </tr>\n", "    <tr>\n", "      <th>7</th>\n", "      <td>2013-09-10 11:27:43.393084</td>\n", "      <td>2013-09-10 11:30:19</td>\n", "      <td>19</td>\n", "      <td>0</td>\n", "      <td>19118</td>\n", "      <td>11</td>\n", "      <td>30</td>\n", "      <td>0.000000</td>\n", "    </tr>\n", "    <tr>\n", "      <th>8</th>\n", "      <td>2013-09-10 11:29:43.672735</td>\n", "      <td>2013-09-10 11:30:19</td>\n", "      <td>21</td>\n", "      <td>0</td>\n", "      <td>20503</td>\n", "      <td>11</td>\n", "      <td>30</td>\n", "      <td>0.000000</td>\n", "    </tr>\n", "    <tr>\n", "      <th>9</th>\n", "      <td>2013-09-10 11:30:43.264442</td>\n", "      <td>2013-09-10 11:30:19</td>\n", "      <td>31</td>\n", "      <td>2</td>\n", "      <td>15023</td>\n", "      <td>11</td>\n", "      <td>30</td>\n", "      <td>0.000555</td>\n", "    </tr>\n", "  </tbody>\n", "</table>\n", "</div>"], "text/plain": ["                 collect_date          last_update  available_bike_stands  \\\n", "0  2013-09-10 11:27:43.394054  2013-09-10 11:30:19                     24   \n", "1  2013-09-10 11:28:44.115980  2013-09-10 11:30:19                     16   \n", "2  2013-09-10 11:30:43.257442  2013-09-10 11:30:19                     16   \n", "3  2013-09-10 11:27:43.400056  2013-09-10 11:30:19                     13   \n", "4  2013-09-10 11:30:43.262502  2013-09-10 11:30:19                     28   \n", "5  2013-09-10 11:27:43.391054  2013-09-10 11:30:19                     12   \n", "6  2013-09-10 11:27:43.396054  2013-09-10 11:30:19                     63   \n", "7  2013-09-10 11:27:43.393084  2013-09-10 11:30:19                     19   \n", "8  2013-09-10 11:29:43.672735  2013-09-10 11:30:19                     21   \n", "9  2013-09-10 11:30:43.264442  2013-09-10 11:30:19                     31   \n", "\n", "   available_bikes  number  heure  minute  distribution_temporelle  \n", "0                3   19115     11      30                 0.000289  \n", "1                2    4101     11      30                 0.000288  \n", "2                8    4103     11      30                 0.002110  \n", "3                1    4104     11      30                 0.000194  \n", "4                4   15020     11      30                 0.000311  \n", "5                6    4107     11      30                 0.000798  \n", "6                0   15021     11      30                 0.000000  \n", "7                0   19118     11      30                 0.000000  \n", "8                0   20503     11      30                 0.000000  \n", "9                2   15023     11      30                 0.000555  "]}, "execution_count": 29, "metadata": {}, "output_type": "execute_result"}], "source": ["%%SQL\n", "SELECT A.*, 1.0 * A.available_bikes / B.nb_velo  AS distribution_temporelle\n", "FROM td8_velib AS A\n", "JOIN (\n", "    SELECT number, SUM(available_bikes) AS nb_velo\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.last_update >= \"2013-09-10 11:30:19\""]}, {"cell_type": "markdown", "metadata": {}, "source": ["## Exercice 4 : distribution horaire\n", "\n", "Pour chaque station, d\u00e9terminer la distribution du nombre de v\u00e9los disponibles pour chaque p\u00e9riode horaire d'une journ\u00e9e (par station, il y aura donc 24 * 12 valeurs comprises entre 0 et 1). Le r\u00e9sultat que vous devriez obtenir est illustr\u00e9 par l'image qui suit."]}, {"cell_type": "code", "execution_count": 29, "metadata": {}, "outputs": [{"data": {"image/png": "iVBORw0KGgoAAAANSUhEUgAAAtkAAAGACAIAAAD3RulrAAAAAXNSR0IArs4c6QAAAARnQU1BAACxjwv8YQUAAAAJcEhZcwAADsMAAA7DAcdvqGQAAIeVSURBVHhe7b1/bBxHlufZuJ3F9h+Dw+yh727udg87wC1wc8DgMIcVxrvbDWOENW56rweN6b31rD3ecbvHY5xGnvZ5PePp3ra61bZbLbtlWW3LcsuWrN+iSJMSKVIiZYr6SUqi9YOySImkKEqiJEqiJP4mi8UfRepe8QVfBSN/VGZVVlVW1feDAJH5IjKZWZUV8c2IFy++9ngxly9fVlsAAOAM1RVzc3NqZwFUIAAAj+jVxde++OIL3iIr0d/fz7sAAOBCb28vVxo6qEAAAB65f/8+1xu0vUiL8AYAAAAAQBaAFgEAAABALoEWAQAAAEAugRYBAAAAQC6BFgEAAABALoEWAQAAAEAugRYBAAAAQC6BFgEAAABALklFizz77LMrVqxQO3ZUVVVRGWbt2rVk6erq4t3ly5dzGYZ2qbDaAQAAAEDx4U+LNDY2sqRw0SJbt26lAmpnAVIkZKcNOpA32MhKBQAAAABFiz8tQiKDlAQJCBctQmVIsqidBZYvX85GOpy1SFVVldFHAgAAAIAixJ8WYVy0CI/OqB0N6Q6hDSrDQzb0l3MBAAAAULQErEVIcCyfh6QGw4LD8Behv3ATAQAAAAARvBYR/UFQSRYfOmQkDHXii/7+/qsAAAAACCu+1uvOSL+I2lnwddXHYsRNhE4ioza8AQAAAIAiJGAtwuJD7VjcR7gvhKUJKRLDmxUAAAAARUgwWoQUhugJfZsEBxXmbYJ2xU1EukNoA74jAAAAQNHiT4uQztCR4RjaFj0hjiCELllIlOi6JB1/EQAAAAAUDKn0ixiICwgAAAAAgF8C0CIkRHTvVAAAAAAA7wSgRQAAAAAAUgZaBAAAAAC5BFoEAAAAALkEWgQAAAAAuQRaBAAAAAC5BFoEAAAAALkEWgQAAAAAuQRaBAAAAAC5BFoEAAAAALkEWgQAAAAAuQRaBAAAAAC5BFoEAAAAALkEWgQAAAAAuQRaBAAAAAC5BFoEAAAAALkEWgQAAAAAuQRaBAAAAAC5BFoEgAJk8vy+oXefGvjp/zm4eunk2XJlDRNyhWG+SABAdoAWAaAAGVz1JDfz8Zb+zSeUNUzoVxjaiwQAZAdoEQAKjbmpiN7MUyKLygsH1iukFLaLBABkDWgREKerq+vZBZYvX66s8yirZztDRls7yAKxgdtGMz87dFflhQO5wuENzwyt/U44LxIAkDWgRYASIlVVVby7Yh7eJvvWrVt5m7RFUjshsgZaJFdM3zjHrbuksDXzcoWjO14e2fyDcF4kACBrQIuAx42NjSQdSEPwLokM1hakTsjORoJ3qZiTnXdJgqxdu5ZOAi2SK6a+Ositu6SZnosqLxzIFY7vWzm65aVwXiQAIGtAixQjJDVIPTBsoQ0SELLNfSRksXZ4kHBxsqv9eaBFcki0aTu37pKmb5xTeeFArnDi6MaxktfCeZEAgKwBLRJSTnePLN919blNV4JKy3Z21rUN0JlJZ9iqBNITDAsRAlokT4kcWsetu6Sp9qMqLxzIFU6eLR/ftzKcFwkAyBrQIiHl9fJuQ0ykn17c1kFnNoZUGDaSyKC/kgstkqeMVbzBrbvMm51s2a/ywoFcIekP0SJhu0gAQNaAFgkpdW0DL2xpN8REOun5z9pLmvv45Kw8CFIMsivqhBUJb0CL5CPigTG64+VwNvMJH5HbrRNHN4bzIgEAWQNapHhhDUFCxNAWpCpYmpCeYFHCsGShDSe7DrRIDhle911u3aXLgdp7lRcO5Apnh+6KFgnbRQIAsga0SFFDGoKUB2sLa78IixXKZTtpC8pysetAi+SQwbe/ya17tLk0nM28XCFth/YiAQBZA1qk6GCpwYieYDkisJHgPhJGFxxOdoIkiMpYQGWArCAhTYfefWqyZT9vRw68o7JDgH6FtBvOiwQAZBNoEQAKCj2kqR7GQ2WHAP0KaTecFwkAyCbQIgAUFHpIU9keq3hDZYcA/Qr13VBdJAAgm0CLAFBQ6N0MiVZ/y0sqOwQYHSHhvEgAQDZJRYs8++yz+rQLW9ifQLwX2dtRtzC0W7UQWQsAkD56SNPYo5shbOb1K6TdcF4kACCb+NMi4rGYVIuQyGB4d+38AiW0QQeKvyQZDbdHAECa6CFNZ4fu8vbwB99T2SFAv0LaDedFAgCyiT8tQiqElAQJCHctUlVVxZpDtAhtkI6hDTIStEFlJBcAEBR6SNPZsX7eHlr7HZUdAvQrpN1wXiQAIJv40yJMUi1CkoWUh65FpDuENkiF8JCNBLQAAASFHtKUdlUzPz97NiQYV0iE8CIBANkkeC1CmoNzdS1i+IvQX7iJAOCXyfP7htc/HT29W+3boYc0pV3epsS5YcC4QiKEFwkAyCYBaxG9w0PXIjp0OGGoE1/09/dfBaD46H/7W/E2++d/pPbt6H/z33C7zruPVj+l74YB4wqJEF4kACBNqKVWbbYHAtYirDN421aLiJsIFZNRG94AALgwFx3lBpuS9CgYiB+o+F7QhjpkRK2MmFvkLvQRmbBdJAAgywSsRUhncG+HjgzH6L0mVNLwZgUAuCBzXynN9FxU1sVMd57kAhxGjBhe/zRbnORLlpG74KCrTNguEgCQZYLRIqQwrHqCLEa/CO2KLpHuENqA70jOkSEzwvjWlNWznb5NlWFZqgakw3R3MzfYlKYuH1bWxURP7+YCkdo1bBFH0diDa2zJLXIXo7teUabwXSQAIMv40yKqhVlAGiHatuoJQ4tQs6S3TOn4i4Bg4e9Cl4kEb5NdVCZ9U0ntdCr5Qo3TgjSRNeQoObmvjlev4gIcuoOQZn76xjm25Ba5i/Gqt5QpfBcJAMgyqfSLGFBjAz2R13AIO5IOvEsig7UF93CwkeBdKuZkV/sa9GCIZAFpMnF8EzfYlCL1HyrrYkY2/8Bo1Mf3rTQsuUXugoOuMmG7SABAlglAi1B7Y9sOgdBCUoPUA8MW2pBeK9omeUEbZJEOD4L7OUi4ONnV/gIuGgWkgPR5UHJaRm5w9VIuMBcZYos085Mt+9mSWxI9N9r1hO0iAQBZJgAtAvILkgi2/VikGxgWIkRqWoQlCAMhEiBjJa9xg01p1G7pFtIfnDu46kllevw4UruGjSFp5uUuprtOKVP4LhIAkGWgRULKVOuhoXdV0IVAEr0xs5OBbXcFG0lkzEsIlZtmvwjbMUYTFCMbn5Nvc3jdd5VVY+Z2K+eOfPq8Mj1+PHF0IxvdI6RlDbkL3U01bBcJAMgy0CIhZfiD73HtHGAafPubfHLpumChYKgTViS8kY4WIej88CUKiqE13058lW8+oawak2fLOVd3C7X1z8ghchcyikSE7SIBAFkGWiSk0AsitTdcQQeTVi6JHFqnzj4PawgSIoa2IFXB0oSUBIsShiULbTjZrUCLBIjxhc5FR1XGArL+bbRpuzJp81YmGjYoU07hizG0VNguEgCQZaBFihrSEKQ8WFtY+0VYrFAu20lVUJaLnWBlw9uEXgykgwRUlWQNxTG66xXOmu48qUz6HNp9K5Upd1jDwjKhukgAQPaBFik6WGowIhRYjghsJLiPhNFFhpOdIGmiMiBEgkN8QSRNdzervAVkzbnYo5vKpEViDUMzL3dh+N6G6iIBANkHWgSAPGDq8mFurSUZU07mZqbYbgx/TN84x3Y9zmmukLsw5iSH6iIBANkHWgSAPCDaXMqttSTDzTN2v5Ptw+ufVqZ5nLoicoLcheG6FKqLBABkH2gRAPKASP2H3FoPb3iGN8arV6m8eRJdDqWvK9M84qKhT/TNFXIXxtzdUF0kACD7QIsAkAdIZNLxqrd4wxjOkBAdxlQUJ3fRnCB3MdV6SJnmCdVFAgCyD7QIAHmArB4nQURGNj6n8uYZ+/zHbDeaefEjCUMzL3cx03NRmeYJ1UUCALIPtAgAeUBijsyCX8jQu09x1uT5fXqIXirAdoHttuHRsozcxezQXWVaIDwXCQDIPtAiAOQBg29/k1vrx7MxCYI3NzMVz1r1JO/G08olc1MRPkQwyucQ/S6UaQG5SLUPACgmoEUAyAO4nSapQdvD65/m3diDa6Q8VNZ8bqT+Qy6vo5dXphwh16n2NYbWfodzrV0mAICCB1oEgNAzG+N2mt0pxkpf592py4djA7d5e3jDM1zWil5emXLC4rswSLiS3G5VJgBA0QAtAkDYMaaZJGbGntySiBK242UubEUvr0y5wH2yTML3tv2oMgEAigZoEQDCjrTiPHdGX71l6quDss2FrYRktRfjLgwitWs4d/JsuTIBAIoGaBEAws5M7xVupzksqUQpHfn0+WjTdt52WW1fL69MucC4C4PoyS2c63IjAIBCBVoEJFg+j9qZR61xt0BVVZXK0LKMQ/Rl9qxr4/Eav4TaBx5IDMRsW0a7c5Eh3h1cvTRyaB1vu3Qn6OWVKRcYd2GApXoBKGagRUAckQhWLaLrD4HsojPokBUrVvA2nUfOQAdSscbGRt5lqCQVILvaBx6Y7m422mlSFWwZ3fEyb7i7WUh50iXKlHWsd6HjxfEFAFCoQIuAOKQP1q5dS/LCixZhkaF2FnZJhah9DTqb3jVCuoQsxuEgKdY+g5FPn2eLhOVwn34i5XM4S8W95yP26CbnukwIAgAUKtAixciKFStIDTDKNI9HLUKqRTpCCO5TMfo/GONwFiKE8X+BO9KKy/K21JyzRZJ7WA4pT6dSpqxjvQuduego50o8WQBA8QAtElJ6enr27t27OzgqKio6OjrozCQFDMEh2GoRQfSHRy3CmkP6S+T/Qov4xeqgKp6ektjuhJS3DYaWHZK62SL0KgBFC7RISKmpqVEiIjjKysrozIZE0LFqER06ilQIbXjUImTUB2ikDLSIXxJr8B7fxJapy4fZwilpX4KUHyt9XZmyjvUuDBB6FYCiBVokpHR0dJSWlioREQQlJSUtLS18clYDhK4VCHctIrletAiVZOHC0LFyCLSIXyYaNnAjLSMssQfX2MIpqY+FlB9e/7QyZR3rXRgg9CoARQu0SPHCGoKUgdr3rEVoQxcTVm1BxXSxQtAulTGg86hs4IrV20MW2eeUdO6JlB/M3UK4SX1WEHoVgKIFWqSoIUGg92cYWoREhsgFFi68q28TRheIVYgYoF/EL9KKT3eeVCZtRIMSFVBWZ3I+AmJ7FzoIvQpA0QItUnSQbiApwOh6QpkWYLvamUfvQSEFo6wLTiQM6wwDlbcAtIhfRne9olrxG+eUiYwLkUUoeYlVKuWnu04pU3axvQsdhF4FoGiBFgEg7CQcKXouKtPjx5ED77CRkpeOBCkfbS5Vpuxiexc6MunXSzcPAKCQgBYBIOyMbP4BN9L68ApJCjZS8uJgIeVJlChTdrG9Cx2EXgWgaIEWASDs2Lp6THedYiMlLxNPpHyuWvqkDisIvQpA0QItAkDYkVZ8biqiTNoS/JScWncdKU9nUyZvTJ7fN/TuU/K/JA2uXho9vVsV8oDtXeg4hV6d+uogX4Df/wgAyBegRQAIO4Nvf5MbabW/gApUunKJ2k9GaoFNB1c9yUdZk68Zwk53oWN7hboSyuGcZABA5khFizyrhQO3ok/TkJkXPAuU0KeMErSrz84AAFhRLbFFc0RPbhlcvdT7rJOkoyRW5qYi6r87JO+nUoe4KifbK1QHLqTZsX6VAQAoFPxpEZnJ6aRFqIBkbZ2PiEUqhLZJoPD0UcqVeaRkJHgbAGDPbIzbYL9jK1ZSCGwaG7jNhxg+HL5P5e0ubE/LFkl0SSoDAFAo+NMipC1ISZCAcA9mJVB57vZYvnw5x9Siw1mLkN3oIwEAWEnZz8NKCoFNnea2+D2Vx7uwPS1bJDmFJwEA5C/+tAjjUYvwuAxLEOkOoQ1SIZzFXSYAABekFR/Z+JwypUoKgU2nvjrIhxgxP/yeyuNdWE87O9LHFkmIEA9A4ZFBLULigwQHbxv+InATAcAjM71XuA0e3fKSMqVKCoFNnRb693sqj3dhPa2IGElOy9kAAPKXTGkRFh+2goMOJwx14ov+/v6rABQHN05Wchvc9/HzypQqPbWf8KnubfsvypSM3j0/40Nu1XykTPP4PZXHu7CetruliS2Sbles5iwAQJihllq12R7IlBYhkcGDMgbiJkInkVEb25IAAGK6u5nb4PQjo6cQ2HSs4g0+xBgZ8Xsqj3dhPa04z0qK1H/IWQCAgiEjWoSECJVROxq6mwgpEsObFeQc+lJYKQrxnisNvaNLmSw9W1RGZdg9BtZ/AdwJcJWWFAKbOs2X8Xsqj3dhPa2oE0npfw4AgLARjBahVkf0hG0LxFAjJI2ZdIfQhu1QDsgmTkNmZLH9dsgu3zgdIs8DnUfOwOeUw53+BXBHWvHIoXXKlCpOgU1dGF73XT5kdnEcEb+n8ngX1tOKFpEwaKO7XuEsAEDB4E+LcFsiSKNC29zkUPvEWYKUIYGiaxS0TKGCvgX6dujrM74O+oKsWoQsZFc7C7v0hap9DTqbLlls/wVwx8l7NDX8hl51CZbq61Te78I4rWiRkY3P8Ub6PrwAgLCRSr+IATVFaF3yixUrVsRl4DzKNI9HLUKSQu8YY1nJI246thoFWsQv1HhzGzxxfJMypYFtYFMnJOiqbeeHr1N5vwvjtFPtR3lXRovSj7MCAAgbAWgRalps34lBWjzY8/j07z4+/rXA0qlvPL4T7x530Y62WkQQ/eGuRViCMNYHA1rELxMNG7gNDmQuq5P/hy1OQVcZX6fyfhfGaXVHE94YXL2USwIACoYAtAjICGd/3xQT6afG36YT23ZXMO5CgY7iUTaP/SJsp3Oq/XmgRfwibXAgWsRXvFT3yTK+TuX9LozTihaJ1K7xsroeACAfgRYJK3fWPT75dVNMpJNO/Nbj63/P55auC19CQXI9ahHCekJoEb9IKz7deVKZ0sBXvFSnoKuMr1N5vwvjtKJFJo5uTAzfYHk8AAoLaJHihTUE6RK171mL0AYdyEaClY3aWQy0SPqM7nqFG+DpINZh8RUv1d3h1NepvN+FcVp9d+TT53kby+MBUGBAixQ1Rn+GIRRIZJCFt/UBF2PwhQ7hsRuCDpFtQi/GGP8CJCXhP9FzUZnSQHe/UCZnIofWcWHbng9fp/J+F8ZpSYLwLm2MblvG24HIMgBAeIAWKTpIK5BEYHQ9oUwLsF3tzKP3oJCCUVZLOBn9VLoQcfoXwJ2RzT/gBtjLdJWkuLuAGDgFXWV8ncr7XRinFS0yeX6fDPR48VABAOQR0CIAhBpfU2eT4iteqvtMGV+n8n4Xxmn1CTgJVxIsjwdAYQEtAkCokVZ8biqiTGkggU0lDa5eGm0uVdnzTJ7fN/TuU3oZWwHh5VSC97swQq9KXwjpj8R4TRChVgAA4QFaBIBQE/hEVglsKoksczNTKpsKrHpyUYGVS1SGhaSnEnzdhV44MS7z1UESSbyN5fEAKDCgRQAIL7NDd7n1HQou2OjE8U1WDRG738m5Zm/HyiUuK8i4n0rwexeJAZ2xftEi0zfOTbUe4m12awUAFAzQIgCEl+nOk9z6evEPTYGx0tf5/NTMs8XvAryC9VSC37vQ5+7KaUmLJNxasTweAIUFtAgA4SV6eje3vpHaNcoUKPqMWbZMdzezxW97bz2V4Pcu9Lm74j9L27EH13ibjKooAKAggBYBILyMV6/i1tdLbNMUkFGPsdLX2ZII71H1Fls8Yj2V4Pcu9Lm7okVi9zszMWIFAAgD0CIAhBcJyzGdmehe1MDz+WVEZuL4JrZYuzfcsZ5K8HsX+txd0SI8l4e3sTweAAUGtAgA4YUaXW595yJDyhQoczNTqnV/8wm2JPowfMbwsJ5K8HsXieGe45uGP/geb7MWwfJ4ABQk0CIAhBRqubndHVz1pDJlAJm0wou8jJW8xrvTXae4gHeMUzEp3IU+dzcxp2Zei+hTbLgwAKAAgBYBIKTM3G7ldnfk0+eVKQMkVq2bX0F3ZONzvBt7cI0LeMc4FZPCXehzd0V8cNgSLI8HQEECLQJASJk8W87trl83Ul+Ic0a0aTvtDq35Nu+mMCpknIpJ4S70ubuiRTgLy+MBUJBAiwAQUmSZXL1pD5yEVqheRbu8bfX58IJxKiaFu9Dn7vIGJc7Sp9iwBQBQAECLABBSbIc8Amem5yL/l5FPn09z0qx+KmVK6S70y+ANiUOvT7FhCwCgAIAWASCkDK/7Lre7sUc3lSkDJHxLVy8V347Ugonpp1KmVO9CzsMboo30KTZsAQAUANAiAIQRlymygSNNvgyyjFW8ofJ8YkzfTfkuZO4uJ9EiWB4PgIIEWgSAMJIIHbb+aWXKGDI5ZezzH/OGy3p47sipZnou0m7Kd5EYnZlPokX0KTZsAQAUANAiAISRqcuHudG1hlQPHIlvNvTuU7wRPb1b5fnECPee8l2IpuEkUkafYsMWAEABAC0CQBhJOEY0bFCmjBFt2s7/S5J1rV2PyKl4GbyU70Lm7nIS/xUsjwdAQQItAkAYkeGSlGWBd2RNf0k8wpICcirut0j5LmTuLidRHmnO9AEAhBNoEQDCyPCGZ7jRjd3vVKaMERu4zf9LEgdcTwHrqTj5vQuZu8tJ7wVhiz5VBwCQ70CLABBGBt98ghvduamIMmUS+XfxtHLJ49mYyvDPolMtnNDvXcjgDqexz3+sMrTzq30AQP4DLQJA6KCWm5vboXefUqYMM3F8k2rjVy5JeRINkzgVJzqh//m3MneXkz5rRqbYpNx5AwAIG9AiAIQOGekY3vCMMhUZMneXk65FRhcCw8/cblUmAECeAy0CQOhIzFzd8bIyFRnyCXDStUjCHxZL0gBQKECLABA6pr46aG2DiwqZu8tp4uhGlaEvSTMfwgQAUABAiwAQOiRKh94GFxUyd9f6OURPbrEaAQB5DbQIAKFD1tkv5ld//gQ46bJjsmU/G4u20wiAwiMVLfLss8+uWLFC7Vjo6uqiAowUE+Py5cvZwtBuVVWV2gEAzDNW8QY3t8XsEqEvjxdt2q6scKYBoBDxp0UaGxtZUjhpEdYcIi9oe+3atbRBf7du3UobdCBvsJFzAQA6mCpC6MvjTbbsV9bHj2OPbrKxaCcZAVB4+NMipC1ISZCAcNIilKv3fNAuHUIbZCQdwxaCNkivGH0kAABmeN13ubkt5hAa+vJ4uhaZi46yMWvBVwAAmcafFmFctAjZ9a4O7kfp6uqS7hDaIBXC3Sf0l4sBAHRkeELtFyX68ni6FiEQehWAAiNLWoTFB8F9IXATAcCJ7AddDSfj2vJ4050nlXUehF4FoMDIkhZR+/NQAcJQJ77o7++/CkCBcu38CW5oH7z/PWUqSnp3/4Q/B0o3TlYq6zx9Hz3H9utNB5QJABAyqKVWbbYHsq1FxE2EismoDW8AAAjME2EmtOXx6DNR1nkQehWAAiNgLUJZej8HiQx9l/tCWJqQ3fBmBQAQCLrK6MvjGVoEoVcBKDCC0SKkMFhPcEeIPqdX1xmkPyRLukNoA74jAAgIusroy+MZfiEIvQpAgeFPi5C20JE+D9oWPUEbnEvoQoQUDKF2nKOfAVDkIOgqoy+PZ2gRhF4FoMBIpV/EgMQH9AQAQYGgq4y+PJ6hReBSA0CBEYAWISGie6cCANIBQVcZ0h/8OVCaHVvkkI/QqwAUGAFoEQBAgCDoqsCfAyW1v4CEXqU0uHppaoNZk+f3DX/wvejp3WofAJA7oEUACBcIuioMrnoyrjbefELta0joVacCSZGTz81MKRMAIEdAiwAQLlQTu3KJ2i9iJs+WD65eatt1MXF8ky5H/OoJCW5LKfbgmrICAHIEtAgAYWI2xg3k0NrvKAtwJuVg8LGB23wgpanLh5UVAJAjoEUACBHisAkt4oWRzT/gj8uvFtEnDEdPblFWAECOgBYBIESIFhnZ+JwyAWdSnnMkwW0pIUgJADkHWgSAEDHTe4UbSGpllQk4M7rrFf64jCDxSZHgtpRGPn1eWQEAOQJaBIAQkYjitW2ZMgFnxvet5I9ruuuUMnlDgttSGly9VFkBADkCWgSAEDHd3cwNJAYOvCBaZLJlvzJ5Q4LbcpqLDKkMAEAugBYBIERgpRVfTDRs4I/LrxYRRxNORR7iFoCcAy0CQIgQLRI5tE6ZgDMTRzfyx+V3LowEt+XkV8oAAIIFWgSAECE+lVgN3wspf1wS3JZTpP5DlQEAyAXQIgCECHnRnzi+SZmAM4luJD9iQg+6ymms9HWVBwDIBdAiAISIlB0gihPRIr7cayToKi9JQ2l4/dMqDwCQC6BFAAgRKU8MKU5Sm3aUmDi94MGa2up6AICggBYBIESIFpnuPKlMwJmEqtjxsjJ5QIKu0qed8oo2AIAAgRYBIESkHEi0OEktTK3u8Uoihrf9RksDAAQItAgAISKxwErPRWUCziSW7/ETx12Crk6eLY8ceIe3o82lKhsAkHWgRQAIESkvPFuciBbxtayxBF2daj9KEoS3SZSobABA1oEWASBEwH3BH7Mx/rh8aZFE59Pt1umuU7zty+MEABAs0CIAhAjRInNTEWUCrvDHxRNhJs/vG3r3qfju6qXR07u5gI4U4ESCT3pWKDkdBQDINNAiAIQIiQeq9kEy9E9Mj6ZqO01XAorE08olyvjmE2LE5F4AcgK0CAAhwmgmQVKkJ0kimEkyxrkWhVtduURW/Jk4vkmXIxgdAyD7QIsAEBpS8n4ockSLTJ4t5w1Jxuq7IlaGNzyjTBq6E4kyAQCyBbQIAGFBfBegRbwjM49kpq6kqfajqtA87oHRxj7/se1RAIAsAC0CQFgQLTKy8TllAsmQ/oyRT5/nDXEKmTxbrgrNo4dbVSaNSO0a26MAAFkAWgSAsJBaFNEiRyLVis+H9JRMHN2oCs2jh1tVJo3oyS0uuQCAjAItAkBYSAwibFumTCAZsoKPJFnr2Oj/0MOtKpNGakv+AgACAVoEgLCQ2qqzRY6pRVYucfIL0cOtKpNGasvsAQACAVoEgLCAV/MUkF4QTsPrn449uqm2F8+XcZ8p43QUACALQIsAEBZEi0joC5CUiaMb+UPjNFby2lx0lLeH3n1KFZpneN132W4bQcTpKABAFrDRIkRvby/vAgCyhrtzJbBFPjROLOPEj5XLMElj2toeBQDIKPfv32fhQdtfYxMzNzfHVgBANpFX/Injm5QJJEM6kzhNnt9HRgmAJl0gEnTVpdvDehQAINPoemORFiGctEhXV9ezC6xYsUJZF9PY2KhKaGXkwOXLl7OFod2qqiq1A0BxI64P1L4qE7BQf2XgtbJr1V894l1Di8z0XCSj1TXEPegqU+ShV+vaBn5U0U1/1T4AWcG3FmE9IdKBtteuXcvbAgsR+su7JDW4DP3dunUrbZA64Q02ci4AgJApIdAiTkSnZ1/Y0v7cpivPf9Y+Go2RRSYfcZqLDJHRGkTVyzSZYg692jcyRR8pfbCUGtoHlRWAzONbi5CG0Hs1aJdkh9pZgJSKbhS1QQeyQKGjCNqgkkYfCQBFjmiR6c6TygQW03RtmNtLSsc647JDRAalwVVPcjFrEFX3oKtMMYde/eTEXflgSe319EdVBgAZxrcWWbFihd6NwV0gXV1dan8e7jthtcHbLEGkO4Q2SIVwlnEsAEWOhBCl9lWZwGLer78tTebq2h6ySLBaShI73xpE1YtfcNGGXtU7RTi9VnYtOj2rsgHIJBnRIgTrDEYGa8TIfSH0F24iABgk/BXmnR6AgQzQcOJhGlnEh9JYxRtcUpxIpBfEPegqYz2qSJBOkRWVN+QT/ujoHZUNQCYhvTE3N8fb5pze/v5+3tXxqEW2zo/dUMl57WEO4hCURRjqxBd0eVcBKDj61j/DbWF3S5MyAY2Kxk5uJiWVnuikz4o/NEq3K1ZzyRsnK9ly/zcvsOXulh+y5WbDLrZYsR5VDFy60vmXm9XneeJC5+5jHfLxfljbrgoB4B9bIWGlt7eXhQdte4p15kWLsBBRO/OHGFJD3EToVDJqwxsAFDlFMqf00p2x18u7y88/UPuekQGan+y7zhvxYZrZGH9olKYuH+aS1iCqXubIFGfo1dPdI/xh0pfClm2n7rOFUulZ318TACngQ4uQetCFBQkIa5eGoVcMV1buC2H5QsfyCA6dB1oEAEK0yNxURJkKjuGJGXFNaLk1qqwe0AdoevqjchJqLPlDo3T3qtIZ1iCq7kFXGTmK0+DqpcXgxCoDNCXNfcr0+PHOMwk5MjA+rawAZAwfWoQ7QsTPg7ZFQ8g2CRFdfBj9IrQth0t3CG3AdwQAImlg0AKg+qtH0sj9sKQrMhWfl+sFmUHzVs1N2t10MjHvo3dl/HN7+NN/tePEdS5MGEFUPX62chQn2lUZhcvyXVf5Y2zrHVemeVZU3mA7z1cCIKP40CIE93MwemcG7YqeYDnC6EKE7ITasXizAlDkFOR6KPVXBqhJo5dsmZTxamkXt3B6WrazU2KXOfFu3S0uzMG4SMSQlGHLlrW/urXyydI1P3thS7uIG70jRPxbh9Z+h3OdmDi+yZAjBdxHRdx4NMGf4YvbOqZjyn+Q2XvhIWd90AAnVpBx/GkRW0iFQE8AkCaF56wQm52TURWSIO33xi/dGeNdfTqMJJIs6kgLtkMGLbdGxfjSduXWeqBVucvpk5KmO0/ytkugM4Mi8d2pbFHdVO99cVuZFrj2ICFT6KtUVgAyQwBahISI4cEKAPCLxA8d3fWKMuU5dwYnuTGzppLmvqqm9s92le+eZ8uuste3NhllbJPhSkm7ZKSz1bUNcAESPdxwjlW8wZ/n1OXD0dO7eTtSu4YPTMrI5h/wIYWqRRraB2V0hpJtrNVlO5XCIx2pTABkhgC0CAAgfRLBLareUqY8RyZoWFN3b39JSQkLEeFvPjtvFDOSS8dJZCpGr+9c7HxP3CU2Uv8hf54kRMarV/G2d1/Uwo71Yv1qHo7aOKh+fKyXczGbBmQaaBEAQsHE8U3c+OVd0E96pX69vFsGR4SS5j5uybadur/p5F0ZlyFJcebMGSVANNbtPMAFrImO3XvhoTqvAzITleOxJvpC6j+UTg7vAW3HSl7ze0hoIeXBbjrsl9M3MiW6jdLzn7U7iTzxF/5RhZruC0CGgBYBIBQk3t3zamG8yFRMptcakz/f+0KFA/nyxogyzTM6OiqdIoPz8DZB26qQf+4OJYaE6EqmLh/mz3Os4o3B1Ut5m1fO84KsDZTv6+SJ24ck8a0hgeI+j2k0GpOjMLMXZBRoEQBCQeJFvOuUMuUDem+/MflT3BFIJSjTPNIpcuLECbbQhmFJjZX7E9NQZ3ou8uc5vP5p3pCV87wgWiSv10yOzc7J1FwjkYK88WhClXNG/0iVCYAMAC0CQCgY2fgcN36xB9eUKR/Q13fVJ3/KK/WL2zqUaR6jU4SNetfI1NQUG1NAn4Yq83gHVi7hjZFPn1flPDBxdCMflddapKF9kD+QF7a0Pxyd1qcj8dTopGBmL8gO0CIAhIKhNd/mxs/7OEIY0Odi6JM/23rH2Ujv5WxhrJ0iDFVBbL9zJ/U2z5iGyp+nJF9OwaJF8nfNXpKDMhxT2aLCt9DGD0u6vPuiYmYvyA7QIgCEAm758ivQp0TKkiSTP2WS7Zame2whbDtFmIsXL7L9woULypQS+jTUoXef4k+VU7RpuyrkgWhzKR+Vv1pEZsG8VnbNiGPmC8zsBVkAWgSAnNHR0VFVVUXNsPfAoKFCImVJkhduHrv5h61Ne8pUEBEdq1/IvXv3OKu2tlaZUkKfhirDXpymO0+qQh6QKdaRA+8okzP0PR48eJD+qv0QoA/HXLozpqwpgZm9IAtAiwCQM0pLS7kB7vzyGLd8o1teUnkhJjo9S02dvC5TeqvmpmzracuuMr5BA6NThJiZmZEuk3RcRvRpqKO7XuFPlVPsUXwhG49MfXWQjxrft1KZHNA7e0IS9VFf9Mdpvq535CN9vbxbYvkDECzQIgDkhpGREW7AiNI9JTd/8SfU8o1VvKGyw0r7vXHrmjLWwRpO6vY0ysrKnBrsQFxG9Gmoj/a+zXqCkt/Br+kb5/jApN+IHiuFxKVVZmWZmBZ6P30hQugfKcfyVxkABAe0CAC5gVpc1YLNU7ltw8OVT0QOrVPZoURmVUiSSFmUJYFGJItvrbzcU7TToFxGJK7JuV1rRYvcWfMfdDfbZTs7rcHZdESLuPdU6Z0iTHV19czMjMrOBRJ6n3SDMqWNMRhXfh6DNSBgoEUAyA1XrpjdBsc+fiN6erfKDh/0QiytEbXlRgQzW/i+ZBFvdzy6jFy9erWiooKK0V/aVlYNWTNv04ZPRIscWf3/ysVLconfJUsVumsR6RSpq6uTETeCro2+X1Uou0jEl/frzeXu0oE+VVkYmRLtqgwAggBaBIDc0NTUxO1WQ0MDbxBjF9Py3Mwc0elZGZpZXdszGnWL18lMTk7yTXnUIrrLiMtIh97k03YsZl5JbHaOW8231peJFilZ8yZfvJ5c4neJN/HwB99TJjvKypRDDF1wV1cXbzN0L+k4vqSMhN4PvPciMhWTPif6hN1jtgLgC2gRAHIDvf1zozUwMHBg50be7mnxMdcjm8hqL8t2dnoRIsT4+DjflEctQiQNwDo0NMQFBFvVwmNJf//xIdEi73+wWZwnvMTvmh3r5wNdZjaJ2Nq7dy9bLl26pEuldBxfUsYp9H4gkP6Q3pFNJwtzBWOQE6BFAMgN0mjNzMw0f7qCt8+fDqMWiU7Pijuk9xZOtEhdXZ0yJYOEBR9C2IqMW7duqewFyKLyNAbGp5//rP2vP1E+H5T21yci63uM36W0yLtPqX0LcrVSfzJBOb6khlPo/aCQITD6hD2qUgCSAi0CQFbhmCLnzp3j5or7DLrWPsu7tbUHuViokFmdK/cvCqLqjmiRhoYGZfKAdI0cP35cmTQuXbrEuUJbW5vKW0zp2QfUWD742R+Rnuj/2ZK5qYjKmEeP33X16tXq6mrreViLUFL7FsT7uLGxUZnmCSpWSgrInBcj9H6wrK7t4f+CRWpAUECLAJBV9D58glvchyufKNm1ky05cTJw5/161e3vcRET5tGjR3xHvrSI3jVinf2rKxXeaGpqUnl2RJu2D65eGqn/UO0vkIjf1XyPvxGre4dE5Vf7FkjE8DW0tLQo0zxBxUpJAafQ+8FCEoT/C4kSZQIgPaBFAMgeVneHeDM2G6MG7+Dm99iSEycDF/QBGl8Lx/f19fEd+dIixKlTp/hAUgnGSE11dTVnXb9+nTe8DwDpSE/PO59/yech3iw9V/2VWreFGFr7HdYisyN9yrQY+u74QCPi6oHW/k179nNWpr9NUofSx0Mbvzigeiz00PuBMxqN8fxt+stL7r1a2uVLpwJgAC0CQPawujtQm8pTNs58+ApbcuJk4II022/V+IhbSogWce+6sDIzMyOaQw/XQRtsLCkpEb/RsrIyzvWFjGW8v+MAn4d4c9sRslDjymWG1z+ttMiQvZOmzITSBQc1zPHPatthzsrotzk8McOawJrqr2RWGcgwzYvbOuSfykcHgF+gRQDIHlZ3h4GBAdYiV9/9D2zJvpOBE/R+r8cH8/viK1rkzJkzyuSZwcFBGcw6dUq5ndJnxRb+iPbu3cu70WiUC/ji42O939/ctnP3Hj4J8cGOarpNWXVldMtLrEViD66xxYCqTT6QLowtLEQovbqlmbN2V1RzVlKuXr1aVVXl5P5iix7r3Uid9xf5xwSODNPoCQvWgJSBFgEge4i7A0Pv9/SuP9N7hRq8eZeRXWyfm8v94uzSrEryNUBD3Lx5k28nBS1C6OE62OtCLNzRInFZ7t9PJdL58MTMT3ee5jMw23ftodsk+cWTa0SLTN84x4cYVFZW8oGTk/EZK/ontmLftZ3qy9x96VbymUckp9jFxOq24oJEfDndHf8X+gVkOviHDNNQkq4R+egA8Au0CADZQ4YeGhsby8rKLl26REYJN75/12bOHR/P8ZIfhhChVieFV15x6UhNixAHDx7kM/T29tLuhQsXeLe9vZ12z549y7u20Ve98HmNGkkR/uaz83S/PG95fN9KFy1CepEPKS0tpV39E6Ntsuz4XLmMrPz8UtIWWg/CyzeblEt3xvjfLdvZOR1T5y8/H589tO1UAMvQJKWubeC1smufnLhLqk6CjmQiqAkoBqBFAMgSuruD3vMx3XWK27z6PZu4QF+fvbNkpjEGZShxs5oaokVYcqWAzHzmM0hHCLfWHR0dvJuaTwZ9HTIM9PHOfbzxX7eekHtveO+/8Pcy2bJfHaMhM5Zramr05VrkEzt2opEL0Dl1l1hbqqqquDDh8eP6oOEO/8eS5tw8LToSPu7dOptwLwAkBVoEgCxhuDsI1NRxm9dU+jEXoFZc5WURoy+EUjpChBAt0traqkw+kVGeI0eO0C4vQ0NEInFnCFIkvHv06NH54v6Qk6/fUfWzbcd4+1c7Dsnt7/3Vf3XRIuINc7ihwXZd3PZ2tTTgqu2HX9zW4TLCJfFIGL5Zd6ZjczJEkqGYZr7g4HJ8PT39qbjvgCIHWgSALGG4OwiiRS5UqTEaXw6M6ROdnt108i43JJyocaU3XZWdKiRB+HZS1iKjo6N8hrKysmg0ytuy6q/k2saYv3Pnjt7ZQMh6dd3d3SJriJIvzv3ddjXcY6V8x+bLh2wWLBSltb/+JH9oRiA4kUrrd+6nXD3efM2Jlq27K3YdbKJPnnYbG1UPCuNlZlDn/Qj/09fK7P1qs48EoQl2TT5QJECLAJAlDHcHIXpyC2uRjgOfcYHm5maVl3na742LCySlNPtCdNLXIoSIBvGoqK+vV3la4DhjhTzrUv4MGSORiJFFlhktOpmVPSXm+QnSi5z7aVUTf3TGPCM6LRfYuqtM/2zvDkbErXVVSdPF6w/kX8vtGFFVrBxo7edzfnzMk3NJFujpj/IlUULXCPALtAgAWcJwdxAmjm5kLXK7YQcXSG3QIQWkSeMUoBAhRIukM+QkM4/E7ffcuYQnqawvaDTespS/FfF4JUgEXLx4kQ+hq3WRI3+3s4V9PqLTs/QpLdvZ+c72Q5z1k+2N/OlZR2HKy8u5zF9tviSf8Gf1SsQQO3fvWb9TubjSncrNWgPOGkgnRKbjiPgCXSMgZaBFAMgShruDEKn/kLVIf7OaI1pTU6PyMokeKYsa18BnQEgwlXS0iD7BhNFnzUi0sZ6eRDByvVNENIqsVydZJEo4y4m7JyuO/uanXPj1rfHOj4+P9Uof0q93KG30w8++pF3bQHD19fVcZtfR+CGcJAyaAV2q3GzSmUfiYhyqHgi9awRxz4AvoEUAyAZWdwdB5o5GW9QrcmqxRP0ikbJ+su96JhZclc6JdLSIuIgKjx4l5qRI4y3dG4T83xMnTiiTxT+U0OWLLdOdJ8/9ehkXJgEhrSynT3YqZfnS5hbatQ0EJ/OA2tvbd31xftOuip9sPbFh5yIvFub9HQdKzz6Qm3USow3tgySGPjmhnHvc1xn2C6/ayC41KfPeF6prBHHPgC+gRQDIBtIW6u4OTCKORXezxBLl8FkZxYiUFTiiCW7e9Bc8Xsfw5KBtPRSYrJQrssO2U4SweoQkjdY6feNcx6/+nAvvqvyCPytKy3Z2NncnwsL+5ea40XaajCye19jYKP9dnEVkDIvguCb3hyakmHWYZjo2pwdcp/SLA4EtTSefG/21Osd4p+XWKF8b4p4BX0CLAJANZIan7u7AjG5bprTIjXP0S+RiejuaCWwjZQWLaJE0w6WIJwf91fs/iJGREf4X0pFg2ynCyGdLeBkFm7ndevvtfyfly88/ICnw8bHe0Wgs0ctSXkWfoZOfjaxULApD4Atoa2urrKz8ZO9R/i5Kzz6Q6yetYzwD8pVJCrDvQf4vMTQ0pKz+If2BuGcgBaBFAMgGUtdbg4RKrPGZ3isyvTPT67tKX3rmImWJM0dGQ7fpU2mcOkUYcRkhkjqLELxO0J5dyqFY7y2QU7mfZ2pqiotZ0Q/U+xImp6Zt1wUktjTd42KSzveMqrz0MKYd3bqVVrwyxD0DKQAtAkA2kBkfursDM7LxOdYi1PjJMvQpxzX3Ar3HS3uWuUhZMm8oo1pEn0pz6tQp3rZ2ihC6y0hSZxGCtUjNlnV8iC5upIsl6XmMGCeCfqDRl0D/SASWrAtISBlJQXn56J0iRJouI3rcszuDuY/DBvICf1qkq6vr2QVWrFihrBbo56cKPfvs2rVrySIHLl++nMswtGsbpwiAAkNaF+vKZ0NrvyNaROKakyhR2UEjr62UMho+XLSIVX4FiPS+yIRYwtopQuguI16W9p2bmaIvRabSiHrwdZ7jx49zSQPjQP1LeWl7Z8VRJUkJvheZovJa2TX61qixD3CApqysTP2zeUiaqIxUebfuFl/tlqZ7ygSAKz60COsJkQ60zTrDYOvWrZSldhagkmSnDVIwvMFG2zMAUGBQ68W1/N69e9ky2bJ/eP3T0dPxgJ6iRWhbnDGN2KxBoc/jDTaaiBXRIhld6s866dflo2ttbSVR6H39GvpSZCrNmQ9f4a/p6rv/gS1VW9fT7uDqpZNnzblRgj4wVFNTQ7vl5eV0GZw7eX7f0LtP8WmNdPiTt/ko7uORJW8C/9ZkkT9BmoOUkVGnF7a0Z3rFYFAY+NAipCH0Xg1bzUGQsbGxUe0sQAeykY4iaIM0jdFHAkChMroQrVxWouEWaPDNJ2h7YOUSbn7i2wtr1qTfHtgi83ht42EES11dHd9LRrWIqDeGKhbva+4nZXDVkzKV5vDGn/PXRKKELY0b/oEt/D3a0tPTw4UJq3PJ4Nvf5DNY081f/Ik6bL5rZOX+G/zFtfUG/GHKIn8Sma2iokLlpcFrZdf4gg+09isTAM740CIrVqzQuzFIW5DsMCae8eiM2tGQ7hDaoDLcxWIcC0ChInEjJKCqNDlzUxHeoGaP7JOTk1yysrKSSwZLpufx6tAvne8lo1pEptIQJSUlJOZURhBEm7bfWf1tPjn3glA6uPk9tlxe8yxbKNH3qI5ZzNDQEBcmDOeS2ZE+Odw2kfrhAxuOHudvLdiAIozI34aGBgnHl/6U8rq2Ab5meuQwuRckJWAtQoJj+TyUxXABFh8E94XQX6qn5o8AoPCRJWFlJF7am9j9Tt4YWvsdzhLPkrm5gGvwLMzj1REtkulYKfKJdXR0KFOgyPkHBwf1fo5oNKr7+qjSi6Ev0cm5ZLrrFB87um2ZMj1+PBqN8SDa2VXP6V0jHIDko6PBz666f/8+/wuq0qkh4O30XXwiUzGJhhLUfB9QwASvRXQjlWfxoUNGwlAnvujv778KQP4gbpXHjh1jCzdClG6crOSNh+/8X5wl4c4uXbrElqB4a5/yFNlQ16FMmUT6/NV+xqBPlf7L4cOH1X7QHDx4kG9k//79e/bs4e26ujrK6lv/DH993S1NXNgKXV5ZWdmRI0fU/gK3qlXs/7u7fqxM8/y0Iv411a9eTlnSNfL+jgNkrGjsVIWCQyYfUSsgWoSMKjsNfn1QaZGffN6uTKCYoJZatdkeyEi/iNqxK0OvSlyATiWjNrwBQKEiM3Xlt8CNEKWprw7yxuiWlzjr6NGjXJheWNkSCKe7R7hhoJS5ebw6okXUft4yODjINyJQPcZeKYnYMD2LgrB5IVK7ho9lF2bhWOcQfUd7f/VfKUvvGnl5y4VMhOqXKHytra22MfVThh4zeeQy7SgN8h0fWoTUg64zDNnBsPhQOxb3Ee4L4eqYjqXCtEHngRYBhY28ekoEM26EKMkivaJFzi4sJNvd3c2W9OkbmZIO80z089vCd0Go/XxGnzCse6WMlbzGX9/0DTOcblJGd7ysju08qUzz8DDNtrXvcO6+nZ/x/91SXqdK2NHW1kb1bQqhQWQJQ3qRvXXrFm9z5Zw+Jc19IkewQg1wwYcWYZ1Bjzvv0rZoCKdtEhx6VwrtyuHSHUIbYgSgILFG2uBmhpK8HI+Vvs5Z8m5KjQRb0mQ6NveTfde5PXi1tCtrcyz5LqxrAeYjeteI7pUiawlNtSuvZO8Mr/suHxsbMFfYX13b8+Gvf8O5B97/ifrHdovUMOLFQjrJ7zQiCXR28+ZN8bQ9ePCgyk4bPbCe7ao9ABA+tAjB/RyM3plBu6InuPODIZ3BRoJEia5LpJi1cwWAAkOieqsZJbMxbmYoyYs1tWrzZRPtih5zMzUOtPbL4vKU6G37xqMJlZd5+C4K5k2jpaVl7969hkAULTLZsl+ZvMGB1OJp5RJl0jjWObRyfQUXaPrl93+1XcWWLbUsUkOMjo7qwcr8rh4g0dj6+vpisRhv0z9S2UGwolJNSKb7UiYAFuNPi9hCdQ30BAAuSFPBuxxcnJM4HIxXvcW5sqCadUVfX+jvo5xs17XPEDI5ubB7PWWIza8WiT24xgcOr39amTRGo7G/39jABS699Wff39y2r3I/f57GIjWkHiQKPuM9khtDjxkfyEvi0ffFuwHOxJaosh80ZGl8EOQdAWgREiJOPYcAAGvQVV2LDK35Nm9Qq8a50WiUy6cTYkSPKU7phS1Bhgz3gkTQKhItIl+fR6baj/KBYyWvKdNias/d5AI9P//jhvZBQqYWuyPx9AyuXr1aU1NDf9X+AiI+IpF4iBRxnb53L7Dw7dceTPBzmIn4KKAwCECLAABcsAZd1bWIJL0xk1YntRAj2Qz07oRokbo6N4/LfCfaXGr9+rwQPbmFD4wcWqdMFgbffILL8C698vFHaktbW5sEMrG6jLisYGx02p07d453raolHZbt7OQHsv1eBgPfgfwFWgSAzGINumqrRahV41yC3l/5EGpClMkP2Qz07oRokYaGBmUqRCZb9vPXFznwjjJ5Y7zqLT5w8vw+ZbJgDaR26dIla+8IWXgKLlXjbLG6jOgr8eqLGMtiNBL3nSQIW0iUsCUQPj7Wy88kZtMAW6BFAMgs1qCrtlpEdzhIM8RINgO9OyFeL4WtRSQ8jLgee2Tk0+f5QJfAJIngJbfVWnruyDp8hsuI3inCSNeISMbq6mq23Lt3jy0inQOh6dowP5M/qghspjooJKBFAMgseiwptthqkanWQ5xLpBNiJMuB3p2Q3qDC1iLTN87x1zdW8YYyeWNw9VI+cC7iOLVk7PMfcxmPE4ZFRvBoID08sr6MwfHjx/kQfTEatmTI0Wc0GuPHkhLingEr0CIAZBZr0FVbLaIHy0o5xEh0enZ1bQ/X+CXNfcqaC0SLuKzgXwCIFpFQdV6Yi47yUbwgohMSe2byrKcYLTMzM9L/EYlEjL4Q4vr162pr4WnUF6PhkxAyDGSdP5wO5ecfiByhbWUFYB5oEQAyizXo6sztVm5j9KRrkdRCjLTfG5fRGUrZCfTuhGgRGZkqSGKP1GwXX1pEHoCRjc8pkx3i3+rdMVZcRqRrjSkrK2PxIU8jCQ6SGqJOmpub+QyEBJnVPUsCQeaZP/9Ze9Zi7oG8AFoEgMxiDboqL9N6olaNc4kUQozIEu2cctspQli9ZAoS6eIa/uB7yuQB8TJxH9kRx1jvzijiMiKdIiRKVN48MzMzEnmPNlpbW3lbBhAJ0ihsJILtGiF+VNHNj+ilO2PKBAC0CACZxgy66qBFZK4E4TfECL1iyiTeZTs7v7yRM5dVQV64C1yLjPXz1ze09jvK5IFIvVqhd+L4JmWyIzEAtONlZUqGuIwIPT09Km8BkhcyCiMbxgxeff2dioqKFJa5cWLbKdU1UtmipDkABLQIAJnFiN9A2GuRsUXra0sjEYsl78qWNXhfLe3KxFKuKSBaJKhVdUILf31D7z6l9j0wVvo6HzV1+bAy2SEDQMMbnlGmZOguIwzpWpWnYQ1VcvPmounfetcIQef0u8yNEyeuxlchpvTeF+YqPKCYgRYBILNwbU6KRO07aBGVt8DBgwf5QI7M7c4nJ+5y/Z7zoRlBtIje+V+QOH2Dtkx9dZBUixwSu9+pMuwQF1drGly9NHrafgFkcRkhampqlNWCLEPD9PWZTw59g+Xl5Sp79+7e3l6VkR53Bif5WV220+3eQbEBLQJABqG3Sa7K9RmSXrSIdJJ7WepMFsBr6w1LUEtbR4SCRKL4q31XBlc9Kd/4wMolc1PxsOsuSOhVa6IsVWgx4jJCGM4iOkbPh5PklTCsAfZvvbRdxWDtGwmmrwUUANAiAGQQidagv6FatQi1ZypvAZkJ3N7erkwO3HiUWOwjhwFFDIpIi0h01JHknVLyjZMQidR/qKzOTBzf5CJHdB8jQXcZsTqL6OhOIbwYjRXxQT5y5IgypY1MO89hLD4QNqBFAMgg8vaph/ya7jxpNCpWz0cZ0Xd5tWUqW1TE91ANwIsWuX79ujIVKMPrn+Yv0VYZLGI25vR1+yIRj9UuZqvuMmLrLCLIw1lamlh/wEBWU9IHGdNEAo0g6FkS7m16fPp3Hx//WjzRxt3fKHshAi0CQAaxLkZDyFxNSdaF4yUIVdL30bdqbnLN3tAe8PTLdLh06RJff8FrEVEGsQfXlMkBmQCcphYZq3iDz+Pk+kpCkOSFEQnelosXL5aXl/NyNk5I8FbSLsqUHud7RvmJXbn/hjIBK0PHlArRU7+jA1C+Ay0CQAbp7e3lelyf2mrVItZIWZFIhA90n9arL8n7cHRaWUMA3S9ff/FokWktWp0tokVGPn1emVJCpgQ7ua8GiwzlSODgNJF48C9saY/NhmVUMVzExh43/54pRCid+eePZ5I7s+cj0CIAZBCZTqLHtfSiRQgv03plSd5fHHDzDMg+okWMyaKFx/i+lfwlJtUiEm7V9uv2DkkQPo8Xj5P0kRUJAgwVIwGCbzyaUCYgTFx73PanSnyc+sbj6Udx/UEqhC2t344X8AWV73whLm4u/9njvp2Pxy6qXfrLpyLpQ3bK/eqPH99ZN39MtikaLSJfBv2lb4I/96bfUd+ul0SFb61WZwPAG7ICu+7CadUitvE3vUzrzcSSvHfu3KmqquJ/reMr5pVoEetk0QJDtAh9rcrkgPgse49dZsvU5cN8Hr8L8qWGjDOK/7U8ISmHQfug4Q4/t/VXBpQJ3F6T8A6R9HBhKaL+moTxxG/ZNGSkWvQWips8X22cpO5X1UmySCFqkXS+A/dETwBJVAA8Iy6cbW1tymSnRWyDfCed1puJJXmt68vreI951dTUxIcUvBZJrGCXVIt0nXL5ur0z03ORz5Nm/4pHdGfYrq4u6xOSwtiNLFnw8bFgwpbkPdT8G80Npet/r3KZGz82C1gTnYcFCrVWRpbfRG0onS2WpVD9eatF9H4O7mUSY/rfgUu65xazGQADCfagu01YtQi1ZypPI+m0Xnm5DDDEmfRnOOEl3gkhq/AUvBaZOLqRv8Sk3hvyvaepRcTvZHjdd5Upw8hTUVpaKt+sQEa/bq2d9yP86L5e3q1MxYxViJCYGLNzKCYjZRmFvSQ6auhYfPzl4rfUYM2jykWSheyUe+1vE4dwosJ0oEd61z8++/upKZj81CIkCFIQHPLp04fLn/ukp1o1Dv1HPsklH5GeAZBK3F2L2C7E6j6tNzo9+8IW5bUa1JK8+iuv0bqIqPIyO4OwrghYqEwc3+TyJerI9z7RsEGZUoXP4xTuLHD0FfWEhw8f6svsURlV2gPTsTnxuQ7JqgU5o+ftRCNFrbgXWJEYDZl3QePOzZWPT37dPJX+zk+If4neHaBP/PGlYObJTy1C9yn3bJtS+w5cmH6k1A/9xTAN8IztUIW8SUuybcbcp/U2XRvmqvytmsCcQ0U5nbAsFi8RtGpra5Vp3humpqbGWFaNES0iKwIWKt4VhnfVkhQJJD8XHVWmDEPaVJypCX5CdOOpU6e4pEck4tmxznyeGHJ7zeNzf5C6KyE16tLwexQiLtBlnP9D32/aTvTXJBxm3RM1izxMYdjpw/FMfmoRukO6bRIcRi8TpcBViHDpKfUvMEwDPGM7VGHVItSeqTwN92m979ff5qq8ri0Y7z/dLcDa5a7nssuISycKUVdXx1nFo0WSjrzI9x5t2q5MqTKy8Tk+VdKgJgEiHXWEfOO2Ri+QBOEHmESJMuUdeldEakqib6c6vOVfK0uomBmKiwy5Ry/p1DcSCoa2PZOfWiQnYJgG+Ke+vp6rab1J9qhFCKdpvfoAzcB4MGFFpOfDaUE1qiW4ALuM6J4l1n4UmYlT8FpEougm1SKRQ+vcv27vjO56hU813Z2YK54F2traSBmfO7do9rIss2d9DFwYjcZ4mIb+5uUwjXVMJAU5Qi/PfOyd3Myk9QQPCRl+Edz7MnQscQucHpbHFUzXsrgQ6XlbncED0CKewTAN8I9tk2zVIlPtiaisOk7TejMxQCMeIU5R53WXEetkCuOdWG58cjIYX5bQkpipu+sVZXKAxAqXTF+LjFevCupU6UNfPX/XhK+ukbAP09D7J0+ybfqdeIvbtxCZw5ikef4PE9ssR8Sdgksas20FfYAm/SGVHMJipfG34zN9UgVaxA8YpgE+oZdIrqPn5hJzbq1axClMltO03sAHaAjp9nBaUE1fdM076uDCxXsEM9EiSaOiJUUeIdpQppwiD6pQXl5uG3qkq6urpqamo6ODtmWYhtKynZ3VX9m84zW0D1IWl3lpu32ZjKB7YrokFh/WPhJr6lqmnCpY1rBe4ayL35r/l0UNtIgfMEwDfKIq5sVNslWL2C5yRthO683EAI3uDuK0oJpeRpDAsraUOi+6VjB4j+zuPVp8UhJOKtWrlCmn6F0jOsYInTxC/GDIMI0kY7W8yJRZgFKm1jow5oYknSFx8uvxKSeCFznilMI8QJMtoEX8gGEa4BOukQ3nU6sWcVri1XZabyYGaJI6izCtra0iR8rKyujyyEhyhF6C2ahDJfVos4WK9xXvRIvE7ncqU6pMdzfzqZIODGUN28fAeAD0rjWWKdVfPRJhzUmXI6e7R/QsTqVnH6jsAOl522YiK6VT8yHYJ+/E5cLFhcgcTtMj9Nmw7rNtjZTXAzQBAS3iEwzTAM9QbcvVblVVlTLNEznwDjckkpy0iO203kwM0CR1FgFOzM1M8ZeYVIsMb3jG/ev2TuzBNT7VyMbnlClM9PT08ONkPPnymBFG4BmSIKI2JHbfJyfuiqXlllrdd/muqwGvqOeiFSQEe5qQTGGnk6GFgGN8fnqtTcPHopCAFvEJhmmAZ0SLyO+LEb8BSXNTEZW3GOu03r6RKem1DmqAhqAr5H/k5CwCXDC+TWsaXL00eno3iRXenR1JNxbtXHRUTi6J/4sqkYzJs+XDH3wvQyv9xmIx6SPRZ7PLY0bcunVLWRfQ5UhPf3ygkGQH77b1jpP++GGJWn3pyxtBrL5EmuDUN1R9zqnlX8eNPDckPU/MFOjq6qqtrdV7krq7u2tqapzCLhcY0CI+wTAN8AzVwlztNjQ0KNM8hhZxj54p03rZ+1XeFN+vv80F0kfeYgknZxHgwuCqJ/Uv1DbRtzy05tu8rQ5LDzqhnFyS90isg29/01d5v5w9e5afqDMLq/sa/ka2IfLe+0L1+dHjfePRBG+/uK2Dl1vae+EhWyg5+braUtc28FrZtUXlp+7PHV88SZW9UHOE/hvkuUjycdFf2uZiBQy0iH8wTAO8IVqkqalJmeYRLTKy8TlqEtynQtCLEZ9kdHRU7xThF8f0odOWlZXxvzCuE3gk2rTdVhk4JXVYekwc32T7T70MAM2O9PkqnwKPHj3ih4qeLm5KjXlYly5d4pI69FSL2pDllkigcO7A+LS7r6stsdk5cUkRv9do9ypRIdPH/knlvpej07OclX303yDBYVr0j+vhw4dcsoCBFvGPDNOkkJp+J+6hrQf2B4XLzZs3uSqRV0MmMbfTQ6Cqo0eP8knu378feKdILBarra3l81dVVXlcgxf4QlxWOQ2uXqoyAkX+y8zt5P7CsmKwx/KpITKaF2PSnUUI40chiDuUpIb2RMyS0rMPDDmSdGHIO4OTUlj8XiMn/xeukz8qXcNZ1x5McFaW0X+DwuDgoP5x2fYhFRjQIv6RYZqsJVIwKa93AHKHzHdtaWlRpnl8xZmQvu72zq5AOkXu3LkjgciEkpKSgYHAPGGBztjnP5aGn1JSF9fUkP/iFDdPJ9pcKtfjpXxqXLlyhZ8uHqPUnUUIY+BS0LtGONlO4tWdS0htKKsd+mQc5fc6UMdV6+iRf/rDXZc4q/5Kbp7/c+fO8QdCv0FZOeHEiRP6x9XcnNXoujnBnxbp6up6doEVK1Yoqx2NjY1UZvny5bwrB4qFoV3D0To/IGVgOwEscwnuKXlIW1sbVyXGzEZfWkQq9IPHz3Klmc4y69Z4qQzHngKZIFK7Rhp+SsMffE9lBIr8l8mzyad+6JfkpXxqRCIRedhu3bplPHgus8dPXB16aXs8vhmJb5dRGAnbuqXpnjLZUdLcx8U4nb9+b+zsUq5X62v+qrLlEdu3nUo+3BM4+u+RfoODWpgW/eMijaIOKFx8aBHWEyIdaHvt2rW8bYVEBsO7VHLr1q20QQqGN9jocobCxDawv8cE95R8gyQIVyVGF6u8wnrRIuLUtq2ygSvNpJ3SLuiLyDClpaUXL2ZmOUkwT/TkFmn4KY0mC8+aGvJfvERiHd3xslxPRiO3SjxWaVll8Zry8nQ1kMzyfWFLe2Qqdrp75Cf7rh9o7VfZC4g/LKVf7Nr28PA/k0r1wJmmS3fGOGvl/htU2OkkGUJ+j0ePqt4pawRbgn6keuDmgsSHFiENIdqCoF2SI2pnMaRXWHNIedpobGykDTIStEFl9LMBRzCLOG+R4RUeLxdkaN+LFhEfwN/s3s+VZltviqvN6S9h9AamrCDDSIxUThnSIolIrMmW6COG131XrsdL+ZTRX/QZslRUVPB2+tNDXiu7xj+KLU33ZATT6EqRicE7y38kKoTSgaof3B2aHI3GOPfFbR26b7gXr9g0sf09Wj8xxliRqvDwoUVIXujdGDwKw4EXDchOuboWke4Q2iAVwl0stscCE8wizlvkpcdYTcaXFolGo3ySz3Z9zjUmz28Uuru7uXKnv7YrgFCBvXv38kkYdtQH2YG+ZWn4KY19npGoFfJfRne8rEwOSHA2j+XTRH/R5wdPVnykxpjLpExd2wBLByOJkhCpsXz7hdjxf8IqZPTIP/1gzzopI+vd/OKAGvThlE4HpBekfjB+j/onJvNrbt4MLMhyOAlei5DmoJK8IVqExQfBFvqbl24iuQKziPMTmQKjh3sifGkRQkKM/OXmxPxGxojZQNvWuTD6dEEGnSLZJPbopjT8lDLUDyH/ZXjDM8rkgMRs9Vg+TfQXfX7wnH4XKRCZipE61wWEJJYabb3jvFt+8H1Vi577A+Ol7t26W3KUkYKaOW/FpZNS/8Skb9Xwfy88AtYieoeHrkV04k4ia9ca6sQX/f39V4uJvou/4F9R5My/VSaQD1RXV3M9cvHiRWWap++j57gZuN78hTK5Ir0aL21u2X2sQ1nnESd84csvv1R584j/LLNnzx765ao8kBW62lqk4ad0d9ePVUagyH95tOqPlcmBmw275GK8lE+fI0eO0INHf3mXmht+Gs+cOcOWdPj1wYQWeWtf+4e1iRm/79W0b6pXud1H/h3Xog9bfqKOXGBD3SI1Qyd5c5+y0LYqFDTyIRw8eFCZNOizoleIY8eO0c+Zi9XU1Ki8/IFaatVmeyBgLcI6g7dttYi4iVAxGbXhDeAIhmnyE+mLNlYrlX4Rj2GmDjcc4fP8f1uajfmNRswGwnBElYhJ4hwHso8elCxzvqLyX9S+A4YvbdLygSM+3bZDin65O5QIH8LdGDu16b6cXvzs3OwJNUBjXYhOFpvkRCfR5xVnomvEu+eWrCNRUVGhTAWKDy1C6kHXFrZSgyzc26EjwzF6rwmVJDVDG3QeaJHkYJgmD6Enn+uRyclF8Q/8apGahiY+zztlZngoebuiXxNv1NfXq7x5Ojo62H7unKfxIJAJZCUaSpnTIon1blyfq/Gqt+RivJQPHGoC+Jm8cOGCMqVH+fkHL23v1NfvNebxflz6jqo/L35LldDQlYdEEZSQa7ZxBXUfLNrwu4qTk6eILbKyTyQSX7WKftHV1dWFN/fNhxbhjhARFrQtGkLfFshiiBXalcOlO4Q2xAgcwWyafCMWi4mfhzIt4FeLbD2gaq6S2tPKNI/uLCJjzPRP6V+rElokpatFELoxtMg3Tmny/D5lDRr5L+6hVEc+fV5dzMolXsoHTm9vLz+TGV1zoOXW6A9Lut4v+VCfxBtf+s5CbHbOGkVQFyh0KjYK6fhg0c9WagYvRzU0NHBh+tzop+2xQyXv8KFFCBINJDsYXXzQrlVPGFpkfvQmMcSTjr9IMaIP08TGlBGEmIcPH3KVQS8xyrSAzKj0qEV+tktpkYOHF71FyfgLh42i3yDv6quxHzmixneosDKBrKOHXp1s2a+sQeMx9Org6qVcbHTbMi/lA2dgYICfSaMPL3AmRjpnjQXwLAM0TPVXj17c1qH3rBCy5AJpmshUQt/LuImOlx4ORpaG8Hj7Fy5c4PJtbW1SqxDe/2Ne4E+L2EI1IPRENjj/h+rnNBwf2wImE9fia/00/U6i3nFKWVkVqL29nauMU6dOKdMCHvvSmRuPJl7+TPnSf/HFospLnEXOnj1Lu/SPeFdfZFx6krmDF+QEPc5p5hp+L6FX56KjXGZw1ZO+QrUGCD2K/ExmvEecfubyq6e3uBv+ZlOT/iAVIr0jlJbvunq6e0T3wZL+SMJjR4VM2fUY7FiWkmhsbJRahSmkrpEAtAgJEcODFWSEq3+tflR23Ywp4r39LrxEdVMmFYnUONbBEV9apLLl0V9tvsSnIioqKuSE4izCw9UyDC8vTDMzM2wpKytjC8gJuruox4ncKeAl9OrM7VYuM7LxOV+hWoOFH8uSkhK1nwnop819yZTGUvSukNCuejp9Xs1NYx8s+aUT5eXl7g65+gCNx9cDkTvV1dX6/yLkl+6CxB+i15Iwt9QBaBGQJXrXq99Vx39WltSIjT3ufvXxqW+osyFRIjV2+c8e9+0McPxLOiSs7y4JLTKSPL7CWzU3qfrbuXsPn41gjxB95DgajQ9yS50lLvfSGV5bW8sWkBP00KuZ0yJeQq9OfXWQy4xVvOErVGuwyHii4dYdJNIpQj/tNDjQ2s+L40jatO8YXzy/FcjvTjDmzen4HaAh5ubm5JcufqwM2d0/QL2WYO7fz3g82dSAFskfRs+pn9a5P1CWFBg69rj599R5Ci9RpePlBYhXBTKO5XTy64F0lsiIcllZmXUhCdEiat8Z8apbue3InoXXKYKqv76+Pt7W1xiTqmpkZIR2ZS2bjDoJgqTooVczN2nFS+jVSP2HXGbi+CYv5TOEdOlZlXpacEcvVXHtz6TfKWIg3qwf7FBxg8QH6/r167pKcJnkIh0bvlajlEV8GKpVxKG1u9ttpUzdv4Sh6iKcS9tAi+QPs9HEr8v29f1RZSo6w2P7XXiwIpGP1JrScCsREWC7MLp3LXJnUMVOeK0sfhkycffWrVuyfu+ZM4mJvhLRklfAuXRJDe4YCwWDLKOHXs2cFvESenWs9HUuM3X5sPdQrYEjTXKQLtW319j8nNPrFDHgib5bdqlJNMYgi8wP2rt3r217n8IADSNnZqhWIQnC20eOHFGF7BD/EiomaimQsC6BAy2SV7i4r+qDo0nTqW88fphVb7WwM3kn7oVz8VvmB0WJPtVrf+t37EZc323fkLxrEYnCxEEOxFm1ra1NXpX0FyOJstrc3Ey7ol38xj8AwSIeo5TmpjLlRKz/l6Qpdr8zafnB1Uujp80Z6YEgU82NZSNTZ+iY+cvlFOiLVk9/9Pub1U+stNT0wSL9UVlZybn0tqCsGikM0DD6mQmqByYnJ3nwxX2YRjTf1atXJdRQWVlZCD3ZoUXyChf3VRkcdU/UsrY/g+Ctjjh1ljT/Xryy80xdXR3/7I1V8ZjBVU9yXa/2nZGQTZUt8a9M6rKmpiZjOIYxBm5qa2t5N+CecOCfwbe/GW/d33xC7WcGPcCrW1q5hCVR0vIZumARzcH02NGrgnQJ0xvFo8r4r5gsPW+rAsGxulJd+eeVibFRQULK2oY5Tm2AhpEzE1yryHR9eRshuzjicAQ23WuNBI1EgiacltLMFdAieYW4r57+XTX5hZ0uyS7NZ3EOuGQC1iX8qXLyPGQj/mK2ryxS0at9Z1bXqoVDz/fEoy2JLyo7xhOkSLgko0c/ozcz2Sa7KgFyxOTZ8sHVSyeOZzZuMp0/uRxZuSRS/6H38pkYVJJ5qtyBly5dy9Qv9NQ3Mv2i9fkxpQm27LXp24hEIvKjM1d+0OK+p9AtoZ+ZaxVjmEY/v4F4rRnuI1TeupRmroAWySvEfdUpBTo4CuL01zw+888TnzBpvmRRCujnzT91eilRpsVILa/2nZHVzAfG48vQyJkF66Q+efeSislaBoCkeIzimhr379/nh/P48ePKlDIP9iR+npkfej52Wg2//mLn0disjVOI+GwZXT6+4r7bQieklxAZ9jWGaeT8VnSvtY6ODt3N1rbjNidAi+QVuvuqbUKnSCaYGTKHwLpfVVl2jIyM8O+8rq5OmRbjUYuQ/mAh8tL2TmXSpgoz1l5W6wxDDNCAFPAYxTU1kv5GvDJx7XHjb6tf5ZX/qIyZRHywXt/a1H7PZu6u+K3vKtv7amnX3gsPyeh9MTxfyDANCRT9/ARvM1avNfE8C2pJoPSBFsk37m2Kj4NeXpj8Ik6XZPQZVRD4gz7wtj/1IkeSvvNxFT/0bpKlhSTO0urahOepzOVjHj6M13QG0jVCoFMEpEZGo7IafYf0sn7w4EHDi6K7u5sXylf7VujdTNz5qQKkd4bMIz5Yf/PZeSNmPBOLxUrLPucyP/zsy3/YdkZ8OIhgf48yTCPI+fVKwNr5IaFjwxN5CFoEAD+QBBE5cvc3yrgYGQvn0OxWlBZZ+x2170BlyyPWIjvPJMIT0Tn55ISxDJ6gvxXRtrIC4IdMR2WV2a26Y5MEBhXPJ/Mhp5exc38Qn75LiC//id+Kj19nHr174/ub214v745Oz5L9QGv/8l1X+dcaf3nYrqKnrN+5f+cu3lQE+3uUYRpBzq9XAlavNd2xLCQuI9AiAPik/RlVA578uu2gmHi8t7W1KdNiPGqRDxrucNXWdG1YmebfIPnkhPxsrVy8eJEqcWvfLAAeyUhUVolFdmedvLjrrSk9tNyayos7kWi/9Ym70iNCKcBlMVwRn4z3dxzg3+arpV0fH+vlbUmygJROWVlZJkKwX7lyhVWd9fz08yejUyVAtQdfWEhcRqBFAPBJbOzxl/9SVYK0YQk9Il0X9rETZmMetchrZde4apN1zAk96lFLS4uyAhA0wUdlvbdJd3cbuaeWcjSorq6mt3ZqQZv2/e3g4X/Rsv8vVHQcfeKunrqWzZ894+idIntP3xDlYU3Pf9a+oywRDoQItjskEOgT5msLicsItAgA/hm7GO8Ukdrw1Dce31qtsrSAzbZLP8wO3fWiRaLTs1yvvbClXXfXpwqRT06ExwceZAp646cG+Ga214shAo7KSkpC/8lQ6lqm+zTU1dXJqM2pU6ca67fOHf9HXPLhmefia0V99cfqQN1/39WLPFiMiTAtt0b1VXz1gVRCwiITZTWHlTVMhM1lBFoEgJS4+5tEhbi4WhTvNj0KmeBRi3Tej3Ad95N9ZueKVNnWYWBQUOjtNz1d1B5f/rN4k8xDErfXxMcpMjY8IVFZkzpZe4Iunm9EVgU/+fWhRyp2HzE4ONh99auOA98eafifL+7/T9drn1TFrOlheVyc0XmyK9HkdyedHJGp2KaTd1/c1mF1Yo1EIrt3q06UnScytRh4OoTNZQRaBIBUubnCfNUjizbt1jbCmEctUn9lgLUIVXbKtEBbW1t5eTl8QQofab+tSfeW6LcJABoIEgZN7aeDhA0k8SRDnLfX0GNMbXz8Yfa4bGeOZgvOa4s4lZWVypSM+sbz23eXvrXt8AcNIe2/DJXLCLQIAGkjk2tO/NbclIpsWFZmLlfBiBYZXv+0MtlBEoS1SF3bgDKBYsMI++uUzvzzDE1nJbnMz2q6oVf1Dh4OQ8DbJD7m5vU6KQy2LE53Di25uP8/DR7+F3Mt34wfRcfmCBnRcF+LTkfW9V25/4YyhYxQuYxAiwAQBAuD2ZM3P+Kft76Uv45okdEtLymTHSsqlXNc5/3QrWIFsoHeflNTff4P42GEqD2+9rfKSMnoljv9u07zzFMjldCr9zbFL4MuRneikg4eugWClJMEKHtUuWie/KlvPDqx4Bdy/Gu1n/+Sf02Dg4NdXV21tbVGMNOscfXqVb6Sc+e8Th4ejcb4J/zDkuCnzwRCgC4j9PkkCQaTDGgRAIKAquD52nPq3JP883Z6f/KiRfpGpp7/rJ0rssiUTQQRUPgY7beOeEv016gyegpuyMZ36FXrYrnsRNX6bbUr3i26opJ05T8+nn5EsuNS9dPjR/77C/v/UiL76V6uOZmTIitv+1rW7oUt6les9kNGUC4jgUSVhRYBIAimHyn3/hO/VbFnI/0mz5w5o7IW40WLfHJCDdC8X39bmUDWiI2ZXqKZ4/Ya1Yvgktyvgc4g3qCcghuycQu9KpFCxIGUPjdbhw/dtUUGWSIdCSOnBddv4uLFi+Xl5a2trfpsFIGnsWQZWWWmt7dXmTwg0/IfjsbXkwohgbiM6EvhpPztQIsAEBCXnuJatbnyr+k3eenSJWVfzMztVnctoneK6JFFQMbh9tUY+NDayIDRxyZckkcnCdIf+iKOi+eZq1tj1UJ/6V+TdEiGTehVW/HEH5FEQeXFcq13Z7idkuDj66EP3GFGDDWQ3MIZZL9rpLq6mv/16Gh8xWyP/OKAWmc7tCOt6buM6J0iTGrfTtFpEV7joL29Xe0DEBQLwzR9X/zv9IN0irGYCCHloEXQKZIDuKl2Wniy/Zl4gWDxIkToenxNG7EO2dB/4T4e6601/158hVsRKIZ2mccMvepyzXrnhyyWq5enbf/IEnoENXh1dXW8nZ2ukY6OjqqqKvobi8X4/9I1qDxvyG/5dLfN9P4wkILLCH8s8alP8zitD1xRUeFrPKu4tIgMj9Ff2/mWAKSODNMc/9rZqr9K9OXqK2gk0yKRqRg6RTKF3jfgnqxeopz0HgUO70FfrkB2yqVGnUraNe0J0m6kHbEO2fhKiy9mUehV/Zo5kcSRvhBJxu2Ia0uqSFQPatjohZu3qQKfm7NZrz9A5HWfLmBgYID/78GDB1W2N8rPP+Df8oHWfmUKGbrLyPi4zbLDBnp5et3SO0VkHS7BaS6hLcWlRfQ1DmwXOAUgLaipm6+RY8f+8XDv/CK9ujfffDXtrkXo/Ykrr9fLu5UJpIPtmIJLovZVX2Po+t+bBSg1/97jjv+c2OXytuExbHWG3qjbFggE/b9wklvrr1k0mmOb6EN7sEdCr07u/d8SWcY1Z/h22traqLWTZSZramq4Avc1VpIC+uu+rDDltz+moX2Qf85GVNZQIa4wXuYo6W0oqTTduZhySY6Ul5ezhb41X4tUFJcWkbExIp3ZRwDYExsbP/o/cb081/y/Pp66b+vNN9fwjyb3/HcTVT9UR2lIp25Jc58yhQ1x7aRbI+0V+OBFgFjbY5dkqBCBjJTlNHxDiXIdwmPE09W/Tnhm0GclCytSykDLvYhbqxMzgQ2nk5mh+DIu5/4gcdd2n9XcQBMJkejO/yFhtL3m+c6P2On/NPTuU1R+cPXS6OndnDN5fh8bOdlm6UYrVGx4/dNSQBpO2wUWrEyeLR/+4Hsu57fF8IGQSMp+V4BquTXKP+fQhjsjenp6+O6qqqqUyRm9DdVJ34OnuLQI3ab65Hbvbm5uVlYAgqP281/Gjv3jRN3Nyb4l+284TquOrDze1pu8vzRdeMyCJAU1SCQvnJwZqUn78l/GS9KrP/01XDvp1sKpSKiB1K+Tk5Pg8EjSHoVT31DeEnrTTp8we2boj0GmhUgK0CdmfLmX/2xyd6JXibbnZhxnfg6uejKhOd58Qhnf/qYYXbLEaIVPKwVk4cnubk8dh/xfXM5vi5MPhMd/KoQ/3BkRi8WkM6OvL8krkN6GCoG47xSRFtEHuoi6ujqVAUBARKNRerTOVv2VVN8q8QoaRkXPSYsGcePRBNdcL27rmI5ldjg87uhgKCS6PKuqsHtdtkl0KouuyiV0F3J3wbb6M0Pxro7G3473hZCykU+A0nx4DFWMsB3f4URZYYbkmnHBx78W3fk/UKMeu9+pyixGFq+RNDt0V6avG8k2iyzqXBr6aWfH4i4XMsvXaZ6ajv5fbM9vi3ViiOB3ZF/CnTmlZTs7q7/SnpkcIQrPKRIBo7ehMm5FBDKtqYi0iD7QRZSWlmba+wkUG+Ljdv3IXyjlQX973lbZ80x3nhx+9/enKxfiTmrRICpbHnEN9d4X/mfQ3F4Tf//26CQYsyya6itxt//QMbMxDi7KVrqQqOJLavtTZckEepttq3is/ShpdsxkjcXf7HTtv+IWfar1kCqwGPEskTTTc5Eedd6Ou75SwyxRXLUsSWTkU+nop40NxH8UMqBw6tQpLuOC/l9sz2+LdIrI0IyQwmqUEu7MJeXcm+TRo0d8g2VlZS6zOqQN5aDSbW1tlZWV3gPRulNEWsQ60DU0lJFFHEDRIuEQmpqalMkCz5McfPP/mDv636q6nhrOed6qucl1U0O7z/cM3UN2vE0ZXehbiOl59vfjx5KwIHkhZ7Cmq38db5x4NMdoSmlXFnPXdFUu0TtFMt3w3/1N/K5dJKDVMyMvoEvlD5BS96sTRzdyi54INLKY6e5mLiBp6vLh6OndvB2pjU8iG6t4w5oliYx8Kh39tNM34m2etJr19fVcxgX9v9ie34reKUKv+zKLhygvtwR88wC9YHiRI3oyOkui07MkVlZU3jjWmcEflzgFX79uLgwuSBsqrsQBkpdahKQxL4VaUVFx5Ury8LodHR1Ukj9EgqQfb9y8eVOVACAIurq6+NFy8XGTmA3RmmcT1f14mz6bN3mURsOBlP7KqajlIx5Vxp08nMZN5K1Xj+lJzQ/ZrQM3SftaqLk13v6bfsdmuCdrSKcI3Q5IGU1mTbUe4od2rPR1zjSQp1oS6YDx6lW8zWFbI/UfWrPEs5WMfCod/bQch56HQQkv6+XKf6Fke36hu7tbbyMI9oHQu0YyMaxPIkMkiJG4s6T93virpV1sofohcytC6CFuqXmlRpbt+jJA4iwiuQGSl1pEFBxBGtY9kP7k5KQx+CdjY36dogFw59KlS/xouQTTWxQ/Stbp6FrmNpuXGnUWHPTX1oFUT5QV6XDzltAHaIJa+LTfbmEUuoZAl2rzhN5FlEf9EOEmdr+TH9rhDc8o02Imjm/iAuLBSspjZPMPeJu7NKSXQs8aK3lNjHwqHTktJfrhsFH6KpIOsst/oWR7fsZwJWTYB6KpqUntexsVSoG9Fx567zhpuZWpmcyRSMT4EOgTkBEx4tatW1KAFKE6LDjyUot0dHToXWfugfSpsCo3L1wuXLggHeneV38GwAsy0uzy3iBaJHJoXaIJP/n1rSc6uLoxZ/PSi6nRV+GUJMjVl/8yYaRkyBEZoLEuupYOtlG26MqdpudkAvpf0kXUbt9qghSYm5nih9ZpQor0QIzueoU3xireGFy9lLfnIvHBhanLh61Zk2fLxcin0tE7NkiXsNF7iBH5L5Rsz88YroRlZWUSNFnvLWhr8zD6GQQunSXl5x+oQhmAblBvWOvr62UMgRAhkqGgt3mpRRgZu3IPpC+dbDIda3x8nC0VFRVsASAQJPKPi7+9aBE19L6gG/ZV/QNXN4tm87pMY9EdSHlaB+0aZSTpcsS6aGrgjF1MaILhRmXMNCREJHoHL4kCgmNo7Xf4uWUfUgPp3pho2MAbw+uf5o3BVU9ymZmei9YsMdqG/pPTUpKODY8hRkgAybGUbM/PuLhB6Avi3Lp1S1kzj3SWPP9Z+0dH79S1DXDlsLo2+MERg8GF4LZOBDJrxkoeaxERs6Q2lMmCfKwk93T3YJlOHYl4WrLo6tWr1dXVWMUGuCNvbC7RlE0tsiAgHh7+Z89vbl00m1cXIm1/GncBYQcR+ms7+jAzFBclcgi1x3oUc5IgfTsXnTOoARpbri5EB8+c4tEhTaY7zciSKCAgpMNjuvOkMmmMbHxOcnljYOUS3hj59Hkuk5hhq2WJcXjdd7mYjpyWkloTx3OIEVmEkpPt+RkXNwh9QZwMtcFe6BuZYi1C9YMyZZITJ06oe57vDpFlgIgMdYoQ/rRIV1fXswusWGHvFrd27VpV4tlnJY6bHLh8+XK2MLTrJdabLfogn5PLyIULF7iAMdQn769eFoAWz2r6m8KcLlAk6M+Jy0i2qUU0AfF+yYeJ2by6aNB7NdzRxQcHsdDPoydfi66lQO969Y86/rOyZAh2ppH7ouT94wKeidSu4ec22rRdmTSG1nybc43eCErjVW+pQo8f22bxtu3oj5yWEokhNnoMMSKjP5ycRpf0dsTWDYKHLaiMy2TXLLBsZyfLkSwsU6V3jejLABGZE2Q+tAjrCZEOtE2yg7eFxsZG0Shbt26lMjzwRiVplzYolzfYaD2DL0TP2rqMUHsgrtFGb55oFC9DgHoMPr9x90DxIM+J+6uDTI+sXP/LpmvDcdOCgGir/TdqNm9qQoSIdCQOpG3GGqE8C6316Dn1v879gbKkyZ118cnDl+dDxI5djOsPq3uKhD0FQSNN+3j1KmXS4Cxu7/Wg75R07WKbJca5qOn/ISUpySCLOFS6O5NGDq3TD6dkPT9hxMywQtKH2pGLC8vS5or362+zFvE94T8lWlpa9u7dK2qPbp80WUY/BB9ahDSE3qvBUkPtOCDahQ4kmUIbdBRBG2Q3+khSwN1lRIb6rLO/ZEVBvioXjBh8cHcFthhhCZTVDtEiu957m2qWh6PTt24nYjkM36lMXYgw1/8+3kgbs3kn78Tb8ovfii9Q4rJ+bIDMRhMut97dV2UpO7oFunc5UP9MnFL7M/ARyRzi2CFjLoKMswyt/Q7t6gMrlPQxHdssMcYeLJoErkdNpcQnJzyGGJFBJUnG+RkXZ5FQUf2VCoT4yQmvAWTzCx9aZMWKFXo3BrXi0u1hC/ejcGMv3SG0QSqEs1yO9Yjh/0zqlcONGJPFrV15eqcTU15ezscSHR0ddJHcZaJ3ihDU3mCYBugY0WuSjqc273iPa0bWIiXNfSsqb5yoXgj4oU/WzULvRUYh3cM34tF91So4mudXctHXk7NNTg40IDhk8GVw9VJlWkA8M7jrwhABsUeJME62WQlPlO5Fa4QZDh/yfz2GGBle910+UDbk/FevXtV/s0wmYmYESPu9cdYihbqCdwa1CIkPKsDbLD4I7gtJx01ExzovnHbpSTWM1qlfc3NzRhlmfHxcRDdB21KsurqaNzBMAwTrw+beKbLzzH2SIFwzblv7DtUsHN/spc+aBxv+x0Xta74LEcKX++pNu6XsjHTtb1XvjosDL8gYxhxdQZ+sS7v6RFzDS8M2S4wSQYSR00pSGVqIkVjMPvaXPgnZen595qqQiZgZARKdnvURCzEPyZQWYfFhKzjiTiJr1xrqxBf9/f0kbJljx46pR2mBI0eOqK15aFcVXQwduGfPHlVoASpMWlvtaBFaSYicOnWKt2tqatQpQNGjO5zT40Q/CpVhx4e18apEtMinH/2GaxZOXzT8RhrdwXPfV8fkMw9afsq3M/Lld5XJgRtX6ueO/yP93nsvbZxpTKwQG7eff5ELg1zx4MM/50f3RmO1Ms1zq1oFVO3d8zPavVOeEBwP3lv01dtmiZE22MLIaSVdu3SWs/bt28c/uosXL7LF4HrzF3wI/Rfj/F999RUfq9PQ0MAHhpk396kQRB8f6lCmcEMttWqzPZApLUJZPChjIG4idCoZtbEt6Zdz587xUyXzdb2Hyevt7eVDbDtLiJ6eHonfSn8xTAMYep7lCVEmB/ZeeMj1iGiRU5W72ELptbJr8am8t9ckWdwkvxD3Vbqpvp2m18it1fHVcNgpRCbC6EvZ5elKLoVLooNhPqa7ENGCu9OuzBSjZMSMt80SI52fLYycVpKENqHWh393TjE/El01pa8b56dD+Njjx49z4Xyh5dYoVxfLd12NzRbawq4+tAipB70PgwSEU5cGCRFdtQjcF8LyhY6l54k26DyBaJGbN2/yEyaQLlN5yZibm9O7QwwqKiq4J1B6XDBMAwhxV5InxIm+kSnpXxV/kYEz+8R46U4Wg5NmDd19ldLJr8c1By9So7uGNP9eohg0R4iJNm3nR5fXuhPG961kO6/iqy9oN9GwgcswtllilFm7jJxW4pFwLHlCHE6dJkKKhzj9F+P8slBDRmeFZALSHz8sUWvTfHljRFkLBR9ahDtCZNhF7/kwtm2FCEH6Qw6X7hDasB3K8cvo6Cg/YYL7yL1Ba2urOmx+REaedUKWrSEJwhbMpgGEvJwlXdjokxN3uQZ574vbkQPvcM1Ir2t7Lzx8cVuHGfS9kHhYntAcSdNlLGUXaiSOmSEaRre8xHZelz/24BrvUmJ1IthmiXFk43NsYeS04nzKy+MR8ubptCD2V3vW7Nv28ZkPX6H/YpxfxlVD7qxqi3SvZiEAa5bxoUUIEg0kNRi9M4N2WU+QkXMF6TshgaJrlHT8RZzQXaPLysqSrpyko68MdOv4533r/0JGa3rf/3/4Ub7/839bsmsnG3vW/kf3hR/TYfL8PmMifobS4OqlmbuLwkZfc3FkxO0dRe8U6emPytseaRFVorCR6cSG8qDU/syiBX7RKRJuYgO3+dGV6bWMaIXZofh007noKO9Sit3v5DKMbZZutE0SCV5+MgMDA/zTc1o7t3S3qqg7zzfq56cab//nnLN7aCjJEvxSD8fryeZSZbWDSg6vfzoLdenA+LRUJryQb8HgT4vYQiokQD2RDrojYUNDg7J6prW1lRQMveMOvv1Nev7OfPRfqLE5/Zsfy3NM6fDGn/P5acMpkF/68AVkJ2XuLgobWXPRPcgBIZ0i79fHR7uLTosIJDUu/5kajjn5deUWw04hp75ROF4yBQ1VF/z0qv15pL56PKtGKlWxlUvmpsxFNmyz5LQ2aeUSWeNGlsebmpriXx/V2GzR0QO3l5aWDhIL53+48gm2U92e9GVV1hymRGeYm3FcEJ5LUhm1n0lKzz7g+oTSppN3o9OzKiPPCUCLkBBx8mDNMvqaiimPBc6O9Mnzp/cocrr5iz9R/2D3btrm94Bg0S8gOykTd1HwWNdctCUyFdM7RchSvFoE5D+yQp5UGlJfDa35NluIaNP2wdVLI3bL9NtmkciwlyMrl0QOrZs8v4939aP27t3LP0DrXFx9QTuiurp69NhmPv/NX/57NrqsYsaQVFLXsJCoOVB5i9FLZqcu1RfyfbW0q/2e4+pXeUQAWiQ89PX18XNG2EaF98J01yl5sKa+OsgbwxsS649L78vhjT+fud2qrMEhFzC6bZkyZYbEKG8G7qKwoRctfgborct9lYrT3SNcZUiEItEiEnkJgHzBWmlkob6aaj3E/4J+O8qkrSlmXa33yuU2zhJ4TuXIp89fXPsCW5wcTQQZkJI0dfmwyluMXjJrdWlJc5/IET0t33XVNkj8gdZ+qoKyEz8+NQpKi+ihz1KedhttLpUHS5aDGt3xssrW2iHiwQX1uQWIXEDkwDvKlBnGPlfDT+IRBjzitOaiFRmgEQfVhBZZmBQAQL5grTSyUF/RL4X/he4zK6v1WudLNh49zFmHtq7jDWJqaoqE1KmP/o53ky66Lv9UUvTkFpW3GL1kNuvSllujMq3GSG29i3pKJGarNSs8FJQWIVpbW+lV1XZ5Go+I/qA0svkHvKHrceLo55/xA32sao8yBUdiPcwMe0LJPzKiBQB3XNZctEKvKUYVAC0C8hdrpZGF+krGyklMKJPmsGWt7Wtr9nPW7U3LZb37O3fu0E+vbtM7vJt0hXbpFJcZxUYrICRKZr0ujUzFNp28+8IWNQos6fXybglAEp2efbU0IVn0rFBRaFokfUZ3vCwPlvhkqeXdF+hr2MoPNDEY9BrKcgH6mlKZgGS+7d0Bd1zWXDS48WiCf/8vbuuIhzKbR94soUVA3mGtNLJQX80uXnuPkQCVR4+aXRGlCwG1h7Yuk+gMJFkiB94p37GZdyMR06nWQIKpyA1aFwVkpCSl3NalwxMzL23v5Dqn+qtHB1r75V1IT5SlDggT0CImMj9NT4banWzZLxNqkq6F5he5AAkymCHoLvgfOen9IoG0RdVC+FQrFQsLLgriMGRdc9GgskUtrfneF4mvUkbcoUVA3mGtNLJTX/G/GNSW5TMCSu3du5fjhcgkmn3bPh7dtkwCEtbW1g41fMrbn+9J3oUTObSO/+nE8U3W/64jJSnlvC6taxvgOkdc5iVRLcQb9Go0MB66FW2gRRYhKyoZyRgFpFZEJtSUlJS4x9z0ReICVi5Rpowhw5y6N0yxoa/174LMFNPLW9dcdHoR0V3GoEVA/mJUGlmrrxLThjWsS9wNDg5Kt+XhT94mZaA7EV47otYXO1S+Q53CmbGKN/g/UuVPKoS3jUUBGSlJKed1aWx27kcV3Ub988KWdnovMrKopjrdHaLgrdAii7BO4uVkeEfHHt0kY80W5RgV4DCNXMDw+qeVKWPwXcT/lzZLqNg4c+YMf4nucJQCvby1P0yfaGckfV1NaBGQvxiVRtbqq8Rc4rHEcmttbW2GHKFfpUR2OL3+Ve6loNaNLYf2qcKnyxZFprdFfqdU+Y98+rxsq2wNKUkpDHVp5/2I1DxGPDQ9ixMHGggD0CKLIAksT5WejFnjHMjv6G9+yk92gLGE5QLGSl5Tpowh4QiH3n1KmYoMvZPDVlDSS1V1dTUXoA0qYy1/rHPIti+E0/OftRvVgdRcRkhKAMKPUWlkrb4SNWA7EkQ/Rv5VEhL4p/W9v4wcWke5+oIeTOued/lAF/RgsqRpeNs2JpA+rB+SurSubeDV0q69Fx6qfQ3KEp8SShyAMQxAiyxCPLOMpLI1Bt984tyvl/GTnXJcNStyAfwryjQSX0jtFxkunRwCVXPWrmC9/Ivb1ELenJIGZhYtkp2wSAAEi15pZK2+Gt22jP+RU2+ieHEJN3/579mTVFxGhFtb/46PckEfFUrcpl30NiNMtrKGm57+qFRZIekagRZZxHjVW/pTxclW6g6t/U7Hr/6cn2yXlswvcgGT5/cpUyaxRlEsHpJ2ighdXV1cTJDyd4cm5Sf9wpZ22xcRA2gRkNfolUbW6ivpmTBc9wT6Saof5zwlu3Y+XPkEx4zXXUYIynr04Z/zUU5IKFXV/XP5MO+Olb7OBQRreNZ8+V2/X69cWUPSNQItsgjpCZQ55ZRshwCpRbn99r/jh7umpkZZ0yYxMDm/4mWmkXYxa+ECw4OXThHh0qVL3DtSVlamr3hwvmeUf8/6TBl3oEVAXqNXGlmrrxJxTZxXTrh+/Xp5eTn9SEt27zrz4St64dbWVpYjJEQoS58bbIuEUk3qFqMHXeWUL3Wp3jWiO7TlCmiRRYi/tHQJxrftXKM5SsSeXTvirdnu3UFNpXF32A6cog296r1TxJ3qr9SsXe/r/uvj0MoEQP6gVxpZq68mjm7kfyTL47ng4t7Bdkpq3wGn6UKDltXvpKSkPKpLZZZv6dkHypQ7oEUSiFvW4KonRYZTYmdsAy4Q7FQa/QKUyRX3wBh+oVcKI5BGYeD+KaU5vvbR0Tv8Yz5x1WtdXMzjYqAASARaXfCi8FhfpYPt8nhOiBaxhl+TX5++SrAVCaUqlb/Tz1YPusopj8JYt9xS3brLd12NTMV2nrm/bGfcrZX+cjy06PQsGVdU3qhrG+BDMge0SIKZ2638MI1sfE53YrUNpccFgp1Ko1+AMjmjv9kHyPh4ISz5KCT9lNIUkT/Zd51/zDceTShTMqBFQF4jdeOoBCT1UF+lie3yeE6M7ooP0FCyOroOr3+as9x/fRJK1Sa8bNeiJaikpLj05jb0qi9is3Oyoo0+uYbTx8d69eDxmQ6PBi2SQBTuWMUbEl6Qkq3O5QLBTqXRL0CZnPEYGMMvra0F5Tji8ikZnh+pIStB0AuEMiUjoUVGvA7rABAeEnXjglOdl/oqTRKDJtryeE4kPFosXiwePeQklGpi2Z0D77Al2lzKFkZKiuuMF7UUHvZeeChqwz0d60ze9VvXNsA9K5Row1dvCrRIgkj9h/wwTRzfpI8C2o7/cYFgp9LoF6BMDgTl7iC32bFzJZ+tqqpK5eU/QX1KTsgkGnp7UCYPiBZR+wDkFXrdyMmLD0ea2C6P54SsaWrt/PC4GpQedJUtTssRS0kZusp56FVfDIxPS7R4Ug9f3ohHYt1pF7bxg4Y7fIgTwxMzRuD5F7d1qDwPQIskGCt9nR+mqcuHJbwgJVsFzQVkKk1QlO/YfO7Xy+gC1L9xwNccEBcSt7nhL9gFnejr6wvWEyXnBDjpWieFSTQEtAjIa/S6kVPS+ip9SFXw/6KfjzI54zIMKq4kVrdWHWv3yXTXKbYYUkNKSndR3oWxrmsbIA3x0dE7o9HEDIzy8w9ISXx8rFd8SmjXfYFfceTnROf07tFPFJEWmTy/b+jdp/hx4TS4eqm+zjU9Q2yP3e8UN1JKtiOLUkCm0gRI5/lG9W/sCPB1X7/Nxg3/wOes2/ROya6dvF0YpPAp0dMyvP5p92XQPU6isT54nFQ2AHmFXmlwyk4EYfl3Rr1tRbSI1UFV5uNEm0tdfuPWyW4ihijpF5BYGvB+pxQwkvsFO9UPRkp61zqTZ8uHP/ie9/KMxytxSnd+/uSm99dSfZjaMjdFpEUGVz1pfHaU9Dla4nzET7DadV7ziQuc/fXfBC5HZPUTW4LqFGHkrq//8v/m0xYSKTuF8NOiPx5WPE6iSfrgAZBfSKURTyuXuM9JCQr9d+T+87FdSI/RnVJdfuO2Z9DvWo7SSy76WBYnlwu2rR9sk8tJDPiqvJdnjACyKaQHP/ujZTs7p2Nu3SdO5KUWIdlluwKIzESyYo2OJ4mVrxTgQHvExPFN9N24xDaOF3B++FJID1c+UbltAzei1dXVMzMz6j9pBO4Dod9F1db1fGbm5i/+RK4tabLtPcpT9KfF6b6i07Ovl6tFL10m0dg/eCuX5JGzPQAGiUpj5RIvk2wDgd719frWpcJRZexeI2UkRfw8KBmnsrYFjFHh01GzI32q5JpvWwsYyfaCXRom2+Ry14JclcfyjH5UOqny2CV1Rp/kpRaRNsA22S4IYsTRI4wRQWuBnEDywrr6iS2Z8IGQJS4Jj+f36JeeX+ixFG3vq/3euMx2e/6zdpdJNCF5rgAoDJJXOLMxLmDrWSJuH+LfSsk4lftvVr+AhBPJtmUq24L7BXusH3xVs3JVHsszLvcyGo1J8+qUvlz1F3z4/fYL6jCf5KUWqWsbkLmUtskqR8T3WzyPjJCj1gK5wrr6iS2BdIoYRCIRv50u+RW5lUP3yKwzp7RyfQXfFKV3Pthu5BrJfQ2a8DxXABQASSucWVcvV+skIErGqdx/s/oFOE2u0XG/YI/1g69qVq7KY3nG/V6oljPmyBipfvVyv//RIC+1iBP6TCRDjljj6CUWOJifQW4tkENk9RNbAgmM4URra2t5ebn3cCnGxxhm9M4M9/TrX3/KN0XpN+s+NHIlyRQ4F0L1XAGQ7yStcESL2EZgk1w9Gady/83qFyDbLl6i7hfssX7wVc1KYY/lGS/34oKvK7SloLQIocsRSi9t7yQLvQ1b4+hJ9EC2WAsEyIHW/h9VdDv5suQ1xscYFPSJ2boEJU3yjasTpXSqLWt/xTdFadd7bxu5lKxT4JzI6HMFQLGRtMKZ6b3CBUbtIpHMRYY4V0/Gqdx/s/oFjEowVkuwecH9gj3WD76qWbkqj+UZL/fiQvoNQaFpEcKQI5Tobfj2XtW6iGoTJyYWpNZAe0GhXw9tK2uhYHyMgWD9Bv0m+sbb78WD2VtP5aUzQx6G9O8rc88VAEVI0gonMerh4MPBuXoyTuX+m9UvIDGhd8AxwpD7BXusH3xVs3JVHsszXu7FhfQbggLUIsTeCw8Nh5JD7/wtf1Iy/C8+AU2//L5tAUn0Vt3Q7tU541jnUGov9HmajI8xzMl7Z4buY+8+jpsUawBHAEDKJHWwmO5u5gJOjaI1hIZxKvffbOICFvxJXeI+EEm8T7zVDx7dSghZUthjeSZxlOu9uOD9Cp0oTC0itNwa5bV/xMv3jY/2c8v02m8a2HLprT+zLWAkOpU6qTP0Lm4cJUkWUSuwZHyMAabU+pDkG0/nVIlaJu35L74c4AEA7kjUV6cfZtIXdImEJsk4lftvVi5A4oIMr39a5dnhfsEe64ekdy1IsHyP5Rk5yv1eXPB+hU4UuBYhIlOxTSfvdr35bf6kXtl4nNunv/5E6bien/8x7V61FDAStXB0KnVSO6LTs7aukS9saeepFtbemgJIxscYSJJPLDX4G+ePOrVT6Z2cRowBv8ipvE/0BwA4IVFfnX6YokUiDqGh9DcNTsap3H+z1rCzYyWvqTw73C/YY/2Q9K6FqfajXNJjeUaOcr8XF7xfoROFr0UY9zh68W27AgQ1bNb3bPe0bGenl4GAgkH/GAsDI/6gsqaE03MFAEgN9wonqTfo6K5XuICeVN48SX+zRkAzJ9EjuFyw9/rBYzUrPqSSVIYrclTSe3EhzYagKLSIUxw96ayTHipbTSeLA3lMSV0jCwz5GAvj1d8aCTHl+3J68AAAKeNe4ZAE4dwJh9WDxxeWx9OTnMrLb9YY5Zk8v09lOOB0wb7qB4/V7HjVW1xMkpfqS45Kei8upNkQFIUWcYptJ511MsnbaazrQGv/S9uTBMii9MKW9sqWApy4606BuUTI0yIp5fvyGFQRAOAd9wpnomED5046rMQbqf+QC+hJTuXlN2uM8sz0JInG5HTBvuoHj9XsyKfPc7GBlUu8lGfkqKT34kKaDUFRaBEnF18JZidxWlL2AS5mfMUEDD/ytEhK+b7S9y0HABi4VzjS7eGkRaTjRE9yKi+/WbkATnMRt6UxCacL9lU/eKxmB1cvVefctsxLeUaOSnovLqTZEBSFFnGKbScSRNYmcHK9Bi6kH3EvVMjTIinl+/IYVBEA4B33Cke0iFPMLnFu1ZOcystvVi6A0uCqJ5XVGacL9lU/eKlmxYGUrsp7tawfpUwp4f0/2uJPi3R1dT27wIoVK5R1MbZlxLh8+XK2MLRbVVWldjKGkzeTOOyIA1HKMeOKmfQj7oUKeVrEFSvl+/IYVBEA4B33CkdcU6dvnFOmxUxdPswFKFl/415+s7p/qG2keQOnC/ZVP3ipZmdut3IZuirv1bJ+lDKlhPf/aIsPLcJ6QqQDba9du5a3Bacy9Hfr1q20QeqEN9jIuZnGKbadVSCnJuiKHPkYC+PtX54WGUNN+b6cHjwAQMq4VzgJrwUH1wd9ENb6G/fym9UbjrGKN5TVGacL9lU/eKlmpaOFrsp7tawfpUwp4f0/2uJDi5CG0Hs1aJekhtpZwKkMGRsbG9lC0AbpFaOPJHM4xbbTH0rbAsALvkY9w488LdLlmPJ9OT14AICUca9wZMDdaTaHROWiZP2Ne/nN6g2H02wdHacL9lU/eKlmxS2Xrsp7tawfpUwp4f0/2uJDi6xYsULvxiBtQTqja/GCsU5lpDuENkiFcPeJcWzmcHRj1h5K2wLAC+lH3AsV8rSIzE/5vtJ0LAcAWHGvcJLOLCU7F6Bk/Y17+c3qDcfU5cPK6ozTBfuqH7xUs2Olr3MZuirv1bJ+lDKlhPf/aEuWtAhBGwT3hdDfdNxEploPDVnWFPCSjKdTfHacCgAvWD/Gwkix+52GJeWE5wqAoPBY4cxNRdQBFqSMy2/c5TerXwCdQVmdSXrBXuoHX9UsXVUK1bKXe3HB+I+Dq5dGT+9WeR7IkhZR+/NQAcJQJ74YeD8Rott76v/Zv7pqYeDnf+ReAHhB/xgLI/HDEMh94bkCIFiS/zB//keqqB39b3+Lyrj8xpP+ZvkoKtZ15ZIyueJywd7rh+R3PZ/kqjyW5+T9Xlww/+Nb/1a12R7IthYRNxEqJqM2vAEAAACAIsSHFiH1oPdhkICwdmm4l+G+EJYmZCelQhtUhpjPBwAAAEDR4UOLcCeHPl9XNIRsu5QhSH9IlnSH0EY6viMAAAAAyGt8aBGCRAPJC0YXGbQresKpzNp51I5z9DMAAAAAFBX+tIgtJD6gJwAAAACQGgFoERIihgcrAAAAAIBHAtAiAAAAAAApY6NFiP7+ft4FAAAXent7udLQQQUCAPDI/fv3ud54/Pjx/w/X5Alz6SVelAAAAABJRU5ErkJggg==\n", "text/plain": ["<IPython.core.display.Image object>"]}, "execution_count": 30, "metadata": {}, "output_type": "execute_result"}], "source": ["from pyquickhelper.helpgen import NbImage\n", "NbImage(\"images/tb8_dis_hor.png\")"]}, {"cell_type": "markdown", "metadata": {}, "source": ["## Exercice 5 : zones de travail\n", "\n", "On souhaite d\u00e9terminer si une station se situe plut\u00f4t dans une zone de travail ou plut\u00f4t dans une zone de r\u00e9sidence. On part de l'hypoth\u00e8se que, dans une zone de travail, les gens arrivent en v\u00e9lib et repartent en v\u00e9lib. C'est sans doute le cas de la station 8003. Les v\u00e9los seront plut\u00f4t disponibles dans la journ\u00e9e. A l'oppos\u00e9, dans une zone de r\u00e9sidence, les v\u00e9los seront disponibles plut\u00f4t la nuit. Comment faire \u00e0 partir de la distribution des v\u00e9los disponibles construite \u00e0 la question pr\u00e9c\u00e9dente ?\n", "\n", "On consid\u00e8re que la plage diurne s'\u00e9tend de 10h \u00e0 16h. Vous trouverez une illustration du r\u00e9sultat dans cet [article](http://www.xavierdupre.fr/blog/2013-09-26_nojs.html).\n"]}, {"cell_type": "code", "execution_count": 30, "metadata": {}, "outputs": [], "source": []}, {"cell_type": "markdown", "metadata": {}, "source": ["## Exercice 6 : lattitude, longitude\n", "\n", "On repart de la requ\u00eate pr\u00e9c\u00e9dente pour effectuer un JOIN avec la table *stations* pour r\u00e9cup\u00e9rer les coordonn\u00e9es (lat, long). Apr\u00e8s un copier/coller dans Excel, on peut situer les zones de travail sur la r\u00e9gion parisienne. "]}, {"cell_type": "code", "execution_count": 31, "metadata": {}, "outputs": [], "source": []}, {"cell_type": "code", "execution_count": 32, "metadata": {}, "outputs": [], "source": ["%SQL_close"]}, {"cell_type": "markdown", "metadata": {}, "source": ["## Sans %%SQL\n", "\n", "La commande magique ``%%SQL`` s'appuie sur le module [sqlite3](https://docs.python.org/3/library/sqlite3.html). On peut faire sans."]}, {"cell_type": "code", "execution_count": 33, "metadata": {}, "outputs": [{"name": "stdout", "output_type": "stream", "text": ["('QUAI ANATOLE FRANCE - PONT SOLFERINO - 75007 PARIS', 'Paris', 48.86138, 2.32442, '00901 - PORT SOLF\u00c9RINO (STATION MOBILE)', 901)\n", "(\"FETE DE L'OH (BERCY) - QUAI MAURIAC ANG PONT DE BERCY - 75013 PARIS\", 'Paris', 48.8371336894515, 2.37434055460561, '00903 - QUAI MAURIAC  / PONT DE BERCY', 903)\n", "('ECOLE MILITAIRE-AVENUE DE LA MOTTE PICQUET - 75007 PARIS', 'Paris', 48.85213620522547, 2.301961227213259, '00904 - PLACE JOFFRE / ECOLE MILITAIRE', 904)\n", "(\"QUAI D'ORSAY - CONCORDE - 75007 PARIS\", 'Paris', 48.86314, 2.31669, '00905 - CONCORDE/BERGES DE SEINE (STATION MOBILE)', 905)\n", "(\"GARDE DE L'EST-PARVIS GARE DE L'EST - 75010 PARIS\", 'Paris', 48.876419813641114, 2.358630064544601, \"00906 - GARE DE L'EST\", 906)\n", "(\"QUAI D'ORSAY - PORT DU GROS CAILLOU - 75007 PARIS\", 'Paris', 48.86288, 2.30652, '00908 - PORT DU GROS CAILLOU (STATION MOBILE)', 908)\n", "(\"41 QUAI DE L'HORLOGE - 75001 PARIS\", 'Paris', 48.857091635218225, 2.341747995157864, '01001 - ILE DE LA CITE PONT NEUF', 1001)\n"]}], "source": ["import sqlite3\n", "conn = sqlite3.connect(\"td8_velib.db3\")       # on ouvre une connexion sur la base de donn\u00e9es\n", "data = conn.execute(\"SELECT * FROM stations\") # on ex\u00e9cute une requ\u00eate SQL\n", "for i, d in enumerate(data):                  # on affiche le r\u00e9sultat\n", "    print(d)\n", "    if i > 5:\n", "        break\n", "conn.close()"]}, {"cell_type": "code", "execution_count": 34, "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}