{
 "cells": [
  {
   "cell_type": "code",
   "execution_count": 41,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "data\n",
      "0       18203710\n",
      "1         123393\n",
      "2          71220\n",
      "3          51085\n",
      "4          39528\n",
      "          ...   \n",
      "89          3191\n",
      "93          3155\n",
      "87          3143\n",
      "82          3140\n",
      "90          3129\n",
      "Length: 101, dtype: int64\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>band</th>\n",
       "      <th>Deciduous woodland</th>\n",
       "      <th>Coniferous woodland</th>\n",
       "      <th>Arable</th>\n",
       "      <th>Improve grassland</th>\n",
       "      <th>Neutral grassland</th>\n",
       "      <th>Calcareous grassland</th>\n",
       "      <th>Acid grassland</th>\n",
       "      <th>Fen</th>\n",
       "      <th>Heather</th>\n",
       "      <th>Heather grassland</th>\n",
       "      <th>...</th>\n",
       "      <th>Inland rock</th>\n",
       "      <th>Saltwater</th>\n",
       "      <th>Freshwater</th>\n",
       "      <th>Supralittoral rock</th>\n",
       "      <th>Supralittoral sediment</th>\n",
       "      <th>Littoral rock</th>\n",
       "      <th>Littoral sediment</th>\n",
       "      <th>Saltmarsh</th>\n",
       "      <th>Urban</th>\n",
       "      <th>Suburban</th>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>y</th>\n",
       "      <th>x</th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th rowspan=\"5\" valign=\"top\">1299500.0</th>\n",
       "      <th>500.0</th>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>...</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1500.0</th>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>...</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2500.0</th>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>...</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3500.0</th>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>...</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4500.0</th>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>...</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>...</th>\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 rowspan=\"5\" valign=\"top\">500.0</th>\n",
       "      <th>695500.0</th>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>...</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>696500.0</th>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>...</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>697500.0</th>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>...</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>698500.0</th>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>...</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>699500.0</th>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>...</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "<p>910000 rows × 21 columns</p>\n",
       "</div>"
      ],
      "text/plain": [
       "band                Deciduous woodland  Coniferous woodland  Arable  \\\n",
       "y         x                                                           \n",
       "1299500.0 500.0                      0                    0       0   \n",
       "          1500.0                     0                    0       0   \n",
       "          2500.0                     0                    0       0   \n",
       "          3500.0                     0                    0       0   \n",
       "          4500.0                     0                    0       0   \n",
       "...                                ...                  ...     ...   \n",
       "500.0     695500.0                   0                    0       0   \n",
       "          696500.0                   0                    0       0   \n",
       "          697500.0                   0                    0       0   \n",
       "          698500.0                   0                    0       0   \n",
       "          699500.0                   0                    0       0   \n",
       "\n",
       "band                Improve grassland  Neutral grassland  \\\n",
       "y         x                                                \n",
       "1299500.0 500.0                     0                  0   \n",
       "          1500.0                    0                  0   \n",
       "          2500.0                    0                  0   \n",
       "          3500.0                    0                  0   \n",
       "          4500.0                    0                  0   \n",
       "...                               ...                ...   \n",
       "500.0     695500.0                  0                  0   \n",
       "          696500.0                  0                  0   \n",
       "          697500.0                  0                  0   \n",
       "          698500.0                  0                  0   \n",
       "          699500.0                  0                  0   \n",
       "\n",
       "band                Calcareous grassland  Acid grassland  Fen  Heather  \\\n",
       "y         x                                                              \n",
       "1299500.0 500.0                        0               0    0        0   \n",
       "          1500.0                       0               0    0        0   \n",
       "          2500.0                       0               0    0        0   \n",
       "          3500.0                       0               0    0        0   \n",
       "          4500.0                       0               0    0        0   \n",
       "...                                  ...             ...  ...      ...   \n",
       "500.0     695500.0                     0               0    0        0   \n",
       "          696500.0                     0               0    0        0   \n",
       "          697500.0                     0               0    0        0   \n",
       "          698500.0                     0               0    0        0   \n",
       "          699500.0                     0               0    0        0   \n",
       "\n",
       "band                Heather grassland  ...  Inland rock  Saltwater  \\\n",
       "y         x                            ...                           \n",
       "1299500.0 500.0                     0  ...            0          0   \n",
       "          1500.0                    0  ...            0          0   \n",
       "          2500.0                    0  ...            0          0   \n",
       "          3500.0                    0  ...            0          0   \n",
       "          4500.0                    0  ...            0          0   \n",
       "...                               ...  ...          ...        ...   \n",
       "500.0     695500.0                  0  ...            0          0   \n",
       "          696500.0                  0  ...            0          0   \n",
       "          697500.0                  0  ...            0          0   \n",
       "          698500.0                  0  ...            0          0   \n",
       "          699500.0                  0  ...            0          0   \n",
       "\n",
       "band                Freshwater  Supralittoral rock  Supralittoral sediment  \\\n",
       "y         x                                                                  \n",
       "1299500.0 500.0              0                   0                       0   \n",
       "          1500.0             0                   0                       0   \n",
       "          2500.0             0                   0                       0   \n",
       "          3500.0             0                   0                       0   \n",
       "          4500.0             0                   0                       0   \n",
       "...                        ...                 ...                     ...   \n",
       "500.0     695500.0           0                   0                       0   \n",
       "          696500.0           0                   0                       0   \n",
       "          697500.0           0                   0                       0   \n",
       "          698500.0           0                   0                       0   \n",
       "          699500.0           0                   0                       0   \n",
       "\n",
       "band                Littoral rock  Littoral sediment  Saltmarsh  Urban  \\\n",
       "y         x                                                              \n",
       "1299500.0 500.0                 0                  0          0      0   \n",
       "          1500.0                0                  0          0      0   \n",
       "          2500.0                0                  0          0      0   \n",
       "          3500.0                0                  0          0      0   \n",
       "          4500.0                0                  0          0      0   \n",
       "...                           ...                ...        ...    ...   \n",
       "500.0     695500.0              0                  0          0      0   \n",
       "          696500.0              0                  0          0      0   \n",
       "          697500.0              0                  0          0      0   \n",
       "          698500.0              0                  0          0      0   \n",
       "          699500.0              0                  0          0      0   \n",
       "\n",
       "band                Suburban  \n",
       "y         x                   \n",
       "1299500.0 500.0            0  \n",
       "          1500.0           0  \n",
       "          2500.0           0  \n",
       "          3500.0           0  \n",
       "          4500.0           0  \n",
       "...                      ...  \n",
       "500.0     695500.0         0  \n",
       "          696500.0         0  \n",
       "          697500.0         0  \n",
       "          698500.0         0  \n",
       "          699500.0         0  \n",
       "\n",
       "[910000 rows x 21 columns]"
      ]
     },
     "execution_count": 41,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "import rioxarray\n",
    "import os\n",
    "\n",
    "LAND_COVER_MAP_PATH = 'Datasets/Machine Learning/1km Rasters/Features/gb2021lcm1km_percentage_target.tif'\n",
    "\n",
    "\n",
    "lcm = rioxarray.open_rasterio(LAND_COVER_MAP_PATH)\n",
    "lcm.name = 'data'\n",
    "pest_df = lcm.to_dataframe().drop(columns='spatial_ref')\n",
    "print(pest_df.value_counts())\n",
    "pest_df = pest_df.unstack(level='band')\n",
    "\n",
    "LCM_CLASSES = [\n",
    "    'Deciduous woodland', \n",
    "    'Coniferous woodland', \n",
    "    'Arable', \n",
    "    'Improve grassland', \n",
    "    'Neutral grassland', \n",
    "    'Calcareous grassland', \n",
    "    'Acid grassland',  \n",
    "    'Fen', \n",
    "    'Heather', \n",
    "    'Heather grassland', \n",
    "    'Bog',\n",
    "    'Inland rock', \n",
    "    'Saltwater',\n",
    "    'Freshwater',\n",
    "    'Supralittoral rock',\n",
    "    'Supralittoral sediment',\n",
    "    'Littoral rock',\n",
    "    'Littoral sediment',\n",
    "    'Saltmarsh',\n",
    "    'Urban',\n",
    "    'Suburban'\n",
    "    ]\n",
    "\n",
    "pest_df = pest_df['data'].rename(columns={i+1: j for i, j in enumerate(LCM_CLASSES)})\n",
    "pest_df"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 43,
   "metadata": {},
   "outputs": [],
   "source": [
    "PATH = 'Datasets/Digimap/Land-Cover-plus-Pesticides_4719434/'\n",
    "#For each pesticide in pesticide folder,\n",
    "    #open file\n",
    "for file in os.listdir(PATH):\n",
    "    filename = os.fsdecode(file)\n",
    "    if filename.endswith('.tif'):\n",
    "        dataset = rioxarray.open_rasterio(PATH+filename)\n",
    "        dataset.name = filename[:-4]\n",
    "        df = dataset.to_dataframe().drop(columns='spatial_ref')\n",
    "        df = df.drop(index=2).droplevel('band')\n",
    "        pest_df = pest_df.join(df)\n",
    "\n",
    "\n",
    "# dataset = rioxarray.open_rasterio(PATH+'2,4-D.tif')\n",
    "# dataset.name = '2,4-D'\n",
    "# df = dataset.to_dataframe().drop(columns='spatial_ref')\n",
    "# df = df.drop(index=2).droplevel('band')\n",
    "# df"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 44,
   "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></th>\n",
       "      <th>Deciduous woodland</th>\n",
       "      <th>Coniferous woodland</th>\n",
       "      <th>Arable</th>\n",
       "      <th>Improve grassland</th>\n",
       "      <th>Neutral grassland</th>\n",
       "      <th>Calcareous grassland</th>\n",
       "      <th>Acid grassland</th>\n",
       "      <th>Fen</th>\n",
       "      <th>Heather</th>\n",
       "      <th>Heather grassland</th>\n",
       "      <th>...</th>\n",
       "      <th>Tralkoxydim</th>\n",
       "      <th>Tri-allate</th>\n",
       "      <th>Triadimenol</th>\n",
       "      <th>Tribenuron-methyl</th>\n",
       "      <th>Triclopyr</th>\n",
       "      <th>Trifloxystrobin</th>\n",
       "      <th>Triflusulfuron-methyl</th>\n",
       "      <th>Triticonazole</th>\n",
       "      <th>Zeta-cypermethrin</th>\n",
       "      <th>Zoxamide</th>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>y</th>\n",
       "      <th>x</th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>1220500.0</th>\n",
       "      <th>461500.0</th>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>...</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1219500.0</th>\n",
       "      <th>460500.0</th>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>...</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th rowspan=\"3\" valign=\"top\">1218500.0</th>\n",
       "      <th>459500.0</th>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>...</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>460500.0</th>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>23</td>\n",
       "      <td>...</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>461500.0</th>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>15</td>\n",
       "      <td>...</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>...</th>\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 rowspan=\"3\" valign=\"top\">6500.0</th>\n",
       "      <th>82500.0</th>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>...</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>83500.0</th>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>...</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>86500.0</th>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>...</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th rowspan=\"2\" valign=\"top\">5500.0</th>\n",
       "      <th>83500.0</th>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>...</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>84500.0</th>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>...</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "<p>242716 rows × 183 columns</p>\n",
       "</div>"
      ],
      "text/plain": [
       "                    Deciduous woodland  Coniferous woodland  Arable  \\\n",
       "y         x                                                           \n",
       "1220500.0 461500.0                   0                    0       0   \n",
       "1219500.0 460500.0                   0                    0       0   \n",
       "1218500.0 459500.0                   0                    0       0   \n",
       "          460500.0                   0                    0       0   \n",
       "          461500.0                   0                    0       0   \n",
       "...                                ...                  ...     ...   \n",
       "6500.0    82500.0                    0                    0       0   \n",
       "          83500.0                    0                    0       0   \n",
       "          86500.0                    0                    0       0   \n",
       "5500.0    83500.0                    0                    0       0   \n",
       "          84500.0                    0                    0       0   \n",
       "\n",
       "                    Improve grassland  Neutral grassland  \\\n",
       "y         x                                                \n",
       "1220500.0 461500.0                  0                  0   \n",
       "1219500.0 460500.0                  0                  0   \n",
       "1218500.0 459500.0                  0                  0   \n",
       "          460500.0                  0                  0   \n",
       "          461500.0                  0                  0   \n",
       "...                               ...                ...   \n",
       "6500.0    82500.0                   0                  0   \n",
       "          83500.0                   0                  0   \n",
       "          86500.0                   0                  0   \n",
       "5500.0    83500.0                   0                  0   \n",
       "          84500.0                   0                  0   \n",
       "\n",
       "                    Calcareous grassland  Acid grassland  Fen  Heather  \\\n",
       "y         x                                                              \n",
       "1220500.0 461500.0                     0               0    0        0   \n",
       "1219500.0 460500.0                     0               0    0        0   \n",
       "1218500.0 459500.0                     0               0    0        0   \n",
       "          460500.0                     0               0    0        0   \n",
       "          461500.0                     0               0    0        0   \n",
       "...                                  ...             ...  ...      ...   \n",
       "6500.0    82500.0                      0               0    0        0   \n",
       "          83500.0                      0               0    0        0   \n",
       "          86500.0                      0               0    0        0   \n",
       "5500.0    83500.0                      0               0    0        0   \n",
       "          84500.0                      0               0    0        0   \n",
       "\n",
       "                    Heather grassland  ...  Tralkoxydim  Tri-allate  \\\n",
       "y         x                            ...                            \n",
       "1220500.0 461500.0                  0  ...          NaN         NaN   \n",
       "1219500.0 460500.0                  0  ...          NaN         NaN   \n",
       "1218500.0 459500.0                  0  ...          NaN         NaN   \n",
       "          460500.0                 23  ...          NaN         NaN   \n",
       "          461500.0                 15  ...          NaN         NaN   \n",
       "...                               ...  ...          ...         ...   \n",
       "6500.0    82500.0                   0  ...          NaN         NaN   \n",
       "          83500.0                   0  ...          NaN         NaN   \n",
       "          86500.0                   0  ...          NaN         NaN   \n",
       "5500.0    83500.0                   0  ...          NaN         NaN   \n",
       "          84500.0                   0  ...          NaN         NaN   \n",
       "\n",
       "                    Triadimenol  Tribenuron-methyl  Triclopyr  \\\n",
       "y         x                                                     \n",
       "1220500.0 461500.0          NaN                NaN        NaN   \n",
       "1219500.0 460500.0          NaN                NaN        NaN   \n",
       "1218500.0 459500.0          NaN                NaN        NaN   \n",
       "          460500.0          NaN                NaN        NaN   \n",
       "          461500.0          NaN                NaN        NaN   \n",
       "...                         ...                ...        ...   \n",
       "6500.0    82500.0           NaN                NaN        NaN   \n",
       "          83500.0           NaN                NaN        NaN   \n",
       "          86500.0           NaN                NaN        NaN   \n",
       "5500.0    83500.0           NaN                NaN        NaN   \n",
       "          84500.0           NaN                NaN        NaN   \n",
       "\n",
       "                    Trifloxystrobin  Triflusulfuron-methyl  Triticonazole  \\\n",
       "y         x                                                                 \n",
       "1220500.0 461500.0              NaN                    NaN            NaN   \n",
       "1219500.0 460500.0              NaN                    NaN            NaN   \n",
       "1218500.0 459500.0              NaN                    NaN            NaN   \n",
       "          460500.0              NaN                    NaN            NaN   \n",
       "          461500.0              NaN                    NaN            NaN   \n",
       "...                             ...                    ...            ...   \n",
       "6500.0    82500.0               NaN                    NaN            NaN   \n",
       "          83500.0               NaN                    NaN            NaN   \n",
       "          86500.0               NaN                    NaN            NaN   \n",
       "5500.0    83500.0               NaN                    NaN            NaN   \n",
       "          84500.0               NaN                    NaN            NaN   \n",
       "\n",
       "                    Zeta-cypermethrin  Zoxamide  \n",
       "y         x                                      \n",
       "1220500.0 461500.0                NaN       NaN  \n",
       "1219500.0 460500.0                NaN       NaN  \n",
       "1218500.0 459500.0                NaN       NaN  \n",
       "          460500.0                NaN       NaN  \n",
       "          461500.0                NaN       NaN  \n",
       "...                               ...       ...  \n",
       "6500.0    82500.0                 NaN       NaN  \n",
       "          83500.0                 NaN       NaN  \n",
       "          86500.0                 NaN       NaN  \n",
       "5500.0    83500.0                 NaN       NaN  \n",
       "          84500.0                 NaN       NaN  \n",
       "\n",
       "[242716 rows x 183 columns]"
      ]
     },
     "execution_count": 44,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# Cleaning data\n",
    "pest_df = pest_df.loc[pest_df['Deciduous woodland']\n",
    "                        + pest_df['Coniferous woodland']\n",
    "                        + pest_df['Arable']\n",
    "                        + pest_df['Improve grassland']\n",
    "                        + pest_df['Neutral grassland']\n",
    "                        + pest_df['Calcareous grassland']\n",
    "                        + pest_df['Acid grassland']\n",
    "                        + pest_df['Fen']\n",
    "                        + pest_df['Heather']\n",
    "                        + pest_df['Heather grassland']\n",
    "                        + pest_df['Bog']\n",
    "                        + pest_df['Inland rock']\n",
    "                        + pest_df['Saltwater']\n",
    "                        + pest_df['Freshwater']\n",
    "                        + pest_df['Supralittoral rock']\n",
    "                        + pest_df['Supralittoral sediment']\n",
    "                        + pest_df['Littoral rock']\n",
    "                        + pest_df['Littoral sediment']\n",
    "                        + pest_df['Saltmarsh']\n",
    "                        + pest_df['Urban']\n",
    "                        + pest_df['Suburban'] != 0\n",
    "                    ]\n",
    "pest_df"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 45,
   "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></th>\n",
       "      <th>2,4-D</th>\n",
       "      <th>2,4-DB</th>\n",
       "      <th>Acetamiprid</th>\n",
       "      <th>Alpha-cypermethrin</th>\n",
       "      <th>Ametoctradin</th>\n",
       "      <th>Amidosulfuron</th>\n",
       "      <th>Amisulbrom</th>\n",
       "      <th>Asulam</th>\n",
       "      <th>Azoxystrobin</th>\n",
       "      <th>Bentazone</th>\n",
       "      <th>...</th>\n",
       "      <th>Tralkoxydim</th>\n",
       "      <th>Tri-allate</th>\n",
       "      <th>Triadimenol</th>\n",
       "      <th>Tribenuron-methyl</th>\n",
       "      <th>Triclopyr</th>\n",
       "      <th>Trifloxystrobin</th>\n",
       "      <th>Triflusulfuron-methyl</th>\n",
       "      <th>Triticonazole</th>\n",
       "      <th>Zeta-cypermethrin</th>\n",
       "      <th>Zoxamide</th>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>y</th>\n",
       "      <th>x</th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>1220500.0</th>\n",
       "      <th>461500.0</th>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>...</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1219500.0</th>\n",
       "      <th>460500.0</th>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>...</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th rowspan=\"3\" valign=\"top\">1218500.0</th>\n",
       "      <th>459500.0</th>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>...</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>460500.0</th>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>...</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>461500.0</th>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>...</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>...</th>\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 rowspan=\"3\" valign=\"top\">6500.0</th>\n",
       "      <th>82500.0</th>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>...</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>83500.0</th>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>...</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>86500.0</th>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>...</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th rowspan=\"2\" valign=\"top\">5500.0</th>\n",
       "      <th>83500.0</th>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>...</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>84500.0</th>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>...</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "<p>242716 rows × 162 columns</p>\n",
       "</div>"
      ],
      "text/plain": [
       "                    2,4-D  2,4-DB  Acetamiprid  Alpha-cypermethrin  \\\n",
       "y         x                                                          \n",
       "1220500.0 461500.0    NaN     NaN          NaN                 NaN   \n",
       "1219500.0 460500.0    NaN     NaN          NaN                 NaN   \n",
       "1218500.0 459500.0    NaN     NaN          NaN                 NaN   \n",
       "          460500.0    NaN     NaN          NaN                 NaN   \n",
       "          461500.0    NaN     NaN          NaN                 NaN   \n",
       "...                   ...     ...          ...                 ...   \n",
       "6500.0    82500.0     NaN     NaN          NaN                 NaN   \n",
       "          83500.0     NaN     NaN          NaN                 NaN   \n",
       "          86500.0     NaN     NaN          NaN                 NaN   \n",
       "5500.0    83500.0     NaN     NaN          NaN                 NaN   \n",
       "          84500.0     NaN     NaN          NaN                 NaN   \n",
       "\n",
       "                    Ametoctradin  Amidosulfuron  Amisulbrom  Asulam  \\\n",
       "y         x                                                           \n",
       "1220500.0 461500.0           NaN            NaN         NaN     NaN   \n",
       "1219500.0 460500.0           NaN            NaN         NaN     NaN   \n",
       "1218500.0 459500.0           NaN            NaN         NaN     NaN   \n",
       "          460500.0           NaN            NaN         NaN     NaN   \n",
       "          461500.0           NaN            NaN         NaN     NaN   \n",
       "...                          ...            ...         ...     ...   \n",
       "6500.0    82500.0            NaN            NaN         NaN     NaN   \n",
       "          83500.0            NaN            NaN         NaN     NaN   \n",
       "          86500.0            NaN            NaN         NaN     NaN   \n",
       "5500.0    83500.0            NaN            NaN         NaN     NaN   \n",
       "          84500.0            NaN            NaN         NaN     NaN   \n",
       "\n",
       "                    Azoxystrobin  Bentazone  ...  Tralkoxydim  Tri-allate  \\\n",
       "y         x                                  ...                            \n",
       "1220500.0 461500.0           NaN        NaN  ...          NaN         NaN   \n",
       "1219500.0 460500.0           NaN        NaN  ...          NaN         NaN   \n",
       "1218500.0 459500.0           NaN        NaN  ...          NaN         NaN   \n",
       "          460500.0           NaN        NaN  ...          NaN         NaN   \n",
       "          461500.0           NaN        NaN  ...          NaN         NaN   \n",
       "...                          ...        ...  ...          ...         ...   \n",
       "6500.0    82500.0            NaN        NaN  ...          NaN         NaN   \n",
       "          83500.0            NaN        NaN  ...          NaN         NaN   \n",
       "          86500.0            NaN        NaN  ...          NaN         NaN   \n",
       "5500.0    83500.0            NaN        NaN  ...          NaN         NaN   \n",
       "          84500.0            NaN        NaN  ...          NaN         NaN   \n",
       "\n",
       "                    Triadimenol  Tribenuron-methyl  Triclopyr  \\\n",
       "y         x                                                     \n",
       "1220500.0 461500.0          NaN                NaN        NaN   \n",
       "1219500.0 460500.0          NaN                NaN        NaN   \n",
       "1218500.0 459500.0          NaN                NaN        NaN   \n",
       "          460500.0          NaN                NaN        NaN   \n",
       "          461500.0          NaN                NaN        NaN   \n",
       "...                         ...                ...        ...   \n",
       "6500.0    82500.0           NaN                NaN        NaN   \n",
       "          83500.0           NaN                NaN        NaN   \n",
       "          86500.0           NaN                NaN        NaN   \n",
       "5500.0    83500.0           NaN                NaN        NaN   \n",
       "          84500.0           NaN                NaN        NaN   \n",
       "\n",
       "                    Trifloxystrobin  Triflusulfuron-methyl  Triticonazole  \\\n",
       "y         x                                                                 \n",
       "1220500.0 461500.0              NaN                    NaN            NaN   \n",
       "1219500.0 460500.0              NaN                    NaN            NaN   \n",
       "1218500.0 459500.0              NaN                    NaN            NaN   \n",
       "          460500.0              NaN                    NaN            NaN   \n",
       "          461500.0              NaN                    NaN            NaN   \n",
       "...                             ...                    ...            ...   \n",
       "6500.0    82500.0               NaN                    NaN            NaN   \n",
       "          83500.0               NaN                    NaN            NaN   \n",
       "          86500.0               NaN                    NaN            NaN   \n",
       "5500.0    83500.0               NaN                    NaN            NaN   \n",
       "          84500.0               NaN                    NaN            NaN   \n",
       "\n",
       "                    Zeta-cypermethrin  Zoxamide  \n",
       "y         x                                      \n",
       "1220500.0 461500.0                NaN       NaN  \n",
       "1219500.0 460500.0                NaN       NaN  \n",
       "1218500.0 459500.0                NaN       NaN  \n",
       "          460500.0                NaN       NaN  \n",
       "          461500.0                NaN       NaN  \n",
       "...                               ...       ...  \n",
       "6500.0    82500.0                 NaN       NaN  \n",
       "          83500.0                 NaN       NaN  \n",
       "          86500.0                 NaN       NaN  \n",
       "5500.0    83500.0                 NaN       NaN  \n",
       "          84500.0                 NaN       NaN  \n",
       "\n",
       "[242716 rows x 162 columns]"
      ]
     },
     "execution_count": 45,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "pest_df = pest_df.iloc[:, 21:]\n",
    "pest_df\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 46,
   "metadata": {},
   "outputs": [],
   "source": [
    "pest_df[pest_df<0]=0\n",
    "s = pest_df.sum()\n",
    "# View in data viewer to sort values"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 47,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "2,4-D                    185901.781250\n",
       "2,4-DB                   333859.062500\n",
       "Acetamiprid                6968.103516\n",
       "Alpha-cypermethrin         5101.584473\n",
       "Ametoctradin              39260.734375\n",
       "                             ...      \n",
       "Trifloxystrobin           39353.886719\n",
       "Triflusulfuron-methyl      6376.227051\n",
       "Triticonazole              4551.947754\n",
       "Zeta-cypermethrin          8268.361328\n",
       "Zoxamide                  21740.683594\n",
       "Length: 162, dtype: float32"
      ]
     },
     "execution_count": 47,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "s"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": []
  }
 ],
 "metadata": {
  "kernelspec": {
   "display_name": "Python 3.9.13 ('env': venv)",
   "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.13"
  },
  "orig_nbformat": 4,
  "vscode": {
   "interpreter": {
    "hash": "f025c48a9b67ab76bdc0400dfa0f9ba99120976b4a6ec6a63d1c946516165c91"
   }
  }
 },
 "nbformat": 4,
 "nbformat_minor": 2
}