Example1: Working with SdwisTable

We first initiate api

import sdwis_drink_water
# Due to the width limitation, I wrote some functions for outputting some results using an interactive scrollbar
# "print_url=True" here will cause subsequent queries to display the requested URL
table_api = sdwis_drink_water.SdwisTable(print_url=False)

---------------------------------------------------------------------------
ModuleNotFoundError                       Traceback (most recent call last)
Cell In[1], line 1
----> 1 import sdwis_drink_water
      2 # Due to the width limitation, I wrote some functions for outputting some results using an interactive scrollbar
      3 # "print_url=True" here will cause subsequent queries to display the requested URL
      4 table_api = sdwis_drink_water.SdwisTable(print_url=False)

ModuleNotFoundError: No module named 'sdwis_drink_water'

fetch all table names

“print_to_console=True” here causes all subsequent queries to be printed

table_names = table_api.get_all_table_names(print_to_console=True)

>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>
table_name
---------------------
ENFORCEMENT_ACTION
GEOGRAPHIC_AREA
LCR_SAMPLE_RESULT
LCR_SAMPLE
SERVICE_AREA
TREATMENT
WATER_SYSTEM
WATER_SYSTEM_FACILITY
VIOLATION
VIOLATION_ENF_ASSOC
>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>

fetch all table names with description (Crawl the latest information from the website)

table_names_and_descriptions = table_api.get_all_table_names_and_descriptions(print_to_console=False);
sdwis_drink_water.utils_for_jupyter_print.print_column_description(table_names_and_descriptions)

Fetching table descriptions from SDWIS Official Website: 100%|██████████| 10/10 [00:07<00:00,  1.39it/s]

fetch structure for specific table name

for table_name in table_names:
    print(f"======{table_name}======")
    columns = table_api.get_table_column_name_by_table_name(table_name=table_name, print_to_console=False)
    sdwis_drink_water.utils_for_jupyter_print.print_columns(columns)

======ENFORCEMENT_ACTION======

======GEOGRAPHIC_AREA======

======LCR_SAMPLE_RESULT======

======LCR_SAMPLE======

======SERVICE_AREA======

======TREATMENT======

======WATER_SYSTEM======

======WATER_SYSTEM_FACILITY======

======VIOLATION======

======VIOLATION_ENF_ASSOC======

fetch first data for specific table name

print(f"======{table_names[3]}======")
table_api.get_table_first_data_by_table_name(table_name=table_names[3], print_to_console=True)

======LCR_SAMPLE======
┌───────────┬─────────────────┬─────────────────────┬───────────────────────┬─────────────────────┬───────────────────────┬──────────────┐
│     pwsid │       sample_id │ sampling_end_date   │ sampling_start_date   │ reconciliation_id   │   primacy_agency_code │   epa_region │
├───────────┼─────────────────┼─────────────────────┼───────────────────────┼─────────────────────┼───────────────────────┼──────────────┤
│ 010106001 │ 010106001171002 │ 2019-12-31 00:00:00 │ 2017-01-01 00:00:00   │                     │                    01 │           01 │
└───────────┴─────────────────┴─────────────────────┴───────────────────────┴─────────────────────┴───────────────────────┴──────────────┘

<sdwis_drink_water.data_praser.ResultDataParser at 0x13b335eb0>

fetch first n for specific table name

print(f"======{table_names[3]}======")
table_api.get_table_first_n_data_by_table_name(table_name=table_names[3], n=5, print_to_console=True)

======LCR_SAMPLE======
┌───────────┬─────────────────┬─────────────────────┬───────────────────────┬─────────────────────┬───────────────────────┬──────────────┐
│     pwsid │       sample_id │ sampling_end_date   │ sampling_start_date   │ reconciliation_id   │   primacy_agency_code │   epa_region │
├───────────┼─────────────────┼─────────────────────┼───────────────────────┼─────────────────────┼───────────────────────┼──────────────┤
│ 010106001 │ 010106001171002 │ 2019-12-31 00:00:00 │ 2017-01-01 00:00:00   │                     │                    01 │           01 │
├───────────┼─────────────────┼─────────────────────┼───────────────────────┼─────────────────────┼───────────────────────┼──────────────┤
│ 010106001 │  01010600117136 │ 2013-12-31 00:00:00 │ 2011-01-01 00:00:00   │                     │                    01 │           01 │
├───────────┼─────────────────┼─────────────────────┼───────────────────────┼─────────────────────┼───────────────────────┼──────────────┤
│ 010106001 │  01010600117137 │ 2013-12-31 00:00:00 │ 2011-01-01 00:00:00   │                     │                    01 │           01 │
├───────────┼─────────────────┼─────────────────────┼───────────────────────┼─────────────────────┼───────────────────────┼──────────────┤
│ 010106001 │ 010106001171409 │ 2022-12-31 00:00:00 │ 2020-01-01 00:00:00   │                     │                    01 │           01 │
├───────────┼─────────────────┼─────────────────────┼───────────────────────┼─────────────────────┼───────────────────────┼──────────────┤
│ 010106001 │  01010600117711 │ 2016-12-31 00:00:00 │ 2014-01-01 00:00:00   │                     │                    01 │           01 │
└───────────┴─────────────────┴─────────────────────┴───────────────────────┴─────────────────────┴───────────────────────┴──────────────┘

<sdwis_drink_water.data_praser.ResultDataParser at 0x13b7812e0>