Page tree

Versions Compared

Key

  • This line was added.
  • This line was removed.
  • Formatting was changed.
Comment: Published by Scroll Versions from space DEV and version r092
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
pagesLISTSUM Function,LISTMIN Function,LISTMAX Function,LISTAVERAGE Function,LISTVAR Function,LISTSTDEV Function,LISTMODE Function

Also:

D generate list excerpts
pagesRANGE 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
RawWrangletrue
p03Value'myArray1'
Typestep
WrangleTextderive type: single value: RANGE(5, 50, 5) as: 'myArray1'
p01NameFormula type
p01ValueSingle row formula
p02NameFormula
p02ValueRANGE(5, 50, 5)
p03NameNew column name
SearchTermNew formula

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

D trans
RawWrangletrue
p03Valuetrue
Typestep
WrangleTextunnest col: myArray1 keys: '[0]', '[1]', '[2]', '[3]', '[4]', '[5]', '[6]', '[7]', '[8]', '[9]' pluck: true markLineage: true
p01NameColumn
p01ValuemyArray1
p02NamePaths to elements
p02Value'[0]', '[1]', '[2]', '[3]', '[4]', '[5]', '[6]', '[7]', '[8]', '[9]'
p03NameRemove elements from original
p04Valuetrue
p04NameInclude original column name
SearchTermUnnest Objects into columns

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

D trans
RawWrangletrue
Typestep
WrangleTextset col: myArray1_0~myArray1_8 value: IF(RAND() > 0.5, $col + (5 * RAND()), $col - RAND())
p01NameColumns
p01ValuemyArray1_0~myArray1_8
p02NameFormula
p02ValueIF(RAND() > 0.5, $col + (5 * RAND()), $col - RAND())
SearchTermEdit column with formula

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

D trans
RawWrangletrue
Typestep
WrangleTextset col: myArray1_0~myArray1_8 value: ROUND($col, 2)
p01NameColumns
p01ValuemyArray1_0~myArray1_8
p02NameFormula
p02ValueROUND($col, 2)
SearchTermEdit column with formula

Renest these columns into an array:

D trans
RawWrangletrue
p03Value'myArray2'
Typestep
WrangleTextnest col: myArray1_0, myArray1_1, myArray1_2, myArray1_3, myArray1_4, myArray1_5, myArray1_6, myArray1_7, myArray1_8 into: array as: 'myArray2'
p01NameColumns
p01ValuemyArray1_0, myArray1_1, myArray1_2, myArray1_3, myArray1_4, myArray1_5, myArray1_6, myArray1_7, myArray1_8
p02NameNest columns to
p02ValueArray
p03NameNew column name
SearchTermNest columns into Objects

Delete the unused columns:

D trans
RawWrangletrue
Typestep
WrangleTextdrop col: myArray1_0~myArray1_8,myArray1 action: Drop
p01NameColumns
p01ValuemyArray1_0~myArray1_8,myArray1
p02NameAction
p02ValueDelete selected columns
SearchTermDelete 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
RawWrangletrue
p03Value'arraySum'
Typestep
WrangleTextderive type: single value: NUMFORMAT(LISTSUM(myArray2), '#.##') as: 'arraySum'
p01NameFormula type
p01ValueSingle row formula
p02NameFormula
p02ValueNUMFORMAT(LISTSUM(myArray2), '#.##')
p03NameNew column name
SearchTermNew formula

Minimum of all values in the array (list):

D trans
RawWrangletrue
p03Value'arrayMin'
Typestep
WrangleTextderive type: single value: NUMFORMAT(LISTMIN(myArray2), '#.##') as: 'arrayMin'
p01NameFormula type
p01ValueSingle row formula
p02NameFormula
p02ValueNUMFORMAT(LISTMIN(myArray2), '#.##')
p03NameNew column name
SearchTermNew formula

Maximum of all values in the array (list):

D trans
RawWrangletrue
p03Value'arrayMax'
Typestep
WrangleTextderive type: single value: NUMFORMAT(LISTMAX(myArray2), '#.##') as: 'arrayMax'
p01NameFormula type
p01ValueSingle row formula
p02NameFormula
p02ValueNUMFORMAT(LISTMAX(myArray2), '#.##')
p03NameNew column name
SearchTermNew formula

Average of all values in the array (list):

D trans
RawWrangletrue
p03Value'arrayAvg'
Typestep
WrangleTextderive type: single value: NUMFORMAT(LISTAVERAGE(myArray2), '#.##') as: 'arrayAvg'
p01NameFormula type
p01ValueSingle row formula
p02NameFormula
p02ValueNUMFORMAT(LISTAVERAGE(myArray2), '#.##')
p03NameNew column name
SearchTermNew formula

Variance of all values in the array (list):

D trans
RawWrangletrue
p03Value'arrayVar'
Typestep
WrangleTextderive type: single value: NUMFORMAT(LISTVAR(myArray2), '#.##') as: 'arrayVar'
p01NameFormula type
p01ValueSingle row formula
p02NameFormula
p02ValueNUMFORMAT(LISTVAR(myArray2), '#.##')
p03NameNew column name
SearchTermNew formula

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

D trans
RawWrangletrue
p03Value'arrayStDv'
Typestep
WrangleTextderive type: single value: NUMFORMAT(LISTSTDEV(myArray2), '#.##') as: 'arrayStDv'
p01NameFormula type
p01ValueSingle row formula
p02NameFormula
p02ValueNUMFORMAT(LISTSTDEV(myArray2), '#.##')
p03NameNew column name
SearchTermNew formula

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

D trans
RawWrangletrue
p03Value'arrayMode'
Typestep
WrangleTextderive type: single value: NUMFORMAT(LISTMODE(myArray2), '#.##') as: 'arrayMode'
p01NameFormula type
p01ValueSingle row formula
p02NameFormula
p02ValueNUMFORMAT(LISTMODE(myArray2), '#.##')
p03NameNew column name
SearchTermNew 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
labelexample_list_math_functions