notebooks/extract-city-from-MaStR-before20190201.ipynb

{
 "cells": [
  {
   "cell_type": "code",
   "execution_count": 1,
   "metadata": {},
   "outputs": [],
   "source": [
    "import pandas as pd"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 2,
   "metadata": {},
   "outputs": [],
   "source": [
    "city = 'Karlsruhe'"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "# Migration 1"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## Load Data"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 3,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "190703_MaStRDaten_MIGRATION_Teil1.csv\r\n",
      "190703_MaStRDaten_MIGRATION_Teil1.xlsx\r\n"
     ]
    }
   ],
   "source": [
    "!ls -1 '/data/MaStRDatenMigration1'"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 4,
   "metadata": {},
   "outputs": [
    {
     "name": "stderr",
     "output_type": "stream",
     "text": [
      "/opt/conda/lib/python3.7/site-packages/IPython/core/interactiveshell.py:3063: DtypeWarning: Columns (6,7,18,31,63,64,66,71) have mixed types.Specify dtype option on import or set low_memory=False.\n",
      "  interactivity=interactivity, compiler=compiler, result=result)\n"
     ]
    }
   ],
   "source": [
    "data = pd.read_csv('/data/MaStRDatenMigration1/190703_MaStRDaten_MIGRATION_Teil1.csv')"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## Extract city: *{{city}}*"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 5,
   "metadata": {},
   "outputs": [],
   "source": [
    "cityData = data[data[\"ENH_Ort\"] == city]"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 6,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "((1227, 106), (1227, 23))"
      ]
     },
     "execution_count": 6,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# remove empty columns\n",
    "cityDataClean = cityData.dropna(axis=1, how='all')\n",
    "(cityData.shape, cityDataClean.shape)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 7,
   "metadata": {
    "scrolled": false
   },
   "outputs": [
    {
     "data": {
      "text/plain": [
       "Solareinheit    1227\n",
       "Name: ENH_EinheitenTyp, dtype: int64"
      ]
     },
     "execution_count": 7,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "cityDataClean['ENH_EinheitenTyp'].value_counts()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 8,
   "metadata": {
    "scrolled": true
   },
   "outputs": [
    {
     "data": {
      "text/plain": [
       "Index(['Unnamed: 0', 'ENH_MastrID', 'ENH_MastrNummer', 'ENH_Systemstatus',\n",
       "       'ENH_Betriebsstatus', 'ENH_EinheitenTyp', 'ENH_InbetriebnahmeDatum',\n",
       "       'ENH_LetzteAktualisierung', 'ENH_Land', 'ENH_Plz', 'ENH_Ort',\n",
       "       'ENH_Standortangabe', 'ENH_Strasse', 'ENH_Hausnummer',\n",
       "       'ENH_Adresszusatz', 'ENH_Bundesland', 'ENH_Breitengrad',\n",
       "       'ENH_Laengengrad', 'ENH_Bruttoleistung', 'ENH_Nettonennleistung',\n",
       "       'ENH_SolarLage', 'ENH_Energietraeger',\n",
       "       'ENH_IsAngeschlossenAnHoechstOderHochspannungsnetz'],\n",
       "      dtype='object')"
      ]
     },
     "execution_count": 8,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "cityDataClean.columns"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### Restrict to entries before 2019-02-01\n",
    "See hint about filtering on [MaStR](https://www.bundesnetzagentur.de/DE/Sachgebiete/ElektrizitaetundGas/Unternehmen_Institutionen/DatenaustauschundMonitoring/Marktstammdatenregister/MaStR_node.html)."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 9,
   "metadata": {},
   "outputs": [],
   "source": [
    "cityDataCleanBefore20190201 = cityDataClean[cityDataClean['ENH_InbetriebnahmeDatum'] < '2019-02-01']"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 10,
   "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>Unnamed: 0</th>\n",
       "      <th>ENH_MastrID</th>\n",
       "      <th>ENH_MastrNummer</th>\n",
       "      <th>ENH_Systemstatus</th>\n",
       "      <th>ENH_Betriebsstatus</th>\n",
       "      <th>ENH_InbetriebnahmeDatum</th>\n",
       "      <th>ENH_LetzteAktualisierung</th>\n",
       "      <th>ENH_Land</th>\n",
       "      <th>ENH_Plz</th>\n",
       "      <th>ENH_Ort</th>\n",
       "      <th>...</th>\n",
       "      <th>ENH_Hausnummer</th>\n",
       "      <th>ENH_Adresszusatz</th>\n",
       "      <th>ENH_Bundesland</th>\n",
       "      <th>ENH_Breitengrad</th>\n",
       "      <th>ENH_Laengengrad</th>\n",
       "      <th>ENH_Bruttoleistung</th>\n",
       "      <th>ENH_Nettonennleistung</th>\n",
       "      <th>ENH_SolarLage</th>\n",
       "      <th>ENH_Energietraeger</th>\n",
       "      <th>ENH_IsAngeschlossenAnHoechstOderHochspannungsnetz</th>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>ENH_EinheitenTyp</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",
       "      <th></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",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>Solareinheit</th>\n",
       "      <td>1227</td>\n",
       "      <td>1227</td>\n",
       "      <td>1227</td>\n",
       "      <td>1227</td>\n",
       "      <td>1227</td>\n",
       "      <td>1227</td>\n",
       "      <td>1227</td>\n",
       "      <td>1227</td>\n",
       "      <td>1227</td>\n",
       "      <td>1227</td>\n",
       "      <td>...</td>\n",
       "      <td>122</td>\n",
       "      <td>7</td>\n",
       "      <td>1227</td>\n",
       "      <td>109</td>\n",
       "      <td>109</td>\n",
       "      <td>1227</td>\n",
       "      <td>1227</td>\n",
       "      <td>1164</td>\n",
       "      <td>1227</td>\n",
       "      <td>1227</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "<p>1 rows × 22 columns</p>\n",
       "</div>"
      ],
      "text/plain": [
       "                  Unnamed: 0  ENH_MastrID  ENH_MastrNummer  ENH_Systemstatus  \\\n",
       "ENH_EinheitenTyp                                                               \n",
       "Solareinheit            1227         1227             1227              1227   \n",
       "\n",
       "                  ENH_Betriebsstatus  ENH_InbetriebnahmeDatum  \\\n",
       "ENH_EinheitenTyp                                                \n",
       "Solareinheit                    1227                     1227   \n",
       "\n",
       "                  ENH_LetzteAktualisierung  ENH_Land  ENH_Plz  ENH_Ort  ...  \\\n",
       "ENH_EinheitenTyp                                                        ...   \n",
       "Solareinheit                          1227      1227     1227     1227  ...   \n",
       "\n",
       "                  ENH_Hausnummer  ENH_Adresszusatz  ENH_Bundesland  \\\n",
       "ENH_EinheitenTyp                                                     \n",
       "Solareinheit                 122                 7            1227   \n",
       "\n",
       "                  ENH_Breitengrad  ENH_Laengengrad  ENH_Bruttoleistung  \\\n",
       "ENH_EinheitenTyp                                                         \n",
       "Solareinheit                  109              109                1227   \n",
       "\n",
       "                  ENH_Nettonennleistung  ENH_SolarLage  ENH_Energietraeger  \\\n",
       "ENH_EinheitenTyp                                                             \n",
       "Solareinheit                       1227           1164                1227   \n",
       "\n",
       "                  ENH_IsAngeschlossenAnHoechstOderHochspannungsnetz  \n",
       "ENH_EinheitenTyp                                                     \n",
       "Solareinheit                                                   1227  \n",
       "\n",
       "[1 rows x 22 columns]"
      ]
     },
     "execution_count": 10,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "cityDataCleanBefore20190201.groupby(['ENH_EinheitenTyp']).count()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 11,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "Column: \"ENH_EinheitenTyp\"\n",
      "\n",
      " Solareinheit    1227\n",
      "Name: ENH_EinheitenTyp, dtype: int64 \n",
      "\n",
      "\n"
     ]
    }
   ],
   "source": [
    "for column in ['ENH_EinheitenTyp']:\n",
    "    print('Column: \"%s\"\\n\\n' % column, cityDataCleanBefore20190201[column].value_counts(), \"\\n\\n\")"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### Special Attributes\n",
    "Attributes that do NOT contain values for each row."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 12,
   "metadata": {
    "scrolled": true
   },
   "outputs": [
    {
     "data": {
      "text/plain": [
       "ENH_Strasse          122\n",
       "ENH_Hausnummer       122\n",
       "ENH_Adresszusatz       7\n",
       "ENH_Breitengrad      109\n",
       "ENH_Laengengrad      109\n",
       "ENH_SolarLage       1164\n",
       "dtype: int64"
      ]
     },
     "execution_count": 12,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "c = cityDataCleanBefore20190201.count()\n",
    "c[c.values < c.max()]"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### Save entries before 2019-02-01"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 13,
   "metadata": {
    "scrolled": false
   },
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "190703_MaStRDaten_MIGRATION_Teil1_Karlsruhe.csv\r\n",
      "190703_MaStRDaten_MIGRATION_Teil1_Karlsruhe_vor010219.csv\r\n",
      "190703_MaStRDaten_MIGRATION_Teil2_Karlsruhe.csv\r\n",
      "190703_MaStRDaten_MIGRATION_Teil2_Karlsruhe_vor010219.csv\r\n",
      "200303_MaStRDaten_ab310119_Karlsruhe.csv\r\n",
      "200303_MaStRDaten_vor010219_Karlsruhe.csv\r\n",
      "DatenAb310119\r\n",
      "DatenAb310119.zip\r\n",
      "download.sh\r\n",
      "MaStRDatenMigration1\r\n",
      "MaStRDatenMigration1.zip\r\n",
      "MaStRDatenMigration2\r\n",
      "MaStRDatenMigration2.zip\r\n",
      "MaStR-excerpt-Karlsruhe-PV-2020-06-01.csv\r\n",
      "README.md\r\n"
     ]
    }
   ],
   "source": [
    "!ls -1 /data"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 14,
   "metadata": {},
   "outputs": [],
   "source": [
    "cityDataCleanBefore20190201.to_csv(\"/data/190703_MaStRDaten_MIGRATION_Teil1_Karlsruhe_vor010219.csv\")"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "# Migration 2"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## Load Data"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 15,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "190703_MaStRDaten_MIGRATION_Teil2.csv\r\n",
      "190703_MaStRDaten_MIGRATION_Teil2.xlsx\r\n"
     ]
    }
   ],
   "source": [
    "!ls -1 '/data/MaStRDatenMigration2'"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 16,
   "metadata": {
    "scrolled": true
   },
   "outputs": [
    {
     "name": "stderr",
     "output_type": "stream",
     "text": [
      "/opt/conda/lib/python3.7/site-packages/IPython/core/interactiveshell.py:3063: DtypeWarning: Columns (6,7,8,9,12,18,19,23,24,25,26,30,31,49,59,63,64,66,71,72,90,99) have mixed types.Specify dtype option on import or set low_memory=False.\n",
      "  interactivity=interactivity, compiler=compiler, result=result)\n"
     ]
    }
   ],
   "source": [
    "data = pd.read_csv('/data/MaStRDatenMigration2/190703_MaStRDaten_MIGRATION_Teil2.csv')"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## Extract city: *{{city}}*"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 17,
   "metadata": {},
   "outputs": [],
   "source": [
    "cityData = data[data[\"ENH_Ort\"] == city]"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 18,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "((1208, 106), (1208, 39))"
      ]
     },
     "execution_count": 18,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# remove empty columns\n",
    "cityDataClean = cityData.dropna(axis=1, how='all')\n",
    "(cityData.shape, cityDataClean.shape)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 19,
   "metadata": {
    "scrolled": false
   },
   "outputs": [
    {
     "data": {
      "text/plain": [
       "Solareinheit            1119\n",
       "Stromspeichereinheit      49\n",
       "Verbrennung               27\n",
       "Biomasse                   6\n",
       "Windeinheit                5\n",
       "Wasser                     2\n",
       "Name: ENH_EinheitenTyp, dtype: int64"
      ]
     },
     "execution_count": 19,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "cityDataClean['ENH_EinheitenTyp'].value_counts()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 20,
   "metadata": {
    "scrolled": false
   },
   "outputs": [
    {
     "data": {
      "text/plain": [
       "Index(['Unnamed: 0', 'ENH_MastrID', 'ENH_MastrNummer', 'ENH_Systemstatus',\n",
       "       'ENH_Betriebsstatus', 'ENH_EinheitenTyp', 'ENH_NameEinheit',\n",
       "       'ENH_NameWindpark', 'ENH_InbetriebnahmeDatum',\n",
       "       'ENH_LetzteAktualisierung', 'ENH_Land', 'ENH_Lage', 'ENH_Plz',\n",
       "       'ENH_Ort', 'ENH_Standortangabe', 'ENH_Strasse', 'ENH_Hausnummer',\n",
       "       'ENH_Adresszusatz', 'ENH_Flurstueck', 'ENH_Bundesland',\n",
       "       'ENH_Gemeindeschluessel', 'ENH_Breitengrad', 'ENH_Laengengrad',\n",
       "       'ENH_Bruttoleistung', 'ENH_Nettonennleistung', 'ENH_SolarLage',\n",
       "       'ENH_Technologie', 'ENH_Nabenhoehe', 'ENH_Rotordurchmesser',\n",
       "       'ENH_Typenbezeichner', 'ENH_Energietraeger', 'ENH_Hauptbrennstoff',\n",
       "       'ENH_Biomasseart', 'ENH_IsFernsteuerbarNetzbetreiber',\n",
       "       'ENH_IsFernsteuerbarDirektvermarkter', 'ENH_IsFernsteuerbarDritte',\n",
       "       'ENH_IsAngeschlossenAnHoechstOderHochspannungsnetz',\n",
       "       'ENH_Kraftwerksnummer', 'ENH_Kopplung'],\n",
       "      dtype='object')"
      ]
     },
     "execution_count": 20,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "cityDataClean.columns"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### Restrict to entries before 2019-02-01\n",
    "See hint about filtering on [MaStR](https://www.bundesnetzagentur.de/DE/Sachgebiete/ElektrizitaetundGas/Unternehmen_Institutionen/DatenaustauschundMonitoring/Marktstammdatenregister/MaStR_node.html)."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 21,
   "metadata": {},
   "outputs": [],
   "source": [
    "cityDataCleanBefore20190201 = cityDataClean[cityDataClean['ENH_InbetriebnahmeDatum'] < '2019-02-01']"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 22,
   "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></th>\n",
       "      <th>Unnamed: 0</th>\n",
       "      <th>ENH_MastrID</th>\n",
       "      <th>ENH_MastrNummer</th>\n",
       "      <th>ENH_Systemstatus</th>\n",
       "      <th>ENH_Betriebsstatus</th>\n",
       "      <th>ENH_NameEinheit</th>\n",
       "      <th>ENH_NameWindpark</th>\n",
       "      <th>ENH_InbetriebnahmeDatum</th>\n",
       "      <th>ENH_LetzteAktualisierung</th>\n",
       "      <th>ENH_Land</th>\n",
       "      <th>...</th>\n",
       "      <th>ENH_Typenbezeichner</th>\n",
       "      <th>ENH_Energietraeger</th>\n",
       "      <th>ENH_Hauptbrennstoff</th>\n",
       "      <th>ENH_Biomasseart</th>\n",
       "      <th>ENH_IsFernsteuerbarNetzbetreiber</th>\n",
       "      <th>ENH_IsFernsteuerbarDirektvermarkter</th>\n",
       "      <th>ENH_IsFernsteuerbarDritte</th>\n",
       "      <th>ENH_IsAngeschlossenAnHoechstOderHochspannungsnetz</th>\n",
       "      <th>ENH_Kraftwerksnummer</th>\n",
       "      <th>ENH_Kopplung</th>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>ENH_EinheitenTyp</th>\n",
       "      <th>ENH_Technologie</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",
       "      <th></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",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>Stromspeichereinheit</th>\n",
       "      <th>Batterie</th>\n",
       "      <td>47</td>\n",
       "      <td>47</td>\n",
       "      <td>47</td>\n",
       "      <td>47</td>\n",
       "      <td>47</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>47</td>\n",
       "      <td>47</td>\n",
       "      <td>25</td>\n",
       "      <td>...</td>\n",
       "      <td>0</td>\n",
       "      <td>47</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>39</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Verbrennung</th>\n",
       "      <th>Kondensationsmaschine mit Entnahme</th>\n",
       "      <td>2</td>\n",
       "      <td>2</td>\n",
       "      <td>2</td>\n",
       "      <td>2</td>\n",
       "      <td>2</td>\n",
       "      <td>2</td>\n",
       "      <td>0</td>\n",
       "      <td>2</td>\n",
       "      <td>2</td>\n",
       "      <td>2</td>\n",
       "      <td>...</td>\n",
       "      <td>0</td>\n",
       "      <td>2</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>2</td>\n",
       "      <td>2</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "<p>2 rows × 37 columns</p>\n",
       "</div>"
      ],
      "text/plain": [
       "                                                         Unnamed: 0  \\\n",
       "ENH_EinheitenTyp     ENH_Technologie                                  \n",
       "Stromspeichereinheit Batterie                                    47   \n",
       "Verbrennung          Kondensationsmaschine mit Entnahme           2   \n",
       "\n",
       "                                                         ENH_MastrID  \\\n",
       "ENH_EinheitenTyp     ENH_Technologie                                   \n",
       "Stromspeichereinheit Batterie                                     47   \n",
       "Verbrennung          Kondensationsmaschine mit Entnahme            2   \n",
       "\n",
       "                                                         ENH_MastrNummer  \\\n",
       "ENH_EinheitenTyp     ENH_Technologie                                       \n",
       "Stromspeichereinheit Batterie                                         47   \n",
       "Verbrennung          Kondensationsmaschine mit Entnahme                2   \n",
       "\n",
       "                                                         ENH_Systemstatus  \\\n",
       "ENH_EinheitenTyp     ENH_Technologie                                        \n",
       "Stromspeichereinheit Batterie                                          47   \n",
       "Verbrennung          Kondensationsmaschine mit Entnahme                 2   \n",
       "\n",
       "                                                         ENH_Betriebsstatus  \\\n",
       "ENH_EinheitenTyp     ENH_Technologie                                          \n",
       "Stromspeichereinheit Batterie                                            47   \n",
       "Verbrennung          Kondensationsmaschine mit Entnahme                   2   \n",
       "\n",
       "                                                         ENH_NameEinheit  \\\n",
       "ENH_EinheitenTyp     ENH_Technologie                                       \n",
       "Stromspeichereinheit Batterie                                          0   \n",
       "Verbrennung          Kondensationsmaschine mit Entnahme                2   \n",
       "\n",
       "                                                         ENH_NameWindpark  \\\n",
       "ENH_EinheitenTyp     ENH_Technologie                                        \n",
       "Stromspeichereinheit Batterie                                           0   \n",
       "Verbrennung          Kondensationsmaschine mit Entnahme                 0   \n",
       "\n",
       "                                                         ENH_InbetriebnahmeDatum  \\\n",
       "ENH_EinheitenTyp     ENH_Technologie                                               \n",
       "Stromspeichereinheit Batterie                                                 47   \n",
       "Verbrennung          Kondensationsmaschine mit Entnahme                        2   \n",
       "\n",
       "                                                         ENH_LetzteAktualisierung  \\\n",
       "ENH_EinheitenTyp     ENH_Technologie                                                \n",
       "Stromspeichereinheit Batterie                                                  47   \n",
       "Verbrennung          Kondensationsmaschine mit Entnahme                         2   \n",
       "\n",
       "                                                         ENH_Land  ...  \\\n",
       "ENH_EinheitenTyp     ENH_Technologie                               ...   \n",
       "Stromspeichereinheit Batterie                                  25  ...   \n",
       "Verbrennung          Kondensationsmaschine mit Entnahme         2  ...   \n",
       "\n",
       "                                                         ENH_Typenbezeichner  \\\n",
       "ENH_EinheitenTyp     ENH_Technologie                                           \n",
       "Stromspeichereinheit Batterie                                              0   \n",
       "Verbrennung          Kondensationsmaschine mit Entnahme                    0   \n",
       "\n",
       "                                                         ENH_Energietraeger  \\\n",
       "ENH_EinheitenTyp     ENH_Technologie                                          \n",
       "Stromspeichereinheit Batterie                                            47   \n",
       "Verbrennung          Kondensationsmaschine mit Entnahme                   2   \n",
       "\n",
       "                                                         ENH_Hauptbrennstoff  \\\n",
       "ENH_EinheitenTyp     ENH_Technologie                                           \n",
       "Stromspeichereinheit Batterie                                              0   \n",
       "Verbrennung          Kondensationsmaschine mit Entnahme                    0   \n",
       "\n",
       "                                                         ENH_Biomasseart  \\\n",
       "ENH_EinheitenTyp     ENH_Technologie                                       \n",
       "Stromspeichereinheit Batterie                                          0   \n",
       "Verbrennung          Kondensationsmaschine mit Entnahme                0   \n",
       "\n",
       "                                                         ENH_IsFernsteuerbarNetzbetreiber  \\\n",
       "ENH_EinheitenTyp     ENH_Technologie                                                        \n",
       "Stromspeichereinheit Batterie                                                           0   \n",
       "Verbrennung          Kondensationsmaschine mit Entnahme                                 0   \n",
       "\n",
       "                                                         ENH_IsFernsteuerbarDirektvermarkter  \\\n",
       "ENH_EinheitenTyp     ENH_Technologie                                                           \n",
       "Stromspeichereinheit Batterie                                                              0   \n",
       "Verbrennung          Kondensationsmaschine mit Entnahme                                    0   \n",
       "\n",
       "                                                         ENH_IsFernsteuerbarDritte  \\\n",
       "ENH_EinheitenTyp     ENH_Technologie                                                 \n",
       "Stromspeichereinheit Batterie                                                    0   \n",
       "Verbrennung          Kondensationsmaschine mit Entnahme                          0   \n",
       "\n",
       "                                                         ENH_IsAngeschlossenAnHoechstOderHochspannungsnetz  \\\n",
       "ENH_EinheitenTyp     ENH_Technologie                                                                         \n",
       "Stromspeichereinheit Batterie                                                                            0   \n",
       "Verbrennung          Kondensationsmaschine mit Entnahme                                                  2   \n",
       "\n",
       "                                                         ENH_Kraftwerksnummer  \\\n",
       "ENH_EinheitenTyp     ENH_Technologie                                            \n",
       "Stromspeichereinheit Batterie                                               0   \n",
       "Verbrennung          Kondensationsmaschine mit Entnahme                     2   \n",
       "\n",
       "                                                         ENH_Kopplung  \n",
       "ENH_EinheitenTyp     ENH_Technologie                                   \n",
       "Stromspeichereinheit Batterie                                      39  \n",
       "Verbrennung          Kondensationsmaschine mit Entnahme             0  \n",
       "\n",
       "[2 rows x 37 columns]"
      ]
     },
     "execution_count": 22,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "cityDataCleanBefore20190201.groupby(['ENH_EinheitenTyp', 'ENH_Technologie']).count()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 23,
   "metadata": {
    "scrolled": true
   },
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "Column: \"ENH_EinheitenTyp\"\n",
      "\n",
      " Solareinheit            1119\n",
      "Stromspeichereinheit      49\n",
      "Verbrennung               27\n",
      "Biomasse                   6\n",
      "Windeinheit                5\n",
      "Wasser                     2\n",
      "Name: ENH_EinheitenTyp, dtype: int64 \n",
      "\n",
      "\n",
      "Column: \"ENH_Technologie\"\n",
      "\n",
      " Batterie                              47\n",
      "Kondensationsmaschine mit Entnahme     2\n",
      "Name: ENH_Technologie, dtype: int64 \n",
      "\n",
      "\n"
     ]
    }
   ],
   "source": [
    "for column in ['ENH_EinheitenTyp', 'ENH_Technologie']:\n",
    "    print('Column: \"%s\"\\n\\n' % column, cityDataCleanBefore20190201[column].value_counts(), \"\\n\\n\")"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### Special Attributes\n",
    "Attributes that do NOT contain values for each row."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 24,
   "metadata": {
    "scrolled": false
   },
   "outputs": [
    {
     "data": {
      "text/plain": [
       "ENH_NameEinheit                                         9\n",
       "ENH_NameWindpark                                        1\n",
       "ENH_Land                                             1186\n",
       "ENH_Lage                                                6\n",
       "ENH_Strasse                                            81\n",
       "ENH_Hausnummer                                         76\n",
       "ENH_Adresszusatz                                        2\n",
       "ENH_Flurstueck                                          2\n",
       "ENH_Gemeindeschluessel                                  3\n",
       "ENH_Breitengrad                                        66\n",
       "ENH_Laengengrad                                        66\n",
       "ENH_SolarLage                                         684\n",
       "ENH_Technologie                                        49\n",
       "ENH_Nabenhoehe                                          1\n",
       "ENH_Rotordurchmesser                                    1\n",
       "ENH_Typenbezeichner                                     1\n",
       "ENH_Hauptbrennstoff                                    23\n",
       "ENH_Biomasseart                                         1\n",
       "ENH_IsFernsteuerbarNetzbetreiber                        5\n",
       "ENH_IsFernsteuerbarDirektvermarkter                     5\n",
       "ENH_IsFernsteuerbarDritte                               5\n",
       "ENH_IsAngeschlossenAnHoechstOderHochspannungsnetz     991\n",
       "ENH_Kraftwerksnummer                                    7\n",
       "ENH_Kopplung                                           39\n",
       "dtype: int64"
      ]
     },
     "execution_count": 24,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "c = cityDataCleanBefore20190201.count()\n",
    "c[c.values < c.max()]"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### Save entries before 2019-02-01"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 25,
   "metadata": {
    "scrolled": false
   },
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "total 627M\r\n",
      "-rw-r--r-- 1 jovyan  1000 315K Jun 14 18:46 190703_MaStRDaten_MIGRATION_Teil1_Karlsruhe.csv\r\n",
      "-rw-r--r-- 1 jovyan users 324K Jun 23 23:49 190703_MaStRDaten_MIGRATION_Teil1_Karlsruhe_vor010219.csv\r\n",
      "-rw-r--r-- 1 jovyan  1000 309K Jun 14 19:16 190703_MaStRDaten_MIGRATION_Teil2_Karlsruhe.csv\r\n",
      "-rw-r--r-- 1 jovyan users 318K Jun 23 23:48 190703_MaStRDaten_MIGRATION_Teil2_Karlsruhe_vor010219.csv\r\n",
      "-rw-r--r-- 1 jovyan users 155K Jun 20 13:25 200303_MaStRDaten_ab310119_Karlsruhe.csv\r\n",
      "-rw-r--r-- 1 jovyan users 348K Jun 20 13:25 200303_MaStRDaten_vor010219_Karlsruhe.csv\r\n",
      "drwxr-xr-x 2 jovyan  1000 4.0K Jun 16 20:41 DatenAb310119\r\n",
      "-rw-r--r-- 1 jovyan  1000 307M Jun 11 18:07 DatenAb310119.zip\r\n",
      "-rwxr-xr-x 1 jovyan  1000  769 Jun 17 22:59 download.sh\r\n",
      "drwxr-xr-x 2 jovyan  1000 4.0K Jun 23 23:19 MaStRDatenMigration1\r\n",
      "-rw-r--r-- 1 jovyan  1000 185M Jun 11 18:07 MaStRDatenMigration1.zip\r\n",
      "drwxr-xr-x 2 jovyan  1000 4.0K Jun 16 21:18 MaStRDatenMigration2\r\n",
      "-rw-r--r-- 1 jovyan  1000 134M Jun 11 18:07 MaStRDatenMigration2.zip\r\n",
      "-rw-r--r-- 1 jovyan  1000 503K Jun 11 18:07 MaStR-excerpt-Karlsruhe-PV-2020-06-01.csv\r\n",
      "-rw-r--r-- 1 jovyan  1000   62 Jun 17 21:41 README.md\r\n"
     ]
    }
   ],
   "source": [
    "!ls -1lh /data"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 26,
   "metadata": {},
   "outputs": [],
   "source": [
    "cityDataCleanBefore20190201.to_csv(\"/data/190703_MaStRDaten_MIGRATION_Teil2_Karlsruhe_vor010219.csv\")"
   ]
  }
 ],
 "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.6"
  }
 },
 "nbformat": 4,
 "nbformat_minor": 4
}