## Key

• This line was removed.
• Formatting was changed.
Excerpt

This example describes how to generate random array (list) data and then to apply statistical functions specifically created for arrays.

Functions:

D generate list excerpts
pages LISTSUM Function,LISTMIN Function,LISTMAX Function,LISTAVERAGE Function,LISTVAR Function,LISTSTDEV Function,LISTMODE Function

Also:

D generate list excerpts
pages RANGE Function,RAND Function,ROUND Function

Source:

For this example, you can generate some randomized data using the following steps. First, you need to seed an array with a range of values using the RANGE function:

D trans
RawWrangle true 'myArray1' step derive type: single value: RANGE(5, 50, 5) as: 'myArray1' Formula type Single row formula Formula RANGE(5, 50, 5) New column name New formula

Then, unpack this array, so you can add a random factor:

D trans
RawWrangle true true step unnest col: myArray1 keys: '[0]', '[1]', '[2]', '[3]', '[4]', '[5]', '[6]', '[7]', '[8]', '[9]' pluck: true markLineage: true Column myArray1 Paths to elements '[0]', '[1]', '[2]', '[3]', '[4]', '[5]', '[6]', '[7]', '[8]', '[9]' Remove elements from original true Include original column name Unnest Objects into columns

Add the randomizing factor. Here, you are adding randomization around individual values:  x-1 < x < x+4.

D trans
RawWrangle true step set col: myArray1_0~myArray1_8 value: IF(RAND() > 0.5, \$col + (5 * RAND()), \$col - RAND()) Columns myArray1_0~myArray1_8 Formula IF(RAND() > 0.5, \$col + (5 * RAND()), \$col - RAND()) Edit column with formula

To make the numbers easier to manipulate, you can round them to two decimal places:

D trans
RawWrangle true step set col: myArray1_0~myArray1_8 value: ROUND(\$col, 2) Columns myArray1_0~myArray1_8 Formula ROUND(\$col, 2) Edit column with formula

Renest these columns into an array:

D trans
RawWrangle true 'myArray2' step nest col: myArray1_0, myArray1_1, myArray1_2, myArray1_3, myArray1_4, myArray1_5, myArray1_6, myArray1_7, myArray1_8 into: array as: 'myArray2' Columns myArray1_0, myArray1_1, myArray1_2, myArray1_3, myArray1_4, myArray1_5, myArray1_6, myArray1_7, myArray1_8 Nest columns to Array New column name Nest columns into Objects

Delete the unused columns:

D trans
RawWrangle true step drop col: myArray1_0~myArray1_8,myArray1 action: Drop Columns myArray1_0~myArray1_8,myArray1 Action Delete selected columns Delete columns

Your data should look similar to the following:

myArray2
["8.29","9.63","14.63","19.63","24.63","29.63","34.63","39.63","44.63"]
["8.32","14.01","19.01","24.01","29.01","34.01","39.01","44.01","49.01"]
["4.55","9.58","14.58","19.58","24.58","29.58","34.58","39.58","44.58"]
["9.22","14.84","19.84","24.84","29.84","34.84","39.84","44.84","49.84"]
["8.75","13.36","18.36","23.36","28.36","33.36","38.36","43.36","48.36"]
["8.47","14.76","19.76","24.76","29.76","34.76","39.76","44.76","49.76"]
["4.93","9.99","14.99","19.99","24.99","29.99","34.99","39.99","44.99"]
["4.65","14.98","19.98","24.98","29.98","34.98","39.98","44.98","49.98"]
["7.80","14.62","19.62","24.62","29.62","34.62","39.62","44.62","49.62"]
["9.32","9.96","14.96","19.96","24.96","29.96","34.96","39.96","44.96"]

Transformation:

These steps demonstrate the individual math functions that you can apply to your list data without unnesting it:

Info

NOTE: The NUMFORMAT function has been wrapped around each list function to account for any floating-point errors or additional digits in the results.

Sum of all values in the array (list):

D trans
RawWrangle true 'arraySum' step derive type: single value: NUMFORMAT(LISTSUM(myArray2), '#.##') as: 'arraySum' Formula type Single row formula Formula NUMFORMAT(LISTSUM(myArray2), '#.##') New column name New formula

Minimum of all values in the array (list):

D trans
RawWrangle true 'arrayMin' step derive type: single value: NUMFORMAT(LISTMIN(myArray2), '#.##') as: 'arrayMin' Formula type Single row formula Formula NUMFORMAT(LISTMIN(myArray2), '#.##') New column name New formula

Maximum of all values in the array (list):

D trans
RawWrangle true 'arrayMax' step derive type: single value: NUMFORMAT(LISTMAX(myArray2), '#.##') as: 'arrayMax' Formula type Single row formula Formula NUMFORMAT(LISTMAX(myArray2), '#.##') New column name New formula

Average of all values in the array (list):

D trans
RawWrangle true 'arrayAvg' step derive type: single value: NUMFORMAT(LISTAVERAGE(myArray2), '#.##') as: 'arrayAvg' Formula type Single row formula Formula NUMFORMAT(LISTAVERAGE(myArray2), '#.##') New column name New formula

Variance of all values in the array (list):

D trans
RawWrangle true 'arrayVar' step derive type: single value: NUMFORMAT(LISTVAR(myArray2), '#.##') as: 'arrayVar' Formula type Single row formula Formula NUMFORMAT(LISTVAR(myArray2), '#.##') New column name New formula

Standard deviation of all values in the array (list):

D trans
RawWrangle true 'arrayStDv' step derive type: single value: NUMFORMAT(LISTSTDEV(myArray2), '#.##') as: 'arrayStDv' Formula type Single row formula Formula NUMFORMAT(LISTSTDEV(myArray2), '#.##') New column name New formula

Mode (most common value) of all values in the array (list):

D trans
RawWrangle true 'arrayMode' step derive type: single value: NUMFORMAT(LISTMODE(myArray2), '#.##') as: 'arrayMode' Formula type Single row formula Formula NUMFORMAT(LISTMODE(myArray2), '#.##') New column name New formula

Results:

Results for the first four math functions:

myArray2arrayAvgarrayMaxarrayMinarraySum
["8.29","9.63","14.63","19.63","24.63","29.63","34.63","39.63","44.63"]25.0444.638.29225.33
["8.32","14.01","19.01","24.01","29.01","34.01","39.01","44.01","49.01"]28.9349.018.32260.4
["4.55","9.58","14.58","19.58","24.58","29.58","34.58","39.58","44.58"]24.5844.584.55221.19
["9.22","14.84","19.84","24.84","29.84","34.84","39.84","44.84","49.84"]29.7749.849.22267.94
["8.75","13.36","18.36","23.36","28.36","33.36","38.36","43.36","48.36"]28.448.368.75255.63
["8.47","14.76","19.76","24.76","29.76","34.76","39.76","44.76","49.76"]29.6249.768.47266.55
["4.93","9.99","14.99","19.99","24.99","29.99","34.99","39.99","44.99"]24.9844.994.93224.85
["4.65","14.98","19.98","24.98","29.98","34.98","39.98","44.98","49.98"]29.3949.984.65264.49
["7.80","14.62","19.62","24.62","29.62","34.62","39.62","44.62","49.62"]29.4249.627.8264.76
["9.32","9.96","14.96","19.96","24.96","29.96","34.96","39.96","44.96"]25.4444.969.32229

Results for the statistical functions:

myArray2 arrayModearrayStDvarrayVar
["8.29","9.63","14.63","19.63","24.63","29.63","34.63","39.63","44.63"] 12.32151.72
["8.32","14.01","19.01","24.01","29.01","34.01","39.01","44.01","49.01"] 13.03169.78
["4.55","9.58","14.58","19.58","24.58","29.58","34.58","39.58","44.58"] 12.92166.8
["9.22","14.84","19.84","24.84","29.84","34.84","39.84","44.84","49.84"] 13.02169.46
["8.75","13.36","18.36","23.36","28.36","33.36","38.36","43.36","48.36"] 12.84164.95
["8.47","14.76","19.76","24.76","29.76","34.76","39.76","44.76","49.76"] 13.14172.56
["4.93","9.99","14.99","19.99","24.99","29.99","34.99","39.99","44.99"] 12.92166.93
["4.65","14.98","19.98","24.98","29.98","34.98","39.98","44.98","49.98"] 13.9193.16
["7.80","14.62","19.62","24.62","29.62","34.62","39.62","44.62","49.62"] 13.23175.08
["9.32","9.96","14.96","19.96","24.96","29.96","34.96","39.96","44.96"] 12.21149.17

Since all values are unique within an individual array, there is no most common value in any of them, which yields empty values for the `arrayMode` column.

D s also
label example_list_math_functions