A specialized array for working with tabular data
| Myna. DataSet | A specialized array for working with tabular data |
| Functions and Properties | |
| DataSet | Creates a new DataSet Object |
| columns | Array of column names in this DataSet |
| containsByCol | returns true if any row in the DataSet has a column value that matches compare |
| findFirstByCol | returns the first row in the DataSet whose column value matches compare, or null if no matches |
| findAllByCol | returns a new DataSet of all the rows in this DataSet whose column value matches compare |
| valueArray | returns an array of the values of a column. |
| map | Creates a new DataSet with the results of calling a provided function on every element in this array. |
| filter | Performs Array.filter, but returns a new DataSet with the same columns as this one |
| concat | Performs Array.concat, but returns a new DataSet with the same columns as this one |
| slice | Performs Array.slice, but returns a new DataSet with the same columns as this one |
| merge | merges another DataSet into this one, by a common column value |
| minByCol | returns the “smallest” value of a column. |
| maxByCol | returns the “largest” value of a column. |
| sumByCol | returns a sum of the values of a column. |
| sortByCol | sorts the DataSet by the supplied column and compare function. |
| avgByCol | returns an average of the column. |
| toHtmlTable | returns an HTML table of this dataset |
| pivot | returns a new DataSet pivoted around a key, category, and value |
| toStruct | converts a DataSet into an hierarchical object |
| toMap | return a JS object where a column is used for the keys |
var DataSet =function DataSet( options, accessFunction, lengthFunction )
Creates a new DataSet Object
| options | Either an array to be converted to a DataSet, or and object containing detailed options. If an array, the array must contain at least one record, and the record should have all the non-function properties expected in the DataSet so that DataSet.columns can be inferred. If this is an object it should conform to the Options Object defined below. |
| accessFunction | Optional, default function(obj,index){return obj[index]} Only used if options is an array like object. Function that takes obj and an index and returns the item at that index |
| lengthFunction | Optional, default function(obj){return obj.length} Only used if options is an array like object. Function that takes obj and returns the length of the collection |
| data | Optional default [] This is an array of initial data. May be empty. |
| columns | Optional default [] Either a comma separated list, an array of column names, or an object whose non-function properties represent the column names. These define the known properties of the objects in a DataSet array. If columns is not provided, but data contains at least one row, columns will be calculated as all the non-function properties of the first row. |
DataSet is a wrapper for an array of objects. This is treated much like the result set of a query, but does not need to come from a query. DataSet’s provide a normalized way to represent any tabular data
DataSet.prototype.containsByCol = function( column, compare )
returns true if any row in the DataSet has a column value that matches compare
| column | name of the column to search. |
| compare | RegExp, string regular expresion, or function to compare. if compare is a function, it will be called with the “Compare Function Arguments” below. The supplied compare function should return true if the current row should be output |
| columnValue | Value of column in the current row, |
| data | An object that represents all the columns in this row |
| index | The index of the current row |
| dataset | A reference to this dataset |
DataSet.prototype.findFirstByCol = function DataSet_findFirst( column, compare )
returns the first row in the DataSet whose column value matches compare, or null if no matches
| column | name of the column to search. |
| compare | RegExp, string regular expresion, or function to compare. if compare is a function, it will be called with the “Compare Function Arguments” below. The supplied compare function should return true if the current row should be output |
| columnValue | Value of column in the current row, |
| data | An object that represents all the columns in this row |
| index | The index of the current row |
| dataset | A reference to this dataset |
DataSet.prototype.findAllByCol = function DataSet_findAll( column, compare )
returns a new DataSet of all the rows in this DataSet whose column value matches compare
| column | name of the column to search. |
| compare | RegExp, string regular expression, or function to compare. if compare is a function, it will be called with the “Compare Function Arguments” below. The supplied compare function should return true if the current row should be output |
| columnValue | Value of column in the current row, |
| data | An object that represents all the columns in this row |
| index | The index of the current row |
| dataset | A reference to this DataSet |
DataSet.prototype.valueArray=function( columnName )
returns an array of the values of a column.
| columnName | String Column name to return |
DataSet.prototype.map = function( func )
Creates a new DataSet with the results of calling a provided function on every element in this array.
DataSet.prototype.filter = function()
Performs Array.filter, but returns a new DataSet with the same columns as this one
DataSet.prototype.concat = function( otherArray )
Performs Array.concat, but returns a new DataSet with the same columns as this one
<Array.concat>
DataSet.prototype.slice = function()
Performs Array.slice, but returns a new DataSet with the same columns as this one
<Array.slice>
DataSet.prototype.merge = function( ds, column )
merges another DataSet into this one, by a common column value
| ds | Other dataset to merge into this one |
| column | String name of column that the two DataSets have in common |
var a = new Myna.DataSet([{
id:1,
name:"bob"
}])
var a = new Myna.DataSet([{
id:1,
age:15
}])
a.merge(b,"id")
DataSet.prototype.minByCol = function( column, compare )
returns the “smallest” value of a column.
| column | column to compare |
| compare | Optiional, default: function(a,b){return a < b} A compare function like sort() uses to determine the minimum value |
DataSet.prototype.maxByCol = function( column, compare )
returns the “largest” value of a column.
| column | column to compare |
| compare | Optiional, default: function(a,b){return a > b} A compare function like sort() uses to determaxe the maximum value |
DataSet.prototype.sumByCol = function( column, accessor )
returns a sum of the values of a column.
| column | column to sum |
| accessor | Optional, default: function(element){return element} A function that takes an element of the column and returns a value to be summed. This is useful to force integer math or to sum a property of the objects in the column rather than the objects themselves. |
DataSet.prototype.sortByCol = function( column, compare )
sorts the DataSet by the supplied column and compare function.
| column | column to sort |
| compare | Optiional, default: String.compareAlpha A compare function that takes 2 elements and returns either 1, 0, or -1 |
var files = new Myna.File("/").listFiles()
files.sortByCol("fileName",String.compareNatural)Returns:
DataSet.prototype.avgByCol = function( column, accessor )
returns an average of the column.
| column | column to average |
| accessor | Optional, default: function(element){return element} A function that takes an element of the column and returns a value to be averaged. This is useful to force integer math or to average a property of the objects in the column rather than the objects themselves. |
null values are ignored. If you want to count nulls as 0, use this accessor
function(element){
return element===null?0:element;
}
DataSet.prototype.pivot = function( keyField, categoryField, valueField )
returns a new DataSet pivoted around a key, category, and value
| keyField | Column name that contains the unique value for every row in the result. Duplicate values for calculated columns will overwrite, missing values will be set to null |
| categoryField | Column name that contains the new columns that should be created. These names will be cleaned such that invalid characters are replaced with “_” and the result is lower cased. If this would result in a blank column (such as numeric values) then “category_<value>” is used for the column name. If that still doesn’t work, then “category__unknown” is used for the column name |
| valueField | Column name that contains the values for each key |
The purpose of this function is to convert a data set that looks like this
user | category | value
bob | Age | 35
sally | Age | 25
bob | Favorite Color | blue
sally | Favorite Color | yellow
bob | Start Date | 01/01/2001
sally | Start Date | 05/16/1997
into something like this
user | age | favorite_color | start _date
bob | 35 | blue | 01/01/2001
sally | 25 | yellow | 05/16/1997
The above transform would be accomplished with
ds.pivot("user","category","value")
DataSet.prototype.toStruct = function( keyCols, remainingProperty, full )
converts a DataSet into an hierarchical object
| keyCols | Array of column names, in order of significance, |
| remainingProperty | Optional, default null if keyCols does not uniquely identify every row in the DataSet, and remainingProperty is defined, then the remain rows will be added to this property as an array. |
| full | Optional, default false If true, each level in the hierarchy contains all the values of the first row of that branch, and sub trees branch of the col name |
The purpose of this function is to convert flat result sets into a structured hierarchy. This is best illustrated by examples
// Original Set
// employee_id | title | position_code | department_name | department_code
// ----------- | --------------------- | ------------- | -------------------------- | ---------------
// 100000001 | Cp Tech-Mental Health | 01021C | MILAGRO | 01106550
// 100000003 | Universal Interviewer | 054700 | MED SPECIALTIES CLINIC B | 01017120
// 100000075 | Clerk Outpt | 054700 | MED SPECIALTIES CLINIC B | 01017120
// 100001035 | Clerk Outpt | 054700 | MED SPECIALTIES CLINIC B | 01017120
var simple = original_set.toStruct(["position_code","department_code"])
[ Object ]
+-[01021C] [ Object ]
| \-[01106550] [ Array ]
| \-[0] [ Object ]
| +-[department_code] 01106550
| +-[department_name] MILAGRO
| +-[employee_id] 100000001
| +-[position_code] 01021C
| \-[title] Cp Tech-Mental Health
\-[054700] [ Object ]
\-[01017120] [ Array ]
+-[0] [ Object ]
| +-[department_code] 01017120
| +-[department_name] MED SPECIALTIES CLINIC B
| +-[employee_id] 100000003
| +-[position_code] 054700
| \-[title] Universal Interviewer
+-[1] [ Object ]
| +-[department_code] 01017120
| +-[department_name] MED SPECIALTIES CLINIC B
| +-[employee_id] 100000075
| +-[position_code] 054700
| \-[title] Clerk Outpt
\-[2] [ Object ]
+-[department_code] 01017120
+-[department_name] MED SPECIALTIES CLINIC B
+-[employee_id] 100001035
+-[position_code] 054700
\-[title] Clerk Outpt
var simple_with_rows = original_set.toStruct(["position_code","department_code"],"rows")
[ Object ]
+-[01021C] [ Object ]
| \-[01106550] [ Object ]
| \-[rows] [ Array ]
| \-[0] [ Object ]
| +-[department_code] 01106550
| +-[department_name] MILAGRO
| +-[employee_id] 100000001
| +-[position_code] 01021C
| \-[title] Cp Tech-Mental Health
\-[054700] [ Object ]
\-[01017120] [ Object ]
\-[rows] [ Array ]
+-[0] [ Object ]
| +-[department_code] 01017120
| +-[department_name] MED SPECIALTIES CLINIC B
| +-[employee_id] 100000003
| +-[position_code] 054700
| \-[title] Universal Interviewer
+-[1] [ Object ]
| +-[department_code] 01017120
| +-[department_name] MED SPECIALTIES CLINIC B
| +-[employee_id] 100000075
| +-[position_code] 054700
| \-[title] Clerk Outpt
\-[2] [ Object ]
+-[department_code] 01017120
+-[department_name] MED SPECIALTIES CLINIC B
+-[employee_id] 100001035
+-[position_code] 054700
\-[title] Clerk Outpt
var full_with_rows = original_set.toStruct(["position_code","department_code"],"rows")
[ Object ]
+-[department_code] 01106550
+-[department_name] MILAGRO
+-[employee_id] 100000001
+-[position_code] [ Object ]
| +-[01021C] [ Object ]
| | +-[department_code] [ Object ]
| | | \-[01106550] [ Object ]
| | | +-[department_code] 01106550
| | | +-[department_name] MILAGRO
| | | +-[employee_id] 100000001
| | | +-[position_code] 01021C
| | | +-[rows] [ Array ]
| | | | \-[0] [ Object ]
| | | | +-[department_code] 01106550
| | | | +-[department_name] MILAGRO
| | | | +-[employee_id] 100000001
| | | | +-[position_code] 01021C
| | | | \-[title] Cp Tech-Mental Health
| | | \-[title] Cp Tech-Mental Health
| | +-[department_name] MILAGRO
| | +-[employee_id] 100000001
| | +-[position_code] 01021C
| | \-[title] Cp Tech-Mental Health
| \-[054700] [ Object ]
| +-[department_code] [ Object ]
| | \-[01017120] [ Object ]
| | +-[department_code] 01017120
| | +-[department_name] MED SPECIALTIES CLINIC B
| | +-[employee_id] 100000003
| | +-[position_code] 054700
| | +-[rows] [ Array ]
| | | +-[0] [ Object ]
| | | | +-[department_code] 01017120
| | | | +-[department_name] MED SPECIALTIES CLINIC B
| | | | +-[employee_id] 100000003
| | | | +-[position_code] 054700
| | | | \-[title] Universal Interviewer
| | | +-[1] [ Object ]
| | | | +-[department_code] 01017120
| | | | +-[department_name] MED SPECIALTIES CLINIC B
| | | | +-[employee_id] 100000075
| | | | +-[position_code] 054700
| | | | \-[title] Clerk Outpt
| | | \-[2] [ Object ]
| | | +-[department_code] 01017120
| | | +-[department_name] MED SPECIALTIES CLINIC B
| | | +-[employee_id] 100001035
| | | +-[position_code] 054700
| | | \-[title] Clerk Outpt
| | \-[title] Universal Interviewer
| +-[department_name] MED SPECIALTIES CLINIC B
| +-[employee_id] 100000003
| +-[position_code] 054700
| \-[title] Universal Interviewer
\-[title] Cp Tech-Mental Health
DataSet.prototype.toMap = function( keyCol, valueCol, valuesInArray )
return a JS object where a column is used for the keys
| keyCol | Column to use for keys |
| valueCol | Optional, default null If defined, column to use for values. Otherwise the entire row will be the value |
| valuesInArray | Optional, default false If true, values will be in an array in order of appearance. Otherwise the last value for valueCol will be assigned |
var empMap = empDataSet.toMap("employee_id","employee_name")
var selectedName = empMap[selectedEmpId];Creates a new DataSet Object
var DataSet =function DataSet( options, accessFunction, lengthFunction )
returns true if any row in the DataSet has a column value that matches compare
DataSet.prototype.containsByCol = function( column, compare )
returns the first row in the DataSet whose column value matches compare, or null if no matches
DataSet.prototype.findFirstByCol = function DataSet_findFirst( column, compare )
returns a new DataSet of all the rows in this DataSet whose column value matches compare
DataSet.prototype.findAllByCol = function DataSet_findAll( column, compare )
returns an array of the values of a column.
DataSet.prototype.valueArray=function( columnName )
Creates a new DataSet with the results of calling a provided function on every element in this array.
DataSet.prototype.map = function( func )
Performs Array.filter, but returns a new DataSet with the same columns as this one
DataSet.prototype.filter = function()
Performs Array.concat, but returns a new DataSet with the same columns as this one
DataSet.prototype.concat = function( otherArray )
Performs Array.slice, but returns a new DataSet with the same columns as this one
DataSet.prototype.slice = function()
merges another DataSet into this one, by a common column value
DataSet.prototype.merge = function( ds, column )
returns the “smallest” value of a column.
DataSet.prototype.minByCol = function( column, compare )
returns the “largest” value of a column.
DataSet.prototype.maxByCol = function( column, compare )
returns a sum of the values of a column.
DataSet.prototype.sumByCol = function( column, accessor )
sorts the DataSet by the supplied column and compare function.
DataSet.prototype.sortByCol = function( column, compare )
returns an average of the column.
DataSet.prototype.avgByCol = function( column, accessor )
returns an HTML table of this dataset
DataSet.prototype.toHtmlTable = function()
returns a new DataSet pivoted around a key, category, and value
DataSet.prototype.pivot = function( keyField, categoryField, valueField )
converts a DataSet into an hierarchical object
DataSet.prototype.toStruct = function( keyCols, remainingProperty, full )
return a JS object where a column is used for the keys
DataSet.prototype.toMap = function( keyCol, valueCol, valuesInArray )
Creates a new array with the results of calling a provided function on every element in this array.
if ( !Array.prototype.map )
Creates a new array with all elements that pass the test implemented by the provided function.
if ( !Array.prototype.filter )