Users Guide to the NIST Data Alignment Tool V1.0

NIST Technical

Note

2015

Users Guide to the NIST Data

Alignment Tool V1.0

Michael A. Galler

This publication is available free of charge from:

https://doi.org/10.6028/NIST.TN.2015

NIST Technical Note

2015

Users Guide to the NIST Data

Alignment Tool V1.0

Michael A. Galler

Energy and Environment Division

Engineering Laboratory

September 2018

U.S. Department of Commerce

Wilbur L. Ross, Jr., Secretary

National Institute of Standards and Technology

Walter Copan, NIST Director and Undersecretary of Commerce for Standards and Technology

This publication is available free of charge from:

https://doi.org/10.6028/NIST.TN.2015

Certain commercial entities, equipment, or materials may be identified in this

document in order to describe an experimental procedure or concept adequately.

Such identification is

not intended to imply recommendation or endorsement by the

National Institute of Standards and Technology, nor is it intended to imply that the

entities, materials, or equipment are necessarily the best available for the purpose.

National Institute of Standards and Technology Technical Note 2015

Natl. Inst. Stand. Technol. Tech. Note 2015, 20 pages (September 2018)

CODEN: NTNOEF

This publication is available free of charge from:

https://doi.org

/10.6028/NIST.TN.

2015

Abstract

The Data Alignment Tool (DAT) was built to assist with the analysis of data used in testing fault

detection and diagnostics (FDD) tools and building commissioning (Cx) tools. The data sources

were the Virtual Cybernetic Building Testbed (VCBT) [1] and data collected from real buildings.

The raw data processed by the DAT typically spanned multiple input files, and was not in a

format appropriate for the analysis tools. The features provided by the DAT were implemented

to address the specific needs of the tools being tested, but should also be applicable to a wide

range of other uses.

The DAT can read data from one or more data files, perform several useful operations on it, and

save it with a variety of options. The operations implemented include combining subsets of

columns from multiple data files into one output file, reducing data by modifying the time span

between data points, performing unit conversions, and filtering data by time of day. Output file

operations include a column filter or splitting out files by date. As a manual process, each of

these operations would be time consuming, tedious and error prone. The DAT largely automates

these operations.

Disclaimers

Any mention of commercial products in the DAT or this user’s guide is for information purposes

only; it does not imply recommendation or endorsement by the National Institute of Standards

and Technology (NIST).

This software was developed at NIST by employees of the Federal Government in the course of

their official duties. Pursuant to Title 17 Section 105 of the United States Code this software is

not subject to copyright protection and is in the public domain. This software is an experimental

system. NIST assumes no responsibility whatsoever for its use by other parties, and makes no

guarantees, expressed or implied, about its quality, reliability, or any other characteristic.

This software can be redistributed and/or modified freely provided that any derivative works

bear some notice that they are derived from it, and any modified versions bear some notice that

they have been modified. We would appreciate acknowledgement if the software is used.

i

______________________________________________________________________________________________________

This publication is available free of charge from: https://doi.org/10.6028/NIST.TN.2015

Table of Contents

1. Introduction to the NIST Data Alignment Tool........................................................................ 1

2. Introduction to the DAT User Interface.................................................................................... 3

2.1 Section 1 of the DAT Interface— Options for Reading Data Files ................................. 3

2.2 Section 2 of the DAT Interface— Output Files ............................................................... 5

2.3 Section 3 of the DAT Interface— Unit Conversion......................................................... 7

2.4 Section 4 of the DAT Interface— Status Display............................................................ 8

2.5 Usage Notes...................................................................................................................... 8

3. Using the DAT— Use Cases .................................................................................................. 10

3.1 Reading a data file.......................................................................................................... 10

3.2 Reading multiple data files............................................................................................. 10

3.3 Using a column label template ....................................................................................... 11

3.4 Changing the data interval of a data file ........................................................................ 11

3.5 Reading in a data file with time bracketing.................................................................... 12

3.6 Performing a unit conversion ......................................................................................... 12

3.7 Saving data to a CSV file ............................................................................................... 13

3.8 Saving data to a CSV file with a modified initial date stamp ........................................ 13

3.9 Saving data to a CSV file with an output template ........................................................ 13

3.10 Saving data to a series of CSV files separated by date .................................................. 14

4. References................................................................................................................................. 15

ii

______________________________________________________________________________________________________

This publication is available free of charge from: https://doi.org/10.6028/NIST.TN.2015

1. Introduction to the NIST Data Alignment Tool

The Data Alignment Tool (DAT) was built to assist with the analysis of data used in testing fault

detection and diagnostics (FDD) tools and building commissioning (Cx) tools. The data sources

were the Virtual Cybernetic Building Testbed (VCBT) [1] and data collected from real buildings

using the BACnet Data Source (BDS). The raw data processed by the DAT typically spanned

multiple input files, and was not in a format appropriate for the analysis tools. The DAT is built

as a 64-bit program and can process large amounts of data. The features provided by the DAT

were implemented to address the specific needs of the tools being tested, but should also be

applicable to a wide range of other uses.

Each FDD and Cx tool tested had unique requirements relating to the format and order of input

data. To analyze the data, specific columns from different series of files often needed to be

combined into one file formatted for the specific analysis tool being tested. A large amount of

work may be required to prepare the data for analysis for even a single day of testing, and many

tests spanned multiple days. As a manual process, this would be time consuming, tedious and

error prone. The DAT largely automates this process.

The DAT can read data from one or more data files, perform several useful operations on it, and

save it with a variety of options. The operations implemented include combining subsets of

columns from multiple data files into one output file, reducing data by modifying the time span

between data points, performing unit conversions, and filtering data by time of day. Output file

operations include a column filter or splitting out files by date. The columns in the output file

may be in any order, and are not restricted by their placement or order in the input files. The

column labels are case sensitive and do not use wildcards. This is illustrated in Figure 1, which

shows selected data from two files (top) being combined into one file (bottom). The Graphical

User Interface for the DAT is shown in Figure 2.

Figure 1:

Combining data colum

n

s from multiple files

The DAT allows the user to make modifications to the time and date of data input, and to convert

the units of some types of data. The time and date modifications involve restricting the time

stamp of input data used (bracketing), and modifying the timestamp of the data to start at a

1

______________________________________________________________________________________________________

This publication is available free of charge from: https://doi.org/10.6028/NIST.TN.2015

different time when written to an output file. The DAT also allows the user to optionally fill in

missing data. When a row of data is partially filled, the data from the previous timestamp will be

copied for the missing data. Data may be saved in files in CSV format or in extensible markup

language (XML). These options are explained in more detail in Section 2.

Figure 2:

The NIST Data Alignment Tool

at

s

tartup

.

2

______________________________________________________________________________________________________

This publication is available free of charge from: https://doi.org/10.6028/NIST.TN.2015

2. Introduction to the DAT User Interface

Interaction with the DAT occurs through the graphical user interface (GUI). The GUI contains

four main sections, each with a separate purpose. The sections are enumerated in Figure 3.

Figure 3:

The sections of interest on the DAT GUI

.

Each section contains features with related functionality. Section 1 contains controls and options

relating to input of data, including methods that allow the user to read data files, to enable

bracketing of the analysis time, to set the bracketing start and stop times, and to set the data

interval. Section 2 contains controls relating to output of data, including methods that allow the

user to save processed data to a new file, adjust the output date stamp, and to specify the

separator used in the output files. Section 3 allows the user to convert data that has been read into

different units. Section 4 displays status and progress information. These features are described

in the following sections.

2.1 Section 1 of the DAT Interface— Options for Reading Data Files

Section 1 contains controls relating to input of data, including methods that allow the user to

enter configuration files, to read data files, to enable bracketing of the analysis time, and to set

the daily start and stop times. This area of the DAT is shown in Figure 4. Each of the controls in

Section 1 are discussed below. Use of some of the controls is demonstrated in use cases 1 to 5 in

Section 3.

Bracket Evaluation Time: The controls in this section enable the user to bracket the times of

day of the data read by the DAT, for example during the occupied period. Times outside of the

range entered are discarded when the input data file is read. The bracketing times are applied to

each input day equally. Bracketing is enabled by selecting the check box labeled "Enable.” If

bracketing is to be used, it must be selected before the data files are read. Changes to this setting

3

______________________________________________________________________________________________________

This publication is available free of charge from: https://doi.org/10.6028/NIST.TN.2015

will not be applied retroactively. The bracketing settings may be modified each time a data file is

read. Template files are not affected by bracketing.

Data Interval: The data interval determines the span of time between datapoints. Input data with

a higher frequency will be reduced to the chosen interval, either by averaging or sampling as

selected. Input data with a lower frequency (i.e., undersampled data) will result in missing

datapoints when output. The desired data interval is selected by using the pull-down menu next

to the label "Data Interval.” The available intervals are 10 s, 1 min, 2 min, 5 min, 10 min, 15

min, or no interval. The resulting time stamps will start at an increment of the next larger unit,

i.e., for the 10 s interval data will be at 10 s, 20 s, 30 s, etc. For the 5 min interval data will be at

5 min, 10 min, 15 min etc. The no interval option allows the data to be read without

modification. If the interval of the data read matches the selected interval, the data will also not

be modified.

Figure 4:

Section 1 of the DAT

:

o

ptions

for

r

eading

d

ata

f

iles

.

Sample Data or Average Data: The pull-down menu labeled "Sample/Average Data" allows

the user to determine the method used to reduce the data. If the Average Data option is selected,

the data will be averaged over the selected Data Interval. If the Sample Data option is selected,

the data point closest to each time indicated by the Data Interval will be used, and the remainder

of the data will be discarded.

Column Label Template: The column label template is read by pressing the button labeled

"Read Label Template" and selecting the correct file in the file selection window. The column

label template tells the DAT the labels of the columns in the output file and their order. The

template file is a CSV file with the first line consisting of the labels. Further lines in the file are

ignored. A previously configured data file may be used as the label template. If no such data files

are available, as may be the case the first time the DAT is used, a template file may be assembled

manually. If no column label template is entered, the columns in the first data file read will be

used as the template.

4

______________________________________________________________________________________________________

This publication is available free of charge from: https://doi.org/10.6028/NIST.TN.2015

Read CSV Data: Data is read by pressing the button labeled "Read CSV Data" and then

selecting one or more CSV files in the file selection window. An XML file previously saved by

the DAT may also be read. If a column label template has not been selected, the columns in the

first data file read will be used as the template. Note that if multiple files are to be read, the XML

file should be read first. If there are multiple files to be read, they may all be selected to be read

at one time, or they may be read one at a time by multiple presses of the "Read CSV Data"

button.

Clear CSV Data: Data that has been read may be cleared from storage by pressing the "Clear

CSV Data" button. This will only erase the data stored by the program, and will not affect the

original data files or the column label template. Additional data files may then be read.

Fill Blanks: When data is collected from more than one source, it is possible that for some

datapoints the timestamps will fail to match, resulting in timestamps that do not receive data

from all input sources. This results in blank positions in the data, which may cause errors or

improper analysis when read by some analysis tools. Using the "Fill Blanks" button causes the

DAT to search for these blank positions and fill them with the value from the preceding

timestamp. After this operation, the total number of blanks that have been filled is displayed to

the user. An excessive number of blank positions may indicate a problem with the input data or

settings.

2.2 Section 2 of the DAT Interface— Output Files

The controls in this section allow the user to save the data to a CSV file or XML file, or to a

series of files. The user may select an output template, which works similarly to the input

template, and specify the delimiter used in the output CSV files. The user may also modify the

date stamp on the output data, which is useful if the date recorded with the data is incorrect. The

controls described in this section are shown in Figure 5. Each of the controls in Section 2 are

discussed below. Use of some of the controls is demonstrated in use cases 7 to 10 in Section 3.

Save to XML: The Save to XML option can be used to save data sets between runs of the DAT.

The XML file also contains metadata about the column template and the data files that have been

read, including the file name, path, and the order of columns. The file is saved in the XML

format that the DAT uses internally to store data, and may be reentered when restarting the DAT.

Figure 5:

Section 2

of the DAT

: s

av

e

o

ptions

for

o

utput

f

iles

.

5

______________________________________________________________________________________________________

This publication is available free of charge from: https://doi.org/10.6028/NIST.TN.2015

Save to CSV: The Save to CSV option is used to export data to a CSV file. The output options

should be reviewed before saving, and are explained below. The CSV file will contain a header

line consisting of the labels for each column, followed by lines containing data. The data lines

start with a date stamp, followed by the data. The date will be in the format specified in the Date

Format box, which is explained below. The exported CSV file can be opened using a spreadsheet

program, a text editor, or any other application which can read CSV files. The exported CSV file

may also be read by the DAT in a future session to further modify the data.

Split Output File: Output CSV files may be split by day, which creates a series of output files.

If this option is chosen, file lengths of 1 to 7 days may be selected. The files will be split with the

data in each file starting at the first data point after midnight, regardless of the starting time of

the data. If a length of more than one day is chosen, the first day will be included even if it

contains less than a full day of data. The output files will be indicated by appending an 'S##' to

the file name mask, i.e., if the output file name mask is "MyData", the output files would be

named "MyData S01.csv", "MyData S02.csv" and so on until all data had been saved.

Set Output Template: Subsets of columns may be designated for output in CSV files by using

an output template. An output template is selected by clicking on the 'Set Output Template'

button, which opens a file selection dialog box. The output template functions similarly to the

input template. The user is given the choice to delete previously entered output templates when

selecting a new one.

CSV Delimiter: The control labelled "CSV Delimiter" allows the user to specify the delimiter

used in CSV files. This is useful because some applications use different symbols as the

delimiter in CSV files, despite the name of the format.

Time Delimiter: The control labelled "Time Delimiter" will cause the program to use the CSV

delimiter between the date and time fields in the output CSV file. It is selected by default. If this

box is not checked there will be no delimiter between the date and time fields.

Write Date Starting At: To enable changing the initial date stamp in the output data, select the

checkbox labeled "Write date starting at:" as shown in Figure 5 and then use the standard date

picker to select the initial date stamp. This feature is intended to correct date stamps when they

do not correspond to the actual date the data represents, i.e., when the date on the data

acquisition system is incorrect. This can happen with a hardware in the loop simulation when the

simulation time does not match the clock time of the data acquisition system. The time of day

associated with each datapoint will not be changed. If the data covers multiple days, the date

written will increment appropriately. Note that using this feature will change the date stamp for

all data, and does not select a range of data to save. For example, if a date corresponding to the

second day of data is selected, the full data set will be saved but with the date stamp of each

datapoint modified to be two days later.

6

______________________________________________________________________________________________________

This publication is available free of charge from: https://doi.org/10.6028/NIST.TN.2015

Date Format: The output date format may also be modified. The output date format will be

written in the format specified in the drop-down menu labelled "Date Format." Note that this is

the same control used to specify the input date format.

2.3 Section 3 of the DAT Interface— Unit Conversion

The controls in this section allow the user to convert the units of columns of data that have been

read. The available conversions are listed in Table 1. To perform a conversion, select one of the

conversions from the drop-down menu. The conversions are listed in (from)

→

(to) order, where

the current units are (from) and the desired units are (to).

Table 1: Available Unit Conversions.

Conversion # From To Default Label

1

°

F

K

TEMP

2

CFM

Kg/s

a

FLOW

3

°

F

°

C

TEMP

4

CFM

L/s

a

FLOW

5

K

°

C

TEMP

6

Kg/s

L/s

a

FLOW

7

Percent

Fraction

Valve

a

Conversion

uses

dry air at 20

°

C

, with a density of 1.2 kg/m

3

.

When a conversion is selected, a default search word is entered in the text box below the drop-

down menu. This is shown as the text "TEMP" in Figure 6. The user may edit the default search

text in the box to match their column headings. The search text will be searched for in column

headings, and columns with matching text will be converted. If the column label contains the

Figure 6: Section 3 of the DAT: unit conversions.

search text as a portion of its text, the selected conversion will be applied to that column. The

selection is case sensitive. For example, if the search text is "Temp", then all columns with labels

such as "SATemp”, "RA_Temp" or "Temp-OA" would be selected. Columns with labels such as

"SA-temp”, "MA_TEMP" or "OARH" would not be selected. Use of the unit conversion

functionality is demonstrated in use case 6 in Section 3.

7

______________________________________________________________________________________________________

This publication is available free of charge from: https://doi.org/10.6028/NIST.TN.2015

The methods for the temperature conversions are straightforward. Conversion 7 is used to

convert numbers from the range 0 to 100 to 0 to 1. For Conversions 2 and 6, which are flow rate

conversions, the fluid is assumed to be air with a density of 1.2 kg/m

3

, which is the density of dry

air at 20 °C. If the data to be converted do not match these conditions, another method should be

used.

2.4 Section 4 of the DAT Interface— Status Display

The controls in this section display information about the status of operations initiated by the

user, such as reading files, converting units, saving files, or other operations selected by the user.

The text shown will vary depending on the exact operations chosen by the user. The status screen

at startup is shown in Figure 7.

Figure 7:

Section 4

of the DAT

:

s

tatus

d

isplay

.

2.5 Usage Notes

This section contains additional information about usage of the DAT.

Time and date: The date and time must be at the beginning of each data line in input CSV data

files. They may be in one or two columns, depending on the selected format. The date must be

formatted as shown in the Date Format box, by default MM/DD/YYYY, and the time as

HH:MM:SS, where seconds are optional. For example, 4:00 PM on March 14, 2016 would be

written as 03/14/2016, 16:00:00. Other examples for this time are given in Table 2. Time/date

stamp formats 1 to 5 have the date in the first column, and time in the second column. Time/date

stamp formats 6 to 8 have the time and date in the first column, and data starting in the second

column. Time/date stamps 9 and 10 have two complete time/date stamps. Time/date stamp

formats 11 and 12 follow ISO 8601 extended and basic formats for calendar dates. Note that for

time stamp formats 6 to 12 the length of each field must be as specified.

After the date and time columns in an input CSV file, any column labels where the label contains

the strings 'DATE' or 'TIME' will be ignored, until a valid column label is found. After a valid

column label is found, these strings are not checked for. Any label without those strings and

consisting of ASCII characters 32 to 126 is considered valid. This check is necessary to process

8

______________________________________________________________________________________________________

This publication is available free of charge from: https://doi.org/10.6028/NIST.TN.2015

some data files generated using the BACnet Data Source (BDS) [2] and BACnet

Communications DLL [3] while collecting data from the VCBT. The BDS applied two sets of

time/date stamps to these files, one set for the actual time, and one set for the time in the HVAC

controllers and computer simulations. The controller/simulation time was used for aligning data,

while the real time/date stamp was saved to verify that data was collected properly. Time formats

9 and 10 in Table 2 should be used when dual time/date stamps are present. Time format 9 will

retain the first time/date stamp, and time format 10 will retain the second time/date stamp.

If modifying an XML file, the user should follow the format used in the file. Note that XML files

are useful as an intermediate step, such as to save progress reading or modifying data, and are

unlikely to be useful as final output.

Table 2: Examples of Different Time/Date Stamp Formats, Using March 14, 2016, 4 PM.

ID Format Column 1 Column 2

1

DD/MM/YYYY

14/3/2016