Instructions for data provider ============================== Data provider is usually the scientist performing measurements in the field. System architecture ------------------- Data flow model :download:`specification <../graphics/DataFlowModel.svg>`. .. image:: ./../graphics/DataFlowModel.svg The data provider must make sure that: 1. For online sensor data: a. The raw data arrive in the landing-zone at the right place, b. A conversion script is provided to interpret the raw data, and c. Meta-data are provided. 2. For offline laboratory data: a. The raw data is copied to the whdp server b. A conversion script is available and running to process the data into the lab_results.csv file c. Meta-data are provided. General workflow ---------------- Working on the real landing-zone would be dangerous. Therefore, all development and testing is done on a copy of the landing-zone. The WHDP provides a command to create development landing-zones. A development landing-zone can have any names, but let's call it ``dlz`` for now: .. code-block:: none $ whdp start-develop dlz This creates a folder (a copy of the real landing-zone) called ``dlz`` in the home directory. You can see how the created landing zone looks like with ``ls -l dlz``. The WHDP provides various checks to ensure that the provided conversion scripts and meta-data are consistent. The checks are ran by: .. code-block:: none $ whdp check dlz If everything is fine, modify the develop landing-zone (e.g. add a new sensor) according to the instructions given below. After the modifications run the checks again. .. code-block:: none $ whdp check dlz It is recommended to execute this checks after any small changes. If this succeeds, update the operational landing zone: .. code-block:: none $ whdp update-operational dlz All future raw data should be delivered directly into the operational database. In the following sections, the different types of modifications/additions are explained. Add site -------- In order to add a new measuring site, the information about this site have to be provided as a ``site.yaml`` file in a new folder for the site, within the ``sites`` folder of the landingzone. The information to be specified are: - Name: Name of the site - Description: Free text describing the site - Unit: A unique name for the room/space (e.g., BU_B09, FC_C24, NE_A017) - Area: The section of the room/space floor plan (e.g., GW). - Setup: The experimental setup (e.g., GW). - Component: A part of the experimental setup (e.g., MBR-Tank1). - Status: The current state of operation of the site (e.g., operational). - Pictures (optional): Pictures relating to the site can be specified. Pictures are normally stored in the ``images`` folder of the specific site. The structure of the file has to be the same as in the example below: .. include:: site_example.rst Adding a site if fairly trivial by starting an interactive input program. Typing a dot (.) at any time will abort the entry: .. code-block:: none $ whdp add dlz site Semi-automatic site updates ~~~~~~~~~~~~~~~~~~~~~~~~~~~ The WHDP-provider-tool-kit (https://gitlab.com/krisvillez/whdp-provider-tool-kit) includes a script dlz_site.py to automatically generate all sites in the dlz. This script reads an Excel file (whdp_table_site.xlsx) to obtain the list of all sites. Note on sampling for laboratory data analysis ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~ - If the added site is a location where laboratory samples can be taken, it is likely necessary to modify the laboratory workflow accordingly (E.g. programmable drop-down menus in Excel sheets). - The laboratory data conversion script is programmed so that adding a site does not require modification of this script. Add or modify variables ----------------------- The file ``variable.yaml`` is stored in the ``data`` folder and contains all variables recognized within the WHDP. This file is modified to add new variables. The information to be included are: - Name: Shorthand name of the variable (e.g., sensor_bp) - Description: Additional description of the variable. In case there is no description required, the field can remain empty. - Unit: Specifies the measurement unit of the variable Within ``variable.yaml``, each variable is described by 4 text lines with the following structure (note the dash in the first line): .. code-block:: YAML - name: sensor_bp unit: mbar description: pressure To modify ``variable.yaml`` with the nano editor, type the following in the command line: .. code-block:: none $ nano dlz/data/variables.yaml Semi-automatic variable updates ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~ The WHDP-provider-tool-kit (https://gitlab.com/krisvillez/whdp-provider-tool-kit) includes a script dlz_variable.py to automatically generate all variables in the dlz. This script reads an Excel file (whdp_table_variable.xlsx) to obtain the list of all variables. Note on name convention ~~~~~~~~~~~~~~~~~~~~~~~ In conventional WHDP practice, each variable name is composed of the following parts separated by an underscore (_): - An actuator/sensor/lab specification (e.g., 'actuator' for valve state; 'sensor' for pressure measurement; 'lab' for laboratory measurement) - A variable description, according to the EN_81346 norm (see https://de.wikipedia.org/wiki/EN_81346 ), e.g. sensor_bp for pressure measurement - [optional] An additional variable descriptor when the EN_81346 norm is too vague, e.g.: sensor_bq_doconc for dissolved oxygen concentration Note on laboratory data ~~~~~~~~~~~~~~~~~~~~~~~ - If the added variable is a new laboratory measurement, it is likely necessary to modify the laboratory workflow accordingly (E.g. programmable drop-down menus in Excel sheets). - Adding a variable requires modification of the laboratory data conversion script . Add or modify persons (offline laboratory data) ----------------------------------------------- The file ``persons.yaml`` is stored in the ``/dlz/lab_data`` folder and contains all current and previous staff members recognized within the WHDP. The information to be included are: - Abbrev: Unique shorthand identifier of the person (e.g., villezkr) - Name: Full name of the staff member (e.g., Kris Villez) - Department: Affiliation of the staff member (e.g., eawag-eng) Within ``persons.yaml``, each variable is described by 4 text lines with the following structure (note the dash in the first line): .. code-block:: YAML - abbrev: villezkr name: Kris Villez department: eawag-eng To modify ``persons.yaml`` with the nano editor, type the following in the command line: .. code-block:: none $ nano dlz/lab_data/persons.yaml Semi-automatic person updates ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~ The WHDP-provider-tool-kit (https://gitlab.com/krisvillez/whdp-provider-tool-kit) includes a script dlz_person.py to automatically generate all persons in the dlz. This script reads the sheet `person` in an Excel file (last known: LabSamples2019.xlsx) to obtain the list of all persons. Conversion of raw data (offline laboratory data) ------------------------------------------------ Standardized file format ~~~~~~~~~~~~~~~~~~~~~~~~ The data arriving in the landing zone are called *raw data*. The raw data must be converted into a so called *standardized file* by a conversion script. The format of the standardized files is defined below. In the WHDP current practice, a single Python script is used to collect all laboratory data and process it into a single file 'lab_results.csv'. This file is overwritten every day. The WHDP takes this file in and also overwrites existing entries when results are changed. In normal operation, data entries in the WHDP can be overwritten but cannot removed. Standardized file format for lab_results.csv ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~ The standardized file format for the input data is a ``csv`` file with 4 columns. It must adhere the following standards: - File format: ``csv`` file with semicolon as delimiter (``;``) - Columns: The first row contains the column names. The columns are always: - lab_identifier - sample_identifier: must be unique - variable: must exist in ``variables.yaml`` and have the exact same name (see above) - filter_lab - dilution_lab [can be empty] - method_lab - value_lab: must contain only numerical values. Missing values (``NULL``, ``NA``, or similar) are not allowed. - description_lab [can be empty] - person_abbrev_lab - timestamp_start_lab [can be empty]: format ``yyyy-mm-dd hh:mm:ss`` - timestamp_end_lab: format ``yyyy-mm-dd hh:mm:ss`` - site: must be defined as such and have the exact same name (see above). - person_abbrev_sample: must be defined in ``persons.yaml`` and have the exact same value for abbrev (see above). - filter_sample - dilution_sample [can be empty] - timestamp_sample: format ``yyyy-mm-dd hh:mm:ss`` - description_sample [can be empty] - method_sample [can be empty] Example: +---------------------+-------------------+-------------+------------+--------------+----------------+-----------+-----------------+-------------------+---------------------+-------------------+------------------------------+----------------------+---------------+-----------------+---------------------+---------------------+----------------------+ | lab_identifier | sample_identifier | variable | filter_lab | dilution_lab | method_lab | value_lab | description_lab | person_abbrev_lab | timestamp_start_lab | timestamp_end_lab | site | person_abbrev_sample | filter_sample | dilution_sample | timestamp_sample | description_sample | manual | +---------------------+-------------------+-------------+------------+--------------+----------------+-----------+-----------------+-------------------+---------------------+-------------------+------------------------------+----------------------+---------------+-----------------+---------------------+---------------------+----------------------+ | NE19063_000_labTOC | NE19063 | lab_bq_toc | None | | Shimadzu_TOC-L | 1.39 | D | villezkr | 2019-02-06 23:59:59 | | NE_WaterHub_GW_GW_Batch-Test | hessange | GF/_PET-45/25 | 1.0 | 2019-02-06_17:15:00 | D | manual | +---------------------+-------------------+-------------+------------+--------------+----------------+-----------+-----------------+-------------------+---------------------+-------------------+------------------------------+----------------------+---------------+-----------------+---------------------+---------------------+----------------------+ | NE19054_000_labNO3N | NE19054 | lab_bq_no3n | None | | IC_Anionen | 14.9 | | villezkr | 2019-02-07 23:59:59 | | NE_WaterHub_GW_GW_M2 | braunfab | None | 1.0 | 2019-02-07_10:00:00 | | manual | +---------------------+-------------------+-------------+------------+--------------+----------------+-----------+-----------------+-------------------+---------------------+-------------------+------------------------------+----------------------+---------------+-----------------+---------------------+---------------------+----------------------+ | NE19055_000_labNO3N | NE19055 | lab_bq_no3n | None | | IC_Anionen | 10.1 | | villezkr | 2019-02-07 23:59:59 | | NE_WaterHub_GW_GW_M3 | braunfab | None | 1.0 | 2019-02-07_10:00:00 | | manual | +---------------------+-------------------+-------------+------------+--------------+----------------+-----------+-----------------+-------------------+---------------------+-------------------+------------------------------+----------------------+---------------+-----------------+---------------------+---------------------+----------------------+ | NE19054_000_labPO4P | NE19054 | lab_bq_po4p | None | | IC_Anionen | 1.5 | | villezkr | 2019-02-07 23:59:59 | | NE_WaterHub_GW_GW_M2 | braunfab | None | 1.0 | 2019-02-07_10:00:00 | | manual | +---------------------+-------------------+-------------+------------+--------------+----------------+-----------+-----------------+-------------------+---------------------+-------------------+------------------------------+----------------------+---------------+-----------------+---------------------+---------------------+----------------------+ Conversion script ~~~~~~~~~~~~~~~~~ Unlike the case for online sensor data, the conversion script is not executed automatically by the WHDP software. Instead the data provider must execute this conversion script manually or set up a time-based execution schedule (with *cron*). Note on automated data transfer and processing ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~ - To automate this process, the following scripts are currently in use - /home/whdp-provider/laboratory/t_lab_central.py (copy *.xlsx files from Q drive) - /home/whdp-provider/laboratory/t_lab_project.py (copy *.xlsx files from Q drive) - /home/whdp-provider/laboratory/c_lab.py (convert *.xlsx files to *.csv format) - The above scripts are executed once per day by means of *cron*, a time-based job scheduler Add a new source_type (online sensor data) ------------------------------------------ In order to add a new signal source_type, the information about this source_type have to be provided as a ``source_type.yaml`` file in a new folder for the source_type within the ``dlz/data`` folder of the landingzone. The information to be specified are:cd .. - Name: unique shorthand name, e.g. obtained by combining the product name and manufacturer (e.g. concube_scan ) - Description: Detailed description Adding a source_type if fairly trivial by starting an interactive input program. Typing a dot (.) at any time will abort the entry: .. code-block:: none $ whdp add dlz source_type Note on programmable logic controllers (PLCs) ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~ In conventional WHDP practice, each programmable logic controller (PLC) is considered a source_type of its own for the following reasons: - The conversion scripts for devices (source) of the same type (source_type) are expected to be similar. Because every PLC is programmed very differently, one cannot expect the conversion scripts to be very similar, at least not to the same degree that one expects for stand-alone sensors. - The use of a source_type to describe a single PLC device allows to provide a conversion script specific to the PLC which can be used to assign the data automatically to their sources (actuators/sensors) associated with the given PLC. Add a source (online sensor data, instance of source_type) ---------------------------------------------------------- In order to add a new signal source, the information about this source have to be provided as a ``source.yaml`` file in a new folder for the source_type within the ``dlz/data`` folder of the landingzone. The information to be specified are: - Name: unique shorthand name for the source (e.g., concube_grey1, prgB615) - Description: detailed description - Serial: [optional] serial number - Manufacturer: [optional] the manufacturer of the device Adding a source_type if fairly trivial by starting an interactive input program. Typing a dot (.) at any time will abort the entry: .. code-block:: none $ whdp add dlz source Semi-automatic source updates ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~ To avoid errors, the WHDP-provider-tool-kit (https://gitlab.com/krisvillez/whdp-provider-tool-kit) includes scripts to automatically generate all sources of a certain source_type in the dlz. This script reads the data from an Excel file which lists the available source_types. Currently, the following scripts and associated Excel sheets are available: +------------------------------+----------------------------+ | script | file | +==============================+============================+ | dlz_sources_memographgrey.py | memographgrey_config.xlsx | +------------------------------+----------------------------+ | dlz_sources_plcgrey.py | plcgrey_config.xlsx | +------------------------------+----------------------------+ Note on programmable logic controllers (PLCs) ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~ - In conventional WHDP practice, each actuator and sensor associated with the same programmable logic controller (PLC) is considered a separate source. A single source can be associated with multiple variables however. E.g., continuous-scale sensors (e.g., pH) typically have a status variable (manual on/manual off/auto on/auto off) and an actual measured value. - In conventional WHDP practice, the name for a source representing a PLC-connected actuator or sensor is equal to name of the PLC program that registers the state (and value) of the actuator (sensor). E.g.: prgB615 Add raw data to existing source (online sensor data) ---------------------------------------------------- Raw data files are written to the respective ``data/`` folders in the operational landing zone as follows: 1. A new file, for example ``data.incomplete``, is created and data are written to this file. 2. Once the file content is complete and the corresponding file handle is closed, the file is renamed to ``data-TIMESTAMP.raw``. *Note, the file must end with ``.raw``!* The actual format of ``TIMESTAMP`` is not fixed but must be unique string, that starts with a dash ``-``, and can be temporarily ordered. Encoding a full date and time string will help the users and developers to inspect and find files, especially if present in the backup zone. This procedure is called *write-rename pattern* and avoids conversion of incomplete data files. The risk for such a rare condition depends on the size of the incoming data files and other factors and is probably very low. But running a data pool over a longer time span increases this risk and could result in missing data in the data base. Note on automated data transfers ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~ - To automate this process, the following scripts are currently in use - t_plcgrey.py (data transfer from plcgrey PLC) - The above script are executed once per day by means of *cron*, a time-based job scheduler - The conversion script for data transfered from plcgrey PLC in use is located here: - /home/whdp-provider/dlz/data/plcgreyconversion.py Conversion of raw data (online sensor data) ------------------------------------------- The files arriving in the landing zone are called *raw data*. Every raw data file must be converted into a so called *standardized file* by a conversion script. The format of the standardized files is defined below. Most typically, every ``source`` needs an individually adapted conversion script. As an alternative, the WHDP also allows an individually adapted conversion script for a ``source_type`` Standardized file format for association with a source ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~ The standardized file format for the input data is a ``csv`` file with 4 columns. It must adhere the following standards: - File format: ``csv`` file with semicolon as delimiter (``;``) - Timestamp format: ``yyyy-mm-dd hh:mm:ss`` - Column names: The first row contains the column names. The columns are always: ``timestamp``, ``variable``, ``value``, and ``site``. The variable must exist in ``variables.yaml`` and have the exact same name (see above). The ``site`` must be defined as such and have the exact same name (see above). - ``value`` column: Must contain only numerical values. Missing values (``NULL``, ``NA``, or similar) are not allowed. Example: +---------------------+-----------------------+-----------+----------------------+ | timestamp | variable | value | site | +=====================+=======================+===========+======================+ | 2019-01-29 12:29:29 | sensor_bp | 18.605044 | NE_WaterHub_GW_GW_M1 | +---------------------+-----------------------+-----------+----------------------+ | 2019-01-29 12:29:30 | sensor_bp | 19.225162 | NE_WaterHub_GW_GW_M1 | +---------------------+-----------------------+-----------+----------------------+ | 2019-01-29 12:29:31 | sensor_bp | 19.535282 | NE_WaterHub_GW_GW_M1 | +---------------------+-----------------------+-----------+----------------------+ | ... | ... | ... | ... | +---------------------+-----------------------+-----------+----------------------+ Standardized file format for association with a source_type ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~ The standardized file format for the input data is a ``csv`` file with either 5 columns. It must adhere the following standards: - File format: ``csv`` file with semicolon as delimiter (``;``) - Timestamp format: ``yyyy-mm-dd hh:mm:ss`` - Column names: The first row contains the column names. The columns are always: ``timestamp``, ``variable``, ``value``, ``site``, and ``source``. The variable must exist in ``variables.yaml`` and have the exact same name (see above). The ``site`` must be defined as such and have the exact same name (see above). The ``source`` must be defined as an instance of the considered ``source_type`` and have the exact same name (see above). - ``value`` column: Must contain only numerical values. Missing values (``NULL``, ``NA``, or similar) are not allowed. Example: +---------------------+-----------------------+-----------+----------------------+---------+ | timestamp | variable | value | site | source | +=====================+=======================+===========+======================+=========+ | 2019-01-29 12:29:29 | sensor_bp | 18.605044 | NE_WaterHub_GW_GW_M1 | prgB615 | +---------------------+-----------------------+-----------+----------------------+---------+ | 2019-01-29 12:29:30 | sensor_bp | 19.225162 | NE_WaterHub_GW_GW_M1 | prgB615 | +---------------------+-----------------------+-----------+----------------------+---------+ | 2019-01-29 12:29:31 | sensor_bp | 19.535282 | NE_WaterHub_GW_GW_M1 | prgB615 | +---------------------+-----------------------+-----------+----------------------+---------+ | ... | ... | ... | ... | ... | +---------------------+-----------------------+-----------+----------------------+---------+ Conversion script ~~~~~~~~~~~~~~~~~ The conversion script must define a *function* which reads raw data and write an output file (a standardized file). The first argument if this function is the path to the input raw data, the second argument the path to the resulting file. The following points should be considered when writing an conversion script: - Indicate corrupt input data by throwing an exception within a conversion script. An informative error message is helpful and will be logged. - If a conversion script writes to ``stdout`` (i.e. normal ``print()`` commands) this may not appear in the WHDP log file and thus might be overseen. - All required third party modules, packages, or libraries must be installed globally. Do not try to install them within a script. The following code snippets show how a conversion script could look like for different languages. R ~ - The file must be named ``conversion.r``. - The function must be named ``convert``. .. include:: r_example.rst Julia ~~~~~ - The function must be named ``convert``. - The name of the julia file and the declared module must be the same (up to the ``.jl`` file extension). So the file containing the module ``conversion_lake_zurich`` must be saved as ``conversion_lake_zurich.jl``. - Further the module and file name must be unique within the landing zone. .. include:: julia_example.rst Python ~~~~~~ .. include:: python_example.rst Matlab ~~~~~~ - The function must be named ``convert``. - The file name must be named ``convert.m``. .. include:: matlab_example.rst