auswertung_berlin.ipynb

{
 "cells": [
  {
   "cell_type": "markdown",
   "metadata": {
    "deletable": true,
    "editable": true
   },
   "source": [
    "<img src=\"img/datenschule_logo_web.png\" width=\"25%\"  align=\"left\">"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {
    "deletable": true,
    "editable": true
   },
   "source": [
    "<img src=\"img/coat_of_arms_of_berlin.svg\" width=\"11%\"  align=\"right\">\n",
    "# Aktivitäten und Partnerschaften an Berliner Schulen\n",
    "In diesem Notebook dokumnetieren wir unser Vorgehen bei der Auswertung der Partnerschaften und Aktivitäten <br> der Berliner Schulen. Da wir die Daten auf der Website  laufend aktualisieren, können die Ergebnisse hier <br> von denen auf der Website abweichen. Der Berechnungsweg ist allerdings der Gleiche.\n",
    " <br> Mehr Informationen über das Schulsystem in Deutschland gibt es auf <a href='https://jedeschule.de'>jedeschule.de<a>"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 2,
   "metadata": {
    "collapsed": true,
    "deletable": true,
    "editable": true
   },
   "outputs": [],
   "source": [
    "#Für die Berechnung nutzen wir hier die Pandas-Library\n",
    "import pandas as pd\n",
    "import urllib.request, json "
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 3,
   "metadata": {
    "collapsed": false,
    "deletable": true,
    "editable": true
   },
   "outputs": [],
   "source": [
    "with urllib.request.urlopen(\"https://lab.okfn.de/jedeschule/all_schools_geocoded.json\") as url:\n",
    "    data = json.loads(url.read().decode())\n",
    "    berlin_data = [item for item in data if item['state'] == 'BE']\n",
    "    partner_data = []\n",
    "    wg_data = []\n",
    "    for school in berlin_data:\n",
    "        for partner in school['partner']:\n",
    "            partner_data.append({\n",
    "                \"Schule\": school['name'],\n",
    "                \"ID\": school['id'],\n",
    "                \"school_type\" : school['school_type'],\n",
    "                \"partner_name\" : partner['name'],\n",
    "                \"Type_grob\": partner['type']['grob'],\n",
    "                \"Type_mittel\": partner['type']['mittel'],\n",
    "                \"Type_detail\": partner['type']['detail']\n",
    "            })\n",
    "        for wg in school['programs']['working_groups']:\n",
    "            wg_data.append({\n",
    "                \"id\": school['id'],\n",
    "                \"ag_name\" : wg['entity'],\n",
    "                \"ag_cat\" : wg['category'],\n",
    "                \"school\" : school['official_id']\n",
    "            })"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {
    "deletable": true,
    "editable": true
   },
   "source": [
    "### 1. Auswertung der Schulaktivitäten in Berlin"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {
    "deletable": true,
    "editable": true
   },
   "source": [
    "Zunächst werden die Berliner Schulen von jedeschule.de geladen und für die Analyse vorbereitet"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 16,
   "metadata": {
    "collapsed": false,
    "deletable": true,
    "editable": true
   },
   "outputs": [],
   "source": [
    "df_berlin_ag = pd.DataFrame.from_dict(wg_data)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 17,
   "metadata": {
    "collapsed": false,
    "deletable": true,
    "editable": true
   },
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>ag_cat</th>\n",
       "      <th>ag_name</th>\n",
       "      <th>id</th>\n",
       "      <th>school</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>Naturwissenschaft / Technik</td>\n",
       "      <td>Astronomie</td>\n",
       "      <td>BE-15586</td>\n",
       "      <td>04A06</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>Naturwissenschaft / Technik</td>\n",
       "      <td>Astrophysik</td>\n",
       "      <td>BE-15586</td>\n",
       "      <td>04A06</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>Literatur / Medien</td>\n",
       "      <td>Literatur</td>\n",
       "      <td>BE-15586</td>\n",
       "      <td>04A06</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>Kunst / Kultur</td>\n",
       "      <td>Bildende Kunst</td>\n",
       "      <td>BE-15586</td>\n",
       "      <td>04A06</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>Sport</td>\n",
       "      <td>Rudern</td>\n",
       "      <td>BE-15590</td>\n",
       "      <td>04P11</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "                        ag_cat         ag_name        id school\n",
       "0  Naturwissenschaft / Technik      Astronomie  BE-15586  04A06\n",
       "1  Naturwissenschaft / Technik     Astrophysik  BE-15586  04A06\n",
       "2           Literatur / Medien       Literatur  BE-15586  04A06\n",
       "3               Kunst / Kultur  Bildende Kunst  BE-15586  04A06\n",
       "4                        Sport          Rudern  BE-15590  04P11"
      ]
     },
     "execution_count": 17,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df_berlin_ag.head(5)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {
    "deletable": true,
    "editable": true
   },
   "source": [
    "Zu Beginn einer Analyse ist es sinnvoll, einen Überblick über die Daten zu gewinnen."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 18,
   "metadata": {
    "collapsed": false,
    "deletable": true,
    "editable": true
   },
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>ag_cat</th>\n",
       "      <th>ag_name</th>\n",
       "      <th>id</th>\n",
       "      <th>school</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>count</th>\n",
       "      <td>3853</td>\n",
       "      <td>3853</td>\n",
       "      <td>3853</td>\n",
       "      <td>3853</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>unique</th>\n",
       "      <td>10</td>\n",
       "      <td>562</td>\n",
       "      <td>492</td>\n",
       "      <td>469</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>top</th>\n",
       "      <td>Sport</td>\n",
       "      <td>Fußball</td>\n",
       "      <td>BE-16461</td>\n",
       "      <td>01P22</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>freq</th>\n",
       "      <td>1322</td>\n",
       "      <td>258</td>\n",
       "      <td>29</td>\n",
       "      <td>48</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "       ag_cat  ag_name        id school\n",
       "count    3853     3853      3853   3853\n",
       "unique     10      562       492    469\n",
       "top     Sport  Fußball  BE-16461  01P22\n",
       "freq     1322      258        29     48"
      ]
     },
     "execution_count": 18,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df_berlin_ag.describe(include='all')"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {
    "deletable": true,
    "editable": true
   },
   "source": [
    "Mit dem Befehl *'describe'* können wir eine Zusammenfassung der Daten erhalten. Wir sehen: \n",
    " * *unique*: 413 der 800 allgemeinbildenden Schulen in Berlin kommunizieren ihre Projekte und Aktivitäten Projekte und Aktivitäten an die Senatsverwaltung.\n",
    " * *count*: Es werden über 3300 Aktivitäten an Berliner Schulen angeboten."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 19,
   "metadata": {
    "collapsed": false,
    "deletable": true,
    "editable": true
   },
   "outputs": [
    {
     "data": {
      "text/plain": [
       "7.83130081300813"
      ]
     },
     "execution_count": 19,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# Anzahl der Schulen in Berlin\n",
    "schools_count = len(df_berlin_ag.id.unique())\n",
    "\n",
    "# Anzahl der Angebotenen AGs\n",
    "projects_count = len(df_berlin_ag)\n",
    "\n",
    "# Durchschnittliche Anzahl an Projekten an Beriner Schulen\n",
    "projects_average = projects_count / schools_count\n",
    "projects_average"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {
    "deletable": true,
    "editable": true
   },
   "source": [
    "Um zu errechnen wie viele Projekte eine Schule in Berlin durchschnittlich anbietet, teilen wir die Anzahl der Projekte durch die Anzahl der Schulen. Da die CSV-Datei für jedes Angebot einen eigenen Zeileneintrag enthält, werden auch Schulen doppelt genannt. Mit der Funktion *'unique()'* filtern wir doppelte Schuleinträge aus. Dadurch erhalten wir die Anzahl der Schulen in Berlin, die ihre Aktivitäten kommuniziert haben. <br>\n",
    "**Ergebnis:**\n",
    "* Durchschnittlich bietet eine Schule in Berlin ihren Schüler*innen rund 8 Aktivitäten an."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 20,
   "metadata": {
    "collapsed": false,
    "deletable": true,
    "editable": true
   },
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "In Berlin werden in durchschnittlich 4.02 Kategorien Aktivitäten angeboten.\n"
     ]
    }
   ],
   "source": [
    "#Wie viel Projekte aus unterschiedlichen Bereichen bieten die Schulen ihren Schüler*innen?\n",
    "category_unique = df_berlin_ag.drop_duplicates(subset=['id','ag_cat'])\n",
    "\n",
    "#Durchschnittliche Anzahl von Aktivitätskategorien pro Schule\n",
    "category_avg = len(category_unique) / schools_count\n",
    "print('In Berlin werden in durchschnittlich %s Kategorien Aktivitäten angeboten.' %(round(category_avg,2)))"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 21,
   "metadata": {
    "collapsed": false,
    "deletable": true,
    "editable": true
   },
   "outputs": [
    {
     "data": {
      "text/plain": [
       "1977"
      ]
     },
     "execution_count": 21,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "len(category_unique)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 23,
   "metadata": {
    "collapsed": false,
    "deletable": true,
    "editable": true
   },
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>id</th>\n",
       "      <th>% Schulen mit Projekt-Kategorie</th>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>ag_cat</th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>Sport</th>\n",
       "      <td>423</td>\n",
       "      <td>85.98</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Musik / Tanz</th>\n",
       "      <td>368</td>\n",
       "      <td>74.80</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Kunst / Kultur</th>\n",
       "      <td>250</td>\n",
       "      <td>50.81</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Naturwissenschaft / Technik</th>\n",
       "      <td>215</td>\n",
       "      <td>43.70</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Handwerk</th>\n",
       "      <td>192</td>\n",
       "      <td>39.02</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Literatur / Medien</th>\n",
       "      <td>179</td>\n",
       "      <td>36.38</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Sprachen</th>\n",
       "      <td>126</td>\n",
       "      <td>25.61</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Gesellschaft / Partizipation</th>\n",
       "      <td>113</td>\n",
       "      <td>22.97</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Umwelt</th>\n",
       "      <td>91</td>\n",
       "      <td>18.50</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Berufsorientierung</th>\n",
       "      <td>20</td>\n",
       "      <td>4.07</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "                               id  % Schulen mit Projekt-Kategorie\n",
       "ag_cat                                                            \n",
       "Sport                         423                            85.98\n",
       "Musik / Tanz                  368                            74.80\n",
       "Kunst / Kultur                250                            50.81\n",
       "Naturwissenschaft / Technik   215                            43.70\n",
       "Handwerk                      192                            39.02\n",
       "Literatur / Medien            179                            36.38\n",
       "Sprachen                      126                            25.61\n",
       "Gesellschaft / Partizipation  113                            22.97\n",
       "Umwelt                         91                            18.50\n",
       "Berufsorientierung             20                             4.07"
      ]
     },
     "execution_count": 23,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "categories_pivot = pd.pivot_table(category_unique,index=['ag_cat'],values='id',aggfunc='count')\n",
    "df_categories = pd.DataFrame(categories_pivot)\n",
    "df_categories['% Schulen mit Projekt-Kategorie'] = (df_categories.id / schools_count * 100).round(2)\n",
    "df_categories.sort_values(by='id',ascending=False)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {
    "deletable": true,
    "editable": true
   },
   "source": [
    "Um zu errechen wie viele Schulen für eine jeweilige Kategorie Aktivitäten anbieten, nutzen wir die Pivot-Funktion. *(pivot_table())*. Mit der Funktion gruppieren wir die Aktivitäten nach ihren Kategorien. Teilt man diese Werte (hier unter Id) durch die Gesamtanzahl der Schulen, erhalten wir den prozentualen Anteil An Schulen die Aktivitäten in einer Kategorie anbieten.\n"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {
    "collapsed": true,
    "deletable": true,
    "editable": true
   },
   "source": [
    "### 2. Auswertung der Schulpartnerschaften in Berlin"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 24,
   "metadata": {
    "collapsed": false,
    "deletable": true,
    "editable": true
   },
   "outputs": [],
   "source": [
    "df_berlin_partner = pd.DataFrame.from_dict(partner_data)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 25,
   "metadata": {
    "collapsed": false,
    "deletable": true,
    "editable": true
   },
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>ID</th>\n",
       "      <th>Schule</th>\n",
       "      <th>Type_detail</th>\n",
       "      <th>Type_grob</th>\n",
       "      <th>Type_mittel</th>\n",
       "      <th>partner_name</th>\n",
       "      <th>school_type</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>BE-15591</td>\n",
       "      <td>Nord-Grundschule</td>\n",
       "      <td>None</td>\n",
       "      <td>Unbestimmt</td>\n",
       "      <td>Modell/Förderprogramm/Projekt</td>\n",
       "      <td>Berliner Literaturinitiative (Projekt / Arbeit...</td>\n",
       "      <td>Grundschule</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>BE-15591</td>\n",
       "      <td>Nord-Grundschule</td>\n",
       "      <td>Polizei</td>\n",
       "      <td>Öffentliche Infrastruktur</td>\n",
       "      <td>Öffentliche Infrastruktur (Sicherheit/Ordnung/...</td>\n",
       "      <td>Berliner Polizei</td>\n",
       "      <td>Grundschule</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>BE-15591</td>\n",
       "      <td>Nord-Grundschule</td>\n",
       "      <td>None</td>\n",
       "      <td>Wirtschaftsakteur</td>\n",
       "      <td>Modell/Förderprogramm/Projekt</td>\n",
       "      <td>Computer Bildung Berlin (CBB)</td>\n",
       "      <td>Grundschule</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>BE-15591</td>\n",
       "      <td>Nord-Grundschule</td>\n",
       "      <td>Verein</td>\n",
       "      <td>Gemeinnütziger Akteur</td>\n",
       "      <td>Gemeinnütziger Akteur</td>\n",
       "      <td>Kaizen Berlin e.V. (Judo)</td>\n",
       "      <td>Grundschule</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>BE-15591</td>\n",
       "      <td>Nord-Grundschule</td>\n",
       "      <td>Verein</td>\n",
       "      <td>Gemeinnütziger Akteur</td>\n",
       "      <td>Gemeinnütziger Akteur</td>\n",
       "      <td>Projekt fee - freiwillig - engagiert - ehrenam...</td>\n",
       "      <td>Grundschule</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "         ID            Schule Type_detail                  Type_grob  \\\n",
       "0  BE-15591  Nord-Grundschule        None                 Unbestimmt   \n",
       "1  BE-15591  Nord-Grundschule     Polizei  Öffentliche Infrastruktur   \n",
       "2  BE-15591  Nord-Grundschule        None          Wirtschaftsakteur   \n",
       "3  BE-15591  Nord-Grundschule      Verein      Gemeinnütziger Akteur   \n",
       "4  BE-15591  Nord-Grundschule      Verein      Gemeinnütziger Akteur   \n",
       "\n",
       "                                         Type_mittel  \\\n",
       "0                      Modell/Förderprogramm/Projekt   \n",
       "1  Öffentliche Infrastruktur (Sicherheit/Ordnung/...   \n",
       "2                      Modell/Förderprogramm/Projekt   \n",
       "3                              Gemeinnütziger Akteur   \n",
       "4                              Gemeinnütziger Akteur   \n",
       "\n",
       "                                        partner_name  school_type  \n",
       "0  Berliner Literaturinitiative (Projekt / Arbeit...  Grundschule  \n",
       "1                                   Berliner Polizei  Grundschule  \n",
       "2                      Computer Bildung Berlin (CBB)  Grundschule  \n",
       "3                          Kaizen Berlin e.V. (Judo)  Grundschule  \n",
       "4  Projekt fee - freiwillig - engagiert - ehrenam...  Grundschule  "
      ]
     },
     "execution_count": 25,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df_berlin_partner.head(5)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {
    "deletable": true,
    "editable": true
   },
   "source": [
    "Auch hier ist es zunächst sinnvoll,einen Blick in die Daten zu werfen."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 26,
   "metadata": {
    "collapsed": false,
    "deletable": true,
    "editable": true
   },
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>ID</th>\n",
       "      <th>Schule</th>\n",
       "      <th>Type_detail</th>\n",
       "      <th>Type_grob</th>\n",
       "      <th>Type_mittel</th>\n",
       "      <th>partner_name</th>\n",
       "      <th>school_type</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>count</th>\n",
       "      <td>2977</td>\n",
       "      <td>2977</td>\n",
       "      <td>1595</td>\n",
       "      <td>2977</td>\n",
       "      <td>2977</td>\n",
       "      <td>2976</td>\n",
       "      <td>2977</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>unique</th>\n",
       "      <td>410</td>\n",
       "      <td>410</td>\n",
       "      <td>23</td>\n",
       "      <td>7</td>\n",
       "      <td>25</td>\n",
       "      <td>2122</td>\n",
       "      <td>15</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>top</th>\n",
       "      <td>BE-16610</td>\n",
       "      <td>Albrecht-von-Graefe-Schule (Integrierte Sekund...</td>\n",
       "      <td>Verein</td>\n",
       "      <td>Gemeinnütziger Akteur</td>\n",
       "      <td>Gemeinnütziger Akteur</td>\n",
       "      <td>Verein Berliner Kaufleute und Industrieller (V...</td>\n",
       "      <td>Grundschule</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>freq</th>\n",
       "      <td>25</td>\n",
       "      <td>25</td>\n",
       "      <td>829</td>\n",
       "      <td>1118</td>\n",
       "      <td>999</td>\n",
       "      <td>29</td>\n",
       "      <td>1127</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "              ID                                             Schule  \\\n",
       "count       2977                                               2977   \n",
       "unique       410                                                410   \n",
       "top     BE-16610  Albrecht-von-Graefe-Schule (Integrierte Sekund...   \n",
       "freq          25                                                 25   \n",
       "\n",
       "       Type_detail              Type_grob            Type_mittel  \\\n",
       "count         1595                   2977                   2977   \n",
       "unique          23                      7                     25   \n",
       "top         Verein  Gemeinnütziger Akteur  Gemeinnütziger Akteur   \n",
       "freq           829                   1118                    999   \n",
       "\n",
       "                                             partner_name  school_type  \n",
       "count                                                2976         2977  \n",
       "unique                                               2122           15  \n",
       "top     Verein Berliner Kaufleute und Industrieller (V...  Grundschule  \n",
       "freq                                                   29         1127  "
      ]
     },
     "execution_count": 26,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df_berlin_partner.describe(include='all')"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {
    "deletable": true,
    "editable": true
   },
   "source": [
    "Über die *'describe()'* Funktion bekommen wir die Anzahl der Partnerschaften (2976) sowie die Anzahl der Schulen (411)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 30,
   "metadata": {
    "collapsed": false,
    "deletable": true,
    "editable": true
   },
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>partner_name</th>\n",
       "      <th>Verteilung</th>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Type_grob</th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>Gemeinnütziger Akteur</th>\n",
       "      <td>1118</td>\n",
       "      <td>0.3757</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Öffentliche Infrastruktur</th>\n",
       "      <td>884</td>\n",
       "      <td>0.2970</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Wirtschaftsakteur</th>\n",
       "      <td>533</td>\n",
       "      <td>0.1791</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Partnerschule</th>\n",
       "      <td>246</td>\n",
       "      <td>0.0827</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Unbestimmt</th>\n",
       "      <td>119</td>\n",
       "      <td>0.0400</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Religiöse Einrichtung</th>\n",
       "      <td>42</td>\n",
       "      <td>0.0141</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Verband / Kammer / Innung / Gewerkschaft</th>\n",
       "      <td>34</td>\n",
       "      <td>0.0114</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "                                          partner_name  Verteilung\n",
       "Type_grob                                                         \n",
       "Gemeinnütziger Akteur                             1118      0.3757\n",
       "Öffentliche Infrastruktur                          884      0.2970\n",
       "Wirtschaftsakteur                                  533      0.1791\n",
       "Partnerschule                                      246      0.0827\n",
       "Unbestimmt                                         119      0.0400\n",
       "Religiöse Einrichtung                               42      0.0141\n",
       "Verband / Kammer / Innung / Gewerkschaft            34      0.0114"
      ]
     },
     "execution_count": 30,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "typ_partner = pd.DataFrame(df_berlin_partner.Type_grob.value_counts())\n",
    "typ_partner = pd.DataFrame(pd.pivot_table(df_berlin_partner,index=['Type_grob'],values='partner_name', aggfunc='count'))\n",
    "typ_partner['Verteilung'] = (typ_partner / sum(typ_partner.partner_name)).round(4)\n",
    "typ_partner.sort_values(by='partner_name', ascending=False)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 31,
   "metadata": {
    "collapsed": true,
    "deletable": true,
    "editable": true
   },
   "outputs": [],
   "source": [
    "schulkategorien = {'Grundschule' : [' Grundschule',\n",
    "                                     'Grundschule'],\n",
    "                    'Sekundarschule' : ['Integrierte Sekundarschule',\n",
    "                                        ],\n",
    "                    'Berufliche Schule' : ['Berufsfachschule',\n",
    "                                           'Berufsoberschule',\n",
    "                                           'Berufsschule',\n",
    "                                           'Berufsschule mit sonderpäd. Aufgaben',\n",
    "                                           'Fachoberschule',\n",
    "                                           'Fachschule'],\n",
    "                    'Förderschulen' : ['Förderschwerp.\"Geistige Entwicklung\"',\n",
    "                                       'Förderschwerp.\"Lernen\"',\n",
    "                                       'Übrige Förderschwerpunkte'\n",
    "                                       ],\n",
    "                    'Sonstige' :['Freie Waldorfschule',\n",
    "                                 'Sonstige Ergänzungsschule',\n",
    "                                 ],\n",
    "                   \n",
    "                   'Gymnasium' : ['Berufliches Gymnasium',\n",
    "                                  'Gymnasien']\n",
    "                   \n",
    "                   }"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {
    "deletable": true,
    "editable": true
   },
   "source": [
    "Um die unterschiedlichen Schultypen zu vereinheitlichen, ordnen wir diese in 6 Kategorien ein: Grundschule, Sekundarschule, Berufliche Schule, Förderschulen, Gymnasium, Sonstige. Dazu erstellen wir das Dictionary: Schulkategorien."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 32,
   "metadata": {
    "collapsed": true,
    "deletable": true,
    "editable": true
   },
   "outputs": [],
   "source": [
    "def check_up_schultyp(schooltyp):\n",
    "    for key, value in schulkategorien.items():\n",
    "        for a in value:\n",
    "            if a in str(schooltyp):\n",
    "                return(key)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 34,
   "metadata": {
    "collapsed": false,
    "deletable": true,
    "editable": true
   },
   "outputs": [],
   "source": [
    "# Hier mappen wir die einzelnen Schultypen auf die 6 Schultypkategorien und säubern leere Zeilen aus.\n",
    "df_berlin_partner['Schooltype_broad'] = df_berlin_partner.school_type.apply(check_up_schultyp)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 85,
   "metadata": {
    "collapsed": false,
    "deletable": true,
    "editable": true
   },
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>Schule</th>\n",
       "      <th>ID</th>\n",
       "      <th>school_type</th>\n",
       "      <th>Partner_name</th>\n",
       "      <th>Type_grob</th>\n",
       "      <th>Type_mittel</th>\n",
       "      <th>Type_detail</th>\n",
       "      <th>Schooltype_broad</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>OSZ Informations- und Medizintechnik</td>\n",
       "      <td>BE-15754</td>\n",
       "      <td>Berufliches Gymnasium</td>\n",
       "      <td>AAP-Programm</td>\n",
       "      <td>Unbestimmt</td>\n",
       "      <td>Modell/Förderprogramm/Projekt</td>\n",
       "      <td>NaN</td>\n",
       "      <td>Gymnasium</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>OSZ Informations- und Medizintechnik</td>\n",
       "      <td>BE-15754</td>\n",
       "      <td>Berufliches Gymnasium</td>\n",
       "      <td>Arbeitskreis Medizintechnik Berlin-Brandenburg</td>\n",
       "      <td>Öffentliche Infrastruktur</td>\n",
       "      <td>Öffentliche Infrastruktur (Bildung/Uni/Hochsch...</td>\n",
       "      <td>NaN</td>\n",
       "      <td>Gymnasium</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>OSZ Handel 1</td>\n",
       "      <td>BE-16262</td>\n",
       "      <td>Berufliches Gymnasium</td>\n",
       "      <td>Ausbildungszentrum ROC Nijmegen</td>\n",
       "      <td>Öffentliche Infrastruktur</td>\n",
       "      <td>Familien-/Bildungs-/Kulturzentrum</td>\n",
       "      <td>NaN</td>\n",
       "      <td>Gymnasium</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>OSZ Informations- und Medizintechnik</td>\n",
       "      <td>BE-15754</td>\n",
       "      <td>Berufliches Gymnasium</td>\n",
       "      <td>Baumann Bildung &amp; Qualifizierung (BBQ)</td>\n",
       "      <td>Wirtschaftsakteur</td>\n",
       "      <td>Wirtschaftsakteur (Bildung)</td>\n",
       "      <td>NaN</td>\n",
       "      <td>Gymnasium</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>Louise-Schroeder-Schule (OSZ Bürowirtschaft un...</td>\n",
       "      <td>BE-15687</td>\n",
       "      <td>Berufliches Gymnasium</td>\n",
       "      <td>Bildung für Berlin und Brandenburg e.V.</td>\n",
       "      <td>Gemeinnütziger Akteur</td>\n",
       "      <td>Gemeinnütziger Akteur</td>\n",
       "      <td>Verein</td>\n",
       "      <td>Gymnasium</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "                                              Schule        ID  \\\n",
       "0               OSZ Informations- und Medizintechnik  BE-15754   \n",
       "1               OSZ Informations- und Medizintechnik  BE-15754   \n",
       "2                                       OSZ Handel 1  BE-16262   \n",
       "3               OSZ Informations- und Medizintechnik  BE-15754   \n",
       "4  Louise-Schroeder-Schule (OSZ Bürowirtschaft un...  BE-15687   \n",
       "\n",
       "             school_type                                    Partner_name  \\\n",
       "0  Berufliches Gymnasium                                    AAP-Programm   \n",
       "1  Berufliches Gymnasium  Arbeitskreis Medizintechnik Berlin-Brandenburg   \n",
       "2  Berufliches Gymnasium                 Ausbildungszentrum ROC Nijmegen   \n",
       "3  Berufliches Gymnasium          Baumann Bildung & Qualifizierung (BBQ)   \n",
       "4  Berufliches Gymnasium         Bildung für Berlin und Brandenburg e.V.   \n",
       "\n",
       "                   Type_grob  \\\n",
       "0                 Unbestimmt   \n",
       "1  Öffentliche Infrastruktur   \n",
       "2  Öffentliche Infrastruktur   \n",
       "3          Wirtschaftsakteur   \n",
       "4      Gemeinnütziger Akteur   \n",
       "\n",
       "                                         Type_mittel Type_detail  \\\n",
       "0                      Modell/Förderprogramm/Projekt         NaN   \n",
       "1  Öffentliche Infrastruktur (Bildung/Uni/Hochsch...         NaN   \n",
       "2                  Familien-/Bildungs-/Kulturzentrum         NaN   \n",
       "3                        Wirtschaftsakteur (Bildung)         NaN   \n",
       "4                              Gemeinnütziger Akteur      Verein   \n",
       "\n",
       "  Schooltype_broad  \n",
       "0        Gymnasium  \n",
       "1        Gymnasium  \n",
       "2        Gymnasium  \n",
       "3        Gymnasium  \n",
       "4        Gymnasium  "
      ]
     },
     "execution_count": 85,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df_berlin_partner.head(5)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 35,
   "metadata": {
    "collapsed": false,
    "deletable": true,
    "editable": true
   },
   "outputs": [
    {
     "data": {
      "text/plain": [
       "7.258536585365854"
      ]
     },
     "execution_count": 35,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "#Durchscnittliche Anzahl an Partnerschaften pro Schule\n",
    "berlin_unique = pd.DataFrame(df_berlin_partner.drop_duplicates())\n",
    "len(berlin_unique) / len(berlin_unique.ID.unique())"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 41,
   "metadata": {
    "collapsed": false,
    "deletable": true,
    "editable": true
   },
   "outputs": [],
   "source": [
    "partner_schulen = pd.DataFrame(pd.pivot_table(df_berlin_partner,index=['Schooltype_broad'],values='school_type',columns='Type_grob', aggfunc='count'))\n",
    "partner_schulen = partner_schulen.fillna(0)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 42,
   "metadata": {
    "collapsed": false,
    "deletable": true,
    "editable": true
   },
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>ID</th>\n",
       "      <th>Schule</th>\n",
       "      <th>Type_detail</th>\n",
       "      <th>Type_grob</th>\n",
       "      <th>Type_mittel</th>\n",
       "      <th>partner_name</th>\n",
       "      <th>school_type</th>\n",
       "      <th>Schooltype_broad</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>BE-15591</td>\n",
       "      <td>Nord-Grundschule</td>\n",
       "      <td>None</td>\n",
       "      <td>Unbestimmt</td>\n",
       "      <td>Modell/Förderprogramm/Projekt</td>\n",
       "      <td>Berliner Literaturinitiative (Projekt / Arbeit...</td>\n",
       "      <td>Grundschule</td>\n",
       "      <td>Grundschule</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>BE-15591</td>\n",
       "      <td>Nord-Grundschule</td>\n",
       "      <td>Polizei</td>\n",
       "      <td>Öffentliche Infrastruktur</td>\n",
       "      <td>Öffentliche Infrastruktur (Sicherheit/Ordnung/...</td>\n",
       "      <td>Berliner Polizei</td>\n",
       "      <td>Grundschule</td>\n",
       "      <td>Grundschule</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "         ID            Schule Type_detail                  Type_grob  \\\n",
       "0  BE-15591  Nord-Grundschule        None                 Unbestimmt   \n",
       "1  BE-15591  Nord-Grundschule     Polizei  Öffentliche Infrastruktur   \n",
       "\n",
       "                                         Type_mittel  \\\n",
       "0                      Modell/Förderprogramm/Projekt   \n",
       "1  Öffentliche Infrastruktur (Sicherheit/Ordnung/...   \n",
       "\n",
       "                                        partner_name  school_type  \\\n",
       "0  Berliner Literaturinitiative (Projekt / Arbeit...  Grundschule   \n",
       "1                                   Berliner Polizei  Grundschule   \n",
       "\n",
       "  Schooltype_broad  \n",
       "0      Grundschule  \n",
       "1      Grundschule  "
      ]
     },
     "execution_count": 42,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df_berlin_partner.head(2)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {
    "deletable": true,
    "editable": true
   },
   "source": [
    "#### Wie viele Akteure für die jeweilige Kategorie kommen auf  eine Schule in Berlin?"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 43,
   "metadata": {
    "collapsed": false,
    "deletable": true,
    "editable": true
   },
   "outputs": [],
   "source": [
    "dftyp = df_berlin_partner.drop_duplicates(subset=['ID','school_type'])\n",
    "dftyp = pd.DataFrame(dftyp.Schooltype_broad.value_counts())"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 44,
   "metadata": {
    "collapsed": false,
    "deletable": true,
    "editable": true
   },
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>Schooltype_broad</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>Grundschule</th>\n",
       "      <td>164</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Sekundarschule</th>\n",
       "      <td>98</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Gymnasium</th>\n",
       "      <td>92</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Berufliche Schule</th>\n",
       "      <td>36</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Förderschulen</th>\n",
       "      <td>16</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Sonstige</th>\n",
       "      <td>4</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "                   Schooltype_broad\n",
       "Grundschule                     164\n",
       "Sekundarschule                   98\n",
       "Gymnasium                        92\n",
       "Berufliche Schule                36\n",
       "Förderschulen                    16\n",
       "Sonstige                          4"
      ]
     },
     "execution_count": 44,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "dftyp"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {
    "deletable": true,
    "editable": true
   },
   "source": [
    "Hier errechnen wir die Anzahl der Schulen in Berlin für jeden Schultyp."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 45,
   "metadata": {
    "collapsed": false,
    "deletable": true,
    "editable": true
   },
   "outputs": [],
   "source": [
    "partner_schulen['Schulanzahl'] = dftyp"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 46,
   "metadata": {
    "collapsed": false,
    "deletable": true,
    "editable": true
   },
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th>Type_grob</th>\n",
       "      <th>Gemeinnütziger Akteur</th>\n",
       "      <th>Partnerschule</th>\n",
       "      <th>Religiöse Einrichtung</th>\n",
       "      <th>Unbestimmt</th>\n",
       "      <th>Verband / Kammer / Innung / Gewerkschaft</th>\n",
       "      <th>Wirtschaftsakteur</th>\n",
       "      <th>Öffentliche Infrastruktur</th>\n",
       "      <th>Schulanzahl</th>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Schooltype_broad</th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>Berufliche Schule</th>\n",
       "      <td>49.0</td>\n",
       "      <td>9.0</td>\n",
       "      <td>4.0</td>\n",
       "      <td>20.0</td>\n",
       "      <td>10.0</td>\n",
       "      <td>55.0</td>\n",
       "      <td>43.0</td>\n",
       "      <td>36</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Förderschulen</th>\n",
       "      <td>40.0</td>\n",
       "      <td>3.0</td>\n",
       "      <td>3.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>1.0</td>\n",
       "      <td>14.0</td>\n",
       "      <td>19.0</td>\n",
       "      <td>16</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "Type_grob          Gemeinnütziger Akteur  Partnerschule  \\\n",
       "Schooltype_broad                                          \n",
       "Berufliche Schule                   49.0            9.0   \n",
       "Förderschulen                       40.0            3.0   \n",
       "\n",
       "Type_grob          Religiöse Einrichtung  Unbestimmt  \\\n",
       "Schooltype_broad                                       \n",
       "Berufliche Schule                    4.0        20.0   \n",
       "Förderschulen                        3.0         0.0   \n",
       "\n",
       "Type_grob          Verband / Kammer / Innung / Gewerkschaft  \\\n",
       "Schooltype_broad                                              \n",
       "Berufliche Schule                                      10.0   \n",
       "Förderschulen                                           1.0   \n",
       "\n",
       "Type_grob          Wirtschaftsakteur  Öffentliche Infrastruktur  Schulanzahl  \n",
       "Schooltype_broad                                                              \n",
       "Berufliche Schule               55.0                       43.0           36  \n",
       "Förderschulen                   14.0                       19.0           16  "
      ]
     },
     "execution_count": 46,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "partner_schulen.head(2)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {
    "deletable": true,
    "editable": true
   },
   "source": [
    "Anschließend fügen wir die Anzahl der Schulen pro Schultyp an das zuvorerstellte Dataframe mit der Anzahl der Partnerschaften pro Schultyp."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 47,
   "metadata": {
    "collapsed": false,
    "deletable": true,
    "editable": true
   },
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th>Type_grob</th>\n",
       "      <th>Gemeinnütziger Akteur</th>\n",
       "      <th>Partnerschule</th>\n",
       "      <th>Religiöse Einrichtung</th>\n",
       "      <th>Unbestimmt</th>\n",
       "      <th>Verband / Kammer / Innung / Gewerkschaft</th>\n",
       "      <th>Wirtschaftsakteur</th>\n",
       "      <th>Öffentliche Infrastruktur</th>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Schooltype_broad</th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>Berufliche Schule</th>\n",
       "      <td>1.361111</td>\n",
       "      <td>0.250000</td>\n",
       "      <td>0.111111</td>\n",
       "      <td>0.555556</td>\n",
       "      <td>0.277778</td>\n",
       "      <td>1.527778</td>\n",
       "      <td>1.194444</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Förderschulen</th>\n",
       "      <td>2.500000</td>\n",
       "      <td>0.187500</td>\n",
       "      <td>0.187500</td>\n",
       "      <td>0.000000</td>\n",
       "      <td>0.062500</td>\n",
       "      <td>0.875000</td>\n",
       "      <td>1.187500</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Grundschule</th>\n",
       "      <td>3.073171</td>\n",
       "      <td>0.164634</td>\n",
       "      <td>0.091463</td>\n",
       "      <td>0.231707</td>\n",
       "      <td>0.018293</td>\n",
       "      <td>0.792683</td>\n",
       "      <td>2.500000</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Gymnasium</th>\n",
       "      <td>1.630435</td>\n",
       "      <td>1.826087</td>\n",
       "      <td>0.130435</td>\n",
       "      <td>0.293478</td>\n",
       "      <td>0.097826</td>\n",
       "      <td>1.347826</td>\n",
       "      <td>2.293478</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Sekundarschule</th>\n",
       "      <td>3.806122</td>\n",
       "      <td>0.377551</td>\n",
       "      <td>0.071429</td>\n",
       "      <td>0.346939</td>\n",
       "      <td>0.112245</td>\n",
       "      <td>2.071429</td>\n",
       "      <td>2.040816</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Sonstige</th>\n",
       "      <td>0.500000</td>\n",
       "      <td>0.500000</td>\n",
       "      <td>0.250000</td>\n",
       "      <td>0.000000</td>\n",
       "      <td>0.000000</td>\n",
       "      <td>1.750000</td>\n",
       "      <td>0.500000</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "Type_grob          Gemeinnütziger Akteur  Partnerschule  \\\n",
       "Schooltype_broad                                          \n",
       "Berufliche Schule               1.361111       0.250000   \n",
       "Förderschulen                   2.500000       0.187500   \n",
       "Grundschule                     3.073171       0.164634   \n",
       "Gymnasium                       1.630435       1.826087   \n",
       "Sekundarschule                  3.806122       0.377551   \n",
       "Sonstige                        0.500000       0.500000   \n",
       "\n",
       "Type_grob          Religiöse Einrichtung  Unbestimmt  \\\n",
       "Schooltype_broad                                       \n",
       "Berufliche Schule               0.111111    0.555556   \n",
       "Förderschulen                   0.187500    0.000000   \n",
       "Grundschule                     0.091463    0.231707   \n",
       "Gymnasium                       0.130435    0.293478   \n",
       "Sekundarschule                  0.071429    0.346939   \n",
       "Sonstige                        0.250000    0.000000   \n",
       "\n",
       "Type_grob          Verband / Kammer / Innung / Gewerkschaft  \\\n",
       "Schooltype_broad                                              \n",
       "Berufliche Schule                                  0.277778   \n",
       "Förderschulen                                      0.062500   \n",
       "Grundschule                                        0.018293   \n",
       "Gymnasium                                          0.097826   \n",
       "Sekundarschule                                     0.112245   \n",
       "Sonstige                                           0.000000   \n",
       "\n",
       "Type_grob          Wirtschaftsakteur  Öffentliche Infrastruktur  \n",
       "Schooltype_broad                                                 \n",
       "Berufliche Schule           1.527778                   1.194444  \n",
       "Förderschulen               0.875000                   1.187500  \n",
       "Grundschule                 0.792683                   2.500000  \n",
       "Gymnasium                   1.347826                   2.293478  \n",
       "Sekundarschule              2.071429                   2.040816  \n",
       "Sonstige                    1.750000                   0.500000  "
      ]
     },
     "execution_count": 47,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "partner_schulen[['Gemeinnütziger Akteur','Partnerschule','Religiöse Einrichtung', 'Unbestimmt', 'Verband / Kammer / Innung / Gewerkschaft', 'Wirtschaftsakteur', 'Öffentliche Infrastruktur']].div(partner_schulen.Schulanzahl, axis=0)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {
    "deletable": true,
    "editable": true
   },
   "source": [
    "Anschließend teilen wir die Anzahl der Partnerschaften pro Akteurskategorie durch die jeweilige Anzahl der Schulen pro Schultyp. Die obenstehende Tabelle zeigt also für jeden Schultyp wie viele Partnerschaften sie durchschnittlich pro Akteurskategorie eingeht."
   ]
  }
 ],
 "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.6.0"
  }
 },
 "nbformat": 4,
 "nbformat_minor": 1
}