The rank procedure computes the ranks of the elements of a column in a and creates an output table with ranks instead of column elements. The smallest value from input table has rank 1 in the output table and the subsequent values from input table have ranks incremented by 1. Equal values have the same rank equal to the smallest possible rank. Such conversion is performed for every column separately.
To use the rank command it is necessary to import it from the specialized.data Gython library module.
Syntax:
rank(alias.intable, alias.outtable, columns)
alias - an alias pointing to the database
intable - input table name
outtable - output table name
columns - the list of names of columns which are to be ranked (if no column is specified then the values from all columns will be ranked)
Example 16.34. Rank procedure
table 'sport_cars':
name weight speed cost
'skoda' 1200 260 10.000
'renault' 1360 280 10.500
'opel' 900 240 8.200
'VW' 1400 280 12.00
'Fiat' 1450 260 7.800
'Alfa Romeo' 1300 280 11.000
from specialized.data import rank
rank('sport_cars', 'sport_cars_rank')
tab = tableRead('sport_cars_rank')
for i in range(len(tab)):
for j in range(len(tab[i])):
print tab[i][j],
print
Output:
name weight speed cost
skoda 2.0 2.5 3.0
renault 4.0 5.0 4.0
opel 1.0 1.0 2.0
VW 5.0 5.0 6.0
Fiat 6.0 2.5 1.0
Alfa Romeo 3.0 5.0 5.0
The interpolate procedure performs data interpolation in table columns. The result is a new table. The output table contains the values of independent and dependent variables. The independent variable is modified in such a way that starting with the smallest value new observations are added with a value increment equal to the step parameter. The correspondent dependent values are interpolated by cubic splines (the required derivative boundary values are equal to 0). To improve the procedure execution time it is recommended to define the column with the independent variable as the PRIMARY KEY.
To use the interpolate command it is necessary to import it from the specialized.data Gython library module.
Syntax:
interpolate(alias.intable, alias.outtable, independent, step, dependents)
alias - an alias pointing to a database
intable - the name of the input table
outtable - the name of the output table
independent - the name of the column with the independent variable
step - the increment step to alter values of the independent variable in the output table
dependents - a list of names of columns with dependent variables. If no dependent column name is specified computations are performed for all columns.
Example 16.35. Interpolate procedure:
table 'interpolate_in':
x y1 y2
0 None None
1 None None
3 None 8
4 5 6
6 9 None
from specialized.data import interpolate
interpolate('interpolate_in', 'interpolate_out', 'x', 0.5)
tab = tableRead('interpolate_out')
for i in range(len(tab)):
for j in range(len(tab[i])):
print tab[i][j],
print
Output:
x y1 y2
3.0 NULL 8.0
3.5 NULL 7.0
4.0 5.0 6.0
4.5 6.0 NULL
5.0 7.0 NULL
5.5 8.0 NULL
6.0 9.0 NULL
The sample command creates a table with a random sample of values from the input table. The output table can contain all columns or only selected ones.
Syntax:
sample(alias.intable, alias.outtable, outsize, columnNames, withReplacement=0|1, seed)
alias - an alias pointing to the database
intable - the input table name
outtable - the output table name
outsize - the number of rows in the output table to sample (i.e. the number of generated sample values)
columnNames - the list of column names to sample (if no column is specified then all columns will be sampled)
withReplacement - if 1 sampling with replacement. Default: 0
seed - by this parameter you can specify a constant seed for the random number generator. If this parameter is not specified, the default random number generator initialization is used.
Example 16.36. Sample procedure
table 'transactions':
id value type
110 210 'outgoing'
120 320 'outgoing'
130 480 'incoming'
140 338 'incoming'
150 270 'outgoing'
160 302 'incoming'
170 None 'outgoing'
180 420 'incoming'
190 570 'incoming'
200 380 'outgoing'
210 90 'incoming'
220 None 'incoming'
230 800 'incoming'
230 670 'outgoing'
sample('transactions', 'transactions_sample1', 5, withReplacement=1, seed=1234)
sample('transactions', 'transactions_sample2', 5, ['id', 'value'], seed=1234)
strFormat = "%-10s%-10s%-10s"
print "Table transactions_sample1"
print strFormat%("id","value","type")
trans None <- 'transactions_sample1':
print $strFormat%(id,value,type)
strFormat = "%-10s%-10s"
print "\nTable transactions_sample2"
print strFormat%("id","value")
trans None <- 'transactions_sample2':
print $strFormat%(id,value)
Output:
Table transactions_sample1
id value type
150 270 outgoing
110 210 outgoing
210 90 incoming
130 480 incoming
230 800 incoming
Table transactions_sample2
id value
150 270
160 302
170 None
190 570
230 800
The tableSplit command splits one table into a number of tables. It may yield the number of tables specified in the split rules list or yield just one table with an additional GroupId column. The GroupId column contains the identifiers of the groups to which the rows are assigned.
The input table can be placed in a different database than the output tables, but some databases are incompatible. For example, a mySQL table can not be split into MSSQL tables because MSSQL does not support the data type double. Moreover, output tables can be placed in different databases. If you want to use this feature you have to specify an alias for every database you want to use.
Syntax:
tableSplit(alias.inTable[, group = 0 | 1][, split = l][, seed = n]
[, output = l])
The meaning of the parameters is explained below:
alias - an alias pointing to the database
inTable - the input table name
group - set this to 1 if you want tableSplit to produce only one table with an added GroupId column which contains the identifiers of the groups to which the current rows belong. By default this parameter is set to 0, so tableSplit yields as many tables as there are split rules.
split - here you specify split proportions. For example split = [3,7] will generate two tables with 30% and 70% randomly taken rows of the input table. It is posssible to specify more than two split shares, e.g. split = [10, 20, 30, 10, 30] will yield 5 tables with ca. 10%, 20%, 30%, 10% and 15 % rows of the input table in each.
seed - with this parameter you can specify a constant seed for the random number generator. If this parameter is not specified, the default random number generator initialization is used.
output - use this parameter to specify the convention of output table naming. If this parameter is not supplied, tableSplit yields output table names as combinations of the input table name and a consequent number - i.e if the input table has the name "test_data" and you split it into two tables, the output names will be "test_data_1", "test_data_2".
You can also specify an output table name prefix - in the example, output="splits" will generate "splits_1", "splits_2", "splits_3", and so on.
You can also provide explicit output names. In this case the number of provided names has to match the number of split rules, e.g. output=["a", "b"], split=[3, 4]
The assignment of rows to groups/tables is performed by a random procedure. For each table there is a probability (specified by the split rules list) that the current row will be assigned to this table. If you try to split the table into two equal parts (split = [1,1]), the row count for each of them may be a little different than exactly 50% of the input table rows, but the sum of the numbers of rows of the output tables will be equal to the number of rows in the input table.
The discrepancies between the proportions in split rules and the actual number of rows in output tables decline as the number of rows in the input table increases.
Example 16.37. Split table
table 'sport_cars':
name weight speed cost
'skoda' 1200 260 10.000
'renault' 1360 280 10.500
'opel' 900 240 8.200
'VW' 1400 280 12.00
'Fiat' 1450 260 7.800
'Alfa Romeo' 1300 280 11.000
# This yields two tables - "sport_cars_part_1" and "sport_cars_part_2"
tableSplit('sport_cars', split=[1, 2], seed=124, output=['sport_cars_part'])
print 'First part:'
trans None <- 'sport_cars_part_1':
print name,weight,speed,cost
print 'Second part:'
trans None <- 'sport_cars_part_2':
print name,weight,speed,cost
Output:
First part:
opel 900 240 8.2
Second part:
skoda 1200 260 10.0
renault 1360 280 10.5
VW 1400 280 12.0
Fiat 1450 260 7.8
Alfa Romeo 1300 280 11.0
The transpose procedure performs table transposition (columns are switched with rows). In the output table a column called 'name' is created with the values in this column being column names from the input table and columns named 'columnX' (where X is the row number from the input table) are created. Transposition will succeed only if the data type of all columns from the input table can be generalized to one data type.
To use the transpose command it is necessary to import it from the specialized.data Gython library module.
Syntax:
transpose(alias.intable, alias.outtable)
alias - an alias pointing to a database
intable - the name of the input table
outtable - the name of the output table
Example 16.38. The transpose procedure
table 'matrix':
a b c
1 2 3
4 5 6
7 8 9
from specialized.data import transpose
tableDelete('trans_matrix')
transpose('matrix', 'trans_matrix')
tab = tableRead('trans_matrix')
for i in range(len(tab)):
for j in range(len(tab[i])):
print tab[i][j],
print
Output:
name column1 column2 column3
a 1.0 4.0 7.0
b 2.0 5.0 8.0
c 3.0 6.0 9.0
The tablesCompare command displays basic information about the comparison of two tables:
columns existing only in the first table
columns existing only in the second table
columns which have different data types
the number of rows with the same keyc value
the number of extra rows in the first table in comparison with the second one
the number of extra rows in the second table in comparison with the first one
the number of rows with matching keys and different values in the two tables
It is possible to map column names from one table to different column names in the other table.
Rows with NULL key values are omitted.
Suntax:
tablesCompare(alias.table1, key1, alias.table2, key2[, {var1: var2,...}][, silent= 0 | 1])
alias - an alias pointing to the database
table1, table2 - the names of the tables to compare
key1, key2 - the keys which are to be used for comparison
var1, var2 - the names of the corresponding columns in the tables (if no columns are specified here then it is required that columns in both tables have the same names)
silent - if 1 the result of comarision will not be shown in a new extealrn window. Default:0
Example 16.39. The tablesCompare procedure
table 'compare1':
format c INTEGER, d INTEGER
a b c d f
1 1 None None 1
2 2 None None 2
3 3 None None 1
4 4 None None 2
6 6 None None 1
7 7 None None 2
8 8 None None 1
table 'compare2':
format d VARCHAR(32), e INTEGER
aa bb d e f
4 4 None None 1
5 5 None None 1
6 5 None None 2
7 5 None None 2
8 8 None None 1
result = tablesCompare('compare1', 'f','compare2', 'f',silent=1)
print "Comparison - (tablesCompare('compare1', 'f','compare2', 'f')):"
print "Variable presented only in first table:", result.columnsInFirst
print "Variable presented only in second table:", result.columnsInSecond
print "Variables with different types:", result.columnsWithDifferentTypes
print "Matching rows:",result.matchingRows
print "Unmatched rows in the first table:", result.unmatchedRowsInFirst
print "Unmatched rows in the second table:", result.unmatchedRowsInSecond
print "Rows with different values:",result.rowsWithDifferentValues
result = tablesCompare('compare1', 'a','compare2', 'aa', {'b' : 'bb'},silent=1)
print "\nComparison - (tablesCompare('compare1', 'a','compare2', 'aa', {'b' : 'bb'})):"
print "Variable presented only in first table:", result.columnsInFirst
print "Variable presented only in second table:", result.columnsInSecond
print "Variables with different types:", result.columnsWithDifferentTypes
print "Matching rows:",result.matchingRows
print "Unmatched rows in the first table:", result.unmatchedRowsInFirst
print "Unmatched rows in the second table:", result.unmatchedRowsInSecond
print "Rows with different values:",result.rowsWithDifferentValues
Output:
Comparison - (tablesCompare('compare1', 'f','compare2', 'f')):
Variable presented only in first table: ['a', 'c', 'b']
Variable presented only in second table: ['bb', 'aa', 'e']
Variables with different types: ['d']
Matching rows: 5
Unmatched rows in the first table: 2
Unmatched rows in the second table: 0
Rows with different values: 0
Comparison - (tablesCompare('compare1', 'a','compare2', 'aa', {'b' : 'bb'})):
Variable presented only in first table: ['c']
Variable presented only in second table: ['e']
Variables with different types: ['d']
Matching rows: 4
Unmatched rows in the first table: 3
Unmatched rows in the second table: 1
Rows with different values: 3