Calculate Metrics across Columns
You can use a variety of mathematical and statistical functions to calculate metrics within a column.
To calculate metrics across columns, you can use a generalized version of the following example.
Source:
Your dataset tracks swimmer performance across multiple heats in a race, and you would like to calculate best, worst, and average times in seconds across all three heats. Here's the data:
Racer | Heat1 | Heat2 | Heat3 |
|---|---|---|---|
Racer X | 37.22 | 38.22 | 37.61 |
Racer Y | 41.33 | DQ | 38.04 |
Racer Z | 39.27 | 39.04 | 38.85 |
In the above data, Racer Y was disqualified (DQ) in Heat 2.
Transformation:
To compute the metrics, you must bundle the data into an array, break out the array into separate rows, and then calculate your metrics by grouping. Here are the steps:
When the data is imported, you may need to create a header for each row:
Transformation Name
Rename columns with a rowParameter: Option
Use row as header
Parameter: Row
1
The columns containing heat time data may need to be retyped. From the drop-down next to each column name, select Decimal type.
The
DQvalue in the Heat2 column is invalid data for Decimal type. You can use the following transformation to turn it into a missing value. For purposes of calculating averages, you may or may not want to turn invalid data into zeroes or blanks. In this case, replacing the data as0.00causes improper calculations for the metrics.Transformation Name
Replace text or patternsParameter: Column
Heat2
Parameter: Find
'DQ'
Parameter: Replace with
''
Use the following to gather all of the heat data into two columns:
Transformation Name
Unpivot columnsParameter: Columns
Heat1,Heat2,Heat3
Parameter: Group size
1
You can now rename the two columns. Rename
keytoHeatNumandvaluetoHeatTime.You may want to delete the rows that have a missing value for
HeatTime:Transformation Name
Delete rowsParameter: Condition
ISMISSING([value])
You can now perform calculations on this column. The following transformations calculate minimum, average (mean), and maximum times for each racer:
Transformation Name
New formulaParameter: Formula type
Multiple row formula
Parameter: Formula
MIN(HeatTime)
Parameter: Group rows by
Racer
Parameter: New column name
'BestTime'
Transformation Name
New formulaParameter: Formula type
Multiple row formula
Parameter: Formula
AVERAGE(HeatTime)
Parameter: Group rows by
Racer
Parameter: New column name
'AvgTime'
Transformation Name
New formulaParameter: Formula type
Multiple row formula
Parameter: Formula
MAX(HeatTime)
Parameter: Group rows by
Racer
Parameter: New column name
'WorstTime'
To make the data look better, you might want to reformat the values in the
AvgTimecolumn to two decimal points:Transformation Name
Edit column with formulaParameter: Columns
AvgTime
Parameter: Formula
NUMFORMAT(AvgTime, '##.00')
Results:
After you use the Move transformation to re-organize your columns, the dataset should look like the following:
Racer | HeatNum | HeatTime | BestTime | WorstTime | AvgTime |
|---|---|---|---|---|---|
Racer X | Heat1 | 37.22 | 37.22 | 38.22 | 37.68 |
Racer X | Heat2 | 38.22 | 37.22 | 38.22 | 37.68 |
Racer X | Heat3 | 37.61 | 37.22 | 38.22 | 37.68 |
Racer Y | Heat1 | 41.33 | 38.04 | 41.33 | 39.69 |
Racer Y | Heat3 | 38.04 | 38.04 | 41.33 | 39.69 |
Racer Z | Heat1 | 39.27 | 38.85 | 39.27 | 39.05 |
Racer Z | Heat2 | 39.04 | 38.85 | 39.27 | 39.05 |
Racer Z | Heat3 | 38.85 | 38.85 | 39.27 | 39.05 |