step-02-rosbank-banki-ru.ipynb

{
 "cells": [
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "# Загрузка данных с агрегатора Banki.Ru"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 1,
   "metadata": {},
   "outputs": [],
   "source": [
    "import time\n",
    "import requests\n",
    "\n",
    "import pandas as pd\n",
    "\n",
    "from tqdm import tqdm"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 2,
   "metadata": {},
   "outputs": [],
   "source": [
    "offset, limit = 0, 1000\n",
    "\n",
    "df_banks_coords = []\n",
    "\n",
    "while True:\n",
    "    data = {\n",
    "        'jsonrpc': '2.0',\n",
    "        'method': 'bankGeo/getObjectsByFilter',\n",
    "        'params': {\n",
    "            'offset': offset,\n",
    "            'limit': limit,\n",
    "            'type': ['office', 'branch', 'atm', 'cash', 'self_office'],\n",
    "            'bank_id': ['36119', '325', '63520', '327', '2764', '4389', '3697', '4725'],\n",
    "        },\n",
    "        'id': ''\n",
    "    }\n",
    "    \n",
    "    r = requests.post('https://www.banki.ru/api/', json=data)\n",
    "    if r.status_code != 200:\n",
    "        raise Exception('Request has failed.')\n",
    "    \n",
    "    r = r.json()\n",
    "    df_banks_coords.extend(r['result']['data'])\n",
    "    \n",
    "    if not len(df_banks_coords) < r['result']['total']:\n",
    "        break\n",
    "        \n",
    "df_banks_coords = pd.DataFrame(df_banks_coords)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 3,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>active</th>\n",
       "      <th>address</th>\n",
       "      <th>bank_id</th>\n",
       "      <th>icon_url</th>\n",
       "      <th>id</th>\n",
       "      <th>is_main</th>\n",
       "      <th>latitude</th>\n",
       "      <th>longitude</th>\n",
       "      <th>name</th>\n",
       "      <th>region_id</th>\n",
       "      <th>sort</th>\n",
       "      <th>type</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>True</td>\n",
       "      <td>г. Казань, ул. Павлюхина, д. 101</td>\n",
       "      <td>36119</td>\n",
       "      <td>/upload/iblock/8e2/akbars.gif</td>\n",
       "      <td>10752354</td>\n",
       "      <td>True</td>\n",
       "      <td>55.765072</td>\n",
       "      <td>49.149404</td>\n",
       "      <td>Банкомат</td>\n",
       "      <td>479</td>\n",
       "      <td>0</td>\n",
       "      <td>atm</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>True</td>\n",
       "      <td>Республика Татарстан, с. Молькеево, ул. Волков...</td>\n",
       "      <td>36119</td>\n",
       "      <td>/upload/iblock/8e2/akbars.gif</td>\n",
       "      <td>10754207</td>\n",
       "      <td>True</td>\n",
       "      <td>55.318403</td>\n",
       "      <td>47.815544</td>\n",
       "      <td>Банкомат</td>\n",
       "      <td>13615</td>\n",
       "      <td>0</td>\n",
       "      <td>atm</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>True</td>\n",
       "      <td>г. Самара, ул. Спортивная, д. 5</td>\n",
       "      <td>36119</td>\n",
       "      <td>/upload/iblock/8e2/akbars.gif</td>\n",
       "      <td>10756829</td>\n",
       "      <td>True</td>\n",
       "      <td>53.187941</td>\n",
       "      <td>50.122472</td>\n",
       "      <td>Банкомат</td>\n",
       "      <td>489</td>\n",
       "      <td>0</td>\n",
       "      <td>atm</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>True</td>\n",
       "      <td>г. Казань, ул. Вагапова, д. 3</td>\n",
       "      <td>36119</td>\n",
       "      <td>/upload/iblock/8e2/akbars.gif</td>\n",
       "      <td>10752496</td>\n",
       "      <td>True</td>\n",
       "      <td>55.783549</td>\n",
       "      <td>49.227291</td>\n",
       "      <td>Банкомат</td>\n",
       "      <td>479</td>\n",
       "      <td>0</td>\n",
       "      <td>atm</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>True</td>\n",
       "      <td>г. Йошкар-Ола, просп. Царьградский, д. 37</td>\n",
       "      <td>36119</td>\n",
       "      <td>/upload/iblock/8e2/akbars.gif</td>\n",
       "      <td>10753962</td>\n",
       "      <td>True</td>\n",
       "      <td>56.637374</td>\n",
       "      <td>47.907160</td>\n",
       "      <td>Банкомат</td>\n",
       "      <td>394</td>\n",
       "      <td>0</td>\n",
       "      <td>atm</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "   active                                            address  bank_id  \\\n",
       "0    True                   г. Казань, ул. Павлюхина, д. 101    36119   \n",
       "1    True  Республика Татарстан, с. Молькеево, ул. Волков...    36119   \n",
       "2    True                    г. Самара, ул. Спортивная, д. 5    36119   \n",
       "3    True                      г. Казань, ул. Вагапова, д. 3    36119   \n",
       "4    True          г. Йошкар-Ола, просп. Царьградский, д. 37    36119   \n",
       "\n",
       "                        icon_url        id  is_main   latitude  longitude  \\\n",
       "0  /upload/iblock/8e2/akbars.gif  10752354     True  55.765072  49.149404   \n",
       "1  /upload/iblock/8e2/akbars.gif  10754207     True  55.318403  47.815544   \n",
       "2  /upload/iblock/8e2/akbars.gif  10756829     True  53.187941  50.122472   \n",
       "3  /upload/iblock/8e2/akbars.gif  10752496     True  55.783549  49.227291   \n",
       "4  /upload/iblock/8e2/akbars.gif  10753962     True  56.637374  47.907160   \n",
       "\n",
       "       name  region_id  sort type  \n",
       "0  Банкомат        479     0  atm  \n",
       "1  Банкомат      13615     0  atm  \n",
       "2  Банкомат        489     0  atm  \n",
       "3  Банкомат        479     0  atm  \n",
       "4  Банкомат        394     0  atm  "
      ]
     },
     "execution_count": 3,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df_banks_coords.head()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 4,
   "metadata": {},
   "outputs": [
    {
     "name": "stderr",
     "output_type": "stream",
     "text": [
      "100%|██████████| 280/280 [14:28<00:00,  3.10s/it]\n"
     ]
    }
   ],
   "source": [
    "chunk_size = 100\n",
    "\n",
    "df_banks_info = []\n",
    "\n",
    "for i_start in tqdm(range(0, df_banks_coords.shape[0], chunk_size)):\n",
    "    id_list = df_banks_coords.iloc[i_start:i_start+chunk_size]['id'].tolist()\n",
    "    \n",
    "    data = {\n",
    "        'jsonrpc': '2.0',\n",
    "        'method': 'bank/getBankObjectsData',\n",
    "        'params': {\n",
    "            'id_list': id_list\n",
    "        },\n",
    "        'id': '16'\n",
    "    }\n",
    "    \n",
    "    r = requests.post('https://www.banki.ru/api/', json=data)\n",
    "    if r.status_code != 200:\n",
    "        raise Exception('Request has failed.')\n",
    "    \n",
    "    r = r.json()\n",
    "    df_banks_info.extend(r['result']['data'])\n",
    "    \n",
    "    time.sleep(0.2)\n",
    "    \n",
    "df_banks_info = pd.DataFrame(df_banks_info)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 5,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>active</th>\n",
       "      <th>additional</th>\n",
       "      <th>address</th>\n",
       "      <th>bank_code</th>\n",
       "      <th>bank_icon_url</th>\n",
       "      <th>bank_id</th>\n",
       "      <th>bank_logo2_url</th>\n",
       "      <th>bank_logo_url</th>\n",
       "      <th>bank_name</th>\n",
       "      <th>bank_site</th>\n",
       "      <th>...</th>\n",
       "      <th>metro_name</th>\n",
       "      <th>name</th>\n",
       "      <th>phone</th>\n",
       "      <th>region_id</th>\n",
       "      <th>schedule_entities</th>\n",
       "      <th>schedule_general</th>\n",
       "      <th>schedule_private_person</th>\n",
       "      <th>schedule_vip</th>\n",
       "      <th>type</th>\n",
       "      <th>without_weekend</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>1</td>\n",
       "      <td>Валюта: рубли</td>\n",
       "      <td>г. Казань, ул. Павлюхина, д. 101</td>\n",
       "      <td>akbars</td>\n",
       "      <td>/upload/iblock/8e2/akbars.gif</td>\n",
       "      <td>36119</td>\n",
       "      <td>https://www.banki.ru/upload/iblock/1a1/abb_135...</td>\n",
       "      <td>https://www.banki.ru/upload/iblock/68f/abb_100...</td>\n",
       "      <td>Ак Барс</td>\n",
       "      <td>www.akbars.ru</td>\n",
       "      <td>...</td>\n",
       "      <td></td>\n",
       "      <td>Банкомат</td>\n",
       "      <td></td>\n",
       "      <td>479</td>\n",
       "      <td></td>\n",
       "      <td></td>\n",
       "      <td></td>\n",
       "      <td></td>\n",
       "      <td>atm</td>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>1</td>\n",
       "      <td>Валюта: рубли</td>\n",
       "      <td>Республика Татарстан, с. Молькеево, ул. Волков...</td>\n",
       "      <td>akbars</td>\n",
       "      <td>/upload/iblock/8e2/akbars.gif</td>\n",
       "      <td>36119</td>\n",
       "      <td>https://www.banki.ru/upload/iblock/1a1/abb_135...</td>\n",
       "      <td>https://www.banki.ru/upload/iblock/68f/abb_100...</td>\n",
       "      <td>Ак Барс</td>\n",
       "      <td>www.akbars.ru</td>\n",
       "      <td>...</td>\n",
       "      <td></td>\n",
       "      <td>Банкомат</td>\n",
       "      <td></td>\n",
       "      <td>13615</td>\n",
       "      <td></td>\n",
       "      <td></td>\n",
       "      <td></td>\n",
       "      <td></td>\n",
       "      <td>atm</td>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>1</td>\n",
       "      <td>Валюта: рубли</td>\n",
       "      <td>г. Самара, ул. Спортивная, д. 5</td>\n",
       "      <td>akbars</td>\n",
       "      <td>/upload/iblock/8e2/akbars.gif</td>\n",
       "      <td>36119</td>\n",
       "      <td>https://www.banki.ru/upload/iblock/1a1/abb_135...</td>\n",
       "      <td>https://www.banki.ru/upload/iblock/68f/abb_100...</td>\n",
       "      <td>Ак Барс</td>\n",
       "      <td>www.akbars.ru</td>\n",
       "      <td>...</td>\n",
       "      <td></td>\n",
       "      <td>Банкомат</td>\n",
       "      <td></td>\n",
       "      <td>489</td>\n",
       "      <td></td>\n",
       "      <td></td>\n",
       "      <td></td>\n",
       "      <td></td>\n",
       "      <td>atm</td>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>1</td>\n",
       "      <td>Валюта: рубли</td>\n",
       "      <td>г. Казань, ул. Вагапова, д. 3</td>\n",
       "      <td>akbars</td>\n",
       "      <td>/upload/iblock/8e2/akbars.gif</td>\n",
       "      <td>36119</td>\n",
       "      <td>https://www.banki.ru/upload/iblock/1a1/abb_135...</td>\n",
       "      <td>https://www.banki.ru/upload/iblock/68f/abb_100...</td>\n",
       "      <td>Ак Барс</td>\n",
       "      <td>www.akbars.ru</td>\n",
       "      <td>...</td>\n",
       "      <td></td>\n",
       "      <td>Банкомат</td>\n",
       "      <td></td>\n",
       "      <td>479</td>\n",
       "      <td></td>\n",
       "      <td></td>\n",
       "      <td></td>\n",
       "      <td></td>\n",
       "      <td>atm</td>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>1</td>\n",
       "      <td>Валюта: рубли</td>\n",
       "      <td>г. Йошкар-Ола, просп. Царьградский, д. 37</td>\n",
       "      <td>akbars</td>\n",
       "      <td>/upload/iblock/8e2/akbars.gif</td>\n",
       "      <td>36119</td>\n",
       "      <td>https://www.banki.ru/upload/iblock/1a1/abb_135...</td>\n",
       "      <td>https://www.banki.ru/upload/iblock/68f/abb_100...</td>\n",
       "      <td>Ак Барс</td>\n",
       "      <td>www.akbars.ru</td>\n",
       "      <td>...</td>\n",
       "      <td></td>\n",
       "      <td>Банкомат</td>\n",
       "      <td></td>\n",
       "      <td>394</td>\n",
       "      <td></td>\n",
       "      <td></td>\n",
       "      <td></td>\n",
       "      <td></td>\n",
       "      <td>atm</td>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "<p>5 rows × 28 columns</p>\n",
       "</div>"
      ],
      "text/plain": [
       "  active     additional                                            address  \\\n",
       "0      1  Валюта: рубли                   г. Казань, ул. Павлюхина, д. 101   \n",
       "1      1  Валюта: рубли  Республика Татарстан, с. Молькеево, ул. Волков...   \n",
       "2      1  Валюта: рубли                    г. Самара, ул. Спортивная, д. 5   \n",
       "3      1  Валюта: рубли                      г. Казань, ул. Вагапова, д. 3   \n",
       "4      1  Валюта: рубли          г. Йошкар-Ола, просп. Царьградский, д. 37   \n",
       "\n",
       "  bank_code                  bank_icon_url  bank_id  \\\n",
       "0    akbars  /upload/iblock/8e2/akbars.gif    36119   \n",
       "1    akbars  /upload/iblock/8e2/akbars.gif    36119   \n",
       "2    akbars  /upload/iblock/8e2/akbars.gif    36119   \n",
       "3    akbars  /upload/iblock/8e2/akbars.gif    36119   \n",
       "4    akbars  /upload/iblock/8e2/akbars.gif    36119   \n",
       "\n",
       "                                      bank_logo2_url  \\\n",
       "0  https://www.banki.ru/upload/iblock/1a1/abb_135...   \n",
       "1  https://www.banki.ru/upload/iblock/1a1/abb_135...   \n",
       "2  https://www.banki.ru/upload/iblock/1a1/abb_135...   \n",
       "3  https://www.banki.ru/upload/iblock/1a1/abb_135...   \n",
       "4  https://www.banki.ru/upload/iblock/1a1/abb_135...   \n",
       "\n",
       "                                       bank_logo_url bank_name      bank_site  \\\n",
       "0  https://www.banki.ru/upload/iblock/68f/abb_100...   Ак Барс  www.akbars.ru   \n",
       "1  https://www.banki.ru/upload/iblock/68f/abb_100...   Ак Барс  www.akbars.ru   \n",
       "2  https://www.banki.ru/upload/iblock/68f/abb_100...   Ак Барс  www.akbars.ru   \n",
       "3  https://www.banki.ru/upload/iblock/68f/abb_100...   Ак Барс  www.akbars.ru   \n",
       "4  https://www.banki.ru/upload/iblock/68f/abb_100...   Ак Барс  www.akbars.ru   \n",
       "\n",
       "        ...       metro_name      name  phone  region_id  schedule_entities  \\\n",
       "0       ...                   Банкомат               479                      \n",
       "1       ...                   Банкомат             13615                      \n",
       "2       ...                   Банкомат               489                      \n",
       "3       ...                   Банкомат               479                      \n",
       "4       ...                   Банкомат               394                      \n",
       "\n",
       "   schedule_general schedule_private_person schedule_vip type without_weekend  \n",
       "0                                                         atm               1  \n",
       "1                                                         atm               1  \n",
       "2                                                         atm               1  \n",
       "3                                                         atm               1  \n",
       "4                                                         atm               1  \n",
       "\n",
       "[5 rows x 28 columns]"
      ]
     },
     "execution_count": 5,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df_banks_info.head()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 6,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "Index(['active', 'additional', 'address', 'bank_code', 'bank_icon_url',\n",
       "       'bank_id', 'bank_logo2_url', 'bank_logo_url', 'bank_name', 'bank_site',\n",
       "       'comment_to_address', 'id', 'is_at_closed_place', 'is_main_office',\n",
       "       'is_round_the_clock', 'is_works_as_shop', 'latitude', 'longitude',\n",
       "       'metro_name', 'name', 'phone', 'region_id', 'schedule_entities',\n",
       "       'schedule_general', 'schedule_private_person', 'schedule_vip', 'type',\n",
       "       'without_weekend'],\n",
       "      dtype='object')"
      ]
     },
     "execution_count": 6,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df_banks_info.columns"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 8,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>id</th>\n",
       "      <th>bank</th>\n",
       "      <th>name</th>\n",
       "      <th>type</th>\n",
       "      <th>address</th>\n",
       "      <th>comment</th>\n",
       "      <th>lat</th>\n",
       "      <th>lng</th>\n",
       "      <th>access24h</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>10752354</td>\n",
       "      <td>Ак Барс</td>\n",
       "      <td>Банкомат</td>\n",
       "      <td>atm</td>\n",
       "      <td>г. Казань, ул. Павлюхина, д. 101</td>\n",
       "      <td>Супермаркет &amp;laquo;Эдельвейс&amp;raquo;</td>\n",
       "      <td>55.765072</td>\n",
       "      <td>49.149404</td>\n",
       "      <td>False</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>10754207</td>\n",
       "      <td>Ак Барс</td>\n",
       "      <td>Банкомат</td>\n",
       "      <td>atm</td>\n",
       "      <td>Республика Татарстан, с. Молькеево, ул. Волков...</td>\n",
       "      <td>Магазин &amp;laquo;Затлы&amp;raquo;</td>\n",
       "      <td>55.318403</td>\n",
       "      <td>47.815544</td>\n",
       "      <td>False</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>10756829</td>\n",
       "      <td>Ак Барс</td>\n",
       "      <td>Банкомат</td>\n",
       "      <td>atm</td>\n",
       "      <td>г. Самара, ул. Спортивная, д. 5</td>\n",
       "      <td>ТК &amp;laquo;Журавль&amp;raquo;</td>\n",
       "      <td>53.187941</td>\n",
       "      <td>50.122472</td>\n",
       "      <td>True</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>10752496</td>\n",
       "      <td>Ак Барс</td>\n",
       "      <td>Банкомат</td>\n",
       "      <td>atm</td>\n",
       "      <td>г. Казань, ул. Вагапова, д. 3</td>\n",
       "      <td>ТК &amp;laquo;Долина изобилия&amp;raquo;</td>\n",
       "      <td>55.783549</td>\n",
       "      <td>49.227291</td>\n",
       "      <td>False</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>10753962</td>\n",
       "      <td>Ак Барс</td>\n",
       "      <td>Банкомат</td>\n",
       "      <td>atm</td>\n",
       "      <td>г. Йошкар-Ола, просп. Царьградский, д. 37</td>\n",
       "      <td>Супермаркет &amp;laquo;Eurospar&amp;raquo;</td>\n",
       "      <td>56.637374</td>\n",
       "      <td>47.90716</td>\n",
       "      <td>True</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "         id     bank      name type  \\\n",
       "0  10752354  Ак Барс  Банкомат  atm   \n",
       "1  10754207  Ак Барс  Банкомат  atm   \n",
       "2  10756829  Ак Барс  Банкомат  atm   \n",
       "3  10752496  Ак Барс  Банкомат  atm   \n",
       "4  10753962  Ак Барс  Банкомат  atm   \n",
       "\n",
       "                                             address  \\\n",
       "0                   г. Казань, ул. Павлюхина, д. 101   \n",
       "1  Республика Татарстан, с. Молькеево, ул. Волков...   \n",
       "2                    г. Самара, ул. Спортивная, д. 5   \n",
       "3                      г. Казань, ул. Вагапова, д. 3   \n",
       "4          г. Йошкар-Ола, просп. Царьградский, д. 37   \n",
       "\n",
       "                               comment        lat        lng  access24h  \n",
       "0  Супермаркет &laquo;Эдельвейс&raquo;  55.765072  49.149404      False  \n",
       "1          Магазин &laquo;Затлы&raquo;  55.318403  47.815544      False  \n",
       "2             ТК &laquo;Журавль&raquo;  53.187941  50.122472       True  \n",
       "3     ТК &laquo;Долина изобилия&raquo;  55.783549  49.227291      False  \n",
       "4   Супермаркет &laquo;Eurospar&raquo;  56.637374   47.90716       True  "
      ]
     },
     "execution_count": 8,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df_banks_info = df_banks_info[['id', 'bank_name', 'name', 'type', 'address', 'comment_to_address',\n",
    "                               'latitude', 'longitude', 'is_round_the_clock']]\n",
    "\n",
    "df_banks_info.rename(columns={\n",
    "    'latitude': 'lat',\n",
    "    'longitude': 'lng',\n",
    "    'comment_to_address': 'comment',\n",
    "    'bank_name': 'bank',\n",
    "    'is_round_the_clock': 'access24h'\n",
    "}, inplace=True)\n",
    "\n",
    "df_banks_info.head()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 9,
   "metadata": {},
   "outputs": [],
   "source": [
    "df_banks_info.to_csv('base_bankiru.csv', sep=',', index=False)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "# Загрузка данных с сайта Росбанка"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 1,
   "metadata": {},
   "outputs": [],
   "source": [
    "import requests\n",
    "from bs4 import BeautifulSoup\n",
    "\n",
    "import urllib\n",
    "\n",
    "import re\n",
    "from operator import attrgetter, itemgetter"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## Скачиваем страницы со списком банкоматов Росбанка"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 65,
   "metadata": {},
   "outputs": [],
   "source": [
    "# кусок HTML-кода выдран из меню выбора региона на сайте Росбанка\n",
    "\n",
    "html = \"\"\"\n",
    "<div class=\"container\">\n",
    "            <div class=\"row\">\n",
    "<div class=\"col-md-3 col-sm-3 col-xs-12\"><div class=\"popup-city__body_section\">\n",
    "            <div class=\"popup-city__body_title\">А</div>\n",
    "            <ul class=\"popup-city__body_list\"><li><a href=\"?region=22\" title=\"Алтайский край\">Алтайский край</a></li><li><a href=\"?region=28\" title=\"Амурская область\">Амурская область</a></li><li><a href=\"?region=29\" title=\"Архангельская область\">Архангельская область</a></li><li><a href=\"?region=30\" title=\"Астраханская область\">Астраханская область</a></li></ul>\n",
    "            </div><div class=\"popup-city__body_section\">\n",
    "            <div class=\"popup-city__body_title\">Б</div>\n",
    "            <ul class=\"popup-city__body_list\"><li><a href=\"?region=31\" title=\"Белгородская область\">Белгородская область</a></li><li><a href=\"?region=32\" title=\"Брянская область\">Брянская область</a></li></ul>\n",
    "            </div><div class=\"popup-city__body_section\">\n",
    "            <div class=\"popup-city__body_title\">В</div>\n",
    "            <ul class=\"popup-city__body_list\"><li><a href=\"?region=33\" title=\"Владимирская область\">Владимирская область</a></li><li><a href=\"?region=34\" title=\"Волгоградская область\">Волгоградская область</a></li><li><a href=\"?region=35\" title=\"Вологодская область\">Вологодская область</a></li><li><a href=\"?region=36\" title=\"Воронежская область\">Воронежская область</a></li></ul>\n",
    "            </div><div class=\"popup-city__body_section\">\n",
    "            <div class=\"popup-city__body_title\">Е</div>\n",
    "            <ul class=\"popup-city__body_list\"><li><a href=\"?region=79\" title=\"Еврейская автономная область\">Еврейская автономная область</a></li></ul>\n",
    "            </div><div class=\"popup-city__body_section\">\n",
    "            <div class=\"popup-city__body_title\">З</div>\n",
    "            <ul class=\"popup-city__body_list\"><li><a href=\"?region=75\" title=\"Забайкальский край\">Забайкальский край</a></li></ul>\n",
    "            </div><div class=\"popup-city__body_section\">\n",
    "            <div class=\"popup-city__body_title\">И</div>\n",
    "            <ul class=\"popup-city__body_list\"><li><a href=\"?region=37\" title=\"Ивановская область\">Ивановская область</a></li><li><a href=\"?region=38\" title=\"Иркутская область\">Иркутская область</a></li></ul>\n",
    "            </div></div>\n",
    "                <div class=\"col-md-3 col-sm-3 col-xs-12\"><div class=\"popup-city__body_section\">\n",
    "            <div class=\"popup-city__body_title\">К</div>\n",
    "            <ul class=\"popup-city__body_list\"><li><a href=\"?region=39\" title=\"Калининградская область\">Калининградская область</a></li><li><a href=\"?region=40\" title=\"Калужская область\">Калужская область</a></li><li><a href=\"?region=41\" title=\"Камчатский край\">Камчатский край</a></li><li><a href=\"?region=42\" title=\"Кемеровская область\">Кемеровская область</a></li><li><a href=\"?region=43\" title=\"Кировская область\">Кировская область</a></li><li><a href=\"?region=44\" title=\"Костромская область\">Костромская область</a></li><li><a href=\"?region=23\" title=\"Краснодарский край\">Краснодарский край</a></li><li><a href=\"?region=24\" title=\"Красноярский край\">Красноярский край</a></li><li><a href=\"?region=45\" title=\"Курганская область\">Курганская область</a></li><li><a href=\"?region=46\" title=\"Курская область\">Курская область</a></li></ul>\n",
    "            </div><div class=\"popup-city__body_section\">\n",
    "            <div class=\"popup-city__body_title\">Л</div>\n",
    "            <ul class=\"popup-city__body_list\"><li><a href=\"?region=48\" title=\"Липецкая область\">Липецкая область</a></li></ul>\n",
    "            </div><div class=\"popup-city__body_section\">\n",
    "            <div class=\"popup-city__body_title\">М</div>\n",
    "            <ul class=\"popup-city__body_list\"><li><a href=\"?region=49\" title=\"Магаданская область\">Магаданская область</a></li><li><a href=\"?region=77\" title=\"Москва и Московская область\">Москва и Московская область</a></li><li><a href=\"?region=51\" title=\"Мурманская область\">Мурманская область</a></li></ul>\n",
    "            </div><div class=\"popup-city__body_section\">\n",
    "            <div class=\"popup-city__body_title\">Н</div>\n",
    "            <ul class=\"popup-city__body_list\"><li><a href=\"?region=52\" title=\"Нижегородская область\">Нижегородская область</a></li><li><a href=\"?region=53\" title=\"Новгородская область\">Новгородская область</a></li><li><a href=\"?region=54\" title=\"Новосибирская область\">Новосибирская область</a></li></ul>\n",
    "            </div></div>\n",
    "                <div class=\"col-md-3 col-sm-3 col-xs-12\"><div class=\"popup-city__body_section\">\n",
    "            <div class=\"popup-city__body_title\">О</div>\n",
    "            <ul class=\"popup-city__body_list\"><li><a href=\"?region=55\" title=\"Омская область\">Омская область</a></li><li><a href=\"?region=56\" title=\"Оренбургская область\">Оренбургская область</a></li><li><a href=\"?region=57\" title=\"Орловская область\">Орловская область</a></li></ul>\n",
    "            </div><div class=\"popup-city__body_section\">\n",
    "            <div class=\"popup-city__body_title\">П</div>\n",
    "            <ul class=\"popup-city__body_list\"><li><a href=\"?region=58\" title=\"Пензенская область\">Пензенская область</a></li><li><a href=\"?region=59\" title=\"Пермский край\">Пермский край</a></li><li><a href=\"?region=25\" title=\"Приморский край\">Приморский край</a></li><li><a href=\"?region=60\" title=\"Псковская область\">Псковская область</a></li></ul>\n",
    "            </div><div class=\"popup-city__body_section\">\n",
    "            <div class=\"popup-city__body_title\">Р</div>\n",
    "            <ul class=\"popup-city__body_list\"><li><a href=\"?region=1\" title=\"Республика Адыгея\">Республика Адыгея</a></li><li><a href=\"?region=2\" title=\"Республика Башкортостан\">Республика Башкортостан</a></li><li><a href=\"?region=3\" title=\"Республика Бурятия\">Республика Бурятия</a></li><li><a href=\"?region=8\" title=\"Республика Калмыкия\">Республика Калмыкия</a></li><li><a href=\"?region=10\" title=\"Республика Карелия\">Республика Карелия</a></li><li><a href=\"?region=11\" title=\"Республика Коми\">Республика Коми</a></li><li><a href=\"?region=12\" title=\"Республика Марий Эл\">Республика Марий Эл</a></li><li><a href=\"?region=13\" title=\"Республика Мордовия\">Республика Мордовия</a></li><li><a href=\"?region=14\" title=\"Республика Саха (Якутия)\">Республика Саха (Якутия)</a></li><li><a href=\"?region=16\" title=\"Республика Татарстан\">Республика Татарстан</a></li><li><a href=\"?region=17\" title=\"Республика Тыва\">Республика Тыва</a></li><li><a href=\"?region=19\" title=\"Республика Хакасия\">Республика Хакасия</a></li><li><a href=\"?region=61\" title=\"Ростовская область\">Ростовская область</a></li><li><a href=\"?region=62\" title=\"Рязанская область\">Рязанская область</a></li></ul>\n",
    "            </div></div>\n",
    "                <div class=\"col-md-3 col-sm-3 col-xs-12\"><div class=\"popup-city__body_section\">\n",
    "            <div class=\"popup-city__body_title\">С</div>\n",
    "            <ul class=\"popup-city__body_list\"><li><a href=\"?region=63\" title=\"Самарская область\">Самарская область</a></li><li><a href=\"?region=78\" title=\"Санкт-Петербург и Ленинградская область\">Санкт-Петербург и Ленинградская область</a></li><li><a href=\"?region=64\" title=\"Саратовская область\">Саратовская область</a></li><li><a href=\"?region=65\" title=\"Сахалинская область\">Сахалинская область</a></li><li><a href=\"?region=66\" title=\"Свердловская область\">Свердловская область</a></li><li><a href=\"?region=67\" title=\"Смоленская область\">Смоленская область</a></li><li><a href=\"?region=26\" title=\"Ставропольский край\">Ставропольский край</a></li></ul>\n",
    "            </div><div class=\"popup-city__body_section\">\n",
    "            <div class=\"popup-city__body_title\">Т</div>\n",
    "            <ul class=\"popup-city__body_list\"><li><a href=\"?region=68\" title=\"Тамбовская область\">Тамбовская область</a></li><li><a href=\"?region=69\" title=\"Тверская область\">Тверская область</a></li><li><a href=\"?region=70\" title=\"Томская область\">Томская область</a></li><li><a href=\"?region=71\" title=\"Тульская область\">Тульская область</a></li><li><a href=\"?region=72\" title=\"Тюменская область\">Тюменская область</a></li></ul>\n",
    "            </div><div class=\"popup-city__body_section\">\n",
    "            <div class=\"popup-city__body_title\">У</div>\n",
    "            <ul class=\"popup-city__body_list\"><li><a href=\"?region=18\" title=\"Удмуртская Республика\">Удмуртская Республика</a></li><li><a href=\"?region=73\" title=\"Ульяновская область\">Ульяновская область</a></li></ul>\n",
    "            </div><div class=\"popup-city__body_section\">\n",
    "            <div class=\"popup-city__body_title\">Х</div>\n",
    "            <ul class=\"popup-city__body_list\"><li><a href=\"?region=27\" title=\"Хабаровский край\">Хабаровский край</a></li></ul>\n",
    "            </div><div class=\"popup-city__body_section\">\n",
    "            <div class=\"popup-city__body_title\">Ч</div>\n",
    "            <ul class=\"popup-city__body_list\"><li><a href=\"?region=74\" title=\"Челябинская область\">Челябинская область</a></li><li><a href=\"?region=21\" title=\"Чувашская Республика\">Чувашская Республика</a></li></ul>\n",
    "            </div><div class=\"popup-city__body_section\">\n",
    "            <div class=\"popup-city__body_title\">Я</div>\n",
    "            <ul class=\"popup-city__body_list\"><li><a href=\"?region=76\" title=\"Ярославская область\">Ярославская область</a></li></ul>\n",
    "            </div></div>            </div>\n",
    "        </div>\n",
    "\"\"\""
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 66,
   "metadata": {},
   "outputs": [],
   "source": [
    "soup = BeautifulSoup(html, 'html.parser')\n",
    "regions = map(itemgetter('href'), soup.find_all('a', href=True))\n",
    "regions = map(lambda s: re.search('\\d+', s).group(0), regions)\n",
    "regions = list(regions)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 67,
   "metadata": {},
   "outputs": [],
   "source": [
    "regions_names = map(itemgetter('title'), soup.find_all('a', href=True))\n",
    "regions_names = list(regions_names)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 4,
   "metadata": {},
   "outputs": [],
   "source": [
    "URL_FORMAT = 'https://www.rosbank.ru/ru/atms/list.php?p_f_2_11={city}&metrocity=-1&p_f_2_22=0&street=&p_f_2_temp_id=459&p_f_2_all=1&page_25={page}'"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 5,
   "metadata": {},
   "outputs": [],
   "source": [
    "!mkdir -p pages"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 8,
   "metadata": {},
   "outputs": [],
   "source": [
    "def process_page(sess, region, city, page):\n",
    "    url = URL_FORMAT.format(city=urllib.parse.quote(city), page=page + 1)\n",
    "    print(url)\n",
    "    r = sess.get(url)\n",
    "    \n",
    "    city = re.sub('\\s', '_', city)\n",
    "    with open(f'pages/{region:02d}_{city}_{page:03d}.html', 'w') as f_html:\n",
    "        f_html.write(r.text)\n",
    "        \n",
    "    return r\n",
    "\n",
    "def process_region(sess, region, cities):\n",
    "    for city in cities:\n",
    "        print(city)\n",
    "        \n",
    "#         if city[0].islower():\n",
    "#             continue\n",
    "    \n",
    "        r = process_page(sess, region, city, 0)\n",
    "        if r.status_code != 200:\n",
    "            print(\"FAIL\", r.status_code)\n",
    "    \n",
    "        try:\n",
    "            soup = BeautifulSoup(r.text, 'html.parser')\n",
    "            pages = soup.find('ul', class_='pagination__inner')\n",
    "            pages = pages.find_all('a', href=True, class_='pagination-page')\n",
    "            pages = max(map(int,map(attrgetter('text'), pages)))\n",
    "        except:\n",
    "            pages = 1\n",
    "    \n",
    "        for page in range(1, pages):\n",
    "            r = process_page(sess, region, city, page)\n",
    "            if r.status_code != 200:\n",
    "                print(\"FAIL\", r.status_code)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "for region in regions:\n",
    "    with requests.Session() as sess:\n",
    "        sess.cookies.update({ 'regionrb': region })\n",
    "        r = sess.get('https://www.rosbank.ru/ru/atms/')\n",
    "        \n",
    "        soup = BeautifulSoup(r.text, 'html.parser')\n",
    "        \n",
    "        cities = soup.find('select', class_='city_select')\n",
    "        cities = cities.find_all('option')\n",
    "        cities = map(attrgetter('text'), cities)\n",
    "        cities = list(cities)\n",
    "        \n",
    "        print(region, len(cities), r.status_code)\n",
    "        \n",
    "        process_region(sess, int(region), cities)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## Скачиваем карты c местоположением банкоматов"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 2,
   "metadata": {},
   "outputs": [],
   "source": [
    "import os"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 7,
   "metadata": {},
   "outputs": [],
   "source": [
    "from urllib.parse import parse_qsl, urlparse\n",
    "\n",
    "def extract_atm_info_file(filename):\n",
    "    atm_info_list = []\n",
    "    \n",
    "    with open(filename, mode='r') as f_name:\n",
    "        soup = BeautifulSoup(f_name, 'html.parser')\n",
    "        \n",
    "    _, name = os.path.split(filename)\n",
    "    name, _ = os.path.splitext(name)\n",
    "    parts = name.split('_')\n",
    "    \n",
    "    region_rb = parts[0]\n",
    "    city = ' '.join(parts[1:-1])\n",
    "\n",
    "    atms = soup.find_all('div', class_=\"page-atm__table_row\")\n",
    "    for atm in atms:\n",
    "        atm_info = {\n",
    "            'bank': atm.find('div', class_=\"address-logo\").text.strip(),\n",
    "            'address': atm.find('div', class_=\"address-title\").text.strip(),\n",
    "            'name': atm.find('div', class_=\"address-type\").text.strip(),\n",
    "            'access24h': 'круглосуточно' in atm.find('div', class_=\"page-atm__table_col--time\").text.lower(),\n",
    "            'id': atm.find('div', class_=\"address-map\").find('a', href=True)[\"href\"],\n",
    "            'region_rb': int(region_rb),\n",
    "            'city': city,\n",
    "        }\n",
    "        \n",
    "        atm_info['id'] = dict(parse_qsl(urlparse(atm_info['id']).query))['showonmap']\n",
    "    \n",
    "        metro = atm.find('div', class_=\"metro-station\")\n",
    "        if metro is not None:\n",
    "            atm_info['metro'] = re.sub('\\s+', ' ', metro.text.strip())\n",
    "            \n",
    "        atm_info_list.append(atm_info)\n",
    "        \n",
    "    return atm_info_list"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "scrolled": true
   },
   "outputs": [],
   "source": [
    "atm_info_list = []\n",
    "\n",
    "for root, dirs, filenames in os.walk('pages'):\n",
    "    for filename in sorted(filenames):\n",
    "        filename = os.path.join(root, filename)\n",
    "        try:\n",
    "            atm_info_list.extend(extract_atm_info_file(filename))\n",
    "        except Exception as e:\n",
    "            print(filename)\n",
    "            raise e\n",
    "        \n",
    "atm_info_list[:5]     "
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 5,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "[('https://www.rosbank.ru/ru/atms/?showonmap=62584&test=1', 77),\n",
       " ('https://www.rosbank.ru/ru/atms/?showonmap=80131', 38),\n",
       " ('https://www.rosbank.ru/ru/atms/?showonmap=75389&test=1', 77),\n",
       " ('https://www.rosbank.ru/ru/atms/?showonmap=69881&test=1', 77),\n",
       " ('https://www.rosbank.ru/ru/atms/?showonmap=82021&test=1', 77)]"
      ]
     },
     "execution_count": 5,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "atm_urls = map(itemgetter('id'), atm_info_list)\n",
    "atm_urls = map(lambda s: 'https://www.rosbank.ru/ru/atms/?showonmap={}'.format(s), atm_urls)\n",
    "\n",
    "region_urls = map(itemgetter('region_rb'), atm_info_list)\n",
    "\n",
    "atm_urls = list(set(zip(atm_urls, region_urls)))\n",
    "atm_urls[:5]"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 6,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "10194"
      ]
     },
     "execution_count": 6,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "len(atm_urls)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 8,
   "metadata": {},
   "outputs": [],
   "source": [
    "!mkdir -p atms"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 9,
   "metadata": {},
   "outputs": [],
   "source": [
    "def process_atm_page(region, url):    \n",
    "    with requests.Session() as sess:\n",
    "        sess.cookies.update({ 'regionrb': str(region) })\n",
    "        r = sess.get(url)\n",
    "        \n",
    "    with open(f'atms/{region:02d}_{atm_id}.html', 'w') as f_html:\n",
    "        f_html.write(r.text)\n",
    "        \n",
    "    return r\n",
    "\n",
    "def process_atm_page_wrapper(pair):\n",
    "    return process_atm_page(*pair[::-1])"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 10,
   "metadata": {},
   "outputs": [],
   "source": [
    "from multiprocessing.pool import ThreadPool"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 11,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "CPU times: user 4min 30s, sys: 32.1 s, total: 5min 2s\n",
      "Wall time: 32min 29s\n"
     ]
    }
   ],
   "source": [
    "%%time\n",
    "\n",
    "pool = ThreadPool(8)\n",
    "r = pool.map(process_atm_page_wrapper, atm_urls)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 12,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "True"
      ]
     },
     "execution_count": 12,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "all(map(lambda k: k.status_code == 200, r))"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## Формируем датасет"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 2,
   "metadata": {},
   "outputs": [],
   "source": [
    "import pandas as pd\n",
    "import numpy as np"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 9,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "CPU times: user 2min 59s, sys: 301 ms, total: 2min 59s\n",
      "Wall time: 3min 10s\n"
     ]
    }
   ],
   "source": [
    "%%time\n",
    "\n",
    "df_atm_info = []\n",
    "\n",
    "for root, dirs, filenames in os.walk('pages'):\n",
    "    for filename in sorted(filenames):\n",
    "        filename = os.path.join(root, filename)\n",
    "        df_atm_info.extend(extract_atm_info_file(filename))\n",
    "        \n",
    "df_atm_info[:5]"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 14,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>access24h</th>\n",
       "      <th>address</th>\n",
       "      <th>bank</th>\n",
       "      <th>city</th>\n",
       "      <th>id</th>\n",
       "      <th>metro</th>\n",
       "      <th>name</th>\n",
       "      <th>region_rb</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>False</td>\n",
       "      <td>Улица Партизана Железняка  35А</td>\n",
       "      <td>Росбанк</td>\n",
       "      <td>Красноярск</td>\n",
       "      <td>45351</td>\n",
       "      <td>NaN</td>\n",
       "      <td>Бизнес Центр Сириус ХАБ ПАО РОСБАНК</td>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>False</td>\n",
       "      <td>Улица Тургеневское шоссе  27</td>\n",
       "      <td>Росбанк</td>\n",
       "      <td>Новая Адыгея</td>\n",
       "      <td>44392</td>\n",
       "      <td>NaN</td>\n",
       "      <td>магазин \"Декатлон\"</td>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>False</td>\n",
       "      <td>Тургеневское ш., 27</td>\n",
       "      <td>Альфабанк</td>\n",
       "      <td>Новая Адыгея</td>\n",
       "      <td>81782</td>\n",
       "      <td>NaN</td>\n",
       "      <td></td>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>False</td>\n",
       "      <td>поселок Новая Адыгея, шоссе Тургеневское, дом 27</td>\n",
       "      <td>УРАЛСИБ Банк</td>\n",
       "      <td>Новая Адыгея</td>\n",
       "      <td>61293</td>\n",
       "      <td>NaN</td>\n",
       "      <td>Входная группа ТЦ \"ИКЕА\"</td>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>True</td>\n",
       "      <td>Адыгейская ул., 93</td>\n",
       "      <td>Альфабанк</td>\n",
       "      <td>Тахтамукай</td>\n",
       "      <td>83137</td>\n",
       "      <td>NaN</td>\n",
       "      <td></td>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "   access24h                                           address          bank  \\\n",
       "0      False                    Улица Партизана Железняка  35А       Росбанк   \n",
       "1      False                      Улица Тургеневское шоссе  27       Росбанк   \n",
       "2      False                               Тургеневское ш., 27     Альфабанк   \n",
       "3      False  поселок Новая Адыгея, шоссе Тургеневское, дом 27  УРАЛСИБ Банк   \n",
       "4       True                                Адыгейская ул., 93     Альфабанк   \n",
       "\n",
       "           city     id metro                                 name  region_rb  \n",
       "0    Красноярск  45351   NaN  Бизнес Центр Сириус ХАБ ПАО РОСБАНК          1  \n",
       "1  Новая Адыгея  44392   NaN                   магазин \"Декатлон\"          1  \n",
       "2  Новая Адыгея  81782   NaN                                               1  \n",
       "3  Новая Адыгея  61293   NaN             Входная группа ТЦ \"ИКЕА\"          1  \n",
       "4    Тахтамукай  83137   NaN                                               1  "
      ]
     },
     "execution_count": 14,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df_atm_info = pd.DataFrame(df_atm_info)\n",
    "df_atm_info.head()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 11,
   "metadata": {},
   "outputs": [],
   "source": [
    "COORD_PATTERN = re.compile('var long=(\\d+\\.\\d+); var lati=(\\d+\\.\\d+);')\n",
    "\n",
    "def extract_atm_coords(filename):\n",
    "    with open(filename, mode='r') as f_atm:\n",
    "        soup = BeautifulSoup(f_atm, 'html.parser')\n",
    "    \n",
    "    _, name = os.path.split(filename)\n",
    "    name, _ = os.path.splitext(name)\n",
    "    name = name.split('_')[1]\n",
    "    \n",
    "    script = soup.find('div', class_='page-atm__map').find('script').text\n",
    "    script = map(str.strip, script.split('\\n'))\n",
    "    \n",
    "    lng, lat = None, None\n",
    "    \n",
    "    for s in script:\n",
    "        if s.startswith('var'):\n",
    "            res = COORD_PATTERN.search(s)\n",
    "            if res is not None:\n",
    "                lng, lat = res.group(1), res.group(2)\n",
    "                break\n",
    "    \n",
    "    return {'id': name, 'lng': lng, 'lat': lat}"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 17,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "atms/03_46063.html\n",
      "atms/17_44107.html\n",
      "atms/17_45284.html\n",
      "atms/25_44667.html\n",
      "atms/25_45485.html\n",
      "atms/25_45578.html\n",
      "atms/25_82239.html\n",
      "atms/27_50041.html\n",
      "atms/27_50050.html\n",
      "atms/30_44668.html\n",
      "atms/37_44626.html\n",
      "atms/37_82535.html\n",
      "atms/49_75072.html\n",
      "atms/51_45355.html\n",
      "atms/52_45087.html\n",
      "atms/55_79041.html\n",
      "atms/72_44801.html\n",
      "atms/75_45721.html\n",
      "atms/77_62931.html\n",
      "CPU times: user 10min 50s, sys: 1.07 s, total: 10min 51s\n",
      "Wall time: 11min 54s\n"
     ]
    }
   ],
   "source": [
    "%%time\n",
    "\n",
    "df_atm_coords = []\n",
    "\n",
    "for root, dirs, filenames in os.walk('atms'):\n",
    "    for filename in sorted(filenames):\n",
    "        filename = os.path.join(root, filename)\n",
    "        try:\n",
    "            df_atm_coords.append(extract_atm_coords(filename))\n",
    "        except Exception as e:\n",
    "            print(filename)\n",
    "        \n",
    "df_atm_coords[:5]"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 18,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>id</th>\n",
       "      <th>lat</th>\n",
       "      <th>lng</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>44392</td>\n",
       "      <td>45.013084</td>\n",
       "      <td>38.929328</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>45351</td>\n",
       "      <td>56.035552</td>\n",
       "      <td>92.930698</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>61293</td>\n",
       "      <td>45.013084</td>\n",
       "      <td>38.929328</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>75759</td>\n",
       "      <td>51.810559</td>\n",
       "      <td>107.607134</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>81782</td>\n",
       "      <td>45.012358</td>\n",
       "      <td>38.928279</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "      id        lat         lng\n",
       "0  44392  45.013084   38.929328\n",
       "1  45351  56.035552   92.930698\n",
       "2  61293  45.013084   38.929328\n",
       "3  75759  51.810559  107.607134\n",
       "4  81782  45.012358   38.928279"
      ]
     },
     "execution_count": 18,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df_atm_coords = pd.DataFrame(df_atm_coords)\n",
    "df_atm_coords.head()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 166,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>access24h</th>\n",
       "      <th>address</th>\n",
       "      <th>bank</th>\n",
       "      <th>city</th>\n",
       "      <th>id</th>\n",
       "      <th>metro</th>\n",
       "      <th>name</th>\n",
       "      <th>region_rb</th>\n",
       "      <th>lat</th>\n",
       "      <th>lng</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>False</td>\n",
       "      <td>Улица Партизана Железняка  35А</td>\n",
       "      <td>Росбанк</td>\n",
       "      <td>Красноярск</td>\n",
       "      <td>45351</td>\n",
       "      <td>NaN</td>\n",
       "      <td>Бизнес Центр Сириус ХАБ ПАО РОСБАНК</td>\n",
       "      <td>1</td>\n",
       "      <td>56.035552</td>\n",
       "      <td>92.930698</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>False</td>\n",
       "      <td>Улица Тургеневское шоссе  27</td>\n",
       "      <td>Росбанк</td>\n",
       "      <td>Новая Адыгея</td>\n",
       "      <td>44392</td>\n",
       "      <td>NaN</td>\n",
       "      <td>магазин \"Декатлон\"</td>\n",
       "      <td>1</td>\n",
       "      <td>45.013084</td>\n",
       "      <td>38.929328</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>False</td>\n",
       "      <td>Тургеневское ш., 27</td>\n",
       "      <td>Альфабанк</td>\n",
       "      <td>Новая Адыгея</td>\n",
       "      <td>81782</td>\n",
       "      <td>NaN</td>\n",
       "      <td></td>\n",
       "      <td>1</td>\n",
       "      <td>45.012358</td>\n",
       "      <td>38.928279</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>5</th>\n",
       "      <td>True</td>\n",
       "      <td>Адыгейская ул., 93</td>\n",
       "      <td>Альфабанк</td>\n",
       "      <td>Тахтамукай</td>\n",
       "      <td>83137</td>\n",
       "      <td>NaN</td>\n",
       "      <td></td>\n",
       "      <td>1</td>\n",
       "      <td>44.9290606</td>\n",
       "      <td>38.9800929</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>6</th>\n",
       "      <td>False</td>\n",
       "      <td>ул. Геологическая, 28</td>\n",
       "      <td>Газпромбанк</td>\n",
       "      <td>Улан-Удэ</td>\n",
       "      <td>75759</td>\n",
       "      <td>NaN</td>\n",
       "      <td>САО \"ВСК\"</td>\n",
       "      <td>1</td>\n",
       "      <td>51.810559</td>\n",
       "      <td>107.607134</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>7</th>\n",
       "      <td>False</td>\n",
       "      <td>ул. Пушкина, 27</td>\n",
       "      <td>Альфабанк</td>\n",
       "      <td>Белорецк</td>\n",
       "      <td>82196</td>\n",
       "      <td>NaN</td>\n",
       "      <td></td>\n",
       "      <td>2</td>\n",
       "      <td>53.971524</td>\n",
       "      <td>58.409304</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>8</th>\n",
       "      <td>False</td>\n",
       "      <td>город Белорецк</td>\n",
       "      <td>УРАЛСИБ Банк</td>\n",
       "      <td>Белорецк</td>\n",
       "      <td>81737</td>\n",
       "      <td>NaN</td>\n",
       "      <td></td>\n",
       "      <td>2</td>\n",
       "      <td>53.967621</td>\n",
       "      <td>58.410023</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>10</th>\n",
       "      <td>False</td>\n",
       "      <td>город Белорецк, улица Кирова, дом 48</td>\n",
       "      <td>УРАЛСИБ Банк</td>\n",
       "      <td>Белорецк</td>\n",
       "      <td>62000</td>\n",
       "      <td>NaN</td>\n",
       "      <td></td>\n",
       "      <td>2</td>\n",
       "      <td>53.962207</td>\n",
       "      <td>58.401435</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>12</th>\n",
       "      <td>False</td>\n",
       "      <td>город Белорецк, улица Точисского, дом 1, строе...</td>\n",
       "      <td>УРАЛСИБ Банк</td>\n",
       "      <td>Белорецк</td>\n",
       "      <td>61965</td>\n",
       "      <td>NaN</td>\n",
       "      <td></td>\n",
       "      <td>2</td>\n",
       "      <td>53.971667</td>\n",
       "      <td>58.402181</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>20</th>\n",
       "      <td>True</td>\n",
       "      <td>город Белорецк, улица Пушкина, дом 27</td>\n",
       "      <td>УРАЛСИБ Банк</td>\n",
       "      <td>Белорецк</td>\n",
       "      <td>61769</td>\n",
       "      <td>NaN</td>\n",
       "      <td></td>\n",
       "      <td>2</td>\n",
       "      <td>53.97154</td>\n",
       "      <td>58.409286</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "    access24h                                            address  \\\n",
       "0       False                     Улица Партизана Железняка  35А   \n",
       "1       False                       Улица Тургеневское шоссе  27   \n",
       "2       False                                Тургеневское ш., 27   \n",
       "5        True                                 Адыгейская ул., 93   \n",
       "6       False                              ул. Геологическая, 28   \n",
       "7       False                                    ул. Пушкина, 27   \n",
       "8       False                                     город Белорецк   \n",
       "10      False               город Белорецк, улица Кирова, дом 48   \n",
       "12      False  город Белорецк, улица Точисского, дом 1, строе...   \n",
       "20       True              город Белорецк, улица Пушкина, дом 27   \n",
       "\n",
       "            bank          city     id metro  \\\n",
       "0        Росбанк    Красноярск  45351   NaN   \n",
       "1        Росбанк  Новая Адыгея  44392   NaN   \n",
       "2      Альфабанк  Новая Адыгея  81782   NaN   \n",
       "5      Альфабанк    Тахтамукай  83137   NaN   \n",
       "6    Газпромбанк      Улан-Удэ  75759   NaN   \n",
       "7      Альфабанк      Белорецк  82196   NaN   \n",
       "8   УРАЛСИБ Банк      Белорецк  81737   NaN   \n",
       "10  УРАЛСИБ Банк      Белорецк  62000   NaN   \n",
       "12  УРАЛСИБ Банк      Белорецк  61965   NaN   \n",
       "20  УРАЛСИБ Банк      Белорецк  61769   NaN   \n",
       "\n",
       "                                   name  region_rb         lat         lng  \n",
       "0   Бизнес Центр Сириус ХАБ ПАО РОСБАНК          1   56.035552   92.930698  \n",
       "1                    магазин \"Декатлон\"          1   45.013084   38.929328  \n",
       "2                                                1   45.012358   38.928279  \n",
       "5                                                1  44.9290606  38.9800929  \n",
       "6                             САО \"ВСК\"          1   51.810559  107.607134  \n",
       "7                                                2   53.971524   58.409304  \n",
       "8                                                2   53.967621   58.410023  \n",
       "10                                               2   53.962207   58.401435  \n",
       "12                                               2   53.971667   58.402181  \n",
       "20                                               2    53.97154   58.409286  "
      ]
     },
     "execution_count": 166,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df_atm = pd.merge(df_atm_info, df_atm_coords, on='id')\n",
    "df_atm.dropna(subset=['lat', 'lng'], inplace=True)\n",
    "df_atm.drop_duplicates(subset=['id'], inplace=True)\n",
    "df_atm.drop_duplicates(subset=['lat', 'lng'], inplace=True)\n",
    "df_atm.head(10)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 167,
   "metadata": {},
   "outputs": [],
   "source": [
    "def preprocess_cities(city):\n",
    "    city = re.sub('\\(.*?\\)', '', city)\n",
    "    city = re.sub('\\S+\\.', '', city)\n",
    "    city = re.sub(',(?:.*?)', '', city)\n",
    "    city = re.sub('поселок городского типа', '', city)\n",
    "    city = re.sub('(?:город|cтаница|поселок|село|ст\\-ца|деревня)', '', city)\n",
    "    city = city.strip().title()\n",
    "    \n",
    "    if city == 'Лесной Ок':\n",
    "        city = 'Лесной Городок'\n",
    "    elif city == 'Ского Типа Белоозерский':\n",
    "        city = 'Белоозерский'\n",
    "    elif city == 'Совхоз Им Ленина':\n",
    "        city = 'поселок совхоза имени Ленина'\n",
    "    elif city == 'Новая Тура С':\n",
    "        city = 'Новая Тура'\n",
    "    elif city == 'Нижний Нов':\n",
    "        city = 'Нижний Новгород'\n",
    "    elif city == 'Великий Нов':\n",
    "        city = 'Великий Новгород'\n",
    "    elif city == 'Бурнашево С':\n",
    "        city = 'Бурнашево'\n",
    "    elif city == 'Апастово Пгт':\n",
    "        city = 'Апастово'\n",
    "    return city"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 168,
   "metadata": {},
   "outputs": [],
   "source": [
    "df_atm['city'] = df_atm['city'].apply(preprocess_cities)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 170,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "(887,)"
      ]
     },
     "execution_count": 170,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df_atm['city'].unique().shape"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 171,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>access24h</th>\n",
       "      <th>address</th>\n",
       "      <th>bank</th>\n",
       "      <th>city</th>\n",
       "      <th>id</th>\n",
       "      <th>metro</th>\n",
       "      <th>name</th>\n",
       "      <th>region_rb</th>\n",
       "      <th>lat</th>\n",
       "      <th>lng</th>\n",
       "      <th>region_name</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>False</td>\n",
       "      <td>Улица Партизана Железняка  35А</td>\n",
       "      <td>Росбанк</td>\n",
       "      <td>Красноярск</td>\n",
       "      <td>45351</td>\n",
       "      <td>NaN</td>\n",
       "      <td>Бизнес Центр Сириус ХАБ ПАО РОСБАНК</td>\n",
       "      <td>1</td>\n",
       "      <td>56.035552</td>\n",
       "      <td>92.930698</td>\n",
       "      <td>Республика Адыгея</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>False</td>\n",
       "      <td>Улица Тургеневское шоссе  27</td>\n",
       "      <td>Росбанк</td>\n",
       "      <td>Новая Адыгея</td>\n",
       "      <td>44392</td>\n",
       "      <td>NaN</td>\n",
       "      <td>магазин \"Декатлон\"</td>\n",
       "      <td>1</td>\n",
       "      <td>45.013084</td>\n",
       "      <td>38.929328</td>\n",
       "      <td>Республика Адыгея</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>False</td>\n",
       "      <td>Тургеневское ш., 27</td>\n",
       "      <td>Альфа-Банк</td>\n",
       "      <td>Новая Адыгея</td>\n",
       "      <td>81782</td>\n",
       "      <td>NaN</td>\n",
       "      <td></td>\n",
       "      <td>1</td>\n",
       "      <td>45.012358</td>\n",
       "      <td>38.928279</td>\n",
       "      <td>Республика Адыгея</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>5</th>\n",
       "      <td>True</td>\n",
       "      <td>Адыгейская ул., 93</td>\n",
       "      <td>Альфа-Банк</td>\n",
       "      <td>Тахтамукай</td>\n",
       "      <td>83137</td>\n",
       "      <td>NaN</td>\n",
       "      <td></td>\n",
       "      <td>1</td>\n",
       "      <td>44.9290606</td>\n",
       "      <td>38.9800929</td>\n",
       "      <td>Республика Адыгея</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>6</th>\n",
       "      <td>False</td>\n",
       "      <td>ул. Геологическая, 28</td>\n",
       "      <td>Газпромбанк</td>\n",
       "      <td>Улан-Удэ</td>\n",
       "      <td>75759</td>\n",
       "      <td>NaN</td>\n",
       "      <td>САО \"ВСК\"</td>\n",
       "      <td>1</td>\n",
       "      <td>51.810559</td>\n",
       "      <td>107.607134</td>\n",
       "      <td>Республика Адыгея</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>7</th>\n",
       "      <td>False</td>\n",
       "      <td>ул. Пушкина, 27</td>\n",
       "      <td>Альфа-Банк</td>\n",
       "      <td>Белорецк</td>\n",
       "      <td>82196</td>\n",
       "      <td>NaN</td>\n",
       "      <td></td>\n",
       "      <td>2</td>\n",
       "      <td>53.971524</td>\n",
       "      <td>58.409304</td>\n",
       "      <td>Республика Башкортостан</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>8</th>\n",
       "      <td>False</td>\n",
       "      <td>город Белорецк</td>\n",
       "      <td>Банк Уралсиб</td>\n",
       "      <td>Белорецк</td>\n",
       "      <td>81737</td>\n",
       "      <td>NaN</td>\n",
       "      <td></td>\n",
       "      <td>2</td>\n",
       "      <td>53.967621</td>\n",
       "      <td>58.410023</td>\n",
       "      <td>Республика Башкортостан</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>10</th>\n",
       "      <td>False</td>\n",
       "      <td>город Белорецк, улица Кирова, дом 48</td>\n",
       "      <td>Банк Уралсиб</td>\n",
       "      <td>Белорецк</td>\n",
       "      <td>62000</td>\n",
       "      <td>NaN</td>\n",
       "      <td></td>\n",
       "      <td>2</td>\n",
       "      <td>53.962207</td>\n",
       "      <td>58.401435</td>\n",
       "      <td>Республика Башкортостан</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>12</th>\n",
       "      <td>False</td>\n",
       "      <td>город Белорецк, улица Точисского, дом 1, строе...</td>\n",
       "      <td>Банк Уралсиб</td>\n",
       "      <td>Белорецк</td>\n",
       "      <td>61965</td>\n",
       "      <td>NaN</td>\n",
       "      <td></td>\n",
       "      <td>2</td>\n",
       "      <td>53.971667</td>\n",
       "      <td>58.402181</td>\n",
       "      <td>Республика Башкортостан</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>20</th>\n",
       "      <td>True</td>\n",
       "      <td>город Белорецк, улица Пушкина, дом 27</td>\n",
       "      <td>Банк Уралсиб</td>\n",
       "      <td>Белорецк</td>\n",
       "      <td>61769</td>\n",
       "      <td>NaN</td>\n",
       "      <td></td>\n",
       "      <td>2</td>\n",
       "      <td>53.97154</td>\n",
       "      <td>58.409286</td>\n",
       "      <td>Республика Башкортостан</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "    access24h                                            address  \\\n",
       "0       False                     Улица Партизана Железняка  35А   \n",
       "1       False                       Улица Тургеневское шоссе  27   \n",
       "2       False                                Тургеневское ш., 27   \n",
       "5        True                                 Адыгейская ул., 93   \n",
       "6       False                              ул. Геологическая, 28   \n",
       "7       False                                    ул. Пушкина, 27   \n",
       "8       False                                     город Белорецк   \n",
       "10      False               город Белорецк, улица Кирова, дом 48   \n",
       "12      False  город Белорецк, улица Точисского, дом 1, строе...   \n",
       "20       True              город Белорецк, улица Пушкина, дом 27   \n",
       "\n",
       "            bank          city     id metro  \\\n",
       "0        Росбанк    Красноярск  45351   NaN   \n",
       "1        Росбанк  Новая Адыгея  44392   NaN   \n",
       "2     Альфа-Банк  Новая Адыгея  81782   NaN   \n",
       "5     Альфа-Банк    Тахтамукай  83137   NaN   \n",
       "6    Газпромбанк      Улан-Удэ  75759   NaN   \n",
       "7     Альфа-Банк      Белорецк  82196   NaN   \n",
       "8   Банк Уралсиб      Белорецк  81737   NaN   \n",
       "10  Банк Уралсиб      Белорецк  62000   NaN   \n",
       "12  Банк Уралсиб      Белорецк  61965   NaN   \n",
       "20  Банк Уралсиб      Белорецк  61769   NaN   \n",
       "\n",
       "                                   name  region_rb         lat         lng  \\\n",
       "0   Бизнес Центр Сириус ХАБ ПАО РОСБАНК          1   56.035552   92.930698   \n",
       "1                    магазин \"Декатлон\"          1   45.013084   38.929328   \n",
       "2                                                1   45.012358   38.928279   \n",
       "5                                                1  44.9290606  38.9800929   \n",
       "6                             САО \"ВСК\"          1   51.810559  107.607134   \n",
       "7                                                2   53.971524   58.409304   \n",
       "8                                                2   53.967621   58.410023   \n",
       "10                                               2   53.962207   58.401435   \n",
       "12                                               2   53.971667   58.402181   \n",
       "20                                               2    53.97154   58.409286   \n",
       "\n",
       "                region_name  \n",
       "0         Республика Адыгея  \n",
       "1         Республика Адыгея  \n",
       "2         Республика Адыгея  \n",
       "5         Республика Адыгея  \n",
       "6         Республика Адыгея  \n",
       "7   Республика Башкортостан  \n",
       "8   Республика Башкортостан  \n",
       "10  Республика Башкортостан  \n",
       "12  Республика Башкортостан  \n",
       "20  Республика Башкортостан  "
      ]
     },
     "execution_count": 171,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "regions_map = dict(zip(map(int, regions), regions_names))\n",
    "df_atm['region_name'] = df_atm['region_rb'].map(regions_map)\n",
    "\n",
    "banks_map = {\n",
    "    'Росбанк': 'Росбанк',\n",
    "    'Альфабанк': 'Альфа-Банк',\n",
    "    'УРАЛСИБ Банк': 'Банк Уралсиб',\n",
    "    'Газпромбанк': 'Газпромбанк',\n",
    "    'АК БАРС': 'Ак Барс',\n",
    "    'Россельхозбанк': 'Россельхозбанк',\n",
    "    'Райффайзен-банк': 'Райффайзенбанк'\n",
    "}\n",
    "df_atm['bank'] = df_atm['bank'].map(banks_map)\n",
    "\n",
    "df_atm.head(10)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 172,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "(7903, 11)"
      ]
     },
     "execution_count": 172,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df_atm.shape"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 174,
   "metadata": {},
   "outputs": [],
   "source": [
    "df_atm.to_csv('dataset_rosbank.csv', sep=',', index=False)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## Приводим адреса банкоматов к единому виду"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 1,
   "metadata": {},
   "outputs": [],
   "source": [
    "import requests"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 2,
   "metadata": {},
   "outputs": [],
   "source": [
    "from functools import wraps\n",
    "\n",
    "import time\n",
    "\n",
    "def timeout(rps=5):\n",
    "    t_max = 1.0 / rps\n",
    "\n",
    "    def decorator(func):\n",
    "        @wraps(func)\n",
    "        def wrapper(*args, **kwargs):\n",
    "            t_start = time.time()\n",
    "            res = func(*args, **kwargs)\n",
    "            t_delta = time.time() - t_start\n",
    "\n",
    "            if t_delta < t_max:\n",
    "                time.sleep(t_max - t_delta)\n",
    "            return res\n",
    "        return wrapper\n",
    "    return decorator"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 3,
   "metadata": {},
   "outputs": [],
   "source": [
    "with open('yandex-api.key', mode='r') as f_key:\n",
    "    API_KEY = next(f_key).strip()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 4,
   "metadata": {},
   "outputs": [],
   "source": [
    "def yandex_request_coord(lat, lon):\n",
    "    r = requests.get(\"https://geocode-maps.yandex.ru/1.x/\",\n",
    "                     params={\"format\": \"json\",\n",
    "                             \"apikey\": API_KEY,\n",
    "                             \"geocode\": f\"{lat},{lon}\",\n",
    "                             \"results\": 1})\n",
    "    return r\n",
    "\n",
    "def yandex_request_address(address):\n",
    "    r = requests.get(\"https://geocode-maps.yandex.ru/1.x/\",\n",
    "                     params={\"format\": \"json\",\n",
    "                             \"apikey\": API_KEY,\n",
    "                             \"geocode\": f\"{address}\",\n",
    "                             \"results\": 1})\n",
    "    return r\n",
    "\n",
    "@timeout(rps=5)\n",
    "def yandex_request(args):\n",
    "    if len(args) == 2:\n",
    "        return yandex_request_coord(*args)\n",
    "    elif len(args) == 1:\n",
    "        return yandex_request_address(*args)\n",
    "    else:\n",
    "        raise TypeError('yandex_request(args) takes 1 positional argument. 1 <= len(args) <= 2')"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 5,
   "metadata": {},
   "outputs": [],
   "source": [
    "from tqdm import tqdm"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 14,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>access24h</th>\n",
       "      <th>address</th>\n",
       "      <th>bank</th>\n",
       "      <th>city</th>\n",
       "      <th>id</th>\n",
       "      <th>metro</th>\n",
       "      <th>name</th>\n",
       "      <th>region_rb</th>\n",
       "      <th>lat</th>\n",
       "      <th>lng</th>\n",
       "      <th>region_name</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>False</td>\n",
       "      <td>Улица Партизана Железняка  35А</td>\n",
       "      <td>Росбанк</td>\n",
       "      <td>Красноярск</td>\n",
       "      <td>45351</td>\n",
       "      <td>NaN</td>\n",
       "      <td>Бизнес Центр Сириус ХАБ ПАО РОСБАНК</td>\n",
       "      <td>1</td>\n",
       "      <td>56.035552</td>\n",
       "      <td>92.930698</td>\n",
       "      <td>Республика Адыгея</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>False</td>\n",
       "      <td>Улица Тургеневское шоссе  27</td>\n",
       "      <td>Росбанк</td>\n",
       "      <td>Новая Адыгея</td>\n",
       "      <td>44392</td>\n",
       "      <td>NaN</td>\n",
       "      <td>магазин \"Декатлон\"</td>\n",
       "      <td>1</td>\n",
       "      <td>45.013084</td>\n",
       "      <td>38.929328</td>\n",
       "      <td>Республика Адыгея</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>False</td>\n",
       "      <td>Тургеневское ш., 27</td>\n",
       "      <td>Альфа-Банк</td>\n",
       "      <td>Новая Адыгея</td>\n",
       "      <td>81782</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>1</td>\n",
       "      <td>45.012358</td>\n",
       "      <td>38.928279</td>\n",
       "      <td>Республика Адыгея</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>True</td>\n",
       "      <td>Адыгейская ул., 93</td>\n",
       "      <td>Альфа-Банк</td>\n",
       "      <td>Тахтамукай</td>\n",
       "      <td>83137</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>1</td>\n",
       "      <td>44.929061</td>\n",
       "      <td>38.980093</td>\n",
       "      <td>Республика Адыгея</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>False</td>\n",
       "      <td>ул. Геологическая, 28</td>\n",
       "      <td>Газпромбанк</td>\n",
       "      <td>Улан-Удэ</td>\n",
       "      <td>75759</td>\n",
       "      <td>NaN</td>\n",
       "      <td>САО \"ВСК\"</td>\n",
       "      <td>1</td>\n",
       "      <td>51.810559</td>\n",
       "      <td>107.607134</td>\n",
       "      <td>Республика Адыгея</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "   access24h                         address         bank          city  \\\n",
       "0      False  Улица Партизана Железняка  35А      Росбанк    Красноярск   \n",
       "1      False    Улица Тургеневское шоссе  27      Росбанк  Новая Адыгея   \n",
       "2      False             Тургеневское ш., 27   Альфа-Банк  Новая Адыгея   \n",
       "3       True              Адыгейская ул., 93   Альфа-Банк    Тахтамукай   \n",
       "4      False           ул. Геологическая, 28  Газпромбанк      Улан-Удэ   \n",
       "\n",
       "      id metro                                 name  region_rb        lat  \\\n",
       "0  45351   NaN  Бизнес Центр Сириус ХАБ ПАО РОСБАНК          1  56.035552   \n",
       "1  44392   NaN                   магазин \"Декатлон\"          1  45.013084   \n",
       "2  81782   NaN                                  NaN          1  45.012358   \n",
       "3  83137   NaN                                  NaN          1  44.929061   \n",
       "4  75759   NaN                            САО \"ВСК\"          1  51.810559   \n",
       "\n",
       "          lng        region_name  \n",
       "0   92.930698  Республика Адыгея  \n",
       "1   38.929328  Республика Адыгея  \n",
       "2   38.928279  Республика Адыгея  \n",
       "3   38.980093  Республика Адыгея  \n",
       "4  107.607134  Республика Адыгея  "
      ]
     },
     "execution_count": 14,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "import pandas as pd\n",
    "\n",
    "df_atm = pd.read_csv('dataset_rosbank.csv', sep=',')\n",
    "df_atm['address'] = df_atm['address'].fillna('')\n",
    "df_atm.head()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 7,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "lat    False\n",
       "lng    False\n",
       "dtype: bool"
      ]
     },
     "execution_count": 7,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df_atm[['lat', 'lng']].isna().any()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "r_yandex = {}\n",
    "\n",
    "for r in tqdm(df_atm.itertuples()):\n",
    "    args = (r.lng, r.lat)\n",
    "\n",
    "    if r.id in r_yandex:\n",
    "        continue\n",
    "    \n",
    "    res = yandex_request(args)\n",
    "    if res.status_code != 200:\n",
    "        print(\"FAILED: \", args)\n",
    "    r_yandex[r.id] = res"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 11,
   "metadata": {},
   "outputs": [],
   "source": [
    "import pickle\n",
    "\n",
    "with open('yandex-rosbank.dump', mode='wb') as f_dump:\n",
    "    pickle.dump(r_yandex, f_dump)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "import pickle\n",
    "\n",
    "with open('yandex-rosbank.dump', mode='rb') as f_dump:\n",
    "    r_yandex = pickle.load(f_dump)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 12,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>address</th>\n",
       "      <th>id</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>Красноярск, улица Партизана Железняка, 35А</td>\n",
       "      <td>45351</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>Республика Адыгея, Тахтамукайский район, посёл...</td>\n",
       "      <td>44392</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>Республика Адыгея, Тахтамукайский район, посёл...</td>\n",
       "      <td>81782</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>Республика Адыгея, Тахтамукайский район, аул Т...</td>\n",
       "      <td>83137</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>Республика Бурятия, Улан-Удэ, Геологическая ул...</td>\n",
       "      <td>75759</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "                                             address     id\n",
       "0         Красноярск, улица Партизана Железняка, 35А  45351\n",
       "1  Республика Адыгея, Тахтамукайский район, посёл...  44392\n",
       "2  Республика Адыгея, Тахтамукайский район, посёл...  81782\n",
       "3  Республика Адыгея, Тахтамукайский район, аул Т...  83137\n",
       "4  Республика Бурятия, Улан-Удэ, Геологическая ул...  75759"
      ]
     },
     "execution_count": 12,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df_atms_address = []\n",
    "\n",
    "for id_, r in r_yandex.items():\n",
    "    atm_info = {'id': id_}\n",
    "    \n",
    "    r = r_yandex[id_].json()['response']['GeoObjectCollection']['featureMember']\n",
    "    if len(r):\n",
    "        r = r[0]['GeoObject']\n",
    "        \n",
    "        try:\n",
    "            address = r['metaDataProperty']['GeocoderMetaData']['Address']['formatted']\n",
    "        except:\n",
    "            address = ''\n",
    "        finally:\n",
    "            atm_info['address'] = address\n",
    "        \n",
    "    df_atms_address.append(atm_info)\n",
    "    \n",
    "df_atms_address = pd.DataFrame(df_atms_address)\n",
    "df_atms_address.head()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 15,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>access24h</th>\n",
       "      <th>bank</th>\n",
       "      <th>city</th>\n",
       "      <th>id</th>\n",
       "      <th>metro</th>\n",
       "      <th>name</th>\n",
       "      <th>region_rb</th>\n",
       "      <th>lat</th>\n",
       "      <th>lng</th>\n",
       "      <th>region_name</th>\n",
       "      <th>address</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>False</td>\n",
       "      <td>Росбанк</td>\n",
       "      <td>Красноярск</td>\n",
       "      <td>45351</td>\n",
       "      <td>NaN</td>\n",
       "      <td>Бизнес Центр Сириус ХАБ ПАО РОСБАНК</td>\n",
       "      <td>1</td>\n",
       "      <td>56.035552</td>\n",
       "      <td>92.930698</td>\n",
       "      <td>Республика Адыгея</td>\n",
       "      <td>Красноярск, улица Партизана Железняка, 35А</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>False</td>\n",
       "      <td>Росбанк</td>\n",
       "      <td>Новая Адыгея</td>\n",
       "      <td>44392</td>\n",
       "      <td>NaN</td>\n",
       "      <td>магазин \"Декатлон\"</td>\n",
       "      <td>1</td>\n",
       "      <td>45.013084</td>\n",
       "      <td>38.929328</td>\n",
       "      <td>Республика Адыгея</td>\n",
       "      <td>Республика Адыгея, Тахтамукайский район, посёл...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>False</td>\n",
       "      <td>Альфа-Банк</td>\n",
       "      <td>Новая Адыгея</td>\n",
       "      <td>81782</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>1</td>\n",
       "      <td>45.012358</td>\n",
       "      <td>38.928279</td>\n",
       "      <td>Республика Адыгея</td>\n",
       "      <td>Республика Адыгея, Тахтамукайский район, посёл...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>True</td>\n",
       "      <td>Альфа-Банк</td>\n",
       "      <td>Тахтамукай</td>\n",
       "      <td>83137</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>1</td>\n",
       "      <td>44.929061</td>\n",
       "      <td>38.980093</td>\n",
       "      <td>Республика Адыгея</td>\n",
       "      <td>Республика Адыгея, Тахтамукайский район, аул Т...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>False</td>\n",
       "      <td>Газпромбанк</td>\n",
       "      <td>Улан-Удэ</td>\n",
       "      <td>75759</td>\n",
       "      <td>NaN</td>\n",
       "      <td>САО \"ВСК\"</td>\n",
       "      <td>1</td>\n",
       "      <td>51.810559</td>\n",
       "      <td>107.607134</td>\n",
       "      <td>Республика Адыгея</td>\n",
       "      <td>Республика Бурятия, Улан-Удэ, Геологическая ул...</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "   access24h         bank          city     id metro  \\\n",
       "0      False      Росбанк    Красноярск  45351   NaN   \n",
       "1      False      Росбанк  Новая Адыгея  44392   NaN   \n",
       "2      False   Альфа-Банк  Новая Адыгея  81782   NaN   \n",
       "3       True   Альфа-Банк    Тахтамукай  83137   NaN   \n",
       "4      False  Газпромбанк      Улан-Удэ  75759   NaN   \n",
       "\n",
       "                                  name  region_rb        lat         lng  \\\n",
       "0  Бизнес Центр Сириус ХАБ ПАО РОСБАНК          1  56.035552   92.930698   \n",
       "1                   магазин \"Декатлон\"          1  45.013084   38.929328   \n",
       "2                                  NaN          1  45.012358   38.928279   \n",
       "3                                  NaN          1  44.929061   38.980093   \n",
       "4                            САО \"ВСК\"          1  51.810559  107.607134   \n",
       "\n",
       "         region_name                                            address  \n",
       "0  Республика Адыгея         Красноярск, улица Партизана Железняка, 35А  \n",
       "1  Республика Адыгея  Республика Адыгея, Тахтамукайский район, посёл...  \n",
       "2  Республика Адыгея  Республика Адыгея, Тахтамукайский район, посёл...  \n",
       "3  Республика Адыгея  Республика Адыгея, Тахтамукайский район, аул Т...  \n",
       "4  Республика Адыгея  Республика Бурятия, Улан-Удэ, Геологическая ул...  "
      ]
     },
     "execution_count": 15,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df_atm.drop(columns=['address'], inplace=True)\n",
    "df_atm = pd.merge(df_atm, df_atms_address, on='id')\n",
    "df_atm.head()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 16,
   "metadata": {},
   "outputs": [],
   "source": [
    "df_atm.to_csv('dataset_rosbank.csv', sep=',', index=False)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## Заполняем пустое поле name в дампе Росбанка"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 52,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>access24h</th>\n",
       "      <th>bank</th>\n",
       "      <th>city</th>\n",
       "      <th>id</th>\n",
       "      <th>metro</th>\n",
       "      <th>name</th>\n",
       "      <th>region_rb</th>\n",
       "      <th>lat</th>\n",
       "      <th>lng</th>\n",
       "      <th>region_name</th>\n",
       "      <th>address</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>False</td>\n",
       "      <td>Росбанк</td>\n",
       "      <td>Красноярск</td>\n",
       "      <td>45351</td>\n",
       "      <td>NaN</td>\n",
       "      <td>Бизнес Центр Сириус ХАБ ПАО РОСБАНК</td>\n",
       "      <td>1</td>\n",
       "      <td>56.035552</td>\n",
       "      <td>92.930698</td>\n",
       "      <td>Республика Адыгея</td>\n",
       "      <td>Красноярск, улица Партизана Железняка, 35А</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>False</td>\n",
       "      <td>Росбанк</td>\n",
       "      <td>Новая Адыгея</td>\n",
       "      <td>44392</td>\n",
       "      <td>NaN</td>\n",
       "      <td>магазин \"Декатлон\"</td>\n",
       "      <td>1</td>\n",
       "      <td>45.013084</td>\n",
       "      <td>38.929328</td>\n",
       "      <td>Республика Адыгея</td>\n",
       "      <td>Республика Адыгея, Тахтамукайский район, посёл...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>False</td>\n",
       "      <td>Альфа-Банк</td>\n",
       "      <td>Новая Адыгея</td>\n",
       "      <td>81782</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>1</td>\n",
       "      <td>45.012358</td>\n",
       "      <td>38.928279</td>\n",
       "      <td>Республика Адыгея</td>\n",
       "      <td>Республика Адыгея, Тахтамукайский район, посёл...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>True</td>\n",
       "      <td>Альфа-Банк</td>\n",
       "      <td>Тахтамукай</td>\n",
       "      <td>83137</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>1</td>\n",
       "      <td>44.929061</td>\n",
       "      <td>38.980093</td>\n",
       "      <td>Республика Адыгея</td>\n",
       "      <td>Республика Адыгея, Тахтамукайский район, аул Т...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>False</td>\n",
       "      <td>Газпромбанк</td>\n",
       "      <td>Улан-Удэ</td>\n",
       "      <td>75759</td>\n",
       "      <td>NaN</td>\n",
       "      <td>САО \"ВСК\"</td>\n",
       "      <td>1</td>\n",
       "      <td>51.810559</td>\n",
       "      <td>107.607134</td>\n",
       "      <td>Республика Адыгея</td>\n",
       "      <td>Республика Бурятия, Улан-Удэ, Геологическая ул...</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "   access24h         bank          city     id metro  \\\n",
       "0      False      Росбанк    Красноярск  45351   NaN   \n",
       "1      False      Росбанк  Новая Адыгея  44392   NaN   \n",
       "2      False   Альфа-Банк  Новая Адыгея  81782   NaN   \n",
       "3       True   Альфа-Банк    Тахтамукай  83137   NaN   \n",
       "4      False  Газпромбанк      Улан-Удэ  75759   NaN   \n",
       "\n",
       "                                  name  region_rb        lat         lng  \\\n",
       "0  Бизнес Центр Сириус ХАБ ПАО РОСБАНК          1  56.035552   92.930698   \n",
       "1                   магазин \"Декатлон\"          1  45.013084   38.929328   \n",
       "2                                  NaN          1  45.012358   38.928279   \n",
       "3                                  NaN          1  44.929061   38.980093   \n",
       "4                            САО \"ВСК\"          1  51.810559  107.607134   \n",
       "\n",
       "         region_name                                            address  \n",
       "0  Республика Адыгея         Красноярск, улица Партизана Железняка, 35А  \n",
       "1  Республика Адыгея  Республика Адыгея, Тахтамукайский район, посёл...  \n",
       "2  Республика Адыгея  Республика Адыгея, Тахтамукайский район, посёл...  \n",
       "3  Республика Адыгея  Республика Адыгея, Тахтамукайский район, аул Т...  \n",
       "4  Республика Адыгея  Республика Бурятия, Улан-Удэ, Геологическая ул...  "
      ]
     },
     "execution_count": 52,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df_atm = pd.read_csv('dataset_rosbank.csv', sep=',')\n",
    "df_atm['address'] = df_atm['address'].fillna('')\n",
    "df_atm.head()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 53,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "access24h      False\n",
       "bank           False\n",
       "city           False\n",
       "id             False\n",
       "metro           True\n",
       "name            True\n",
       "region_rb      False\n",
       "lat            False\n",
       "lng            False\n",
       "region_name    False\n",
       "address        False\n",
       "dtype: bool"
      ]
     },
     "execution_count": 53,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df_atm.isna().any(axis=0)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 54,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>bank</th>\n",
       "      <th>name</th>\n",
       "      <th>type</th>\n",
       "      <th>address</th>\n",
       "      <th>comment</th>\n",
       "      <th>lat</th>\n",
       "      <th>lon</th>\n",
       "      <th>access24h</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>Альфа-Банк</td>\n",
       "      <td>Банкомат</td>\n",
       "      <td>atm</td>\n",
       "      <td>Алтайский край, с. Алтайское</td>\n",
       "      <td>Алтай Пэлас</td>\n",
       "      <td>51.821321</td>\n",
       "      <td>85.665832</td>\n",
       "      <td>True</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>ВТБ</td>\n",
       "      <td>Банкомат</td>\n",
       "      <td>atm</td>\n",
       "      <td>Алтайский край, с. Алтайское, ул. Советское, д...</td>\n",
       "      <td>Магазин «Мария-Ра»</td>\n",
       "      <td>51.949234</td>\n",
       "      <td>85.337500</td>\n",
       "      <td>False</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>Ак Барс</td>\n",
       "      <td>Банкомат</td>\n",
       "      <td>atm</td>\n",
       "      <td>г. Барнаул, ул. Попова, д. 51</td>\n",
       "      <td>ОО «Барнаульский № 2»</td>\n",
       "      <td>53.367107</td>\n",
       "      <td>83.677421</td>\n",
       "      <td>True</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>5</th>\n",
       "      <td>Ак Барс</td>\n",
       "      <td>Банкоматы</td>\n",
       "      <td>atm</td>\n",
       "      <td>г. Барнаул, просп. Красноармейский, д. 75 Б</td>\n",
       "      <td>ОО «Барнаульский № 1»</td>\n",
       "      <td>53.339939</td>\n",
       "      <td>83.771164</td>\n",
       "      <td>True</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>6</th>\n",
       "      <td>Альфа-Банк</td>\n",
       "      <td>Банкомат</td>\n",
       "      <td>atm</td>\n",
       "      <td>г. Барнаул, Красноармейский пр-кт, 47А</td>\n",
       "      <td>ТРК «Сити-центр»</td>\n",
       "      <td>53.335253</td>\n",
       "      <td>83.775410</td>\n",
       "      <td>False</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "         bank       name type  \\\n",
       "0  Альфа-Банк   Банкомат  atm   \n",
       "1         ВТБ   Банкомат  atm   \n",
       "4     Ак Барс   Банкомат  atm   \n",
       "5     Ак Барс  Банкоматы  atm   \n",
       "6  Альфа-Банк   Банкомат  atm   \n",
       "\n",
       "                                             address                comment  \\\n",
       "0                       Алтайский край, с. Алтайское            Алтай Пэлас   \n",
       "1  Алтайский край, с. Алтайское, ул. Советское, д...     Магазин «Мария-Ра»   \n",
       "4                      г. Барнаул, ул. Попова, д. 51  ОО «Барнаульский № 2»   \n",
       "5        г. Барнаул, просп. Красноармейский, д. 75 Б  ОО «Барнаульский № 1»   \n",
       "6             г. Барнаул, Красноармейский пр-кт, 47А       ТРК «Сити-центр»   \n",
       "\n",
       "         lat        lon  access24h  \n",
       "0  51.821321  85.665832       True  \n",
       "1  51.949234  85.337500      False  \n",
       "4  53.367107  83.677421       True  \n",
       "5  53.339939  83.771164       True  \n",
       "6  53.335253  83.775410      False  "
      ]
     },
     "execution_count": 54,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df_banki = pd.read_csv('./base_bankiru.csv')\n",
    "df_banki = df_banki[df_banki[\"type\"] == \"atm\"]\n",
    "df_banki.dropna(subset=['lat', 'lon'], inplace=True)\n",
    "df_banki.head()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 55,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "(17791, 8)"
      ]
     },
     "execution_count": 55,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df_banki.shape"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 56,
   "metadata": {},
   "outputs": [],
   "source": [
    "from sklearn.metrics.pairwise import pairwise_distances\n",
    "from sklearn.neighbors import DistanceMetric\n",
    "\n",
    "import numpy as np"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 57,
   "metadata": {},
   "outputs": [],
   "source": [
    "from tqdm import tqdm"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 58,
   "metadata": {},
   "outputs": [
    {
     "name": "stderr",
     "output_type": "stream",
     "text": [
      "100%|██████████| 7903/7903 [00:24<00:00, 325.16it/s]\n"
     ]
    }
   ],
   "source": [
    "bank_index = []\n",
    "bank_dists = []\n",
    "\n",
    "R = 6373.0\n",
    "\n",
    "metric = DistanceMetric.get_metric('haversine')\n",
    "\n",
    "b = np.deg2rad(df_banki[[\"lat\", \"lon\"]]).values.reshape(-1, 2)\n",
    "a = np.deg2rad(df_atm[[\"lat\", \"lng\"]]).values.reshape(-1, 2)\n",
    "\n",
    "for i in tqdm(range(df_atm.shape[0])):\n",
    "    dists = metric.pairwise(a[i].reshape(-1, 2), b) * R\n",
    "    j = np.argsort(dists, axis=1)[0,0]\n",
    "    d = dists[0,j]\n",
    "    \n",
    "    if not d < 0.125:\n",
    "        j = None       \n",
    "        \n",
    "    bank_index.append(j)\n",
    "    bank_dists.append(d)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 59,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "1759"
      ]
     },
     "execution_count": 59,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "sum(map(lambda s: s is None, bank_index))"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 60,
   "metadata": {},
   "outputs": [],
   "source": [
    "for r in df_atm.itertuples():\n",
    "    if r.bank == 'Росбанк':\n",
    "        continue\n",
    "    \n",
    "    i = bank_index[r.Index]\n",
    "    if i is None:\n",
    "        continue\n",
    "    \n",
    "    df_atm['name'].at[r.Index] = df_banki[\"comment\"].iloc[i]"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 61,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "1318\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>access24h</th>\n",
       "      <th>bank</th>\n",
       "      <th>city</th>\n",
       "      <th>id</th>\n",
       "      <th>metro</th>\n",
       "      <th>name</th>\n",
       "      <th>region_rb</th>\n",
       "      <th>lat</th>\n",
       "      <th>lng</th>\n",
       "      <th>region_name</th>\n",
       "      <th>address</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>True</td>\n",
       "      <td>Альфа-Банк</td>\n",
       "      <td>Тахтамукай</td>\n",
       "      <td>83137</td>\n",
       "      <td>NaN</td>\n",
       "      <td></td>\n",
       "      <td>1</td>\n",
       "      <td>44.929061</td>\n",
       "      <td>38.980093</td>\n",
       "      <td>Республика Адыгея</td>\n",
       "      <td>Республика Адыгея, Тахтамукайский район, аул Т...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>8</th>\n",
       "      <td>False</td>\n",
       "      <td>Банк Уралсиб</td>\n",
       "      <td>Белорецк</td>\n",
       "      <td>61965</td>\n",
       "      <td>NaN</td>\n",
       "      <td></td>\n",
       "      <td>2</td>\n",
       "      <td>53.971667</td>\n",
       "      <td>58.402181</td>\n",
       "      <td>Республика Башкортостан</td>\n",
       "      <td>Республика Башкортостан, Белорецк, улица П. То...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>16</th>\n",
       "      <td>False</td>\n",
       "      <td>Банк Уралсиб</td>\n",
       "      <td>Белорецк</td>\n",
       "      <td>61317</td>\n",
       "      <td>NaN</td>\n",
       "      <td></td>\n",
       "      <td>2</td>\n",
       "      <td>53.959877</td>\n",
       "      <td>58.366401</td>\n",
       "      <td>Республика Башкортостан</td>\n",
       "      <td>Республика Башкортостан, Белорецк, улица В. Бл...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>22</th>\n",
       "      <td>False</td>\n",
       "      <td>Ак Барс</td>\n",
       "      <td>Вавилово</td>\n",
       "      <td>71290</td>\n",
       "      <td>NaN</td>\n",
       "      <td></td>\n",
       "      <td>2</td>\n",
       "      <td>54.811714</td>\n",
       "      <td>55.849997</td>\n",
       "      <td>Республика Башкортостан</td>\n",
       "      <td>Республика Башкортостан, Уфимский район, дерев...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>28</th>\n",
       "      <td>False</td>\n",
       "      <td>Банк Уралсиб</td>\n",
       "      <td>Давлеканово</td>\n",
       "      <td>61779</td>\n",
       "      <td>NaN</td>\n",
       "      <td></td>\n",
       "      <td>2</td>\n",
       "      <td>54.222727</td>\n",
       "      <td>55.031270</td>\n",
       "      <td>Республика Башкортостан</td>\n",
       "      <td>Республика Башкортостан, Давлеканово, улица 50...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>30</th>\n",
       "      <td>False</td>\n",
       "      <td>Банк Уралсиб</td>\n",
       "      <td>Давлеканово</td>\n",
       "      <td>61698</td>\n",
       "      <td>NaN</td>\n",
       "      <td></td>\n",
       "      <td>2</td>\n",
       "      <td>54.225354</td>\n",
       "      <td>55.033606</td>\n",
       "      <td>Республика Башкортостан</td>\n",
       "      <td>Республика Башкортостан, Давлеканово, Карански...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>31</th>\n",
       "      <td>False</td>\n",
       "      <td>Банк Уралсиб</td>\n",
       "      <td>Давлеканово</td>\n",
       "      <td>61347</td>\n",
       "      <td>NaN</td>\n",
       "      <td></td>\n",
       "      <td>2</td>\n",
       "      <td>54.213077</td>\n",
       "      <td>55.005354</td>\n",
       "      <td>Республика Башкортостан</td>\n",
       "      <td>Республика Башкортостан, Давлеканово, улица Ал...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>33</th>\n",
       "      <td>False</td>\n",
       "      <td>Ак Барс</td>\n",
       "      <td>Камское Устье</td>\n",
       "      <td>71329</td>\n",
       "      <td>NaN</td>\n",
       "      <td></td>\n",
       "      <td>2</td>\n",
       "      <td>55.201560</td>\n",
       "      <td>55.201560</td>\n",
       "      <td>Республика Башкортостан</td>\n",
       "      <td>Республика Башкортостан, Кушнаренковский район</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>39</th>\n",
       "      <td>False</td>\n",
       "      <td>Банк Уралсиб</td>\n",
       "      <td>Нефтекамск</td>\n",
       "      <td>61956</td>\n",
       "      <td>NaN</td>\n",
       "      <td></td>\n",
       "      <td>2</td>\n",
       "      <td>56.106188</td>\n",
       "      <td>54.220379</td>\n",
       "      <td>Республика Башкортостан</td>\n",
       "      <td>Республика Башкортостан, Нефтекамск, улица Дзе...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>58</th>\n",
       "      <td>False</td>\n",
       "      <td>Альфа-Банк</td>\n",
       "      <td>Октябрьский</td>\n",
       "      <td>81506</td>\n",
       "      <td>NaN</td>\n",
       "      <td></td>\n",
       "      <td>2</td>\n",
       "      <td>58.082799</td>\n",
       "      <td>52.865862</td>\n",
       "      <td>Республика Башкортостан</td>\n",
       "      <td>Удмуртская Республика, Глазовский район, село ...</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "    access24h          bank           city     id metro name  region_rb  \\\n",
       "3        True    Альфа-Банк     Тахтамукай  83137   NaN               1   \n",
       "8       False  Банк Уралсиб       Белорецк  61965   NaN               2   \n",
       "16      False  Банк Уралсиб       Белорецк  61317   NaN               2   \n",
       "22      False       Ак Барс       Вавилово  71290   NaN               2   \n",
       "28      False  Банк Уралсиб    Давлеканово  61779   NaN               2   \n",
       "30      False  Банк Уралсиб    Давлеканово  61698   NaN               2   \n",
       "31      False  Банк Уралсиб    Давлеканово  61347   NaN               2   \n",
       "33      False       Ак Барс  Камское Устье  71329   NaN               2   \n",
       "39      False  Банк Уралсиб     Нефтекамск  61956   NaN               2   \n",
       "58      False    Альфа-Банк    Октябрьский  81506   NaN               2   \n",
       "\n",
       "          lat        lng              region_name  \\\n",
       "3   44.929061  38.980093        Республика Адыгея   \n",
       "8   53.971667  58.402181  Республика Башкортостан   \n",
       "16  53.959877  58.366401  Республика Башкортостан   \n",
       "22  54.811714  55.849997  Республика Башкортостан   \n",
       "28  54.222727  55.031270  Республика Башкортостан   \n",
       "30  54.225354  55.033606  Республика Башкортостан   \n",
       "31  54.213077  55.005354  Республика Башкортостан   \n",
       "33  55.201560  55.201560  Республика Башкортостан   \n",
       "39  56.106188  54.220379  Республика Башкортостан   \n",
       "58  58.082799  52.865862  Республика Башкортостан   \n",
       "\n",
       "                                              address  \n",
       "3   Республика Адыгея, Тахтамукайский район, аул Т...  \n",
       "8   Республика Башкортостан, Белорецк, улица П. То...  \n",
       "16  Республика Башкортостан, Белорецк, улица В. Бл...  \n",
       "22  Республика Башкортостан, Уфимский район, дерев...  \n",
       "28  Республика Башкортостан, Давлеканово, улица 50...  \n",
       "30  Республика Башкортостан, Давлеканово, Карански...  \n",
       "31  Республика Башкортостан, Давлеканово, улица Ал...  \n",
       "33     Республика Башкортостан, Кушнаренковский район  \n",
       "39  Республика Башкортостан, Нефтекамск, улица Дзе...  \n",
       "58  Удмуртская Республика, Глазовский район, село ...  "
      ]
     },
     "execution_count": 61,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df_atm['name'] = df_atm['name'].fillna('')\n",
    "\n",
    "mask = df_atm['name'] == ''\n",
    "print(mask.sum())\n",
    "df_atm[mask].head(10)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 62,
   "metadata": {},
   "outputs": [],
   "source": [
    "df_atm.rename(columns={'name': 'comment'}, inplace=True)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## Объединяем два датасета в один"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 63,
   "metadata": {},
   "outputs": [],
   "source": [
    "from collections import Counter"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 76,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>bank</th>\n",
       "      <th>address</th>\n",
       "      <th>comment</th>\n",
       "      <th>lat</th>\n",
       "      <th>lng</th>\n",
       "      <th>metro</th>\n",
       "      <th>access24h</th>\n",
       "      <th>id</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>Росбанк</td>\n",
       "      <td>Красноярск, улица Партизана Железняка, 35А</td>\n",
       "      <td>Бизнес Центр Сириус ХАБ ПАО РОСБАНК</td>\n",
       "      <td>56.035552</td>\n",
       "      <td>92.930698</td>\n",
       "      <td>NaN</td>\n",
       "      <td>False</td>\n",
       "      <td>45351.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>Росбанк</td>\n",
       "      <td>Республика Адыгея, Тахтамукайский район, посёл...</td>\n",
       "      <td>магазин \"Декатлон\"</td>\n",
       "      <td>45.013084</td>\n",
       "      <td>38.929328</td>\n",
       "      <td>NaN</td>\n",
       "      <td>False</td>\n",
       "      <td>44392.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>Альфа-Банк</td>\n",
       "      <td>Республика Адыгея, Тахтамукайский район, посёл...</td>\n",
       "      <td>ТЦ «Мега Адыгея»</td>\n",
       "      <td>45.012358</td>\n",
       "      <td>38.928279</td>\n",
       "      <td>NaN</td>\n",
       "      <td>False</td>\n",
       "      <td>81782.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>Альфа-Банк</td>\n",
       "      <td>Республика Адыгея, Тахтамукайский район, аул Т...</td>\n",
       "      <td></td>\n",
       "      <td>44.929061</td>\n",
       "      <td>38.980093</td>\n",
       "      <td>NaN</td>\n",
       "      <td>True</td>\n",
       "      <td>83137.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>Газпромбанк</td>\n",
       "      <td>Республика Бурятия, Улан-Удэ, Геологическая ул...</td>\n",
       "      <td>САО «ВСК»</td>\n",
       "      <td>51.810559</td>\n",
       "      <td>107.607134</td>\n",
       "      <td>NaN</td>\n",
       "      <td>False</td>\n",
       "      <td>75759.0</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "          bank                                            address  \\\n",
       "0      Росбанк         Красноярск, улица Партизана Железняка, 35А   \n",
       "1      Росбанк  Республика Адыгея, Тахтамукайский район, посёл...   \n",
       "2   Альфа-Банк  Республика Адыгея, Тахтамукайский район, посёл...   \n",
       "3   Альфа-Банк  Республика Адыгея, Тахтамукайский район, аул Т...   \n",
       "4  Газпромбанк  Республика Бурятия, Улан-Удэ, Геологическая ул...   \n",
       "\n",
       "                               comment        lat         lng metro  \\\n",
       "0  Бизнес Центр Сириус ХАБ ПАО РОСБАНК  56.035552   92.930698   NaN   \n",
       "1                   магазин \"Декатлон\"  45.013084   38.929328   NaN   \n",
       "2                     ТЦ «Мега Адыгея»  45.012358   38.928279   NaN   \n",
       "3                                       44.929061   38.980093   NaN   \n",
       "4                            САО «ВСК»  51.810559  107.607134   NaN   \n",
       "\n",
       "   access24h       id  \n",
       "0      False  45351.0  \n",
       "1      False  44392.0  \n",
       "2      False  81782.0  \n",
       "3       True  83137.0  \n",
       "4      False  75759.0  "
      ]
     },
     "execution_count": 76,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df_comment = pd.concat([\n",
    "    df_atm[['bank', 'address', 'comment', 'lat', 'lng', 'metro', 'access24h', 'id']],\n",
    "    df_banki[['bank', 'address', 'comment', 'lat', 'lon', 'access24h']]\\\n",
    "        .rename(columns={'lon': 'lng'})\n",
    "], sort=False)\n",
    "df_comment.head()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 77,
   "metadata": {},
   "outputs": [],
   "source": [
    "df_comment.to_csv('base_banks_join.csv', index=False, sep=',')"
   ]
  },
  {
   "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.8"
  }
 },
 "nbformat": 4,
 "nbformat_minor": 2
}