showing_recent_exposes.ipynb

{
 "cells": [
  {
   "cell_type": "code",
   "execution_count": 1,
   "id": "civilian-clinic",
   "metadata": {},
   "outputs": [],
   "source": [
    "import os\n",
    "import sys\n",
    "#sys.path.append(\"/Users/dirkulbricht/projects/hauspreis_getting_ids/scr\")\n",
    "import re\n",
    "import json\n",
    "from sklearn.preprocessing import MultiLabelBinarizer \n",
    "import itertools\n",
    "import pandas as pd\n",
    "import numpy as np\n",
    "import webbrowser\n",
    "from datetime import date\n",
    "from importlib import reload\n",
    "#import cleaning as clean\n",
    "from scr.cleaning import *\n",
    "\n",
    "import openpyxl\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 2,
   "id": "responsible-watch",
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "/Users/dirkulbricht/projects/hauspreis_getting_ids\n",
      "76  json-files have been loaded\n",
      "508  rows without price and zip data were deleted\n",
      "merkmale cleaned and put into list\n",
      "merkmale binarized\n",
      "preis has been cleaned up and converted to float\n",
      "numerical columns: anzahl_raeume, wohnflaeche, grundstuecksflaeche cleaned\n",
      "beschreibung has been dropped\n"
     ]
    }
   ],
   "source": [
    "df = load_and_prepare_data()\n",
    "save_data_as_csv(df)\n",
    "remove_expose_files()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 9,
   "id": "caring-membership",
   "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>transaktionsArt</th>\n",
       "      <th>objektArt</th>\n",
       "      <th>datumDownload</th>\n",
       "      <th>suchOrt</th>\n",
       "      <th>url</th>\n",
       "      <th>title</th>\n",
       "      <th>preis</th>\n",
       "      <th>anzahl_raeume</th>\n",
       "      <th>wohnflaeche</th>\n",
       "      <th>grundstuecksflaeche</th>\n",
       "      <th>...</th>\n",
       "      <th>frei</th>\n",
       "      <th>provisionsfrei</th>\n",
       "      <th>renoviert</th>\n",
       "      <th>rollstuhlgerecht</th>\n",
       "      <th>saniert</th>\n",
       "      <th>seniorengerechtes Wohnen</th>\n",
       "      <th>teilweise klimatisiert</th>\n",
       "      <th>teilweise unterkellert</th>\n",
       "      <th>voll klimatisiert</th>\n",
       "      <th>voll unterkellert</th>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>id</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>2zp9946</th>\n",
       "      <td>mieten</td>\n",
       "      <td>wohnungen</td>\n",
       "      <td>2021-04-29</td>\n",
       "      <td>norderstedt</td>\n",
       "      <td>https://www.immowelt.de/expose/2zp9946</td>\n",
       "      <td>Selbstbestimmt wohnen im Alter (Wohnberechtigu...</td>\n",
       "      <td>286.33</td>\n",
       "      <td>1.0</td>\n",
       "      <td>46.94</td>\n",
       "      <td>NaN</td>\n",
       "      <td>...</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>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2zz7746</th>\n",
       "      <td>mieten</td>\n",
       "      <td>wohnungen</td>\n",
       "      <td>2021-04-29</td>\n",
       "      <td>norderstedt</td>\n",
       "      <td>https://www.immowelt.de/expose/2zz7746</td>\n",
       "      <td>Süßes kleines Schmuckstück ! Super 1  Zi. Wohn...</td>\n",
       "      <td>360.00</td>\n",
       "      <td>1.0</td>\n",
       "      <td>30.50</td>\n",
       "      <td>NaN</td>\n",
       "      <td>...</td>\n",
       "      <td>1</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>0</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2zsw948</th>\n",
       "      <td>mieten</td>\n",
       "      <td>wohnungen</td>\n",
       "      <td>2021-04-29</td>\n",
       "      <td>norderstedt</td>\n",
       "      <td>https://www.immowelt.de/expose/2zsw948</td>\n",
       "      <td>1 - Zimmer Wohnung in Norderstedt | Etagenwohn...</td>\n",
       "      <td>425.00</td>\n",
       "      <td>1.0</td>\n",
       "      <td>36.11</td>\n",
       "      <td>NaN</td>\n",
       "      <td>...</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>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2z8zl43</th>\n",
       "      <td>mieten</td>\n",
       "      <td>wohnungen</td>\n",
       "      <td>2021-04-29</td>\n",
       "      <td>norderstedt</td>\n",
       "      <td>https://www.immowelt.de/expose/2z8zl43</td>\n",
       "      <td>Super schicke 1,5 Zi.Whg. mit Balkon in Norder...</td>\n",
       "      <td>430.00</td>\n",
       "      <td>1.5</td>\n",
       "      <td>40.12</td>\n",
       "      <td>NaN</td>\n",
       "      <td>...</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>1</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2zz9648</th>\n",
       "      <td>mieten</td>\n",
       "      <td>wohnungen</td>\n",
       "      <td>2021-04-29</td>\n",
       "      <td>norderstedt</td>\n",
       "      <td>https://www.immowelt.de/expose/2zz9648</td>\n",
       "      <td>Kleines Schmuckstück moderne 1 Zimmer Wohnung ...</td>\n",
       "      <td>455.00</td>\n",
       "      <td>1.0</td>\n",
       "      <td>40.70</td>\n",
       "      <td>NaN</td>\n",
       "      <td>...</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>1</td>\n",
       "      <td>0</td>\n",
       "      <td>1</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>...</th>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2zpen48</th>\n",
       "      <td>mieten</td>\n",
       "      <td>wohnungen</td>\n",
       "      <td>2021-04-29</td>\n",
       "      <td>norderstedt</td>\n",
       "      <td>https://www.immowelt.de/expose/2zpen48</td>\n",
       "      <td>helle Familienwohnung mit Balkon | Etagenwohnu...</td>\n",
       "      <td>1370.00</td>\n",
       "      <td>4.0</td>\n",
       "      <td>100.67</td>\n",
       "      <td>NaN</td>\n",
       "      <td>...</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>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2zrg942</th>\n",
       "      <td>mieten</td>\n",
       "      <td>wohnungen</td>\n",
       "      <td>2021-04-29</td>\n",
       "      <td>norderstedt</td>\n",
       "      <td>https://www.immowelt.de/expose/2zrg942</td>\n",
       "      <td>4 Zimmer auf zwei Ebenen mit eigener Terrasse ...</td>\n",
       "      <td>1390.00</td>\n",
       "      <td>4.0</td>\n",
       "      <td>109.30</td>\n",
       "      <td>NaN</td>\n",
       "      <td>...</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>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2yph44z</th>\n",
       "      <td>mieten</td>\n",
       "      <td>wohnungen</td>\n",
       "      <td>2021-04-29</td>\n",
       "      <td>norderstedt</td>\n",
       "      <td>https://www.immowelt.de/expose/2yph44z</td>\n",
       "      <td>Traumhafte Wohnung in der Endetage | Etagenwoh...</td>\n",
       "      <td>1522.50</td>\n",
       "      <td>3.0</td>\n",
       "      <td>128.70</td>\n",
       "      <td>NaN</td>\n",
       "      <td>...</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>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2ztzh46</th>\n",
       "      <td>mieten</td>\n",
       "      <td>wohnungen</td>\n",
       "      <td>2021-04-29</td>\n",
       "      <td>norderstedt</td>\n",
       "      <td>https://www.immowelt.de/expose/2ztzh46</td>\n",
       "      <td>Neubau! Exklusive 3 Zimmer-Staffelgeschoss- Wo...</td>\n",
       "      <td>1665.00</td>\n",
       "      <td>3.0</td>\n",
       "      <td>110.81</td>\n",
       "      <td>NaN</td>\n",
       "      <td>...</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>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2zr3j46</th>\n",
       "      <td>mieten</td>\n",
       "      <td>wohnungen</td>\n",
       "      <td>2021-04-29</td>\n",
       "      <td>norderstedt</td>\n",
       "      <td>https://www.immowelt.de/expose/2zr3j46</td>\n",
       "      <td>Neubau! Exklusive 3 Zimmer-Staffelgeschoss- Wo...</td>\n",
       "      <td>1845.00</td>\n",
       "      <td>3.0</td>\n",
       "      <td>123.07</td>\n",
       "      <td>NaN</td>\n",
       "      <td>...</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>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "<p>68 rows × 50 columns</p>\n",
       "</div>"
      ],
      "text/plain": [
       "        transaktionsArt  objektArt datumDownload      suchOrt  \\\n",
       "id                                                              \n",
       "2zp9946          mieten  wohnungen    2021-04-29  norderstedt   \n",
       "2zz7746          mieten  wohnungen    2021-04-29  norderstedt   \n",
       "2zsw948          mieten  wohnungen    2021-04-29  norderstedt   \n",
       "2z8zl43          mieten  wohnungen    2021-04-29  norderstedt   \n",
       "2zz9648          mieten  wohnungen    2021-04-29  norderstedt   \n",
       "...                 ...        ...           ...          ...   \n",
       "2zpen48          mieten  wohnungen    2021-04-29  norderstedt   \n",
       "2zrg942          mieten  wohnungen    2021-04-29  norderstedt   \n",
       "2yph44z          mieten  wohnungen    2021-04-29  norderstedt   \n",
       "2ztzh46          mieten  wohnungen    2021-04-29  norderstedt   \n",
       "2zr3j46          mieten  wohnungen    2021-04-29  norderstedt   \n",
       "\n",
       "                                            url  \\\n",
       "id                                                \n",
       "2zp9946  https://www.immowelt.de/expose/2zp9946   \n",
       "2zz7746  https://www.immowelt.de/expose/2zz7746   \n",
       "2zsw948  https://www.immowelt.de/expose/2zsw948   \n",
       "2z8zl43  https://www.immowelt.de/expose/2z8zl43   \n",
       "2zz9648  https://www.immowelt.de/expose/2zz9648   \n",
       "...                                         ...   \n",
       "2zpen48  https://www.immowelt.de/expose/2zpen48   \n",
       "2zrg942  https://www.immowelt.de/expose/2zrg942   \n",
       "2yph44z  https://www.immowelt.de/expose/2yph44z   \n",
       "2ztzh46  https://www.immowelt.de/expose/2ztzh46   \n",
       "2zr3j46  https://www.immowelt.de/expose/2zr3j46   \n",
       "\n",
       "                                                     title    preis  \\\n",
       "id                                                                    \n",
       "2zp9946  Selbstbestimmt wohnen im Alter (Wohnberechtigu...   286.33   \n",
       "2zz7746  Süßes kleines Schmuckstück ! Super 1  Zi. Wohn...   360.00   \n",
       "2zsw948  1 - Zimmer Wohnung in Norderstedt | Etagenwohn...   425.00   \n",
       "2z8zl43  Super schicke 1,5 Zi.Whg. mit Balkon in Norder...   430.00   \n",
       "2zz9648  Kleines Schmuckstück moderne 1 Zimmer Wohnung ...   455.00   \n",
       "...                                                    ...      ...   \n",
       "2zpen48  helle Familienwohnung mit Balkon | Etagenwohnu...  1370.00   \n",
       "2zrg942  4 Zimmer auf zwei Ebenen mit eigener Terrasse ...  1390.00   \n",
       "2yph44z  Traumhafte Wohnung in der Endetage | Etagenwoh...  1522.50   \n",
       "2ztzh46  Neubau! Exklusive 3 Zimmer-Staffelgeschoss- Wo...  1665.00   \n",
       "2zr3j46  Neubau! Exklusive 3 Zimmer-Staffelgeschoss- Wo...  1845.00   \n",
       "\n",
       "         anzahl_raeume  wohnflaeche  grundstuecksflaeche  ... frei  \\\n",
       "id                                                        ...        \n",
       "2zp9946            1.0        46.94                  NaN  ...    0   \n",
       "2zz7746            1.0        30.50                  NaN  ...    1   \n",
       "2zsw948            1.0        36.11                  NaN  ...    0   \n",
       "2z8zl43            1.5        40.12                  NaN  ...    0   \n",
       "2zz9648            1.0        40.70                  NaN  ...    0   \n",
       "...                ...          ...                  ...  ...  ...   \n",
       "2zpen48            4.0       100.67                  NaN  ...    0   \n",
       "2zrg942            4.0       109.30                  NaN  ...    0   \n",
       "2yph44z            3.0       128.70                  NaN  ...    0   \n",
       "2ztzh46            3.0       110.81                  NaN  ...    0   \n",
       "2zr3j46            3.0       123.07                  NaN  ...    0   \n",
       "\n",
       "        provisionsfrei renoviert rollstuhlgerecht  saniert  \\\n",
       "id                                                           \n",
       "2zp9946              0         0                0        0   \n",
       "2zz7746              0         0                0        0   \n",
       "2zsw948              0         0                0        0   \n",
       "2z8zl43              0         0                0        1   \n",
       "2zz9648              0         1                0        1   \n",
       "...                ...       ...              ...      ...   \n",
       "2zpen48              0         0                0        0   \n",
       "2zrg942              0         0                0        0   \n",
       "2yph44z              0         0                0        0   \n",
       "2ztzh46              0         0                0        0   \n",
       "2zr3j46              0         0                0        0   \n",
       "\n",
       "         seniorengerechtes Wohnen  teilweise klimatisiert  \\\n",
       "id                                                          \n",
       "2zp9946                         0                       0   \n",
       "2zz7746                         0                       0   \n",
       "2zsw948                         0                       0   \n",
       "2z8zl43                         0                       0   \n",
       "2zz9648                         0                       0   \n",
       "...                           ...                     ...   \n",
       "2zpen48                         0                       0   \n",
       "2zrg942                         0                       0   \n",
       "2yph44z                         0                       0   \n",
       "2ztzh46                         0                       0   \n",
       "2zr3j46                         0                       0   \n",
       "\n",
       "         teilweise unterkellert  voll klimatisiert  voll unterkellert  \n",
       "id                                                                     \n",
       "2zp9946                       0                  0                  0  \n",
       "2zz7746                       0                  0                  0  \n",
       "2zsw948                       0                  0                  0  \n",
       "2z8zl43                       0                  0                  0  \n",
       "2zz9648                       0                  0                  0  \n",
       "...                         ...                ...                ...  \n",
       "2zpen48                       0                  0                  0  \n",
       "2zrg942                       0                  0                  0  \n",
       "2yph44z                       0                  0                  0  \n",
       "2ztzh46                       0                  0                  0  \n",
       "2zr3j46                       0                  0                  0  \n",
       "\n",
       "[68 rows x 50 columns]"
      ]
     },
     "execution_count": 9,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "test = df[(df.preis.isna()==0)\n",
    "    &(df.suchOrt==\"norderstedt\")\n",
    "    &(df.transaktionsArt==\"mieten\")\n",
    "    &(df.objektArt==\"wohnungen\")\n",
    "    #&(df[\"datumDownload\"]=='2021-05-01')#pd.to_datetime('today').normalize())#'2021-04-24')\n",
    "]\n",
    "test =     test[test[\"datumDownload\"]==str(test.datumDownload.sort_values().unique()[-1])]\n",
    "\n",
    "test.sort_values([\"preis\"])"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "dangerous-filing",
   "metadata": {},
   "outputs": [],
   "source": [
    "str(test.datumDownload.sort_values().unique()[-1])[:10]"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "eight-dinner",
   "metadata": {},
   "outputs": [],
   "source": [
    "interessanteCols = [\"preis\",\"anzahl_raeume\",\"wohnflaeche\",\"transaktionsArt\",\"url\",\"datumDownload\",\"plz\"]\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 262,
   "id": "circular-voltage",
   "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>transaktionsArt</th>\n",
       "      <th>objektArt</th>\n",
       "      <th>datumDownload</th>\n",
       "      <th>suchOrt</th>\n",
       "      <th>url</th>\n",
       "      <th>url_maps</th>\n",
       "      <th>title</th>\n",
       "      <th>preis</th>\n",
       "      <th>anzahl_raeume</th>\n",
       "      <th>wohnflaeche</th>\n",
       "      <th>...</th>\n",
       "      <th>frei</th>\n",
       "      <th>provisionsfrei</th>\n",
       "      <th>renoviert</th>\n",
       "      <th>rollstuhlgerecht</th>\n",
       "      <th>saniert</th>\n",
       "      <th>seniorengerechtes Wohnen</th>\n",
       "      <th>teilweise klimatisiert</th>\n",
       "      <th>teilweise unterkellert</th>\n",
       "      <th>voll klimatisiert</th>\n",
       "      <th>voll unterkellert</th>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>id</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>2ygna4c</th>\n",
       "      <td>mieten</td>\n",
       "      <td>wohnungen</td>\n",
       "      <td>2021-04-13</td>\n",
       "      <td>norderstedt</td>\n",
       "      <td>https://www.immowelt.de/expose/2ygna4c</td>\n",
       "      <td>https://www.google.de/maps/dir/22846/22844</td>\n",
       "      <td>Neuwertige 3 Zimmer-Mietwohnung in gefragter L...</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>...</td>\n",
       "      <td>1</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>0</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2xblv4d</th>\n",
       "      <td>mieten</td>\n",
       "      <td>wohnungen</td>\n",
       "      <td>2021-04-13</td>\n",
       "      <td>norderstedt</td>\n",
       "      <td>https://www.immowelt.de/expose/2xblv4d</td>\n",
       "      <td>https://www.google.de/maps/dir/22846/22844</td>\n",
       "      <td>Erstbezug! Moderne Erdgeschosswohnung mit Gart...</td>\n",
       "      <td>1120.0</td>\n",
       "      <td>3.0</td>\n",
       "      <td>95.94</td>\n",
       "      <td>...</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>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2yehr4g</th>\n",
       "      <td>mieten</td>\n",
       "      <td>wohnungen</td>\n",
       "      <td>2021-04-13</td>\n",
       "      <td>norderstedt</td>\n",
       "      <td>https://www.immowelt.de/expose/2yehr4g</td>\n",
       "      <td>https://www.google.de/maps/dir/22846/22850</td>\n",
       "      <td>Traumhaftte 3 Zi.Wohnung mit Sonnenbalkon im K...</td>\n",
       "      <td>950.0</td>\n",
       "      <td>3.0</td>\n",
       "      <td>79.60</td>\n",
       "      <td>...</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>1</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",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2rj4c49</th>\n",
       "      <td>mieten</td>\n",
       "      <td>wohnungen</td>\n",
       "      <td>2021-04-13</td>\n",
       "      <td>norderstedt</td>\n",
       "      <td>https://www.immowelt.de/expose/2rj4c49</td>\n",
       "      <td>https://www.google.de/maps/dir/22846/22850</td>\n",
       "      <td>Schöne 3-Zimmer Wohnung, ruhige aber zentrale ...</td>\n",
       "      <td>585.0</td>\n",
       "      <td>2.5</td>\n",
       "      <td>66.58</td>\n",
       "      <td>...</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>1</td>\n",
       "      <td>0</td>\n",
       "      <td>1</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2mrur47</th>\n",
       "      <td>mieten</td>\n",
       "      <td>wohnungen</td>\n",
       "      <td>2021-04-13</td>\n",
       "      <td>norderstedt</td>\n",
       "      <td>https://www.immowelt.de/expose/2mrur47</td>\n",
       "      <td>https://www.google.de/maps/dir/22846/22850</td>\n",
       "      <td>Renovierte Traumwohnung mit Terrasse und Garte...</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>...</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>1</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",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>...</th>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2y4x845</th>\n",
       "      <td>kaufen</td>\n",
       "      <td>haus</td>\n",
       "      <td>2021-04-13</td>\n",
       "      <td>luebeck-hansestadt</td>\n",
       "      <td>https://www.immowelt.de/expose/2y4x845</td>\n",
       "      <td>https://www.google.de/maps/dir/22846/23562</td>\n",
       "      <td>Hochwertig sanierte Altbauvilla in Adresslage ...</td>\n",
       "      <td>1190000.0</td>\n",
       "      <td>6.0</td>\n",
       "      <td>214.00</td>\n",
       "      <td>...</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>1</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2x6wz4v</th>\n",
       "      <td>kaufen</td>\n",
       "      <td>haus</td>\n",
       "      <td>2021-04-13</td>\n",
       "      <td>luebeck-hansestadt</td>\n",
       "      <td>https://www.immowelt.de/expose/2x6wz4v</td>\n",
       "      <td>https://www.google.de/maps/dir/22846/23560</td>\n",
       "      <td>Doppelhaushälfte Moisling | Doppelhaushälfte L...</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>...</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>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2x54g4p</th>\n",
       "      <td>kaufen</td>\n",
       "      <td>haus</td>\n",
       "      <td>2021-04-13</td>\n",
       "      <td>luebeck-hansestadt</td>\n",
       "      <td>https://www.immowelt.de/expose/2x54g4p</td>\n",
       "      <td>https://www.google.de/maps/dir/22846/23566</td>\n",
       "      <td>Gepflegtes Zweifamilienhaus in ruhiger Lage vo...</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>...</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>0</td>\n",
       "      <td>0</td>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2xray4g</th>\n",
       "      <td>kaufen</td>\n",
       "      <td>haus</td>\n",
       "      <td>2021-04-13</td>\n",
       "      <td>luebeck-hansestadt</td>\n",
       "      <td>https://www.immowelt.de/expose/2xray4g</td>\n",
       "      <td>https://www.google.de/maps/dir/22846/23554</td>\n",
       "      <td>Provisionsfrei - Doppelhaushälfte in Lübeck-Fa...</td>\n",
       "      <td>240000.0</td>\n",
       "      <td>6.0</td>\n",
       "      <td>140.00</td>\n",
       "      <td>...</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>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2w56p4y</th>\n",
       "      <td>kaufen</td>\n",
       "      <td>haus</td>\n",
       "      <td>2021-04-13</td>\n",
       "      <td>luebeck-hansestadt</td>\n",
       "      <td>https://www.immowelt.de/expose/2w56p4y</td>\n",
       "      <td>https://www.google.de/maps/dir/22846/23554</td>\n",
       "      <td>*** Reduziert*** Gepflegtes Mitteilreihenhaus ...</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>...</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>0</td>\n",
       "      <td>0</td>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "<p>16716 rows × 51 columns</p>\n",
       "</div>"
      ],
      "text/plain": [
       "        transaktionsArt  objektArt datumDownload             suchOrt  \\\n",
       "id                                                                     \n",
       "2ygna4c          mieten  wohnungen    2021-04-13         norderstedt   \n",
       "2xblv4d          mieten  wohnungen    2021-04-13         norderstedt   \n",
       "2yehr4g          mieten  wohnungen    2021-04-13         norderstedt   \n",
       "2rj4c49          mieten  wohnungen    2021-04-13         norderstedt   \n",
       "2mrur47          mieten  wohnungen    2021-04-13         norderstedt   \n",
       "...                 ...        ...           ...                 ...   \n",
       "2y4x845          kaufen       haus    2021-04-13  luebeck-hansestadt   \n",
       "2x6wz4v          kaufen       haus    2021-04-13  luebeck-hansestadt   \n",
       "2x54g4p          kaufen       haus    2021-04-13  luebeck-hansestadt   \n",
       "2xray4g          kaufen       haus    2021-04-13  luebeck-hansestadt   \n",
       "2w56p4y          kaufen       haus    2021-04-13  luebeck-hansestadt   \n",
       "\n",
       "                                            url  \\\n",
       "id                                                \n",
       "2ygna4c  https://www.immowelt.de/expose/2ygna4c   \n",
       "2xblv4d  https://www.immowelt.de/expose/2xblv4d   \n",
       "2yehr4g  https://www.immowelt.de/expose/2yehr4g   \n",
       "2rj4c49  https://www.immowelt.de/expose/2rj4c49   \n",
       "2mrur47  https://www.immowelt.de/expose/2mrur47   \n",
       "...                                         ...   \n",
       "2y4x845  https://www.immowelt.de/expose/2y4x845   \n",
       "2x6wz4v  https://www.immowelt.de/expose/2x6wz4v   \n",
       "2x54g4p  https://www.immowelt.de/expose/2x54g4p   \n",
       "2xray4g  https://www.immowelt.de/expose/2xray4g   \n",
       "2w56p4y  https://www.immowelt.de/expose/2w56p4y   \n",
       "\n",
       "                                           url_maps  \\\n",
       "id                                                    \n",
       "2ygna4c  https://www.google.de/maps/dir/22846/22844   \n",
       "2xblv4d  https://www.google.de/maps/dir/22846/22844   \n",
       "2yehr4g  https://www.google.de/maps/dir/22846/22850   \n",
       "2rj4c49  https://www.google.de/maps/dir/22846/22850   \n",
       "2mrur47  https://www.google.de/maps/dir/22846/22850   \n",
       "...                                             ...   \n",
       "2y4x845  https://www.google.de/maps/dir/22846/23562   \n",
       "2x6wz4v  https://www.google.de/maps/dir/22846/23560   \n",
       "2x54g4p  https://www.google.de/maps/dir/22846/23566   \n",
       "2xray4g  https://www.google.de/maps/dir/22846/23554   \n",
       "2w56p4y  https://www.google.de/maps/dir/22846/23554   \n",
       "\n",
       "                                                     title      preis  \\\n",
       "id                                                                      \n",
       "2ygna4c  Neuwertige 3 Zimmer-Mietwohnung in gefragter L...        NaN   \n",
       "2xblv4d  Erstbezug! Moderne Erdgeschosswohnung mit Gart...     1120.0   \n",
       "2yehr4g  Traumhaftte 3 Zi.Wohnung mit Sonnenbalkon im K...      950.0   \n",
       "2rj4c49  Schöne 3-Zimmer Wohnung, ruhige aber zentrale ...      585.0   \n",
       "2mrur47  Renovierte Traumwohnung mit Terrasse und Garte...        NaN   \n",
       "...                                                    ...        ...   \n",
       "2y4x845  Hochwertig sanierte Altbauvilla in Adresslage ...  1190000.0   \n",
       "2x6wz4v  Doppelhaushälfte Moisling | Doppelhaushälfte L...        NaN   \n",
       "2x54g4p  Gepflegtes Zweifamilienhaus in ruhiger Lage vo...        NaN   \n",
       "2xray4g  Provisionsfrei - Doppelhaushälfte in Lübeck-Fa...   240000.0   \n",
       "2w56p4y  *** Reduziert*** Gepflegtes Mitteilreihenhaus ...        NaN   \n",
       "\n",
       "         anzahl_raeume  wohnflaeche  ...  frei provisionsfrei renoviert  \\\n",
       "id                                   ...                                  \n",
       "2ygna4c            NaN          NaN  ...     1              0         0   \n",
       "2xblv4d            3.0        95.94  ...     0              0         0   \n",
       "2yehr4g            3.0        79.60  ...     0              0         0   \n",
       "2rj4c49            2.5        66.58  ...     0              0         1   \n",
       "2mrur47            NaN          NaN  ...     0              0         1   \n",
       "...                ...          ...  ...   ...            ...       ...   \n",
       "2y4x845            6.0       214.00  ...     0              0         0   \n",
       "2x6wz4v            NaN          NaN  ...     0              0         0   \n",
       "2x54g4p            NaN          NaN  ...     0              0         0   \n",
       "2xray4g            6.0       140.00  ...     0              0         0   \n",
       "2w56p4y            NaN          NaN  ...     0              0         0   \n",
       "\n",
       "        rollstuhlgerecht saniert  seniorengerechtes Wohnen  \\\n",
       "id                                                           \n",
       "2ygna4c                0       0                         0   \n",
       "2xblv4d                0       0                         0   \n",
       "2yehr4g                1       0                         0   \n",
       "2rj4c49                0       1                         0   \n",
       "2mrur47                0       0                         0   \n",
       "...                  ...     ...                       ...   \n",
       "2y4x845                0       1                         0   \n",
       "2x6wz4v                0       0                         0   \n",
       "2x54g4p                0       0                         0   \n",
       "2xray4g                0       0                         0   \n",
       "2w56p4y                0       0                         0   \n",
       "\n",
       "         teilweise klimatisiert  teilweise unterkellert  voll klimatisiert  \\\n",
       "id                                                                           \n",
       "2ygna4c                       0                       0                  0   \n",
       "2xblv4d                       0                       0                  0   \n",
       "2yehr4g                       0                       0                  0   \n",
       "2rj4c49                       0                       0                  0   \n",
       "2mrur47                       0                       0                  0   \n",
       "...                         ...                     ...                ...   \n",
       "2y4x845                       0                       0                  0   \n",
       "2x6wz4v                       0                       0                  0   \n",
       "2x54g4p                       0                       0                  0   \n",
       "2xray4g                       0                       0                  0   \n",
       "2w56p4y                       0                       0                  0   \n",
       "\n",
       "         voll unterkellert  \n",
       "id                          \n",
       "2ygna4c                  0  \n",
       "2xblv4d                  0  \n",
       "2yehr4g                  0  \n",
       "2rj4c49                  0  \n",
       "2mrur47                  0  \n",
       "...                    ...  \n",
       "2y4x845                  1  \n",
       "2x6wz4v                  0  \n",
       "2x54g4p                  1  \n",
       "2xray4g                  0  \n",
       "2w56p4y                  1  \n",
       "\n",
       "[16716 rows x 51 columns]"
      ]
     },
     "execution_count": 262,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "def make_googlemap_link(df,startzip=\"22846\"):\n",
    "    googlempadir_root = 'https://www.google.de/maps/dir/'\n",
    "    root_and_start = googlempadir_root + startzip+'/'\n",
    "    googlemap_url = df.apply(\n",
    "        lambda row: root_and_start + row['plz']\n",
    "        ,axis=1)\n",
    "    loc_url = df.columns.get_loc(\"url\")\n",
    "    test = df.copy()\n",
    "    test.insert(loc_url+1,\"url_maps\",googlemap_url)\n",
    "    return test\n",
    "make_googlemap_link(df)\n",
    "                  "
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 258,
   "id": "pressed-captain",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "Index(['transaktionsArt', 'objektArt', 'datumDownload', 'suchOrt', 'url',\n",
       "       'title', 'preis', 'anzahl_raeume', 'wohnflaeche', 'grundstuecksflaeche',\n",
       "       'weitere_eigenschaften', 'beschreibung', 'plz', 'ortsname',\n",
       "       'Bad mit Wanne', 'Balkon', 'Blockheizkraftwerk', 'Dachterrasse',\n",
       "       'Dielenboden', 'Einbauküche', 'Garage', 'Garten', 'Gartenanteil',\n",
       "       'Gartenmitbenutzung', 'Gäste WC', 'Keine Angabe', 'Kelleranteil',\n",
       "       'Loggia', 'Neubau', 'Neubaustandard', 'Personenaufzug', 'Stellplatz',\n",
       "       'Terrasse', 'Tiefgarage', 'WG geeignet',\n",
       "       'Wohnberechtigungsschein erforderlich', 'Zentralheizung',\n",
       "       'Zwangsversteigerung', 'als Ferienimmobilie geeignet', 'barrierefrei',\n",
       "       'frei', 'provisionsfrei', 'renoviert', 'rollstuhlgerecht', 'saniert',\n",
       "       'seniorengerechtes Wohnen', 'teilweise klimatisiert',\n",
       "       'teilweise unterkellert', 'voll klimatisiert', 'voll unterkellert'],\n",
       "      dtype='object')"
      ]
     },
     "execution_count": 258,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df.columns"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 259,
   "id": "former-honolulu",
   "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>preis</th>\n",
       "      <th>anzahl_raeume</th>\n",
       "      <th>wohnflaeche</th>\n",
       "      <th>transaktionsArt</th>\n",
       "      <th>url</th>\n",
       "      <th>datumDownload</th>\n",
       "      <th>plz</th>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>id</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>2zew943</th>\n",
       "      <td>148900.0</td>\n",
       "      <td>4.0</td>\n",
       "      <td>118.00</td>\n",
       "      <td>kaufen</td>\n",
       "      <td>https://www.immowelt.de/expose/2zew943</td>\n",
       "      <td>2021-05-02</td>\n",
       "      <td>19055</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2zhxb48</th>\n",
       "      <td>230000.0</td>\n",
       "      <td>4.0</td>\n",
       "      <td>110.00</td>\n",
       "      <td>kaufen</td>\n",
       "      <td>https://www.immowelt.de/expose/2zhxb48</td>\n",
       "      <td>2021-05-02</td>\n",
       "      <td>19057</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2z9sl47</th>\n",
       "      <td>247000.0</td>\n",
       "      <td>4.0</td>\n",
       "      <td>108.50</td>\n",
       "      <td>kaufen</td>\n",
       "      <td>https://www.immowelt.de/expose/2z9sl47</td>\n",
       "      <td>2021-05-02</td>\n",
       "      <td>19053</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2zyrl47</th>\n",
       "      <td>247000.0</td>\n",
       "      <td>4.0</td>\n",
       "      <td>108.50</td>\n",
       "      <td>kaufen</td>\n",
       "      <td>https://www.immowelt.de/expose/2zyrl47</td>\n",
       "      <td>2021-05-02</td>\n",
       "      <td>19053</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2yfv74t</th>\n",
       "      <td>330000.0</td>\n",
       "      <td>4.0</td>\n",
       "      <td>120.00</td>\n",
       "      <td>kaufen</td>\n",
       "      <td>https://www.immowelt.de/expose/2yfv74t</td>\n",
       "      <td>2021-05-02</td>\n",
       "      <td>19061</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2xv9r4j</th>\n",
       "      <td>350000.0</td>\n",
       "      <td>4.0</td>\n",
       "      <td>118.00</td>\n",
       "      <td>kaufen</td>\n",
       "      <td>https://www.immowelt.de/expose/2xv9r4j</td>\n",
       "      <td>2021-05-02</td>\n",
       "      <td>19057</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2xyar4j</th>\n",
       "      <td>355000.0</td>\n",
       "      <td>4.0</td>\n",
       "      <td>118.00</td>\n",
       "      <td>kaufen</td>\n",
       "      <td>https://www.immowelt.de/expose/2xyar4j</td>\n",
       "      <td>2021-05-02</td>\n",
       "      <td>19057</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2xmbr4j</th>\n",
       "      <td>355000.0</td>\n",
       "      <td>4.0</td>\n",
       "      <td>118.00</td>\n",
       "      <td>kaufen</td>\n",
       "      <td>https://www.immowelt.de/expose/2xmbr4j</td>\n",
       "      <td>2021-05-02</td>\n",
       "      <td>19057</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2x47r4j</th>\n",
       "      <td>359000.0</td>\n",
       "      <td>4.0</td>\n",
       "      <td>118.00</td>\n",
       "      <td>kaufen</td>\n",
       "      <td>https://www.immowelt.de/expose/2x47r4j</td>\n",
       "      <td>2021-05-02</td>\n",
       "      <td>19057</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2y5524z</th>\n",
       "      <td>394563.0</td>\n",
       "      <td>3.0</td>\n",
       "      <td>101.17</td>\n",
       "      <td>kaufen</td>\n",
       "      <td>https://www.immowelt.de/expose/2y5524z</td>\n",
       "      <td>2021-05-02</td>\n",
       "      <td>19055</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2yhel4w</th>\n",
       "      <td>399900.0</td>\n",
       "      <td>5.5</td>\n",
       "      <td>144.00</td>\n",
       "      <td>kaufen</td>\n",
       "      <td>https://www.immowelt.de/expose/2yhel4w</td>\n",
       "      <td>2021-05-02</td>\n",
       "      <td>19055</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2zz4249</th>\n",
       "      <td>443000.0</td>\n",
       "      <td>4.0</td>\n",
       "      <td>179.00</td>\n",
       "      <td>kaufen</td>\n",
       "      <td>https://www.immowelt.de/expose/2zz4249</td>\n",
       "      <td>2021-05-02</td>\n",
       "      <td>19057</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2zr5249</th>\n",
       "      <td>443000.0</td>\n",
       "      <td>4.0</td>\n",
       "      <td>179.00</td>\n",
       "      <td>kaufen</td>\n",
       "      <td>https://www.immowelt.de/expose/2zr5249</td>\n",
       "      <td>2021-05-02</td>\n",
       "      <td>19057</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2zbjw46</th>\n",
       "      <td>499000.0</td>\n",
       "      <td>4.0</td>\n",
       "      <td>250.00</td>\n",
       "      <td>kaufen</td>\n",
       "      <td>https://www.immowelt.de/expose/2zbjw46</td>\n",
       "      <td>2021-05-02</td>\n",
       "      <td>19061</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2yg5y4s</th>\n",
       "      <td>515000.0</td>\n",
       "      <td>6.0</td>\n",
       "      <td>148.00</td>\n",
       "      <td>kaufen</td>\n",
       "      <td>https://www.immowelt.de/expose/2yg5y4s</td>\n",
       "      <td>2021-05-02</td>\n",
       "      <td>19057</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2zpfw46</th>\n",
       "      <td>535000.0</td>\n",
       "      <td>6.0</td>\n",
       "      <td>192.00</td>\n",
       "      <td>kaufen</td>\n",
       "      <td>https://www.immowelt.de/expose/2zpfw46</td>\n",
       "      <td>2021-05-02</td>\n",
       "      <td>19053</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2ybhl4p</th>\n",
       "      <td>695000.0</td>\n",
       "      <td>8.0</td>\n",
       "      <td>280.00</td>\n",
       "      <td>kaufen</td>\n",
       "      <td>https://www.immowelt.de/expose/2ybhl4p</td>\n",
       "      <td>2021-05-02</td>\n",
       "      <td>19055</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2y5z24y</th>\n",
       "      <td>920000.0</td>\n",
       "      <td>13.0</td>\n",
       "      <td>380.23</td>\n",
       "      <td>kaufen</td>\n",
       "      <td>https://www.immowelt.de/expose/2y5z24y</td>\n",
       "      <td>2021-05-02</td>\n",
       "      <td>19053</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2yuw64t</th>\n",
       "      <td>1100000.0</td>\n",
       "      <td>17.0</td>\n",
       "      <td>439.57</td>\n",
       "      <td>kaufen</td>\n",
       "      <td>https://www.immowelt.de/expose/2yuw64t</td>\n",
       "      <td>2021-05-02</td>\n",
       "      <td>19055</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2yhyp4d</th>\n",
       "      <td>NaN</td>\n",
       "      <td>6.0</td>\n",
       "      <td>250.00</td>\n",
       "      <td>kaufen</td>\n",
       "      <td>https://www.immowelt.de/expose/2yhyp4d</td>\n",
       "      <td>2021-05-02</td>\n",
       "      <td>19063</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "             preis  anzahl_raeume  wohnflaeche transaktionsArt  \\\n",
       "id                                                               \n",
       "2zew943   148900.0            4.0       118.00          kaufen   \n",
       "2zhxb48   230000.0            4.0       110.00          kaufen   \n",
       "2z9sl47   247000.0            4.0       108.50          kaufen   \n",
       "2zyrl47   247000.0            4.0       108.50          kaufen   \n",
       "2yfv74t   330000.0            4.0       120.00          kaufen   \n",
       "2xv9r4j   350000.0            4.0       118.00          kaufen   \n",
       "2xyar4j   355000.0            4.0       118.00          kaufen   \n",
       "2xmbr4j   355000.0            4.0       118.00          kaufen   \n",
       "2x47r4j   359000.0            4.0       118.00          kaufen   \n",
       "2y5524z   394563.0            3.0       101.17          kaufen   \n",
       "2yhel4w   399900.0            5.5       144.00          kaufen   \n",
       "2zz4249   443000.0            4.0       179.00          kaufen   \n",
       "2zr5249   443000.0            4.0       179.00          kaufen   \n",
       "2zbjw46   499000.0            4.0       250.00          kaufen   \n",
       "2yg5y4s   515000.0            6.0       148.00          kaufen   \n",
       "2zpfw46   535000.0            6.0       192.00          kaufen   \n",
       "2ybhl4p   695000.0            8.0       280.00          kaufen   \n",
       "2y5z24y   920000.0           13.0       380.23          kaufen   \n",
       "2yuw64t  1100000.0           17.0       439.57          kaufen   \n",
       "2yhyp4d        NaN            6.0       250.00          kaufen   \n",
       "\n",
       "                                            url datumDownload    plz  \n",
       "id                                                                    \n",
       "2zew943  https://www.immowelt.de/expose/2zew943    2021-05-02  19055  \n",
       "2zhxb48  https://www.immowelt.de/expose/2zhxb48    2021-05-02  19057  \n",
       "2z9sl47  https://www.immowelt.de/expose/2z9sl47    2021-05-02  19053  \n",
       "2zyrl47  https://www.immowelt.de/expose/2zyrl47    2021-05-02  19053  \n",
       "2yfv74t  https://www.immowelt.de/expose/2yfv74t    2021-05-02  19061  \n",
       "2xv9r4j  https://www.immowelt.de/expose/2xv9r4j    2021-05-02  19057  \n",
       "2xyar4j  https://www.immowelt.de/expose/2xyar4j    2021-05-02  19057  \n",
       "2xmbr4j  https://www.immowelt.de/expose/2xmbr4j    2021-05-02  19057  \n",
       "2x47r4j  https://www.immowelt.de/expose/2x47r4j    2021-05-02  19057  \n",
       "2y5524z  https://www.immowelt.de/expose/2y5524z    2021-05-02  19055  \n",
       "2yhel4w  https://www.immowelt.de/expose/2yhel4w    2021-05-02  19055  \n",
       "2zz4249  https://www.immowelt.de/expose/2zz4249    2021-05-02  19057  \n",
       "2zr5249  https://www.immowelt.de/expose/2zr5249    2021-05-02  19057  \n",
       "2zbjw46  https://www.immowelt.de/expose/2zbjw46    2021-05-02  19061  \n",
       "2yg5y4s  https://www.immowelt.de/expose/2yg5y4s    2021-05-02  19057  \n",
       "2zpfw46  https://www.immowelt.de/expose/2zpfw46    2021-05-02  19053  \n",
       "2ybhl4p  https://www.immowelt.de/expose/2ybhl4p    2021-05-02  19055  \n",
       "2y5z24y  https://www.immowelt.de/expose/2y5z24y    2021-05-02  19053  \n",
       "2yuw64t  https://www.immowelt.de/expose/2yuw64t    2021-05-02  19055  \n",
       "2yhyp4d  https://www.immowelt.de/expose/2yhyp4d    2021-05-02  19063  "
      ]
     },
     "execution_count": 259,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "mask = ((df[\"anzahl_raeume\"]>=3) \n",
    "        #& (df[\"suchOrt\"]==\"ludwigslust-meckl\")\n",
    "        & (df[\"suchOrt\"]==\"schwerin\")\n",
    "        & (df[\"wohnflaeche\"]>100) \n",
    "        & (df[\"transaktionsArt\"]==\"kaufen\") \n",
    "        #& (df[\"preis\"]<1500)\n",
    "        & (df[\"datumDownload\"]==pd.to_datetime('today').normalize())\n",
    "        #& (df[\"plz\"]!=\"24568\")\n",
    "       )\n",
    "(df.loc[mask,interessanteCols].sort_values(by=[\"preis\"],ascending=True))"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 263,
   "id": "excellent-mechanics",
   "metadata": {},
   "outputs": [],
   "source": [
    "def open_in_browser(df):\n",
    "    df = make_googlemap_link(df)\n",
    "    mask = ((df[\"anzahl_raeume\"]>=4) \n",
    "        & (df[\"wohnflaeche\"]>120) \n",
    "        & (df[\"transaktionsArt\"]==\"kaufen\") \n",
    "        & (df[\"preis\"]<500000)\n",
    "        & (df[\"datumDownload\"]==pd.to_datetime('today').normalize())\n",
    "       # & (df[\"plz\"]!=\"24568\")\n",
    "        #& (df[\"plz\"]!=\"29493\")\n",
    "        #& (df[\"suchOrt\"]==\"ludwigslust-meckl\")\n",
    "       )\n",
    "    \n",
    "    subsel = (df.loc[mask,interessanteCols+[\"url_maps\"]]\n",
    "              .sort_values(by=[\"preis\",\"anzahl_raeume\"])\n",
    "    )\n",
    "    firefoxPath = \"/Applications/Firefox.app/Contents/MacOS\"\n",
    "    client = webbrowser.get(\"open -a /Applications/Firefox.app %s\")\n",
    "    for url,google in zip(subsel.url,subsel.url_maps):\n",
    "        client.open(url)\n",
    "        #client.open(google)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 264,
   "id": "auburn-interaction",
   "metadata": {},
   "outputs": [],
   "source": [
    "open_in_browser(df)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 265,
   "id": "negative-machinery",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "Index(['transaktionsArt', 'objektArt', 'datumDownload', 'suchOrt', 'url',\n",
       "       'title', 'preis', 'anzahl_raeume', 'wohnflaeche', 'grundstuecksflaeche',\n",
       "       'weitere_eigenschaften', 'beschreibung', 'plz', 'ortsname',\n",
       "       'Bad mit Wanne', 'Balkon', 'Blockheizkraftwerk', 'Dachterrasse',\n",
       "       'Dielenboden', 'Einbauküche', 'Garage', 'Garten', 'Gartenanteil',\n",
       "       'Gartenmitbenutzung', 'Gäste WC', 'Keine Angabe', 'Kelleranteil',\n",
       "       'Loggia', 'Neubau', 'Neubaustandard', 'Personenaufzug', 'Stellplatz',\n",
       "       'Terrasse', 'Tiefgarage', 'WG geeignet',\n",
       "       'Wohnberechtigungsschein erforderlich', 'Zentralheizung',\n",
       "       'Zwangsversteigerung', 'als Ferienimmobilie geeignet', 'barrierefrei',\n",
       "       'frei', 'provisionsfrei', 'renoviert', 'rollstuhlgerecht', 'saniert',\n",
       "       'seniorengerechtes Wohnen', 'teilweise klimatisiert',\n",
       "       'teilweise unterkellert', 'voll klimatisiert', 'voll unterkellert'],\n",
       "      dtype='object')"
      ]
     },
     "execution_count": 265,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df.columns"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "expanded-salem",
   "metadata": {},
   "outputs": [],
   "source": []
  }
 ],
 "metadata": {
  "kernelspec": {
   "display_name": "hauspreis_getting_ids",
   "language": "python",
   "name": "hauspreis_getting_ids"
  },
  "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.8.5"
  }
 },
 "nbformat": 4,
 "nbformat_minor": 5
}