1-Cleaning.ipynb

{
 "cells": [
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "# Capstone Hotel reservation prediction\n",
    "\n",
    "![Logo](img/hotel-logo.png)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## Business Understanding and Set-up\n",
    "Analysis of the booking data of a hotel chain/ hotel cooperation with five properties in locations.\n",
    "\n",
    "### DataSet\n",
    "The dataset contains bookings made in different hotels from one hotel chain.\n",
    "One line corresponds to one person. For example, a double room is usually displayed in two rows with the same booking number.\n",
    "The customer id is also assigned per person and provides information about whether customers book in several houses or more than once in one house.\n",
    "\n",
    "### Final Deliverables\n",
    "- Jupyter notebook following PEP8 designed for data science / technical audience.\n",
    "- Slide deck (pdf / 10min presentation) pushed to GitHub designed for non-technical stakeholders outlining findings and recommendations, as well as future work.\n",
    "\n",
    "### Goal\n",
    "- Help identify repeating customers, as well as the features used for predition.\n",
    "- Recognition of guests who are willing to spend above average amounts of money (called VIPs). \n",
    "- Predict in which quarter and destination customer will book their next stay.\n",
    "\n",
    "### Key Question\n",
    "- How to identify repeating customers?\n",
    "- What distinguishes VIPs from the other guests?\n",
    "- What do repeating customers/ VIPs have in common?\n",
    "\n",
    "### Step by step\n",
    "this notebook:\n",
    "- Merging the three datasets\n",
    "- Cleaning\n",
    " - handling missing values\n",
    " - inconsistency checks\n",
    " - change variable types\n",
    "\n",
    "next notebooks:\n",
    "- Feature engineering\n",
    "- EDA\n",
    "- Basic and advanded modelling\n",
    "- TimeSeries\n",
    "- Conclusion\n",
    "\n",
    "### Outcome/TakeAways\n",
    "- Columns reise_special_event & flag_old have no value for the analysis\n",
    "- Columns lkz & sprache_deutsch have low value for the analysis\n",
    "- Guest of the Viana do Castelo show a different booking behaviour than the rest\n",
    "- Guest from Linz and Düsseldorf are very similar. \n",
    "- XXX is best suited for forecasting regular guests.\n",
    "- To identify solvent customers, XXX delivers the best results.\n",
    "\n",
    "### To-Do-List / Outlook\n",
    "- Adding additional information like:\n",
    " - Events at the destination\n",
    " - Cancellations\n",
    " - Revenue per available room (required capacity for each destination)\n",
    " - Feedback from tripadvisor, yelp, etc. as well as from hotel internal surveys"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### Glossary\n",
    "\n",
    "| Variables | Description | Key | Type | Source \n",
    "| :- |:- | :- | --- | --- |\n",
    "| **buchungsdatum** | Date of booking | YYYY-mm-dd | interval | booking |\n",
    "| **buchungsjahr** | Year of booking |  | interval | new feature |\n",
    "| **buchungsquartal_jahr** | Quarter of booking and year |  | interval | new feature |\n",
    "| **buchungsquartal** | Quarter of booking |  | interval | new feature |\n",
    "| **buchungsmonat_jahr** | Month of booking and year |  | interval | new feature |\n",
    "| **buchungsmonat** | Month of booking |  | interval | new feature |\n",
    "| **buchungswoche** | Week of booking |  | interval | new feature |\n",
    "| **buchungstag** | Day of booking |  | interval | new feature |  \n",
    "| **buchung_wochentag** | Weekday of booking | 0 = Mo - 6 = So | nominal | new feature |\n",
    "| **anreisedatum** | Date of arrival | YYYY-mm-dd | interval | booking | \n",
    "| **anreisejahr** | Year of arrival |  | interval | new feature |\n",
    "| **anreisequartal_jahr** | Quarter of arrival and year |  | interval | new feature |\n",
    "| **anreisequartal** | Quarter of arrival |  | interval | new feature |\n",
    "| **anreisemonat_jahr** | Month of arrival and year |  | interval | new feature |\n",
    "| **anreisemonat** | Month of arrival |  | interval | new feature |\n",
    "| **anreisewoche** | Week of arrival |  | interval | new feature |\n",
    "| **anreisetag** | Day of arrival |  | interval | new feature |\n",
    "| **anreise_wochentag** | Weekday of arrival | 0 = Mo - 6 = So | nominal | new feature |\n",
    "| **abreisedatum** | Date of departure | YYYY-mm-dd | interval | new feature |\n",
    "| **abreisejahr** | Year of departure |  | interval | new feature |\n",
    "| **abreisequartal_jahr** | Quarter of departure and year |  | interval | new feature |\n",
    "| **abreisequartal** | Quarter of departure |  | interval | new feature |\n",
    "| **abreisemonat_jahr** | Month of departure and year |  | interval | new feature |\n",
    "| **abreisemonat** | Month of departure |  | interval | new feature |\n",
    "| **abreisewoche** | Week of departure |  | interval | new feature |\n",
    "| **abreisetag** | Day of departure |  | interval | new feature |\n",
    "| **abreise_wochentag** | Weekday of departure | 0 = Mo - 6 = So | nominal | new feature |\n",
    "| **naechte_insgesamt** | Total nights incl. excursion with nights out |  | ratio | booking | \n",
    "| **gebuchte_naechte** | Booked night at the hotel | | ratio | booking | \n",
    "| **naechte_ausserhalb** | nights out of hotel |  | ratio | new feature |\n",
    "| **aufenthalt** | Difference between depature and arrival date |  | ratio | new feature |\n",
    "| **Diff** | Difference between rows per booking and person per booking |  | ratio | new feature |\n",
    "| **buchungsvorlauf** | Lead booking time in days |  | ratio | new feature |\n",
    "| **anz_buch** | Amount of bookings per guest |  | ratio | new feature |\n",
    "| **gesamtpreis** | Total money spent |  | ratio | booking | \n",
    "| **preis_nacht** | Price per night |  | ratio | new feature |\n",
    "| **personen_anzahl** | Amount of person for reservation |  | ratio | booking |\n",
    "| **preis_per** | Price per Person |  | ratio | new feature |\n",
    "| **preis_nacht_per** | Price per person and night |  | ratio | new feature |\n",
    "| **buch_zeilen** | Rows corresponding to booking number |  | ratio | new feature |\n",
    "| **erstbuchung** | First booking by client | 0 - 1 | dichotomous | new feature |\n",
    "| **folgebuchung** | Follow-up booking by client | 0 - 1 | dichotomous | new feature |\n",
    "| **repeater** | Client is repeating guest | 0 - 1 | dichotomous | new feature |\n",
    "| **VIP** | Solvent client that spends more than 75% of the guest in this destination | 0 - 1 | dichotomous | new feature |\n",
    "| **saison** | Season of booking | YYYY | interval | booking |\n",
    "| **buchungskanal** | Booking source | phone, online or travel agency | nominal | new feature |\n",
    "| **buchung_telefon** | Booking made by phone | 0 - 1 | dichotomous | booking |\n",
    "| **buchung_online** | Booking made online | 0 - 1 | dichotomous | booking |\n",
    "| **buchung_reisebuero** | Booking via travel agency | 0 - 1 | dichotomous | booking |\n",
    "| **zimmerarten** | room category | 0 = basic, 1 = classic, 2 = premium | nominal | booking |\n",
    "| **reise_adventure** | Theme trip: adventure | 0 - 1 | dichotomous | booking |\n",
    "| **reise_relax** | Theme trip: relax | 0 - 1 | dichotomous | booking |\n",
    "| **reise_standard** | Theme trip: standard | 0 - 1 | dichotomous | booking |\n",
    "| **reise_genuss** | Theme trip: enjoyment | 0 - 1 | dichotomous | booking |\n",
    "| **kind** | Child | 0 - 1 | dichotomous | booking |\n",
    "| **entfernung** | Distance |  | ratio | booking |\n",
    "| **zahler** | Payer | 0 - 1 | dichotomous | booking |\n",
    "| **kunden_id** | Client ID |  | nominal | booking/client |\n",
    "| **buchungsnr** | Booking number |  | nominal | booking |\n",
    "| **buchungsnr_reisebuero** | Travel agency booking number |  | nominal | booking |\n",
    "| **menue_code** | Menue Code|  | nominal | booking |\n",
    "| **zusatzleistung** | Additional service |  | nominal | booking |\n",
    "| **destination** | Hotel |  | nominal | booking |\n",
    "| **anrede** | Title |  | nominal | client |\n",
    "| **plz** | ZIP code |  | nominal | client/area |\n",
    "| **ort** | City |  | nominal | client/area |\n",
    "| **lkz** | Country |  | nominal | client |\n",
    "| **kontinent** | continent |  | nominal | client |\n",
    "| **kein_email_ad** | Advertising ban e-mail | 0 - 1 | dichotomous | client |\n",
    "| **kein_brief_ad** | Advertising ban mail | 0 - 1 | dichotomous | client |\n",
    "| **kein_anruf_ad** | Advertising ban Call | 0 - 1 | dichotomous | client |\n",
    "| **kein_katalog_ad** | Advertising ban catalogue | 0 - 1 | dichotomous | client |\n",
    "| **kein_sms_ad** | Advertising ban SMS | 0 - 1 | dichotomous | client |\n",
    "| **keine_werbung** | general advertising ban | 0 - 1 | dichotomous | client |\n",
    "| **werbe_ban** | Advertising channels ban |  | nominal | new feature |\n",
    "| **anz_werbe_ban** | Amount of banned advertising channels | 0 - 5 | ordinal | new feature |\n",
    "| **sprache_deutsch** | Speaks german | 0 - 1 | dichotomous | client |\n",
    "| **jahrgangsalter_klassiert** | Age group of the guest |  | dichotomous | client |\n",
    "| **bundesland** | Federal state |  | dichotomous | area |\n",
    "| **einwohner_adjusted_plz** | Residents adapted for the zip code |  | dichotomous | area |\n",
    "| **bev_m_plz** | Male population of the zip code |  | dichotomous | area |\n",
    "| **bev_w_plz** | Female population of the zip code |  | dichotomous | area |\n",
    "| **bev_insgesamt_ort** | Total population of the city |  | dichotomous | area |\n",
    "| **bev_weiblich_ort** | Female population of the city |  | dichotomous | area |\n",
    "| **bev_maennlich_ort** | Male population of the city |  | dichotomous | area |\n",
    "| **qkm_plz** | Square kilometer of the zip code area |  | dichotomous | area |\n",
    "| **flaeche_ort** | Are of the city |  | dichotomous | area |\n",
    "| **einwohnerdichte_prokm2_plz** | Population density per square kilometers of the zip code |  | dichotomous | area |\n",
    "| **haushalte_regiog_2017_plz** | Households in the region 2017 by zip code |  | dichotomous | area |\n",
    "| **kaufkraft_einwohner_in_eur_regio** | Residents' purchasing power in EUR for the region |  | dichotomous | area |\n",
    "| **kk_einwohner_index_regiog_2017** | Purchasing power of inhabitants Index for the region (2017) |  | dichotomous | area |\n",
    "| **kk_hh_index_regiog_2017** | Federal state |  | dichotomous | area |\n",
    "| **gemeindetyp_regiostar_2016** | Community type Regiostar 2016 |  | dichotomous | area |\n",
    "| **wirtschaftskraftmr1** | economic power mr1 |  | dichotomous | area |\n",
    "| **wirtschaftskraftmr2** | economic power mr2 |  | dichotomous | area |\n",
    "| **flag_old** | marked as old information |  | dichotomous | area |"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### Import required libraries"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 1,
   "metadata": {
    "ExecuteTime": {
     "end_time": "2020-08-22T21:23:33.108309Z",
     "start_time": "2020-08-22T21:23:32.624591Z"
    }
   },
   "outputs": [],
   "source": [
    "import pandas as pd\n",
    "import numpy as np\n",
    "pd.set_option(\"display.max_columns\", None)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "- read both files \n",
    "- Compare if the kunden_id from both csvs has the exact same value for each row\n",
    "- merge them into one DataFrame"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 2,
   "metadata": {
    "ExecuteTime": {
     "end_time": "2020-08-22T21:23:33.958331Z",
     "start_time": "2020-08-22T21:23:33.109307Z"
    }
   },
   "outputs": [
    {
     "data": {
      "text/plain": [
       "True"
      ]
     },
     "execution_count": 2,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df_c = pd.read_csv(\"data/kunden_daten_2018.csv\", sep=\";\", encoding=\"iso-8859-15\")\n",
    "df_b = pd.read_csv(\"data/buchung_daten_2018.csv\", sep=\";\", encoding=\"iso-8859-15\")\n",
    "\n",
    "# check if kunden_id is in the exact same order for both datasets\n",
    "# receiving one value instead of one for each row through the tuple \n",
    "tuple(df_c[\"kunden_id\"].values) == tuple(df_b[\"kunden_id\"].values) "
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Since both kunden_id are exactly the same, we are able to merge both DataFrames to one"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 3,
   "metadata": {
    "ExecuteTime": {
     "end_time": "2020-08-22T21:23:34.022160Z",
     "start_time": "2020-08-22T21:23:33.959328Z"
    }
   },
   "outputs": [
    {
     "data": {
      "text/plain": [
       "(245131, 39)"
      ]
     },
     "execution_count": 3,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df_c.drop(\"kunden_id\", axis=1, inplace=True)\n",
    "df = pd.concat([df_b, df_c], axis=1)\n",
    "df.shape"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "#### Loading and merging area dataset"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 4,
   "metadata": {
    "ExecuteTime": {
     "end_time": "2020-08-22T21:23:34.275114Z",
     "start_time": "2020-08-22T21:23:34.023157Z"
    }
   },
   "outputs": [
    {
     "data": {
      "text/plain": [
       "(245131, 57)"
      ]
     },
     "execution_count": 4,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df_r = pd.read_csv(\"data/export_regio.csv\", sep=\";\") #without encoding=\"iso-8859-15\"\n",
    "\n",
    "# need to convert plz to string, float and integer not possible due to british zip codes\n",
    "df.plz = df.plz.astype(\"str\")\n",
    "df_r.plz = df_r.plz.astype(\"str\")\n",
    "\n",
    "# drop ort (exists in both datasets with small difference)\n",
    "df_r.drop(\"ort\", axis=1, inplace=True)\n",
    "\n",
    "# preparing four letter zip codes in df_r\n",
    "mask = df_r.plz.str.len() == 4\n",
    "df_r.loc[mask, 'plz'] = '0' + df_r.loc[mask, 'plz']\n",
    "\n",
    "df = df.merge(df_r.assign(lkz='DE'), on=['plz', 'lkz'], how='left')\n",
    "df.shape"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Overview about the DataFrame"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 5,
   "metadata": {
    "ExecuteTime": {
     "end_time": "2020-08-22T21:23:34.322049Z",
     "start_time": "2020-08-22T21:23:34.276112Z"
    }
   },
   "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>buchungsdatum</th>\n",
       "      <th>anreisetag</th>\n",
       "      <th>abreisetag</th>\n",
       "      <th>naechte_insgesamt</th>\n",
       "      <th>gebuchte_naechte</th>\n",
       "      <th>personen_anzahl</th>\n",
       "      <th>gesamtpreis</th>\n",
       "      <th>saison</th>\n",
       "      <th>buchung_telefon</th>\n",
       "      <th>buchung_online</th>\n",
       "      <th>buchung_reisebuero</th>\n",
       "      <th>zimmerarten</th>\n",
       "      <th>reise_special_event</th>\n",
       "      <th>reise_adventure</th>\n",
       "      <th>reise_relax</th>\n",
       "      <th>reise_standard</th>\n",
       "      <th>reise_genuss</th>\n",
       "      <th>kind</th>\n",
       "      <th>entfernung</th>\n",
       "      <th>zahler</th>\n",
       "      <th>kunden_id</th>\n",
       "      <th>buchungsnr</th>\n",
       "      <th>buchungsnr_reisebuero</th>\n",
       "      <th>menue_code</th>\n",
       "      <th>zusatzleistung</th>\n",
       "      <th>destination</th>\n",
       "      <th>anrede</th>\n",
       "      <th>plz</th>\n",
       "      <th>ort</th>\n",
       "      <th>lkz</th>\n",
       "      <th>kein_email_ad</th>\n",
       "      <th>kein_brief_ad</th>\n",
       "      <th>kein_anruf_ad</th>\n",
       "      <th>kein_katalog_ad</th>\n",
       "      <th>kein_sms_ad</th>\n",
       "      <th>keine_werbung</th>\n",
       "      <th>kundenclub</th>\n",
       "      <th>sprache_deutsch</th>\n",
       "      <th>jahrgangsalter_klassiert</th>\n",
       "      <th>bundesland</th>\n",
       "      <th>einwohner_adjusted_plz</th>\n",
       "      <th>bev_m_plz</th>\n",
       "      <th>bev_w_plz</th>\n",
       "      <th>bev_insgesamt_ort</th>\n",
       "      <th>bev_weiblich_ort</th>\n",
       "      <th>bev_männlich_ort</th>\n",
       "      <th>qkm_plz</th>\n",
       "      <th>fläche_ort</th>\n",
       "      <th>einwohnerdichte_prokm2_plz</th>\n",
       "      <th>haushalte_regiog_2017_plz</th>\n",
       "      <th>kaufkraft_einwohner_in_eur_regio</th>\n",
       "      <th>kk_einwohner_index_regiog_2017</th>\n",
       "      <th>kk_hh_index_regiog_2017</th>\n",
       "      <th>gemeindetyp_regiostar_2016</th>\n",
       "      <th>wirtschaftskraftmr1</th>\n",
       "      <th>wirtschaftskraftmr2</th>\n",
       "      <th>flag_old</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>29jan2010</td>\n",
       "      <td>02apr2010 00:00:00</td>\n",
       "      <td>10apr2010 00:00:00</td>\n",
       "      <td>8.0</td>\n",
       "      <td>8</td>\n",
       "      <td>2</td>\n",
       "      <td>2982</td>\n",
       "      <td>2010</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>1</td>\n",
       "      <td>classic</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>535.48</td>\n",
       "      <td>1</td>\n",
       "      <td>1</td>\n",
       "      <td>1151</td>\n",
       "      <td>456</td>\n",
       "      <td>5.0</td>\n",
       "      <td>5</td>\n",
       "      <td>linz</td>\n",
       "      <td>Herr</td>\n",
       "      <td>29664</td>\n",
       "      <td>Walsrode</td>\n",
       "      <td>DE</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.0</td>\n",
       "      <td>55-64</td>\n",
       "      <td>Niedersachsen</td>\n",
       "      <td>25173.0</td>\n",
       "      <td>12540.0</td>\n",
       "      <td>12633.0</td>\n",
       "      <td>25173.0</td>\n",
       "      <td>11577.0</td>\n",
       "      <td>11491.0</td>\n",
       "      <td>344.326464</td>\n",
       "      <td>344.326464</td>\n",
       "      <td>73.108</td>\n",
       "      <td>11453.0</td>\n",
       "      <td>20641.89</td>\n",
       "      <td>92.819</td>\n",
       "      <td>98.104</td>\n",
       "      <td>Landregion - Zentrale Stadt</td>\n",
       "      <td>-0.333392</td>\n",
       "      <td>-0.388738</td>\n",
       "      <td>0.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>14jan2010</td>\n",
       "      <td>05apr2010 00:00:00</td>\n",
       "      <td>10apr2010 00:00:00</td>\n",
       "      <td>5.0</td>\n",
       "      <td>5</td>\n",
       "      <td>2</td>\n",
       "      <td>1437</td>\n",
       "      <td>2010</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>1</td>\n",
       "      <td>classic</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>544.72</td>\n",
       "      <td>0</td>\n",
       "      <td>2</td>\n",
       "      <td>40</td>\n",
       "      <td>6413</td>\n",
       "      <td>5.0</td>\n",
       "      <td>5</td>\n",
       "      <td>linz</td>\n",
       "      <td>Frau</td>\n",
       "      <td>50226</td>\n",
       "      <td>Frechen</td>\n",
       "      <td>DE</td>\n",
       "      <td>1</td>\n",
       "      <td>1</td>\n",
       "      <td>1</td>\n",
       "      <td>1</td>\n",
       "      <td>1</td>\n",
       "      <td>1</td>\n",
       "      <td>0</td>\n",
       "      <td>1.0</td>\n",
       "      <td>55-64</td>\n",
       "      <td>Nordrhein-Westfalen</td>\n",
       "      <td>52473.0</td>\n",
       "      <td>25645.0</td>\n",
       "      <td>26828.0</td>\n",
       "      <td>52473.0</td>\n",
       "      <td>26828.0</td>\n",
       "      <td>25645.0</td>\n",
       "      <td>45.118960</td>\n",
       "      <td>45.118960</td>\n",
       "      <td>1162.992</td>\n",
       "      <td>26227.0</td>\n",
       "      <td>25268.58</td>\n",
       "      <td>113.623</td>\n",
       "      <td>111.379</td>\n",
       "      <td>Landregion - Zentrale Stadt</td>\n",
       "      <td>0.853802</td>\n",
       "      <td>-0.345112</td>\n",
       "      <td>0.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>01mar2010</td>\n",
       "      <td>05apr2010 00:00:00</td>\n",
       "      <td>10apr2010 00:00:00</td>\n",
       "      <td>5.0</td>\n",
       "      <td>5</td>\n",
       "      <td>2</td>\n",
       "      <td>1737</td>\n",
       "      <td>2010</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>1</td>\n",
       "      <td>classic</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>606.63</td>\n",
       "      <td>0</td>\n",
       "      <td>3</td>\n",
       "      <td>2134</td>\n",
       "      <td>4322</td>\n",
       "      <td>5.0</td>\n",
       "      <td>5</td>\n",
       "      <td>linz</td>\n",
       "      <td>Herr</td>\n",
       "      <td>23970</td>\n",
       "      <td>Wismar</td>\n",
       "      <td>DE</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.0</td>\n",
       "      <td>75-84</td>\n",
       "      <td>Mecklenburg-Vorpommern</td>\n",
       "      <td>1423.0</td>\n",
       "      <td>692.0</td>\n",
       "      <td>731.0</td>\n",
       "      <td>46442.0</td>\n",
       "      <td>21844.0</td>\n",
       "      <td>20706.0</td>\n",
       "      <td>50.563027</td>\n",
       "      <td>83.131346</td>\n",
       "      <td>17.117</td>\n",
       "      <td>6355.0</td>\n",
       "      <td>18697.65</td>\n",
       "      <td>84.076</td>\n",
       "      <td>73.665</td>\n",
       "      <td>Stadtregion - Kleinstädtischer, dörflicher Raum</td>\n",
       "      <td>-1.012536</td>\n",
       "      <td>-1.012536</td>\n",
       "      <td>0.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>25jan2010</td>\n",
       "      <td>21mar2010 00:00:00</td>\n",
       "      <td>25mar2010 00:00:00</td>\n",
       "      <td>4.0</td>\n",
       "      <td>4</td>\n",
       "      <td>2</td>\n",
       "      <td>1047</td>\n",
       "      <td>2010</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>1</td>\n",
       "      <td>classic</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>421.59</td>\n",
       "      <td>1</td>\n",
       "      <td>4</td>\n",
       "      <td>776</td>\n",
       "      <td>6414</td>\n",
       "      <td>69.0</td>\n",
       "      <td>9</td>\n",
       "      <td>linz</td>\n",
       "      <td>Frau</td>\n",
       "      <td>15732</td>\n",
       "      <td>Eichwalde</td>\n",
       "      <td>DE</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.0</td>\n",
       "      <td>65-74</td>\n",
       "      <td>Brandenburg</td>\n",
       "      <td>14690.0</td>\n",
       "      <td>7302.0</td>\n",
       "      <td>7388.0</td>\n",
       "      <td>14690.0</td>\n",
       "      <td>4135.0</td>\n",
       "      <td>4087.0</td>\n",
       "      <td>11.965427</td>\n",
       "      <td>11.965427</td>\n",
       "      <td>1227.704</td>\n",
       "      <td>7159.0</td>\n",
       "      <td>23442.06</td>\n",
       "      <td>105.410</td>\n",
       "      <td>104.087</td>\n",
       "      <td>Landregion - Städtischer Raum</td>\n",
       "      <td>0.417226</td>\n",
       "      <td>0.417226</td>\n",
       "      <td>0.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>26jan2010</td>\n",
       "      <td>07mar2010 00:00:00</td>\n",
       "      <td>09mar2010 00:00:00</td>\n",
       "      <td>2.0</td>\n",
       "      <td>2</td>\n",
       "      <td>20</td>\n",
       "      <td>5279</td>\n",
       "      <td>2010</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>1</td>\n",
       "      <td>classic</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>37.88</td>\n",
       "      <td>0</td>\n",
       "      <td>5</td>\n",
       "      <td>813</td>\n",
       "      <td>2938</td>\n",
       "      <td>113.0</td>\n",
       "      <td>2</td>\n",
       "      <td>duesseldorf</td>\n",
       "      <td>Frau</td>\n",
       "      <td>53881</td>\n",
       "      <td>Euskirchen</td>\n",
       "      <td>DE</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",
       "      <td>1.0</td>\n",
       "      <td>45-54</td>\n",
       "      <td>Nordrhein-Westfalen</td>\n",
       "      <td>28531.0</td>\n",
       "      <td>13975.0</td>\n",
       "      <td>14556.0</td>\n",
       "      <td>57975.0</td>\n",
       "      <td>29578.0</td>\n",
       "      <td>28397.0</td>\n",
       "      <td>127.679129</td>\n",
       "      <td>139.430913</td>\n",
       "      <td>204.625</td>\n",
       "      <td>11683.0</td>\n",
       "      <td>23328.82</td>\n",
       "      <td>104.901</td>\n",
       "      <td>120.110</td>\n",
       "      <td>Stadtregion - Kleinstädtischer, dörflicher Raum</td>\n",
       "      <td>0.212335</td>\n",
       "      <td>-0.579173</td>\n",
       "      <td>0.0</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "  buchungsdatum          anreisetag          abreisetag  naechte_insgesamt  \\\n",
       "0     29jan2010  02apr2010 00:00:00  10apr2010 00:00:00                8.0   \n",
       "1     14jan2010  05apr2010 00:00:00  10apr2010 00:00:00                5.0   \n",
       "2     01mar2010  05apr2010 00:00:00  10apr2010 00:00:00                5.0   \n",
       "3     25jan2010  21mar2010 00:00:00  25mar2010 00:00:00                4.0   \n",
       "4     26jan2010  07mar2010 00:00:00  09mar2010 00:00:00                2.0   \n",
       "\n",
       "   gebuchte_naechte  personen_anzahl  gesamtpreis  saison  buchung_telefon  \\\n",
       "0                 8                2         2982    2010                0   \n",
       "1                 5                2         1437    2010                0   \n",
       "2                 5                2         1737    2010                0   \n",
       "3                 4                2         1047    2010                0   \n",
       "4                 2               20         5279    2010                0   \n",
       "\n",
       "   buchung_online  buchung_reisebuero zimmerarten  reise_special_event  \\\n",
       "0               0                   1     classic                    0   \n",
       "1               0                   1     classic                    0   \n",
       "2               0                   1     classic                    0   \n",
       "3               0                   1     classic                    0   \n",
       "4               0                   1     classic                    0   \n",
       "\n",
       "   reise_adventure  reise_relax  reise_standard  reise_genuss  kind  \\\n",
       "0                0            0               0             1     0   \n",
       "1                0            0               1             0     0   \n",
       "2                0            0               1             0     0   \n",
       "3                0            1               0             0     0   \n",
       "4                1            0               0             0     0   \n",
       "\n",
       "   entfernung  zahler  kunden_id  buchungsnr  buchungsnr_reisebuero  \\\n",
       "0      535.48       1          1        1151                    456   \n",
       "1      544.72       0          2          40                   6413   \n",
       "2      606.63       0          3        2134                   4322   \n",
       "3      421.59       1          4         776                   6414   \n",
       "4       37.88       0          5         813                   2938   \n",
       "\n",
       "   menue_code  zusatzleistung  destination anrede    plz         ort lkz  \\\n",
       "0         5.0               5         linz   Herr  29664    Walsrode  DE   \n",
       "1         5.0               5         linz   Frau  50226     Frechen  DE   \n",
       "2         5.0               5         linz   Herr  23970      Wismar  DE   \n",
       "3        69.0               9         linz   Frau  15732   Eichwalde  DE   \n",
       "4       113.0               2  duesseldorf   Frau  53881  Euskirchen  DE   \n",
       "\n",
       "   kein_email_ad  kein_brief_ad  kein_anruf_ad  kein_katalog_ad  kein_sms_ad  \\\n",
       "0              0              0              0                0            0   \n",
       "1              1              1              1                1            1   \n",
       "2              0              0              0                0            0   \n",
       "3              0              0              0                0            0   \n",
       "4              0              0              0                0            0   \n",
       "\n",
       "   keine_werbung  kundenclub  sprache_deutsch jahrgangsalter_klassiert  \\\n",
       "0              0           0              1.0                    55-64   \n",
       "1              1           0              1.0                    55-64   \n",
       "2              0           0              1.0                    75-84   \n",
       "3              0           0              1.0                    65-74   \n",
       "4              0           1              1.0                    45-54   \n",
       "\n",
       "               bundesland  einwohner_adjusted_plz  bev_m_plz  bev_w_plz  \\\n",
       "0           Niedersachsen                 25173.0    12540.0    12633.0   \n",
       "1     Nordrhein-Westfalen                 52473.0    25645.0    26828.0   \n",
       "2  Mecklenburg-Vorpommern                  1423.0      692.0      731.0   \n",
       "3             Brandenburg                 14690.0     7302.0     7388.0   \n",
       "4     Nordrhein-Westfalen                 28531.0    13975.0    14556.0   \n",
       "\n",
       "   bev_insgesamt_ort  bev_weiblich_ort  bev_männlich_ort     qkm_plz  \\\n",
       "0            25173.0           11577.0           11491.0  344.326464   \n",
       "1            52473.0           26828.0           25645.0   45.118960   \n",
       "2            46442.0           21844.0           20706.0   50.563027   \n",
       "3            14690.0            4135.0            4087.0   11.965427   \n",
       "4            57975.0           29578.0           28397.0  127.679129   \n",
       "\n",
       "   fläche_ort  einwohnerdichte_prokm2_plz  haushalte_regiog_2017_plz  \\\n",
       "0  344.326464                      73.108                    11453.0   \n",
       "1   45.118960                    1162.992                    26227.0   \n",
       "2   83.131346                      17.117                     6355.0   \n",
       "3   11.965427                    1227.704                     7159.0   \n",
       "4  139.430913                     204.625                    11683.0   \n",
       "\n",
       "   kaufkraft_einwohner_in_eur_regio  kk_einwohner_index_regiog_2017  \\\n",
       "0                          20641.89                          92.819   \n",
       "1                          25268.58                         113.623   \n",
       "2                          18697.65                          84.076   \n",
       "3                          23442.06                         105.410   \n",
       "4                          23328.82                         104.901   \n",
       "\n",
       "   kk_hh_index_regiog_2017                       gemeindetyp_regiostar_2016  \\\n",
       "0                   98.104                      Landregion - Zentrale Stadt   \n",
       "1                  111.379                      Landregion - Zentrale Stadt   \n",
       "2                   73.665  Stadtregion - Kleinstädtischer, dörflicher Raum   \n",
       "3                  104.087                    Landregion - Städtischer Raum   \n",
       "4                  120.110  Stadtregion - Kleinstädtischer, dörflicher Raum   \n",
       "\n",
       "   wirtschaftskraftmr1  wirtschaftskraftmr2  flag_old  \n",
       "0            -0.333392            -0.388738       0.0  \n",
       "1             0.853802            -0.345112       0.0  \n",
       "2            -1.012536            -1.012536       0.0  \n",
       "3             0.417226             0.417226       0.0  \n",
       "4             0.212335            -0.579173       0.0  "
      ]
     },
     "execution_count": 5,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df.head()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## Missings"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 6,
   "metadata": {
    "ExecuteTime": {
     "end_time": "2020-08-22T21:23:34.497333Z",
     "start_time": "2020-08-22T21:23:34.323047Z"
    }
   },
   "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>Number</th>\n",
       "      <th>Percentage</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>jahrgangsalter_klassiert</th>\n",
       "      <td>57516</td>\n",
       "      <td>23.46</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>entfernung</th>\n",
       "      <td>46790</td>\n",
       "      <td>19.09</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>gemeindetyp_regiostar_2016</th>\n",
       "      <td>39833</td>\n",
       "      <td>16.25</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>bundesland</th>\n",
       "      <td>39450</td>\n",
       "      <td>16.09</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>bev_m_plz</th>\n",
       "      <td>39450</td>\n",
       "      <td>16.09</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>bev_w_plz</th>\n",
       "      <td>39450</td>\n",
       "      <td>16.09</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>bev_männlich_ort</th>\n",
       "      <td>39450</td>\n",
       "      <td>16.09</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>bev_weiblich_ort</th>\n",
       "      <td>39450</td>\n",
       "      <td>16.09</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>qkm_plz</th>\n",
       "      <td>39413</td>\n",
       "      <td>16.08</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>bev_insgesamt_ort</th>\n",
       "      <td>39413</td>\n",
       "      <td>16.08</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>einwohner_adjusted_plz</th>\n",
       "      <td>39413</td>\n",
       "      <td>16.08</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>einwohnerdichte_prokm2_plz</th>\n",
       "      <td>39413</td>\n",
       "      <td>16.08</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>fläche_ort</th>\n",
       "      <td>39413</td>\n",
       "      <td>16.08</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>wirtschaftskraftmr2</th>\n",
       "      <td>39401</td>\n",
       "      <td>16.07</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>haushalte_regiog_2017_plz</th>\n",
       "      <td>39401</td>\n",
       "      <td>16.07</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>kaufkraft_einwohner_in_eur_regio</th>\n",
       "      <td>39401</td>\n",
       "      <td>16.07</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>kk_einwohner_index_regiog_2017</th>\n",
       "      <td>39401</td>\n",
       "      <td>16.07</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>kk_hh_index_regiog_2017</th>\n",
       "      <td>39401</td>\n",
       "      <td>16.07</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>wirtschaftskraftmr1</th>\n",
       "      <td>39401</td>\n",
       "      <td>16.07</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>flag_old</th>\n",
       "      <td>39346</td>\n",
       "      <td>16.05</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>lkz</th>\n",
       "      <td>24665</td>\n",
       "      <td>10.06</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>ort</th>\n",
       "      <td>24652</td>\n",
       "      <td>10.06</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>anrede</th>\n",
       "      <td>22980</td>\n",
       "      <td>9.37</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>destination</th>\n",
       "      <td>8655</td>\n",
       "      <td>3.53</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>sprache_deutsch</th>\n",
       "      <td>1089</td>\n",
       "      <td>0.44</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>naechte_insgesamt</th>\n",
       "      <td>332</td>\n",
       "      <td>0.14</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>zimmerarten</th>\n",
       "      <td>129</td>\n",
       "      <td>0.05</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>menue_code</th>\n",
       "      <td>57</td>\n",
       "      <td>0.02</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "                                  Number  Percentage\n",
       "jahrgangsalter_klassiert           57516       23.46\n",
       "entfernung                         46790       19.09\n",
       "gemeindetyp_regiostar_2016         39833       16.25\n",
       "bundesland                         39450       16.09\n",
       "bev_m_plz                          39450       16.09\n",
       "bev_w_plz                          39450       16.09\n",
       "bev_männlich_ort                   39450       16.09\n",
       "bev_weiblich_ort                   39450       16.09\n",
       "qkm_plz                            39413       16.08\n",
       "bev_insgesamt_ort                  39413       16.08\n",
       "einwohner_adjusted_plz             39413       16.08\n",
       "einwohnerdichte_prokm2_plz         39413       16.08\n",
       "fläche_ort                         39413       16.08\n",
       "wirtschaftskraftmr2                39401       16.07\n",
       "haushalte_regiog_2017_plz          39401       16.07\n",
       "kaufkraft_einwohner_in_eur_regio   39401       16.07\n",
       "kk_einwohner_index_regiog_2017     39401       16.07\n",
       "kk_hh_index_regiog_2017            39401       16.07\n",
       "wirtschaftskraftmr1                39401       16.07\n",
       "flag_old                           39346       16.05\n",
       "lkz                                24665       10.06\n",
       "ort                                24652       10.06\n",
       "anrede                             22980        9.37\n",
       "destination                         8655        3.53\n",
       "sprache_deutsch                     1089        0.44\n",
       "naechte_insgesamt                    332        0.14\n",
       "zimmerarten                          129        0.05\n",
       "menue_code                            57        0.02"
      ]
     },
     "execution_count": 6,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "missing = pd.DataFrame(df.isnull().sum().sort_values(ascending=False),columns=['Number'])\n",
    "missing['Percentage'] = round(missing.Number/df.shape[0]*100,2)\n",
    "missing[missing.Number!=0]"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "# Datacleaning"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 7,
   "metadata": {
    "ExecuteTime": {
     "end_time": "2020-08-22T21:23:34.513291Z",
     "start_time": "2020-08-22T21:23:34.498331Z"
    }
   },
   "outputs": [],
   "source": [
    "initial_size = df.shape"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "#### Dropping all bookings not made by Germans"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 8,
   "metadata": {
    "ExecuteTime": {
     "end_time": "2020-08-22T21:23:34.671986Z",
     "start_time": "2020-08-22T21:23:34.515286Z"
    }
   },
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "93.87% of the booking lines are from Germany\n",
      "38186 rows dropped and column lkz\n"
     ]
    }
   ],
   "source": [
    "res = round((df.lkz.value_counts(normalize=True)*100)[0],2)\n",
    "print(f\"{res}% of the booking lines are from Germany\")\n",
    "\n",
    "rows = df[df.lkz != \"DE\"].count()[0]\n",
    "df = df[df.lkz == \"DE\"]\n",
    "\n",
    "# copy lkz for feature continent\n",
    "#df[\"kontinent\"] = df.lkz\n",
    "\n",
    "df.drop(\"lkz\", axis=1, inplace=True)\n",
    "print(f\"{rows} rows dropped and column lkz\")"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "#### Show and drop unique values per column equals 1\n",
    "dropping two more columns with just one value"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 9,
   "metadata": {
    "ExecuteTime": {
     "end_time": "2020-08-22T21:23:35.588303Z",
     "start_time": "2020-08-22T21:23:34.672984Z"
    }
   },
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "Index(['reise_special_event'], dtype='object')\n"
     ]
    }
   ],
   "source": [
    "x = df.nunique()\n",
    "print(df[list(x[x<=1].index)].columns)\n",
    "df.drop(\"reise_special_event\", axis=1, inplace=True)\n",
    "df.drop(\"flag_old\", axis=1, inplace=True)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "#### finding deduplicates \n",
    "\n",
    "(every entry is exact the same)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 10,
   "metadata": {
    "ExecuteTime": {
     "end_time": "2020-08-22T21:23:36.011761Z",
     "start_time": "2020-08-22T21:23:35.590297Z"
    }
   },
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "Duplicate Rows based on all columns are : 0\n"
     ]
    }
   ],
   "source": [
    "duplicateRowsDF  = df[df.duplicated()]\n",
    "print(f\"Duplicate Rows based on all columns are : {len(duplicateRowsDF)}\")"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "#### date of booking, arrival and departure (buchungsdatum, anreisetag and abreisetag)\n",
    "\n",
    "change format and rename columns"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 11,
   "metadata": {
    "ExecuteTime": {
     "end_time": "2020-08-22T21:23:48.701301Z",
     "start_time": "2020-08-22T21:23:36.012759Z"
    }
   },
   "outputs": [],
   "source": [
    "column_names       = {\"anreisetag\": \"anreisedatum\", \"abreisetag\": \"abreisedatum\"}\n",
    "df.rename(columns  = column_names, inplace=True)\n",
    "\n",
    "df[\"buchungsdatum\"] = pd.to_datetime(df[\"buchungsdatum\"])\n",
    "df[\"anreisedatum\"] = pd.to_datetime(df[\"anreisedatum\"])\n",
    "df[\"abreisedatum\"] = pd.to_datetime(df[\"abreisedatum\"])"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Check if buchungsdatum < anreisedatum <\tabreisetag"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 12,
   "metadata": {
    "ExecuteTime": {
     "end_time": "2020-08-22T21:23:48.732219Z",
     "start_time": "2020-08-22T21:23:48.702299Z"
    }
   },
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "21 wrong Buchungsdatum\n",
      "\n",
      "0 wrong Anreisedatum\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>buchungsdatum</th>\n",
       "      <th>anreisedatum</th>\n",
       "      <th>abreisedatum</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>24442</th>\n",
       "      <td>2011-11-14</td>\n",
       "      <td>2011-11-13</td>\n",
       "      <td>2011-11-17</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>40980</th>\n",
       "      <td>2011-12-21</td>\n",
       "      <td>2011-12-11</td>\n",
       "      <td>2011-12-15</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>41599</th>\n",
       "      <td>2017-04-27</td>\n",
       "      <td>2017-04-15</td>\n",
       "      <td>2017-04-18</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>45867</th>\n",
       "      <td>2017-04-27</td>\n",
       "      <td>2017-04-15</td>\n",
       "      <td>2017-04-18</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>58557</th>\n",
       "      <td>2010-05-27</td>\n",
       "      <td>2010-04-13</td>\n",
       "      <td>2010-04-17</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "      buchungsdatum anreisedatum abreisedatum\n",
       "24442    2011-11-14   2011-11-13   2011-11-17\n",
       "40980    2011-12-21   2011-12-11   2011-12-15\n",
       "41599    2017-04-27   2017-04-15   2017-04-18\n",
       "45867    2017-04-27   2017-04-15   2017-04-18\n",
       "58557    2010-05-27   2010-04-13   2010-04-17"
      ]
     },
     "execution_count": 12,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "ab_errors = df.loc[(df['buchungsdatum'] > df['anreisedatum'])] \n",
    "print(f\"{len(ab_errors)} wrong Buchungsdatum\")\n",
    "\n",
    "bc_errors = df.loc[(df['anreisedatum'] > df['abreisedatum'])] \n",
    "print(f\"\\n{len(bc_errors)} wrong Anreisedatum\")\n",
    "\n",
    "ab_errors.iloc[:, 0:3].head()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Dropping all 21 rows"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 13,
   "metadata": {
    "ExecuteTime": {
     "end_time": "2020-08-22T21:23:48.795050Z",
     "start_time": "2020-08-22T21:23:48.733216Z"
    }
   },
   "outputs": [],
   "source": [
    "df.drop(ab_errors.index, inplace=True)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "#### Nights in total (naechte_insgesamt)\n",
    "- will be transformed to integer after feature engeneering\n",
    "- NaNs will be replaced by new feature values"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 14,
   "metadata": {
    "ExecuteTime": {
     "end_time": "2020-08-22T21:23:48.810011Z",
     "start_time": "2020-08-22T21:23:48.796048Z"
    }
   },
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "7.0     87709\n",
      "4.0     49986\n",
      "5.0     33947\n",
      "3.0     10144\n",
      "9.0      6731\n",
      "8.0      4428\n",
      "10.0     3801\n",
      "6.0      3031\n",
      "16.0     2599\n",
      "11.0     1783\n",
      "14.0     1207\n",
      "17.0      476\n",
      "2.0       409\n",
      "NaN       275\n",
      "12.0      219\n",
      "13.0       86\n",
      "15.0       52\n",
      "1.0        41\n",
      "Name: naechte_insgesamt, dtype: int64\n"
     ]
    }
   ],
   "source": [
    "print(df.naechte_insgesamt.value_counts(dropna=False))"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "#### nights booked (gebuchte_naechte)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 15,
   "metadata": {
    "ExecuteTime": {
     "end_time": "2020-08-22T21:23:48.905754Z",
     "start_time": "2020-08-22T21:23:48.811008Z"
    }
   },
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "7       92093\n",
      "4       50894\n",
      "5       34402\n",
      "3        9951\n",
      "9        6136\n",
      "10       3515\n",
      "16       3034\n",
      "6        2253\n",
      "11       1444\n",
      "8        1290\n",
      "14       1174\n",
      "2         380\n",
      "17        119\n",
      "12         47\n",
      "1          47\n",
      "13         39\n",
      "18         24\n",
      "15         17\n",
      "0          15\n",
      "366        13\n",
      "22          8\n",
      "19          5\n",
      "95          5\n",
      "21          2\n",
      "30          2\n",
      "35          2\n",
      "3111        2\n",
      "41          2\n",
      "2198        1\n",
      "31          1\n",
      "20          1\n",
      "36          1\n",
      "311         1\n",
      "316         1\n",
      "192         1\n",
      "66          1\n",
      "135         1\n",
      "Name: gebuchte_naechte, dtype: int64\n",
      "\n",
      "49 entries with 0 or more than 22 nights booked\n",
      "\n",
      "34 with more than 22 nights booked and naechte_insgesamt  NaN\n",
      "\n",
      "Dropping 49 rows with 0 or more than 22 nights booked\n"
     ]
    }
   ],
   "source": [
    "print(df.gebuchte_naechte.value_counts())\n",
    "print(f\"\\n{df.query('gebuchte_naechte > 22 or gebuchte_naechte == 0').count()[0]} entries with 0 or more than 22 nights booked\")\n",
    "\n",
    "res = df.query('gebuchte_naechte > 22 and naechte_insgesamt == \"NaN\"').count()[0]\n",
    "print(f\"\\n{res} with more than 22 nights booked and naechte_insgesamt  NaN\")\n",
    "\n",
    "rows = df.query('gebuchte_naechte > 22 or gebuchte_naechte == 0')\n",
    "df.drop(rows.index, inplace=True)\n",
    "print(f\"\\nDropping {len(rows)} rows with 0 or more than 22 nights booked\")"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "#### amount of persons (personen_anzahl)\n",
    "\n",
    "amount of persons are aggregated"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 16,
   "metadata": {
    "ExecuteTime": {
     "end_time": "2020-08-22T21:23:48.953134Z",
     "start_time": "2020-08-22T21:23:48.907258Z"
    }
   },
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "2    136361\n",
      "4     21928\n",
      "3     14153\n",
      "1      9292\n",
      "6      7674\n",
      "Name: personen_anzahl, dtype: int64\n",
      "\n",
      "61    18\n",
      "28    18\n",
      "66    14\n",
      "21    12\n",
      "60     8\n",
      "Name: personen_anzahl, dtype: int64\n",
      "\n",
      "5109 bookings with more than 10 persons.\n",
      "\n",
      "1351 bookings with more than 20 persons.\n",
      "\n",
      "722 bookings with more than 30 persons.\n",
      "\n",
      "442 bookings with more than 40 persons.\n"
     ]
    }
   ],
   "source": [
    "print(df.personen_anzahl.value_counts().head())\n",
    "print(f\"\\n{df.personen_anzahl.value_counts().tail()}\")\n",
    "print(f\"\\n{df.query('personen_anzahl > 10').count()[0]} bookings with more than 10 persons.\")\n",
    "print(f\"\\n{df.query('personen_anzahl > 20').count()[0]} bookings with more than 20 persons.\")\n",
    "print(f\"\\n{df.query('personen_anzahl > 30').count()[0]} bookings with more than 30 persons.\")\n",
    "print(f\"\\n{df.query('personen_anzahl > 40').count()[0]} bookings with more than 40 persons.\")"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "#### total price (gesamtpreis)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 17,
   "metadata": {
    "ExecuteTime": {
     "end_time": "2020-08-22T21:23:49.048388Z",
     "start_time": "2020-08-22T21:23:48.954132Z"
    }
   },
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "10302 different values for gesamtpreis\n",
      "\n",
      "25 entries with gesamtpreis = 0 dropped\n",
      "\n",
      "16813 entries with gesamtpreis > 10,000\n"
     ]
    }
   ],
   "source": [
    "print(f\"{df.gesamtpreis.nunique()} different values for gesamtpreis\")\n",
    "\n",
    "rows = df.query('gesamtpreis == 0')\n",
    "df.drop(rows.index, inplace=True)\n",
    "print(f\"\\n{len(rows)} entries with gesamtpreis = 0 dropped\")\n",
    "\n",
    "print(f\"\\n{df.query('gesamtpreis > 10_000').count()[0]} entries with gesamtpreis > 10,000\")\n",
    "# vielleicht über arr aufschlussreicher"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "#### buchung_telefon _online and _reisebuero\n",
    "\n",
    "no modifications need to be done"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 18,
   "metadata": {
    "ExecuteTime": {
     "end_time": "2020-08-22T21:23:49.176046Z",
     "start_time": "2020-08-22T21:23:49.049385Z"
    }
   },
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "0    182631\n",
      "1     24219\n",
      "Name: buchung_telefon, dtype: int64\n",
      "\n",
      "0    188548\n",
      "1     18302\n",
      "Name: buchung_online, dtype: int64\n",
      "\n",
      "1    164329\n",
      "0     42521\n",
      "Name: buchung_reisebuero, dtype: int64\n",
      "\n",
      "206850 bookings with a source.\n",
      "0 bookings without a source.\n"
     ]
    }
   ],
   "source": [
    "print(f\"{df.buchung_telefon.value_counts()}\")\n",
    "print(f\"\\n{df.buchung_online.value_counts()}\")\n",
    "print(f\"\\n{df.buchung_reisebuero.value_counts()}\")\n",
    "\n",
    "print(f\"\\n{df.query('buchung_telefon == 1 or buchung_online == 1 or buchung_reisebuero == 1').count()[0]} bookings with a source.\")\n",
    "print(f\"{df.query('buchung_telefon == 0 and buchung_online == 0 and buchung_reisebuero == 0').count()[0]} bookings without a source.\")"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "#### room categories (zimmerarten)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 19,
   "metadata": {
    "ExecuteTime": {
     "end_time": "2020-08-22T21:23:49.222920Z",
     "start_time": "2020-08-22T21:23:49.177044Z"
    }
   },
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "['classic' 'premium' 'basic' nan]\n",
      "\n",
      "zimmerarten\n",
      "basic        5226\n",
      "classic     58229\n",
      "premium    143327\n",
      "Name: gesamtpreis, dtype: int64\n",
      "\n",
      "zimmerarten\n",
      "basic      3367.66\n",
      "classic    3312.05\n",
      "premium    5465.73\n",
      "Name: gesamtpreis, dtype: float64\n",
      "\n",
      "68 NaNs in zimmerarten changed to classic\n"
     ]
    }
   ],
   "source": [
    "print(f'{df.zimmerarten.unique()}')\n",
    "print(f'\\n{df.groupby([\"zimmerarten\"])[\"gesamtpreis\"].count()}')\n",
    "print(f'\\n{round(df.groupby([\"zimmerarten\"])[\"gesamtpreis\"].mean(),2)}')\n",
    "\n",
    "# change NaN to classic\n",
    "print(f\"\\n{df.zimmerarten.isna().sum() } NaNs in zimmerarten changed to classic\")\n",
    "df.zimmerarten.fillna(\"classic\", inplace=True) "
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "#### no modifications for reise_adventure, reise_relax, reise_standard & reise_genuss"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 20,
   "metadata": {
    "ExecuteTime": {
     "end_time": "2020-08-22T21:23:49.347587Z",
     "start_time": "2020-08-22T21:23:49.223918Z"
    }
   },
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "\n",
      "0    196258\n",
      "1     10592\n",
      "Name: reise_adventure, dtype: int64\n",
      "\n",
      "0    156866\n",
      "1     49984\n",
      "Name: reise_relax, dtype: int64\n",
      "\n",
      "1    124660\n",
      "0     82190\n",
      "Name: reise_standard, dtype: int64\n",
      "\n",
      "0    185236\n",
      "1     21614\n",
      "Name: reise_genuss, dtype: int64\n",
      "\n",
      "206850 Buchungen mit Sourceangabe.\n"
     ]
    }
   ],
   "source": [
    "print(f\"\\n{df.reise_adventure.value_counts()}\")\n",
    "print(f\"\\n{df.reise_relax.value_counts()}\")\n",
    "print(f\"\\n{df.reise_standard.value_counts()}\")\n",
    "print(f\"\\n{df.reise_genuss.value_counts()}\")\n",
    "\n",
    "print(f\"\\n{df.query('reise_adventure == 1 or reise_relax == 1 or reise_standard == 1 or reise_genuss == 1').count()[0]} Buchungen mit Sourceangabe.\")"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "#### child (kind) \n",
    "\n",
    "seem to indicate if the room was booked for child or for an adult"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 21,
   "metadata": {
    "ExecuteTime": {
     "end_time": "2020-08-22T21:23:49.363544Z",
     "start_time": "2020-08-22T21:23:49.348585Z"
    }
   },
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "0    202638\n",
      "1      4212\n",
      "Name: kind, dtype: int64\n"
     ]
    }
   ],
   "source": [
    "print(df.kind.value_counts(dropna=False))"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "#### distance (entfernung)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 22,
   "metadata": {
    "ExecuteTime": {
     "end_time": "2020-08-22T21:23:49.457294Z",
     "start_time": "2020-08-22T21:23:49.364542Z"
    }
   },
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "distance in feature entfernung from 0.0 to 2140.6 km\n",
      "\n",
      "6820 entries with distance under 20 km\n",
      "\n",
      "104 entries with distance 0 km dropped\n"
     ]
    }
   ],
   "source": [
    "print(f\"distance in feature entfernung from {df.entfernung.min()} to {df.entfernung.max()} km\")\n",
    "print(f\"\\n{df.query('entfernung < 20').count()[0]} entries with distance under 20 km\")\n",
    "\n",
    "rows= df.query('entfernung == 0')\n",
    "df.drop(rows.index, inplace=True)\n",
    "print(f\"\\n{len(rows)} entries with distance 0 km dropped\")"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "#### payer (zahler)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 23,
   "metadata": {
    "ExecuteTime": {
     "end_time": "2020-08-22T21:23:49.505166Z",
     "start_time": "2020-08-22T21:23:49.459289Z"
    }
   },
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "[1 0]\n",
      "\n",
      "   kind  zahler   count\n",
      "0     0       0  125868\n",
      "1     0       1   76669\n",
      "2     1       0    4187\n",
      "3     1       1      22\n",
      "\n",
      "21 children under 18 had to pay for their rooms themself\n"
     ]
    }
   ],
   "source": [
    "print(df.zahler.unique())\n",
    "\n",
    "print(f\"\\n{df.groupby(['kind','zahler']).size().reset_index(name='count')}\")\n",
    "\n",
    "res = df.query('kind == 1 and zahler == 1 and jahrgangsalter_klassiert == \"<18\"').count()[0]\n",
    "\n",
    "print(f\"\\n{res} children under 18 had to pay for their rooms themself\") "
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "#### client ids (kunden_id)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 24,
   "metadata": {
    "ExecuteTime": {
     "end_time": "2020-08-22T21:23:50.071675Z",
     "start_time": "2020-08-22T21:23:49.507161Z"
    }
   },
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "164725 client ids\n",
      "\n",
      "Are the client ids strictly rising? False\n",
      "\n",
      "Are the booking dates strictly rising? False\n",
      "\n",
      "Are the booking numbers strictly rising? False\n",
      "\n",
      "DataFrame sorted by client id\n",
      "\n",
      "DataFrame not sorted by booking date\n",
      "\n",
      "DataFrame not sorted by booking number\n"
     ]
    }
   ],
   "source": [
    "print(f\"{df.kunden_id.nunique()} client ids\")\n",
    "\n",
    "kunden_liste = list(df.kunden_id)\n",
    "res = all(i < j for i, j in zip(kunden_liste, kunden_liste[1:])) \n",
    "print (f\"\\nAre the client ids strictly rising? {str(res)}\") \n",
    "# Caused by several lines per booking with the same client id\n",
    "\n",
    "booking_dates = list(df.buchungsdatum)\n",
    "res2 = all(i < j for i, j in zip(booking_dates, booking_dates[1:])) \n",
    "print (f\"\\nAre the booking dates strictly rising? {str(res2)}\") \n",
    "\n",
    "booking_numbers = list(df.buchungsnr)\n",
    "res3 = all(i < j for i, j in zip(booking_numbers, booking_numbers[1:])) \n",
    "print (f\"\\nAre the booking numbers strictly rising? {str(res3)}\") \n",
    "\n",
    "print(\"\\nDataFrame sorted by client id\") if kunden_liste == sorted(kunden_liste) else print(\"\\nDataFrame not sorted by client id\")\n",
    "print(\"\\nDataFrame sorted by booking date\") if booking_dates == sorted(booking_dates) else print(\"\\nDataFrame not sorted by booking date\")\n",
    "print(\"\\nDataFrame sorted by booking number\") if booking_numbers == sorted(booking_numbers) else print(\"\\nDataFrame not sorted by booking number\")"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "#### booking number (buchungsnr)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 25,
   "metadata": {
    "ExecuteTime": {
     "end_time": "2020-08-22T21:23:50.087633Z",
     "start_time": "2020-08-22T21:23:50.074669Z"
    }
   },
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "115046 unique booking numbers\n",
      "\n",
      "1.8 rows in average per booking. Indicating more than one person per room.\n"
     ]
    }
   ],
   "source": [
    "print(f\"{df.buchungsnr.nunique()} unique booking numbers\")\n",
    "print(f\"\\n{round(df.shape[0]/df.buchungsnr.nunique(),2)} rows in average per booking. Indicating more than one person per room.\")"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "#### travel agency booking number (buchungsnr_reisebuero)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 26,
   "metadata": {
    "ExecuteTime": {
     "end_time": "2020-08-22T21:23:50.293083Z",
     "start_time": "2020-08-22T21:23:50.088630Z"
    }
   },
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "7768 unique booking numbers from travel agencies\n",
      "\n",
      "206746 entries with travel agency booking number in total\n",
      "\n",
      "164265 through a travel agency\n",
      "\n",
      "Travel agency booking number for bookings via phone: [8316]\n",
      "\n",
      "Travel agency booking number for bookings online: [8319]\n",
      "\n",
      "0 NaNs in buchungsnr_reisebuero\n",
      "\n",
      "21.15 average number of rooms per booking via channel travel agency\n",
      "\n",
      "26.62 average number of rooms per booking per travel agengy boooking number\n"
     ]
    }
   ],
   "source": [
    "print(f\"{df.buchungsnr_reisebuero.nunique()} unique booking numbers from travel agencies\")\n",
    "print(f\"\\n{df.buchungsnr_reisebuero.count()} entries with travel agency booking number in total\")\n",
    "print(f\"\\n{df.query('buchung_reisebuero == 1').count()[0]} through a travel agency\")\n",
    "\n",
    "print(f'\\nTravel agency booking number for bookings via phone: {df[\"buchungsnr_reisebuero\"][(df[\"buchung_reisebuero\"] == 0) & (df[\"buchung_telefon\"] == 1)].unique()}')\n",
    "print(f'\\nTravel agency booking number for bookings online: {df[\"buchungsnr_reisebuero\"][(df[\"buchung_reisebuero\"] == 0) & (df[\"buchung_online\"] == 1)].unique()}')\n",
    "print(f\"\\n{df.buchungsnr_reisebuero.isna().sum() } NaNs in buchungsnr_reisebuero\")\n",
    "\n",
    "print(f\"\\n{round(df.query('buchung_reisebuero == 1').count()[0]/df.buchungsnr_reisebuero.nunique(),2)} average number of rooms per booking via channel travel agency\")\n",
    "print(f\"\\n{round(df.buchungsnr_reisebuero.count()/df.buchungsnr_reisebuero.nunique(),2)} average number of rooms per booking per travel agengy boooking number\")"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "#### menue_code \n",
    "\n",
    "replaced NaN with 0 and changed float to int"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 27,
   "metadata": {
    "ExecuteTime": {
     "end_time": "2020-08-22T21:23:50.340955Z",
     "start_time": "2020-08-22T21:23:50.294081Z"
    }
   },
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "213 different menue options\n",
      "\n",
      "54 entries with NaN for menue_code\n",
      "\n",
      "<class 'float'>    206746\n",
      "Name: menue_code, dtype: int64\n",
      "\n"
     ]
    }
   ],
   "source": [
    "print(f\"{df.menue_code.nunique()} different menue options\")\n",
    "print(f\"\\n{df.menue_code.isna().sum()} entries with NaN for menue_code\")\n",
    "\n",
    "print(f\"\\n{df.menue_code.apply(type).value_counts()}\\n\")\n",
    "\n",
    "# change NaN to 0\n",
    "df.menue_code.fillna(0, inplace=True) \n",
    "\n",
    "# change column from float to int\n",
    "df.menue_code = df.menue_code.astype(\"int64\")"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "#### additional services (zusatzleistung)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 28,
   "metadata": {
    "ExecuteTime": {
     "end_time": "2020-08-22T21:23:50.356912Z",
     "start_time": "2020-08-22T21:23:50.341953Z"
    }
   },
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "13 different additional services\n"
     ]
    },
    {
     "data": {
      "text/plain": [
       "3     30648\n",
       "8     22825\n",
       "2     20765\n",
       "9     20129\n",
       "12    20011\n",
       "4     19833\n",
       "5     19298\n",
       "10    15989\n",
       "6     15141\n",
       "7     11297\n",
       "11     9667\n",
       "1      1139\n",
       "0         4\n",
       "Name: zusatzleistung, dtype: int64"
      ]
     },
     "execution_count": 28,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "print(f\"{df.zusatzleistung.nunique()} different additional services\")\n",
    "df.zusatzleistung.value_counts()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "#### destination"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 29,
   "metadata": {
    "ExecuteTime": {
     "end_time": "2020-08-22T21:23:50.481579Z",
     "start_time": "2020-08-22T21:23:50.357910Z"
    }
   },
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "linz                 94697\n",
      "duesseldorf          79187\n",
      "saint etienne        20473\n",
      "NaN                   7201\n",
      "chalons-sur-marne     4049\n",
      "viana do castelo      1139\n",
      "Name: destination, dtype: int64\n",
      "\n",
      "7201 entries without a destination dropped.\n"
     ]
    }
   ],
   "source": [
    "print(df.destination.value_counts(dropna=False))\n",
    "\n",
    "rows= df.query('destination != destination')\n",
    "df.drop(rows.index, inplace=True)\n",
    "print(f\"\\n{len(rows)} entries without a destination dropped.\")"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "#### title (anrede)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 30,
   "metadata": {
    "ExecuteTime": {
     "end_time": "2020-08-22T21:23:50.513494Z",
     "start_time": "2020-08-22T21:23:50.482577Z"
    }
   },
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "Frau     103890\n",
      "Herr      79530\n",
      "NaN       15979\n",
      "Firma       146\n",
      "Name: anrede, dtype: int64\n",
      "Replace missing values with 'Herr'\n"
     ]
    }
   ],
   "source": [
    "print(df.anrede.value_counts(dropna=False))\n",
    "df.anrede.fillna(\"Herr\", inplace=True)\n",
    "print(\"Replace missing values with 'Herr'\")"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "#### zipcode (plz)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 31,
   "metadata": {
    "ExecuteTime": {
     "end_time": "2020-08-22T21:23:50.545408Z",
     "start_time": "2020-08-22T21:23:50.514492Z"
    }
   },
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "8195 unique zipcodes\n",
      "\n",
      "0 NaNs for plz\n"
     ]
    }
   ],
   "source": [
    "print(f\"{df.plz.nunique()} unique zipcodes\")\n",
    "print(f\"\\n{df.plz.isna().sum()} NaNs for plz\")"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "#### city (ort)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 32,
   "metadata": {
    "ExecuteTime": {
     "end_time": "2020-08-22T21:23:50.577323Z",
     "start_time": "2020-08-22T21:23:50.546406Z"
    }
   },
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "8727 unique cities\n",
      "\n",
      "0 NaNs for ort\n"
     ]
    }
   ],
   "source": [
    "print(f\"{df.ort.nunique()} unique cities\")\n",
    "print(f\"\\n{df.ort.isna().sum()} NaNs for ort\")\n"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "#### no advertisment through different channels (email, phone, etc.)\n",
    "\n",
    "(kein_email_ad / kein_brief_ad / kein_anruf_ad / kein_katalog_ad / kein_sms_ad /keine_werbung)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 33,
   "metadata": {
    "ExecuteTime": {
     "end_time": "2020-08-22T21:23:50.719942Z",
     "start_time": "2020-08-22T21:23:50.578321Z"
    }
   },
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "\n",
      "0    159487\n",
      "1     40058\n",
      "Name: kein_email_ad, dtype: int64\n",
      "\n",
      "0    159533\n",
      "1     40012\n",
      "Name: kein_brief_ad, dtype: int64\n",
      "\n",
      "0    177577\n",
      "1     21968\n",
      "Name: kein_anruf_ad, dtype: int64\n",
      "\n",
      "0    189232\n",
      "1     10313\n",
      "Name: kein_katalog_ad, dtype: int64\n",
      "\n",
      "0    177578\n",
      "1     21967\n",
      "Name: kein_sms_ad, dtype: int64\n",
      "\n",
      "0    160262\n",
      "1     39283\n",
      "Name: keine_werbung, dtype: int64\n",
      "\n",
      "189257 reservations with inforamtion about advertisment allowance.\n",
      "10288 of the reservations with no advertisment at all.\n"
     ]
    }
   ],
   "source": [
    "print(f\"\\n{df.kein_email_ad.value_counts()}\")\n",
    "print(f\"\\n{df.kein_brief_ad.value_counts()}\")\n",
    "print(f\"\\n{df.kein_anruf_ad.value_counts()}\")\n",
    "print(f\"\\n{df.kein_katalog_ad.value_counts()}\")\n",
    "print(f\"\\n{df.kein_sms_ad.value_counts()}\")\n",
    "print(f\"\\n{df.keine_werbung.value_counts()}\")\n",
    "\n",
    "print(f\"\\n{df.query('kein_email_ad == 0 or kein_brief_ad == 0 or kein_anruf_ad == 0 or kein_katalog_ad == 0 or kein_sms_ad == 0 or keine_werbung == 0').count()[0]} reservations with inforamtion about advertisment allowance.\")\n",
    "print(f\"{df.query('kein_email_ad == 1 and kein_brief_ad == 1 and kein_anruf_ad == 1 and kein_katalog_ad == 1 and kein_sms_ad == 1 and keine_werbung == 1').count()[0]} of the reservations with no advertisment at all.\")"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "#### club member (kundenclub)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 34,
   "metadata": {
    "ExecuteTime": {
     "end_time": "2020-08-22T21:23:50.735900Z",
     "start_time": "2020-08-22T21:23:50.720940Z"
    }
   },
   "outputs": [
    {
     "data": {
      "text/plain": [
       "0    153415\n",
       "1     46130\n",
       "Name: kundenclub, dtype: int64"
      ]
     },
     "execution_count": 34,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df.kundenclub.value_counts(dropna=False)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "#### speaks german (sprache_deutsch)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 35,
   "metadata": {
    "ExecuteTime": {
     "end_time": "2020-08-22T21:23:50.845116Z",
     "start_time": "2020-08-22T21:23:50.736898Z"
    }
   },
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "1.0    197711\n",
      "NaN       997\n",
      "0.0       837\n",
      "Name: sprache_deutsch, dtype: int64\n",
      "\n",
      "<class 'float'>    199545\n",
      "Name: sprache_deutsch, dtype: int64\n",
      "\n"
     ]
    }
   ],
   "source": [
    "print(f\"{df.sprache_deutsch.value_counts(dropna=False)}\")\n",
    "\n",
    "#res = df.query('(lkz == \"DE\" or lkz == \"CH\" or lkz == \"Au\") and sprache_deutsch == 0').count()[0]\n",
    "#print(f\"\\n{res} speaking german unknown for german, austrian or swiss citizen. Chaning all to speaking german True\")\n",
    "\n",
    "df.sprache_deutsch.fillna(1, inplace=True) \n",
    "print(f\"\\n{df.sprache_deutsch.apply(type).value_counts()}\\n\")\n",
    "\n",
    "df.sprache_deutsch = df.sprache_deutsch.astype(\"int64\")\n",
    "\n",
    "# since all Country != DE will be dropped, this column can be dropped\n",
    "df.drop(\"sprache_deutsch\", axis=1, inplace=True)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "#### age group (jahrgangsalter_klassiert)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 36,
   "metadata": {
    "ExecuteTime": {
     "end_time": "2020-08-22T21:23:50.892497Z",
     "start_time": "2020-08-22T21:23:50.846114Z"
    }
   },
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "65-74    46571\n",
      "75-84    44668\n",
      "55-64    32005\n",
      "45-54    12941\n",
      "85-94    12633\n",
      "35-44     4161\n",
      "<18       3488\n",
      "25-34     1983\n",
      "18-24     1942\n",
      ">=95      1054\n",
      "Name: jahrgangsalter_klassiert, dtype: int64\n",
      "\n",
      "Show age group for children:\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>jahrgangsalter_klassiert</th>\n",
       "      <th>zahler</th>\n",
       "      <th>count</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>19</th>\n",
       "      <td>18-24</td>\n",
       "      <td>0</td>\n",
       "      <td>818</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>20</th>\n",
       "      <td>18-24</td>\n",
       "      <td>1</td>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>21</th>\n",
       "      <td>25-34</td>\n",
       "      <td>0</td>\n",
       "      <td>11</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>22</th>\n",
       "      <td>&lt;18</td>\n",
       "      <td>0</td>\n",
       "      <td>3312</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>23</th>\n",
       "      <td>&lt;18</td>\n",
       "      <td>1</td>\n",
       "      <td>21</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "   jahrgangsalter_klassiert  zahler  count\n",
       "19                    18-24       0    818\n",
       "20                    18-24       1      1\n",
       "21                    25-34       0     11\n",
       "22                      <18       0   3312\n",
       "23                      <18       1     21"
      ]
     },
     "execution_count": 36,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "print(df.jahrgangsalter_klassiert.value_counts())\n",
    "\n",
    "tmp = df.groupby(['kind','jahrgangsalter_klassiert', 'zahler']).size().reset_index(name='count')\n",
    "\n",
    "print(f\"\\nShow age group for children:\")\n",
    "tmp[tmp.kind == 1].iloc[:,1:4]"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {
    "ExecuteTime": {
     "end_time": "2020-08-01T09:41:45.563850Z",
     "start_time": "2020-08-01T09:41:45.533422Z"
    }
   },
   "source": [
    "#### Adjusted citizen per zip code"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 37,
   "metadata": {
    "ExecuteTime": {
     "end_time": "2020-08-22T21:23:51.035115Z",
     "start_time": "2020-08-22T21:23:50.893495Z"
    }
   },
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "<class 'float'>    199545\n",
      "Name: einwohner_adjusted_plz, dtype: int64\n",
      "\n",
      "NaN        1196\n",
      "54071.0     396\n",
      "19967.0     382\n",
      "56385.0     337\n",
      "48326.0     323\n",
      "Name: einwohner_adjusted_plz, dtype: int64\n",
      "\n",
      "6389 unique values.\n",
      "\n",
      "12345    152\n",
      "10000    115\n",
      "00000    104\n",
      "x         43\n",
      "11111     13\n",
      "Name: plz, dtype: int64\n",
      "\n",
      "Correlated zip code for NaNs in einwohner_adjusted_plz are wrong zip codes.\n",
      "\n",
      "xxxxx    60\n",
      "xxx      55\n",
      "Abcd     52\n",
      "xx       45\n",
      "abcd     34\n",
      "Name: ort, dtype: int64\n",
      "\n",
      "Since city information are also misleading, rows will be dropped.\n",
      "<class 'int'>    198349\n",
      "Name: einwohner_adjusted_plz, dtype: int64\n"
     ]
    }
   ],
   "source": [
    "print(f\"{df.einwohner_adjusted_plz.apply(type).value_counts()}\")\n",
    "print(f\"\\n{df.einwohner_adjusted_plz.value_counts(dropna=False).head()}\")\n",
    "print(f\"\\n{df.einwohner_adjusted_plz.nunique()} unique values.\")\n",
    "\n",
    "\n",
    "print(f\"\\n{df[df.einwohner_adjusted_plz.isna()].plz.value_counts().head()}\")\n",
    "print(f\"\\nCorrelated zip code for NaNs in einwohner_adjusted_plz are wrong zip codes.\")\n",
    "\n",
    "print(f\"\\n{df[df.einwohner_adjusted_plz.isna()].ort.value_counts().head()}\")\n",
    "\n",
    "rows= df[df.einwohner_adjusted_plz.isna()]\n",
    "df.drop(rows.index, inplace=True)\n",
    "\n",
    "print(f\"\\nSince city information are also misleading, rows will be dropped.\")\n",
    "\n",
    "df.einwohner_adjusted_plz = df.einwohner_adjusted_plz.astype(\"int64\")\n",
    "print(f\"{df.einwohner_adjusted_plz.apply(type).value_counts()}\")"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "#### Federal state (bundesland)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 38,
   "metadata": {
    "ExecuteTime": {
     "end_time": "2020-08-22T21:23:51.130860Z",
     "start_time": "2020-08-22T21:23:51.036113Z"
    }
   },
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "Nordrhein-Westfalen       55356\n",
      "Bayern                    31672\n",
      "Baden-Württemberg         21442\n",
      "Niedersachsen             19483\n",
      "Hessen                    14989\n",
      "Rheinland-Pfalz            9662\n",
      "Berlin                     7147\n",
      "Schleswig-Holstein         7090\n",
      "Sachsen                    6169\n",
      "Thüringen                  4822\n",
      "Brandenburg                4610\n",
      "Mecklenburg-Vorpommern     4559\n",
      "Hamburg                    4346\n",
      "Sachsen-Anhalt             4339\n",
      "Bremen                     1447\n",
      "Saarland                   1179\n",
      "NaN                          37\n",
      "Name: bundesland, dtype: int64\n",
      "\n",
      "37 rows dropped.\n"
     ]
    }
   ],
   "source": [
    "print(f\"{df.bundesland.value_counts(dropna=False)}\")\n",
    "\n",
    "rows= df[df.bundesland.isna()]\n",
    "df.drop(rows.index, inplace=True)\n",
    "\n",
    "print(f\"\\n{len(rows)} rows dropped.\")"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "#### bev_m_plz"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 39,
   "metadata": {
    "ExecuteTime": {
     "end_time": "2020-08-22T21:23:51.193692Z",
     "start_time": "2020-08-22T21:23:51.131858Z"
    }
   },
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "26105.0    396\n",
      "27590.0    337\n",
      "23544.0    323\n",
      "27323.0    302\n",
      "25645.0    300\n",
      "Name: bev_m_plz, dtype: int64\n",
      "\n",
      "0 NaNs.\n",
      "\n",
      "<class 'int'>    198312\n",
      "Name: bev_m_plz, dtype: int64\n"
     ]
    }
   ],
   "source": [
    "print(f\"{df.bev_m_plz.value_counts(dropna=False).head()}\")\n",
    "print(f\"\\n{df.bev_m_plz.isna().sum()} NaNs.\")\n",
    "\n",
    "df.bev_m_plz = df.bev_m_plz.astype(\"int64\")\n",
    "print(f\"\\n{df.bev_m_plz.apply(type).value_counts()}\")"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "#### bev_w_plz"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 40,
   "metadata": {
    "ExecuteTime": {
     "end_time": "2020-08-22T21:23:51.270486Z",
     "start_time": "2020-08-22T21:23:51.194689Z"
    }
   },
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "27966.0    396\n",
      "28795.0    337\n",
      "24782.0    323\n",
      "28444.0    302\n",
      "26828.0    300\n",
      "Name: bev_w_plz, dtype: int64\n",
      "\n",
      "0 NaNs.\n",
      "\n",
      "<class 'int'>    198312\n",
      "Name: bev_w_plz, dtype: int64\n"
     ]
    }
   ],
   "source": [
    "print(f\"{df.bev_w_plz.value_counts(dropna=False).head()}\")\n",
    "print(f\"\\n{df.bev_w_plz.isna().sum()} NaNs.\")\n",
    "\n",
    "df.bev_w_plz = df.bev_w_plz.astype(\"int64\")\n",
    "print(f\"\\n{df.bev_w_plz.apply(type).value_counts()}\")"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "#### bev_insgesamt_ort"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 41,
   "metadata": {
    "ExecuteTime": {
     "end_time": "2020-08-22T21:23:51.333318Z",
     "start_time": "2020-08-22T21:23:51.271484Z"
    }
   },
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "1066252.0    4882\n",
      "1860977.0    4346\n",
      "1471505.0    3506\n",
      "583108.0     1535\n",
      "620757.0     1505\n",
      "Name: bev_insgesamt_ort, dtype: int64\n",
      "\n",
      "0 NaNs.\n",
      "\n",
      "<class 'int'>    198312\n",
      "Name: bev_insgesamt_ort, dtype: int64\n"
     ]
    }
   ],
   "source": [
    "print(f\"{df.bev_insgesamt_ort.value_counts(dropna=False).head()}\")\n",
    "print(f\"\\n{df.bev_insgesamt_ort.isna().sum()} NaNs.\")\n",
    "\n",
    "df.bev_insgesamt_ort = df.bev_insgesamt_ort.astype(\"int64\")\n",
    "print(f\"\\n{df.bev_insgesamt_ort.apply(type).value_counts()}\")"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "#### bev_weiblich_ort"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 42,
   "metadata": {
    "ExecuteTime": {
     "end_time": "2020-08-22T21:23:51.395663Z",
     "start_time": "2020-08-22T21:23:51.334316Z"
    }
   },
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "1852025.0    7147\n",
      "556296.0     5001\n",
      "939131.0     4346\n",
      "754200.0     3585\n",
      "319979.0     1560\n",
      "Name: bev_weiblich_ort, dtype: int64\n",
      "\n",
      "0 NaNs.\n",
      "\n",
      "<class 'int'>    198312\n",
      "Name: bev_weiblich_ort, dtype: int64\n"
     ]
    }
   ],
   "source": [
    "print(f\"{df.bev_weiblich_ort.value_counts(dropna=False).head()}\")\n",
    "print(f\"\\n{df.bev_weiblich_ort.isna().sum()} NaNs.\")\n",
    "\n",
    "df.bev_weiblich_ort = df.bev_weiblich_ort.astype(\"int64\")\n",
    "print(f\"\\n{df.bev_weiblich_ort.apply(type).value_counts()}\")"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "#### rename columns"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 43,
   "metadata": {
    "ExecuteTime": {
     "end_time": "2020-08-22T21:23:51.459002Z",
     "start_time": "2020-08-22T21:23:51.396661Z"
    }
   },
   "outputs": [],
   "source": [
    "column_names       = {\"bev_männlich_ort\": \"bev_maennlich_ort\", \"fläche_ort\": \"flaeche_ort\"}\n",
    "df.rename(columns  = column_names, inplace=True)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "#### bev_maennlich_ort"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 44,
   "metadata": {
    "ExecuteTime": {
     "end_time": "2020-08-22T21:23:51.506875Z",
     "start_time": "2020-08-22T21:23:51.459999Z"
    }
   },
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "1792801.0    7147\n",
      "529368.0     5001\n",
      "902048.0     4346\n",
      "717308.0     3585\n",
      "299315.0     1560\n",
      "Name: bev_maennlich_ort, dtype: int64\n",
      "\n",
      "0 NaNs.\n",
      "\n",
      "<class 'int'>    198312\n",
      "Name: bev_maennlich_ort, dtype: int64\n"
     ]
    }
   ],
   "source": [
    "print(f\"{df.bev_maennlich_ort.value_counts(dropna=False).head()}\")\n",
    "print(f\"\\n{df.bev_maennlich_ort.isna().sum()} NaNs.\")\n",
    "\n",
    "df.bev_maennlich_ort = df.bev_maennlich_ort.astype(\"int64\")\n",
    "print(f\"\\n{df.bev_maennlich_ort.apply(type).value_counts()}\")"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "#### qkm_plz"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 45,
   "metadata": {
    "ExecuteTime": {
     "end_time": "2020-08-22T21:23:51.522832Z",
     "start_time": "2020-08-22T21:23:51.507873Z"
    }
   },
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "72.380213    396\n",
      "75.824964    337\n",
      "82.674026    323\n",
      "34.203325    302\n",
      "45.118960    300\n",
      "Name: qkm_plz, dtype: int64\n",
      "\n",
      "0 NaNs.\n"
     ]
    }
   ],
   "source": [
    "print(f\"{df.qkm_plz.value_counts(dropna=False).head()}\")\n",
    "print(f\"\\n{df.qkm_plz.isna().sum()} NaNs.\")"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "#### flaeche_ort"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 46,
   "metadata": {
    "ExecuteTime": {
     "end_time": "2020-08-22T21:23:51.537792Z",
     "start_time": "2020-08-22T21:23:51.523829Z"
    }
   },
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "404.873529    4882\n",
      "794.661540    4346\n",
      "310.893670    3506\n",
      "210.316002    1535\n",
      "215.063404    1505\n",
      "Name: flaeche_ort, dtype: int64\n",
      "\n",
      "0 NaNs.\n"
     ]
    }
   ],
   "source": [
    "print(f\"{df.flaeche_ort.value_counts(dropna=False).head()}\")\n",
    "print(f\"\\n{df.flaeche_ort.isna().sum()} NaNs.\")"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "#### einwohnerdichte_prokm2_plz"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 47,
   "metadata": {
    "ExecuteTime": {
     "end_time": "2020-08-22T21:23:51.569215Z",
     "start_time": "2020-08-22T21:23:51.538790Z"
    }
   },
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "747.041     396\n",
      "743.621     337\n",
      "584.537     323\n",
      "1630.456    302\n",
      "1162.992    300\n",
      "Name: einwohnerdichte_prokm2_plz, dtype: int64\n",
      "\n",
      "0 NaNs.\n"
     ]
    }
   ],
   "source": [
    "print(f\"{df.einwohnerdichte_prokm2_plz.value_counts(dropna=False).head()}\")\n",
    "print(f\"\\n{df.einwohnerdichte_prokm2_plz.isna().sum()} NaNs.\")"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "#### haushalte_regiog_2017_plz"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 48,
   "metadata": {
    "ExecuteTime": {
     "end_time": "2020-08-22T21:23:51.712337Z",
     "start_time": "2020-08-22T21:23:51.570212Z"
    }
   },
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "25601.0    396\n",
      "26298.0    337\n",
      "21417.0    323\n",
      "25970.0    302\n",
      "26227.0    300\n",
      "Name: haushalte_regiog_2017_plz, dtype: int64\n",
      "\n",
      "11 rows with NaNs dropped.\n",
      "\n",
      "<class 'int'>    198301\n",
      "Name: haushalte_regiog_2017_plz, dtype: int64\n"
     ]
    }
   ],
   "source": [
    "print(f\"{df.haushalte_regiog_2017_plz.value_counts(dropna=False).head()}\")\n",
    "\n",
    "rows= df[df.haushalte_regiog_2017_plz.isna()]\n",
    "df.drop(rows.index, inplace=True)\n",
    "\n",
    "print(f\"\\n{len(rows)} rows with NaNs dropped.\")\n",
    "\n",
    "df.haushalte_regiog_2017_plz = df.haushalte_regiog_2017_plz.astype(\"int64\")\n",
    "print(f\"\\n{df.haushalte_regiog_2017_plz.apply(type).value_counts()}\")"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "#### kaufkraft_einwohner_in_eur_regio"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 49,
   "metadata": {
    "ExecuteTime": {
     "end_time": "2020-08-22T21:23:51.728295Z",
     "start_time": "2020-08-22T21:23:51.713335Z"
    }
   },
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "28414.48    396\n",
      "20620.07    337\n",
      "24458.49    323\n",
      "24212.53    302\n",
      "25268.58    300\n",
      "Name: kaufkraft_einwohner_in_eur_regio, dtype: int64\n",
      "\n",
      "0 NaNs.\n"
     ]
    }
   ],
   "source": [
    "print(f\"{df.kaufkraft_einwohner_in_eur_regio.value_counts(dropna=False).head()}\")\n",
    "print(f\"\\n{df.kaufkraft_einwohner_in_eur_regio.isna().sum()} NaNs.\")"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "#### kk_einwohner_index_regiog_2017"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 50,
   "metadata": {
    "ExecuteTime": {
     "end_time": "2020-08-22T21:23:51.760210Z",
     "start_time": "2020-08-22T21:23:51.729293Z"
    }
   },
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "127.769    396\n",
      "92.721     337\n",
      "109.980    323\n",
      "108.875    302\n",
      "98.591     300\n",
      "Name: kk_einwohner_index_regiog_2017, dtype: int64\n",
      "\n",
      "0 NaNs.\n"
     ]
    }
   ],
   "source": [
    "print(f\"{df.kk_einwohner_index_regiog_2017.value_counts(dropna=False).head()}\")\n",
    "print(f\"\\n{df.kk_einwohner_index_regiog_2017.isna().sum()} NaNs.\")"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "#### kk_hh_index_regiog_2017"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 51,
   "metadata": {
    "ExecuteTime": {
     "end_time": "2020-08-22T21:23:51.776167Z",
     "start_time": "2020-08-22T21:23:51.761207Z"
    }
   },
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "115.470    408\n",
      "133.739    396\n",
      "97.460     337\n",
      "120.943    323\n",
      "111.379    300\n",
      "Name: kk_hh_index_regiog_2017, dtype: int64\n",
      "\n",
      "0 NaNs.\n"
     ]
    }
   ],
   "source": [
    "print(f\"{df.kk_hh_index_regiog_2017.value_counts(dropna=False).head()}\")\n",
    "print(f\"\\n{df.kk_hh_index_regiog_2017.isna().sum()} NaNs.\")"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "#### gemeindetyp_regiostar_2016"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 52,
   "metadata": {
    "ExecuteTime": {
     "end_time": "2020-08-22T21:23:51.855953Z",
     "start_time": "2020-08-22T21:23:51.777164Z"
    }
   },
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "Landregion - Städtischer Raum                     47897\n",
      "Landregion - Zentrale Stadt                       38238\n",
      "Landregion - Kleinstädtischer, dörflicher Raum    36564\n",
      "Stadtregion - Metropole                           33626\n",
      "Stadtregion - Mittelstadt, städtischer Raum       16468\n",
      "Name: gemeindetyp_regiostar_2016, dtype: int64\n",
      "\n",
      "372 NaNs for 84 different Cities.\n"
     ]
    }
   ],
   "source": [
    "print(f\"{df.gemeindetyp_regiostar_2016.value_counts(dropna=False).head()}\")\n",
    "print(f\"\\n{df.gemeindetyp_regiostar_2016.isna().sum()} NaNs for {df[df.gemeindetyp_regiostar_2016.isna()].ort.nunique()} different Cities.\")\n",
    "\n",
    "df.gemeindetyp_regiostar_2016.fillna(\"Stadtregion - Mittelstadt, städtischer Raum\", inplace=True)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "#### wirtschaftskraftmr1"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 53,
   "metadata": {
    "ExecuteTime": {
     "end_time": "2020-08-22T21:23:51.870914Z",
     "start_time": "2020-08-22T21:23:51.856951Z"
    }
   },
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      " 1.355972    396\n",
      "-0.076041    337\n",
      " 0.648826    323\n",
      " 0.605635    302\n",
      " 0.853802    300\n",
      "Name: wirtschaftskraftmr1, dtype: int64\n",
      "\n",
      "0 NaNs.\n"
     ]
    }
   ],
   "source": [
    "print(f\"{df.wirtschaftskraftmr1.value_counts(dropna=False).head()}\")\n",
    "print(f\"\\n{df.wirtschaftskraftmr1.isna().sum()} NaNs.\")"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "#### wirtschaftskraftmr2"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 54,
   "metadata": {
    "ExecuteTime": {
     "end_time": "2020-08-22T21:23:51.885873Z",
     "start_time": "2020-08-22T21:23:51.871912Z"
    }
   },
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      " 0.342068    396\n",
      "-0.998071    337\n",
      " 0.402773    323\n",
      "-0.267881    302\n",
      "-0.345112    300\n",
      "Name: wirtschaftskraftmr2, dtype: int64\n",
      "\n",
      "0 NaNs.\n"
     ]
    }
   ],
   "source": [
    "print(f\"{df.wirtschaftskraftmr2.value_counts(dropna=False).head()}\")\n",
    "print(f\"\\n{df.wirtschaftskraftmr2.isna().sum()} NaNs.\")"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## Change of the data set after the adjustments"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Drop rate for rows in percent"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 55,
   "metadata": {
    "ExecuteTime": {
     "end_time": "2020-08-22T21:23:51.901831Z",
     "start_time": "2020-08-22T21:23:51.886871Z"
    }
   },
   "outputs": [
    {
     "data": {
      "text/plain": [
       "19.1"
      ]
     },
     "execution_count": 55,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "round((round(1-(df.shape[0] / initial_size[0]),4)*100),2)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Drop rate for columns in percent"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 56,
   "metadata": {
    "ExecuteTime": {
     "end_time": "2020-08-22T21:23:51.933746Z",
     "start_time": "2020-08-22T21:23:51.902829Z"
    }
   },
   "outputs": [
    {
     "data": {
      "text/plain": [
       "7.02"
      ]
     },
     "execution_count": 56,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "(round(1-(df.shape[1]/ initial_size[1]),4)*100) "
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Missing value reduction of the columns that are not dropped"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 57,
   "metadata": {
    "ExecuteTime": {
     "end_time": "2020-08-22T21:23:52.011537Z",
     "start_time": "2020-08-22T21:23:51.934743Z"
    }
   },
   "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>Number</th>\n",
       "      <th>Percentage</th>\n",
       "      <th>new_Number</th>\n",
       "      <th>new_Percentage</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>jahrgangsalter_klassiert</th>\n",
       "      <td>57516</td>\n",
       "      <td>23.46</td>\n",
       "      <td>37896.0</td>\n",
       "      <td>19.1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>entfernung</th>\n",
       "      <td>46790</td>\n",
       "      <td>19.09</td>\n",
       "      <td>251.0</td>\n",
       "      <td>0.1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>naechte_insgesamt</th>\n",
       "      <td>332</td>\n",
       "      <td>0.14</td>\n",
       "      <td>222.0</td>\n",
       "      <td>0.1</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "                          Number  Percentage  new_Number  new_Percentage\n",
       "jahrgangsalter_klassiert   57516       23.46     37896.0            19.1\n",
       "entfernung                 46790       19.09       251.0             0.1\n",
       "naechte_insgesamt            332        0.14       222.0             0.1"
      ]
     },
     "execution_count": 57,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "missing['new_Number'] = df.isnull().sum()\n",
    "missing['new_Percentage'] = round(missing.new_Number/df.shape[0]*100,1)\n",
    "missing[missing.new_Number>0.01]"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### Handling the remaining missing values"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "#### Distance (entfernung)\n",
    "next and previous zip code will be used. "
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 58,
   "metadata": {
    "ExecuteTime": {
     "end_time": "2020-08-22T21:23:54.897393Z",
     "start_time": "2020-08-22T21:23:52.012536Z"
    }
   },
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "\n",
      "114 less NaNs for distance.\n",
      " Rest will be dropped.\n"
     ]
    }
   ],
   "source": [
    "nan_entf = df.entfernung.isna().sum()\n",
    "\n",
    "plz_list = df[df.entfernung.isna()].plz.unique()\n",
    "\n",
    "for entry in plz_list:\n",
    "    for desti in df[df.plz == entry].destination.unique():\n",
    "        res = df.query(\"plz == @entry & destination == @desti\").entfernung.unique()\n",
    "        if len(res) == 1: \n",
    "            nentry = str(int(entry) + 1)\n",
    "            res = df.query(\"plz == @nentry & destination == @desti\").entfernung.unique()\n",
    "            if len(res) == 1:\n",
    "                mask = df.query(\"plz == @entry & destination == @desti\")\n",
    "                df.loc[mask.index, 'entfernung'] = res\n",
    "\n",
    "plz_list = df[df.entfernung.isna()].plz.unique()\n",
    "\n",
    "for entry in plz_list:\n",
    "    for desti in df[df.plz == entry].destination.unique():\n",
    "        res = df.query(\"plz == @entry & destination == @desti\").entfernung.unique()\n",
    "        if len(res) == 1: \n",
    "            nentry = str(int(entry) - 1)\n",
    "            res = df.query(\"plz == @nentry & destination == @desti\").entfernung.unique()\n",
    "            if len(res) == 1:\n",
    "                mask = df.query(\"plz == @entry & destination == @desti\")\n",
    "                df.loc[mask.index, 'entfernung'] = res\n",
    "                \n",
    "new_nan_entf = df.entfernung.isna().sum()  \n",
    "df = df[df['entfernung'].notna()]\n",
    "print(f\"\\n{nan_entf - new_nan_entf} less NaNs for distance.\\n Rest will be dropped.\")"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "#### jahrgangsalter_klassiert\n",
    "Source: regionalstatistik.de\n",
    "Bevölkerung nach Geschlecht und Altersjahren - Stichtag 09.05.2011 - Ebenen Bundesland"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 59,
   "metadata": {
    "ExecuteTime": {
     "end_time": "2020-08-22T21:23:55.259425Z",
     "start_time": "2020-08-22T21:23:54.898392Z"
    }
   },
   "outputs": [],
   "source": [
    "df.bundesland.replace(\"Baden-Württemberg\", \"Baden-Wuerttemberg\", inplace=True)\n",
    "df.bundesland.replace(\"Thüringen\", \"Thueringen\", inplace=True)\n",
    "\n",
    "df.jahrgangsalter_klassiert.fillna('35-44', inplace=True) \n",
    "\n",
    "mask = (df['jahrgangsalter_klassiert'] == '35-44') & (df['bundesland'] == 'Brandenburg') & (df['anrede'] == 'Frau')\n",
    "df.loc[mask, 'jahrgangsalter_klassiert'] = '45-54'\n",
    "\n",
    "mask = (df['jahrgangsalter_klassiert'] == '35-44') & (df['bundesland'] == 'Bremen') & (df['anrede'] == 'Frau')\n",
    "df.loc[mask, 'jahrgangsalter_klassiert'] = '45-54'\n",
    "\n",
    "mask = (df['jahrgangsalter_klassiert'] == '35-44') & (df['bundesland'] == 'Mecklenburg-Vorpommern') & (df['anrede'] == 'Frau')\n",
    "df.loc[mask, 'jahrgangsalter_klassiert'] = '45-54'\n",
    "\n",
    "mask = (df['jahrgangsalter_klassiert'] == '35-44') & (df['bundesland'] == 'Niedersachsen') & (df['anrede'] == 'Frau')\n",
    "df.loc[mask, 'jahrgangsalter_klassiert'] = '45-54'\n",
    "\n",
    "mask = (df['jahrgangsalter_klassiert'] == '35-44') & (df['bundesland'] == 'Nordrhein-Westfalen') & (df['anrede'] == 'Frau')\n",
    "df.loc[mask, 'jahrgangsalter_klassiert'] = '45-54'\n",
    "\n",
    "mask = (df['jahrgangsalter_klassiert'] == '35-44') & (df['bundesland'] == 'Rheinland-Pfalz') & (df['anrede'] == 'Frau')\n",
    "df.loc[mask, 'jahrgangsalter_klassiert'] = '45-54'\n",
    "\n",
    "mask = (df['jahrgangsalter_klassiert'] == '35-44') & (df['bundesland'] == 'Saarland') & (df['anrede'] == 'Frau')\n",
    "df.loc[mask, 'jahrgangsalter_klassiert'] = '45-54'\n",
    "\n",
    "mask = (df['jahrgangsalter_klassiert'] == '35-44') & (df['bundesland'] == 'Sachsen') & (df['anrede'] == 'Frau')\n",
    "df.loc[mask, 'jahrgangsalter_klassiert'] = '45-54'\n",
    "\n",
    "mask = (df['jahrgangsalter_klassiert'] == '35-44') & (df['bundesland'] == 'Sachsen-Anhalt') & (df['anrede'] == 'Herr')\n",
    "df.loc[mask, 'jahrgangsalter_klassiert'] = '45-54'\n",
    "mask = (df['jahrgangsalter_klassiert'] == '35-44') & (df['bundesland'] == 'Sachsen-Anhalt') & (df['anrede'] == 'Frau')\n",
    "df.loc[mask, 'jahrgangsalter_klassiert'] = '45-54'\n",
    "\n",
    "mask = (df['jahrgangsalter_klassiert'] == '35-44') & (df['bundesland'] == 'Schleswig-Holstein') & (df['anrede'] == 'Frau')\n",
    "df.loc[mask, 'jahrgangsalter_klassiert'] = '45-54'\n",
    "\n",
    "mask = (df['jahrgangsalter_klassiert'] == '35-44') & (df['bundesland'] == 'Thueringen') & (df['anrede'] == 'Frau')\n",
    "df.loc[mask, 'jahrgangsalter_klassiert'] = '45-54'"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "#### Nights in total (naechte_insgesamt) \n",
    "NaNs will be replaced by new faeture values (see next notebook)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "##  new merged and cleaned dataset.csv"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 60,
   "metadata": {
    "ExecuteTime": {
     "end_time": "2020-08-22T21:24:01.954573Z",
     "start_time": "2020-08-22T21:23:55.260423Z"
    }
   },
   "outputs": [],
   "source": [
    "df.to_csv(\"data/cleaned_dataset.csv\", encoding=\"iso-8859-15\")"
   ]
  }
 ],
 "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.8.3"
  },
  "latex_envs": {
   "LaTeX_envs_menu_present": true,
   "autoclose": false,
   "autocomplete": true,
   "bibliofile": "biblio.bib",
   "cite_by": "apalike",
   "current_citInitial": 1,
   "eqLabelWithNumbers": true,
   "eqNumInitial": 1,
   "hotkeys": {
    "equation": "Ctrl-E",
    "itemize": "Ctrl-I"
   },
   "labels_anchors": false,
   "latex_user_defs": false,
   "report_style_numbering": false,
   "user_envs_cfg": false
  },
  "varInspector": {
   "cols": {
    "lenName": 16,
    "lenType": 16,
    "lenVar": 40
   },
   "kernels_config": {
    "python": {
     "delete_cmd_postfix": "",
     "delete_cmd_prefix": "del ",
     "library": "var_list.py",
     "varRefreshCmd": "print(var_dic_list())"
    },
    "r": {
     "delete_cmd_postfix": ") ",
     "delete_cmd_prefix": "rm(",
     "library": "var_list.r",
     "varRefreshCmd": "cat(var_dic_list()) "
    }
   },
   "types_to_exclude": [
    "module",
    "function",
    "builtin_function_or_method",
    "instance",
    "_Feature"
   ],
   "window_display": false
  }
 },
 "nbformat": 4,
 "nbformat_minor": 4
}