Datasets.ipynb

{
 "cells": [
  {
   "cell_type": "markdown",
   "id": "fd4ebe7f",
   "metadata": {},
   "source": [
    "<img title=\"GitHub Octocat\" src='./img/Octocat.jpg' style='height: 60px; padding-right: 15px' alt=\"Octocat\" align=\"left\"> This notebook is part of a GitHub repository: https://github.com/pessini/avian-flu-wild-birds-ireland \n",
    "<br>MIT Licensed\n",
    "<br>Author: Leandro Pessini"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "7ad0c0cd",
   "metadata": {},
   "source": [
    "# <p style=\"font-size:100%; text-align:left; color:#444444;\">Gathering Data</p>"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 2,
   "id": "10a2025a",
   "metadata": {},
   "outputs": [],
   "source": [
    "import geopandas as gpd\n",
    "import json\n",
    "from pathlib import Path\n",
    "from shapely.geometry import Point\n",
    "from shapely.geometry import Polygon\n",
    "\n",
    "import pandas as pd\n",
    "import numpy as np\n",
    "from bs4 import BeautifulSoup\n",
    "import requests\n",
    "import urllib\n",
    "from urllib import parse\n",
    "from skimage import io"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "64f38f85",
   "metadata": {},
   "source": [
    "## BirdWatch Ireland - Species & Habitat Conservation\n",
    "\n",
    "<img title=\"BirdWatch Ireland\" src=\"img/BWI_logo_03_80px@2x.webp\" alt=\"BirdWatch Ireland\" style='height:150px; padding: 15px' align = \"right\">**BirdWatch Ireland** is the largest independent conservation organisation in Ireland and their objective is the protection of wild birds and their habitats. They have been doing an incredible work protecting birds and biodiversity in Ireland. Check out their amazing work [here](https://birdwatchireland.ie/our-work/).\n",
    "\n",
    "The data is collected using **Web Scraping** technique from BirdWatch Ireland's website. There is a [list of Ireland's birds](https://birdwatchireland.ie/irelands-birds-birdwatch-ireland/list-of-irelands-birds/) with detailed information on every species.\n",
    "\n",
    "To create our dataset and merge with the data provided by the Department of Agriculture, Food and the Marine, the focus will be on the bird's image and common name.\n",
    "\n"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "23ad2ce3",
   "metadata": {},
   "source": [
    "**Note**: `scikit-image imread`: OpenCV represents images in BGR order, whereas scikit-image represents images in RGB order. To utilize OpenCV functions after downloading the image, there is a extra step which is to convert the image from RGB to BGR."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 4,
   "id": "8df14f2d",
   "metadata": {},
   "outputs": [],
   "source": [
    "# div class birds-with-filters (Parent)\n",
    "# page/2/\n",
    "no_pages = 24\n",
    "\n",
    "def get_data(pageNo):  \n",
    "    headers = {\"User-Agent\":\"Mozilla/5.0 (Windows NT 10.0; Win64; x64; rv:66.0) Gecko/20100101 Firefox/66.0\", \"Accept-Encoding\":\"gzip, deflate\", \"Accept\":\"text/html,application/xhtml+xml,application/xml;q=0.9,*/*;q=0.8\", \"DNT\":\"1\",\"Connection\":\"close\", \"Upgrade-Insecure-Requests\":\"1\"}\n",
    "\n",
    "    r = requests.get('https://birdwatchireland.ie/irelands-birds-birdwatch-ireland/list-of-irelands-birds/'+str(pageNo), headers=headers)#, proxies=proxies)\n",
    "    content = r.content\n",
    "    soup = BeautifulSoup(content)\n",
    "    #print(soup)\n",
    "\n",
    "    alls = []\n",
    "    for d in soup.findAll('article', attrs={'class':'bird'}):\n",
    "        \n",
    "        # image\n",
    "        image_div = d.find('div', attrs={'class':'bird-img'})\n",
    "        img_html = image_div.find('img')\n",
    "\n",
    "        # text\n",
    "        bird_name = d.find('h3', attrs={'class':'title'})\n",
    "\n",
    "        complementary_info = d.find('div', attrs={'class':'bird-info'})\n",
    "        bird_info = complementary_info.find_all('p')\n",
    "        irish_name = bird_info[0]\n",
    "        scientific_name = bird_info[1]\n",
    "        bird_family = bird_info[2]\n",
    "\n",
    "        all1=[]\n",
    "\n",
    "        if img_html is not None:\n",
    "            str_image = img_html['data-src']\n",
    "\n",
    "            # there is a URL on Page 20 with an accent \n",
    "            # Snow Goose - https://birdwatchireland.ie/app/uploads/2019/02/Snowy-Owl-08-with-kill-René-Bruun.jpg\n",
    "            # for that reason it needs to parse.quote() ignoring : and /, otherwise it will throw an error\n",
    "\n",
    "            image = io.imread(urllib.parse.quote(str_image, safe=':/'))\n",
    "            all1.append(image)\n",
    "        else:\n",
    "            all1.append('0')\n",
    "\n",
    "        if bird_name is not None:\n",
    "            all1.append(bird_name.text) if bird_name.text != '' else all1.append(np.nan)\n",
    "        else:    \n",
    "            all1.append('0')\n",
    "\n",
    "        if irish_name is not None:\n",
    "            all1.append(irish_name.text) if irish_name.text != '' else all1.append(np.nan)\n",
    "        else:\n",
    "            all1.append('0')\n",
    "\n",
    "        if scientific_name is not None:\n",
    "            all1.append(scientific_name.text) if scientific_name.text != '' else all1.append(np.nan)\n",
    "        else:\n",
    "            all1.append('0')\n",
    "\n",
    "        if bird_family is not None:\n",
    "            all1.append(bird_family.text) if bird_family.text != '' else all1.append(np.nan)\n",
    "        else:\n",
    "            all1.append('0')\n",
    "\n",
    "        alls.append(all1)    \n",
    "\n",
    "    return alls"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 5,
   "id": "f022e656",
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "page/1/: OK\n",
      "page/2/: OK\n",
      "page/3/: OK\n",
      "page/4/: OK\n",
      "page/5/: OK\n",
      "page/6/: OK\n",
      "page/7/: OK\n",
      "page/8/: OK\n",
      "page/9/: OK\n",
      "page/10/: OK\n",
      "page/11/: OK\n",
      "page/12/: OK\n",
      "page/13/: OK\n",
      "page/14/: OK\n",
      "page/15/: OK\n",
      "page/16/: OK\n",
      "page/17/: OK\n",
      "page/18/: OK\n",
      "page/19/: OK\n",
      "page/20/: OK\n",
      "page/21/: OK\n",
      "page/22/: OK\n",
      "page/23/: OK\n",
      "page/24/: OK\n"
     ]
    }
   ],
   "source": [
    "results = []\n",
    "# for i in range(1, no_pages+1):\n",
    "for i in range(1, no_pages+1):\n",
    "    url_to_append = \"page/{}/\".format(i)\n",
    "    results.append(get_data(url_to_append))\n",
    "    print(url_to_append+': OK')\n",
    "\n",
    "flatten = lambda l: [item for sublist in l for item in sublist]\n",
    "data_webscraping = flatten(results)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 6,
   "id": "c05f2681",
   "metadata": {},
   "outputs": [],
   "source": [
    "df_birds = pd.DataFrame(data_webscraping, columns=['Image','Bird_Name','Irish_Name','Scientific_Name','Bird_Family'])\n",
    "df_birds.to_pickle('./data/BirdWatchIreland.pkl')\n",
    "#df_birds.to_csv('./data/BirdWatchIreland.csv', index=False)"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "d91aa00c",
   "metadata": {},
   "source": [
    "---\n",
    "## H5N1 Wild Bird Species Identification\n",
    "\n",
    "<img title=\"Department of Agriculture, Food and the Marine\" src=\"img/department-of-agriculture-food-and-the-marine.png\" alt=\"Department of Agriculture, Food and the Marine\" align=\"center\" style='height:150px; padding: 15px'>\n",
    "\n",
    "\n",
    "Dataset provided by Ireland's [Department of Agriculture, Food and the Marine](https://data.gov.ie/dataset/h5n1-wild-bird-species-identification) which contains the locations of bird species captured in Ireland from 1980-09-01 to 2020-01-27 and wild birds that are targeted for the H5N1 strain of avian flu."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 7,
   "id": "28535e05",
   "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>Scientific_Name</th>\n",
       "      <th>Common_Name</th>\n",
       "      <th>Date</th>\n",
       "      <th>Year</th>\n",
       "      <th>Month</th>\n",
       "      <th>Day</th>\n",
       "      <th>Time</th>\n",
       "      <th>Country</th>\n",
       "      <th>Country_State_County</th>\n",
       "      <th>State</th>\n",
       "      <th>County</th>\n",
       "      <th>Locality</th>\n",
       "      <th>Latitude</th>\n",
       "      <th>Longitude</th>\n",
       "      <th>Parent_Species</th>\n",
       "      <th>target_H5_HPAI</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>Acrocephalus scirpaceus</td>\n",
       "      <td>Eurasian Reed Warbler</td>\n",
       "      <td>15/09/2015</td>\n",
       "      <td>2015</td>\n",
       "      <td>9</td>\n",
       "      <td>15</td>\n",
       "      <td>1100</td>\n",
       "      <td>Ireland</td>\n",
       "      <td>IE-C-GY</td>\n",
       "      <td>Connaught</td>\n",
       "      <td>Galway</td>\n",
       "      <td>Inishmore (Inis Mór)</td>\n",
       "      <td>53.1291</td>\n",
       "      <td>-9.7507</td>\n",
       "      <td>Acrocephalus scirpaceus</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>Acrocephalus scirpaceus</td>\n",
       "      <td>Eurasian Reed Warbler</td>\n",
       "      <td>15/09/2015</td>\n",
       "      <td>2015</td>\n",
       "      <td>9</td>\n",
       "      <td>15</td>\n",
       "      <td>1100</td>\n",
       "      <td>Ireland</td>\n",
       "      <td>IE-C-GY</td>\n",
       "      <td>Connaught</td>\n",
       "      <td>Galway</td>\n",
       "      <td>Inishmore (Inis Mór)</td>\n",
       "      <td>53.1291</td>\n",
       "      <td>-9.7507</td>\n",
       "      <td>Acrocephalus scirpaceus</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>Limosa haemastica</td>\n",
       "      <td>Hudsonian Godwit</td>\n",
       "      <td>15/09/2015</td>\n",
       "      <td>2015</td>\n",
       "      <td>9</td>\n",
       "      <td>15</td>\n",
       "      <td>1100</td>\n",
       "      <td>Ireland</td>\n",
       "      <td>IE-C-GY</td>\n",
       "      <td>Connaught</td>\n",
       "      <td>Galway</td>\n",
       "      <td>Inishmore (Inis Mór)</td>\n",
       "      <td>53.1291</td>\n",
       "      <td>-9.7507</td>\n",
       "      <td>Limosa haemastica</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>Limosa haemastica</td>\n",
       "      <td>Hudsonian Godwit</td>\n",
       "      <td>15/09/2015</td>\n",
       "      <td>2015</td>\n",
       "      <td>9</td>\n",
       "      <td>15</td>\n",
       "      <td>1100</td>\n",
       "      <td>Ireland</td>\n",
       "      <td>IE-C-GY</td>\n",
       "      <td>Connaught</td>\n",
       "      <td>Galway</td>\n",
       "      <td>Inishmore (Inis Mór)</td>\n",
       "      <td>53.1291</td>\n",
       "      <td>-9.7507</td>\n",
       "      <td>Limosa haemastica</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>Limosa haemastica</td>\n",
       "      <td>Hudsonian Godwit</td>\n",
       "      <td>15/09/2015</td>\n",
       "      <td>2015</td>\n",
       "      <td>9</td>\n",
       "      <td>15</td>\n",
       "      <td>1100</td>\n",
       "      <td>Ireland</td>\n",
       "      <td>IE-C-GY</td>\n",
       "      <td>Connaught</td>\n",
       "      <td>Galway</td>\n",
       "      <td>Inishmore (Inis Mór)</td>\n",
       "      <td>53.1291</td>\n",
       "      <td>-9.7507</td>\n",
       "      <td>Limosa haemastica</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "           Scientific_Name            Common_Name        Date  Year  Month  \\\n",
       "0  Acrocephalus scirpaceus  Eurasian Reed Warbler  15/09/2015  2015      9   \n",
       "1  Acrocephalus scirpaceus  Eurasian Reed Warbler  15/09/2015  2015      9   \n",
       "2        Limosa haemastica       Hudsonian Godwit  15/09/2015  2015      9   \n",
       "3        Limosa haemastica       Hudsonian Godwit  15/09/2015  2015      9   \n",
       "4        Limosa haemastica       Hudsonian Godwit  15/09/2015  2015      9   \n",
       "\n",
       "   Day  Time  Country Country_State_County      State  County  \\\n",
       "0   15  1100  Ireland              IE-C-GY  Connaught  Galway   \n",
       "1   15  1100  Ireland              IE-C-GY  Connaught  Galway   \n",
       "2   15  1100  Ireland              IE-C-GY  Connaught  Galway   \n",
       "3   15  1100  Ireland              IE-C-GY  Connaught  Galway   \n",
       "4   15  1100  Ireland              IE-C-GY  Connaught  Galway   \n",
       "\n",
       "               Locality  Latitude  Longitude           Parent_Species  \\\n",
       "0  Inishmore (Inis Mór)   53.1291    -9.7507  Acrocephalus scirpaceus   \n",
       "1  Inishmore (Inis Mór)   53.1291    -9.7507  Acrocephalus scirpaceus   \n",
       "2  Inishmore (Inis Mór)   53.1291    -9.7507        Limosa haemastica   \n",
       "3  Inishmore (Inis Mór)   53.1291    -9.7507        Limosa haemastica   \n",
       "4  Inishmore (Inis Mór)   53.1291    -9.7507        Limosa haemastica   \n",
       "\n",
       "   target_H5_HPAI  \n",
       "0               0  \n",
       "1               0  \n",
       "2               0  \n",
       "3               0  \n",
       "4               0  "
      ]
     },
     "execution_count": 7,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "wild_birds = pd.read_csv(\"./data/98696_58589762-e8f9-4bb0-9d39-09570efbad62.xls\", encoding='latin-1')\n",
    "birdwatch = pd.read_pickle('./data/BirdWatchIreland.pkl')\n",
    "wild_birds.head()"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "47034b65",
   "metadata": {},
   "source": [
    "**JOIN** the two bird datasets to link image to the specie. Using Scientific Name as key column"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 8,
   "id": "2357c423",
   "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>Image</th>\n",
       "      <th>Bird_Name</th>\n",
       "      <th>Irish_Name</th>\n",
       "      <th>Scientific_Name</th>\n",
       "      <th>Bird_Family</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>[[[193, 145, 9], [189, 141, 5], [185, 137, 1],...</td>\n",
       "      <td>Arctic Tern</td>\n",
       "      <td>Geabhróg artach</td>\n",
       "      <td>Sterna paradisaea</td>\n",
       "      <td>Terns</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>[[[33, 72, 87], [35, 74, 89], [37, 76, 93], [4...</td>\n",
       "      <td>Balearic Shearwater</td>\n",
       "      <td>Cánóg Bhailéarach</td>\n",
       "      <td>Puffinus mauretanicus</td>\n",
       "      <td>Tubenoses</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>[[[52, 64, 88], [55, 67, 91], [57, 69, 93], [5...</td>\n",
       "      <td>Bar-tailed Godwit</td>\n",
       "      <td>Guilbneach stríocearrach</td>\n",
       "      <td>Limosa lapponica</td>\n",
       "      <td>Waders</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>[[[89, 78, 46], [88, 77, 45], [85, 74, 42], [8...</td>\n",
       "      <td>Barn Owl</td>\n",
       "      <td>Scréachóg reilige</td>\n",
       "      <td>Tyto alba</td>\n",
       "      <td>Owls</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>[[[130, 112, 74], [129, 111, 73], [127, 109, 7...</td>\n",
       "      <td>Barnacle Goose</td>\n",
       "      <td>Gé ghiúrainn</td>\n",
       "      <td>Branta leucopsis</td>\n",
       "      <td>Geese</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "                                               Image            Bird_Name  \\\n",
       "0  [[[193, 145, 9], [189, 141, 5], [185, 137, 1],...          Arctic Tern   \n",
       "1  [[[33, 72, 87], [35, 74, 89], [37, 76, 93], [4...  Balearic Shearwater   \n",
       "2  [[[52, 64, 88], [55, 67, 91], [57, 69, 93], [5...    Bar-tailed Godwit   \n",
       "3  [[[89, 78, 46], [88, 77, 45], [85, 74, 42], [8...             Barn Owl   \n",
       "4  [[[130, 112, 74], [129, 111, 73], [127, 109, 7...       Barnacle Goose   \n",
       "\n",
       "                 Irish_Name        Scientific_Name Bird_Family  \n",
       "0           Geabhróg artach      Sterna paradisaea       Terns  \n",
       "1         Cánóg Bhailéarach  Puffinus mauretanicus   Tubenoses  \n",
       "2  Guilbneach stríocearrach       Limosa lapponica      Waders  \n",
       "3         Scréachóg reilige              Tyto alba        Owls  \n",
       "4              Gé ghiúrainn       Branta leucopsis       Geese  "
      ]
     },
     "execution_count": 8,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "birdwatch.head()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 9,
   "id": "93bf743d",
   "metadata": {},
   "outputs": [],
   "source": [
    "bird_flu = wild_birds.join(birdwatch.set_index('Scientific_Name'), on='Scientific_Name', lsuffix='_original', rsuffix='_bwi')"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 10,
   "id": "dcb2c362",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "Scientific_Name\n",
       "Chroicocephalus ridibundus    332\n",
       "Cygnus olor                   273\n",
       "Ardea cinerea                 259\n",
       "Egretta garzetta              227\n",
       "Larus marinus                 152\n",
       "Anas platyrhynchos            144\n",
       "Buteo buteo                   121\n",
       "Aythya fuligula               119\n",
       "Pica pica                     118\n",
       "Cygnus cygnus                 105\n",
       "Falco peregrinus               92\n",
       "Tadorna tadorna                83\n",
       "Tachybaptus ruficollis         78\n",
       "Larus canus                    76\n",
       "Branta bernicla                63\n",
       "Somateria mollissima           44\n",
       "Anser anser                    43\n",
       "Anas acuta                     40\n",
       "Aythya marila                  37\n",
       "Anser brachyrhynchus           33\n",
       "Podiceps cristatus             29\n",
       "Anas crecca                    25\n",
       "Turdus pilaris                 21\n",
       "Branta canadensis              18\n",
       "Aythya ferina                  18\n",
       "Bucephala clangula             16\n",
       "Mergus merganser               15\n",
       "Haliaeetus albicilla            4\n",
       "dtype: int64"
      ]
     },
     "execution_count": 10,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# Selecting only infected birds\n",
    "infected_birds = bird_flu[bird_flu['target_H5_HPAI'] == 1]\n",
    "top_infected_species = infected_birds.groupby('Scientific_Name').size().sort_values(ascending=False)\n",
    "top_infected_species"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 11,
   "id": "6cf74cea",
   "metadata": {},
   "outputs": [],
   "source": [
    "infected_birds_new = top_infected_species.to_frame().join(birdwatch.set_index('Scientific_Name'), on='Scientific_Name', lsuffix='_original', rsuffix='_bwi')"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 12,
   "id": "765d994b",
   "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>0</th>\n",
       "      <th>Image</th>\n",
       "      <th>Bird_Name</th>\n",
       "      <th>Irish_Name</th>\n",
       "      <th>Bird_Family</th>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Scientific_Name</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>Chroicocephalus ridibundus</th>\n",
       "      <td>332</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Branta bernicla</th>\n",
       "      <td>63</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Aythya marila</th>\n",
       "      <td>37</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "                              0 Image Bird_Name Irish_Name Bird_Family\n",
       "Scientific_Name                                                       \n",
       "Chroicocephalus ridibundus  332   NaN       NaN        NaN         NaN\n",
       "Branta bernicla              63   NaN       NaN        NaN         NaN\n",
       "Aythya marila                37   NaN       NaN        NaN         NaN"
      ]
     },
     "execution_count": 12,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "infected_birds_new[infected_birds_new.isna().any(axis=1)]"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "c38d7d2a",
   "metadata": {},
   "source": [
    "---\n",
    "\n",
    "There are **3 species** among those classified as Infected Birds which do not have image. Something might be different on those two datasets. Let's investigate.\n",
    "\n",
    "- Chroicocephalus ridibundus\n",
    "- Branta bernicla\n",
    "- Aythya marila\n",
    "\n",
    "`Chroicocephalus ridibundus`: have the same Common Name Black-headed Gull on both dataset but on BirdWatch Ireland's dataset the Scientific Name is Larus ridibundus. I will switch the Scientific name so that the Join can work.\n",
    "\n",
    "---\n",
    "\n",
    "`Branta bernicla`: There are three subspecies of Brant (or Brent) Goose.\n",
    "- **Branta bernicla nigricans** — Black-bellied Brant of extreme north-east Siberia to north central Canada.\n",
    "- **Branta bernicla bernicla** — Dark-bellied Brant of northern and central Siberia.\n",
    "- **Branta bernicla hrota** — Pale-bellied Brant of Canada, Greenland, Svalbard and Franz Josef Land.\n",
    "\n",
    "A fourth Brent Goose population has been recorded in Ireland, though its taxonomic status remains uncertain and it has no scientific name. It is generally known colloquially as **‘Grey-bellied Brant’**.\n",
    "\n",
    "**Department of Agriculture, Food and the Marine dataset**\n",
    "- Branta bernicla                   63\n",
    "- Branta bernicla hrota             45\n",
    "- Branta bernicla bernicla           4\n",
    "- Branta bernicla (Gray-bellied)     1\n",
    "\n",
    "\n",
    "Black-bellied Brant (*nigricans*) is very similar to Light-bellied Brent Goose (*hrota*) and care is needed to distinguish the two species. Brent Goose (Dark-bellied) and Black Brant are a rare winter visitor. As Brent Goose (Light-bellied) is the most common species in Ireland and it is easily mistaken by Black-bellied Brant (*nigricans*), I will combine Pale-bellied Brant and Black-bellied Brant.\n",
    "\n",
    "\n",
    "Sources: \n",
    "\n",
    "https://www.waterfowl.org.uk/wildfowl/swans-geese-allies/brent-goose/\n",
    "\n",
    "https://www.birdguides.com/articles/identification/brent-geese-photo-id-guide/\n",
    "\n",
    "---\n",
    "\n",
    "`Aythya marila`: Greater Scaup from the Duck family is under the Scientific Name Anas marila on BirdWatch Ireland's dataset."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 13,
   "id": "24ba605d",
   "metadata": {},
   "outputs": [],
   "source": [
    "wild_birds_copy = wild_birds.copy()\n",
    "birdwatch_copy = birdwatch.copy()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 14,
   "id": "c62e094c",
   "metadata": {},
   "outputs": [],
   "source": [
    "# Fixing 1st issue 'Chroicocephalus ridibundus'\n",
    "birdwatch_copy['Scientific_Name'] = birdwatch_copy['Scientific_Name'].replace('Larus ridibundus','Chroicocephalus ridibundus')"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 15,
   "id": "f7b49974",
   "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>Scientific_Name</th>\n",
       "      <th>Common_Name</th>\n",
       "      <th>Date</th>\n",
       "      <th>County</th>\n",
       "      <th>Locality</th>\n",
       "      <th>Parent_Species</th>\n",
       "      <th>target_H5_HPAI</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>179</th>\n",
       "      <td>Branta bernicla</td>\n",
       "      <td>Brant</td>\n",
       "      <td>30/04/2016</td>\n",
       "      <td>Donegal</td>\n",
       "      <td>Donegal</td>\n",
       "      <td>Branta bernicla</td>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1055</th>\n",
       "      <td>Branta bernicla hrota</td>\n",
       "      <td>Brant (Atlantic)</td>\n",
       "      <td>13/12/2016</td>\n",
       "      <td>Wexford</td>\n",
       "      <td>Wexford Wildfowl Reserve</td>\n",
       "      <td>Branta bernicla</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>6235</th>\n",
       "      <td>Branta bernicla bernicla</td>\n",
       "      <td>Brant (Dark-bellied)</td>\n",
       "      <td>03/01/2019</td>\n",
       "      <td>Galway</td>\n",
       "      <td>Barna Pier, County Galway, IE (53.249, -9.15)</td>\n",
       "      <td>Branta bernicla</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>6839</th>\n",
       "      <td>Branta bernicla (Gray-bellied)</td>\n",
       "      <td>Brant (Gray-bellied)</td>\n",
       "      <td>18/03/2019</td>\n",
       "      <td>Louth</td>\n",
       "      <td>Dundalk Bay--Lurgangreen (hide and saltmarsh)</td>\n",
       "      <td>Branta bernicla</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "                     Scientific_Name           Common_Name        Date  \\\n",
       "179                  Branta bernicla                 Brant  30/04/2016   \n",
       "1055           Branta bernicla hrota      Brant (Atlantic)  13/12/2016   \n",
       "6235        Branta bernicla bernicla  Brant (Dark-bellied)  03/01/2019   \n",
       "6839  Branta bernicla (Gray-bellied)  Brant (Gray-bellied)  18/03/2019   \n",
       "\n",
       "       County                                       Locality   Parent_Species  \\\n",
       "179   Donegal                                        Donegal  Branta bernicla   \n",
       "1055  Wexford                       Wexford Wildfowl Reserve  Branta bernicla   \n",
       "6235   Galway  Barna Pier, County Galway, IE (53.249, -9.15)  Branta bernicla   \n",
       "6839    Louth  Dundalk Bay--Lurgangreen (hide and saltmarsh)  Branta bernicla   \n",
       "\n",
       "      target_H5_HPAI  \n",
       "179                1  \n",
       "1055               0  \n",
       "6235               0  \n",
       "6839               0  "
      ]
     },
     "execution_count": 15,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# Different Brent Geese species\n",
    "wild_birds_copy[wild_birds_copy['Scientific_Name'].str.startswith('Branta bernicla')].drop_duplicates(subset='Common_Name').drop(['Year', 'Month','Day','Time','Country','Country_State_County','State','Latitude','Longitude'], axis='columns')"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 16,
   "id": "61378344",
   "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>Image</th>\n",
       "      <th>Bird_Name</th>\n",
       "      <th>Irish_Name</th>\n",
       "      <th>Scientific_Name</th>\n",
       "      <th>Bird_Family</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>7</th>\n",
       "      <td>[[[36, 18, 4], [33, 15, 1], [42, 24, 10], [49,...</td>\n",
       "      <td>Black Brant</td>\n",
       "      <td>Cadhan</td>\n",
       "      <td>Branta bernicla nigricans</td>\n",
       "      <td>Geese</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>18</th>\n",
       "      <td>[[[157, 171, 184], [157, 171, 184], [156, 170,...</td>\n",
       "      <td>Brent Goose (Dark-bellied)</td>\n",
       "      <td>Cadhan</td>\n",
       "      <td>Branta bernicla bernicla</td>\n",
       "      <td>Geese</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>19</th>\n",
       "      <td>[[[153, 176, 190], [153, 176, 190], [153, 176,...</td>\n",
       "      <td>Brent Goose (Light-bellied)</td>\n",
       "      <td>Cadhan</td>\n",
       "      <td>Branta bernicla hrota</td>\n",
       "      <td>Geese</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "                                                Image  \\\n",
       "7   [[[36, 18, 4], [33, 15, 1], [42, 24, 10], [49,...   \n",
       "18  [[[157, 171, 184], [157, 171, 184], [156, 170,...   \n",
       "19  [[[153, 176, 190], [153, 176, 190], [153, 176,...   \n",
       "\n",
       "                      Bird_Name Irish_Name            Scientific_Name  \\\n",
       "7                   Black Brant     Cadhan  Branta bernicla nigricans   \n",
       "18   Brent Goose (Dark-bellied)     Cadhan   Branta bernicla bernicla   \n",
       "19  Brent Goose (Light-bellied)     Cadhan      Branta bernicla hrota   \n",
       "\n",
       "   Bird_Family  \n",
       "7        Geese  \n",
       "18       Geese  \n",
       "19       Geese  "
      ]
     },
     "execution_count": 16,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "birdwatch_copy[birdwatch_copy['Irish_Name'] == 'Cadhan']"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 17,
   "id": "1c3d4055",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "Scientific_Name\n",
       "Branta bernicla                   63\n",
       "Branta bernicla hrota             45\n",
       "Branta bernicla bernicla           4\n",
       "Branta bernicla (Gray-bellied)     1\n",
       "dtype: int64"
      ]
     },
     "execution_count": 17,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "#infected_birds = bird_flu[bird_flu['target_H5_HPAI'] == 1]\n",
    "top_infected_species = wild_birds_copy.groupby('Scientific_Name').size().sort_values(ascending=False)\n",
    "top_infected_species[top_infected_species.index.str.startswith('Branta bernicla')]"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 18,
   "id": "ead2c8d2",
   "metadata": {},
   "outputs": [],
   "source": [
    "# Fixing 2nd issue 'Branta bernicla'\n",
    "wild_birds_copy['Scientific_Name'] = wild_birds_copy['Scientific_Name'].replace(['Branta bernicla'],'Branta bernicla hrota')"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 19,
   "id": "f7c27c2c",
   "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>Image</th>\n",
       "      <th>Bird_Name</th>\n",
       "      <th>Irish_Name</th>\n",
       "      <th>Scientific_Name</th>\n",
       "      <th>Bird_Family</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>32</th>\n",
       "      <td>[[[179, 185, 185], [179, 185, 185], [178, 184,...</td>\n",
       "      <td>Common Scoter</td>\n",
       "      <td>Scótar</td>\n",
       "      <td>Melanitta nigra</td>\n",
       "      <td>Ducks</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>46</th>\n",
       "      <td>[[[32, 36, 45], [33, 37, 46], [34, 38, 47], [3...</td>\n",
       "      <td>Eider</td>\n",
       "      <td>Éadar</td>\n",
       "      <td>Somateria mollissima</td>\n",
       "      <td>Ducks</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>51</th>\n",
       "      <td>[[[96, 131, 163], [96, 131, 163], [96, 131, 16...</td>\n",
       "      <td>Gadwall</td>\n",
       "      <td>Gadual</td>\n",
       "      <td>Anas strepera</td>\n",
       "      <td>Ducks</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>54</th>\n",
       "      <td>[[[119, 149, 77], [120, 150, 78], [122, 149, 7...</td>\n",
       "      <td>Garganey</td>\n",
       "      <td>Praslacha shamhraidh</td>\n",
       "      <td>Anas querquedula</td>\n",
       "      <td>Ducks</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>59</th>\n",
       "      <td>[[[159, 169, 171], [158, 168, 170], [158, 168,...</td>\n",
       "      <td>Goldeneye</td>\n",
       "      <td>Órshúileach</td>\n",
       "      <td>Bucephala clangula</td>\n",
       "      <td>Ducks</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>61</th>\n",
       "      <td>[[[66, 67, 51], [69, 70, 54], [72, 73, 57], [7...</td>\n",
       "      <td>Goosander</td>\n",
       "      <td>Síolta mhór</td>\n",
       "      <td>Mergus merganser</td>\n",
       "      <td>Ducks</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>71</th>\n",
       "      <td>[[[180, 196, 212], [180, 196, 212], [180, 196,...</td>\n",
       "      <td>Green-winged Teal</td>\n",
       "      <td>Praslacha ghlaseiteach</td>\n",
       "      <td>Anas carolinensis</td>\n",
       "      <td>Ducks</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>108</th>\n",
       "      <td>[[[26, 24, 12], [26, 24, 12], [26, 24, 12], [2...</td>\n",
       "      <td>Long-tailed Duck</td>\n",
       "      <td>Lacha earrfhada</td>\n",
       "      <td>Clangula hyemalis</td>\n",
       "      <td>Ducks</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>111</th>\n",
       "      <td>[[[119, 154, 192], [120, 155, 193], [122, 157,...</td>\n",
       "      <td>Mallard</td>\n",
       "      <td>Mallard</td>\n",
       "      <td>Anas platyrhynchos</td>\n",
       "      <td>Ducks</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>128</th>\n",
       "      <td>[[[102, 91, 59], [96, 85, 53], [91, 81, 46], [...</td>\n",
       "      <td>Pintail</td>\n",
       "      <td>Biorearrach</td>\n",
       "      <td>Anas acuta</td>\n",
       "      <td>Ducks</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>129</th>\n",
       "      <td>[[[193, 196, 203], [193, 196, 203], [193, 196,...</td>\n",
       "      <td>Pochard</td>\n",
       "      <td>Póiseard cíordhearg</td>\n",
       "      <td>Aythya ferina</td>\n",
       "      <td>Ducks</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>137</th>\n",
       "      <td>[[[21, 19, 20], [21, 19, 20], [21, 19, 20], [2...</td>\n",
       "      <td>Red-breasted Merganser</td>\n",
       "      <td>Síolta rua</td>\n",
       "      <td>Mergus serrator</td>\n",
       "      <td>Ducks</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>149</th>\n",
       "      <td>[[[89, 88, 86], [89, 88, 86], [90, 89, 87], [9...</td>\n",
       "      <td>Ring-necked Duck</td>\n",
       "      <td>Lacha mhuinceach</td>\n",
       "      <td>Aythya collaris</td>\n",
       "      <td>Ducks</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>156</th>\n",
       "      <td>[[[65, 72, 38], [65, 72, 38], [65, 72, 38], [6...</td>\n",
       "      <td>Ruddy Duck</td>\n",
       "      <td>Lacha rua</td>\n",
       "      <td>Oxyura jamaicensis</td>\n",
       "      <td>Ducks</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>162</th>\n",
       "      <td>[[[171, 161, 102], [167, 157, 98], [164, 152, ...</td>\n",
       "      <td>Scaup</td>\n",
       "      <td>Lacha iascán</td>\n",
       "      <td>Anas marila</td>\n",
       "      <td>Ducks</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>165</th>\n",
       "      <td>[[[62, 85, 116], [61, 84, 115], [61, 84, 115],...</td>\n",
       "      <td>Shelduck</td>\n",
       "      <td>Seil-lacha</td>\n",
       "      <td>Tadorna tadorna</td>\n",
       "      <td>Ducks</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>167</th>\n",
       "      <td>[[[105, 117, 129], [105, 117, 129], [105, 117,...</td>\n",
       "      <td>Shoveler</td>\n",
       "      <td>Spadalach</td>\n",
       "      <td>Anas clypeata</td>\n",
       "      <td>Ducks</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>171</th>\n",
       "      <td>[[[88, 109, 136], [87, 108, 135], [87, 108, 13...</td>\n",
       "      <td>Smew</td>\n",
       "      <td>Síolta gheal</td>\n",
       "      <td>Mergellus albellus</td>\n",
       "      <td>Ducks</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>185</th>\n",
       "      <td>[[[81, 107, 142], [86, 112, 147], [91, 117, 15...</td>\n",
       "      <td>Surf Scoter</td>\n",
       "      <td>Scótar toinne</td>\n",
       "      <td>Mellanitta perspicillata</td>\n",
       "      <td>Ducks</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>188</th>\n",
       "      <td>[[[204, 208, 217], [204, 208, 217], [204, 208,...</td>\n",
       "      <td>Teal</td>\n",
       "      <td>Praslacha</td>\n",
       "      <td>Anas crecca</td>\n",
       "      <td>Ducks</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>191</th>\n",
       "      <td>[[[159, 159, 157], [159, 159, 157], [159, 159,...</td>\n",
       "      <td>Tufted Duck</td>\n",
       "      <td>Lacha bhadánach</td>\n",
       "      <td>Aythya fuligula</td>\n",
       "      <td>Ducks</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>195</th>\n",
       "      <td>[[[131, 124, 105], [139, 132, 114], [117, 110,...</td>\n",
       "      <td>Velvet Scoter</td>\n",
       "      <td>Sceadach</td>\n",
       "      <td>Mellanitta fusca</td>\n",
       "      <td>Ducks</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>204</th>\n",
       "      <td>[[[51, 48, 39], [46, 43, 34], [42, 39, 30], [4...</td>\n",
       "      <td>Wigeon</td>\n",
       "      <td>Rualacha</td>\n",
       "      <td>Anas penelope</td>\n",
       "      <td>Ducks</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "                                                 Image  \\\n",
       "32   [[[179, 185, 185], [179, 185, 185], [178, 184,...   \n",
       "46   [[[32, 36, 45], [33, 37, 46], [34, 38, 47], [3...   \n",
       "51   [[[96, 131, 163], [96, 131, 163], [96, 131, 16...   \n",
       "54   [[[119, 149, 77], [120, 150, 78], [122, 149, 7...   \n",
       "59   [[[159, 169, 171], [158, 168, 170], [158, 168,...   \n",
       "61   [[[66, 67, 51], [69, 70, 54], [72, 73, 57], [7...   \n",
       "71   [[[180, 196, 212], [180, 196, 212], [180, 196,...   \n",
       "108  [[[26, 24, 12], [26, 24, 12], [26, 24, 12], [2...   \n",
       "111  [[[119, 154, 192], [120, 155, 193], [122, 157,...   \n",
       "128  [[[102, 91, 59], [96, 85, 53], [91, 81, 46], [...   \n",
       "129  [[[193, 196, 203], [193, 196, 203], [193, 196,...   \n",
       "137  [[[21, 19, 20], [21, 19, 20], [21, 19, 20], [2...   \n",
       "149  [[[89, 88, 86], [89, 88, 86], [90, 89, 87], [9...   \n",
       "156  [[[65, 72, 38], [65, 72, 38], [65, 72, 38], [6...   \n",
       "162  [[[171, 161, 102], [167, 157, 98], [164, 152, ...   \n",
       "165  [[[62, 85, 116], [61, 84, 115], [61, 84, 115],...   \n",
       "167  [[[105, 117, 129], [105, 117, 129], [105, 117,...   \n",
       "171  [[[88, 109, 136], [87, 108, 135], [87, 108, 13...   \n",
       "185  [[[81, 107, 142], [86, 112, 147], [91, 117, 15...   \n",
       "188  [[[204, 208, 217], [204, 208, 217], [204, 208,...   \n",
       "191  [[[159, 159, 157], [159, 159, 157], [159, 159,...   \n",
       "195  [[[131, 124, 105], [139, 132, 114], [117, 110,...   \n",
       "204  [[[51, 48, 39], [46, 43, 34], [42, 39, 30], [4...   \n",
       "\n",
       "                  Bird_Name              Irish_Name           Scientific_Name  \\\n",
       "32            Common Scoter                  Scótar           Melanitta nigra   \n",
       "46                    Eider                   Éadar      Somateria mollissima   \n",
       "51                  Gadwall                  Gadual             Anas strepera   \n",
       "54                 Garganey    Praslacha shamhraidh          Anas querquedula   \n",
       "59                Goldeneye             Órshúileach        Bucephala clangula   \n",
       "61                Goosander             Síolta mhór          Mergus merganser   \n",
       "71        Green-winged Teal  Praslacha ghlaseiteach         Anas carolinensis   \n",
       "108        Long-tailed Duck         Lacha earrfhada         Clangula hyemalis   \n",
       "111                 Mallard                 Mallard        Anas platyrhynchos   \n",
       "128                 Pintail             Biorearrach                Anas acuta   \n",
       "129                 Pochard     Póiseard cíordhearg             Aythya ferina   \n",
       "137  Red-breasted Merganser              Síolta rua           Mergus serrator   \n",
       "149        Ring-necked Duck        Lacha mhuinceach           Aythya collaris   \n",
       "156              Ruddy Duck               Lacha rua        Oxyura jamaicensis   \n",
       "162                   Scaup            Lacha iascán               Anas marila   \n",
       "165                Shelduck              Seil-lacha           Tadorna tadorna   \n",
       "167                Shoveler               Spadalach             Anas clypeata   \n",
       "171                    Smew            Síolta gheal        Mergellus albellus   \n",
       "185             Surf Scoter           Scótar toinne  Mellanitta perspicillata   \n",
       "188                    Teal               Praslacha               Anas crecca   \n",
       "191             Tufted Duck         Lacha bhadánach           Aythya fuligula   \n",
       "195           Velvet Scoter                Sceadach          Mellanitta fusca   \n",
       "204                  Wigeon                Rualacha             Anas penelope   \n",
       "\n",
       "    Bird_Family  \n",
       "32        Ducks  \n",
       "46        Ducks  \n",
       "51        Ducks  \n",
       "54        Ducks  \n",
       "59        Ducks  \n",
       "61        Ducks  \n",
       "71        Ducks  \n",
       "108       Ducks  \n",
       "111       Ducks  \n",
       "128       Ducks  \n",
       "129       Ducks  \n",
       "137       Ducks  \n",
       "149       Ducks  \n",
       "156       Ducks  \n",
       "162       Ducks  \n",
       "165       Ducks  \n",
       "167       Ducks  \n",
       "171       Ducks  \n",
       "185       Ducks  \n",
       "188       Ducks  \n",
       "191       Ducks  \n",
       "195       Ducks  \n",
       "204       Ducks  "
      ]
     },
     "execution_count": 19,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "birdwatch_copy[birdwatch_copy['Bird_Family'] == 'Ducks']"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 20,
   "id": "807a8566",
   "metadata": {},
   "outputs": [],
   "source": [
    "# Fixing 3rd issue 'Anas marila'\n",
    "birdwatch_copy['Scientific_Name'] = birdwatch_copy['Scientific_Name'].replace('Anas marila','Aythya marila')"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 21,
   "id": "a091eae1",
   "metadata": {},
   "outputs": [],
   "source": [
    "final_df = wild_birds_copy.join(birdwatch_copy.set_index('Scientific_Name'), on='Scientific_Name', lsuffix='_original', rsuffix='_bwi')\n",
    "\n",
    "final_df.to_pickle('./data/bird-flu.pkl')"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "db8ef873",
   "metadata": {},
   "source": [
    "---"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "f5ae5b13",
   "metadata": {},
   "source": [
    "## Ordnance Survey *Ireland* (OSi) - Ireland’s National Mapping Agency\n",
    "\n",
    "<img title=\"Ordnance Survey Ireland (OSi)\" src=\"img/Logo-Colour-298x100.jpeg\" alt=\"Ireland’s National Mapping Agency\" align=\"right\" style='height:120px; padding: 15px'>The spacial data is provided by [**Ordnance Survey *Ireland* (OSi)**](https://www.osi.ie/) under [Creative Commons licence](https://creativecommons.org/licenses/by/4.0/legalcode).\n",
    "\n",
    "Ordnance Survey Ireland has evolved from the Ordnance Survey Office which was established in 1824, later becoming a state body under the Ordnance Survey Ireland Act 2001. Under this Act, Ordnance Survey Ireland continued its mainstream public service function of creating and maintaining the definitive mapping records of the State and also assumed the commercial function assigned to it under the Act of developing its commercial business and sales revenues.\n",
    "\n",
    "**Administrative Areas** dataset generated from the [**2019 OSi National Statutory Boundary dataset**](https://data-osi.opendata.arcgis.com/datasets/OSi::administrative-areas-osi-national-statutory-boundaries-/about).\n",
    "\n",
    "Dataset License: https://creativecommons.org/licenses/by/4.0/"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 22,
   "id": "c06eec47",
   "metadata": {},
   "outputs": [],
   "source": [
    "url_geoJSON = 'https://opendata.arcgis.com/datasets/0d5984f732c54246bd087768223c92eb_0.geojson'\n",
    "admin_areas_json = 'data/Administrative_Areas_Ireland.json'"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 23,
   "id": "b9f58f1f",
   "metadata": {},
   "outputs": [],
   "source": [
    "# GeoJSON API\n",
    "admin_areas = gpd.read_file(url_geoJSON, driver='GeoJSON')"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "fcdebc1e",
   "metadata": {},
   "source": [
    "### Adding Count of bird flu occurences on each Administrative Area"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 44,
   "id": "85fe8e44",
   "metadata": {},
   "outputs": [],
   "source": [
    "avian_flu = wild_birds.copy()\n",
    "avian_flu['geometry'] = None\n",
    "\n",
    "for index, row in avian_flu.iterrows():\n",
    "    avian_flu.loc[index, 'geometry'] = Point(row.Longitude, row.Latitude)"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "057ec595",
   "metadata": {},
   "source": [
    "**Coordinate Reference System (CRS)**: Setting a projection with Spatial Reference [**EPSG Code**](https://epsg.io/29902)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 45,
   "id": "6d764008",
   "metadata": {},
   "outputs": [],
   "source": [
    "gdf_infected_birds = gpd.GeoDataFrame(avian_flu, geometry='geometry').set_crs(epsg=29902, inplace=True)"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "69a04477-ec47-46f4-b8bd-b6009d385345",
   "metadata": {},
   "source": [
    "On the dataset with birds' information we have only Latitude and Longitude, so first I convert them in geometry Points to use later on a polygon operation. After that, a loop is created and for every point an intersect operation is done to check if the Point belongs to that Polygon (Administrative Area). "
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 46,
   "id": "d79b0f38",
   "metadata": {},
   "outputs": [],
   "source": [
    "# adding Count of bird flu occurences on each Administrative Area\n",
    "for index, area in admin_areas.iterrows():\n",
    "    \n",
    "    count_infected_birds = len(gdf_infected_birds[(gdf_infected_birds['target_H5_HPAI'] == 1) & (gdf_infected_birds.intersects(area.geometry)) ])\n",
    "    count_healthy_birds = len(gdf_infected_birds[(gdf_infected_birds['target_H5_HPAI'] == 0) & (gdf_infected_birds.intersects(area.geometry)) ])\n",
    "    total_birds = count_healthy_birds + count_infected_birds\n",
    "    \n",
    "    admin_areas.loc[index, 'TOTAL_BIRDS'] = total_birds\n",
    "    admin_areas.loc[index, 'HEALTHY_BIRDS'] = count_healthy_birds\n",
    "    admin_areas.loc[index, 'INFECTED_BIRDS'] = count_infected_birds"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 47,
   "id": "968707d6-9f65-45fd-9783-85c5006a8d44",
   "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>ENGLISH</th>\n",
       "      <th>GAEILGE</th>\n",
       "      <th>CONTAE</th>\n",
       "      <th>COUNTY</th>\n",
       "      <th>PROVINCE</th>\n",
       "      <th>GUID</th>\n",
       "      <th>CENTROID_X</th>\n",
       "      <th>CENTROID_Y</th>\n",
       "      <th>AREA</th>\n",
       "      <th>CC_ID</th>\n",
       "      <th>OBJECTID</th>\n",
       "      <th>Shape__Area</th>\n",
       "      <th>Shape__Length</th>\n",
       "      <th>geometry</th>\n",
       "      <th>TOTAL_BIRDS</th>\n",
       "      <th>HEALTHY_BIRDS</th>\n",
       "      <th>INFECTED_BIRDS</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>DUBLIN CITY COUNCIL</td>\n",
       "      <td>None</td>\n",
       "      <td>Baile Átha Cliath</td>\n",
       "      <td>DUBLIN</td>\n",
       "      <td>Leinster</td>\n",
       "      <td>2ae19629-1433-13a3-e055-000000000001</td>\n",
       "      <td>716469.75</td>\n",
       "      <td>735272.06</td>\n",
       "      <td>1.283502e+08</td>\n",
       "      <td>265011</td>\n",
       "      <td>1</td>\n",
       "      <td>1.283502e+08</td>\n",
       "      <td>101493.212412</td>\n",
       "      <td>POLYGON ((-6.38258 53.33367, -6.38261 53.33370...</td>\n",
       "      <td>2161.0</td>\n",
       "      <td>1642.0</td>\n",
       "      <td>519.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>CORK CITY COUNCIL</td>\n",
       "      <td>None</td>\n",
       "      <td>Corcaigh</td>\n",
       "      <td>CORK</td>\n",
       "      <td>Munster</td>\n",
       "      <td>2ae19629-1434-13a3-e055-000000000001</td>\n",
       "      <td>565833.13</td>\n",
       "      <td>571933.83</td>\n",
       "      <td>1.865976e+08</td>\n",
       "      <td>45511</td>\n",
       "      <td>2</td>\n",
       "      <td>1.865976e+08</td>\n",
       "      <td>80293.730785</td>\n",
       "      <td>POLYGON ((-8.38436 51.90533, -8.38425 51.90529...</td>\n",
       "      <td>223.0</td>\n",
       "      <td>182.0</td>\n",
       "      <td>41.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>GALWAY CITY COUNCIL</td>\n",
       "      <td>None</td>\n",
       "      <td>Gaillimh</td>\n",
       "      <td>GALWAY</td>\n",
       "      <td>Connacht</td>\n",
       "      <td>2ae19629-1435-13a3-e055-000000000001</td>\n",
       "      <td>530067.66</td>\n",
       "      <td>726500.52</td>\n",
       "      <td>5.069505e+07</td>\n",
       "      <td>65011</td>\n",
       "      <td>3</td>\n",
       "      <td>5.069505e+07</td>\n",
       "      <td>64020.725628</td>\n",
       "      <td>MULTIPOLYGON (((-9.13605 53.26682, -9.13606 53...</td>\n",
       "      <td>989.0</td>\n",
       "      <td>730.0</td>\n",
       "      <td>259.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>OFFALY COUNTY COUNCIL</td>\n",
       "      <td>None</td>\n",
       "      <td>Uíbh Fhailí</td>\n",
       "      <td>OFFALY</td>\n",
       "      <td>Leinster</td>\n",
       "      <td>2ae19629-1496-13a3-e055-000000000001</td>\n",
       "      <td>631261.72</td>\n",
       "      <td>709672.35</td>\n",
       "      <td>2.000025e+09</td>\n",
       "      <td>185001</td>\n",
       "      <td>4</td>\n",
       "      <td>2.000025e+09</td>\n",
       "      <td>389927.708615</td>\n",
       "      <td>POLYGON ((-7.97902 53.33689, -7.97878 53.33684...</td>\n",
       "      <td>76.0</td>\n",
       "      <td>69.0</td>\n",
       "      <td>7.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>WICKLOW COUNTY COUNCIL</td>\n",
       "      <td>None</td>\n",
       "      <td>Cill Mhantáin</td>\n",
       "      <td>WICKLOW</td>\n",
       "      <td>Leinster</td>\n",
       "      <td>2ae19629-149e-13a3-e055-000000000001</td>\n",
       "      <td>707784.79</td>\n",
       "      <td>690738.10</td>\n",
       "      <td>2.025161e+09</td>\n",
       "      <td>255001</td>\n",
       "      <td>5</td>\n",
       "      <td>2.025161e+09</td>\n",
       "      <td>320629.958733</td>\n",
       "      <td>MULTIPOLYGON (((-6.14602 52.78372, -6.14607 52...</td>\n",
       "      <td>1231.0</td>\n",
       "      <td>1062.0</td>\n",
       "      <td>169.0</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "                  ENGLISH GAEILGE             CONTAE   COUNTY  PROVINCE  \\\n",
       "0     DUBLIN CITY COUNCIL    None  Baile Átha Cliath   DUBLIN  Leinster   \n",
       "1       CORK CITY COUNCIL    None           Corcaigh     CORK   Munster   \n",
       "2     GALWAY CITY COUNCIL    None           Gaillimh   GALWAY  Connacht   \n",
       "3   OFFALY COUNTY COUNCIL    None        Uíbh Fhailí   OFFALY  Leinster   \n",
       "4  WICKLOW COUNTY COUNCIL    None      Cill Mhantáin  WICKLOW  Leinster   \n",
       "\n",
       "                                   GUID  CENTROID_X  CENTROID_Y          AREA  \\\n",
       "0  2ae19629-1433-13a3-e055-000000000001   716469.75   735272.06  1.283502e+08   \n",
       "1  2ae19629-1434-13a3-e055-000000000001   565833.13   571933.83  1.865976e+08   \n",
       "2  2ae19629-1435-13a3-e055-000000000001   530067.66   726500.52  5.069505e+07   \n",
       "3  2ae19629-1496-13a3-e055-000000000001   631261.72   709672.35  2.000025e+09   \n",
       "4  2ae19629-149e-13a3-e055-000000000001   707784.79   690738.10  2.025161e+09   \n",
       "\n",
       "    CC_ID  OBJECTID   Shape__Area  Shape__Length  \\\n",
       "0  265011         1  1.283502e+08  101493.212412   \n",
       "1   45511         2  1.865976e+08   80293.730785   \n",
       "2   65011         3  5.069505e+07   64020.725628   \n",
       "3  185001         4  2.000025e+09  389927.708615   \n",
       "4  255001         5  2.025161e+09  320629.958733   \n",
       "\n",
       "                                            geometry  TOTAL_BIRDS  \\\n",
       "0  POLYGON ((-6.38258 53.33367, -6.38261 53.33370...       2161.0   \n",
       "1  POLYGON ((-8.38436 51.90533, -8.38425 51.90529...        223.0   \n",
       "2  MULTIPOLYGON (((-9.13605 53.26682, -9.13606 53...        989.0   \n",
       "3  POLYGON ((-7.97902 53.33689, -7.97878 53.33684...         76.0   \n",
       "4  MULTIPOLYGON (((-6.14602 52.78372, -6.14607 52...       1231.0   \n",
       "\n",
       "   HEALTHY_BIRDS  INFECTED_BIRDS  \n",
       "0         1642.0           519.0  \n",
       "1          182.0            41.0  \n",
       "2          730.0           259.0  \n",
       "3           69.0             7.0  \n",
       "4         1062.0           169.0  "
      ]
     },
     "execution_count": 47,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "admin_areas.head()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 48,
   "id": "f1950a26",
   "metadata": {},
   "outputs": [],
   "source": [
    "# Saving the data downloaded to a local json file\n",
    "admin_areas.to_file(admin_areas_json, driver='GeoJSON')"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "9cbb897c",
   "metadata": {},
   "source": [
    "---\n",
    "<img title=\"GitHub Mark\" src=\"./img/GitHub-Mark-64px.png\" style=\"height: 32px; padding-right: 15px\" alt=\"GitHub Mark\" align=\"left\"> [GitHub repository](https://github.com/pessini/avian-flu-wild-birds-ireland) <br>Author: Leandro Pessini"
   ]
  }
 ],
 "metadata": {
  "hide_input": false,
  "kernelspec": {
   "display_name": "Python 3.9.6 (conda)",
   "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.6"
  },
  "metadata": {
   "interpreter": {
    "hash": "31f2aee4e71d21fbe5cf8b01ff0e069b9275f58929596ceb00d14d90e3e16cd6"
   }
  },
  "vscode": {
   "interpreter": {
    "hash": "ae5ef0c7311e5822b8a274a6761986a9f5cd6ff0f6f6646c362c7f9c6daa9a30"
   }
  }
 },
 "nbformat": 4,
 "nbformat_minor": 5
}