doc/Metadata for DottedImages.ipynb

{
 "cells": [
  {
   "cell_type": "markdown",
   "id": "891de72e",
   "metadata": {},
   "source": [
    "# Avian data Data transformation notebook\n",
    "\n",
    "This notebook performs and document the transformations performed on the data received from Colibri by The Water Institute as part of this project. \n",
    "\n",
    "The main goals of this transformations are:\n",
    "  - Create a unified dataset for 2010-2021 data\n",
    "  - Rename both high resolution photos and screenshots to have them in a unified folder structure\n",
    "  - Create thumbnails for the high resolution photos to be visualized on the web dashboard\n",
    "  - Analyze difference between the summary data and the totals calculated from the raw data (if any)\n",
    "  - Generate datasets for being used in the visualizations"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "6655df25",
   "metadata": {},
   "outputs": [],
   "source": [
    "#%pip install tqdm pandas boto3\n"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "0e2d7c4b",
   "metadata": {},
   "source": [
    "### Parameters"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "3b33d973",
   "metadata": {},
   "outputs": [],
   "source": [
    "thumbnail_size = (518, 345)\n",
    "create_thumbnails = False\n",
    "rename_files=False\n",
    "replace = False\n",
    "_base_folder = \"avian_monitoring\"\n",
    "\n"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "815e7fd2",
   "metadata": {},
   "source": [
    "### Common imports"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "fb1130e6",
   "metadata": {},
   "outputs": [],
   "source": [
    "from PIL import Image\n",
    "import re\n",
    "import pandas as pd\n",
    "\n",
    "# import geopandas as gpd\n",
    "import pandas_access\n",
    "from datetime import datetime\n",
    "import pyodbc\n",
    "import numpy as np\n",
    "from multiprocessing import Pool\n",
    "from functools import partial\n",
    "import geopandas as gp\n",
    "import boto3\n",
    "import re\n",
    "from tqdm.notebook import tqdm\n",
    "from dateutil.parser import parse\n",
    "from concurrent.futures import ThreadPoolExecutor\n",
    "from io import BytesIO\n",
    "from functools import partial\n",
    "\n",
    "tqdm.pandas()\n",
    "\n",
    "boto3.setup_default_session(profile_name=\"GLO\")\n",
    "bucket_name = \"twi-aviandata\"\n",
    "starting_folder = \"HighResolutionImages\"\n",
    "new_folder_hr = \"avian_monitoring_\"\n",
    "aws_s3 = boto3.resource(\n",
    "    \"s3\",\n",
    ")\n",
    "avian_data = aws_s3.Bucket(bucket_name)"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "bd281c6d",
   "metadata": {},
   "source": [
    "### Utilities"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "0583c1df",
   "metadata": {},
   "outputs": [],
   "source": [
    "def clean_date(text):\n",
    "    \"\"\"Return a date as yyyy-MMM-dd (e.g. 2022-May-15)\"\"\"\n",
    "    datetimestr = parse(text)\n",
    "    text = datetime.strftime(datetimestr, \"%Y-%b-%d\")\n",
    "    return text\n",
    "\n",
    "\n",
    "def update_mime_type(t, mime=\"image/png\"):\n",
    "    \"\"\"Update the metadata of s3 objects to the given Mime type\"\"\"\n",
    "    s3_object = avian_data.Object(t)\n",
    "    s3_object.copy_from(\n",
    "        CopySource={\"Bucket\": avian_data.name, \"Key\": t},\n",
    "        Metadata=s3_object.metadata,\n",
    "        MetadataDirective=\"REPLACE\",\n",
    "        ContentType=mime,\n",
    "    )\n",
    "\n",
    "\n",
    "def copy_wnew_mime_type(t, k, mime=\"image/png\"):\n",
    "    \"\"\"Copy a s3 object, modifying its mime type\"\"\"\n",
    "    s3_object = avian_data.Object(t)\n",
    "    avian_data.copy(\n",
    "        {\"Bucket\": avian_data.name, \"Key\": t},\n",
    "        k,\n",
    "        Metadata=s3_object.metadata,\n",
    "        MetadataDirective=\"REPLACE\",\n",
    "        ContentType=mime,\n",
    "    )\n",
    "\n",
    "\n",
    "def rename(key, new_name, replace=False):\n",
    "    \"\"\"Copy an object (key) to a new location (new_name) on the avian_data bucket\"\"\"\n",
    "    if not replace and [o for o in avian_data.objects.filter(Prefix=new_name)]:\n",
    "        return True\n",
    "\n",
    "    try:\n",
    "        avian_data.copy({\"Bucket\": avian_data.name, \"Key\": key}, new_name)\n",
    "    except Exception as e:\n",
    "        print(e)\n",
    "        check_lowercase = list(\n",
    "            avian_data.objects.filter(Prefix=key.replace(\".JPG\", \".jpg\"))\n",
    "        )\n",
    "        if check_lowercase:\n",
    "            avian_data.copy(\n",
    "                {\"Bucket\": avian_data.name, \"Key\": check_lowercase[0].key}, new_name\n",
    "            )\n",
    "            return True\n",
    "        check_uppercase = list(\n",
    "            avian_data.objects.filter(Prefix=key.replace(\".jpg\", \".JPG\"))\n",
    "        )\n",
    "        if check_uppercase:\n",
    "            avian_data.copy(\n",
    "                {\"Bucket\": avian_data.name, \"Key\": check_uppercase[0].key}, new_name\n",
    "            )\n",
    "            return True\n",
    "        print(f\"{key} not found\")\n",
    "        return False\n",
    "\n",
    "\n",
    "def generate_thumbnail(high_res_key, thumb_key, regenerate=False):\n",
    "    \"\"\"Generate a low resolution thumbnail at thumb_key of the high_res_key object.\n",
    "    it uses the global thumbnail_size to define the size.\n",
    "    \"\"\"\n",
    "    current = avian_data.objects.filter(Prefix=thumb_key)\n",
    "    if not regenerate and [o for o in current]:\n",
    "        return True\n",
    "    objs = [o for o in avian_data.objects.filter(Prefix=high_res_key)]\n",
    "    if not objs:\n",
    "        print(f\"there is no {high_res_key}\")\n",
    "        return False\n",
    "    image = Image.open(BytesIO(objs[0].get()[\"Body\"].read()))\n",
    "    buffer = BytesIO()\n",
    "    image.thumbnail(thumbnail_size)\n",
    "    image.save(buffer, format=\"png\")\n",
    "    buffer.seek(0)\n",
    "    avian_data.put_object(Key=thumb_key, Body=buffer, ContentType=\"image/png\")\n",
    "    return True\n"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "61ba25dd",
   "metadata": {},
   "source": [
    "# Unified 2010-2021 Data\n",
    "\n",
    "Data has been unified by Colibri into a single access database, however it contains tables discriminated by years, with some differences in the schema. This process will combine all the datasets into a single one with a common deffinition of the total birds and total nests. "
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "1147a2b5",
   "metadata": {},
   "outputs": [],
   "source": [
    "# Notes: In order to use all the fields, I did a rename of the columns containing '?' or '/'\n",
    "# acc_db = \"/mnt/c/Users/carizaporras/Downloads/Colibri_tblsSpeciesData2010-2021_18sept22.accdb\"\n",
    "#acc_db = \"/mnt/z/Colibri2010-2021CWBColonies_12Nov2022_working_copy.accdb\"\n",
    "#acc_db = \"/mnt/z/Colibri2010-2021CWBColonies_2Jan2023.accdb\"\n",
    "acc_db = \"Colibri2010-2021CWBColonies_2Jan2023.accdb\"\n",
    "\n",
    "schema = pandas_access.read_schema(acc_db)\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "ef6dd021",
   "metadata": {},
   "outputs": [],
   "source": [
    "ct_name = \"tblRWCWB_ColonyInventory_10Nov22\"\n",
    "# colonies_table = schema.pop(\"tblRWCWB_ColonyInventory_13Sept2022\")\n",
    "colonies_table = schema.pop(ct_name)\n"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "17882869",
   "metadata": {},
   "source": [
    "There are three tables with slighly different schema"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "2150d7a0",
   "metadata": {},
   "outputs": [],
   "source": [
    "schema\n"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "0f47f136",
   "metadata": {},
   "source": [
    "Taking the 2015 to 2021 table as a reference, lets compare the schemas.\n",
    "\n",
    "Fields in the reference but not in the `tblSpeciesData2011_2013` table"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "7535028f",
   "metadata": {},
   "outputs": [],
   "source": [
    "schema[\"tblSpeciesData2015_2018_2021\"].keys() - schema[\"tblSpeciesData2011_2013\"].keys()\n"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "eb00a611",
   "metadata": {},
   "source": [
    "Fields in the `tblSpeciesData2011_2013` table but not in the reference"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "2bef7532",
   "metadata": {},
   "outputs": [],
   "source": [
    "schema[\"tblSpeciesData2011_2013\"].keys() - schema[\"tblSpeciesData2015_2018_2021\"].keys()"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "cbfcb572",
   "metadata": {},
   "source": [
    "Fields in the referece but not in the 2010 table"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "e20b6a5e",
   "metadata": {},
   "outputs": [],
   "source": [
    "schema[\"tblSpeciesData2015_2018_2021\"].keys() - schema[\"tblSpeciesData2010\"].keys()\n"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "95e4fdbf",
   "metadata": {},
   "source": [
    "Fields in the referece but not in the 2010 table"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "5a7763ae",
   "metadata": {},
   "outputs": [],
   "source": [
    "schema[\"tblSpeciesData2010\"].keys() - schema[\"tblSpeciesData2015_2018_2021\"].keys()\n"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "d1b0e1a8",
   "metadata": {},
   "source": [
    "That also means that we have different formulas to calculate the total. The bestForBPE field is used as filter in 2013 to 2021, but it is not used in the 2010.\n",
    "\n",
    "### Formulas\n",
    "\n",
    "|                | 2010                                                                                                                                                               | 2011-2013                                                                                                                                                          | 2015-2021                                                                                                 |\n",
    "|----------------|--------------------------------------------------------------------------------------------------------------------------------------------------------------------|--------------------------------------------------------------------------------------------------------------------------------------------------------------------|-----------------------------------------------------------------------------------------------------------|\n",
    "| Nests          | sum(<br>[WBN]<br>+[ChickNestw/outAdult]<br>+[AbandNest]<br>+[EmptyNest]<br>+[PBN]<br>+[Site]<br>+[Brood])                                                          | Sum(<br>[WBN]<br>+[ChickNestw/outAdult]<br>+[AbandNest]<br>+[EmptyNest]<br>+[PBN]<br>+[Site]<br>+[Brood])                                                          | Sum(<br>[WBN]<br>+[ChickNest]<br>+[ChickNestw/outAdult]<br>+[AbandNest]<br>+[PBN]<br>+[Site]<br>+[Brood]) |\n",
    "| Birds          | sum([WBN]<br>+[PBN]<br>+[Site]<br>+[OtherAdultsInColony]<br>+[OtherImmInColony]<br>+[RoostingBirds]<br>+[RoostingAdults]<br>+[RoostingImmatures]<br>+[UnknownAge]) | Sum([WBN]<br>+[PBN]<br>+[Site]<br>+[OtherAdultsInColony]<br>+[OtherImmInColony]<br>+[RoostingBirds]<br>+[RoostingAdults]<br>+[RoostingImmatures]<br>+[UnknownAge]) | Sum(<br>[WBN]<br>+[ChickNest]<br>+[PBN]<br>+[Territory]<br>+[Site]<br>+[OtherBirds])                      |\n",
    "| SumOfEmptyNest | sum(EmptyNest)                                                                                                                                                     | EmptyNest                                                                                                                                                          | EmptyNest                                                                                                 |\n",
    "\n",
    "\n",
    "But 2015 to 2021 has additional categories: \n",
    "\n",
    "```SQL\n",
    "\n",
    "Sum(WBN) AS SumOfWBN\n",
    "\n",
    "Sum(ChickNest) AS SumOfChickNest\n",
    "\n",
    "Sum([ChickNestw/outAdult]) AS [SumOfChickNestw/outAdult] \n",
    "\n",
    "Sum(Brood) AS SumOfBrood\n",
    "\n",
    "Sum(AbandNest) AS SumOfAbandNest\n",
    "\n",
    "Sum(PBN) AS SumOfPBN\n",
    "\n",
    "Sum(Territory) AS SumOfTerritory\n",
    "```"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "54afca74",
   "metadata": {},
   "source": [
    "## Generating a common dataset:\n",
    "- merge 2010 species with the colonies. \n",
    "- select only the common columns "
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "c81790db",
   "metadata": {},
   "outputs": [],
   "source": [
    "cols_2015_2021 = set(schema[\"tblSpeciesData2015_2018_2021\"].keys())\n",
    "cols_2011_2013 = set(schema[\"tblSpeciesData2011_2013\"].keys())\n",
    "cols_2010 = set(schema[\"tblSpeciesData2010\"].keys())\n",
    "common_fields = cols_2011_2013.intersection(cols_2015_2021)\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "6c1f4e29",
   "metadata": {},
   "outputs": [],
   "source": [
    "cols_2010 - common_fields\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "1577e587",
   "metadata": {},
   "outputs": [],
   "source": [
    "common_fields - cols_2010\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "185fc3af",
   "metadata": {},
   "outputs": [],
   "source": [
    "pd_species_2010 = pandas_access.read_table(acc_db, \"tblSpeciesData2010\")\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "05678f37",
   "metadata": {},
   "outputs": [],
   "source": [
    "pd_species_2010[\"Notes\"] = \"\"\n",
    "pd_species_2010[\"BestForBPE\"] = \"N\"\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "a3b25c82",
   "metadata": {},
   "outputs": [],
   "source": [
    "pd_species_2011_2013 = pandas_access.read_table(\n",
    "    acc_db, \"tblSpeciesData2011_2013\"\n",
    ")  # [common_fields]\n",
    "pd_species_2015_2021 = pandas_access.read_table(\n",
    "    acc_db, \"tblSpeciesData2015_2018_2021\"\n",
    ")  # [common_fields]\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "5660d42f",
   "metadata": {},
   "outputs": [],
   "source": [
    "pd_species = pd.concat(\n",
    "    [pd_species_2010, pd_species_2011_2013, pd_species_2015_2021], ignore_index=True\n",
    ")\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "109be71b",
   "metadata": {},
   "outputs": [],
   "source": [
    "pd_species = pd_species.drop(columns=\"AutoID\")\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "3fca9f44",
   "metadata": {},
   "outputs": [],
   "source": [
    "pd_species[\"Year\"] = pd_species[\"Year\"].astype(int).astype(str)\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "ab03ef09",
   "metadata": {},
   "outputs": [],
   "source": [
    "pd_colonies = pandas_access.read_table(acc_db, ct_name)\n",
    "pd_colonies[\"ColonyName\"] = pd_colonies[\"ColonyName\"].astype(str)\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "f296373d",
   "metadata": {},
   "outputs": [],
   "source": [
    "pd_species = pd.merge(pd_species, pd_colonies, on=\"ColonyName\")\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "ef5d90bd",
   "metadata": {},
   "outputs": [],
   "source": [
    "pd_species[\"HighResImage_new\"] = (\n",
    "    f\"{_base_folder}/high_resolution_photos/\"\n",
    "    + pd_species[\"Year\"].astype(str)\n",
    "    + \"/\"\n",
    "    + pd_species[\"GeoRegion\"]\n",
    "    + \"/\"\n",
    "    + pd_species[\"ColonyName\"]\n",
    "    + \"/\"\n",
    "    + pd.to_datetime(\n",
    "        pd_species[\"Date\"].astype(\"str\"), format=\"%m/%d/%y %H:%M:%S\"\n",
    "    ).dt.strftime(\"%d%B%y\")\n",
    "    + \"Camera\"\n",
    "    + pd_species[\"CameraNumber\"]\n",
    "    + \"-\"\n",
    "    + \"Card\"\n",
    "    + (pd_species[\"CardNumber\"].where(~pd_species[\"CardNumber\"].isnull(), other=\"1\"))\n",
    "    + \"-\"\n",
    "    + pd_species[\"PhotoNumber\"]\n",
    "    + \".jpg\"\n",
    ")\n",
    "pd_species[\"screenshot_new\"] = (\n",
    "    f\"{_base_folder}/screenshots/\"\n",
    "    + pd_species[\"Year\"].astype(str)\n",
    "    + \"/\"\n",
    "    + pd_species[\"GeoRegion\"]\n",
    "    + \"/\"\n",
    "    + pd_species[\"ColonyName\"]\n",
    "    + \"/\"\n",
    "    + pd.to_datetime(\n",
    "        pd_species[\"Date\"].astype(\"str\"), format=\"%m/%d/%y %H:%M:%S\"\n",
    "    ).dt.strftime(\"%d%B%y\")\n",
    "    + \"Camera\"\n",
    "    + pd_species[\"CameraNumber\"]\n",
    "    + \"-\"\n",
    "    + \"Card\"\n",
    "    + (pd_species[\"CardNumber\"].where(~pd_species[\"CardNumber\"].isnull(), other=\"1\"))\n",
    "    + \"-\"\n",
    "    + pd_species[\"PhotoNumber\"]\n",
    "    + \".jpg\"\n",
    ")\n",
    "pd_species[\"thumbnail_new\"] = (\n",
    "    f\"{_base_folder}/thumbnails/\"\n",
    "    + pd_species[\"Year\"].astype(str)\n",
    "    + \"/\"\n",
    "    + pd_species[\"GeoRegion\"]\n",
    "    + \"/\"\n",
    "    + pd_species[\"ColonyName\"]\n",
    "    + \"/\"\n",
    "    + pd.to_datetime(\n",
    "        pd_species[\"Date\"].astype(\"str\"), format=\"%m/%d/%y %H:%M:%S\"\n",
    "    ).dt.strftime(\"%d%B%y\")\n",
    "    + \"Camera\"\n",
    "    + pd_species[\"CameraNumber\"]\n",
    "    + \"-\"\n",
    "    + \"Card\"\n",
    "    + (pd_species[\"CardNumber\"].where(~pd_species[\"CardNumber\"].isnull(), other=\"1\"))\n",
    "    + \"-\"\n",
    "    + pd_species[\"PhotoNumber\"]\n",
    "    + \".png\"\n",
    ")\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "83726a42",
   "metadata": {},
   "outputs": [],
   "source": [
    "pd_species[\"total_nests\"] = np.where(\n",
    "    pd_species[\"Year\"].isin([\"2010\", \"2011\", \"2012\", \"2013\"]),\n",
    "    pd_species[\"WBN\"]\n",
    "    + pd_species[\"ChickNestwithoutAdult\"]\n",
    "    + pd_species[\"AbandNest\"]\n",
    "#    + pd_species[\"EmptyNest\"]\n",
    "    + pd_species[\"PBN\"]\n",
    "    + pd_species[\"Site\"]\n",
    "    + pd_species[\"Brood\"],\n",
    "    pd_species[\"WBN\"]\n",
    "    + pd_species[\"ChickNestwithoutAdult\"]\n",
    "    + pd_species[\"AbandNest\"]\n",
    "    + pd_species[\"ChickNest\"]\n",
    "    + pd_species[\"PBN\"]\n",
    "    + pd_species[\"Site\"]\n",
    "    + pd_species[\"Brood\"],\n",
    ")\n",
    "pd_species[\"total_birds\"] = np.where(\n",
    "    pd_species[\"Year\"].isin([\"2010\", \"2011\", \"2012\", \"2013\"]),\n",
    "    pd_species[\"WBN\"]\n",
    "    + pd_species[\"PBN\"]\n",
    "    + pd_species[\"Site\"]\n",
    "    + pd_species[\"OtherAdultsInColony\"]\n",
    "    + pd_species[\"OtherImmInColony\"]\n",
    "    + pd_species[\"RoostingBirds\"]\n",
    "    + pd_species[\"RoostingAdults\"]\n",
    "    + pd_species[\"RoostingImmatures\"]\n",
    "    + pd_species[\"UnknownAge\"],\n",
    "    pd_species[\"WBN\"]\n",
    "    + +pd_species[\"ChickNest\"]\n",
    "    + pd_species[\"PBN\"]\n",
    "    + pd_species[\"Territory\"]\n",
    "    + pd_species[\"Site\"]\n",
    "    + pd_species[\"OtherBirds\"],\n",
    ")\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "75d1f9ae",
   "metadata": {},
   "outputs": [],
   "source": [
    "pd_species[\"date2\"] = pd_species[\"Date\"].astype(\"str\").apply(clean_date)\n",
    "pd_species[\"month\"] = pd_species[\"date2\"].apply(lambda x: x.split(\"-\")[1])\n",
    "order_by_field = \"total_nests\" \n",
    "agg_2010 = (\n",
    "    pd_species[pd_species.Year == \"2010\"]\n",
    "    .groupby([\"Year\", \"month\", \"date2\",\"ColonyName\", \"SpeciesCode\"])\n",
    "    .agg({\"total_nests\": \"sum\", \"total_birds\":\"sum\"})\n",
    "    .reset_index()\n",
    ")\n"
   ]
  },
  {
   "attachments": {
    "image.png": {
     "image/png": "iVBORw0KGgoAAAANSUhEUgAABu4AAAAeCAYAAADDyeh4AAAXC0lEQVR4nO2dO3LjPJeGX019u5hU7qA/rwBagdRJR512RoVS4qyrJnHmhArNrFPXHzgxuQJxBXIHTa5iwkkwAS/iBSQBEhQl6n2qGFgGAfCcg9sBebA4nU4ShBBCCCGEEEIIIYQQQgghhJBJ+QcA/v3336nrcdd8fn5SBxNDHUwPdUDI9cN2Oj2fn594/M9jZzr5P3wvayyog+lhXzQ91MH0UAfTQx1MD3UwPdTB9FAHhBAyPz4/P/FfU1eCEEIIIYTYgxtG00MdEEIIIYQQQgghpC/cuCOEEEIImQncMJoe6oAQQgghhBBCCCFD4MYdIYQQQsgM4IbR9FAHhBBCCCGEEEIIGQo37gghhBBCbhxuGE0PdUAIIYQQQgghhBAbcOOOEEIIIeSG4YbR9FAHhBBCCCGEEEIIsQU37gghhBBCbhRuGE0PdUAIIYQQQgghhBCbcOOOEDIewRaLxQKrQzzP8noR47BaYLHYIuibxU085w1AOZIbhxtG00MdEEIIIYQQQgi5O0b1qVnwnc6A2W3cxXGA7WqFxWKRXitsDwHUJhQj2K6wytMusFptEbTZW3zAtsNwzOrQXM5qscBiq2uemUGb3ENayXRQvVYrbFuN5B6IEQdbrFaLkp2vtgcE8Tgd9mXLI4MptJ9VU5+UDvKz6bLiAIfKmLJYrLBabXGYc5/Ru69UtetFt7yqY6xGOXMcH402jBS22Shnk7QWyjOZM5nPr+rzvMRempK3zfHOdpRR1YFR/VIZlWx/azpXNNWVhjya2vOiY9FkeX5sZS59S4zUZnq352B7HsP5cssF6bE2JZahDqbnznXA8YAAsNIOWud0M9fnPfoiSI34sJq/rTdwz88+O06nk5wLkSskAAkIKRxHOk72NyQcv5pauiL9nxDScRzpiPP9btSWPyTgyGqO5nVIUOogcqVouaeG73TWjTTTqoPMPir6FCojsYYvHUBCuHLMUvpRaDu5nRfbT8H+UrvUkVVzXzROedORPc+AdnoLz5m1n5Y+NXsO3W6uHy1tyaIc/WJfL5xznyHOv4/7nJehT1+pHsd86bS26+Q+lc4yu8ruEV198szGR/N5W9oGIKRwXOn7vvTds9zK6jFJa6G8XDfdcybz+ZWiHr4rHUdI4dbTd8/xIum7ic2pdGBWv3rd3Cy99rhvqitNeaTtWRT7sfxStEkt2Unrup7T+sVEl0PtTK89F+ddzX3rvHRwDZivTakD21AH03PvOuB4QKS01g6Ua7TzpZgOT8BIfq9b8EWQ0cn6yf7+njH1N65tDH92TUb1TVrwnd44p9NJzm7jTrgVp0LeYZc763yiU3UWZI6F0u/ngVO4fqvhmNQhw8bGne8k+WcTtjk4iC+JkQ5yfY7ZeVzvAJ/YWoNTXUbSL9q/hY27scqbjvvauBPZxkrLxtmtb9zlNipc6SuzimTkN/3vtjAbr85OgrKIC2Oqql1HfmHMLf83G+uqKkvGXrWA5zY+ms7bsvlO/f2lut5M0toqr3nOVO4jzeZXmY01LNSlKm33HC+jaeNOt35Zn1GVUfa7Tp9kpisDeaT367UTW/Njc13Pbf1ip82o1zqm7Tm5T0jXbR8j56SDa8BsbZpAHdiFOpiee9cBxwMipcV2YPoxwCSMu3F31b4IcgPc7sbdxeDG3aicTic5q1CZy90Rx90ay9KP3/BDAECIP1H2Y4yPtxCAgPu0LuexfoIrAHjv51A/8QfeQgE3kjjuHizVwSYB3j0Azi+8Pv1AUn1+7z0auT5P+HtvXx0HW2w8AMJF9FqxcwDAEuuq/d9SecQ6Ib7jlwMg3ONljt1SfMCzBwAO/OMOa6UxLrFcN/1vzqzx3QFqY1/wgn0IQLj4rWrXyzV2v10IAOH+pRB2L8bfEwA84kvlpmTsVQmY42P0J5nvfG2fvhintZFH+5ypPMYaza/iD7yFgHB/Q2kWqKbVm+P1f5Zi/VKbFC4qU1Csn1K715gsGunKRB4mWJsfD9D1DLDXZsqy6deeA7zsQ8D5hd0Xk/vIMAzXpmQEqIPpoQ44HhC2A7vM3hdBCJk9s9q4a6c4UYmQzF1+4FvdY4gvj0DJibDc4SiPFpwdw5xhjQTv8AA439fnyRoH8wtQcR4XYmQH+bk1KxxKiuiOVR5sF1gsNvAAINzjIUtbPoQGwWGrOKdGsZNYqFccFO9ZYWsY7zh49wAAzq+d8WZZ+SwrvbLHL099dp4ybR85Vs8w6oxLPzSWveb9jfHcGw5/1bLtZjJntPd80DyfqJ99V+um15bSEnvYJwAEL3uEABz/FevO1PdGttFWHvu02vVylyyy4KG+z6b6rQGOj3j4KgCEePso23T88YYQgCgoxyStjfLsUbaxpCyBH/VJXh1rc7w2CvWL/+LUWJcvSKagfzv7ShM5G8nDhIvIrspIc+kJsddmyrLpk2+w3cCDA/+VI9plMVybkhGgDqaHOuB4QCZrB22+jmwNnp9n3OUzWeEQV9fX9bOc29bqyf8azp9L66p7dlcvX4SWT6bus1itDvk6s9sX0X5/W/3MfFh6+uifXsMHauTbsVi+0lb05Kftl+3hv7Pu81UXUn/2Xr7huHIu+6r7XHZTfyMwge/0hpj/Z+1ZqK7Cp5VaIQGaPpnu86mmog4Fhn7WnoRWOud9sVi2M6JXqMzqJ82lc5TSWPNCFGKHn0NV1WPLF8JZRMm5M0kZjnR9P0kbVfMpxisXhXOfarG38rRAdm5NIb22nZiEHlPJpL3sug7GLU/KQlvJ5ezm527V5GIsx6zdIz/zTBTOCKj3B5r2UahLuUzz++vdS0P/1mnbCirtRxkCTlmPnvatqltXWzK0F8VD3t2n+/p9ZST9LMxLqa/Ub9fKsaygM2WYzQpzHB/N523F0KSu9KOCbmoyNElrozwFLWGAFImV86tM767vls9NhJBONayUsu7moTK169c0hzAov5y2W85G8shCC7lpf+n70o90NN+jPxyg63mtX4a2u6Z1hmG+ue7T/3SEvpmXDiam59qUOrAIdTA91IHkeECstgOTUJlVX0fl3ESRn8Xccca47yRr8pLP5Jy+VJe2tXpL3ZuOTmh7dnNfRLdPJc+zmE7o+/U67298NFMflqY+eqfX8YGa+HYslq/ou7Tlp+2X1fC/1ZRo2eerQtVvW/Bp1mTVUo62v3FM3+mNM7sz7lQo40NfeOOuKUZ1xrBBNjXwUrqZxMq9IO2H+bpn51XWGQin3hnkg4a6ozCLLd+sw2Z7ajhPqqlerc5DFe0b0Opb9Muu62Dc8pLffVn3RzbI3jDvpg2C80Hi9TN9tO2jZQJicr/5xp3BIFgrV6FPRT2s2Xf1vpa49v3bRoONlg7DhrnsrpjWvlJx1TfX9Nt1YxvynVJ5iQNClcM8x8d+87ZI+o4o66bRIE3SWigvyjaIXOkWnAI6/b66vygsdFL7qC7KmudWdjfu2uun6hNM55g6cjaUR2N7FtJpX4F2192irue3funf7trXGbr5pvor9o101F4OblhMD3UwPdRBCseDu2aMjbvc8V+4qounhnXx2XdR3dToepm9Os9tOvu8aW3W4eDvWssN9EV0+1QU7URJx/P1WZPa8GEZ+1b6+WLs+XZ6+oJUfZeJ/DT8sv3Phrfo81Vm37JxN9CnmazpusvR9TeO6ju9cWZ3xl2N+ICf+xCY8hP/kesQH57PYcBy0nOFwjd8zPEz0UsT7rHZbJJr7yEEgPCEPw3CVZ8hk8YqFw6+P8SI48KFL3gU0AqNdY55rrKnNZ5cdRgMZb2ysHHhH5xD4McIgqByDTcivbLtoV3eco1lTVcP+NpSN105Znr6VTGG5e53Epu+xFD7sGVf7Qw7HymzTw/PLZ/hW7NvA6zb5/ILfjgOnPQSMz2XqYYQ6TMLJCYu8Pjd/hmUy/UrjjKC7zrJeWDeHpuHVS0Uw/2Mjxr9dvyB91NyXoZIDBLh/qc6JIZJ2sYq6ecRfzynY+weey8EAIjHr+jssDrnV05y9trrDrv1Guv1Dq/H6HJngzTWb4lvSQeD/cMK20Ois8Nhi9XiITn/UbsME11pymO5w1FKyMIV+S4EQnj7B3XIIu3qjqXrGdC33XXJRjff9OzRPqHJCSGEWITjAbFNGMLzvPL1rl6YVtfFy2/JGeFw/PJ54h3r5fravH0tXyebL1d8B+kxCOLHN0P7NPBFmPhUwjd8DPGX9bnfhg/L2LfSxxdj07dj0RfUQ351xvS/9ZebLkN9mliusba2JLu07/T2mPHGXYDtwx4hBNxoqrOHxq7D2cC/ouy0A4Y3ZpLi+CUHlox8uCJxYOnG1c5jlYceNg8PeChdG3jajros5vlXqCLcL5Og5wj77hAEL+dNyvx6SR166WB2sbMFLlVejDg44LDdYrvdYrt9wZuJ41RJu54a0/e2D1v2NS7L3S8kZ0P/hLrpjGzfF2ON3esrXtPr1+PU9bkQj7/SZz7iGPlwEMLbVOOXm7frxy+qWfgS6122gZeMd+Wy7mh8bO23kcSYf9jDgws/OuJ4PJbGsVIsfZO0jfUxy2O5OxbG2AiR7wDpZmzzEKs3v6qbTnY2iME5ib1or99yd0TkOxAihLdPdLZ/O+HRdeEI6I0dPXTVVx7L9Q5H3wFgcj6IIp8RdX3T9G53HbLRzjfAduMBjo+57osSQshNwPGAjEHVnyUlpK6Cs/OXLZCt5bXTZ76Dt4987pmcld7v3GZtX4SWT2WJ3W8Xjgix3zwkZ48dOs79Ktdm4P3DfVjG+jBMb9u3Y15+G0PlN6b/7Vp8YqY+zUuVcxu+T5v8M3UFxiHGYZUc9Oj4x/rOezr4hKe/iFH/GiBKrAbaZ//2qYMN4o+0c/Gw33jKJOHbB+Id35ayynKN3dHHn8UG3v4Fwc7AkSRc+L+/qTuk5XJ6Pa1fIeVrwz9TB1+Yfjk0emUvUF6wxWqTfkV5DQy1j2u3L6zx6jvwNh72LwF236euz1CSr7c8z8N78GrxraMZsEzeBvP2Hp4PT1jng6Buu8423rrG4iXWuyP8Pwtsinq4p/Gxtd9OHTBw4B9357FqucbuGAGrB+y9Zxye1tgtTdI2VWZoHsvki0ofWGT9RM2x0DW/ymws2RxeX1zBevO/5foVx3VVbwG2ewDOlw67NJGzJXk8fE23vG1hQ9dzoG+b6ZKNfr7fPpKvk8XpHdvt+zmL0wkAEL69YPsH+Pr9FTuOc+NwkbUpaYU6mJ671wHHAwK2gxLZWvsNH/EOu2WAdw+A86vnnFDTF6HrU1nu8Hrc4Sk44OV5D2+/gbcXcCPNOWvf+6/Nh3Vr2JTf1fvfZswdyX6GX9zFOKySUEOOLxveFGr7DDYdDPCoeDPZZh2GE3+8pW9VyfobNFLCT14nwcvo8aBIN2ebw3KJpeoyzEf1fkX8N5lUi5Fmcuvvw9+4v57yYhyePYQQcPyo9BVA/XNsU0w/tR9qH/3uP13m08ky66c0NNszDn+r/5zWvvtw6TZxS5zfanwpfXX3kHxyB6/tE59s4038QI8XKjk+ZsR/cQIa3mDLvrbqkdZGeTrUwlzoza8SG1OFp42RdCNjOTwGzv/S8D/l8K6qYszkPJ08DOip65unV5vRkE2PfMOwEkYrTF0a6e9vU8wZ7oax16akG+pgeu5cBxwPCIA5t4NsLa+OpqImWWunkVJ058mtGer5Ikx8Ksv1Dq9HmUSSQIi94QLT7H57PixTfZjrz65vp4/9KHKxJD9b/t32vK/CJzbgeCM9f+Olfae3x8w27ooTl6hlkd8QLxnnM3HgfO8Zjke3DkPJvkZoHrRzh/K48aDuk3TSoP85b7PNAQDiGHHp56bO6xzWqpZPHOBln7yB1Sd0gBbZRCfc46fyU/4YweFg7+ygUcvLPsn+gafSJwgR/vTKr0iTnmIE25+KM4xM7cPO/dXP6+ODqm62WWL3K52U7qtfQo1h36YTAUPWr/km0MPqAAtHQs6I9Dy5Shi+bEMP3kYdbjjOwv1UzteID1ittnUZx9ki1kGyjuP4mJOFt1Haf7Zpk8rJJC2Q6GOxwGJb6JuN8ohxWK1wqCs0DYMD4LH45Zn+/CoLIVLbUE/Pbem7IdzOwPlffMBq4wHCxdO68vsgOduQR4zDz6RNmp8pkt4/kq5vHtN2pysbg3zLIUwLVxoeVbiJY+M4z08er4Qx16ZED+pgeu5cBxwPCIC5tIN6OMrzWr6839CxVl9/T8NlvmCbfG6HIft2Xb6IIT6ZZVrXMvq+CPX9Vfr5sPT10S+9mv6+HTvlqzCVX7Nfdpj/rj3vSX2+OWdfjqoe9XVdHT1/46V9pzfI6XSSc8F3IAFICEc6juJy/WJq6aCSXqR/w5F+KedI+oV8BCABIUUh36hXHRKUOojcpBwhFPn4MvKdpBynnl/9GavPQ6oY6SC3EyHdqJA+1Yko/VjkbHPCcaXr+9L3Xek6Qnlf2ZaEFLmui7ab1U2kdVKU31ivSLqih31kcqm2AyHqcjEou7EvGqm8JPlZH77vS9d1CmVV5GIqx0K9haj0HUq5G9iHsi4m9pXV+dxXJXVqqFunbSvInr+hj8rtG5DlJLbsW1FWtS1ZaxsFeVbqXbRdE/FdK619pUrXTWNVY7tGs66L9+RjrMI2Zj4+ms7bIlec7dL1pe/70nezNl+Ws0naXM4VGernUWw3ojLHau6v9eZXirxVY0aaVneO16YDk/olaQtzi6Z5hRU5m8jDl46AFKV5z7mNQbgVWejKzr6u57R+MdGliZ2Z2YiCjvF1Tjq4DkzWpgnUgW2og+m5bx1wPCAJltpBq0/Rka7f5V8o1KW2rmpYL+fz1qIf5LwmVtlQs98re4zCXLR1fad49gG+iFafSuRKAZH7kXzfzXVULVL5fAb3N9XdxIelrY+e6XV8oCa+HWvlK/5vJD/ZZp9m/l119brz1pKb5rMP8WnW+qJWn5qhv3FM3+mNczqd5Iw27iqOU9VVdThEfq7Ys/Ow6pSQstRolFdmRD3qIDsG2YbyHMesU9cd3+4VMx2kA2yDc629k4ikX3LmJx2ZW8tMysyBpu6cI+lW8hFN+djeuGt6jlwu/QaG9r7Ifnn575U+wPGjtN0M3LiTUsqoosPUbpT5Nz2nSq8tddG2r8iXrhAl+2ms2wgbd8X2VU9iw75LidRtyXLbiHxXOqIq/4b+4kYx3rjLZanYkIhSexWqdt1QgXTcrtpGsR/QHfdudXzsM2/LbLO6EFK1Ke20Bb3XVKtdXlRyHOWLk9pmWZ/5Vb0/VNuW7hzv/Nx1HZjVL/Idhd3X5WhPzvryUOUJkYyNdUxkZ1fX81m/JOjp0rwdmNlIBTpqL4/22jSBOhgB6mB67lwHHA+IlNJOO2j1Keo69Ptt3Ak3klFpQ6RpLpmWofXypsGLsAN9Ed0+laF+PZP76/Xr48PS0seA9F31NfXtWCm/aVNJV35JJq1+2f567M5bW266z97bp1ntiwrrt6Y8TfyNeTn1depg3+mNczqd5OJ0Osl///0XZDo+Pz9BHUwLdTA91AEh1w/b6fRQB9NDHUwPdTA91MH0UAfTQx1MD3UwPdTB9FyNDoItFhsPwo0shlQNsF1s4AkX0XE3u/OrRsVUH6Poz4Cpyyfkyvj8/JzbGXeEEEIIIYQQQgghhBBCbprgHR76nrNMCCG3DTfuCCGEEEIIIYQQQgghhFwJMQ7PHgCBH9+4bUcIuT+4cUcIIYQQQgghhBBCCCHkOog/8BYCED/AfTtCyD3yz9QVIIQQQgghhBBCCCGEEHKjrF8h5au9/JY7HOXOXn73hqk+bOvPlKnLJ+QK4Rd3hBBCCCGEEEIIIYQQQgghhFwBi9PpJKeuBCGEEEIIIYQQQgghhBB9/ve//2/qKhBCCBmB/welKYAeUORWpgAAAABJRU5ErkJggg=="
    }
   },
   "cell_type": "markdown",
   "id": "4e10b399",
   "metadata": {},
   "source": [
    "### Exceptions\n",
    "For 2010 data there are a few instances where taking the month with the largest total_nests is not the used approach.\n",
    "We are encodign the exceptions here:\n",
    "\n",
    "- Breton Island, ROSA, we pick data from June\n",
    "- Do not include values of \"UNTE\", \"UNGT\",\"UNWA\",\"UNGU\",\"UNIB\",\"UNEG\" for \"Breton Island\",\"Cat Bay Island\",\"Martin Island\"\n",
    "- Martin Island, exclude ROSA counts\n",
    "- Drum Bay Island UNIB data not best for BPE\n",
    "- Photos for BLSK in Martin Island for May 18 are not best for BPE\n",
    "\n",
    "\n",
    "![image.png](attachment:image.png)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "ee641473",
   "metadata": {},
   "outputs": [],
   "source": [
    "# Exceptions\n",
    "agg_2010[\"Exception\"] = 0\n",
    "agg_2010.loc[(agg_2010[\"ColonyName\"]==\"Breton Island\") & (agg_2010.SpeciesCode == \"ROSA\") & (agg_2010.month == \"Jun\"), \"Exception\"] =1\n",
    "#agg_2010.loc[agg_2010.SpeciesCode.isin([\"UNTE\", \"UNGT\",\"UNWA\",\"UNGU\",\"UNIB\",\"UNEG\"]) & (agg_2010.month == \"Jun\"), \"Exception\"] = 1\n",
    "agg_2010.loc[(agg_2010.SpeciesCode==\"BLSK\") & (agg_2010.ColonyName==\"Martin Island\") & (agg_2010.date2 == \"2010-May-18\"), \"Exception\"] = -1\n",
    "agg_2010.loc[(agg_2010.SpeciesCode==\"ROSA\") & (agg_2010.ColonyName==\"Martin Island\"), \"Exception\"] = -1\n",
    "agg_2010.loc[(agg_2010.SpeciesCode==\"UNWA\") & (agg_2010.ColonyName==\"Drum Bay Island\") & (agg_2010.month==\"Jun\"), \"Exception\"] = -1\n",
    "agg_2010.loc[agg_2010.SpeciesCode.isin([\"UNTE\", \"UNGT\",\"UNWA\",\"UNGU\",\"UNIB\",\"UNEG\"]) & agg_2010.ColonyName.isin([\"Breton Island\",\"Cat Bay Island\",\"Martin Island\"]), \"Exception\"] = -1\n",
    "agg_2010.loc[(agg_2010.SpeciesCode==\"UNIB\") & (agg_2010.ColonyName==\"Drum Bay Island\"), \"Exception\"] = -1\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "4e45469c",
   "metadata": {},
   "outputs": [],
   "source": [
    "agg_2010.loc[agg_2010.SpeciesCode.isin([\"UNTE\", \"UNGT\",\"UNWA\",\"UNGU\",\"UNIB\",\"UNEG\"]) & agg_2010.ColonyName.isin([\"Breton Island\",\"Cat Bay Island\",\"Martin Island\"])]"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "790246b9",
   "metadata": {},
   "outputs": [],
   "source": [
    "selected = (\n",
    "    agg_2010[agg_2010.Exception>-1].sort_values([\"Exception\", order_by_field, \"total_nests\" if order_by_field==\"total_birds\" else \"total_birds\"])\n",
    "    .drop_duplicates([\"Year\", \"ColonyName\", \"SpeciesCode\"], keep=\"last\")[\n",
    "        [\"Year\", \"month\", 'date2',\"ColonyName\", \"SpeciesCode\"]\n",
    "    ]\n",
    "    .reset_index(drop=True)\n",
    ")\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "2676ff28",
   "metadata": {},
   "outputs": [],
   "source": [
    "agg_2010[(agg_2010.Exception>-1) & agg_2010.SpeciesCode.isin([\"UNTE\", \"UNGT\",\"UNWA\"])]"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "90de1bc5",
   "metadata": {},
   "outputs": [],
   "source": [
    "pd_species.loc[\n",
    "    pd_species[[\"Year\", \"month\",'date2', \"ColonyName\", \"SpeciesCode\"]]\n",
    "    .apply(lambda row: str([x for x in row]), axis=1)\n",
    "    .isin(selected.apply(lambda row: str([x for x in row]), axis=1)),\n",
    "    \"BestForBPE\",\n",
    "] = \"Y\"\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "2431145a",
   "metadata": {},
   "outputs": [],
   "source": [
    "pd_species[\"uid\"] = (\n",
    "    pd_species[\"HighResImage_new\"]\n",
    "    + \"#\"\n",
    "    + np.where(pd_species[\"SpeciesCode\"].isna(), \"N/A\", pd_species[\"SpeciesCode\"])\n",
    ")\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "884a2186",
   "metadata": {},
   "outputs": [],
   "source": [
    "pd_species[\"SpeciesCode\"] = pd_species[\"SpeciesCode\"].str.upper()\n",
    "# For 2021 REEG data is divided in subspecies on the access database, but reported as REEG in the summary.\n",
    "pd_species.loc[\n",
    "    (pd_species.Year == \"2021\") & pd_species.SpeciesCode.str.startswith(\"REEG \"),\n",
    "    \"SpeciesCode\",\n",
    "] = \"REEG\"\n",
    "\n",
    "pd_species.loc[pd_species.SpeciesCode==\"COGA\",\"SpeciesCode\"] = \"COMO\"\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "24561418",
   "metadata": {},
   "outputs": [],
   "source": [
    "pd_species.to_excel(\"avianmonitoring_2010-2021.xlsx\", index=False)\n"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "7d0ad175",
   "metadata": {},
   "source": [
    "# Reorganize Files\n",
    "Photos from 2010 to 2021 originally are not organized by GeoRegion/Colony and can have different naming standard.\n",
    "We are using the results of the dotting process to organize the images referenced from the Access database. \n",
    "\n",
    "We are also generating a thumbnail and reorganizing and renaming the screenshots from the dotting process. \n",
    "\n",
    "Note: This will be a subset of the photos, you can see all the high resolution photos available on the HighResolutionImages folder in the S3 bucket. "
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "6527b3b5",
   "metadata": {},
   "outputs": [],
   "source": [
    "pd_species[pd_species[\"HighResImage_new\"].isna()]\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "369d81b9",
   "metadata": {},
   "outputs": [],
   "source": [
    "files = [\n",
    "    o\n",
    "    for o in avian_data.objects.filter(Prefix=starting_folder)\n",
    "    if re.match(r\".*/20[1-2][0-9]/.*(\\.jp(.{0,1})g|.tiff)\", o.key.lower())\n",
    "] if rename_files or create_thumbnails else []\n"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "ac9aac25",
   "metadata": {},
   "source": [
    "We will need to obtain the photo information from the HighResolution photo path. The file naming patter varies, even in the same year, but we can define a regular expression general enough to catch all the cases for most of the information. "
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "de57fb3b",
   "metadata": {},
   "outputs": [],
   "source": [
    "image_groups = re.compile(\n",
    "    r\"HighResolutionImages/(?P<year>\\d{4}).*[/ ,]+(?P<date>\\d+\\s*[A-Z]+\\s*\\d{2,4}).*(Camera|Cam)\\s*(?P<camera>\\d+)[ /-]*(Card\\s*(?P<card>\\d+)){0,1}[\\s-]*((\\w+/))*(IMGP){0,1}(?P<photo>\\d+)\\.(?P<extension>jp.?g|tiff?)\",\n",
    "    flags=re.IGNORECASE,\n",
    ")\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "618b8f57",
   "metadata": {},
   "outputs": [],
   "source": [
    "m = image_groups.match(\n",
    "    \"HighResolutionImages/2010/June 2010/10 June 2010/10 June 2010 Camera 1 Card 1/10 June 2010 Camera 1 Card 1 010.JPG\"\n",
    ")\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "7405642c",
   "metadata": {},
   "outputs": [],
   "source": [
    "df_files = []\n",
    "no_files = []\n",
    "for i, o in tqdm(enumerate(files)):\n",
    "    if image_groups.match(o.key):\n",
    "        dict_t = image_groups.match(o.key).groupdict()\n",
    "        df_files.append({\"key\": o.key, \"object\": o, **dict_t})\n",
    "    else:\n",
    "        no_files.append(o.key)\n"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "c39e3868",
   "metadata": {},
   "source": [
    "There are a few files with not enough information:"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "1216bab9",
   "metadata": {},
   "outputs": [],
   "source": [
    "no_files\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "03d931d8",
   "metadata": {},
   "outputs": [],
   "source": [
    "no_files = list(filter(lambda k: not \"numbering off\" in k, no_files))\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "6af26c2c",
   "metadata": {},
   "outputs": [],
   "source": [
    "with open(\"n_files_report.txt\", \"w\") as n_files_report:\n",
    "    n_files_report.write(\"\\n\".join(no_files))\n"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "c7102a54",
   "metadata": {},
   "source": [
    "Create a dataframe from the collected information, and merge it with the species dataset to be able to rearrange/rename the photos to the desire location based on the dotting information. "
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "cfccbd41",
   "metadata": {},
   "outputs": [],
   "source": [
    "if df_files:\n",
    "    fdf = pd.DataFrame(df_files)\n",
    "    fdf[\"extension\"] = fdf[\"extension\"].str.lower()\n",
    "    fdf[\"date2\"] = fdf[\"date\"].astype(\"str\").apply(clean_date)\n",
    "    fdf[\"month\"] = fdf[\"date2\"].apply(lambda x: x.split(\"-\")[1])\n",
    "    fdf[\"day\"] = fdf[\"date2\"].apply(lambda x: x.split(\"-\")[2])\n",
    "    pd_species[\"date2\"] = pd_species[\"Date\"].astype(\"str\").apply(clean_date)\n",
    "    pd_species[\"month\"] = pd_species[\"date2\"].apply(lambda x: x.split(\"-\")[1])\n",
    "    pd_species[\"day\"] = pd_species[\"date2\"].apply(lambda x: x.split(\"-\")[2])\n",
    "    pd_species[~pd_species[\"HighResImage_new\"].isna()].to_csv(\n",
    "        \"avianData20102021.csv.gz\", index=False\n",
    "    )\n",
    "    fdf = fdf.rename(\n",
    "        columns={\n",
    "            \"camera\": \"CameraNumber\",\n",
    "            \"card\": \"CardNumber\",\n",
    "            \"photo\": \"PhotoNumber\",\n",
    "            \"year\": \"Year\",\n",
    "        }\n",
    "    )\n",
    "    fdf[\"PhotoNumber\"] = fdf[\"PhotoNumber\"].str.rjust(5, \"0\")\n",
    "    pd_species[\"PhotoNumber\"] = pd_species[\"PhotoNumber\"].str.rjust(5, \"0\")\n",
    "    join_cols = [\"CameraNumber\", \"CardNumber\", \"PhotoNumber\", \"Year\", \"month\", \"day\"]\n",
    "    for c in join_cols:\n",
    "        fdf[c] = fdf[c].astype(\"str\").str.strip()\n",
    "        pd_species[c] = pd_species[c].astype(\"str\").str.strip()\n",
    "\n",
    "    c = \"CardNumber\"\n",
    "    fdf.loc[fdf[c] == \"None\", c] = (\n",
    "        fdf.loc[fdf[c] == \"None\", \"key\"]\n",
    "        .str.extract(r\".*Card\\s*(\\d+).*\", flags=re.IGNORECASE, expand=False)\n",
    "        .where(fdf.loc[fdf[c] == \"None\", \"key\"].str.match(r\".*Card\\s(\\d+).*\", False), 1)\n",
    "    )\n",
    "    merged = pd_species.merge(fdf, on=join_cols, how=\"left\")\n",
    "    m_grouped = (\n",
    "        merged[\n",
    "            [\n",
    "                \"HighResImage_new\",\n",
    "                \"key\",\n",
    "                \"CameraNumber\",\n",
    "                \"CardNumber\",\n",
    "                \"PhotoNumber\",\n",
    "                \"Date\",\n",
    "                \"Year\",\n",
    "                \"month\",\n",
    "                \"thumbnail_new\",\n",
    "            ]\n",
    "        ]\n",
    "        .groupby(\n",
    "            [\n",
    "                \"HighResImage_new\",\n",
    "                \"key\",\n",
    "                \"CameraNumber\",\n",
    "                \"CardNumber\",\n",
    "                \"PhotoNumber\",\n",
    "                \"Date\",\n",
    "                \"Year\",\n",
    "                \"month\",\n",
    "                \"thumbnail_new\",\n",
    "            ]\n",
    "        )\n",
    "        .count()\n",
    "        .reset_index()\n",
    "    )\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "45afd8a8",
   "metadata": {
    "scrolled": true
   },
   "outputs": [],
   "source": [
    "# r = m_grouped.progress_apply(lambda x:avian_data.copy ({'Bucket': avian_data.name,'Key':x['key']}, x['HighResImage_new']), axis=1)\n",
    "if rename_files:\n",
    "    with ThreadPoolExecutor(max_workers=16) as e:\n",
    "        futures = list(\n",
    "            tqdm(\n",
    "                e.map(\n",
    "                    lambda x, y: rename(x, y),\n",
    "                    m_grouped[\"key\"].tolist(),\n",
    "                    m_grouped[\"HighResImage_new\"].tolist(),\n",
    "                ),\n",
    "                total=m_grouped.shape[0],\n",
    "            )\n",
    "        )\n",
    "    with pd.option_context(\"display.max_colwidth\", None):\n",
    "        display(\n",
    "            m_grouped[\n",
    "                m_grouped[\"HighResImage_new\"]\n",
    "                == \"avian_monitoring/high_resolution_photos/2021/Birdsfoot West/Birdsfoot West 6 A/22May21Camera1-Card2-3297.jpg\"\n",
    "            ]\n",
    "        )\n",
    "        m_grouped.to_excel(\"UsedPhotos2010-2021.xlsx\", index=False)\n",
    "\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "4c753f2f",
   "metadata": {
    "scrolled": true
   },
   "outputs": [],
   "source": [
    "display(pd_species[\n",
    "    pd_species[\"HighResImage_new\"]\n",
    "    == \"avian_monitoring/high_resolution_photos/2021/Biloxi North/Biloxi North 4/16June21Camera2-Card2-6268.jpg\"\n",
    "]\n",
    ")\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "f86ec0a1",
   "metadata": {},
   "outputs": [],
   "source": [
    "if create_thumbnails:\n",
    "    with ThreadPoolExecutor(max_workers=16) as e:\n",
    "        futures = list(\n",
    "            tqdm(\n",
    "                e.map(\n",
    "                    partial(generate_thumbnail, regenerate=False),\n",
    "                    m_grouped[\"HighResImage_new\"].tolist(),\n",
    "                    m_grouped[\"thumbnail_new\"].tolist(),\n",
    "                ),\n",
    "                total=m_grouped.shape[0],\n",
    "            )\n",
    "        )\n"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "5a4375db",
   "metadata": {},
   "source": [
    "# Previous version code\n",
    "```python\n",
    "#acc_db = \"/mnt/c/Users/carizaporras/Downloads/Colibri_tblSpeciesData2015_2018_2021_2Sept2022 (1).accdb\"\n",
    "#schema = pandas_access.read_schema(acc_db)\n",
    "#pd_species = pandas_access.read_table(acc_db, \"tblSpeciesData2015_2018_2021\")\n",
    "#pd_species[\"ColonyName\"] = pd_species[\"ColonyName\"].astype(str)\n",
    "#pd_colonies = pandas_access.read_table(acc_db, \"tblRWCWB_ColonyInventory_2022\")\n",
    "#pd_colonies[\"ColonyName\"] = pd_colonies[\"ColonyName\"].astype(str)\n",
    "#pd_species = pd.merge(pd_species, pd_colonies, on=\"ColonyName\")\n",
    "#pd_species[\"HighResImage_new\"] = f\"{_base_folder}/high_resolution_photos/\"+pd_species['Year'].astype(str)+'/'+pd_species['GeoRegion']+'/'+pd_species['ColonyName']+'/'+pd.to_datetime(pd_species[\"Date\"].astype('str'), format=\"%m/%d/%y %H:%M:%S\").dt.strftime(\"%d%B%y\")+'Camera'+pd_species[\"CameraNumber\"]+'-'+'Card'+(pd_species[\"CardNumber\"].where(~pd_species[\"CardNumber\"].isnull(), other=\"1\"))+\"-\"+pd_species[\"PhotoNumber\"]+\".jpg\"\n",
    "#pd_species[\"screenshot_new\"] = f'{_base_folder}/screenshots/'+pd_species['Year'].astype(str)+'/'+pd_species['GeoRegion']+'/'+pd_species['ColonyName']+'/'+pd.to_datetime(pd_species[\"Date\"].astype('str'), format=\"%m/%d/%y %H:%M:%S\").dt.strftime(\"%d%B%y\")+'Camera'+pd_species[\"CameraNumber\"]+'-'+'Card'+(pd_species[\"CardNumber\"].where(~pd_species[\"CardNumber\"].isnull(), other=\"1\"))+\"-\"+pd_species[\"PhotoNumber\"]+\".jpg\"\n",
    "#pd_species[\"thumbnail_new\"] =  f'{_base_folder}/thumbnails/'+pd_species['Year'].astype(str)+'/'+pd_species['GeoRegion']+'/'+pd_species['ColonyName']+'/'+pd.to_datetime(pd_species[\"Date\"].astype('str'), format=\"%m/%d/%y %H:%M:%S\").dt.strftime(\"%d%B%y\")+'Camera'+pd_species[\"CameraNumber\"]+'-'+'Card'+(pd_species[\"CardNumber\"].where(~pd_species[\"CardNumber\"].isnull(), other=\"1\"))+\"-\"+pd_species[\"PhotoNumber\"]+\".png\"\n",
    "#gdf = gp.GeoDataFrame(\n",
    "#    pd_species, geometry=gp.points_from_xy(pd_species[\"Longitude\"], pd_species[\"Latitude\"]))\n",
    "#set_index([\"State\",\"GeoRegion\",\"ColonyName\",\"Year\", \"Latitude\", \"Longitude\", \"Date\", \"SpeciesCode\"])\n",
    "#pd_species = pd_species.drop(columns=[\"AutoID\",\"Subcolony\"])\n",
    "#gdf[\"huc\"] = \"TBD\"\n",
    "\n",
    "def _convert_to_degress(value):\n",
    "    \"\"\"\n",
    "    Helper function to convert the GPS coordinates stored in the EXIF to degress in float format\n",
    "    Borrowed from: https://gist.github.com/snakeye/fdc372dbf11370fe29eb\n",
    "    Modified to recieve a tuple instead of a exifread.utils.Ratio\n",
    "    :param value:\n",
    "    :type value: tuple\n",
    "    :rtype: float\n",
    "    \"\"\"\n",
    "    d = float(value[0][0]) / float(value[0][1])\n",
    "    m = float(value[1][0]) / float(value[1][1])\n",
    "    s = float(value[2][0]) / float(value[2][1])\n",
    "\n",
    "    return d + (m / 60.0) + (s / 3600.0)\n",
    "```"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "320e71af",
   "metadata": {},
   "source": [
    "# UPDATE Mime types "
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "33cd5233",
   "metadata": {},
   "outputs": [],
   "source": [
    "def update_mime_type(t, mime=\"image/png\"):\n",
    "    s3_object = avian_data.Object(t)\n",
    "    s3_object.copy_from(\n",
    "        CopySource={\"Bucket\": avian_data.name, \"Key\": t},\n",
    "        Metadata=s3_object.metadata,\n",
    "        MetadataDirective=\"REPLACE\",\n",
    "        ContentType=mime,\n",
    "    )\n",
    "\n",
    "\n",
    "def copy_wnew_mime_type(t, k, mime=\"image/png\"):\n",
    "    avian_data.copy(\n",
    "        {\"Bucket\": avian_data.name, \"Key\": t},\n",
    "        k,\n",
    "        Metadata=s3_object.metadata,\n",
    "        MetadataDirective=\"REPLACE\",\n",
    "        ContentType=mime,\n",
    "    )\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "99084b2a",
   "metadata": {},
   "outputs": [],
   "source": [
    "if replace and create_thumbnails:\n",
    "    m_grouped[\"thumbnail_new\"].progress_apply(lambda t: update_mime_type(t))\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "c87814aa",
   "metadata": {},
   "outputs": [],
   "source": [
    "if rename_files:\n",
    "    with ThreadPoolExecutor(max_workers=16) as e:\n",
    "        futures = list(\n",
    "            tqdm(\n",
    "                e.map(\n",
    "                    lambda t: update_mime_type(t, mime=\"image/jpeg\"),\n",
    "                    m_grouped[\"HighResImage_new\"].tolist(),\n",
    "                ),\n",
    "                total=m_grouped.shape[0],\n",
    "            )\n",
    "        )\n"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "df1066ff",
   "metadata": {},
   "source": [
    "# Reorganize/rename screenshots"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "0afeeda2",
   "metadata": {},
   "outputs": [],
   "source": [
    "m_grouped = pd.read_excel(\"UsedPhotos2010-2021.xlsx\")\n",
    "# to_rename = pd.read_excel(\"toRename2.xlsx\")\n",
    "to_rename = pd.read_excel(\"Renaming_Avian.xlsx\")\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "638a9d72",
   "metadata": {},
   "outputs": [],
   "source": [
    "if not \"Original_Path\" in to_rename:\n",
    "    to_rename[\"Original_Path\"] = to_rename[\"Path\"]\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "e6b839c9",
   "metadata": {},
   "outputs": [],
   "source": [
    "to_rename = to_rename[[\"HighResImage_new\", \"Original_Path\"]]\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "20407b59",
   "metadata": {},
   "outputs": [],
   "source": [
    "to_rename = to_rename[\n",
    "    ~(to_rename[\"Original_Path\"].isna() | to_rename[\"Original_Path\"].isna())\n",
    "]\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "1d1a94e0",
   "metadata": {},
   "outputs": [],
   "source": [
    "to_rename[\"Original_Path\"] = to_rename[\"Original_Path\"].str.replace(\n",
    "    \"/to82sp\", \"DottedImages\"\n",
    ")\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "cc37a652",
   "metadata": {},
   "outputs": [],
   "source": [
    "to_rename = to_rename.merge(m_grouped, on=\"HighResImage_new\")\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "0c5b3a3c",
   "metadata": {},
   "outputs": [],
   "source": [
    "to_rename[\"screenshot_new\"] = to_rename[\"HighResImage_new\"].str.replace(\n",
    "    \"/high_resolution_photos/\", \"/screenshots/\"\n",
    ")\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "1399b14f",
   "metadata": {},
   "outputs": [],
   "source": [
    "to_rename[[\"screenshot_new\", \"Original_Path\"]]\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "d047a721",
   "metadata": {},
   "outputs": [],
   "source": [
    "if rename_files:\n",
    "    with ThreadPoolExecutor(max_workers=16) as e:\n",
    "        futures = list(\n",
    "            tqdm(\n",
    "                e.map(\n",
    "                    lambda x, y: rename(x, y),\n",
    "                    to_rename[\"Original_Path\"].tolist(),\n",
    "                    to_rename[\"screenshot_new\"].tolist(),\n",
    "                ),\n",
    "                total=to_rename.shape[0],\n",
    "            )\n",
    "        )\n"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "bccf3bd0",
   "metadata": {},
   "source": [
    "# Join summary excel with locations from colonies"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "d330ca4f",
   "metadata": {},
   "outputs": [],
   "source": [
    "pd_species = pd.read_excel(\"avianmonitoring_2010-2021.xlsx\")\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "2bf6f1cf",
   "metadata": {},
   "outputs": [],
   "source": [
    "totals = pd.read_excel(\"/mnt/z/Colibri2010-21ColonyTotalsMayJuneCombined_8Nov22.xlsx\")\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "ee9ab360",
   "metadata": {},
   "outputs": [],
   "source": [
    "totals\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "a9829ea6",
   "metadata": {},
   "outputs": [],
   "source": [
    "unique_colonies = (\n",
    "    pd_species[\n",
    "        [\"Year\", \"State\", \"GeoRegion\", \"ColonyName\", \"Longitude_y\", \"Latitude_y\"]\n",
    "    ]\n",
    "    .drop_duplicates()\n",
    "    .reset_index(drop=True)\n",
    ")\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "16c89d97",
   "metadata": {},
   "outputs": [],
   "source": [
    "join_cols = [\"Year\", \"State\", \"GeoRegion\", \"ColonyName\"]\n",
    "for c in join_cols:\n",
    "    totals[c] = totals[c].astype(\"str\").str.strip()\n",
    "    unique_colonies[c] = unique_colonies[c].astype(\"str\").str.strip()\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "4285db37",
   "metadata": {},
   "outputs": [],
   "source": [
    "joined_totals = pd.merge(totals, unique_colonies, on=join_cols)\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "0a656279",
   "metadata": {},
   "outputs": [],
   "source": [
    "joined_totals\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "218e4765",
   "metadata": {},
   "outputs": [],
   "source": [
    "joined_totals.to_excel(\"joined_totals.xlsx\", index=False)\n"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "0ffd5121",
   "metadata": {},
   "source": [
    "# Compare totals from totals and pd_species"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "1f6861ab",
   "metadata": {},
   "outputs": [],
   "source": [
    "joined_totals = pd.read_excel(\"joined_totals.xlsx\")\n",
    "joined_totals[\"Date\"] = joined_totals[\"Date\"].astype(\"str\").apply(clean_date)\n",
    "pd_species[\"Date\"] =  pd_species[\"Date\"].astype(\"str\").apply(clean_date)\n",
    "pd_species.columns\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "2d31e5b2",
   "metadata": {},
   "outputs": [],
   "source": [
    "join_cols = [\n",
    "    \"Year\",\n",
    "    \"State\",\n",
    "    \"GeoRegion\",\n",
    "    \"ColonyName\",\n",
    "    \"SpeciesCode\",\n",
    "    \"Longitude_y\",\n",
    "    \"Latitude_y\",\n",
    "]\n",
    "for c in join_cols:\n",
    "    pd_species[c] = pd_species[c].astype(\"str\").str.strip()\n",
    "    joined_totals[c] = joined_totals[c].astype(\"str\").str.strip()\n",
    "\n",
    "pd_species[\"SpeciesCode\"] = pd_species[\"SpeciesCode\"].str.upper()\n",
    "joined_totals[\"SpeciesCode\"] = joined_totals[\"SpeciesCode\"].str.upper()\n",
    "# For 2021 REEG data is divided in subspecies on the access database, but reported as REEG in the summary.\n",
    "pd_species.loc[\n",
    "    (pd_species.Year == \"2021\") & pd_species.SpeciesCode.str.startswith(\"REEG \"),\n",
    "    \"SpeciesCode\",\n",
    "] = \"REEG\"\n",
    "\n",
    "pd_species.loc[pd_species.SpeciesCode==\"COGA\",\"SpeciesCode\"] = \"COMO\"\n",
    "joined_totals.loc[joined_totals.SpeciesCode==\"COGA\",\"SpeciesCode\"] = \"COMO\"\n",
    "\n",
    "agg_pd_species = (\n",
    "    pd_species.loc[\n",
    "        pd_species.BestForBPE == \"Y\",\n",
    "        [\n",
    "            \"Year\",\n",
    "            \"Date\",\n",
    "            \"State\",\n",
    "            \"GeoRegion\",\n",
    "            \"ColonyName\",\n",
    "            \"SpeciesCode\",\n",
    "            \"Longitude_y\",\n",
    "            \"Latitude_y\",\n",
    "            \"total_nests\",\n",
    "            \"total_birds\",\n",
    "        ],\n",
    "    ]\n",
    "    .groupby(\n",
    "        [\n",
    "            \"Year\",\n",
    "            \"Date\",\n",
    "            \"State\",\n",
    "            \"GeoRegion\",\n",
    "            \"ColonyName\",\n",
    "            \"SpeciesCode\",\n",
    "            \"Longitude_y\",\n",
    "            \"Latitude_y\",\n",
    "        ]\n",
    "    )\n",
    "    .sum()\n",
    ")\n",
    "\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "bc584ce5",
   "metadata": {},
   "outputs": [],
   "source": [
    "agg_totals = joined_totals.groupby(\n",
    "    [\n",
    "        \"Year\",\n",
    "        \"State\",\n",
    "        \"GeoRegion\",\n",
    "        \"ColonyName\",\n",
    "        \"SpeciesCode\",\n",
    "        \"Longitude_y\",\n",
    "        \"Latitude_y\",\n",
    "    ]\n",
    ").sum()\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "4203ae35",
   "metadata": {},
   "outputs": [],
   "source": [
    "joined_totals = agg_totals.join(agg_pd_species.groupby(    [\n",
    "        \"Year\",\n",
    "        \"State\",\n",
    "        \"GeoRegion\",\n",
    "        \"ColonyName\",\n",
    "        \"SpeciesCode\",\n",
    "        \"Longitude_y\",\n",
    "        \"Latitude_y\",\n",
    "    ]).sum(), on=join_cols, how=\"outer\").reset_index()\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "93e77364",
   "metadata": {
    "scrolled": false
   },
   "outputs": [],
   "source": [
    "joined_totals[joined_totals.Nests.isna() ]\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "39496882",
   "metadata": {
    "scrolled": true
   },
   "outputs": [],
   "source": [
    "joined_totals[\"diff_nests\"] = joined_totals[\"Nests\"].fillna(0) - joined_totals[\n",
    "    \"total_nests\"\n",
    "].fillna(0)\n",
    "joined_totals[\"diff_birds\"] = joined_totals[\"Birds\"].fillna(0) - joined_totals[\n",
    "    \"total_birds\"\n",
    "].fillna(0)\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "1d3e1e9b",
   "metadata": {},
   "outputs": [],
   "source": [
    "joined_totals[\n",
    "    ((joined_totals[\"diff_nests\"] != 0) | (joined_totals[\"diff_birds\"] != 0))\n",
    "   # & (joined_totals[\"SpeciesCode\"] == \"ROSA\")\n",
    "].sort_values(\n",
    "#    [\"Year\",\"SpeciesCode\",\"State\",\"GeoRegion\",\"ColonyName\"]\n",
    "    \"diff_birds\"\n",
    ").to_excel(\"/mnt/z/differences_with_previous_summary.xlsx\")\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "52f1c43a",
   "metadata": {},
   "outputs": [],
   "source": [
    "#joined_totals.to_excel(\"/mnt/z/joined_totals.xlsx\")\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "b8363595",
   "metadata": {},
   "outputs": [],
   "source": [
    "pd_species.loc[\n",
    "    (pd_species.Year == \"2021\") & pd_species.SpeciesCode.str.startswith(\"REEG \"),\n",
    "    \"SpeciesCode\",\n",
    "]\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "3ed5c2e5",
   "metadata": {},
   "outputs": [],
   "source": [
    "agg_pd_species.reset_index().rename(columns={\"total_nests\": \"Nests\", \"total_birds\": \"Birds\"}).to_excel(\"SummaryFileGenerated.xlsx\", index=False)"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "c40e6229",
   "metadata": {},
   "source": [
    "# Check existing files"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "fd2e110c",
   "metadata": {},
   "outputs": [],
   "source": [
    "pd_species = pd.read_excel(\"avianmonitoring_2010-2021.xlsx\")\n",
    "pd_species = pd_species.drop(columns=[\"Latitude_x\", \"Longitude_x\"]).drop_duplicates()\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "3aa12b89",
   "metadata": {},
   "outputs": [],
   "source": [
    "print(pd_species[\"thumbnail_new\"][0])\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "a4a950c3",
   "metadata": {},
   "outputs": [],
   "source": [
    "def exists_key(key, replace=False):\n",
    "    try:\n",
    "        return len(list(avian_data.objects.filter(Prefix=key))) > 0\n",
    "    except Exception as e:\n",
    "        print(f\"key:{key}, exception {e}\")\n",
    "        return False\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "3c9ad18c",
   "metadata": {},
   "outputs": [],
   "source": [
    "# This is equivalent, but faster than: pd_species[\"thumbnail_new\"] = pd_species[\"thumbnail_new\"].progress_apply(exists_key)\n",
    "with ThreadPoolExecutor(max_workers=128) as e:\n",
    "    futures = {}\n",
    "    _grouped = pd_species[\n",
    "        filter(lambda x: \"_new\" in x, pd_species.columns)\n",
    "    ].drop_duplicates()\n",
    "    for f in _grouped.columns:\n",
    "        futures[f] = list(\n",
    "            tqdm(\n",
    "                e.map(lambda y: exists_key(y), _grouped[f].tolist()),\n",
    "                total=_grouped.shape[0],\n",
    "            )\n",
    "        )"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "f21fa4d9",
   "metadata": {},
   "outputs": [],
   "source": [
    "for x in futures:\n",
    "    with pd.option_context(\"display.max_colwidth\", None):\n",
    "        display(_grouped.loc[~np.array(futures[x]), x])\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "d13ef54d",
   "metadata": {},
   "outputs": [],
   "source": [
    "x = \"HighResImage_new\"\n",
    "pd_species[pd_species[x].isin(_grouped.loc[~np.array(futures[x]), x])]\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "955f4bad",
   "metadata": {},
   "outputs": [],
   "source": [
    "for x in futures:\n",
    "    print(x, np.sum(~np.array(futures[x])))\n",
    "    with pd.option_context(\"display.max_colwidth\", None):\n",
    "        _no_exists = pd_species[\n",
    "            pd_species[x].isin(_grouped.loc[~np.array(futures[x]), x])\n",
    "        ]\n",
    "        display(_no_exists)\n",
    "        display(_no_exists[[\"Year\", x]].drop_duplicates().groupby([\"Year\"]).count())\n",
    "        pd_species.loc[\n",
    "            pd_species[x].isin(_grouped.loc[~np.array(futures[x]), x]), x\n",
    "        ] = None\n",
    "#   with pd.option_context('display.max_colwidth', None):\n",
    "#       display(pd_species.loc[~np.array(futures[x]), x].drop_duplicates())\n",
    "#       pd_species.loc[~np.array(futures[x]), x].drop_duplicates().to_csv(f\"not_found_{x}.csv\", index=False)\n",
    "\n",
    "#   display(pd_species.loc[~np.array(futures[x]),[x,\"Year\"]].drop_duplicates().groupby(\"Year\").count())\n",
    "#   pd_species.loc[~np.array(futures[x]), x] = None\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "7e18d1f7",
   "metadata": {},
   "outputs": [],
   "source": [
    "pd_species.to_csv(\"avianmonitoring_2010-2021_Nulls.csv.gz\", index=False)\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "a44496b3",
   "metadata": {},
   "outputs": [],
   "source": [
    "pd_species.to_excel(\"avianmonitoring_2010-2021_Nulls.xlsx\", index=False)\n"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "f4ace44d",
   "metadata": {},
   "source": [
    "### Delete inconsistent photos\n",
    "In some previous processing it seems some photos have been incorrectly created. This cell allows to display photos with high resolution images but without thumbnail, that will indicate an annomalus situation. "
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "2f8030ff",
   "metadata": {},
   "outputs": [],
   "source": [
    "with pd.option_context(\"display.max_colwidth\", None):\n",
    "    to_delete = pd_species.loc[\n",
    "        pd_species[\"thumbnail_new\"].isna() & ~pd_species[\"HighResImage_new\"].isna(),\n",
    "        futures.keys(),\n",
    "    ].drop_duplicates()\n",
    "    display(to_delete)\n",
    "    # to_delete[\"HighResImage_new\"].apply(lambda x:avian_data.Object(key=x).delete())\n"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "b9fcab17",
   "metadata": {},
   "source": [
    "## Update list of files on AWS (for file browsing)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "78c52402",
   "metadata": {},
   "outputs": [],
   "source": [
    "files_in_bucket = list(avian_data.objects.all())\n",
    "tree = {}\n",
    "filenames = [f.key for f in files_in_bucket if not f.key.endswith(\"/\")]\n",
    "for file in filenames:\n",
    "    parent = tree\n",
    "    filepath = file.split(\"/\")\n",
    "    for p in filepath:\n",
    "        level = parent.get(p, {})\n",
    "        parent[p] = level\n",
    "        parent = parent[p]"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "cbc917b8",
   "metadata": {},
   "outputs": [],
   "source": [
    "def createListings(tree, path):\n",
    "    \"\"\"Recursive method to list all files and folders in a tree,\n",
    "    with the format required by jquery browse files\"\"\"\n",
    "    dirs = []\n",
    "    files = []\n",
    "    listings = {}\n",
    "    for e in tree:\n",
    "        if tree[e]:\n",
    "            dirs.append(e)\n",
    "            listings.update(createListings(tree[e], path + \"/\" + e))\n",
    "        elif \".\" in e:\n",
    "            files.append(e)\n",
    "\n",
    "    listings[path] = {\"dirs\": dirs, \"files\": files}\n",
    "    return listings"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "f5371ad8",
   "metadata": {},
   "outputs": [],
   "source": [
    "listings = createListings(tree, \"\")\n",
    "import json\n",
    "\n",
    "with open(\"file_listing.json\", \"w\") as jsonfile:\n",
    "    json.dump(listings, jsonfile)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "c90c5848",
   "metadata": {},
   "outputs": [],
   "source": []
  }
 ],
 "metadata": {
  "kernelspec": {
   "display_name": "Python 3",
   "language": "python",
   "name": "python3"
  },
  "language_info": {
   "codemirror_mode": {
    "name": "ipython",
    "version": 3
   },
   "file_extension": ".py",
   "mimetype": "text/x-python",
   "name": "python",
   "nbconvert_exporter": "python",
   "pygments_lexer": "ipython3",
   "version": "3.8.10"
  }
 },
 "nbformat": 4,
 "nbformat_minor": 5
}