anbieter-merge.ipynb

{
 "cells": [
  {
   "cell_type": "code",
   "execution_count": 118,
   "metadata": {},
   "outputs": [],
   "source": [
    "import pandas as pd\n",
    "import time"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "# Daten zusammenführen\n",
    "Alle unsere Daten:"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 119,
   "metadata": {},
   "outputs": [],
   "source": [
    "bna = pd.read_csv(\"cleaned_data/StromVersorgerListe_Bundesnetzagentur-cleaned-2019-08-07.csv\")\n",
    "vv = pd.read_csv(\"cleaned_data/verivox-alle-anbieter-cleaned-2019-08-19.csv\")\n",
    "c24 = pd.read_csv(\"raw_data/check24-alle-anbieter-scaped-2019-08-07.csv\")\n",
    "sa1 = pd.read_csv(\"cleaned_data/Stromauskunftde-oekostrom-cleaned-2019-08-08.csv\")\n",
    "sa2 = pd.read_csv(\"cleaned_data/stromauskunftde-reine-oekostrom-cleaned-2019-08-06.csv\")\n",
    "ett = pd.read_csv(\"cleaned_data/ecotopten_oekostrom_cleaned-2019-08-19.csv\")\n",
    "oet = pd.read_csv(\"cleaned_data/oekotest-strom-cleaned-2019-08-19.csv\")\n",
    "ut = pd.read_csv(\"cleaned_data/utopia-bestenliste-cleaned-2019-08-19.csv\")\n",
    "op = pd.read_csv(\"cleaned_data/okpower-cleaned-2019-07-30.csv\")\n",
    "gs = pd.read_csv(\"cleaned_data/gruenstrom-cleaned-2019-07-30.csv\")"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## Stromanbieter\n",
    "\n",
    "Wir haben ein paar Quellen, die nur Stromanbieter im Allgemeinen auflisten.\n",
    "Leider machen es die unterschiedlichen Arten der Benennung sehr schwer die Listen einigermaßen brauchbar zusammenzuführen.\n",
    "\n",
    "Gerade der von Check24 gescrapte Datensatz ist fast unbrauchbar. Verivox sieht da vielversprechender aus.\n"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## Ökostromanbieter\n",
    "\n",
    "Widmen wir uns den Ökostrom(tarif|produkt)anbieterlisten. Mal sehen!\n",
    "\n",
    "### Stromauskunft + Verivox\n",
    "\n",
    "Zuerst die großen Listen Stromauskunft `sa1` und Verivox `vv` mit Firmenadressen!"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 208,
   "metadata": {
    "scrolled": true
   },
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "Verivox dimensions (1147, 2)\n",
      "Stromauskunft dimensions (715, 1)\n",
      "Ergebnis (1157, 6)\n",
      "Write to csv\n"
     ]
    }
   ],
   "source": [
    "# Zuerst Spalten umbenennen\n",
    "sa1.rename(columns={\"Stromanbieter\": \"Firmenname\",\n",
    "                   \"Adresse 1\": \"Firmenanschrift\",\n",
    "                   \"Stadt\": \"Firmenort\"}, inplace=True)\n",
    "\n",
    "sa1['Stromauskunft'] = True\n",
    "\n",
    "# Dann mergen\n",
    "sa_vv_list = pd.merge(sa1,\n",
    "               vv[['Firmenname', 'Verivox']],\n",
    "               on='Firmenname',\n",
    "               how='outer')\n",
    "\n",
    "print(\"Verivox dimensions {}\".format(vv.shape))\n",
    "print(\"Stromauskunft dimensions {}\".format(sa1[[\"Firmenname\"]].shape))\n",
    "print(\"Ergebnis {}\".format(sa_vv_list.shape))\n",
    "\n",
    "print(\"Write to csv\")\n",
    "sa_vv_list.to_csv(\"merged_data/sa_vv_list-{}.csv\".format(time.strftime(\"%Y%m%d-%H%M%S\")))"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Sehr viele Überschneidungen! Wir belassen das so, ohne nachzuarbeiten."
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "\n",
    "### Kleine Listen zusammenführen\n",
    "\n",
    "Die Liste der \"echten\" Ökostromanbieter von Stromauskunft (`sa2`), EcoTopTen(`ett`), Ökotest (`oet`) und Utopia (`ut`) sind nicht sehr lang. Evtl können wir hier einfach mergen und manuell weitermachen."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 159,
   "metadata": {
    "scrolled": false
   },
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "Stromauskunft dimensions (18, 3)\n",
      "EcoTopTen dimensions (30, 2)\n",
      "Ökotest dimensions (34, 3)\n",
      "Utopia dimensions (16, 2)\n"
     ]
    }
   ],
   "source": [
    "# Spalten umbenennen\n",
    "sa2['Stromauskunft echter Oekostrom'] = True\n",
    "sa2.rename(columns={\"Anbieter\": \"Firmenname\"}, inplace=True)\n",
    "print(\"Stromauskunft dimensions {}\".format(sa2.shape))\n",
    "\n",
    "ett['EcoTopTen'] = True\n",
    "print(\"EcoTopTen dimensions {}\".format(ett[['Firmenname', 'EcoTopTen']].shape))\n",
    "\n",
    "oet['Ökotest'] = True\n",
    "oet.rename(columns={\"Anbieter\": \"Firmenname\"}, inplace=True)\n",
    "print(\"Ökotest dimensions {}\".format(oet.shape))\n",
    "\n",
    "ut['Utopia'] = True\n",
    "ut.rename(columns={\"Name\": \"Firmenname\"}, inplace=True)\n",
    "print(\"Utopia dimensions {}\".format(ut[['Firmenname', 'Utopia']].shape))  "
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 165,
   "metadata": {
    "scrolled": true
   },
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "List dimensions oet + ett (62, 3)\n",
      "List dimensions + ut (74, 4)\n",
      "List dimensions + sa2 (85, 5)\n",
      "Write to csv\n"
     ]
    }
   ],
   "source": [
    "# Mergen!\n",
    "small_list = pd.merge(oet[['Firmenname', 'Ökotest']],\n",
    "               ett[['Firmenname', 'EcoTopTen']],\n",
    "               on='Firmenname',\n",
    "               how='outer')\n",
    "\n",
    "print(\"List dimensions oet + ett {}\".format(small_list.shape))\n",
    "\n",
    "small_list = pd.merge(small_list,\n",
    "                     ut[['Firmenname', 'Utopia']],\n",
    "                     on='Firmenname',\n",
    "                     how='outer')\n",
    "\n",
    "print(\"List dimensions + ut {}\".format(small_list.shape))\n",
    "\n",
    "small_list = pd.merge(small_list,\n",
    "                     sa2[['Firmenname', 'Stromauskunft echter Oekostrom']],\n",
    "                     on='Firmenname',\n",
    "                     how='outer')\n",
    "\n",
    "print(\"List dimensions + sa2 {}\".format(small_list.shape))\n",
    "\n",
    "print(\"Write to csv\")\n",
    "small_list.to_csv(\"merged_data/small_list-{}.csv\".format(time.strftime(\"%Y%m%d-%H%M%S\")))"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Leider haben wir auch hier das Problem der unterschiedlichen Firmen- und Markennamen. In Open Refine habe ich die Liste manuell gesäubert und zusammengefasst. Es stechen nur zwei Anbieter hervor, die nur in jeweils einer Liste auftauchen und nicht mit anderen in Verbindung stehen."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 213,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "(36, 6)\n"
     ]
    }
   ],
   "source": [
    "small_list_clean = pd.read_csv(\"merged_data/small_list-oet-ett-ut-st2-20190821-cleaned.csv\")\n",
    "print(small_list_clean.shape)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### Labellisten"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 187,
   "metadata": {
    "scrolled": true
   },
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "OK power dimensions (75, 6)\n",
      "Grüner Strom dimensions (64, 6)\n",
      "Label Liste dimensions (137, 9)\n"
     ]
    }
   ],
   "source": [
    "op['OK Power'] = True\n",
    "op.rename(columns={\"Anbieter\": \"Firmenname\"}, inplace=True)\n",
    "print(\"OK power dimensions {}\".format(op.shape))\n",
    "\n",
    "\n",
    "gs['Grüner Strom'] = True\n",
    "gs.rename(columns={\"Anbieter\": \"Firmenname\"}, inplace=True)\n",
    "print(\"Grüner Strom dimensions {}\".format(gs.shape))\n",
    "\n",
    "label_list = pd.merge(op[['Firmenname', 'Adresse', 'Telefon', 'Website', 'OK Power']],\n",
    "                     gs[['Firmenname', 'Verfügbarkeit', 'Telefon', 'Website', 'Grüner Strom']],\n",
    "                     on='Firmenname',\n",
    "                     how='outer')\n",
    "\n",
    "print(\"Label Liste dimensions {}\".format(label_list.shape))\n",
    "\n",
    "print(\"Write to csv\")\n",
    "label_list.to_csv(\"merged_data/label_list-{}.csv\".format(time.strftime(\"%Y%m%d-%H%M%S\")))\n"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Auch hier müssen wir manuell ran und die Telefonnummers und Websites in Open Refine umtragen.\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 214,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "(134, 10)\n"
     ]
    }
   ],
   "source": [
    "label_list_clean = pd.read_csv(\"merged_data/label_list-20190821-cleaned.csv\")\n",
    "print(label_list_clean.shape)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### Labellisten und kleine Liste\n",
    "\n",
    "Wir arbeiten weiter mit den gesäuberten Listen der letzten beiden Schritte."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 193,
   "metadata": {
    "scrolled": true
   },
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "Small Lis dimensions (36, 6)\n",
      "Label Liste dimensions (134, 10)\n",
      "Öko Liste dimensions (161, 15)\n",
      "Write to csv\n"
     ]
    }
   ],
   "source": [
    "#small_list_clean = pd.read_csv(\"merged_data/small_list-oet-ett-ut-st2-20190821-cleaned.csv\")\n",
    "#label_list_clean = pd.read_csv(\"merged_data/label_list-20190821-cleaned.csv\")\n",
    "\n",
    "oeko_list = pd.merge(small_list_clean,\n",
    "                    label_list_clean,\n",
    "                    on=\"Firmenname\",\n",
    "                    how=\"outer\")\n",
    "\n",
    "print(\"Small Lis dimensions {}\".format(small_list_clean.shape))\n",
    "print(\"Label Liste dimensions {}\".format(label_list_clean.shape))\n",
    "print(\"Öko Liste dimensions {}\".format(oeko_list.shape))\n",
    "\n",
    "print(\"Write to csv\")\n",
    "oeko_list.to_csv(\"merged_data/oeko_list-{}.csv\".format(time.strftime(\"%Y%m%d-%H%M%S\")))\n"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Hier hatte ich mehr Überschneidungen erwartet, schließlich sollten die Anbieter auf der kleinen Liste zum Großteil in der Labelliste vertreten sein. Zeit in Open Refine nachzuarbeiten."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 215,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "(144, 13)\n"
     ]
    }
   ],
   "source": [
    "oeko_list_clean = pd.read_csv(\"merged_data/oeko_list-20190821-cleaned.csv\")\n",
    "print(oeko_list_clean.shape)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### Große Anbieterliste\n",
    "Jetzt legen wir diese auf die große Anbieterliste aus Vervox und Stromauskunft.de"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 216,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "SA VV Liste dimensions (1157, 6)\n",
      "kleine Öko Liste dimensions (144, 13)\n",
      "Große Öko Liste dimensions (1226, 18)\n",
      "Write to csv\n"
     ]
    }
   ],
   "source": [
    "oeko_list_clean = pd.read_csv(\"merged_data/oeko_list-20190821-cleaned.csv\")\n",
    "\n",
    "oeko_list_large = pd.merge(oeko_list_clean,\n",
    "                          sa_vv_list,\n",
    "                          on=\"Firmenname\",\n",
    "                          how=\"outer\")\n",
    "\n",
    "print(\"SA VV Liste dimensions {}\".format(sa_vv_list.shape))\n",
    "print(\"kleine Öko Liste dimensions {}\".format(oeko_list_clean.shape))\n",
    "print(\"Große Öko Liste dimensions {}\".format(oeko_list_large.shape))\n",
    "\n",
    "print(\"Write to csv\")\n",
    "oeko_list_large.to_csv(\"merged_data/oeko_list_large-{}.csv\".format(time.strftime(\"%Y%m%d-%H%M%S\")))"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Wieder trefen ein paar Dopplungen auf, auch die Spalten machen jetzt ärger. In Open Refine wird beides so gut es geht bereinigt."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 211,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "(1202, 15)\n"
     ]
    },
    {
     "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>Firmenname</th>\n",
       "      <th>Grüner Strom</th>\n",
       "      <th>OK Power</th>\n",
       "      <th>Stromauskunft</th>\n",
       "      <th>Verivox</th>\n",
       "      <th>Ökotest</th>\n",
       "      <th>EcoTopTen</th>\n",
       "      <th>Utopia</th>\n",
       "      <th>Stromauskunft echter Oekostrom</th>\n",
       "      <th>Telefon</th>\n",
       "      <th>Website</th>\n",
       "      <th>Verfügbarkeit</th>\n",
       "      <th>Firmenanschrift</th>\n",
       "      <th>PLZ</th>\n",
       "      <th>Firmenort</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>123energie - eine Marke der Pfalzwerke AG</td>\n",
       "      <td>NaN</td>\n",
       "      <td>True</td>\n",
       "      <td>True</td>\n",
       "      <td>True</td>\n",
       "      <td>True</td>\n",
       "      <td>True</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>0621 570573123</td>\n",
       "      <td>www.123energie.de</td>\n",
       "      <td>NaN</td>\n",
       "      <td>Kurfürstenstraße 29</td>\n",
       "      <td>67061</td>\n",
       "      <td>Ludwigshafen</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>17er Oberlandenergie GmbH</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>True</td>\n",
       "      <td>True</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>Viehmarktplatz 1</td>\n",
       "      <td>82418</td>\n",
       "      <td>Murnau am Staffelsee</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>24/7 Energie und Kommunikation GmbH</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>True</td>\n",
       "      <td>True</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>Meeboldstraße 1</td>\n",
       "      <td>89522</td>\n",
       "      <td>Heidenheim</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>4hundred GmbH</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>True</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>Abens-Donau Energie GmbH</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>True</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "                                  Firmenname Grüner Strom OK Power  \\\n",
       "0  123energie - eine Marke der Pfalzwerke AG          NaN     True   \n",
       "1                  17er Oberlandenergie GmbH          NaN      NaN   \n",
       "2        24/7 Energie und Kommunikation GmbH          NaN      NaN   \n",
       "3                              4hundred GmbH          NaN      NaN   \n",
       "4                   Abens-Donau Energie GmbH          NaN      NaN   \n",
       "\n",
       "  Stromauskunft Verivox Ökotest EcoTopTen Utopia  \\\n",
       "0          True    True    True      True    NaN   \n",
       "1          True    True     NaN       NaN    NaN   \n",
       "2          True    True     NaN       NaN    NaN   \n",
       "3           NaN    True     NaN       NaN    NaN   \n",
       "4           NaN    True     NaN       NaN    NaN   \n",
       "\n",
       "  Stromauskunft echter Oekostrom         Telefon            Website  \\\n",
       "0                            NaN  0621 570573123  www.123energie.de   \n",
       "1                            NaN             NaN                NaN   \n",
       "2                            NaN             NaN                NaN   \n",
       "3                            NaN             NaN                NaN   \n",
       "4                            NaN             NaN                NaN   \n",
       "\n",
       "  Verfügbarkeit      Firmenanschrift    PLZ             Firmenort  \n",
       "0           NaN  Kurfürstenstraße 29  67061          Ludwigshafen  \n",
       "1           NaN     Viehmarktplatz 1  82418  Murnau am Staffelsee  \n",
       "2           NaN      Meeboldstraße 1  89522            Heidenheim  \n",
       "3           NaN                  NaN    NaN                   NaN  \n",
       "4           NaN                  NaN    NaN                   NaN  "
      ]
     },
     "execution_count": 211,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "list_final = pd.read_csv(\"merged_data/oeko_list_large-20190826-cleaned.csv\")\n",
    "print(list_final.shape)\n",
    "list_final.head()"
   ]
  }
 ],
 "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.4"
  }
 },
 "nbformat": 4,
 "nbformat_minor": 2
}