exploration.ipynb

{
 "cells": [
  {
   "attachments": {},
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "# Data Exploration\n",
    "\n",
    "In this notebook describe your data exploration steps."
   ]
  },
  {
   "attachments": {},
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## Install dependencies"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 1,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "Requirement already satisfied: pandas in /Applications/anaconda3/envs/py39/lib/python3.9/site-packages (2.0.1)\n",
      "Requirement already satisfied: pytz>=2020.1 in /Applications/anaconda3/envs/py39/lib/python3.9/site-packages (from pandas) (2023.3)\n",
      "Requirement already satisfied: python-dateutil>=2.8.2 in /Applications/anaconda3/envs/py39/lib/python3.9/site-packages (from pandas) (2.8.2)\n",
      "Requirement already satisfied: tzdata>=2022.1 in /Applications/anaconda3/envs/py39/lib/python3.9/site-packages (from pandas) (2023.3)\n",
      "Requirement already satisfied: numpy>=1.20.3 in /Applications/anaconda3/envs/py39/lib/python3.9/site-packages (from pandas) (1.24.3)\n",
      "Requirement already satisfied: six>=1.5 in /Applications/anaconda3/envs/py39/lib/python3.9/site-packages (from python-dateutil>=2.8.2->pandas) (1.16.0)\n",
      "Note: you may need to restart the kernel to use updated packages.\n",
      "\u001b[31mERROR: Could not find a version that satisfies the requirement sqlite3 (from versions: none)\u001b[0m\u001b[31m\n",
      "\u001b[0m\u001b[31mERROR: No matching distribution found for sqlite3\u001b[0m\u001b[31m\n",
      "\u001b[0mNote: you may need to restart the kernel to use updated packages.\n",
      "Requirement already satisfied: SQLAlchemy in /Applications/anaconda3/envs/py39/lib/python3.9/site-packages (2.0.15)\n",
      "Requirement already satisfied: typing-extensions>=4.2.0 in /Applications/anaconda3/envs/py39/lib/python3.9/site-packages (from SQLAlchemy) (4.6.0)\n",
      "Requirement already satisfied: greenlet!=0.4.17 in /Applications/anaconda3/envs/py39/lib/python3.9/site-packages (from SQLAlchemy) (2.0.2)\n",
      "Note: you may need to restart the kernel to use updated packages.\n"
     ]
    }
   ],
   "source": [
    "%pip install pandas\n",
    "%pip install sqlite3\n",
    "%pip install SQLAlchemy"
   ]
  },
  {
   "attachments": {},
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## Load data"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 2,
   "metadata": {},
   "outputs": [],
   "source": [
    "import pandas as pd\n",
    "import sqlite3 as db\n",
    "conn = db.connect('./data/database/data.db')\n",
    "\n",
    "# Setting queries for loading data\n",
    "query_1 = f\"SELECT * FROM table1\"\n",
    "query_2 = f\"SELECT * FROM table2\"\n",
    "\n",
    "# load data from the database into pandas dataframe\n",
    "df_1 = pd.read_sql_query(query_1, conn)\n",
    "df_2 = pd.read_sql_query(query_2, conn)"
   ]
  },
  {
   "attachments": {},
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### Look at the first rows"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 3,
   "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>betreiber</th>\n",
       "      <th>art_der_ladeeinrichung</th>\n",
       "      <th>anzahl_ladepunkte</th>\n",
       "      <th>anschlussleistung</th>\n",
       "      <th>steckertypen1</th>\n",
       "      <th>steckertypen2</th>\n",
       "      <th>steckertypen3</th>\n",
       "      <th>steckertypen4</th>\n",
       "      <th>p1_kw</th>\n",
       "      <th>p2_kw</th>\n",
       "      <th>p3_kw</th>\n",
       "      <th>p4_kw</th>\n",
       "      <th>kreis_kreisfreie_stadt</th>\n",
       "      <th>ort</th>\n",
       "      <th>postleitzahl</th>\n",
       "      <th>strasse</th>\n",
       "      <th>hausnummer</th>\n",
       "      <th>adresszusatz</th>\n",
       "      <th>inbetriebnahmedatum</th>\n",
       "      <th>koordinaten</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>Technische Werke Schussental GmbH &amp; Co. KG</td>\n",
       "      <td>Normalladeeinrichtung</td>\n",
       "      <td>2</td>\n",
       "      <td>22.0</td>\n",
       "      <td>AC Steckdose Typ 2</td>\n",
       "      <td>AC Steckdose Typ 2</td>\n",
       "      <td>None</td>\n",
       "      <td>None</td>\n",
       "      <td>22.0</td>\n",
       "      <td>22.0</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>Landkreis Ravensburg</td>\n",
       "      <td>Ravensburg</td>\n",
       "      <td>88212</td>\n",
       "      <td>Elisabethenstr.</td>\n",
       "      <td>10</td>\n",
       "      <td>Gymnasien 4</td>\n",
       "      <td>2021-09-28</td>\n",
       "      <td>47.785013, 9.619366</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>Technische Werke Schussental GmbH &amp; Co. KG</td>\n",
       "      <td>Normalladeeinrichtung</td>\n",
       "      <td>2</td>\n",
       "      <td>22.0</td>\n",
       "      <td>AC Steckdose Typ 2</td>\n",
       "      <td>AC Steckdose Typ 2</td>\n",
       "      <td>None</td>\n",
       "      <td>None</td>\n",
       "      <td>22.0</td>\n",
       "      <td>22.0</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>Landkreis Ravensburg</td>\n",
       "      <td>Ravensburg</td>\n",
       "      <td>88212</td>\n",
       "      <td>Kuppelnaustr.</td>\n",
       "      <td>15</td>\n",
       "      <td>Kuppelnauschule 4</td>\n",
       "      <td>2021-09-28</td>\n",
       "      <td>47.788212, 9.614901</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>Technische Werke Schussental GmbH &amp; Co. KG</td>\n",
       "      <td>Normalladeeinrichtung</td>\n",
       "      <td>2</td>\n",
       "      <td>22.0</td>\n",
       "      <td>AC Steckdose Typ 2</td>\n",
       "      <td>AC Steckdose Typ 2</td>\n",
       "      <td>None</td>\n",
       "      <td>None</td>\n",
       "      <td>22.0</td>\n",
       "      <td>22.0</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>Landkreis Ravensburg</td>\n",
       "      <td>Ravensburg</td>\n",
       "      <td>88212</td>\n",
       "      <td>BleicherstraÃe</td>\n",
       "      <td>20</td>\n",
       "      <td>Oberschwabenhalle</td>\n",
       "      <td>2021-09-28</td>\n",
       "      <td>47.790771, 9.613788</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>Technische Werke Schussental GmbH &amp; Co. KG</td>\n",
       "      <td>Normalladeeinrichtung</td>\n",
       "      <td>1</td>\n",
       "      <td>22.0</td>\n",
       "      <td>AC Steckdose Typ 2</td>\n",
       "      <td>None</td>\n",
       "      <td>None</td>\n",
       "      <td>None</td>\n",
       "      <td>22.0</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>Landkreis Ravensburg</td>\n",
       "      <td>Ravensburg</td>\n",
       "      <td>88212</td>\n",
       "      <td>Marienplatz</td>\n",
       "      <td>2/1</td>\n",
       "      <td>P1 Marienplatz-TG</td>\n",
       "      <td>2021-09-28</td>\n",
       "      <td>47.779743, 9.61319</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>Technische Werke Schussental GmbH &amp; Co. KG</td>\n",
       "      <td>Normalladeeinrichtung</td>\n",
       "      <td>1</td>\n",
       "      <td>22.0</td>\n",
       "      <td>AC Steckdose Typ 2</td>\n",
       "      <td>None</td>\n",
       "      <td>None</td>\n",
       "      <td>None</td>\n",
       "      <td>22.0</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>Landkreis Ravensburg</td>\n",
       "      <td>Ravensburg</td>\n",
       "      <td>88212</td>\n",
       "      <td>Rauneggstr.</td>\n",
       "      <td>1/1</td>\n",
       "      <td>P4 Rauenegg-PH</td>\n",
       "      <td>2021-09-28</td>\n",
       "      <td>47.781416, 9.618929</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>5</th>\n",
       "      <td>Technische Werke Schussental GmbH &amp; Co. KG</td>\n",
       "      <td>Normalladeeinrichtung</td>\n",
       "      <td>1</td>\n",
       "      <td>22.0</td>\n",
       "      <td>AC Steckdose Typ 2</td>\n",
       "      <td>None</td>\n",
       "      <td>None</td>\n",
       "      <td>None</td>\n",
       "      <td>22.0</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>Landkreis Ravensburg</td>\n",
       "      <td>Ravensburg</td>\n",
       "      <td>88212</td>\n",
       "      <td>Bahnhofplatz</td>\n",
       "      <td>2</td>\n",
       "      <td>P7 Bahnstadt-PH</td>\n",
       "      <td>2021-09-28</td>\n",
       "      <td>47.782248, 9.605752</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>6</th>\n",
       "      <td>Technische Werke Schussental GmbH &amp; Co. KG</td>\n",
       "      <td>Normalladeeinrichtung</td>\n",
       "      <td>2</td>\n",
       "      <td>22.0</td>\n",
       "      <td>AC Steckdose Typ 2</td>\n",
       "      <td>AC Steckdose Typ 2</td>\n",
       "      <td>None</td>\n",
       "      <td>None</td>\n",
       "      <td>22.0</td>\n",
       "      <td>22.0</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>Landkreis Ravensburg</td>\n",
       "      <td>Ravensburg</td>\n",
       "      <td>88212</td>\n",
       "      <td>St. Christina</td>\n",
       "      <td>1</td>\n",
       "      <td>St. Christina 1 Schulparkplatz</td>\n",
       "      <td>2021-09-28</td>\n",
       "      <td>47.7754187, 9.612971217</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>7</th>\n",
       "      <td>Lidl</td>\n",
       "      <td>Schnellladeeinrichtung</td>\n",
       "      <td>3</td>\n",
       "      <td>75.0</td>\n",
       "      <td>AC Kupplung Typ 2, DC Kupplung Combo</td>\n",
       "      <td>AC Kupplung Typ 2, DC CHAdeMO</td>\n",
       "      <td>AC Steckdose Typ 2, AC Kupplung Typ 2</td>\n",
       "      <td>None</td>\n",
       "      <td>55.0</td>\n",
       "      <td>50.0</td>\n",
       "      <td>22.0</td>\n",
       "      <td>NaN</td>\n",
       "      <td>Landkreis Ravensburg</td>\n",
       "      <td>Ravensburg</td>\n",
       "      <td>88212</td>\n",
       "      <td>Ulmerstr.</td>\n",
       "      <td>7</td>\n",
       "      <td>None</td>\n",
       "      <td>2020-10-27</td>\n",
       "      <td>47.787302, 9.609988</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>8</th>\n",
       "      <td>Citywatt GmbH</td>\n",
       "      <td>Schnellladeeinrichtung</td>\n",
       "      <td>2</td>\n",
       "      <td>300.0</td>\n",
       "      <td>DC Kupplung Combo</td>\n",
       "      <td>DC Kupplung Combo</td>\n",
       "      <td>None</td>\n",
       "      <td>None</td>\n",
       "      <td>300.0</td>\n",
       "      <td>300.0</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>Landkreis Ravensburg</td>\n",
       "      <td>Ravensburg</td>\n",
       "      <td>88212</td>\n",
       "      <td>BleicherstraÃe</td>\n",
       "      <td>53</td>\n",
       "      <td>None</td>\n",
       "      <td>2022-09-06</td>\n",
       "      <td>47.79409191, 9.613287363</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>9</th>\n",
       "      <td>Citywatt GmbH</td>\n",
       "      <td>Schnellladeeinrichtung</td>\n",
       "      <td>2</td>\n",
       "      <td>300.0</td>\n",
       "      <td>DC Kupplung Combo</td>\n",
       "      <td>DC Kupplung Combo</td>\n",
       "      <td>None</td>\n",
       "      <td>None</td>\n",
       "      <td>300.0</td>\n",
       "      <td>300.0</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>Landkreis Ravensburg</td>\n",
       "      <td>Ravensburg</td>\n",
       "      <td>88212</td>\n",
       "      <td>BleicherstraÃe</td>\n",
       "      <td>53</td>\n",
       "      <td>None</td>\n",
       "      <td>2022-09-06</td>\n",
       "      <td>47.79423446, 9.613337517</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "                                    betreiber  art_der_ladeeinrichung   \n",
       "0  Technische Werke Schussental GmbH & Co. KG   Normalladeeinrichtung  \\\n",
       "1  Technische Werke Schussental GmbH & Co. KG   Normalladeeinrichtung   \n",
       "2  Technische Werke Schussental GmbH & Co. KG   Normalladeeinrichtung   \n",
       "3  Technische Werke Schussental GmbH & Co. KG   Normalladeeinrichtung   \n",
       "4  Technische Werke Schussental GmbH & Co. KG   Normalladeeinrichtung   \n",
       "5  Technische Werke Schussental GmbH & Co. KG   Normalladeeinrichtung   \n",
       "6  Technische Werke Schussental GmbH & Co. KG   Normalladeeinrichtung   \n",
       "7                                        Lidl  Schnellladeeinrichtung   \n",
       "8                               Citywatt GmbH  Schnellladeeinrichtung   \n",
       "9                               Citywatt GmbH  Schnellladeeinrichtung   \n",
       "\n",
       "   anzahl_ladepunkte  anschlussleistung                         steckertypen1   \n",
       "0                  2               22.0                    AC Steckdose Typ 2  \\\n",
       "1                  2               22.0                    AC Steckdose Typ 2   \n",
       "2                  2               22.0                    AC Steckdose Typ 2   \n",
       "3                  1               22.0                    AC Steckdose Typ 2   \n",
       "4                  1               22.0                    AC Steckdose Typ 2   \n",
       "5                  1               22.0                    AC Steckdose Typ 2   \n",
       "6                  2               22.0                    AC Steckdose Typ 2   \n",
       "7                  3               75.0  AC Kupplung Typ 2, DC Kupplung Combo   \n",
       "8                  2              300.0                     DC Kupplung Combo   \n",
       "9                  2              300.0                     DC Kupplung Combo   \n",
       "\n",
       "                   steckertypen2                          steckertypen3   \n",
       "0             AC Steckdose Typ 2                                   None  \\\n",
       "1             AC Steckdose Typ 2                                   None   \n",
       "2             AC Steckdose Typ 2                                   None   \n",
       "3                           None                                   None   \n",
       "4                           None                                   None   \n",
       "5                           None                                   None   \n",
       "6             AC Steckdose Typ 2                                   None   \n",
       "7  AC Kupplung Typ 2, DC CHAdeMO  AC Steckdose Typ 2, AC Kupplung Typ 2   \n",
       "8              DC Kupplung Combo                                   None   \n",
       "9              DC Kupplung Combo                                   None   \n",
       "\n",
       "  steckertypen4  p1_kw  p2_kw  p3_kw  p4_kw kreis_kreisfreie_stadt   \n",
       "0          None   22.0   22.0    NaN    NaN   Landkreis Ravensburg  \\\n",
       "1          None   22.0   22.0    NaN    NaN   Landkreis Ravensburg   \n",
       "2          None   22.0   22.0    NaN    NaN   Landkreis Ravensburg   \n",
       "3          None   22.0    NaN    NaN    NaN   Landkreis Ravensburg   \n",
       "4          None   22.0    NaN    NaN    NaN   Landkreis Ravensburg   \n",
       "5          None   22.0    NaN    NaN    NaN   Landkreis Ravensburg   \n",
       "6          None   22.0   22.0    NaN    NaN   Landkreis Ravensburg   \n",
       "7          None   55.0   50.0   22.0    NaN   Landkreis Ravensburg   \n",
       "8          None  300.0  300.0    NaN    NaN   Landkreis Ravensburg   \n",
       "9          None  300.0  300.0    NaN    NaN   Landkreis Ravensburg   \n",
       "\n",
       "          ort  postleitzahl          strasse hausnummer   \n",
       "0  Ravensburg         88212  Elisabethenstr.         10  \\\n",
       "1  Ravensburg         88212    Kuppelnaustr.         15   \n",
       "2  Ravensburg         88212  BleicherstraÃe         20   \n",
       "3  Ravensburg         88212      Marienplatz        2/1   \n",
       "4  Ravensburg         88212      Rauneggstr.        1/1   \n",
       "5  Ravensburg         88212     Bahnhofplatz          2   \n",
       "6  Ravensburg         88212    St. Christina          1   \n",
       "7  Ravensburg         88212        Ulmerstr.          7   \n",
       "8  Ravensburg         88212  BleicherstraÃe         53   \n",
       "9  Ravensburg         88212  BleicherstraÃe         53   \n",
       "\n",
       "                     adresszusatz inbetriebnahmedatum   \n",
       "0                     Gymnasien 4          2021-09-28  \\\n",
       "1               Kuppelnauschule 4          2021-09-28   \n",
       "2               Oberschwabenhalle          2021-09-28   \n",
       "3               P1 Marienplatz-TG          2021-09-28   \n",
       "4                  P4 Rauenegg-PH          2021-09-28   \n",
       "5                 P7 Bahnstadt-PH          2021-09-28   \n",
       "6  St. Christina 1 Schulparkplatz          2021-09-28   \n",
       "7                            None          2020-10-27   \n",
       "8                            None          2022-09-06   \n",
       "9                            None          2022-09-06   \n",
       "\n",
       "                koordinaten  \n",
       "0       47.785013, 9.619366  \n",
       "1       47.788212, 9.614901  \n",
       "2       47.790771, 9.613788  \n",
       "3        47.779743, 9.61319  \n",
       "4       47.781416, 9.618929  \n",
       "5       47.782248, 9.605752  \n",
       "6   47.7754187, 9.612971217  \n",
       "7       47.787302, 9.609988  \n",
       "8  47.79409191, 9.613287363  \n",
       "9  47.79423446, 9.613337517  "
      ]
     },
     "execution_count": 3,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df_1.head(10)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 4,
   "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>Betreiber</th>\n",
       "      <th>Straße</th>\n",
       "      <th>Hausnummer</th>\n",
       "      <th>Adresszusatz</th>\n",
       "      <th>Postleitzahl</th>\n",
       "      <th>Ort</th>\n",
       "      <th>Bundesland</th>\n",
       "      <th>Kreis/kreisfreie Stadt</th>\n",
       "      <th>Breitengrad</th>\n",
       "      <th>Längengrad</th>\n",
       "      <th>...</th>\n",
       "      <th>Public Key1</th>\n",
       "      <th>Steckertypen2</th>\n",
       "      <th>P2 [kW]</th>\n",
       "      <th>Public Key2</th>\n",
       "      <th>Steckertypen3</th>\n",
       "      <th>P3 [kW]</th>\n",
       "      <th>Public Key3</th>\n",
       "      <th>Steckertypen4</th>\n",
       "      <th>P4 [kW]</th>\n",
       "      <th>Public Key4</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>Albwerk GmbH &amp; Co. KG</td>\n",
       "      <td>Ennabeurer Weg</td>\n",
       "      <td>0</td>\n",
       "      <td>None</td>\n",
       "      <td>72535</td>\n",
       "      <td>Heroldstatt</td>\n",
       "      <td>Baden-Württemberg</td>\n",
       "      <td>Landkreis Alb-Donau-Kreis</td>\n",
       "      <td>48.442398</td>\n",
       "      <td>9.659075</td>\n",
       "      <td>...</td>\n",
       "      <td>None</td>\n",
       "      <td>AC Steckdose Typ 2</td>\n",
       "      <td>22.0</td>\n",
       "      <td>None</td>\n",
       "      <td>None</td>\n",
       "      <td>NaN</td>\n",
       "      <td>None</td>\n",
       "      <td>None</td>\n",
       "      <td>NaN</td>\n",
       "      <td>None</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>smopi®</td>\n",
       "      <td>Albstraße</td>\n",
       "      <td>14</td>\n",
       "      <td>None</td>\n",
       "      <td>72535</td>\n",
       "      <td>Heroldstatt</td>\n",
       "      <td>Baden-Württemberg</td>\n",
       "      <td>Landkreis Alb-Donau-Kreis</td>\n",
       "      <td>48.449353</td>\n",
       "      <td>9.672201</td>\n",
       "      <td>...</td>\n",
       "      <td>None</td>\n",
       "      <td>AC Steckdose Typ 2</td>\n",
       "      <td>22.0</td>\n",
       "      <td>None</td>\n",
       "      <td>AC Steckdose Typ 2</td>\n",
       "      <td>22.0</td>\n",
       "      <td>None</td>\n",
       "      <td>AC Steckdose Typ 2</td>\n",
       "      <td>22.0</td>\n",
       "      <td>None</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>Albwerk GmbH &amp; Co. KG</td>\n",
       "      <td>Parkplatz Campingplatz</td>\n",
       "      <td>0</td>\n",
       "      <td>None</td>\n",
       "      <td>72589</td>\n",
       "      <td>Westerheim</td>\n",
       "      <td>Baden-Württemberg</td>\n",
       "      <td>Landkreis Alb-Donau-Kreis</td>\n",
       "      <td>48.510500</td>\n",
       "      <td>9.609000</td>\n",
       "      <td>...</td>\n",
       "      <td>None</td>\n",
       "      <td>AC Steckdose Typ 2</td>\n",
       "      <td>22.0</td>\n",
       "      <td>None</td>\n",
       "      <td>None</td>\n",
       "      <td>NaN</td>\n",
       "      <td>None</td>\n",
       "      <td>None</td>\n",
       "      <td>NaN</td>\n",
       "      <td>None</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>EnBW mobility+ AG und Co.KG</td>\n",
       "      <td>Hauptstraße</td>\n",
       "      <td>91c</td>\n",
       "      <td>None</td>\n",
       "      <td>73340</td>\n",
       "      <td>Amstetten</td>\n",
       "      <td>Baden-Württemberg</td>\n",
       "      <td>Landkreis Alb-Donau-Kreis</td>\n",
       "      <td>48.578534</td>\n",
       "      <td>9.874840</td>\n",
       "      <td>...</td>\n",
       "      <td>None</td>\n",
       "      <td>DC Kupplung Combo, DC CHAdeMO</td>\n",
       "      <td>50.0</td>\n",
       "      <td>None</td>\n",
       "      <td>None</td>\n",
       "      <td>NaN</td>\n",
       "      <td>None</td>\n",
       "      <td>None</td>\n",
       "      <td>NaN</td>\n",
       "      <td>None</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>SWU Energie GmbH</td>\n",
       "      <td>Sterngasse</td>\n",
       "      <td>6</td>\n",
       "      <td>None</td>\n",
       "      <td>88481</td>\n",
       "      <td>Balzheim</td>\n",
       "      <td>Baden-Württemberg</td>\n",
       "      <td>Landkreis Alb-Donau-Kreis</td>\n",
       "      <td>48.179959</td>\n",
       "      <td>10.076977</td>\n",
       "      <td>...</td>\n",
       "      <td>None</td>\n",
       "      <td>None</td>\n",
       "      <td>NaN</td>\n",
       "      <td>None</td>\n",
       "      <td>None</td>\n",
       "      <td>NaN</td>\n",
       "      <td>None</td>\n",
       "      <td>None</td>\n",
       "      <td>NaN</td>\n",
       "      <td>None</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>5</th>\n",
       "      <td>Physiotherapie im Stadtgarten</td>\n",
       "      <td>Lange Strasse</td>\n",
       "      <td>15</td>\n",
       "      <td>None</td>\n",
       "      <td>89129</td>\n",
       "      <td>Langenau</td>\n",
       "      <td>Baden-Württemberg</td>\n",
       "      <td>Landkreis Alb-Donau-Kreis</td>\n",
       "      <td>48.501099</td>\n",
       "      <td>10.122168</td>\n",
       "      <td>...</td>\n",
       "      <td>None</td>\n",
       "      <td>AC Schuko</td>\n",
       "      <td>3.7</td>\n",
       "      <td>None</td>\n",
       "      <td>None</td>\n",
       "      <td>NaN</td>\n",
       "      <td>None</td>\n",
       "      <td>None</td>\n",
       "      <td>NaN</td>\n",
       "      <td>None</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>6</th>\n",
       "      <td>Solarcollect</td>\n",
       "      <td>Eichlesstrasse</td>\n",
       "      <td>18</td>\n",
       "      <td>None</td>\n",
       "      <td>89129</td>\n",
       "      <td>Langenau</td>\n",
       "      <td>Baden-Württemberg</td>\n",
       "      <td>Landkreis Alb-Donau-Kreis</td>\n",
       "      <td>48.505686</td>\n",
       "      <td>10.138781</td>\n",
       "      <td>...</td>\n",
       "      <td>None</td>\n",
       "      <td>AC Schuko</td>\n",
       "      <td>3.7</td>\n",
       "      <td>None</td>\n",
       "      <td>None</td>\n",
       "      <td>NaN</td>\n",
       "      <td>None</td>\n",
       "      <td>None</td>\n",
       "      <td>NaN</td>\n",
       "      <td>None</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>7</th>\n",
       "      <td>EnBW mobility+ AG und Co.KG</td>\n",
       "      <td>Karlstr.</td>\n",
       "      <td>45</td>\n",
       "      <td>None</td>\n",
       "      <td>89129</td>\n",
       "      <td>Langenau</td>\n",
       "      <td>Baden-Württemberg</td>\n",
       "      <td>Landkreis Alb-Donau-Kreis</td>\n",
       "      <td>48.500374</td>\n",
       "      <td>10.117366</td>\n",
       "      <td>...</td>\n",
       "      <td>None</td>\n",
       "      <td>AC Steckdose Typ 2, AC Schuko</td>\n",
       "      <td>22.0</td>\n",
       "      <td>None</td>\n",
       "      <td>None</td>\n",
       "      <td>NaN</td>\n",
       "      <td>None</td>\n",
       "      <td>None</td>\n",
       "      <td>NaN</td>\n",
       "      <td>None</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>8</th>\n",
       "      <td>EnBW mobility+ AG und Co.KG</td>\n",
       "      <td>Flurstück</td>\n",
       "      <td>313</td>\n",
       "      <td>None</td>\n",
       "      <td>89129</td>\n",
       "      <td>Langenau/Seligweiler</td>\n",
       "      <td>Baden-Württemberg</td>\n",
       "      <td>Landkreis Alb-Donau-Kreis</td>\n",
       "      <td>48.456817</td>\n",
       "      <td>10.029649</td>\n",
       "      <td>...</td>\n",
       "      <td>None</td>\n",
       "      <td>DC Kupplung Combo</td>\n",
       "      <td>300.0</td>\n",
       "      <td>None</td>\n",
       "      <td>None</td>\n",
       "      <td>NaN</td>\n",
       "      <td>None</td>\n",
       "      <td>None</td>\n",
       "      <td>NaN</td>\n",
       "      <td>None</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>9</th>\n",
       "      <td>EnBW mobility+ AG und Co.KG</td>\n",
       "      <td>Flurstück</td>\n",
       "      <td>313</td>\n",
       "      <td>None</td>\n",
       "      <td>89129</td>\n",
       "      <td>Langenau/Seligweiler</td>\n",
       "      <td>Baden-Württemberg</td>\n",
       "      <td>Landkreis Alb-Donau-Kreis</td>\n",
       "      <td>48.456817</td>\n",
       "      <td>10.029649</td>\n",
       "      <td>...</td>\n",
       "      <td>None</td>\n",
       "      <td>DC Kupplung Combo</td>\n",
       "      <td>300.0</td>\n",
       "      <td>None</td>\n",
       "      <td>None</td>\n",
       "      <td>NaN</td>\n",
       "      <td>None</td>\n",
       "      <td>None</td>\n",
       "      <td>NaN</td>\n",
       "      <td>None</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "<p>10 rows × 26 columns</p>\n",
       "</div>"
      ],
      "text/plain": [
       "                       Betreiber                  Straße Hausnummer   \n",
       "0          Albwerk GmbH & Co. KG          Ennabeurer Weg          0  \\\n",
       "1                        smopi®                Albstraße         14   \n",
       "2          Albwerk GmbH & Co. KG  Parkplatz Campingplatz          0   \n",
       "3   EnBW mobility+ AG und Co.KG              Hauptstraße        91c   \n",
       "4               SWU Energie GmbH              Sterngasse          6   \n",
       "5  Physiotherapie im Stadtgarten           Lange Strasse         15   \n",
       "6                   Solarcollect          Eichlesstrasse         18   \n",
       "7   EnBW mobility+ AG und Co.KG                 Karlstr.         45   \n",
       "8   EnBW mobility+ AG und Co.KG                Flurstück        313   \n",
       "9   EnBW mobility+ AG und Co.KG                Flurstück        313   \n",
       "\n",
       "  Adresszusatz  Postleitzahl                   Ort         Bundesland   \n",
       "0         None         72535           Heroldstatt  Baden-Württemberg  \\\n",
       "1         None         72535           Heroldstatt  Baden-Württemberg   \n",
       "2         None         72589            Westerheim  Baden-Württemberg   \n",
       "3         None         73340             Amstetten  Baden-Württemberg   \n",
       "4         None         88481              Balzheim  Baden-Württemberg   \n",
       "5         None         89129              Langenau  Baden-Württemberg   \n",
       "6         None         89129              Langenau  Baden-Württemberg   \n",
       "7         None         89129              Langenau  Baden-Württemberg   \n",
       "8         None         89129  Langenau/Seligweiler  Baden-Württemberg   \n",
       "9         None         89129  Langenau/Seligweiler  Baden-Württemberg   \n",
       "\n",
       "      Kreis/kreisfreie Stadt  Breitengrad  Längengrad  ... Public Key1   \n",
       "0  Landkreis Alb-Donau-Kreis    48.442398    9.659075  ...        None  \\\n",
       "1  Landkreis Alb-Donau-Kreis    48.449353    9.672201  ...        None   \n",
       "2  Landkreis Alb-Donau-Kreis    48.510500    9.609000  ...        None   \n",
       "3  Landkreis Alb-Donau-Kreis    48.578534    9.874840  ...        None   \n",
       "4  Landkreis Alb-Donau-Kreis    48.179959   10.076977  ...        None   \n",
       "5  Landkreis Alb-Donau-Kreis    48.501099   10.122168  ...        None   \n",
       "6  Landkreis Alb-Donau-Kreis    48.505686   10.138781  ...        None   \n",
       "7  Landkreis Alb-Donau-Kreis    48.500374   10.117366  ...        None   \n",
       "8  Landkreis Alb-Donau-Kreis    48.456817   10.029649  ...        None   \n",
       "9  Landkreis Alb-Donau-Kreis    48.456817   10.029649  ...        None   \n",
       "\n",
       "                   Steckertypen2 P2 [kW]  Public Key2       Steckertypen3   \n",
       "0             AC Steckdose Typ 2    22.0         None                None  \\\n",
       "1             AC Steckdose Typ 2    22.0         None  AC Steckdose Typ 2   \n",
       "2             AC Steckdose Typ 2    22.0         None                None   \n",
       "3  DC Kupplung Combo, DC CHAdeMO    50.0         None                None   \n",
       "4                           None     NaN         None                None   \n",
       "5                      AC Schuko     3.7         None                None   \n",
       "6                      AC Schuko     3.7         None                None   \n",
       "7  AC Steckdose Typ 2, AC Schuko    22.0         None                None   \n",
       "8              DC Kupplung Combo   300.0         None                None   \n",
       "9              DC Kupplung Combo   300.0         None                None   \n",
       "\n",
       "   P3 [kW] Public Key3       Steckertypen4  P4 [kW] Public Key4  \n",
       "0      NaN        None                None      NaN        None  \n",
       "1     22.0        None  AC Steckdose Typ 2     22.0        None  \n",
       "2      NaN        None                None      NaN        None  \n",
       "3      NaN        None                None      NaN        None  \n",
       "4      NaN        None                None      NaN        None  \n",
       "5      NaN        None                None      NaN        None  \n",
       "6      NaN        None                None      NaN        None  \n",
       "7      NaN        None                None      NaN        None  \n",
       "8      NaN        None                None      NaN        None  \n",
       "9      NaN        None                None      NaN        None  \n",
       "\n",
       "[10 rows x 26 columns]"
      ]
     },
     "execution_count": 4,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df_2.head(10)"
   ]
  },
  {
   "attachments": {},
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### Data exploration\n",
    "Print some basic information about the data. Your data exploration would continue here."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 6,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "<class 'pandas.core.frame.DataFrame'>\n",
      "RangeIndex: 36770 entries, 0 to 36769\n",
      "Data columns (total 20 columns):\n",
      " #   Column                  Non-Null Count  Dtype  \n",
      "---  ------                  --------------  -----  \n",
      " 0   betreiber               36770 non-null  object \n",
      " 1   art_der_ladeeinrichung  36770 non-null  object \n",
      " 2   anzahl_ladepunkte       36770 non-null  int64  \n",
      " 3   anschlussleistung       36770 non-null  float64\n",
      " 4   steckertypen1           36770 non-null  object \n",
      " 5   steckertypen2           31111 non-null  object \n",
      " 6   steckertypen3           2233 non-null   object \n",
      " 7   steckertypen4           1132 non-null   object \n",
      " 8   p1_kw                   36770 non-null  float64\n",
      " 9   p2_kw                   31129 non-null  float64\n",
      " 10  p3_kw                   2233 non-null   float64\n",
      " 11  p4_kw                   1131 non-null   float64\n",
      " 12  kreis_kreisfreie_stadt  36770 non-null  object \n",
      " 13  ort                     36770 non-null  object \n",
      " 14  postleitzahl            36770 non-null  int64  \n",
      " 15  strasse                 36770 non-null  object \n",
      " 16  hausnummer              36770 non-null  object \n",
      " 17  adresszusatz            4623 non-null   object \n",
      " 18  inbetriebnahmedatum     36770 non-null  object \n",
      " 19  koordinaten             36770 non-null  object \n",
      "dtypes: float64(5), int64(2), object(13)\n",
      "memory usage: 5.6+ MB\n"
     ]
    }
   ],
   "source": [
    "df_1.info()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 7,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "<class 'pandas.core.frame.DataFrame'>\n",
      "RangeIndex: 42995 entries, 0 to 42994\n",
      "Data columns (total 26 columns):\n",
      " #   Column                  Non-Null Count  Dtype  \n",
      "---  ------                  --------------  -----  \n",
      " 0   Betreiber               42995 non-null  object \n",
      " 1   Straße                  42995 non-null  object \n",
      " 2   Hausnummer              42995 non-null  object \n",
      " 3   Adresszusatz            6115 non-null   object \n",
      " 4   Postleitzahl            42995 non-null  int64  \n",
      " 5   Ort                     42995 non-null  object \n",
      " 6   Bundesland              42995 non-null  object \n",
      " 7   Kreis/kreisfreie Stadt  42995 non-null  object \n",
      " 8   Breitengrad             42995 non-null  float64\n",
      " 9   Längengrad              42995 non-null  float64\n",
      " 10  Inbetriebnahmedatum     42995 non-null  object \n",
      " 11  Anschlussleistung       42995 non-null  float64\n",
      " 12  Art der Ladeeinrichung  42995 non-null  object \n",
      " 13  Anzahl Ladepunkte       42995 non-null  int64  \n",
      " 14  Steckertypen1           42995 non-null  object \n",
      " 15  P1 [kW]                 42995 non-null  float64\n",
      " 16  Public Key1             4678 non-null   object \n",
      " 17  Steckertypen2           34649 non-null  object \n",
      " 18  P2 [kW]                 34668 non-null  float64\n",
      " 19  Public Key2             3668 non-null   object \n",
      " 20  Steckertypen3           2506 non-null   object \n",
      " 21  P3 [kW]                 2505 non-null   float64\n",
      " 22  Public Key3             236 non-null    object \n",
      " 23  Steckertypen4           1352 non-null   object \n",
      " 24  P4 [kW]                 1351 non-null   float64\n",
      " 25  Public Key4             169 non-null    object \n",
      "dtypes: float64(7), int64(2), object(17)\n",
      "memory usage: 8.5+ MB\n"
     ]
    }
   ],
   "source": [
    "df_2.info()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 8,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "count    36770.000000\n",
       "mean        37.648928\n",
       "std         58.958237\n",
       "min          1.000000\n",
       "25%         22.000000\n",
       "50%         22.000000\n",
       "75%         22.000000\n",
       "max        360.000000\n",
       "Name: p1_kw, dtype: float64"
      ]
     },
     "execution_count": 8,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df_1[\"p1_kw\"].describe()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 9,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "count    31129.000000\n",
       "mean        38.365378\n",
       "std         58.080733\n",
       "min          2.000000\n",
       "25%         22.000000\n",
       "50%         22.000000\n",
       "75%         22.000000\n",
       "max        360.000000\n",
       "Name: p2_kw, dtype: float64"
      ]
     },
     "execution_count": 9,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df_1[\"p2_kw\"].describe()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 11,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "count    42995.000000\n",
       "mean        43.619511\n",
       "std         70.511931\n",
       "min          1.000000\n",
       "25%         22.000000\n",
       "50%         22.000000\n",
       "75%         22.000000\n",
       "max        360.000000\n",
       "Name: P1 [kW], dtype: float64"
      ]
     },
     "execution_count": 11,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df_2[\"P1 [kW]\"].describe()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 12,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "count    34668.000000\n",
       "mean        40.688187\n",
       "std         63.300356\n",
       "min          2.000000\n",
       "25%         22.000000\n",
       "50%         22.000000\n",
       "75%         22.000000\n",
       "max        360.000000\n",
       "Name: P2 [kW], dtype: float64"
      ]
     },
     "execution_count": 12,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df_2[\"P2 [kW]\"].describe()"
   ]
  },
  {
   "attachments": {},
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### where is the most charging station available?"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 26,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "The most common postal code in data source 1 is: 80937\n",
      "The most common postal code in data source 2 is: 80937\n"
     ]
    }
   ],
   "source": [
    "\n",
    "most_common_postcode = df_1['postleitzahl'].value_counts().idxmax()\n",
    "print(\"The most common postal code in data source 1 is:\", most_common_postcode)\n",
    "\n",
    "most_common_postcode = df_2['Postleitzahl'].value_counts().idxmax()\n",
    "print(\"The most common postal code in data source 2 is:\", most_common_postcode)\n"
   ]
  },
  {
   "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.9.0"
  },
  "orig_nbformat": 4
 },
 "nbformat": 4,
 "nbformat_minor": 2
}