Import Post.ipynb

{
 "cells": [
  {
   "cell_type": "code",
   "execution_count": 102,
   "metadata": {},
   "outputs": [],
   "source": [
    "import pandas as pd\n",
    "import openpyxl\n",
    "import xlrd"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 103,
   "metadata": {},
   "outputs": [
    {
     "name": "stderr",
     "output_type": "stream",
     "text": [
      "/opt/conda/lib/python3.6/site-packages/IPython/core/interactiveshell.py:2728: DtypeWarning: Columns (20,21,22,23,24,31,33,35,37) have mixed types. Specify dtype option on import or set low_memory=False.\n",
      "  interactivity=interactivity, compiler=compiler, result=result)\n"
     ]
    }
   ],
   "source": [
    "df_post = pd.read_csv('post.csv', sep=';')"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 104,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>id</th>\n",
       "      <th>poitypeid</th>\n",
       "      <th>description_de</th>\n",
       "      <th>description_fr</th>\n",
       "      <th>description_it</th>\n",
       "      <th>description_en</th>\n",
       "      <th>address_city</th>\n",
       "      <th>address_kantoncode</th>\n",
       "      <th>address_zip</th>\n",
       "      <th>address_countrycode</th>\n",
       "      <th>...</th>\n",
       "      <th>url_fr</th>\n",
       "      <th>urlmobil_fr</th>\n",
       "      <th>groupename_it</th>\n",
       "      <th>service_it</th>\n",
       "      <th>url_it</th>\n",
       "      <th>urlmobil_it</th>\n",
       "      <th>groupename_en</th>\n",
       "      <th>service_en</th>\n",
       "      <th>url_en</th>\n",
       "      <th>urlmobil_en</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>001PFST_001107450</td>\n",
       "      <td>001PFST</td>\n",
       "      <td>Post Filiale 8750 Riedern</td>\n",
       "      <td>Poste Filiale 8750 Riedern</td>\n",
       "      <td>Posta Filiale 8750 Riedern</td>\n",
       "      <td>Post Branch 8750 Riedern</td>\n",
       "      <td>Riedern</td>\n",
       "      <td>GL</td>\n",
       "      <td>8750</td>\n",
       "      <td>CH</td>\n",
       "      <td>...</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>Traffico dei pagamenti</td>\n",
       "      <td>Prelievo in contanti (max. CHF 500.-, CHF 50.-...</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>Payment transactions</td>\n",
       "      <td>Cash withdrawal (max. CHF 500, CHF 50 guarante...</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>001PFST_001107450</td>\n",
       "      <td>001PFST</td>\n",
       "      <td>Post Filiale 8750 Riedern</td>\n",
       "      <td>Poste Filiale 8750 Riedern</td>\n",
       "      <td>Posta Filiale 8750 Riedern</td>\n",
       "      <td>Post Branch 8750 Riedern</td>\n",
       "      <td>Riedern</td>\n",
       "      <td>GL</td>\n",
       "      <td>8750</td>\n",
       "      <td>CH</td>\n",
       "      <td>...</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>Informazioni</td>\n",
       "      <td>Solo versamenti nazionali</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>Informations</td>\n",
       "      <td>National payments only</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>001PFST_001107450</td>\n",
       "      <td>001PFST</td>\n",
       "      <td>Post Filiale 8750 Riedern</td>\n",
       "      <td>Poste Filiale 8750 Riedern</td>\n",
       "      <td>Posta Filiale 8750 Riedern</td>\n",
       "      <td>Post Branch 8750 Riedern</td>\n",
       "      <td>Riedern</td>\n",
       "      <td>GL</td>\n",
       "      <td>8750</td>\n",
       "      <td>CH</td>\n",
       "      <td>...</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>Lettere e pacchi</td>\n",
       "      <td>Inviare lettere e pacchi*</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>Letters and parcels</td>\n",
       "      <td>Sending letter and parcels*</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>001PFST_001107450</td>\n",
       "      <td>001PFST</td>\n",
       "      <td>Post Filiale 8750 Riedern</td>\n",
       "      <td>Poste Filiale 8750 Riedern</td>\n",
       "      <td>Posta Filiale 8750 Riedern</td>\n",
       "      <td>Post Branch 8750 Riedern</td>\n",
       "      <td>Riedern</td>\n",
       "      <td>GL</td>\n",
       "      <td>8750</td>\n",
       "      <td>CH</td>\n",
       "      <td>...</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>Traffico dei pagamenti</td>\n",
       "      <td>Prelievo in contanti</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>Payment transactions</td>\n",
       "      <td>Cash withdrawals</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>001PFST_001107452</td>\n",
       "      <td>001PFST</td>\n",
       "      <td>Post Filiale 8718 Schänis</td>\n",
       "      <td>Poste Filiale 8718 Schänis</td>\n",
       "      <td>Posta Filiale 8718 Schänis</td>\n",
       "      <td>Post Branch 8718 Schänis</td>\n",
       "      <td>Schänis</td>\n",
       "      <td>SG</td>\n",
       "      <td>8718</td>\n",
       "      <td>CH</td>\n",
       "      <td>...</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>Informazioni</td>\n",
       "      <td>*Spedire pacchi fino a max. 100 x 60 x 60 cm e...</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>Informations</td>\n",
       "      <td>*Send parcels up to max. 100 x 60 x 60 cm and ...</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "<p>5 rows × 56 columns</p>\n",
       "</div>"
      ],
      "text/plain": [
       "                  id poitypeid             description_de  \\\n",
       "0  001PFST_001107450   001PFST  Post Filiale 8750 Riedern   \n",
       "1  001PFST_001107450   001PFST  Post Filiale 8750 Riedern   \n",
       "2  001PFST_001107450   001PFST  Post Filiale 8750 Riedern   \n",
       "3  001PFST_001107450   001PFST  Post Filiale 8750 Riedern   \n",
       "4  001PFST_001107452   001PFST  Post Filiale 8718 Schänis   \n",
       "\n",
       "               description_fr              description_it  \\\n",
       "0  Poste Filiale 8750 Riedern  Posta Filiale 8750 Riedern   \n",
       "1  Poste Filiale 8750 Riedern  Posta Filiale 8750 Riedern   \n",
       "2  Poste Filiale 8750 Riedern  Posta Filiale 8750 Riedern   \n",
       "3  Poste Filiale 8750 Riedern  Posta Filiale 8750 Riedern   \n",
       "4  Poste Filiale 8718 Schänis  Posta Filiale 8718 Schänis   \n",
       "\n",
       "             description_en address_city address_kantoncode  address_zip  \\\n",
       "0  Post Branch 8750 Riedern      Riedern                 GL         8750   \n",
       "1  Post Branch 8750 Riedern      Riedern                 GL         8750   \n",
       "2  Post Branch 8750 Riedern      Riedern                 GL         8750   \n",
       "3  Post Branch 8750 Riedern      Riedern                 GL         8750   \n",
       "4  Post Branch 8718 Schänis      Schänis                 SG         8718   \n",
       "\n",
       "  address_countrycode     ...     url_fr  urlmobil_fr           groupename_it  \\\n",
       "0                  CH     ...        NaN          NaN  Traffico dei pagamenti   \n",
       "1                  CH     ...        NaN          NaN            Informazioni   \n",
       "2                  CH     ...        NaN          NaN        Lettere e pacchi   \n",
       "3                  CH     ...        NaN          NaN  Traffico dei pagamenti   \n",
       "4                  CH     ...        NaN          NaN            Informazioni   \n",
       "\n",
       "                                          service_it url_it urlmobil_it  \\\n",
       "0  Prelievo in contanti (max. CHF 500.-, CHF 50.-...    NaN         NaN   \n",
       "1                          Solo versamenti nazionali    NaN         NaN   \n",
       "2                          Inviare lettere e pacchi*    NaN         NaN   \n",
       "3                               Prelievo in contanti    NaN         NaN   \n",
       "4  *Spedire pacchi fino a max. 100 x 60 x 60 cm e...    NaN         NaN   \n",
       "\n",
       "          groupename_en                                         service_en  \\\n",
       "0  Payment transactions  Cash withdrawal (max. CHF 500, CHF 50 guarante...   \n",
       "1          Informations                             National payments only   \n",
       "2   Letters and parcels                        Sending letter and parcels*   \n",
       "3  Payment transactions                                   Cash withdrawals   \n",
       "4          Informations  *Send parcels up to max. 100 x 60 x 60 cm and ...   \n",
       "\n",
       "  url_en urlmobil_en  \n",
       "0    NaN         NaN  \n",
       "1    NaN         NaN  \n",
       "2    NaN         NaN  \n",
       "3    NaN         NaN  \n",
       "4    NaN         NaN  \n",
       "\n",
       "[5 rows x 56 columns]"
      ]
     },
     "execution_count": 104,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df_post.head()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 105,
   "metadata": {},
   "outputs": [],
   "source": [
    "# Löschen der Column Names Übersetzungen\n",
    "del_columns =df_post.columns[df_post.columns.str.endswith(('_fr','_it','_en'))]\n",
    "df_post = df_post.drop(del_columns, axis = 1)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 106,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "(89853, 29)"
      ]
     },
     "execution_count": 106,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df_post.shape"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 107,
   "metadata": {},
   "outputs": [],
   "source": [
    "#Löschen nicht brauchbarer columns\n",
    "df_post = df_post.drop(['id',\n",
    " 'poitypeid',\n",
    " 'address_countrycode',\n",
    " 'address_technicalplz6',\n",
    " 'address_gemeindeid',\n",
    " 'contact_phone',\n",
    " 'contact_fax',\n",
    " 'contact_email_addresse_de',\n",
    " 'additionaldescription',\n",
    " 'offerpackage',\n",
    " 'openinghoursevent',\n",
    " 'accessinformation',\n",
    " 'pickuptime_description_de',\n",
    " 'pickuptime_time',\n",
    " 'service_servicetypeid',\n",
    " 'receipttype_de',\n",
    " 'forprivatecustomer',\n",
    " 'forbusinesscustomer',\n",
    " 'url_de',\n",
    " 'urlmobil_de'], axis=1)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 108,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>description_de</th>\n",
       "      <th>address_city</th>\n",
       "      <th>address_kantoncode</th>\n",
       "      <th>address_zip</th>\n",
       "      <th>address_street</th>\n",
       "      <th>geopoint</th>\n",
       "      <th>poityp_de</th>\n",
       "      <th>groupename_de</th>\n",
       "      <th>service_de</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>Post Filiale 8750 Riedern</td>\n",
       "      <td>Riedern</td>\n",
       "      <td>GL</td>\n",
       "      <td>8750</td>\n",
       "      <td>Dorfplatz 3</td>\n",
       "      <td>47.05008563, 9.05175151</td>\n",
       "      <td>Postfachanlage</td>\n",
       "      <td>Zahlungsverkehr</td>\n",
       "      <td>Bargeldbezug (max. CHF 500.-, CHF 50.- garanti...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>Post Filiale 8750 Riedern</td>\n",
       "      <td>Riedern</td>\n",
       "      <td>GL</td>\n",
       "      <td>8750</td>\n",
       "      <td>Dorfplatz 3</td>\n",
       "      <td>47.05008563, 9.05175151</td>\n",
       "      <td>Postfachanlage</td>\n",
       "      <td>Hinweise</td>\n",
       "      <td>Nur Einzahlungen Inland</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>Post Filiale 8750 Riedern</td>\n",
       "      <td>Riedern</td>\n",
       "      <td>GL</td>\n",
       "      <td>8750</td>\n",
       "      <td>Dorfplatz 3</td>\n",
       "      <td>47.05008563, 9.05175151</td>\n",
       "      <td>Postfachanlage</td>\n",
       "      <td>Briefe und Pakete</td>\n",
       "      <td>Briefe und Pakete* versenden</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>Post Filiale 8750 Riedern</td>\n",
       "      <td>Riedern</td>\n",
       "      <td>GL</td>\n",
       "      <td>8750</td>\n",
       "      <td>Dorfplatz 3</td>\n",
       "      <td>47.05008563, 9.05175151</td>\n",
       "      <td>Postfachanlage</td>\n",
       "      <td>Zahlungsverkehr</td>\n",
       "      <td>Bargeldbezug</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>Post Filiale 8718 Schänis</td>\n",
       "      <td>Schänis</td>\n",
       "      <td>SG</td>\n",
       "      <td>8718</td>\n",
       "      <td>Oberdorf 14</td>\n",
       "      <td>47.15889137, 9.04443994</td>\n",
       "      <td>Postfachanlage</td>\n",
       "      <td>Hinweise</td>\n",
       "      <td>*Pakete versenden bis max. 100 x 60 x 60 cm un...</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "              description_de address_city address_kantoncode  address_zip  \\\n",
       "0  Post Filiale 8750 Riedern      Riedern                 GL         8750   \n",
       "1  Post Filiale 8750 Riedern      Riedern                 GL         8750   \n",
       "2  Post Filiale 8750 Riedern      Riedern                 GL         8750   \n",
       "3  Post Filiale 8750 Riedern      Riedern                 GL         8750   \n",
       "4  Post Filiale 8718 Schänis      Schänis                 SG         8718   \n",
       "\n",
       "  address_street                 geopoint       poityp_de      groupename_de  \\\n",
       "0    Dorfplatz 3  47.05008563, 9.05175151  Postfachanlage    Zahlungsverkehr   \n",
       "1    Dorfplatz 3  47.05008563, 9.05175151  Postfachanlage           Hinweise   \n",
       "2    Dorfplatz 3  47.05008563, 9.05175151  Postfachanlage  Briefe und Pakete   \n",
       "3    Dorfplatz 3  47.05008563, 9.05175151  Postfachanlage    Zahlungsverkehr   \n",
       "4    Oberdorf 14  47.15889137, 9.04443994  Postfachanlage           Hinweise   \n",
       "\n",
       "                                          service_de  \n",
       "0  Bargeldbezug (max. CHF 500.-, CHF 50.- garanti...  \n",
       "1                            Nur Einzahlungen Inland  \n",
       "2                       Briefe und Pakete* versenden  \n",
       "3                                       Bargeldbezug  \n",
       "4  *Pakete versenden bis max. 100 x 60 x 60 cm un...  "
      ]
     },
     "execution_count": 108,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df_post.head()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 109,
   "metadata": {},
   "outputs": [],
   "source": [
    "#Split der geopoint daten in 2 separate Columns. Geopoint kann danach gelöscht werden\n",
    "df_post[['post WGS84_E','post WGS84_N']] = df_post['geopoint'].str.split(pat=',', expand=True)\n",
    "df_post = df_post.drop(['geopoint'], axis=1)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 110,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>description_de</th>\n",
       "      <th>address_city</th>\n",
       "      <th>address_kantoncode</th>\n",
       "      <th>address_zip</th>\n",
       "      <th>address_street</th>\n",
       "      <th>poityp_de</th>\n",
       "      <th>groupename_de</th>\n",
       "      <th>service_de</th>\n",
       "      <th>post WGS84_E</th>\n",
       "      <th>post WGS84_N</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>Post Filiale 8750 Riedern</td>\n",
       "      <td>Riedern</td>\n",
       "      <td>GL</td>\n",
       "      <td>8750</td>\n",
       "      <td>Dorfplatz 3</td>\n",
       "      <td>Postfachanlage</td>\n",
       "      <td>Zahlungsverkehr</td>\n",
       "      <td>Bargeldbezug (max. CHF 500.-, CHF 50.- garanti...</td>\n",
       "      <td>47.05008563</td>\n",
       "      <td>9.05175151</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>Post Filiale 8750 Riedern</td>\n",
       "      <td>Riedern</td>\n",
       "      <td>GL</td>\n",
       "      <td>8750</td>\n",
       "      <td>Dorfplatz 3</td>\n",
       "      <td>Postfachanlage</td>\n",
       "      <td>Hinweise</td>\n",
       "      <td>Nur Einzahlungen Inland</td>\n",
       "      <td>47.05008563</td>\n",
       "      <td>9.05175151</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>Post Filiale 8750 Riedern</td>\n",
       "      <td>Riedern</td>\n",
       "      <td>GL</td>\n",
       "      <td>8750</td>\n",
       "      <td>Dorfplatz 3</td>\n",
       "      <td>Postfachanlage</td>\n",
       "      <td>Briefe und Pakete</td>\n",
       "      <td>Briefe und Pakete* versenden</td>\n",
       "      <td>47.05008563</td>\n",
       "      <td>9.05175151</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>Post Filiale 8750 Riedern</td>\n",
       "      <td>Riedern</td>\n",
       "      <td>GL</td>\n",
       "      <td>8750</td>\n",
       "      <td>Dorfplatz 3</td>\n",
       "      <td>Postfachanlage</td>\n",
       "      <td>Zahlungsverkehr</td>\n",
       "      <td>Bargeldbezug</td>\n",
       "      <td>47.05008563</td>\n",
       "      <td>9.05175151</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>Post Filiale 8718 Schänis</td>\n",
       "      <td>Schänis</td>\n",
       "      <td>SG</td>\n",
       "      <td>8718</td>\n",
       "      <td>Oberdorf 14</td>\n",
       "      <td>Postfachanlage</td>\n",
       "      <td>Hinweise</td>\n",
       "      <td>*Pakete versenden bis max. 100 x 60 x 60 cm un...</td>\n",
       "      <td>47.15889137</td>\n",
       "      <td>9.04443994</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "              description_de address_city address_kantoncode  address_zip  \\\n",
       "0  Post Filiale 8750 Riedern      Riedern                 GL         8750   \n",
       "1  Post Filiale 8750 Riedern      Riedern                 GL         8750   \n",
       "2  Post Filiale 8750 Riedern      Riedern                 GL         8750   \n",
       "3  Post Filiale 8750 Riedern      Riedern                 GL         8750   \n",
       "4  Post Filiale 8718 Schänis      Schänis                 SG         8718   \n",
       "\n",
       "  address_street       poityp_de      groupename_de  \\\n",
       "0    Dorfplatz 3  Postfachanlage    Zahlungsverkehr   \n",
       "1    Dorfplatz 3  Postfachanlage           Hinweise   \n",
       "2    Dorfplatz 3  Postfachanlage  Briefe und Pakete   \n",
       "3    Dorfplatz 3  Postfachanlage    Zahlungsverkehr   \n",
       "4    Oberdorf 14  Postfachanlage           Hinweise   \n",
       "\n",
       "                                          service_de post WGS84_E post WGS84_N  \n",
       "0  Bargeldbezug (max. CHF 500.-, CHF 50.- garanti...  47.05008563   9.05175151  \n",
       "1                            Nur Einzahlungen Inland  47.05008563   9.05175151  \n",
       "2                       Briefe und Pakete* versenden  47.05008563   9.05175151  \n",
       "3                                       Bargeldbezug  47.05008563   9.05175151  \n",
       "4  *Pakete versenden bis max. 100 x 60 x 60 cm un...  47.15889137   9.04443994  "
      ]
     },
     "execution_count": 110,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df_post.head()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 90,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "description_de            0\n",
       "address_city              0\n",
       "address_kantoncode        0\n",
       "address_zip               0\n",
       "address_street        14452\n",
       "poityp_de                 0\n",
       "groupename_de         14799\n",
       "service_de            14799\n",
       "post WGS84_E             11\n",
       "post WGS84_N             11\n",
       "dtype: int64"
      ]
     },
     "execution_count": 90,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df_post.isnull().sum()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 91,
   "metadata": {},
   "outputs": [],
   "source": [
    "#NaN Werte aus geodaten entfernen -> ganze row wird gelöscht -> betrifft nur 11 Zeilen\n",
    "df_post = df_post.dropna(subset=['post WGS84_E'])"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 92,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "description_de            0\n",
       "address_city              0\n",
       "address_kantoncode        0\n",
       "address_zip               0\n",
       "address_street        14441\n",
       "poityp_de                 0\n",
       "groupename_de         14799\n",
       "service_de            14799\n",
       "post WGS84_E              0\n",
       "post WGS84_N              0\n",
       "dtype: int64"
      ]
     },
     "execution_count": 92,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df_post.isnull().sum()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 93,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>address_city</th>\n",
       "      <th>groupename_de</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>Aadorf</td>\n",
       "      <td>39</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>Aarau</td>\n",
       "      <td>90</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>Aarau Rohr</td>\n",
       "      <td>14</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>Aarberg</td>\n",
       "      <td>42</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>Aarburg</td>\n",
       "      <td>23</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "  address_city  groupename_de\n",
       "0       Aadorf             39\n",
       "1        Aarau             90\n",
       "2   Aarau Rohr             14\n",
       "3      Aarberg             42\n",
       "4      Aarburg             23"
      ]
     },
     "execution_count": 93,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "#Kumulierte Anzahl Dienstleistungen ermitteln pro Gemeinde\n",
    "df_DL = df_post.groupby(by=['address_city'])['groupename_de'].count()\n",
    "df_DL = df_DL.to_frame()\n",
    "df_DL = df_DL.reset_index()\n",
    "df_DL.head()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 94,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>description_de</th>\n",
       "      <th>address_city</th>\n",
       "      <th>address_kantoncode</th>\n",
       "      <th>address_zip</th>\n",
       "      <th>address_street</th>\n",
       "      <th>poityp_de</th>\n",
       "      <th>groupename_de</th>\n",
       "      <th>service_de</th>\n",
       "      <th>post WGS84_E</th>\n",
       "      <th>post WGS84_N</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>Post Filiale 8750 Riedern</td>\n",
       "      <td>Riedern</td>\n",
       "      <td>GL</td>\n",
       "      <td>8750</td>\n",
       "      <td>Dorfplatz 3</td>\n",
       "      <td>Postfachanlage</td>\n",
       "      <td>Zahlungsverkehr</td>\n",
       "      <td>Bargeldbezug (max. CHF 500.-, CHF 50.- garanti...</td>\n",
       "      <td>47.05008563</td>\n",
       "      <td>9.05175151</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>Post Filiale 8750 Riedern</td>\n",
       "      <td>Riedern</td>\n",
       "      <td>GL</td>\n",
       "      <td>8750</td>\n",
       "      <td>Dorfplatz 3</td>\n",
       "      <td>Postfachanlage</td>\n",
       "      <td>Hinweise</td>\n",
       "      <td>Nur Einzahlungen Inland</td>\n",
       "      <td>47.05008563</td>\n",
       "      <td>9.05175151</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>Post Filiale 8750 Riedern</td>\n",
       "      <td>Riedern</td>\n",
       "      <td>GL</td>\n",
       "      <td>8750</td>\n",
       "      <td>Dorfplatz 3</td>\n",
       "      <td>Postfachanlage</td>\n",
       "      <td>Briefe und Pakete</td>\n",
       "      <td>Briefe und Pakete* versenden</td>\n",
       "      <td>47.05008563</td>\n",
       "      <td>9.05175151</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>Post Filiale 8750 Riedern</td>\n",
       "      <td>Riedern</td>\n",
       "      <td>GL</td>\n",
       "      <td>8750</td>\n",
       "      <td>Dorfplatz 3</td>\n",
       "      <td>Postfachanlage</td>\n",
       "      <td>Zahlungsverkehr</td>\n",
       "      <td>Bargeldbezug</td>\n",
       "      <td>47.05008563</td>\n",
       "      <td>9.05175151</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>Post Filiale 8718 Schänis</td>\n",
       "      <td>Schänis</td>\n",
       "      <td>SG</td>\n",
       "      <td>8718</td>\n",
       "      <td>Oberdorf 14</td>\n",
       "      <td>Postfachanlage</td>\n",
       "      <td>Hinweise</td>\n",
       "      <td>*Pakete versenden bis max. 100 x 60 x 60 cm un...</td>\n",
       "      <td>47.15889137</td>\n",
       "      <td>9.04443994</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "              description_de address_city address_kantoncode  address_zip  \\\n",
       "0  Post Filiale 8750 Riedern      Riedern                 GL         8750   \n",
       "1  Post Filiale 8750 Riedern      Riedern                 GL         8750   \n",
       "2  Post Filiale 8750 Riedern      Riedern                 GL         8750   \n",
       "3  Post Filiale 8750 Riedern      Riedern                 GL         8750   \n",
       "4  Post Filiale 8718 Schänis      Schänis                 SG         8718   \n",
       "\n",
       "  address_street       poityp_de      groupename_de  \\\n",
       "0    Dorfplatz 3  Postfachanlage    Zahlungsverkehr   \n",
       "1    Dorfplatz 3  Postfachanlage           Hinweise   \n",
       "2    Dorfplatz 3  Postfachanlage  Briefe und Pakete   \n",
       "3    Dorfplatz 3  Postfachanlage    Zahlungsverkehr   \n",
       "4    Oberdorf 14  Postfachanlage           Hinweise   \n",
       "\n",
       "                                          service_de post WGS84_E post WGS84_N  \n",
       "0  Bargeldbezug (max. CHF 500.-, CHF 50.- garanti...  47.05008563   9.05175151  \n",
       "1                            Nur Einzahlungen Inland  47.05008563   9.05175151  \n",
       "2                       Briefe und Pakete* versenden  47.05008563   9.05175151  \n",
       "3                                       Bargeldbezug  47.05008563   9.05175151  \n",
       "4  *Pakete versenden bis max. 100 x 60 x 60 cm un...  47.15889137   9.04443994  "
      ]
     },
     "execution_count": 94,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df_post.head()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 95,
   "metadata": {},
   "outputs": [],
   "source": [
    "data_merged = df_post.merge(df_DL, left_on='address_city', right_on='address_city')"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 96,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>description_de</th>\n",
       "      <th>address_city</th>\n",
       "      <th>address_kantoncode</th>\n",
       "      <th>address_zip</th>\n",
       "      <th>address_street</th>\n",
       "      <th>poityp_de</th>\n",
       "      <th>groupename_de_x</th>\n",
       "      <th>service_de</th>\n",
       "      <th>post WGS84_E</th>\n",
       "      <th>post WGS84_N</th>\n",
       "      <th>groupename_de_y</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>Post Filiale 8750 Riedern</td>\n",
       "      <td>Riedern</td>\n",
       "      <td>GL</td>\n",
       "      <td>8750</td>\n",
       "      <td>Dorfplatz 3</td>\n",
       "      <td>Postfachanlage</td>\n",
       "      <td>Zahlungsverkehr</td>\n",
       "      <td>Bargeldbezug (max. CHF 500.-, CHF 50.- garanti...</td>\n",
       "      <td>47.05008563</td>\n",
       "      <td>9.05175151</td>\n",
       "      <td>26</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>Post Filiale 8750 Riedern</td>\n",
       "      <td>Riedern</td>\n",
       "      <td>GL</td>\n",
       "      <td>8750</td>\n",
       "      <td>Dorfplatz 3</td>\n",
       "      <td>Postfachanlage</td>\n",
       "      <td>Hinweise</td>\n",
       "      <td>Nur Einzahlungen Inland</td>\n",
       "      <td>47.05008563</td>\n",
       "      <td>9.05175151</td>\n",
       "      <td>26</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>Post Filiale 8750 Riedern</td>\n",
       "      <td>Riedern</td>\n",
       "      <td>GL</td>\n",
       "      <td>8750</td>\n",
       "      <td>Dorfplatz 3</td>\n",
       "      <td>Postfachanlage</td>\n",
       "      <td>Briefe und Pakete</td>\n",
       "      <td>Briefe und Pakete* versenden</td>\n",
       "      <td>47.05008563</td>\n",
       "      <td>9.05175151</td>\n",
       "      <td>26</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>Post Filiale 8750 Riedern</td>\n",
       "      <td>Riedern</td>\n",
       "      <td>GL</td>\n",
       "      <td>8750</td>\n",
       "      <td>Dorfplatz 3</td>\n",
       "      <td>Postfachanlage</td>\n",
       "      <td>Zahlungsverkehr</td>\n",
       "      <td>Bargeldbezug</td>\n",
       "      <td>47.05008563</td>\n",
       "      <td>9.05175151</td>\n",
       "      <td>26</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>Post Filiale 8750 Riedern</td>\n",
       "      <td>Riedern</td>\n",
       "      <td>GL</td>\n",
       "      <td>8750</td>\n",
       "      <td>Dorfplatz 3</td>\n",
       "      <td>Filiale</td>\n",
       "      <td>Briefe und Pakete</td>\n",
       "      <td>PickPost</td>\n",
       "      <td>47.05008563</td>\n",
       "      <td>9.05175151</td>\n",
       "      <td>26</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "              description_de address_city address_kantoncode  address_zip  \\\n",
       "0  Post Filiale 8750 Riedern      Riedern                 GL         8750   \n",
       "1  Post Filiale 8750 Riedern      Riedern                 GL         8750   \n",
       "2  Post Filiale 8750 Riedern      Riedern                 GL         8750   \n",
       "3  Post Filiale 8750 Riedern      Riedern                 GL         8750   \n",
       "4  Post Filiale 8750 Riedern      Riedern                 GL         8750   \n",
       "\n",
       "  address_street       poityp_de    groupename_de_x  \\\n",
       "0    Dorfplatz 3  Postfachanlage    Zahlungsverkehr   \n",
       "1    Dorfplatz 3  Postfachanlage           Hinweise   \n",
       "2    Dorfplatz 3  Postfachanlage  Briefe und Pakete   \n",
       "3    Dorfplatz 3  Postfachanlage    Zahlungsverkehr   \n",
       "4    Dorfplatz 3         Filiale  Briefe und Pakete   \n",
       "\n",
       "                                          service_de post WGS84_E  \\\n",
       "0  Bargeldbezug (max. CHF 500.-, CHF 50.- garanti...  47.05008563   \n",
       "1                            Nur Einzahlungen Inland  47.05008563   \n",
       "2                       Briefe und Pakete* versenden  47.05008563   \n",
       "3                                       Bargeldbezug  47.05008563   \n",
       "4                                           PickPost  47.05008563   \n",
       "\n",
       "  post WGS84_N  groupename_de_y  \n",
       "0   9.05175151               26  \n",
       "1   9.05175151               26  \n",
       "2   9.05175151               26  \n",
       "3   9.05175151               26  \n",
       "4   9.05175151               26  "
      ]
     },
     "execution_count": 96,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "data_merged.head()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 97,
   "metadata": {},
   "outputs": [],
   "source": [
    "data_merged = data_merged.drop_duplicates(subset='address_city')"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 98,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>description_de</th>\n",
       "      <th>address_city</th>\n",
       "      <th>address_kantoncode</th>\n",
       "      <th>address_zip</th>\n",
       "      <th>address_street</th>\n",
       "      <th>poityp_de</th>\n",
       "      <th>groupename_de_x</th>\n",
       "      <th>service_de</th>\n",
       "      <th>post WGS84_E</th>\n",
       "      <th>post WGS84_N</th>\n",
       "      <th>groupename_de_y</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>Post Filiale 8750 Riedern</td>\n",
       "      <td>Riedern</td>\n",
       "      <td>GL</td>\n",
       "      <td>8750</td>\n",
       "      <td>Dorfplatz 3</td>\n",
       "      <td>Postfachanlage</td>\n",
       "      <td>Zahlungsverkehr</td>\n",
       "      <td>Bargeldbezug (max. CHF 500.-, CHF 50.- garanti...</td>\n",
       "      <td>47.05008563</td>\n",
       "      <td>9.05175151</td>\n",
       "      <td>26</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>27</th>\n",
       "      <td>Post Filiale 8718 Schänis</td>\n",
       "      <td>Schänis</td>\n",
       "      <td>SG</td>\n",
       "      <td>8718</td>\n",
       "      <td>Oberdorf 14</td>\n",
       "      <td>Postfachanlage</td>\n",
       "      <td>Hinweise</td>\n",
       "      <td>*Pakete versenden bis max. 100 x 60 x 60 cm un...</td>\n",
       "      <td>47.15889137</td>\n",
       "      <td>9.04443994</td>\n",
       "      <td>26</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>56</th>\n",
       "      <td>Post Filiale 3367 Thörigen</td>\n",
       "      <td>Thörigen</td>\n",
       "      <td>BE</td>\n",
       "      <td>3367</td>\n",
       "      <td>Buchsistrasse 1A</td>\n",
       "      <td>Postfachanlage</td>\n",
       "      <td>Hinweise</td>\n",
       "      <td>Nur Einzahlungen Inland</td>\n",
       "      <td>47.17361643</td>\n",
       "      <td>7.72757174</td>\n",
       "      <td>24</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>83</th>\n",
       "      <td>Post Filiale 4323 Wallbach</td>\n",
       "      <td>Wallbach</td>\n",
       "      <td>AG</td>\n",
       "      <td>4323</td>\n",
       "      <td>Zentrumstrasse 2</td>\n",
       "      <td>Postfachanlage</td>\n",
       "      <td>Dienstleistungen GK</td>\n",
       "      <td>Annahmestelle PromoPost</td>\n",
       "      <td>47.55776355</td>\n",
       "      <td>7.90482353</td>\n",
       "      <td>26</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>112</th>\n",
       "      <td>Hausservice 8564 Sonterswil</td>\n",
       "      <td>Sonterswil</td>\n",
       "      <td>TG</td>\n",
       "      <td>8564</td>\n",
       "      <td>NaN</td>\n",
       "      <td>Hausservice</td>\n",
       "      <td>Hinweise</td>\n",
       "      <td>Hausservice</td>\n",
       "      <td>47.62014751</td>\n",
       "      <td>9.08146395</td>\n",
       "      <td>8</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "                  description_de address_city address_kantoncode  address_zip  \\\n",
       "0      Post Filiale 8750 Riedern      Riedern                 GL         8750   \n",
       "27     Post Filiale 8718 Schänis      Schänis                 SG         8718   \n",
       "56    Post Filiale 3367 Thörigen     Thörigen                 BE         3367   \n",
       "83    Post Filiale 4323 Wallbach     Wallbach                 AG         4323   \n",
       "112  Hausservice 8564 Sonterswil   Sonterswil                 TG         8564   \n",
       "\n",
       "       address_street       poityp_de      groupename_de_x  \\\n",
       "0         Dorfplatz 3  Postfachanlage      Zahlungsverkehr   \n",
       "27        Oberdorf 14  Postfachanlage             Hinweise   \n",
       "56   Buchsistrasse 1A  Postfachanlage             Hinweise   \n",
       "83   Zentrumstrasse 2  Postfachanlage  Dienstleistungen GK   \n",
       "112               NaN     Hausservice             Hinweise   \n",
       "\n",
       "                                            service_de post WGS84_E  \\\n",
       "0    Bargeldbezug (max. CHF 500.-, CHF 50.- garanti...  47.05008563   \n",
       "27   *Pakete versenden bis max. 100 x 60 x 60 cm un...  47.15889137   \n",
       "56                             Nur Einzahlungen Inland  47.17361643   \n",
       "83                             Annahmestelle PromoPost  47.55776355   \n",
       "112                                        Hausservice  47.62014751   \n",
       "\n",
       "    post WGS84_N  groupename_de_y  \n",
       "0     9.05175151               26  \n",
       "27    9.04443994               26  \n",
       "56    7.72757174               24  \n",
       "83    7.90482353               26  \n",
       "112   9.08146395                8  "
      ]
     },
     "execution_count": 98,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "data_merged.head()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": []
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": []
  }
 ],
 "metadata": {
  "kernelspec": {
   "display_name": "Python 3",
   "language": "python",
   "name": "python3"
  },
  "language_info": {
   "codemirror_mode": {
    "name": "ipython",
    "version": 3
   },
   "file_extension": ".py",
   "mimetype": "text/x-python",
   "name": "python",
   "nbconvert_exporter": "python",
   "pygments_lexer": "ipython3",
   "version": "3.6.2"
  }
 },
 "nbformat": 4,
 "nbformat_minor": 2
}