check_site_overlap.ipynb

{
 "cells": [
  {
   "cell_type": "code",
   "execution_count": 1,
   "id": "75de31f5-9f3e-4747-a62d-f052614418dc",
   "metadata": {},
   "outputs": [],
   "source": [
    "import pandas as pd"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "efc044e3-2b2f-432e-abb1-2c7a0f3708a2",
   "metadata": {},
   "source": [
    "From Evans et al. (2017) supplementary information:\n",
    "\n",
    "Evans et al. data: lake and reservoir budget data only, only one (mean) value per site regardless of study duration, but included both source and sink sites\n",
    "Catalan et al.: restricted to sites acting as DOC sinks, incorporated data from a wider range of different waterbody types and assessment methods, and treated multiple years of data from some sites as independent measurements in the statistical analysis. Of the 107 data points used, 29 were from rivers or catchment studies, 8 from oceans or estuaries, and 75 from lakes or reservoirs. Taking into account the use of multiple data years from single sites, the study included data from 48 unique lakes or reservoirs. Of these, 22 k values were derived from modelled data, and four from summer-only studies, i.e. 26 not thought to be reliable, leaving 22 lakes.\n",
    "\n",
    "Evans et al. excluded these 26 data points to compare to their results, giving 22 sites with annual lake input-output budgets that were common to both studies. Of these, 20 were obtained from Ontario, Canada, from the Experimental Lakes Area (ELA) and the Dorset region. The original authors of the ELA study calculated DOC half-lives assuming that they were closed systems with no outflows. Evans et al. re-calculated T½ for these sites to take account of DOC loss through the lake outflows, thus obtaining different somewhat estimates to those used in the analysis of Catalán et al. \n",
    "\n",
    "Overall, therefore, the two lake datasets are not wholly independent, but there are major differences in site selection criteria, geographic range and data analysis methods, and approximately three quarters of the data points used by each study were independent of the other.  "
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 9,
   "id": "7baf7517-1db1-4ad2-85b3-272cc8b73683",
   "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>Site</th>\n",
       "      <th>Ref</th>\n",
       "      <th>Lat</th>\n",
       "      <th>Lon</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>Mirror lake (epilimnion)</td>\n",
       "      <td>Cole et al. 1989</td>\n",
       "      <td>43.944013</td>\n",
       "      <td>-71.693437</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>Friskjön</td>\n",
       "      <td>Sobek et al. 2006</td>\n",
       "      <td>57.937636</td>\n",
       "      <td>16.697475</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>239_ELA</td>\n",
       "      <td>Schindler et al. 1992</td>\n",
       "      <td>49.663097</td>\n",
       "      <td>-93.723074</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>114_ELA</td>\n",
       "      <td>Curtis and Schindler, 1997</td>\n",
       "      <td>49.671072</td>\n",
       "      <td>-93.757008</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>221_ELA</td>\n",
       "      <td>Curtis and Schindler, 1997</td>\n",
       "      <td>49.702057</td>\n",
       "      <td>-93.726280</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>5</th>\n",
       "      <td>222_ELA</td>\n",
       "      <td>Curtis and Schindler, 1997</td>\n",
       "      <td>49.696173</td>\n",
       "      <td>-93.723190</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>6</th>\n",
       "      <td>224_ELA</td>\n",
       "      <td>Curtis and Schindler, 1997</td>\n",
       "      <td>49.690621</td>\n",
       "      <td>-93.716152</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>7</th>\n",
       "      <td>225_ELA</td>\n",
       "      <td>Curtis and Schindler, 1997</td>\n",
       "      <td>49.687955</td>\n",
       "      <td>-93.714264</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>8</th>\n",
       "      <td>226_ELA</td>\n",
       "      <td>Curtis and Schindler, 1997</td>\n",
       "      <td>49.689954</td>\n",
       "      <td>-93.741730</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>9</th>\n",
       "      <td>227_ELA</td>\n",
       "      <td>Curtis and Schindler, 1997</td>\n",
       "      <td>49.687733</td>\n",
       "      <td>-93.688515</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>10</th>\n",
       "      <td>239_ELA</td>\n",
       "      <td>Curtis and Schindler, 1997</td>\n",
       "      <td>49.663097</td>\n",
       "      <td>-93.723074</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>11</th>\n",
       "      <td>303_ELA</td>\n",
       "      <td>Curtis and Schindler, 1997</td>\n",
       "      <td>49.663961</td>\n",
       "      <td>-93.742245</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>12</th>\n",
       "      <td>304_ELA</td>\n",
       "      <td>Curtis and Schindler, 1997</td>\n",
       "      <td>49.659517</td>\n",
       "      <td>-93.748425</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>13</th>\n",
       "      <td>305_ELA</td>\n",
       "      <td>Curtis and Schindler, 1997</td>\n",
       "      <td>49.693064</td>\n",
       "      <td>-93.688172</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>14</th>\n",
       "      <td>382_ELA</td>\n",
       "      <td>Curtis and Schindler, 1997</td>\n",
       "      <td>49.705721</td>\n",
       "      <td>-93.677185</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>15</th>\n",
       "      <td>Large lakes simulation</td>\n",
       "      <td>Hanson et al. 2011</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>16</th>\n",
       "      <td>Small lakes simulation</td>\n",
       "      <td>Hanson et al. 2011</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>17</th>\n",
       "      <td>Örsträsket</td>\n",
       "      <td>Jonsson et al. 2001</td>\n",
       "      <td>64.517180</td>\n",
       "      <td>20.030302</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>18</th>\n",
       "      <td>La Grande_100M1</td>\n",
       "      <td>Weissenberger et al. 2010</td>\n",
       "      <td>53.800651</td>\n",
       "      <td>-75.303498</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>19</th>\n",
       "      <td>La Grande_100M2</td>\n",
       "      <td>Weissenberger et al. 2010</td>\n",
       "      <td>53.800651</td>\n",
       "      <td>-75.303498</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>20</th>\n",
       "      <td>La Grande_100M3</td>\n",
       "      <td>Weissenberger et al. 2010</td>\n",
       "      <td>53.800651</td>\n",
       "      <td>-75.303498</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>21</th>\n",
       "      <td>La Grande_50M1</td>\n",
       "      <td>Weissenberger et al. 2010</td>\n",
       "      <td>53.800651</td>\n",
       "      <td>-75.303498</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>22</th>\n",
       "      <td>La Grande_50M2</td>\n",
       "      <td>Weissenberger et al. 2010</td>\n",
       "      <td>53.800651</td>\n",
       "      <td>-75.303498</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>23</th>\n",
       "      <td>La Grande_50M3</td>\n",
       "      <td>Weissenberger et al. 2010</td>\n",
       "      <td>53.800651</td>\n",
       "      <td>-75.303498</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>24</th>\n",
       "      <td>Allequash Lake</td>\n",
       "      <td>Hanson et al. 2014</td>\n",
       "      <td>46.039459</td>\n",
       "      <td>-89.623732</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>25</th>\n",
       "      <td>Big Muskellunge</td>\n",
       "      <td>Hanson et al. 2014</td>\n",
       "      <td>46.019913</td>\n",
       "      <td>-89.618152</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>26</th>\n",
       "      <td>Cristal Lake</td>\n",
       "      <td>Hanson et al. 2014</td>\n",
       "      <td>46.001493</td>\n",
       "      <td>-89.612842</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>27</th>\n",
       "      <td>Crystal Bog Lake</td>\n",
       "      <td>Hanson et al. 2014</td>\n",
       "      <td>46.005338</td>\n",
       "      <td>-89.624411</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>28</th>\n",
       "      <td>Trout Bog Lake</td>\n",
       "      <td>Hanson et al. 2014</td>\n",
       "      <td>46.022565</td>\n",
       "      <td>-89.710800</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>29</th>\n",
       "      <td>Sparkling Lake</td>\n",
       "      <td>Hanson et al. 2014</td>\n",
       "      <td>46.010614</td>\n",
       "      <td>-89.699813</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>30</th>\n",
       "      <td>Trout Lake</td>\n",
       "      <td>Hanson et al. 2014</td>\n",
       "      <td>46.032070</td>\n",
       "      <td>-89.692089</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>31</th>\n",
       "      <td>Mälaren</td>\n",
       "      <td>Köhler et al. 2013</td>\n",
       "      <td>59.489726</td>\n",
       "      <td>17.495327</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>32</th>\n",
       "      <td>Mälaren- Basin C</td>\n",
       "      <td>Köhler et al. 2013</td>\n",
       "      <td>59.454848</td>\n",
       "      <td>17.201443</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>33</th>\n",
       "      <td>Mälaren- Basin E</td>\n",
       "      <td>Köhler et al. 2013</td>\n",
       "      <td>59.362595</td>\n",
       "      <td>17.800198</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>34</th>\n",
       "      <td>Glass Lake</td>\n",
       "      <td>McKnight et al 1997</td>\n",
       "      <td>40.281498</td>\n",
       "      <td>-105.665529</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>35</th>\n",
       "      <td>Blue Chalk</td>\n",
       "      <td>Dillon and Molot, 1997</td>\n",
       "      <td>45.198369</td>\n",
       "      <td>-78.939520</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>36</th>\n",
       "      <td>Chub</td>\n",
       "      <td>Dillon and Molot, 1997</td>\n",
       "      <td>45.295781</td>\n",
       "      <td>-79.233920</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>37</th>\n",
       "      <td>Crosson</td>\n",
       "      <td>Dillon and Molot, 1997</td>\n",
       "      <td>45.083339</td>\n",
       "      <td>-79.036981</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>38</th>\n",
       "      <td>Dickie</td>\n",
       "      <td>Dillon and Molot, 1997</td>\n",
       "      <td>45.149419</td>\n",
       "      <td>-79.089381</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>39</th>\n",
       "      <td>Harp</td>\n",
       "      <td>Dillon and Molot, 1997</td>\n",
       "      <td>45.379402</td>\n",
       "      <td>-79.135650</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>40</th>\n",
       "      <td>Plastic</td>\n",
       "      <td>Dillon and Molot, 1997</td>\n",
       "      <td>45.178951</td>\n",
       "      <td>-78.823775</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>41</th>\n",
       "      <td>Red Chalk</td>\n",
       "      <td>Dillon and Molot, 1997</td>\n",
       "      <td>45.189205</td>\n",
       "      <td>-78.948275</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>42</th>\n",
       "      <td>Green Lake 4</td>\n",
       "      <td>Miller et al. 2009</td>\n",
       "      <td>40.137481</td>\n",
       "      <td>-105.585486</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>43</th>\n",
       "      <td>San Diego Bay_Aug</td>\n",
       "      <td>Delgadillo-Hinojosa et al. 2008</td>\n",
       "      <td>32.655563</td>\n",
       "      <td>-117.136967</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>44</th>\n",
       "      <td>San Diego Bay_Jan</td>\n",
       "      <td>Delgadillo-Hinojosa et al. 2008</td>\n",
       "      <td>32.655563</td>\n",
       "      <td>-117.136967</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>45</th>\n",
       "      <td>San Diego Bay_May</td>\n",
       "      <td>Delgadillo-Hinojosa et al. 2008</td>\n",
       "      <td>32.655563</td>\n",
       "      <td>-117.136967</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>46</th>\n",
       "      <td>San Diego Bay_Sep</td>\n",
       "      <td>Delgadillo-Hinojosa et al. 2008</td>\n",
       "      <td>32.655563</td>\n",
       "      <td>-117.136967</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>47</th>\n",
       "      <td>Superior</td>\n",
       "      <td>Stephens and Minor, 2010</td>\n",
       "      <td>47.765176</td>\n",
       "      <td>-86.985054</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "                        Site                              Ref        Lat  \\\n",
       "0   Mirror lake (epilimnion)                 Cole et al. 1989  43.944013   \n",
       "1                   Friskjön                Sobek et al. 2006  57.937636   \n",
       "2                    239_ELA            Schindler et al. 1992  49.663097   \n",
       "3                    114_ELA       Curtis and Schindler, 1997  49.671072   \n",
       "4                    221_ELA       Curtis and Schindler, 1997  49.702057   \n",
       "5                    222_ELA       Curtis and Schindler, 1997  49.696173   \n",
       "6                    224_ELA       Curtis and Schindler, 1997  49.690621   \n",
       "7                    225_ELA       Curtis and Schindler, 1997  49.687955   \n",
       "8                    226_ELA       Curtis and Schindler, 1997  49.689954   \n",
       "9                    227_ELA       Curtis and Schindler, 1997  49.687733   \n",
       "10                   239_ELA       Curtis and Schindler, 1997  49.663097   \n",
       "11                   303_ELA       Curtis and Schindler, 1997  49.663961   \n",
       "12                   304_ELA       Curtis and Schindler, 1997  49.659517   \n",
       "13                   305_ELA       Curtis and Schindler, 1997  49.693064   \n",
       "14                   382_ELA       Curtis and Schindler, 1997  49.705721   \n",
       "15    Large lakes simulation               Hanson et al. 2011        NaN   \n",
       "16    Small lakes simulation               Hanson et al. 2011        NaN   \n",
       "17                Örsträsket              Jonsson et al. 2001  64.517180   \n",
       "18           La Grande_100M1       Weissenberger et al. 2010   53.800651   \n",
       "19           La Grande_100M2       Weissenberger et al. 2010   53.800651   \n",
       "20           La Grande_100M3       Weissenberger et al. 2010   53.800651   \n",
       "21            La Grande_50M1       Weissenberger et al. 2010   53.800651   \n",
       "22            La Grande_50M2       Weissenberger et al. 2010   53.800651   \n",
       "23            La Grande_50M3       Weissenberger et al. 2010   53.800651   \n",
       "24            Allequash Lake              Hanson et al. 2014   46.039459   \n",
       "25           Big Muskellunge              Hanson et al. 2014   46.019913   \n",
       "26              Cristal Lake              Hanson et al. 2014   46.001493   \n",
       "27          Crystal Bog Lake              Hanson et al. 2014   46.005338   \n",
       "28            Trout Bog Lake              Hanson et al. 2014   46.022565   \n",
       "29            Sparkling Lake              Hanson et al. 2014   46.010614   \n",
       "30                Trout Lake              Hanson et al. 2014   46.032070   \n",
       "31                   Mälaren              Köhler et al. 2013   59.489726   \n",
       "32          Mälaren- Basin C              Köhler et al. 2013   59.454848   \n",
       "33          Mälaren- Basin E              Köhler et al. 2013   59.362595   \n",
       "34                Glass Lake              McKnight et al 1997  40.281498   \n",
       "35                Blue Chalk           Dillon and Molot, 1997  45.198369   \n",
       "36                      Chub           Dillon and Molot, 1997  45.295781   \n",
       "37                   Crosson           Dillon and Molot, 1997  45.083339   \n",
       "38                    Dickie           Dillon and Molot, 1997  45.149419   \n",
       "39                      Harp           Dillon and Molot, 1997  45.379402   \n",
       "40                   Plastic           Dillon and Molot, 1997  45.178951   \n",
       "41                 Red Chalk           Dillon and Molot, 1997  45.189205   \n",
       "42              Green Lake 4               Miller et al. 2009  40.137481   \n",
       "43         San Diego Bay_Aug  Delgadillo-Hinojosa et al. 2008  32.655563   \n",
       "44         San Diego Bay_Jan  Delgadillo-Hinojosa et al. 2008  32.655563   \n",
       "45         San Diego Bay_May  Delgadillo-Hinojosa et al. 2008  32.655563   \n",
       "46         San Diego Bay_Sep  Delgadillo-Hinojosa et al. 2008  32.655563   \n",
       "47                  Superior         Stephens and Minor, 2010  47.765176   \n",
       "\n",
       "           Lon  \n",
       "0   -71.693437  \n",
       "1    16.697475  \n",
       "2   -93.723074  \n",
       "3   -93.757008  \n",
       "4   -93.726280  \n",
       "5   -93.723190  \n",
       "6   -93.716152  \n",
       "7   -93.714264  \n",
       "8   -93.741730  \n",
       "9   -93.688515  \n",
       "10  -93.723074  \n",
       "11  -93.742245  \n",
       "12  -93.748425  \n",
       "13  -93.688172  \n",
       "14  -93.677185  \n",
       "15         NaN  \n",
       "16         NaN  \n",
       "17   20.030302  \n",
       "18  -75.303498  \n",
       "19  -75.303498  \n",
       "20  -75.303498  \n",
       "21  -75.303498  \n",
       "22  -75.303498  \n",
       "23  -75.303498  \n",
       "24  -89.623732  \n",
       "25  -89.618152  \n",
       "26  -89.612842  \n",
       "27  -89.624411  \n",
       "28  -89.710800  \n",
       "29  -89.699813  \n",
       "30  -89.692089  \n",
       "31   17.495327  \n",
       "32   17.201443  \n",
       "33   17.800198  \n",
       "34 -105.665529  \n",
       "35  -78.939520  \n",
       "36  -79.233920  \n",
       "37  -79.036981  \n",
       "38  -79.089381  \n",
       "39  -79.135650  \n",
       "40  -78.823775  \n",
       "41  -78.948275  \n",
       "42 -105.585486  \n",
       "43 -117.136967  \n",
       "44 -117.136967  \n",
       "45 -117.136967  \n",
       "46 -117.136967  \n",
       "47  -86.985054  "
      ]
     },
     "execution_count": 9,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# Read in data\n",
    "data_fpath = r\"data/catalan_2016_doc_data.xlsx\"\n",
    "df_full = pd.read_excel(data_fpath)\n",
    "\n",
    "df_full.rename(\n",
    "    {\n",
    "        \"WRT (years) \": \"tau\",\n",
    "        \"Degradation rate temperature corrected (d-1)\": \"k_per_day\",\n",
    "        \"FieldII\": \"Method\",\n",
    "        \"System10\": \"System\",\n",
    "        \"Latitude (d.d.)\": \"Lat\",\n",
    "        \"Longitude (d.d.)\": \"Lon\",\n",
    "        \"Cite\": \"Ref\"\n",
    "    },\n",
    "    axis=\"columns\",\n",
    "    inplace=True,\n",
    ")\n",
    "\n",
    "# Drop data that Catalan et al. excluded from their regression analyses, for comparability\n",
    "df = df_full.loc[df_full[\"Excluded from analysis\"] == 0].copy()\n",
    "\n",
    "# Drop all but lake data\n",
    "cat_df = df.loc[df_full[\"Method\"] == \"FieldModel\"]\n",
    "cat_df = cat_df.loc[cat_df[\"System\"] == \"Lake\"]\n",
    "\n",
    "# df of unique lakes with lat & lon\n",
    "groupby_cols = [\"Site\", \"Ref\", \"Lat\", \"Lon\"]\n",
    "cat_lake_df = cat_df[groupby_cols].drop_duplicates(subset=groupby_cols).reset_index(drop=True)\n",
    "\n",
    "cat_lake_df.to_csv(r'data/catalan_lake_sites.csv')\n",
    "\n",
    "cat_lake_df"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "3e04e653-4144-4ae5-b629-af2e6e7eae76",
   "metadata": {},
   "source": [
    "Notes:\n",
    "\n",
    "- Site 239_ELA has 17 rows, with diff WRT and k values. i.e. including changes over time. WRT of the lake is 5-17 years.\n",
    "- What are Hanson et al. 2011-derived rows? Large lakes simulation and Small lakes simulation give as the Site name...\n",
    "- San Diego Bay doesn't sound like a lake... Maybe that's ok. In there four times, one per season\n",
    "\n",
    "Still some repeat sites due to different times of year. Drop duplicates and just keep the first value of name and ref"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 13,
   "id": "32dedee2-ffce-487c-9290-879d4c984769",
   "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>Site</th>\n",
       "      <th>Ref</th>\n",
       "      <th>Lat</th>\n",
       "      <th>Lon</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>Mirror lake (epilimnion)</td>\n",
       "      <td>Cole et al. 1989</td>\n",
       "      <td>43.944013</td>\n",
       "      <td>-71.693437</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>Friskjön</td>\n",
       "      <td>Sobek et al. 2006</td>\n",
       "      <td>57.937636</td>\n",
       "      <td>16.697475</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>239_ELA</td>\n",
       "      <td>Schindler et al. 1992</td>\n",
       "      <td>49.663097</td>\n",
       "      <td>-93.723074</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>114_ELA</td>\n",
       "      <td>Curtis and Schindler, 1997</td>\n",
       "      <td>49.671072</td>\n",
       "      <td>-93.757008</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>221_ELA</td>\n",
       "      <td>Curtis and Schindler, 1997</td>\n",
       "      <td>49.702057</td>\n",
       "      <td>-93.726280</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>5</th>\n",
       "      <td>222_ELA</td>\n",
       "      <td>Curtis and Schindler, 1997</td>\n",
       "      <td>49.696173</td>\n",
       "      <td>-93.723190</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>6</th>\n",
       "      <td>224_ELA</td>\n",
       "      <td>Curtis and Schindler, 1997</td>\n",
       "      <td>49.690621</td>\n",
       "      <td>-93.716152</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>7</th>\n",
       "      <td>225_ELA</td>\n",
       "      <td>Curtis and Schindler, 1997</td>\n",
       "      <td>49.687955</td>\n",
       "      <td>-93.714264</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>8</th>\n",
       "      <td>226_ELA</td>\n",
       "      <td>Curtis and Schindler, 1997</td>\n",
       "      <td>49.689954</td>\n",
       "      <td>-93.741730</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>9</th>\n",
       "      <td>227_ELA</td>\n",
       "      <td>Curtis and Schindler, 1997</td>\n",
       "      <td>49.687733</td>\n",
       "      <td>-93.688515</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>10</th>\n",
       "      <td>303_ELA</td>\n",
       "      <td>Curtis and Schindler, 1997</td>\n",
       "      <td>49.663961</td>\n",
       "      <td>-93.742245</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>11</th>\n",
       "      <td>304_ELA</td>\n",
       "      <td>Curtis and Schindler, 1997</td>\n",
       "      <td>49.659517</td>\n",
       "      <td>-93.748425</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>12</th>\n",
       "      <td>305_ELA</td>\n",
       "      <td>Curtis and Schindler, 1997</td>\n",
       "      <td>49.693064</td>\n",
       "      <td>-93.688172</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>13</th>\n",
       "      <td>382_ELA</td>\n",
       "      <td>Curtis and Schindler, 1997</td>\n",
       "      <td>49.705721</td>\n",
       "      <td>-93.677185</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>14</th>\n",
       "      <td>Large lakes simulation</td>\n",
       "      <td>Hanson et al. 2011</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>15</th>\n",
       "      <td>Örsträsket</td>\n",
       "      <td>Jonsson et al. 2001</td>\n",
       "      <td>64.517180</td>\n",
       "      <td>20.030302</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>16</th>\n",
       "      <td>La Grande_100M1</td>\n",
       "      <td>Weissenberger et al. 2010</td>\n",
       "      <td>53.800651</td>\n",
       "      <td>-75.303498</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>17</th>\n",
       "      <td>Allequash Lake</td>\n",
       "      <td>Hanson et al. 2014</td>\n",
       "      <td>46.039459</td>\n",
       "      <td>-89.623732</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>18</th>\n",
       "      <td>Big Muskellunge</td>\n",
       "      <td>Hanson et al. 2014</td>\n",
       "      <td>46.019913</td>\n",
       "      <td>-89.618152</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>19</th>\n",
       "      <td>Cristal Lake</td>\n",
       "      <td>Hanson et al. 2014</td>\n",
       "      <td>46.001493</td>\n",
       "      <td>-89.612842</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>20</th>\n",
       "      <td>Crystal Bog Lake</td>\n",
       "      <td>Hanson et al. 2014</td>\n",
       "      <td>46.005338</td>\n",
       "      <td>-89.624411</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>21</th>\n",
       "      <td>Trout Bog Lake</td>\n",
       "      <td>Hanson et al. 2014</td>\n",
       "      <td>46.022565</td>\n",
       "      <td>-89.710800</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>22</th>\n",
       "      <td>Sparkling Lake</td>\n",
       "      <td>Hanson et al. 2014</td>\n",
       "      <td>46.010614</td>\n",
       "      <td>-89.699813</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>23</th>\n",
       "      <td>Trout Lake</td>\n",
       "      <td>Hanson et al. 2014</td>\n",
       "      <td>46.032070</td>\n",
       "      <td>-89.692089</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>24</th>\n",
       "      <td>Mälaren</td>\n",
       "      <td>Köhler et al. 2013</td>\n",
       "      <td>59.489726</td>\n",
       "      <td>17.495327</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>25</th>\n",
       "      <td>Mälaren- Basin C</td>\n",
       "      <td>Köhler et al. 2013</td>\n",
       "      <td>59.454848</td>\n",
       "      <td>17.201443</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>26</th>\n",
       "      <td>Mälaren- Basin E</td>\n",
       "      <td>Köhler et al. 2013</td>\n",
       "      <td>59.362595</td>\n",
       "      <td>17.800198</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>27</th>\n",
       "      <td>Glass Lake</td>\n",
       "      <td>McKnight et al 1997</td>\n",
       "      <td>40.281498</td>\n",
       "      <td>-105.665529</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>28</th>\n",
       "      <td>Blue Chalk</td>\n",
       "      <td>Dillon and Molot, 1997</td>\n",
       "      <td>45.198369</td>\n",
       "      <td>-78.939520</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>29</th>\n",
       "      <td>Chub</td>\n",
       "      <td>Dillon and Molot, 1997</td>\n",
       "      <td>45.295781</td>\n",
       "      <td>-79.233920</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>30</th>\n",
       "      <td>Crosson</td>\n",
       "      <td>Dillon and Molot, 1997</td>\n",
       "      <td>45.083339</td>\n",
       "      <td>-79.036981</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>31</th>\n",
       "      <td>Dickie</td>\n",
       "      <td>Dillon and Molot, 1997</td>\n",
       "      <td>45.149419</td>\n",
       "      <td>-79.089381</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>32</th>\n",
       "      <td>Harp</td>\n",
       "      <td>Dillon and Molot, 1997</td>\n",
       "      <td>45.379402</td>\n",
       "      <td>-79.135650</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>33</th>\n",
       "      <td>Plastic</td>\n",
       "      <td>Dillon and Molot, 1997</td>\n",
       "      <td>45.178951</td>\n",
       "      <td>-78.823775</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>34</th>\n",
       "      <td>Red Chalk</td>\n",
       "      <td>Dillon and Molot, 1997</td>\n",
       "      <td>45.189205</td>\n",
       "      <td>-78.948275</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>35</th>\n",
       "      <td>Green Lake 4</td>\n",
       "      <td>Miller et al. 2009</td>\n",
       "      <td>40.137481</td>\n",
       "      <td>-105.585486</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>36</th>\n",
       "      <td>San Diego Bay_Aug</td>\n",
       "      <td>Delgadillo-Hinojosa et al. 2008</td>\n",
       "      <td>32.655563</td>\n",
       "      <td>-117.136967</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>37</th>\n",
       "      <td>Superior</td>\n",
       "      <td>Stephens and Minor, 2010</td>\n",
       "      <td>47.765176</td>\n",
       "      <td>-86.985054</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "                        Site                              Ref        Lat  \\\n",
       "0   Mirror lake (epilimnion)                 Cole et al. 1989  43.944013   \n",
       "1                   Friskjön                Sobek et al. 2006  57.937636   \n",
       "2                    239_ELA            Schindler et al. 1992  49.663097   \n",
       "3                    114_ELA       Curtis and Schindler, 1997  49.671072   \n",
       "4                    221_ELA       Curtis and Schindler, 1997  49.702057   \n",
       "5                    222_ELA       Curtis and Schindler, 1997  49.696173   \n",
       "6                    224_ELA       Curtis and Schindler, 1997  49.690621   \n",
       "7                    225_ELA       Curtis and Schindler, 1997  49.687955   \n",
       "8                    226_ELA       Curtis and Schindler, 1997  49.689954   \n",
       "9                    227_ELA       Curtis and Schindler, 1997  49.687733   \n",
       "10                   303_ELA       Curtis and Schindler, 1997  49.663961   \n",
       "11                   304_ELA       Curtis and Schindler, 1997  49.659517   \n",
       "12                   305_ELA       Curtis and Schindler, 1997  49.693064   \n",
       "13                   382_ELA       Curtis and Schindler, 1997  49.705721   \n",
       "14    Large lakes simulation               Hanson et al. 2011        NaN   \n",
       "15                Örsträsket              Jonsson et al. 2001  64.517180   \n",
       "16           La Grande_100M1       Weissenberger et al. 2010   53.800651   \n",
       "17            Allequash Lake              Hanson et al. 2014   46.039459   \n",
       "18           Big Muskellunge              Hanson et al. 2014   46.019913   \n",
       "19              Cristal Lake              Hanson et al. 2014   46.001493   \n",
       "20          Crystal Bog Lake              Hanson et al. 2014   46.005338   \n",
       "21            Trout Bog Lake              Hanson et al. 2014   46.022565   \n",
       "22            Sparkling Lake              Hanson et al. 2014   46.010614   \n",
       "23                Trout Lake              Hanson et al. 2014   46.032070   \n",
       "24                   Mälaren              Köhler et al. 2013   59.489726   \n",
       "25          Mälaren- Basin C              Köhler et al. 2013   59.454848   \n",
       "26          Mälaren- Basin E              Köhler et al. 2013   59.362595   \n",
       "27                Glass Lake              McKnight et al 1997  40.281498   \n",
       "28                Blue Chalk           Dillon and Molot, 1997  45.198369   \n",
       "29                      Chub           Dillon and Molot, 1997  45.295781   \n",
       "30                   Crosson           Dillon and Molot, 1997  45.083339   \n",
       "31                    Dickie           Dillon and Molot, 1997  45.149419   \n",
       "32                      Harp           Dillon and Molot, 1997  45.379402   \n",
       "33                   Plastic           Dillon and Molot, 1997  45.178951   \n",
       "34                 Red Chalk           Dillon and Molot, 1997  45.189205   \n",
       "35              Green Lake 4               Miller et al. 2009  40.137481   \n",
       "36         San Diego Bay_Aug  Delgadillo-Hinojosa et al. 2008  32.655563   \n",
       "37                  Superior         Stephens and Minor, 2010  47.765176   \n",
       "\n",
       "           Lon  \n",
       "0   -71.693437  \n",
       "1    16.697475  \n",
       "2   -93.723074  \n",
       "3   -93.757008  \n",
       "4   -93.726280  \n",
       "5   -93.723190  \n",
       "6   -93.716152  \n",
       "7   -93.714264  \n",
       "8   -93.741730  \n",
       "9   -93.688515  \n",
       "10  -93.742245  \n",
       "11  -93.748425  \n",
       "12  -93.688172  \n",
       "13  -93.677185  \n",
       "14         NaN  \n",
       "15   20.030302  \n",
       "16  -75.303498  \n",
       "17  -89.623732  \n",
       "18  -89.618152  \n",
       "19  -89.612842  \n",
       "20  -89.624411  \n",
       "21  -89.710800  \n",
       "22  -89.699813  \n",
       "23  -89.692089  \n",
       "24   17.495327  \n",
       "25   17.201443  \n",
       "26   17.800198  \n",
       "27 -105.665529  \n",
       "28  -78.939520  \n",
       "29  -79.233920  \n",
       "30  -79.036981  \n",
       "31  -79.089381  \n",
       "32  -79.135650  \n",
       "33  -78.823775  \n",
       "34  -78.948275  \n",
       "35 -105.585486  \n",
       "36 -117.136967  \n",
       "37  -86.985054  "
      ]
     },
     "execution_count": 13,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "unique_coords_df = cat_lake_df.drop_duplicates(subset=[\"Lat\", \"Lon\"]).reset_index(drop=True)\n",
    "unique_coords_df"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "51c91233-eb08-41c8-9496-04dc9eeeb03f",
   "metadata": {},
   "source": [
    "Just did the comparison manually in the end. Result: 15/85 of Evans et al. sites are in the Catalan et al. data.\n",
    "Limiting this to just the oligotrophic lakes where retention > 0.1, then 15/49 lakes are in Evans et al. data. Can't really afford to drop those... Then would only have 34 data points. Live with the overlap?? Or drop?"
   ]
  }
 ],
 "metadata": {
  "kernelspec": {
   "display_name": "Python 3 (ipykernel)",
   "language": "python",
   "name": "python3"
  },
  "language_info": {
   "codemirror_mode": {
    "name": "ipython",
    "version": 3
   },
   "file_extension": ".py",
   "mimetype": "text/x-python",
   "name": "python",
   "nbconvert_exporter": "python",
   "pygments_lexer": "ipython3",
   "version": "3.11.6"
  }
 },
 "nbformat": 4,
 "nbformat_minor": 5
}