EPA/600/B-15/274
Quantitative Microbial Risk Assessment Tutorial
Publishing a Microbial Density Time Series as a TXT File
Gene Whelan
Kurt Wolfe
Rajbir Parmar
Michael Galvin
Marirosa Molina
U.S. Environmental Protection Agency
Office of Research and Development
National Exposure Research Laboratory
Athens, GA
Mark Gray
Paul Duda
AQUA TERRA Consultants, a Division of RESPEC, INC.
Decatur, GA
10/10/15
-------
Summary
A SARA Timeseries Utility supports analysis and management of time-varying environmental data
including listing, graphing, computing statistics, computing meteorological data and saving in a WDM or
text file. File formats supported include WDM, HSPF Binary (.hbn), USGS RDB, and Text (txt) files (using
the same import scripts as WDMUtil and BASINS). WDMUtil users will find that most of its functionality
is included in this utility. In particular, the SARA Timeseries Utility is being used to publish a time series
of microbial densities that can be viewed or consumed by other components, such as the Microbial Risk
Assessment-Interface Tool (MRA-IT), which is a MathCad-based, integrated software tool based on the
pathogen of interest, exposure, intake, and dose. MRA-IT consumes txt files using a Unicode
Transformation Format using 8-bit code units (UTF-8), which is a character encoding capable of encoding
all possible characters in Unicode using 8-bit code units. The SARA Timeseries Utility tool produces UTF-
16, which is a character encoding capable of encoding all possible characters in Unicode using 16-bit
code units. The objective of this effort was to modify the SARA Timeseries Utility to produce txt files in a
UTF-8 format, which contributes to a seamless communication with components such as MRA-IT. This
tutorial reviews some of the screens, icons, and basic functions of the SARA Timeseries Utility tool,
which allows users to publish Hydrological Simulation Program - FORTRAN (HSPF) data as a UTF-8 txt
file.
-------
Publishing a Microbial Density Time Series as a TXT File
PURPOSE
Publish a microbial density time series as a Unicode Transformation Format using 8-bit code units (UTF-
8) file.
OBJECTIVE
The objective is to publish a time series of microbial densities that can be viewed or consumed by other
components, such as the Microbial Risk Assessment-Interface Tool (MRA-IT), which is a MathCad-based,
integrated software tool based on the pathogen of interest, exposure, intake, and dose (Whelan et al.,
2014; Seller et al., 2008, 2004). A C++ version of MRA-IT also exists, called the Irrigation and Recreation
Risk Assessment (IRRA) module (Pelton, 2015).
DEMONSTRATION
The SARA Timeseries Utility supports analysis and management of time-varying environmental data
including listing, graphing, computing statistics, computing meteorological data, and saving in a WDM or
text file (Aqua Terra, 2011). File formats supported include: WDM, HSPF Binary (.hbn), USGS RDB, and
Text files (using the same import scripts as WDMUtil and BASINS). WDMUtil users will find that most of
its functionality is included in this new utility. This tutorial reviews some of the screens, icons, and basic
functions of the SARA Timeseries Utility tool (Aqua Terra, 2011), which allows users to publish HSPF data
as a txt file.
SOFTWARE ACCESS, RETRIEVAL, AND DOWNLOAD
Instructions for access, retrieval, and download of the SARA Timeseries Utility software product are
provided under the heading, titled SARA Timeseries Utility, in Whelan et al. (2015), Installation of
Software for Watershed Modeling in Support of QMRA. When installed, a shortcut icon should now be
on your computer screen for SARA Timeseries Utility:
(Kfflfcy
-------
TUTORIAL: USING THE SARA TIMESERIES TOOL TO SAVE DATA FROM AN HSPF MODEL AS A TEXT FILE
The SARA Timeseries Utility tool is an independent software tool that can read watershed WDM files
created by HSPF and convert WDM binary information to provide multiple types of plots including txt
files. Typically, a watershed WDM file is created when the user executes HSPF, and the file exists within
the working folder (e.g., \HSPF\). For example, if the user's working folder is titled
"TestA", as in the example below, then the watershed WDM (e.g., 040301010406.WDM) would be
located in the HSPF folder:
C:\Users\gwhelan\iemTechnologies\SDMProjectBuilder_100214\TestA\HSPF\
« Users ^ gwhelan > iemTechnologies > SDMProjectBuilder_10Q214 * TestA > HSPF
SDMProjectBuilder_100214
bin
data
TestA
HSPF
Ji nuc!2
met
NHDPIus
NLCD
pcs
TestB
TestC
Date modified Type
51 04030101Q406.wdm
10/6/2014 6:08 PM WDM File
1,400 KB |
10/6/2014 6:04 PM WDM File
6,920 KB
Filename: 040301010406,wdm
|WDMFiles(*.wdm)
Note that there may be multiple WDM files, and the correct one has a watershed ID designation (e.g.,
040301010406) as illustrated above.
For simplicity, a WDM file was created a priori (i.e., 040301010406.WDM), and it accompanied the
software download described in Whelan et al. (2015): "O40301010406.wdm". For the sake of this
tutorial, it is assumed that the WDM is located in
C:\Users\gwhelan\iemTechnologies\SDMProjectBuilder_100214\TestA\HSPF\
although in actuality, this particular file is located in the folder designated by the user during software
installation.
1. Run SARA Timeseries Utility, using the icon from the desktop, or double click the executable, titled
TimeseriesUtility_2015-08-13.exe, located in the folder where the user saved the file, when the
software was originally downloaded. See the example below, which is consistent with the
information contained in the software download tutorial (Whelan et al., 2015).
-------
> Computer > OSDisk (CO > Users ^ gwhelan > TimeSeriesUtility > SARA_Times_Series_Utility
File Edit View lools Help
Organize ^ Include in library T Share with T
Burn
f Links
MaxStat Lite
MicrobialPropertyDB
MicrobialPropertyDE_Original
Jl My Music
: My Pictures
• My Videos
SametimeTranscripts
U Saved Games
ft Searches
Temp
TimeSeriesUtitrty
SARA_Times_Series_Utility
- SARA_Time5_Series_Utility.zip
USEPA
t vkfi r- rj;j._.
£ 04Q3010104G6.wdm
•& SARA_Timeseries_Utitity_2015-08-13.exe
Date modified Type
10/6/20146:08 PM WDM File
S/13/2015 11:31 AM Application
1,400 K8
2,768 KB
iu 3 e • ^
Pame:
e
Name
WinZip - SARA_Times_Series_Utility.2ip
Edit Share Backup Tools
SARA_Timcs_Series_U«lity | \.-
-jwhelanMimeSeriesUtility Unz'P
Unzip
Settings View Help
VI.*
Dropbox Google SkyDrive S
Drive
Unzip to Cloud
LELUJ-^HJ
Upgrade •**'
© (*Allfites
earch , • , Selected Files
Files
Type Modified Size
||p'SRATime£eries_Utilitrajl5-03-13^exe Application 3:13^2015 11-31AM j^ja''
SOWSOlOlOWe.wdm WDM File 10/6/2014 6i08 PM 1,433,600
Selected 0 files, 0 bytes Total 2 files, 4,168KB
|
eo
The following screen appears:
••', SARA Timeseries Utility
Open File
Manage Files No files are open
Select Timeseries No Timeseries are selected
View
Save
Compute
List
Save As Text
Meteorologic
Graph
Tree
Save As Data
Import Text to WDM
Math
SAM ANTONIO
RIVER ALITHORTTY
About
Help
2. Choose the'OpenFile'
SARA Timeseries Utility
View
No files are open
No Timeseries are selected
Save Compute
SAN ANTONIO
RIVER AUTHORITY
List
Save As Text
Graph
Tree
Save As Data
Import Text to WDM
Meteorologic
Math
About
Help
-------
3. Select the 'WDM Time Series'; choose 'Ok'.
; Select type of file to open
••• Basins Observed Water Quality DBF
•• GSSHA Timeseries
HSPF Binary Output
NCDC New Data Rles
••• Read Data With Script
••• SUSTAIN or PLTGEN Timeseries
Ok
Cancel
4. In the HSPF folder within your working folder, locate the WDM file referenced to the watershed
ID. In this example, it is the WDM that accompanied the install (i.e., O40301010406.wdm). For
simplicity, it is assumed that the WDM file is located within the working folder "TestA":
C:\Users\gwhelan\iemTechnologies\SDMProjectBuilder_100214\TestA\HSPF\
and the WDM file is 040301010406.WDM, as noted in the figure below:
Select WDM Time Series file to open
*,* .-? « Users > gwhelan > iemTechnologies > SDMProjectBuilder_100214 > TestA * HSPF
Organize T New folder
p • a
SDMProjectBmlder.100214
bin
data
TestA
HSPF
til huc!2
met
NHDPIus
NLCD
pcs
TestB
TestC
Type
g. 04030Hll(l406.wdni
10/6/2014 6:08 PM WDM File
1,400 KB
10 6 2014 6:04 PM WDM File
6,920 KB
Filename: O40301010406.wdm
•r WDM Files f.wdm)
5. Click 'Open', and the name of the file becomes registered:
Select Timeseries ] No timeseries are
Save
View
Compute
Save As Texl
Save As Data
Meteorologic
Math
Tree Import Text to WDM
-------
6. Choose 'Select Timeseries',
Select Timeseries
*"•»•. *"•
View
List
Graph
Tree
Manage Files No files ar
*W
Jib Timeseries are selected
^^
Save Com
Save As Text
Save As Data
Import Text to WDM
^ m _£^
B open A
H
SAN ANTONIO
RIVER AUTHORITY
pute
Meteorologic
Math
| About | | Help |
7. A screen similar to the following will appear:
• Select Timeseries
File Attributes Select Help
Select Attribute Values to Filter Available Data
Scenario "
04030101
Matching Data (51 of 51)
04030101
04030101
04030101
04030101
04030101
04030101
04030101
04030101
Selected Data (1 of 51)
04030101
Dates to Include
[ All [ Common |
Start 2000/01/01 2000/01/01
End 2000/12/31 2000/12/31
| Location
IMP102
IMP202
PER101
PER102
PER103
RCH2
PER101
PER101
PER102
PER102
PER103
PER103
PER104
RCH2
2000/01/01
2000/12/31
l.=
^^^^•^^^^•"K
-][ Constituent
- DQAL
Q FLOW
PERO
POQC
RO
FLOW
PERO
POQC
PERO
POQC
PERO
POQC
PERO
DQAL
ifj^£5^
\
a
w
a
T
d] App^ month/day range to each year
n Change Time Step To: 1
Day "•• | Average/Sa
me *• | Ok ) |
Cancel |
8. To add an attribute, such as the Time Unit (e.g., hour, day, month, etc.), choose 'Attributes',
then 'Add'. Go to the down arrow, and choose 'Time Unit'. This is not a necessity, but it allows
to user to ensure that they are choosing the data with the correct units. In this case, hourly
output is needed.
-------
\ Select Timesenes
File rckttributesl Select Help
Select Attrftuli Values to Fitter Available Data
1HP102
IMP202
PER101
PER102
PER103
RCH2
PER101
PER101
PER102
PER102
PER103
PER104
Hatching Data (51 of 51)
04030101
04030101
04030101
04030101
04030101
04030101
04030101
04030101
Selected Data (1 of 51)
04030101
Dates to Include
| AH ) | Common ]
Start 2000/01/01 2000/01/01
End 2000/12/31 2000/12/31
LH Apply month/day range to each year
D Change Tme Step To: 1
• DQAL
D FLOW
PERO
POQC
RO
FLOW
PERO
POQC
PERO
FJQ-
PERO
Constituent
Data Source
Description
History 1
ID
Interval
Location
Pant
STANAM
TGROUP
TSBYR
TSFILL
TSFORM
TSTYPE
Tim.
Calculated Attributes:
Coefficient of Variation
Count
Count Hissing
Count Posrtive
Count Zero
End Date
Geometric Mean
Last
Hax
MaxDate
(ten
Mil
MinDate
Serial Correlation Coefficient
Skew
Standard Deviation
Standard Error of Skew
Start Date
Sum
Sum Annual
Variance
9.
Within the 'Select Attribute Values to Filter Available Data' section, under the 'Constituent'
heading, choose 'DQAL', as this is the constituent that provides the microbial density time
series. Choose the location of interest under the 'Matching Data' section (i.e., click on 'RCH2'). In
this case, we choose 'RCH2', this location appears in the 'Selected Data' section.
File Attributes Select Help
Select Attribute Values to Fifter Available Data
04030101 IHP102
IMP202
PER101
PER102
PER103
Matching Dalo(B of 51)
04030101 RCH1
04030101 RCH6
04030101 RCH7
04030101 RCH5
04030101 F1CH4
04030101 RCH3
04030101 RCH8
Selected Data (1 of 51)
04030101
Dates to Include
Al | [Common
Stall 2000/01/01 2000/01/01
End 2000/12/31 2000/12/31
L! Apply month/day range to each year
G Change Time Step To: 1 | Day
LI FLOW
PERO
POQC
RO
DQAL
DQAL
Hour
D.y
Hour
Hour
DQAL
DQAL
Hour
Hour
Hour
Hour
-------
If the user wants to specify a subset of the time series, they can modify the 'Start' and 'End' dates
located in the lower most section, titled 'Dates to Include'. In the example provided, the time series
extends over one year (i.e., 2000). If the user is interested in a particular day or weekend (e.g., when
a beach is being used), this is the place where the user chooses which days to assess. In this
particular example, we would like to assess a three day exposure period from March 1, 2000
through March 3, 2000.
. Select Tories ~^ggjj^gf
File Attributes Select Help
Select Attribute Values to Filter Available Data
Scenario T Location
04030101 IMP102
IMP202
PER101
PER102
PER103
ni~rn r\ i
Matching Data (8 of 51)
04030101 RCH1
04030101 RCH6
04030101 RCH7
04030101 RCH5
04030101 RCH4
04030101 RCH3
CO301Q1 RCH8
04030101 RCH2
T Constituent » Time Unit T
* GZZ^^^^H' Hour
U FLOW || Day
PERO
PQQC
RO
T- T"
C*f\/~if*
DQAL Hour
DQAL Hour
DQAL Hour
DQAL Hour
DQAL Hour
DQAL Hour
DQAL Hour
DQAL Hour
Selected Data (1 of 51}
04030101 RCH2
Dates to Include
All Common ^^^
DQAL Hour
•****.
Start 2000/01/01 2000/01/01 X200Q/03/of\
End 2000/12/31 2000/12/31 ^ 2000/03/03^
ED Apply month/day range to each year ^^"^^^
O Change Time Step To: 1 Day
T] [Average/Same ^ Ok | Cancel
10. Select 'OK'. The selected time series is registered next to the label 'Select Timeseries':
;'; SARA TimeeHes Utility
[I—IT"- ^C:\U3eu'fl»lietan.|emTechnolDgii5j\SDHPni|eclBuildefjm2H\Ti5aA^SPF\IM03010H)406.»diii
-------
11. Select 'List'.
-:i- SARA Timeseries Utility j-o- 1 B U^»
OpenRle
Select Timeseries
>^^,
C Ust Ij
f'oapi; Y
Tree
Manage Rles j C:\Usere^whelan^emTechnologies\SDMProjectBuilder_HX)214\TestA\HSPF\040301010406.wdni
04030101 RCH2DQAL
Save Compute
Save As Text Meteorologic
Save As Data Math
Import I eat to WUM I ^ I „. I
and a Timeseries List' appears:
v : Tim* Series List . -=M 0 UrfJW.
File Edit
History 1
Constituent
Id
Min
Max
Mean
View Analysis Help
from 040301 01 MOG.wdm
DQAL
151
9.203.1
21,029
15.126
10173
10275
j*
=
10000 Ull
9377.3
9315
9203.1
9303.4
94S9.6
9734.9
10014
10320
10656
11024
11427
11853
12250
12532
12712
12879
13030
13164
13280
11170
'
Note that the information between the pull-down menus and the data is denoted as
"Attributes" (e.g., History, Constituent, Id, Min, etc.). The view may also appear as follows,
because the last settings from your machine are saved, so one screen capture in this tutorial will
not necessarily capture everyone's view.
-------
Timeseries List •"•
File Edit View
History 1 |
Constituent |
Id I
Min |
Max |
Mean |
1990W/01 01:00
1991/'01 02:00
1990/01/01 03:00
199CH01/01 04:00
T99Q'01/Q1 05:00
Analysis Help
from040301010406,wdm
RO
138
OQ06134S
40.172
27202
fromQ40301Q10406.wdm | *
DQAL |
139 |
103.36 |
329.280.000 |
12.202,000
14893 103.36
11.199 109.61
84653 118.88
6,44 131.4
4.9386 147.29
V
12. The output that will be saved to file can be customized. Because we will use this example to
cater to the input needs of the MRA-IT model, it requires only a listing of the microbial densities
without any other identifying information, such as headers or the times associated with each
density. To produce such a txt file, choose 'File', then 'Select Attributes':
File Edit View
Analysis Help
13173
10275
10000
9377.3
9315
9203.1
9303.4
9489.6
9734.9
10014
103-2D
10656
11024
11427
11853
12250
12532
12712
12879
13030
13164
122 S3
11TIS.
>
'
10
-------
13. Deselect all Attributes by clicking on the highlighted ones, producing:
Select
Coefficient of Variation
Constituent
Count
Count Missing
Count Positive
Count Zero
Data Source
Description
End Date
Geometric Mean
History 1
Id
ID
Interval
Last
Location
Max
MaxDate
Mean
Min
MinDate
Point
Scenario
Serial Correlation Coefficient
Skew
All
None
Ok
Cancel
14. Choose 'OK'. Resulting in all Attributes being removed. It just so happens that this screen
capture produces the same results as the previous one.
File Edit View
Analysis Help
10173
10275
10000
9377.3
9315
9203.1
9303.4
9489.6
9734.9
10014
10320
10656
11024
11427
11853
12250
12532
12712
12879
13030
13164
13280
11
-------
15. Choose 'View', then 'Date and Value Formats' to select/deselect formatting information. One
may or may not receive a screen that looks like the following:
i and Value For
Value Format
Date Format
® Year Month Day
O Month Day Year
O Day Month Year
O Days starting 1900
@ Yeara D Two Dig* Years
S Months D Month Names
0 Days
H Hours H Midnight as 24:00
3 Mnutes
D Seconds
Date Separator 7]
Tine Separator Q]
Standard Format tt.88Q.tttit)tlttt)ttti
Exponent Format
Sgnificant Digls
Maximum Length
If Value Cannot Fit
ID
Hdp
Cancel
Apply
Ok
16. Remove the comma in the 'Standard Format', if it exists. If the time formats (e.g., Years,
Months, etc.) have been checked, deselect them by clicking on them. Finally, remove the title.
Date Format
'«• Year Month Day
Month Day Year
Day Month Year
Days starting 1900
j Two Digit Years
ths D Month Names
D Midnight as 24:00
Date Separator /
Time Separator :
Help Cancel I Apply
Value Format
Standard F
Exponent Format
Significant Digits
Maximum Length
If Value Cannot Fit
1D
Ok
12
-------
17. Click 'Apply', then 'Ok', and the commas will be removed as well as Attributes:
File Edit View
Analysis Help
10173
10275
10000
9377.3
9315
9203.1
9303.4
94S9.6
9734.9
10014
10320
10656
11024
11427
11853
12250
12532
12712
12879
13030
13164
13280
11TTS
'
^
18. When satisfied with the selected data, attributes, and format, Choose 'File', then 'Save
Changes'.
19. Close by clicking on the X in the upper right hand corner.
20. Choose 'Save as Text'
Open File
Manage Files No files are open
Select Timeseries No Timeseries are selected
View
Compute
SAN ANTONIO
RIVER AUTHORITY
Meteorologic
Math
About
Help
13
-------
21. Create a name for the file, and Save it to your working folder:
s—,.
^^^^^^^^^^^^^^^^B 1
Organize^ New folder
«= - O
SARATimeSerieslltility •* Name Date modified Type
SDMPB
TestAOutput.txt 10/12/2014 12:17 ... Text [
SDMProjectBuilder 100214
^ TestAOutput_Revl.txt 8/11/2015 10:44 AM Text!
bin
data
ju TestA
«>
Filename:
Save as type:
" Hide Folders
uc!2
TestAOutput_Rev2|
-
Text Files (*.trtj T
| Save | Cancel
After carefully choosing the format the first time using the Timeseries List' form, the 'Save As
Text' button on the main window can be used to save other data using the same format without
having to open the 'Timeseries List' form again.
22. Close by clicking on the X in the upper right hand corner. This file is ready to be consumed by
other modules. In this case, by the MRA-IT risk assessment module.
DISCLAIMER
This document has been reviewed in accordance with U.S. Environmental Protection Agency policy and
approved for publication.
REFERENCES
Aqua Terra. 2011. SARA Timeseries Utility
(last accessed 12.08.15).
Pelton, M.A. 2015. IRRA Requirements, Design and Specifications Documentation. Battelle, Pacific
Northwest Division, Richland, WA (Draft)
Seller, J.A., Olivieri, A.W., Eisenberg, J.N.S., Sakaji, R., Danielson, R., 2004. Evaluation of Microbial Risk
Assessment Techniques and Applications, OO-PUM-3. Water Environmental Research Foundation,
Alexandria, VA.
Seller, J.A., Seto, E., Olivieri, A.W., 2008.Microbial Risk Assessment Interface Tool: User Documentation.
Water Environmental Research Foundation, Alexandria, VA.
14
-------
Whelan, G., K. Kim, K. Wolfe, R. Parmar, M. Galvin. 2015. Quantitative Microbial Risk Assessment
Tutorial: Installation of Software for Watershed Modeling in Support of QMRA. EPA/600/B-15/276. U.S.
Environmental Protection Agency, National Exposure Research Laboratory, Athens, GA.
Whelan, G., K. Kim, M.A. Pelton, J.A. Seller, K.J. Castleton, M. Molina, Y. Pachepsky, J. Ravenscroft, R.
Zepp. 2014. An Integrated Environmental Modeling Framework for Performing Quantitative Microbial
Risk Assessments. Environ ModellSoftw, 55:77-91.
15
------- |