1_Kuerzungen.ipynb

{
 "cells": [
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "<img src=\"https://raw.githubusercontent.com/tamedia-ddj/brennpunkt_bauernhof_public/master/files/Bauernhof_Logo.jpg\">\n",
    "\n",
    "# Brennpunkt Bauernhof\n",
    "## Analyse des Datenbestandes zu den Direktzahlungen 2014 bis 2017\n",
    "\n",
    "Ein Rechercheteam von Tamedia veröffentlich diese Woche eine Serie von Artikeln zur Landwirtschaft. Es wurden dazu Daten über Direktzahlung sowie über 600 Strafurteile gegen Landwirte ausgewertet.\n",
    "\n",
    "In den folgenden Notebooks sind die relevanten Ausschnitte der Analyse dokumentiert. Der beiliegende Datensatz wurde zur Wahrung des Datenschutzes gekürzt und verändert (Gemeinde, Kantone und Zahlenwerte wurden willkürlich vertauscht).\n",
    "\n",
    "\n",
    "\n",
    "\n",
    "1. [Datenvorbereitung](https://github.com/tamedia-ddj/brennpunkt_bauernhof_public/blob/master/0_Data_Preparation.ipynb)\n",
    "2. **Kürzungen**\n",
    "    * Artikel:\n",
    "        * [Skandalöse Zustände auf Schweizer Bauernhöfen](https://www.tagesanzeiger.ch/schweiz/brennpunkt-bauernhof/skandaloese-zustaende-auf-schweizer-bauernhoefen/story/17687029)\n",
    "        * [Sogar Tierquäler erhalten Subventionen](https://www.tagesanzeiger.ch/schweiz/brennpunkt-bauernhof/bauern-erhalten-subventionen-trotz-leidender-tiere/story/19918846)\n",
    "3. Kontrollen\n",
    "\t* Artikel:\n",
    "\t\t* [Zu wenig Kontrollen: Tierquäler bleiben unentdeckt](https://www.tagesanzeiger.ch/schweiz/brennpunkt-bauernhof/zu-wenig-geld-fuer-tierkontrollen/story/12827795)\n",
    "        * [Der Bauern-Sherlock](https://www.tagesanzeiger.ch/schweiz/brennpunkt-bauernhof/wie-ein-ermittler-mit-videobeweisen-landwirte-ueberfuehrt/story/16595684)\n",
    "4. Direktzahlungsbeträge\n",
    "    * Methodik: [Github](https://github.com/tamedia-ddj/brennpunkt_bauernhof_public/blob/master/2_Betraege.ipynb)\n",
    "    * Artikel: [«Das war für uns wie ein Lottosechser»](https://www.tagesanzeiger.ch/schweiz/brennpunkt-bauernhof/welche-bauern-am-meisten-erhalten/story/25753875)\n",
    "5. Interview\n",
    "\t* Artikel: [«Beim Tierschutz gibt es keine Schmerzgrenze»](https://www.tagesanzeiger.ch/schweiz/brennpunkt-bauernhof/beim-tierschutz-gibt-es-keine-schmerzgrenze/story/20647149)\n",
    "6. Interaktiv\n",
    "    * Artikel: [Ein Leben lang trächtig und am Ende eine Wurst](https://interaktiv.tagesanzeiger.ch/2019/leben-einer-kuh/)\n",
    "7. Tiertransporte\n",
    "    * Artikel: [Die letzte Reise von Schwein Fanny](https://www.tagesanzeiger.ch/schweiz/brennpunkt-bauernhof/die-letzte-reise-von-schwein-fanny/story/14773958)\n",
    "8. Verendungen\n",
    "    * Artikel: [Zu krank für den Schlachthof](https://www.tagesanzeiger.ch/sonntagszeitung/zu-krank-fuer-den-schlachthof/story/28505215)\n",
    "9. Leitartikel\n",
    "    * Artikel: [Köpfe einschlagen bringt nichts](https://www.tagesanzeiger.ch/schweiz/brennpunkt-bauernhof/koepfe-einschlagen-bringt-nichts/story/26915768)\n",
    "\n",
    "\n",
    "Quelle des Datensatzes: BLW"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "---"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### Notebook 2 - Kürzungen\n",
    "#### Berechnungen zu Artikeln"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "---"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "#### Load Packages"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 2,
   "metadata": {},
   "outputs": [],
   "source": [
    "# Import necessary packages\n",
    "import pandas as pd\n",
    "import numpy as np\n",
    "import matplotlib.pyplot as plt\n",
    "import matplotlib.ticker as tkr"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "#### Import data"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 23,
   "metadata": {},
   "outputs": [],
   "source": [
    "# read pickle-file\n",
    "data = pd.read_pickle(\"files/data_mod.pkl\")\n",
    "# import fixed list of municipalities and their corresponding cantons\n",
    "clean_gde = pd.read_pickle(\"files/gemeinden_bereinigt.pkl\")"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "---"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "#### Calculate key facts"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "##### sum of all cutbacks in 2017"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 4,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "8114199.1\n"
     ]
    }
   ],
   "source": [
    "# sum of all cutbacks in 2017\n",
    "print(data[data.JAHR == 2017].KUERZUNGEN.sum())"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "##### number of farms overall and with cutbacks in 2017"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 5,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "Alle Höfe: 52192\n",
      "Mit Kürzungen: 7398\n",
      "Jeder 7. Hof\n"
     ]
    }
   ],
   "source": [
    "print(\"Alle Höfe: {}\".format(len(data[data.JAHR == 2017].BETRIEBSNUMMER_ZUFALL.unique())))\n",
    "print(\"Mit Kürzungen: {}\".format(len(data[(data.JAHR == 2017) & (data.KUERZUNGEN > 0)].BETRIEBSNUMMER_ZUFALL.unique())))\n",
    "print(\"Jeder {0:.0f}. Hof\" .format(52192/7398))"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "##### number of farms which received subsidies in the full period"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 6,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "Im Zeitraum 2014 - 2017 erhielten insgesamt 62003 Betriebe Gelder vom Bund.\n"
     ]
    }
   ],
   "source": [
    "print(\"Im Zeitraum 2014 - 2017 erhielten insgesamt {} Betriebe Gelder vom Bund.\".format(len(data.BETRIEBSNUMMER_ZUFALL.unique())))"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "##### number of farms with cutbacks in the full period\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 13,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "18313 Betrieben wurden Direktzahlungen gekürzt.\n"
     ]
    }
   ],
   "source": [
    "print(\"{} Betrieben wurden Direktzahlungen gekürzt.\".format(len(data[data.KUERZUNGEN > 0].BETRIEBSNUMMER_ZUFALL.unique())))"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "##### farms with multiple cutbacks"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 14,
   "metadata": {},
   "outputs": [],
   "source": [
    "anzahl_kuerzungen = pd.DataFrame(data[(data.KUERZUNGEN > 0)].BETRIEBSNUMMER_ZUFALL.value_counts()).reset_index()\n",
    "anzahl_kuerzungen.columns = [\"BETRIEBSNUMMER_ZUFALL\", \"Anzahl_Kuerzungen\"]"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 43,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "Anzahl Betriebe mit mehreren Kürzungen: 4614\n"
     ]
    }
   ],
   "source": [
    "### >1\n",
    "print(\"Anzahl Betriebe mit mehreren Kürzungen: {}\".format(len(anzahl_kuerzungen[anzahl_kuerzungen.Anzahl_Kuerzungen > 1])))"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 20,
   "metadata": {
    "scrolled": true
   },
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>n_cutbacks</th>\n",
       "      <th>counts</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>1</td>\n",
       "      <td>13699</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>2</td>\n",
       "      <td>3550</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>3</td>\n",
       "      <td>869</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>4</td>\n",
       "      <td>195</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "   n_cutbacks  counts\n",
       "0           1   13699\n",
       "1           2    3550\n",
       "2           3     869\n",
       "3           4     195"
      ]
     },
     "execution_count": 20,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "### count by number of cutbacks\n",
    "anzahl_kuerzungen.Anzahl_Kuerzungen.value_counts().rename_axis('n_cutbacks').reset_index(name='counts')"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "---"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "#### cutbacks by municipality"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 24,
   "metadata": {},
   "outputs": [],
   "source": [
    "### get number of farm with cutbacks by municipality\n",
    "cutbacks_gde = data[(data.KUERZUNGEN > 0)][[\"GDE_GEMEINDE_NR\", \"KUERZUNGEN\"]].groupby([\"GDE_GEMEINDE_NR\"]).count().reset_index()\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 26,
   "metadata": {},
   "outputs": [],
   "source": [
    "### collect matching name and canton\n",
    "cutbacks_gde = pd.merge(clean_gde, cutbacks_gde, on=\"GDE_GEMEINDE_NR\", how=\"left\")"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 27,
   "metadata": {},
   "outputs": [],
   "source": [
    "### replace NAs\n",
    "cutbacks_gde.KUERZUNGEN.fillna(0, inplace=True)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 28,
   "metadata": {},
   "outputs": [],
   "source": [
    "### function to get total number of farms by GDE_GEMEINDE_NR\n",
    "def get_total_betriebe(gde_nr):\n",
    "    return len(data[data.GDE_GEMEINDE_NR == gde_nr].BETRIEBSNUMMER_ZUFALL.unique())"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 29,
   "metadata": {},
   "outputs": [],
   "source": [
    "### apply get_total_betriebe()\n",
    "cutbacks_gde[\"BETRIEBE_TOTAL\"] = cutbacks_gde.GDE_GEMEINDE_NR.apply(get_total_betriebe)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 30,
   "metadata": {},
   "outputs": [],
   "source": [
    "### calculate percentage of farms with cutbacks\n",
    "cutbacks_gde[\"KUERZUNGEN_ANTEIL\"] = cutbacks_gde.KUERZUNGEN / cutbacks_gde.BETRIEBE_TOTAL"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 31,
   "metadata": {},
   "outputs": [],
   "source": [
    "### function to get average cutback by GDE_GEMEINDE_NR\n",
    "def get_avg_kuerzung(gde_nr):\n",
    "    return data[data.GDE_GEMEINDE_NR == gde_nr].KUERZUNGEN.mean()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 32,
   "metadata": {},
   "outputs": [],
   "source": [
    "### apply get_avg_kuerzung()\n",
    "cutbacks_gde[\"KUERZUNGEN_BETRAG_AVG\"] = cutbacks_gde.GDE_GEMEINDE_NR.apply(get_avg_kuerzung)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 33,
   "metadata": {},
   "outputs": [],
   "source": [
    "### function to clean municipality name / remove canton in parentheses\n",
    "import re\n",
    "def remove_kte(gde):\n",
    "    return re.sub(r\" ?\\([^)]+\\)\", \"\", gde)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 35,
   "metadata": {},
   "outputs": [],
   "source": [
    "### apply remove_kte()\n",
    "cutbacks_gde[\"GDE_CLEAN\"] = cutbacks_gde.GDE_NAME.apply(remove_kte)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 41,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>GDE_GEMEINDE_NR</th>\n",
       "      <th>GDE_NAME</th>\n",
       "      <th>ABJ_KANTON</th>\n",
       "      <th>KUERZUNGEN</th>\n",
       "      <th>BETRIEBE_TOTAL</th>\n",
       "      <th>KUERZUNGEN_ANTEIL</th>\n",
       "      <th>KUERZUNGEN_BETRAG_AVG</th>\n",
       "      <th>GDE_CLEAN</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>452</th>\n",
       "      <td>1010</td>\n",
       "      <td>Escholzmatt-Marbach</td>\n",
       "      <td>LU</td>\n",
       "      <td>83.0</td>\n",
       "      <td>330</td>\n",
       "      <td>0.251515</td>\n",
       "      <td>41.405611</td>\n",
       "      <td>Escholzmatt-Marbach</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "     GDE_GEMEINDE_NR             GDE_NAME ABJ_KANTON  KUERZUNGEN  \\\n",
       "452             1010  Escholzmatt-Marbach         LU        83.0   \n",
       "\n",
       "     BETRIEBE_TOTAL  KUERZUNGEN_ANTEIL  KUERZUNGEN_BETRAG_AVG  \\\n",
       "452             330           0.251515              41.405611   \n",
       "\n",
       "               GDE_CLEAN  \n",
       "452  Escholzmatt-Marbach  "
      ]
     },
     "execution_count": 41,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "### look at sample row\n",
    "cutbacks_gde[cutbacks_gde.index == 452]"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 36,
   "metadata": {},
   "outputs": [],
   "source": [
    "### Export relevant data to csv for visualization\n",
    "cutbacks_gde[[\"KUERZUNGEN\", \"GDE_NAME\", \"BETRIEBE_TOTAL\", \"KUERZUNGEN_BETRAG_AVG\", \"ABJ_KANTON\", \"GDE_CLEAN\"]].to_csv(\"data_output/n2_final.csv\")\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 56,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>KUERZUNGEN</th>\n",
       "      <th>GDE_NAME</th>\n",
       "      <th>BETRIEBE_TOTAL</th>\n",
       "      <th>KUERZUNGEN_BETRAG_AVG</th>\n",
       "      <th>ABJ_KANTON</th>\n",
       "      <th>GDE_CLEAN</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>7.0</td>\n",
       "      <td>Aeugst am Albis</td>\n",
       "      <td>14</td>\n",
       "      <td>303.235577</td>\n",
       "      <td>ZH</td>\n",
       "      <td>Aeugst am Albis</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>16.0</td>\n",
       "      <td>Affoltern am Albis</td>\n",
       "      <td>23</td>\n",
       "      <td>361.000633</td>\n",
       "      <td>ZH</td>\n",
       "      <td>Affoltern am Albis</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>10.0</td>\n",
       "      <td>Bonstetten</td>\n",
       "      <td>14</td>\n",
       "      <td>270.619048</td>\n",
       "      <td>ZH</td>\n",
       "      <td>Bonstetten</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>29.0</td>\n",
       "      <td>Hausen am Albis</td>\n",
       "      <td>45</td>\n",
       "      <td>320.239362</td>\n",
       "      <td>ZH</td>\n",
       "      <td>Hausen am Albis</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>6.0</td>\n",
       "      <td>Hedingen</td>\n",
       "      <td>13</td>\n",
       "      <td>109.026829</td>\n",
       "      <td>ZH</td>\n",
       "      <td>Hedingen</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>5</th>\n",
       "      <td>14.0</td>\n",
       "      <td>Kappel am Albis</td>\n",
       "      <td>24</td>\n",
       "      <td>255.989873</td>\n",
       "      <td>ZH</td>\n",
       "      <td>Kappel am Albis</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>6</th>\n",
       "      <td>15.0</td>\n",
       "      <td>Knonau</td>\n",
       "      <td>23</td>\n",
       "      <td>218.288095</td>\n",
       "      <td>ZH</td>\n",
       "      <td>Knonau</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>7</th>\n",
       "      <td>6.0</td>\n",
       "      <td>Maschwanden</td>\n",
       "      <td>20</td>\n",
       "      <td>240.514706</td>\n",
       "      <td>ZH</td>\n",
       "      <td>Maschwanden</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>8</th>\n",
       "      <td>17.0</td>\n",
       "      <td>Mettmenstetten</td>\n",
       "      <td>44</td>\n",
       "      <td>187.509236</td>\n",
       "      <td>ZH</td>\n",
       "      <td>Mettmenstetten</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>9</th>\n",
       "      <td>11.0</td>\n",
       "      <td>Obfelden</td>\n",
       "      <td>18</td>\n",
       "      <td>181.833077</td>\n",
       "      <td>ZH</td>\n",
       "      <td>Obfelden</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "   KUERZUNGEN            GDE_NAME  BETRIEBE_TOTAL  KUERZUNGEN_BETRAG_AVG  \\\n",
       "0         7.0     Aeugst am Albis              14             303.235577   \n",
       "1        16.0  Affoltern am Albis              23             361.000633   \n",
       "2        10.0          Bonstetten              14             270.619048   \n",
       "3        29.0     Hausen am Albis              45             320.239362   \n",
       "4         6.0            Hedingen              13             109.026829   \n",
       "5        14.0     Kappel am Albis              24             255.989873   \n",
       "6        15.0              Knonau              23             218.288095   \n",
       "7         6.0         Maschwanden              20             240.514706   \n",
       "8        17.0      Mettmenstetten              44             187.509236   \n",
       "9        11.0            Obfelden              18             181.833077   \n",
       "\n",
       "  ABJ_KANTON           GDE_CLEAN  \n",
       "0         ZH     Aeugst am Albis  \n",
       "1         ZH  Affoltern am Albis  \n",
       "2         ZH          Bonstetten  \n",
       "3         ZH     Hausen am Albis  \n",
       "4         ZH            Hedingen  \n",
       "5         ZH     Kappel am Albis  \n",
       "6         ZH              Knonau  \n",
       "7         ZH         Maschwanden  \n",
       "8         ZH      Mettmenstetten  \n",
       "9         ZH            Obfelden  "
      ]
     },
     "execution_count": 56,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "### Look at export\n",
    "cutbacks_gde[[\"KUERZUNGEN\", \"GDE_NAME\", \"BETRIEBE_TOTAL\", \"KUERZUNGEN_BETRAG_AVG\", \"ABJ_KANTON\", \"GDE_CLEAN\"]].head(10)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "---"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "#### localize farms with multiple cutbacks and prepare data for visualization"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 44,
   "metadata": {
    "scrolled": true
   },
   "outputs": [],
   "source": [
    "### mark farms with more than 2 cutbacks\n",
    "anzahl_kuerzungen[\"multiple_cutbacks\"] = 0\n",
    "anzahl_kuerzungen.loc[anzahl_kuerzungen.Anzahl_Kuerzungen == 3, \"multiple_cutbacks\"] = 1  \n",
    "anzahl_kuerzungen.loc[anzahl_kuerzungen.Anzahl_Kuerzungen == 4, \"multiple_cutbacks\"] = 1 "
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 45,
   "metadata": {},
   "outputs": [],
   "source": [
    "### assign municipality\n",
    "cutbacks = pd.merge(data[[\"GDE_GEMEINDE_NR\", \"BETRIEBSNUMMER_ZUFALL\"]].drop_duplicates(), anzahl_kuerzungen, how=\"left\", on=\"BETRIEBSNUMMER_ZUFALL\")"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 47,
   "metadata": {},
   "outputs": [],
   "source": [
    "### replace NAs\n",
    "cutbacks.loc[np.isnan(cutbacks.multiple_cutbacks), \"multiple_cutbacks\"] = 0 "
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 48,
   "metadata": {},
   "outputs": [],
   "source": [
    "### collect name and canton\n",
    "cutbacks = pd.merge(clean_gde, cutbacks, on=\"GDE_GEMEINDE_NR\", how=\"left\")"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 49,
   "metadata": {},
   "outputs": [],
   "source": [
    "### apply remove_kte()\n",
    "cutbacks[\"GDE_CLEAN\"] = cutbacks.GDE_NAME.apply(remove_kte)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 50,
   "metadata": {},
   "outputs": [],
   "source": [
    "### subset data\n",
    "cutbacks = cutbacks[cutbacks.multiple_cutbacks != 0]"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 51,
   "metadata": {},
   "outputs": [],
   "source": [
    "### save number of farms with multiple cutbacks by municipality\n",
    "cutbacks[[\"GDE_GEMEINDE_NR\", \"GDE_NAME\", \"GDE_CLEAN\", \"ABJ_KANTON\", \"multiple_cutbacks\"]].groupby([\"GDE_GEMEINDE_NR\", \"GDE_NAME\", \"GDE_CLEAN\", \"ABJ_KANTON\"]).sum().reset_index().to_csv(\"data_output/n3_final.csv\")\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 57,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>GDE_GEMEINDE_NR</th>\n",
       "      <th>GDE_NAME</th>\n",
       "      <th>GDE_CLEAN</th>\n",
       "      <th>ABJ_KANTON</th>\n",
       "      <th>multiple_cutbacks</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>558</th>\n",
       "      <td>6298</td>\n",
       "      <td>Visperterminen</td>\n",
       "      <td>Visperterminen</td>\n",
       "      <td>VS</td>\n",
       "      <td>5.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>559</th>\n",
       "      <td>6421</td>\n",
       "      <td>La Chaux-de-Fonds</td>\n",
       "      <td>La Chaux-de-Fonds</td>\n",
       "      <td>NE</td>\n",
       "      <td>1.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>560</th>\n",
       "      <td>6422</td>\n",
       "      <td>Les Planchettes</td>\n",
       "      <td>Les Planchettes</td>\n",
       "      <td>NE</td>\n",
       "      <td>1.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>561</th>\n",
       "      <td>6423</td>\n",
       "      <td>La Sagne</td>\n",
       "      <td>La Sagne</td>\n",
       "      <td>NE</td>\n",
       "      <td>1.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>562</th>\n",
       "      <td>6431</td>\n",
       "      <td>Les Brenets</td>\n",
       "      <td>Les Brenets</td>\n",
       "      <td>NE</td>\n",
       "      <td>1.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>563</th>\n",
       "      <td>6512</td>\n",
       "      <td>Val-de-Travers</td>\n",
       "      <td>Val-de-Travers</td>\n",
       "      <td>NE</td>\n",
       "      <td>3.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>564</th>\n",
       "      <td>6607</td>\n",
       "      <td>Bernex</td>\n",
       "      <td>Bernex</td>\n",
       "      <td>GE</td>\n",
       "      <td>3.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>565</th>\n",
       "      <td>6639</td>\n",
       "      <td>Soral</td>\n",
       "      <td>Soral</td>\n",
       "      <td>GE</td>\n",
       "      <td>1.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>566</th>\n",
       "      <td>6712</td>\n",
       "      <td>Develier</td>\n",
       "      <td>Develier</td>\n",
       "      <td>JU</td>\n",
       "      <td>1.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>567</th>\n",
       "      <td>6778</td>\n",
       "      <td>Bure</td>\n",
       "      <td>Bure</td>\n",
       "      <td>JU</td>\n",
       "      <td>1.0</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "     GDE_GEMEINDE_NR           GDE_NAME          GDE_CLEAN ABJ_KANTON  \\\n",
       "558             6298     Visperterminen     Visperterminen         VS   \n",
       "559             6421  La Chaux-de-Fonds  La Chaux-de-Fonds         NE   \n",
       "560             6422    Les Planchettes    Les Planchettes         NE   \n",
       "561             6423           La Sagne           La Sagne         NE   \n",
       "562             6431        Les Brenets        Les Brenets         NE   \n",
       "563             6512     Val-de-Travers     Val-de-Travers         NE   \n",
       "564             6607             Bernex             Bernex         GE   \n",
       "565             6639              Soral              Soral         GE   \n",
       "566             6712           Develier           Develier         JU   \n",
       "567             6778               Bure               Bure         JU   \n",
       "\n",
       "     multiple_cutbacks  \n",
       "558                5.0  \n",
       "559                1.0  \n",
       "560                1.0  \n",
       "561                1.0  \n",
       "562                1.0  \n",
       "563                3.0  \n",
       "564                3.0  \n",
       "565                1.0  \n",
       "566                1.0  \n",
       "567                1.0  "
      ]
     },
     "execution_count": 57,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "### look at export\n",
    "cutbacks[[\"GDE_GEMEINDE_NR\", \"GDE_NAME\", \"GDE_CLEAN\", \"ABJ_KANTON\", \"multiple_cutbacks\"]].groupby([\"GDE_GEMEINDE_NR\", \"GDE_NAME\", \"GDE_CLEAN\", \"ABJ_KANTON\"]).sum().reset_index().tail(10)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": []
  }
 ],
 "metadata": {
  "kernelspec": {
   "display_name": "Python 3",
   "language": "python",
   "name": "python3"
  },
  "language_info": {
   "codemirror_mode": {
    "name": "ipython",
    "version": 3
   },
   "file_extension": ".py",
   "mimetype": "text/x-python",
   "name": "python",
   "nbconvert_exporter": "python",
   "pygments_lexer": "ipython3",
   "version": "3.7.2"
  }
 },
 "nbformat": 4,
 "nbformat_minor": 2
}