Write and validate output

Introduction

This article demonstrates on how to write the processed data (annex statistics) into the final pre-filled XLSX file and how to validate the file format. The example is based on the demo data set demo_UIBK.xlsx again, for details about the following code chunk please read the article Prepare data from XLSX.

library("readxl")

# Reading measurement data
raw_df <- read_excel("demo_UIBK.xlsx", sheet = "measurements")

# Read and prepare config object
config <- read_excel("demo_UIBK.xlsx", sheet = "annex_configuration")
config <- subset(config, process == TRUE) # Remove all rows process = FALSE

# Prepare annex object
library("annex")
prepared_df <- annex_prepare(raw_df, config, quiet = TRUE)
annex_df    <- annex(RH + T + CO2 ~ datetime | study + home + room,
                     data = prepared_df, tz = "Europe/Berlin")
stats       <- annex_stats(annex_df, format = "long")
head(stats)

##   study    home room year month tod variable         stats value
## 1  demo General  AMB 2011    10 all       RH quality_lower     0
## 2  demo General  AMB 2011    10 all       RH quality_upper     0
## 3  demo General  AMB 2011    10 all       RH quality_start 15248
## 4  demo General  AMB 2011    10 all       RH   quality_end 15253
## 5  demo General  AMB 2011    10 all       RH  interval_Min   300
## 6  demo General  AMB 2011    10 all       RH   interval_Q1   300

Write annex statistics to file

Once the statistics have been calculated, the data - as returned by annex_stats() - can be written into the final ouput file.

The function annex_write_stats() will create a new XLSX file based on an annex template, store the calculated statistics, and pre-fill a series of META sheets to take up the required meta information. This information must be added manually by the user.

annex_write_stats(stats, file = "final_UIBK.xlsx", user = 1234)

## Get a copy of the template

## Starting to write final_UIBK.xlsx
##  - Writing META-Study
##  - Write 1 rows into 'META-Study'
##  - Writing META-Home
##  - Write 19 rows into 'META-Home'
##  - Writing META-Room
##  - Write 27 rows into 'META-Room'
##  - Writing META-Variable
##  - Write 80 rows into 'META-Variable'
##  - Writing STAT
##  - Adding version numbers
##  - Saving file

Where to get a user ID?

Will be defined during Annex 86 meetings or contact Gabriel.

Update meta information

At this point, the XLSX file final_UIBK.xlsx does contain all the statistics, but missing the meta information.

Where additional information is needed, the XLSX sheet contains placeholders like <....> (check any of the META sheets) which need to be updated by the user. Some columns/fields require meta information, others are optional. If optional and no meta information is available, the placeholders (<....>) can also be deleted. annex_validate() will inform the user what needs to be done:

WARNING: Required meta information missing (either empty or placeholder)
NOTE: If it still contains the placeholder. In this case simply replace the placeholder with the correct meta information or delete it (for optional information).

At any time the file can be validated (see next section) to see if everything is according to the requested standard of information is still missing or invalid information has been entered.

Append or update the XLSX file

The function write_annex_stats() has an argument mode which defaults to "write" but provides two alternative modes: "append" and "update". These two modes allow to write additional/updated statistics into an existing XLSX file and will preserve already added meta information (see section above).

Append: Append new data to an existing file. The function will throw an error if the new data overlaps with the existing one.
Update: Will replace existing data in STAT if the new data overlaps with existing data in the XLSX file.

Note that mode = "update" will overwrite existing data which may result in loss of data and potentially replaces existing records with new partial data depending on the new object provided. Consider making a backup of the XLSX file beforehand.

WARNING: The two modes "append" and "update" are in an early stage and may have some unexpected outcomes. Feel free to test these modes and report any problems/issues.

One known issue is that it will not work properly if the XLSX file has been saved using libreoffice/openoffice when using a versino of openxlsx prior to version 4.2.5.9001. The latest version (currently only available via github as of April 21, 2023) resolves this problem. For details see (closed) issue #9.

Validate final file

The function annex_validate() performs a series of checks both against the original template (included in the package; always uses current version) as well as within the XLSX file provided as input.

There are critical checks which will result in an exception (error; stops execution) as well as checks resulting in notes for the user. These notes will tell what’s wrong or missing to allow the user to make the appropriate changes, before validating the file again.

The function returns TRUE or FALSE (if it does not throw an error), once TRUE is returned everything is OK with the file and all required meta information has been entered properly. At this stage, the file can be used for further processing or to upload to the research repository (planned for the near future).

check <- annex_validate("final_UIBK.xlsx", user = 1234)

## Checking columns in all sheets
##

##   'META-Study' ........ OK
##   'META-Home' ......... OK
##   'META-Room' ......... OK
##   'META-Variable' ..... OK
##   'Definitions' ....... OK

## Validating XLSX sheet STAT 
##   Everything OK (no warnings)
## Validating XLSX sheet META-Study 
##

##   WARNING: 'META-Study' column 'Contact' missing required info for ID: 1234-demo (row: 2)
##   WARNING: 'META-Study' column 'Institution' missing required info for ID: 1234-demo (row: 2)
##   NOTE: 'META-Study' column 'Year of first publication' provides no info for ID: 1234-demo (row: 2)
##   NOTE: 'META-Study' column 'Publications' provides no info for ID: 1234-demo (row: 2)
##   NOTE: 'META-Study' column 'Links' provides no info for ID: 1234-demo (row: 2)

## Validating XLSX sheet META-Home 
##

##   WARNING: 'META-Home' column 'Location: Country' missing required info for IDs: 1234-demo-General, 1234-demo-W1, 1234-demo-W10, 1234-demo-W11, 1234-demo-W12 and 14 more (rows: 2, 3, 4, 5 and 15 more)
##   NOTE: 'META-Home' column 'Location: City' provides no info for IDs: 1234-demo-General, 1234-demo-W1, 1234-demo-W10, 1234-demo-W11, 1234-demo-W12 and 14 more (rows: 2, 3, 4, 5 and 15 more)
##   WARNING: 'META-Home' column 'Ventilation type' missing required info for IDs: 1234-demo-General, 1234-demo-W1, 1234-demo-W10, 1234-demo-W11, 1234-demo-W12 and 14 more (rows: 2, 3, 4, 5 and 15 more)
##   NOTE: 'META-Home' column 'Comment Vent. Type' provides no info for IDs: 1234-demo-General, 1234-demo-W1, 1234-demo-W10, 1234-demo-W11, 1234-demo-W12 and 14 more (rows: 2, 3, 4, 5 and 15 more)
##   NOTE: 'META-Home' column 'Ventilation rate (entire home; [l/s])' provides no info for IDs: 1234-demo-General, 1234-demo-W1, 1234-demo-W10, 1234-demo-W11, 1234-demo-W12 and 14 more (rows: 2, 3, 4, 5 and 15 more)
##   NOTE: 'META-Home' column 'Method of vent. rate determination' provides no info for IDs: 1234-demo-General, 1234-demo-W1, 1234-demo-W10, 1234-demo-W11, 1234-demo-W12 and 14 more (rows: 2, 3, 4, 5 and 15 more)
##   NOTE: 'META-Home' column 'Comment vent. Rate determination' provides no info for IDs: 1234-demo-General, 1234-demo-W1, 1234-demo-W10, 1234-demo-W11, 1234-demo-W12 and 14 more (rows: 2, 3, 4, 5 and 15 more)
##   NOTE: 'META-Home' column 'Airtightn. [xx]' provides no info for IDs: 1234-demo-General, 1234-demo-W1, 1234-demo-W10, 1234-demo-W11, 1234-demo-W12 and 14 more (rows: 2, 3, 4, 5 and 15 more)
##   NOTE: 'META-Home' column 'Airtightness ref press [Pa]' provides no info for IDs: 1234-demo-General, 1234-demo-W1, 1234-demo-W10, 1234-demo-W11, 1234-demo-W12 and 14 more (rows: 2, 3, 4, 5 and 15 more)
##   NOTE: 'META-Home' column 'Airtightness normalization value' provides no info for IDs: 1234-demo-General, 1234-demo-W1, 1234-demo-W10, 1234-demo-W11, 1234-demo-W12 and 14 more (rows: 2, 3, 4, 5 and 15 more)
##   NOTE: 'META-Home' column 'Type of building' provides no info for IDs: 1234-demo-General, 1234-demo-W1, 1234-demo-W10, 1234-demo-W11, 1234-demo-W12 and 14 more (rows: 2, 3, 4, 5 and 15 more)
##   NOTE: 'META-Home' column 'Size of home / TFA [m^2]' provides no info for IDs: 1234-demo-General, 1234-demo-W1, 1234-demo-W10, 1234-demo-W11, 1234-demo-W12 and 14 more (rows: 2, 3, 4, 5 and 15 more)
##   NOTE: 'META-Home' column 'Type of Occupants' provides no info for IDs: 1234-demo-General, 1234-demo-W1, 1234-demo-W10, 1234-demo-W11, 1234-demo-W12 and 14 more (rows: 2, 3, 4, 5 and 15 more)
##   NOTE: 'META-Home' column 'Constr. type / building materials' provides no info for IDs: 1234-demo-General, 1234-demo-W1, 1234-demo-W10, 1234-demo-W11, 1234-demo-W12 and 14 more (rows: 2, 3, 4, 5 and 15 more)
##   NOTE: 'META-Home' column 'Energy standard' provides no info for IDs: 1234-demo-General, 1234-demo-W1, 1234-demo-W10, 1234-demo-W11, 1234-demo-W12 and 14 more (rows: 2, 3, 4, 5 and 15 more)
##   NOTE: 'META-Home' column 'Year of contruction / major renovation (four digit year)' provides no info for IDs: 1234-demo-General, 1234-demo-W1, 1234-demo-W10, 1234-demo-W11, 1234-demo-W12 and 14 more (rows: 2, 3, 4, 5 and 15 more)
##   WARNING: 'Location: Country' in 'META-Home' not in ISO3 standard, found: <ISO3>, <ISO3>, <ISO3>, <ISO3>, <ISO3>, <ISO3>, <ISO3>, <ISO3>, <ISO3>, <ISO3>, <ISO3>, <ISO3>, <ISO3>, <ISO3>, <ISO3>, <ISO3>, <ISO3>, <ISO3>, <ISO3> (rows: 2, 3, 4, 5, 6 and 14 more).
##   See `?annex_countries` to get all allowed country abbrevations (ISO3166 alpha-3; ISO3).

## Validating XLSX sheet META-Room 
##

##   NOTE: 'META-Room' column 'Additionial room information (e.g., ceiling height, with atrium)' provides no info for IDs: 1234-demo-General-AMB, 1234-demo-General-ETA, 1234-demo-General-SUP, 1234-demo-W1-LIV, 1234-demo-W10-LIV and 22 more (rows: 2, 3, 4, 5 and 23 more)
##   NOTE: 'META-Room' column 'Occupancy: Type' provides no info for IDs: 1234-demo-General-AMB, 1234-demo-General-ETA, 1234-demo-General-SUP, 1234-demo-W1-LIV, 1234-demo-W10-LIV and 22 more (rows: 2, 3, 4, 5 and 23 more)
##   NOTE: 'META-Room' column 'Occupancy: Number' provides no info for IDs: 1234-demo-General-AMB, 1234-demo-General-ETA, 1234-demo-General-SUP, 1234-demo-W1-LIV, 1234-demo-W10-LIV and 22 more (rows: 2, 3, 4, 5 and 23 more)
##   NOTE: 'META-Room' column 'Fresh air supply in measurement location' provides no info for IDs: 1234-demo-General-AMB, 1234-demo-General-ETA, 1234-demo-General-SUP, 1234-demo-W1-LIV, 1234-demo-W10-LIV and 22 more (rows: 2, 3, 4, 5 and 23 more)
##   NOTE: 'META-Room' column 'Ventilation rate (room; [l/s])' provides no info for IDs: 1234-demo-General-AMB, 1234-demo-General-ETA, 1234-demo-General-SUP, 1234-demo-W1-LIV, 1234-demo-W10-LIV and 22 more (rows: 2, 3, 4, 5 and 23 more)
##   NOTE: 'META-Room' column 'Method of vent. rate determination' provides no info for IDs: 1234-demo-General-AMB, 1234-demo-General-ETA, 1234-demo-General-SUP, 1234-demo-W1-LIV, 1234-demo-W10-LIV and 22 more (rows: 2, 3, 4, 5 and 23 more)
##   NOTE: 'META-Room' column 'Comments' provides no info for IDs: 1234-demo-General-AMB, 1234-demo-General-ETA, 1234-demo-General-SUP, 1234-demo-W1-LIV, 1234-demo-W10-LIV and 22 more (rows: 2, 3, 4, 5 and 23 more)

##   Everything OK (no warnings)
## Validating XLSX sheet META-Variable 
##

##   NOTE: 'META-Variable' column 'Variable additional information' provides no info for IDs: 1234-demo-General-AMB-RH, 1234-demo-General-AMB-T, 1234-demo-General-ETA-CO2, 1234-demo-General-ETA-RH, 1234-demo-General-ETA-T and 75 more (rows: 2, 3, 4, 5 and 76 more)
##   NOTE: 'META-Variable' column 'Measurement device' provides no info for IDs: 1234-demo-General-AMB-RH, 1234-demo-General-AMB-T, 1234-demo-General-ETA-CO2, 1234-demo-General-ETA-RH, 1234-demo-General-ETA-T and 75 more (rows: 2, 3, 4, 5 and 76 more)
##   NOTE: 'META-Variable' column 'Comments (e.g., sensor location)' provides no info for IDs: 1234-demo-General-AMB-RH, 1234-demo-General-AMB-T, 1234-demo-General-ETA-CO2, 1234-demo-General-ETA-RH, 1234-demo-General-ETA-T and 75 more (rows: 2, 3, 4, 5 and 76 more)

##   Everything OK (no warnings)
##

In his case no changes have been made to the file written by annex_write_stats(). As no illegal changes have been made, no critical errors are detected. However, a series of messages are printed to inform that not everything has been filled in properly.

Simply open the file in the spreadsheet editing software of your choice (MS Excel, Libreoffice, …), make the required changes, save the file, and repeat validation until the annex_validate() is happy and returns TRUE.

Reto Stauffer