Page tree



Contents:

The cloud-based version of Trifacta Wrangler is now available! Read all about it, and register for your free account.

This example describes how to use the rolling computational functions:

Source:

In this example, the following data comes from times recorded at regular intervals during a three-lap race around a track. The source data is in cumulative time in seconds (time_sc). You can use ROLLING and other windowing functions to break down the data into more meaningful metrics.

lapquartertime_sc
100.000
1119.554
1239.785
1360.021
2080.950
21101.785
22121.005
23141.185
30162.008
31181.887
32200.945
33220.856

Transformation:

Primary key: Since the quarter information repeats every lap, there is no unique identifier for each row. The following steps create this identifier:

Transformation Name Change column data type
Parameter: Columns lap,quarter
Parameter: New type String

Transformation Name New formula
Parameter: Formula type Single row formula
Parameter: Formula MERGE(['l',lap,'q',quarter])
Parameter: New column name 'splitId'

Get split times: Use the following transform to break down the splits for each quarter of the race:

Transformation Name New formula
Parameter: Formula type Single row formula
Parameter: Formula ROUND(time_sc - PREV(time_sc, 1), 3)
Parameter: Order rows by splitId
Parameter: New column name 'split_time_sc'

Compute rolling computations: You can use the following types of computations to provide rolling metrics on the current and three previous splits:

Transformation Name New formula
Parameter: Formula type Single row formula
Parameter: Formula ROLLINGAVERAGE(split_time_sc, 3)
Parameter: Order rows by splitId
Parameter: New column name 'ravg'

Transformation Name New formula
Parameter: Formula type Single row formula
Parameter: Formula ROLLINGMAX(split_time_sc, 3)
Parameter: Order rows by splitId
Parameter: New column name 'rmax'

Transformation Name New formula
Parameter: Formula type Single row formula
Parameter: Formula ROLLINGMIN(split_time_sc, 3)
Parameter: Order rows by splitId
Parameter: New column name 'rmin'

Transformation Name New formula
Parameter: Formula type Single row formula
Parameter: Formula ROUND(ROLLINGSTDEV(split_time_sc, 3), 3)
Parameter: Order rows by splitId
Parameter: New column name 'rstdev'

Transformation Name New formula
Parameter: Formula type Single row formula
Parameter: Formula ROUND(ROLLINGVAR(split_time_sc, 3), 3)
Parameter: Order rows by splitId
Parameter: New column name 'rvar'

Results:

When the above transforms have been completed, the results look like the following:

lapquartersplitIdtime_scsplit_time_scrvarrstdevrminrmaxravg
10l1q00      
11l1q120.09620.0960020.09620.09620.096
12l1q240.5320.4340.0290.16920.09620.43420.265
13l1q361.03120.5010.0310.17720.09620.50120.344
20l2q081.08720.0560.0390.19820.05620.50120.272
21l2q1101.38320.2960.0290.1720.05620.50120.322
22l2q2122.09220.7090.0590.24220.05620.70920.39
23l2q3141.88619.7940.1130.33719.79420.70920.214
30l3q0162.58120.6950.1390.37319.79420.70920.373
31l3q1183.01820.4370.1380.37119.79420.70920.409
32l3q2203.49320.4750.1130.33619.79420.69520.35
33l3q3222.89319.40.2520.50219.420.69520.252

You can reduce the number of steps by applying a window transform such as the following:

Transformation Name Window
Parameter: Formula1 lap
Parameter: Formula2 rollingaverage(split_time_sc, 0, 3)
Parameter: Formula3 rollingmax(split_time_sc, 0, 3)
Parameter: Formula4 rollingmin(split_time_sc, 0, 3)
Parameter: Formula5 round(rollingstdev(split_time_sc, 0, 3), 3)
Parameter: Formula6 round(rollingvar(split_time_sc, 0, 3), 3)
Parameter: Group by lap
Parameter: Order by lap

However, you must rename all of the generated windowX columns.

Your Rating: Results: 1 Star2 Star3 Star4 Star5 Star 14 rates

This page has no comments.