lessons/PandasDataCleaning.ipynb

{
  "cells": [
    {
      "attachments": {},
      "cell_type": "markdown",
      "metadata": {
        "colab_type": "text",
        "id": "view-in-github"
      },
      "source": [
        "<a href=\"https://colab.research.google.com/github/redadmiral/python-for-journalists/blob/main/lessons/PandasDataCleaning.ipynb\" target=\"_parent\"><img src=\"https://colab.research.google.com/assets/colab-badge.svg\" alt=\"Open In Colab\"/></a>"
      ]
    },
    {
      "cell_type": "markdown",
      "metadata": {
        "id": "0hKSq86IRQ9j"
      },
      "source": [
        "# Data Cleaning\n",
        "\n",
        "Nachdem wir in der Explorativen Analyse gesehen haben, wie unser Datensatz aufgebaut ist und welche Probleme er hat, machen wir uns nun daran, den Datensatz zu bereinigen um sicher damit arbeiten zu können.\n",
        "\n",
        "Den Datensatz speichern wir dann anschließend ab, um die Analyse auf dem bereinigten Datensatz durchführen zu können.\n",
        "\n",
        "# Datensatz reduzieren\n",
        "\n",
        "Zuerst lesen wir den Datensatz ein und wiederholen wir den Schritt aus der explorativen Analyse und reduzieren ihn auf die von uns benötigten Spalten:"
      ]
    },
    {
      "cell_type": "code",
      "execution_count": 1,
      "metadata": {
        "colab": {
          "base_uri": "https://localhost:8080/",
          "height": 557
        },
        "id": "BcI7uISgRNQH",
        "outputId": "c52fcb15-0011-49f2-e09e-9bee0a71be4a"
      },
      "outputs": [
        {
          "data": {
            "text/html": [
              "\n",
              "  <div id=\"df-7d1ef807-5e05-4c71-864d-92731cd158fa\">\n",
              "    <div class=\"colab-df-container\">\n",
              "      <div>\n",
              "<style scoped>\n",
              "    .dataframe tbody tr th:only-of-type {\n",
              "        vertical-align: middle;\n",
              "    }\n",
              "\n",
              "    .dataframe tbody tr th {\n",
              "        vertical-align: top;\n",
              "    }\n",
              "\n",
              "    .dataframe thead th {\n",
              "        text-align: right;\n",
              "    }\n",
              "</style>\n",
              "<table border=\"1\" class=\"dataframe\">\n",
              "  <thead>\n",
              "    <tr style=\"text-align: right;\">\n",
              "      <th></th>\n",
              "      <th>StichtagDatJahr</th>\n",
              "      <th>HalterId</th>\n",
              "      <th>AlterV10Cd</th>\n",
              "      <th>SexLang</th>\n",
              "      <th>KreisCd</th>\n",
              "      <th>KreisLang</th>\n",
              "      <th>QuarCd</th>\n",
              "      <th>QuarLang</th>\n",
              "      <th>Rasse1Text</th>\n",
              "      <th>GebDatHundJahr</th>\n",
              "      <th>AlterVHundCd</th>\n",
              "      <th>SexHundLang</th>\n",
              "      <th>HundefarbeText</th>\n",
              "    </tr>\n",
              "  </thead>\n",
              "  <tbody>\n",
              "    <tr>\n",
              "      <th>0</th>\n",
              "      <td>2015</td>\n",
              "      <td>126</td>\n",
              "      <td>60</td>\n",
              "      <td>männlich</td>\n",
              "      <td>9</td>\n",
              "      <td>Kreis 9</td>\n",
              "      <td>92</td>\n",
              "      <td>Altstetten</td>\n",
              "      <td>Welsh Terrier</td>\n",
              "      <td>2011</td>\n",
              "      <td>3</td>\n",
              "      <td>weiblich</td>\n",
              "      <td>schwarz/braun</td>\n",
              "    </tr>\n",
              "    <tr>\n",
              "      <th>1</th>\n",
              "      <td>2015</td>\n",
              "      <td>574</td>\n",
              "      <td>60</td>\n",
              "      <td>weiblich</td>\n",
              "      <td>2</td>\n",
              "      <td>Kreis 2</td>\n",
              "      <td>23</td>\n",
              "      <td>Leimbach</td>\n",
              "      <td>Cairn Terrier</td>\n",
              "      <td>2002</td>\n",
              "      <td>12</td>\n",
              "      <td>weiblich</td>\n",
              "      <td>brindle</td>\n",
              "    </tr>\n",
              "    <tr>\n",
              "      <th>2</th>\n",
              "      <td>2015</td>\n",
              "      <td>695</td>\n",
              "      <td>40</td>\n",
              "      <td>männlich</td>\n",
              "      <td>6</td>\n",
              "      <td>Kreis 6</td>\n",
              "      <td>63</td>\n",
              "      <td>Oberstrass</td>\n",
              "      <td>Labrador Retriever</td>\n",
              "      <td>2012</td>\n",
              "      <td>2</td>\n",
              "      <td>weiblich</td>\n",
              "      <td>braun</td>\n",
              "    </tr>\n",
              "    <tr>\n",
              "      <th>3</th>\n",
              "      <td>2015</td>\n",
              "      <td>893</td>\n",
              "      <td>60</td>\n",
              "      <td>weiblich</td>\n",
              "      <td>7</td>\n",
              "      <td>Kreis 7</td>\n",
              "      <td>71</td>\n",
              "      <td>Fluntern</td>\n",
              "      <td>Mittelschnauzer</td>\n",
              "      <td>2010</td>\n",
              "      <td>4</td>\n",
              "      <td>weiblich</td>\n",
              "      <td>schwarz</td>\n",
              "    </tr>\n",
              "    <tr>\n",
              "      <th>4</th>\n",
              "      <td>2015</td>\n",
              "      <td>1177</td>\n",
              "      <td>50</td>\n",
              "      <td>männlich</td>\n",
              "      <td>10</td>\n",
              "      <td>Kreis 10</td>\n",
              "      <td>102</td>\n",
              "      <td>Wipkingen</td>\n",
              "      <td>Shih Tzu</td>\n",
              "      <td>2011</td>\n",
              "      <td>3</td>\n",
              "      <td>männlich</td>\n",
              "      <td>schwarz/weiss</td>\n",
              "    </tr>\n",
              "    <tr>\n",
              "      <th>...</th>\n",
              "      <td>...</td>\n",
              "      <td>...</td>\n",
              "      <td>...</td>\n",
              "      <td>...</td>\n",
              "      <td>...</td>\n",
              "      <td>...</td>\n",
              "      <td>...</td>\n",
              "      <td>...</td>\n",
              "      <td>...</td>\n",
              "      <td>...</td>\n",
              "      <td>...</td>\n",
              "      <td>...</td>\n",
              "      <td>...</td>\n",
              "    </tr>\n",
              "    <tr>\n",
              "      <th>61467</th>\n",
              "      <td>2022</td>\n",
              "      <td>160175</td>\n",
              "      <td>20</td>\n",
              "      <td>weiblich</td>\n",
              "      <td>8</td>\n",
              "      <td>Kreis 8</td>\n",
              "      <td>82</td>\n",
              "      <td>Mühlebach</td>\n",
              "      <td>Chihuahua</td>\n",
              "      <td>2021</td>\n",
              "      <td>0</td>\n",
              "      <td>weiblich</td>\n",
              "      <td>hellbraun</td>\n",
              "    </tr>\n",
              "    <tr>\n",
              "      <th>61468</th>\n",
              "      <td>2022</td>\n",
              "      <td>160176</td>\n",
              "      <td>20</td>\n",
              "      <td>weiblich</td>\n",
              "      <td>8</td>\n",
              "      <td>Kreis 8</td>\n",
              "      <td>83</td>\n",
              "      <td>Weinegg</td>\n",
              "      <td>Zwergpudel</td>\n",
              "      <td>2012</td>\n",
              "      <td>9</td>\n",
              "      <td>weiblich</td>\n",
              "      <td>apricot</td>\n",
              "    </tr>\n",
              "    <tr>\n",
              "      <th>61469</th>\n",
              "      <td>2022</td>\n",
              "      <td>160178</td>\n",
              "      <td>50</td>\n",
              "      <td>männlich</td>\n",
              "      <td>11</td>\n",
              "      <td>Kreis 11</td>\n",
              "      <td>111</td>\n",
              "      <td>Affoltern</td>\n",
              "      <td>Shetland Sheepdog</td>\n",
              "      <td>2022</td>\n",
              "      <td>0</td>\n",
              "      <td>weiblich</td>\n",
              "      <td>tricolor</td>\n",
              "    </tr>\n",
              "    <tr>\n",
              "      <th>61470</th>\n",
              "      <td>2022</td>\n",
              "      <td>160182</td>\n",
              "      <td>30</td>\n",
              "      <td>weiblich</td>\n",
              "      <td>7</td>\n",
              "      <td>Kreis 7</td>\n",
              "      <td>72</td>\n",
              "      <td>Hottingen</td>\n",
              "      <td>Miniature American Shepard</td>\n",
              "      <td>2022</td>\n",
              "      <td>0</td>\n",
              "      <td>männlich</td>\n",
              "      <td>braun</td>\n",
              "    </tr>\n",
              "    <tr>\n",
              "      <th>61471</th>\n",
              "      <td>2022</td>\n",
              "      <td>160183</td>\n",
              "      <td>20</td>\n",
              "      <td>weiblich</td>\n",
              "      <td>11</td>\n",
              "      <td>Kreis 11</td>\n",
              "      <td>119</td>\n",
              "      <td>Seebach</td>\n",
              "      <td>Dachshund</td>\n",
              "      <td>2021</td>\n",
              "      <td>0</td>\n",
              "      <td>männlich</td>\n",
              "      <td>braun/rot</td>\n",
              "    </tr>\n",
              "  </tbody>\n",
              "</table>\n",
              "<p>61472 rows × 13 columns</p>\n",
              "</div>\n",
              "      <button class=\"colab-df-convert\" onclick=\"convertToInteractive('df-7d1ef807-5e05-4c71-864d-92731cd158fa')\"\n",
              "              title=\"Convert this dataframe to an interactive table.\"\n",
              "              style=\"display:none;\">\n",
              "        \n",
              "  <svg xmlns=\"http://www.w3.org/2000/svg\" height=\"24px\"viewBox=\"0 0 24 24\"\n",
              "       width=\"24px\">\n",
              "    <path d=\"M0 0h24v24H0V0z\" fill=\"none\"/>\n",
              "    <path d=\"M18.56 5.44l.94 2.06.94-2.06 2.06-.94-2.06-.94-.94-2.06-.94 2.06-2.06.94zm-11 1L8.5 8.5l.94-2.06 2.06-.94-2.06-.94L8.5 2.5l-.94 2.06-2.06.94zm10 10l.94 2.06.94-2.06 2.06-.94-2.06-.94-.94-2.06-.94 2.06-2.06.94z\"/><path d=\"M17.41 7.96l-1.37-1.37c-.4-.4-.92-.59-1.43-.59-.52 0-1.04.2-1.43.59L10.3 9.45l-7.72 7.72c-.78.78-.78 2.05 0 2.83L4 21.41c.39.39.9.59 1.41.59.51 0 1.02-.2 1.41-.59l7.78-7.78 2.81-2.81c.8-.78.8-2.07 0-2.86zM5.41 20L4 18.59l7.72-7.72 1.47 1.35L5.41 20z\"/>\n",
              "  </svg>\n",
              "      </button>\n",
              "      \n",
              "  <style>\n",
              "    .colab-df-container {\n",
              "      display:flex;\n",
              "      flex-wrap:wrap;\n",
              "      gap: 12px;\n",
              "    }\n",
              "\n",
              "    .colab-df-convert {\n",
              "      background-color: #E8F0FE;\n",
              "      border: none;\n",
              "      border-radius: 50%;\n",
              "      cursor: pointer;\n",
              "      display: none;\n",
              "      fill: #1967D2;\n",
              "      height: 32px;\n",
              "      padding: 0 0 0 0;\n",
              "      width: 32px;\n",
              "    }\n",
              "\n",
              "    .colab-df-convert:hover {\n",
              "      background-color: #E2EBFA;\n",
              "      box-shadow: 0px 1px 2px rgba(60, 64, 67, 0.3), 0px 1px 3px 1px rgba(60, 64, 67, 0.15);\n",
              "      fill: #174EA6;\n",
              "    }\n",
              "\n",
              "    [theme=dark] .colab-df-convert {\n",
              "      background-color: #3B4455;\n",
              "      fill: #D2E3FC;\n",
              "    }\n",
              "\n",
              "    [theme=dark] .colab-df-convert:hover {\n",
              "      background-color: #434B5C;\n",
              "      box-shadow: 0px 1px 3px 1px rgba(0, 0, 0, 0.15);\n",
              "      filter: drop-shadow(0px 1px 2px rgba(0, 0, 0, 0.3));\n",
              "      fill: #FFFFFF;\n",
              "    }\n",
              "  </style>\n",
              "\n",
              "      <script>\n",
              "        const buttonEl =\n",
              "          document.querySelector('#df-7d1ef807-5e05-4c71-864d-92731cd158fa button.colab-df-convert');\n",
              "        buttonEl.style.display =\n",
              "          google.colab.kernel.accessAllowed ? 'block' : 'none';\n",
              "\n",
              "        async function convertToInteractive(key) {\n",
              "          const element = document.querySelector('#df-7d1ef807-5e05-4c71-864d-92731cd158fa');\n",
              "          const dataTable =\n",
              "            await google.colab.kernel.invokeFunction('convertToInteractive',\n",
              "                                                     [key], {});\n",
              "          if (!dataTable) return;\n",
              "\n",
              "          const docLinkHtml = 'Like what you see? Visit the ' +\n",
              "            '<a target=\"_blank\" href=https://colab.research.google.com/notebooks/data_table.ipynb>data table notebook</a>'\n",
              "            + ' to learn more about interactive tables.';\n",
              "          element.innerHTML = '';\n",
              "          dataTable['output_type'] = 'display_data';\n",
              "          await google.colab.output.renderOutput(dataTable, element);\n",
              "          const docLink = document.createElement('div');\n",
              "          docLink.innerHTML = docLinkHtml;\n",
              "          element.appendChild(docLink);\n",
              "        }\n",
              "      </script>\n",
              "    </div>\n",
              "  </div>\n",
              "  "
            ],
            "text/plain": [
              "       StichtagDatJahr  HalterId  AlterV10Cd   SexLang  KreisCd KreisLang  \\\n",
              "0                 2015       126          60  männlich        9   Kreis 9   \n",
              "1                 2015       574          60  weiblich        2   Kreis 2   \n",
              "2                 2015       695          40  männlich        6   Kreis 6   \n",
              "3                 2015       893          60  weiblich        7   Kreis 7   \n",
              "4                 2015      1177          50  männlich       10  Kreis 10   \n",
              "...                ...       ...         ...       ...      ...       ...   \n",
              "61467             2022    160175          20  weiblich        8   Kreis 8   \n",
              "61468             2022    160176          20  weiblich        8   Kreis 8   \n",
              "61469             2022    160178          50  männlich       11  Kreis 11   \n",
              "61470             2022    160182          30  weiblich        7   Kreis 7   \n",
              "61471             2022    160183          20  weiblich       11  Kreis 11   \n",
              "\n",
              "       QuarCd    QuarLang                  Rasse1Text  GebDatHundJahr  \\\n",
              "0          92  Altstetten               Welsh Terrier            2011   \n",
              "1          23    Leimbach               Cairn Terrier            2002   \n",
              "2          63  Oberstrass          Labrador Retriever            2012   \n",
              "3          71    Fluntern             Mittelschnauzer            2010   \n",
              "4         102   Wipkingen                    Shih Tzu            2011   \n",
              "...       ...         ...                         ...             ...   \n",
              "61467      82   Mühlebach                   Chihuahua            2021   \n",
              "61468      83     Weinegg                  Zwergpudel            2012   \n",
              "61469     111   Affoltern           Shetland Sheepdog            2022   \n",
              "61470      72   Hottingen  Miniature American Shepard            2022   \n",
              "61471     119     Seebach                   Dachshund            2021   \n",
              "\n",
              "       AlterVHundCd SexHundLang HundefarbeText  \n",
              "0                 3    weiblich  schwarz/braun  \n",
              "1                12    weiblich        brindle  \n",
              "2                 2    weiblich          braun  \n",
              "3                 4    weiblich        schwarz  \n",
              "4                 3    männlich  schwarz/weiss  \n",
              "...             ...         ...            ...  \n",
              "61467             0    weiblich      hellbraun  \n",
              "61468             9    weiblich        apricot  \n",
              "61469             0    weiblich       tricolor  \n",
              "61470             0    männlich          braun  \n",
              "61471             0    männlich      braun/rot  \n",
              "\n",
              "[61472 rows x 13 columns]"
            ]
          },
          "execution_count": 1,
          "metadata": {},
          "output_type": "execute_result"
        }
      ],
      "source": [
        "import pandas as pd\n",
        "\n",
        "dogs = pd.read_csv(\"KUL100OD1001.csv\")\n",
        "\n",
        "dogs = dogs[['StichtagDatJahr', 'HalterId', 'AlterV10Cd', 'SexLang', 'KreisCd', 'KreisLang', 'QuarCd', 'QuarLang', 'Rasse1Text',\n",
        "       'GebDatHundJahr', 'AlterVHundCd', 'SexHundLang', 'HundefarbeText']]\n",
        "\n",
        "dogs"
      ]
    },
    {
      "cell_type": "markdown",
      "metadata": {
        "id": "HZegMcbxTRh6"
      },
      "source": [
        "## Ersetzen von falschen Werten\n",
        "\n",
        "In der explorativen Analyse haben wir herausgefunden, dass die Rasse des Hundes manchmal als Unbekannt angegeben ist und das Alter des Hundes in acht Fällen falsch ist.\n",
        "\n",
        "Für kategorielle Datenreihen wie die Rasse können wir, müssen wir den Eintrag nicht ersetzen: Wir würden unsere Auswertung nicht verfälschen wenn wir den Eintrag \"Unbekannt\" in `Rasse1Text` beibehalten.\n",
        "\n",
        "Tatsächlich würden wir ein wenig an Informationen verlieren, weil wir so wissen, dass 7856 Hundehalter\\*innen die Rasse ihres Hundes nicht kennen. Deshalb belassen wir den Eintrag `Unbekannt`.\n",
        "\n",
        "Anders sieht es beim Alter der Hunde aus. Wenn wir uns etwa für das durchschnittliche Alter des Hundes interessieren, dann verfälschen die acht Hunde mit dem Alter 999 unser Ergebnis.\n",
        "\n",
        "Mit den acht Einträgen erhalten wir als durchschnittliches Alter über alle Jahre hinweg (was kein sinnvolles Maß ist):"
      ]
    },
    {
      "cell_type": "code",
      "execution_count": 2,
      "metadata": {
        "colab": {
          "base_uri": "https://localhost:8080/"
        },
        "id": "UnnY2krtVzQg",
        "outputId": "ec79744c-b549-4216-fab9-b27e9feb91b6"
      },
      "outputs": [
        {
          "data": {
            "text/plain": [
              "6.080052706923477"
            ]
          },
          "execution_count": 2,
          "metadata": {},
          "output_type": "execute_result"
        }
      ],
      "source": [
        "dogs[\"AlterVHundCd\"].mean()"
      ]
    },
    {
      "cell_type": "markdown",
      "metadata": {
        "id": "SfNgCL33TwwF"
      },
      "source": [
        "Um die Werte die wir als fehlerhafte Eingaben identifiziert haben gegen `pd.NA` auszutauschen, das etwa von der `.mean()`-Methode einfach ignoriert wird können wir die `.replace()`-Methode verwenden.\n",
        "\n",
        "Sie ersetzt in einem Dataframe oder einer Series einen Wert durch einen anderen. \n"
      ]
    },
    {
      "cell_type": "code",
      "execution_count": 3,
      "metadata": {
        "id": "s4KudSEWT2va"
      },
      "outputs": [],
      "source": [
        "dogs[\"AlterVHundCd\"] = dogs[\"AlterVHundCd\"].replace(999, pd.NA)"
      ]
    },
    {
      "cell_type": "markdown",
      "metadata": {
        "id": "RaWxjQwEYoXO"
      },
      "source": [
        "Wenn wir nun wieder alle Werte in der Spalte ansehen, sehen wir dass die falschen Einträge verschwunden sind:"
      ]
    },
    {
      "cell_type": "code",
      "execution_count": 4,
      "metadata": {
        "colab": {
          "base_uri": "https://localhost:8080/"
        },
        "id": "Ju0o2iIFYx8Q",
        "outputId": "56161c21-f94c-495c-d87a-8f3218d8a054"
      },
      "outputs": [
        {
          "data": {
            "text/plain": [
              "1     5463\n",
              "0     5312\n",
              "2     5151\n",
              "3     5070\n",
              "4     4905\n",
              "5     4743\n",
              "6     4540\n",
              "7     4387\n",
              "8     4139\n",
              "9     3871\n",
              "10    3566\n",
              "11    3156\n",
              "12    2599\n",
              "13    1991\n",
              "14    1313\n",
              "15     731\n",
              "16     334\n",
              "17     122\n",
              "18      41\n",
              "19      12\n",
              "20       9\n",
              "21       6\n",
              "22       2\n",
              "23       1\n",
              "Name: AlterVHundCd, dtype: int64"
            ]
          },
          "execution_count": 4,
          "metadata": {},
          "output_type": "execute_result"
        }
      ],
      "source": [
        "dogs[\"AlterVHundCd\"].value_counts()"
      ]
    },
    {
      "cell_type": "markdown",
      "metadata": {
        "id": "P8VhenvtbJqq"
      },
      "source": [
        "Das gleiche machen wir noch einmal mit dem Eintrag für das Geburtsjahr:"
      ]
    },
    {
      "cell_type": "code",
      "execution_count": 5,
      "metadata": {
        "id": "2DdhHtcUbQ0h"
      },
      "outputs": [],
      "source": [
        "dogs[\"GebDatHundJahr\"] = dogs[\"GebDatHundJahr\"].replace(9999, pd.NA)"
      ]
    },
    {
      "cell_type": "markdown",
      "metadata": {
        "id": "ijs4UfJBY7QI"
      },
      "source": [
        "Und auch das durchschnittliche Alter ist nun ein anderes:"
      ]
    },
    {
      "cell_type": "code",
      "execution_count": 6,
      "metadata": {
        "colab": {
          "base_uri": "https://localhost:8080/"
        },
        "id": "g4xbEcXJY-8e",
        "outputId": "3c7fcfdb-2e56-4ce2-c5bb-b1a23f346c2d"
      },
      "outputs": [
        {
          "data": {
            "text/plain": [
              "5.950816738253287"
            ]
          },
          "execution_count": 6,
          "metadata": {},
          "output_type": "execute_result"
        }
      ],
      "source": [
        "dogs[\"AlterVHundCd\"].mean()"
      ]
    },
    {
      "cell_type": "markdown",
      "metadata": {
        "id": "s8RR4ZxqZCsp"
      },
      "source": [
        "Manchmal macht es Sinn, fehlerhafte Einträge zu löschen oder zu versuchen, die fehlenden Informationen zu ersetzen.\n",
        "\n",
        "Es könnte etwa sein, dass das Alter des Hundes nur einmal vergessen wurde und in einem anderen Jahr eingetragen wurde.\n",
        "\n",
        "Dafür werfen wir einen Blick auf alle fehlerhaften Einträge, die wir nun einfach mit der `.isna()`-Methode finden können:"
      ]
    },
    {
      "cell_type": "code",
      "execution_count": 7,
      "metadata": {
        "colab": {
          "base_uri": "https://localhost:8080/",
          "height": 416
        },
        "id": "XF9wudKyZFYY",
        "outputId": "751b9b92-4f23-42b8-bd4d-83a191a065f1"
      },
      "outputs": [
        {
          "data": {
            "text/html": [
              "\n",
              "  <div id=\"df-f6516820-d62c-4c87-a4f7-0ca70389735d\">\n",
              "    <div class=\"colab-df-container\">\n",
              "      <div>\n",
              "<style scoped>\n",
              "    .dataframe tbody tr th:only-of-type {\n",
              "        vertical-align: middle;\n",
              "    }\n",
              "\n",
              "    .dataframe tbody tr th {\n",
              "        vertical-align: top;\n",
              "    }\n",
              "\n",
              "    .dataframe thead th {\n",
              "        text-align: right;\n",
              "    }\n",
              "</style>\n",
              "<table border=\"1\" class=\"dataframe\">\n",
              "  <thead>\n",
              "    <tr style=\"text-align: right;\">\n",
              "      <th></th>\n",
              "      <th>StichtagDatJahr</th>\n",
              "      <th>HalterId</th>\n",
              "      <th>AlterV10Cd</th>\n",
              "      <th>SexLang</th>\n",
              "      <th>KreisCd</th>\n",
              "      <th>KreisLang</th>\n",
              "      <th>QuarCd</th>\n",
              "      <th>QuarLang</th>\n",
              "      <th>Rasse1Text</th>\n",
              "      <th>GebDatHundJahr</th>\n",
              "      <th>AlterVHundCd</th>\n",
              "      <th>SexHundLang</th>\n",
              "      <th>HundefarbeText</th>\n",
              "    </tr>\n",
              "  </thead>\n",
              "  <tbody>\n",
              "    <tr>\n",
              "      <th>2632</th>\n",
              "      <td>2015</td>\n",
              "      <td>91205</td>\n",
              "      <td>40</td>\n",
              "      <td>männlich</td>\n",
              "      <td>6</td>\n",
              "      <td>Kreis 6</td>\n",
              "      <td>61</td>\n",
              "      <td>Unterstrass</td>\n",
              "      <td>Zwergschnauzer</td>\n",
              "      <td>&lt;NA&gt;</td>\n",
              "      <td>&lt;NA&gt;</td>\n",
              "      <td>weiblich</td>\n",
              "      <td>schwarz/silber</td>\n",
              "    </tr>\n",
              "    <tr>\n",
              "      <th>4525</th>\n",
              "      <td>2015</td>\n",
              "      <td>111954</td>\n",
              "      <td>50</td>\n",
              "      <td>weiblich</td>\n",
              "      <td>3</td>\n",
              "      <td>Kreis 3</td>\n",
              "      <td>34</td>\n",
              "      <td>Sihlfeld</td>\n",
              "      <td>Parson Russell Terrier</td>\n",
              "      <td>&lt;NA&gt;</td>\n",
              "      <td>&lt;NA&gt;</td>\n",
              "      <td>männlich</td>\n",
              "      <td>tricolor</td>\n",
              "    </tr>\n",
              "    <tr>\n",
              "      <th>5584</th>\n",
              "      <td>2015</td>\n",
              "      <td>121585</td>\n",
              "      <td>40</td>\n",
              "      <td>männlich</td>\n",
              "      <td>7</td>\n",
              "      <td>Kreis 7</td>\n",
              "      <td>74</td>\n",
              "      <td>Witikon</td>\n",
              "      <td>Malteser</td>\n",
              "      <td>&lt;NA&gt;</td>\n",
              "      <td>&lt;NA&gt;</td>\n",
              "      <td>männlich</td>\n",
              "      <td>weiss</td>\n",
              "    </tr>\n",
              "    <tr>\n",
              "      <th>9429</th>\n",
              "      <td>2016</td>\n",
              "      <td>91205</td>\n",
              "      <td>40</td>\n",
              "      <td>männlich</td>\n",
              "      <td>6</td>\n",
              "      <td>Kreis 6</td>\n",
              "      <td>61</td>\n",
              "      <td>Unterstrass</td>\n",
              "      <td>Zwergschnauzer</td>\n",
              "      <td>&lt;NA&gt;</td>\n",
              "      <td>&lt;NA&gt;</td>\n",
              "      <td>weiblich</td>\n",
              "      <td>schwarz/silber</td>\n",
              "    </tr>\n",
              "    <tr>\n",
              "      <th>11247</th>\n",
              "      <td>2016</td>\n",
              "      <td>111954</td>\n",
              "      <td>50</td>\n",
              "      <td>weiblich</td>\n",
              "      <td>3</td>\n",
              "      <td>Kreis 3</td>\n",
              "      <td>34</td>\n",
              "      <td>Sihlfeld</td>\n",
              "      <td>Parson Russell Terrier</td>\n",
              "      <td>&lt;NA&gt;</td>\n",
              "      <td>&lt;NA&gt;</td>\n",
              "      <td>männlich</td>\n",
              "      <td>tricolor</td>\n",
              "    </tr>\n",
              "    <tr>\n",
              "      <th>16151</th>\n",
              "      <td>2017</td>\n",
              "      <td>91205</td>\n",
              "      <td>40</td>\n",
              "      <td>männlich</td>\n",
              "      <td>6</td>\n",
              "      <td>Kreis 6</td>\n",
              "      <td>61</td>\n",
              "      <td>Unterstrass</td>\n",
              "      <td>Zwergschnauzer</td>\n",
              "      <td>&lt;NA&gt;</td>\n",
              "      <td>&lt;NA&gt;</td>\n",
              "      <td>weiblich</td>\n",
              "      <td>schwarz/silber</td>\n",
              "    </tr>\n",
              "    <tr>\n",
              "      <th>23071</th>\n",
              "      <td>2018</td>\n",
              "      <td>91205</td>\n",
              "      <td>40</td>\n",
              "      <td>männlich</td>\n",
              "      <td>6</td>\n",
              "      <td>Kreis 6</td>\n",
              "      <td>61</td>\n",
              "      <td>Unterstrass</td>\n",
              "      <td>Zwergschnauzer</td>\n",
              "      <td>&lt;NA&gt;</td>\n",
              "      <td>&lt;NA&gt;</td>\n",
              "      <td>weiblich</td>\n",
              "      <td>schwarz/silber</td>\n",
              "    </tr>\n",
              "    <tr>\n",
              "      <th>35341</th>\n",
              "      <td>2019</td>\n",
              "      <td>143717</td>\n",
              "      <td>30</td>\n",
              "      <td>weiblich</td>\n",
              "      <td>12</td>\n",
              "      <td>Kreis 12</td>\n",
              "      <td>122</td>\n",
              "      <td>Schwamendingen-Mitte</td>\n",
              "      <td>Bolonka Zwetna</td>\n",
              "      <td>&lt;NA&gt;</td>\n",
              "      <td>&lt;NA&gt;</td>\n",
              "      <td>männlich</td>\n",
              "      <td>rotbraun</td>\n",
              "    </tr>\n",
              "  </tbody>\n",
              "</table>\n",
              "</div>\n",
              "      <button class=\"colab-df-convert\" onclick=\"convertToInteractive('df-f6516820-d62c-4c87-a4f7-0ca70389735d')\"\n",
              "              title=\"Convert this dataframe to an interactive table.\"\n",
              "              style=\"display:none;\">\n",
              "        \n",
              "  <svg xmlns=\"http://www.w3.org/2000/svg\" height=\"24px\"viewBox=\"0 0 24 24\"\n",
              "       width=\"24px\">\n",
              "    <path d=\"M0 0h24v24H0V0z\" fill=\"none\"/>\n",
              "    <path d=\"M18.56 5.44l.94 2.06.94-2.06 2.06-.94-2.06-.94-.94-2.06-.94 2.06-2.06.94zm-11 1L8.5 8.5l.94-2.06 2.06-.94-2.06-.94L8.5 2.5l-.94 2.06-2.06.94zm10 10l.94 2.06.94-2.06 2.06-.94-2.06-.94-.94-2.06-.94 2.06-2.06.94z\"/><path d=\"M17.41 7.96l-1.37-1.37c-.4-.4-.92-.59-1.43-.59-.52 0-1.04.2-1.43.59L10.3 9.45l-7.72 7.72c-.78.78-.78 2.05 0 2.83L4 21.41c.39.39.9.59 1.41.59.51 0 1.02-.2 1.41-.59l7.78-7.78 2.81-2.81c.8-.78.8-2.07 0-2.86zM5.41 20L4 18.59l7.72-7.72 1.47 1.35L5.41 20z\"/>\n",
              "  </svg>\n",
              "      </button>\n",
              "      \n",
              "  <style>\n",
              "    .colab-df-container {\n",
              "      display:flex;\n",
              "      flex-wrap:wrap;\n",
              "      gap: 12px;\n",
              "    }\n",
              "\n",
              "    .colab-df-convert {\n",
              "      background-color: #E8F0FE;\n",
              "      border: none;\n",
              "      border-radius: 50%;\n",
              "      cursor: pointer;\n",
              "      display: none;\n",
              "      fill: #1967D2;\n",
              "      height: 32px;\n",
              "      padding: 0 0 0 0;\n",
              "      width: 32px;\n",
              "    }\n",
              "\n",
              "    .colab-df-convert:hover {\n",
              "      background-color: #E2EBFA;\n",
              "      box-shadow: 0px 1px 2px rgba(60, 64, 67, 0.3), 0px 1px 3px 1px rgba(60, 64, 67, 0.15);\n",
              "      fill: #174EA6;\n",
              "    }\n",
              "\n",
              "    [theme=dark] .colab-df-convert {\n",
              "      background-color: #3B4455;\n",
              "      fill: #D2E3FC;\n",
              "    }\n",
              "\n",
              "    [theme=dark] .colab-df-convert:hover {\n",
              "      background-color: #434B5C;\n",
              "      box-shadow: 0px 1px 3px 1px rgba(0, 0, 0, 0.15);\n",
              "      filter: drop-shadow(0px 1px 2px rgba(0, 0, 0, 0.3));\n",
              "      fill: #FFFFFF;\n",
              "    }\n",
              "  </style>\n",
              "\n",
              "      <script>\n",
              "        const buttonEl =\n",
              "          document.querySelector('#df-f6516820-d62c-4c87-a4f7-0ca70389735d button.colab-df-convert');\n",
              "        buttonEl.style.display =\n",
              "          google.colab.kernel.accessAllowed ? 'block' : 'none';\n",
              "\n",
              "        async function convertToInteractive(key) {\n",
              "          const element = document.querySelector('#df-f6516820-d62c-4c87-a4f7-0ca70389735d');\n",
              "          const dataTable =\n",
              "            await google.colab.kernel.invokeFunction('convertToInteractive',\n",
              "                                                     [key], {});\n",
              "          if (!dataTable) return;\n",
              "\n",
              "          const docLinkHtml = 'Like what you see? Visit the ' +\n",
              "            '<a target=\"_blank\" href=https://colab.research.google.com/notebooks/data_table.ipynb>data table notebook</a>'\n",
              "            + ' to learn more about interactive tables.';\n",
              "          element.innerHTML = '';\n",
              "          dataTable['output_type'] = 'display_data';\n",
              "          await google.colab.output.renderOutput(dataTable, element);\n",
              "          const docLink = document.createElement('div');\n",
              "          docLink.innerHTML = docLinkHtml;\n",
              "          element.appendChild(docLink);\n",
              "        }\n",
              "      </script>\n",
              "    </div>\n",
              "  </div>\n",
              "  "
            ],
            "text/plain": [
              "       StichtagDatJahr  HalterId  AlterV10Cd   SexLang  KreisCd KreisLang  \\\n",
              "2632              2015     91205          40  männlich        6   Kreis 6   \n",
              "4525              2015    111954          50  weiblich        3   Kreis 3   \n",
              "5584              2015    121585          40  männlich        7   Kreis 7   \n",
              "9429              2016     91205          40  männlich        6   Kreis 6   \n",
              "11247             2016    111954          50  weiblich        3   Kreis 3   \n",
              "16151             2017     91205          40  männlich        6   Kreis 6   \n",
              "23071             2018     91205          40  männlich        6   Kreis 6   \n",
              "35341             2019    143717          30  weiblich       12  Kreis 12   \n",
              "\n",
              "       QuarCd              QuarLang              Rasse1Text GebDatHundJahr  \\\n",
              "2632       61           Unterstrass          Zwergschnauzer           <NA>   \n",
              "4525       34              Sihlfeld  Parson Russell Terrier           <NA>   \n",
              "5584       74               Witikon                Malteser           <NA>   \n",
              "9429       61           Unterstrass          Zwergschnauzer           <NA>   \n",
              "11247      34              Sihlfeld  Parson Russell Terrier           <NA>   \n",
              "16151      61           Unterstrass          Zwergschnauzer           <NA>   \n",
              "23071      61           Unterstrass          Zwergschnauzer           <NA>   \n",
              "35341     122  Schwamendingen-Mitte          Bolonka Zwetna           <NA>   \n",
              "\n",
              "      AlterVHundCd SexHundLang  HundefarbeText  \n",
              "2632          <NA>    weiblich  schwarz/silber  \n",
              "4525          <NA>    männlich        tricolor  \n",
              "5584          <NA>    männlich           weiss  \n",
              "9429          <NA>    weiblich  schwarz/silber  \n",
              "11247         <NA>    männlich        tricolor  \n",
              "16151         <NA>    weiblich  schwarz/silber  \n",
              "23071         <NA>    weiblich  schwarz/silber  \n",
              "35341         <NA>    männlich        rotbraun  "
            ]
          },
          "execution_count": 7,
          "metadata": {},
          "output_type": "execute_result"
        }
      ],
      "source": [
        "dogs[dogs[\"AlterVHundCd\"].isna()]"
      ]
    },
    {
      "cell_type": "markdown",
      "metadata": {
        "id": "p3AYFw7McYaW"
      },
      "source": [
        "Wir sehen, dass es sich nur um vier verschiedene Halter handelt. Wir können sie also einfach alle durchprobieren um zu sehen, ob wir recht haben:"
      ]
    },
    {
      "cell_type": "code",
      "execution_count": 11,
      "metadata": {
        "colab": {
          "base_uri": "https://localhost:8080/",
          "height": 162
        },
        "id": "NuTs_iy7ag-4",
        "outputId": "13e014a2-2746-42d7-881f-ab3676e14226"
      },
      "outputs": [
        {
          "data": {
            "text/html": [
              "\n",
              "  <div id=\"df-ebe64cc8-b85b-4894-863b-147515d0136b\">\n",
              "    <div class=\"colab-df-container\">\n",
              "      <div>\n",
              "<style scoped>\n",
              "    .dataframe tbody tr th:only-of-type {\n",
              "        vertical-align: middle;\n",
              "    }\n",
              "\n",
              "    .dataframe tbody tr th {\n",
              "        vertical-align: top;\n",
              "    }\n",
              "\n",
              "    .dataframe thead th {\n",
              "        text-align: right;\n",
              "    }\n",
              "</style>\n",
              "<table border=\"1\" class=\"dataframe\">\n",
              "  <thead>\n",
              "    <tr style=\"text-align: right;\">\n",
              "      <th></th>\n",
              "      <th>StichtagDatJahr</th>\n",
              "      <th>HalterId</th>\n",
              "      <th>AlterV10Cd</th>\n",
              "      <th>SexLang</th>\n",
              "      <th>KreisCd</th>\n",
              "      <th>KreisLang</th>\n",
              "      <th>QuarCd</th>\n",
              "      <th>QuarLang</th>\n",
              "      <th>Rasse1Text</th>\n",
              "      <th>GebDatHundJahr</th>\n",
              "      <th>AlterVHundCd</th>\n",
              "      <th>SexHundLang</th>\n",
              "      <th>HundefarbeText</th>\n",
              "    </tr>\n",
              "  </thead>\n",
              "  <tbody>\n",
              "    <tr>\n",
              "      <th>35341</th>\n",
              "      <td>2019</td>\n",
              "      <td>143717</td>\n",
              "      <td>30</td>\n",
              "      <td>weiblich</td>\n",
              "      <td>12</td>\n",
              "      <td>Kreis 12</td>\n",
              "      <td>122</td>\n",
              "      <td>Schwamendingen-Mitte</td>\n",
              "      <td>Bolonka Zwetna</td>\n",
              "      <td>&lt;NA&gt;</td>\n",
              "      <td>&lt;NA&gt;</td>\n",
              "      <td>männlich</td>\n",
              "      <td>rotbraun</td>\n",
              "    </tr>\n",
              "  </tbody>\n",
              "</table>\n",
              "</div>\n",
              "      <button class=\"colab-df-convert\" onclick=\"convertToInteractive('df-ebe64cc8-b85b-4894-863b-147515d0136b')\"\n",
              "              title=\"Convert this dataframe to an interactive table.\"\n",
              "              style=\"display:none;\">\n",
              "        \n",
              "  <svg xmlns=\"http://www.w3.org/2000/svg\" height=\"24px\"viewBox=\"0 0 24 24\"\n",
              "       width=\"24px\">\n",
              "    <path d=\"M0 0h24v24H0V0z\" fill=\"none\"/>\n",
              "    <path d=\"M18.56 5.44l.94 2.06.94-2.06 2.06-.94-2.06-.94-.94-2.06-.94 2.06-2.06.94zm-11 1L8.5 8.5l.94-2.06 2.06-.94-2.06-.94L8.5 2.5l-.94 2.06-2.06.94zm10 10l.94 2.06.94-2.06 2.06-.94-2.06-.94-.94-2.06-.94 2.06-2.06.94z\"/><path d=\"M17.41 7.96l-1.37-1.37c-.4-.4-.92-.59-1.43-.59-.52 0-1.04.2-1.43.59L10.3 9.45l-7.72 7.72c-.78.78-.78 2.05 0 2.83L4 21.41c.39.39.9.59 1.41.59.51 0 1.02-.2 1.41-.59l7.78-7.78 2.81-2.81c.8-.78.8-2.07 0-2.86zM5.41 20L4 18.59l7.72-7.72 1.47 1.35L5.41 20z\"/>\n",
              "  </svg>\n",
              "      </button>\n",
              "      \n",
              "  <style>\n",
              "    .colab-df-container {\n",
              "      display:flex;\n",
              "      flex-wrap:wrap;\n",
              "      gap: 12px;\n",
              "    }\n",
              "\n",
              "    .colab-df-convert {\n",
              "      background-color: #E8F0FE;\n",
              "      border: none;\n",
              "      border-radius: 50%;\n",
              "      cursor: pointer;\n",
              "      display: none;\n",
              "      fill: #1967D2;\n",
              "      height: 32px;\n",
              "      padding: 0 0 0 0;\n",
              "      width: 32px;\n",
              "    }\n",
              "\n",
              "    .colab-df-convert:hover {\n",
              "      background-color: #E2EBFA;\n",
              "      box-shadow: 0px 1px 2px rgba(60, 64, 67, 0.3), 0px 1px 3px 1px rgba(60, 64, 67, 0.15);\n",
              "      fill: #174EA6;\n",
              "    }\n",
              "\n",
              "    [theme=dark] .colab-df-convert {\n",
              "      background-color: #3B4455;\n",
              "      fill: #D2E3FC;\n",
              "    }\n",
              "\n",
              "    [theme=dark] .colab-df-convert:hover {\n",
              "      background-color: #434B5C;\n",
              "      box-shadow: 0px 1px 3px 1px rgba(0, 0, 0, 0.15);\n",
              "      filter: drop-shadow(0px 1px 2px rgba(0, 0, 0, 0.3));\n",
              "      fill: #FFFFFF;\n",
              "    }\n",
              "  </style>\n",
              "\n",
              "      <script>\n",
              "        const buttonEl =\n",
              "          document.querySelector('#df-ebe64cc8-b85b-4894-863b-147515d0136b button.colab-df-convert');\n",
              "        buttonEl.style.display =\n",
              "          google.colab.kernel.accessAllowed ? 'block' : 'none';\n",
              "\n",
              "        async function convertToInteractive(key) {\n",
              "          const element = document.querySelector('#df-ebe64cc8-b85b-4894-863b-147515d0136b');\n",
              "          const dataTable =\n",
              "            await google.colab.kernel.invokeFunction('convertToInteractive',\n",
              "                                                     [key], {});\n",
              "          if (!dataTable) return;\n",
              "\n",
              "          const docLinkHtml = 'Like what you see? Visit the ' +\n",
              "            '<a target=\"_blank\" href=https://colab.research.google.com/notebooks/data_table.ipynb>data table notebook</a>'\n",
              "            + ' to learn more about interactive tables.';\n",
              "          element.innerHTML = '';\n",
              "          dataTable['output_type'] = 'display_data';\n",
              "          await google.colab.output.renderOutput(dataTable, element);\n",
              "          const docLink = document.createElement('div');\n",
              "          docLink.innerHTML = docLinkHtml;\n",
              "          element.appendChild(docLink);\n",
              "        }\n",
              "      </script>\n",
              "    </div>\n",
              "  </div>\n",
              "  "
            ],
            "text/plain": [
              "       StichtagDatJahr  HalterId  AlterV10Cd   SexLang  KreisCd KreisLang  \\\n",
              "35341             2019    143717          30  weiblich       12  Kreis 12   \n",
              "\n",
              "       QuarCd              QuarLang      Rasse1Text GebDatHundJahr  \\\n",
              "35341     122  Schwamendingen-Mitte  Bolonka Zwetna           <NA>   \n",
              "\n",
              "      AlterVHundCd SexHundLang HundefarbeText  \n",
              "35341         <NA>    männlich       rotbraun  "
            ]
          },
          "execution_count": 11,
          "metadata": {},
          "output_type": "execute_result"
        }
      ],
      "source": [
        "dogs[dogs[\"HalterId\"] == 143717]"
      ]
    },
    {
      "cell_type": "markdown",
      "metadata": {
        "id": "lNBwm0fScw74"
      },
      "source": [
        "Leider können wir bei keinem der Hunde das wahre Alter herausfinden. \n",
        "\n",
        "Eine andere Option ist es immer noch die fehlerhaften Einträge ganz zu löschen, aber in diesem Fall schränken sie unsere Analyse nicht ein. Deshalb können wir die Einträge beibehalten.\n",
        "\n",
        "Zum Ende können wir die Funktion `.describe()` auf dem Datensatz ausführen. Diese zeigt uns noch einen kleinen Überblick über den Datensatz. "
      ]
    },
    {
      "cell_type": "code",
      "execution_count": 12,
      "metadata": {
        "colab": {
          "base_uri": "https://localhost:8080/",
          "height": 300
        },
        "id": "7wbllOHreg8f",
        "outputId": "7d56c20e-0206-403c-c7ec-6f6a5238189f"
      },
      "outputs": [
        {
          "data": {
            "text/html": [
              "\n",
              "  <div id=\"df-1d0eec32-5876-4f2e-a337-6e4eefe4abb9\">\n",
              "    <div class=\"colab-df-container\">\n",
              "      <div>\n",
              "<style scoped>\n",
              "    .dataframe tbody tr th:only-of-type {\n",
              "        vertical-align: middle;\n",
              "    }\n",
              "\n",
              "    .dataframe tbody tr th {\n",
              "        vertical-align: top;\n",
              "    }\n",
              "\n",
              "    .dataframe thead th {\n",
              "        text-align: right;\n",
              "    }\n",
              "</style>\n",
              "<table border=\"1\" class=\"dataframe\">\n",
              "  <thead>\n",
              "    <tr style=\"text-align: right;\">\n",
              "      <th></th>\n",
              "      <th>StichtagDatJahr</th>\n",
              "      <th>HalterId</th>\n",
              "      <th>AlterV10Cd</th>\n",
              "      <th>KreisCd</th>\n",
              "      <th>QuarCd</th>\n",
              "    </tr>\n",
              "  </thead>\n",
              "  <tbody>\n",
              "    <tr>\n",
              "      <th>count</th>\n",
              "      <td>61472.000000</td>\n",
              "      <td>61472.000000</td>\n",
              "      <td>61472.000000</td>\n",
              "      <td>61472.000000</td>\n",
              "      <td>61472.000000</td>\n",
              "    </tr>\n",
              "    <tr>\n",
              "      <th>mean</th>\n",
              "      <td>2018.710974</td>\n",
              "      <td>113860.603478</td>\n",
              "      <td>48.316551</td>\n",
              "      <td>7.400442</td>\n",
              "      <td>76.575189</td>\n",
              "    </tr>\n",
              "    <tr>\n",
              "      <th>std</th>\n",
              "      <td>2.311283</td>\n",
              "      <td>25806.372691</td>\n",
              "      <td>58.500427</td>\n",
              "      <td>3.312585</td>\n",
              "      <td>33.549868</td>\n",
              "    </tr>\n",
              "    <tr>\n",
              "      <th>min</th>\n",
              "      <td>2015.000000</td>\n",
              "      <td>126.000000</td>\n",
              "      <td>10.000000</td>\n",
              "      <td>1.000000</td>\n",
              "      <td>10.000000</td>\n",
              "    </tr>\n",
              "    <tr>\n",
              "      <th>25%</th>\n",
              "      <td>2017.000000</td>\n",
              "      <td>89705.000000</td>\n",
              "      <td>30.000000</td>\n",
              "      <td>4.000000</td>\n",
              "      <td>44.000000</td>\n",
              "    </tr>\n",
              "    <tr>\n",
              "      <th>50%</th>\n",
              "      <td>2019.000000</td>\n",
              "      <td>116324.000000</td>\n",
              "      <td>40.000000</td>\n",
              "      <td>8.000000</td>\n",
              "      <td>81.000000</td>\n",
              "    </tr>\n",
              "    <tr>\n",
              "      <th>75%</th>\n",
              "      <td>2021.000000</td>\n",
              "      <td>133157.000000</td>\n",
              "      <td>60.000000</td>\n",
              "      <td>10.000000</td>\n",
              "      <td>102.000000</td>\n",
              "    </tr>\n",
              "    <tr>\n",
              "      <th>max</th>\n",
              "      <td>2022.000000</td>\n",
              "      <td>160183.000000</td>\n",
              "      <td>999.000000</td>\n",
              "      <td>99.000000</td>\n",
              "      <td>999.000000</td>\n",
              "    </tr>\n",
              "  </tbody>\n",
              "</table>\n",
              "</div>\n",
              "      <button class=\"colab-df-convert\" onclick=\"convertToInteractive('df-1d0eec32-5876-4f2e-a337-6e4eefe4abb9')\"\n",
              "              title=\"Convert this dataframe to an interactive table.\"\n",
              "              style=\"display:none;\">\n",
              "        \n",
              "  <svg xmlns=\"http://www.w3.org/2000/svg\" height=\"24px\"viewBox=\"0 0 24 24\"\n",
              "       width=\"24px\">\n",
              "    <path d=\"M0 0h24v24H0V0z\" fill=\"none\"/>\n",
              "    <path d=\"M18.56 5.44l.94 2.06.94-2.06 2.06-.94-2.06-.94-.94-2.06-.94 2.06-2.06.94zm-11 1L8.5 8.5l.94-2.06 2.06-.94-2.06-.94L8.5 2.5l-.94 2.06-2.06.94zm10 10l.94 2.06.94-2.06 2.06-.94-2.06-.94-.94-2.06-.94 2.06-2.06.94z\"/><path d=\"M17.41 7.96l-1.37-1.37c-.4-.4-.92-.59-1.43-.59-.52 0-1.04.2-1.43.59L10.3 9.45l-7.72 7.72c-.78.78-.78 2.05 0 2.83L4 21.41c.39.39.9.59 1.41.59.51 0 1.02-.2 1.41-.59l7.78-7.78 2.81-2.81c.8-.78.8-2.07 0-2.86zM5.41 20L4 18.59l7.72-7.72 1.47 1.35L5.41 20z\"/>\n",
              "  </svg>\n",
              "      </button>\n",
              "      \n",
              "  <style>\n",
              "    .colab-df-container {\n",
              "      display:flex;\n",
              "      flex-wrap:wrap;\n",
              "      gap: 12px;\n",
              "    }\n",
              "\n",
              "    .colab-df-convert {\n",
              "      background-color: #E8F0FE;\n",
              "      border: none;\n",
              "      border-radius: 50%;\n",
              "      cursor: pointer;\n",
              "      display: none;\n",
              "      fill: #1967D2;\n",
              "      height: 32px;\n",
              "      padding: 0 0 0 0;\n",
              "      width: 32px;\n",
              "    }\n",
              "\n",
              "    .colab-df-convert:hover {\n",
              "      background-color: #E2EBFA;\n",
              "      box-shadow: 0px 1px 2px rgba(60, 64, 67, 0.3), 0px 1px 3px 1px rgba(60, 64, 67, 0.15);\n",
              "      fill: #174EA6;\n",
              "    }\n",
              "\n",
              "    [theme=dark] .colab-df-convert {\n",
              "      background-color: #3B4455;\n",
              "      fill: #D2E3FC;\n",
              "    }\n",
              "\n",
              "    [theme=dark] .colab-df-convert:hover {\n",
              "      background-color: #434B5C;\n",
              "      box-shadow: 0px 1px 3px 1px rgba(0, 0, 0, 0.15);\n",
              "      filter: drop-shadow(0px 1px 2px rgba(0, 0, 0, 0.3));\n",
              "      fill: #FFFFFF;\n",
              "    }\n",
              "  </style>\n",
              "\n",
              "      <script>\n",
              "        const buttonEl =\n",
              "          document.querySelector('#df-1d0eec32-5876-4f2e-a337-6e4eefe4abb9 button.colab-df-convert');\n",
              "        buttonEl.style.display =\n",
              "          google.colab.kernel.accessAllowed ? 'block' : 'none';\n",
              "\n",
              "        async function convertToInteractive(key) {\n",
              "          const element = document.querySelector('#df-1d0eec32-5876-4f2e-a337-6e4eefe4abb9');\n",
              "          const dataTable =\n",
              "            await google.colab.kernel.invokeFunction('convertToInteractive',\n",
              "                                                     [key], {});\n",
              "          if (!dataTable) return;\n",
              "\n",
              "          const docLinkHtml = 'Like what you see? Visit the ' +\n",
              "            '<a target=\"_blank\" href=https://colab.research.google.com/notebooks/data_table.ipynb>data table notebook</a>'\n",
              "            + ' to learn more about interactive tables.';\n",
              "          element.innerHTML = '';\n",
              "          dataTable['output_type'] = 'display_data';\n",
              "          await google.colab.output.renderOutput(dataTable, element);\n",
              "          const docLink = document.createElement('div');\n",
              "          docLink.innerHTML = docLinkHtml;\n",
              "          element.appendChild(docLink);\n",
              "        }\n",
              "      </script>\n",
              "    </div>\n",
              "  </div>\n",
              "  "
            ],
            "text/plain": [
              "       StichtagDatJahr       HalterId    AlterV10Cd       KreisCd  \\\n",
              "count     61472.000000   61472.000000  61472.000000  61472.000000   \n",
              "mean       2018.710974  113860.603478     48.316551      7.400442   \n",
              "std           2.311283   25806.372691     58.500427      3.312585   \n",
              "min        2015.000000     126.000000     10.000000      1.000000   \n",
              "25%        2017.000000   89705.000000     30.000000      4.000000   \n",
              "50%        2019.000000  116324.000000     40.000000      8.000000   \n",
              "75%        2021.000000  133157.000000     60.000000     10.000000   \n",
              "max        2022.000000  160183.000000    999.000000     99.000000   \n",
              "\n",
              "             QuarCd  \n",
              "count  61472.000000  \n",
              "mean      76.575189  \n",
              "std       33.549868  \n",
              "min       10.000000  \n",
              "25%       44.000000  \n",
              "50%       81.000000  \n",
              "75%      102.000000  \n",
              "max      999.000000  "
            ]
          },
          "execution_count": 12,
          "metadata": {},
          "output_type": "execute_result"
        }
      ],
      "source": [
        "dogs.describe()"
      ]
    },
    {
      "cell_type": "markdown",
      "metadata": {
        "id": "FC3eeYAsfNaB"
      },
      "source": [
        "Die Tabelle ist sehr umfangreich, aber die Werte sind schnell erklärt:\n",
        "\n",
        "- count ist Zahl der Zeilen\n",
        "- mean ist der durchschnittliche Wert in der Spalte\n",
        "- std ist die Standardabweichung. Je kleiner diese Zahl ist, desto weniger verteilt sind sie.\n",
        "- min/max: Der Minimal- bzw. Maximalwert der Spalte\n",
        "- 25%/50%/75%: Oberer Wert des Quartils. Wenn man den Datensatz sortiert und in vier Teile teilt, würde diese Zahl als jeweils letzte in jedem der Viertel stehen.\n",
        "\n",
        "Hier sehen wir, dass auch die Spalten `AlterV10Cd`, `KreisCd` und\t`QuarCd` sehr hohe Maximalwerte haben. Ein Blick auf die [Quartierliste Zürichs](https://de.wikipedia.org/wiki/Stadtteile_der_Stadt_Z%C3%BCrich) zeigt, dass es 12 Kreise und 22 Stadtquartieren bzw. 34 Statistische Quartiere gibt. \n",
        "\n",
        "Wir können also davon ausgehen, dass die 99 bzw. 999 hier auch wieder bedeuten, dass es keine Einträge gibt und können sie durch `pd.NA` ersetzen.\n",
        "\n",
        "Um weniger tippen zu müssen, können wir alle drei Spalten gleichzeitig ändern. Alternativ zu `.replace()` benutzen wir hier die `mask`-Methode. \n",
        "\n",
        "Dafür erstellen wir zuerst eine Maske, die wie gehabt ein DataFrame aus True/False-Werten ist."
      ]
    },
    {
      "cell_type": "code",
      "execution_count": 15,
      "metadata": {
        "id": "AkiXZHoiiJZb"
      },
      "outputs": [],
      "source": [
        "mask = dogs[[\"AlterV10Cd\", \"KreisCd\", \"QuarCd\"]].isin([99, 999])"
      ]
    },
    {
      "cell_type": "markdown",
      "metadata": {
        "id": "OlyBT4Omv_nB"
      },
      "source": [
        "Diese Maske wenden wir dann mit der `mask()`-Methode auf den DataFrame an:"
      ]
    },
    {
      "cell_type": "code",
      "execution_count": 16,
      "metadata": {
        "id": "Spo2Gphkir4P"
      },
      "outputs": [],
      "source": [
        "dogs[[\"AlterV10Cd\", \"KreisCd\", \"QuarCd\"]] = dogs[[\"AlterV10Cd\", \"KreisCd\", \"QuarCd\"]].mask(mask, pd.NA)"
      ]
    },
    {
      "cell_type": "markdown",
      "metadata": {
        "id": "9JqSzvIxwGJi"
      },
      "source": [
        "Die `mask()`-Funktion ist mächtiger als das reine `replace()`, weil wir statt eines Werts, wie hier `pd.NA` auch einen zweiten DataFrame mit den gleichen Abmessungen übergeben können. Mask tauscht dann an allen Stellen an denen die Maske `True` ist die Werte aus dem ersten DataFrame mit den Werten aus dem zweiten DataFrame aus.\n",
        "\n",
        "Wenn wir jetzt nochmals die `describe()`-Methode aufrufen, sehen wir dass die falschen 99 bzw. 999 Werte verschwunden sind."
      ]
    },
    {
      "cell_type": "code",
      "execution_count": 17,
      "metadata": {
        "colab": {
          "base_uri": "https://localhost:8080/",
          "height": 300
        },
        "id": "Avnk1-sMj9jY",
        "outputId": "0f219b42-5a44-483e-df84-a74e0da509ce"
      },
      "outputs": [
        {
          "data": {
            "text/html": [
              "\n",
              "  <div id=\"df-4f6b0625-85ae-45b2-bae9-cbc88504d94b\">\n",
              "    <div class=\"colab-df-container\">\n",
              "      <div>\n",
              "<style scoped>\n",
              "    .dataframe tbody tr th:only-of-type {\n",
              "        vertical-align: middle;\n",
              "    }\n",
              "\n",
              "    .dataframe tbody tr th {\n",
              "        vertical-align: top;\n",
              "    }\n",
              "\n",
              "    .dataframe thead th {\n",
              "        text-align: right;\n",
              "    }\n",
              "</style>\n",
              "<table border=\"1\" class=\"dataframe\">\n",
              "  <thead>\n",
              "    <tr style=\"text-align: right;\">\n",
              "      <th></th>\n",
              "      <th>StichtagDatJahr</th>\n",
              "      <th>HalterId</th>\n",
              "      <th>AlterV10Cd</th>\n",
              "      <th>KreisCd</th>\n",
              "      <th>QuarCd</th>\n",
              "    </tr>\n",
              "  </thead>\n",
              "  <tbody>\n",
              "    <tr>\n",
              "      <th>count</th>\n",
              "      <td>61472.000000</td>\n",
              "      <td>61472.000000</td>\n",
              "      <td>61257.000000</td>\n",
              "      <td>61470.000000</td>\n",
              "      <td>61470.000000</td>\n",
              "    </tr>\n",
              "    <tr>\n",
              "      <th>mean</th>\n",
              "      <td>2018.710974</td>\n",
              "      <td>113860.603478</td>\n",
              "      <td>44.979839</td>\n",
              "      <td>7.397462</td>\n",
              "      <td>76.545177</td>\n",
              "    </tr>\n",
              "    <tr>\n",
              "      <th>std</th>\n",
              "      <td>2.311283</td>\n",
              "      <td>25806.372691</td>\n",
              "      <td>15.842023</td>\n",
              "      <td>3.271172</td>\n",
              "      <td>33.135252</td>\n",
              "    </tr>\n",
              "    <tr>\n",
              "      <th>min</th>\n",
              "      <td>2015.000000</td>\n",
              "      <td>126.000000</td>\n",
              "      <td>10.000000</td>\n",
              "      <td>1.000000</td>\n",
              "      <td>10.000000</td>\n",
              "    </tr>\n",
              "    <tr>\n",
              "      <th>25%</th>\n",
              "      <td>2017.000000</td>\n",
              "      <td>89705.000000</td>\n",
              "      <td>30.000000</td>\n",
              "      <td>4.000000</td>\n",
              "      <td>44.000000</td>\n",
              "    </tr>\n",
              "    <tr>\n",
              "      <th>50%</th>\n",
              "      <td>2019.000000</td>\n",
              "      <td>116324.000000</td>\n",
              "      <td>40.000000</td>\n",
              "      <td>8.000000</td>\n",
              "      <td>81.000000</td>\n",
              "    </tr>\n",
              "    <tr>\n",
              "      <th>75%</th>\n",
              "      <td>2021.000000</td>\n",
              "      <td>133157.000000</td>\n",
              "      <td>60.000000</td>\n",
              "      <td>10.000000</td>\n",
              "      <td>102.000000</td>\n",
              "    </tr>\n",
              "    <tr>\n",
              "      <th>max</th>\n",
              "      <td>2022.000000</td>\n",
              "      <td>160183.000000</td>\n",
              "      <td>90.000000</td>\n",
              "      <td>12.000000</td>\n",
              "      <td>123.000000</td>\n",
              "    </tr>\n",
              "  </tbody>\n",
              "</table>\n",
              "</div>\n",
              "      <button class=\"colab-df-convert\" onclick=\"convertToInteractive('df-4f6b0625-85ae-45b2-bae9-cbc88504d94b')\"\n",
              "              title=\"Convert this dataframe to an interactive table.\"\n",
              "              style=\"display:none;\">\n",
              "        \n",
              "  <svg xmlns=\"http://www.w3.org/2000/svg\" height=\"24px\"viewBox=\"0 0 24 24\"\n",
              "       width=\"24px\">\n",
              "    <path d=\"M0 0h24v24H0V0z\" fill=\"none\"/>\n",
              "    <path d=\"M18.56 5.44l.94 2.06.94-2.06 2.06-.94-2.06-.94-.94-2.06-.94 2.06-2.06.94zm-11 1L8.5 8.5l.94-2.06 2.06-.94-2.06-.94L8.5 2.5l-.94 2.06-2.06.94zm10 10l.94 2.06.94-2.06 2.06-.94-2.06-.94-.94-2.06-.94 2.06-2.06.94z\"/><path d=\"M17.41 7.96l-1.37-1.37c-.4-.4-.92-.59-1.43-.59-.52 0-1.04.2-1.43.59L10.3 9.45l-7.72 7.72c-.78.78-.78 2.05 0 2.83L4 21.41c.39.39.9.59 1.41.59.51 0 1.02-.2 1.41-.59l7.78-7.78 2.81-2.81c.8-.78.8-2.07 0-2.86zM5.41 20L4 18.59l7.72-7.72 1.47 1.35L5.41 20z\"/>\n",
              "  </svg>\n",
              "      </button>\n",
              "      \n",
              "  <style>\n",
              "    .colab-df-container {\n",
              "      display:flex;\n",
              "      flex-wrap:wrap;\n",
              "      gap: 12px;\n",
              "    }\n",
              "\n",
              "    .colab-df-convert {\n",
              "      background-color: #E8F0FE;\n",
              "      border: none;\n",
              "      border-radius: 50%;\n",
              "      cursor: pointer;\n",
              "      display: none;\n",
              "      fill: #1967D2;\n",
              "      height: 32px;\n",
              "      padding: 0 0 0 0;\n",
              "      width: 32px;\n",
              "    }\n",
              "\n",
              "    .colab-df-convert:hover {\n",
              "      background-color: #E2EBFA;\n",
              "      box-shadow: 0px 1px 2px rgba(60, 64, 67, 0.3), 0px 1px 3px 1px rgba(60, 64, 67, 0.15);\n",
              "      fill: #174EA6;\n",
              "    }\n",
              "\n",
              "    [theme=dark] .colab-df-convert {\n",
              "      background-color: #3B4455;\n",
              "      fill: #D2E3FC;\n",
              "    }\n",
              "\n",
              "    [theme=dark] .colab-df-convert:hover {\n",
              "      background-color: #434B5C;\n",
              "      box-shadow: 0px 1px 3px 1px rgba(0, 0, 0, 0.15);\n",
              "      filter: drop-shadow(0px 1px 2px rgba(0, 0, 0, 0.3));\n",
              "      fill: #FFFFFF;\n",
              "    }\n",
              "  </style>\n",
              "\n",
              "      <script>\n",
              "        const buttonEl =\n",
              "          document.querySelector('#df-4f6b0625-85ae-45b2-bae9-cbc88504d94b button.colab-df-convert');\n",
              "        buttonEl.style.display =\n",
              "          google.colab.kernel.accessAllowed ? 'block' : 'none';\n",
              "\n",
              "        async function convertToInteractive(key) {\n",
              "          const element = document.querySelector('#df-4f6b0625-85ae-45b2-bae9-cbc88504d94b');\n",
              "          const dataTable =\n",
              "            await google.colab.kernel.invokeFunction('convertToInteractive',\n",
              "                                                     [key], {});\n",
              "          if (!dataTable) return;\n",
              "\n",
              "          const docLinkHtml = 'Like what you see? Visit the ' +\n",
              "            '<a target=\"_blank\" href=https://colab.research.google.com/notebooks/data_table.ipynb>data table notebook</a>'\n",
              "            + ' to learn more about interactive tables.';\n",
              "          element.innerHTML = '';\n",
              "          dataTable['output_type'] = 'display_data';\n",
              "          await google.colab.output.renderOutput(dataTable, element);\n",
              "          const docLink = document.createElement('div');\n",
              "          docLink.innerHTML = docLinkHtml;\n",
              "          element.appendChild(docLink);\n",
              "        }\n",
              "      </script>\n",
              "    </div>\n",
              "  </div>\n",
              "  "
            ],
            "text/plain": [
              "       StichtagDatJahr       HalterId    AlterV10Cd       KreisCd  \\\n",
              "count     61472.000000   61472.000000  61257.000000  61470.000000   \n",
              "mean       2018.710974  113860.603478     44.979839      7.397462   \n",
              "std           2.311283   25806.372691     15.842023      3.271172   \n",
              "min        2015.000000     126.000000     10.000000      1.000000   \n",
              "25%        2017.000000   89705.000000     30.000000      4.000000   \n",
              "50%        2019.000000  116324.000000     40.000000      8.000000   \n",
              "75%        2021.000000  133157.000000     60.000000     10.000000   \n",
              "max        2022.000000  160183.000000     90.000000     12.000000   \n",
              "\n",
              "             QuarCd  \n",
              "count  61470.000000  \n",
              "mean      76.545177  \n",
              "std       33.135252  \n",
              "min       10.000000  \n",
              "25%       44.000000  \n",
              "50%       81.000000  \n",
              "75%      102.000000  \n",
              "max      123.000000  "
            ]
          },
          "execution_count": 17,
          "metadata": {},
          "output_type": "execute_result"
        }
      ],
      "source": [
        "dogs.describe()"
      ]
    },
    {
      "cell_type": "markdown",
      "metadata": {
        "id": "EkX2scI7y69T"
      },
      "source": [
        "Hier fällt uns etwas weiteres auf: \n",
        "\n",
        "Es sind nicht alle Spalten aus `dogs` in der Auswertung von `.describe()`. Das liegt daran, dass `describe()` Zahlen priorisiert. \n",
        "\n",
        "Der Blick auf die Datenypem mit `dtypes` zeigt, dass die zuvor mit `replace` und `mask` bearbeiteten Spalten aber einen anderen Typ haben. "
      ]
    },
    {
      "cell_type": "code",
      "execution_count": 18,
      "metadata": {
        "colab": {
          "base_uri": "https://localhost:8080/"
        },
        "id": "i-x6mX4141y6",
        "outputId": "8229fdc1-638e-40ea-cb4a-7c8d11a864d6"
      },
      "outputs": [
        {
          "data": {
            "text/plain": [
              "StichtagDatJahr      int64\n",
              "HalterId             int64\n",
              "AlterV10Cd         float64\n",
              "SexLang             object\n",
              "KreisCd            float64\n",
              "KreisLang           object\n",
              "QuarCd             float64\n",
              "QuarLang            object\n",
              "Rasse1Text          object\n",
              "GebDatHundJahr      object\n",
              "AlterVHundCd        object\n",
              "SexHundLang         object\n",
              "HundefarbeText      object\n",
              "dtype: object"
            ]
          },
          "execution_count": 18,
          "metadata": {},
          "output_type": "execute_result"
        }
      ],
      "source": [
        "dogs.dtypes"
      ]
    },
    {
      "cell_type": "markdown",
      "metadata": {
        "id": "x4unWeeP7nK2"
      },
      "source": [
        "Deshalb ändern wir den Datentyp der Spalten mit der `.astype()`-Methode."
      ]
    },
    {
      "cell_type": "code",
      "execution_count": 20,
      "metadata": {
        "id": "re0NIcJ-4_2L"
      },
      "outputs": [],
      "source": [
        "dogs[[\"AlterVHundCd\", \"GebDatHundJahr\", \"QuarCd\", \"KreisCd\", \"AlterV10Cd\"]] = dogs[[\"AlterVHundCd\", \"GebDatHundJahr\", \"QuarCd\", \"KreisCd\", \"AlterV10Cd\"]].astype('Int64')"
      ]
    },
    {
      "cell_type": "markdown",
      "metadata": {
        "id": "B4rnimU97w6c"
      },
      "source": [
        "Und jetzt tauchen alle numerischen Spalten wieder in der Ausgabe von `describe()` auf:"
      ]
    },
    {
      "cell_type": "code",
      "execution_count": 21,
      "metadata": {
        "colab": {
          "base_uri": "https://localhost:8080/",
          "height": 300
        },
        "id": "897_Oy4N5xF0",
        "outputId": "32f10ada-a4bf-45c8-cc34-804a2947aa3e"
      },
      "outputs": [
        {
          "data": {
            "text/html": [
              "\n",
              "  <div id=\"df-99e87de7-c0ae-4a29-8fe3-085fcb724d2d\">\n",
              "    <div class=\"colab-df-container\">\n",
              "      <div>\n",
              "<style scoped>\n",
              "    .dataframe tbody tr th:only-of-type {\n",
              "        vertical-align: middle;\n",
              "    }\n",
              "\n",
              "    .dataframe tbody tr th {\n",
              "        vertical-align: top;\n",
              "    }\n",
              "\n",
              "    .dataframe thead th {\n",
              "        text-align: right;\n",
              "    }\n",
              "</style>\n",
              "<table border=\"1\" class=\"dataframe\">\n",
              "  <thead>\n",
              "    <tr style=\"text-align: right;\">\n",
              "      <th></th>\n",
              "      <th>StichtagDatJahr</th>\n",
              "      <th>HalterId</th>\n",
              "      <th>AlterV10Cd</th>\n",
              "      <th>KreisCd</th>\n",
              "      <th>QuarCd</th>\n",
              "      <th>GebDatHundJahr</th>\n",
              "      <th>AlterVHundCd</th>\n",
              "    </tr>\n",
              "  </thead>\n",
              "  <tbody>\n",
              "    <tr>\n",
              "      <th>count</th>\n",
              "      <td>61472.000000</td>\n",
              "      <td>61472.000000</td>\n",
              "      <td>61257.000000</td>\n",
              "      <td>61470.000000</td>\n",
              "      <td>61470.000000</td>\n",
              "      <td>61464.000000</td>\n",
              "      <td>61464.000000</td>\n",
              "    </tr>\n",
              "    <tr>\n",
              "      <th>mean</th>\n",
              "      <td>2018.710974</td>\n",
              "      <td>113860.603478</td>\n",
              "      <td>44.979839</td>\n",
              "      <td>7.397462</td>\n",
              "      <td>76.545177</td>\n",
              "      <td>2011.763829</td>\n",
              "      <td>5.950817</td>\n",
              "    </tr>\n",
              "    <tr>\n",
              "      <th>std</th>\n",
              "      <td>2.311283</td>\n",
              "      <td>25806.372691</td>\n",
              "      <td>15.842023</td>\n",
              "      <td>3.271172</td>\n",
              "      <td>33.135252</td>\n",
              "      <td>4.812745</td>\n",
              "      <td>4.164937</td>\n",
              "    </tr>\n",
              "    <tr>\n",
              "      <th>min</th>\n",
              "      <td>2015.000000</td>\n",
              "      <td>126.000000</td>\n",
              "      <td>10.000000</td>\n",
              "      <td>1.000000</td>\n",
              "      <td>10.000000</td>\n",
              "      <td>1994.000000</td>\n",
              "      <td>0.000000</td>\n",
              "    </tr>\n",
              "    <tr>\n",
              "      <th>25%</th>\n",
              "      <td>2017.000000</td>\n",
              "      <td>89705.000000</td>\n",
              "      <td>30.000000</td>\n",
              "      <td>4.000000</td>\n",
              "      <td>44.000000</td>\n",
              "      <td>2008.000000</td>\n",
              "      <td>2.000000</td>\n",
              "    </tr>\n",
              "    <tr>\n",
              "      <th>50%</th>\n",
              "      <td>2019.000000</td>\n",
              "      <td>116324.000000</td>\n",
              "      <td>40.000000</td>\n",
              "      <td>8.000000</td>\n",
              "      <td>81.000000</td>\n",
              "      <td>2012.000000</td>\n",
              "      <td>6.000000</td>\n",
              "    </tr>\n",
              "    <tr>\n",
              "      <th>75%</th>\n",
              "      <td>2021.000000</td>\n",
              "      <td>133157.000000</td>\n",
              "      <td>60.000000</td>\n",
              "      <td>10.000000</td>\n",
              "      <td>102.000000</td>\n",
              "      <td>2015.000000</td>\n",
              "      <td>9.000000</td>\n",
              "    </tr>\n",
              "    <tr>\n",
              "      <th>max</th>\n",
              "      <td>2022.000000</td>\n",
              "      <td>160183.000000</td>\n",
              "      <td>90.000000</td>\n",
              "      <td>12.000000</td>\n",
              "      <td>123.000000</td>\n",
              "      <td>2022.000000</td>\n",
              "      <td>23.000000</td>\n",
              "    </tr>\n",
              "  </tbody>\n",
              "</table>\n",
              "</div>\n",
              "      <button class=\"colab-df-convert\" onclick=\"convertToInteractive('df-99e87de7-c0ae-4a29-8fe3-085fcb724d2d')\"\n",
              "              title=\"Convert this dataframe to an interactive table.\"\n",
              "              style=\"display:none;\">\n",
              "        \n",
              "  <svg xmlns=\"http://www.w3.org/2000/svg\" height=\"24px\"viewBox=\"0 0 24 24\"\n",
              "       width=\"24px\">\n",
              "    <path d=\"M0 0h24v24H0V0z\" fill=\"none\"/>\n",
              "    <path d=\"M18.56 5.44l.94 2.06.94-2.06 2.06-.94-2.06-.94-.94-2.06-.94 2.06-2.06.94zm-11 1L8.5 8.5l.94-2.06 2.06-.94-2.06-.94L8.5 2.5l-.94 2.06-2.06.94zm10 10l.94 2.06.94-2.06 2.06-.94-2.06-.94-.94-2.06-.94 2.06-2.06.94z\"/><path d=\"M17.41 7.96l-1.37-1.37c-.4-.4-.92-.59-1.43-.59-.52 0-1.04.2-1.43.59L10.3 9.45l-7.72 7.72c-.78.78-.78 2.05 0 2.83L4 21.41c.39.39.9.59 1.41.59.51 0 1.02-.2 1.41-.59l7.78-7.78 2.81-2.81c.8-.78.8-2.07 0-2.86zM5.41 20L4 18.59l7.72-7.72 1.47 1.35L5.41 20z\"/>\n",
              "  </svg>\n",
              "      </button>\n",
              "      \n",
              "  <style>\n",
              "    .colab-df-container {\n",
              "      display:flex;\n",
              "      flex-wrap:wrap;\n",
              "      gap: 12px;\n",
              "    }\n",
              "\n",
              "    .colab-df-convert {\n",
              "      background-color: #E8F0FE;\n",
              "      border: none;\n",
              "      border-radius: 50%;\n",
              "      cursor: pointer;\n",
              "      display: none;\n",
              "      fill: #1967D2;\n",
              "      height: 32px;\n",
              "      padding: 0 0 0 0;\n",
              "      width: 32px;\n",
              "    }\n",
              "\n",
              "    .colab-df-convert:hover {\n",
              "      background-color: #E2EBFA;\n",
              "      box-shadow: 0px 1px 2px rgba(60, 64, 67, 0.3), 0px 1px 3px 1px rgba(60, 64, 67, 0.15);\n",
              "      fill: #174EA6;\n",
              "    }\n",
              "\n",
              "    [theme=dark] .colab-df-convert {\n",
              "      background-color: #3B4455;\n",
              "      fill: #D2E3FC;\n",
              "    }\n",
              "\n",
              "    [theme=dark] .colab-df-convert:hover {\n",
              "      background-color: #434B5C;\n",
              "      box-shadow: 0px 1px 3px 1px rgba(0, 0, 0, 0.15);\n",
              "      filter: drop-shadow(0px 1px 2px rgba(0, 0, 0, 0.3));\n",
              "      fill: #FFFFFF;\n",
              "    }\n",
              "  </style>\n",
              "\n",
              "      <script>\n",
              "        const buttonEl =\n",
              "          document.querySelector('#df-99e87de7-c0ae-4a29-8fe3-085fcb724d2d button.colab-df-convert');\n",
              "        buttonEl.style.display =\n",
              "          google.colab.kernel.accessAllowed ? 'block' : 'none';\n",
              "\n",
              "        async function convertToInteractive(key) {\n",
              "          const element = document.querySelector('#df-99e87de7-c0ae-4a29-8fe3-085fcb724d2d');\n",
              "          const dataTable =\n",
              "            await google.colab.kernel.invokeFunction('convertToInteractive',\n",
              "                                                     [key], {});\n",
              "          if (!dataTable) return;\n",
              "\n",
              "          const docLinkHtml = 'Like what you see? Visit the ' +\n",
              "            '<a target=\"_blank\" href=https://colab.research.google.com/notebooks/data_table.ipynb>data table notebook</a>'\n",
              "            + ' to learn more about interactive tables.';\n",
              "          element.innerHTML = '';\n",
              "          dataTable['output_type'] = 'display_data';\n",
              "          await google.colab.output.renderOutput(dataTable, element);\n",
              "          const docLink = document.createElement('div');\n",
              "          docLink.innerHTML = docLinkHtml;\n",
              "          element.appendChild(docLink);\n",
              "        }\n",
              "      </script>\n",
              "    </div>\n",
              "  </div>\n",
              "  "
            ],
            "text/plain": [
              "       StichtagDatJahr       HalterId    AlterV10Cd       KreisCd  \\\n",
              "count     61472.000000   61472.000000  61257.000000  61470.000000   \n",
              "mean       2018.710974  113860.603478     44.979839      7.397462   \n",
              "std           2.311283   25806.372691     15.842023      3.271172   \n",
              "min        2015.000000     126.000000     10.000000      1.000000   \n",
              "25%        2017.000000   89705.000000     30.000000      4.000000   \n",
              "50%        2019.000000  116324.000000     40.000000      8.000000   \n",
              "75%        2021.000000  133157.000000     60.000000     10.000000   \n",
              "max        2022.000000  160183.000000     90.000000     12.000000   \n",
              "\n",
              "             QuarCd  GebDatHundJahr  AlterVHundCd  \n",
              "count  61470.000000    61464.000000  61464.000000  \n",
              "mean      76.545177     2011.763829      5.950817  \n",
              "std       33.135252        4.812745      4.164937  \n",
              "min       10.000000     1994.000000      0.000000  \n",
              "25%       44.000000     2008.000000      2.000000  \n",
              "50%       81.000000     2012.000000      6.000000  \n",
              "75%      102.000000     2015.000000      9.000000  \n",
              "max      123.000000     2022.000000     23.000000  "
            ]
          },
          "execution_count": 21,
          "metadata": {},
          "output_type": "execute_result"
        }
      ],
      "source": [
        "dogs.describe()"
      ]
    },
    {
      "cell_type": "markdown",
      "metadata": {
        "id": "Zd_nVTdlB7Iy"
      },
      "source": [
        "Allerdings sind die Werte in QuarCd höher als wir erwarten würden: Zürich hat nur 34 Quartiere, aber die maximale Zahl ist 123. Also sehen wir uns alle Quartiere in der Spalte an. \n",
        "\n",
        "Dafür benutzen wir die `.unique()`-Methode, die jeden Eintrag nur ein einziges Mal darstellt."
      ]
    },
    {
      "cell_type": "code",
      "execution_count": 22,
      "metadata": {
        "colab": {
          "base_uri": "https://localhost:8080/"
        },
        "id": "0APIRe_e75ZH",
        "outputId": "cb1bb15f-e1ae-47e1-8334-547f2af72b21"
      },
      "outputs": [
        {
          "data": {
            "text/plain": [
              "array(['Altstetten', 'Leimbach', 'Oberstrass', 'Fluntern', 'Wipkingen',\n",
              "       'Sihlfeld', 'Affoltern', 'Wollishofen', 'Seefeld', 'Albisrieden',\n",
              "       'Witikon', 'Escher Wyss', 'Langstrasse', 'Mühlebach',\n",
              "       'Gewerbeschule', 'Hottingen', 'Seebach', 'Höngg', 'Unterstrass',\n",
              "       'Unbekannt (Stadt Zürich)', 'Schwamendingen-Mitte', 'Werd',\n",
              "       'Friesenberg', 'Alt-Wiedikon', 'Enge', 'Hirslanden', 'Saatlen',\n",
              "       'Hard', 'Hirzenbach', 'Weinegg', 'Oerlikon', 'City', 'Rathaus',\n",
              "       'Lindenhof', 'Unbekannt (Kreis 4)', 'Hochschulen',\n",
              "       'Unbekannt (Kreis 6)', 'Unbekannt (Kreis 1)',\n",
              "       'Unbekannt (Kreis 8)'], dtype=object)"
            ]
          },
          "execution_count": 22,
          "metadata": {},
          "output_type": "execute_result"
        }
      ],
      "source": [
        "dogs[\"QuarLang\"].unique()"
      ]
    },
    {
      "cell_type": "markdown",
      "metadata": {
        "id": "RNCaL2OGCSSX"
      },
      "source": [
        "Hier sind einige Quartiere als Unbekannt aufgeführt. Aber der Name ist nicht immer gleich – sie haben in Klammern entweder den Kreis oder die Stadt stehen.\n",
        "\n",
        "Außerdem sind mehr als die 34 Quartiere enthalten:"
      ]
    },
    {
      "cell_type": "code",
      "execution_count": 23,
      "metadata": {
        "colab": {
          "base_uri": "https://localhost:8080/"
        },
        "id": "dKUyMJcYFcmM",
        "outputId": "c65a9ec1-8bee-45cf-9cb5-ff2cbe3fe1a6"
      },
      "outputs": [
        {
          "data": {
            "text/plain": [
              "39"
            ]
          },
          "execution_count": 23,
          "metadata": {},
          "output_type": "execute_result"
        }
      ],
      "source": [
        "dogs[\"QuarLang\"].unique().size"
      ]
    },
    {
      "cell_type": "markdown",
      "metadata": {
        "id": "fkyYRy1AFc6g"
      },
      "source": [
        "Auch diese Einträge wollen wir durch `pd.NA` ersetzen. Doch nicht nur in der Spalte `QuarLang` Spalte sondern auch in der Spalte `QuarCd`.\n",
        "\n",
        "Hierfür können wir die `mask()`-Funktion gut gebrauchen, denn so können wir auf der Spalte `QuarLang` eine Maske erstellen, mit der wir alle Felder maskieren, die mit \"Unbekannt\" beginnen. "
      ]
    },
    {
      "cell_type": "code",
      "execution_count": 24,
      "metadata": {
        "id": "l3DMCf6ICRso"
      },
      "outputs": [],
      "source": [
        "mask = dogs[\"QuarLang\"].str.startswith(\"Unbekannt\")"
      ]
    },
    {
      "cell_type": "markdown",
      "metadata": {
        "id": "kUkgFVUREsho"
      },
      "source": [
        "Diese Maske wenden wir nun auf beide "
      ]
    },
    {
      "cell_type": "code",
      "execution_count": 25,
      "metadata": {
        "id": "Ga4LRIWHEUJa"
      },
      "outputs": [],
      "source": [
        "dogs[\"QuarLang\"] = dogs[\"QuarLang\"].mask(mask, pd.NA)\n",
        "dogs[\"QuarCd\"] = dogs[\"QuarCd\"].mask(mask, pd.NA)"
      ]
    },
    {
      "cell_type": "markdown",
      "metadata": {
        "id": "9VotlBu5Fm32"
      },
      "source": [
        "Nun sind nur mehr 34 Quartiere und das `pd.NA` in der Spalte:"
      ]
    },
    {
      "cell_type": "code",
      "execution_count": 28,
      "metadata": {
        "colab": {
          "base_uri": "https://localhost:8080/"
        },
        "id": "gCLNBz7BEgBK",
        "outputId": "aca17458-2049-4320-e040-b8203d0455bf"
      },
      "outputs": [
        {
          "data": {
            "text/plain": [
              "array(['Altstetten', 'Leimbach', 'Oberstrass', 'Fluntern', 'Wipkingen',\n",
              "       'Sihlfeld', 'Affoltern', 'Wollishofen', 'Seefeld', 'Albisrieden',\n",
              "       'Witikon', 'Escher Wyss', 'Langstrasse', 'Mühlebach',\n",
              "       'Gewerbeschule', 'Hottingen', 'Seebach', 'Höngg', 'Unterstrass',\n",
              "       <NA>, 'Schwamendingen-Mitte', 'Werd', 'Friesenberg',\n",
              "       'Alt-Wiedikon', 'Enge', 'Hirslanden', 'Saatlen', 'Hard',\n",
              "       'Hirzenbach', 'Weinegg', 'Oerlikon', 'City', 'Rathaus',\n",
              "       'Lindenhof', 'Hochschulen'], dtype=object)"
            ]
          },
          "execution_count": 28,
          "metadata": {},
          "output_type": "execute_result"
        }
      ],
      "source": [
        "dogs[\"QuarLang\"].unique()"
      ]
    },
    {
      "cell_type": "markdown",
      "metadata": {
        "id": "f_I-XHUJZGp6"
      },
      "source": [
        "Zuletzt schreiben wir den bereinigten Datensatz auf die Festplatte. Dafür nutzen wir die `to_csv()`-Methode. \n",
        "\n",
        "Vergesst nicht, den Datensatz herunterzuladen – wenn das Colab-Notebook eine Zeit lang inaktiv war sind alle eure Daten gelöscht."
      ]
    },
    {
      "cell_type": "code",
      "execution_count": 29,
      "metadata": {
        "id": "Ewy8tMBGZXF0"
      },
      "outputs": [],
      "source": [
        "dogs.to_csv(\"dogs.csv\")"
      ]
    }
  ],
  "metadata": {
    "colab": {
      "authorship_tag": "ABX9TyN1/knez3Es2Ph/ADDdHt40",
      "include_colab_link": true,
      "provenance": []
    },
    "kernelspec": {
      "display_name": "Python 3",
      "name": "python3"
    },
    "language_info": {
      "name": "python"
    }
  },
  "nbformat": 4,
  "nbformat_minor": 0
}