Homepage | Boston University



Tools for Working With SMART Water Quality Data Julia Masterman, Fall 2017Creating the Pivot Table:‘WPP WQD 2005 to 2014 SMART WSheds’ Excel WorkbookCopied ‘Lab Data Main’ sheet to a new Excel workbookSaved Document as ‘Measurement_Count’ Excel WorkbookCreated copy of ‘Lab Data Main’ sheet in ‘Measurement_Count’ workbook entitled ‘Working_Lab Data Main”Deleted columns to clarify the data in ‘Working_lab Data Main’ until the following columns remained: ID, Datayear, Watershed, Waterbody, UNIQUE_ID, MLTYP_NAME, QAQCType, StartDate, StartTime, FLOWSTAT, DWN_Name, DWM_Units, ResVal, nResult. Created a pivot table. In the pivot table builder:‘DWM_Names’ in ‘Filters’‘Datayear’ in ‘Columns’‘Watershed’, ‘Waterbody’, and ‘UNIQUE_ID’ in ‘Rows’‘Count of ResVal’ in ‘Values’The resulting pivot table allows the user to choose a measurement type (for example Ammonia-N or Chloride), and communicates how many measurements (the count) were taken per year in each watershed. Each watershed can then be expanded to show how many samples were taken at each water body, and from there further expanded to illustrate how many samples were taken at each site per year. This tool not only allows researchers more easily sift through data to find sites that have the most complete data set useful for creating trends, but it also clearly highlights gaps in data collection. Firstly, it shows gaps in specific measurements (for example the grand total of measurement count for a specific measurement type may very). Secondly it illustrates what bodies of water or watersheds lack measurement or consistent measurement. This information also allows for community members to more easily identify the information that they might find the most relevant to their township. This first step makes sifting through the datasheets far less daunting by helping individuals identify how to best filter the data relevant to their studies. Additionally, by seeing what is omitted, watershed associations and other water advocates can see the areas where they can most effectively assist or best request assistance from governmental agencies. By identifying the specific sampling sites that have a consistent measurement, it makes filtering the data in ‘Lab Data Main’ simpler so that graphs of trends over time for various measurement types can be produced. Identify the sample site you are interested in looking at using the Pivot tableGo to Lab Data Main pageUse ‘Filter’ tool on the “STAID_YR” Column to filter data such that only data from the station in question is shownDo the same in “DWN_Name” Column to filter data such that only data for the specific measurement type is shownCopy the following columns into a new sheet: Datayear, UNIQUE_ID, DWM_NAME, DWM_Units, ResVal, and StartDate.Use the sort toll on the column “StartDate” in the new page to verify that the data is listed in chronological orderInsert a scatterplot, and select data such that “ResVal” is on the y axis and “StartDate” is on the x axis. Format the chart as desired, add trend lines and more. ................
................

In order to avoid copyright disputes, this page is only a partial summary.

Google Online Preview   Download