Skip to content

TSTool / Command / CreateTimeSeriesEventTable


Overview

This command is under development and is experimental.

The CreateTimeSeriesEventTable command creates an event table associated with time series, which is a join of events and time series data. A general goal is to associate event data with time series to allow further analysis and visualization, for example to highlight time series peaks or show a period of drought or flood.

There are currently two uses for the command:

  1. Analyze time series data to identify events:
    • peaks are determined from data
  2. Associate historical events with time series:
    • time series properties, such as county or basin identifier, are used to associate the time series with historical events

Analyze Time Series for Events

This functionality is under development and is experimental.

If an existing event table is not specified with TableID parameter, then the time series will analyzed to create an event table that includes peak value events. Currently, all command parameters and output format uses default values. A peak event is defined as follows. Enhancements to the analysis may implement additional refinements to define events.

  • The first value in the event is a low point:
    • the previous value is >= the starting value
    • the next value is > the starting value
  • The last value in the event is a low point:
    • the previous value is > the ending value
    • the next value is >= the ending value
  • The peak may include repeated values:
    • Currently the repeated values must have exactly the same value.
    • In the future repeated values may be allowed to have some variability.
  • All values after start but before the peak are greater than the previous value.
  • All values after the peak but before the end are less than the previous value (no flat sequences are allowed).
  • Event as part of longer slow increase:
    • in the future, enhancements may be implemented to require events to fit within a limited time period
  • Event magnitude to avoid interpreting noise as events:
    • the maximum difference between peak and starting value, and peak and ending value, must be >= 1% of the starting value
    • this criteria may identify too many local peaks if the time series base value hovers around a value that is relatively large compared to the event local size

The output event table has the following columns. If an extreme value is repeated, the extreme date/time and value are for the first repeated value.

Event Table Columns

Property                         Description
TSID Time series identifier corresponding to the event.
EventStartDateTime Starting date/time for the event.
EventStartValue Starting time series value for the event.
EventExtremeDateTime Extreme (peak) date/time for the event.
EventExtremeValue Extreme (peak) time series value for the event.
EventEndDateTime Ending date/time for the event.
EventEndtValue Ending time series value for the event.

The following graph illustrates the results of an analysis. The blue markers indicate the location of events for a test dataset, marked at the starting y-value. The green markers indicate the location of peaks, marked at the first peak value in each event.

Time Series event analysis results

Time Series Event Analysis Results (see full-size image)

Associate Historical Events with Time Series

Historical data may be available in a table, for example to indicate periods for floods and droughts, or events that occurred at a specific date/time, such as new infrastructure or policy. Such information can be "coded" to apply to one or more regions, such as county or river basin. Time series spatial properties such as county and basin may be available in time series data or can be determined from spatial data analysis based on the longitude and latitude for a time series site.

The event table can therefore be related to the time series to create a joined time series event table. The event table can then be used for additional analysis and visualization, for example to convert the table into irregular interval time series or annotations that can be displayed on time series data graphs.

Time series events have the following properties, which can be included in the event table.

Event Properties

Property                         Description
EventID Unique identifier for the event.
EventType Event type for the event data (e.g., natural disasters such as drought and flood, economic events, political or legal events).
EventStart Starting date/time for the event.
EventEnd Ending date/time for the event.
EventLocationType The type of location (e.g., County, State), used to join the event to time series by location.
EventLocationID The location identifier (e.g., county name, state abbreviation), used to join the event to time series by location.
EventLabel Short string suitable for labeling a graph or map.
EventDescription Longer string suitable for a narrative description about the event.
TSID Time series identifier to uniquely identify the matching time series.

Basic event data are associated with time series using location data to create a “time series event”, which can then be used to annotate time series graph products. The following figure illustrates event data in an Excel worksheet. Location data can be specified in multiple columns, as shown in the far right of the figure, where non-blank values indicate the locations that are applicable for a location type.

Event data before relating to time series InputTable

Event Data before Relating to Time Series (see full-size image)

Command Editor

The command is available in the following TSTool menu:

  • Commands(Table) / Table, Time Series Processing

The following dialog is used to edit the command and illustrates the syntax of the command (in this case illustrating how an input event table can be processed to create a time series event table). It is envisioned that additional methods will be enabled in the future to create time series events, for example to estimate extreme events from data.

CreateTimeSeriesEventTable command editor

CreateTimeSeriesEventTable Command Editor (see full-size image)

Command Syntax

The command syntax is as follows:

CreateTimeSeriesEventTable(Parameter="Value",...)

Command Parameters (General)

Parameter             Description Default                           
TSList Indicates the list of time series to be processed, one of:
  • AllMatchingTSID – all time series that match the TSID (single TSID or TSID with wildcards) will be processed.
  • AllTS – all time series before the command.
  • EnsembleID – all time series in the ensemble will be processed (see the EnsembleID parameter).
  • FirstMatchingTSID – the first time series that matches the TSID (single TSID or TSID with wildcards) will be processed.
  • LastMatchingTSID – the last time series that matches the TSID (single TSID or TSID with wildcards) will be processed.
  • SelectedTS – the time series are those selected with the SelectTimeSeries command.
AllTS
TSID The time series identifier or alias for the time series to be processed, using the * wildcard character to match multiple time series. Can be specified using ${Property}. Required if TSList=*TSID
EnsembleID The ensemble to be processed, if processing an ensemble. Can be specified using ${Property}. Required if TSList=*EnsembleID

Command Parameters (Create events from existing table)

Parameter                                                   Description Default                           
TimeSeriesLocations
required
A dictionary of event location type and time series identifier format specifiers:
LocationType1:Specifier1,
LocationType2:Specifier2
The specifiers can use the % formats that are commonly used for time series alias parameters (e.g., %L is location identifier) and also the ${TS:property} syntax that indicates general time series properties. For example, time series location properties are often read when the time series is read, or can be set later with the SetTimeSeriesPropertiesFromTable command.
None – must be specified.
TableID
required
The identifier for the original event table (which has not been matched with time series). None – must be specified.
IncludeColumns The names of columns in the TableID table to copy, separated by commas. The required column tables listed below are always copied, but additional columns can be specified. This parameter is not enabled. Only copy the required columns.
InputTableEventIDColumn
required
The name of the column in the TableID table containing event identifiers. None – must be specified.
InputTableEventTypeColumn
required
The name of the column in the TableID table containing event types. None – must be specified.
IncludeInputTableEventTypes The event types from the TableID table that should be included when processing (others will be ignored). Include all event types.
InputTableEventStartColumn
required
The name of the column in the TableID table containing event start date/time. None – must be specified.
InputTableEventEndColumn
required
The name of the column in the TableID table containing event end date/time. None – must be specified.
InputTableEventLocationColumns
required
A dictionary of location types mapped to column names in the TableID table, using syntax:
LocationType1:Column1,
LocationType2:Column2
None – must be specified.
InputTableEventLabelColumn
required
The name of the column in the TableID table containing event labels. None – must be specified.
InputTableEventDescriptionColumn
required
The name of the column in the TableID table containing event descriptions. None – must be specified.
NewTableID
required
The identifier for the new time series event table, which will be a join of the TableID table and time series identifier column specified by the OutputTableTSIDColumn. None – must be specified.
OutputTableTSIDColumn
required
The name of the column in the NewTableID table containing event types. None – must be specified.
OutputTableTSIDFormat
required
The format specifier to be applied to the time series identifier to create the value for the OutputTableTSIDColumn. None – must be specified.

Examples

See the automated tests.

The following figure illustrates the result of processing the input event table with a time series that has property basin=South Platte and division=1, which results in 21 of the 55 input rows being used in the output time series event table. The results can then be used when processing time series products to annotate the graphs (see the ProcessTSProduct command).

Event data after relating to time series

Event Data after Relating to Time Series (see full-size image)

Troubleshooting

See the main TSTool Troubleshooting documentation.

See Also