New Data Feed in wcds.py

[cmora1es]

Client Code: 2496.14

Data Source

Pull Daily Operation Monthly Reports from US Army Corps of Engineers Sacramento District Website - Water Control Data System: URL to be used = https://www.spk-wc.usace.army.mil/fcgi-bin/monthly.py?month=feb&year=2023&project=frm Where "month=", "year=" and "project=" are inputs to a function.

Sample Code:

def download_table(month, year, project):
    url = f"https://www.spk-wc.usace.army.mil/fcgi-bin/monthly.py?month={month}&year={year}&project={project}&textonly=true"
    response = requests.get(url, verify=False)
    lines = response.text.splitlines()
    rows = [line.split() for line in lines if line.strip()]
    return rows

def save_to_csv(data, file, month_num, year):
    for j, sublist in enumerate(data):
        if sublist[0] == 'Totals':
            break

    for i, sublist in enumerate(data[2:]):
        if sublist[0] == 'Date':
            name_list = sublist 
            break

    units=data[i+3]
    column_name = []
    for i, item in enumerate(name_list):
        if i > 0 and item.startswith('#'):
            column_name[-1] = ' '.join([column_name[-1], item])
        else:
            column_name.append(item)
    in_index = column_name.index('(in)')
    units.insert(0, '')
    units.insert(in_index, '(in)')

    if len(column_name)==len(units):
        column_list = [f'{column_name[i]}\n{units[i]}' for i in range(len(column_name))]
    else: 
        column_list = data[8]

    df = pd.DataFrame(data[9:j])
    df['datetime']= [f'{year}-{month_num}-{i}' for i in df[0]]
    df.set_index('datetime', inplace=True)
    df.columns = column_list

    table = df
    print(table)
    table.to_csv(file, index = True, header=column_list, mode='a')

def download_and_save(month_num, months, years, project, file_prefix):
    for year in years:
        for i in range(len(month_num)):
            data = download_table(months[i], year, project)
            file = f"{year}_{month_num[i]}_{file_prefix}.csv"
            save_to_csv(data, file, month_num[i], year)

def append_to_csv(input_file, output_file):
    with open(input_file, 'r') as in_file, open(output_file, 'a') as out_file:
        reader = csv.reader(in_file)
        writer = csv.writer(out_file)
        header = next(reader)
        if not os.path.isfile(output_file):
            writer.writerow(header)
        for row in reader:
            writer.writerow(row)

months = ['jan', 'feb', 'mar','apr','may','dec']
month_num = ['01','02','03','12']
years = range(2000,2004)
project = 'eng'
file_prefix = 'eng'
download_and_save(month_num, months, years, project, file_prefix)

output_file = 'eng_output_1990-2022.csv'

for year in years:
    for i in range(len(month_num)):
        input_file = f"{year}_{month_num[i]}_{file_prefix}.csv"
        append_to_csv(input_file, output_file)

Use Case

This URL will be an alternative source to pull data starting 1990 as opposed to starting 1995 when collecting from Corps and Section 7 Projects in California - Hourly/Daily Data and Plots

This feature will be added in ".../collect/collect/usace/wcds.py"

Breaking Changes

Unknown

Priority

Low

[narlesky]

@cmora1es here's my pseudo-code notes from our meeting:

def get_monthly_report_data_for_reservoir(reservoir, year, month):
    """
    description (omit the totals)

    Arguments:
        reservoir (str):
        year (int):
        month (??): as a number or as a string?
    Returns:
    """

    # use helper function to lookup the columns as long as it's reliable for the reservoir
    # for the whole period of record

    return {'data': df, # date-indexed dataframe
            'info': {'url': ,
                    'date_published':
                    'date_retrieved': dt.datetime.now(),
                    'raw': the plain text}}

def get_por_monthly_report_data_for_reservoir(reservoir, start, end):

    frames = []
    # list of months between start and end
    for month in list:
        frames.append(get_monthly_report_data_for_reservoir(reservoir, year, month)['data'])
    df = pd.concat(frames, axis=0)

    return {'data': df, 
            'info': {
                    'urls': [],
                    'dates_published': [],
                    'date_retrieved': dt.datetime.now(),
                    }}