Lake Levels ETL

Setting up db tables

In [4]:
import pandas as pd

from sqlalchemy import create_engine
from sqlalchemy import Column, Date, Integer, String, Float, Table

# import schema from python file
from schema_lake_levels import Base
from schema_lake_levels import Lake_names, Lake_levels

Create tables from schema file in SQLite

In [5]:
# create sqlite file if not exist, establish connection, create tables from schema (using 'Base')
database_path = "resources/lake-levels-data.sqlite"
engine = create_engine(f"sqlite:///{database_path}")
Base.metadata.create_all(engine)

Entering tables into SQLite

Lake names, ids --> reformat, enter into database

In [6]:
# read in csv file
lake_id_df = pd.read_csv("data/water-level/lakes/MCWD_Lake_ID.csv")

lake_id_df.head()
Out[6]:
LAKE_NAME LAKE_ID latitude longitude
0 Hiawatha 27001800 44.921034 -93.236141
1 Mother 27002300 44.893298 -93.241013
2 Nokomis 27001900 44.908634 -93.242187
3 Taft 27068300 44.892951 -93.249752
4 Legion 27002400 44.885760 -93.262240
In [7]:
# make col names lowercase
lake_id_df.columns = ['name', 'id', 'lat', 'lng']

# drop duplicate ids
lake_id_df.drop_duplicates(subset='id', inplace=True)

# set 'lake_id' as index
lake_id_df.set_index('id', inplace=True)

# preview
lake_id_df.head()
Out[7]:
name lat lng
id
27001800 Hiawatha 44.921034 -93.236141
27002300 Mother 44.893298 -93.241013
27001900 Nokomis 44.908634 -93.242187
27068300 Taft 44.892951 -93.249752
27002400 Legion 44.885760 -93.262240
In [8]:
# export lake_id_df as SQL table 'lake_names'
lake_id_df.to_sql('lake_names', con=engine, if_exists='replace', index=True)
print("Values inserted into: 'lake_names'")

Values inserted into: 'lake_names'
In [142]:
# function to convert all lake data for a given lake id into dict format
def lake_to_dict(lake_id):

    # grab all measurements for lake
    elevations = list(test.loc[test.id == lake_id].elevation)
    read_dates = list(test.loc[test.id == lake_id].read_date)
    datum_adjs = list(test.loc[test.id == lake_id].datum_adj)
    
    measurements = list(zip(elevations, read_dates, datum_adjs))

    measurement_keys = ['elevation', 'read_date', 'datum_adj']
    
    # make measurements json format
    measurements_json = []
    for i in range(len(aslist)):
        measurement_dict = dict(zip(measurement_keys, aslist[i]))
        measurements_json.append(measurement_dict)

    # create lake_dict using measurements_json
    lake_dict = {
        'lake':
            {
                'name': lake_id_df['name'][lake_id],
                'id': lake_id,
                'location': {
                    'lat': lake_id_df['lat'][lake_id],
                    'lng': lake_id_df['lng'][lake_id]
                },
                'measurements': measurements_json
            }
    }
    
    return lake_dict

Scrape lake level data for all lakes in watershed, reformat and enter into SQLite

In [9]:
# base url for scraping lake-level data
base_url = "https://files.dnr.state.mn.us/cgi-bin/lk_levels_dump.pl?format=csv&id="
In [17]:
# variable to track progress of loop
lake_count = 0

# store ids for lakes that failed and succeeded to be inserted
failed = []
successful = []

df_list = []

# loop through index of lake_id_df (lake ids are the index)
for lake_id in lake_id_df.index:
    
    lake_count += 1
    print(f"Getting data for lake {lake_count} of {len(lake_id_df.index)}.......")
    print(f"Lake id: {lake_id}.........")
    
    
    try:
        # read data from url into df
        df = pd.read_csv(f"{base_url}{lake_id}")

        # change column names to lowercase
        df.columns = map(str.lower, df.columns)

        # rename 'chr_id' to 'id'
        df.rename(columns={"chr_id": "id"}, inplace=True)

        # drop duplicate date entries
        df.drop_duplicates(subset='read_date', inplace=True)
        
        df_list.append(df.copy())
        
        # set multi-index for multiple primary keys
        df.set_index(['id', 'read_date'], inplace=True)
        
        
        
        # insert df into sql table 'lake_levels'
        df.to_sql('lake_levels', con=engine, if_exists='append', index=True, index_label=['id', 'read_date'])
        print("..............Values successfully inserted into 'lake_levels'")
        print("------------------------------------------------------------------\n\n")
        successful.append(lake_id)
    except:
        print(".......................Process failed")
        print("xxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxx\n\n")
        failed.append(lake_id)

Getting data for lake 1 of 141.......
Lake id: 27001800.........
..............Values successfully inserted into 'lake_levels'
------------------------------------------------------------------


Getting data for lake 2 of 141.......
Lake id: 27002300.........
..............Values successfully inserted into 'lake_levels'
------------------------------------------------------------------


Getting data for lake 3 of 141.......
Lake id: 27001900.........
..............Values successfully inserted into 'lake_levels'
------------------------------------------------------------------


Getting data for lake 4 of 141.......
Lake id: 27068300.........
..............Values successfully inserted into 'lake_levels'
------------------------------------------------------------------


Getting data for lake 5 of 141.......
Lake id: 27002400.........
..............Values successfully inserted into 'lake_levels'
------------------------------------------------------------------


Getting data for lake 6 of 141.......
Lake id: 27002200.........
..............Values successfully inserted into 'lake_levels'
------------------------------------------------------------------


Getting data for lake 7 of 141.......
Lake id: 27068400.........
..............Values successfully inserted into 'lake_levels'
------------------------------------------------------------------


Getting data for lake 8 of 141.......
Lake id: 27068500.........
..............Values successfully inserted into 'lake_levels'
------------------------------------------------------------------


Getting data for lake 9 of 141.......
Lake id: 27001600.........
..............Values successfully inserted into 'lake_levels'
------------------------------------------------------------------


Getting data for lake 10 of 141.......
Lake id: 27001700.........
..............Values successfully inserted into 'lake_levels'
------------------------------------------------------------------


Getting data for lake 11 of 141.......
Lake id: 27004000.........
..............Values successfully inserted into 'lake_levels'
------------------------------------------------------------------


Getting data for lake 12 of 141.......
Lake id: 27003100.........
..............Values successfully inserted into 'lake_levels'
------------------------------------------------------------------


Getting data for lake 13 of 141.......
Lake id: 27003900.........
..............Values successfully inserted into 'lake_levels'
------------------------------------------------------------------


Getting data for lake 14 of 141.......
Lake id: 27003800.........
..............Values successfully inserted into 'lake_levels'
------------------------------------------------------------------


Getting data for lake 15 of 141.......
Lake id: 27067500.........
..............Values successfully inserted into 'lake_levels'
------------------------------------------------------------------


Getting data for lake 16 of 141.......
Lake id: 27001500.........
..............Values successfully inserted into 'lake_levels'
------------------------------------------------------------------


Getting data for lake 17 of 141.......
Lake id: 27065600.........
..............Values successfully inserted into 'lake_levels'
------------------------------------------------------------------


Getting data for lake 18 of 141.......
Lake id: 27066400.........
..............Values successfully inserted into 'lake_levels'
------------------------------------------------------------------


Getting data for lake 19 of 141.......
Lake id: 27004100.........
..............Values successfully inserted into 'lake_levels'
------------------------------------------------------------------


Getting data for lake 20 of 141.......
Lake id: 27067000.........
..............Values successfully inserted into 'lake_levels'
------------------------------------------------------------------


Getting data for lake 21 of 141.......
Lake id: 27066900.........
..............Values successfully inserted into 'lake_levels'
------------------------------------------------------------------


Getting data for lake 22 of 141.......
Lake id: 27005400.........
..............Values successfully inserted into 'lake_levels'
------------------------------------------------------------------


Getting data for lake 23 of 141.......
Lake id: 27066100.........
..............Values successfully inserted into 'lake_levels'
------------------------------------------------------------------


Getting data for lake 24 of 141.......
Lake id: 27071000.........
..............Values successfully inserted into 'lake_levels'
------------------------------------------------------------------


Getting data for lake 25 of 141.......
Lake id: 27005100.........
..............Values successfully inserted into 'lake_levels'
------------------------------------------------------------------


Getting data for lake 26 of 141.......
Lake id: 27077900.........
..............Values successfully inserted into 'lake_levels'
------------------------------------------------------------------


Getting data for lake 27 of 141.......
Lake id: 27071400.........
..............Values successfully inserted into 'lake_levels'
------------------------------------------------------------------


Getting data for lake 28 of 141.......
Lake id: 27005300.........
..............Values successfully inserted into 'lake_levels'
------------------------------------------------------------------


Getting data for lake 29 of 141.......
Lake id: 27005200.........
..............Values successfully inserted into 'lake_levels'
------------------------------------------------------------------


Getting data for lake 30 of 141.......
Lake id: 27071300.........
..............Values successfully inserted into 'lake_levels'
------------------------------------------------------------------


Getting data for lake 31 of 141.......
Lake id: 27008400.........
..............Values successfully inserted into 'lake_levels'
------------------------------------------------------------------


Getting data for lake 32 of 141.......
Lake id: 27008200.........
..............Values successfully inserted into 'lake_levels'
------------------------------------------------------------------


Getting data for lake 33 of 141.......
Lake id: 27077100.........
..............Values successfully inserted into 'lake_levels'
------------------------------------------------------------------


Getting data for lake 34 of 141.......
Lake id: 27075100.........
..............Values successfully inserted into 'lake_levels'
------------------------------------------------------------------


Getting data for lake 35 of 141.......
Lake id: 27075000.........
..............Values successfully inserted into 'lake_levels'
------------------------------------------------------------------


Getting data for lake 36 of 141.......
Lake id: 27074700.........
..............Values successfully inserted into 'lake_levels'
------------------------------------------------------------------


Getting data for lake 37 of 141.......
Lake id: 27073900.........
..............Values successfully inserted into 'lake_levels'
------------------------------------------------------------------


Getting data for lake 38 of 141.......
Lake id: 27013300.........
..............Values successfully inserted into 'lake_levels'
------------------------------------------------------------------


Getting data for lake 39 of 141.......
Lake id: 27074600.........
..............Values successfully inserted into 'lake_levels'
------------------------------------------------------------------


Getting data for lake 40 of 141.......
Lake id: 27068701.........
..............Values successfully inserted into 'lake_levels'
------------------------------------------------------------------


Getting data for lake 41 of 141.......
Lake id: 27013301.........
..............Values successfully inserted into 'lake_levels'
------------------------------------------------------------------


Getting data for lake 42 of 141.......
Lake id: 27008500.........
..............Values successfully inserted into 'lake_levels'
------------------------------------------------------------------


Getting data for lake 43 of 141.......
Lake id: 99001038.........
..............Values successfully inserted into 'lake_levels'
------------------------------------------------------------------


Getting data for lake 44 of 141.......
Lake id: 27009500.........
..............Values successfully inserted into 'lake_levels'
------------------------------------------------------------------


Getting data for lake 45 of 141.......
Lake id: 27010800.........
..............Values successfully inserted into 'lake_levels'
------------------------------------------------------------------


Getting data for lake 46 of 141.......
Lake id: 27046800.........
..............Values successfully inserted into 'lake_levels'
------------------------------------------------------------------


Getting data for lake 47 of 141.......
Lake id: 27008600.........
..............Values successfully inserted into 'lake_levels'
------------------------------------------------------------------


Getting data for lake 48 of 141.......
Lake id: 27082200.........
..............Values successfully inserted into 'lake_levels'
------------------------------------------------------------------


Getting data for lake 49 of 141.......
Lake id: 27082400.........
..............Values successfully inserted into 'lake_levels'
------------------------------------------------------------------


Getting data for lake 50 of 141.......
Lake id: 27013302.........
..............Values successfully inserted into 'lake_levels'
------------------------------------------------------------------


Getting data for lake 51 of 141.......
Lake id: 27010900.........
..............Values successfully inserted into 'lake_levels'
------------------------------------------------------------------


Getting data for lake 52 of 141.......
Lake id: 27008700.........
..............Values successfully inserted into 'lake_levels'
------------------------------------------------------------------


Getting data for lake 53 of 141.......
Lake id: 27087000.........
..............Values successfully inserted into 'lake_levels'
------------------------------------------------------------------


Getting data for lake 54 of 141.......
Lake id: 27013400.........
..............Values successfully inserted into 'lake_levels'
------------------------------------------------------------------


Getting data for lake 55 of 141.......
Lake id: 27013303.........
..............Values successfully inserted into 'lake_levels'
------------------------------------------------------------------


Getting data for lake 56 of 141.......
Lake id: 27087600.........
..............Values successfully inserted into 'lake_levels'
------------------------------------------------------------------


Getting data for lake 57 of 141.......
Lake id: 27015800.........
..............Values successfully inserted into 'lake_levels'
------------------------------------------------------------------


Getting data for lake 58 of 141.......
Lake id: 27015900.........
..............Values successfully inserted into 'lake_levels'
------------------------------------------------------------------


Getting data for lake 59 of 141.......
Lake id: 27013800.........
..............Values successfully inserted into 'lake_levels'
------------------------------------------------------------------


Getting data for lake 60 of 141.......
Lake id: 27087700.........
..............Values successfully inserted into 'lake_levels'
------------------------------------------------------------------


Getting data for lake 61 of 141.......
Lake id: 27014200.........
..............Values successfully inserted into 'lake_levels'
------------------------------------------------------------------


Getting data for lake 62 of 141.......
Lake id: 27013304.........
..............Values successfully inserted into 'lake_levels'
------------------------------------------------------------------


Getting data for lake 63 of 141.......
Lake id: 27016000.........
..............Values successfully inserted into 'lake_levels'
------------------------------------------------------------------


Getting data for lake 64 of 141.......
Lake id: 27014100.........
..............Values successfully inserted into 'lake_levels'
------------------------------------------------------------------


Getting data for lake 65 of 141.......
Lake id: 27014400.........
..............Values successfully inserted into 'lake_levels'
------------------------------------------------------------------


Getting data for lake 66 of 141.......
Lake id: 27089600.........
..............Values successfully inserted into 'lake_levels'
------------------------------------------------------------------


Getting data for lake 67 of 141.......
Lake id: 27015700.........
..............Values successfully inserted into 'lake_levels'
------------------------------------------------------------------


Getting data for lake 68 of 141.......
Lake id: 27089800.........
..............Values successfully inserted into 'lake_levels'
------------------------------------------------------------------


Getting data for lake 69 of 141.......
Lake id: 27016100.........
..............Values successfully inserted into 'lake_levels'
------------------------------------------------------------------


Getting data for lake 70 of 141.......
Lake id: 27014300.........
..............Values successfully inserted into 'lake_levels'
------------------------------------------------------------------


Getting data for lake 71 of 141.......
Lake id: 27014000.........
..............Values successfully inserted into 'lake_levels'
------------------------------------------------------------------


Getting data for lake 72 of 141.......
Lake id: 27090000.........
..............Values successfully inserted into 'lake_levels'
------------------------------------------------------------------


Getting data for lake 73 of 141.......
Lake id: 27015600.........
..............Values successfully inserted into 'lake_levels'
------------------------------------------------------------------


Getting data for lake 74 of 141.......
Lake id: 27015500.........
..............Values successfully inserted into 'lake_levels'
------------------------------------------------------------------


Getting data for lake 75 of 141.......
Lake id: 27090800.........
..............Values successfully inserted into 'lake_levels'
------------------------------------------------------------------


Getting data for lake 76 of 141.......
Lake id: 27015000.........
..............Values successfully inserted into 'lake_levels'
------------------------------------------------------------------


Getting data for lake 77 of 141.......
Lake id: 27015100.........
..............Values successfully inserted into 'lake_levels'
------------------------------------------------------------------


Getting data for lake 78 of 141.......
Lake id: 27013310.........
..............Values successfully inserted into 'lake_levels'
------------------------------------------------------------------


Getting data for lake 79 of 141.......
Lake id: 27091200.........
..............Values successfully inserted into 'lake_levels'
------------------------------------------------------------------


Getting data for lake 80 of 141.......
Lake id: 27013305.........
..............Values successfully inserted into 'lake_levels'
------------------------------------------------------------------


Getting data for lake 81 of 141.......
Lake id: 27013311.........
..............Values successfully inserted into 'lake_levels'
------------------------------------------------------------------


Getting data for lake 82 of 141.......
Lake id: 27016200.........
..............Values successfully inserted into 'lake_levels'
------------------------------------------------------------------


Getting data for lake 83 of 141.......
Lake id: 10000900.........
..............Values successfully inserted into 'lake_levels'
------------------------------------------------------------------


Getting data for lake 84 of 141.......
Lake id: 99001005.........
..............Values successfully inserted into 'lake_levels'
------------------------------------------------------------------


Getting data for lake 85 of 141.......
Lake id: 27013312.........
..............Values successfully inserted into 'lake_levels'
------------------------------------------------------------------


Getting data for lake 86 of 141.......
Lake id: 27051800.........
..............Values successfully inserted into 'lake_levels'
------------------------------------------------------------------


Getting data for lake 87 of 141.......
Lake id: 27013313.........
..............Values successfully inserted into 'lake_levels'
------------------------------------------------------------------


Getting data for lake 88 of 141.......
Lake id: 27052200.........
..............Values successfully inserted into 'lake_levels'
------------------------------------------------------------------


Getting data for lake 89 of 141.......
Lake id: 10020600.........
..............Values successfully inserted into 'lake_levels'
------------------------------------------------------------------


Getting data for lake 90 of 141.......
Lake id: 10001100.........
..............Values successfully inserted into 'lake_levels'
------------------------------------------------------------------


Getting data for lake 91 of 141.......
Lake id: 27015400.........
..............Values successfully inserted into 'lake_levels'
------------------------------------------------------------------


Getting data for lake 92 of 141.......
Lake id: 10001500.........
..............Values successfully inserted into 'lake_levels'
------------------------------------------------------------------


Getting data for lake 93 of 141.......
Lake id: 27013900.........
..............Values successfully inserted into 'lake_levels'
------------------------------------------------------------------


Getting data for lake 94 of 141.......
Lake id: 10001000.........
..............Values successfully inserted into 'lake_levels'
------------------------------------------------------------------


Getting data for lake 95 of 141.......
Lake id: 27013306.........
..............Values successfully inserted into 'lake_levels'
------------------------------------------------------------------


Getting data for lake 96 of 141.......
Lake id: 27096500.........
..............Values successfully inserted into 'lake_levels'
------------------------------------------------------------------


Getting data for lake 97 of 141.......
Lake id: 27094500.........
..............Values successfully inserted into 'lake_levels'
------------------------------------------------------------------


Getting data for lake 98 of 141.......
Lake id: 27040800.........
..............Values successfully inserted into 'lake_levels'
------------------------------------------------------------------


Getting data for lake 99 of 141.......
Lake id: 99000999.........
..............Values successfully inserted into 'lake_levels'
------------------------------------------------------------------


Getting data for lake 100 of 141.......
Lake id: 10001800.........
..............Values successfully inserted into 'lake_levels'
------------------------------------------------------------------


Getting data for lake 101 of 141.......
Lake id: 27094800.........
..............Values successfully inserted into 'lake_levels'
------------------------------------------------------------------


Getting data for lake 102 of 141.......
Lake id: 27013315.........
..............Values successfully inserted into 'lake_levels'
------------------------------------------------------------------


Getting data for lake 103 of 141.......
Lake id: 27013314.........
..............Values successfully inserted into 'lake_levels'
------------------------------------------------------------------


Getting data for lake 104 of 141.......
Lake id: 27094700.........
..............Values successfully inserted into 'lake_levels'
------------------------------------------------------------------


Getting data for lake 105 of 141.......
Lake id: 10004500.........
..............Values successfully inserted into 'lake_levels'
------------------------------------------------------------------


Getting data for lake 106 of 141.......
Lake id: 10004100.........
..............Values successfully inserted into 'lake_levels'
------------------------------------------------------------------


Getting data for lake 107 of 141.......
Lake id: 10020001.........
..............Values successfully inserted into 'lake_levels'
------------------------------------------------------------------


Getting data for lake 108 of 141.......
Lake id: 10004600.........
..............Values successfully inserted into 'lake_levels'
------------------------------------------------------------------


Getting data for lake 109 of 141.......
Lake id: 10020002.........
..............Values successfully inserted into 'lake_levels'
------------------------------------------------------------------


Getting data for lake 110 of 141.......
Lake id: 10004700.........
..............Values successfully inserted into 'lake_levels'
------------------------------------------------------------------


Getting data for lake 111 of 141.......
Lake id: 10004800.........
..............Values successfully inserted into 'lake_levels'
------------------------------------------------------------------


Getting data for lake 112 of 141.......
Lake id: 27018200.........
..............Values successfully inserted into 'lake_levels'
------------------------------------------------------------------


Getting data for lake 113 of 141.......
Lake id: 10005600.........
..............Values successfully inserted into 'lake_levels'
------------------------------------------------------------------


Getting data for lake 114 of 141.......
Lake id: 10005400.........
..............Values successfully inserted into 'lake_levels'
------------------------------------------------------------------


Getting data for lake 115 of 141.......
Lake id: 10004400.........
..............Values successfully inserted into 'lake_levels'
------------------------------------------------------------------


Getting data for lake 116 of 141.......
Lake id: 27109600.........
..............Values successfully inserted into 'lake_levels'
------------------------------------------------------------------


Getting data for lake 117 of 141.......
Lake id: 27018100.........
..............Values successfully inserted into 'lake_levels'
------------------------------------------------------------------


Getting data for lake 118 of 141.......
Lake id: 10013400.........
..............Values successfully inserted into 'lake_levels'
------------------------------------------------------------------


Getting data for lake 119 of 141.......
Lake id: 27095200.........
..............Values successfully inserted into 'lake_levels'
------------------------------------------------------------------


Getting data for lake 120 of 141.......
Lake id: 10013500.........
..............Values successfully inserted into 'lake_levels'
------------------------------------------------------------------


Getting data for lake 121 of 141.......
Lake id: 10005000.........
..............Values successfully inserted into 'lake_levels'
------------------------------------------------------------------


Getting data for lake 122 of 141.......
Lake id: 27013309.........
..............Values successfully inserted into 'lake_levels'
------------------------------------------------------------------


Getting data for lake 123 of 141.......
Lake id: 27018500.........
..............Values successfully inserted into 'lake_levels'
------------------------------------------------------------------


Getting data for lake 124 of 141.......
Lake id: 10004401.........
..............Values successfully inserted into 'lake_levels'
------------------------------------------------------------------


Getting data for lake 125 of 141.......
Lake id: 10005300.........
..............Values successfully inserted into 'lake_levels'
------------------------------------------------------------------


Getting data for lake 126 of 141.......
Lake id: 10013800.........
..............Values successfully inserted into 'lake_levels'
------------------------------------------------------------------


Getting data for lake 127 of 141.......
Lake id: 10019100.........
..............Values successfully inserted into 'lake_levels'
------------------------------------------------------------------


Getting data for lake 128 of 141.......
Lake id: 27018300.........
..............Values successfully inserted into 'lake_levels'
------------------------------------------------------------------


Getting data for lake 129 of 141.......
Lake id: 10004900.........
..............Values successfully inserted into 'lake_levels'
------------------------------------------------------------------


Getting data for lake 130 of 141.......
Lake id: 10013900.........
..............Values successfully inserted into 'lake_levels'
------------------------------------------------------------------


Getting data for lake 131 of 141.......
Lake id: 27095800.........
..............Values successfully inserted into 'lake_levels'
------------------------------------------------------------------


Getting data for lake 132 of 141.......
Lake id: 27093900.........
..............Values successfully inserted into 'lake_levels'
------------------------------------------------------------------


Getting data for lake 133 of 141.......
Lake id: 27095700.........
..............Values successfully inserted into 'lake_levels'
------------------------------------------------------------------


Getting data for lake 134 of 141.......
Lake id: 10014000.........
..............Values successfully inserted into 'lake_levels'
------------------------------------------------------------------


Getting data for lake 135 of 141.......
Lake id: 10004300.........
..............Values successfully inserted into 'lake_levels'
------------------------------------------------------------------


Getting data for lake 136 of 141.......
Lake id: 10014100.........
..............Values successfully inserted into 'lake_levels'
------------------------------------------------------------------


Getting data for lake 137 of 141.......
Lake id: 10005100.........
..............Values successfully inserted into 'lake_levels'
------------------------------------------------------------------


Getting data for lake 138 of 141.......
Lake id: 10004200.........
..............Values successfully inserted into 'lake_levels'
------------------------------------------------------------------


Getting data for lake 139 of 141.......
Lake id: 10019000.........
..............Values successfully inserted into 'lake_levels'
------------------------------------------------------------------


Getting data for lake 140 of 141.......
Lake id: 27018600.........
..............Values successfully inserted into 'lake_levels'
------------------------------------------------------------------


Getting data for lake 141 of 141.......
Lake id: 10014300.........
..............Values successfully inserted into 'lake_levels'
------------------------------------------------------------------
In [18]:
# print failed lake additions
print(f"Failed additions: {len(failed)}")
print("----------------------------------------------\n")
for id in failed:
    print(id, lake_id_df.name[id])

Failed additions: 0
----------------------------------------------
In [9]:
# print successful additions
print(f"Successful additions: {len(successful)}")
print("----------------------------------------------\n")
for id in successful:
    print(id, lake_id_df.name[id])

Successful additions: 141
----------------------------------------------

27001800 Hiawatha
27002300 Mother
27001900 Nokomis
27068300 Taft
27002400 Legion
27002200 Diamond
27068400 Milner Pond
27068500 Norby's Pond
27001600 Harriet
27001700 Cemetery
27004000 Lake of the Isles
27003100 Calhoun
27003900 Cedar
27003800 Brownie
27067500 Pamela Pond
27001500 Bass
27065600 Twin
27066400 Wolfe Park
27004100 Edina Mill Pond
27067000 Harvey
27066900 Melody
27005400 Meadowbrook
27066100 South Oak
27071000 Lamplighter
27005100 Victoria
27077900 Unnamed
27071400 Westling
27005300 Unnamed (Cobblecrest)
27005200 Hannan
27071300 Unnamed (Cedar Manor)
27008400 Minnehaha Marsh
27008200 Windsor
27077100 Unnamed
27075100 Unnamed
27075000 Unnamed
27074700 Unnamed
27073900 Unnamed
27013300 Gray's Bay Outlet
27074600 Unnamed
27068701 Unnamed (East)
27013301 Grays Bay
27008500 Libbs
99001038 Unnamed
27009500 Gleason
27010800 Snyder
27046800 Unnamed
27008600 Shaver
27082200 Unnamed
27082400 Unnamed
27013302 Wayzata Bay
27010900 Hadley
27008700 Marion
27087000 Louise
27013400 Mooney
27013303 Carsons Bay
27087600 Hooper
27015800 Holy Name
27015900 Lydiard
27013800 Peavey Lake
27087700 Unnamed
27014200 William
27013304 St. Albans Bay
27016000 Long
27014100 Tanager Lake
27014400 Galpin
27089600 Unnamed (Mud)
27015700 Wolsfeld
27089800 Unnamed
27016100 Dickey's
27014300 Mary
27014000 French Marsh
27090000 Unnamed
27015600 Thies
27015500 Krieg
27090800 Unnamed
27015000 Marsh
27015100 School
27013310 Crystal Bay
27091200 Unnamed
27013305 Carman Bay
27013311 Maxwell Bay
27016200 Classen
10000900 Minnewashta
99001005 Unnamed
27013312 Stubbs Bay
27051800 Academy Marsh
27013313 North Arm
27052200 Unnamed
10020600 Unnamed
10001100 St. Joe
27015400 Katrina
10001500 Virginia
27013900 Forest Lake
10001000 Tamarack
27013306 Black Lake
27096500 Unnamed
27094500 Unnamed
27040800 Unnamed
99000999 Unnamed
10001800 Schutz
27094800 Unnamed
27013315 Jennings Bay
27013314 Harrisons Bay
27094700 Unnamed
10004500 Steiger
10004100 Zumbra
10020001 Unnamed (north portion)
10004600 Church
10020002 Unnamed (south portion)
10004700 Kelser's Pond
10004800 Wassermann
27018200 Langdon
10005600 Stone
10005400 Marsh
10004400 East Auburn
27109600 Unnamed
27018100 Dutch
10013400 Sunny
27095200 Unnamed
10013500 Unnamed
10005000 Carl Krey
27013309 Halsted Bay
27018500 Saunders
10004401 West Auburn
10005300 Piersons
10013800 Unnamed
10019100 Unnamed
27018300 Unnamed (Black)
10004900 Unnamed (Auburn Marsh)
10013900 Unnamed
27095800 Unnamed
27093900 Unnamed
27095700 Unnamed
10014000 Unnamed
10004300 Lundsten South Bay
10014100 Unnamed
10005100 Turbid
10004200 Parley
10019000 Unnamed
27018600 Mud
10014300 Unnamed

Concatenate all df's created into big df

In [19]:
lake_levels_df = pd.concat(df_list)
In [20]:
lake_levels_df.head()
Out[20]:
id elevation read_date datum_adj
0 27001800 811.40 1926-04-15 NGVD 29
1 27001800 815.35 1926-08-05 NGVD 29
2 27001800 812.72 1927-03-29 NGVD 29
3 27001800 813.04 1927-11-30 NGVD 29
4 27001800 814.50 1928-04-12 NGVD 29