Skip to content

TSTool / Command / TimeSeriesToTable


Overview

The TimeSeriesToTable command copies one or more time series into a table. This command is useful when performing table analysis processing and outputting table formats (e.g., with the WriteTableToDelimitedFile or WriteTableToHTML commands). The command can be configured to output one of two table forms:

  • Each time series in a separate column, with shared date/time column:
    • The time series must be regular interval (no irregular interval time series) and the intervals must match in order to allow alignment of the date/times.
    • Do not specify the TableTSIDColumn or TableTSIDFormat parameters.
  • All time series values in a single column (useful for converting time series to a stream of data for loading into a database)
    • Any interval is allowed although mixing time series of varying precision is discouraged.
    • Specify the TableTSIDColumn and optionally TableTSIDFormat parameters.
    • See also the WriteTimeSeriesToDataStream command.

Time series can be appended to an existing table. If time series are being appended in multi-column mode (one time series per column), the following checks are done to align the time series into the existing table:

  1. If the output start and output end are specified, the specified period is used to write. If not specified, an overlapping period of the time series being written is determined. The first date/time in this period is matched with an existing date/time in the date/time column. If the time series being written do not overlap, new records are added to fill out the time sequence. Once the starting row is established, the time series are written as a “block” within the existing table.
  2. The date/time values must be sequential. For example, using a SortTable command prior to this command that results in date/time column values being reordered is not supported because it would be a major performance penalty to search the date/time column when setting each time series data value. A warning will be generated if the date/time column is determined to be out of order.
  3. As an additional check, when adding time series to an existing table if the date/time column value does not equal the time series date/time, a warning will be generated.

Command Editor

The following dialog is used to edit the command and illustrates the syntax of the command when writing a multi-column data table while also outputting data flags. Note that the value columns can be specified using time series properties.

TimeSeriesToTable

TimeSeriesToTable Command Editor to Create Multi-Column Data Table (see also the full-size image)

Command Syntax

The command syntax is as follows:

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

Command Parameters

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
TableID
required
The identifier for the table to copy data into (or the identifier for the new table to create if IfTableNotFound=Create). Can be specified using processor ${Property}. None – must be specified.
DateTimeColumn
required
The table column name to receive date/time information. Can be specified using processor ${Property}. None – must be specified.
TableTSIDColumn For single-column output, the name of the column in the table for time series identifier information. The format of the identifier can be specified using the TableTSIDFormat parameter. Can be specified using processor ${Property}. Optional – if specified will indicate single-column output.
TableTSIDFormat For single-column output, indicates how to format the time series identifier that is inserted in the column specified by the TableTSIDColumn parameter. Can be specified using processor ${Property}. Optional – if not specified the alias or full TSID will be used.
IncludeMissingValues For single-column output, indicates whether missing values should be transferred to the table. This is useful to screen out missing values from sparse time series. True
ValueColumn The data value column name(s) to receive time series data, specified as follows:
  • Multiple names separated by a comma.
  • Time series property format specifiers, available in a list of choices. These specifiers are consistent with other commands and the legend formatter in the graphing tool.
  • If a literal string is specified with multi-column output, names for columns 2+ will be generated by adding a sequential number to ValueColumn.

Can be specified using processor ${Property}.
None – must be specified.
OutputPrecision The number of digits after the decimal to use for data values in the output table. 2 (in the future may auto-detect from units)
FlagColumn The data flag column name(s) to receive time series flags, specified using the same syntax as ValueColumn. A blank in the list will result in no transfer of flags for the specific time series. Can be specified using processor ${Property}. Do not output flags to the table.
OutputStart The starting date/time for the copy. Can be specified using processor ${Property}. Available period.
OutputEnd The ending date/time for the copy. Can be specified using processor ${Property}. Available period.
OutputWindowStart The calendar date/time for the output start within each year. Specify using the format MM, MM-DD, MM-DD hh, or MM-DD hh:mm, consistent with the time series interval precision. A year of 2000 will be used internally to parse the date/time. Use this parameter to limit data processing within the year, for example to output only a single month or a season. Can be specified using processor ${Property} by using the text field below the date editor. Output the full year.
OutputWindowEnd Specify date/time for the output end within each year. See OutputWindowStart for details. Can be specified using processor ${Property}. Output the full year.
IfTableNotFound Indicate action if the table identifier is not matched, one of:
  • Create – create a new table
  • Warn – warn that the table was not matched
Warn

Examples

See the automated tests.

Example for Multi-Column Output Data Table

A sample command file is as follows:

# Test copying annual time series to a table, and also create the table
StartLog(LogFile="Results/Test_TimeSeriesToTable_Year_Create.TSTool.log")
NewPatternTimeSeries(Alias=”ts1”,NewTSID="ts1..Flow.Year",SetStart="1960",SetEnd="2000",Units="ACFT",PatternValues="1,2,5,8,,20")
NewPatternTimeSeries(Alias=”ts2”,NewTSID="ts2..Flow.Year",SetStart="1950",SetEnd="2005",Units="ACFT",PatternValues="2,4,10,16,,40")
TimeSeriesToTable(TableID=TestTable,DateTimeColumn=Year,ValueColumn=%L-%T,FlagColumn=”%L-%T-flag”,IfTableNotFound="Create")
# Generate the results.
WriteTableToDelimitedFile(TableID="TestTable",OutputFile="Results\Test_TimeSeriesToTable_Year_Create_out.csv")

The resulting table will be listed in the Tables area of the TSTool interface and clicking on the TestTable identifier will display the table similar to the following:

TimeSeriesToTable results

TimeSeriesToTable Multi-Column Data Table (see also the full-size image)

Example for Single Column Output Data Table

The following example illustrates how to create a single data column table. Because a single column is being used for data, the data value and corresponding data flag column names are specified literally (not as time series properties). The column and format for the TSID also must be specified.

TimeSeriesToTable Single

TimeSeriesToTable Command Editor to Create Single Data Column Table (see also the full-size image)

The resulting table is as shown in the following figure

TimeSeriesToTable Single2

Single Data Column Table (see also the full-size image)

Troubleshooting

See Also