# Time-aware wrangling
URL: https://docs.datarobot.com/en/docs/workbench/nxt-workbench/dataprep/wrangle-data/build-recipe/ts-wrangling.html

> Manually or automatically create a derivation plan for time series data.

# Time-aware data wrangling

Feature engineering is a major component of successfully addressing use case requirements. By wrangling—creating recipes of operations and applying them first to a sample and then, when verified, to a full dataset—time-aware data, you can perform time series feature engineering during the data preparation phase. Executing operations like lags and rolling statistics on input data provides control over which time-based features are generated before modeling. By reviewing the preview that results from adding both time-aware and non-time-aware operations, you can adjust before publishing, preventing the need to rerun modeling if what would otherwise be done automatically doesn't fit your use case.

This page describes the workflow specific to time-aware wrangling. See also the [full data wrangling documentation](https://docs.datarobot.com/en/docs/workbench/nxt-workbench/dataprep/wrangle-data/index.html) for more information.

## Basic workflow

The following describes the basic workflow:

> [!NOTE] Note
> There is no validation of settings as configuration of operations progresses. This is because partitioning, where validation occurs in conjunction with other experiment settings, happens later in the process.

1. Connect to Snowflake, Databricks, or BigQuery using a configured data connection . It is important that once you have connected to your data you use the Wrangle option and not Add to Use Case . If you add, the sample will come in as a static dataset and will not eligible for wrangling.
2. From Preview settings , configure a live sample.
3. When sampling settings are complete, click Resample to apply the settings.
4. Add operations from the Recipes panel.

## Set the sampling method

By default, DataRobot retrieves 10000 random rows for the live sample.

> [!NOTE] Note
> When setting the sampling method, the data sample you configure is used only inside wrangling; it is not applied on the full data until the recipe is published.

To use time-aware wrangling, and more specifically, to configure DataRobot to create a derivation plan for your sample, you must use the date/time sampling method. That is, while you can apply lags and rolling statistics to non-date/time samples, you cannot take advantage of the automation provided by the [suggested derivation plan](https://docs.datarobot.com/en/docs/workbench/nxt-workbench/dataprep/wrangle-data/build-recipe/ts-wrangling.html#derive-time-series-features).

To configure date/time sampling, open Preview settings and set the Sampling method to date/time.

Then, complete the date/time-specific fields described in the table below:

| Field | Description |
| --- | --- |
| Sampling method | Set the method to date/time to create a sample that contains the specified latest/earliest rows, ordered by date/time feature. If you choose Random or First-N Rows, as described here, you will not have access to the automated time series feature derivation plan suggestions. |
| Number of rows | Specify the number of rows to be retrieved from the source data. Enter a value under 10000. Note that the more rows you retrieve, the longer it will take to render the live sample. |
| Ordering feature | Select the name of the column that contains the primary date/time feature that DataRobot will use to create a sample. Use the same feature you intend to use as the ordering feature when configuring date/time-aware feature transformations. |
| Strategy | Set which rows are pulled from the sample, either the earliest or the latest. Earliest is the better selection if DataRobot will be suggesting a derivation plan. This is because when the plan is generated, target values are used in the feature reduction process. Given that, using the earliest rows to create modeling data minimizes the likelihood that those rows will be part of validation or holdout later. |
| Series identifier column | Enter the name of the column that multiseries modeling will use to identify multiple, individual time series datasets. The identifier indicates which series each row belongs to. |
| Selected series | (Optional) Select one or more series, within the multiseries data, that will be present in the sample. |

When all fields are complete, click Resample. The live sample updates to display the data selected based on the configuration. You can now add operations.

## Add operations

Operations are the transformations that will be applied to the source data to prepare it for modeling. The sections below describe the time-aware operations; see the [operation reference](https://docs.datarobot.com/en/docs/workbench/nxt-workbench/dataprep/wrangle-data/build-recipe/add-operation.html) for information on other operations and working with operations within a recipe. Click + Add operations to begin configuring transformations.

The following time-aware-specific operations are available:

| Operation | Description |
| --- | --- |
| Derive time series features | Generates new time-aware features. This operation substitutes the input dataset with an output dataset that is expanded by the specified forecast distances for each operation. |
| Lag features | Sets the time periods in the past to create features from, adding one or more features to the dataset. |
| Derive rolling statistics (numeric and categorical) | Calculates statistics for a rolling time window. This operation can result in one or more features added to the dataset. |

When all fields are complete for a given operation, click +Add to recipe. The preview updates based on your changes. A summary of each operation is provided in the Recipe panel.

## Derive time series features

When working with time-aware wrangling, the data is ordered by the specified date column (the "ordering feature") and partitioned by series ID column, if applicable. The feature derivation windows (FDW) are defined in terms of rows. In those windows, the window start is excluded and the end is included. So, the dataset is "reordered" according to date and then, for example, if FDW=5, five rows are used to derive features.

The Derive time series features operation generates new time series features. To start, you supply fundamental information and DataRobot then expands the data according to forecast distances, adds known in advance columns (if specified) and naive baseline features, and then replaces the original sample. To create new features, you can have DataRobot suggest a derivation plan and automatically create new features, manually add tasks for up to 200 features, or apply a hybrid approach.

> [!NOTE] Note
> If you manually add features and then request and execute a suggested derivation plan,  the manual additions will be overwritten by the auto-generated plan.

### Configure parameters

To configure parameters that set up the ability to use the automated derivation plan, after setting the sampling method to date/time, select the Derive time series features operation. Alternatively, if the sampling method is random or N-rows, you can configure time-aware wrangling parameters but cannot take advantage of the automation.

The following table describes the parameters to configure:

| Field | Description |
| --- | --- |
| Target feature | Set the target feature, which will be used for generating naive baseline (the last known value, possibly seasonally) features during feature derivation. The target feature must be numeric. |
| Ordering feature | Select the name of the column that contains the primary date/time feature that DataRobot will use to order rows during feature transformation. |
| Series identifier column | Enter the name of the column that multiseries modeling will use to identify multiple, individual time series datasets. The identifier indicates which series each row belongs to. |
| Forecast distances | Set the relative position(s) that determines how many rows ahead to predict from each position. Enter one or more integers. |
| Naive baseline periodicities (rows) | Set one or more integers, which represent the periodicities, expressed as a number of rows. These values are used to calculate naive baseline features. Periodicities assume that the value at a given point in the future will be the same as the last observed value from the same period in the cycle. |
| Known in advance features | (Optional) Specify any features for which you know the value in advance. This maintains the feature's actual value, making feature value for the prediction date a predictor. |
| User-defined function settings |  |
| Rolling median user-defined function (numeric) | (Optional, advanced) Specify the path to a helper function that can improve performance. |
| Rolling most frequent user-defined function (categorical) | (Optional, advanced) Specify the path to a helper function that can improve performance. |

Note that all setting are summarized in the right panel summary. When all fields are complete and correct, click Next.

### Features for derivation

After clicking Next, the Features for derivation panel opens.

The following options are available for creating derived time series features:

|  | Method | Description | Sampling |
| --- | --- | --- | --- |
| (1) | Suggest derivation plan | Have DataRobot suggest a derivation plan and automatically create new features. | Requires date/time |
| (2) | Add features manually | Add up to 200 features and assign tasks. | Any |
| (1) | Hybrid approach | Use the suggested derivation plan and then manually add more features to the results. | Requires date/time (for the automated plan) |

### Suggest derivation plan

Use the Suggest derivation plan option when you have set the sampling method to date/time and want DataRobot to create lags and rolling statistics based on the configuration settings you supply. Click the option and complete the fields:

| Field | Description |
| --- | --- |
| Feature derivation windows* | Configures the number of rows (periods of data) that DataRobot uses to derive features. Enter one or more values. |
| Features with minimal derivation | (Optional) Specify features for which only a single lag (the first) is created. You cannot exclude the primary date/time feature or the series ID. |
| Maximum number of lags per feature | (Optional) Specify the maximum number of lags created by the derivation plan. |
| Feature reduction threshold | Set a feature reduction threshold that selects the most impactful features, which serves a the threshold for feature reduction. For example, 0.9, the default, means that features which cumulatively reach 90% of importance are returned. Importance is derived based on SHAP impact calculations. |
| Exclude low information features | When enabled, the default, features must pass a "reasonableness" check that determines whether they contain information useful for building a generalizable model. |

* The forecast point is part of a feature derivation window; the feature derivation window end is always zero. For this reason, there is no [blind history gap](https://docs.datarobot.com/en/docs/classic-ui/modeling/time/ts-adv-modeling/ts-customization.html#blind-history-example) in time series data wrangling.

Once all fields are completed, select Create plan. DataRobot applies operations (tasks) for features.

Click on a feature name in the left panel to see the tasks created reflected in the middle panel. From there, click Add derivation task to add more tasks to the feature. Click Delete task to remove any tasks you do not want eventually applied to the modeling dataset. In the right panel, tasks-per-feature are added to the summary.

Review the plan:

- If you are satisfied with the derivation plan, click Add to recipe . The live sample updates after DataRobot retrieves a new sample from the data source and applies the operations, allowing you to review the transformation in realtime.
- If you are not satisfied, click Suggest derivation plan and reset the configuration to different values. The original output will be overwritten by the output from the new plan.

### Add features manually

To add features manually, regardless of the sampling method, [configure parameters](https://docs.datarobot.com/en/docs/workbench/nxt-workbench/dataprep/wrangle-data/build-recipe/ts-wrangling.html#configure-parameters) and the toggle on the option to Add features manually. Click in the entry box to select a feature to derive and then click Add.

The feature is added to the list in the left panel and derivation configuration becomes available in the center panel. Or, click on any previously added feature in the left panel to open the derivation configuration, for example, if you want to revisit the configuration, and add or remove tasks.

To add a task, from the Task dropdown, make a selection. Each task has its own configuration fields:

| Task | Field |
| --- | --- |
| Lag |  |
| Lags to create | Enter one or more integers, representing the lag order to create for the named feature. |
| Numeric rolling statistics |  |
| Feature derivation window (rows) | Set the number rows that each rolling window contains. Statistics are calculated in a window that includes the current row. |
| Statistical methods | Set the method: Average, Median, Standard deviation, Minimum, or Maximum. |
| Categorical rolling statistics |  |
| Feature derivation window (rows) | Set the number rows that each rolling window contains. Statistics are calculated in a window that includes the current row. |
| Statistical methods | This field is preset to Most frequent and cannot be changed. |

Click Add derivation task to add more tasks or delete task to remove an individual task from the derivation configuration. As you work with features, DataRobot reports the number of tasks (including incompletely configured tasks) in the right and left panels.

When all derivation tasks are configured for a feature, click Add to recipe. The live sample updates after DataRobot retrieves a new sample from the data source and applies the operations, allowing you to review the transformation in realtime.

### Hybrid approach

With sampling set to date/time, you can use a hybrid approach that leverages the automation of Suggest derivation plan while allowing you to add features that may have been missed due to your configuration. You can also add tasks to features that were transformed.

To add new features after the automated plan creates transformations, either:

- Toggle on Add features manually to search for features and then configure tasks.
- Click a feature in the left panel to show the task configuration and click Add derivation task to set new tasks for that feature.

When all feature transformation instructions are complete, click Add to recipe. The live sample updates after DataRobot retrieves a new sample from the data source and applies the operations, allowing you to review the transformation in realtime.

## Lag features

A lag represents a specific time period between an occurrence and its impact and is important for capturing delayed relationships between features in time series data. The lag measurement (e.g., 3, 7) is implemented based on the time step that is detected in the data. A lag is calculated relative to the current row—the first lag is the previous row, the second lag is two rows back, and so on. Click Lag features in the right-hand operations panel to set the lag configuration.

The following table describes the fields:

| Field | Description |
| --- | --- |
| Feature name | Set the name of the feature to lag. |
| Ordering feature | Select the name of the column that contains the primary date/time feature that DataRobot will use to order rows during feature transformation. |
| Series identifier column | Enter the name of the column that multiseries modeling will use to identify multiple, individual time series datasets. The identifier indicates which series each row belongs to. |
| Lags to create | Enter one or more integers, representing the lag order to create for the named feature. |

## Derive rolling statistics

Rolling statistics allows you to calculate statistics for a rolling window that is comprised of a specified number of rows. They are added as user-defined functions (UDFs) and can be created for both numeric and categorical features. You can configure these helper functions from two places:

**Add operation:**
Configure from the panel on the right. For example, for a numeric:

[https://docs.datarobot.com/en/docs/images/ts-wrangle-6.png](https://docs.datarobot.com/en/docs/images/ts-wrangle-6.png)

**Derive time series features:**
As a setting in the configuration:

[https://docs.datarobot.com/en/docs/images/ts-wrangle-6a.png](https://docs.datarobot.com/en/docs/images/ts-wrangle-6a.png)


Output of the operation is:

- Numeric: One or several columns, depending on the statistical method specified.
- Categorical: One column, calculated in a window including the current row

The following table describes the fields for both numeric and categorical rolling statistics:

| Field | Description |
| --- | --- |
| Feature name | Set the name of the feature them |
| Ordering feature | Select the name of the column that contains the primary date/time feature that DataRobot will use to order rows during feature transformation. |
| Series identifier column | Enter the name of the column that multiseries modeling will use to identify multiple, individual time series datasets. The identifier indicates which series each row belongs to. |
| Feature derivation window (rows) | Set the number rows that each rolling window contains. Statistics are calculated in a window that includes the current row. |
| Statistical methods (numeric) | Set the method: Average, Median, Standard deviation, Minimum, or Maximum. |
| Statistical methods (categorical) | This field is preset to Most frequent and cannot be changed. |
| User-defined function settings | Specify the path to a UDF helper function, either DataRobot-generated or custom. |

### Specify a UDF

When using median statistics (for numeric features) or most frequent statistics (for categorical features), UDFs are essential to improving query performance and DataRobot recommends using them when wrangling with time series operations. They generate SQL that is smaller and faster, without needing additional joins to create windows. Create functions, either DataRobot-generated or linked via URL, and they will be added to the recipe’s data store. Or, store and re-use them. SQL scripts with functions are available on [GitHub](https://github.com/datarobot-community/wrangling_helpers). The scripts include UDFs/aggregations that compute rolling median and most frequent statistics for different databases.

To add a function for numeric or categorical rolling statistics, toggle the function option:

Once enabled, the option to specify the function origin becomes available.

#### Saved functions

Choose Saved functions to select from DataRobot-generated functions previously added to this recipe’s data source. If no functions have been saved, use the New function option.

#### New function

When creating a new function, you have the option create automatically or manually:

| Method | Description |
| --- | --- |
| Automatic | DataRobot generates the UDF and adds it to the recipe’s data source. |
| Manual | Provide the path to a custom function that has been added to the schema database. For function examples, see wrangling helper scripts on GitHub. To use the sample UDFs, download the scripts locally, modify as necessary, and then provide them in the Function path field, in the format catalog.schema.udf_name. |

## Feature considerations

- For time-aware wrangling, only Snowflake, Databricks, and BigQuery connections are generally available. Postgres connections and DataRobot Data Registry datasets are currently preview features.
- The maximum number of output features is 200.
- Floats are not allowed for multiseries features.
- The date/time column values must be a date, a timestamp, or a timestamp with time zone included.
- Only regression experiments (numeric targets) are supported.
- Only one derivation plan is allowed. You can add features manually to the plan output if more are needed.
- Windows defined in terms of time units—for example, days and minutes—are not supported.
- There is no validation of the data quality on the input dataset.

## Next steps

From here, you can:

- Publish the recipe to the data source, generating a new output dataset.

---
