Customer_Segmentation.ipynb

{
 "cells": [
  {
   "cell_type": "markdown",
   "id": "a68e02b4",
   "metadata": {
    "pycharm": {
     "name": "#%% md\n"
    }
   },
   "source": [
    "# Imports"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "823268a9",
   "metadata": {
    "pycharm": {
     "name": "#%%\n"
    }
   },
   "outputs": [],
   "source": [
    "from __future__ import division\n",
    "from datetime import datetime\n",
    "from sklearn.cluster import KMeans\n",
    "from IPython.display import display\n",
    "from typing import List\n",
    "\n",
    "import pandas as pd\n",
    "import numpy as np\n",
    "import plotly.graph_objs as go"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "9212de0e",
   "metadata": {
    "code_folding": [],
    "pycharm": {
     "name": "#%%\n"
    }
   },
   "outputs": [],
   "source": [
    "# Pandas anweisen, immer alle Zeilen und Spalten eines Datenrahmens anzuzeigen\n",
    "pd.set_option('display.max_rows', None)\n",
    "pd.set_option('display.max_columns', None)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "7a1f6292",
   "metadata": {
    "pycharm": {
     "name": "#%%\n"
    }
   },
   "outputs": [],
   "source": [
    "# Set the random state for the whole notebook\n",
    "rand_state=1"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "24094e13",
   "metadata": {
    "pycharm": {
     "name": "#%% md\n"
    }
   },
   "source": [
    "# Schritt 1: Grundlegende Datenaufbereitung / Exploration\n",
    "\n",
    "Datenquelle = Online Retail Dataset: alle Einkäufe, die für ein Online-Einzelhandelsunternehmen mit Sitz im Vereinigten Königreich während eines Zeitraums von etwa 12 Monaten getätigt wurden.\n",
    "\n",
    "Chen, Daqing, Sai Laing Sain, and Kun Guo. \"Data mining for the online retail industry: A case study of RFM model-based customer segmentation using data mining.\" Journal of Database Marketing & Customer Strategy Management 19.3 (2012): 197-208.\n",
    "\n",
    "Eine CSV-Datei wird mit Ihrem Kursmaterial bereitgestellt. Wenn Sie die Datei direkt von der Quelle herunterladen möchten, können Sie \"Online Retail.xlsx\" von der folgenden URL herunterladen und in eine CSV-Datei umwandeln: https://archive.ics.uci.edu/ml/datasets/online+retail"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "1697ced8",
   "metadata": {
    "pycharm": {
     "name": "#%% md\n"
    }
   },
   "source": [
    "### Daten Lesen; Einfache Preprocessing"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "658eaf86",
   "metadata": {
    "code_folding": [],
    "pycharm": {
     "name": "#%%\n"
    }
   },
   "outputs": [],
   "source": [
    "# Die Daten lesen\n",
    "# Ersetzen Sie \"OnlineRetail.csv\" durch den Pfad zu dem Ort, an dem Sie die Datei gespeichert haben\n",
    "# Es wird empfohlen, die Daten am gleichen Ort wie dieses Notebook zu speichern.\n",
    "df_data = pd.read_csv('OnlineRetail.csv', encoding='unicode-escape')\n",
    "df_data.head() # Show the first five rows"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "8ef51bff",
   "metadata": {
    "code_folding": [
     0
    ],
    "pycharm": {
     "name": "#%%\n"
    }
   },
   "outputs": [],
   "source": [
    "# Show a random sample of 20 rows\n",
    "df_data.sample(20)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "4ad558e7",
   "metadata": {
    "code_folding": [
     0
    ],
    "pycharm": {
     "name": "#%%\n"
    }
   },
   "outputs": [],
   "source": [
    "# Display basic information about the data (e.g. data types)\n",
    "df_data.info()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "193dc71f",
   "metadata": {
    "code_folding": [
     0
    ],
    "pycharm": {
     "name": "#%%\n"
    }
   },
   "outputs": [],
   "source": [
    "# nans entfernen (nur für CustomerID, Description ist kein Problem)\n",
    "print(df_data.isna().sum())\n",
    "df_data.dropna(inplace=True, subset=['CustomerID'])\n",
    "# By specifying inplace=True, we manipulate df_data directly. Otherwise the function would only return a copy of \n",
    "# the dataframe, which we would need to save somehow, e.g. with: \n",
    "# df_data = df_data.dropna(subset=['CustomerID'])\n",
    "f\"Data shape after dropping nans: {df_data.shape}\""
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "11e86faa",
   "metadata": {
    "code_folding": [
     0
    ],
    "pycharm": {
     "name": "#%%\n"
    }
   },
   "outputs": [],
   "source": [
    "# Konvertiert den Typ von 'InvoiceDate' in datetime\n",
    "df_data.InvoiceDate = pd.to_datetime(df_data.InvoiceDate)\n",
    "f\"Min/max dates: {df_data.InvoiceDate.min()}, {df_data.InvoiceDate.max()}\""
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "14f5689e",
   "metadata": {
    "code_folding": [],
    "pycharm": {
     "name": "#%%\n"
    }
   },
   "outputs": [],
   "source": [
    "# Doppelte Zeilen entfernen\n",
    "duplicates = df_data.duplicated(keep='first')\n",
    "df_data = df_data[~duplicates]\n",
    "f\"Num duplicates: {duplicates.sum()}. Data shape after dropping duplicates: {df_data.shape}\""
   ]
  },
  {
   "cell_type": "markdown",
   "id": "57bc7fc4",
   "metadata": {
    "pycharm": {
     "name": "#%% md\n"
    }
   },
   "source": [
    "### Daten Verstehen"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "1648ffb4",
   "metadata": {
    "code_folding": [],
    "pycharm": {
     "name": "#%%\n"
    }
   },
   "outputs": [],
   "source": [
    "# Die numerischen Werte anschauen\n",
    "print(f\"Number of customers: {df_data.CustomerID.nunique()}, number of transactions: {df_data.InvoiceNo.nunique()}\")\n",
    "print(f\"Description of numeric values:\")\n",
    "df_data[['Quantity', 'UnitPrice']].describe().round(3) "
   ]
  },
  {
   "cell_type": "markdown",
   "id": "7261a8cb",
   "metadata": {
    "pycharm": {
     "name": "#%% md\n"
    }
   },
   "source": [
    "Es gibt einige Artikel, bei denen UnitPrice == 0 ist. Lassen Sie uns herausfinden, warum."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "cdcb4505",
   "metadata": {
    "code_folding": [],
    "pycharm": {
     "name": "#%%\n"
    },
    "scrolled": true
   },
   "outputs": [],
   "source": [
    "# Explore data where UnitPrice == 0\n",
    "df_zero_price = df_data.query('UnitPrice == 0')\n",
    "# Alternativ\n",
    "# df_zero_price = df_data[df_data.UnitPrice == 0]\n",
    "print(f\"Num transactions where UnitPrice == 0: {len(df_zero_price)}\")\n",
    "df_zero_price"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "15f15944",
   "metadata": {
    "pycharm": {
     "name": "#%% md\n"
    }
   },
   "source": [
    "Es gibt nur 40 Zeilen, in denen UnitPrice == 0 ist, so dass wir sie getrost weglassen können."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "acb01c50",
   "metadata": {
    "code_folding": [],
    "pycharm": {
     "name": "#%%\n"
    }
   },
   "outputs": [],
   "source": [
    "# Remove transactions where UnitPrice == 0\n",
    "df_data = df_data.query('UnitPrice > 0')\n",
    "print(f\"Data shape after dropping transactions where UnitPrice == 0: {df_data.shape}\")"
   ]
  },
  {
   "attachments": {
    "image.png": {
     "image/png": "iVBORw0KGgoAAAANSUhEUgAAATwAAAEMCAYAAABZfwqrAAAK1WlDQ1BJQ0MgUHJvZmlsZQAASImVlwdUU+kSgP9700NCS4h0Qm+CdAJICT106SAqIQkklBgSgohdWVyBFUVFBJUFXQVRcHUpshbEgoVFwYJ9gywi6rpYsKGyF3iE3X3nvXfenDN3vjN3/vln/nP/c+YCQPZli0SZsDIAWcIccWSANz0+IZGOewJgoAYogASobI5ExIyICAGIzNi/y7vbAJq0N6wmc/37+/8qqlyehAMAlIRwClfCyUK4A9EnHJE4BwBUHeI3XJYjmuRuhKlipECEZZOcNs1vJzllitH4qZjoSB+EtQHAk9hscRoAJDPET8/lpCF5SIEI2wi5AiHCeQh7cPhsLsJtCM/Nylo6yb8hbIbEiwAgkxBmpPwlZ9rf8qfI87PZaXKe7mtK8L4CiSiTvfz/PJr/LVmZ0pk9TBAl8cWBkYhVRM7vTsbSYDkLU8LCZ1jAnYqfYr40MGaGORKfxBnmsn2D5Wszw0JmOFXgz5LnyWFFzzBP4hc1w+KlkfK9UsU+zBlmi2f3lWbEyP18HkueP58fHTfDuYLYsBmWZEQFz8b4yP1iaaS8fp4wwHt2X39571mSv/QrYMnX5vCjA+W9s2fr5wmZszkl8fLauDxfv9mYGHm8KMdbvpcoM0Iez8sMkPsluVHytTnIxzm7NkJ+hunsoIgZBjHADjCAH6KOwAaAHF5ezmQTPktFy8WCNH4OnYncNB6dJeRYz6Xb2djZATB5b6c/hTe0qfsI0a7M+jIeA+DiBgD8ZtaXjPTZjNw7suWsz6IaABUBAOeUOFJx7rQPPfnAACJQAlSgAXSBITADVkiFTsANeCFVBoFwEA0SwGLAAXyQBcRgGVgJ1oFCUAy2gB2gElSDfaAOHAHHQCs4Cc6Ci+AquA5ugftABobAczAK3oFxCIJwEBmiQBqQHmQMWUJ2EAPygPygECgSSoCSoTRICEmhldAGqBgqgyqhGqge+hE6AZ2FLkO90F1oABqBXkOfYBRMgqmwDmwCz4MZMBMOhqPhRXAanA3nwwXwZrgCroUPwy3wWfgqfAuWwc/hMRRAKaBoKH2UFYqB8kGFoxJRqSgxajWqCFWOqkU1otpRXagbKBnqBeojGoumoOloK7QbOhAdg+ags9Gr0SXoSnQdugV9Hn0DPYAeRX/FkDHaGEuMK4aFicekYZZhCjHlmAOYZswFzC3MEOYdFoulYU2xzthAbAI2HbsCW4Ldg23CdmB7sYPYMRwOp4GzxLnjwnFsXA6uELcLdxh3BteHG8J9wCvg9fB2eH98Il6IX48vxx/Cn8b34Yfx4wRlgjHBlRBO4BKWE0oJ+wnthGuEIcI4UYVoSnQnRhPTieuIFcRG4gXiA+IbBQUFAwUXhQUKAoW1ChUKRxUuKQwofCSpkixIPqQkkpS0mXSQ1EG6S3pDJpNNyF7kRHIOeTO5nnyO/Ij8QZGiaK3IUuQqrlGsUmxR7FN8qURQMlZiKi1WylcqVzqudE3phTJB2UTZR5mtvFq5SvmEcr/ymApFxVYlXCVLpUTlkMpllaeqOFUTVT9VrmqB6j7Vc6qDFBTFkOJD4VA2UPZTLlCGqFiqKZVFTacWU49Qe6ijaqpqDmqxanlqVWqn1GQ0FM2ExqJl0kppx2i3aZ/m6MxhzuHN2TSncU7fnPfqWupe6jz1IvUm9VvqnzToGn4aGRpbNVo1HmqiNS00F2gu09yreUHzhRZVy02Lo1WkdUzrnjasbaEdqb1Ce592t/aYjq5OgI5IZ5fOOZ0XujRdL9103e26p3VH9Ch6HnoCve16Z/Se0dXoTHomvYJ+nj6qr60fqC/Vr9Hv0R83MDWIMVhv0GTw0JBoyDBMNdxu2Gk4aqRnFGq00qjB6J4xwZhhzDfeadxl/N7E1CTOZKNJq8lTU3VTlmm+aYPpAzOymadZtlmt2U1zrDnDPMN8j/l1C9jC0YJvUWVxzRK2dLIUWO6x7J2LmesyVzi3dm6/FcmKaZVr1WA1YE2zDrFeb91q/XKe0bzEeVvndc37auNok2mz3+a+raptkO1623bb13YWdhy7Krub9mR7f/s19m32rxwsHXgOex3uOFIcQx03OnY6fnFydhI7NTqNOBs5Jzvvdu5nUBkRjBLGJReMi7fLGpeTLh9dnVxzXI+5/uFm5Zbhdsjt6XzT+bz5++cPuhu4s91r3GUedI9kj+89ZJ76nmzPWs/HXoZeXK8DXsNMc2Y68zDzpbeNt9i72fu9j6vPKp8OX5RvgG+Rb4+fql+MX6XfI38D/zT/Bv/RAMeAFQEdgZjA4MCtgf0sHRaHVc8aDXIOWhV0PpgUHBVcGfw4xCJEHNIeCocGhW4LfRBmHCYMaw0H4azwbeEPI0wjsiN+XoBdELGgasGTSNvIlZFdUZSoJVGHot5Fe0eXRt+PMYuRxnTGKsUmxdbHvo/zjSuLk8XPi18VfzVBM0GQ0JaIS4xNPJA4ttBv4Y6FQ0mOSYVJtxeZLspbdHmx5uLMxaeWKC1hLzmejEmOSz6U/Jkdzq5lj6WwUnanjHJ8ODs5z7le3O3cEZ47r4w3nOqeWpb6NM09bVvaCN+TX85/IfARVApepQemV6e/zwjPOJgxkRmX2ZSFz0rOOiFUFWYIzy/VXZq3tFdkKSoUybJds3dkj4qDxQckkGSRpC2HigxI3VIz6TfSgVyP3KrcD8tilx3PU8kT5nUvt1i+aflwvn/+DyvQKzgrOlfqr1y3cmAVc1XNamh1yurONYZrCtYMrQ1YW7eOuC5j3S/rbdaXrX+7IW5De4FOwdqCwW8CvmkoVCwUF/ZvdNtY/S36W8G3PZvsN+3a9LWIW3Sl2Ka4vPhzCafkyne231V8N7E5dXNPqVPp3i3YLcItt7d6bq0rUynLLxvcFrqtZTt9e9H2tzuW7Lhc7lBevZO4U7pTVhFS0bbLaNeWXZ8r+ZW3qryrmnZr7960+/0e7p6+vV57G6t1qourP30v+P5OTUBNS61Jbfk+7L7cfU/2x+7v+oHxQ/0BzQPFB74cFB6U1UXWna93rq8/pH2otAFukDaMHE46fP2I75G2RqvGmiZaU/FRcFR69NmPyT/ePhZ8rPM443jjT8Y/7W6mNBe1QC3LW0Zb+a2ytoS23hNBJzrb3dqbf7b++eBJ/ZNVp9ROlZ4mni44PXEm/8xYh6jjxdm0s4OdSzrvn4s/d/P8gvM9F4IvXLrof/FcF7PrzCX3Sycvu14+cYVxpfWq09WWbsfu5l8cf2nuceppueZ8re26y/X23vm9p/s8+87e8L1x8Sbr5tVbYbd6b8fcvtOf1C+7w73z9G7m3Vf3cu+N31/7APOg6KHyw/JH2o9qfzX/tUnmJDs14DvQ/Tjq8f1BzuDz3yS/fR4qeEJ+Uj6sN1z/1O7pyRH/kevPFj4bei56Pv6i8HeV33e/NHv50x9ef3SPxo8OvRK/mnhd8kbjzcG3Dm87xyLGHr3Lejf+vuiDxoe6j4yPXZ/iPg2PL/uM+1zxxfxL+9fgrw8msiYmRGwxe2oUQCEKp6YC8PogMjckAEC5DgBx4fRcPSXQ9L/AFIH/xNOz95Q4AVDrBUAcouEdCCNqthYAJcROjkPRXgC2t5frv0SSam83nYuETJWYDxMTb3QAwLUD8EU8MTG+Z2Liy36k2LsAdGRPz/OTEmKFjMhXbUKj4vrhvrXgHzI96/+lx39aMFmBA/in/ROxiBcTdvW10AAAAIplWElmTU0AKgAAAAgABAEaAAUAAAABAAAAPgEbAAUAAAABAAAARgEoAAMAAAABAAIAAIdpAAQAAAABAAAATgAAAAAAAACQAAAAAQAAAJAAAAABAAOShgAHAAAAEgAAAHigAgAEAAAAAQAAATygAwAEAAAAAQAAAQwAAAAAQVNDSUkAAABTY3JlZW5zaG90Ww+1zwAAAAlwSFlzAAAWJQAAFiUBSVIk8AAAAdZpVFh0WE1MOmNvbS5hZG9iZS54bXAAAAAAADx4OnhtcG1ldGEgeG1sbnM6eD0iYWRvYmU6bnM6bWV0YS8iIHg6eG1wdGs9IlhNUCBDb3JlIDYuMC4wIj4KICAgPHJkZjpSREYgeG1sbnM6cmRmPSJodHRwOi8vd3d3LnczLm9yZy8xOTk5LzAyLzIyLXJkZi1zeW50YXgtbnMjIj4KICAgICAgPHJkZjpEZXNjcmlwdGlvbiByZGY6YWJvdXQ9IiIKICAgICAgICAgICAgeG1sbnM6ZXhpZj0iaHR0cDovL25zLmFkb2JlLmNvbS9leGlmLzEuMC8iPgogICAgICAgICA8ZXhpZjpQaXhlbFlEaW1lbnNpb24+MjY4PC9leGlmOlBpeGVsWURpbWVuc2lvbj4KICAgICAgICAgPGV4aWY6UGl4ZWxYRGltZW5zaW9uPjMxNjwvZXhpZjpQaXhlbFhEaW1lbnNpb24+CiAgICAgICAgIDxleGlmOlVzZXJDb21tZW50PlNjcmVlbnNob3Q8L2V4aWY6VXNlckNvbW1lbnQ+CiAgICAgIDwvcmRmOkRlc2NyaXB0aW9uPgogICA8L3JkZjpSREY+CjwveDp4bXBtZXRhPgqMt8VPAAAAHGlET1QAAAACAAAAAAAAAIYAAAAoAAAAhgAAAIYAAChy7gpJuAAAKD5JREFUeAHsnQeYFEUWx9/ssiRFUMSAoICKOWFAMBwq5oRiOiOYE+Yz5zMHxJwFw2EWBZQzpxNRPMXAqQeiqCgqCiqIsmGuf+XVUFPbPWln2Anvfd9ud1dXV3f9q/tfr957VROLByIqioAioAhUAAIxJbwKaGWtoiKgCBgESprw6urqZOLEiTJjxjfSseNSsummm0qbNm20aRUBRUARCEWgZAmvvr5ebr/9dpk+/ctExZZYYgk58cQTZPHFF0+k6Y4ioAgoAhaBkiW8t99+Wx5//Albj8R20017yx577JE41h1FQBFQBCwCJUt4o0Y9KRMmTLD1SGy7dOkiQ4YcnzjWHUVAEVAELALFQXi180ViVSItWtnnSrt99dVX5ZlnxjXKt846a8uBBx7YKF0TFAFFQBHIifAWLFggk//zH2nXrp2ssvLKmaM4/xeJf/5u8Pee1H8+SWT2dxL/7WeJ1y4wZcRatpbYYkuILLW8VHdbX2I9ekms+wYiLRdrdI958+bJsGHXyy+//JI416JFCzn66KOla9cuiTTdUQQUAUXAIpA14c2dO1euvuYa+fnnn00Z/f7yFxkwYIAtL3w77R2pf/NRqf/gFYnX14XniUiN1bSS6g36S3WffUS6rp2Ua86cOfLss88FXtoZstRSS8rWW28tK664YlIePVAEFAFFwCKQNeG98MILMvbpp+31Ul1dLZf8/e/h4SCBNlf75BXSMOOzRP6m7FR1W0tqBpwp0mXNphSj15YgAt9//70ss8wyJfjk+sjFhEDWhPfKK6/Ik089lahDLBYzhLfYYs6wc95PUj9mqNS909jGlrgwx51YVVWg7e0h1TsOEWndLsdS9LJiR6ChoUHwxE+aNMn8ffvtt/LEE0+YDrbYn12fr3gRyJrwfvvtNxl2/fVCj4v069dPBuy++8IazvhEau85URp+nrUwrQB7VZ26SM2hN4h0WqkApWuRzY3A7NmzZdCgQUmPoYSXBIce5IBA1oTHPXBaTPv8c2ndurV0W2kh4cQ/eklqR54n8QW/5/Ao2V8Sa9tOag6+UmKr9M7+Yr2iqBFQwmv+5qmtrZUffvjB/GEvX3bZZWWl4Hsv5dlMORFeWFPEJwdkd+8ZEg+GIqmkqt1SIkt3kargL9a2g8TxzLZqG1wSl/gf8yW2YL7E582RhlnBDIpZM6Rh7pxUxUmsRY3UHHGjxFbeOGU+PVlaCCjhNU97QWyjR4+WRx55RJ52bPXu0xDrus4668jhhx9uHJZVgZmpVCQ/hPflh7Lg1qOC8JI/GtW7aoWVpbpnXxNeEuu+vkib9o3ypEyYN9uEsZhQlk/HS8PMLxplj7VZXFoOGSGyTPdG5zShNBFQwlu07YbN9N577w2mZp4ov/76a8Y3X3PNNeXCCy+UPffcsyTsq00nvF9/kAXX7idxRxOrar+0VG86QKrW3zGwsXXLGLxMMsZnTpWGSeOkYcKTSdpf1VLLSs0pjwSODJ1Hmw7H+fPnF/2wZFEQHh95KWkn6do11/OEmkFYzz//fK5FyBFHHGHmtuPELGZpMuHV3XdKEF/3qqlj1TJdpcXWgyXWa2eRqhaFrXfdAon/e7TUvTRcGn6cae7Vos8AqR54XkHuO2rUKOEDsYKzpmPHjvaw0fYf//hHUtqOO+4YxAoGw/kM5LXXXpNvvvkmkXPttdcW/qJk3LhxCScSeQ455JCkrJ988ol5md9//32BSFhlpqamRpZbbjnp3bu3iV9cYYUVkq7xD9544w0T72jTWaBhp512socZbd977z2ZMmVKIq9fxuuvvy5fffWVOf/777/LU040AIn77LOPhH1Q++67b0baxfTp04V7vPvuu8YuRdB627ZtZemll5YePXpI//79zVAt8YBpdp577jn56aefTC7sWrs7zjvSGRZOnjzZ4LbqqqvKlVdemabERX+aeNqdd95ZaF9fiGsdOHCgsduxMAfv5LRp0+Shhx6SDz74wM8uF1xwgdH2Gp0oooQmEV588suyYPhpEmvZRlpsd6hUbXlw4YnOBy8gvvqX7gr+7hMJgppbHnuHSPdefq4mH++1116CEdfK5ZdfLqjzYQIx+gsYDB06VFbOcFbK34O4xnfeeSdR9H777Sd//etfE8f+znnnnZf0Aj788MPGocTLzH0J7UglxFISPL7//vsLs1XChI/75ptvTjrFajWQZqbCcOmLL75IZKcTYGaMlcsuu0zeeuste5jxFmJp1Sp6WiJ2qbvuusuQXbpCIaa//e1vxkCfLu/ZZ59tCI187du3l/vuC97BQJj2eMsttwikbQVj/w033GAPi2Y7ZMgQuemmm5KeBxvdM888E0n+rBnM+RNOOMEQoHvxPffcI4MHD3aTimo/d8KLN0jtFbsGNrnAU3rItSJLptYQCl7r76YFTpNTg2loraXmpAfzfrtiJjyfILHFoGFcfPHFRqPLFIz1119fzjnnHGnZsmWjSxgGEybifsTpiNgthNkwxx57rJsk1wQzdiAYK4UgPDQSNA93CqK9X9SWmNKLLroo6dnC8pIHbRFBU3zwwQfN+ox0hixf5koxEt7UqVMb1XHjjTc2TotMOjLalDUov/7660RVIcsvv/wyVBNPZGrGnZwJL/7hC9IQ/FXvc3Ew6b/xB9IsdVowT+oeOENa9Bsk0mOjvD5CMRMeQ6Xx48cn6nv11VcLfzZWkuHreuutZzRMhtWzZs0yH6arbdmLGeKiuYQJmoBr5+GjQMvLRNDC3GF+165dG2kWDzzwgHz88cemOLTpTz/9NKnotdZaK/RDwmhOHX1hCEtdsFFZQSvfZZddzIfOkPrHH380GjDDZ0IwrGCuuPbaa2XJJZe0SY22Lu5oxjw/nkv3fvaiYiQ8TAG0ixWGsGPGjDHkbdPSbcNIEy19k002SXdps5zPnfAmPCqx3nsFq5wUmZGyvlbi746R2MZ75hXQYiY8hq0Mo6x07tw5YQOkxz7ssMNk+eWXt6cTW2YyQGJ2XrQ9wdCTl9+X//73v2a456bz0a+++upuUui+P5xFW/SH/e6FTXVaoGGdcsopSUPoAw44wNgB3fvYfRajQMP86KOPbJLBgOeOEh93VumB9JAOHToYM8Qaa6xhsEdDZthbLMJK4T4p8T7wvmQrfBuPP/544rLTTz89yV7JqMB2vmSiM0mamZW4cuEOsb4zZ/5pmyeVjofFSqIEbZM60clha0TjZioisYNooXwTSM6EFwTcBVdXRd2/edMbggUK8uw0KWbCuz6Y+fLSSy81whzSSvXBcgFOgjPPPDNJK0ELxOaFbc8Xn7h22GEHOeaYY/xsScf+cJZy77777pTaU1MJ79FHH02QDw/j2wuTHvD/B4RjYFO0GhoeXOyW9mPxrxk2bJi8/PLLiWS0TDTTddddV/joU32giYuaaWe33XYz2py9Pe/Kiy++aA+z2uI0cx1YOPRcXNh3O9D7778/7RJuOLh69Vpoi7/xxhvl+OOPb/Rc//73v+Wqq65K0lQbZQoS6FxPOy3wN+iP+ITB0zitmAnPH2ry9D179hRsSVFOCLeGGKD9oSna0V+ClXB8IRj1jjsCx9D/hWEhNsNU9/GHsxtttJHgaEklTSE8vNBotTgrEHp76oenMZ1A9AzrrKSyU+KE8EkCLY6Ps5i0OVsXu4WUfTsti4Jss802Nktet4UiPDr5bJ9ZCS/Dpi1mwsMj+OyzzybV5IwzzpC+ffsmpUUdMHwgjsoSBPmibHkM/RiOco0VNMQ+ffrYw0ZbXytE+9lss80a5XMTmkJ4hJ7gELGCrQoPdCbCkAjvoxXCVa677jp7mLQN62hwzGy//fZJ+YrtAEfLhhtumPRYhfy11kIQHvbdMFMKThM6+z/++MNELvhB1Ep4Sc0efVBKhIcmM2LEiNAhaVQNH3vsMWGoYYXhmLVH2TS7hQBYNcdKFDlyHnuKO+TNRCPkuqYQHhoWGosVHDh8BJkIHz6xZ9bLyvAbDTVMg/UJj7xou8U8lAUD2vWggw5KwMEQnBjNQkkhCI93kFGIFZxR1GuDDTawSca8MHbsWBOXaolPCS8BT+qdUiI8Gh3PZTZCgKzvnWXoitHXFz8vZADBhn3ovi0NW89RRx3lF9nouCmEB8HawG3i80aOHJkV+TMcxntrBTsemoMvPuGhcRRjcLH/3Nh8TzrppETy3nvvndYGlsicw04hCI/3CNuhlVQOF9dBo4RnEUuzLSXCy8RA71eXD/zQQw9NSk6lGTF0wxlhBWM/9/WFD+vzYGUdK4R6rLLKKvYwcpsr4aGZMU0qnxLlifYJb8stt5RTTw1iQYtciEskRtMK5o8rrrjCHuZ9WwjCI0TpP8HPTFiB/HCgRYnNr4QXhZCXXkqEx1ShI4880qtB+kO/jueff34jW48t5cknn5Thw4fbQ1lttdWMtyyREOz4w1mW32e4mYnkSniE2Bx8cDDjJ4+SKeHtuuuuJg4vj7cuSFFnnXVWEsGVIuH5XmZsrXj+8RCHCR0h5golvDB0QtJ8MiimqWW+0yJXwoMo3Jg8ZhIw+yJMmLnAFCI8olZuu+22pHg/fzhL/rS/f/L/wnIlPD8Exj5bU7bgGzbX2NfwmEvra8lNuW+hrsVj7U7pwxPNLJFCSSE0vDvvvDO0UyfmjncM7y22Sd8brYSXYSs3lfAyHcrxOP5UsVShEeTPF+H5dcQwTM8ZJcQ/uZPO/ed0h7MY9JlnSUBuJpIr4flDcwKuo7ysmTwHeaIWvCxVwsOhs+222yaqT7AxNrBCSSEIj46W980NeA57fjQ+hroQIOFQSnhhKIWk+WSQSsPjcgId3dVVcCK4HqSQWySSmkp4udjwwgiGIQIriUQJnj2GvVYgF7Q8hCh51znBR3XuuefarGm3Yc+TyRLvxJjRVlbsHFd7nM9tqRKePx0MZ1M2c43DMGRKnfWEgjkxitaJVQjCs8+AsyzTxQp4B5XwLHJpttkSHtOM7AtA0Wg7W221VZq7/HkawzcvpRVfc7LpdutreKnCROw1/hZP1iWXXJJI5mWNCktJZAp2sBV+9913iSRmH3Tv3t0s7YRGZyWbuECuyZXwuHZQECfI9VYY/hTiF89KlfCIofRXl2HaoLuQg8Uuky1mBN+LzVQ6fgICKSThUT5aPav5EIJC0Lj73XHeFSU8F40U+9kSHjYSfmnLCnFPruZh0/0thlUCZQmctJIt4XXq1MnMhshmcUv/4yWQmIDidOLb6WyQL4Zx60WDPOmJw2LZosonCNpf149YwbBFAvwyMB+wpqAVNE136pNNb+rWx6xUbHjUm6XKWEnGCl53f/kvey7dloUXXNssw2UIyEouhOcPu6Omltl72C1DXcKm3nzzTTO/nLX7XFHCc9FIsZ8t4blLB1EsoRh8iOmEOYR+DF2qSe+U52t4pLHMkz85nPQwYTgDKfCLdFaOO+442W677exh5JZlqBjO2EDdbt26GU0RsrJpuThReBZ/DUC8wpksosqUI2LNrDDU5mPGjphPKWXCI+YSs4wrmCHC4i7dPGH7xD1aUwbnKdftLHMhPN+xkinh+c/H6AMN305lVMLzEYo4zpbwfM2HYvlAWBYpSrA/ET3OemKu4D0l+j9KwgiP5YgIp4gyuLtl+YsPMA+UOaW+h8u9xt2/9NJLk4zekB0zDqykc37YfO4W+yd1du2grGZCPFU6AUdI2J0qR/u5swvSlZHJ+VImPIaBvn0WGyv242yEhWr9FVZwZLnTGlm6zJ1KyPvqzr4Jux/B365JxCU8lhBzpw5iw9t8883Dikmk4dzgHVDCS0CSeidbwmO4cPLJJycVyi89ofmFaRrYPCCODz/8MOkaDtINh8MIj+twy9OzpSIuVH4/JAHCyiZ417f/YR+yQ/JUc1F5xlTimwUYKoWtmBFWBr+8hdPFFTRGhtxhy8S7+TLdL2XCo47YbK3mY+tMADL21kyEjgXPp7vcO+0NIbnvHIHnrrefNkwVj8lPErCslisu4RHf6YYJ0TFi7kglKBEoATkTXvyziVKsP40Yn/qW5Pu3arMlPMCnx/QJDMMwHx6/UcFLgcoNYeCBtL+PQMS+a4PKdkhrlyniGVh7jInz9IDWiEw69jXmiDKEdgUNig8hG/sfWhgaFVqDL02xn/nLL1E2dSHEgPXRuB/3jlqdF9OAXz9MC9ib+FDDtF+WyyJEg0Us6WjopKKk1AkPUwbOBt/Ij3ZFJ+qSlo8Bv03CyGPChAlJp8IW/2Q9PB9rcPYdHRREmxLhwAIQrriER7pvg+R72WKLLdxLkvbRFqlXzoTX8K9/SKxNO4ltuFtSwc190PDGg8Hv3LaR2EYD8voo9CJukG26sBRu/tlnn5nekp4wTND0rJ3LnseTi/HbnetoHQE2j7/1NTzCUlj1wzoNyI9Wg6eSF48Vj+2ab25ZrPuGlpmJncy9jn3mq/JbGq5AsDgr/JfdzZNqn84iKpSF+uDgYajE6ithwofMFCo8kL5A6NircKjgteTj589tYzomHEZRUuqER73QgumsfGEtOjQxVofGCYYQlI5DAHLBKeULoxc3TMk9T4frxmyymAO2NdqP7wCtDfJkqOsuFmrL8AkPJ8utt95qT5st3yRthiZnBc2OEYy1KeZMePLHPFlw8Q7Sgp9j3DmYiFyVX4OwfeCMt8FKx/WjLpP6j16TlucGk4rzvOw8WgEfmJVMA4lZiZiYJPdDsmX4W4iKlw9Nj+WarPDSucc23W59wmPaDb/whabG8CATQePkpbCxU5lc4+bhdw1wdLjCMkm8mE0Rn1T8spgJwocWJZAZHwaGc7f9ovK76SyL7845dc+x7z9bKXlp3brg0MnUVOBe5+6jXbE2YJQXHa9tNstmQYhuR+UTHg4WbIfu72m4z4P2CEH72mvuhBeU3vDinVI77japWmktaTHwXIl1zmwJHvfB8rIf/BB47eOXSMOMqVKz59+kqm90r5zL/fhoWFHCFV72VA4INy+kgxPA/YlC9zzDLIbMdk051pxz129Lt3JxGOGhvjPc4+clsWe5Bnz33sxvhRxTDQfc/FH7YIQ25GqsvKSU3xSBpNAeqUeYphw2hzfsfthUGcKzFpy1L4blI4324GNimhJe5ygpF8KjfrwjdFhRBBKFAR0kzjE65HRhRzgX0PjTCWTFYgCuOcEnPMpgTTzaySe1VOU3ifAkWEq9bui+Uj/zi2C192pp0XcPqdr6MJEllkl1z/ydm/2N1L9wu9S//bTpvatWWlNqjr+P8Vv+7hGUhF3BnyOZbhZC2APw0THMpDx6QoYK2CJcg669zg0R4UVKZU+JIjxbFsTHrAheZno9nAoMW7HXRdm/7LWZbtGgsLlZyWWJKntt2BbCZjlvO9whmp8Oh3XQXNtk2LVuGpo2RnU0BLCAqFk/EM80W8rMdEjPtS7B005RGo77DMW6T0cLsRD+42pXUc/LN4HnPJtQFswekGMUSTGkxawCkbpY8kyDBg1q9CgEmBMgz7vnxhX6GVEasMk2jfAodfr7suDmw4OfuGgw94i1qJHqXttJNVpWlzX9++bnePokqX/jIamf9FJw3/o/71sdvGwnPSCx5VfNzz2cUmh8fqvUCjaHqEUhbZ5FuU1HeIV+Fj58XlRsg1Z4aVP9eLjNp9viRADtCYcPHQOONbRrOmeGmmjAaGFh0QaZ1AatHXLCRkvnC9FiP8Y8QUeWi9Dx0KET7G+XLaMjhIwZZdiZNk0nvODpGsY/JLVPXN3oOas6rSBV620r1T37iqwYeLtytavVzpf49A8k/ul4qX//eWn4aeFUJnvTlvtfKLFeu9rDvG5ZPh1SsVJsP7nX3ITnOywgOghPRREoNgTyQnhUqmHstVL7ysjI+qH5xbr0lKple0hs6a4S69gl+HWVDhJr2VbirQJWD1g/tuA3iS+YLzJvtsRnfSXxH7+ShpnTJD5jisTr6yLLrtnhSKnqn34V3cgC0pzg43VXk0hnU0tTXN5PNyfhEVKDd8wO7fCesnBorvMy8w6OFqgIOAjkjfAos/6JS6Ru/Cin+MLv1mx10J9e4gLditAShrP2g+Y2eAWj1okr0GOkLLa5CI8hCV5MhrRW+vfvL0OGDLGHulUEigqBvBIeNSM+r2709QnbWqFqG8Nmt9eZwQ9u71GoW5i4IbyrNiCYG7lLIBXsxlkW3ByEx+Ru4qgIKrXCVCVCcNL9yLLNr1tFYFEjkHfCowLx/74pdY/9PdTWlo8KVnXqIjX7XCDSfeEP9eajXLyZX3zxhflxaryOfpQ+RlrmGmYynzMfz5NpGYuS8HDg4GljDqUrkBweu1RhHG5+3VcEmgOBghCeqUjt79Lwwh1S9+pIideFzzTItsKxmlbSYptBUrXVYJHqmmwvT5s/bIUOexGR+bjFCS4tNllUhEcQ9dChQxtVn/ABhvnF1hE0elBNqHgECkd4FtogVq5h/CNSN3GMxOfOsalZbauW6ChVvXeX6j5B8G+BY/yYt+pPu8KlTSQ6kffFKIuK8AhRcFcxBgvc/ixYSgCwiiJQ7Ag0ifAgholvTzRDwKrqP+cmstouAZyNJJj61TA5mN4z5S1p+HySxL+bHjnVJxZoU7Hlukt1jw0ktmpvia3xF1lUU9dYnglHBYG+BLUykb9fsC5+rjFHjXAoQAL2NPeHlPlVeZ65EGKj8cEDbCDAXOfKFuL5tExFIBUCORPe3F/nCj/VR9CgKwxvBu410ESuu+mN9uf/IvE5QTzdb0EIyrw5fy7ZE4SpyGJLSqzDciKtF290yaJIIHCRj5lo+3RTZRbF8xTbPVhCG68sixxkOiOh2Oqgz1O5CORMeEwUnjpl4e8uuBB2XqGz8BudKoqAIqAIFBMCORPeiOEjIidhY+AffOhg1ZCKqaX1WRQBRSD3ubR333V3yiWPBg0e1OiXkRRvRUARUASaE4GcNbyxY8YmJun6FWjfoX3KhRP9/HqsCCgCisCiQCBnwiNEYczoMUk/smIfeNvttg1d8sie160ioAgoAs2BQM6Ex8OyLv3rr72eWNuK8IQ+ffvoxPHmaEm9pyKgCKRFoEmEZ0tncUbWuOrQIVj9JFgtQ0URUAQUgWJEIC+EV4wV02dSBBQBRcBHQAnPR0SPFQFFoGwRUMIr26bViikCioCPgBKej4geKwKKQNkioIRXtk2rFVMEFAEfASU8HxE9VgQUgbJFQAmvbJtWK6YIKAI+Akp4PiJ6rAgoAmWLgBJe2TatVkwRUAR8BJTwfET0WBFQBMoWASW8sm1arZgioAj4CCjh+YjosSKgCJQtAkp4Zdu0WjFFQBHwEVDC8xHRY0VAEShbBJTwyrZptWKKgCLgI6CE5yOix4qAIlC2CCjhlW3TasUUAUXAR0AJz0dEjxUBRaBsEVDCK9um1YopAoqAj4ASno+IHisCikDZIhD79ttv42VbO62YIqAIKAIOAkp4Dhi6qwgoAuWNQGJIyw9rI0suuWR511hrpwgoAhWLgBJexTa9VlwRqDwElPAqr821xopAxSKghFexTa8VVwQqDwElvMprc62xIlCxCCjhVWzTa8UVgcpDQAmv8tpca6wIVCwCSngV2/RacUWg8hBQwqu8NtcaKwIVi4ASXsU2vVZcEag8BJTwKq/NtcaKQMUioIRXsU2vFVcEKg8BJbzKa3OtsSJQsQgo4VVs02vFFYHKQ0AJr/LaXGusCFQsAkp4Fdv0WnFFoPIQUMKrvDbXGisCFYuAEl7FNr1WXBGoPASU8CqvzbXGikDFIqCEV7FNrxVXBCoPgaIivJ9++knOPffcrFrhkEMOkd69e8vEiRNl+PDh5toLL7xQlllmGbP/0EMPyWuvvSaLL764XHXVVVmVrZlF7rvvPpkwYUJWUAwYMEC22267jK+hfO6DXH311bLYYouZ/ccff1xefPFFbTuDRuN/H3/8sdx4443mxNprry3HHnts40xeyowZM+TSSy81qd27dzfXnHHGGdLQ0CD777+/bL755t4V5XVYVIQ3a9YsOeWUU7JC+NBDD5V+/foZUrvrrrvMtVdccYV07tzZ7N98883y1ltvSU1Njdx9991Zla2ZxXxQdCbZyM477yz77rtvxpeMHTtWHnnkEZOf9mrXrl1iP6zt5syZI5dffrnJc/DBB8taa62V8b3KKeNvv/0mRx99dKJKd9xxh7Ru3TpxHLbz6KOPypgxY8ypgQMHyhZbbCEnnXSSOT7ooINk2223DbusbNKKlvA6dOggm266aVqg+/TpI/RU//rXv4QGR6688kpZfvnlzb4SnoEh539oEJbwNttsswQZpSpwvfXWy4qEsiW877//Xk477TTzCIMGDZKtt9461eOU9Tne9cmTJ5s6HnPMMcL3kEogN0ZSyLBhw8xWCc/AsOj/uRre+uuvn5W2V1tbm2hIhrOxWMxUQAmvae3oEp7bkTSt1OSrowjvl19+kfnz5xvtfKmllkpcpISXgMKMXnjHkXXXXTfRESzMsXCP4exZZ51lElZccUW55JJLzFD2hx9+MGkoGa1atVp4QRnuFa2Gly3hRbVNNoRXV1cnLVq0iCqqItMLQXh//PFH0ocVRXhRgCvhLUSGjv7II4+U+vp6k4hZp2XLlgszOHvucBZTQP/+/Z2zme2W+jdSNoT30UcfycMPP2xaDRW9Y8eOZj8d4dHrPfHEEzJlyhTBNoStb4011hCGZQyVqqurM3sTyjRXPggvHo/L008/Le+++65Mnz5d+EjBdfXVV5d99tnHDMnCbHjPPPOMvPnmm8ZpgWGdofXo0aNlwYIF8u233xrEcUbR1mj0559/fkV2WG4bHXfcccaJF/Y6usPZW2+91TiHsANae+juu+8uG220UdKltu3eeecd03YQ63LLLSfrrLOOsf9169YtkZ8RwNy5c6V9+/ahmib3/Oabb0x+2pO2c+WFF16QV1991bQlmmibNm3c03nZLxvCwxObrdMCgK13MAxNjOHYiiqZ9NyPKZchLR/AtddeK5999lkYxCZthRVWEDoehA4qymnxz3/+U0aOHGnyhf277bbbpG3btmGnyjrtgw8+kGuuucbUMWpkBNGceeaZJg/vNYSDYM+LsuHNnj3btN2XX35p8ob9o8w111zTnOIZeBaEfRspwfHvv/9uNFH2kTBihuR4DyDCW2655c+Mef5fsYQ3adIkGTp0qIETrW633XaTlVZaSbBnoFn8+OOP5lw6u0ie26Poimsq4REC8emnn5p68SJvs802xsnEB/j6668nNDVb8VSE9/XXX5tr+BBtqMxqq60mPXr0MJfvvffeFanhEVJyxBFHJDRnnHe80664w1nCV6xDMIrw0OwgM6tJo9HhEMEL/OGHH8rLL7+cKP6iiy4ybYo2jhaHHHjggUmhSS4pc37LLbeUww8/nF0j2GqPOuoos887QrhZIaRoCY/KpnOxX3DBBYJ2gGSj4WGHoFfDKI5GgOaCGm4FtZ20Tz75xCTlotnYskp96xIeH1E6bZdO45xzzjHVnjp1qlx88cVmH6cD+0sssUQCEnCG4BguWUlFeDaP2vAsEgu3hFwxHESGDBkiG2+88cKTwZ4dztJ+d955Z6JjiCK8V155Re655x5Txp577inEVrpCJ2bj+axW6WpxmIWsg4Tr0MzR0K3gILnhhhvsobz99tty0003mWPeHzqyQkhRE166CuNlwtuEZEN4fGAWbLe3c+/3888/mxeHtEL2OO49i3HfJbxMnm/ZZZc1wcPkhbyIo0OIr+TD8IWPhHAKa3RXwvMRyuzY7Vx69eqVGKZytTucJUif4aSVKMI7+eSTzSiHjuq6665LRD3Y69hCiBAjwhAUDf6yyy4zigLEionJdpCQ2FdffWXeAUZXCO+WVTRuv/12eeONN4xmynU2ysJkzOO/oiU8gE4XU7TDDjskAMuG8Ijgf+qppwyMqNVRxlFsQhjYMYrT6JUoLuExDLH2tSgsaDcbvGptMmiGqV5ihkEMhxAlvChk06fTeWMz9bW4xx57zDh7KOH0008XZmVYCSM8hrN2SMkICg0vTHAU2qHt8ccfL5tssokhQKsZnn322cYxxTd02GGHmSJwLKEZ0sERNN23b1+Tbp+dWE87tA27Z1PTipbwrJqcaQWzITxsd7aXyaR8htY2qDmT/OWUxyW8bIf2vOS87ASBc22U0PnQCSFKeFEopU93h40nnniibLjhhuYiO5zlPUaTcrWnMMKbOXOmIcb0d1yYg2lpKCDz5s0zGjtnOCadwGjaHyKGDJniSRpkB+m5JgqchNjNCyUVSXjM18TwiliVOxXABGOi7VWiNIXwBg8ebHpytAQb+hCG4bhx4+TBBx80p5TwwhDKLM0NLCa85IQTTkgazhJmNSiYmeJKGOEx9LR2WPJm8o0Q0mLtfMxlnzZtmljzBuFihCVhl6Nc297WG/vcc8/JAw88YO7jDoPd58zXfkUSntsTAnBUoGa+QC7lcppCeAyf0BZwDKXqMAgNIkQIUcJr2ttibW+QFO/2qFGjEsNZ60117xBGeDj1mKOObL/99nLAAQe4l6TdtwRGRtoTjY74S7zou+66q7HlWUJlehtaJwshWJJOe4MmZKhIwnPn3RKPFDX5nGDYX3/9Vbp27ZrkYm8C3iV3aVMIj5eZYGMk7GMjHXsRRnRsT4gSnoEh53+ueQDyu/fee02sne8VtTcIIzzOWZuanYJm87tbHCXWM4yGt/TSS5vTBPCjXSIQp7Xp4aW3gcrW3MEogA4Pm561A5oLC/SvIgmPcBRsHICMfQkjqj+lbPz48QmtZKeddpL99tuvQE1Q3MU2hfDee++9hLNn5ZVXNkt/+cOj559/Xu6///4ECNkSXq5TpBI3LLMd4kchOsQN6IaQWB3FlyjCo01oGwTy8mdg8O2ceuqpifnr/kot1mHFsNU6UiA+az+0QcqEKfE9IotitFWRhAe4biBmp06dhJ6GAFYaB62E8xjckWyN9eaiMvnnEh4fDV7YdNKlSxdZddVVTTb74nNAjB5GbHAmwBtNmyBvVzIhPLRuG1pBWdiOGDb37NnTLapi920IiAsAs114z32JIjymnIExxIYQmI+TgYgF4lNZp9A6/sKGoixBxTdkxY/L8zs6d/aHvaYQ24olPOK/sC2glqeSStcgXMJLhZN7zo2iJwaM2Czbi7v5wvYzITyus0Mit4xKnVrmYsC+TyapnEZRhEc5xKvSHpb0SPOFoTLrT/pT+ujQ0ACtsD4i6yRaYQaHnd5GGuFhvDeFlqIiPHfsH9ZrpALDndbizuOjwQh+DTOcYz9ipQ48SPRortCTYaz1VXk3TyXsuzFymdbXJTyuAVuGPHjGrdZMOvF5u+yyi9mGLR6Qqu3ef/99GTFiRGIKIOUp4YGCGLuz1YA55j3G+RAmLuExytlqq62SshEyQlAxXldfWHh3r732Spo94+aB8OzSU+4kAZuHVV5QPJBF1XZFRXgWiObY8lEySRobE5OebQR4czxLOd+TD4g/hr1oByqlgQDKAR53CAznBN+Ib/cuhZoo4ZVCK+kzKgKKQF4QUMLLC4xaiCKgCJQCAkp4pdBK+oyKgCKQFwSU8PICoxaiCCgCpYCAEl4ptJI+oyKgCOQFASW8vMCohSgCikApIKCEVwqtpM+oCCgCeUFACS8vMGohioAiUAoIKOGVQivpMyoCikBeEFDCywuMWogioAiUAgJKeKXQSvqMioAikBcElPDyAqMWoggoAqWAgBJeKbSSPqMioAjkBQElvLzAqIUoAopAKSDwPwAAAP//0vCrQAAAPgdJREFU7Z0HnBRFFsZrTSgiiBEQFTCBGXPAEzGdmPOdOQAqIkZQjIgEFUVBVDCeOWP2TCAqZjkTJkxECR4CHipguvrX7DdTO9szO7PbM7s71PvtbKVX6dWrryt1ddlfloylmTNnYpimTZs6M/wLEggSCBIoNQmUBcArtSYN9QkSCBLIJIEAeJkkE/yDBIIESk4CAfBKrklDhYIEggQySSAAXibJBP8ggSCBkpNAEvBmzZpl2L9YeeWVTVlZWclVNFQoSCBIIEggCXhz5841ixYtMksttZRp3LhxAL2gG0ECQQIlJ4Ek4P32229mzpw5JVfBUKEggSCBIAFJIAl4eAB6CxYsMIsXL3bTWzEFM0ggSCBIoBQkUAHwSqFCoQ5BAkECQQKZJBAAL5Nkgn+QQJBAyUkgAF7JNWmoUJBAkEAmCQTAyySZ4B8kECRQchIIgFdyTRoqFCQQJJBJAgHwMkkm+AcJBAmUnAQC4JVck4YKBQkECWSSQAC8TJIJ/kECQQIlJ4EAeCXXpKFCQQJBApkkEAAvk2SCf5BAkEDJSaAC4P3yyy9mxowZ5qeffjJ//vlnSVRWlyE0b97cNGzYMK86zZs3z3z++edm9uzZ5o8//sgrbl1kXnrppc0aa6xh2rVr527FyaeM8+fPd7qxcOHCkpBFPnUPvKUhAfQ/CXiA3ZdfflkyQJfeRADfRhttlDPoAXavvvpqSXZuGn7XXXfNGfQAu6+//jpdpMEdJFDvJJAEvG+++cbQyVdddVXTsmVLs8wyy9S7ykQV+PfffzfTpk1zN8Fw1996660XxVbJ76233nIjGkZE66yzjll22WUr8dQ3Dy6HmDJlihuxMuLdcccdc6rCF198YX7++Wez1lprmdVWW61kdCNT5cePH++Ctt5660wsJeW/pNQXLEgC3gcffOBGd1tssUXJKTQV/eijj9xdf+3bt89JWZ988kk3uttmm21KAuxUaUDv/fffN4zyDjzwQHlnNT/88EMni1LUjaiKLykAoLovSfVNAl6pVzrf+o0aNcrpQ66jIClPfTAZvUKHHHJITsXNV3Y5JVqHmUJ963Dj1LBoAfAyCDAAXkowAQBSsihF25LUvgHwMmhwALyUYJakDkGtQ31TbV9qtgB4GVo0AF5KMAEAUrIoRduS1L4B8DJocAC8lGCWpA5BrUN9U21farYAeBlaNABeSjABAFKyKEXbktS+AfAyaHAAvJRglqQOQa1DfVNtX2q2AHgZWjQAXkowAQBSsojLxkfvJ02a5A63R6XZpk0b06xZs6ig2P3ial++ePjxxx/XqHwbb7xxzm8AVSejAHgZpBYALyWYuDpEKsW6bSt0fSdOnGj69u1rMDMRr0LeeeedZpNNNsnEEpt/HPXl1dR9993X/O9//6tRuRo0aGCefvpps8oqq9QonUyRYwe8uXPnmrFjx7pv22bKNJP/2muvbXidh0rHTfk2arEA79tvvzWfffZZsrqbb765e5Ut6VEASzh4nF2o+epK9tQqhtLWp59+ehIYDjvsMPfWi8+FTrz33numZ8+e5rjjjvODCmKPo77/+c9/TLdu3ZLlY4S67bbbJt3ZLLwFxeuLosGDB5vddttNzljN2AHv6KOPdpcQVLeUvK/Zv39/s9lmm1U3ich4+TZqMQCPm2lOOeWUCh895x3mf/3rX6ZJkyaR9YjDMwBedinmqyvZU0uF+mDHq33cwPPGG29UesA/8cQTrg/UZ8DLp+yqryR19dVXm06dOskZqxk74PHuKS/pd+3a1fCkeuyxxyoVeK+99jK8lwlxFdXIkSNdnO2339688MIL7kaT4cOHG0Y7cVG+SlwMwHvzzTfNoEGDKlURv0033bSSf1wetQl4rF2RP9O5X3/91Y1meb+5RYsWcVWvxunkqyu5ZOiDXffu3c2LL77obqApVcCryQiv3gEeU9PHH3/c0KFB+nQaNmyY2WmnnZLegKTi3Hfffea6666LHfTyVeIAeMnmiW3X8ocffjDnn39+pYVtRju777676dGjR50Avnx1JSWpaFs62J100knmH//4R0kDXrQkcvNdogAPkRQC9PJV4gB4KeXMV3apmBVtPPx4CEKNGzd2u5BTp051Iz38Vl99dXPjjTcaRge5EmvGLAEAmsccc0wsi91x1Zc6RIEd/rkA3hFHHOFkxG013PiTTk2bNjUnn3xyjdd846hv+hpeelnzcV955ZVmjz32yCdKzrwFmdJqtFadEZ5K7oPeiBEjDNvVNaF8GzUAXkra+couFTNl+/HHHw1LGdB5551nDj/8cAdSixcvNk899ZQDOnb4AMKHH37Y3buXil3ZRrwHHnjAPPTQQy4tpsqPPvqoOeuss5L5VI6Vm08c9SWnr776yi3kUy+msYzsRLkAnnizmch04MCB2ViqDIujvnECHg++m266ybRu3brKsufLUGcBj4oI9Hjio9hlZWX51i/Jn2+jBsBLii6WKS03JtPJIdZ111133VQG1sbFpMcee6y7aJSp7QknnFAhXA6A7aWXXnIdYrvttjOnnnqqG9UBgGx2cUFp1DKK4udi5qsrmdJkxMnuI6PP9DXKmTNnus2qcePGVbp/khEra9ii3r17V7qsdc6cOaZLly7uVANr4DWhOOqbD+Bx5OT222+vVOS7777bLYURAA/1ihv0agXwBgwYYPbee29XYZ5+bEFrVJguBZ6M7777rgO8XG8rTk8Dd76NGgAvJcV8ZZeKmbIBVFw4+v3335sDDjjAXHrppanActvQoUPNPffc467i52GXThxqHTJkiFlppZXM2WefnZz6Ah6AAhthjPAAmJpQHPWdPn161gtWuUGbEw2Au098VoB1TuTFg55RYtSaFt9Z6dy5s9lqq63MLbfc4ieRtz2O+uYDeNwi/txzz1UqJ2v7gB6fYuBzE4UAvYICHgrqD+Mr1dDz4MMyKHs6cSaH0V36Rkc6X1XufBs1LsADrCk734WoKaEAvXr1qvEObm3t0t51113mhhtucGJgKsZITG8T8NGoiy++2O1eskPNKEcESBKPXX8Azb+U9Z133jFXXHGFOeOMM5IPUcWrrpmvrkTlIwA46KCDXL2ieNL9fLC7/PLL3U42AJAN8Di3Wt9GeFUBHvXlbB4PvbhBr6CAx1OKBUgOUWLPRI0aNTKnnXZahZ1b8dZ3wONEvTqQ6lQTk4/vsAZWE6otwEMHrr32WvPggw+64nPmkPOWfEcFgGCdD1p++eXN/vvv76a4rMtxhIMHJ+DBGwgQaQGKzzzzjLnqqqvM+uuv7/zj+Kf2qsk3LfIFvHSw+/vf/+4elEsq4HEOj9MacYNeQQEvDuWr74B34YUXmk8++SQOUbg0OM7Tp0+fGqVXW4CnQt97773mjjvucGcw5Sdzww03NGxSAeoTJkxw074TTzyxwtfm+KAQU2JAr1+/foYHZpxUbMCLAjvqoynekjjC08HjuEEvAF6GnhLXlDYAXrSAOXSsw7esR7E4zUYGQMjyBkcxWJviHU3Wd/XVOL6uxxoXIyCOZNRkIyu6ZPmv90alk+sIjwPYvD7GsRPeRdXaNmnyIanRo0cvcVNa2lUvJiAH9ICHH1NhdvBr8oALgIdEIyguwGPtiY4dF/HhHUY8NaHaHuFlKztfVWNNDiBg+stGB6/f8eYOciSMDQqmvIWiYo7wWJNkpzYbcfxmgw02qMBSipsWvKzAhmY2Ylee3enqUsEBj+MIUqBcC8l3cVmYZr2mvk9p2ax4/vnnIzcteJeWTyamE9NW1rXSiQXcffbZx6y44orpQXm56wrg0WkZsfEGRqtWrdwIj+ksGxHnnnuu04H777/fdOjQwfAA4lOie+65pzvWAvDF+eqhL0DpazHW8Ji6AfLsXEcRu7C8hZJOArxS2rTgaBHv1U6ePDm9uu5wOuc16RtM9atLBQU8GoUnMS9J50us0aAE9R3wstV7SXyXFnlwNu22224zrF2lb2ZxJIFrkTiWcM0117hLFLhnjSMajOyWW2459/YCSwVHHXWU4W2EuKmYgLfzzju7OkYd08hWr1IEvGLUt6CApzctMj2loiqoCweOPPJIdwQjAF6UlGrmV5sjvNdee81ccMEFFa4Pa9iwoVurU624TIBXxtABRnas9/FUX2eddcTiwgE9TuVfdNFFlW4cSTJWwxIAL3+hac0yl5iZjqVkixsXwBcF8ARe2SqkMIGk4gTAk2TiM2sL8LgZ5JxzznEjft48YC2GdyYBPN45Zf2GkR1HTDiTyUYF5/O4EPLWW291VwYRh9fPIMJ4I4EpMDuZXC0WBwXAy1+KAfC821IEXrmIMQCecVdGleL1UJyl4zA6u7BcEiDgkl4wzWUNjx3bRx55RN7OZLeWM2ms4/AKGu/ico4PGjNmjDuzxXEd/xYeF1iNf3EAHrvMgDOjmWxnBN9++233OlyY0mZvqDDCyy6fSqH5KnFcu7SVCuJ5LElreP6rVrxH6R87QCQsWLMmx3ceWJdjvS6KUHzAkmMKvF3RsWNHx0Y84vAOLq9c1YTy1ZWovNiI4N3hqAX4dP6aTPFK6dWydLn47gB4vjRysOerxMUAPHZoeYUonVisZ/G+UFQbU9pp06a5NyWoE2ft/B1WwtikYvTHuToOJldV/88//9yN6uDnvVo2P5jetm3b1gFhTWSXr65kyospN6/FpW/M+PzMfrgwt7ojvLoCeDyAeNjkQjUB+JruSoc1vAwtVAzAYxRAR0dZIDovDYpfIQ7Uqqq1AXjs1PNqGEdxWLNj6smBYo4tMdIlnJf+2YDIdERD5fdNvp/CVULE5Xwia4J6/czny8ceF+Dlkmep7NIC7jx4aMtsAI9MagJ4NQX42AGPKQbHCOKm9FFBvunnq8TFALx86xAXf20AHmVnYZsLAxYuXFipKpwxZA2uUB9vqZRhFo98dSVLUlUGlQrgVVnRcobarm/sgMeiMi/8siYTBzHS4etHHGWoyagnXyUOgJdqvXxll4pZ2TZr1iy3+cC1Rxw3YZTHFf/77befO2NXOUbxfeKsb1WlBwA4SB51P1y2uHXtPrxsZfXDaru+sQOeX7m6ZM9XiQPgpVovX9mlYtZPWzHry6tzbOhUl+rKjce5lr+26xsAL0NLBcBLCaaYAJDKtfZsxawvG1e8EB81za9KAnXpmxZVlVXhtV3fAHhqiTQzAF5KIMUEgFSutWcL9a092Rc65wB4GSQcAC8lmAAAKVmUom1Jat8AeBk0OABeSjBLUoeg1qG+qbYvNVsAvAwtGgAvJZgAAClZlKJtSWrfAHgZNDgAXkowS1KHoNahvqm2LzVbALwMLRoALyWYAAApWZSibUlq3wB4GTQ4AF5KMEtSh6DWob6pti81WxLwuD6b9+G4xULX7pRKZflACt+55B1LLpfMhfiACu938haAPiCTS7y6zsP7u5yF4t1TDoHmQlx1hCxKUTei6h8AL0oqpeGXBDy+LTBv3jz3LQW+KVEqoAfYcRsHr+JwK8V6662XU8vxvikvuvOiMzftlgLoAXZTpkwxXLXTvHnzCh+0ziYUrmTn04hcsLnaaquVjG5kqnMAvEySqd/+YEES8LhgkdtmGeWVIjG648ohburIhQB/vrnAyKa+ELdU6H3jKLtuseBhtssuu7ivulM/4iAf2t6Pjx9x+BARD0TFRx7YFQcTIr7shCst+fvx4SdceWKPCpdfejj+If9Em0nmkjOyRT71Tf6Uu9CUBDwyAvQY1fz0008lA3woAzfrMqLJFewkdECPe9cYEdUV4PMBgHLKrTLLFJjgVofAr1mzZu7OOEa7fqcQP7z468fUFzvfmEAOvPDPk1JpCrCULyb8hBMGiRcZKj0X4P2Lyl9xSY+0FFfpEx7yTz2kJE7Jp5Tkj37UlNCfCoBX0wRD/MJIIF1xUWh+KIFvCqwEAnJTqig+/ARMsmNCAinsIf+KwI2M+AX5157+VRcAA+DRo+sgCbQydSyFRxVdysCIivgaGYlXaZKG1moFasT17eL1TYGk8lG6MuVPOiH/IP9M+ifdQG+wQ/nqn3TNRc7hXwC8HIRUTBZAQiMzAEuAhJ3G1cgLt+woC/EI109xmX6icAqnLkofu9JVvKj8CXMLvuX5y026UMg/yF/6JT2KQ/9IM5v++/pHvrlQALxcpFQEHkCDRhPgkKX/1MNNmPwALSkZYcRVGgI5/FA8H/AUPz0uaZAm/Jgqi9LGJK7iZ8tfeYb8g/ylC9I36Y/c6BW6JDOb/sGj+Nn0zyWW4V8AvAyCKaa3gASA4IcbhRBQoQQ86fAXENHg+OOG4MUtHqWDG5I/dimLb1e44il//DPl7yttrvmTfsi/4oYOcix1+aNrtH0+7S+dj9I/9Q3SU9rIkB/6lYkC4GWSTJH8aSCBCyYkQFBj0uBqYBVLPLj9DiPgIS7+8JEH/oz8UCLSg5QmfL6/4qTnTzoiP3/8+cEf8k88eIL8q69/6JD0CX2T/kv3MKV/yNnXP/ljRlEAvCipFMkPoBGo+B0EYFKDqUHFK9Pnp7j4QzS+D27yc4H2H8qjNPHDLpM0yBceP3/Cla94/Pxlh6+q/JUOfFDIf8mQv9o5l/aXjkk/pF8y0W/Z0VPs0idM6TB86RQAL10iRXTTsBANxk9PMjW4NgpUJPlj+nYalrhKiwZXXBRAygGf4ik/Pw5paKQnpYFPPIpLmrJjKo74lD9myD/IP5P+oTu+zkj/pFu4iasf/uiw9E/6LP0THyZx0T/sPgXA86VRJDsNp8ZR4+FWQ+MHye03Hn6K7wML/IonxcFPSkOYFAQ7+RFf6cmOKSKMOMpfPH58hRFH/iH/yrvrQf7Z9U+6Kf1Tf/D9ZUeW8PFD1/CX/qX7ocNKE54AeE5Uxf1Hg/mgpUbBVJgal4aHCFO4wIp3Y8VHPBHhvht//KQcygM/xUcpSB/CxB8+ePgpb/zg5ccoUvFdxPJ/8Cuu/PEL+afeNEGeyETyWxLlT53T6y9dwx/C7csGvSIMP+mTZCldIyxK/1x69iaMv4jAC/K1Qb3H9csp26s7XJoTX11m8htBdhpNRONChNEmamh4sMtNeHp8XzGwEw4gabivPFAWPz75KE+FER9/3OStvHD7/JSHcOWtfGWSP5cu+HVUHkrTT09hIf+K8v/ss8+cjDfYYINK8pf8aAvJXSZyp/3FQzvDp/b32w8/3CLiqL2xE8ZPaYlfbsKUr8xs+ZMPfIqPW+2vfJS/eFUXpS8+3Nj5Sa+IQ/5KU+Uts9cmOcBbd9114Sk69RrXN6c8B3fIjS+nxGqRiQZOCt82FI1CQ0HYITUovIRpPQ43YZAaU/FJEz5IDY9d/EpL+eOPMig+vErfz1+AKT+lDa/CpFQqv3hIEzsU8k89wNQpc5X/hAkTXDu2bt06KU9kHSV/2smXP25+8Cs/2kN8an/xiF9tpjj4S28Jq2n+lJGf8pdd5SIvfnpgqlwypX+YlFFlUrr4Ky3CpH+1DnjnjetLeaqka+o54KkBqCgNAQFQNJAaTYBFGI3tkxTDj0M6cktB8FNc7MQTKV+ZSlP5i1fhpEP5cEthFEdh5I/C+XnKLiVW/rgJU/p+WupY8CpceSzp+XM9F7LgajNkJnkjF1+Gkp1kLLnhhiR/mUqLtgU8aUvi8FMcuf32ryp/lSNT/vhD0jvlT3mUn2Ow/8hL/EqPskJy++URL+HUk7RFhJV98skntTrCO/f1y1SerOa1u1yeNbyuB0oJZfoNgR9uNZZ4pIBcU/Xpp586JSQMwqShURLxk4aUWXy4IT9955H2T/wqA6YPZrjhUV6Y5E+62KVcvh9xQv41l//MmTOdbqyyyiqu1ZArcqfjp8tf7a/2lvz9dlNb+iqgcPwI56f2V1rwKDyO/MlDpLQpL/6Y5K/yi7ddu3bmb3/7m9M56k88ftI7TKVF2rgh/EijzA6XaxXwznk9t7W5Ibv0cwWvj//UAAgcReFHQ/IDsETw6SmHn3hvvfVWM2PmDMemhleaBh20eoO/a1TrsCqQdLtI5f/Ekx6HYKsKLp7PL7uLV+5YyuZDfPKAXJ4oUnn+5WyV3PiLJz2Oq4KXptKQqXhKA7Po9bdtE0f+f5KOq3BKHripo61UuVRpjwSb8lR9HR9h5eUhHFJ8a0m2SRSP75cuV8IStaxp/omHt5+Xyig/6pcsK7XNUn/xEaXZ6qubrl27JuuYSCaxPCO7D8byk1lmRw61Cnhnv3YJZamSrvvbFVXy1FUGQExPHjU4ZXUKVq64NJKUGX494eAZMWKEmf3DbPP999/XXhVRSEuJ//EWw3WytA4cbw51JzUBSoUSRXpW4CiKI75i5JtSdq1Sau7G7aZNTZcuXZI3kGtkR3+hD0GM/OhLuOWv/pcEvFatWjnmYv8767WLc8ry+r/1z4mvrjIJ0AAwjewEfgI3PXHVcIRjB/BmzZ4VAK+uNm4e5VLnrRAl0rMCR1Ec8RUj35RyA7wWLVqYNe0nBk4++WQHaPQbSNNW3PzoM/Qd/DWIwI29zG55uxFebQHema9elFNjDt11QE58dY1JoCYwk5ty4qd1OvzVUNj1I5wpbQC8utay1StPJBREelYv/ZrEiq8Y+aaUG+CtZQFvVTvCO/XUU101fUDDwwEaoGZ/hNGffD/6VJm9wrxWAa/nqxfm1EbDdh2YE19dY0LoPHUEYGoE+cuttTvc/OCXedNNN5n/zvlvGOHVtcatRnkioSDSsxqJ1zBKfMXIN6X8AY++oZEdfYVpLD8GCBxlIRywgzQSdIBnt7wD4NVQUbJF9wXvBG4bQWBGPBpFYKfRHqZAEvvNN99sfvjvDwHwsgm6noRFQkGkZ/ErFF8x8k0pd8BbxX6LhTU8+gcAJ9BTf8FN/+JHuPoc0nSjPfulsloFvB5j++TUssM7DsqJry4yCfQQuOzamMDED6JxcPt2wkaOHBk2LZxU6v+/SCiI9Cx+XeMrRr4p5Q54TGlPOeUUJxyBm0ZwuB2o2X6GiT8/DSiwlwnwWrduXXwJ2xx7jL0gp3yHd7wyJ766xiSAowEYrUHY9RTCDY+eRDSOQFBA6Ka0/7VT2hlhlxZ51WeKhIJIz+LXMr5i5JtSjoBnv4u8SpMmplu3bg7I6EfQcsstV0FYABvgp77ngM7yOndtA97pOQLejfUQ8AAxiIaR8OWHGwBUwwgA8RcPlwNAYQ3PiaEk/kVCQaRn8asbXzHyTSlHwLObFk1WWsmN8Jiu0q/8UR52uZEe4fwAPPoVYWUTJ050U9raGuF1f+X8nFr2pt2uyomvLjIxYkPYCB0SuMmOv0Z2AjtM/ADFW265xcz5cY6ZPn26i5/rv06dOpnDDzvcfWv4yquuNIsXL841amU+qzhQdtWsHC0XH9c9yh8OufDXRZ4tt9zS7Nt5X7No0SJz7ZBrkw+tnmf0NCvZTjp6zGjz9ttvJw/2VqiDhw89e/Y0jS3/y6MT/BX4YnS0p7z7Jsp7zbWJ8nrFqGFO+aaUXauUGsdSVrNTWkZ49BmAjNEdfQU7JBDEDijiz8CBjQz4yr766qtaBrzelK1Kumm3q6vkqWsMCFiNQQOlu+WnchNO4wB04oWHTYvq7NIO6D/AfYCc9IcOG2rsRRHKKn+zDgAeD43mzZpb0Pgrb/DPv8L5xdh7773NkUcc6SJ16dol+XBjdL58g+XN0888bR5//PEqAe9m+Je3/E8/bUZZ/kIR5f3HkYnynmw3AZwuxpaZICrXBHMDPA4ea0qrkRs5AGroBgAHEUb/wQ8SGOJX64B32iu5Ad7N9RjwJHSADOFjigA4gRumiNEYbnjZtKgO4B31z6MMo7yFCxeaCy+60Pz0009KPn+zDgDeVlttZXqc3sOVve/lfc2UKVPyr0eBYgTA8wWb0mPfN7M9N8BjhNe0cWO3Swu4AWgCNUz6i+8n4MOEn75U9vXXX9fqCO+0V3plloMXcvNugz1X/bDy1ATgMEXY+dEIMgVsNAh+uJnKYvJjlPDj3B+rdSxlxRVXdKPGGk1nKXwdALxttt7GdO/e3Ymy3xX9zKRJk5y9LvwLgOe3QmEAj4PHHEvhXVr6D+CGSR/TlJVSyB87QEef0iiv7JtvvqlVwDt1TG6AN6JT/QM8BA4JuBKu1L13+NMYahAATzu5hDH6I4xXy6ozwmNqtJp9FYd1pR9++EHZm2bNmjlFmD1rtln822LTcq2WZp111zELFiww06ZNMz/++GOSN2kpBzzWl1qt28qtS/H2x/Rp083CRQuTbOkWFHK9NuuZNdZYw3z73beGmz/Sie7RbM01k2X6488/TJvWbcyazdZ05QHYeLpvvdXW5uCDD3bR77jzDgd4jFrzGblSHu5+ZHpE/SdPmZx1bdOV317L5Mr/bXT5KVChAI9y8kMX7EsCbrTuBJDlXxM7CtqobVvz66+/GrspGVm/XKa0KzVqZDbccEOXk13rN/+z+pFOa9p2W86uj82dN8/pTyI8cY8dbQrNmm31zFs/btiwoVnV3vzym32oJ/QhtxEegLeajXfiiSc6UAPkBHjkA9ABbPzwVxh9CTtU64B3ypjzXEGq+jey0zVVsdS5cMAKQSNwSEIH2PCjYQAjTAEdfFIOmQAemxb5Xh5w0EEHmQP2P8B1ku6nJ0ZGgOBNN95ENubFF1802223nVnZPjVFlIsF9gceeCBZbsIa2pFiV7vWs8XmW4jVmdRl1OOjzAsvvFCBnyfr8ccdb7Zsv6VpuELDZBzA9IUXXzAvv/xy0q8BZRp+o3OPGzfObLPtNm7dC4/XX3/dPPfv58yggdHnMD/97FNzrV10r4oozwnHn2DYXKDDiWijt95+y9x9990OVOTvyn/88ab9lu0r8NMO1NUvP3HiBrzXbL1XW3VVs/HGG6tI5ncr64kWwEbaTawokN98883NoYccYtZee+2krqFXdp3e3GJfT5xnQUmUDfC2sOkcfthhDmils8Rj0+zhRx9NrgUTNsKuLzewGwcv2fa87/77y5P/y+yw/Q7m1PLzcnfe9S/DFWeiQw851Oy/335uinlq99Os3BPHtRSebmq8qCkt79ICdgAcRFsJ7HDTnyDKJzsm7gB4TjSF+aeRG6ZATyAoPxpBAKhprMJwY2fTojpTWkZD+++3vxuBaSq4wgormBvLwSVbre++524zduzYJMull15mR3brOjdAzIiD6bLogQcfqAAC3bp2M9tvv72CK5kjbxlp3n33Xee/PGW6YXglHjxeHv2yef755w0bMA0aNKjAw+j0448+NjfdnADwCoGeAxlzWHW7bbfzfCtav/r6KzNoUApU2Qmk02aiESNHJMsPT9yAlylf/CdaALv66qud3ohvXfuJhj59+lSSkcInT55sBtr66SGaCfDWt6PZXr16ORBTXN9cZNt+8ODB5utvvnHeF9k8uXqe9dRL+/YtZ/3LdDm5i+mw887OPf4/480Nw1Pte6GNs+EGG5pJtkysxVpoKo8XbQjw9C4tgAeAAXT0K8BPhJ8PdACh+p4DPU1p27RpozhFNUt5hIcgETY/hA2wyU/Ap00LGkmAB59AEJMRHoCX77GUXADv9XGvm1GjRjnl6bBzB3PAAQc4haGDXN7vcldepsADBwx09g8+/MBtolDuVq1amV7n9XK7iii8+NkoOfqoox0/09h77rnHHY3ZZJNNDEAIcBH/gj4XuFFHOuB98MEH5tnnnnXTHaZlUtjqruHttddedkfyH648jJLvuvsuM3nSZLP+BuubIw4/Ivk9F46TcNEq5T/m6GMS5bfTWMB/5oyZZuNNNjandDslVf4LLrBTubmOrxCAx2j49jvuMHad3Y3ajj3mGDcdJ8MnnnzSPGl/EJ16sAXApvbIBrK677777Kj1bfdAOuzQQ90oHr7R9qjLvTYMigK8ZSxwDLGjZaay0EMPP2zefPNNZ99pp53sLvQRzs7U9uxzznH6uv/++5tD7YOVe/56nHGG+eWXXyzPX2bo9debJo2bOH7a8PQzeriHN8dIGM0DTM88+4x59LHHLE/ugLdy+aYF/UlTWo3wqDuyEMgRjh8/eFyfC4Dn2qQg/zRSUwMAaAheYKZwH/woCHz84AMIdSxlxowZeZWzKsAD1Fj8RyFEZ515lmFq5JS0x+nOe4sttjBn9jzT2QFHwEjEGg5TDaZL3333nfPmLBlT3/nz57snuD/92nbbbe1UJ3HbBZsxPP19wGO97or+V1Qok/KqLuCddZat02abu6k9oDxr1iwlaVo0b2Euuugis/QyS5tnn3nWHR+hrtSZ8l/W97IK00fKf9qpp7n4N950oxk/fryzxw14TF8HDhyYlCmZrGqnuJfbURQj6y/s1PaqqxJnU3nwXHbppa4cjz/xhHnqqaecnX/o28UXX2wYAbI+e0k5XxTgtbZvW116ySUu7osvvWTut8saPh31z3+avfbc03n1u+IKuyb7nXvo9S1Pc+iwYeaDDz90eVFOnwYOGuhGpm3t2uIFvc93QQOvHGRYG8wH8JraNy3Sp7T0LwDQjeBsf6HPUG/ptYDP9bNvv/3WbVrU1giv25hzfblktN/Sqep1moyRazFAQkfYEG6N9gA07Ix2fPATL6CH/cYbbyzIlJap4sOPPFxBOofYNaD99t3P+Z108knObLpy0+Q6GWfg7JfuDKOwiV9OdJcaVEjAOoYNHeY6JXW95dZbKgQvt+xybtEZT4ATAPUBj1HLk08lRi4VIlpHdQGP8jSyo5b3x7/vdrzT00133zDshmT5mXr7xAjlpBMTcnn22WfNY6MYocQ/pWVUN8ACXjoxNd/BLhUwkjq9R+KIzm4dO5rjjjvOsXLWzwd0PA+1o7zV7U3B0Cn2aiWmtVGAt5sd2R5nR5HQhRYk09eMebAN7N/fhd99771mzJgxDlyGDR3qRoX/tvrEqHDfzvu4A+9MWf+wOsy3OJ5y5wpHmYMOPND+Dko8UMtHfTkDXvk5PABPIzn6D3ZAjrahv2AX6FFY7Op3ZQHwXPsV5J9AjAaRnYywQwCawgRuNIxGd/Bh15sW6QroEsnyr6oRHlPNV8a+UiGFffZJKCueAjzsSgu7T1OnTTWvvfaaU378m9gn8JBrh/gsGe1Mj4fbtR0f8NLXDv3I1QE8H6yfevop84QdAWUjyn/dkOuysSTDAP0bht/g3HGP8Ni0uPPOO5N5ycKSw8F2Mwo697zz3I768XZzpeOuu4olq3mZHXmx/BAFeEoHvetS/jaDnxjgcpvd/OCa/7F2E+Jfd93lgk+xvDvusIP5zo7OL+/Xz1xwfm/TdqO2FuSecrOUgw862O2o97Wj6/Pt6K6dHeVVXNfLb0rL+qoATYCHyQ9/AaCATqM93AHw/BYtgJ1RHA2AKULwKJUADZDTD4CDMBn5QQAex0ryvTxAIMWxkahNiyhwyQR4VpPck7rDTjubzTbbzOiDMq6A9t+YV8a4tSNGUkOvHypvO/X6ImmXhc7A9BfAY5RUSMBj+seIDfr3v/9tHnn0ERUj0qT8jAhFfDEsnZiWufJbwGMdCoob8Fg7u/W229Kzdg+eA+y6GcRUfb49lnOMHZXtbkdnEFNE9Mqnle3aHkdEONZy2+23O5CMAryjjz7a7LH77k4Xu9mRoPRPaTGCGmk30NBfXn3TeuBOO+5o12a7unzPtRseg6++yiyz9DKm/4D+bme5r93wQr/POe9cc9WgK91IDLAc++rY8qRzAzxGmHrTgvQoj4AON8AmAEQGrBXTj+oW4I3OcUq7e/2c0iJ4GkFKKGADAGWn1QWIKJn8seMP4FXnHF7cgEc5pZpMkXa1o4pOu3VKKlYPO0WhzIOvHuwA8b3333MbLsTLRoUEPPJlrWv11VZ3GxJsTKQTIMdDiTU76JrB1yTK/9575uYRN6ezR7rjBrzJdhTWN20djIzPsNNY3jgB6AA8aJdddrHT7BOd/aKIqagLSPsXBXgdOnQwJ5+UmK4zEqQMPnF+8fLLLnNeAOe4N95w9sZ2I2Hoddc5IGTkx2iTM51nnNnT6fIw+wCEB4DruGtHF+fcXueZOXPmOHtKq8qdacZf5W52abVp4YObQC4d/DR7glf9sPZHeCUMeAIu2kuA54bV9gkJkOGHm6eQD4DY9XQljMX9efPnVVpTSdOLSs64AG/99dc3W2+9tVv05+wer6qJOATK7i7U58I+ZrY9ZMrrX+3bt3d8LFb7u8uMuJjilC1VZt555x03IqkO4LE2yMv4uRBXgnMkhfYYct0QB3yKt9GGG5nevXu7dnjwwQfNiy+9aHoAKu23SpTfrqNNmz5N7G5tD7kuVbaUK/+XE790YXEDHonS7u+9/34yb9bCLrA7w8tYcLafV7V1SUy9OXfX7/LLHR/nGNnZ9WlHOwLj6Ajt9rBdY4OiAG+tli1NfzslhVinvd5uQvh0lt2M4hwjdJHd3PDblU0KNkZEb7/zthlhX4mE/CMquNl862NfdUyRHqMpH9/mA542LdIBTxsTAj1M+haESds7v1pfwythwAO4ELKEDYjRUIAYfjQEPLjhIxwQJAx/7JgO8Owu6PTvp/t6UKU9LsD7+9//7o5vkKH9rKfbVOCEPKfwOVzM05sjFL16J96a2cGu53Tt0tWVb+7cuW7XkPXHFRslwI4OCvGtDjpGroDXrm07d0aMuJPsehFrRByq/fnnn/HKSID16d0TO86MPNjFnDR5kjtYy8FsjnNAnAljfWvHHXZ0ry/hR/nZRKH8jVZsZA46+CCzztqJju2DbiEAD4BizXHq1KnuTQ/W71RWDkq/MnYsRXS609/umjZv3ty5AT2AkrpyKwrLFIx2aLM+FyaAJgrwrELa84797c51Ip0333rLvFN+VnIHe0Ad4IS+t4DFSBI9FXH8ZT97+4qIg85vvpU40sLh9u7lO9uEJ3aA7xerNfMDPG48FuDRh+g7/Kgj/hD+CsNNGH0pjPCQRhEI5eCnkR6mbycM4IN0OFSgV93LA+ICPEZlvOnA1C8TPfTwQ+7NDYUfduhhrqPJnW5O+HSCGWp395BBroBH/kyX/QPIub5pwQFs5JGJ3rDTs9vvuD0ZfJh906DzPp2T7nQLwH/90OuTbVgIwEvPU26AiIeFTxxZucSOunitLIp4mF5v5W0/2uWCowAP+GJt9hJ7TEfAmp4WD4ArBgyo9PrhRhttZPqcnzhuwpm8M+10Vq+i8WbLcLuOCihBLCt8YuWXotwBj/vw2LQQsGHyE7hh4kavdE5P+TgQDCM8iSN+UyCHoAEvGpyGwA2hhOKRGx75A3zwc/AYRct3hEeHpeNyDu6ssxPrPSz0soiPMtx6263mLdt5fNKhW155O82+9iNqYl8/O9YexuXwMICjcvOBcI6WMAVKJ0YWTIFa2/NdTAGp94KfF7gDsGwgUE9oWcpkNwoylclPl/N0++63r9lg/Q3ca3lWf83gawb7LBnte+25l5uacwQLOVMevhXyyiuvuLc50iMiv2T5y/kZNXGAl9fdVH7idexoj4Yce5x7WCE35ANdf931bgT8qH0lizipMZELTvzzPK93B3Ybu4PFvAbIJgKjE4jlgg/tFV+P2cO6ft6JRIw7nNzZypxNFXabqR/lYOPlMdtG9sytWI2OsqBjp56WKK+Kwbu7+3Xu7NLRa4ecsySdZ557rsJUVglSxuE33GBWsGWmTfrZs5Q+6e0K8uvR84zkQz3BkxvgadOCER51E6BhUk/KQLsCePQ3TEj9zgGj3W2q1XN4XUefk6hzFf9v3T23ow5VJFP0YAEcpgSPXUTDaGQnk3D8pdScw+NEf77HUpRHLKZVMIj/jCYoH1NJldEFZvgHQDJKJI42BnxW19HKAcL3z2Z3oPunnfrbiwZINx9SeZgyJt4MyB5b/JnKnz12xVCBSgXfSM8EB50Y0EHO/gHuCvEjHAAeQIC8c2kjkogqhoDTfw83Irs0r6iU0lgqOHMDPICY66G4LQW5OACzeomdH4Qf/Ye6q79hQvCUBcBzsijIPz3llTiAhtAFaITTGPjzxEIxCRPw0cFwM8ILXy2TFOu3GQkFkZ7Fr2d8xcg3pdwAjxHeynZKyyYUgEbfYMYC0Y/40Y8Efg7grBs/fvAHwCugXgFcPHEANoStYTd2fhA8ctMouOED/HAzBaju5QGxVs2WBcqumtXL0XWPPEd41cup9mNFQkGkZ/HLGl8x8k0pu1YpNUZ4nMNjhAe4+VNZ+pkAjjCIfgUg0p/4OeALI7zCKRYCl/AZrWHXqI1cBXAqASBHwwgAATt4mNJW53oopRuLGQAvFjGq81ZILNKzAkdRHPEVI9+UcgM8Rnh800JreG5Zw/YXjfLoO4CeRnj0N370IfqUmwIHwCusLiFoGiKd8NPUFR4aRVNahSkuU9rZP8yuM2t46XWpqdtJJ0JGNU23LsavrAm2lJGexS99fMXIN6XcAE9reExpATIBmwMy2398P9nVt+hf7hcAr3CKhbARPCTBY8oNsPHDj5Gf78bOTikURnhODCXxLxIKIj2LX934ipFvSrkBnnZpATyIEZ4I0INkCuAEhvSnAHiSVoFMgRzJI3DAT2t2ahCt2cEDv9yyA4S8WsZ16nVll5ayxkmue1j5LAkUWctIz+JLI75i5JtS7oC3pv1kgaa09CFNYVnPg/CDADr6EH1Ofc3ZwwjPyacg/yRwrd8BevhB2OWWqTB/wwJe3rQIu7RIov5TJBREeha/rvEVI9+UcgM8bVpw8FjHTgA2+o+mt0hNYfQn/DHFF3ZpC6hXCFpTWkCPJw2N44/0yB5/bVjghpe48GJnl3bmrJlhhIdw6jlFQkGkZ/ErGl8x8k0pN8BjSssIT/fh0Y/4adqKHYCj3wgE8VO/cyO9MMIrnGIBWggZwhSI+TkCdCIBHSYNBj9TXDYtwpRWUqrfZiQURHoWv57xFSPflHIDPHdbSpOVzT6HH2++n7fQtFy1kWmzRiPTYNnU5xodqNm+lhzRlfc/jfTCCK+AeqUnDU8ZAZhA0DcVRlGwA3gCP9xckhl2aQvYUEVMOhIKIj2LWKjyrOIrRr4pVQ14vPHRyL6tM/L1H4zZYK/yEtt4FtC2bru6GfbPLcwma6/s+g3HVOhf9Lspc341m1wzzr6X96uLEwCvwHolYCMbwIsnkEANP9yAG6bO3TGqEx92AI8RXpKkT669E+mJX6Y76hChR3Zz3gYpgUT+frmUB3z8EebIlg/iv/zIS/kpXZlK00XK8g9+O3mvkC7s+POnvCokoeLDYsugvHwz9vr7BahB/uUSTLaAK799RS5ZD5sPyeOGIuvvQhL/kvHS5EAoKaioCe7ydG2byt+Pz0v/iqP8bQES5SlPQPFwuhKm5evi/UWLEp6may6SjWUDVYJE/olyufazPC5uef2VP1FHvjbbXqnd0pg12hmzvN2kWGw9LaglIixl+v9zU9Nzzw2czEiLUd6HU+abDn1fITqZmAB4CVEU9L9Aj8aVHSDjCYSJH0Q4bim57HzT8+OPP3Y8ig9IQjIVBz/frjxIGzv8MjXMJ038/PJhVzqyy4RX5VDZyZdwn3DDSz1kV16UI+SfWFCXTJCR5Ipd8ue2a+y8wwyp/WSKF1Mku3gUT3n58idt+NPTUf6kIbvS8fUoPR48+P262K4/j/7OKuSfpt3aTcy+WzR39SM9ykH7P/vRDPOZBSWz4rKmV6f1bczye+vK6zJ+8jzz0id2VAcKNm9jhnQ/1BywVUvTaPllzILFf5kH35lqLn7C3kq9iP6wlHnyvJ1Nx3arJ/WNPD6a9KP5YcFv5ulPZpkAeFZMhSRfgen4EMoASYng8e3w4ae4chMPRVOYTCk1aRAOH35aH8StuPAQJsJfeeOPnXTlJ1DCjV3+Kj9unqTEgTBJh5+Azs8fnpB/fvLnSifkzEWs6fJHnmoj7Nnkr3aV/GkXSGnij118hOMnd1T7Ez9b/uc+NMGM+diOzOxo7/VL/2ZWskAFP/Trb3+a7S4Z4+xn7ruh6b47gJei7+cvMrv1t9NRirlSA/N2713N2quukNQvOCnjG1/PMwcPsXfvUR/LN/+6zlYny++/K68DfPe8OTkAXkq8hbGhQAgbpcGUcmECCFIWwiH8xaPjKYTBqzjwkZbAjTCR/P300vNXOYivxV3Sxh/C5Ee4lFzlx49OoM6BqXIQx+8gfpmIrzjwkV/IPzf5861c5MWNx2oHybKQ8vfbTPnKzDX/N7+aY7rd9h8U21x6+KbmyO3WSurZ4+OnmYse+typyauX7GqaN23owkibH6O3Pg/Y+/usWl577BbmHzus4+RAmM7dST8Pvfl9M/Yju+xjryH7aNAedjNjxWQ+8FPuB96dHgBPnbKQpg8c5IPy8pMdEx4ahR8kwMFfbo3YcGOHF8ASMAr4iEsYDY2dcAGrwEZpUg7xq0y4iYtb6eCmLEpTeaF42BVOXOVBntjJX6NAhYX8E22ci/y5hw75tmrVKif53/PWVDNolAUS2/nd6MheoWXKGFWhW4mHGvJPUoNlzN0ntTdbt0p8OFttHEf72+VJ0+6K0cbM/920bLmSGX32Tk6v0IODb37PfPbNXLPdxqube0/eyhUHf/JHd3o9PME88saURNktgK3qdmNtPeznAaxiWn9bP/uxIKbM//3e3npNn7Lx7zlje3Pgls2dzpEe+on+hRFessULZ/HBTgok8MGdTjQOIKMwTIGhQEXpYAp4aFh+ikscuf38iIMbk5/iySQe/hD5QeJVPPkRR/lgh19x8cdNeRQvPR3cyldmyL+y/DXC40p9yVcm8pXsJP8rn5tobn/p6wQgWDBwwOAWwVxjptw0s8O/MjP0hPZ2ja1ZUn9o46raH56o/PH32//msZPN0Ge/xNs8ce5OZtOWK5vvZi8wew563frYyzG6bGU6b948qS+qz8F21PafL+yHfoTRDrSJUu4B4KGrzm39yvX1ud67mA4brpIsv+px71tTTFjDoxUKSCgEpM4vEz8altFPFAnIUBwIBcdOegIKxYOXdDXSI11IvNgJJ77AT37KR0om01dYpUe+6mik5/OSLmnhRxjk8xMe8q+e/HUtOzc1i7LJ/19vTDX9H+MqdwsGHqjZBrFKR9vgaQk9YfRn6cGeO5ht26zi7NIVTAEaAdJl7NnyJwxS+8+wZ+Z26fcqHuaYTm1MvwPbmiEvfG2GP/+NMcuVmS8G7OHO0ilN6eiZD0wwo+xo1RbU3HbqtqaxXf/Djo7BU2brspQd7ZHPUtZtH7dmjSYrmI3Xaux4VAbp5D1vTjIB8JBKgclXGhoHt5RJygEYSEHEQ0PBq/jyp7j4yY2p9ARU+PHzgYh48odf8fz0sEN+WLqdcuEnPkwpqeqmMPwpE3Hkp/Rkqp5KU3ySjfjkv6Tlz/czqDObFpDq78srXf4cMSG8gvzxs/ElT5lcv/9nOfC5DDwe3PCRp6iq/MkT8vM/+tbx5s1P7eZFg6XNhH6dzKb9XzPmf4vNCRYA+x+6SQUdl27ePm6q6ftIArgfPnMns9vGa7hyEO6XB7fqrzxVVvnDH0Z4kkqBTRpBhOB9AAAgIPyx88NOI2rERnz94GUkhRseAaXSwU/5YSpd4kHp+YtX8RQHRdHIMREz8V/xcUmZsPvlxi2Kyl+88IT8E7qRTf4AHgTg+W2udVHCJFPk7ZP45a/2kDuT/NPjKU35Ey/X/Cnb8xNmmW63jLfJlJnO2zY3z703wyX5/AUd7BS3iasXHqRLHujWB5Pmmr2vsru0jOrWXNFMuWQ3s9IKiUPF8JI/RJxZ8381D70/wyxY9Kc5Z6/1LV/qMgHVMQCeE1dx/iF0X8nUsIAajSsQpLHxwxTgoDACNkqLm3DSwB+70lY6xIXEhx0efsSHlIeAizDsype0cJOP4sCjspOGykJacsNDXEj546ef0gr5J9q4KvnzDVpkxrdl0+WvNsIfgg+7L2PaS+GKj5/f1vDTHmoTTNIGVLK1f675/253L1pdPNqO6ljCSYB883Wamvd775TUEfJUmSSTCx//wtzx8reubis2X8mMOGxjs+N6q5nVGy9v5v3yu3n9q9nmgXdmmGfLAZS0n+7VwXRsu6qr808L/zCfT//JxX/q45kmTGmdKAr/D8WgQaVYKJGUjjAUERPC31da8RImf6UDOKKUgJTiwQeJB5Ow9PzhQbGUP3zqEOLF7RP+Kg928iV/0lAe4pe7qvyVr0w/Xsi/zH10G5kCeAIC3JK//JAfflB6O/oyVRvCh11yl5nuT1ylp7zEQ/vLT/nDK36lTxoDn51ohtsNFbeOaN1XHrO5OWmX1hX0VOVXmr/+9pfZb/jb5uOJP5JlIm7CVp6O7TNldqTH5oylPbZqYR46bTv7IkZCDmtePMYscIBnQdZO3QPgSXhFMGl0kRQQhZGCoDAiKQ8mP4GaeDClVMRBUQRcuEkfpREQCpQIg/xw0pFiYlc4fvDxU3zs6eGKT/5SWKUvIFR8F9n+U7jqH/JPAU+6fHnLhrbkHJ4I+UlmmLSB5I8d/RBoSH8IhwiX/DWbkJ/SFx+meBVffvnmP+d/i8xG/V+xXze3F9s2Xd5MH7CXsY9bVw/SIh/KAWHq96cdHT703vemuz2mYn6ycct5rMXxmibLmzN3bWX+uU0Ls/m6iY+qSz7H2rXDR8dNTsYJgJcQWVH+07np+DQGJBM7DU44SiqQ8JVWygyv/InvK6HCMKWMUnZ4SZ9zc/hB+EnB4CctdRLC+OHGVJji4Q/hrzQUhqn8ias6h/yrJ/+P7LdoabN27dq5tlC7IOd0+eOncMIgvw1pC7W/Cyz/p/ZXODxqO+z8lI7SJ2q++Vt4M1P/+7Np0XQFs8JyCR0iHdImLT8P/KU75I8d0Px61s9m3qLfTYuVG5oWTZZz01t40UPKBpGO6oBdFABPkiiSSSOgXDQMPyma7IRDNK4UVqbiqSHhIx4NjZ8aGTt+vgKID1N8spM+dqWv/FUWmcSDTz+lL1PpKn8/PeKk8+FHnJB/dvnrI+c+4CE7X760mS9LZM0PghfCTduonaLk7xjtP/EoDv7Kj3hx5E+afpmxiwS85K/yEy678hc/fJSP+hEmt/hxQwHwJLEimgIEKR9ZY4doGAEcdvGq4dSY6f4usv2ncNzw4PYVBX/xEI5iQdgh5Sm7eH1TiiUlchG9f/IP+ccjfzYtkH/btm1dOwl4PJG7doMHkvxx0wZR7U8a6Jnf/n48pa12V7qkF3f+5Ovrn/KWqXJJ75Q//grzeeXn65/C/w9MYjBl5jWdXAAAAABJRU5ErkJggg=="
    }
   },
   "cell_type": "markdown",
   "id": "311fe717",
   "metadata": {
    "pycharm": {
     "name": "#%% md\n"
    }
   },
   "source": [
    "__Challenge: Arbeiten mit Pandas query()__\n",
    "\n",
    "Pandas query() ist eine experimentelle neue Funktion mit einer (meiner Meinung nach) einfachen und intuitiven Syntax. In der Pandas-Dokumentation [1] heißt es außerdem, dass es bei großen Datensätzen (über 200.000 Zeilen, wie wir sie haben) einen kleinen Geschwindigkeitszuwachs gegenüber der Python-Indizierung bringen kann. \n",
    "\n",
    "Wenn Sie noch nie mit query() gearbeitet haben, finden Sie hier einige Beispiele für die Verwendung:\n",
    "\n",
    "- String ist gleich: df.query('Land == \"Australien\"')\n",
    "- Mathematische Operationen: df.query('Einkommen_Main+Einkommen_Sonstige > 60000'), df.query('Punktzahl_Dezimal * 100 != Punktzahl_Prozent') # z.B. um zu sehen, welche prozentualen Punktzahlen nicht korrekt berechnet wurden\n",
    "\n",
    "- Vergleich mit einem Variablennamen: favourite_song='Gimme Shelter', df.query('Song == @favourite_song')\n",
    "- Prüfen, ob Element in einer Liste: df.query(\"Zutat in ['Eierr','Mehl']\")\n",
    "\n",
    "Vervollständigen Sie mit diesen Hinweisen die folgenden Aufgaben (Um Ihr Notebook nicht zu verlangsamen, verwenden Sie .head(), um nur die ersten 5 Zeilen eines jeden Ergebnisses anzuzeigen.):\n",
    "\n",
    "- Transaktionen anzeigen, die *nicht* in dem 'United Kingdom' stattgefunden haben.\n",
    "- Transaktionen anzeigen, bei denen der Wert (Menge * Stückpreis) £2000 übersteigt (alle Transaktionen sind in £).\n",
    "- Eine Variable definieren, die das Produkt '23168' bezeichnet (Achtung: Der StockCode ist eine Zeichenkette und keine Zahl, daher muss er in Anführungszeichen angegeben werden). Transaktionen für diesen Artikel anzeigen.\n",
    "- Transaktionen für der folgenden Artikel anzeigen: ['CHILDS GARDEN TROWEL PINK',\n",
    " 'CREAM SWEETHEART MINI CHEST',\n",
    " 'LUNCH BAG CARS BLUE',\n",
    " 'WALL ART DOLLY GIRL ',\n",
    " 'CHRISTMAS TREE HANGING GOLD']\n",
    " \n",
    " \n",
    "Schreiben Sie Ihren Code in eine neue Zelle, unten. Sie können eine neue Zelle hinzufügen, indem Sie \"Esc\" gedrückt halten und die \"B\"-Taste drücken, oder klicken Sie einfach auf die Plus-Schaltfläche im oberen Menü, wie so:\n",
    "\n",
    "![image.png](attachment:image.png)\n",
    "\n",
    "Die Lösung finden Sie unten.\n",
    "\n",
    "[1] https://pandas.pydata.org/pandas-docs/stable/user_guide/indexing.html Siehe den Abschnitt 'Performance of query()'."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "135af126",
   "metadata": {
    "code_folding": [],
    "pycharm": {
     "name": "#%%\n"
    }
   },
   "outputs": [],
   "source": [
    "# Challenge: Working with Pandas query() - solutions\n",
    "print(\"Transactions which did not occur in the United Kingdom:\")\n",
    "display(df_data.query('Country != \"United Kingdom\"').head())\n",
    "\n",
    "print(\"\\nTransactions where the total value exceeded £2000:\")\n",
    "display(df_data.query('Quantity*UnitPrice > 2000').head())\n",
    "\n",
    "print(\"\\nTransactions for product 23168:\")\n",
    "stock_code = '23168'\n",
    "display(df_data.query('StockCode == @stock_code').head())\n",
    "\n",
    "print(\"\\nTransactions for any of a given list of products:\")\n",
    "product_list = ['CHILDS GARDEN TROWEL PINK', 'CREAM SWEETHEART MINI CHEST', 'LUNCH BAG CARS BLUE',\n",
    "                'WALL ART DOLLY GIRL ', 'CHRISTMAS TREE HANGING GOLD']\n",
    "display(df_data.query(f'Description in {product_list}').head())\n",
    "\n",
    "# Notice the use of display() here. We didn’t need it when viewing dataframes before, because it was always the\n",
    "# last line of the cell, which Jupyter displays automatically. We don't need it on the last line of this cell,\n",
    "# but I left it here for visual consistency."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "9700dbe7",
   "metadata": {
    "code_folding": [],
    "pycharm": {
     "name": "#%%\n"
    }
   },
   "outputs": [],
   "source": [
    "# Explore the UnitPrice values above 95th quantile\n",
    "(df_data\n",
    " .drop_duplicates(['StockCode', 'UnitPrice'])\n",
    " .query('UnitPrice > UnitPrice.quantile(.95)')\n",
    " .sort_values(by='UnitPrice', ascending=False)\n",
    " .head(100)\n",
    ")"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "b186b730",
   "metadata": {
    "code_folding": [],
    "pycharm": {
     "name": "#%%\n"
    }
   },
   "outputs": [],
   "source": [
    "# Explore UnitPrice per Description\n",
    "(df_data\n",
    " .groupby('Description')\n",
    " .mean()\n",
    " .sort_values(by='UnitPrice', ascending=False)\n",
    " .head(25)\n",
    ")\n",
    "\n",
    "# A note on groupby(), for the unsure:\n",
    "# groupby() splits the data frame into 'chunks', one for each unique value in the given column (here 'Description').\n",
    "# Then for each chunk, it applies the function after the groupby; here it gets the mean() per column, per chunk.\n",
    "# The result is a dataframe with an index containing all the descriptions, and then three numeric columns,\n",
    "# containing the mean Quantity, UnitPrice and CustomerID per description (clearly mean CustomerID makes no sense)\n",
    "# We then sort this data frame by ascending UnitPrice, and display the first 25 rows"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "ac34a864",
   "metadata": {
    "code_folding": [],
    "pycharm": {
     "name": "#%%\n"
    }
   },
   "outputs": [],
   "source": [
    "# Let's find the stock codes which seem to be causing the bulk of the strange values\n",
    "[sc for sc in df_data.StockCode.unique() if not sc.isnumeric()]"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "549fe68f",
   "metadata": {
    "code_folding": [],
    "pycharm": {
     "name": "#%%\n"
    }
   },
   "outputs": [],
   "source": [
    "# Let's refine the unusual stock codes\n",
    "[sc for sc in df_data.StockCode.unique() if not sc[:-1].isnumeric()]"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "843fe344",
   "metadata": {
    "pycharm": {
     "name": "#%% md\n"
    }
   },
   "source": [
    "__Challenge: Indizierung in Python (für die Ungeübte)__\n",
    "\n",
    "Wenn Sie bereits mit der Indizierung in Python vertraut sind, können Sie diese Zelle überspringen. "
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "3702c88e",
   "metadata": {
    "code_folding": [],
    "pycharm": {
     "name": "#%%\n"
    }
   },
   "outputs": [],
   "source": [
    "# Challenge: Python indexing for the unfamiliar:\n",
    "\n",
    "# The indexing used in sc[:-1], above, works as follows:\n",
    "# A number inside the brackets would indicate only the character at that position.\n",
    "# eg. 'Hello World'[0] selects 'H'\n",
    "# Notice that indexes start with 0, not 1. So 'Hello World' is 11 characters, with indexes [0,1,2,3,4,5,6,7,8,9,10]\n",
    "# A negative number counts backwards, e.g. 'Hello World'[-1] selects 'd' (it's the same as 'Hello World'[10]\n",
    "# but saves you from having to count the length of the string and subtract 1)\n",
    "# You can specify a start and end range using : and giving a start and end value.\n",
    "# This will select from (including) the start value up to (but excluding) the end value\n",
    "# e.g. 'Hello World'[1:-1] and 'Hello World'[1:10] will both select 'ello Worl'\n",
    "# You don't have to include a start AND end value; you can leave out either, as long as you include the :\n",
    "# e.g. for a stock code sc='85123A', we get sc[:-1] selects '85123'\n",
    "\n",
    "# Feel free to use this cell to write some examples and play around. Try to answer these questions:\n",
    "# If you have a 5 letter string, what's the highest index you can use?\n",
    "# Why is it smaller than the length of the string?\n",
    "# What will you get from 'abcdefghijklmnop'[4:-4]?\n",
    "# What is your full name, ignoring the first two and last three letters?\n",
    "# What will be the results of '123abc'.isnumeric()? What about '123abc'[:3].isnumeric()\n",
    "# What about '123abc'.isalpha()? Or '123abc'[3:].isalpha()? (isalpha() checks whether a string is all alphabetical)\n",
    "# What happens if you select an end index which is equal to or greater than the length of your string? Try it!\n",
    "\n",
    "# If you run into an IndexError while experimenting, perfect! That means you're doing it right. \n",
    "# But errors are ugly, so when you're finished, you can clear your output by clicking this cell\n",
    "# and selecting from the top menu Cell > Current Outputs > Clear)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "c929f153",
   "metadata": {
    "code_folding": [],
    "pycharm": {
     "name": "#%%\n"
    }
   },
   "outputs": [],
   "source": [
    "# Challenge: Python indexing for the unfamiliar - solution:\n",
    "# If you have a 5 letter string, what's the highest index you can use? --> 4\n",
    "# Why is it smaller than the length of the string? --> Because indexes start at 0, i.e. 'Hello' = [0,1,2,3,4]\n",
    "# What will you get from 'abcdefghijklmnop'[4:-4]? --> 'efghijkl'\n",
    "# What is your name, ignoring the first two and last three letters? --> 'Katherine'[2:-3] --> 'ther'\n",
    "# What will be the results of '123abc'.isnumeric()? What about '123abc'[:3].isnumeric() --> False, True\n",
    "# What about '123abc'.isalpha()? Or '123abc'[3:].isalpha()? --> False, True\n",
    "# What happens if you select an end index which is equal to or greater than the length of your string? Try it!\n",
    "# --> '123abc'[10] --> IndexError: string index out of range"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "f89961ce",
   "metadata": {
    "code_folding": [],
    "pycharm": {
     "name": "#%%\n"
    }
   },
   "outputs": [],
   "source": [
    "# Display the data for a specific StockCode of interest\n",
    "df_data.query(\"StockCode == '15056BL'\")\n",
    "# Alternativ: \n",
    "# df_data[df_data.StockCode == '15056BL']"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "73bece57",
   "metadata": {
    "code_folding": [],
    "pycharm": {
     "name": "#%%\n"
    }
   },
   "outputs": [],
   "source": [
    "# Describe the UnitPrice distribution for the unusual stock codes\n",
    "unusual_codes = ['POST', 'D', 'C2', 'M', 'BANK CHARGES', 'PADS', 'DOT']\n",
    "for sc in unusual_codes:\n",
    "    # The use of \\n in the string prints a newline, which is just done for my own visual preferencce\n",
    "    print(f'\\nData described, where StockCode == {sc}')\n",
    "    # We need 'display' since we are looping, so we can't just rely on Jupyter outputting the last line of the cell\n",
    "    display(df_data.query(f\"StockCode == '{sc}'\")[['UnitPrice']]\n",
    "           .describe()\n",
    "           .round(3)\n",
    "           .T)\n",
    "            # 'T' for 'Transpose' flips the dataframe from being tall and skinny to short and wide.\n",
    "            # You can re-run the cell with a # in front of the .T, if you are curious how it would look."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "4bafb8f4",
   "metadata": {
    "code_folding": [],
    "pycharm": {
     "name": "#%%\n"
    }
   },
   "outputs": [],
   "source": [
    "# Do only a small group of customers use the unusual stock codes? Part 1: Customers per Stock Code\n",
    "sc_unusual = df_data.query('StockCode in @unusual_codes')\n",
    "sc_normal = df_data.query('StockCode not in @unusual_codes')\n",
    "\n",
    "print('Unique Customers per Stock Code: unusual Stock Codes')\n",
    "display(sc_unusual\n",
    "        .groupby('StockCode')\n",
    "        .agg({'CustomerID': 'nunique'})\n",
    "       )\n",
    "\n",
    "print('Overview Unique Customers per Stock Code: Normal Stock Codes')\n",
    "display(sc_normal\n",
    "        .groupby('StockCode')\n",
    "        .agg({'CustomerID': 'nunique'})\n",
    "        .describe()\n",
    "        .round(3)\n",
    "       )\n",
    "\n",
    "# A note on agg(), for the unfamiliar:\n",
    "# We've seen that Pandas' groupby() splits the dataframe into one chunk per unique value in the given column.\n",
    "# We then have to perform some kind of aggregation on eacch of those chunks. So far we've used mean();\n",
    "# Here, we use agg() and specify a dictionary of ColumnName: function.\n",
    "# We are asking: for  each chunk of the data representing one StockCode, how many unique CustomerIDs are there?\n",
    "# Note that because we only specify one column:function pair here, we'll only get one column back as a result.\n",
    "# However, one can use multiple pairs, to specify different aggregations for different columns. "
   ]
  },
  {
   "cell_type": "markdown",
   "id": "68d23dac",
   "metadata": {
    "pycharm": {
     "name": "#%% md\n"
    }
   },
   "source": [
    "__Challenge: Anzeige der Anzahl der eindeutigen StockCodes pro Kunde__\n",
    "\n",
    "In der letzten Zelle teilten wir die Daten in sc_unusual, d. h. alle Transaktionen mit ungewöhnlichen Bestandscodes, und sc_normal, d. h. alle anderen. Dann erhielten wir für jeden StockCode die Anzahl der eindeutigen Kunden-IDs. Bei den normalen Lagerbestandsdaten hätte dies zu einer großen Anzahl von Zeilen geführt, so dass wir eine describe()-Funktion hinzugefügt haben, um die Ergebnisse zusammenzufassen.\n",
    "\n",
    "Diesmal ist die Logik genau umgekehrt. Sowohl für sc_unusual als auch für sc_normal wollen wir die Anzahl der eindeutigen Bestandscodes pro CustomerID anzeigen. Und da es sehr viele CustomerIDs gibt, wollen wir wieder die Funktion summary verwenden. Sind Sie der Herausforderung gewachsen? Fügen Sie eine neue Zelle hinzu (Esc+B) und probieren Sie es aus, bevor Sie mit dem Video fortfahren. Viel Erfolg!"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "1d0dbec2",
   "metadata": {
    "code_folding": [],
    "pycharm": {
     "name": "#%%\n"
    }
   },
   "outputs": [],
   "source": [
    "# Solution: Do only a small group of customers use the unusual stock codes? Part 2: Stock Codes per Customer\n",
    "print('Unique Stock Codes per Customer: unusual Stock Codes')\n",
    "display(sc_unusual\n",
    "        .groupby('CustomerID')\n",
    "        .agg({'StockCode': 'nunique'})\n",
    "        .describe()\n",
    "        .round(3)\n",
    "       )\n",
    "\n",
    "print('Overview Unique Stock Codes per Customer: Normal Stock Codes')\n",
    "display(sc_normal\n",
    "        .groupby('CustomerID')\n",
    "        .agg({'StockCode': 'nunique'})\n",
    "        .describe()\n",
    "        .round(3)\n",
    "       )\n"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "ea54bf8b",
   "metadata": {
    "pycharm": {
     "name": "#%% md\n"
    }
   },
   "source": [
    "Die ungewöhnlichen Lagercodes haben im Allgemeinen nur wenige Kunden (mit Ausnahme von \"M\" und \"POST\"), während der Durchschnitt für die anderen Lagercodes bei etwa 72 liegt. Außerdem haben die Kunden in den Daten zu den anormalen Codes jeweils nur etwa einen Lagercode, während die Kunden in den übrigen Daten jeweils etwa 61 Codes haben.  Mit anderen Worten, nur eine sehr kleine Anzahl von Kunden verursacht den Großteil der seltsamen Transaktionen. In Anbetracht der Tatsache, dass diese Transaktionen offensichtlich eher mit Dienstleistungen als mit Produkten zusammenhängen, sollte das Unternehmen diese Kunden getrennt behandeln. Daher werden wir alle Zeilen mit diesen seltsamen Lagercodes streichen."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "f7aca7d3",
   "metadata": {
    "code_folding": [],
    "pycharm": {
     "name": "#%%\n"
    }
   },
   "outputs": [],
   "source": [
    "# Remove the rows with unusual stock codes\n",
    "df_data = df_data.query('StockCode not in @unusual_codes')\n",
    "print(f\"After dropping rows with unusual stock codes, data shape: {df_data.shape}. Summary:\")\n",
    "display(df_data.describe().round(3))\n",
    "print(f\"Number of customers: {df_data.CustomerID.nunique()}, number of transactions: {df_data.InvoiceNo.nunique()}\")"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "9a40df6c",
   "metadata": {
    "pycharm": {
     "name": "#%% md\n"
    }
   },
   "source": [
    "__Challenge: Erkunden Sie die Mengenwerte über dem 95. Perzentil__\n",
    "\n",
    "Bisher haben wir die Transaktionen mit den obersten 5 % der UnitPrice-Werte angezeigt. Können Sie dies wiederholen, aber für die Menge? Wenn Sie einen Hinweis benötigen, klappen Sie die nächste Zelle auf."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "9b7f3c6a",
   "metadata": {
    "code_folding": [
     0
    ],
    "pycharm": {
     "name": "#%%\n"
    }
   },
   "outputs": [],
   "source": [
    "# Challenge Hint\n",
    "# The solution could include some of these steps, but not necessarily in this order: \n",
    "# - Sorting values \n",
    "# - Querying or otherwise slicing the dataframe df_data\n",
    "# - Taking the dataframe head()\n",
    "# - Using the quantile() function\n",
    "# - Wrapping these steps in brackets () to make a pipeline"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "71dc6f75",
   "metadata": {
    "code_folding": [],
    "pycharm": {
     "name": "#%%\n"
    }
   },
   "outputs": [],
   "source": [
    "# Solution: Explore the Quantity values above 95th percentile\n",
    "(df_data\n",
    " .drop_duplicates(['StockCode', 'UnitPrice'])\n",
    " .query('Quantity > Quantity.quantile(.95)')\n",
    " .sort_values(by='Quantity', ascending=False)\n",
    " .head(100)\n",
    ")"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "25f0a936",
   "metadata": {
    "pycharm": {
     "name": "#%% md\n"
    }
   },
   "source": [
    "__Challenge: Erforschen Menge pro Beschreibung__\n",
    "    \n",
    "Zuvor haben wir den durchschnittlichen Stückpreis pro Beschreibung angezeigt. Können Sie dies wiederholen, aber für die Menge? Wenn Sie einen Hinweis benötigen, klappen Sie die nächste Zelle auf."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "acfe0d51",
   "metadata": {
    "code_folding": [
     0
    ],
    "pycharm": {
     "name": "#%%\n"
    }
   },
   "outputs": [],
   "source": [
    "# Challenge Hint\n",
    "# The solution could include some of these steps, but not necessarily in this order: \n",
    "# Grouping the data\n",
    "# Sorting values\n",
    "# Calculating a mean()\n",
    "# Viewing the dataframe head()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "3d83c9ec",
   "metadata": {
    "code_folding": [],
    "pycharm": {
     "name": "#%%\n"
    }
   },
   "outputs": [],
   "source": [
    "# Solution: Explore Quantity per Description\n",
    "(df_data\n",
    " .groupby('Description')\n",
    " .mean()\n",
    " .sort_values(by='Quantity', ascending=False)\n",
    " .head(20)\n",
    ")"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "54585845",
   "metadata": {
    "pycharm": {
     "name": "#%% md\n"
    }
   },
   "source": [
    "Es gibt nichts Ungewöhnliches an den großen Werten für Menge, also behalten wir diese Zeilen bei; sie enthalten legitime Informationen, die wir modellieren müssen.\n",
    "\n",
    "Bei der ursprünglichen Beschreibung der numerischen Daten hatte die Menge einen Höchstwert von 80995 und einen Mindestwert von -80995. Woran kann das Ihrer Meinung nach liegen?"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "2a793b52",
   "metadata": {
    "pycharm": {
     "name": "#%%\n"
    }
   },
   "outputs": [],
   "source": [
    "# Check for some specific, suspicious values\n",
    "df_data.query('Quantity in [80995.000000, -80995.000000]')"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "29ebb9f5",
   "metadata": {
    "pycharm": {
     "name": "#%% md\n"
    }
   },
   "source": [
    "Eine Rechnungsnummer mit \"C\" bedeutet eine Rücksendung:"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "03a0df8a",
   "metadata": {
    "pycharm": {
     "name": "#%%\n"
    }
   },
   "outputs": [],
   "source": [
    "returns = df_data.query('InvoiceNo.str.startswith(\"C\")')\n",
    "# Alternativ: returns = df_data[df_data['InvoiceNo'].str.startswith('C')]\n",
    "returns.sample(5)"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "a84768b4",
   "metadata": {
    "pycharm": {
     "name": "#%% md\n"
    }
   },
   "source": [
    "... was wir überprüfen können, indem wir prüfen, ob ein Wert in der Spalte \"Menge\" größer als 0 ist, wenn die Rechnungsnummer mit \"C\" beginnt: "
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "42baeece",
   "metadata": {
    "pycharm": {
     "name": "#%%\n"
    }
   },
   "outputs": [],
   "source": [
    "print((returns.Quantity.values > 0).any())"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "2bd0838b",
   "metadata": {
    "code_folding": [],
    "pycharm": {
     "name": "#%%\n"
    }
   },
   "outputs": [],
   "source": [
    "# Define is_return_without_purchase, for identifying such transactions\n",
    "def is_return_without_purchase(df: pd.DataFrame, df_row: pd.Series) -> bool:\n",
    "    \"\"\"\n",
    "    Determine whether a transaction is a return without a corresponding purchase    \n",
    "    Args:\n",
    "        df: a dataframe of transaction data, including at least the columns 'CustomerID', Quantity' and 'StockCode'\n",
    "        df_row: a row of a dataframe of transaction data, also including these columns\n",
    "    Returns:\n",
    "        True if a transaction is a return without a corresponding purchase, else False\n",
    "    \"\"\"\n",
    "    if df_row['Quantity'] > 0:\n",
    "        return False # Can't be a return without purchase as it's not a return\n",
    "    else:\n",
    "        customer_id = df_row['CustomerID']\n",
    "        quantity = df_row['Quantity']\n",
    "        stock_code = df_row['StockCode']\n",
    "\n",
    "        matching_purchases = df[\n",
    "            (df['CustomerID'] == customer_id) & (df['StockCode'] == stock_code) & (df['Quantity'] == abs(quantity))\n",
    "        ]\n",
    "\n",
    "        if len(matching_purchases) > 0:\n",
    "            return False # Can't be a return without purchase as corresponding purchases were found\n",
    "        else:\n",
    "            return True"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "6a3b5ab9",
   "metadata": {
    "pycharm": {
     "name": "#%%\n"
    }
   },
   "outputs": [],
   "source": [
    "# Identify whether returns have purchases or not\n",
    "returns = df_data.query('Quantity <= 0')\n",
    "returns = returns.copy(deep=True) # Create a new copy, to avoid Pandas SettingWithCopyWarning\n",
    "purchases = df_data.query('Quantity > 0')\n",
    "\n",
    "returns['return_without_purchase'] = returns.apply(\n",
    "    lambda x: is_return_without_purchase(df=purchases, df_row=x), axis=1)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "913b63a3",
   "metadata": {
    "pycharm": {
     "name": "#%%\n"
    }
   },
   "outputs": [],
   "source": [
    "# Combine return details with original dataframe\n",
    "df_data = df_data.merge(\n",
    "    returns[['return_without_purchase', 'InvoiceNo', 'InvoiceDate', 'StockCode', 'Quantity', 'UnitPrice']],\n",
    "    how='left',\n",
    "    on=['InvoiceNo', 'InvoiceDate', 'StockCode', 'Quantity', 'UnitPrice']\n",
    ")\n",
    "df_data.fillna(False, inplace=True)\n",
    "print(\"Value counts for 'return_without_purchase:'\", \\\n",
    "      df_data.return_without_purchase.value_counts())"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "58f9745b",
   "metadata": {
    "pycharm": {
     "name": "#%%\n"
    }
   },
   "outputs": [],
   "source": [
    "# Remove the returns without purchases\n",
    "df_data = df_data[~df_data.return_without_purchase]\n",
    "df_data.drop(columns=['return_without_purchase'], inplace=True)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "766228b2",
   "metadata": {
    "pycharm": {
     "name": "#%%\n"
    }
   },
   "outputs": [],
   "source": [
    "# Save the preprocessed data as a csv; we'll need it in the next notebook\n",
    "df_data.to_csv(\"OnlineRetail_Preprocessed.csv\", index=False)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "19793cb2",
   "metadata": {
    "code_folding": [],
    "pycharm": {
     "name": "#%%\n"
    }
   },
   "outputs": [],
   "source": [
    "# Get the number of invoices per day\n",
    "df_data['InvoiceDate_Date'] = df_data['InvoiceDate'].dt.normalize()\n",
    "counts = (df_data\n",
    "          .groupby('InvoiceDate_Date')\n",
    "          .count()\n",
    "          .reset_index()\n",
    "         )\n",
    "# A note on reset_index(), and it's usage above, for the unsure:\n",
    "# Our groupby().count() produces a DataFrame with a so-called 'multi-level Index', which reset_index() flattens\n",
    "# (you can picture the effect by adding a cell and simply displaying the pipeline with and without reset_index()). \n",
    "# Flattening isn't always necessary, but here it is, as it will convert 'InvoiceDate_Date' from being an index \n",
    "# for the grouping, to being a column again."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "28eff905",
   "metadata": {
    "code_folding": [],
    "pycharm": {
     "name": "#%%\n"
    }
   },
   "outputs": [],
   "source": [
    "# Display the number of invoices per day\n",
    "\n",
    "# import plotly.graph_objects as go - just a reminder, this is how we imported plotly graph objects already\n",
    "fig = go.Figure()\n",
    "fig.add_trace(\n",
    "    go.Bar(x=counts['InvoiceDate_Date'], y=counts['InvoiceNo'])\n",
    ")\n",
    "\n",
    "fig.update_layout(xaxis_title='Date',\n",
    "                  yaxis_title='Invoices',\n",
    "                  title=f'Count of Invoices Recorded per Day',\n",
    "                  hovermode='x unified')\n",
    "\n",
    "# Feel free to re-run this cell with different hover modes to control the label style.\n",
    "# Choose between 'closest', 'x', 'y', 'x unified' (my favourite) or 'y unified'\n",
    "# More details here: https://plotly.com/python/hover-text-and-formatting/"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "fe37a2c1",
   "metadata": {
    "pycharm": {
     "name": "#%% md\n"
    }
   },
   "source": [
    "Es gibt Tage ohne Rechnungen. Dies könnte bedeuten, dass an diesem Tag wirklich keine Verkäufe getätigt wurden, oder es könnte ein Problem mit der Datenerfassung vorliegen. Idealerweise könnten wir das Unternehmen bitten, seine Pipelines zu überprüfen, aber wir können auch mit den Daten arbeiten, die wir haben. \n",
    "\n",
    "Wir werden den oben beschriebenen Vorgang des Plottens ziemlich oft wiederholen. Also verpacken wir sie in eine Funktion"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "ce75f831",
   "metadata": {
    "code_folding": [],
    "pycharm": {
     "name": "#%%\n"
    }
   },
   "outputs": [],
   "source": [
    "# Define plot_histogram\n",
    "def plot_histogram(data: pd.Series, x_title: str, y_title: str, title: str) -> None:\n",
    "    \"\"\"\n",
    "    Plot a histogram given a pandas data series\n",
    "    \n",
    "    Args:\n",
    "        data: the pandas series, e.g. df[some_column]\n",
    "        x_title, y_title: titles for the x and y axes, respectively\n",
    "        title: title of the plott itself\n",
    "    \"\"\"\n",
    "    fig = go.Figure()\n",
    "    fig.add_trace(\n",
    "        go.Histogram(x=data)\n",
    "    )\n",
    "        \n",
    "    fig.update_layout(xaxis_title=x_title,\n",
    "                      yaxis_title=y_title,\n",
    "                      title=title,\n",
    "                      hovermode='x unified')\n",
    "    fig.show()"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "791b4008",
   "metadata": {
    "pycharm": {
     "name": "#%% md\n"
    }
   },
   "source": [
    "Nun ist es an der Zeit, die Verteilung der Daten zu überprüfen."
   ]
  },
  {
   "cell_type": "markdown",
   "id": "6fb96d32",
   "metadata": {
    "pycharm": {
     "name": "#%% md\n"
    }
   },
   "source": [
    "__Challenge: Visualisierung der Daten mit selbst definierten Plotly-Funktionen__\n",
    "    \n",
    "Eine großartige Fähigkeit ist es, sich eine Funktionsdefinition und ihren Docstring anzusehen und herauszufinden, wie sie zu verwenden ist. Aus diesem Grund habe ich in der Definition von plot_histogram() \"type hints\" hinzugefügt. Typ-Hinweise zeigen dem Benutzer, welche Argumente die Funktion erwartet und von welchem Typ sie sein sollten (z. B. sollte 'title' ein String sein), und zeigen auch, was die Funktion zurückgibt (in diesem Fall ist die Ausgabe None, da die Funktion das Diagramm anzeigt, aber nicht zurückgibt). \n",
    "\n",
    "Für die nächste Aufgabe erstellen Sie also zwei neue Zellen, in denen Sie die Anwendung von plot_histogram() üben. Eine Zelle sollte die Verteilung der Mengenwerte darstellen, die andere die Werte des Stückpreises aus unserem df_data-Datenframe. Viel Erfolg!"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "8d894495",
   "metadata": {
    "code_folding": [],
    "pycharm": {
     "name": "#%%\n"
    }
   },
   "outputs": [],
   "source": [
    "# Plot distribution of 'Quantity' values\n",
    "plot_histogram(data=df_data['Quantity'],\n",
    "               x_title='Quantity Values',\n",
    "               y_title='Count',\n",
    "               title='Count of Quantity of Purchases Per Transaction')"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "e016c352",
   "metadata": {
    "code_folding": [],
    "pycharm": {
     "name": "#%%\n"
    }
   },
   "outputs": [],
   "source": [
    "# Plot distribution of 'Unit Price' values\n",
    "plot_histogram(data=df_data['UnitPrice'],\n",
    "               x_title='Unit Prices',\n",
    "               y_title='Count',\n",
    "               title='Count of Unit Price Values Per Transaction')"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "04b8d5b0",
   "metadata": {
    "pycharm": {
     "name": "#%% md\n"
    }
   },
   "source": [
    "Diese Daten sind eindeutig verzerrt, was auf einige extrem hohe Werte zurückzuführen ist. Ihr erster Gedanke könnte sein, diese Werte zu entfernen, aber wir haben bereits bestätigt, dass sie legitime Informationen enthalten, mit denen unsere Modelle umgehen müssen. Wir werden also keine derartigen Daten entfernen. "
   ]
  },
  {
   "cell_type": "markdown",
   "id": "ce25c5bb",
   "metadata": {
    "pycharm": {
     "name": "#%% md\n"
    }
   },
   "source": [
    "# Schritt 2: Die Werte für Recency, Frequency und Revenue berechnen"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "65218837",
   "metadata": {
    "pycharm": {
     "name": "#%% md\n"
    }
   },
   "source": [
    "## Recency\n",
    "\n",
    "Für die Recency (Aktualität) verwenden wir das letzte Kaufdatum pro Kunde*, um die inaktiven Tage zu berechnen. *Wir verwenden das InvoiceDate"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "0df701b4",
   "metadata": {
    "code_folding": [],
    "pycharm": {
     "name": "#%%\n"
    }
   },
   "outputs": [],
   "source": [
    "# Prepare Recency values\n",
    "# Create a generic customer dataframe to keep CustomerID and new segmentation scores\n",
    "df_customer = pd.DataFrame(df_data.CustomerID.unique(), columns=['CustomerID'])\n",
    "\n",
    "# Get the MaxPurchaseDate for each customer and create a dataframe with it\n",
    "df_max_purchase = df_data.groupby('CustomerID').InvoiceDate.max().reset_index()\n",
    "df_max_purchase.columns = ['CustomerID','MaxPurchaseDate']\n",
    "\n",
    "# Calculate the max of all MaxPurchaseDates, then calculate recency for each customer \n",
    "# based on the difference between this and their MaxPurchaseDate\n",
    "max_of_max_dates = df_max_purchase['MaxPurchaseDate'].max()\n",
    "df_max_purchase['Recency'] = (max_of_max_dates - df_max_purchase['MaxPurchaseDate']).dt.days\n",
    "\n",
    "# Merge this dataframe to our new customer dataframe\n",
    "df_customer = pd.merge(df_customer, df_max_purchase, on='CustomerID')\n",
    "df_customer.sample(10)"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "1fa8c06d",
   "metadata": {
    "pycharm": {
     "name": "#%% md\n"
    }
   },
   "source": [
    "## Frequency"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "3cfa29e0",
   "metadata": {
    "code_folding": [],
    "pycharm": {
     "name": "#%%\n"
    }
   },
   "outputs": [],
   "source": [
    "# Add each customer's minimum purchase date\n",
    "df_min_purchase = df_data.groupby('CustomerID').InvoiceDate.min().reset_index()\n",
    "df_min_purchase.columns = ['CustomerID','MinPurchaseDate']\n",
    "# Merge this dataframe to our new customer dataframe\n",
    "df_customer = pd.merge(df_customer, df_min_purchase, on='CustomerID')"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "bfc5ca91",
   "metadata": {
    "code_folding": [],
    "pycharm": {
     "name": "#%%\n"
    }
   },
   "outputs": [],
   "source": [
    "# Calculate each customer's lifespan\n",
    "df_customer['Lifespan'] = (df_customer.MaxPurchaseDate - df_customer.MinPurchaseDate).dt.days"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "d01960f1",
   "metadata": {
    "code_folding": [],
    "pycharm": {
     "name": "#%%\n"
    }
   },
   "outputs": [],
   "source": [
    "# Prepare Frequency values\n",
    "# Get the number of purchases for each customer and create a dataframe with it; combine with df_customer\n",
    "df_frequency = df_data.groupby('CustomerID').InvoiceDate_Date.nunique().reset_index()\n",
    "df_data.drop(columns=['InvoiceDate_Date'], inplace=True)\n",
    "df_frequency.columns = ['CustomerID', 'Frequency']\n",
    "\n",
    "df_customer = pd.merge(df_customer, df_frequency, on='CustomerID')"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "260bf146",
   "metadata": {
    "code_folding": [
     0
    ],
    "pycharm": {
     "name": "#%%\n"
    }
   },
   "outputs": [],
   "source": [
    "# Remove 'MaxPurchaseDate', 'MinPurchaseDate' as they are no longer needed\n",
    "df_customer.drop(columns=['MaxPurchaseDate', 'MinPurchaseDate'], inplace=True)"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "06b26b19",
   "metadata": {
    "pycharm": {
     "name": "#%% md\n"
    }
   },
   "source": [
    "## MonetaryValue"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "944d81c9",
   "metadata": {
    "pycharm": {
     "name": "#%% md\n"
    }
   },
   "source": [
    "__Challenge: MonetaryValue Berechnen__\n",
    "\n",
    "Für diese Aufgabe müssen wir eine neue Spalte mit der Bezeichnung \"MonetaryValue\" hinzufügen. Für jede Zeile in den Daten sollte MonetaryValue der UnitPrice multipliziert mit der Quantity sein. Auf diese Weise können wir den Geldwert pro Kunde ermitteln. \n",
    "\n",
    "Das mag kompliziert klingen, aber sobald wir die neue Spalte haben, ist der Prozess derselbe wie bei den Spalten \"Recency\" und \"Frequency\". Testen Sie also Ihr Gedächtnis, und wenn Sie wirklich nicht weiterkommen, können Sie sich von dem, was wir oben gemacht haben, inspirieren lassen. \n",
    "\n",
    "Viel Erfolg!"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "c09dcd1c",
   "metadata": {
    "code_folding": [],
    "pycharm": {
     "name": "#%%\n"
    }
   },
   "outputs": [],
   "source": [
    "# Solution: Prepare Monetary Value values\n",
    "df_data['MonetaryValue'] = df_data['UnitPrice'] * df_data['Quantity']\n",
    "df_monetary_value = df_data.groupby('CustomerID').MonetaryValue.sum().reset_index()\n",
    "df_customer = pd.merge(df_customer, df_monetary_value, on='CustomerID')"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "0ce38ee2",
   "metadata": {
    "pycharm": {
     "name": "#%% md\n"
    }
   },
   "source": [
    "## Der vorbereiteten R-, F- und M-Werte Visualisieren"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "0356f39d",
   "metadata": {
    "code_folding": [],
    "pycharm": {
     "name": "#%%\n"
    }
   },
   "outputs": [],
   "source": [
    "# Examine the final R, F & M scores\n",
    "print(\"Sample:\")\n",
    "display(df_customer.sample(10))\n",
    "\n",
    "print(\"Summary statistics:\")\n",
    "rfm_cols = ['Recency', 'Frequency', 'MonetaryValue']\n",
    "display(df_customer[rfm_cols].describe().round(3))"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "3659df34",
   "metadata": {
    "code_folding": [],
    "pycharm": {
     "name": "#%%\n"
    }
   },
   "outputs": [],
   "source": [
    "# Visualise the distributions of R, F & M values per shopper\n",
    "titles = ['Recency Values (Days Since Last Purchase) Per Shopper',\n",
    "          'Purchase Frequency Values per Shopper',\n",
    "          'Monetary Value Values per Shopper']\n",
    "\n",
    "for col, title in zip(rfm_cols, titles):\n",
    "    plot_histogram(data=df_customer[col], x_title=col, y_title='Count', title=title)\n",
    "    \n",
    "# A note on zip(), for the unfamiliar:\n",
    "# Zip takes in iterables (lists, pandas Series, etc), which are objects that can return their members one at a time.\n",
    "# It then iterates over the iterables, always returning one of each. \n",
    "\n",
    "# Example:\n",
    "# for col, title in zip(rfm_cols, titles):\n",
    "#     print(f\"Columm: {col}; Title: {title}\")\n",
    "# This will print:\n",
    "# Columm: Recency; Title: Recency Values (Days Since Last Purchase) Per Shopper\n",
    "# Columm: Frequency; Title: Purchase Frequency Values per Shopper\n",
    "# Columm: MonetaryValue; Title: Monetary Value Values per Shopper"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "40c7725b",
   "metadata": {
    "pycharm": {
     "name": "#%% md\n"
    }
   },
   "source": [
    "# Schritt 3: Kunden anhand ihrer R-, F- und M-Bewertungen clustern\n",
    "\n",
    "## Wichtiger Hinweis: K-Means-Annahmen (Assumptions)\n",
    "\n",
    "Die Annahmen von K-Means sind:\n",
    "\n",
    "- symmetrische Verteilungen (keine Skewness/Schiefe) zwischen den Variablen\n",
    "- gleicher Durchschnittswert für alle Variablen (damit sie die Segmentierung nicht verzerren)\n",
    "- gleiche Standardabweichung für alle Variablen\n",
    "\n",
    "Wir haben gesehen, dass keine der Verteilungen symmetrisch ist. In der Tat waren sie alle rechtsschief. Daher sollten wir sie transformieren.\n",
    "\n",
    "Es gibt jedoch noch ein Problem, mit dem wir umgehen müssen: Die beiden Spalten MonetaryValue und Recency enthalten jetzt 0s, die die Log-Transformation nicht verarbeiten kann. Daher fügen wir zu all ihren Werten 1 hinzu, bevor wir die Transformation durchführen."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "0ac4df27",
   "metadata": {
    "pycharm": {
     "name": "#%%\n"
    }
   },
   "outputs": [],
   "source": [
    "# Add 1 to all Recency and MonetaryValue scores, so that the log transformation works correctly for them\n",
    "df_customer['Recency'] += 1\n",
    "df_customer['MonetaryValue'] += 1"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "1fb74033",
   "metadata": {
    "code_folding": [],
    "pycharm": {
     "name": "#%%\n"
    }
   },
   "outputs": [],
   "source": [
    "# Apply the log transformation\n",
    "for col in rfm_cols:\n",
    "    df_customer[f'{col}_log'] = np.log10(df_customer[[col]])\n",
    "    # A note on log10, for the unsure:\n",
    "    # An explanation of logarithms is beyond the scope of this course, but resources are available online\n",
    "    # Example: https://www.mathsisfun.com/algebra/logarithms.html\n",
    "    \n",
    "    #  Visualise the transformed scores\n",
    "    plot_histogram(data=df_customer[f'{col}_log'],\n",
    "                   x_title=f'{col} (Log-Transformed)',\n",
    "                   y_title='Count',\n",
    "                   title=col)"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "79d45dca",
   "metadata": {
    "pycharm": {
     "name": "#%% md\n"
    }
   },
   "source": [
    "## Recency clustern\n",
    "\n",
    "Jetzt können wir die Kunden auf der Grundlage ihrer Recency Bewertungen clustern. \n",
    "\n",
    "Zunächst müssen wir K mithilfe der Elbow-Methode definieren.\n",
    "Beachten Sie, dass das optimale K auch von den Unternehmenszielen beeinflusst werden kann. "
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "17cfaa7d",
   "metadata": {
    "code_folding": [],
    "pycharm": {
     "name": "#%%\n"
    }
   },
   "outputs": [],
   "source": [
    "# Try K-Means clustering with different values of K for the log-transformed recency scores\n",
    "ssd={}\n",
    "\n",
    "tmp = df_customer[['Recency_log']].reset_index()\n",
    "for k_tmp in range(1, 10):\n",
    "    kmeans = KMeans(n_clusters=k_tmp, max_iter=1000, random_state=rand_state).fit(tmp)\n",
    "    ssd[k_tmp] = kmeans.inertia_"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "76ccbd13",
   "metadata": {
    "code_folding": [],
    "pycharm": {
     "name": "#%%\n"
    }
   },
   "outputs": [],
   "source": [
    "# Visualise the inertia / sum of squared distances (ssd.values()) for each value of k (ssd.keys()) we tried\n",
    "fig = go.Figure()\n",
    "fig.add_trace(\n",
    "            go.Scatter(x=list(ssd.keys()),\n",
    "                       y=list(ssd.values()),\n",
    "                       mode='lines'))\n",
    "\n",
    "fig.update_layout(xaxis_title='Number of Clusters',\n",
    "                  yaxis_title='SSD',\n",
    "                  title='SSD per Number of Clusters',\n",
    "                  hovermode='x unified')\n",
    "\n",
    "fig.show()"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "55b12b1b",
   "metadata": {
    "pycharm": {
     "name": "#%% md\n"
    }
   },
   "source": [
    "Die Grafik zeigt, dass zwei Cluster wahrscheinlich ideal sind. Je nach Geschäftsanforderungen können Sie auch mit mehr, z. B. drei Clustern experimentieren, die Ihre Kunden mit hohem, mittlerem und niedrigem Wert widerspiegeln.\n",
    "\n",
    "Sie haben bereits gesehen, wie man ein Clustering durchführt. Da wir das jetzt ein paar Mal machen müssen, erstellen wir eine Funktion, die die Arbeit für uns erledigt."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "61640ee2",
   "metadata": {
    "code_folding": [],
    "pycharm": {
     "name": "#%%\n"
    }
   },
   "outputs": [],
   "source": [
    "# Define cluster_data()\n",
    "\n",
    "def cluster_data(data: pd.DataFrame, kmeans: KMeans, cols_to_cluster: List[str]) -> None:\n",
    "    \"\"\"\n",
    "    Fit a Kmeans model to a dataframe for a specified cluster column; assign cluster values to the data\n",
    "    \n",
    "    Args:\n",
    "        data: the data to process\n",
    "        kmeans: an object of the class sklearn.cluster._kmeans.KMeans\n",
    "        cols_to_cluster: the columns upon which to cluster, e.g. ['Recency']\n",
    "    \"\"\"\n",
    "    kmeans.fit(data[cols_to_cluster])\n",
    "    new_col_name = '_'.join(cols_to_cluster)\n",
    "    data[f'{new_col_name}_Cluster_(k={kmeans.n_clusters})'] = kmeans.predict(data[cols_to_cluster])\n",
    "    # An example final new column, if e.g. cols_to_cluster = ['Recency', 'Frequency'] and kmeans.n_clusters = 2,\n",
    "    # would be 'Recency_Frequency_Cluster_(k=2)' \n",
    "\n",
    "    return data"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "eeac8283",
   "metadata": {
    "code_folding": [],
    "pycharm": {
     "name": "#%%\n"
    }
   },
   "outputs": [],
   "source": [
    "# Build our clusters for recency and add them to the dataframe\n",
    "k_range = [2,3,4]\n",
    "for k_tmp in k_range:\n",
    "    kmeans = KMeans(n_clusters=k_tmp, max_iter=1000, random_state=rand_state)\n",
    "    df_customer = cluster_data(data=df_customer, kmeans=kmeans, cols_to_cluster=['Recency_log'])"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "6bf111a5",
   "metadata": {
    "pycharm": {
     "name": "#%% md\n"
    }
   },
   "source": [
    "K-means weist die Labels nicht in geordneter Weise zu, d. h. 1 ist nicht unbedingt das beste/schlechteste im Vergleich zu k. Wir müssen die Cluster neu ordnen, so dass ein höheres Label eine bessere R-, F- oder M-Bewertung anzeigt. Zum Beispiel erhalten die Kunden mit den ältesten Aktualitätswerten eine niedrigere Label (0)."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "97bb39ae",
   "metadata": {
    "code_folding": [],
    "pycharm": {
     "name": "#%%\n"
    }
   },
   "outputs": [],
   "source": [
    "# Re-order cluster labels so that a high label number indicates a more desirable score\n",
    "\n",
    "for k_tmp in k_range:\n",
    "    # Group by cluster labels and get the mean of the Recency values per cluster label,\n",
    "    tmp = (df_customer\n",
    "           .groupby(f'Recency_log_Cluster_(k={k_tmp})')['Recency']\n",
    "           .mean()\n",
    "           .reset_index()\n",
    "           # Then sort by the Recency values and drop the index\n",
    "           .sort_values(by='Recency', ascending=False)\n",
    "           .reset_index(drop=True))\n",
    "    \n",
    "    # Assign a new rank column using the index of the sorted data\n",
    "    tmp['Rank'] = tmp.index\n",
    "\n",
    "    \n",
    "    # Merge the data frames on the cluster labels, drop the original cluster label column, replacing it with rank\n",
    "    df_customer = (pd.merge(df_customer, tmp[[f'Recency_log_Cluster_(k={k_tmp})', 'Rank']],\n",
    "                            on=f'Recency_log_Cluster_(k={k_tmp})')\n",
    "                   .drop([f'Recency_log_Cluster_(k={k_tmp})'], axis=1)\n",
    "                   .rename(columns={'Rank': f'Recency_log_Cluster_(k={k_tmp})'}))"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "87aecd73",
   "metadata": {
    "pycharm": {
     "name": "#%% md\n"
    }
   },
   "source": [
    "__Challenge: Eine Cluster Relabelling Funktion Definieren__\n",
    "\n",
    "Da wir diese Aktion mehrmals durchführen müssen, verpacken wir den Vorgang wieder in eine Funktion.\n",
    "\n",
    "Wenn Sie darüber nachdenken, wie Sie diese Funktion aufbauen wollen, können Sie den obigen Code als Leitfaden verwenden. Aber denken Sie daran, dass Sie die for-Schleife nicht brauchen: Wir können die Funktion stattdessen später innerhalb einer for-Schleife aufrufen. \n",
    "\n",
    "Wenn Sie wirklich nicht weiterkommen, habe ich in der nächsten Zelle einen Hinweis vorbereitet. Viel Glück!"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "ac076396",
   "metadata": {
    "code_folding": [],
    "pycharm": {
     "name": "#%%\n"
    }
   },
   "outputs": [],
   "source": [
    "# Challenge Hint: This is the function definition and docstring I used in my answer.\n",
    "def relabel_clusters(data: pd.DataFrame, cluster_col: str, cluster_col_orig: str, ascending: bool) -> pd.DataFrame:\n",
    "    \"\"\"\n",
    "    Re-label clusters so that a higher cluster label indicates a better R, F or M score\n",
    "    \n",
    "    Args:\n",
    "        data: the data to process\n",
    "        cluster_col: the column containing cluster labels, e.g. 'Recency_Cluster'\n",
    "        cluster_col_orig: the column which was used to create the clusters, e.g. 'Recency'\n",
    "        ascending: whether a higher mean cluster_col_orig value is better (ascending=True) or worse\n",
    "        (ascending=False). E.g. if cluster_col_orig='MonetaryValue', ascending=True\n",
    "        \n",
    "    \"\"\"\n",
    "    \n",
    "    raise NotImplementedError(\"This is just a function definition and docstring; your challenge is to implement it\")"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "034a91c9",
   "metadata": {
    "code_folding": [],
    "pycharm": {
     "name": "#%%\n"
    }
   },
   "outputs": [],
   "source": [
    "# Challenge Solution: Define relabel_clusters()\n",
    "def relabel_clusters(data: pd.DataFrame, cluster_col: str, cluster_col_orig: str, ascending: bool) -> pd.DataFrame:\n",
    "    \"\"\"\n",
    "    Re-label clusters so that a higher cluster label indicates a better R, F or M score\n",
    "    \n",
    "    Args:\n",
    "        data: the data to process\n",
    "        cluster_col: the column containing cluster labels, e.g. 'Recency_Cluster'\n",
    "        cluster_col_orig: the column which was used to create the clusters, e.g. 'Recency'\n",
    "        ascending: whether a higher mean cluster_col_orig value is better (ascending=True) or worse\n",
    "        (ascending=False). E.g. if cluster_col_orig='MonetaryValue', ascending=True\n",
    "        \n",
    "    \"\"\"\n",
    "    # Group by cluster labels and get the mean of cluster_col_orig per cluster label\n",
    "    data_tmp = data.groupby(cluster_col)[cluster_col_orig].mean().reset_index()\n",
    "    # Sort by cluster_col_orig values and drop the index\n",
    "    data_tmp = data_tmp.sort_values(by=cluster_col_orig, ascending=ascending).reset_index(drop=True)\n",
    "    # Assign a new rank column using the index of the sorted data\n",
    "    data_tmp['Rank'] = data_tmp.index\n",
    "    # Merge the two data frames on the cluster labels, drop the original cluster label column, replace it with rank\n",
    "    data_new = (pd.merge(data, data_tmp[[cluster_col, 'Rank']], on=cluster_col)\n",
    "                .drop([cluster_col], axis=1)\n",
    "                .rename(columns={\"Rank\": cluster_col}))\n",
    "                \n",
    "    return data_new\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "17cb643f",
   "metadata": {
    "code_folding": [],
    "pycharm": {
     "name": "#%%\n"
    }
   },
   "outputs": [],
   "source": [
    "# Visualise the clusters in order to help choose the best value of k\n",
    "col = 'Recency'\n",
    "k_range = [2, 3, 4]\n",
    "for k_tmp in k_range:\n",
    "    fig = go.Figure()\n",
    "    fig.add_trace(\n",
    "        go.Scatter(\n",
    "            x=df_customer[col], y=np.random.randint(low=0, high=100, size=len(df_customer)), mode='markers',\n",
    "            marker_color=df_customer[f'{col}_log_Cluster_(k={k_tmp})'], marker_colorscale='plotly3',\n",
    "            hovertemplate='Recency: %{x}'\n",
    "                          '<extra></extra>'\n",
    "        )\n",
    "    )\n",
    "    # Other plotly colorscales can be found here: https://plotly.com/python/builtin-colorscales/\n",
    "    fig.update_layout(\n",
    "        xaxis_title=col, title=f'{col} Values in {k_tmp} Clusters',\n",
    "        yaxis=go.layout.YAxis(title='Random Scatter', showticklabels=False)\n",
    "    )\n",
    "    fig.show()"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "4198410f",
   "metadata": {
    "pycharm": {
     "name": "#%% md\n"
    }
   },
   "source": [
    "## Frequency & MonetaryValue clustern\n",
    "\n",
    "Probieren wir noch einmal zahlreiche k-Werte für die Frequency- und MonetaryValue aus und entscheiden wir, wie viele Cluster jeweils verwendet werden sollen.\n",
    "\n",
    "Die Fehlerkurven sehen fast identisch zueinander und zu der Kurve aus, die wir für verschiedene Anzahlen von Recency-Clustern gesehen haben. Das liegt daran, dass wir die Daten logarithmisch skaliert haben, so dass die Werte für alle drei Variablen in einem ähnlichen Bereich liegen. Daher sind auch die Fehler ähnlich."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "2ce8381a",
   "metadata": {
    "code_folding": [],
    "pycharm": {
     "name": "#%%\n"
    }
   },
   "outputs": [],
   "source": [
    "# Try different k values for Frequency and MonetaryValue_log and plot the results\n",
    "for col in ['Frequency_log', 'MonetaryValue_log']:\n",
    "    ssd={}\n",
    "    \n",
    "    tmp = df_customer[[col]].reset_index()\n",
    "    for k_tmp in range(1, 10):\n",
    "        kmeans = KMeans(n_clusters=k_tmp, max_iter=1000, random_state=rand_state).fit(tmp)\n",
    "        ssd[k_tmp] = kmeans.inertia_\n",
    "        \n",
    "    fig = go.Figure()\n",
    "    fig.add_trace(\n",
    "        go.Scatter(x=list(ssd.keys()),\n",
    "                   y=list(ssd.values()),\n",
    "                   mode='lines'))\n",
    "\n",
    "    fig.update_layout(xaxis_title='Number of Clusters',\n",
    "                      yaxis_title='SSD',\n",
    "                      title=f'SSD per Number of Clusters for {col}',\n",
    "                      hovermode='x unified')\n",
    "    fig.show()\n",
    "    print(ssd)"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "a5fdc9f1",
   "metadata": {
    "pycharm": {
     "name": "#%% md\n"
    }
   },
   "source": [
    "Auf der Grundlage dieser Ergebnisse sollten wir bei einem k-Wert von 2 bleiben. \n",
    "\n",
    "Wir können nun ganz einfach die Frequency- und MonetaryValue-Cluster ableiten und sie neu anordnen, indem wir die zuvor definierten Funktionen und eine einfache for-loop verwenden."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "d7374361",
   "metadata": {
    "code_folding": [],
    "pycharm": {
     "name": "#%%\n"
    }
   },
   "outputs": [],
   "source": [
    "# Build our clusters for Frequency and MonetaryValue and add them to the dataframe\n",
    "for k_tmp in k_range:\n",
    "    for col in  ['Frequency_log', 'MonetaryValue_log']:\n",
    "        kmeans = KMeans(n_clusters=k_tmp, max_iter=1000, random_state=rand_state)\n",
    "        df_customer = cluster_data(data=df_customer, kmeans=kmeans, cols_to_cluster=[col])\n",
    "        df_customer = relabel_clusters(data=df_customer,\n",
    "                                       cluster_col=f'{col}_Cluster_(k={k_tmp})',\n",
    "                                       cluster_col_orig=col,\n",
    "                                       ascending=True)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "8d7fc5ad",
   "metadata": {
    "code_folding": [],
    "pycharm": {
     "name": "#%%\n"
    }
   },
   "outputs": [],
   "source": [
    "# Visualise the clusters in order to help choose the best value of k\n",
    "k_range = [2,3,4]\n",
    "for col in ['Frequency', 'MonetaryValue']:\n",
    "    for k_tmp in k_range:\n",
    "        fig = go.Figure()    \n",
    "        fig.add_trace(\n",
    "            go.Scatter(\n",
    "                x=df_customer[col], y=np.random.randint(low=0, high=100, size=len(df_customer)), mode='markers',\n",
    "                marker_color=df_customer[f'{col}_log_Cluster_(k={k_tmp})'], marker_colorscale='plotly3',\n",
    "                hovertemplate=f'{col}'+': %{x}'\n",
    "                              '<extra></extra>'\n",
    "            )\n",
    "        )\n",
    "        \n",
    "        fig.update_layout(\n",
    "            xaxis_title=col, title=f'{col} Values in {k_tmp} Clusters',\n",
    "            yaxis = go.layout.YAxis(title='Random Scatter', showticklabels=False)\n",
    "        )\n",
    "        fig.show()"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "8ac9ccaa",
   "metadata": {
    "pycharm": {
     "name": "#%% md\n"
    }
   },
   "source": [
    "## Schritt 4: Berechnung der Gesamtpunktzahl pro Kunde\n",
    "\n",
    "Wir werden einfach die Summe aller drei R, F & M Scores verwenden.\n",
    "\n",
    "Danach können wir die Daten nach der OverallScore gruppiert betrachten und die Mittelwerte der ursprünglichen (d. h. nicht transformierten) R-, F- und M-Werte pro OverallScore überprüfen."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "4475b4e2",
   "metadata": {
    "code_folding": [],
    "pycharm": {
     "name": "#%%\n"
    }
   },
   "outputs": [],
   "source": [
    "# Calculate overall scores\n",
    "k=2\n",
    "df_customer['OverallScore'] = (df_customer[f'Recency_log_Cluster_(k={k})']\n",
    "                           + df_customer[f'Frequency_log_Cluster_(k={k})']\n",
    "                           + df_customer[f'MonetaryValue_log_Cluster_(k={k})'])\n",
    "                           \n",
    "df_customer.groupby('OverallScore')[rfm_cols].mean()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "d54ccbf7",
   "metadata": {
    "pycharm": {
     "name": "#%%\n"
    }
   },
   "outputs": [],
   "source": [
    "# Visualise the number of customers per score (helps in assigning Tiers)\n",
    "pd.DataFrame(df_customer.value_counts('OverallScore')).reset_index().sort_values(by='OverallScore')"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "9dcd1ec9",
   "metadata": {
    "pycharm": {
     "name": "#%% md\n"
    }
   },
   "source": [
    "Schließlich weisen wir jedem Wert von OverallScore einen Namen zu und visualisieren das Ergebnis.\n",
    "\n",
    "- 0 = Low Value\n",
    "- 1 = Low-Mid Value\n",
    "- 2 = Mid-High Value\n",
    "- 3 = High Value"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "268fec26",
   "metadata": {
    "code_folding": [],
    "pycharm": {
     "name": "#%%\n"
    }
   },
   "outputs": [],
   "source": [
    "# Assign customers to tiers; show how many customers we have per tier\n",
    "df_customer['Tier'] = 'Low-Value'\n",
    "df_customer.loc[df_customer['OverallScore'] == 1,'Tier'] = 'Low-Mid-Value' \n",
    "df_customer.loc[df_customer['OverallScore'] == 2,'Tier'] = 'Mid-High-Value'\n",
    "df_customer.loc[df_customer['OverallScore'] == 3,'Tier'] = 'High-Value'"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "987e743c",
   "metadata": {
    "code_folding": [],
    "pycharm": {
     "name": "#%%\n"
    }
   },
   "outputs": [],
   "source": [
    "# Plot MonetaryValue and Frequency\n",
    "plot_data = [\n",
    "    go.Scatter(\n",
    "        x=df_customer.query(\"Tier == 'Low-Value'\")['Frequency'],\n",
    "        y=df_customer.query(\"Tier == 'Low-Value'\")['MonetaryValue'],\n",
    "        mode='markers',\n",
    "        name='Low',\n",
    "        marker=dict(size= 7,\n",
    "            line=dict(width=1),\n",
    "            color='blue',\n",
    "                    \n",
    "            opacity=0.5\n",
    "           )\n",
    "    ),\n",
    "        go.Scatter(\n",
    "        x=df_customer.query(\"Tier == 'Low-Mid-Value'\")['Frequency'],\n",
    "        y=df_customer.query(\"Tier == 'Low-Mid-Value'\")['MonetaryValue'],\n",
    "        mode='markers',\n",
    "        name='Low-Mid',\n",
    "        marker=dict(size=7,\n",
    "            line=dict(width=1),\n",
    "            color='red',\n",
    "            opacity=0.5\n",
    "           )\n",
    "    ),\n",
    "        go.Scatter(\n",
    "        x=df_customer.query(\"Tier == 'Mid-High-Value'\")['Frequency'],\n",
    "        y=df_customer.query(\"Tier == 'Mid-High-Value'\")['MonetaryValue'],\n",
    "        mode='markers',\n",
    "        name='Mid-High',\n",
    "        marker=dict(size=7,\n",
    "            line=dict(width=1),\n",
    "            color='green',\n",
    "            opacity=0.5\n",
    "           )\n",
    "    ),\n",
    "        go.Scatter(\n",
    "            x=df_customer.query(\"Tier == 'High-Value'\")['Frequency'],\n",
    "            y=df_customer.query(\"Tier == 'High-Value'\")['MonetaryValue'],\n",
    "            mode='markers',\n",
    "            name='High',\n",
    "            marker=dict(size=7,\n",
    "                line=dict(width=1),\n",
    "                color='purple',\n",
    "                opacity=0.5\n",
    "               )\n",
    "        )\n",
    "]\n",
    "\n",
    "plot_layout = go.Layout(\n",
    "        yaxis={'title': \"MonetaryValue\"},\n",
    "        xaxis={'title': \"Frequency\"},\n",
    "        title='Tiering Customers For MonetaryValue and Frequency'\n",
    "    )\n",
    "fig = go.Figure(data=plot_data, layout=plot_layout)\n",
    "fig.show()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "330713f4",
   "metadata": {
    "code_folding": [],
    "pycharm": {
     "name": "#%%\n"
    }
   },
   "outputs": [],
   "source": [
    "# Plot MonetaryValue and Recency\n",
    "plot_data = [\n",
    "    go.Scatter(\n",
    "        x=df_customer.query(\"Tier == 'Low-Value'\")['Recency'],\n",
    "        y=df_customer.query(\"Tier == 'Low-Value'\")['MonetaryValue'],\n",
    "        mode='markers',\n",
    "        name='Low',\n",
    "        marker= dict(size= 7,\n",
    "            line= dict(width=1),\n",
    "            color= 'blue',\n",
    "            opacity= 0.5\n",
    "           )\n",
    "    ),\n",
    "        go.Scatter(\n",
    "        x=df_customer.query(\"Tier == 'Low-Mid-Value'\")['Recency'],\n",
    "        y=df_customer.query(\"Tier == 'Low-Mid-Value'\")['MonetaryValue'],\n",
    "        mode='markers',\n",
    "        name='Low-Mid',\n",
    "        marker= dict(size=7,\n",
    "            line= dict(width=1),\n",
    "            color= 'red',\n",
    "            opacity= 0.5\n",
    "           )\n",
    "    ),\n",
    "        go.Scatter(\n",
    "        x=df_customer.query(\"Tier == 'Mid-High-Value'\")['Recency'],\n",
    "        y=df_customer.query(\"Tier == 'Mid-High-Value'\")['MonetaryValue'],\n",
    "        mode='markers',\n",
    "        name='Mid-High',\n",
    "        marker= dict(size=7,\n",
    "            line= dict(width=1),\n",
    "            color= 'green',\n",
    "            opacity= 0.5\n",
    "           )\n",
    "    ),\n",
    "        go.Scatter(\n",
    "            x=df_customer.query(\"Tier == 'High-Value'\")['Recency'],\n",
    "            y=df_customer.query(\"Tier == 'High-Value'\")['MonetaryValue'],\n",
    "            mode='markers',\n",
    "            name='High',\n",
    "            marker= dict(size=7,\n",
    "                line= dict(width=1),\n",
    "                color= 'purple',\n",
    "                opacity= 0.5\n",
    "               )\n",
    "        )\n",
    "]\n",
    "\n",
    "plot_layout = go.Layout(\n",
    "        yaxis= {'title': \"MonetaryValue\"},\n",
    "        xaxis= {'title': \"Recency\"},\n",
    "        title='Tiering Customers For MonetaryValue and Recency'\n",
    "    )\n",
    "fig = go.Figure(data=plot_data, layout=plot_layout)\n",
    "fig.show()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "94280db3",
   "metadata": {
    "code_folding": [],
    "pycharm": {
     "name": "#%%\n"
    }
   },
   "outputs": [],
   "source": [
    "# Plot Frequency and Recency\n",
    "plot_data = [\n",
    "    go.Scatter(\n",
    "        x=df_customer.query(\"Tier == 'Low-Value'\")['Frequency'],\n",
    "        y=df_customer.query(\"Tier == 'Low-Value'\")['Recency'],\n",
    "        mode='markers',\n",
    "        name='Low',\n",
    "        marker= dict(size= 7,\n",
    "            line= dict(width=1),\n",
    "            color= 'blue',\n",
    "            opacity= 0.5\n",
    "           )\n",
    "    ),\n",
    "        go.Scatter(\n",
    "        x=df_customer.query(\"Tier == 'Low-Mid-Value'\")['Frequency'],\n",
    "        y=df_customer.query(\"Tier == 'Low-Mid-Value'\")['Recency'],\n",
    "        mode='markers',\n",
    "        name='Low-Mid',\n",
    "        marker= dict(size=7,\n",
    "            line= dict(width=1),\n",
    "            color= 'red',\n",
    "            opacity= 0.5\n",
    "           )\n",
    "    ),\n",
    "        go.Scatter(\n",
    "        x=df_customer.query(\"Tier == 'Mid-High-Value'\")['Frequency'],\n",
    "        y=df_customer.query(\"Tier == 'Mid-High-Value'\")['Recency'],\n",
    "        mode='markers',\n",
    "        name='Mid-High',\n",
    "        marker= dict(size=7,\n",
    "            line= dict(width=1),\n",
    "            color= 'green',\n",
    "            opacity= 0.5\n",
    "           )\n",
    "    ),\n",
    "        go.Scatter(\n",
    "            x=df_customer.query(\"Tier == 'High-Value'\")['Frequency'],\n",
    "            y=df_customer.query(\"Tier == 'High-Value'\")['Recency'],\n",
    "            mode='markers',\n",
    "            name='High',\n",
    "            marker= dict(size=7,\n",
    "                line= dict(width=1),\n",
    "                color= 'purple',\n",
    "                opacity= 0.5\n",
    "               )\n",
    "        )\n",
    "]\n",
    "\n",
    "plot_layout = go.Layout(\n",
    "        yaxis= {'title': 'Recency'},\n",
    "        xaxis= {'title': 'Frequency'},\n",
    "        title='Tiering Customers For Frequency and Recency'\n",
    "    )\n",
    "fig = go.Figure(data=plot_data, layout=plot_layout)\n",
    "fig.show()"
   ]
  }
 ],
 "metadata": {
  "kernelspec": {
   "display_name": "Python 3 (ipykernel)",
   "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.10"
  }
 },
 "nbformat": 4,
 "nbformat_minor": 5
}