generate_geo_features.ipynb

{
 "cells": [
  {
   "cell_type": "code",
   "execution_count": 1,
   "metadata": {},
   "outputs": [],
   "source": [
    "import pandas as pd\n",
    "import numpy as np\n",
    "from sklearn.neighbors import BallTree\n",
    "from tqdm import tqdm"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 2,
   "metadata": {},
   "outputs": [],
   "source": [
    "train_geo = pd.read_csv('train_geo.csv')\n",
    "train_geo = train_geo[train_geo.label != 0].reset_index(drop=True)\n",
    "test_geo = pd.read_csv('test_geo.csv')"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "# train"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 3,
   "metadata": {},
   "outputs": [],
   "source": [
    "tree = BallTree(train_geo[['x', 'y']], leaf_size=2) "
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 4,
   "metadata": {},
   "outputs": [],
   "source": [
    "dist, ind = tree.query(train_geo[['x', 'y']], k=51)\n",
    "dist, ind = dist[:, 1:], ind[:, 1:]\n",
    "dist *= 107\n",
    "# 107 - constant for conversion to kilometers"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 5,
   "metadata": {},
   "outputs": [
    {
     "name": "stderr",
     "output_type": "stream",
     "text": [
      "100%|█████████████████████████████████| 87112/87112 [00:00<00:00, 112788.09it/s]\n"
     ]
    }
   ],
   "source": [
    "dists = []\n",
    "\n",
    "for i in tqdm(range(train_geo.shape[0])):\n",
    "    dist_temp_res = np.zeros((4))\n",
    "    temp_dist = dist[i]\n",
    "    for i in range(1, 5):\n",
    "        dist_temp_res[i-1] += len(temp_dist[temp_dist < i])\n",
    "    dists.append(dist_temp_res)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 6,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "array([ 5., 14., 26., 34.])"
      ]
     },
     "execution_count": 6,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "dists[5]"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 9,
   "metadata": {},
   "outputs": [
    {
     "name": "stderr",
     "output_type": "stream",
     "text": [
      "100%|██████████████████████████████████| 87112/87112 [00:07<00:00, 11540.55it/s]\n"
     ]
    }
   ],
   "source": [
    "labs = []\n",
    "\n",
    "for i in tqdm(range(train_geo.shape[0])):\n",
    "    labs.append(train_geo.iloc[ind[i]].label.values[:10])"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 10,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "array([5, 5, 5, 5, 5, 5, 3, 3, 5, 5])"
      ]
     },
     "execution_count": 10,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "labs[5]"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 11,
   "metadata": {},
   "outputs": [
    {
     "name": "stderr",
     "output_type": "stream",
     "text": [
      "100%|███████████████████████████████████| 87112/87112 [00:09<00:00, 9479.18it/s]\n"
     ]
    }
   ],
   "source": [
    "labs_count = []\n",
    "\n",
    "for i in tqdm(range(train_geo.shape[0])):\n",
    "    lc_temp = np.zeros((9))\n",
    "    temp_dist = dist[i]\n",
    "    temp_labs = train_geo.iloc[ind[i]].label.values[:10]\n",
    "    for i in range(1, 10):\n",
    "        lc_temp[i-1] += len(temp_labs[temp_labs == i])\n",
    "    labs_count.append(lc_temp)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 12,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "array([0., 0., 2., 0., 8., 0., 0., 0., 0.])"
      ]
     },
     "execution_count": 12,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "labs_count[5]"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 13,
   "metadata": {},
   "outputs": [],
   "source": [
    "train_nearest_dist = pd.DataFrame(dists, columns=['dist_{}'.format(x) for x in range(1, 5)])\n",
    "train_nearest = pd.DataFrame(labs, columns=['nearest_{}'.format(x) for x in range(1, 11)], dtype=int)\n",
    "train_nearest_count = pd.DataFrame(labs_count, columns=['nearest_{}_count'.format(x) for x in range(1, 10)])\n",
    "train_nearest_count['most_frequent'] = train_nearest_count.apply(lambda x: 1+np.where(x == x.max())[0][0], axis=1)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 14,
   "metadata": {},
   "outputs": [],
   "source": [
    "train_result = pd.concat([train_geo, train_nearest, train_nearest_count, train_nearest_dist], axis=1)\n",
    "train_result = train_result[train_result.columns[1:]]"
   ]
  },
  {
   "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>field_id</th>\n",
       "      <th>label</th>\n",
       "      <th>square</th>\n",
       "      <th>x</th>\n",
       "      <th>y</th>\n",
       "      <th>nearest_1</th>\n",
       "      <th>nearest_2</th>\n",
       "      <th>nearest_3</th>\n",
       "      <th>nearest_4</th>\n",
       "      <th>nearest_5</th>\n",
       "      <th>...</th>\n",
       "      <th>nearest_5_count</th>\n",
       "      <th>nearest_6_count</th>\n",
       "      <th>nearest_7_count</th>\n",
       "      <th>nearest_8_count</th>\n",
       "      <th>nearest_9_count</th>\n",
       "      <th>most_frequent</th>\n",
       "      <th>dist_1</th>\n",
       "      <th>dist_2</th>\n",
       "      <th>dist_3</th>\n",
       "      <th>dist_4</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>43134</td>\n",
       "      <td>5</td>\n",
       "      <td>11018</td>\n",
       "      <td>-31.781216</td>\n",
       "      <td>18.337039</td>\n",
       "      <td>5</td>\n",
       "      <td>5</td>\n",
       "      <td>5</td>\n",
       "      <td>3</td>\n",
       "      <td>5</td>\n",
       "      <td>...</td>\n",
       "      <td>6.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>5</td>\n",
       "      <td>3.0</td>\n",
       "      <td>16.0</td>\n",
       "      <td>32.0</td>\n",
       "      <td>34.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>67175</td>\n",
       "      <td>6</td>\n",
       "      <td>25</td>\n",
       "      <td>-31.771337</td>\n",
       "      <td>18.350873</td>\n",
       "      <td>6</td>\n",
       "      <td>2</td>\n",
       "      <td>2</td>\n",
       "      <td>6</td>\n",
       "      <td>5</td>\n",
       "      <td>...</td>\n",
       "      <td>2.0</td>\n",
       "      <td>4.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>6</td>\n",
       "      <td>8.0</td>\n",
       "      <td>16.0</td>\n",
       "      <td>24.0</td>\n",
       "      <td>37.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>75612</td>\n",
       "      <td>3</td>\n",
       "      <td>13798</td>\n",
       "      <td>-31.775677</td>\n",
       "      <td>18.328824</td>\n",
       "      <td>3</td>\n",
       "      <td>3</td>\n",
       "      <td>5</td>\n",
       "      <td>3</td>\n",
       "      <td>3</td>\n",
       "      <td>...</td>\n",
       "      <td>5.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>3</td>\n",
       "      <td>1.0</td>\n",
       "      <td>11.0</td>\n",
       "      <td>29.0</td>\n",
       "      <td>33.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>84337</td>\n",
       "      <td>3</td>\n",
       "      <td>1545</td>\n",
       "      <td>-31.766074</td>\n",
       "      <td>18.339741</td>\n",
       "      <td>6</td>\n",
       "      <td>5</td>\n",
       "      <td>1</td>\n",
       "      <td>5</td>\n",
       "      <td>6</td>\n",
       "      <td>...</td>\n",
       "      <td>2.0</td>\n",
       "      <td>5.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>6</td>\n",
       "      <td>5.0</td>\n",
       "      <td>17.0</td>\n",
       "      <td>28.0</td>\n",
       "      <td>33.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>88832</td>\n",
       "      <td>5</td>\n",
       "      <td>1980</td>\n",
       "      <td>-31.766351</td>\n",
       "      <td>18.346550</td>\n",
       "      <td>5</td>\n",
       "      <td>2</td>\n",
       "      <td>1</td>\n",
       "      <td>6</td>\n",
       "      <td>6</td>\n",
       "      <td>...</td>\n",
       "      <td>1.0</td>\n",
       "      <td>4.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>6</td>\n",
       "      <td>12.0</td>\n",
       "      <td>17.0</td>\n",
       "      <td>24.0</td>\n",
       "      <td>35.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>...</th>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>87107</th>\n",
       "      <td>56334</td>\n",
       "      <td>2</td>\n",
       "      <td>138</td>\n",
       "      <td>-33.235454</td>\n",
       "      <td>19.239919</td>\n",
       "      <td>2</td>\n",
       "      <td>2</td>\n",
       "      <td>2</td>\n",
       "      <td>2</td>\n",
       "      <td>2</td>\n",
       "      <td>...</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>2</td>\n",
       "      <td>26.0</td>\n",
       "      <td>48.0</td>\n",
       "      <td>50.0</td>\n",
       "      <td>50.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>87108</th>\n",
       "      <td>86618</td>\n",
       "      <td>5</td>\n",
       "      <td>93</td>\n",
       "      <td>-33.242147</td>\n",
       "      <td>19.232390</td>\n",
       "      <td>5</td>\n",
       "      <td>2</td>\n",
       "      <td>2</td>\n",
       "      <td>2</td>\n",
       "      <td>2</td>\n",
       "      <td>...</td>\n",
       "      <td>1.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>2</td>\n",
       "      <td>10.0</td>\n",
       "      <td>31.0</td>\n",
       "      <td>50.0</td>\n",
       "      <td>50.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>87109</th>\n",
       "      <td>87401</td>\n",
       "      <td>2</td>\n",
       "      <td>30</td>\n",
       "      <td>-33.240038</td>\n",
       "      <td>19.241228</td>\n",
       "      <td>2</td>\n",
       "      <td>2</td>\n",
       "      <td>2</td>\n",
       "      <td>2</td>\n",
       "      <td>2</td>\n",
       "      <td>...</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>2</td>\n",
       "      <td>26.0</td>\n",
       "      <td>50.0</td>\n",
       "      <td>50.0</td>\n",
       "      <td>50.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>87110</th>\n",
       "      <td>98071</td>\n",
       "      <td>2</td>\n",
       "      <td>114</td>\n",
       "      <td>-33.241047</td>\n",
       "      <td>19.236755</td>\n",
       "      <td>2</td>\n",
       "      <td>2</td>\n",
       "      <td>2</td>\n",
       "      <td>2</td>\n",
       "      <td>5</td>\n",
       "      <td>...</td>\n",
       "      <td>2.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>2</td>\n",
       "      <td>22.0</td>\n",
       "      <td>41.0</td>\n",
       "      <td>50.0</td>\n",
       "      <td>50.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>87111</th>\n",
       "      <td>112672</td>\n",
       "      <td>5</td>\n",
       "      <td>63</td>\n",
       "      <td>-33.238571</td>\n",
       "      <td>19.223333</td>\n",
       "      <td>5</td>\n",
       "      <td>5</td>\n",
       "      <td>2</td>\n",
       "      <td>2</td>\n",
       "      <td>2</td>\n",
       "      <td>...</td>\n",
       "      <td>2.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>2</td>\n",
       "      <td>0.0</td>\n",
       "      <td>16.0</td>\n",
       "      <td>38.0</td>\n",
       "      <td>50.0</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "<p>87112 rows × 29 columns</p>\n",
       "</div>"
      ],
      "text/plain": [
       "       field_id  label  square          x          y  nearest_1  nearest_2  \\\n",
       "0         43134      5   11018 -31.781216  18.337039          5          5   \n",
       "1         67175      6      25 -31.771337  18.350873          6          2   \n",
       "2         75612      3   13798 -31.775677  18.328824          3          3   \n",
       "3         84337      3    1545 -31.766074  18.339741          6          5   \n",
       "4         88832      5    1980 -31.766351  18.346550          5          2   \n",
       "...         ...    ...     ...        ...        ...        ...        ...   \n",
       "87107     56334      2     138 -33.235454  19.239919          2          2   \n",
       "87108     86618      5      93 -33.242147  19.232390          5          2   \n",
       "87109     87401      2      30 -33.240038  19.241228          2          2   \n",
       "87110     98071      2     114 -33.241047  19.236755          2          2   \n",
       "87111    112672      5      63 -33.238571  19.223333          5          5   \n",
       "\n",
       "       nearest_3  nearest_4  nearest_5  ...  nearest_5_count  nearest_6_count  \\\n",
       "0              5          3          5  ...              6.0              0.0   \n",
       "1              2          6          5  ...              2.0              4.0   \n",
       "2              5          3          3  ...              5.0              0.0   \n",
       "3              1          5          6  ...              2.0              5.0   \n",
       "4              1          6          6  ...              1.0              4.0   \n",
       "...          ...        ...        ...  ...              ...              ...   \n",
       "87107          2          2          2  ...              0.0              0.0   \n",
       "87108          2          2          2  ...              1.0              0.0   \n",
       "87109          2          2          2  ...              0.0              0.0   \n",
       "87110          2          2          5  ...              2.0              0.0   \n",
       "87111          2          2          2  ...              2.0              0.0   \n",
       "\n",
       "       nearest_7_count  nearest_8_count  nearest_9_count  most_frequent  \\\n",
       "0                  0.0              0.0              0.0              5   \n",
       "1                  0.0              0.0              0.0              6   \n",
       "2                  0.0              0.0              0.0              3   \n",
       "3                  0.0              0.0              0.0              6   \n",
       "4                  0.0              0.0              0.0              6   \n",
       "...                ...              ...              ...            ...   \n",
       "87107              0.0              0.0              0.0              2   \n",
       "87108              0.0              0.0              0.0              2   \n",
       "87109              0.0              0.0              0.0              2   \n",
       "87110              0.0              0.0              0.0              2   \n",
       "87111              0.0              0.0              0.0              2   \n",
       "\n",
       "       dist_1  dist_2  dist_3  dist_4  \n",
       "0         3.0    16.0    32.0    34.0  \n",
       "1         8.0    16.0    24.0    37.0  \n",
       "2         1.0    11.0    29.0    33.0  \n",
       "3         5.0    17.0    28.0    33.0  \n",
       "4        12.0    17.0    24.0    35.0  \n",
       "...       ...     ...     ...     ...  \n",
       "87107    26.0    48.0    50.0    50.0  \n",
       "87108    10.0    31.0    50.0    50.0  \n",
       "87109    26.0    50.0    50.0    50.0  \n",
       "87110    22.0    41.0    50.0    50.0  \n",
       "87111     0.0    16.0    38.0    50.0  \n",
       "\n",
       "[87112 rows x 29 columns]"
      ]
     },
     "execution_count": 15,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "train_result"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 196,
   "metadata": {},
   "outputs": [],
   "source": [
    "train_result.to_csv('data/train_geo_2.csv', index=False)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "# test"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 16,
   "metadata": {},
   "outputs": [],
   "source": [
    "tree = BallTree(train_geo[['x', 'y']], leaf_size=2)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 19,
   "metadata": {},
   "outputs": [],
   "source": [
    "dist, ind = tree.query(test_geo[['x', 'y']], k=50)\n",
    "dist *= 107\n",
    "# 107 - constant for conversion to kilometers"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 20,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "(35295, 50)"
      ]
     },
     "execution_count": 20,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "ind.shape"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 21,
   "metadata": {},
   "outputs": [
    {
     "name": "stderr",
     "output_type": "stream",
     "text": [
      "100%|█████████████████████████████████| 35295/35295 [00:00<00:00, 106117.81it/s]\n"
     ]
    }
   ],
   "source": [
    "dists = []\n",
    "\n",
    "for i in tqdm(range(test_geo.shape[0])):\n",
    "    dist_temp_res = np.zeros((4))\n",
    "    temp_dist = dist[i]\n",
    "    k = 0\n",
    "    for i in range(1, 5):\n",
    "        dist_temp_res[k] += len(temp_dist[temp_dist < i])\n",
    "        k += 1\n",
    "    dists.append(dist_temp_res)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 22,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "array([ 0.,  0.,  7., 50.])"
      ]
     },
     "execution_count": 22,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "dists[5000]"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 23,
   "metadata": {},
   "outputs": [
    {
     "name": "stderr",
     "output_type": "stream",
     "text": [
      "100%|██████████████████████████████████| 35295/35295 [00:02<00:00, 11777.00it/s]\n"
     ]
    }
   ],
   "source": [
    "labs = []\n",
    "\n",
    "for i in tqdm(range(test_geo.shape[0])):\n",
    "    labs.append(train_geo.iloc[ind[i]].label.values[:10])"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 24,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "array([7, 2, 7, 7, 7, 7, 7, 7, 7, 7])"
      ]
     },
     "execution_count": 24,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "labs[5000]"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 25,
   "metadata": {},
   "outputs": [
    {
     "name": "stderr",
     "output_type": "stream",
     "text": [
      "100%|███████████████████████████████████| 35295/35295 [00:03<00:00, 9516.70it/s]\n"
     ]
    }
   ],
   "source": [
    "labs_count = []\n",
    "\n",
    "for i in tqdm(range(test_geo.shape[0])):\n",
    "    lc_temp = np.zeros((9))\n",
    "    temp_labs = train_geo.iloc[ind[i]].label.values[:10]\n",
    "    for i in range(1, 10):\n",
    "        lc_temp[i-1] += len(temp_labs[temp_labs == i])\n",
    "    labs_count.append(lc_temp)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 26,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "array([0., 1., 0., 0., 0., 0., 9., 0., 0.])"
      ]
     },
     "execution_count": 26,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "labs_count[5000]"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 27,
   "metadata": {},
   "outputs": [],
   "source": [
    "test_nearest_dist = pd.DataFrame(dists, columns=['dist_{}'.format(x) for x in range(1, 5)])\n",
    "test_nearest = pd.DataFrame(labs, columns=['nearest_{}'.format(x) for x in range(1, 11)], dtype=int)\n",
    "test_nearest_count = pd.DataFrame(labs_count, columns=['nearest_{}_count'.format(x) for x in range(1, 10)])\n",
    "test_nearest_count['most_frequent'] = test_nearest_count.apply(lambda x: 1+np.where(x == x.max())[0][0], axis=1)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 28,
   "metadata": {},
   "outputs": [],
   "source": [
    "test_result = pd.concat([test_geo, test_nearest, test_nearest_count, test_nearest_dist], axis=1)\n",
    "test_result = test_result[test_result.columns[1:]]"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 195,
   "metadata": {},
   "outputs": [],
   "source": [
    "test_result.to_csv('data/test_geo_2.csv', index=False)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 29,
   "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>field_id</th>\n",
       "      <th>square</th>\n",
       "      <th>x</th>\n",
       "      <th>y</th>\n",
       "      <th>nearest_1</th>\n",
       "      <th>nearest_2</th>\n",
       "      <th>nearest_3</th>\n",
       "      <th>nearest_4</th>\n",
       "      <th>nearest_5</th>\n",
       "      <th>nearest_6</th>\n",
       "      <th>...</th>\n",
       "      <th>nearest_5_count</th>\n",
       "      <th>nearest_6_count</th>\n",
       "      <th>nearest_7_count</th>\n",
       "      <th>nearest_8_count</th>\n",
       "      <th>nearest_9_count</th>\n",
       "      <th>most_frequent</th>\n",
       "      <th>dist_1</th>\n",
       "      <th>dist_2</th>\n",
       "      <th>dist_3</th>\n",
       "      <th>dist_4</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>2199</td>\n",
       "      <td>398</td>\n",
       "      <td>-33.580269</td>\n",
       "      <td>19.194868</td>\n",
       "      <td>4</td>\n",
       "      <td>4</td>\n",
       "      <td>4</td>\n",
       "      <td>4</td>\n",
       "      <td>4</td>\n",
       "      <td>4</td>\n",
       "      <td>...</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>4</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>30.0</td>\n",
       "      <td>50.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>2277</td>\n",
       "      <td>213</td>\n",
       "      <td>-33.573848</td>\n",
       "      <td>19.200675</td>\n",
       "      <td>4</td>\n",
       "      <td>4</td>\n",
       "      <td>4</td>\n",
       "      <td>4</td>\n",
       "      <td>4</td>\n",
       "      <td>4</td>\n",
       "      <td>...</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>4</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>50.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>2571</td>\n",
       "      <td>181</td>\n",
       "      <td>-33.573573</td>\n",
       "      <td>19.204510</td>\n",
       "      <td>4</td>\n",
       "      <td>4</td>\n",
       "      <td>4</td>\n",
       "      <td>4</td>\n",
       "      <td>4</td>\n",
       "      <td>4</td>\n",
       "      <td>...</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>4</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>50.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>3112</td>\n",
       "      <td>120</td>\n",
       "      <td>-33.561649</td>\n",
       "      <td>19.207030</td>\n",
       "      <td>4</td>\n",
       "      <td>4</td>\n",
       "      <td>4</td>\n",
       "      <td>4</td>\n",
       "      <td>4</td>\n",
       "      <td>4</td>\n",
       "      <td>...</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>4</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>7.0</td>\n",
       "      <td>50.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>7476</td>\n",
       "      <td>275</td>\n",
       "      <td>-33.575499</td>\n",
       "      <td>19.204510</td>\n",
       "      <td>4</td>\n",
       "      <td>4</td>\n",
       "      <td>4</td>\n",
       "      <td>4</td>\n",
       "      <td>4</td>\n",
       "      <td>4</td>\n",
       "      <td>...</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>4</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>50.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>...</th>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>35290</th>\n",
       "      <td>100931</td>\n",
       "      <td>2053</td>\n",
       "      <td>-32.702577</td>\n",
       "      <td>18.649715</td>\n",
       "      <td>9</td>\n",
       "      <td>1</td>\n",
       "      <td>9</td>\n",
       "      <td>9</td>\n",
       "      <td>9</td>\n",
       "      <td>9</td>\n",
       "      <td>...</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>1.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>8.0</td>\n",
       "      <td>9</td>\n",
       "      <td>0.0</td>\n",
       "      <td>11.0</td>\n",
       "      <td>32.0</td>\n",
       "      <td>50.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>35291</th>\n",
       "      <td>105031</td>\n",
       "      <td>674</td>\n",
       "      <td>-32.701195</td>\n",
       "      <td>18.645137</td>\n",
       "      <td>9</td>\n",
       "      <td>1</td>\n",
       "      <td>9</td>\n",
       "      <td>9</td>\n",
       "      <td>9</td>\n",
       "      <td>9</td>\n",
       "      <td>...</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>1.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>8.0</td>\n",
       "      <td>9</td>\n",
       "      <td>2.0</td>\n",
       "      <td>19.0</td>\n",
       "      <td>40.0</td>\n",
       "      <td>50.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>35292</th>\n",
       "      <td>108337</td>\n",
       "      <td>4291</td>\n",
       "      <td>-32.705894</td>\n",
       "      <td>18.640669</td>\n",
       "      <td>9</td>\n",
       "      <td>9</td>\n",
       "      <td>1</td>\n",
       "      <td>7</td>\n",
       "      <td>9</td>\n",
       "      <td>9</td>\n",
       "      <td>...</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>3.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>6.0</td>\n",
       "      <td>9</td>\n",
       "      <td>0.0</td>\n",
       "      <td>15.0</td>\n",
       "      <td>37.0</td>\n",
       "      <td>50.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>35293</th>\n",
       "      <td>112109</td>\n",
       "      <td>18</td>\n",
       "      <td>-32.693823</td>\n",
       "      <td>18.634784</td>\n",
       "      <td>7</td>\n",
       "      <td>9</td>\n",
       "      <td>7</td>\n",
       "      <td>9</td>\n",
       "      <td>7</td>\n",
       "      <td>9</td>\n",
       "      <td>...</td>\n",
       "      <td>0.0</td>\n",
       "      <td>1.0</td>\n",
       "      <td>4.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>5.0</td>\n",
       "      <td>9</td>\n",
       "      <td>19.0</td>\n",
       "      <td>44.0</td>\n",
       "      <td>50.0</td>\n",
       "      <td>50.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>35294</th>\n",
       "      <td>116145</td>\n",
       "      <td>30</td>\n",
       "      <td>-32.707460</td>\n",
       "      <td>18.651240</td>\n",
       "      <td>9</td>\n",
       "      <td>1</td>\n",
       "      <td>9</td>\n",
       "      <td>9</td>\n",
       "      <td>9</td>\n",
       "      <td>9</td>\n",
       "      <td>...</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>1.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>8.0</td>\n",
       "      <td>9</td>\n",
       "      <td>0.0</td>\n",
       "      <td>3.0</td>\n",
       "      <td>21.0</td>\n",
       "      <td>50.0</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "<p>35295 rows × 28 columns</p>\n",
       "</div>"
      ],
      "text/plain": [
       "       field_id  square          x          y  nearest_1  nearest_2  \\\n",
       "0          2199     398 -33.580269  19.194868          4          4   \n",
       "1          2277     213 -33.573848  19.200675          4          4   \n",
       "2          2571     181 -33.573573  19.204510          4          4   \n",
       "3          3112     120 -33.561649  19.207030          4          4   \n",
       "4          7476     275 -33.575499  19.204510          4          4   \n",
       "...         ...     ...        ...        ...        ...        ...   \n",
       "35290    100931    2053 -32.702577  18.649715          9          1   \n",
       "35291    105031     674 -32.701195  18.645137          9          1   \n",
       "35292    108337    4291 -32.705894  18.640669          9          9   \n",
       "35293    112109      18 -32.693823  18.634784          7          9   \n",
       "35294    116145      30 -32.707460  18.651240          9          1   \n",
       "\n",
       "       nearest_3  nearest_4  nearest_5  nearest_6  ...  nearest_5_count  \\\n",
       "0              4          4          4          4  ...              0.0   \n",
       "1              4          4          4          4  ...              0.0   \n",
       "2              4          4          4          4  ...              0.0   \n",
       "3              4          4          4          4  ...              0.0   \n",
       "4              4          4          4          4  ...              0.0   \n",
       "...          ...        ...        ...        ...  ...              ...   \n",
       "35290          9          9          9          9  ...              0.0   \n",
       "35291          9          9          9          9  ...              0.0   \n",
       "35292          1          7          9          9  ...              0.0   \n",
       "35293          7          9          7          9  ...              0.0   \n",
       "35294          9          9          9          9  ...              0.0   \n",
       "\n",
       "       nearest_6_count  nearest_7_count  nearest_8_count  nearest_9_count  \\\n",
       "0                  0.0              0.0              0.0              0.0   \n",
       "1                  0.0              0.0              0.0              0.0   \n",
       "2                  0.0              0.0              0.0              0.0   \n",
       "3                  0.0              0.0              0.0              0.0   \n",
       "4                  0.0              0.0              0.0              0.0   \n",
       "...                ...              ...              ...              ...   \n",
       "35290              0.0              1.0              0.0              8.0   \n",
       "35291              0.0              1.0              0.0              8.0   \n",
       "35292              0.0              3.0              0.0              6.0   \n",
       "35293              1.0              4.0              0.0              5.0   \n",
       "35294              0.0              1.0              0.0              8.0   \n",
       "\n",
       "       most_frequent  dist_1  dist_2  dist_3  dist_4  \n",
       "0                  4     0.0     0.0    30.0    50.0  \n",
       "1                  4     0.0     0.0     0.0    50.0  \n",
       "2                  4     0.0     0.0     0.0    50.0  \n",
       "3                  4     0.0     0.0     7.0    50.0  \n",
       "4                  4     0.0     0.0     0.0    50.0  \n",
       "...              ...     ...     ...     ...     ...  \n",
       "35290              9     0.0    11.0    32.0    50.0  \n",
       "35291              9     2.0    19.0    40.0    50.0  \n",
       "35292              9     0.0    15.0    37.0    50.0  \n",
       "35293              9    19.0    44.0    50.0    50.0  \n",
       "35294              9     0.0     3.0    21.0    50.0  \n",
       "\n",
       "[35295 rows x 28 columns]"
      ]
     },
     "execution_count": 29,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "test_result"
   ]
  }
 ],
 "metadata": {
  "kernelspec": {
   "display_name": "Python 3 (ipykernel)",
   "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.9.5"
  }
 },
 "nbformat": 4,
 "nbformat_minor": 4
}