Bring in `Missouri_USACE_*_profiles` data

[padilla410]

US Army Corps of Engineers (USACE) reservoir profile data from 2009-2021. Data includes the following:

• Site
• Date
• Time
• Depth (m)
• Temp (deg C)
• DO (mg/L)
• DO sat (%)

Other parameters (not included in all data sets):

• secchi
• Sp conductivity (uS/cm)
• pH
• Turbidity (NTU)
• PCY (cell/mL)

For a given year, the data is organized by reservoir, with multiple locations for each reservoir. Lat/longs are provided in a separate excel file called Missouri_USACE_samplingLocations.xlsx. The format of the data is optimized for "in-workbook" data analysis and some workbooks contain figures.

Note - there is likely sampling location overlap between this data set and the following data sets:

• Bull Shoals (PR #218)
• Univ of MO (#269)
• Navico (#219)

[padilla410]

DRAFT

I am actively working on this issue right now. There are enough small issues with the data that I am going to track things here on a year by year basis to help with the final munge.

Data Quality Review

• Global issues

  • Site names in the data spreadsheets do not always match the site labeling provided in Missouri_USACE_samplingLocations.xlsx (e.g., PT24 in the sampling locations spreadsheet but PT-24 in the data spreadsheet). There is also inconsistent use of case.
  • Not all locations with data are listed in the location spreadsheet (e.g., PE_dam from 2010 doesn't have a location)
  • "Time" will be omitted - data sets contain a mix of time formats and typos (e.g., excel serial time, "1400", "14:00", "14:000")
  • Occasionally, locations are misnamed (e.g., "10" instead of "PT10"). These values were dropped because of the additional level of effort that would be required to repair this issue (it drops ~200 records out of >40,000)

• 2009

  • Has a different format than the later datasets. Site is always last, but isn't always labeled
  • Depth has inconsistent names ("Dep100" vs "Depth")

• 2010

  • Not all sheets contain raw data with location ("Sept", "apriljune", "PE_dam")
  • Not all sheets contain data (LB)
  • One sheet has random data in row 1 (PT)
  • Site is not always a labeled column or a sensible location (e.g., PE_dam) that matches the site list in Missouri_USACE_samplingLocations.xlsx
  • Site column name varies (e.g., "Site", "Sample ID", NA)
  • Site column location varies
  • Starting row varies (minor issue)

• 2011

  • Includes 3 sheets of inflows - these will be ignored
  • Inconsistent names for depth ("Dep25" and "Depth (m)")
  • Inconsistent names and column position for "site" ("Site Name", "Site #", "site", etc)

• 2012

  • Several empty, unlabeled sheets
  • Site column name and position varies (e.g., "Site #", "Site Name")

• 2013

  • "Sheet1" contains info but not relevant
  • Site column name and position varies (e.g., "Site #", "Site Name")

• 2014

  • "Sheet1" contains info but not relevant
  • Site column name and position varies (e.g., "Site #", "Site", case etc)

• 2015

  • Site column name varies (e.g., "Site #", "Sample ID", NA)
  • There is a small number of records where the "Date is in the first column instead of the second (n = 3). Leaving them out.

• 2016

  • Not all datasets contain a row of units (e.g., PT, ST, PO, ME, WI)
  • Not all datasets have labeled the "Site" column (HT) but site is always in the first column
  • Site column name varies (e.g., "Site #", "Site", NA)

• 2017

  • A very small subset of the data has missing dates (example sheet TC row 92 in Missouri_USACE_2017_profiles.xlsx). This seems like a small enough component of the data that I am going to ignore it (2/4398 records)

• 2018

  • one sheet (LB) has missing column names at the top. Needs modified wrangling

• 2019

  • No problems identified

• 2020

  • No problems identified

• 2021

  • No problems identified