def search_dir_only(pattern, filename):
    try:
        fn = os.path.join(os.getcwd(), 'file_search.config')
        last_dir = configsectionmap(fn, 'last path')
        dir_selected = fdd.get_directory(last_dir)
        if not dir_selected:
            return
        df = pd.DataFrame(find_dirs(dir_selected, pattern),
                          columns=['Directory'])
        df['Directory'] = '=HYPERLINK("' + df['Directory'] + '")'
        if df.shape[0] == 0:
            msgbox.show_message('Bummer',
                                'No directories found using that expression')
            return
        filename = os.path.splitext(filename)[0]
        # if using pandas to save to excel (requires openpyxl)
        # df.to_excel(filename + '.xlsx', index=False)
        # os.startfile(filename + '.xlsx')
        # use xlwings to save to excel
        new_excel_wb = New_Excel_WB()
        new_excel_wb.df_to_excel(df=df, path=filename + '.xlsx')
    except PermissionError:
        msgbox.show_error('Permission Error', filename + '.xlsx already open')
    except Exception as e:
        msgbox.show_error('Error', e)
Exemple #2
0
def pws_detail(url_detail,
               url_summary,
               detail='Detail',
               buyers='Buyers',
               purchases='Purchases',
               wells='Wells',
               sources='Sources'):
    """
    Called from Excel when user clicks hyperlink for a PWS
    :param url_detail:
    :param url_summary:
    :param detail:
    :param buyers:
    :param purchases:
    :param wells
    :param sources:
    :return:
    """
    get_buyers_and_purchases = True
    sht_detail = xw.Book.caller().sheets[detail]
    sht_sources = xw.Book.caller().sheets[sources]
    sht_wells = xw.Book.caller().sheets[wells]
    sht_buyers = xw.Book.caller().sheets[buyers]
    sht_purchases = xw.Book.caller().sheets[purchases]
    sht_detail.book.app.screen_updating = False
    pwsd = PWS_Detail()
    try:
        pwsd.get_detail(url_detail, url_summary, buyers=True, purchases=True)
    except Exception as e:
        msgbox.show_error('Python Error in get_detail', e)
    try:
        lrow_detail = sht_detail.range('A1').end('down').last_cell.row
        lrow_sources = sht_sources.range('A1').end('down').last_cell.row
        lrow_wells = sht_wells.range('A1').end('down').last_cell.row
        lrow_buyers = sht_buyers.range('A1').end('down').last_cell.row
        lrow_purchases = sht_purchases.range('A1').end('down').last_cell.row
        lrow_detail = lrow_detail if lrow_detail < 2**20 else 0
        lrow_sources = lrow_sources if lrow_sources < 2**20 else 0
        lrow_wells = lrow_wells if lrow_wells < 2**20 else 0
        lrow_buyers = lrow_buyers if lrow_buyers < 2**20 else 0
        lrow_purchases = lrow_purchases if lrow_purchases < 2**20 else 0

        sht_detail.range(lrow_detail + 1, 1).options(index=False, header=not lrow_detail).value = \
            pwsd.get_basic_detail()
        sht_sources.range(lrow_sources + 1, 1).options(index=False, header=not lrow_sources).value = \
            pwsd.get_sources()
        sht_wells.range(lrow_wells + 1, 1).options(index=False, header=not lrow_wells).value = \
            pwsd.get_wells()
        if get_buyers_and_purchases:
            sht_buyers.range(lrow_buyers + 1, 1).options(index=False, header=not lrow_buyers).value = \
                pwsd.get_buyers()
            sht_purchases.range(lrow_purchases + 1, 1).options(index=False, header=not lrow_purchases).value = \
                pwsd.get_purchases()
    except Exception as e:
        msgbox.show_error('Python Error', e)
    sht_detail.book.app.screen_updating = True
def search_dir_topdown(pattern, filename, dt1=None, dt2=None):
    try:
        fn = os.path.join(os.getcwd(), 'file_search.config')
        last_dir = configsectionmap(fn, 'last path')
        # create dataframe from generator, tweak it, write to excel
        dir_selected = fdd.get_directory(last_dir)
        # be sure directory is not None type, and that it exists before setting last path
        if dir_selected and os.path.isdir(dir_selected):
            update_setting(fn, 'last path', 'last', dir_selected)
        if not dir_selected:
            return
        df = pd.DataFrame(find_files(dir_selected, pattern, dt1, dt2),
                          columns=[
                              'file', 'directory', 'filename', 'extension',
                              'file_size', 'lastmod'
                          ])
        df['directory'] = '=HYPERLINK("' + df['directory'] + '")'
        df['filename'] = '=HYPERLINK("' + df['file'] + '", "' + df[
            'filename'] + '")'
        df = df[['directory', 'filename', 'extension', 'file_size', 'lastmod']]
        files_searched_header = 'files searched: {:,.0f}'.format(
            num_files_searched)
        df.insert(5, files_searched_header, '')
        df.insert(6, pattern, '')
        if df.shape[0] == 0:
            msgbox.show_message('Bummer',
                                'No files found using that expression')
            return
        filename = os.path.splitext(filename)[0]

        # if using pandas to save to excel:
        # df.to_excel(filename + '.xlsx', index=False)
        # os.startfile(filename + '.xlsx')

        # if using xlwings to save to excel
        try:
            new_excel_wb = New_Excel_WB()
            new_excel_wb.df_to_excel(df=df, path=filename + '.xlsx')
        except:
            # msgbox.show_message('debug', str(len(df.index)) + ': ' + str(df.shape))
            # msgbox.show_error('Error', 'Error creating Excel file')
            new_excel_wb.close()
            msgbox.show_message(
                'Info',
                'There are too many rows to write to Excel.\nSwitching to csv.'
            )
            df.to_csv(filename + '.csv', index=False)
            os.startfile(filename + '.csv')
    except PermissionError:
        msgbox.show_error('Permission Error', filename + '.xlsx already open')
    except Exception as e:
        msgbox.show_error('Error', e)
Exemple #4
0
    def okclick(self):
        self.county = self.entered_county.get()
        print(self.county)
        self.buyers = self.chk_val_buyers.get()
        self.purchases = self.chk_val_purchases.get()

        # reset dataframes and dictionaries
        self.df = pd.DataFrame()
        self.df_buyer = pd.DataFrame()
        self.df_source = pd.DataFrame()
        self.df_purchase = pd.DataFrame()
        self.pwsDict = {}
        self.buyerDict = {}
        self.sourceDict = {}
        self.purchaseDict = {}

        # create a Pandas Excel writer using XlsxWriter as the engine.
        xlsfile = os.path.join(os.getcwd(), self.county + '.xlsx')
        writer = pd.ExcelWriter(xlsfile, engine='xlsxwriter')

        # get the list of counties and the pws detail for each
        #     get a county pws object
        county_pws = CountyPWS()
        #     get the urls for the selected county
        #     can be sliced --> county_pws.get_url(self.county.upper())[0:3]
        pws_list = county_pws.get_urls(self.county.upper())
        max_to_process = len(pws_list)
        self.num_systems = len(pws_list)

        for i, url in enumerate(pws_list[:max_to_process]):
            print(
                str(int(round((i + 1) / max_to_process * 100, 0))) + '% : ' +
                url)
            try:
                self.pws_detail(url)
            except Exception as e:
                msgbox.show_error('Error', 'Error processing ' + url)

        # convert the dataframe to an XlsxWriter Excel object.
        self.df.to_excel(writer, sheet_name='Detail')
        self.df_source.to_excel(writer, sheet_name='Sources')

        if self.buyers:
            self.df_buyer.to_excel(writer, sheet_name='Buyers')
        if self.purchases:
            self.df_purchase.to_excel(writer, sheet_name='Purchases')

        # close the Pandas Excel writer and output the Excel file.
        writer.save()
        # open the file
        file = xlsfile
        os.startfile(file)
def get_samples():
    try:
        # sht = xw.Book.caller().sheets[0]
        sht = xw.Book.caller().sheets.active
        sht.range('D1').expand().clear_contents()
        url = sht.range('B4').value
        if not 'AnalyteListByCode' in url:
            raise ValueError('url must represent Analyte List by Code')
        headers = {
            'user-agent':
            'Mozilla/5.0 (X11; Linux x86_64) AppleWebKit/537.36 (KHTML, like Gecko) '
            'Chrome/51.0.2704.103 Safari/537.36'
        }
        r = requests.get(url=url, headers=headers)
        doc = lxml.html.fromstring(r.content)
        tables = doc.findall('.//table')
        # get name of PWS in 4th table
        info_table = tables[3]
        tr_info = info_table.findall('.//tr')
        td_info = [_unpack(tr, kind='p') for tr in tr_info[2]]
        sys_name = td_info[1]
        sysnum = sht.range('A3').value
        sht.range('B8').value = sys_name[0] + ' (' + sysnum + ')'
        # links are in the 5th table
        analyte_table = tables[4]
        # each analyte's info is in a separate tr
        tr_tags = analyte_table.findall('.//tr')
        a_tags = [tr.findall('.//a') for tr in tr_tags[2:]]
        # use a list comprehension to get all the url's
        base_url = 'https://dww2.tceq.texas.gov/DWW/JSP/'
        urls = [
            base_url + re.sub(r'\s', '', a_tag[0].get('href'))
            for a_tag in a_tags
        ]
        # for each tr, get all the td's
        td_vals = [_unpack(tr) for tr in tr_tags[2:]]
        # get the table values and create dataframe
        analyte_codes = [el[0] for el in td_vals]
        analyte_names = [el[1] for el in td_vals]
        analyte_type = [el[2] for el in td_vals]
        analyte_num_results = [el[3] for el in td_vals]
        df_analyte = pd.DataFrame({
            'URL': urls,
            'Code': analyte_codes,
            'Name': analyte_names,
            'Type': analyte_type,
            'Number Results': analyte_num_results
        })
        # get the list of analytes to filter the dataframe
        filter_list = sht.range('B10').options(ndim=1).expand('down').value
        if type(filter_list) is str:
            filter_list = [filter_list]
        if filter_list is None:
            raise TypeError('There is no analyte list')
        df_scrape = df_analyte[df_analyte['Name'].isin(filter_list)]
        if df_scrape.empty:
            raise ValueError(
                'None of the analytes in the list exist for this PWS')
        # for each url, get the sampling data and build a pandas dataframe
        df_sampling = pd.DataFrame()
        columns = [
            'Code', 'Name', 'Facility', 'Sample Point',
            'Sample Collection Date', 'TCEQ Sample ID', 'Laboratory Sample ID',
            'Method', 'Less Than Ind.', 'Level Type', 'Reporting Level',
            'Concentration', 'Current MCL'
        ]
        for index, row in df_scrape.iterrows():
            r = requests.get(url=row['URL'], headers=headers)
            doc = lxml.html.fromstring(r.content)
            tables = doc.findall('.//table')
            # sampling data in the 5th table
            sampling_table = tables[4]
            # get the tr's: headings in the 2nd tr, data starts in 3rd tr
            tr_tags = sampling_table.findall('.//tr')
            # get the headers: html is inconsistent - use xpath function to get all nodes within the th
            # headers = [_unpack2(tr, xpath_arg='node()') for tr in tr_tags[1]]
            # headers = [el[0] for el in headers]
            # get the values
            td_vals = [_unpack(tr) for tr in tr_tags[2:]]
            # create a sampling dataframe
            for i, item in enumerate(td_vals):
                zipped = zip(columns, item)
                dict_item = {k: v for k, v in zipped}
                df_sampling = df_sampling.append(dict_item, ignore_index=True)
        df_sampling['Sample Collection Date'] = pd.to_datetime(
            df_sampling['Sample Collection Date'])
        # put columns in correct order
        df_sampling = df_sampling[columns]
        if not df_sampling.empty:
            sht.range('D1').options(index=False).value = df_sampling
        else:
            msgbox.show_message('Message', 'No results')
    except Exception as e:
        msgbox.show_error('Error', e)
Exemple #6
0
    def get_detail(self, url_detail, url_summary, buyers=True, purchases=True):
        headers = {'user-agent': 'Mozilla/5.0 (X11; Linux x86_64) AppleWebKit/537.36 (KHTML, like Gecko) '
                                 'Chrome/51.0.2704.103 Safari/537.36'}
        r = requests.get(url=url_detail, headers=headers)
        doc = lxml.html.fromstring(r.content)
        tables = doc.findall('.//table')

        # sys. no., name, pop in 4th table
        systable = tables[3]
        rows = systable.findall('.//tr')
        sysinfo = self._unpack(rows[1])
        sysinfo = ["".join(x.split()) for x in sysinfo]
        self.pwsDict['Sys Num'] = sysinfo[1].strip()
        self.pwsDict['Sys Type'] = sysinfo[3].strip()

        # name is in the 3rd tr
        sysinfo = self._unpack(rows[2])
        sysinfo = [re.sub(r'(\s+|&nbsp)', ' ', val) for val in sysinfo]
        self.pwsDict['Sys Name'] = sysinfo[1].strip()
        self.pwsDict['Primary Source Type'] = sysinfo[3].strip()

        # pop is in the 6th tr
        sysinfo = self._unpack(rows[5])
        sysinfo = ["".join(x.split()) for x in sysinfo]
        self.pwsDict['Population'] = sysinfo[1].strip()

        # contact in the 5th table
        systable = tables[4]
        rows = systable.findall('.//tr')
        if len(rows) >= 3:
            sysinfo = self._unpack(rows[2])
            sysinfo = [re.sub(r'(\s+|&nbsp)', ' ', val) for val in sysinfo]
            self.pwsDict['Contact'] = sysinfo[1].strip()
            if len(sysinfo) >= 5:
                self.pwsDict['Business Phone'] = sysinfo[4].strip()
            if len(sysinfo) >= 7:
                self.pwsDict['Mobile Phone'] = sysinfo[6].strip()

        # the number of buyers and sellers dictate how many tables there are, so certain tables cannot always be
        #     found in the same location. have to look at the th values to find them.
        for table in tables:
            rows = table.findall('.//tr')
            # if rows has list elements, its boolean is True; if an empty list, False
            if rows:
                header = self._unpack(rows[0], kind='th')
                if header is not None:
                    if header[0] == 'Buyers of Water':
                        self.buyertable = table
                    elif header[0] == 'Water Purchases':
                        self.purchasetable = table
                    elif header[0] == 'Sources of Water':
                        self.sourcetable = table
                    elif header[0] == 'WS Flow Rates':
                        self.flowratetable = table
                    elif header[0] == 'WS Measures':
                        self.measurestable = table
        # WS Flow Rates table
        if self.flowratetable is not None:
            rows = self.flowratetable.findall('.//tr')
            if len(rows) >= 3:
                flowinfo = self._unpack(rows[2])
                flowinfo = [re.sub(r'(\s+|&nbsp)', ' ', val) for val in flowinfo]
                self.pwsDict['Max Daily Demand'] = flowinfo[1].strip() + ' (' + flowinfo[2].strip() + ')'

            if len(rows) >= 4:
                flowinfo = self._unpack(rows[3])
                flowinfo = [re.sub(r'(\s+|&nbsp)', ' ', val) for val in flowinfo]
                self.pwsDict['Provided Prod. Capacity'] = flowinfo[1].strip() + ' (' + flowinfo[2].strip() + ')'

            if len(rows) >= 5:
                flowinfo = self._unpack(rows[4])
                flowinfo = [re.sub(r'(\s+|&nbsp)', ' ', val) for val in flowinfo]
                self.pwsDict['Provided Service Pump Capacity'] = flowinfo[1].strip() + ' (' + flowinfo[2].strip() + ')'

            if len(rows) >= 6:
                flowinfo = self._unpack(rows[5])
                flowinfo = [re.sub(r'(\s+|&nbsp)', ' ', val) for val in flowinfo]
                self.pwsDict['Avg. Daily Usage'] = flowinfo[1].strip() + ' (' + flowinfo[2].strip() + ')'
        # WS Measures table
        if self.measurestable is not None:
            rows = self.measurestable.findall('.//tr')
            if len(rows) >= 3:
                flowinfo = self._unpack(rows[2])
                flowinfo = [re.sub(r'(\s+|&nbsp)', ' ', val) for val in flowinfo]
                self.pwsDict['Total Storage Cap.'] = flowinfo[1].strip() + ' (' + flowinfo[2].strip() + ')'

            if len(rows) >= 4:
                flowinfo = self._unpack(rows[3])
                flowinfo = [re.sub(r'(\s+|&nbsp)', ' ', val) for val in flowinfo]
                self.pwsDict['Total Pressure Tank Cap.'] = flowinfo[1].strip() + ' (' + flowinfo[2].strip() + ')'

            if len(rows) >= 5:
                flowinfo = self._unpack(rows[4])
                flowinfo = [re.sub(r'(\s+|&nbsp)', ' ', val) for val in flowinfo]
                self.pwsDict['Elevated Storage Cap.'] = flowinfo[1].strip() + ' (' + flowinfo[2].strip() + ')'

        # add dictionary to dataframe
        self.df = self.df.append(self.pwsDict, ignore_index=True)

        # put columns in correct order
        self.df = self.df[['Sys Num', 'Sys Name', 'Sys Type', 'Primary Source Type', 'Population',
                           'Contact', 'Business Phone', 'Mobile Phone', 'Max Daily Demand', 'Provided Prod. Capacity',
                           'Provided Service Pump Capacity', 'Avg. Daily Usage', 'Total Storage Cap.',
                           'Total Pressure Tank Cap.', 'Elevated Storage Cap.']]

        ###################
        # get the sources #
        ###################
        self.sourceDict['Sys Name'] = self.pwsDict['Sys Name']
        self.sourceDict['Sys Num'] = self.pwsDict['Sys Num']
        if self.sourcetable is not None:
            rows = self.sourcetable.findall('.//tr')
            # get sources as a list of lists
            if len(rows) >= 3:
                sources = [self._unpack(row) for row in rows[2:]]
                for xlr, row in enumerate(sources):
                    for xlc, val in enumerate(row):
                        if xlc == 0:
                            self.sourceDict['Source Name'] = val.strip()
                        if xlc == 1:
                            self.sourceDict['Type'] = val.strip()
                        if xlc == 2:
                            self.sourceDict['Activity'] = val.strip()
                        if xlc == 3:
                            self.sourceDict['Availability'] = val.strip()
                    self.df_source = self.df_source.append(self.sourceDict, ignore_index=True)
            else:
                # there are no sources
                self.sourceDict['Source Name'] = 'NO SOURCES LISTED'
                self.df_source = self.df_source.append(self.sourceDict, ignore_index=True)
        else:
            self.sourceDict['Source Name'] = 'SOURCE TABLE NOT FOUND'
            self.df_source = self.df_source.append(self.sourceDict, ignore_index=True)
        self.df_source = self.df_source[['Sys Name', 'Sys Num', 'Source Name', 'Type', 'Activity', 'Availability']]

        ###################
        # get the buyers  #
        ###################
        try:
            self.buyerDict['Sys Name'] = self.pwsDict['Sys Name']
            self.buyerDict['Sys Num'] = self.pwsDict['Sys Num']
            if self.buyertable is not None and buyers:
                rows = self.buyertable.findall('.//tr')
                if rows and len(rows) >= 3:
                    thebuyers = [self._unpack(row) for row in rows[2:]]
                    # buyers contains who is buying, their population, and their status...separated by '/'
                    #     remove the whitespace
                    if thebuyers:
                        thebuyers = [re.sub(r'(\s+|&nbsp)', ' ', val) for vals in thebuyers for val in vals]
                        # split in '/', creating a list of lists
                        buyers_split = [x.split('/') for x in thebuyers]
                        if buyers_split:
                            for xlr, row in enumerate(buyers_split):
                                if row:
                                    for xlc, val in enumerate(row):
                                        if xlc == 0:
                                            self.buyerDict['Buyer'] = val.strip()
                                        if xlc == 1:
                                            self.buyerDict['Buyer Pop'] = val.strip()
                                        if xlc == 2:
                                            self.buyerDict['Buyer Status'] = val.strip()
                                    self.df_buyer = self.df_buyer.append(self.buyerDict, ignore_index=True)
                                    if xlr > 25000:
                                        self.buyerDict['Buyer'] = 'BUYER DATA TRUNCATED DUE TO LENGTH. ' \
                                                                  'SEE TCEQ FOR MORE INFO.'
                                        self.buyerDict['Buyer Pop'] = ''
                                        self.buyerDict['Buyer Status'] = ''
                                        self.df_buyer = self.df_buyer.append(self.buyerDict, ignore_index=True)
                                        break
            else:
                self.buyerDict['Buyer'] = 'BUYER TABLE NOT FOUND'
                self.df_buyer = self.df_buyer.append(self.buyerDict, ignore_index=True)
        except Exception as e:
            msgbox.show_error('Python Error', str(e))
            self.buyerDict['Buyer'] = 'PROBLEM READING BUYER TABLE IN HTML.'
            self.df_buyer = self.df_buyer.append(self.buyerDict, ignore_index=True)

        self.df_buyer = self.df_buyer[['Sys Name', 'Sys Num', 'Buyer', 'Buyer Pop', 'Buyer Status']]
        self.df_buyer = self.df_buyer.drop_duplicates()

        ######################
        # get the purchases  #
        ######################
        try:
            self.purchaseDict['Sys Name'] = self.pwsDict['Sys Name']
            self.purchaseDict['Sys Num'] = self.pwsDict['Sys Num']
            if self.purchasetable is not None and purchases:
                rows = self.purchasetable.findall('.//tr')
                if len(rows) >= 3:
                    purchases = [self._unpack(row) for row in rows[2:]]
                    # remove the whitespace
                    purchases = [re.sub(r'(\s+|&nbsp)', ' ', val) for vals in purchases for val in vals]
                    for xlr, row in enumerate(purchases):
                        self.purchaseDict['Purchase Info'] = row.strip()
                        self.df_purchase = self.df_purchase.append(self.purchaseDict, ignore_index=True)
                        if xlr > 2000:
                            self.purchaseDict[
                                'Purchase Info'] = 'PURCHASE DATA TRUNCATED DUE TO LENGTH: SEE TCEQ FOR MORE INFO'
                            self.df_purchase = self.df_purchase.append(self.purchaseDict, ignore_index=True)
                            break
            else:
                self.purchaseDict['Purchase Info'] = 'PURCHASE TABLE NOT FOUND'
                self.df_purchase = self.df_purchase.append(self.purchaseDict, ignore_index=True)
        except Exception:
            self.purchaseDict['Purchase Info'] = 'PURCHASE TABLE NOT FOUND'
            self.df_purchase = self.df_purchase.append(self.purchaseDict, ignore_index=True)

        self.df_purchase = self.df_purchase[['Sys Name', 'Sys Num', 'Purchase Info']]
        self.df_purchase = self.df_purchase.drop_duplicates()

        ############################################
        # get the well details from summary sheet  #
        ############################################
        try:
            headers = {'user-agent': 'Mozilla/5.0 (X11; Linux x86_64) AppleWebKit/537.36 (KHTML, like Gecko) '
                                     'Chrome/51.0.2704.103 Safari/537.36'}
            r = requests.get(url=url_summary, headers=headers)
            doc = lxml.html.fromstring(r.content)
            tables = doc.findall('.//table')
            for table in tables:
                rows = table.findall('.//tr')
                # if rows has list elements, its boolean is True; if an empty list, False
                if rows:
                    stuff = [self._unpack2(row) for row in rows]
                    if stuff and len(stuff) >= 3:
                        if stuff[0] and stuff[0][0] == '(Active Sources)':
                            for i, item in enumerate(stuff):
                                if isinstance(item, list):
                                    if item[0] == 'SourceNumber':
                                        try:
                                            headings = [re.sub(r'(\s+|&nbsp)', ' ', item) for item in stuff[i]]
                                            values = [re.sub(r'(\s+|&nbsp)', ' ', item).replace(' GPM', '')
                                                      for item in stuff[i + 1]]
                                            if 'Depth' not in headings:
                                                headings.append('Depth')
                                                values.append('')
                                            if 'Tested GPM' not in headings:
                                                headings.append('Tested GPM')
                                                values.append('')
                                            if 'Rated GPM' not in headings:
                                                headings.append('Rated GPM')
                                                values.append('')
                                        except IndexError as e:
                                            pass
                                        dict_well = dict(zip(headings, values))
                                        try:
                                            dict_well['Latitude'] = re.sub(r'(\s+|&nbsp)', '', stuff[i + 7][0])
                                            dict_well['Longitude'] = re.sub(r'(\s+|&nbsp)', '', stuff[i + 7][1])
                                        except IndexError as e:
                                            dict_well['Latitude'] = ''
                                            dict_well['Longitude'] = ''
                                        try:
                                            dict_well['Drill Date'] = re.sub(r'(\s+|&nbsp)', '', stuff[i + 5][0])
                                            dict_well['Source Summary'] = re.sub(r'(\s+|&nbsp)*$', '', stuff[i + 5][1])
                                        except IndexError as e:
                                            dict_well['Drill Date'] = '-'
                                            dict_well['Source Summary'] = '-'
                                        dict_well['Sys Name'] = self.pwsDict['Sys Name']
                                        dict_well['Sys Num'] = self.pwsDict['Sys Num']
                                        self.df_well = self.df_well.append(dict_well, ignore_index=True)
            if self.df_well.empty:
                dict_well = {'Sys Name': self.pwsDict['Sys Name'], 'Sys Num': self.pwsDict['Sys Num'],
                             'SourceNumber': 'No Active Sources', 'Source Name (Activity Status)': '-',
                             'OperationalStatus': '-', 'SourceType': '-', 'Depth': '-', 'Tested GPM': '-',
                             'Rated GPM': '-', 'Drill Date': '-', 'Source Summary': '-',
                             'Latitude': '-', 'Longitude': '-'}
                self.df_well = self.df_well.append(dict_well, ignore_index=True)
            self.df_well = self.df_well[['Sys Name', 'Sys Num', 'SourceNumber', 'Source Name (Activity Status)',
                                         'OperationalStatus', 'SourceType', 'Depth', 'Tested GPM', 'Rated GPM',
                                         'Drill Date', 'Source Summary', 'Latitude', 'Longitude']]
            self.df_well = self.df_well.drop_duplicates()
        except Exception as e:
            msgbox.show_error('Error in well summary', e)