Datamining Drinking Water Watch

"""
Brandon Taylor, PE
Parsing Louisiana Drinking Water Watch (Part 1)
https://sdw.ldh.la.gov/DWW/
"""

import bs4
import csv
import os
import requests

url_actpws = "https://sdw.ldh.la.gov/DWW/JSP/SearchDispatch?number=&name=&county=All&" \
             "WaterSystemType=All&SourceWaterType=All&PointOfContactType=None&SampleType=null&" \
             "begin_date=8%2F11%2F2019&end_date=8%2F11%2F2021&action=Search+For+Water+Systems"

pws_html = ".\\data\\LA_PublicWaterSystems.html"
pws_csv = ".\\data\\LA_PublicWaterSystems.csv"


def main():
    # retrieve public water system inventory if it does not exist
    if not os.path.isfile(pws_html):
        resp = requests.get(url_actpws)
        resp.raise_for_status()
        if resp.text:
            soup = bs4.BeautifulSoup(resp.text, 'html.parser')
            # save copy of html file locally
            with open(pws_html, "w") as file:
                file.write(str(soup))

    # build csv inventory of public water systems if it does not exist
    if not os.path.isfile(pws_csv):

        # open html file and parse for all build list of all public water systems (pws)
        soup = bs4.BeautifulSoup(open(pws_html), 'html.parser')

        # find all pws links
        pwslinks = soup.find_all("a", attrs={'title': 'Water System Detail'})

        # dictionary for storing pws inventory
        pwss = {}

        # cycle thru public water systems and add them to pws dictionary
        for link in pwslinks:
            url = link['href'].rstrip()
            pwsid = link.text.rstrip()

            # url retrieved is missing website prefix so we will add here
            url = 'https://sdw.ldh.la.gov/DWW/JSP/' + url

            # update pws inventory dictionary
            pwss.update({pwsid: url})

        # save pws inventory to .csv
        with open(pws_csv, 'w', newline='') as csvfile:
            writer = csv.DictWriter(csvfile, fieldnames=['pwsid', 'url'])
            writer.writeheader()

            for pwsid, url in pwss.items():
                writer.writerow({'pwsid': pwsid, 'url': url})


if __name__ == '__main__':
    main()

"""
Brandon Taylor, PE
Parsing Louisiana Drinking Water Watch (Part 2)
https://sdw.ldh.la.gov/DWW/
"""

import bs4
import csv
import os
import requests

pws_csv = ".\\data\\LA_PublicWaterSystems.csv"
pws_dir = ".\\data\\pwss\\"


def main():
    pwsroster = {}

    # read pws roster .csv and build pws inventory
    with open(pws_csv, newline='') as csvfile:
        reader = csv.DictReader(csvfile)

        for row in reader:
            pwsroster.update({row['pwsid']: row['url']})

    # retrieve url (main pws page) and save html locally
    for pwsid, url in pwsroster.items():
        # create filename structure
        pwsfilename = os.path.join(pws_dir, pwsid + '_PWS.html')

        # if .html does not exist locally then retrieve
        if not os.path.isfile(pwsfilename):
            print(f'Retrieving mainpage for PWSID: {pwsid}')
            resp = requests.get(url)
            resp.raise_for_status()
            if resp.text:
                soup = bs4.BeautifulSoup(resp.text, 'html.parser')
                # save copy of html file locally
                with open(pwsfilename, "w") as file:
                    file.write(str(soup))


if __name__ == '__main__':
    main()

"""
Brandon Taylor, PE
Parsing Louisiana Drinking Water Watch (Part 3)
https://sdw.ldh.la.gov/DWW/
"""

import bs4
import csv
import os
import requests

pws_dir = ".\\data\\pwss\\"
pws_csv = ".\\data\\LA_PublicWaterSystems_wWaterSystemDetails.csv"


def main():
    # build list of all pws .html files stored locally
    pwss = [os.path.join(pws_dir, file) for file in os.listdir(pws_dir)
            if file.endswith('.html')]

    # we define our dictionary that will store all of our data
    pwssdict = {}

    # cycle thru pws .html files parsing desired data
    for pws_html in pwss:
        # open html file with beautiful soup
        soup = bs4.BeautifulSoup(open(pws_html), 'html.parser')

        # find pws info table
        pwsinfotbl = soup.find("table", attrs={'id': 'AutoNumber10'})

        # the Water System Details table follows the format of
        # 1st data cell <td> is field name and second data cell is the value
        # first we will grab all of the table data cells <td>
        tdcells = pwsinfotbl.find_all("td")

        # next we remove the html markup and strip the text
        tdtxt = [txt.text.rstrip() for txt in tdcells]

        # now we build a list of our fields and values
        fields = tdtxt[0::2]
        values = tdtxt[1::2]

        # now we build a dictionary combining fields and values
        pwsinfo = dict(zip(fields, values))

        # print(pwsinfo)

        # we append our newly created dict to our dictionary for all pwss
        pwssdict.update({pwsinfo['Water System No. :']: pwsinfo})

    # now we write our pws inventory data to a new csv
    with open(pws_csv, 'w', newline='') as csvfile:

        # we need a list of our keys to pass to csv.DictWriter
        # first we grab the first key and its values as a list
        firstdict = list(pwssdict.values())[0]
        keys = list(firstdict.keys())

        # now we write our csv
        writer = csv.DictWriter(csvfile, fieldnames=keys)

        writer.writeheader()

        for row in pwssdict.values():
            writer.writerow(row)


if __name__ == '__main__':
    main()

"""
Brandon Taylor, PE
Parsing Louisiana Drinking Water Watch (Part 4)
https://sdw.ldh.la.gov/DWW/
"""

import bs4
import csv
import os
import requests

pws_dir = ".\\data\\pwss\\"
pws_csv = ".\\data\\LA_PublicWaterSystems_ACContactDetails.csv"


def main():
    # build list of all pws .html files stored locally
    pwss = [os.path.join(pws_dir, file) for file in
            os.listdir(pws_dir) if file.endswith('.html')]

    # we define our dictionary that will store all of our data
    pocdict = {}

    # cycle thru pws .html files parsing desired data
    for pws_html in pwss:

        # open html file with beautiful soup
        soup = bs4.BeautifulSoup(open(pws_html), 'html.parser')

        # find water system details table (to get PWSID, PWSNAME)
        wsdetails = soup.find("table", attrs={'id': 'AutoNumber10'})
        pwsid = wsdetails.find_all("tr")[0].find_all("td")[1].text.strip()
        pwsname = wsdetails.find_all("tr")[1].find_all("td")[1].text.strip()

        # find points of contact table
        pocinfotbl = soup.find("table", attrs={'id': 'AutoNumber2'})

        # the Points of Contact table follows the format of
        # first table row contains the field names with all
        # subsequent fields containing individuals

        # first we parse the first row and get all table headers
        fieldrow = pocinfotbl.find("tr").find_all("th")

        # now we build a list of the fields
        fields = [field.text.replace("\n", "") for field in fieldrow]

        # now we cycle thru the remaining rows
        # but first we create a soup object of table rows (skipping first)
        contactrows = pocinfotbl.find_all("tr")[1:]

        # list for pws pocs
        pwspocs = []

        for row in contactrows:
            # each field for our contacts is stored in a td cell
            allvals = row.find_all("td")

            # now we strip the html formatting from the values
            valstxt = [val.text for val in allvals]

            # we will cleanup the data some by stripping and replacing new lines
            valstxt = [val.lstrip().rstrip().replace("\n", "") for val in valstxt]

            # we build a dictionary for the poc
            pocs_for_pws = dict(zip(fields, valstxt))

            # for now we are not restricting to only 1 email address (removing \xa0)
            if '\xa0' in pocs_for_pws['Email']:
                pocs_for_pws['Email'] = pocs_for_pws['Email'][:pocs_for_pws['Email'].find('\xa0')]

            # now we add our pwsname to our dict
            pocs_for_pws['PWSName'] = pwsname

            # we append our dictionary to our list for the pws (only if 'AC' Contact)
            if pocs_for_pws['Type'] == 'AC':
                pwspocs.append(pocs_for_pws)

        pocdict.update({pwsid: pwspocs})


    # now we write our pws poc data to a new csv
    with open(pws_csv, 'w', newline='') as csvfile:

        # we need a list of our fields to pass to csv.DictWriter
        # this is a little more complicated since we are using nested dictionaries
        firstdict = list(pocdict.values())[0][0]

        # then we build a list of its keys
        keys = list(firstdict.keys())

        # add pwsid to beginning of fieldnames list
        keys.insert(0, 'PWSID')

        # let's move pwsname to the second position
        keys.insert(1, keys.pop(7))

        # now we write our csv
        writer = csv.DictWriter(csvfile, fieldnames=keys)

        writer.writeheader()

        # we cycle thru the nested dictionaries
        for pwsid, rows in pocdict.items():
            # each row contains a poc
            for poc in rows:
                # we have to add our pwsid to our poc
                poc.update({'PWSID': pwsid})
                # and we finally write our poc row
                writer.writerow(poc)


if __name__ == '__main__':
    main()