# Create time series data with Pipeline Builder

The pipeline you will create with this guide will generate [time series data](../time-series-concepts-glossary/index.md#time-series-object-type-backing-dataset) that backs a [time series sync](../time-series-concepts-glossary/index.md#time-series-sync) to associate with [time series properties](../time-series-concepts-glossary/index.md#time-series-property-tsp) on the `Carrier`, `Route`, and `Airport` object types to create new [time series objects](../time-series-concepts-glossary/index.md#time-series-object-type). This pipeline involves a more complex set up than standard mappings from time series datasets to time series properties, as we will make calculations on non-time series data to generate time series data. Review our [Pipeline Builder documentation](../../pipeline-builder/overview/index.md) for information on general pipeline guidance.

The flight dataset we are working with includes the following columns that we will use to create time series properties:

* **flight\_id:** `string` | A unique string to identify the flight and every row in the dataset.
* **date:** `date` | The date the flight took place.
* **destination\_airport\_id:** `string` | The string to identify the destination airport.
* **airline\_id:** `string` | The ID of the airline carrier.
* **origin\_airport\_id:** `string` | The ID of the origin airport.
* **dep\_delay:** `integer` | The number of minutes the departure was delayed.
* **arr\_delay:** `integer` | The number of minutes the arrival was delayed.
* **route\_id:** `string`| A unique string to identify the route.

The pipeline at the end of this guide will look like this:

![Overview of final time series pipeline](../../../assets/abe6d92ef8a798b77e73a7ebea84746a964bd7ffcc8c0bc8c556131577d48dfa.webp)

## Part I: Generate time series data

Using the same flights dataset that is used to back the `Flights` object type, we can perform some aggregation transformations and generate time series data based on flight metrics. This step is not necessary if you have time series data coming into Foundry from a historian or edge sensor. You can move on to [generate a time series sync](#part-ii-create-the-time-series-sync).

### 1. Apply transforms to `Carrier` and `Route` object types

From the flights dataset, apply transforms using the steps below. You will do this for both the `Carrier` and `Route` object types.

#### Aggregate the data

Use the aggregate transform to group by date and ID of the object, (in this case, using the `route_id` for the `Route` object type; you will separately need to do the same using `airline_id` for the `Carrier` object type), and calculate average arrival delays, average departure delays, and daily flight counts.

![The aggregate transform board in Pipeline Builder.](../../../assets/8873b97b0a62796e297e3151f82c6fd6704326bf20c15ca90b41b4acb8aeca2e.webp)

After aggregating, the dataset should preview with the following schema:

| route\_id   | date       | daily\_avg\_dep\_delay | daily\_avg\_arr\_delay | daily\_count\_of\_flights |
| --------   | ---------- | ------------------- | ------------------- | ---------------------- |
| ATL -> SFO | 2023-06-12 | 33.4545454545450000 | 40.0000000000000000 | 11                     |
| ATL -> FLL | 2023-08-24 | 29.7272727272720000 | 19.4090909090909100 | 22                     |
| ATL -> TVC | 2023-07-05 | -8.0000000000000000 | -8.0000000000000000 | 1                      |

#### Cast to a new data type

To use this new data as a time series, we must create a timestamp type column. To do this, use the cast transform to cast the `date` column to a timestamp type column. We will also soon apply an unpivot transform to merge `daily_avg_dep_delay`, `daily_avg_arr_delay`, and `daily_count_of_flights` values into one column. Since this function requires that all values be of the same data type, we must also cast our daily count of flights metric to a double type (the same data type as the average delay metrics).

![The cast board in Pipeline Builder, showing a cast to timestamp and a cast to double](../../../assets/1c8b7886bdf537e36b90476a62a0bdc6a50e8d7394a6e071e96f32235161ec26.webp)

#### Unpivot to merge time series values

Since this dataset contains time series data in different columns, we must use an unpivot transform to merge it into one value column so the data can match the required schema for a time series sync, as shown below:

* **series ID:** `string` | The series ID for the set of timestamp and value pairs referred to by a TSP, which must match the TSP's series ID.
* **timestamp:** `timestamp` or `long` | The time at which the quantity is measured.
* **value:** `integer`, `float`, `double`, `string` | The value of the quantity at the point that it is measured. A string type indicates a categorical time series; each categorical time series can have, at most, 10,000 unique variants.

The unpivot transform shown below places values for `daily_avg_dep_delay`, `daily_avg_arr_delay`, and `daily_count_of_flights` into the same `series_value` column. Those original column names are outputs to the new `series_name` column that will be used in the `series_id`.

![The unpivot transform board in Pipeline Builder](../../../assets/9378aaa40675fe05d2c5e531fb57fe4ffe16048529adef80ce25cc19bbf21e9d.webp)

The dataset schema should now appear as follows:

| series\_name            | series\_value        | route\_id   | date                     |
| ---------------------- | ------------------- | ---------- | ------------------------ |
| daily\_avg\_dep\_delay    | 33.4545454545450000 | ATL -> SFO | 2023-06-12T00:00:00.000Z |
| daily\_avg\_arr\_delay    | 40.0000000000000000 | ATL -> SFO | 2023-06-12T00:00:00.000Z |
| daily\_count\_of\_flights | 11.0000000000000000 | ATL -> SFO | 2023-06-12T00:00:00.000Z |

#### Concatenate string values to create the series ID

Now, we can use the concatenate strings transform to create the series ID (the identifier for the associated time series values). Use the transform to combine the `series_name` (what each sensor represents) with the primary key of each object.

![The concatenate strings transform board in Pipeline Builder.](../../../assets/e1d6b8d9ab01a3e6009cf286e298b3a28b6b6c57fc7ce7584fcdeea7fef9ae12.webp)

| series\_id                        | series\_name            | series\_value        | route\_id   | date                     |
| -------------------------------- | ---------------------- | ------------------- | --------   | ------------------------ |
| CMH -> IAH\_daily\_avg\_dep\_delay   | daily\_avg\_dep\_delay    | 33.4545454545450000 | ATL -> SFO | 2023-06-12T00:00:00.000Z |
| CMH -> IAH\_daily\_avg\_arr\_delay   | daily\_avg\_arr\_delay    | 40.0000000000000000 | ATL -> SFO | 2023-06-12T00:00:00.000Z |
| CMH -> IAH\_daily\_count\_of\_flights| daily\_count\_of\_flights | 11.0000000000000000 | ATL -> SFO | 2023-06-12T00:00:00.000Z |

#### Select necessary columns

Using the select columns transform, we will only keep the columns that are required for the time series sync: `series_id`, `series_value`, and `date`. The flights backing dataset will hold time series values for all series, regardless of what they are measuring. Repeat this for the `airline_carrier_id` column (from the flights dataset).

![The select columns transform board in Pipeline Builder](../../../assets/61047df8fbc485545dee330ba6735b06879b257b82b170edf7ac16cf3b763587.webp)

| series\_id                        | series\_value        | date                     |
| -------------------------------- | ------------------- | ------------------------ |
| CMH -> IAH\_daily\_avg\_dep\_delay   | 33.4545454545450000 | 2023-06-12T00:00:00.000Z |
| CMH -> IAH\_daily\_avg\_arr\_delay   | 40.0000000000000000 | 2023-06-12T00:00:00.000Z |
| CMH -> IAH\_daily\_count\_of\_flights| 11.0000000000000000 | 2023-06-12T00:00:00.000Z |

### 2. Add a transform to aggregate and generate data for origin and destination airports

Now, you must repeat the aggregate and cast transform steps for both origin airports and destination airports.

#### Aggregate for the number of flights per day per route

Use the aggregate transform to group by `date` and `origin_airport_id`, then calculate the average arrival and departure times. The total number of rows in each group is equivalent to the number of flights per day per route.

![The aggregate transform board in Pipeline Builder, used to aggregate the number of flights per day](../../../assets/f053bbee21b0823bac2a20bfca2b26a91188dca286b3e62a7688a0ec38ef7c93.webp)

| date       | origin\_airport\_id | daily\_avg\_dep\_delay | daily\_count\_of\_departing\_flights |
| ---------- | ----------------- | ------------------- | -------------------------------- |
| 2023-07-02 | 10299             | 9.34375000000000000 | 33                               |
| 2023-09-06 | 10431             | -2.3333333333333333 | 6                                |
| 2023-01-12 | 10620             | -7.0000000000000000 | 2                                |

#### Cast to timestamp

To use this new data as a time series, we must create a timestamp column. To do this, use the cast transform to cast the `date` column to a timestamp type column.

![The cast transform board in Pipeline Builder, used to cast the data to a timestamp type](../../../assets/f456bb8ef761a4e958831eca6427a8d898e48d6c5d0016182f1c55ff5e6cd95c.webp)

### 3. Create a join to combine destination and origin airports

Using the join board, create a left join that combines data from the destination airport and origin airport, resulting in complete time series properties for airport data. Be sure the following configurations are set for your join:

* Match the date and `origin_airport_id` to the `dest_airport_id`.
* Auto-select columns from the left dataset.
* As the right columns, select the two that represent daily average delay and the daily count of flights.

![The join board in Pipeline Builder, configured to create a left join for airport data.](../../../assets/81d08bc1089db768f73e1aceddce87b22c4a1b591fb811a9d8e9037bc8623d8b.webp)

| date       | origin\_airport\_id | daily\_avg\_dep\_delay | daily\_count\_of\_departing\_flights | daily\_avg\_arr\_delay | daily\_count\_of\_arriving\_flights |
| ---------- | ----------------- | ------------------- | -------------------------------- | ------------------- | ------------------------------- |
| 2023-07-02 | 10299             | 9.34375000000000000 | 33                               | 18.5294117647058840 | 34                              |
| 2023-09-06 | 10431             | -2.3333333333333333 | 6                                | -8.0000000000000000 | 6                               |
| 2023-01-12 | 10620             | -7.0000000000000000 | 2                                | 56.5000000000000000 | 2                               |

### 4. Apply transforms to format data for a time series sync

#### Rename column

Now that we joined the origin airport data with the destination airport data, we have both arrival and departure metrics for all airports. We no longer need to differentiate origin from destination, so we can use the rename columns transform to change `origin_airport_id` to simply `airport_id`.

![The rename column transform board in Pipeline Builder](../../../assets/1bb2d23319c143789f24a9f68f11f2fcc141aa0ec9b240dbbea0edb8bdf9c653.webp)

The data should preview as follows with the renamed column:

| date       | airport\_id | daily\_avg\_dep\_delay | daily\_count\_of\_departing\_flights | daily\_avg\_arr\_delay | daily\_count\_of\_arriving\_flights |
| ---------- | ---------- | ------------------- | -------------------------------- | ------------------- | ------------------------------- |
| 2023-07-02 | 10299      | 9.34375000000000000 | 33                               | 18.5294117647058840 | 34                              |
| 2023-09-06 | 10431      | -2.3333333333333333 | 6                                | -8.0000000000000000 | 6                               |
| 2023-01-12 | 10620      | -7.0000000000000000 | 2                                | 56.5000000000000000 | 2                               |

#### Cast to double

We will soon apply an unpivot transform. This function requires that all values be of the same data type, so we must use the cast transform board again to cast our daily count of flights metrics to a double data type so they are the same type as the average delay metrics.

#### Add flight numbers

To calculate the full daily flight count, we will use the add numbers transform to add together the daily count of arriving flights and the daily count of departing flights, as shown below.

![The cast and add numbers transform boards in Pipeline Builder](../../../assets/3374fd4d46f17b59f8cb696a49063415d5a6b84af439ce7a505b5c3d78a610fb.webp)

| daily\_count\_of\_flights | date       | airport\_id | daily\_avg\_dep\_delay | daily\_count\_of\_departing\_flights | daily\_avg\_arr\_delay | daily\_count\_of\_arriving\_flights |
| ---------------------- | ---------- | ---------- | ------------------- | -------------------------------- | ------------------- | ------------------------------- |
| 77                     | 2023-07-02 | 10299      | 9.34375000000000000 | 33                               | 18.5294117647058840 | 34                              |
| 12                     | 2023-09-06 | 10431      | -2.3333333333333333 | 6                                | -8.0000000000000000 | 6                               |
| 4                      | 2023-01-12 | 10620      | -7.0000000000000000 | 2                                | 56.5000000000000000 | 2                               |

#### Unpivot to merge series values

Since this dataset contains time series data in different columns, we must use an unpivot transform to merge it into one value column so the data can match the required schema for a time series sync, as shown below:

* **series ID:** `string` | The series ID for the set of timestamp and value pairs referred to by a TSP, which must match the TSP's series ID.
* **timestamp:** `timestamp` or `long` | The time at which the quantity is measured.
* **value:** `integer`, `float`, `double`, `string` | The value of the quantity at the point that it is measured. A string type indicates a categorical time series; each categorical time series can have, at most, 10,000 unique variants.

The unpivot transform shown below places values for `daily_avg_dep_delay`, `daily_avg_arr_delay`, and `daily_count_of_flights` into the same `series_value` column. Those original column names are outputs to the new `series_name` column that will be used in the series ID.

![The unpivot transform board in Pipeline Builder, configured to create a series\_name column output](../../../assets/759d75e9896ad644cbd5d0e0497c876705db73839a1f86d8c58eeb7016181d59.webp)

The data should preview with the following schema:

| series\_name                      | series\_value        | date                     | airport\_id |
| -------------------------------- | ------------------- | ------------------------ | ---------- |
| daily\_count\_of\_flights           | 77                  | 2023-07-02T00:00:00.000Z | 10299      |
| daily\_avg\_dep\_delay              | 9.34375000000000000 | 2023-07-02T00:00:00.000Z | 10299      |
| daily\_avg\_arr\_delay              | 18.5294117647058840 | 2023-07-02T00:00:00.000Z | 10299      |

#### Concatenate string values to create the series ID

Now, we can use the concatenate strings transform to create the series ID (the identifier for the associated time series values). Use the transform to combine the `series_name` (what each sensor represents) with the primary key of the `Airport` object (`airport_id`).

![The concatenate strings board in Pipeline Builder, configured to combine the series\_name with the airport\_id](../../../assets/d6b5c9ebc6056954603db0add35a9d71b16edcd831d90241b2aa4a4c0cf2baca.webp)

| series\_id                    | series\_name                      | series\_value        | date                     | airport\_id |
| ---------------------------- | -------------------------------- | ------------------- | ------------------------ | ---------- |
| 12099\_daily\_count\_of\_flights | daily\_count\_of\_flights           | 77                  | 2023-07-02T00:00:00.000Z | 10299      |
| 12099\_daily\_avg\_dep\_delay    | daily\_avg\_dep\_delay              | 9.34375000000000000 | 2023-07-02T00:00:00.000Z | 10299      |
| 12099\_daily\_avg\_arr\_delay    | daily\_avg\_arr\_delay              | 18.5294117647058840 | 2023-07-02T00:00:00.000Z | 10299      |

#### Select necessary columns

Using the select columns transform, we will only keep the columns that are required for the time series sync: `series_id`, `series_value`, and `date`. The flights backing dataset will hold time series values for all series, regardless of what they are measuring.

![The select columns board in Pipeline Builder](../../../assets/d4c40365a721fc62d0df88ad04316c4a5636c3b4e31a9399b2e2dc9d23c9d92e.webp)

The resulting dataset should look as follows:

| series\_id                    | series\_value        | date                     |
| ---------------------------- | ------------------- | ------------------------ |
| 12099\_daily\_count\_of\_flights | 77                  | 2023-07-02T00:00:00.000Z |
| 12099\_daily\_avg\_dep\_delay    | 9.34375000000000000 | 2023-07-02T00:00:00.000Z |
| 12099\_daily\_avg\_arr\_delay    | 18.5294117647058840 | 2023-07-02T00:00:00.000Z |

### 5. Union the time series properties into a backing dataset

Create a union with the type `Union by name`, using the transforms representing the `Carrier`, `Route`, and `Airport` time series properties.

![Three time series properties nodes are selected for unioning from the Pipeline Builder graph](../../../assets/997c4f68c54d126436a35471a3601f25b9c7f2733ee5155705171787410b6f8b.webp)

![The union board in Pipeline Builder, configured to union the three TSP property sets by name](../../../assets/366268a8cb4a3d697b79832924b2133c3833d72d2044aad7435eec59be89f04d.webp)

| series\_id                      | series\_value        | date                     |
| ------------------------------ | ------------------- | ------------------------ |
| 12099\_daily\_count\_of\_flights   | 77                  | 2023-07-02T00:00:00.000Z |
| 12099\_daily\_avg\_dep\_delay      | 9.34375000000000000 | 2023-07-02T00:00:00.000Z |
| 12099\_daily\_avg\_arr\_delay      | 18.5294117647058840 | 2023-07-02T00:00:00.000Z |
| CMH -> IAH\_daily\_avg\_dep\_delay | -8.0000000000000000 | 2023-03-21T00:00:00.000Z |
| 20304\_daily\_avg\_arr\_delay      | 9.12500000000000000 | 2023-08-13T00:00:00.000Z |

## Part II: Create the time series sync

### 1. Remove null values

Apply a filter transform on the resulting dataset to remove any `null` values.

![The filter transform board in Pipeline Builder, configured to remove null values.](../../../assets/4eaab9a186df20988b9ae1004318a322967f884a95ee8fd70e89e6197458fc55.webp)

### 2. Configure the time series sync

Now, create a [time series sync](../time-series-concepts-glossary/index.md#time-series-sync) by selecting **Add** from the pipeline output section to the right of the screen. Then, select **Time series sync**. Fill out the necessary data for the new time series sync, with the following considerations:

* The title “\[Example] Time series sync | Events” will correspond to the resulting resource in your Palantir filesystem folder.
* Select the `series_id` column for the [**Series ID**](../time-series-concepts-glossary/index.md#series-id) field.
* Add the created `date` timestamp column in the **Time** field.
* Add `series_value` to the **Value** field.

Now, [save and build](../../pipeline-builder/outputs-deliver-pipeline/index.md#save) the pipeline. The output will be created in the same folder as the pipeline.

### 3. Use a time series sync to add properties to object types

Now that you created a pipeline with a time series sync, you are ready to use the sync to add time series properties to the `Route`, `Carrier` and `Airport` object types. Move on to our documentation for [adding time series properties to object types](../time-series-properties-use-case-ontology/index.md) for more guidance.