Retrieve Simulation Outputs Using EnergyPlus SQLite Output File

EplusSql class wraps SQL queries that can retrieve simulation outputs using EnergyPlus SQLite output file.

Details

SQLite output is an optional output format for EnergyPlus. It will be created if there is an object in class Output:SQLite. If the value of field Option in class Output:SQLite is set to "SimpleAndTabular", then database tables related to the tabular reports will be also included.

There are more than 30 tables in the SQLite output file which contains all of the data found in EnergyPlus's tabular output files, standard variable and meter output files, plus a number of reports that are found in the eplusout.eio output file. The full description for SQLite outputs can be found in the EnergyPlus "Output Details and Examples" documentation. Note that all column names of tables returned have been tidied, i.e. "KeyValue" becomes "key_value", "IsMeter" becomes "is_meter" and etc.

EplusSql class makes it possible to directly retrieve simulation results without creating an EplusJob object. EplusJob can only get simulation outputs after the job was successfully run before.

However, it should be noted that, unlike EplusJob, there is no checking on whether the simulation is terminated or completed unsuccessfully or, the parent Idf has been changed since last simulation. This means that you may encounter some problems when retrieve data from an unsuccessful simulation. It is suggested to carefully go through the .err file using read_err() to make sure the output data in the SQLite is correct and reliable.

Author

Hongyuan Jia

Methods

Method `new()`

Create an EplusSql object

Usage

EplusSql$new(sql)

Arguments

sql: A path to an local EnergyPlus SQLite output file.

Returns

An EplusSql object.

Examples

\dontrun{
if (is_avail_eplus("8.8")) {
    idf_name <- "1ZoneUncontrolled.idf"
    epw_name <-  "USA_CA_San.Francisco.Intl.AP.724940_TMY3.epw"

    idf_path <- path_eplus_example("8.8", idf_name)
    epw_path <- path_eplus_weather("8.8", epw_name)

    # copy to tempdir and run the model
    idf <- read_idf(idf_path)
    idf$run(epw_path, tempdir(), echo = FALSE)

    # create from local file
    sql <- eplus_sql(file.path(tempdir(), "1ZoneUncontrolled.sql"))
}
}

Method `path()`

Get the file path of current EpwSql object

Usage

EplusSql$path()

Details

$path() returns the path of EnergyPlus SQLite file.

Returns

A single string.

Examples

\dontrun{
# get path
sql$path()
}

Method `path_idf()`

Get the path of corresponding IDF file

Usage

EplusSql$path_idf()

Details

$path_idf() returns the IDF file path with same name as the SQLite file in the same folder. NULL is returned if no corresponding IDF is found.

Returns

NULL or a single string.

Examples

\dontrun{
# get path
sql$path_idf()
}

Method `list_table()`

List all table names in current EnergyPlus SQL output

Usage

EplusSql$list_table()

Details

$list_table() returns all available table and view names in the EnergyPlus SQLite file.

Returns

A character vector

Examples

\dontrun{
sql$list_table()
}

Method `read_table()`

Read a single table from current EnergyPlus SQL output

Usage

EplusSql$read_table(name)

Arguments

name: A single string specifying the name of table to read.

Details

$read_table() takes a valid table name of those from $list_table() and returns that table data in a data.table::data.table() format.

Returns

A data.table::data.table().

Examples

\dontrun{
# read a specific table
sql$read_table("Zones")
}

Method `report_data_dict()`

Read report data dictionary from current EnergyPlus SQL output

Usage

EplusSql$report_data_dict()

Details

$report_data_dict() returns a data.table::data.table() which contains all information about report data.

For details on the meaning of each columns, please see "2.20.2.1 ReportDataDictionary Table" in EnergyPlus "Output Details and Examples" documentation.

Returns

A data.table::data.table() of 10 columns:

report_data_dictionary_index: The integer used to link the dictionary data to the variable data. Mainly useful when joining different tables
is_meter: Whether report data is a meter data. Possible values: 0 and 1
timestep_type: Type of data timestep. Possible values: Zone and HVAC System
key_value: Key name of the data
name: Actual report data name
reporting_frequency:
schedule_name: Name of the the schedule that controls reporting frequency.
units: The data units

Examples

\dontrun{
sql$report_data_dict()
}

Method `report_data()`

Read report data

Usage

EplusSql$report_data(
  key_value = NULL,
  name = NULL,
  year = NULL,
  tz = "UTC",
  case = "auto",
  all = FALSE,
  wide = FALSE,
  period = NULL,
  month = NULL,
  day = NULL,
  hour = NULL,
  minute = NULL,
  interval = NULL,
  simulation_days = NULL,
  day_type = NULL,
  environment_name = NULL
)

Arguments

key_value

A character vector to identify key values of the data. If NULL, all keys of that variable will be returned. key_value can also be data.frame that contains key_value and name columns. In this case, name argument in $report_data() is ignored. All available key_value for current simulation output can be obtained using $report_data_dict(). Default: NULL.

name

A character vector to identify names of the data. If NULL, all names of that variable will be returned. If key_value is a data.frame, name is ignored. All available name for current simulation output can be obtained using $report_data_dict(). Default: NULL.

year

Year of the date time in column datetime. If NULL, it will calculate a year value that meets the start day of week restriction for each environment. Default: NULL.

tz

Time zone of date time in column datetime. Default: "UTC".

case

A single string used to add a character column case in the returned results to indicate the case of this simulation. If NULL, no column is added. If "auto", the name of the IDF file without extension is used. Default: "auto".

all

If TRUE, extra columns are also included in the returned data.table::data.table().

wide

If TRUE, the output is formatted in the same way as standard EnergyPlus csv output file.

period

A Date or POSIXt vector used to specify which time period to return. The year value does not matter and only month, day, hour and minute value will be used when subsetting. If NULL, all time period of data is returned. Default: NULL.

month, day, hour, minute

Each is an integer vector for month, day, hour, minute subsetting of datetime column when querying on the SQL database. If NULL, no subsetting is performed on those components. All possible month, day, hour and minute can be obtained using $read_table("Time"). Default: NULL.

interval

An integer vector used to specify which interval length of report to extract. If NULL, all interval will be used. Default: NULL.

simulation_days

An integer vector to specify which simulation day data to extract. Note that this number resets after warmup and at the beginning of an environment period. All possible simulation_days can be obtained using $read_table("Time"). If NULL, all simulation days will be used. Default: NULL.

day_type

A character vector to specify which day type of data to extract. All possible day types are: Sunday, Monday, Tuesday, Wednesday, Thursday, Friday, Saturday, Holiday, SummerDesignDay, WinterDesignDay, CustomDay1, and CustomDay2. All possible values for current simulation output can be obtained using $read_table("Time"). A few grouped options are also provided:

"Weekday": All working days, i.e. from Monday to Friday
"Weekend": Saturday and Sunday
"DesignDay": Equivalent to "SummerDesignDay" plus "WinterDesignDay"
"CustomDay": CustomDay1 and CustomDay2
"SpecialDay": Equivalent to "DesignDay" plus "CustomDay"
"NormalDay": Equivalent to "Weekday" and "Weekend" plus "Holiday"

environment_name

A character vector to specify which environment data to extract. If NULL, all environment data are returned. Default: NULL. All possible environment_name for current simulation output can be obtained using:

$read_table("EnvironmentPeriods")

Details

$report_data() extracts the report data in a data.table::data.table() using key values, variable names and other specifications.

$report_data() can also directly take all or subset output from $report_data_dict() as input, and extract all data specified.

The returned column numbers varies depending on all argument.

all is FALSE, the returned data.table::data.table() has 6 columns:
- case: Simulation case specified using case argument
- datetime: The date time of simulation result
- key_value: Key name of the data
- name: Actual report data name
- units: The data units
- value: The data value
all is TRUE, besides columns described above, extra columns are also included:
- month: The month of reported date time
- day: The day of month of reported date time
- hour: The hour of reported date time
- minute: The minute of reported date time
- dst: Daylight saving time indicator. Possible values: 0 and 1
- interval: Length of reporting interval
- simulation_days: Day of simulation
- day_type: The type of day, e.g. Monday, Tuesday and etc.
- environment_period_index: The indices of environment.
- environment_name: A text string identifying the environment.
- is_meter: Whether report data is a meter data. Possible values: 0 and 1
- type: Nature of data type with respect to state. Possible values: Sum and Avg
- index_group: The report group, e.g. Zone, System
- timestep_type: Type of data timestep. Possible values: Zone and HVAC System
- reporting_frequency: The reporting frequency of the variable, e.g. HVAC System Timestep, Zone Timestep.
- schedule_name: Name of the the schedule that controls reporting frequency.

With the datetime column, it is quite straightforward to apply time-series analysis on the simulation output. However, another painful thing is that every simulation run period has its own Day of Week for Start Day. Randomly setting the year may result in a date time series that does not have the same start day of week as specified in the RunPeriod objects.

eplusr provides a simple solution for this. By setting year to NULL, which is the default behavior, eplusr will calculate a year value (from year 2017 backwards) for each run period that compliances with the start day of week restriction.

It is worth noting that EnergyPlus uses 24-hour clock system where 24 is only used to denote midnight at the end of a calendar day. In EnergyPlus output, "00:24:00" with a time interval being 15 mins represents a time period from "00:23:45" to "00:24:00", and similarly "00:15:00" represents a time period from "00:24:00" to "00:15:00" of the next day. This means that if current day is Friday, day of week rule applied in schedule time period "00:23:45" to "00:24:00" (presented as "00:24:00" in the output) is also Friday, but not Saturday. However, if you try to get the day of week of time "00:24:00" in R, you will get Saturday, but not Friday. This introduces inconsistency and may cause problems when doing data analysis considering day of week value.

With wide equals TRUE, $report_data() will format the simulation output in the same way as standard EnergyPlus csv output file. Sometimes this can be useful as there may be existing tools/workflows that depend on this format. When both wide and all are TRUE, columns of runperiod environment names and date time components are also returned, including: environment_period_index", "environment_name, simulation_days, datetime, month, day, hour, minute, day_type.

For convenience, input character arguments matching in $report_data() are case-insensitive.

Returns

A data.table::data.table().

Examples

\dontrun{
# read all report data
sql$report_data()

# specify output variables using report data dictionary
dict <- sql$report_data_dict()
sql$report_data(dict[units == "C"])

# specify output variables using 'key_value' and 'name'
sql$report_data("environment", "site outdoor air drybulb temperature")

# explicitly specify year value and time zone
sql$report_data(dict[1], year = 2020, tz = "Etc/GMT+8")

# explicitly specify case name
sql$report_data(dict[1], case = "example")

# get all possible columns
sql$report_data(dict[1], all = TRUE)

# return in a format that is similar as EnergyPlus CSV output
sql$report_data(dict[1], wide = TRUE)

# return in a format that is similar as EnergyPlus CSV output with
# extra columns
sql$report_data(dict[1], wide = TRUE, all = TRUE)

# only get data at the working hour on the first Monday
sql$report_data(dict[1], hour = 8:18, day_type = "monday", simulation_days = 1:7)

# only get specified run period data
sql$read_table("EnvironmentPeriods") # possible environment name
sql$report_data(dict[1], environment_name = "San Francisco Intl Ap CA USA TMY3 WMO#=724940")
# can also be done using 'environment_period_index' column
sql$report_data(dict[1], all = TRUE)[environment_period_index == 3L]
}

Method `tabular_data()`

Read tabular data

Usage

EplusSql$tabular_data(
  report_name = NULL,
  report_for = NULL,
  table_name = NULL,
  column_name = NULL,
  row_name = NULL,
  case = "auto",
  wide = FALSE,
  string_value = !wide
)

Arguments

report_name, report_for, table_name, column_name, row_name

Each is a character vector for subsetting when querying the SQL database. For the meaning of each argument, please see the description above.

case

wide

If TRUE, each table will be converted into the similar format as it is shown in EnergyPlus HTML output file. Default: FALSE.

string_value

Only applicable when wide is TRUE. If string_value is FALSE, instead of keeping all values as characters, values in possible numeric columns are converted into numbers. Default: the opposite of wide. Possible numeric columns indicate column that:

columns that have associated units
columns that contents numbers

Details

$tabular_data() extracts the tabular data in a data.table::data.table() using report, table, column and row name specifications. The returned data.table::data.table() has 9 columns:

case: Simulation case specified using case argument
index: Tabular data index
report_name: The name of the report that the record belongs to
report_for: The For text that is associated with the record
table_name: The name of the table that the record belongs to
column_name: The name of the column that the record belongs to
row_name: The name of the row that the record belongs to
units: The units of the record
value: The value of the record in string format by default.

For convenience, input character arguments matching in $tabular_data() are case-insensitive.

Returns

A data.table::data.table() with 9 columns (when wide is FALSE) or a named list of data.table::data.table()s where the names are the combination of report_name, report_for and table_name.

Examples

\dontrun{
# read all tabular data
sql$tabular_data()

# explicitly specify data you want
str(sql$tabular_data(
    report_name = "AnnualBuildingUtilityPerformanceSummary",
    table_name = "Site and Source Energy",
    column_name = "Total Energy",
    row_name = "Total Site Energy"
))

# get tabular data in wide format and coerce numeric values
str(sql$tabular_data(
    report_name = "AnnualBuildingUtilityPerformanceSummary",
    table_name = "Site and Source Energy",
    column_name = "Total Energy",
    row_name = "Total Site Energy",
    wide = TRUE, string_value = FALSE
))
}

Method `print()`

Print EplusSql object

Usage

EplusSql$print()

Details

$print() shows the core information of this EplusSql object, including the path of the EnergyPlus SQLite file, last modified time of the SQLite file and the path of the IDF file with the same name in the same folder.

Returns

The EplusSql object itself, invisibly.

Examples

\dontrun{
sql$print()
}

Examples


## ------------------------------------------------
## Method `EplusSql$new`
## ------------------------------------------------

if (FALSE) {
if (is_avail_eplus("8.8")) {
    idf_name <- "1ZoneUncontrolled.idf"
    epw_name <-  "USA_CA_San.Francisco.Intl.AP.724940_TMY3.epw"

    idf_path <- path_eplus_example("8.8", idf_name)
    epw_path <- path_eplus_weather("8.8", epw_name)

    # copy to tempdir and run the model
    idf <- read_idf(idf_path)
    idf$run(epw_path, tempdir(), echo = FALSE)

    # create from local file
    sql <- eplus_sql(file.path(tempdir(), "1ZoneUncontrolled.sql"))
}
}


## ------------------------------------------------
## Method `EplusSql$path`
## ------------------------------------------------

if (FALSE) {
# get path
sql$path()
}


## ------------------------------------------------
## Method `EplusSql$path_idf`
## ------------------------------------------------

if (FALSE) {
# get path
sql$path_idf()
}


## ------------------------------------------------
## Method `EplusSql$list_table`
## ------------------------------------------------

if (FALSE) {
sql$list_table()
}


## ------------------------------------------------
## Method `EplusSql$read_table`
## ------------------------------------------------

if (FALSE) {
# read a specific table
sql$read_table("Zones")
}


## ------------------------------------------------
## Method `EplusSql$report_data_dict`
## ------------------------------------------------

if (FALSE) {
sql$report_data_dict()
}


## ------------------------------------------------
## Method `EplusSql$report_data`
## ------------------------------------------------

if (FALSE) {
# read all report data
sql$report_data()

# specify output variables using report data dictionary
dict <- sql$report_data_dict()
sql$report_data(dict[units == "C"])

# specify output variables using 'key_value' and 'name'
sql$report_data("environment", "site outdoor air drybulb temperature")

# explicitly specify year value and time zone
sql$report_data(dict[1], year = 2020, tz = "Etc/GMT+8")

# explicitly specify case name
sql$report_data(dict[1], case = "example")

# get all possible columns
sql$report_data(dict[1], all = TRUE)

# return in a format that is similar as EnergyPlus CSV output
sql$report_data(dict[1], wide = TRUE)

# return in a format that is similar as EnergyPlus CSV output with
# extra columns
sql$report_data(dict[1], wide = TRUE, all = TRUE)

# only get data at the working hour on the first Monday
sql$report_data(dict[1], hour = 8:18, day_type = "monday", simulation_days = 1:7)

# only get specified run period data
sql$read_table("EnvironmentPeriods") # possible environment name
sql$report_data(dict[1], environment_name = "San Francisco Intl Ap CA USA TMY3 WMO#=724940")
# can also be done using 'environment_period_index' column
sql$report_data(dict[1], all = TRUE)[environment_period_index == 3L]
}


## ------------------------------------------------
## Method `EplusSql$tabular_data`
## ------------------------------------------------

if (FALSE) {
# read all tabular data
sql$tabular_data()

# explicitly specify data you want
str(sql$tabular_data(
    report_name = "AnnualBuildingUtilityPerformanceSummary",
    table_name = "Site and Source Energy",
    column_name = "Total Energy",
    row_name = "Total Site Energy"
))

# get tabular data in wide format and coerce numeric values
str(sql$tabular_data(
    report_name = "AnnualBuildingUtilityPerformanceSummary",
    table_name = "Site and Source Energy",
    column_name = "Total Energy",
    row_name = "Total Site Energy",
    wide = TRUE, string_value = FALSE
))
}


## ------------------------------------------------
## Method `EplusSql$print`
## ------------------------------------------------

if (FALSE) {
sql$print()
}

Retrieve Simulation Outputs Using EnergyPlus SQLite Output File

Details

Author

Methods

Public methods

Method new()

Usage

Arguments

Returns

Examples

Method path()

Usage

Details

Returns

Examples

Method path_idf()

Usage

Details

Returns

Examples

Method list_table()

Usage

Details

Returns

Examples

Method read_table()

Usage

Arguments

Details

Returns

Examples

Method report_data_dict()

Usage

Details

Returns

Examples

Method report_data()

Usage

Arguments

Details

Returns

Examples

Method tabular_data()

Usage

Arguments

Details

Returns

Examples

Method print()

Usage

Details

Returns

Examples

Examples

Method `new()`

Method `path()`

Method `path_idf()`

Method `list_table()`

Method `read_table()`

Method `report_data_dict()`

Method `report_data()`

Method `tabular_data()`

Method `print()`