This example illustrates how you can use the following conditional calculation functions to analyze weather data:
AVERAGEIF - Average of a set of values by group that meet a specified condition. See AVERAGEIF Function.MINIF - Minimum of a set of values by group that meet a specified condition. See MINIF Function.MAXIF - Maximum of a set of values by group that meet a specified condition. See MAXIF Function.VARIF - Variance of a set of values by group that meet a specified condition. See VARIF Function.STDEVIF - Standard deviation of a set of values by group that meet a specified condition. See STDEVIF Function.
Source:
Here is some example weather data:
| date | city | rain | temp | wind |
|---|
| 1/23/17 | Valleyville | 0.00 | 12.8 | 6.7 |
| 1/23/17 | Center Town | 0.31 | 9.4 | 5.3 |
| 1/23/17 | Magic Mountain | 0.00 | 0.0 | 7.3 |
| 1/24/17 | Valleyville | 0.25 | 17.2 | 3.3 |
| 1/24/17 | Center Town | 0.54 | 1.1 | 7.6 |
| 1/24/17 | Magic Mountain | 0.32 | 5.0 | 8.8 |
| 1/25/17 | Valleyville | 0.02 | 3.3 | 6.8 |
| 1/25/17 | Center Town | 0.83 | 3.3 | 5.1 |
| 1/25/17 | Magic Mountain | 0.59 | -1.7 | 6.4 |
| 1/26/17 | Valleyville | 1.08 | 15.0 | 4.2 |
| 1/26/17 | Center Town | 0.96 | 6.1 | 7.6 |
| 1/26/17 | Magic Mountain | 0.77 | -3.9 | 3.0 |
| 1/27/17 | Valleyville | 1.00 | 7.2 | 2.8 |
| 1/27/17 | Center Town | 1.32 | 20.0 | 0.2 |
| 1/27/17 | Magic Mountain | 0.77 | 5.6 | 5.2 |
| 1/28/17 | Valleyville | 0.12 | -6.1 | 5.1 |
| 1/28/17 | Center Town | 0.14 | 5.0 | 4.9 |
| 1/28/17 | Magic Mountain | 1.50 | 1.1 | 0.4 |
| 1/29/17 | Valleyville | 0.36 | 13.3 | 7.3 |
| 1/29/17 | Center Town | 0.75 | 6.1 | 9.0 |
| 1/29/17 | Magic Mountain | 0.60 | 3.3 | 6.0 |
Transformation:
The following computes average temperature for rainy days by city:
| D trans |
|---|
| RawWrangle | true |
|---|
| p03Value | city |
|---|
| Type | step |
|---|
| WrangleText | derive type:single value:AVERAGEIF(temp, rain > 0) group:city as:'avgTempWRain' |
|---|
| p01Name | Formula type |
|---|
| p01Value | Single row formula |
|---|
| p02Name | Formula |
|---|
| p02Value | AVERAGEIF(temp, rain > 0) |
|---|
| p03Name | Group rows by |
|---|
| p04Value | 'avgTempWRain' |
|---|
| p04Name | New column name |
|---|
| SearchTerm | New formula |
|---|
|
The following computes maximum wind for sub-zero days by city:
| D trans |
|---|
| RawWrangle | true |
|---|
| p03Value | city |
|---|
| Type | step |
|---|
| WrangleText | derive type:single value:MAXIF(wind,temp < 0) group:city as:'maxWindSubZero' |
|---|
| p01Name | Formula type |
|---|
| p01Value | Single row formula |
|---|
| p02Name | Formula |
|---|
| p02Value | MAXIF(wind,temp < 0) |
|---|
| p03Name | Group rows by |
|---|
| p04Value | 'maxWindSubZero' |
|---|
| p04Name | New column name |
|---|
| SearchTerm | New formula |
|---|
|
This step calculates the minimum temp when the wind is less than 5 mph by city:
| D trans |
|---|
| RawWrangle | true |
|---|
| p03Value | city |
|---|
| Type | step |
|---|
| WrangleText | derive type:single value:MINIF(temp,wind<5) group:city as:'minTempWind5' |
|---|
| p01Name | Formula type |
|---|
| p01Value | Single row formula |
|---|
| p02Name | Formula |
|---|
| p02Value | MINIF(temp,wind<5) |
|---|
| p03Name | Group rows by |
|---|
| p04Value | 'minTempWind5' |
|---|
| p04Name | New column name |
|---|
| SearchTerm | New formula |
|---|
|
This step computes the variance in temperature for rainy days by city:
| D trans |
|---|
| RawWrangle | true |
|---|
| p03Value | city |
|---|
| Type | step |
|---|
| WrangleText | derive type:single value:VARIF(temp,rain >0) group:city as:'varTempWRain' |
|---|
| p01Name | Formula type |
|---|
| p01Value | Single row formula |
|---|
| p02Name | Formula |
|---|
| p02Value | VARIF(temp,rain >0) |
|---|
| p03Name | Group rows by |
|---|
| p04Value | 'varTempWRain' |
|---|
| p04Name | New column name |
|---|
| SearchTerm | New formula |
|---|
|
The following computes the standard deviation in rainfall for Center Town:
| D trans |
|---|
| RawWrangle | true |
|---|
| p03Value | city |
|---|
| Type | step |
|---|
| WrangleText | derive type:single value:STDEVIF(rain,city=='Center Town') as:'stDevRainCT' |
|---|
| p01Name | Formula type |
|---|
| p01Value | Single row formula |
|---|
| p02Name | Formula |
|---|
| p02Value | STDEVIF(rain,city=='Center Town') |
|---|
| p03Name | Group rows by |
|---|
| p04Value | 'stDevRainCT' |
|---|
| p04Name | New column name |
|---|
| SearchTerm | New formula |
|---|
|
You can use the following transforms to format the generated output. Note the $col placeholder value for the multi-column transforms:
| D trans |
|---|
| RawWrangle | true |
|---|
| Type | step |
|---|
| WrangleText | set col:stDevRainCenterTown,maxWindSubZero value:numformat($col,'##.##') |
|---|
| p01Name | Columns |
|---|
| p01Value | stDevRainCenterTown,maxWindSubZero |
|---|
| p02Name | Formula |
|---|
| p02Value | numformat($col,'##.##') |
|---|
| SearchTerm | Edit column with formula |
|---|
|
Since the following rely on data that has only one significant digit, you should format them differently:
| D trans |
|---|
| RawWrangle | true |
|---|
| Type | step |
|---|
| WrangleText | set col:varTempWRain,avgTempWRain,minTempWind5 value:numformat($col,'##.#') |
|---|
| p01Name | Columns |
|---|
| p01Value | varTempWRain,avgTempWRain,minTempWind5 |
|---|
| p02Name | Formula |
|---|
| p02Value | numformat($col,'##.#') |
|---|
| SearchTerm | Edit column with formula |
|---|
|
Results:
| date | city | rain | temp | wind | avgTempWRain | maxWindSubZero | minTempWind5 | varTempWRain | stDevRainCT |
|---|
| 1/23/17 | Valleyville | 0.00 | 12.8 | 6.7 | 8.3 | 5.1 | 7.2 | 63.8 | 0.37 |
| 1/23/17 | Center Town | 0.31 | 9.4 | 5.3 | 7.3 | | 5 | 32.6 | 0.37 |
| 1/23/17 | Magic Mountain | 0.00 | 0.0 | 7.3 | 1.6 | 6.43 | -3.9 | 12 | 0.37 |
| 1/24/17 | Valleyville | 0.25 | 17.2 | 3.3 | 8.3 | 5.1 | 7.2 | 63.8 | 0.37 |
| 1/24/17 | Center Town | 0.54 | 1.1 | 7.6 | 7.3 | | 5 | 32.6 | 0.37 |
| 1/24/17 | Magic Mountain | 0.32 | 5.0 | 8.8 | 1.6 | 6.43 | -3.9 | 12 | 0.37 |
| 1/25/17 | Valleyville | 0.02 | 3.3 | 6.8 | 8.3 | 5.1 | 7.2 | 63.8 | 0.37 |
| 1/25/17 | Center Town | 0.83 | 3.3 | 5.1 | 7.3 | | 5 | 32.6 | 0.37 |
| 1/25/17 | Magic Mountain | 0.59 | -1.7 | 6.4 | 1.6 | 6.43 | -3.9 | 12 | 0.37 |
| 1/26/17 | Valleyville | 1.08 | 15.0 | 4.2 | 8.3 | 5.1 | 7.2 | 63.8 | 0.37 |
| 1/26/17 | Center Town | 0.96 | 6.1 | 7.6 | 7.3 | | 5 | 32.6 | 0.37 |
| 1/26/17 | Magic Mountain | 0.77 | -3.9 | 3.0 | 1.6 | 6.43 | -3.9 | 12 | 0.37 |
| 1/27/17 | Valleyville | 1.00 | 7.2 | 2.8 | 8.3 | 5.1 | 7.2 | 63.8 | 0.37 |
| 1/27/17 | Center Town | 1.32 | 20.0 | 0.2 | 7.3 | | 5 | 32.6 | 0.37 |
| 1/27/17 | Magic Mountain | 0.77 | 5.6 | 5.2 | 1.6 | 6.43 | -3.9 | 12 | 0.37 |
| 1/28/17 | Valleyville | 0.12 | -6.1 | 5.1 | 8.3 | 5.1 | 7.2 | 63.8 | 0.37 |
| 1/28/17 | Center Town | 0.14 | 5.0 | 4.9 | 7.3 | | 5 | 32.6 | 0.37 |
| 1/28/17 | Magic Mountain | 1.50 | 1.1 | 0.4 | 1.6 | 6.43 | -3.9 | 12 | 0.37 |
| 1/29/17 | Valleyville | 0.36 | 13.3 | 7.3 | 8.3 | 5.1 | 7.2 | 63.8 | 0.37 |
| 1/29/17 | Center Town | 0.75 | 6.1 | 9.0 | 7.3 | | 5 | 32.6 | 0.37 |
| 1/29/17 | Magic Mountain | 0.60 | 3.3 | 6.0 | 1.6 | 6.43 | -3.9 | 12 | 0.37 |
