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
orTableTSIDFormat
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 optionallyTableTSIDFormat
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:
- 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.
- 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. - 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
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:
|
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:
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:
|
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
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
Command Editor to Create Single Data Column Table (see also the full-size image)
The resulting table is as shown in the following figure
Single Data Column Table (see also the full-size image)
Troubleshooting
See Also
SortTable
commandTableToTimeSeries
commandWriteTableToDelimitedFile
commandWriteTimeSeriesToDataStream
commandWriteTableToHTML
command