Database Builder L1C & L2A.ipynb

{
 "cells": [
  {
   "cell_type": "markdown",
   "id": "dd2b0f01",
   "metadata": {},
   "source": [
    "### Notebook for creating dataset that will be uploaded to aws rds (MySQL), a similar process will be used for L2A.\n",
    "#### The L1C and L2A index datasets were download sentinel-2 on google cloud and contain an index of all of the safe files located at: https://cloud.google.com/storage/docs/public-datasets/sentinel-2"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 70,
   "id": "20e724ed",
   "metadata": {},
   "outputs": [],
   "source": [
    "import pandas as pd\n",
    "import mgrs #local python file\n",
    "import numpy as np"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 71,
   "id": "ad754493",
   "metadata": {},
   "outputs": [],
   "source": [
    "df = pd.read_csv('California_Fire_Incidentsv2.csv')"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 72,
   "id": "56b92e76",
   "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>incident_name</th>\n",
       "      <th>incident_is_final</th>\n",
       "      <th>incident_date_last_update</th>\n",
       "      <th>incident_date_created</th>\n",
       "      <th>incident_administrative_unit</th>\n",
       "      <th>incident_administrative_unit_url</th>\n",
       "      <th>incident_county</th>\n",
       "      <th>incident_location</th>\n",
       "      <th>incident_acres_burned</th>\n",
       "      <th>incident_containment</th>\n",
       "      <th>...</th>\n",
       "      <th>incident_latitude</th>\n",
       "      <th>incident_type</th>\n",
       "      <th>incident_id</th>\n",
       "      <th>incident_url</th>\n",
       "      <th>incident_date_extinguished</th>\n",
       "      <th>incident_dateonly_extinguished</th>\n",
       "      <th>incident_dateonly_created</th>\n",
       "      <th>is_active</th>\n",
       "      <th>calfire_incident</th>\n",
       "      <th>notification_desired</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>Bridge Fire</td>\n",
       "      <td>Y</td>\n",
       "      <td>2018-01-09T13:46:00Z</td>\n",
       "      <td>2017-10-31T11:22:00Z</td>\n",
       "      <td>Shasta-Trinity National Forest</td>\n",
       "      <td>NaN</td>\n",
       "      <td>Shasta</td>\n",
       "      <td>I-5 and Turntable Bay, 7 miles NE of Shasta Lake</td>\n",
       "      <td>37.0</td>\n",
       "      <td>100.0</td>\n",
       "      <td>...</td>\n",
       "      <td>40.774000</td>\n",
       "      <td>NaN</td>\n",
       "      <td>2ca11d45-8139-4c16-8af0-880d99b21e82</td>\n",
       "      <td>https://osfm.fire.ca.gov/incidents/2017/10/31/...</td>\n",
       "      <td>2018-01-09T13:46:00Z</td>\n",
       "      <td>2018-01-09</td>\n",
       "      <td>2017-10-31</td>\n",
       "      <td>N</td>\n",
       "      <td>False</td>\n",
       "      <td>False</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>Pala Fire</td>\n",
       "      <td>Y</td>\n",
       "      <td>2020-09-16T14:07:35Z</td>\n",
       "      <td>2009-05-24T14:56:00Z</td>\n",
       "      <td>CAL FIRE San Diego Unit</td>\n",
       "      <td>NaN</td>\n",
       "      <td>San Diego</td>\n",
       "      <td>Hwy 76 and Pala Temecula, northwest of Pala</td>\n",
       "      <td>122.0</td>\n",
       "      <td>100.0</td>\n",
       "      <td>...</td>\n",
       "      <td>1.000000</td>\n",
       "      <td>Wildfire</td>\n",
       "      <td>8f61f461-552d-4538-b186-35ab030da416</td>\n",
       "      <td>https://osfm.fire.ca.gov/incidents/2009/5/24/p...</td>\n",
       "      <td>2009-05-25T00:00:00Z</td>\n",
       "      <td>2009-05-25</td>\n",
       "      <td>2009-05-24</td>\n",
       "      <td>N</td>\n",
       "      <td>True</td>\n",
       "      <td>False</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>River Fire</td>\n",
       "      <td>Y</td>\n",
       "      <td>2013-02-28T20:00:00Z</td>\n",
       "      <td>2013-02-24T08:16:00Z</td>\n",
       "      <td>CAL FIRE San Bernardino Unit</td>\n",
       "      <td>NaN</td>\n",
       "      <td>Inyo</td>\n",
       "      <td>south of Narrow Gauge Rd &amp; north of Hwy 136, e...</td>\n",
       "      <td>406.0</td>\n",
       "      <td>100.0</td>\n",
       "      <td>...</td>\n",
       "      <td>36.602575</td>\n",
       "      <td>NaN</td>\n",
       "      <td>094719ba-a47b-4abb-9ec5-a506b2b9fd23</td>\n",
       "      <td>https://osfm.fire.ca.gov/incidents/2013/2/24/r...</td>\n",
       "      <td>2013-02-28T20:00:00Z</td>\n",
       "      <td>2013-02-28</td>\n",
       "      <td>2013-02-24</td>\n",
       "      <td>N</td>\n",
       "      <td>True</td>\n",
       "      <td>False</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>Fawnskin Fire</td>\n",
       "      <td>Y</td>\n",
       "      <td>2013-04-22T09:00:00Z</td>\n",
       "      <td>2013-04-20T17:30:00Z</td>\n",
       "      <td>San Bernardino National Forest</td>\n",
       "      <td>NaN</td>\n",
       "      <td>San Bernardino</td>\n",
       "      <td>west of Delamar Mountain, north of the communi...</td>\n",
       "      <td>30.0</td>\n",
       "      <td>100.0</td>\n",
       "      <td>...</td>\n",
       "      <td>34.288877</td>\n",
       "      <td>NaN</td>\n",
       "      <td>58f89ff8-bd3e-4355-b1c0-8fa05c747d3f</td>\n",
       "      <td>https://osfm.fire.ca.gov/incidents/2013/4/20/f...</td>\n",
       "      <td>2013-04-22T09:00:00Z</td>\n",
       "      <td>2013-04-22</td>\n",
       "      <td>2013-04-20</td>\n",
       "      <td>N</td>\n",
       "      <td>False</td>\n",
       "      <td>False</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>Gold Fire</td>\n",
       "      <td>Y</td>\n",
       "      <td>2013-05-01T07:00:00Z</td>\n",
       "      <td>2013-04-30T12:59:00Z</td>\n",
       "      <td>CAL FIRE Madera-Mariposa-Merced Unit</td>\n",
       "      <td>NaN</td>\n",
       "      <td>Madera</td>\n",
       "      <td>Between Road 210 and Road 200 near Fine Gold C...</td>\n",
       "      <td>274.0</td>\n",
       "      <td>100.0</td>\n",
       "      <td>...</td>\n",
       "      <td>37.116295</td>\n",
       "      <td>NaN</td>\n",
       "      <td>357ffc13-bef9-48eb-810f-c5de851972eb</td>\n",
       "      <td>https://osfm.fire.ca.gov/incidents/2013/4/30/g...</td>\n",
       "      <td>2013-05-01T07:00:00Z</td>\n",
       "      <td>2013-05-01</td>\n",
       "      <td>2013-04-30</td>\n",
       "      <td>N</td>\n",
       "      <td>True</td>\n",
       "      <td>False</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "<p>5 rows × 23 columns</p>\n",
       "</div>"
      ],
      "text/plain": [
       "   incident_name incident_is_final incident_date_last_update  \\\n",
       "0    Bridge Fire                 Y      2018-01-09T13:46:00Z   \n",
       "1      Pala Fire                 Y      2020-09-16T14:07:35Z   \n",
       "2     River Fire                 Y      2013-02-28T20:00:00Z   \n",
       "3  Fawnskin Fire                 Y      2013-04-22T09:00:00Z   \n",
       "4      Gold Fire                 Y      2013-05-01T07:00:00Z   \n",
       "\n",
       "  incident_date_created          incident_administrative_unit  \\\n",
       "0  2017-10-31T11:22:00Z       Shasta-Trinity National Forest    \n",
       "1  2009-05-24T14:56:00Z               CAL FIRE San Diego Unit   \n",
       "2  2013-02-24T08:16:00Z          CAL FIRE San Bernardino Unit   \n",
       "3  2013-04-20T17:30:00Z        San Bernardino National Forest   \n",
       "4  2013-04-30T12:59:00Z  CAL FIRE Madera-Mariposa-Merced Unit   \n",
       "\n",
       "   incident_administrative_unit_url incident_county  \\\n",
       "0                               NaN          Shasta   \n",
       "1                               NaN       San Diego   \n",
       "2                               NaN            Inyo   \n",
       "3                               NaN  San Bernardino   \n",
       "4                               NaN          Madera   \n",
       "\n",
       "                                   incident_location  incident_acres_burned  \\\n",
       "0  I-5 and Turntable Bay, 7 miles NE of Shasta Lake                    37.0   \n",
       "1        Hwy 76 and Pala Temecula, northwest of Pala                  122.0   \n",
       "2  south of Narrow Gauge Rd & north of Hwy 136, e...                  406.0   \n",
       "3  west of Delamar Mountain, north of the communi...                   30.0   \n",
       "4  Between Road 210 and Road 200 near Fine Gold C...                  274.0   \n",
       "\n",
       "   incident_containment  ... incident_latitude incident_type  \\\n",
       "0                 100.0  ...         40.774000           NaN   \n",
       "1                 100.0  ...          1.000000      Wildfire   \n",
       "2                 100.0  ...         36.602575           NaN   \n",
       "3                 100.0  ...         34.288877           NaN   \n",
       "4                 100.0  ...         37.116295           NaN   \n",
       "\n",
       "                            incident_id  \\\n",
       "0  2ca11d45-8139-4c16-8af0-880d99b21e82   \n",
       "1  8f61f461-552d-4538-b186-35ab030da416   \n",
       "2  094719ba-a47b-4abb-9ec5-a506b2b9fd23   \n",
       "3  58f89ff8-bd3e-4355-b1c0-8fa05c747d3f   \n",
       "4  357ffc13-bef9-48eb-810f-c5de851972eb   \n",
       "\n",
       "                                        incident_url  \\\n",
       "0  https://osfm.fire.ca.gov/incidents/2017/10/31/...   \n",
       "1  https://osfm.fire.ca.gov/incidents/2009/5/24/p...   \n",
       "2  https://osfm.fire.ca.gov/incidents/2013/2/24/r...   \n",
       "3  https://osfm.fire.ca.gov/incidents/2013/4/20/f...   \n",
       "4  https://osfm.fire.ca.gov/incidents/2013/4/30/g...   \n",
       "\n",
       "  incident_date_extinguished incident_dateonly_extinguished  \\\n",
       "0       2018-01-09T13:46:00Z                     2018-01-09   \n",
       "1       2009-05-25T00:00:00Z                     2009-05-25   \n",
       "2       2013-02-28T20:00:00Z                     2013-02-28   \n",
       "3       2013-04-22T09:00:00Z                     2013-04-22   \n",
       "4       2013-05-01T07:00:00Z                     2013-05-01   \n",
       "\n",
       "  incident_dateonly_created is_active calfire_incident notification_desired  \n",
       "0                2017-10-31         N            False                False  \n",
       "1                2009-05-24         N             True                False  \n",
       "2                2013-02-24         N             True                False  \n",
       "3                2013-04-20         N            False                False  \n",
       "4                2013-04-30         N             True                False  \n",
       "\n",
       "[5 rows x 23 columns]"
      ]
     },
     "execution_count": 72,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df.head()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 73,
   "id": "6870989e",
   "metadata": {},
   "outputs": [],
   "source": [
    "# Latitude\n",
    "# Longitude\n",
    "# mgrs.LLtoMGRS(Lat, Lon)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 74,
   "id": "54791af5",
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "0       40.774000\n",
      "1        1.000000\n",
      "2       36.602575\n",
      "3       34.288877\n",
      "4       37.116295\n",
      "          ...    \n",
      "1905    38.170366\n",
      "1906    34.553000\n",
      "1907    39.986836\n",
      "1908    41.862388\n",
      "1909    37.013464\n",
      "Name: incident_latitude, Length: 1910, dtype: float64 0      -122.309000\n",
      "1         1.000000\n",
      "2      -118.016510\n",
      "3      -116.941311\n",
      "4      -119.635004\n",
      "           ...    \n",
      "1905   -122.241128\n",
      "1906   -120.136000\n",
      "1907   -122.277441\n",
      "1908   -120.358206\n",
      "1909   -121.752106\n",
      "Name: incident_longitude, Length: 1910, dtype: float64\n"
     ]
    }
   ],
   "source": [
    "print(df['incident_latitude'], df['incident_longitude'])"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 75,
   "id": "8c6c9f0c",
   "metadata": {},
   "outputs": [],
   "source": [
    "#create column named MGRS and add tile index to it\n",
    "df['MGRS'] = df.apply(lambda x: mgrs.LLtoMGRS(x['incident_latitude'], x['incident_longitude'])[0:5], axis=1)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 76,
   "id": "46002f1e",
   "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>incident_name</th>\n",
       "      <th>incident_is_final</th>\n",
       "      <th>incident_date_last_update</th>\n",
       "      <th>incident_date_created</th>\n",
       "      <th>incident_administrative_unit</th>\n",
       "      <th>incident_administrative_unit_url</th>\n",
       "      <th>incident_county</th>\n",
       "      <th>incident_location</th>\n",
       "      <th>incident_acres_burned</th>\n",
       "      <th>incident_containment</th>\n",
       "      <th>...</th>\n",
       "      <th>incident_type</th>\n",
       "      <th>incident_id</th>\n",
       "      <th>incident_url</th>\n",
       "      <th>incident_date_extinguished</th>\n",
       "      <th>incident_dateonly_extinguished</th>\n",
       "      <th>incident_dateonly_created</th>\n",
       "      <th>is_active</th>\n",
       "      <th>calfire_incident</th>\n",
       "      <th>notification_desired</th>\n",
       "      <th>MGRS</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>Bridge Fire</td>\n",
       "      <td>Y</td>\n",
       "      <td>2018-01-09T13:46:00Z</td>\n",
       "      <td>2017-10-31T11:22:00Z</td>\n",
       "      <td>Shasta-Trinity National Forest</td>\n",
       "      <td>NaN</td>\n",
       "      <td>Shasta</td>\n",
       "      <td>I-5 and Turntable Bay, 7 miles NE of Shasta Lake</td>\n",
       "      <td>37.0</td>\n",
       "      <td>100.0</td>\n",
       "      <td>...</td>\n",
       "      <td>NaN</td>\n",
       "      <td>2ca11d45-8139-4c16-8af0-880d99b21e82</td>\n",
       "      <td>https://osfm.fire.ca.gov/incidents/2017/10/31/...</td>\n",
       "      <td>2018-01-09T13:46:00Z</td>\n",
       "      <td>2018-01-09</td>\n",
       "      <td>2017-10-31</td>\n",
       "      <td>N</td>\n",
       "      <td>False</td>\n",
       "      <td>False</td>\n",
       "      <td>10TEL</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>Pala Fire</td>\n",
       "      <td>Y</td>\n",
       "      <td>2020-09-16T14:07:35Z</td>\n",
       "      <td>2009-05-24T14:56:00Z</td>\n",
       "      <td>CAL FIRE San Diego Unit</td>\n",
       "      <td>NaN</td>\n",
       "      <td>San Diego</td>\n",
       "      <td>Hwy 76 and Pala Temecula, northwest of Pala</td>\n",
       "      <td>122.0</td>\n",
       "      <td>100.0</td>\n",
       "      <td>...</td>\n",
       "      <td>Wildfire</td>\n",
       "      <td>8f61f461-552d-4538-b186-35ab030da416</td>\n",
       "      <td>https://osfm.fire.ca.gov/incidents/2009/5/24/p...</td>\n",
       "      <td>2009-05-25T00:00:00Z</td>\n",
       "      <td>2009-05-25</td>\n",
       "      <td>2009-05-24</td>\n",
       "      <td>N</td>\n",
       "      <td>True</td>\n",
       "      <td>False</td>\n",
       "      <td>31NBB</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>River Fire</td>\n",
       "      <td>Y</td>\n",
       "      <td>2013-02-28T20:00:00Z</td>\n",
       "      <td>2013-02-24T08:16:00Z</td>\n",
       "      <td>CAL FIRE San Bernardino Unit</td>\n",
       "      <td>NaN</td>\n",
       "      <td>Inyo</td>\n",
       "      <td>south of Narrow Gauge Rd &amp; north of Hwy 136, e...</td>\n",
       "      <td>406.0</td>\n",
       "      <td>100.0</td>\n",
       "      <td>...</td>\n",
       "      <td>NaN</td>\n",
       "      <td>094719ba-a47b-4abb-9ec5-a506b2b9fd23</td>\n",
       "      <td>https://osfm.fire.ca.gov/incidents/2013/2/24/r...</td>\n",
       "      <td>2013-02-28T20:00:00Z</td>\n",
       "      <td>2013-02-28</td>\n",
       "      <td>2013-02-24</td>\n",
       "      <td>N</td>\n",
       "      <td>True</td>\n",
       "      <td>False</td>\n",
       "      <td>11SMA</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>Fawnskin Fire</td>\n",
       "      <td>Y</td>\n",
       "      <td>2013-04-22T09:00:00Z</td>\n",
       "      <td>2013-04-20T17:30:00Z</td>\n",
       "      <td>San Bernardino National Forest</td>\n",
       "      <td>NaN</td>\n",
       "      <td>San Bernardino</td>\n",
       "      <td>west of Delamar Mountain, north of the communi...</td>\n",
       "      <td>30.0</td>\n",
       "      <td>100.0</td>\n",
       "      <td>...</td>\n",
       "      <td>NaN</td>\n",
       "      <td>58f89ff8-bd3e-4355-b1c0-8fa05c747d3f</td>\n",
       "      <td>https://osfm.fire.ca.gov/incidents/2013/4/20/f...</td>\n",
       "      <td>2013-04-22T09:00:00Z</td>\n",
       "      <td>2013-04-22</td>\n",
       "      <td>2013-04-20</td>\n",
       "      <td>N</td>\n",
       "      <td>False</td>\n",
       "      <td>False</td>\n",
       "      <td>11SNT</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>Gold Fire</td>\n",
       "      <td>Y</td>\n",
       "      <td>2013-05-01T07:00:00Z</td>\n",
       "      <td>2013-04-30T12:59:00Z</td>\n",
       "      <td>CAL FIRE Madera-Mariposa-Merced Unit</td>\n",
       "      <td>NaN</td>\n",
       "      <td>Madera</td>\n",
       "      <td>Between Road 210 and Road 200 near Fine Gold C...</td>\n",
       "      <td>274.0</td>\n",
       "      <td>100.0</td>\n",
       "      <td>...</td>\n",
       "      <td>NaN</td>\n",
       "      <td>357ffc13-bef9-48eb-810f-c5de851972eb</td>\n",
       "      <td>https://osfm.fire.ca.gov/incidents/2013/4/30/g...</td>\n",
       "      <td>2013-05-01T07:00:00Z</td>\n",
       "      <td>2013-05-01</td>\n",
       "      <td>2013-04-30</td>\n",
       "      <td>N</td>\n",
       "      <td>True</td>\n",
       "      <td>False</td>\n",
       "      <td>11SKB</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "<p>5 rows × 24 columns</p>\n",
       "</div>"
      ],
      "text/plain": [
       "   incident_name incident_is_final incident_date_last_update  \\\n",
       "0    Bridge Fire                 Y      2018-01-09T13:46:00Z   \n",
       "1      Pala Fire                 Y      2020-09-16T14:07:35Z   \n",
       "2     River Fire                 Y      2013-02-28T20:00:00Z   \n",
       "3  Fawnskin Fire                 Y      2013-04-22T09:00:00Z   \n",
       "4      Gold Fire                 Y      2013-05-01T07:00:00Z   \n",
       "\n",
       "  incident_date_created          incident_administrative_unit  \\\n",
       "0  2017-10-31T11:22:00Z       Shasta-Trinity National Forest    \n",
       "1  2009-05-24T14:56:00Z               CAL FIRE San Diego Unit   \n",
       "2  2013-02-24T08:16:00Z          CAL FIRE San Bernardino Unit   \n",
       "3  2013-04-20T17:30:00Z        San Bernardino National Forest   \n",
       "4  2013-04-30T12:59:00Z  CAL FIRE Madera-Mariposa-Merced Unit   \n",
       "\n",
       "   incident_administrative_unit_url incident_county  \\\n",
       "0                               NaN          Shasta   \n",
       "1                               NaN       San Diego   \n",
       "2                               NaN            Inyo   \n",
       "3                               NaN  San Bernardino   \n",
       "4                               NaN          Madera   \n",
       "\n",
       "                                   incident_location  incident_acres_burned  \\\n",
       "0  I-5 and Turntable Bay, 7 miles NE of Shasta Lake                    37.0   \n",
       "1        Hwy 76 and Pala Temecula, northwest of Pala                  122.0   \n",
       "2  south of Narrow Gauge Rd & north of Hwy 136, e...                  406.0   \n",
       "3  west of Delamar Mountain, north of the communi...                   30.0   \n",
       "4  Between Road 210 and Road 200 near Fine Gold C...                  274.0   \n",
       "\n",
       "   incident_containment  ... incident_type  \\\n",
       "0                 100.0  ...           NaN   \n",
       "1                 100.0  ...      Wildfire   \n",
       "2                 100.0  ...           NaN   \n",
       "3                 100.0  ...           NaN   \n",
       "4                 100.0  ...           NaN   \n",
       "\n",
       "                            incident_id  \\\n",
       "0  2ca11d45-8139-4c16-8af0-880d99b21e82   \n",
       "1  8f61f461-552d-4538-b186-35ab030da416   \n",
       "2  094719ba-a47b-4abb-9ec5-a506b2b9fd23   \n",
       "3  58f89ff8-bd3e-4355-b1c0-8fa05c747d3f   \n",
       "4  357ffc13-bef9-48eb-810f-c5de851972eb   \n",
       "\n",
       "                                        incident_url  \\\n",
       "0  https://osfm.fire.ca.gov/incidents/2017/10/31/...   \n",
       "1  https://osfm.fire.ca.gov/incidents/2009/5/24/p...   \n",
       "2  https://osfm.fire.ca.gov/incidents/2013/2/24/r...   \n",
       "3  https://osfm.fire.ca.gov/incidents/2013/4/20/f...   \n",
       "4  https://osfm.fire.ca.gov/incidents/2013/4/30/g...   \n",
       "\n",
       "   incident_date_extinguished incident_dateonly_extinguished  \\\n",
       "0        2018-01-09T13:46:00Z                     2018-01-09   \n",
       "1        2009-05-25T00:00:00Z                     2009-05-25   \n",
       "2        2013-02-28T20:00:00Z                     2013-02-28   \n",
       "3        2013-04-22T09:00:00Z                     2013-04-22   \n",
       "4        2013-05-01T07:00:00Z                     2013-05-01   \n",
       "\n",
       "  incident_dateonly_created is_active calfire_incident notification_desired  \\\n",
       "0                2017-10-31         N            False                False   \n",
       "1                2009-05-24         N             True                False   \n",
       "2                2013-02-24         N             True                False   \n",
       "3                2013-04-20         N            False                False   \n",
       "4                2013-04-30         N             True                False   \n",
       "\n",
       "    MGRS  \n",
       "0  10TEL  \n",
       "1  31NBB  \n",
       "2  11SMA  \n",
       "3  11SNT  \n",
       "4  11SKB  \n",
       "\n",
       "[5 rows x 24 columns]"
      ]
     },
     "execution_count": 76,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df.head()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 77,
   "id": "2ac8bba3",
   "metadata": {},
   "outputs": [],
   "source": [
    "# get rid of the rows with no longitude or latitide\n",
    "\n",
    "df = df[df['incident_longitude'] != 0]\n",
    "df = df[df['incident_latitude'] != 0]"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 79,
   "id": "9d17be3a",
   "metadata": {},
   "outputs": [],
   "source": [
    "# convert to datetime for filtering\n",
    "df['incident_dateonly_created'] = pd.to_datetime(df['incident_dateonly_created'])\n",
    "df['incident_dateonly_extinguished'] = pd.to_datetime(df['incident_dateonly_extinguished'])"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 80,
   "id": "56e47a64",
   "metadata": {},
   "outputs": [],
   "source": [
    "# for development purposes and code buildout, only use 2016, production code will use 2015-2021\n",
    "df = df[df['incident_dateonly_created'].dt.year == 2016]\n",
    "df = df[df['incident_dateonly_extinguished'].dt.year == 2016]"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 81,
   "id": "08a4be13",
   "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>incident_name</th>\n",
       "      <th>incident_is_final</th>\n",
       "      <th>incident_date_last_update</th>\n",
       "      <th>incident_date_created</th>\n",
       "      <th>incident_administrative_unit</th>\n",
       "      <th>incident_administrative_unit_url</th>\n",
       "      <th>incident_county</th>\n",
       "      <th>incident_location</th>\n",
       "      <th>incident_acres_burned</th>\n",
       "      <th>incident_containment</th>\n",
       "      <th>...</th>\n",
       "      <th>incident_type</th>\n",
       "      <th>incident_id</th>\n",
       "      <th>incident_url</th>\n",
       "      <th>incident_date_extinguished</th>\n",
       "      <th>incident_dateonly_extinguished</th>\n",
       "      <th>incident_dateonly_created</th>\n",
       "      <th>is_active</th>\n",
       "      <th>calfire_incident</th>\n",
       "      <th>notification_desired</th>\n",
       "      <th>MGRS</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>259</th>\n",
       "      <td>Creek Fire</td>\n",
       "      <td>Y</td>\n",
       "      <td>2016-10-12T18:00:00Z</td>\n",
       "      <td>2016-10-10T16:13:00Z</td>\n",
       "      <td>CAL FIRE Sonoma-Lake-Napa Unit</td>\n",
       "      <td>NaN</td>\n",
       "      <td>Napa</td>\n",
       "      <td>Dry Creek Road 3 miles west of Yountville</td>\n",
       "      <td>65.0</td>\n",
       "      <td>100.0</td>\n",
       "      <td>...</td>\n",
       "      <td>NaN</td>\n",
       "      <td>3135c367-1274-4cc4-9152-661f6fd7707e</td>\n",
       "      <td>https://osfm.fire.ca.gov/incidents/2016/10/10/...</td>\n",
       "      <td>2016-10-12T18:00:00Z</td>\n",
       "      <td>2016-10-12</td>\n",
       "      <td>2016-10-10</td>\n",
       "      <td>N</td>\n",
       "      <td>True</td>\n",
       "      <td>False</td>\n",
       "      <td>10SEH</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>312</th>\n",
       "      <td>Taglio Fire</td>\n",
       "      <td>Y</td>\n",
       "      <td>2016-04-24T15:30:00Z</td>\n",
       "      <td>2016-04-24T11:10:00Z</td>\n",
       "      <td>CAL FIRE Madera-Mariposa-Merced Unit</td>\n",
       "      <td>NaN</td>\n",
       "      <td>Merced</td>\n",
       "      <td>I-5 &amp; Taglio Road, east of Gustine</td>\n",
       "      <td>30.0</td>\n",
       "      <td>100.0</td>\n",
       "      <td>...</td>\n",
       "      <td>NaN</td>\n",
       "      <td>7106ba43-100b-41f9-acbf-9fb9e9ab9ef7</td>\n",
       "      <td>https://osfm.fire.ca.gov/incidents/2016/4/24/t...</td>\n",
       "      <td>2016-04-24T15:30:00Z</td>\n",
       "      <td>2016-04-24</td>\n",
       "      <td>2016-04-24</td>\n",
       "      <td>N</td>\n",
       "      <td>True</td>\n",
       "      <td>False</td>\n",
       "      <td>10SFG</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>320</th>\n",
       "      <td>Gorman Fire</td>\n",
       "      <td>Y</td>\n",
       "      <td>2016-04-19T15:30:00Z</td>\n",
       "      <td>2016-04-19T13:09:00Z</td>\n",
       "      <td>Los Angeles County Fire</td>\n",
       "      <td>NaN</td>\n",
       "      <td>Los Angeles</td>\n",
       "      <td>off northbound Interstate 5 at Gorman Creek</td>\n",
       "      <td>0.0</td>\n",
       "      <td>100.0</td>\n",
       "      <td>...</td>\n",
       "      <td>NaN</td>\n",
       "      <td>2c77fa07-73e6-45fe-a527-5bd526bdef41</td>\n",
       "      <td>https://osfm.fire.ca.gov/incidents/2016/4/19/g...</td>\n",
       "      <td>2016-04-19T15:30:00Z</td>\n",
       "      <td>2016-04-19</td>\n",
       "      <td>2016-04-19</td>\n",
       "      <td>N</td>\n",
       "      <td>False</td>\n",
       "      <td>False</td>\n",
       "      <td>11SLU</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>321</th>\n",
       "      <td>Tulloch Fire</td>\n",
       "      <td>Y</td>\n",
       "      <td>2016-06-01T19:45:00Z</td>\n",
       "      <td>2016-05-30T15:10:00Z</td>\n",
       "      <td>CAL FIRE Tuolumne-Calaveras Unit</td>\n",
       "      <td>NaN</td>\n",
       "      <td>Tuolumne</td>\n",
       "      <td>off New Peoria Flat Road and Old Melones Road,...</td>\n",
       "      <td>85.0</td>\n",
       "      <td>100.0</td>\n",
       "      <td>...</td>\n",
       "      <td>NaN</td>\n",
       "      <td>ffec404e-dea9-4175-8c50-8f2b0ca3efdd</td>\n",
       "      <td>https://osfm.fire.ca.gov/incidents/2016/5/30/t...</td>\n",
       "      <td>2016-06-01T19:45:00Z</td>\n",
       "      <td>2016-06-01</td>\n",
       "      <td>2016-05-30</td>\n",
       "      <td>N</td>\n",
       "      <td>True</td>\n",
       "      <td>False</td>\n",
       "      <td>10SGH</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>322</th>\n",
       "      <td>Metz Fire</td>\n",
       "      <td>Y</td>\n",
       "      <td>2016-05-25T18:15:00Z</td>\n",
       "      <td>2016-05-22T15:27:00Z</td>\n",
       "      <td>CAL FIRE San Benito-Monterey Unit</td>\n",
       "      <td>NaN</td>\n",
       "      <td>Monterey</td>\n",
       "      <td>Metz Rd &amp; Blue Jay Ln, southeast of Soledad</td>\n",
       "      <td>3876.0</td>\n",
       "      <td>100.0</td>\n",
       "      <td>...</td>\n",
       "      <td>NaN</td>\n",
       "      <td>55fa95e6-f09e-42c4-889b-a4b5b63dccf2</td>\n",
       "      <td>https://osfm.fire.ca.gov/incidents/2016/5/22/m...</td>\n",
       "      <td>2016-05-25T18:15:00Z</td>\n",
       "      <td>2016-05-25</td>\n",
       "      <td>2016-05-22</td>\n",
       "      <td>N</td>\n",
       "      <td>True</td>\n",
       "      <td>False</td>\n",
       "      <td>10SFF</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "<p>5 rows × 24 columns</p>\n",
       "</div>"
      ],
      "text/plain": [
       "    incident_name incident_is_final incident_date_last_update  \\\n",
       "259    Creek Fire                 Y      2016-10-12T18:00:00Z   \n",
       "312   Taglio Fire                 Y      2016-04-24T15:30:00Z   \n",
       "320   Gorman Fire                 Y      2016-04-19T15:30:00Z   \n",
       "321  Tulloch Fire                 Y      2016-06-01T19:45:00Z   \n",
       "322     Metz Fire                 Y      2016-05-25T18:15:00Z   \n",
       "\n",
       "    incident_date_created          incident_administrative_unit  \\\n",
       "259  2016-10-10T16:13:00Z        CAL FIRE Sonoma-Lake-Napa Unit   \n",
       "312  2016-04-24T11:10:00Z  CAL FIRE Madera-Mariposa-Merced Unit   \n",
       "320  2016-04-19T13:09:00Z               Los Angeles County Fire   \n",
       "321  2016-05-30T15:10:00Z      CAL FIRE Tuolumne-Calaveras Unit   \n",
       "322  2016-05-22T15:27:00Z     CAL FIRE San Benito-Monterey Unit   \n",
       "\n",
       "     incident_administrative_unit_url incident_county  \\\n",
       "259                               NaN            Napa   \n",
       "312                               NaN          Merced   \n",
       "320                               NaN     Los Angeles   \n",
       "321                               NaN        Tuolumne   \n",
       "322                               NaN        Monterey   \n",
       "\n",
       "                                     incident_location  incident_acres_burned  \\\n",
       "259          Dry Creek Road 3 miles west of Yountville                   65.0   \n",
       "312                 I-5 & Taglio Road, east of Gustine                   30.0   \n",
       "320        off northbound Interstate 5 at Gorman Creek                    0.0   \n",
       "321  off New Peoria Flat Road and Old Melones Road,...                   85.0   \n",
       "322        Metz Rd & Blue Jay Ln, southeast of Soledad                 3876.0   \n",
       "\n",
       "     incident_containment  ... incident_type  \\\n",
       "259                 100.0  ...           NaN   \n",
       "312                 100.0  ...           NaN   \n",
       "320                 100.0  ...           NaN   \n",
       "321                 100.0  ...           NaN   \n",
       "322                 100.0  ...           NaN   \n",
       "\n",
       "                              incident_id  \\\n",
       "259  3135c367-1274-4cc4-9152-661f6fd7707e   \n",
       "312  7106ba43-100b-41f9-acbf-9fb9e9ab9ef7   \n",
       "320  2c77fa07-73e6-45fe-a527-5bd526bdef41   \n",
       "321  ffec404e-dea9-4175-8c50-8f2b0ca3efdd   \n",
       "322  55fa95e6-f09e-42c4-889b-a4b5b63dccf2   \n",
       "\n",
       "                                          incident_url  \\\n",
       "259  https://osfm.fire.ca.gov/incidents/2016/10/10/...   \n",
       "312  https://osfm.fire.ca.gov/incidents/2016/4/24/t...   \n",
       "320  https://osfm.fire.ca.gov/incidents/2016/4/19/g...   \n",
       "321  https://osfm.fire.ca.gov/incidents/2016/5/30/t...   \n",
       "322  https://osfm.fire.ca.gov/incidents/2016/5/22/m...   \n",
       "\n",
       "     incident_date_extinguished incident_dateonly_extinguished  \\\n",
       "259        2016-10-12T18:00:00Z                     2016-10-12   \n",
       "312        2016-04-24T15:30:00Z                     2016-04-24   \n",
       "320        2016-04-19T15:30:00Z                     2016-04-19   \n",
       "321        2016-06-01T19:45:00Z                     2016-06-01   \n",
       "322        2016-05-25T18:15:00Z                     2016-05-25   \n",
       "\n",
       "    incident_dateonly_created is_active calfire_incident notification_desired  \\\n",
       "259                2016-10-10         N             True                False   \n",
       "312                2016-04-24         N             True                False   \n",
       "320                2016-04-19         N            False                False   \n",
       "321                2016-05-30         N             True                False   \n",
       "322                2016-05-22         N             True                False   \n",
       "\n",
       "      MGRS  \n",
       "259  10SEH  \n",
       "312  10SFG  \n",
       "320  11SLU  \n",
       "321  10SGH  \n",
       "322  10SFF  \n",
       "\n",
       "[5 rows x 24 columns]"
      ]
     },
     "execution_count": 81,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df.head()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 82,
   "id": "265152ec",
   "metadata": {},
   "outputs": [],
   "source": [
    "#filter down to these four cols\n",
    "df = df[['incident_dateonly_created', 'incident_dateonly_extinguished', 'MGRS', 'incident_id']]"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 83,
   "id": "9aad5bf9",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "incident_dateonly_created         0\n",
       "incident_dateonly_extinguished    0\n",
       "MGRS                              0\n",
       "incident_id                       0\n",
       "dtype: int64"
      ]
     },
     "execution_count": 83,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df.isna().sum()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 84,
   "id": "a884b8e6",
   "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>incident_dateonly_created</th>\n",
       "      <th>incident_dateonly_extinguished</th>\n",
       "      <th>MGRS</th>\n",
       "      <th>incident_id</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>259</th>\n",
       "      <td>2016-10-10</td>\n",
       "      <td>2016-10-12</td>\n",
       "      <td>10SEH</td>\n",
       "      <td>3135c367-1274-4cc4-9152-661f6fd7707e</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>312</th>\n",
       "      <td>2016-04-24</td>\n",
       "      <td>2016-04-24</td>\n",
       "      <td>10SFG</td>\n",
       "      <td>7106ba43-100b-41f9-acbf-9fb9e9ab9ef7</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>320</th>\n",
       "      <td>2016-04-19</td>\n",
       "      <td>2016-04-19</td>\n",
       "      <td>11SLU</td>\n",
       "      <td>2c77fa07-73e6-45fe-a527-5bd526bdef41</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>321</th>\n",
       "      <td>2016-05-30</td>\n",
       "      <td>2016-06-01</td>\n",
       "      <td>10SGH</td>\n",
       "      <td>ffec404e-dea9-4175-8c50-8f2b0ca3efdd</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>322</th>\n",
       "      <td>2016-05-22</td>\n",
       "      <td>2016-05-25</td>\n",
       "      <td>10SFF</td>\n",
       "      <td>55fa95e6-f09e-42c4-889b-a4b5b63dccf2</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "    incident_dateonly_created incident_dateonly_extinguished   MGRS  \\\n",
       "259                2016-10-10                     2016-10-12  10SEH   \n",
       "312                2016-04-24                     2016-04-24  10SFG   \n",
       "320                2016-04-19                     2016-04-19  11SLU   \n",
       "321                2016-05-30                     2016-06-01  10SGH   \n",
       "322                2016-05-22                     2016-05-25  10SFF   \n",
       "\n",
       "                              incident_id  \n",
       "259  3135c367-1274-4cc4-9152-661f6fd7707e  \n",
       "312  7106ba43-100b-41f9-acbf-9fb9e9ab9ef7  \n",
       "320  2c77fa07-73e6-45fe-a527-5bd526bdef41  \n",
       "321  ffec404e-dea9-4175-8c50-8f2b0ca3efdd  \n",
       "322  55fa95e6-f09e-42c4-889b-a4b5b63dccf2  "
      ]
     },
     "execution_count": 84,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df.head()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 85,
   "id": "e59ff06a",
   "metadata": {},
   "outputs": [],
   "source": [
    "df = df.rename(columns={\"incident_dateonly_created\": \"STARTED\", \"incident_dateonly_extinguished\": \"ENDED\", 'incident_id':'UNIQUE_ID' })"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 90,
   "id": "c7e47cb4",
   "metadata": {},
   "outputs": [],
   "source": [
    "df_l2a = pd.read_csv('CA_L1C_INDEX.csv')"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 91,
   "id": "a6df2846",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "GRANULE_ID                    0\n",
       "PRODUCT_ID                    0\n",
       "DATATAKE_IDENTIFIER           0\n",
       "MGRS_TILE                     0\n",
       "SENSING_TIME                  0\n",
       "TOTAL_SIZE                  396\n",
       "CLOUD_COVER                   0\n",
       "GEOMETRIC_QUALITY_FLAG    32900\n",
       "GENERATION_TIME               0\n",
       "NORTH_LAT                     0\n",
       "SOUTH_LAT                     0\n",
       "WEST_LON                      0\n",
       "EAST_LON                      0\n",
       "BASE_URL                      0\n",
       "num                           0\n",
       "let                           0\n",
       "sub                           0\n",
       "dtype: int64"
      ]
     },
     "execution_count": 91,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df_l2a.isna().sum()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 92,
   "id": "e55867c8",
   "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>STARTED</th>\n",
       "      <th>ENDED</th>\n",
       "      <th>MGRS</th>\n",
       "      <th>UNIQUE_ID</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>259</th>\n",
       "      <td>2016-10-10</td>\n",
       "      <td>2016-10-12</td>\n",
       "      <td>10SEH</td>\n",
       "      <td>3135c367-1274-4cc4-9152-661f6fd7707e</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>312</th>\n",
       "      <td>2016-04-24</td>\n",
       "      <td>2016-04-24</td>\n",
       "      <td>10SFG</td>\n",
       "      <td>7106ba43-100b-41f9-acbf-9fb9e9ab9ef7</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>320</th>\n",
       "      <td>2016-04-19</td>\n",
       "      <td>2016-04-19</td>\n",
       "      <td>11SLU</td>\n",
       "      <td>2c77fa07-73e6-45fe-a527-5bd526bdef41</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>321</th>\n",
       "      <td>2016-05-30</td>\n",
       "      <td>2016-06-01</td>\n",
       "      <td>10SGH</td>\n",
       "      <td>ffec404e-dea9-4175-8c50-8f2b0ca3efdd</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>322</th>\n",
       "      <td>2016-05-22</td>\n",
       "      <td>2016-05-25</td>\n",
       "      <td>10SFF</td>\n",
       "      <td>55fa95e6-f09e-42c4-889b-a4b5b63dccf2</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "       STARTED      ENDED   MGRS                             UNIQUE_ID\n",
       "259 2016-10-10 2016-10-12  10SEH  3135c367-1274-4cc4-9152-661f6fd7707e\n",
       "312 2016-04-24 2016-04-24  10SFG  7106ba43-100b-41f9-acbf-9fb9e9ab9ef7\n",
       "320 2016-04-19 2016-04-19  11SLU  2c77fa07-73e6-45fe-a527-5bd526bdef41\n",
       "321 2016-05-30 2016-06-01  10SGH  ffec404e-dea9-4175-8c50-8f2b0ca3efdd\n",
       "322 2016-05-22 2016-05-25  10SFF  55fa95e6-f09e-42c4-889b-a4b5b63dccf2"
      ]
     },
     "execution_count": 92,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df.head()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 93,
   "id": "a45ee8b8",
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "<class 'pandas.core.frame.DataFrame'>\n",
      "Int64Index: 153 entries, 259 to 472\n",
      "Data columns (total 4 columns):\n",
      " #   Column     Non-Null Count  Dtype         \n",
      "---  ------     --------------  -----         \n",
      " 0   STARTED    153 non-null    datetime64[ns]\n",
      " 1   ENDED      153 non-null    datetime64[ns]\n",
      " 2   MGRS       153 non-null    object        \n",
      " 3   UNIQUE_ID  153 non-null    object        \n",
      "dtypes: datetime64[ns](2), object(2)\n",
      "memory usage: 6.0+ KB\n"
     ]
    }
   ],
   "source": [
    "df.info()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 94,
   "id": "5342179b",
   "metadata": {},
   "outputs": [],
   "source": [
    "# convert to datatime for anlysis\n",
    "df_l2a['SENSING_TIME'] = pd.to_datetime(df_l2a['SENSING_TIME'])"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 95,
   "id": "badd326e",
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "<class 'pandas.core.frame.DataFrame'>\n",
      "RangeIndex: 41232 entries, 0 to 41231\n",
      "Data columns (total 17 columns):\n",
      " #   Column                  Non-Null Count  Dtype              \n",
      "---  ------                  --------------  -----              \n",
      " 0   GRANULE_ID              41232 non-null  object             \n",
      " 1   PRODUCT_ID              41232 non-null  object             \n",
      " 2   DATATAKE_IDENTIFIER     41232 non-null  object             \n",
      " 3   MGRS_TILE               41232 non-null  object             \n",
      " 4   SENSING_TIME            41232 non-null  datetime64[ns, UTC]\n",
      " 5   TOTAL_SIZE              40836 non-null  float64            \n",
      " 6   CLOUD_COVER             41232 non-null  float64            \n",
      " 7   GEOMETRIC_QUALITY_FLAG  8332 non-null   object             \n",
      " 8   GENERATION_TIME         41232 non-null  object             \n",
      " 9   NORTH_LAT               41232 non-null  float64            \n",
      " 10  SOUTH_LAT               41232 non-null  float64            \n",
      " 11  WEST_LON                41232 non-null  float64            \n",
      " 12  EAST_LON                41232 non-null  float64            \n",
      " 13  BASE_URL                41232 non-null  object             \n",
      " 14  num                     41232 non-null  int64              \n",
      " 15  let                     41232 non-null  object             \n",
      " 16  sub                     41232 non-null  object             \n",
      "dtypes: datetime64[ns, UTC](1), float64(6), int64(1), object(9)\n",
      "memory usage: 5.3+ MB\n"
     ]
    }
   ],
   "source": [
    "df_l2a.info()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 96,
   "id": "6aa2b632",
   "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>GRANULE_ID</th>\n",
       "      <th>PRODUCT_ID</th>\n",
       "      <th>DATATAKE_IDENTIFIER</th>\n",
       "      <th>MGRS_TILE</th>\n",
       "      <th>SENSING_TIME</th>\n",
       "      <th>TOTAL_SIZE</th>\n",
       "      <th>CLOUD_COVER</th>\n",
       "      <th>GEOMETRIC_QUALITY_FLAG</th>\n",
       "      <th>GENERATION_TIME</th>\n",
       "      <th>NORTH_LAT</th>\n",
       "      <th>SOUTH_LAT</th>\n",
       "      <th>WEST_LON</th>\n",
       "      <th>EAST_LON</th>\n",
       "      <th>BASE_URL</th>\n",
       "      <th>num</th>\n",
       "      <th>let</th>\n",
       "      <th>sub</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>L1C_T11SKA_A014224_20180313T185032</td>\n",
       "      <td>S2A_MSIL1C_20180313T184141_N0206_R070_T11SKA_2...</td>\n",
       "      <td>GS2A_20180313T184141_014224_N02.06</td>\n",
       "      <td>11SKA</td>\n",
       "      <td>2018-03-13 18:50:32.410000+00:00</td>\n",
       "      <td>677636187.0</td>\n",
       "      <td>98.2627</td>\n",
       "      <td>NaN</td>\n",
       "      <td>2018-03-13T23:36:24.000000Z</td>\n",
       "      <td>37.027394</td>\n",
       "      <td>36.010587</td>\n",
       "      <td>-120.371431</td>\n",
       "      <td>-119.111546</td>\n",
       "      <td>gs://gcp-public-data-sentinel-2/tiles/11/S/KA/...</td>\n",
       "      <td>11</td>\n",
       "      <td>S</td>\n",
       "      <td>KA</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>L1C_T11SKV_A018214_20200831T184219</td>\n",
       "      <td>S2B_MSIL1C_20200831T182919_N0209_R027_T11SKV_2...</td>\n",
       "      <td>GS2B_20200831T182919_018214_N02.09</td>\n",
       "      <td>11SKV</td>\n",
       "      <td>2020-08-31 18:44:44.816000+00:00</td>\n",
       "      <td>42049014.0</td>\n",
       "      <td>0.0000</td>\n",
       "      <td>NaN</td>\n",
       "      <td>2020-08-31T22:21:43.000000Z</td>\n",
       "      <td>35.673846</td>\n",
       "      <td>35.134082</td>\n",
       "      <td>-119.251242</td>\n",
       "      <td>-119.088005</td>\n",
       "      <td>gs://gcp-public-data-sentinel-2/tiles/11/S/KV/...</td>\n",
       "      <td>11</td>\n",
       "      <td>S</td>\n",
       "      <td>KV</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>L1C_T11SKD_A024449_20211110T185617</td>\n",
       "      <td>S2B_MSIL1C_20211110T185619_N0301_R113_T11SKD_2...</td>\n",
       "      <td>GS2B_20211110T185619_024449_N03.01</td>\n",
       "      <td>11SKD</td>\n",
       "      <td>2021-11-10 19:03:32.406000+00:00</td>\n",
       "      <td>497920476.0</td>\n",
       "      <td>56.6109</td>\n",
       "      <td>NaN</td>\n",
       "      <td>2021-11-10T20:21:27.000000Z</td>\n",
       "      <td>39.724764</td>\n",
       "      <td>38.710009</td>\n",
       "      <td>-120.499098</td>\n",
       "      <td>-119.437275</td>\n",
       "      <td>gs://gcp-public-data-sentinel-2/tiles/11/S/KD/...</td>\n",
       "      <td>11</td>\n",
       "      <td>S</td>\n",
       "      <td>KD</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>L1C_T11SLA_A006245_20180517T183956</td>\n",
       "      <td>S2B_MSIL1C_20180517T183919_N0206_R070_T11SLA_2...</td>\n",
       "      <td>GS2B_20180517T183919_006245_N02.06</td>\n",
       "      <td>11SLA</td>\n",
       "      <td>2018-05-17 18:39:56.150000+00:00</td>\n",
       "      <td>878962850.0</td>\n",
       "      <td>9.2473</td>\n",
       "      <td>NaN</td>\n",
       "      <td>2018-05-17T22:19:06.000000Z</td>\n",
       "      <td>37.042336</td>\n",
       "      <td>36.036257</td>\n",
       "      <td>-119.248493</td>\n",
       "      <td>-118.006945</td>\n",
       "      <td>gs://gcp-public-data-sentinel-2/tiles/11/S/LA/...</td>\n",
       "      <td>11</td>\n",
       "      <td>S</td>\n",
       "      <td>LA</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>L1C_T11SKV_A002627_20170906T184420</td>\n",
       "      <td>S2B_MSIL1C_20170906T184419_N0205_R027_T11SKV_2...</td>\n",
       "      <td>GS2B_20170906T184419_002627_N02.05</td>\n",
       "      <td>11SKV</td>\n",
       "      <td>2017-09-06 18:44:20.460000+00:00</td>\n",
       "      <td>37238502.0</td>\n",
       "      <td>0.0000</td>\n",
       "      <td>PASSED</td>\n",
       "      <td>2017-09-06T18:44:20.000000Z</td>\n",
       "      <td>35.684658</td>\n",
       "      <td>35.134021</td>\n",
       "      <td>-119.254906</td>\n",
       "      <td>-119.088005</td>\n",
       "      <td>gs://gcp-public-data-sentinel-2/tiles/11/S/KV/...</td>\n",
       "      <td>11</td>\n",
       "      <td>S</td>\n",
       "      <td>KV</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "                           GRANULE_ID  \\\n",
       "0  L1C_T11SKA_A014224_20180313T185032   \n",
       "1  L1C_T11SKV_A018214_20200831T184219   \n",
       "2  L1C_T11SKD_A024449_20211110T185617   \n",
       "3  L1C_T11SLA_A006245_20180517T183956   \n",
       "4  L1C_T11SKV_A002627_20170906T184420   \n",
       "\n",
       "                                          PRODUCT_ID  \\\n",
       "0  S2A_MSIL1C_20180313T184141_N0206_R070_T11SKA_2...   \n",
       "1  S2B_MSIL1C_20200831T182919_N0209_R027_T11SKV_2...   \n",
       "2  S2B_MSIL1C_20211110T185619_N0301_R113_T11SKD_2...   \n",
       "3  S2B_MSIL1C_20180517T183919_N0206_R070_T11SLA_2...   \n",
       "4  S2B_MSIL1C_20170906T184419_N0205_R027_T11SKV_2...   \n",
       "\n",
       "                  DATATAKE_IDENTIFIER MGRS_TILE  \\\n",
       "0  GS2A_20180313T184141_014224_N02.06     11SKA   \n",
       "1  GS2B_20200831T182919_018214_N02.09     11SKV   \n",
       "2  GS2B_20211110T185619_024449_N03.01     11SKD   \n",
       "3  GS2B_20180517T183919_006245_N02.06     11SLA   \n",
       "4  GS2B_20170906T184419_002627_N02.05     11SKV   \n",
       "\n",
       "                      SENSING_TIME   TOTAL_SIZE  CLOUD_COVER  \\\n",
       "0 2018-03-13 18:50:32.410000+00:00  677636187.0      98.2627   \n",
       "1 2020-08-31 18:44:44.816000+00:00   42049014.0       0.0000   \n",
       "2 2021-11-10 19:03:32.406000+00:00  497920476.0      56.6109   \n",
       "3 2018-05-17 18:39:56.150000+00:00  878962850.0       9.2473   \n",
       "4 2017-09-06 18:44:20.460000+00:00   37238502.0       0.0000   \n",
       "\n",
       "  GEOMETRIC_QUALITY_FLAG              GENERATION_TIME  NORTH_LAT  SOUTH_LAT  \\\n",
       "0                    NaN  2018-03-13T23:36:24.000000Z  37.027394  36.010587   \n",
       "1                    NaN  2020-08-31T22:21:43.000000Z  35.673846  35.134082   \n",
       "2                    NaN  2021-11-10T20:21:27.000000Z  39.724764  38.710009   \n",
       "3                    NaN  2018-05-17T22:19:06.000000Z  37.042336  36.036257   \n",
       "4                 PASSED  2017-09-06T18:44:20.000000Z  35.684658  35.134021   \n",
       "\n",
       "     WEST_LON    EAST_LON                                           BASE_URL  \\\n",
       "0 -120.371431 -119.111546  gs://gcp-public-data-sentinel-2/tiles/11/S/KA/...   \n",
       "1 -119.251242 -119.088005  gs://gcp-public-data-sentinel-2/tiles/11/S/KV/...   \n",
       "2 -120.499098 -119.437275  gs://gcp-public-data-sentinel-2/tiles/11/S/KD/...   \n",
       "3 -119.248493 -118.006945  gs://gcp-public-data-sentinel-2/tiles/11/S/LA/...   \n",
       "4 -119.254906 -119.088005  gs://gcp-public-data-sentinel-2/tiles/11/S/KV/...   \n",
       "\n",
       "   num let sub  \n",
       "0   11   S  KA  \n",
       "1   11   S  KV  \n",
       "2   11   S  KD  \n",
       "3   11   S  LA  \n",
       "4   11   S  KV  "
      ]
     },
     "execution_count": 96,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df_l2a.head()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 97,
   "id": "28bc4481",
   "metadata": {},
   "outputs": [],
   "source": [
    "#filter by year for code dev purposes\n",
    "df_l2a = df_l2a[df_l2a['SENSING_TIME'].dt.year == 2016]"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 98,
   "id": "fe02d047",
   "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>GRANULE_ID</th>\n",
       "      <th>PRODUCT_ID</th>\n",
       "      <th>DATATAKE_IDENTIFIER</th>\n",
       "      <th>MGRS_TILE</th>\n",
       "      <th>SENSING_TIME</th>\n",
       "      <th>TOTAL_SIZE</th>\n",
       "      <th>CLOUD_COVER</th>\n",
       "      <th>GEOMETRIC_QUALITY_FLAG</th>\n",
       "      <th>GENERATION_TIME</th>\n",
       "      <th>NORTH_LAT</th>\n",
       "      <th>SOUTH_LAT</th>\n",
       "      <th>WEST_LON</th>\n",
       "      <th>EAST_LON</th>\n",
       "      <th>BASE_URL</th>\n",
       "      <th>num</th>\n",
       "      <th>let</th>\n",
       "      <th>sub</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>12</th>\n",
       "      <td>S2A_OPER_MSI_L1C_TL_MTI__20160117T214954_A0029...</td>\n",
       "      <td>S2A_OPER_PRD_MSIL1C_PDMC_20160119T041819_R127_...</td>\n",
       "      <td>GS2A_20160117T182712_002984_N02.01</td>\n",
       "      <td>11SMS</td>\n",
       "      <td>2016-01-17 18:34:34.821000+00:00</td>\n",
       "      <td>279018323.0</td>\n",
       "      <td>8.0000</td>\n",
       "      <td>PASSED</td>\n",
       "      <td>2016-01-19T04:18:19.000000Z</td>\n",
       "      <td>33.439621</td>\n",
       "      <td>32.448398</td>\n",
       "      <td>-117.456101</td>\n",
       "      <td>-116.895105</td>\n",
       "      <td>gs://gcp-public-data-sentinel-2/tiles/11/S/MS/...</td>\n",
       "      <td>11</td>\n",
       "      <td>S</td>\n",
       "      <td>MS</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>30</th>\n",
       "      <td>L1C_T10TFK_A003299_20160208T190605</td>\n",
       "      <td>S2A_MSIL1C_20160208T190542_N0201_R013_T10TFK_2...</td>\n",
       "      <td>GS2A_20160208T190542_003299_N02.01</td>\n",
       "      <td>10TFK</td>\n",
       "      <td>2016-02-08 19:06:05.324000+00:00</td>\n",
       "      <td>62268939.0</td>\n",
       "      <td>0.0000</td>\n",
       "      <td>FAILED</td>\n",
       "      <td>2016-02-08T19:06:05.000000Z</td>\n",
       "      <td>40.644890</td>\n",
       "      <td>40.019779</td>\n",
       "      <td>-121.828279</td>\n",
       "      <td>-121.608135</td>\n",
       "      <td>gs://gcp-public-data-sentinel-2/tiles/10/T/FK/...</td>\n",
       "      <td>10</td>\n",
       "      <td>T</td>\n",
       "      <td>FK</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>31</th>\n",
       "      <td>L1C_T10SEG_A004829_20160525T190354</td>\n",
       "      <td>S2A_MSIL1C_20160525T190352_N0202_R113_T10SEG_2...</td>\n",
       "      <td>GS2A_20160525T190352_004829_N02.02</td>\n",
       "      <td>10SEG</td>\n",
       "      <td>2016-05-25 19:03:54+00:00</td>\n",
       "      <td>748966454.0</td>\n",
       "      <td>60.7736</td>\n",
       "      <td>PASSED</td>\n",
       "      <td>2016-05-25T19:03:54.000000Z</td>\n",
       "      <td>37.947680</td>\n",
       "      <td>36.951572</td>\n",
       "      <td>-123.000342</td>\n",
       "      <td>-121.750759</td>\n",
       "      <td>gs://gcp-public-data-sentinel-2/tiles/10/S/EG/...</td>\n",
       "      <td>10</td>\n",
       "      <td>S</td>\n",
       "      <td>EG</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>40</th>\n",
       "      <td>L1C_T10TDL_A007732_20161214T190804</td>\n",
       "      <td>S2A_MSIL1C_20161214T190802_N0204_R013_T10TDL_2...</td>\n",
       "      <td>GS2A_20161214T190802_007732_N02.04</td>\n",
       "      <td>10TDL</td>\n",
       "      <td>2016-12-14 19:08:04.459000+00:00</td>\n",
       "      <td>608233858.0</td>\n",
       "      <td>100.0000</td>\n",
       "      <td>PASSED</td>\n",
       "      <td>2016-12-14T19:08:04.000000Z</td>\n",
       "      <td>41.551922</td>\n",
       "      <td>40.556797</td>\n",
       "      <td>-124.199569</td>\n",
       "      <td>-122.883088</td>\n",
       "      <td>gs://gcp-public-data-sentinel-2/tiles/10/T/DL/...</td>\n",
       "      <td>10</td>\n",
       "      <td>T</td>\n",
       "      <td>DL</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>46</th>\n",
       "      <td>L1C_T10TEM_A007260_20161111T185723</td>\n",
       "      <td>S2A_MSIL1C_20161111T185622_N0204_R113_T10TEM_2...</td>\n",
       "      <td>GS2A_20161111T185622_007260_N02.04</td>\n",
       "      <td>10TEM</td>\n",
       "      <td>2016-11-11 18:57:23.790000+00:00</td>\n",
       "      <td>348753536.0</td>\n",
       "      <td>65.4965</td>\n",
       "      <td>FAILED</td>\n",
       "      <td>2016-11-11T18:57:23.000000Z</td>\n",
       "      <td>42.449494</td>\n",
       "      <td>41.456335</td>\n",
       "      <td>-122.443546</td>\n",
       "      <td>-121.665230</td>\n",
       "      <td>gs://gcp-public-data-sentinel-2/tiles/10/T/EM/...</td>\n",
       "      <td>10</td>\n",
       "      <td>T</td>\n",
       "      <td>EM</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "                                           GRANULE_ID  \\\n",
       "12  S2A_OPER_MSI_L1C_TL_MTI__20160117T214954_A0029...   \n",
       "30                 L1C_T10TFK_A003299_20160208T190605   \n",
       "31                 L1C_T10SEG_A004829_20160525T190354   \n",
       "40                 L1C_T10TDL_A007732_20161214T190804   \n",
       "46                 L1C_T10TEM_A007260_20161111T185723   \n",
       "\n",
       "                                           PRODUCT_ID  \\\n",
       "12  S2A_OPER_PRD_MSIL1C_PDMC_20160119T041819_R127_...   \n",
       "30  S2A_MSIL1C_20160208T190542_N0201_R013_T10TFK_2...   \n",
       "31  S2A_MSIL1C_20160525T190352_N0202_R113_T10SEG_2...   \n",
       "40  S2A_MSIL1C_20161214T190802_N0204_R013_T10TDL_2...   \n",
       "46  S2A_MSIL1C_20161111T185622_N0204_R113_T10TEM_2...   \n",
       "\n",
       "                   DATATAKE_IDENTIFIER MGRS_TILE  \\\n",
       "12  GS2A_20160117T182712_002984_N02.01     11SMS   \n",
       "30  GS2A_20160208T190542_003299_N02.01     10TFK   \n",
       "31  GS2A_20160525T190352_004829_N02.02     10SEG   \n",
       "40  GS2A_20161214T190802_007732_N02.04     10TDL   \n",
       "46  GS2A_20161111T185622_007260_N02.04     10TEM   \n",
       "\n",
       "                       SENSING_TIME   TOTAL_SIZE  CLOUD_COVER  \\\n",
       "12 2016-01-17 18:34:34.821000+00:00  279018323.0       8.0000   \n",
       "30 2016-02-08 19:06:05.324000+00:00   62268939.0       0.0000   \n",
       "31        2016-05-25 19:03:54+00:00  748966454.0      60.7736   \n",
       "40 2016-12-14 19:08:04.459000+00:00  608233858.0     100.0000   \n",
       "46 2016-11-11 18:57:23.790000+00:00  348753536.0      65.4965   \n",
       "\n",
       "   GEOMETRIC_QUALITY_FLAG              GENERATION_TIME  NORTH_LAT  SOUTH_LAT  \\\n",
       "12                 PASSED  2016-01-19T04:18:19.000000Z  33.439621  32.448398   \n",
       "30                 FAILED  2016-02-08T19:06:05.000000Z  40.644890  40.019779   \n",
       "31                 PASSED  2016-05-25T19:03:54.000000Z  37.947680  36.951572   \n",
       "40                 PASSED  2016-12-14T19:08:04.000000Z  41.551922  40.556797   \n",
       "46                 FAILED  2016-11-11T18:57:23.000000Z  42.449494  41.456335   \n",
       "\n",
       "      WEST_LON    EAST_LON                                           BASE_URL  \\\n",
       "12 -117.456101 -116.895105  gs://gcp-public-data-sentinel-2/tiles/11/S/MS/...   \n",
       "30 -121.828279 -121.608135  gs://gcp-public-data-sentinel-2/tiles/10/T/FK/...   \n",
       "31 -123.000342 -121.750759  gs://gcp-public-data-sentinel-2/tiles/10/S/EG/...   \n",
       "40 -124.199569 -122.883088  gs://gcp-public-data-sentinel-2/tiles/10/T/DL/...   \n",
       "46 -122.443546 -121.665230  gs://gcp-public-data-sentinel-2/tiles/10/T/EM/...   \n",
       "\n",
       "    num let sub  \n",
       "12   11   S  MS  \n",
       "30   10   T  FK  \n",
       "31   10   S  EG  \n",
       "40   10   T  DL  \n",
       "46   10   T  EM  "
      ]
     },
     "execution_count": 98,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df_l2a.head()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 99,
   "id": "c32a37f4",
   "metadata": {},
   "outputs": [],
   "source": [
    "# had trouble using pd merge so instead found this solution\n",
    "# https://stackoverflow.com/questions/30627968/merge-pandas-dataframes-where-one-value-is-between-two-others"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 100,
   "id": "42d5dbe4",
   "metadata": {},
   "outputs": [],
   "source": [
    "import sqlite3"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 101,
   "id": "b91aad59",
   "metadata": {},
   "outputs": [],
   "source": [
    "conn = sqlite3.connect(':memory:')\n",
    "\n",
    "df_l2a.to_sql('l2a', conn, index=False)\n",
    "df.to_sql('fire', conn, index=False)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 102,
   "id": "0defc731",
   "metadata": {},
   "outputs": [],
   "source": [
    "qry = '''\n",
    "SELECT * FROM l2a\n",
    "JOIN fire ON\n",
    "SENSING_TIME BETWEEN STARTED AND ENDED\n",
    "WHERE l2a.MGRS_TILE = fire.MGRS\n",
    "'''"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 103,
   "id": "9f411897",
   "metadata": {},
   "outputs": [],
   "source": [
    "df_merge = pd.read_sql_query(qry, conn)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 104,
   "id": "265fb758",
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "<class 'pandas.core.frame.DataFrame'>\n",
      "RangeIndex: 180 entries, 0 to 179\n",
      "Data columns (total 21 columns):\n",
      " #   Column                  Non-Null Count  Dtype  \n",
      "---  ------                  --------------  -----  \n",
      " 0   GRANULE_ID              180 non-null    object \n",
      " 1   PRODUCT_ID              180 non-null    object \n",
      " 2   DATATAKE_IDENTIFIER     180 non-null    object \n",
      " 3   MGRS_TILE               180 non-null    object \n",
      " 4   SENSING_TIME            180 non-null    object \n",
      " 5   TOTAL_SIZE              180 non-null    float64\n",
      " 6   CLOUD_COVER             180 non-null    float64\n",
      " 7   GEOMETRIC_QUALITY_FLAG  179 non-null    object \n",
      " 8   GENERATION_TIME         180 non-null    object \n",
      " 9   NORTH_LAT               180 non-null    float64\n",
      " 10  SOUTH_LAT               180 non-null    float64\n",
      " 11  WEST_LON                180 non-null    float64\n",
      " 12  EAST_LON                180 non-null    float64\n",
      " 13  BASE_URL                180 non-null    object \n",
      " 14  num                     180 non-null    int64  \n",
      " 15  let                     180 non-null    object \n",
      " 16  sub                     180 non-null    object \n",
      " 17  STARTED                 180 non-null    object \n",
      " 18  ENDED                   180 non-null    object \n",
      " 19  MGRS                    180 non-null    object \n",
      " 20  UNIQUE_ID               180 non-null    object \n",
      "dtypes: float64(6), int64(1), object(14)\n",
      "memory usage: 29.7+ KB\n"
     ]
    }
   ],
   "source": [
    "df_merge.info()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 105,
   "id": "d040b3a0",
   "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>GRANULE_ID</th>\n",
       "      <th>PRODUCT_ID</th>\n",
       "      <th>DATATAKE_IDENTIFIER</th>\n",
       "      <th>MGRS_TILE</th>\n",
       "      <th>SENSING_TIME</th>\n",
       "      <th>TOTAL_SIZE</th>\n",
       "      <th>CLOUD_COVER</th>\n",
       "      <th>GEOMETRIC_QUALITY_FLAG</th>\n",
       "      <th>GENERATION_TIME</th>\n",
       "      <th>NORTH_LAT</th>\n",
       "      <th>...</th>\n",
       "      <th>WEST_LON</th>\n",
       "      <th>EAST_LON</th>\n",
       "      <th>BASE_URL</th>\n",
       "      <th>num</th>\n",
       "      <th>let</th>\n",
       "      <th>sub</th>\n",
       "      <th>STARTED</th>\n",
       "      <th>ENDED</th>\n",
       "      <th>MGRS</th>\n",
       "      <th>UNIQUE_ID</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>L1C_T11SMT_A006030_20160817T183824</td>\n",
       "      <td>S2A_MSIL1C_20160817T182922_N0204_R027_T11SMT_2...</td>\n",
       "      <td>GS2A_20160817T182922_006030_N02.04</td>\n",
       "      <td>11SMT</td>\n",
       "      <td>2016-08-17 18:38:24.233000+00:00</td>\n",
       "      <td>877287683.0</td>\n",
       "      <td>0.5399</td>\n",
       "      <td>FAILED</td>\n",
       "      <td>2016-08-17T18:38:24.000000Z</td>\n",
       "      <td>34.341743</td>\n",
       "      <td>...</td>\n",
       "      <td>-118.087692</td>\n",
       "      <td>-116.893994</td>\n",
       "      <td>gs://gcp-public-data-sentinel-2/tiles/11/S/MT/...</td>\n",
       "      <td>11</td>\n",
       "      <td>S</td>\n",
       "      <td>MT</td>\n",
       "      <td>2016-08-16 00:00:00</td>\n",
       "      <td>2016-08-23 00:00:00</td>\n",
       "      <td>11SMT</td>\n",
       "      <td>ea5193a1-69bc-4684-bfcd-235c17d36756</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>L1C_T10TDM_A006874_20161015T190928</td>\n",
       "      <td>S2A_MSIL1C_20161015T190342_N0204_R013_T10TDM_2...</td>\n",
       "      <td>GS2A_20161015T190342_006874_N02.04</td>\n",
       "      <td>10TDM</td>\n",
       "      <td>2016-10-15 19:09:28.200000+00:00</td>\n",
       "      <td>601803878.0</td>\n",
       "      <td>100.0000</td>\n",
       "      <td>PASSED</td>\n",
       "      <td>2016-10-15T19:09:28.000000Z</td>\n",
       "      <td>42.452768</td>\n",
       "      <td>...</td>\n",
       "      <td>-124.216605</td>\n",
       "      <td>-122.881427</td>\n",
       "      <td>gs://gcp-public-data-sentinel-2/tiles/10/T/DM/...</td>\n",
       "      <td>10</td>\n",
       "      <td>T</td>\n",
       "      <td>DM</td>\n",
       "      <td>2016-06-07 00:00:00</td>\n",
       "      <td>2016-11-08 00:00:00</td>\n",
       "      <td>10TDM</td>\n",
       "      <td>103e1cb6-0a02-4ca8-8739-4cf77b5d1c98</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>S2A_OPER_MSI_L1C_TL_MPS__20160628T232902_A0053...</td>\n",
       "      <td>S2A_OPER_PRD_MSIL1C_PDMC_20160629T001014_R027_...</td>\n",
       "      <td>GS2A_20160628T184432_005315_N02.04</td>\n",
       "      <td>11SLV</td>\n",
       "      <td>2016-06-28 18:44:29.458000+00:00</td>\n",
       "      <td>394959521.0</td>\n",
       "      <td>38.2607</td>\n",
       "      <td>PASSED</td>\n",
       "      <td>2016-06-29T00:10:14.000000Z</td>\n",
       "      <td>35.826762</td>\n",
       "      <td>...</td>\n",
       "      <td>-119.199331</td>\n",
       "      <td>-117.990390</td>\n",
       "      <td>gs://gcp-public-data-sentinel-2/tiles/11/S/LV/...</td>\n",
       "      <td>11</td>\n",
       "      <td>S</td>\n",
       "      <td>LV</td>\n",
       "      <td>2016-06-23 00:00:00</td>\n",
       "      <td>2016-07-11 00:00:00</td>\n",
       "      <td>11SLV</td>\n",
       "      <td>91816ff6-d4ec-4f31-8d40-bf2538aca09b</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>S2A_OPER_MSI_L1C_TL_SGS__20160628T234323_A0053...</td>\n",
       "      <td>S2A_OPER_PRD_MSIL1C_PDMC_20160629T002006_R027_...</td>\n",
       "      <td>GS2A_20160628T184432_005315_N02.04</td>\n",
       "      <td>11SNS</td>\n",
       "      <td>2016-06-28 18:44:47.497000+00:00</td>\n",
       "      <td>250358253.0</td>\n",
       "      <td>0.0000</td>\n",
       "      <td>PASSED</td>\n",
       "      <td>2016-06-29T00:20:06.000000Z</td>\n",
       "      <td>33.439665</td>\n",
       "      <td>...</td>\n",
       "      <td>-117.000323</td>\n",
       "      <td>-116.440893</td>\n",
       "      <td>gs://gcp-public-data-sentinel-2/tiles/11/S/NS/...</td>\n",
       "      <td>11</td>\n",
       "      <td>S</td>\n",
       "      <td>NS</td>\n",
       "      <td>2016-06-19 00:00:00</td>\n",
       "      <td>2016-06-30 00:00:00</td>\n",
       "      <td>11SNS</td>\n",
       "      <td>33099cdd-ccfc-454e-b5a3-770a69a30bd3</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>L1C_T10TDL_A006016_20160816T190939</td>\n",
       "      <td>S2A_MSIL1C_20160816T185922_N0204_R013_T10TDL_2...</td>\n",
       "      <td>GS2A_20160816T185922_006016_N02.04</td>\n",
       "      <td>10TDL</td>\n",
       "      <td>2016-08-16 19:09:39.996000+00:00</td>\n",
       "      <td>829006155.0</td>\n",
       "      <td>4.2208</td>\n",
       "      <td>PASSED</td>\n",
       "      <td>2016-08-16T19:09:39.000000Z</td>\n",
       "      <td>41.551922</td>\n",
       "      <td>...</td>\n",
       "      <td>-124.199569</td>\n",
       "      <td>-122.883088</td>\n",
       "      <td>gs://gcp-public-data-sentinel-2/tiles/10/T/DL/...</td>\n",
       "      <td>10</td>\n",
       "      <td>T</td>\n",
       "      <td>DL</td>\n",
       "      <td>2016-08-12 00:00:00</td>\n",
       "      <td>2016-08-19 00:00:00</td>\n",
       "      <td>10TDL</td>\n",
       "      <td>861bfbd3-ce1c-46bd-a32b-2848b7399119</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "<p>5 rows × 21 columns</p>\n",
       "</div>"
      ],
      "text/plain": [
       "                                          GRANULE_ID  \\\n",
       "0                 L1C_T11SMT_A006030_20160817T183824   \n",
       "1                 L1C_T10TDM_A006874_20161015T190928   \n",
       "2  S2A_OPER_MSI_L1C_TL_MPS__20160628T232902_A0053...   \n",
       "3  S2A_OPER_MSI_L1C_TL_SGS__20160628T234323_A0053...   \n",
       "4                 L1C_T10TDL_A006016_20160816T190939   \n",
       "\n",
       "                                          PRODUCT_ID  \\\n",
       "0  S2A_MSIL1C_20160817T182922_N0204_R027_T11SMT_2...   \n",
       "1  S2A_MSIL1C_20161015T190342_N0204_R013_T10TDM_2...   \n",
       "2  S2A_OPER_PRD_MSIL1C_PDMC_20160629T001014_R027_...   \n",
       "3  S2A_OPER_PRD_MSIL1C_PDMC_20160629T002006_R027_...   \n",
       "4  S2A_MSIL1C_20160816T185922_N0204_R013_T10TDL_2...   \n",
       "\n",
       "                  DATATAKE_IDENTIFIER MGRS_TILE  \\\n",
       "0  GS2A_20160817T182922_006030_N02.04     11SMT   \n",
       "1  GS2A_20161015T190342_006874_N02.04     10TDM   \n",
       "2  GS2A_20160628T184432_005315_N02.04     11SLV   \n",
       "3  GS2A_20160628T184432_005315_N02.04     11SNS   \n",
       "4  GS2A_20160816T185922_006016_N02.04     10TDL   \n",
       "\n",
       "                       SENSING_TIME   TOTAL_SIZE  CLOUD_COVER  \\\n",
       "0  2016-08-17 18:38:24.233000+00:00  877287683.0       0.5399   \n",
       "1  2016-10-15 19:09:28.200000+00:00  601803878.0     100.0000   \n",
       "2  2016-06-28 18:44:29.458000+00:00  394959521.0      38.2607   \n",
       "3  2016-06-28 18:44:47.497000+00:00  250358253.0       0.0000   \n",
       "4  2016-08-16 19:09:39.996000+00:00  829006155.0       4.2208   \n",
       "\n",
       "  GEOMETRIC_QUALITY_FLAG              GENERATION_TIME  NORTH_LAT  ...  \\\n",
       "0                 FAILED  2016-08-17T18:38:24.000000Z  34.341743  ...   \n",
       "1                 PASSED  2016-10-15T19:09:28.000000Z  42.452768  ...   \n",
       "2                 PASSED  2016-06-29T00:10:14.000000Z  35.826762  ...   \n",
       "3                 PASSED  2016-06-29T00:20:06.000000Z  33.439665  ...   \n",
       "4                 PASSED  2016-08-16T19:09:39.000000Z  41.551922  ...   \n",
       "\n",
       "     WEST_LON    EAST_LON                                           BASE_URL  \\\n",
       "0 -118.087692 -116.893994  gs://gcp-public-data-sentinel-2/tiles/11/S/MT/...   \n",
       "1 -124.216605 -122.881427  gs://gcp-public-data-sentinel-2/tiles/10/T/DM/...   \n",
       "2 -119.199331 -117.990390  gs://gcp-public-data-sentinel-2/tiles/11/S/LV/...   \n",
       "3 -117.000323 -116.440893  gs://gcp-public-data-sentinel-2/tiles/11/S/NS/...   \n",
       "4 -124.199569 -122.883088  gs://gcp-public-data-sentinel-2/tiles/10/T/DL/...   \n",
       "\n",
       "  num  let sub              STARTED                ENDED   MGRS  \\\n",
       "0  11    S  MT  2016-08-16 00:00:00  2016-08-23 00:00:00  11SMT   \n",
       "1  10    T  DM  2016-06-07 00:00:00  2016-11-08 00:00:00  10TDM   \n",
       "2  11    S  LV  2016-06-23 00:00:00  2016-07-11 00:00:00  11SLV   \n",
       "3  11    S  NS  2016-06-19 00:00:00  2016-06-30 00:00:00  11SNS   \n",
       "4  10    T  DL  2016-08-12 00:00:00  2016-08-19 00:00:00  10TDL   \n",
       "\n",
       "                              UNIQUE_ID  \n",
       "0  ea5193a1-69bc-4684-bfcd-235c17d36756  \n",
       "1  103e1cb6-0a02-4ca8-8739-4cf77b5d1c98  \n",
       "2  91816ff6-d4ec-4f31-8d40-bf2538aca09b  \n",
       "3  33099cdd-ccfc-454e-b5a3-770a69a30bd3  \n",
       "4  861bfbd3-ce1c-46bd-a32b-2848b7399119  \n",
       "\n",
       "[5 rows x 21 columns]"
      ]
     },
     "execution_count": 105,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df_merge.head()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 106,
   "id": "b9d9fe43",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "True"
      ]
     },
     "execution_count": 106,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df_merge['MGRS_TILE'].sum() == df_merge['MGRS'].sum()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 109,
   "id": "026adc3f",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "UNIQUE_ID\n",
       "103e1cb6-0a02-4ca8-8739-4cf77b5d1c98    21\n",
       "ffb3293b-3349-49c5-a7cb-c488391e8a72    16\n",
       "89c304db-e288-4582-90d4-884d57fd71f9    11\n",
       "e247e423-6171-4184-9dca-b373de703885    10\n",
       "2458cd6c-82d6-43ef-b509-706e3af69451     9\n",
       "954fb899-decb-461b-a0dd-76442e3370d3     8\n",
       "91816ff6-d4ec-4f31-8d40-bf2538aca09b     8\n",
       "1bc85249-0d96-42d7-a23f-db99bdce17a4     6\n",
       "5c86c26b-e6c0-4e47-90b7-bc5da3885ebe     6\n",
       "b8cf80c7-b617-4fe4-91b0-43b40d38c7f6     4\n",
       "b604ffa7-8cee-4724-bb82-f1f4553a192f     4\n",
       "423df2bd-4464-44e0-8b8a-74f953613287     4\n",
       "2dae3029-2d17-445a-9fac-e95345b179e0     4\n",
       "33099cdd-ccfc-454e-b5a3-770a69a30bd3     4\n",
       "c27c5212-1891-4105-86ee-9f97016ef159     4\n",
       "6afa1ccf-e1b6-492b-bcaa-9189cd3487aa     4\n",
       "92c20948-6ff6-4a2c-b13f-32c75e71b16c     3\n",
       "040d03d0-fbdd-438c-8174-f81e4a1bba57     3\n",
       "55fa95e6-f09e-42c4-889b-a4b5b63dccf2     2\n",
       "85832754-5848-4454-84fc-8b0a9abbff40     2\n",
       "f1769b9d-c522-4a0d-92a7-f9c8321b9f1d     2\n",
       "db833182-22df-44cc-ba10-0901e74222bc     2\n",
       "d96a2f4f-8e4d-4534-abe2-d90530e2bbc3     2\n",
       "c88af25d-aff4-4b20-9c58-0b819c8ae99a     2\n",
       "bae93037-2a91-4189-b256-3325cd2b6979     2\n",
       "140def53-175c-4f5d-b8e3-de0643781ff7     2\n",
       "a0950d5e-6f35-4023-bb60-6737146ba7bd     2\n",
       "79b446a4-d809-4aa2-9f30-091a05ed697d     2\n",
       "403e6185-c0e1-4b8f-a290-b50553a47b67     2\n",
       "69b7a19e-7be7-43c1-8779-c7110b22f552     2\n",
       "69c7b8cc-061e-4ca6-b5e8-83c6ae9d8a25     2\n",
       "6f07d935-7d64-4be8-8e96-d34384686bf8     2\n",
       "78eccd24-9fe2-4ccd-90fa-a1e0fdc19b41     2\n",
       "140161a1-ca06-448a-87f2-27e688b2d922     1\n",
       "f6afb8c4-32dc-4e6d-8d30-e6b7ccd4a62f     1\n",
       "59084c8f-904c-42cb-9aa8-cae4a1f8c6ef     1\n",
       "ed16bf52-f886-4384-b841-2e755234aa29     1\n",
       "ea5193a1-69bc-4684-bfcd-235c17d36756     1\n",
       "1243f711-c29f-4bc3-a7e7-c705a6fc40f2     1\n",
       "da72b0c2-835d-42a5-8fa8-4de5442610cc     1\n",
       "d045584a-8838-4d64-91dc-3ebfbcd8e708     1\n",
       "057fd4e2-7400-4fc4-9b23-3daf67fc8610     1\n",
       "bd2b7bdc-0f92-451a-a3a5-03569d27c952     1\n",
       "b82b0e22-4155-43dd-ac33-1da015b6d3d2     1\n",
       "1f8b3267-c3bd-43f5-9733-2b30c97d45ee     1\n",
       "5823867d-c458-4673-858f-0ded99b6121f     1\n",
       "97772dc4-7fde-4412-982c-974b3316e22b     1\n",
       "2656ad63-b3e1-467f-9982-0229fd31c0f5     1\n",
       "2a2e0af5-24d7-4903-bab4-3705f5d788fb     1\n",
       "346c59b1-375e-4d42-876a-d97b769c939f     1\n",
       "8d9b87e4-6fdb-49e4-ba6d-be85ffd6b222     1\n",
       "383c3df6-a1eb-40fd-b798-41481dff679a     1\n",
       "861bfbd3-ce1c-46bd-a32b-2848b7399119     1\n",
       "3a4871dc-2299-4934-8a31-b92780fe31a3     1\n",
       "Name: UNIQUE_ID, dtype: int64"
      ]
     },
     "execution_count": 109,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "#reoccuring fires, the merge created many duplicates\n",
    "df_merge.groupby('UNIQUE_ID')['UNIQUE_ID'].count().sort_values(ascending = False)#.reset_index()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 110,
   "id": "60c0380b",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "BASE_URL\n",
       "gs://gcp-public-data-sentinel-2/tiles/11/S/LA/S2A_MSIL1C_20161105T183542_N0204_R027_T11SLA_20161105T184337.SAFE    2\n",
       "gs://gcp-public-data-sentinel-2/tiles/11/S/LA/S2A_MSIL1C_20161029T184512_N0204_R070_T11SLA_20161029T184636.SAFE    2\n",
       "gs://gcp-public-data-sentinel-2/tiles/11/S/KU/S2A_MSIL1C_20160618T183831_N0204_R027_T11SKU_20160619T013511.SAFE    2\n",
       "gs://gcp-public-data-sentinel-2/tiles/11/S/KU/S2A_MSIL1C_20160618T182922_N0204_R027_T11SKU_20160618T183831.SAFE    2\n",
       "gs://gcp-public-data-sentinel-2/tiles/11/S/MT/S2A_MSIL1C_20160903T183312_N0204_R127_T11SMT_20160903T183309.SAFE    2\n",
       "                                                                                                                  ..\n",
       "gs://gcp-public-data-sentinel-2/tiles/10/T/DM/S2A_MSIL1C_20161028T191512_N0204_R056_T10TDM_20161028T191513.SAFE    1\n",
       "gs://gcp-public-data-sentinel-2/tiles/10/T/DM/S2A_MSIL1C_20161104T190552_N0204_R013_T10TDM_20161104T190549.SAFE    1\n",
       "gs://gcp-public-data-sentinel-2/tiles/10/T/EK/S2A_MSIL1C_20160803T184922_N0204_R113_T10TEK_20160803T190133.SAFE    1\n",
       "gs://gcp-public-data-sentinel-2/tiles/10/T/EM/S2A_MSIL1C_20160912T184952_N0204_R113_T10TEM_20160912T190101.SAFE    1\n",
       "gs://gcp-public-data-sentinel-2/tiles/11/S/NS/S2A_MSIL1C_20160718T184432_N0204_R027_T11SNS_20160718T184431.SAFE    1\n",
       "Name: BASE_URL, Length: 175, dtype: int64"
      ]
     },
     "execution_count": 110,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# some overlap that will be adressed later in thihs notebook\n",
    "df_merge.groupby('BASE_URL')['BASE_URL'].count().sort_values(ascending = False)#.reset_index()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 112,
   "id": "7877ae75",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "GRANULE_ID                0\n",
       "PRODUCT_ID                0\n",
       "DATATAKE_IDENTIFIER       0\n",
       "MGRS_TILE                 0\n",
       "SENSING_TIME              0\n",
       "TOTAL_SIZE                0\n",
       "CLOUD_COVER               0\n",
       "GEOMETRIC_QUALITY_FLAG    1\n",
       "GENERATION_TIME           0\n",
       "NORTH_LAT                 0\n",
       "SOUTH_LAT                 0\n",
       "WEST_LON                  0\n",
       "EAST_LON                  0\n",
       "BASE_URL                  0\n",
       "num                       0\n",
       "let                       0\n",
       "sub                       0\n",
       "STARTED                   0\n",
       "ENDED                     0\n",
       "MGRS                      0\n",
       "UNIQUE_ID                 0\n",
       "dtype: int64"
      ]
     },
     "execution_count": 112,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df_merge.isna().sum()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 113,
   "id": "f81f6ae8",
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "<class 'pandas.core.frame.DataFrame'>\n",
      "RangeIndex: 180 entries, 0 to 179\n",
      "Data columns (total 21 columns):\n",
      " #   Column                  Non-Null Count  Dtype  \n",
      "---  ------                  --------------  -----  \n",
      " 0   GRANULE_ID              180 non-null    object \n",
      " 1   PRODUCT_ID              180 non-null    object \n",
      " 2   DATATAKE_IDENTIFIER     180 non-null    object \n",
      " 3   MGRS_TILE               180 non-null    object \n",
      " 4   SENSING_TIME            180 non-null    object \n",
      " 5   TOTAL_SIZE              180 non-null    float64\n",
      " 6   CLOUD_COVER             180 non-null    float64\n",
      " 7   GEOMETRIC_QUALITY_FLAG  179 non-null    object \n",
      " 8   GENERATION_TIME         180 non-null    object \n",
      " 9   NORTH_LAT               180 non-null    float64\n",
      " 10  SOUTH_LAT               180 non-null    float64\n",
      " 11  WEST_LON                180 non-null    float64\n",
      " 12  EAST_LON                180 non-null    float64\n",
      " 13  BASE_URL                180 non-null    object \n",
      " 14  num                     180 non-null    int64  \n",
      " 15  let                     180 non-null    object \n",
      " 16  sub                     180 non-null    object \n",
      " 17  STARTED                 180 non-null    object \n",
      " 18  ENDED                   180 non-null    object \n",
      " 19  MGRS                    180 non-null    object \n",
      " 20  UNIQUE_ID               180 non-null    object \n",
      "dtypes: float64(6), int64(1), object(14)\n",
      "memory usage: 29.7+ KB\n"
     ]
    }
   ],
   "source": [
    "df_merge.info()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 114,
   "id": "c6992f54",
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "<class 'pandas.core.frame.DataFrame'>\n",
      "Int64Index: 3959 entries, 12 to 41217\n",
      "Data columns (total 17 columns):\n",
      " #   Column                  Non-Null Count  Dtype              \n",
      "---  ------                  --------------  -----              \n",
      " 0   GRANULE_ID              3959 non-null   object             \n",
      " 1   PRODUCT_ID              3959 non-null   object             \n",
      " 2   DATATAKE_IDENTIFIER     3959 non-null   object             \n",
      " 3   MGRS_TILE               3959 non-null   object             \n",
      " 4   SENSING_TIME            3959 non-null   datetime64[ns, UTC]\n",
      " 5   TOTAL_SIZE              3674 non-null   float64            \n",
      " 6   CLOUD_COVER             3959 non-null   float64            \n",
      " 7   GEOMETRIC_QUALITY_FLAG  3937 non-null   object             \n",
      " 8   GENERATION_TIME         3959 non-null   object             \n",
      " 9   NORTH_LAT               3959 non-null   float64            \n",
      " 10  SOUTH_LAT               3959 non-null   float64            \n",
      " 11  WEST_LON                3959 non-null   float64            \n",
      " 12  EAST_LON                3959 non-null   float64            \n",
      " 13  BASE_URL                3959 non-null   object             \n",
      " 14  num                     3959 non-null   int64              \n",
      " 15  let                     3959 non-null   object             \n",
      " 16  sub                     3959 non-null   object             \n",
      "dtypes: datetime64[ns, UTC](1), float64(6), int64(1), object(9)\n",
      "memory usage: 556.7+ KB\n"
     ]
    }
   ],
   "source": [
    "df_l2a.info()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 115,
   "id": "c02fa191",
   "metadata": {
    "scrolled": true
   },
   "outputs": [
    {
     "data": {
      "text/plain": [
       "175"
      ]
     },
     "execution_count": 115,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "len(df_merge['GRANULE_ID'].unique().tolist())"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 116,
   "id": "0ddcd36c",
   "metadata": {
    "scrolled": true
   },
   "outputs": [
    {
     "data": {
      "text/plain": [
       "3959"
      ]
     },
     "execution_count": 116,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "len(df_l2a['GRANULE_ID'].unique().tolist())"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 117,
   "id": "1485d0e3",
   "metadata": {
    "scrolled": true
   },
   "outputs": [
    {
     "data": {
      "text/plain": [
       "175"
      ]
     },
     "execution_count": 117,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "len(df_merge['BASE_URL'].unique().tolist())"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 120,
   "id": "2671986a",
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "<class 'pandas.core.frame.DataFrame'>\n",
      "RangeIndex: 180 entries, 0 to 179\n",
      "Data columns (total 21 columns):\n",
      " #   Column                  Non-Null Count  Dtype  \n",
      "---  ------                  --------------  -----  \n",
      " 0   GRANULE_ID              180 non-null    object \n",
      " 1   PRODUCT_ID              180 non-null    object \n",
      " 2   DATATAKE_IDENTIFIER     180 non-null    object \n",
      " 3   MGRS_TILE               180 non-null    object \n",
      " 4   SENSING_TIME            180 non-null    object \n",
      " 5   TOTAL_SIZE              180 non-null    float64\n",
      " 6   CLOUD_COVER             180 non-null    float64\n",
      " 7   GEOMETRIC_QUALITY_FLAG  179 non-null    object \n",
      " 8   GENERATION_TIME         180 non-null    object \n",
      " 9   NORTH_LAT               180 non-null    float64\n",
      " 10  SOUTH_LAT               180 non-null    float64\n",
      " 11  WEST_LON                180 non-null    float64\n",
      " 12  EAST_LON                180 non-null    float64\n",
      " 13  BASE_URL                180 non-null    object \n",
      " 14  num                     180 non-null    int64  \n",
      " 15  let                     180 non-null    object \n",
      " 16  sub                     180 non-null    object \n",
      " 17  STARTED                 180 non-null    object \n",
      " 18  ENDED                   180 non-null    object \n",
      " 19  MGRS                    180 non-null    object \n",
      " 20  UNIQUE_ID               180 non-null    object \n",
      "dtypes: float64(6), int64(1), object(14)\n",
      "memory usage: 29.7+ KB\n"
     ]
    }
   ],
   "source": [
    "df_merge.info()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 121,
   "id": "c32a72d6",
   "metadata": {},
   "outputs": [],
   "source": [
    "# the sql join appeard to remove the datatime conversion\n",
    "df_merge['SENSING_TIME'] = pd.to_datetime(df_merge['SENSING_TIME'])"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 122,
   "id": "670a29dc",
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "<class 'pandas.core.frame.DataFrame'>\n",
      "RangeIndex: 180 entries, 0 to 179\n",
      "Data columns (total 21 columns):\n",
      " #   Column                  Non-Null Count  Dtype              \n",
      "---  ------                  --------------  -----              \n",
      " 0   GRANULE_ID              180 non-null    object             \n",
      " 1   PRODUCT_ID              180 non-null    object             \n",
      " 2   DATATAKE_IDENTIFIER     180 non-null    object             \n",
      " 3   MGRS_TILE               180 non-null    object             \n",
      " 4   SENSING_TIME            180 non-null    datetime64[ns, UTC]\n",
      " 5   TOTAL_SIZE              180 non-null    float64            \n",
      " 6   CLOUD_COVER             180 non-null    float64            \n",
      " 7   GEOMETRIC_QUALITY_FLAG  179 non-null    object             \n",
      " 8   GENERATION_TIME         180 non-null    object             \n",
      " 9   NORTH_LAT               180 non-null    float64            \n",
      " 10  SOUTH_LAT               180 non-null    float64            \n",
      " 11  WEST_LON                180 non-null    float64            \n",
      " 12  EAST_LON                180 non-null    float64            \n",
      " 13  BASE_URL                180 non-null    object             \n",
      " 14  num                     180 non-null    int64              \n",
      " 15  let                     180 non-null    object             \n",
      " 16  sub                     180 non-null    object             \n",
      " 17  STARTED                 180 non-null    object             \n",
      " 18  ENDED                   180 non-null    object             \n",
      " 19  MGRS                    180 non-null    object             \n",
      " 20  UNIQUE_ID               180 non-null    object             \n",
      "dtypes: datetime64[ns, UTC](1), float64(6), int64(1), object(13)\n",
      "memory usage: 29.7+ KB\n"
     ]
    }
   ],
   "source": [
    "df_merge.info()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 123,
   "id": "7abaa428",
   "metadata": {},
   "outputs": [],
   "source": [
    "#add date column\n",
    "df_merge['DATE'] = df_merge['SENSING_TIME'].dt.date "
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 124,
   "metadata": {},
   "outputs": [],
   "source": [
    "'''\n",
    "the below code eliminates duplicate .safe folders by saving the .safe folder that is the largest size.\n",
    "throughout this research I found that there were often duplicates of one image scene and the quality varried, \n",
    "generally the larger size .safe folder had thhe best quality scene\n",
    "https://stackoverflow.com/questions/12497402/python-pandas-remove-duplicates-by-columns-a-keeping-the-row-with-the-highest\n",
    "'''\n",
    "df_merge = df_merge.sort_values('TOTAL_SIZE', ascending=False).drop_duplicates(['MGRS_TILE', 'DATE']).sort_index()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 125,
   "id": "60c25df2",
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "<class 'pandas.core.frame.DataFrame'>\n",
      "Int64Index: 121 entries, 0 to 179\n",
      "Data columns (total 22 columns):\n",
      " #   Column                  Non-Null Count  Dtype              \n",
      "---  ------                  --------------  -----              \n",
      " 0   GRANULE_ID              121 non-null    object             \n",
      " 1   PRODUCT_ID              121 non-null    object             \n",
      " 2   DATATAKE_IDENTIFIER     121 non-null    object             \n",
      " 3   MGRS_TILE               121 non-null    object             \n",
      " 4   SENSING_TIME            121 non-null    datetime64[ns, UTC]\n",
      " 5   TOTAL_SIZE              121 non-null    float64            \n",
      " 6   CLOUD_COVER             121 non-null    float64            \n",
      " 7   GEOMETRIC_QUALITY_FLAG  120 non-null    object             \n",
      " 8   GENERATION_TIME         121 non-null    object             \n",
      " 9   NORTH_LAT               121 non-null    float64            \n",
      " 10  SOUTH_LAT               121 non-null    float64            \n",
      " 11  WEST_LON                121 non-null    float64            \n",
      " 12  EAST_LON                121 non-null    float64            \n",
      " 13  BASE_URL                121 non-null    object             \n",
      " 14  num                     121 non-null    int64              \n",
      " 15  let                     121 non-null    object             \n",
      " 16  sub                     121 non-null    object             \n",
      " 17  STARTED                 121 non-null    object             \n",
      " 18  ENDED                   121 non-null    object             \n",
      " 19  MGRS                    121 non-null    object             \n",
      " 20  UNIQUE_ID               121 non-null    object             \n",
      " 21  DATE                    121 non-null    object             \n",
      "dtypes: datetime64[ns, UTC](1), float64(6), int64(1), object(14)\n",
      "memory usage: 21.7+ KB\n"
     ]
    }
   ],
   "source": [
    "df_merge.info()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 126,
   "id": "b38c581a",
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "121\n",
      "121\n"
     ]
    }
   ],
   "source": [
    "print(len(df_merge['GRANULE_ID'].unique()))\n",
    "print(len(df_merge['PRODUCT_ID'].unique()))"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 127,
   "id": "739c94dc",
   "metadata": {},
   "outputs": [],
   "source": [
    "'''\n",
    "This finds the next granule id. Basically we want to feed sequntial images to our neural network and we do\n",
    "this by tracking the granule id and the next tile granule id.\n",
    "'''\n",
    "\n",
    "# gets the next tile and time of the next tile grouped by MGRS\n",
    "df_merge['NEXT_TILE'] = df_merge.sort_values('SENSING_TIME', ascending=False).groupby('MGRS')['GRANULE_ID'].shift()\n",
    "df_merge['NEXT_TILE_TIME'] = df_merge.sort_values('SENSING_TIME', ascending=False).groupby('MGRS')['SENSING_TIME'].shift()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 128,
   "metadata": {},
   "outputs": [],
   "source": [
    "# days betweeen first tile and next tile\n",
    "# https://stackoverflow.com/questions/37840812/pandas-subtracting-two-date-columns-and-the-result-being-an-integer/46966942\n",
    "df_merge['DAYS_BETWEEN'] = (df_merge['NEXT_TILE_TIME'] - df_merge['SENSING_TIME']).dt.days"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 129,
   "id": "8703c3ad",
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "<class 'pandas.core.frame.DataFrame'>\n",
      "Int64Index: 61 entries, 5 to 177\n",
      "Data columns (total 25 columns):\n",
      " #   Column                  Non-Null Count  Dtype              \n",
      "---  ------                  --------------  -----              \n",
      " 0   GRANULE_ID              61 non-null     object             \n",
      " 1   PRODUCT_ID              61 non-null     object             \n",
      " 2   DATATAKE_IDENTIFIER     61 non-null     object             \n",
      " 3   MGRS_TILE               61 non-null     object             \n",
      " 4   SENSING_TIME            61 non-null     datetime64[ns, UTC]\n",
      " 5   TOTAL_SIZE              61 non-null     float64            \n",
      " 6   CLOUD_COVER             61 non-null     float64            \n",
      " 7   GEOMETRIC_QUALITY_FLAG  60 non-null     object             \n",
      " 8   GENERATION_TIME         61 non-null     object             \n",
      " 9   NORTH_LAT               61 non-null     float64            \n",
      " 10  SOUTH_LAT               61 non-null     float64            \n",
      " 11  WEST_LON                61 non-null     float64            \n",
      " 12  EAST_LON                61 non-null     float64            \n",
      " 13  BASE_URL                61 non-null     object             \n",
      " 14  num                     61 non-null     int64              \n",
      " 15  let                     61 non-null     object             \n",
      " 16  sub                     61 non-null     object             \n",
      " 17  STARTED                 61 non-null     object             \n",
      " 18  ENDED                   61 non-null     object             \n",
      " 19  MGRS                    61 non-null     object             \n",
      " 20  UNIQUE_ID               61 non-null     object             \n",
      " 21  DATE                    61 non-null     object             \n",
      " 22  NEXT_TILE               61 non-null     object             \n",
      " 23  NEXT_TILE_TIME          61 non-null     datetime64[ns, UTC]\n",
      " 24  DAYS_BETWEEN            61 non-null     float64            \n",
      "dtypes: datetime64[ns, UTC](2), float64(7), int64(1), object(15)\n",
      "memory usage: 12.4+ KB\n"
     ]
    }
   ],
   "source": [
    "#remove tiles more than 10 days apart\n",
    "df_merge[df_merge['DAYS_BETWEEN'] <= 10].info()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 130,
   "id": "ce874719",
   "metadata": {},
   "outputs": [],
   "source": [
    "df_merge =  df_merge[df_merge['DAYS_BETWEEN'] <= 10].sort_values('MGRS')"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 131,
   "id": "35d68eb4",
   "metadata": {},
   "outputs": [],
   "source": [
    "# remove .safe files that are smaller than 300 mb\n",
    "df_merge = df_merge[df_merge['TOTAL_SIZE'] > 300000000]"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 132,
   "id": "f273208f",
   "metadata": {},
   "outputs": [],
   "source": [
    "#save progress\n",
    "df_merge.to_csv('in_progess_2016.csv', index=False)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 1,
   "id": "fd6a1d43",
   "metadata": {},
   "outputs": [],
   "source": [
    "import pandas as pd\n",
    "import numpy as np"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 2,
   "id": "1be75afb",
   "metadata": {},
   "outputs": [],
   "source": [
    "df=pd.read_csv('in_progess_2016.csv')"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 3,
   "id": "b0e26aa5",
   "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>GRANULE_ID</th>\n",
       "      <th>PRODUCT_ID</th>\n",
       "      <th>DATATAKE_IDENTIFIER</th>\n",
       "      <th>MGRS_TILE</th>\n",
       "      <th>SENSING_TIME</th>\n",
       "      <th>TOTAL_SIZE</th>\n",
       "      <th>CLOUD_COVER</th>\n",
       "      <th>GEOMETRIC_QUALITY_FLAG</th>\n",
       "      <th>GENERATION_TIME</th>\n",
       "      <th>NORTH_LAT</th>\n",
       "      <th>...</th>\n",
       "      <th>let</th>\n",
       "      <th>sub</th>\n",
       "      <th>STARTED</th>\n",
       "      <th>ENDED</th>\n",
       "      <th>MGRS</th>\n",
       "      <th>UNIQUE_ID</th>\n",
       "      <th>DATE</th>\n",
       "      <th>NEXT_TILE</th>\n",
       "      <th>NEXT_TILE_TIME</th>\n",
       "      <th>DAYS_BETWEEN</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>L1C_T10SEJ_A006016_20160816T190939</td>\n",
       "      <td>S2A_MSIL1C_20160816T185922_N0204_R013_T10SEJ_2...</td>\n",
       "      <td>GS2A_20160816T185922_006016_N02.04</td>\n",
       "      <td>10SEJ</td>\n",
       "      <td>2016-08-16 19:09:39.996000+00:00</td>\n",
       "      <td>596451172.0</td>\n",
       "      <td>0.0000</td>\n",
       "      <td>PASSED</td>\n",
       "      <td>2016-08-16T19:09:39.000000Z</td>\n",
       "      <td>39.750358</td>\n",
       "      <td>...</td>\n",
       "      <td>S</td>\n",
       "      <td>EJ</td>\n",
       "      <td>2016-08-13 00:00:00</td>\n",
       "      <td>2016-08-26 00:00:00</td>\n",
       "      <td>10SEJ</td>\n",
       "      <td>a0950d5e-6f35-4023-bb60-6737146ba7bd</td>\n",
       "      <td>2016-08-16</td>\n",
       "      <td>L1C_T10SEJ_A006116_20160823T185715</td>\n",
       "      <td>2016-08-23 18:57:15.690000+00:00</td>\n",
       "      <td>6.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>L1C_T10SFE_A004972_20160604T185400</td>\n",
       "      <td>S2A_MSIL1C_20160604T184922_N0202_R113_T10SFE_2...</td>\n",
       "      <td>GS2A_20160604T184922_004972_N02.02</td>\n",
       "      <td>10SFE</td>\n",
       "      <td>2016-06-04 18:54:00+00:00</td>\n",
       "      <td>695518354.0</td>\n",
       "      <td>65.0656</td>\n",
       "      <td>PASSED</td>\n",
       "      <td>2016-06-04T18:54:00.000000Z</td>\n",
       "      <td>36.139831</td>\n",
       "      <td>...</td>\n",
       "      <td>S</td>\n",
       "      <td>FE</td>\n",
       "      <td>2016-06-04 00:00:00</td>\n",
       "      <td>2016-06-20 00:00:00</td>\n",
       "      <td>10SFE</td>\n",
       "      <td>b8cf80c7-b617-4fe4-91b0-43b40d38c7f6</td>\n",
       "      <td>2016-06-04</td>\n",
       "      <td>L1C_T10SFE_A005072_20160611T184746</td>\n",
       "      <td>2016-06-11 18:47:46+00:00</td>\n",
       "      <td>6.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>L1C_T10SFE_A006116_20160823T185715</td>\n",
       "      <td>S2A_MSIL1C_20160823T184922_N0204_R113_T10SFE_2...</td>\n",
       "      <td>GS2A_20160823T184922_006116_N02.04</td>\n",
       "      <td>10SFE</td>\n",
       "      <td>2016-08-23 18:57:15.690000+00:00</td>\n",
       "      <td>698481486.0</td>\n",
       "      <td>44.5017</td>\n",
       "      <td>PASSED</td>\n",
       "      <td>2016-08-23T18:57:15.000000Z</td>\n",
       "      <td>36.139831</td>\n",
       "      <td>...</td>\n",
       "      <td>S</td>\n",
       "      <td>FE</td>\n",
       "      <td>2016-08-13 00:00:00</td>\n",
       "      <td>2016-09-06 00:00:00</td>\n",
       "      <td>10SFE</td>\n",
       "      <td>040d03d0-fbdd-438c-8174-f81e4a1bba57</td>\n",
       "      <td>2016-08-23</td>\n",
       "      <td>L1C_T10SFE_A006216_20160830T185344</td>\n",
       "      <td>2016-08-30 18:53:44.330000+00:00</td>\n",
       "      <td>6.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>L1C_T10SFE_A006073_20160820T185430</td>\n",
       "      <td>S2A_MSIL1C_20160820T185432_N0204_R070_T10SFE_2...</td>\n",
       "      <td>GS2A_20160820T185432_006073_N02.04</td>\n",
       "      <td>10SFE</td>\n",
       "      <td>2016-08-20 18:54:30.460000+00:00</td>\n",
       "      <td>525774690.0</td>\n",
       "      <td>53.1735</td>\n",
       "      <td>PASSED</td>\n",
       "      <td>2016-08-20T18:54:30.000000Z</td>\n",
       "      <td>36.052252</td>\n",
       "      <td>...</td>\n",
       "      <td>S</td>\n",
       "      <td>FE</td>\n",
       "      <td>2016-08-13 00:00:00</td>\n",
       "      <td>2016-09-06 00:00:00</td>\n",
       "      <td>10SFE</td>\n",
       "      <td>040d03d0-fbdd-438c-8174-f81e4a1bba57</td>\n",
       "      <td>2016-08-20</td>\n",
       "      <td>L1C_T10SFE_A006116_20160823T185715</td>\n",
       "      <td>2016-08-23 18:57:15.690000+00:00</td>\n",
       "      <td>3.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>L1C_T10SGH_A006116_20160823T185715</td>\n",
       "      <td>S2A_MSIL1C_20160823T184922_N0204_R113_T10SGH_2...</td>\n",
       "      <td>GS2A_20160823T184922_006116_N02.04</td>\n",
       "      <td>10SGH</td>\n",
       "      <td>2016-08-23 18:57:15.690000+00:00</td>\n",
       "      <td>580047251.0</td>\n",
       "      <td>0.0578</td>\n",
       "      <td>PASSED</td>\n",
       "      <td>2016-08-23T18:57:15.000000Z</td>\n",
       "      <td>38.826372</td>\n",
       "      <td>...</td>\n",
       "      <td>S</td>\n",
       "      <td>GH</td>\n",
       "      <td>2016-08-18 00:00:00</td>\n",
       "      <td>2016-08-30 00:00:00</td>\n",
       "      <td>10SGH</td>\n",
       "      <td>5823867d-c458-4673-858f-0ded99b6121f</td>\n",
       "      <td>2016-08-23</td>\n",
       "      <td>L1C_T10SGH_A006216_20160830T185344</td>\n",
       "      <td>2016-08-30 18:53:44.330000+00:00</td>\n",
       "      <td>6.0</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "<p>5 rows × 25 columns</p>\n",
       "</div>"
      ],
      "text/plain": [
       "                           GRANULE_ID  \\\n",
       "0  L1C_T10SEJ_A006016_20160816T190939   \n",
       "1  L1C_T10SFE_A004972_20160604T185400   \n",
       "2  L1C_T10SFE_A006116_20160823T185715   \n",
       "3  L1C_T10SFE_A006073_20160820T185430   \n",
       "4  L1C_T10SGH_A006116_20160823T185715   \n",
       "\n",
       "                                          PRODUCT_ID  \\\n",
       "0  S2A_MSIL1C_20160816T185922_N0204_R013_T10SEJ_2...   \n",
       "1  S2A_MSIL1C_20160604T184922_N0202_R113_T10SFE_2...   \n",
       "2  S2A_MSIL1C_20160823T184922_N0204_R113_T10SFE_2...   \n",
       "3  S2A_MSIL1C_20160820T185432_N0204_R070_T10SFE_2...   \n",
       "4  S2A_MSIL1C_20160823T184922_N0204_R113_T10SGH_2...   \n",
       "\n",
       "                  DATATAKE_IDENTIFIER MGRS_TILE  \\\n",
       "0  GS2A_20160816T185922_006016_N02.04     10SEJ   \n",
       "1  GS2A_20160604T184922_004972_N02.02     10SFE   \n",
       "2  GS2A_20160823T184922_006116_N02.04     10SFE   \n",
       "3  GS2A_20160820T185432_006073_N02.04     10SFE   \n",
       "4  GS2A_20160823T184922_006116_N02.04     10SGH   \n",
       "\n",
       "                       SENSING_TIME   TOTAL_SIZE  CLOUD_COVER  \\\n",
       "0  2016-08-16 19:09:39.996000+00:00  596451172.0       0.0000   \n",
       "1         2016-06-04 18:54:00+00:00  695518354.0      65.0656   \n",
       "2  2016-08-23 18:57:15.690000+00:00  698481486.0      44.5017   \n",
       "3  2016-08-20 18:54:30.460000+00:00  525774690.0      53.1735   \n",
       "4  2016-08-23 18:57:15.690000+00:00  580047251.0       0.0578   \n",
       "\n",
       "  GEOMETRIC_QUALITY_FLAG              GENERATION_TIME  NORTH_LAT  ...  let  \\\n",
       "0                 PASSED  2016-08-16T19:09:39.000000Z  39.750358  ...    S   \n",
       "1                 PASSED  2016-06-04T18:54:00.000000Z  36.139831  ...    S   \n",
       "2                 PASSED  2016-08-23T18:57:15.000000Z  36.139831  ...    S   \n",
       "3                 PASSED  2016-08-20T18:54:30.000000Z  36.052252  ...    S   \n",
       "4                 PASSED  2016-08-23T18:57:15.000000Z  38.826372  ...    S   \n",
       "\n",
       "   sub              STARTED                ENDED   MGRS  \\\n",
       "0   EJ  2016-08-13 00:00:00  2016-08-26 00:00:00  10SEJ   \n",
       "1   FE  2016-06-04 00:00:00  2016-06-20 00:00:00  10SFE   \n",
       "2   FE  2016-08-13 00:00:00  2016-09-06 00:00:00  10SFE   \n",
       "3   FE  2016-08-13 00:00:00  2016-09-06 00:00:00  10SFE   \n",
       "4   GH  2016-08-18 00:00:00  2016-08-30 00:00:00  10SGH   \n",
       "\n",
       "                              UNIQUE_ID        DATE  \\\n",
       "0  a0950d5e-6f35-4023-bb60-6737146ba7bd  2016-08-16   \n",
       "1  b8cf80c7-b617-4fe4-91b0-43b40d38c7f6  2016-06-04   \n",
       "2  040d03d0-fbdd-438c-8174-f81e4a1bba57  2016-08-23   \n",
       "3  040d03d0-fbdd-438c-8174-f81e4a1bba57  2016-08-20   \n",
       "4  5823867d-c458-4673-858f-0ded99b6121f  2016-08-23   \n",
       "\n",
       "                            NEXT_TILE                    NEXT_TILE_TIME  \\\n",
       "0  L1C_T10SEJ_A006116_20160823T185715  2016-08-23 18:57:15.690000+00:00   \n",
       "1  L1C_T10SFE_A005072_20160611T184746         2016-06-11 18:47:46+00:00   \n",
       "2  L1C_T10SFE_A006216_20160830T185344  2016-08-30 18:53:44.330000+00:00   \n",
       "3  L1C_T10SFE_A006116_20160823T185715  2016-08-23 18:57:15.690000+00:00   \n",
       "4  L1C_T10SGH_A006216_20160830T185344  2016-08-30 18:53:44.330000+00:00   \n",
       "\n",
       "  DAYS_BETWEEN  \n",
       "0          6.0  \n",
       "1          6.0  \n",
       "2          6.0  \n",
       "3          3.0  \n",
       "4          6.0  \n",
       "\n",
       "[5 rows x 25 columns]"
      ]
     },
     "execution_count": 3,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df.head()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 4,
   "id": "b32cb23f",
   "metadata": {},
   "outputs": [],
   "source": [
    "df = df[['GRANULE_ID', 'SENSING_TIME', 'TOTAL_SIZE', 'CLOUD_COVER', 'BASE_URL', 'STARTED', 'ENDED', 'MGRS', 'UNIQUE_ID', 'NEXT_TILE', 'NEXT_TILE_TIME', 'DAYS_BETWEEN' ]]"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 5,
   "id": "dcd982f4",
   "metadata": {},
   "outputs": [],
   "source": [
    "# these columns will be used for tracking processing in aws\n",
    "df['IN_PROGRESS'] = 0\n",
    "df['PROCESSED'] = 0"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 6,
   "id": "5feb009b",
   "metadata": {},
   "outputs": [],
   "source": [
    "'''\n",
    "track how long each .safe took to process and get the instance id\n",
    "instance id is useful if there is an issue with processing or data intergrity we \n",
    "can find it in cloudwatch and we will know what other tiles were processed by this instance\n",
    "'''\n",
    "df['PROCESS_TIME'] = \" \"\n",
    "df['INSTANCE_ID'] = \" \""
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 7,
   "id": "c98a8d24",
   "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>GRANULE_ID</th>\n",
       "      <th>SENSING_TIME</th>\n",
       "      <th>TOTAL_SIZE</th>\n",
       "      <th>CLOUD_COVER</th>\n",
       "      <th>BASE_URL</th>\n",
       "      <th>STARTED</th>\n",
       "      <th>ENDED</th>\n",
       "      <th>MGRS</th>\n",
       "      <th>UNIQUE_ID</th>\n",
       "      <th>NEXT_TILE</th>\n",
       "      <th>NEXT_TILE_TIME</th>\n",
       "      <th>DAYS_BETWEEN</th>\n",
       "      <th>IN_PROGRESS</th>\n",
       "      <th>PROCESSED</th>\n",
       "      <th>PROCESS_TIME</th>\n",
       "      <th>INSTANCE_ID</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>L1C_T10SEJ_A006016_20160816T190939</td>\n",
       "      <td>2016-08-16 19:09:39.996000+00:00</td>\n",
       "      <td>596451172.0</td>\n",
       "      <td>0.0000</td>\n",
       "      <td>gs://gcp-public-data-sentinel-2/tiles/10/S/EJ/...</td>\n",
       "      <td>2016-08-13 00:00:00</td>\n",
       "      <td>2016-08-26 00:00:00</td>\n",
       "      <td>10SEJ</td>\n",
       "      <td>a0950d5e-6f35-4023-bb60-6737146ba7bd</td>\n",
       "      <td>L1C_T10SEJ_A006116_20160823T185715</td>\n",
       "      <td>2016-08-23 18:57:15.690000+00:00</td>\n",
       "      <td>6.0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td></td>\n",
       "      <td></td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>L1C_T10SFE_A004972_20160604T185400</td>\n",
       "      <td>2016-06-04 18:54:00+00:00</td>\n",
       "      <td>695518354.0</td>\n",
       "      <td>65.0656</td>\n",
       "      <td>gs://gcp-public-data-sentinel-2/tiles/10/S/FE/...</td>\n",
       "      <td>2016-06-04 00:00:00</td>\n",
       "      <td>2016-06-20 00:00:00</td>\n",
       "      <td>10SFE</td>\n",
       "      <td>b8cf80c7-b617-4fe4-91b0-43b40d38c7f6</td>\n",
       "      <td>L1C_T10SFE_A005072_20160611T184746</td>\n",
       "      <td>2016-06-11 18:47:46+00:00</td>\n",
       "      <td>6.0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td></td>\n",
       "      <td></td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>L1C_T10SFE_A006116_20160823T185715</td>\n",
       "      <td>2016-08-23 18:57:15.690000+00:00</td>\n",
       "      <td>698481486.0</td>\n",
       "      <td>44.5017</td>\n",
       "      <td>gs://gcp-public-data-sentinel-2/tiles/10/S/FE/...</td>\n",
       "      <td>2016-08-13 00:00:00</td>\n",
       "      <td>2016-09-06 00:00:00</td>\n",
       "      <td>10SFE</td>\n",
       "      <td>040d03d0-fbdd-438c-8174-f81e4a1bba57</td>\n",
       "      <td>L1C_T10SFE_A006216_20160830T185344</td>\n",
       "      <td>2016-08-30 18:53:44.330000+00:00</td>\n",
       "      <td>6.0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td></td>\n",
       "      <td></td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>L1C_T10SFE_A006073_20160820T185430</td>\n",
       "      <td>2016-08-20 18:54:30.460000+00:00</td>\n",
       "      <td>525774690.0</td>\n",
       "      <td>53.1735</td>\n",
       "      <td>gs://gcp-public-data-sentinel-2/tiles/10/S/FE/...</td>\n",
       "      <td>2016-08-13 00:00:00</td>\n",
       "      <td>2016-09-06 00:00:00</td>\n",
       "      <td>10SFE</td>\n",
       "      <td>040d03d0-fbdd-438c-8174-f81e4a1bba57</td>\n",
       "      <td>L1C_T10SFE_A006116_20160823T185715</td>\n",
       "      <td>2016-08-23 18:57:15.690000+00:00</td>\n",
       "      <td>3.0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td></td>\n",
       "      <td></td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>L1C_T10SGH_A006116_20160823T185715</td>\n",
       "      <td>2016-08-23 18:57:15.690000+00:00</td>\n",
       "      <td>580047251.0</td>\n",
       "      <td>0.0578</td>\n",
       "      <td>gs://gcp-public-data-sentinel-2/tiles/10/S/GH/...</td>\n",
       "      <td>2016-08-18 00:00:00</td>\n",
       "      <td>2016-08-30 00:00:00</td>\n",
       "      <td>10SGH</td>\n",
       "      <td>5823867d-c458-4673-858f-0ded99b6121f</td>\n",
       "      <td>L1C_T10SGH_A006216_20160830T185344</td>\n",
       "      <td>2016-08-30 18:53:44.330000+00:00</td>\n",
       "      <td>6.0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td></td>\n",
       "      <td></td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "                           GRANULE_ID                      SENSING_TIME  \\\n",
       "0  L1C_T10SEJ_A006016_20160816T190939  2016-08-16 19:09:39.996000+00:00   \n",
       "1  L1C_T10SFE_A004972_20160604T185400         2016-06-04 18:54:00+00:00   \n",
       "2  L1C_T10SFE_A006116_20160823T185715  2016-08-23 18:57:15.690000+00:00   \n",
       "3  L1C_T10SFE_A006073_20160820T185430  2016-08-20 18:54:30.460000+00:00   \n",
       "4  L1C_T10SGH_A006116_20160823T185715  2016-08-23 18:57:15.690000+00:00   \n",
       "\n",
       "    TOTAL_SIZE  CLOUD_COVER  \\\n",
       "0  596451172.0       0.0000   \n",
       "1  695518354.0      65.0656   \n",
       "2  698481486.0      44.5017   \n",
       "3  525774690.0      53.1735   \n",
       "4  580047251.0       0.0578   \n",
       "\n",
       "                                            BASE_URL              STARTED  \\\n",
       "0  gs://gcp-public-data-sentinel-2/tiles/10/S/EJ/...  2016-08-13 00:00:00   \n",
       "1  gs://gcp-public-data-sentinel-2/tiles/10/S/FE/...  2016-06-04 00:00:00   \n",
       "2  gs://gcp-public-data-sentinel-2/tiles/10/S/FE/...  2016-08-13 00:00:00   \n",
       "3  gs://gcp-public-data-sentinel-2/tiles/10/S/FE/...  2016-08-13 00:00:00   \n",
       "4  gs://gcp-public-data-sentinel-2/tiles/10/S/GH/...  2016-08-18 00:00:00   \n",
       "\n",
       "                 ENDED   MGRS                             UNIQUE_ID  \\\n",
       "0  2016-08-26 00:00:00  10SEJ  a0950d5e-6f35-4023-bb60-6737146ba7bd   \n",
       "1  2016-06-20 00:00:00  10SFE  b8cf80c7-b617-4fe4-91b0-43b40d38c7f6   \n",
       "2  2016-09-06 00:00:00  10SFE  040d03d0-fbdd-438c-8174-f81e4a1bba57   \n",
       "3  2016-09-06 00:00:00  10SFE  040d03d0-fbdd-438c-8174-f81e4a1bba57   \n",
       "4  2016-08-30 00:00:00  10SGH  5823867d-c458-4673-858f-0ded99b6121f   \n",
       "\n",
       "                            NEXT_TILE                    NEXT_TILE_TIME  \\\n",
       "0  L1C_T10SEJ_A006116_20160823T185715  2016-08-23 18:57:15.690000+00:00   \n",
       "1  L1C_T10SFE_A005072_20160611T184746         2016-06-11 18:47:46+00:00   \n",
       "2  L1C_T10SFE_A006216_20160830T185344  2016-08-30 18:53:44.330000+00:00   \n",
       "3  L1C_T10SFE_A006116_20160823T185715  2016-08-23 18:57:15.690000+00:00   \n",
       "4  L1C_T10SGH_A006216_20160830T185344  2016-08-30 18:53:44.330000+00:00   \n",
       "\n",
       "   DAYS_BETWEEN  IN_PROGRESS  PROCESSED PROCESS_TIME INSTANCE_ID  \n",
       "0           6.0            0          0                           \n",
       "1           6.0            0          0                           \n",
       "2           6.0            0          0                           \n",
       "3           3.0            0          0                           \n",
       "4           6.0            0          0                           "
      ]
     },
     "execution_count": 7,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df.head()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 8,
   "id": "710d0d0d",
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "<class 'pandas.core.frame.DataFrame'>\n",
      "RangeIndex: 51 entries, 0 to 50\n",
      "Data columns (total 16 columns):\n",
      " #   Column          Non-Null Count  Dtype  \n",
      "---  ------          --------------  -----  \n",
      " 0   GRANULE_ID      51 non-null     object \n",
      " 1   SENSING_TIME    51 non-null     object \n",
      " 2   TOTAL_SIZE      51 non-null     float64\n",
      " 3   CLOUD_COVER     51 non-null     float64\n",
      " 4   BASE_URL        51 non-null     object \n",
      " 5   STARTED         51 non-null     object \n",
      " 6   ENDED           51 non-null     object \n",
      " 7   MGRS            51 non-null     object \n",
      " 8   UNIQUE_ID       51 non-null     object \n",
      " 9   NEXT_TILE       51 non-null     object \n",
      " 10  NEXT_TILE_TIME  51 non-null     object \n",
      " 11  DAYS_BETWEEN    51 non-null     float64\n",
      " 12  IN_PROGRESS     51 non-null     int64  \n",
      " 13  PROCESSED       51 non-null     int64  \n",
      " 14  PROCESS_TIME    51 non-null     object \n",
      " 15  INSTANCE_ID     51 non-null     object \n",
      "dtypes: float64(3), int64(2), object(11)\n",
      "memory usage: 6.5+ KB\n"
     ]
    }
   ],
   "source": [
    "df.info()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 9,
   "id": "e197cab0",
   "metadata": {},
   "outputs": [],
   "source": [
    "# remove single tiles that dont have a next tile or are not a next tile\n",
    "df = df[(df['GRANULE_ID'].isin(df['NEXT_TILE'])) | ~(df['NEXT_TILE'].isna())]\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 10,
   "id": "49050019",
   "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>GRANULE_ID</th>\n",
       "      <th>SENSING_TIME</th>\n",
       "      <th>TOTAL_SIZE</th>\n",
       "      <th>CLOUD_COVER</th>\n",
       "      <th>BASE_URL</th>\n",
       "      <th>STARTED</th>\n",
       "      <th>ENDED</th>\n",
       "      <th>MGRS</th>\n",
       "      <th>UNIQUE_ID</th>\n",
       "      <th>NEXT_TILE</th>\n",
       "      <th>NEXT_TILE_TIME</th>\n",
       "      <th>DAYS_BETWEEN</th>\n",
       "      <th>IN_PROGRESS</th>\n",
       "      <th>PROCESSED</th>\n",
       "      <th>PROCESS_TIME</th>\n",
       "      <th>INSTANCE_ID</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>L1C_T10SEJ_A006016_20160816T190939</td>\n",
       "      <td>2016-08-16 19:09:39.996000+00:00</td>\n",
       "      <td>596451172.0</td>\n",
       "      <td>0.0000</td>\n",
       "      <td>gs://gcp-public-data-sentinel-2/tiles/10/S/EJ/...</td>\n",
       "      <td>2016-08-13 00:00:00</td>\n",
       "      <td>2016-08-26 00:00:00</td>\n",
       "      <td>10SEJ</td>\n",
       "      <td>a0950d5e-6f35-4023-bb60-6737146ba7bd</td>\n",
       "      <td>L1C_T10SEJ_A006116_20160823T185715</td>\n",
       "      <td>2016-08-23 18:57:15.690000+00:00</td>\n",
       "      <td>6.0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td></td>\n",
       "      <td></td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>L1C_T10SFE_A004972_20160604T185400</td>\n",
       "      <td>2016-06-04 18:54:00+00:00</td>\n",
       "      <td>695518354.0</td>\n",
       "      <td>65.0656</td>\n",
       "      <td>gs://gcp-public-data-sentinel-2/tiles/10/S/FE/...</td>\n",
       "      <td>2016-06-04 00:00:00</td>\n",
       "      <td>2016-06-20 00:00:00</td>\n",
       "      <td>10SFE</td>\n",
       "      <td>b8cf80c7-b617-4fe4-91b0-43b40d38c7f6</td>\n",
       "      <td>L1C_T10SFE_A005072_20160611T184746</td>\n",
       "      <td>2016-06-11 18:47:46+00:00</td>\n",
       "      <td>6.0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td></td>\n",
       "      <td></td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>L1C_T10SFE_A006116_20160823T185715</td>\n",
       "      <td>2016-08-23 18:57:15.690000+00:00</td>\n",
       "      <td>698481486.0</td>\n",
       "      <td>44.5017</td>\n",
       "      <td>gs://gcp-public-data-sentinel-2/tiles/10/S/FE/...</td>\n",
       "      <td>2016-08-13 00:00:00</td>\n",
       "      <td>2016-09-06 00:00:00</td>\n",
       "      <td>10SFE</td>\n",
       "      <td>040d03d0-fbdd-438c-8174-f81e4a1bba57</td>\n",
       "      <td>L1C_T10SFE_A006216_20160830T185344</td>\n",
       "      <td>2016-08-30 18:53:44.330000+00:00</td>\n",
       "      <td>6.0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td></td>\n",
       "      <td></td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>L1C_T10SFE_A006073_20160820T185430</td>\n",
       "      <td>2016-08-20 18:54:30.460000+00:00</td>\n",
       "      <td>525774690.0</td>\n",
       "      <td>53.1735</td>\n",
       "      <td>gs://gcp-public-data-sentinel-2/tiles/10/S/FE/...</td>\n",
       "      <td>2016-08-13 00:00:00</td>\n",
       "      <td>2016-09-06 00:00:00</td>\n",
       "      <td>10SFE</td>\n",
       "      <td>040d03d0-fbdd-438c-8174-f81e4a1bba57</td>\n",
       "      <td>L1C_T10SFE_A006116_20160823T185715</td>\n",
       "      <td>2016-08-23 18:57:15.690000+00:00</td>\n",
       "      <td>3.0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td></td>\n",
       "      <td></td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>L1C_T10SGH_A006116_20160823T185715</td>\n",
       "      <td>2016-08-23 18:57:15.690000+00:00</td>\n",
       "      <td>580047251.0</td>\n",
       "      <td>0.0578</td>\n",
       "      <td>gs://gcp-public-data-sentinel-2/tiles/10/S/GH/...</td>\n",
       "      <td>2016-08-18 00:00:00</td>\n",
       "      <td>2016-08-30 00:00:00</td>\n",
       "      <td>10SGH</td>\n",
       "      <td>5823867d-c458-4673-858f-0ded99b6121f</td>\n",
       "      <td>L1C_T10SGH_A006216_20160830T185344</td>\n",
       "      <td>2016-08-30 18:53:44.330000+00:00</td>\n",
       "      <td>6.0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td></td>\n",
       "      <td></td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "                           GRANULE_ID                      SENSING_TIME  \\\n",
       "0  L1C_T10SEJ_A006016_20160816T190939  2016-08-16 19:09:39.996000+00:00   \n",
       "1  L1C_T10SFE_A004972_20160604T185400         2016-06-04 18:54:00+00:00   \n",
       "2  L1C_T10SFE_A006116_20160823T185715  2016-08-23 18:57:15.690000+00:00   \n",
       "3  L1C_T10SFE_A006073_20160820T185430  2016-08-20 18:54:30.460000+00:00   \n",
       "4  L1C_T10SGH_A006116_20160823T185715  2016-08-23 18:57:15.690000+00:00   \n",
       "\n",
       "    TOTAL_SIZE  CLOUD_COVER  \\\n",
       "0  596451172.0       0.0000   \n",
       "1  695518354.0      65.0656   \n",
       "2  698481486.0      44.5017   \n",
       "3  525774690.0      53.1735   \n",
       "4  580047251.0       0.0578   \n",
       "\n",
       "                                            BASE_URL              STARTED  \\\n",
       "0  gs://gcp-public-data-sentinel-2/tiles/10/S/EJ/...  2016-08-13 00:00:00   \n",
       "1  gs://gcp-public-data-sentinel-2/tiles/10/S/FE/...  2016-06-04 00:00:00   \n",
       "2  gs://gcp-public-data-sentinel-2/tiles/10/S/FE/...  2016-08-13 00:00:00   \n",
       "3  gs://gcp-public-data-sentinel-2/tiles/10/S/FE/...  2016-08-13 00:00:00   \n",
       "4  gs://gcp-public-data-sentinel-2/tiles/10/S/GH/...  2016-08-18 00:00:00   \n",
       "\n",
       "                 ENDED   MGRS                             UNIQUE_ID  \\\n",
       "0  2016-08-26 00:00:00  10SEJ  a0950d5e-6f35-4023-bb60-6737146ba7bd   \n",
       "1  2016-06-20 00:00:00  10SFE  b8cf80c7-b617-4fe4-91b0-43b40d38c7f6   \n",
       "2  2016-09-06 00:00:00  10SFE  040d03d0-fbdd-438c-8174-f81e4a1bba57   \n",
       "3  2016-09-06 00:00:00  10SFE  040d03d0-fbdd-438c-8174-f81e4a1bba57   \n",
       "4  2016-08-30 00:00:00  10SGH  5823867d-c458-4673-858f-0ded99b6121f   \n",
       "\n",
       "                            NEXT_TILE                    NEXT_TILE_TIME  \\\n",
       "0  L1C_T10SEJ_A006116_20160823T185715  2016-08-23 18:57:15.690000+00:00   \n",
       "1  L1C_T10SFE_A005072_20160611T184746         2016-06-11 18:47:46+00:00   \n",
       "2  L1C_T10SFE_A006216_20160830T185344  2016-08-30 18:53:44.330000+00:00   \n",
       "3  L1C_T10SFE_A006116_20160823T185715  2016-08-23 18:57:15.690000+00:00   \n",
       "4  L1C_T10SGH_A006216_20160830T185344  2016-08-30 18:53:44.330000+00:00   \n",
       "\n",
       "   DAYS_BETWEEN  IN_PROGRESS  PROCESSED PROCESS_TIME INSTANCE_ID  \n",
       "0           6.0            0          0                           \n",
       "1           6.0            0          0                           \n",
       "2           6.0            0          0                           \n",
       "3           3.0            0          0                           \n",
       "4           6.0            0          0                           "
      ]
     },
     "execution_count": 10,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df.head()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 11,
   "id": "58fb468e",
   "metadata": {},
   "outputs": [],
   "source": [
    "from sqlalchemy import create_engine\n",
    "import os"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 12,
   "id": "e300c5b3",
   "metadata": {},
   "outputs": [],
   "source": [
    "USER = os.environ.get('DBUSERNAME')\n",
    "PASSWORD = os.environ.get('DBPASSWORD')\n",
    "ENDPOINT = os.environ.get('DBDEV')\n",
    "SCHEMA = 'dev'\n",
    "\n",
    "engine = create_engine(f\"mysql+pymysql://{USER}:{PASSWORD}@{ENDPOINT}/{SCHEMA}\")"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 13,
   "id": "b44bdb78",
   "metadata": {},
   "outputs": [],
   "source": [
    "# send to database\n",
    "table = 'dev_l1c'\n",
    "df.to_sql(table, con = engine, if_exists = 'append')"
   ]
  }
 ],
 "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.9.5"
  }
 },
 "nbformat": 4,
 "nbformat_minor": 5
}