{ "cells": [ { "cell_type": "markdown", "metadata": {}, "source": [ "# Split a Table into Groups\n", "\n", "The _groupby_ function is one of the most powerful and useful functions for dataframes in Pandas. The main flow of the _groupby_ function is as follows:\n", "- **split** a large dataframe table into groups based on some values or categories in some of the columns, and then \n", "- **apply** some aggregation or other function on each group, and then \n", "- **combine** them back together into a single table. \n", "\n", "[![Open In Studio Lab](https://studiolab.sagemaker.aws/studiolab.svg)](https://studiolab.sagemaker.aws/import/github/aiola-lab/from-excel-to-pandas/blob/master/notebooks/03.02_group_by.ipynb)" ] }, { "cell_type": "code", "execution_count": 1, "metadata": {}, "outputs": [], "source": [ "import pandas as pd" ] }, { "cell_type": "markdown", "metadata": {}, "source": [ "## Loading data\n", "\n", "We will now start with another data from API from an open source [brewery-DB API](https://www.openbrewerydb.org/). We can analyze it to answer questions regarding states or other groups in the data." ] }, { "cell_type": "code", "execution_count": 2, "metadata": {}, "outputs": [ { "data": { "text/plain": [ "" ] }, "execution_count": 2, "metadata": {}, "output_type": "execute_result" } ], "source": [ "open_brewery_api_url = 'https://api.openbrewerydb.org/breweries?per_page=50'\n", "\n", "import requests\n", "response = requests.get(open_brewery_api_url)\n", "response" ] }, { "cell_type": "code", "execution_count": 3, "metadata": {}, "outputs": [ { "data": { "text/html": [ "
\n", "\n", "\n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", "
idnamebrewery_typestreetaddress_2address_3citystatecounty_provincepostal_codecountrylongitudelatitudephonewebsite_urlupdated_atcreated_at
010-56-brewing-company-knox10-56 Brewing Companymicro400 Brown CirNoneNoneKnoxIndianaNone46534United States-86.62795441.2897156308165790None2021-10-23T02:24:55.243Z2021-10-23T02:24:55.243Z
110-barrel-brewing-co-bend-110 Barrel Brewing Colarge62970 18th StNoneNoneBendOregonNone97701-9847United StatesNoneNone5415851007http://www.10barrel.com2021-10-23T02:24:55.243Z2021-10-23T02:24:55.243Z
210-barrel-brewing-co-bend-210 Barrel Brewing Colarge1135 NW Galveston Ave Ste BNoneNoneBendOregonNone97703-2465United StatesNoneNone5415851007None2021-10-23T02:24:55.243Z2021-10-23T02:24:55.243Z
310-barrel-brewing-co-bend-pub-bend10 Barrel Brewing Co - Bend Publarge62950 NE 18th StNoneNoneBendOregonNone97701United States-121.280953644.09121095415851007None2021-10-23T02:24:55.243Z2021-10-23T02:24:55.243Z
410-barrel-brewing-co-boise-boise10 Barrel Brewing Co - Boiselarge826 W Bannock StNoneNoneBoiseIdahoNone83702-5857United States-116.20292943.6185162083445870http://www.10barrel.com2021-10-23T02:24:55.243Z2021-10-23T02:24:55.243Z
\n", "
" ], "text/plain": [ " id name \\\n", "0 10-56-brewing-company-knox 10-56 Brewing Company \n", "1 10-barrel-brewing-co-bend-1 10 Barrel Brewing Co \n", "2 10-barrel-brewing-co-bend-2 10 Barrel Brewing Co \n", "3 10-barrel-brewing-co-bend-pub-bend 10 Barrel Brewing Co - Bend Pub \n", "4 10-barrel-brewing-co-boise-boise 10 Barrel Brewing Co - Boise \n", "\n", " brewery_type street address_2 address_3 city \\\n", "0 micro 400 Brown Cir None None Knox \n", "1 large 62970 18th St None None Bend \n", "2 large 1135 NW Galveston Ave Ste B None None Bend \n", "3 large 62950 NE 18th St None None Bend \n", "4 large 826 W Bannock St None None Boise \n", "\n", " state county_province postal_code country longitude \\\n", "0 Indiana None 46534 United States -86.627954 \n", "1 Oregon None 97701-9847 United States None \n", "2 Oregon None 97703-2465 United States None \n", "3 Oregon None 97701 United States -121.2809536 \n", "4 Idaho None 83702-5857 United States -116.202929 \n", "\n", " latitude phone website_url updated_at \\\n", "0 41.289715 6308165790 None 2021-10-23T02:24:55.243Z \n", "1 None 5415851007 http://www.10barrel.com 2021-10-23T02:24:55.243Z \n", "2 None 5415851007 None 2021-10-23T02:24:55.243Z \n", "3 44.0912109 5415851007 None 2021-10-23T02:24:55.243Z \n", "4 43.618516 2083445870 http://www.10barrel.com 2021-10-23T02:24:55.243Z \n", "\n", " created_at \n", "0 2021-10-23T02:24:55.243Z \n", "1 2021-10-23T02:24:55.243Z \n", "2 2021-10-23T02:24:55.243Z \n", "3 2021-10-23T02:24:55.243Z \n", "4 2021-10-23T02:24:55.243Z " ] }, "execution_count": 3, "metadata": {}, "output_type": "execute_result" } ], "source": [ "brewery_list = (\n", " pd\n", " .json_normalize(\n", " response\n", " .json()\n", " )\n", ")\n", "\n", "brewery_list.head(5)" ] }, { "cell_type": "markdown", "metadata": {}, "source": [ "## Loading all pages\n", "\n", "If the number of results from an API is high, the API often uses pagination and returns a constant number of results in each page. \n", "* Start with an empty list of values\n", "* For each page between page 1 and page 150:\n", "* Read the data from the API with the page number\n", "* Add the data to the growing list\n", "* Finally, create a dataframe with the above whole list of breweries " ] }, { "cell_type": "code", "execution_count": 4, "metadata": {}, "outputs": [], "source": [ "api_dataset = [] \n", "\n", "#looping through and putting data to the list api_dataset\n", "for page in range (1, 150):\n", " response = requests.get(open_brewery_api_url + f\"&page={page}\").json()\n", " api_dataset.extend(response)\n", "\n", "breweries_data = pd.json_normalize(api_dataset)" ] }, { "cell_type": "markdown", "metadata": {}, "source": [ "### Saving the dataset\n", "\n", "We can save the dataset for later usage. We can save it in Excel format or CSV." ] }, { "cell_type": "code", "execution_count": 5, "metadata": {}, "outputs": [], "source": [ "breweries_data.to_csv('../data/us_breweries.csv', index=False)" ] }, { "cell_type": "markdown", "metadata": {}, "source": [ "## Counting Values\n", "\n", "The simplest aggregation function for each group is the _size_. How many breweries do we have in each state?\n", "* Start with the brewery table above\n", "* Group the rows by _state_\n", "* Count the size of each group" ] }, { "cell_type": "code", "execution_count": 6, "metadata": {}, "outputs": [ { "data": { "text/plain": [ "state\n", "Alabama 44\n", "Alaska 51\n", "Arizona 116\n", "Arkansas 43\n", "Bouche du Rhône 2\n", "California 855\n", "Colorado 391\n", "Connecticut 86\n", "Delaware 26\n", "District of Columbia 16\n", "Florida 290\n", "Georgia 96\n", "Hawaii 21\n", "Idaho 57\n", "Illinois 232\n", "Indiana 148\n", "Iowa 83\n", "Kansas 40\n", "Kentucky 53\n", "Louisiana 40\n", "MIssouri 1\n", "Maine 109\n", "Maryland 105\n", "Massachusetts 152\n", "Michigan 355\n", "Minnesota 161\n", "Mississippi 15\n", "Missouri 128\n", "Montana 87\n", "Nebraska 53\n", "Nevada 47\n", "New Hampshire 69\n", "New Jersey 110\n", "New Mexico 82\n", "New York 384\n", "North Carolina 277\n", "North Dakota 26\n", "Ohio 279\n", "Oklahoma 39\n", "Oregon 264\n", "Pennsylvania 317\n", "Rhode Island 21\n", "South Carolina 69\n", "South Dakota 40\n", "Tennessee 102\n", "Texas 315\n", "Utah 34\n", "Vermont 54\n", "Virginia 237\n", "Washington 437\n", "Washington 1\n", "West Virginia 28\n", "Wisconsin 199\n", "Wyoming 29\n", "dtype: int64" ] }, "execution_count": 6, "metadata": {}, "output_type": "execute_result" } ], "source": [ "(\n", " breweries_data\n", " .groupby('state')\n", " .size()\n", ")" ] }, { "cell_type": "markdown", "metadata": {}, "source": [ "## Sorting Values\n", "\n", "To sort the values is also simple with _sort_value()_ function\n", "* Start with the brewery table above\n", "* Group the rows by _state_\n", "* Count the size of each group\n", "* Sort the list of states and and the count of the breweries in descending order" ] }, { "cell_type": "code", "execution_count": 7, "metadata": {}, "outputs": [ { "data": { "text/plain": [ "state\n", "California 855\n", "Washington 437\n", "Colorado 391\n", "New York 384\n", "Michigan 355\n", "Pennsylvania 317\n", "Texas 315\n", "Florida 290\n", "Ohio 279\n", "North Carolina 277\n", "Oregon 264\n", "Virginia 237\n", "Illinois 232\n", "Wisconsin 199\n", "Minnesota 161\n", "Massachusetts 152\n", "Indiana 148\n", "Missouri 128\n", "Arizona 116\n", "New Jersey 110\n", "Maine 109\n", "Maryland 105\n", "Tennessee 102\n", "Georgia 96\n", "Montana 87\n", "Connecticut 86\n", "Iowa 83\n", "New Mexico 82\n", "South Carolina 69\n", "New Hampshire 69\n", "Idaho 57\n", "Vermont 54\n", "Kentucky 53\n", "Nebraska 53\n", "Alaska 51\n", "Nevada 47\n", "Alabama 44\n", "Arkansas 43\n", "Louisiana 40\n", "South Dakota 40\n", "Kansas 40\n", "Oklahoma 39\n", "Utah 34\n", "Wyoming 29\n", "West Virginia 28\n", "Delaware 26\n", "North Dakota 26\n", "Rhode Island 21\n", "Hawaii 21\n", "District of Columbia 16\n", "Mississippi 15\n", "Bouche du Rhône 2\n", "Washington 1\n", "MIssouri 1\n", "dtype: int64" ] }, "execution_count": 7, "metadata": {}, "output_type": "execute_result" } ], "source": [ "(\n", " breweries_data\n", " .groupby('state')\n", " .size()\n", " .sort_values(ascending=False)\n", ")" ] }, { "cell_type": "markdown", "metadata": {}, "source": [ "## Visualization of the data\n", "\n", "Let's do a simple visualization of the data without any map, just using the longitude and latitude values of each brewery. \n", "* Start with the brewery table above\n", "* Convert the longitude and latitude to be numeric values \n", "* Plot the results\n", "* as hexbin with longitude and latitudes as the coordinates \n" ] }, { "cell_type": "code", "execution_count": 8, "metadata": {}, "outputs": [ { "data": { "image/png": "iVBORw0KGgoAAAANSUhEUgAAAXEAAADtCAYAAABJVEUoAAAABHNCSVQICAgIfAhkiAAAAAlwSFlzAAALEgAACxIB0t1+/AAAADh0RVh0U29mdHdhcmUAbWF0cGxvdGxpYiB2ZXJzaW9uMy4xLjMsIGh0dHA6Ly9tYXRwbG90bGliLm9yZy+AADFEAAAgAElEQVR4nO29eZwkZ3nn+X0iIrOO7ur7UKtPCVpCDTopSdjCrBACJHG0bRCWwCDbmu3xDuzCMGsj/JldMx7zGTy7Y8Cf8cg0FljYYKEFY4QGjIWQLDAg0S0JoRM1Uksq9Vl9Vnd1VWVGPPtHRFRFZeURWZ2RlZH1fPsTXZmRb7zxxpsRv3jzjecQVcUwDMPIJ85cN8AwDMOYPSbihmEYOcZE3DAMI8eYiBuGYeQYE3HDMIwc4811A9KwYsUK3bRp01w3wzCMHLBz585hVV052+1lRa8yEaQrPFL6rqpeM9t9tYJciPimTZvYsWPHXDfDMIwcICIvnFYFEwFcvipd2e+9vOK09tUCciHihmEYbUVkrluQmq4T8dh5SVJ+CaqKAk7G5aWJNgWqTZWfzTE3Wz7LY7Y+yuaY52MftQQBXBPxtqOqlAPFj967KJ4jNb98VaUUKOXIYdURpehIzRNMVfF1qrygFBqUD4ByEJ6IAAUnfJJcq01B1Ka4vCeKK7WPIYiOOZ69S3PMyT5yomOoW14Vv4lj9hXK8cUNeHWOuWofCTiSvo9cUbyM+8hrcMzN9lGgUDqNPkpzXjTTR/G1kGUfzebaKQVT3uSNrp2Wkx8Nz7+IV56AMT7gBzpDqCrFOyZQGPPD8kV36gSrPAEn9wtMBIrEJzDhCVbtBIyJ11UKVeVFF1OOBLHyIqwUpspjrrwIKy+6yWMGxqM+Sl6ElcJU7ZgLTro+KgU6Q6iqCdNkH6mCpu8jX8HPuI8mWtRHSfE+nT6Kz4tKMe/WPoLEtdMWMRebTmkHtcS7klioJDoxKk/AauVjMXeEhvUnL0JHZp6w1YiFyiVsf6NN4ovQiUo2alPyBpamfHwRCopD4z6KL0JQ3JTlu6mPJEX5pFCl7dNYqFwJxazRMU/e5DPuo7THHPcR0TE31UdCuvMiIeZuVkIr5Mr4OrciXm0EUY80J/mM8pr+hqykOwmTNLqwq7Upy/JK821qpny39FEzhzCrPu2wPmr2mJvdx2zOi1KgOE6GI3IbiWePxV40DCMz8qPhefrRYBiG0QZi65Q0S9oqRVwReURE7o7enyUiD4rIsyLyVREpRut7ove7os83NarbRNwwDKMSkXRLej4MPJV4/2fAp1V1M3AEuDlafzNwRFVfCXw6KlcXE3HDMIxKJOWSpiqRdcDbgL+O3gtwFfC1qMjtwK9Hr7dG74k+f5M0mPifNyKuqpPOCYZhGDUJzajSLbBCRHYklm1VavwM8IdMPe9dDhxV1XL0fghYG71eC7wEEH1+LCpfk9w+2HQktO1uRGyKGD/9dlDcOo4G05CpB6g5es5hGF1P5tdj+h0Mq+pgzWpE3g4cUNWdInJlndrrSU1dpcutiHuOg1vHVrxSvGMCIIjsg2uJeTV9NzE3jM6gkHCuywQRcFs2SXEF8E4RuQ7oBRYRjsyXiIgXjbbXAXui8kPAemBIRDxgMXC43g5yPZ0iIhRdhx5HcKN1qsqEHzDm13cqib0qS36QiAHR+FnFbGxmDcM4PYRQvHscqRtyoKU7bMGcuKp+XFXXqeom4Abg+6r6PuA+4N1RsZuAb0av74reE33+fW0wD5xrEY8REQqRmAfapOPArB40G4bRLgqOUGyXeMe03jqlko8BHxWRXYRz3rdF628DlkfrPwrc0qii3E6nVENEomcNhmF0C5m519cjg12q6v3A/dHr54DLqpQZA65vpt6uEnHDMIzTJrZOyQkm4oZhGJXkR8NNxA3DMGaQo6QQmT7YFJElIvI1EXlaRJ4SkV8RkWUick8UM+AeEVnayn2aP49hdBdtd9JL+1CzQ6whsrZO+SzwT6r6KuBCwtgBtwD3RjED7iXF09c0BJFpYbNfd7Pnh6qCeX8aRtsYDxQ/aPM110K3+6zJTMRFZBHwBiLTGVWdUNWjTI8NkIwZMCti8Z6InH5cR+h1JdWvIUeg4KYzW4rd9pWp2OTmym8Y7aGk2l4xt5E4AGcDB4EvRiEY/1pEFgCrVXUvQPR3VbWNRWRbHI/g4MGDMz7XCvGu2Jai69QUc0egxw3L1HS/19CtJyneyVMnFvMAE3PDaBexmJeDZlNbNImTcukAsmyGB1wC3KqqFwMnaWLqRFW3q+qgqg6uXLlyxudpUrNVirmbEO9Go+9wpJ3OQzMp5oZhZE9ZM7zemguANedkKeJDwJCqPhi9/xqhqO8XkTUA0d8DGbaBaD8UXIdCCvFOYpJsGPMUE3FQ1X3ASyJybrTqTcCTTI8NkIwZYBiG0RnkaE48azvx/x34cpR66DngdwlvHHeKyM3AizTpYmoYhpEpHWR5koZMRVxVHwWqxdp9U5b7NQzDmD3pA211wpSreWy2GCVXN3EjY5IXeZrzIvmsrkN+rc9LTMTbQNrMPrMl+YsqjTGTTP5nCSSM6hd33dQtOnM71eg8tBNpBll2iRD6m6QhY0PHVORWxOPMPuVA8VOUT34ljbQ/FOSpLZzoCqv2hUmV8pX7sWtw/pBmXJE8L6qJd2VZE/MpBPAyz+yTYd0ZkFsRh9h0UPCaEHOYEtXKC6fW1xZ/oUkxryfeldgUS/czmx+FzfySjMW8Q6za2k5bxDu5vxyJeIf4HJ0eycw+zXR9s2EQJDIrcmTqtWHMhtn4qczXs639mX3C/aRZGtYk0isiD4nIz0TkCRH5T9H6vxGR50Xk0Wi5KFovIvIXIrJLRB4TkUsa7SPXI/FKwsw+9XNrtmhPWe/AMIyIucjs08JdjgNXqeoJESkAPxSR70Sf/YGqfq2i/LXA5mi5HLg1+luTrhJxwzCM0yV8/tAaFY+SHJ+I3haipd4wcyvwpWi7n0ThvNfE8aaq0RXTKYZhGC1DwBEn1QKsiAP1Rcu2GdWJuCLyKGGIkXsSoUg+GU2ZfFpEeqJ1a4GXEpsPRetqYiNxwzCMCpoYiQ+rajWHxklU1QcuEpElwDdE5DXAx4F9QBHYDnwM+BNqWKDWq7/rRuIWSNAwuou5iA6aReiUKJ/C/cA1qrpXQ8aBLzKV+X4IWJ/YbB2wp169XSPicXKITjC+r4bdW4wYjWLVB03EoVedKj/fQh7H8cPbddyC4Ei6pWFdIiujETgi0gdcDTydiOQqhIlxHo82uQv4QGSl8jrgWL35cOiC6ZQgshFvl3gLsxNks2fpbtKcF7EIlVUZLQf4qngi9Lkubg0Hk1i8T/o+pSAsv8BzKUTDrzzZM58OZQ37zRNti6lhC+tfA9wuIi5R8D9VvVtEvi8iKwlPnUeB34/Kfxu4DtgFjBIGDaxLbkW83eKdpJazUK1yxvyg6nkRZYUCKAfKqB+Kd0xZlZFyORJzZ5o5na/KaNmnVFH+WKmMK8ICz6HohGo+H8XcczKaSBBwWuRVpaqPARdXWX9VjfIKfLCZfeRWxOdKwJM06/lpzA+S50UAlIKAMT+o678QirmPJ0LBESb8YJp4V+Krcrzk44rPkmJh3p1zZQVXNZObVytNDNtBbkW8k8jP1220EwFQOFlOP9woqzLRRHlfw1+laeZnjfSYiBuGYeSWdrn3twYTccMwjCQWxdAwDCPf5EjDu8dOPG8EGvDInic5VRpLvU2zNsJZlzfS4TWpCM2WN1qLAI7jpFo6gUxH4iKyGxgBfKCsqoMisgz4KrAJ2A28R1WPNFt31pl9ssIPfL777A/4zA++wOFTx/Acl22X38gNF76D/kLvjPIamaglLRtEtaZdMYQPunxNJB9QxatTXhNmcPH7vD2h71QcgUUFl0Bh1PeZqHPS9jhCv+dO2pyfLPuM+bUfchad0GZ8LqL8zTVZH3GeHhS3Yzrljao6nHh/C3Cvqn5KRG6J3n+s2Uqbzewz15QDn+88cz+f+eEXGRk/yWjp1ORnt/747/jcT77CzZe+h/ddvJUFxf6q4h2jhCZWlWJeKd7J8iWdKeaV4l25jYn56RP3nSuwUKqLeVK84/ICLPRcFnjuDDEPxdureyPvVtqX2SebqrNgLubEtwJXRq9vJ4wl0LSIw+wz+7Sb7zzzL/zXf/kcJydGGa0yfXKqPA7A9ofu4K9/eie3XPn7vGPLm2k03ojFPA4Y09BjkCkxd1OepCbmrUNEpsQcKPkBRdep2bciMk3Mx/2AouNMJSWZRwhhcoh2jJDFrFOmocA/i4gCn1PV7cDqOBaAqu4VkVXVNoxCOm4D2LBhQ92dJMV8Iqg9upwrbvnOf8XXxreYsUjML1jzapr5wTib41XN12ijmxARXMBxnbTZYRCgN2X5bqPYJvFO0lyOsLklaxG/QlX3REJ9j4g8nXbDSPC3AwwODqbSqfZl9mmOZm8rbhsemMxDLeg4mhXk+SjgMDfz03nq60xFXFX3RH8PiMg3CMMt7o8zVUSRvA5k2QbDMIxmaVXslHaQ2ZBPRBaIyED8GngLYbjFu4CbomI3Ad/Mqg2GYRjNItEzh1YkSm4HWY7EVxNmsYj38xVV/ScR+Slwp4jcDLwIXJ9hGwzDMJpEEOkMG/A0ZCbiqvoccGGV9YeAN2W336xq7i40owhw7arfmD/MxbmUp3M3P7ebBnRiZh9VpRT4DK67EM/xUj3x7vV6+NELDxNoukwmqhrGrE559/KDgHLg42uAHzTurdiLMzY1TFs+IPxO0qCJxTAqaXdmHwBxnFRLJ5D72CmBKqUOMytUVXwNODh2gh/tf57XbrmW9Wsv5MFf3Me+oy/jB/4Mi5Ver4dl/Uv57Utv4OINF/PSyaMs6+lngddT1Y44PqF9ZfLG5aA1nSD8ICBA2XXsAM8eP4iivGJgBVuWrMYRZ0aA/bj+U77PyVKZAOh1XRYVvMgKqHp7xv2AkVKJsipFx2FRoYBbw0Ss2nc26WVa5TNj/tLOzD4iguO4mdXfanIr4p0s3vtPjfCj/c9zYGxk8rNVi8/kHZe+j4PH9/HQL+5jz5GX8AOfHq/IioXLef+lN3LJugsnT86yBhwYO4Eno9PEPCYp3jFBtC4Wc4AgyuX4i6P7+eXIMGWd2uoXxw/yy5Fhzlq4nFcvPQNPnEmxPVX2OVkuT9vHmO8z5vv0ui4DBQ9Hpn5bjPs+x0vlaVlrJoKA4fFxio7DQMHDc0LHljT2jSbmRjXaktkHWjYnLiK9wANAD6Hefk1V/1hEzgLuAJYBDwPvV9UJEekBvgS8FjgE/Jaq7q63j9yKeLnDBHzC9/nFsf08cXQfw2MnapZbuegM3jZ4I4dGDnD40C5et/FSLlx7fs2RRSzmroyypm8xrjgNp4xiMR8eO87w2AmeGzmEr9W38lXZNTLMcyOHuGDZmazpX8Kpsl+3b6fE3KHouJwsTxfvSiaCgEPjExQch+U9xQatn45iQm7MJMvMPi2OJz4OXKWqJ0SkAPxQRL4DfBT4tKreISJ/BdwM3Br9PaKqrxSRG4A/A36r3g5yK+KdhqL8YP8vU88DLx9YxW+c83oKKX+2+RowEfj0uOlHCPtGR3j+xHDjgoQj9r2jIwwUFuCkHIWM+UHdAE2VlILARNnIBa0aiUc5M+NRXSFaFLgKeG+0/nbgE4QivjV6DfA14L+LiGidBwKdMTNvGIbRKTRnJ75CRHYklm0zqhNxReRRQsfGe4BfAkdVtRwVGQLWRq/XAi8BRJ8fA5bXa66NxA3DMBJIc3biw6o6WK+AqvrARSKyBPgGcF61YpO7r/1ZVUzEDcMwKsjCOkVVj4rI/cDrgCUi4kWj7XXAnqjYELAeGBIRD1gMHK7b1pa3dB6ztn8JTsoZXwGKjtfU/HAUBTx1+aLj0e+lf5C4f3gfj//yidTlj548xoPP7CBIYW8OYUz1Z4/tpxx0atDg/GE29hkgEmbzSLM0rEpWRiNwRKQPuBp4CrgPeHdULBl+JBmW5N3A9+vNh0OOR+Kdltmn6Lhct34LpcDnJwd28/TR/QRVLi1B2LhwBRcsW0+P6wHCKX+cMX+85oXY6xZYUuzDk3h0UN8AL1DFD5SNC1ewfuFyDo2d4BfH93EyCnVbyYHhffzjt77Cjkd/jCsO5244h1tu+gMuf/VlVcsfHjnC5/7pi/zt/V9FEFYsWsbH3vURrrnk6qopq0qBz2OHdvPggV0EKJ64vHndFi5bdVbDB7v2ELQ6893GPutjbKHb/RrgdhFxCQfNd6rq3SLyJHCHiPwp8AhwW1T+NuBvRWQX4Qj8hoZtzUNOxcHBQd2xY8eM9apKWTsv9Gw58CkFAQ8e3M1TR/cRqOIgbBpYwflLN+A5Dl5CvOLvYLRCzEPx7sdN2G9XJ5HZp4rpZehBqRwZP8kzx/Zxohwmpti3/2W+8a0v88jPf0oQ+Pj+1Ai5r6ePs9eexS0f+EN+9fzXISIcPH6IW799G3c88HVUA8bLE5PlF/T0s3jBYv7wNz/M2y59C67jUgrKPHpoNw8d+GXovZqIqV50XFxxeNPa87h81dkU3enjifkgRLOhmVO9G/swTXIIEdnZaJ66Hv2bluur/q/rUpV95N/83WntqxXkWsRjOlnMyxrw1JEDrOpbgicObp2RZ/xdlLREv1dMId4hoXinyOwTifmu/S/ysb/6jzzy+A4C38evM73R39PHutXrOXvzZu574oegOk28q5Uf6BvgQ+/7PzlaCG8opTr1F5wwR+QNr7ic85au6UrhaRWzPb27oU+byexzuiK+YNMKfdX//bZUZR+++UtzLuK5nU5JIiIUpPMy+3iOi4fL+oUrU5WPHQwGvN6mAuGnEfC4fhfh+z++h4cfe4iyX264zej4KX6xdxe7Tr5UdXqoWnnxPPYyjhs0fjhUCnxKwCsXrewKsek0uqFP5ySzj0UxnBs6NbNPNyAiTYSIFDTwwc1P/Amjc2l7Zh+LnWIYhpFv8hSK1kTcMAwjQRg11KZTDMMw8onYSHxOyYGxTQZkH1aqaSumHF0ERmfT/sw+0jEJH9KQeUuj4C+PiMjd0fuzRORBEXlWRL4qIs3FJq1BoMp4B2b2UVVcSS+CqkrJ9ye3bcSEX2Y8KFHyy6k8If0g4NLzXktvbx+9Pb0Ny3ueh6jgFgp4XqFheUccxsZGOXxkP75falgeQjPD50aG25q5Zb7QDT06HmgYAbON54fjuKmWTqAdI/EPE7qZLore/xnV4+jOik5NDgFhHO3jE2GWGwA3DK1TdVShqgQaMOqPU1IfV4QBr3fSbb5ymwm/zKnyBHe/uJNHDj5P0fV4w5otXHHGFlyZ7kwEYSjbQJVdx/dzaKDA//PJ2/jhj7/HN+/+e8rlEmPjY9PKe55HgLLu1eey8bWvodDXy4GhIXY9+hgTY6col6ebJzrioCi9i3tZsHKAB37+TVYsXsOFm69g0YLluFWceTzHZd2CpVy3/nw2DtQN1GYQ9lmz53m3/B7yNYx974ritSGzT56mUzJ19hGRdYSxcj9JGAT9HcBB4AxVLYvIrwCfUNW31qunmrNPnsS7EldiMQ+nf/xIvMs6cyTtiLAoEnMldCAaLY/zrRd28ujw7hlp3nrcAq8/4zzesObVuOIgCAEBzx7fz4snD82Id14ul/jRg/fxj9/6ChMT45T9MgHKhvPPY+Mlr6HYN320rqocfHkPu372M8ZHRye9PPsX99O/aiFecea4YNmi1Vy4+QqWLFyJ63oUHJeNC5dx7frzWb9wWer+NaZodN7nR4JmhytQqDHlcbrOPgOvWK0X/5cbU5X9wW99tuudfT4D/CEwEL1fTu04uk3RaZl9AlVGy2VOlf2a4h0TZsFRykGZUuBXFe9kvUdLpzheGuOF4/t55tgeHjv0Qs1jH/dL3PvyY/xg75Nct2GQhcV+hk4erumo43kF3nDFW/jV113F//yXf+SpoadYd/65FHp7qpYXEVatW8vKtWey54XnefHZp+hb1l9VvGMOH9/PfTv/geWLVvMf33YLgys3snbB0prljcbEIl35rXa7eMf4Cl6Gc+V5GolnJuIi8nbggKruFJEr49VVilZVlyi4+jaADRs2ZNLGVjNSauwBmeSUX9t9vZIA5Qf7nmbv6JFU5SeCMj8/8iJrU3qLeq7HBZdchr+2j1LQ+DhEhGWrV3F8dD9+Cs9PgKMjB3nHxgtSZw4yGpMfqckTTcUTn3OyHIlfAbxTRK4DegnnxD9D7Ti601DV7cB2CKdTMmynYRjGdHIk4pm1VFU/rqrrVHUTYTjF76vq+6gdR9cwDGPOkcjtPi/WKXNxu/kY8NEoXu5ypuLoGoZhdASOSKqlE2iLiKvq/ar69uj1c6p6maq+UlWvV9XqmQpySE8TDgKBBgyPjdQN01rJmf3LWFToT11+Re8ACwuNbcFj+rwezlyQbg49RFjQv6hxsbj+JtpiGHOJpPzXCeTWY7PTMvsIsLQnNAMcKZUYLVcXZ18DXhgZ5qlje/A1QFHWL1jGpoEVNbPceOLS4xS4bsMgAQFPHn6J+/b8nKMTJ6uW3zSwkrdvHOTMBctwcDg8fpInj+7heOlU1fIFx2VFzwLOHljO5avP5cj4CN978UGeP/Zy3ePt613A+g3n4ftl9u57jmNHD1Ytu6DYx02v/U1+/1feG3rUdsa5b+SYHGX2yZzcirjnOLgdlAwiNkkSYFGhwEChME3MfQ3YPXKQp4/tIVClrFO+pS+dOMxLJw+zrn8ZmwaWT2a5icV70kFIwMXh/OUb2bJsA88cHeLelx/jyPgJAM5etJq3bxxkdd8SCo472aYVvQt5/erNHJkIxfzYRCjmRcdlec9C+rzC5E9Dx3VY1b+M6zdfzbGJE3zvhQf55bGhqeMkNicSEEXEwXOKrF97DmvOOJt9+57n6NGDgDLQs4Dfu/R6/s3l1+M5Hj1N5Ps0jGo0kxxi9vtonbOPiKwHvgScAQTAdlX9rIh8AvhfCf1mAP5IVb8dbfNxQidIH/g/VPW79faRWxGH6ckgOkXMIfL4YkrMHx4e4pFDYVIFX2cGBghQUBg6eZih0cOct/hMXrlodc2TyREHR2DL0g2cu2QdQycOsrx3gOW9A9PEO9keV4TlPQu5YtVmDo+f4OXRIxQct+bFUHALrOhbyrs2X82RseP8/TPf4UTpFLGMz7DSd0IxX7d2M2esOZt3nnkxH7joHbiOa+JtnDbtEO/kzhxp2UPLMvAfVPVhERkAdorIPdFnn1bV/3farkW2EBqCvBo4E/ieiJyjWtuZJNciHpMU81KgHRM/JRbznw7vTuWYFIv5mf1LU9lShw9XXM5dspZCigQMsZiLSJSkuTEF1+NEaZSxyKa9oYuV47CqbzE3D/4mBacrTi9jjml/Zh9pmS+Dqu4F9kavR0TkKeo7OG4F7oieFT4fGYBcBvy41gb5mfhJQRjzYK5bcfo0+8Ck2WNutouUcGqnmR0EQafcSo280/bUbDT1YHOFiOxILNtq1iuyCbgYeDBa9SEReUxEviAisQvzWuClxGYNvdptqGQYhlFBEw82h9PEThGRhcDXgY+o6nERuRX4z4RjpP8M/Dfg92jCqz3GRNwwDKOCVsZOEZECoYB/WVX/AUBV9yc+/zxwd/R2CFif2LymV3tMV02nGIZhnD5pJ1MaC72Ed4PbgKdU9c8T69ckiv0G8Hj0+i7gBhHpEZGzgM3AQ/X2kWokLiLnEMb8Xq2qrxGRC4B3quqfptm+nXRDXoHOis8YUhnCthF5igJndDbtzuwjgNs665QrgPcDPxeRR6N1fwTcKCIXEU6V7Ab+LYCqPiEidwJPElq2fLCeZQqkn075PPAHwOeiHT0mIl8BOkbEOzG+eMwrF63kuZFDBForIOwUZb/E04d2c/6Ks3EdBy+FhYcfBHiOQ5pHlqpKn1fEQaoZCs5AgNX9y1hUXMDxiZNMBPWz9bjicKo0zvCp45y50BI9GKfPeNCeZBCTSOuiGKrqD6l+YX67zjafJMzBkIq0It6vqg9VdGBzcVczopPFO+bNa1/FkfFRHjy4mxdOHCHQCvnUMLb4kZOH+NHT97LnyIss61vCey+5nree+0ZccfGqmAQ6QJ/nTQbH95Wa5pWq4R7H/BITgc+qvsWMlsc5URqrKuaxU0+vW2DpwCr+6LLf4anDu/nWcw9weGxkhph7Etqn/9qZr+HGc97I6n6LF260jnZm9oF8/ZJMK+LDIvIKoqekIvJuItvHuSIP4p1kaU8/16zbwrGJUzx48AWeHzmErz5lv8zhkYP86zP3su/olGfk4VNH+e//+nn+budXufHid3HteW+m4Hi4jjtNvJMnmxfl8pwSc0VD0/NIvKfuuyLCgkIv/V4Pp/wJRkqnJjMTheJdDEfsifq3LD+L85Zt4pkjL/Kt5x5g+NQxfPVxxeF/WXsBN5zzRlb2Lc60H435TTvEPDQxzM/jwrQi/kHC2N6vEpGXgeeB386sVSnotMw+aVlc7OMta1/F8YkxPnbv/+DZfU+y/1jth89Hx45z64+/yJcf/hq3vefPOXNg1QzxTiIik2I+6vuUAp+JOkG2RIR+r4c+t8ip8jilwKe3Qrwry79q2UbOXfrbvHB8D/tO7OeaTZeyvDd9ICzDOF18DQctWdF1I3FVfQ64WkQWAI6qjmTbrO5nUbGXn+3+MSPj1YNYVXJ8fISTYyMUl6xpXJjwJAw0qCvgleV7vSKFKmEBapU/Z+kG3rj21R0TktMwWkMXZfYRkY/WWA9A0mTGMAyjGwitU7pExJlKcHwucCmhDSOEWesfyKpRhmEYc4d0z5y4qv4nABH5Z+CSeBolCqP4/2XeOsMwjHYj+ZoTT3u72QAkU7NPAJta3pp5xhvOuowlKR8ICsJAT39Twav8IJi0OEmDK1IzMUUtxn2/qX0YRh4QcVItnUBa65S/BR4SkW8QWqD9BmGg8znD7bDMPrPhT67+CIEGfP3x7/L5n97BodGjM8o44vDWc36Nf//632N5/xIKjhAolLW6dY6qMhH4HJsYm0w8IRrWU2t04YrQ5xbxxEEJU8edKI9PM0lMEsZ29vDE5VipxHDl4asAABOmSURBVEipxALPo9/zcjWCMfJL1vLZKanX0pDWOuWTIvId4NeiVb+rqo/U20ZEegnnzXui/XxNVf84igdwB7AMeBh4v6pO1K6pOq7j4HRYMohmKXoFAN5zwXW86/y38s0nv8df/eQrDI8ewRWHa8+9ko+8/ndY1LNwWn5KB6Uo08VcVRkPfI5PjEVp36ZQwsxClWKeFO9wVXjqOuKyuNCHrwEny+OMR2IuhCP12LEnJgBOlMucKJdZaGJuZEi7Mvu4HZLJPg1pY6dsAIaBbyTXqeqLdTYbB65S1RNRFK8fRjeCjxJmtLhDRP6KMA3RrbNpfDIZhK9KOadiXnA9Cni869VvZeuWq9k59DjnrjybBcW+qsmFY4GMxfxU2Wd4fHSGeFcSi7mrwkCxb5p4V9uHJy6LCn34QcCpoFQ3bVW831jMl/X0THqSGsbp4gBeG5NDdM2DzQT/k6nrtA84C3iGMIVQVTScKD0RvS1EiwJXAe+N1t8OfIJZinhMKDiCq8pETp2AADzXw8PjVzdekmokG5c5XhqblrOzEUXXiwQ85T4kfaYTJbrgbCRutIi5yOyTp1+SaadTzk++F5FLiKJu1UNEXGAn8ErgL4FfAkdVNZ5srZm1IsqQsQ1gw4YNaZqJiOBIfqdXYpo+gTrwfLOk9karmKvMPnlhVr8ZVPVhQrvxRuV8Vb2IMLD5ZcB51YrV2Ha7qg6q6uDKlStn00zDMIzmkS60Tqnw3HSAS4CDaXeiqkdF5H7gdcASEfGi0XjDrBWGYRjtJV/TKWlvJQOJpYdwjnxrvQ1EZKWILIle9wFXA08B9wHvjordBHyz+WYbhmFkQ5wUIs3SCaR9sPmkqk7z0BSR66nvtbkGuD2aF3eAO1X1bhF5ErhDRP4UeIQwdVHLML+TziA/4xij02l3Zh9o/zz86ZBWxD/OTMGutm4SVX0MuLjK+ucI58dbhqoSkN/wtKfLgFfkVDl8VpwmU085CEh7jqpq4udauseVAaH9eiFHF4LRubQ9sw+C06JhiIisJ3SMPIPw0tiuqp8VkWXAVwk933cD71HVI1FOzs8C1wGjwO9EzyBr0iiK4bVRZWtF5C8SHy2iAzL7zHfxjllQKLLRK3B8YpyjE+M1M/WICEuLvQwUikAotBN+bc9PgPGgPC2crQM1bcsB+lyXhYUCrgm40UImk0GgeE62Yh5fKy2iDPwHVX1YRAaAnSJyD/A7wL2q+ikRuQW4BfgYcC1hcuTNwOWE5teX19tBo5H4HmAH8E5CU8GYEeDfN304LSRvmX2yxhFhSU8vi4o9jEyMcyQS88nPIvFOnpwFEbzIJHPcDyY9P2GmeMcEhKmCHAif4kdibuJttAMf8IOMxVxaN52iqnuJsqCp6oiIPEVoVr0VuDIqdjtwP6GIbwW+FPnZ/ERElojImqieqjSKYvgz4Gci8uWEbXdHMN9H37VwRFgcifnx0gSOCAu8QsNMQK44jJbLnArCbECNCFA8HPpdl34Tb6PN+KSfC54NTXhsrhCRHYn321V1e9U6RTYRTjE/CKyOhVlV94rIqqjYWuClxGaxL83sRFxE7lTV9wCPiMgMzVTVC+ptb8wdIsKiYk9T5UWgnDITEIQ/OxcUCrl6CGQYjRAEL33IiGFVHWxYp8hC4OvAR1T1eJ1fENU+qDtebXQz+3D09+0NyhmGYXQNrZymiWJHfR34sqr+Q7R6fzxNIiJrgAPR+iFgfWLzhr40dW83iXmYf6eqLyQX4N81ezCGYRidjgBOZKHSaGlYV3g3uA14qiKd5V2EfjIw3V/mLuADEvI64Fi9+XBI7+zz5irrrk25rWEYRq6QyJyx0ZKCK4D3A1eJyKPRch3wKeDNIvIsob5+Kir/beA5YBfweVIMlhvNif9vUSVni8hjiY8GgH9NcwSdQnJSyWZwq+MARcdlPOW8uM2FG11JE1E7G6GqP6S25LypSnkFPtjMPhrNiX8F+A7wXwjtGGNGVPVwMztqNWkz+1S1gY7+mgRNp9f16HE9AlWOlyY45VcXc1eERYUCfW6W9gGGUZssQ091VbZ7VT0GHANuBIjMYHqBhSKysEFSiEyJM/vUSgaRxvyw28U8eVxpPDmROLOPsKTYw6IKMfdEWFQo0uuGMSPyFCTI6A7alRwiT78y00YxfAfw58CZhE9RNxIGs6qZFKIdJJNBxGI+G9vx+RD7Oj6+ap6c1XBEJsV8cZQGrxiZXZl4G+2mvZl9pCvjif8pYRjZX6jqWYRzOR0zJy4S2nX2OLNLqpSfr+v0kYqlEY4IruNQdJxmHuYYRssoOkLRddqYmm1qENNo6QTSal5JVQ8Bjog4qnofcFGG7ZoVYWafuW5Fd2LibcwVcyGWeRLxtE+mjkYeRw8AXxaRA3RAACzDMIws6MZEyVuBMcKgV+8DFgN/klWjDMMw5gqJphDzQtpEyScTb2/PqC2GYRgdQadMlaShkbPPCNUNPoTQLn1RJq2aJaqaynbcMIz80O7MPrHbfV5oZCc+0K6GnA4amReO+wEB4EX9n/aLnw8mhoaRV8azjh9ehTw9yM+1y12leMeUNfK6in5ENPpC8vN1Gcb8pC3JICZpndt9O8ispSKyXkTuE5GnROQJEflwtH6ZiNwjIs9Gf5fOpn4/UE6UA05VCHiMEoq5r6HYa5UMymltpQ3D6Ax8wpF5yQ+qXtOtINSFdP86gSxvN3FuufMIHYU+KCJbCGOw3Kuqm4F7mR6TJTXjQZDatb6sUZyV6Es38TaMfJM+dcksEPAcJ9XSCWTWClXdG2dpVtURQjf9OLdcbOFyO/DrWbWhGibehmHUI+0ovFNG4m2ZE0+ZW65ym23ANoANGza0o5mGYRhAvkwMM/89UJlbLu12qrpdVQdVdXDlypXZNdAwDKMCESfV0glk2op6ueWiz5O55QzDMOacFqdn+4KIHBCRxxPrPiEiL1dk+ok/+7iI7BKRZ0TkrWnam6V1SrO55QzDMOYeEVwn3ZKCvwGuqbL+06p6UbR8O9ytbAFuIAzxfQ3wP0TEbbSDLEfizeaWa4qCk+6xghA6/7iSLwN+wzBqk3Vmn1aNxFX1ASBtFrStwB2qOq6qzxPm2bys0UaZPdhsNrdcsxQcB0+UkirjvlZNduBKZE5o4m0YXUG7kkO0QTM+JCIfAHYQmmIfIbTe+0mizFC0ri6dMTM/S0SEouOw0HPoc6dG5p6Ei2NJDAyjK3CAnjYmh2ginvgKEdmRWLalqP5W4BWEORn2Av8tWl/twBq6w+Ta7T5GRCiI4IkyEcwclRuGkV962hgzBZoOgDWsqoPN1K+q+yf3JfJ54O7o7RCwPlF0HbCnUX25HolXYpl9DKP7aP+vaZlMRdhomVXtkXVexG8AseXKXcANItIjImcBm4GHGtXXFSNxwzCMVhE+T2vN+FZE/h64knDaZQj4Y+BKEbmIcKpkN/BvAVT1CRG5E3iSMGzJB1W1YYQBE3HDMIwk0jqPTVW9scrq2+qU/yTwyWb2YSJuGIZRQdckhcgbYcjZuW6FYRitZC4y++TJqK0rRFxVCYCyWaYYRtfR/sw+2duht5Jci3gs3iVLrGkYXU07M/u08sFmO8itiAca2oQbhjF/aJeY25x4GyibgBvGvMUnW/HKk6d3bkXcMAwjC0RsTtwwDCPXdErqtTSYiBuGYVRgI3HDMIycYtYphmEYOSdPgfRyK+KeI2ZiaBjzlGzHyWJz4u3AEaHHgUChZL72hjEvcIhSM2bs7GNz4m1CRHAFHMU8Nw2ji3GhfW73LYxi2A6yzHb/BRE5ICKPJ9YtE5F7ROTZ6O/SFu0LV4QeR7ory4VhGPQ4QsF12hwEK92/TiBLzfsb4JqKdbcA96rqZuDe6H3LCLNttLJGwzDmmnZ7TwqC6ziplk4gs1ao6gPA4YrVW4Hbo9e3A7+e1f4NwzBmi4OkWjqBdt9KVqvqXoDo76paBUVkW5xB+uDBg21roGEY85v4wWbKbPf162piWllC/kJEdonIYyJySZr2dsbvgSqo6nZVHVTVwZUrV851cwzDmEdIyiUFf0P6aeVrCZMjbwa2Abem2UG7RXx/nOk5+nuglZVbZh/D6D50Ti7q1sh4k9PKW4EvachPgCWxXtaj3SJ+F3BT9Pom4JutqFRV8QNlPAiTRBiG0T2MB8qEH7RVzJuYTlkRT/tGy7YU1deaVl4LvJQoNxStq0tmduIi8vfAlYQHOQT8MfAp4E4RuRl4Ebj+dPahqubsYxjzgIBQzJ0oGUSWdtxNmg8Oq+pgy3Y9k4bilpmIq+qNNT56Uyvqt8w+hjH/CICJSMwz89yUzBMl7xeRNaq6t2JaeQhYnyi3DtjTqLKOfbDZCMvsYxjzl+ynTVv4aHMmtaaV7wI+EFmpvA44Fk+71CPXbveGYRhZ0CpvzCanlb8NXAfsAkaB302zDxNxwzCMClo1m9LMtLKGT24/2Ow+TMQNwzAqsETJhmEYOSWc7TYRNwzDyCltCnnbInJrneLlKX+SYRgtxc24/kxtU1pMbkfik5l9CM0NzeDQMLqfdiWHsOmUNiEiuIBjYm4YXU1bM/vkjFyLeExSzEsWP8UwuoqeNot3GIo2PzPNXSHiMWFmH00RbcAwjLwwF6PvPI33u0rEDcMwThvJPnhKKzERNwzDqCA/Em4ibhiGMQOzTpkjLLOPYXQfgWqm8cMriXNs5oWuEHFVxVelbAJuGF3HRJuSQUzHRLwtmHgbxvwgmQyiHWKeHwnPsYj7QUDJxNsw5hVtyexDvqIYzolFu4hcIyLPiMguEbllNnX4JuCGMW/J1qFPUv/rBNou4iLiAn8JXAtsAW4UkS3tbodhGEYtLABWfS4DdqnqcwAicgewFXhyDtpiGIYxDaG10ykishsYAXygrKqDIrIM+CqwCdgNvEdVj8ym/rmYTlkLvJR4PxStMwzD6AgymE55o6pepKqD0ftbgHtVdTNwb/R+VsyFiFc78hkz3CKyTUR2iMiOgwcPtqFZhmEYbWMrcHv0+nbg12db0VyI+BCwPvF+HbCnspCqblfVQVUdXLlyZdsaZxiG0cSc+Ip4sBkt26pUp8A/i8jOxOerVXUvQPR31WzbOhdz4j8FNovIWcDLwA3Ae5utxHOEksUPN4x5SeaZfdLPlAwnpkhqcYWq7hGRVcA9IvL0aTWugraLuKqWReRDwHcJv4svqOoTzdbjiFC0ZBCGMa/IY3IIVd0T/T0gIt8gNO7YLyJrVHWviKwBDsy2/jmxE1fVb6vqOar6ClX95GzrERFcEYqOhIb/rWykYRgdg0uYHKLgOm0R8FY92BSRBSIyEL8G3gI8DtwF3BQVuwn45mzbmluPzSSW2ccwupe2Z/ZpbTjx1cA3ovZ7wFdU9Z9E5KfAnSJyM/AicP1sd9AVIh5jmX0Mo/vI09RJJZE/zIVV1h8C3tSKfXSViBuGYbSCPN02TMQNwzAqMBE3DMPIMXmawekqEVdVApsPN4yuot2ZfULyo+JdIeKWHMIwuo0w1aICp3zFFSg6TtvEPD8SnnMRN/E2jO5CoyS5ynQjM1/hlB+0XczzQG5F3DL7GEZ3oIns5pXiXUlSzHucbBx/OilWeBryK+Im4IbRNTTroJf19Z+ngX5uRdwwDCMrcqThcxM7xTAMw2gNNhI3DMOYRr6iJNpI3DAMI8fYSNwwDCNB3qxTcjsS92YRP3y+lW/HPjqxfCe2KcvyzV7EndhHzWbqKeRouiNrcjsSd0TocSV09qmT2UeAgiOTzgFBVL6eSVPBERzCEJgalffrlPcEXJGp8qp1TaCS2UnSOCw5UZvi8o2yGVU75nqp7CRqT3zMqfpIBEfS95Er4GXcR150zJ3SRzPOi4z7KFAoae0Nsu4jmHntNIrv7wm4kb23qjIRBHW/54LI5LWQJU6O7hG5FfEYVwTXlRknWOUJGOOIUHSrX4TJEzBGRCi4glflIkxepNPKS1S+4iJMXqTJ8p4IbhWhSop3snycAKPyIkxepJXH3FOjj7wqx1y3jxLinUkfMTP9VrKPKoWq2jHX66N658Vc9VGt8yLuo8rzolYfuQKO0lQfuVUGQmn6KHnMtfpIoj6qdgOrdV70uC7F6DtIHkO7xDuP5F7EY5InWPy+Ufn4IlSYcQJWkrwIg7Tlpbnyk0KVsnxSqIR0x5z3PpoUqrTlCfto8kbXZB9Jg33EfaTNHHOTfdT0eZHoozTnRXIgRIry7Tgviq5QiG7wrtQvnwV5ulXkdk68Fo7MHEE0Kl85IqiHdGj5Zo95vvXRbI7Z6bBjmI/nxdwlRZaUS4qaRK4RkWdEZJeI3NLqlnadiBuGYZwWwmSezUZLw6pEXOAvgWuBLcCNIrKllc01ETcMw0iQdgye8vfBZcAuVX1OVSeAO4CtrWxvLubEd+7cOSwiL2RU/QpgOKO6O4FuPz7o/mO042uOjaez8cM7d363z3NXpCzeKyI7Eu+3q+r2xPu1wEuJ90PA5afTvkpyIeKqujKrukVkh6oOZlX/XNPtxwfdf4x2fO1FVa9pYXXVBuwtjcFo0ymGYRjZMQSsT7xfB+xp5Q5MxA3DMLLjp8BmETlLRIrADcBdrdxBLqZTMmZ74yK5ptuPD7r/GO34coqqlkXkQ8B3Cf20vqCqT7RyH5JMjWQYhmHkC5tOMQzDyDEm4oZhGDnGRNwwDCPHmIgbhmHkGBNxwzCMHGMibhiGkWNMxA3DMHLM/w80tXCxwKAe3gAAAABJRU5ErkJggg==\n", "text/plain": [ "
" ] }, "metadata": { "needs_background": "light" }, "output_type": "display_data" } ], "source": [ "(\n", " breweries_data\n", " .assign(longitude = lambda x : pd.to_numeric(x.longitude))\n", " .assign(latitude = lambda x : pd.to_numeric(x.latitude))\n", " .plot\n", " .hexbin(\n", " x='longitude', \n", " y='latitude', \n", " gridsize=20\n", " )\n", ");" ] }, { "cell_type": "markdown", "metadata": {}, "source": [ "The graph above looks like the map of the US and the distribution of breweries in geographic terms. We can see the deep green in NY area which we already know that is third on the list of states (after the huge California and Colorado), but also see an even deeper blue area around Illinois. We will dive deeper into these questions, when we merge the zip code level with the data, and find the where should you live or visit if you want to taste as many breweries as possible. " ] }, { "cell_type": "markdown", "metadata": {}, "source": [ "## More advanced Aggregation Functions\n", "\n", "Our data set doesn't have many numeric field, and we should load a different data set to examine the more advanced aggregation options of _groupby_. We will use one of the famouse data sets used for learning to build machine learning models. The last column _annual_income_ has only two values _<=50K_ and _>50K_, and we want to try and predict based on the other parameters such as age, gender, years of education, etc. in which bucket each person lands. \n", "\n", "* Define the column names for the data\n", "* Read the CSV format of the data from the dataset URL\n", "* Set the names of the columns as defined above" ] }, { "cell_type": "code", "execution_count": 9, "metadata": {}, "outputs": [ { "data": { "text/html": [ "
\n", "\n", "\n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", "
ageworkclassideducationeducation_nummarital_statusoccupationrelationshipracegendercapital_gaincapital_losshours_per_weeknative_countryannual_income
039State-gov77516Bachelors13Never-marriedAdm-clericalNot-in-familyWhiteMale2174040United-States<=50K
150Self-emp-not-inc83311Bachelors13Married-civ-spouseExec-managerialHusbandWhiteMale0013United-States<=50K
238Private215646HS-grad9DivorcedHandlers-cleanersNot-in-familyWhiteMale0040United-States<=50K
353Private23472111th7Married-civ-spouseHandlers-cleanersHusbandBlackMale0040United-States<=50K
428Private338409Bachelors13Married-civ-spouseProf-specialtyWifeBlackFemale0040Cuba<=50K
................................................
3255627Private257302Assoc-acdm12Married-civ-spouseTech-supportWifeWhiteFemale0038United-States<=50K
3255740Private154374HS-grad9Married-civ-spouseMachine-op-inspctHusbandWhiteMale0040United-States>50K
3255858Private151910HS-grad9WidowedAdm-clericalUnmarriedWhiteFemale0040United-States<=50K
3255922Private201490HS-grad9Never-marriedAdm-clericalOwn-childWhiteMale0020United-States<=50K
3256052Self-emp-inc287927HS-grad9Married-civ-spouseExec-managerialWifeWhiteFemale15024040United-States>50K
\n", "

32561 rows × 15 columns

\n", "
" ], "text/plain": [ " age workclass id education education_num \\\n", "0 39 State-gov 77516 Bachelors 13 \n", "1 50 Self-emp-not-inc 83311 Bachelors 13 \n", "2 38 Private 215646 HS-grad 9 \n", "3 53 Private 234721 11th 7 \n", "4 28 Private 338409 Bachelors 13 \n", "... ... ... ... ... ... \n", "32556 27 Private 257302 Assoc-acdm 12 \n", "32557 40 Private 154374 HS-grad 9 \n", "32558 58 Private 151910 HS-grad 9 \n", "32559 22 Private 201490 HS-grad 9 \n", "32560 52 Self-emp-inc 287927 HS-grad 9 \n", "\n", " marital_status occupation relationship race \\\n", "0 Never-married Adm-clerical Not-in-family White \n", "1 Married-civ-spouse Exec-managerial Husband White \n", "2 Divorced Handlers-cleaners Not-in-family White \n", "3 Married-civ-spouse Handlers-cleaners Husband Black \n", "4 Married-civ-spouse Prof-specialty Wife Black \n", "... ... ... ... ... \n", "32556 Married-civ-spouse Tech-support Wife White \n", "32557 Married-civ-spouse Machine-op-inspct Husband White \n", "32558 Widowed Adm-clerical Unmarried White \n", "32559 Never-married Adm-clerical Own-child White \n", "32560 Married-civ-spouse Exec-managerial Wife White \n", "\n", " gender capital_gain capital_loss hours_per_week native_country \\\n", "0 Male 2174 0 40 United-States \n", "1 Male 0 0 13 United-States \n", "2 Male 0 0 40 United-States \n", "3 Male 0 0 40 United-States \n", "4 Female 0 0 40 Cuba \n", "... ... ... ... ... ... \n", "32556 Female 0 0 38 United-States \n", "32557 Male 0 0 40 United-States \n", "32558 Female 0 0 40 United-States \n", "32559 Male 0 0 20 United-States \n", "32560 Female 15024 0 40 United-States \n", "\n", " annual_income \n", "0 <=50K \n", "1 <=50K \n", "2 <=50K \n", "3 <=50K \n", "4 <=50K \n", "... ... \n", "32556 <=50K \n", "32557 >50K \n", "32558 <=50K \n", "32559 <=50K \n", "32560 >50K \n", "\n", "[32561 rows x 15 columns]" ] }, "execution_count": 9, "metadata": {}, "output_type": "execute_result" } ], "source": [ "adult_names = ['age','workclass','id','education','education_num','marital_status',\n", " 'occupation', 'relationship','race','gender','capital_gain',\n", " 'capital_loss','hours_per_week','native_country','annual_income']\n", "adults_data = (\n", " pd\n", " .read_csv(\n", " 'https://archive.ics.uci.edu/ml/machine-learning-databases/adult/adult.data', \n", " names = adult_names\n", " )\n", ")\n", "adults_data" ] }, { "cell_type": "markdown", "metadata": {}, "source": [ "## Calclulating mean\n", "\n", "First we can calculate the average age in each income group, and see if there is any difference.\n", "* Start with the adults data that was loaded above\n", "* Group using the _annual\\_income_ value\n", "* Take only the _age_ value\n", "* Calculate the average of each group" ] }, { "cell_type": "code", "execution_count": 10, "metadata": {}, "outputs": [ { "data": { "text/plain": [ "annual_income\n", " <=50K 36.783738\n", " >50K 44.249841\n", "Name: age, dtype: float64" ] }, "execution_count": 10, "metadata": {}, "output_type": "execute_result" } ], "source": [ "(\n", " adults_data\n", " .groupby('annual_income')\n", " ['age']\n", " .mean()\n", ")" ] }, { "cell_type": "markdown", "metadata": {}, "source": [ "We can see that the average age of people who earned more than 50K is over 44, while the average age of the lower income (less than 50K) is younger and around 36 years" ] }, { "cell_type": "markdown", "metadata": {}, "source": [ "## Group by multiple keys\n", "\n", "Let's see if women earn less than men, and we will do that by grouping both by the income group (_annual_income_) and the gender column.\n", "\n", "* Start with the adults data that was loaded above\n", "* Group using the _annual\\_income_ and the _gender_ values \n", "* Calculate the size of each group" ] }, { "cell_type": "code", "execution_count": 11, "metadata": {}, "outputs": [ { "data": { "text/plain": [ "annual_income gender \n", " <=50K Female 9592\n", " Male 15128\n", " >50K Female 1179\n", " Male 6662\n", "dtype: int64" ] }, "execution_count": 11, "metadata": {}, "output_type": "execute_result" } ], "source": [ "(\n", " adults_data\n", " .groupby(['annual_income','gender'])\n", " .size()\n", ")" ] }, { "cell_type": "markdown", "metadata": {}, "source": [ "It is hard to calculate the percentage and evaluate the difference from the size of each group. Let's calculate the percentage and also format the out to make it easier to read the results.\n", "\n", "* Start with the adults data that was loaded above\n", "* Group using the _annual\\_income_ and _gender_ values\n", "* Take only the _id_ value\n", "* Calculate the size of each group\n", "* Group again by the first level (_annual\\_income_)\n", "* For each row in each group calculate the ratio of the size of the row with the total size of the group\n", "* Apply style to the results\n", "* Format the results as percentage with two digit precision. " ] }, { "cell_type": "code", "execution_count": 12, "metadata": {}, "outputs": [ { "data": { "text/html": [ "\n", "\n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", "
  id
annual_incomegender 
<=50K Female38.80%
Male61.20%
>50K Female15.04%
Male84.96%
\n" ], "text/plain": [ "" ] }, "execution_count": 12, "metadata": {}, "output_type": "execute_result" } ], "source": [ "(\n", " adults_data\n", " .groupby(['annual_income','gender'])\n", " [['id']]\n", " .count()\n", " .groupby(level=0)\n", " .apply(lambda x: x / float(x.sum()))\n", " .style\n", " .format('{:.2%}')\n", ")" ] }, { "cell_type": "markdown", "metadata": {}, "source": [ "We can now easily see that the percentage of women with higher income is less than half that of the lower income. Hopefully, the current data (this data is extracted from the 1994 Census database) has improved the gender equlity in this aspect." ] }, { "cell_type": "markdown", "metadata": {}, "source": [ "## Advanced Group Functions\n", "\n", "We saw before how to apply a function on each row in a dataframe table, but many times we want to apply a function on a group of rows based on a value of a column or a couple of columns.\n", "\n", "We can also take each of the groups and calculate more advanced functions such as Correlation. Let's see if we find Correlation with in each group between years of education and the number of work per week (which shows some quality of hard work)\n", "\n", "* Start with the adults data that was loaded above\n", "* Take the _annual\\_income_ and _gender_ values with the _education\\_num_ and _hours\\_per\\_week_ values\n", "* Group by the _annual\\_income_ and _gender_ (and calculate the average of the other values in each group)\n", "* Calculate the Correlation of the values in each group\n", "* Replace the self-Correlation values of 1 with 0 \n", "* Apply style to the results\n", "* Format the results with three digit precision. \n", "* Color the background of the cells according to the correlation values" ] }, { "cell_type": "code", "execution_count": 13, "metadata": {}, "outputs": [ { "data": { "text/html": [ "\n", "\n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", "
   education_numhours_per_week
annual_incomegender   
<=50K Femaleeducation_num0.00.151
hours_per_week0.1510.0
Maleeducation_num0.00.069
hours_per_week0.0690.0
>50K Femaleeducation_num0.00.187
hours_per_week0.1870.0
Maleeducation_num0.00.0477
hours_per_week0.04770.0
\n" ], "text/plain": [ "" ] }, "execution_count": 13, "metadata": {}, "output_type": "execute_result" } ], "source": [ "(\n", " adults_data\n", " [['annual_income','gender','education_num','hours_per_week']]\n", " .groupby(['annual_income','gender'])\n", " .corr()\n", " .replace(1,0)\n", " .style\n", " .format('{:.3}')\n", " .background_gradient(cmap='coolwarm')\n", ")" ] }, { "cell_type": "markdown", "metadata": {}, "source": [ "We can see that within each group the correlation of \"hard work\", as we defined it before, is higher for the female (0.151 and 0.187) than the male (0.069 and 0.0477). " ] }, { "cell_type": "code", "execution_count": null, "metadata": {}, "outputs": [], "source": [] } ], "metadata": [], "nbformat": 4, "nbformat_minor": 4 }