Table

Table

It realizes an object to store data in Data.Table format and
provides the methods to load, read and process it

Constructor

new Table()

Source:
Create a new Data.Table instance.

All data loaded by the methods .feed(), .object() and .broker() is stored in a Table instance.

The Table class provides the methods to read and process the data.

Example
// the data of the Table object ist stored like this example:
{
    table : {    
              fields:3,
             records:2
            },
   fields : [
             {id:"column 1"},
             {id:"column 2"},
             {id:"column 3"}
            ],
  records : [
             ["value11","value12","value13"],
             ["value21","value22","value23"]
            ]
}
Returns:
A new Data.Table object

in the following the methods of the Data.Table to read and process the data

Methods

addColumn(options, function(currentValue)) → {Data.Table}

Source:
creates a new column based on existing ones
the values of the new column are defined by a user function, which receives data from the actual row and must returns the new value
Example
mydata = mydata.addColumn({'source':'created_at','destination':'date'},
       function(value){
           var d = new Date(__normalizeTime(value));
           return( String(d.getDate()) + "." + String(d.getMonth()+1) + "." + String(d.getFullYear()) );
    });
Parameters:
Name Type Description
options object the creation parameter
propertydescription
"source"[optional] the name of the source column
"destination"the name of the new colmn to create
function(currentValue) function Required: A function to be run for each element in the array
Function arguments:
argumentdescription
currentValuethe value of the current source column cell or
an array of all values of the current row, if non source column is defined
Must return the values for the new column.
It is called for every row of the table and receives as parameter the value of the source column, or, if no source column defined, an array of all values of the table row.
Returns:
the enhanced table
Type
Data.Table

addRow(options) → {Data.Table}

Source:
adds a row to the data
the values of columns are defined by a JSON Object, which defines values for selected columns; non defined columns are set to ' '
Example
mydata = mydata.addRow({'column 1':'Rossi','column 2':'Aldo'} );
Parameters:
Name Type Description
options object the creation parameter
propertydescription
"column name"value
"column name"value
Returns:
the enhanced table
Type
Data.Table

aggregate(valueColumn, aggregateColumn)

Source:
aggregate the values of one column for the unique values of one or more other columns
usefull to transform journals with more than one qualifying column (time, product class, ...)
into something like a pivot table
Examples
myData.aggregate("value","month|type");
	     
 // "value"     : the value source column is named "value"
 // "month|type": columns "month" and "type" will lead the aggregation
if the source table is like:

 "data"     "month" "day" "hour" "operator" "type" "value"
 2015/07/15 "jul"   15    03     "everyone" "wood" 15  
 2015/07/15 "jul"   15    06     "clerk"    "iron" 25  
 2015/07/16 "jul"   16    11     "clerk"    "iron" 32  
 2015/07/22 "jul"   16    15     "carp"     "wood" 17  
 2015/08/02 "aug"   02    22     "carp"     "wood" 22  
 ...

 the result will be:

 "month" "type" "value"
 "jul"   "wood"  32
 "jul"   "iron"  57 
 "aug"   "wood"  22
Parameters:
Name Type Description
valueColumn String the value source
aggregateColumn String the aggregation leads; more than one column can be defined with seperator '|'
example: "month|type"
Returns:
Data.Table object with the aggregation result in dbTable format

append(sourceTable)

Source:
appends the rows of a data table to the actual table
! important: the structure of both tables must be identical, i.e. same column count and names
Parameters:
Name Type Description
sourceTable Data.Table table the source of the rows to append
Returns:
the extended table

column(columnName) → {Column}

Source:
get a column object for one column from the Data.Table
the column object provides methods to read or map the column values
Example
var myfeed = new Data.Feed("Segnalazioni",{"source":szUrl,"type":"csv"}).load(function(mydata){
   var dateArray = mydata.column('created_at').values();
   ...
});
Parameters:
Name Type Description
columnName String the name of the column to get a handle to
Returns:
Data.Column object
Type
Column

columnIndex(columnName) → {int}

Source:
get the index of a column by its name
useful if you have the values of one data row as array and want to access a column value
Parameters:
Name Type Description
columnName String the name of the column
Returns:
the index of the column or null
Type
int

columnNames()

Source:
get an array of the column names
Returns:
an array with the column names

condense(leadColumn, option)

Source:
condense (aggregate) the rows of a table by the unique values of one column
sums the numeric values of the rows with the same leed column value
don't sum the values of columns defined as 'keep' in the 'option'
Example
data.condense({lead:'name',keep:'codice'});

 if the source table is like:

 "name"     "codice" "hours"
 "everyone" "001"     15  
 "clerk"    "002"     25  
 "clerk"    "002"     32  
 "carp"     "005"     17  
 "carp"     "005"     22  
 ...

 the result will be:

 "name"     "codice" "value"
 "everyone" "001"     15
 "clerk"    "002"     57 
 "carp"     "005"     39
Parameters:
Name Type Description
leadColumn String the column of the values to make unique
option Object parameter
Returns:
the condensed table

filter(the) → {Table}

Source:
filter rows from a dbtable objects data by callback
Example
mydata.filter(
       function(row){
           return( (row[0] == 'filtervalue') ? 1 : 0 );
    });
Parameters:
Name Type Description
the function user defined filter function, must return 0 or 1
Returns:
Type
Table

getArray()

Source:
get the data of a Data.Table as 2d array first row are the column names
Returns:
table as array of arrays

groupColumns(options) → {Data.Table}

Source:
creates a new column based on existing ones
the values of the new column are the sum of the source columns
Example
mydata = mydata.groupColumns({'source':['col_1','col_2'],'destination':'col_sum'});
Parameters:
Name Type Description
options object the creation parameter
propertydescription
"source"the name of the source columns
"destination"the name of the new colmn to create
Returns:
the enhanced table
Type
Data.Table

json()

Source:
creates a json object array from the table
every row creates an array element array elements are of type: { name_1: value_1, name_2: value_2, ... }

lookup(value, option)

Source:
get the value of a column cell by the known value of a lookup column
Parameters:
Name Type Description
value the value we know
option object a json structure with {value:value column name, lookup:lookup column name}
Returns:
the found value

lookupArray(szValue, szLookup) → {array}

Source:
get an associative array of the values of two columns like array[String(lookup column value)] = value
Example
id           nome
-------------------------------------------
00000000000  ITALIA
01000000000  PIEMONTE 1
01100000000  PIEMONTE 1 - 01
01110000000  01 TORINO - ZONA STATISTICA 16
01110812620  TORINO - PIEMONTE 1 - 01 - 01
01120000000  02 TORINO - ZONA STATISTICA 38
...

// create assoc.array with id ==> nome from camera_geopolitico_italia.csv (id == ELIGENDO_C_UID_CI)
var nomeA = camera_geopolitico_italia.lookupArray("nome","id");

['00000000000']="ITALIA";
['01000000000']="PIEMONTE 1";
['01100000000']="PIEMONTE 1 - 01";
['01110000000']="01 TORINO - ZONA STATISTICA 16";
['01110812620']="TORINO - PIEMONTE 1 - 01 - 01";
['01120000000']="02 TORINO - ZONA STATISTICA 38";
...
Parameters:
Name Type Description
szValue String the name of the value column
szLookup String the name of the lookup value column
Returns:
associative array for lookup
Type
array

lookupStringArray(szValue, szLookup) → {array}

Source:
get an associative array of the values of two columns like array[String(lookup column value)] = value only for string values, creates aggregated string of multiple values
Example
id           nome
-------------------------------------------
00000000000  ITALIA
00000000000  PIEMONTE 1
01100000000  PIEMONTE 1 - 01
01100000000  01 TORINO - ZONA STATISTICA 16
01110812620  TORINO - PIEMONTE 1 - 01 - 01
01120000000  02 TORINO - ZONA STATISTICA 38
...

// create assoc.array with id ==> nome from camera_geopolitico_italia.csv (id == ELIGENDO_C_UID_CI)
var nomeA = camera_geopolitico_italia.lookupArray("nome","id");

['00000000000']="ITALIA, PIEMONTE 1";
['01100000000']="PIEMONTE 1 - 01, 01 TORINO - ZONA STATISTICA 16";
['01110812620']="TORINO - PIEMONTE 1 - 01 - 01";
['01120000000']="02 TORINO - ZONA STATISTICA 38";
...
Parameters:
Name Type Description
szValue String the name of the value column
szLookup String the name of the lookup value column
Returns:
associative array for lookup
Type
array

pivot(options)

Source:
creates a pivot table
Example
// we have a table 'scrutini' with election results like:
	
assemblea  codice       tipo   tipo_riga  cand_descr_riga  descr_lista              voti   perc    
--------------------------------------------------------------------------------------------------
Camera     01110812620  Comune CA         ANDREA GIORGIS                            49654  "40,93"
Camera     01110812620  Comune LI                          PARTITO DEMOCRATICO      33228  "28,75" 
Camera     01110812620  Comune LI                          +EUROPA                  12970  "11,22"
Camera     01110812620  Comune LI                          ITALIA EUROPA INSIEME    846    "0,73"
Camera     01110812620  Comune LI                          CIVICA POPOLARE LORENZIN 601    "0,52" 
...

// --------------------------------------------------------------------------------------------
// make pivot table with columns == descr_lista (partiti)  
// --------------------------------------------------------------------------------------------

var pivot = scrutini.pivot({
             "lead":	'codice',
             "keep":	['tipo'],
             "sum":	['membri'],
             "cols":	'descr_lista',
             "value":  "voti" 
             });


// the resulting pivot table is:

codice       tipo   PARTITO DEMOCRATICO +EUROPA  ITALIA EUROPA INSIEME  CIVICA POPOLARE LORENZIN    
--------------------------------------------------------------------------------------------------
01110812620  Comune 33228               12970    846                    601
...
Parameters:
Name Type Description
options Object the pivot creation parameter
Property Description
lead the sourcetable field that defines the pivot rows
keep columns of the sourcetable to copy into the pivot
sum columns of the sourcetable to copy and sum into the pivot
cols the sourcetable field that defines the pivot columns (together with 'keep')
value the sourcetable field where to get the value to accumulate if '1', count the cases of the cols topicsthan
Returns:
the pivot table

reverse()

Source:
reverse the rows of a data table
Returns:
the reversed table

revert()

Source:
revert the rows of a data table
Returns:
the reverted table

select(szSelection)

Source:
select rows from a dbtable objects data by SQL query
Example
var mydata   =  mydata.select('WHERE description like "montana"');
var ageTotal = rawdata.select('WHERE "Age" = "Total" AND "SEX" = "MW" AND "Series" = "Labour force participation rate"');
var ageWork  = rawdata.select('WHERE "Age" BETWEEN "18" AND "65"');
Parameters:
Name Type Description
szSelection String the selection query string
WHERE "column name" [operator] "selection value"
Operator Description
= Equal
<> Not equal. Note: In some versions of SQL this operator may be written as !=
> Greater than
< Less than
>= Greater than or equal
<= Less than or equal
BETWEEN Between an inclusive range;
example: WHERE "column" BETWEEN "value1" AND "value2"
LIKE Search for a pattern
NOT Must not contain pattern
IN To specify multiple possible values for a column;
example: WHERE "column" IN "value1,value2,value3"
Returns:
Data.Table object with the selection result in dbTable format

setArray(dataA)

Source:
set the data of a Data.Table by a given 2d array first row must be the column names
Parameters:
Name Type Description
dataA Array a 2 dimensionale array with the table data
first row must contain the column names
Returns:
itself

sort(sortColumn)

Source:
sort the rows of a data table by values of a given column
Parameters:
Name Type Description
sortColumn String the column by which values to sort the table
Returns:
the sorted table

subtable(options)

Source:
creates a sub table
which only contains the specified columns
Examples
subTable = table.subtable({"columns":[1,2,3]});
subTable = table.subtable({"fields":['comune_scr','provincia_scr','Lat','Lon']});
Parameters:
Name Type Description
options object the subtable columns definition; use either 'columns' or 'fields'
Property Description
columns array of column indices
fields array of column names
Returns:
the generated sub table