<jupyter_start><jupyter_text>Trailhead eBird hotspot update1. Ask the eBird API for the latest list of hotspots for each trailhead2. Add new eBird hotspots to the table 'Hotspots' in the trailheadDirectBirds_sous database3. Update hotspot data<jupyter_code>def get_trailheadHotspots(radius: int = 4,fmt: str = 'json'):
    trailheadRef = getTrailheadRef()
    cleanTrailheads = pd.DataFrame(clean_trailheadRef(trailheadRef.Latitude,trailheadRef.Longitude,trailheadRef.Address))
    import time
    trailheadHotspots = []
    try:
        for StopName in cleanTrailheads.itertuples():
            time.sleep(0.3)
            ebird_url = 'https://api.ebird.org/v2/ref/hotspot/geo?'
            ebird_auth_header = {'X-eBirdApiToken': ebird_token}
            ebird_params = {
                'lat': str(StopName.Latitude),
                'lng': str(StopName.Longitude),
                'dist': str(radius),
                'fmt': str(fmt)
            }
            ebird_request = requests.get(ebird_url,headers=ebird_auth_header,params=ebird_params)
            ebird_response = pd.DataFrame(ebird_request.json())
            if ebird_request.status_code == requests.codes.ok:
                ebird_response['StopName'] = StopName.StopName
                trailheadHotspots.append(ebird_response)
            ebird_request.raise_for_status()
    except Exception as ee:
        raise UserWarning(ee)
    all_trailheadHotspots = pd.DataFrame()
    all_trailheadHotspots = pd.concat(trailheadHotspots,ignore_index=True)
    return all_trailheadHotspots
##hotspots = get_trailheadHotspots()

def update_trailheadHotspots():
    cnx = connectDB()
    new_hotspots = pd.DataFrame(get_trailheadHotspots())
    ##new_hotspots = pd.DataFrame(hotspots)
    new_hotspots['latestUpdate'] = dt.datetime.today()
    try:
        current_hotspots = pd.DataFrame()
        current_hotspots = pd.read_sql('select * from hotspots',cnx,parse_dates=["latestObsDt","latestUpdate"])
        #TODO #61 write an exception to creat a 'hotspots' table in the current database if one does not exist
        current_hotspots.set_index('index',inplace=True)
    except Exception as ex:
        raise UserWarning(ex.__cause__)
    else:
        frames = [new_hotspots,current_hotspots]
        all_hotspots = pd.concat(frames,ignore_index=True)
        all_hotspots.sort_values(by=['StopName','locId','latestObsDt','latestUpdate'],ascending=False,inplace=True)
        all_hotspots.drop_duplicates(subset=['StopName','locId'],keep='first',inplace=True)
        all_hotspots.to_sql(name='Hotspots_cooking',con=cnx,if_exists='append')
        cnx.close()
    return all_hotspots
def closestStop():
    import geopy.distance
    cnx = connectDB()
    try:
        geoTable = pd.read_sql('SELECT locId,hs.StopName,lat,lng,Latitude,Longitude FROM Hotspots as hs LEFT JOIN trailheadRef as tr ON hs.StopName = tr.StopName',con=cnx)
        geoTable['dist_fromStop'] = geoTable.apply(lambda g: geopy.distance.distance((g.lat,g.lng),(g.Latitude,g.Longitude)).miles,axis=1)
        geoTable.sort_values(by=['locId','dist_fromStop'],ascending=True,inplace=True)
        geoTable.drop_duplicates(subset=['locId'],keep='first',inplace=True)
        geoTable.drop(columns=['lat','lng','Latitude','Longitude'],inplace=True)
        #geoTable.set_index('index',inplace=True)
        #geoTable.reset_index(inplace=True)
        geoTable.to_sql(name='closestStop',con=cnx,if_exists='replace')
        cnx.close()
    except Exception as proxExc:
        raise proxExc
    return geoTable
update_trailheadHotspots()<jupyter_output><empty_output>
{'fraction_non_alphanumeric': 0.11370586599944398, 'fraction_numerical': 0.0027800945232137894, 'mean_word_length': 3.746701846965699, 'pattern_counts': {'":': 0, '<': 5, '<?xml version=': 0, '>': 5, 'https://': 1, 'lorem ipsum': 0, 'www.': 0, 'xml': 0}, 'pii_count': 0, 'substrings_counts': 0, 'word_list_counts': {'cursed_substrings.json': 0, 'profanity_word_list.json': 0, 'sexual_word_list.json': 0, 'zh_pornsignals.json': 0}}
{'chain_length': '1', 'dir': 'jupyter-structured-clean-dedup', 'id': '214636', 'license': 'MIT', 'n_tokens_mistral': 1220, 'n_tokens_neox': 1161, 'n_words': 185, 'path': 'sous/hotspots_cooking.ipynb', 'repo_name': 'Tanag3r/trailheadDirect_birds', 'size': '3597'}