This module defines the table class that provides convenient functionality to work with tabular data. It features functions to calculate statistical moments, e.g. mean, standard deviations as well as functionality to plot the data using matplotlib.
Populate table with data:
from tap import *
# create table with two columns, x and y both of float type
tab=Tab(['x', 'y'], 'ff')
for x in range(1000):
tab.add_row([x, x**2])
# create a plot
plt=tab.plot('x', 'y', save='x-vs-y.png')
Iterating over table items:
# load table from file
tab=load(...)
# iterate over all rows
for row in tab.rows:
# print complete row
print row
for f in tab.foo:
print f
# iterate over all rows of selected columns
for foo, bar in tab.zip('foo','bar'):
print foo, bar
Adding/Removing/Reordering data | |
add_row() | add a row to the table |
add_col() | add a column to the table |
remove_col() | remove a column from the table |
rename_col() | rename a column |
extend() | append a table to the end of another table |
merge() | merge two tables together |
sort() | sort table by column |
filter() | filter table by values |
zip() | extract multiple columns at once |
seach_col_names() | search for matching column names |
Input/Output | |
save() | save a table to a file |
load() | load a table from a file |
to_string() | convert a table to a string for printing |
Simple Math | |
min() | compute the minimum of a column |
max() | compute the maximum of a column |
sum() | compute the sum of a column |
mean() | compute the mean of a column |
row_mean() | compute the mean for each row |
median() | compute the median of a column |
std_dev() | compute the standard deviation of a column |
count() | compute the number of items in a column |
More Sophisticated Math | |
correl() | compute Pearson’s correlation coefficient |
spearman_correl() | compute Spearman’s rank correlation coefficient |
compute_mcc() | compute Matthew’s correlation coefficient |
compute_roc() | compute receiver operating characteristics (ROC) |
compute_enrichment() | compute enrichment |
get_optimal_prefactors() | compute optimal coefficients for linear combination of columns |
Plot | |
plot() | Plot data in 1, 2 or 3 dimensions |
plot_histogram() | Plot data as histogram |
plot_roc() | Plot receiver operating characteristics (ROC) |
plot_enrichment() | Plot enrichment |
plot_hexbin() | Hexagonal density plot |
plot_bar() | Bar plot |
Table columns have a specific type, e.g. string, float etc. Each cell in a column must either be of that type or set to not available (None). As a result, a float column can’t contain string values. The following column types exist:
long name | abbreviation |
---|---|
string | s |
float | f |
int | i |
bool | b |
When adding new data to the table, values are automatically coerced (forced) to the column type. When coercing fails, a ValueError is thrown.
The column types can be specified when initialing a new table. For convenience, several different formats are supported, which allow to specify the column types as strings, or list using long, or abbreviated forms. The following 5 examples initialise an empty table with a string, float, int and bool column each.
# abbreviated, compact form
tab = Tab(['x', 'y', 'z', 'u'], 'sfib')
# abbreviated, separated by coma
tab = Tab(['x', 'y', 'z', 'u'], 's, f, i, b')
# extended separated by coma
tab = Tab(['x', 'y', 'z', 'u'], 'string, float, int, bool')
# list abbreviated
tab = Tab(['x', 'y', 'z', 'u'], ['s', 'f', 'i', 'b'])
# list extended
tab = Tab(['x', 'y', 'z', 'u'], ['string', 'float', 'int', 'bool'])
For the lazy, the table supports guessing the column type from data when initialising a new table. The detection of column types tries to convert each value to a particular type, e.g. int. When the type conversion is not successful for any value, the column type is set to string. As a special case, when the data arrays are empty, the array types are set to string.
# initialises a table with an bool and int column
t = Tab(['x','y'], x='True False False'.split(), y='1 NA 3'.split())
print t.col_types # bool int
The table class provides convenient access to data in tabular form. An empty table can be easily constructed as follows
tab=Tab()
If you want to add columns directly when creating the table, column names and column types can be specified as follows
tab=Tab(['nameX','nameY','nameZ'], 'sfb')
this will create three columns called nameX, nameY and nameZ of type string, float and bool, respectively. When the second argument is omitted, the columns will all have a string type. There will be no data in the table and thus, the table will not contain any rows.
The following column types are supported:
name | abbrev |
---|---|
string | s |
float | f |
int | i |
bool | b |
If you want to add data to the table in addition, use the following:
tab=Tab(['nameX','nameY','nameZ'],
'sfb',
nameX=['a','b','c'],
nameY=[0.1, 1.2, 3.414],
nameZ=[True, False, False])
if values for one column is left out, they will be filled with NA, but if values are specified, all values must be specified (i.e. same number of values per column).
Add a column to the right of the table.
Parameters: |
|
---|
Example:
tab=Tab(['x'], 'f', x=range(5))
tab.add_col('even', 'bool', itertools.cycle([True, False]))
print tab
'''
will produce the table
==== ====
x even
==== ====
0 True
1 False
2 True
3 False
4 True
==== ====
'''
If data is a constant instead of an iterable object, it’s value will be written into each row:
tab=Tab(['x'], 'f', x=range(5))
tab.add_col('num', 'i', 1)
print tab
'''
will produce the table
==== ====
x num
==== ====
0 1
1 1
2 1
3 1
4 1
==== ====
'''
As a special case, if there are no previous rows, and data is not None, rows are added for every item in data.
Add a row to the table.
data may either be a dictionary or a list-like object:
- If data is a dictionary the keys in the dictionary must match the column names. Columns not found in the dict will be initialized to None. If the dict contains list-like objects, multiple rows will be added, if the number of items in all list-like objects is the same, otherwise a ValueError is raised.
- If data is a list-like object, the row is initialized from the values in data. The number of items in data must match the number of columns in the table. A ValuerError is raised otherwise. The values are added in the order specified in the list, thus, the order of the data must match the columns.
If overwrite is not None and set to an existing column name, the specified column in the table is searched for the first occurrence of a value matching the value of the column with the same name in the dictionary. If a matching value is found, the row is overwritten with the dictionary. If no matching row is found, a new row is appended to the table.
Parameters: |
|
---|---|
Raises : | ValueError if list-like object is used and number of items does not match number of columns in table. |
Raises : | ValueError if dict is used and multiple rows are added but the number of data items is different for different columns. |
Example: add multiple data rows to a subset of columns using a dictionary
# create table with three float columns
tab = Tab(['x','y','z'], 'fff')
# add rows from dict
data = {'x': [1.2, 1.6], 'z': [1.6, 5.3]}
tab.add_row(data)
print tab
'''
will produce the table
==== ==== ====
x y z
==== ==== ====
1.20 NA 1.60
1.60 NA 5.30
==== ==== ====
'''
# overwrite the row with x=1.2 and add row with x=1.9
data = {'x': [1.2, 1.9], 'z': [7.9, 3.5]}
tab.add_row(data, overwrite='x')
print tab
'''
will produce the table
==== ==== ====
x y z
==== ==== ====
1.20 NA 7.90
1.60 NA 5.30
1.90 NA 3.50
==== ==== ====
'''
Returns the column index for the column with the given name.
Raises : | ValueError if no column with the name is found |
---|
Computes the enrichment of column score_col classified according to class_col.
For this it is necessary, that the datapoints are classified into positive and negative points. This can be done in two ways:
- by using one ‘bool’ type column (class_col) which contains True for positives and False for negatives
- by specifying a classification column (class_col), a cutoff value (class_cutoff) and the classification columns direction (class_dir). This will generate the classification on the fly
- if class_dir=='-': values in the classification column that are less than or equal to class_cutoff will be counted as positives
- if class_dir=='+': values in the classification column that are larger than or equal to class_cutoff will be counted as positives
During the calculation, the table will be sorted according to score_dir, where a ‘-‘ values means smallest values first and therefore, the smaller the value, the better.
Warning : | If either the value of class_col or score_col is None, the data in this row is ignored. |
---|
Computes the area under the curve of the enrichment using the trapezoidal rule.
For more information about parameters of the enrichment, see compute_enrichment().
Warning : | The function depends on numpy |
---|
Compute Matthews correlation coefficient (MCC) for one column (score_col) with the points classified into true positives, false positives, true negatives and false negatives according to a specified classification column (class_col).
The datapoints in score_col and class_col are classified into positive and negative points. This can be done in two ways:
- by using ‘bool’ columns which contains True for positives and False for negatives
- by using ‘float’ or ‘int’ columns and specifying a cutoff value and the columns direction. This will generate the classification on the fly
- if class_dir/score_dir=='-': values in the classification column that are less than or equal to class_cutoff/score_cutoff will be counted as positives
- if class_dir/score_dir=='+': values in the classification column that are larger than or equal to class_cutoff/score_cutoff will be counted as positives
The two possibilities can be used together, i.e. ‘bool’ type for one column and ‘float’/’int’ type and cutoff/direction for the other column.
Computes the receiver operating characteristics (ROC) of column score_col classified according to class_col.
For this it is necessary, that the datapoints are classified into positive and negative points. This can be done in two ways:
- by using one ‘bool’ column (class_col) which contains True for positives and False for negatives
- by using a non-bool column (class_col), a cutoff value (class_cutoff) and the classification columns direction (class_dir). This will generate the classification on the fly
- if class_dir=='-': values in the classification column that are less than or equal to class_cutoff will be counted as positives
- if class_dir=='+': values in the classification column that are larger than or equal to class_cutoff will be counted as positives
During the calculation, the table will be sorted according to score_dir, where a ‘-‘ values means smallest values first and therefore, the smaller the value, the better.
If class_col does not contain any positives (i.e. value is True (if column is of type bool) or evaluated to True (if column is of type int or float (depending on class_dir and class_cutoff))) the ROC is not defined and the function will return None.
Warning : | If either the value of class_col or score_col is None, the data in this row is ignored. |
---|
Computes the area under the curve of the receiver operating characteristics using the trapezoidal rule.
For more information about parameters of the ROC, see compute_roc().
Warning : | The function depends on numpy |
---|
Calculate the Pearson correlation coefficient between col1 and col2, only taking rows into account where both of the values are not equal to None. If there are not enough data points to calculate a correlation coefficient, None is returned.
Parameters: |
|
---|
count the number of cells in column that are not equal to None.
Parameters: |
|
---|
Checks if a table is empty.
If no column name is specified, the whole table is checked for being empty, whereas if a column name is specified, only this column is checked.
By default, all NAN (or None) values are ignored, and thus, a table containing only NAN values is considered as empty. By specifying the option ignore_nan=False, NAN values are counted as ‘normal’ values.
Append each row of tab to the current table. The data is appended based on the column names, thus the order of the table columns is not relevant, only the header names.
If there is a column in tab that is not present in the current table, it is added to the current table and filled with None for all the rows present in the current table.
If the type of any column in tab is not the same as in the current table a TypeError is raised.
If overwrite is not None and set to an existing column name, the specified column in the table is searched for the first occurrence of a value matching the value of the column with the same name in the dictionary. If a matching value is found, the row is overwritten with the dictionary. If no matching row is found, a new row is appended to the table.
Returns a filtered table only containing rows matching all the predicates in kwargs and args For example,
tab.filter(town='Basel')
will return all the rows where the value of the column “town” is equal to “Basel”. Several predicates may be combined, i.e.
tab.filter(town='Basel', male=True)
will return the rows with “town” equal to “Basel” and “male” equal to true. args are unary callables returning true if the row should be included in the result and false if not.
In place gaussian smooth of a column in the table with a given standard deviation. All nan are set to nan_value before smoothing.
Parameters: |
|
---|---|
Warning : | The function depends on scipy |
Returns a list containing all column names.
Get name of table
Returns a numpy matrix containing the selected columns from the table as columns in the matrix. Only columns of type int or float are supported. NA values in the table will be converted to None values.
Parameters: | *args – column names to include in numpy matrix |
---|---|
Warning : | The function depends on numpy |
Extract a list of all unique values from one column
Parameters: |
|
---|
Checks if the column with a given name is present in the table.
Returns the maximum value in col. None values are ignored.
Parameters: | col (str) – column name |
---|
Returns the row index of the cell with the maximal value in col. If several rows have the highest value, only the first one is returned. None values are ignored.
Parameters: | col (str) – column name |
---|
Returns the row containing the cell with the maximal value in col. If several rows have the highest value, only the first one is returned. None values are ignored.
Parameters: | col (str) – column name |
---|---|
Returns: | row with maximal col value or None if the table is empty |
Returns the mean of the given column. Cells with None are ignored. Returns None, if the column doesn’t contain any elements. Col must be of numeric (‘float’, ‘int’) or boolean column type.
If column type is bool, the function returns the ratio of number of ‘Trues’ by total number of elements.
Parameters: | col (str) – column name |
---|---|
Raises : | TypeError if column type is string |
Returns the median of the given column. Cells with None are ignored. Returns None, if the column doesn’t contain any elements. Col must be of numeric column type (‘float’, ‘int’) or boolean column type.
Parameters: | col (str) – column name |
---|---|
Raises : | TypeError if column type is string |
Returns the minimal value in col. None values are ignored.
Parameters: | col (str) – column name |
---|
Returns the row index of the cell with the minimal value in col. If several rows have the lowest value, only the first one is returned. None values are ignored.
Parameters: | col (str) – column name |
---|
Returns the row containing the cell with the minimal value in col. If several rows have the lowest value, only the first one is returned. None values are ignored.
Parameters: | col (str) – column name |
---|---|
Returns: | row with minimal col value or None if the table is empty |
This returns the optimal prefactor values (i.e. a, b, c, ...) for the following equation
where u, v, w and z are vectors. In matrix notation
where A contains the data from the table (u,v,w,...), p are the prefactors to optimize (a,b,c,...) and z is the vector containing the result of equation (1).
The parameter ref_col equals to z in both equations, and *args are columns u, v and w (or A in (2)). All columns must be specified by their names.
Example:
tab.optimal_prefactors('colC', 'colA', 'colB')
The function returns a list of containing the prefactors a, b, c, ... in the correct order (i.e. same as columns were specified in *args).
Weighting: If the kwarg weights=”columX” is specified, the equations are weighted by the values in that column. Each row is multiplied by the weight in that row, which leads to (3):
Weights must be float or int and can have any value. A value of 0 ignores this equation, a value of 1 means the same as no weight. If all weights are the same for each row, the same result will be obtained as with no weights.
Example:
tab.optimal_prefactors('colC', 'colA', 'colB', weights='colD')
Two-sided test for the null-hypothesis that two related samples have the same average (expected values)
Parameters: |
|
---|---|
Returns: | P-value between 0 and 1 that the two columns have the same average. The smaller the value, the less related the two columns are. |
returns the percentiles of column col given in nths.
The percentils are calculated as
values[min(len(values), int(round(len(values)*p/100+0.5)-1))]
where values are the sorted values of col not equal to none :param: nths: list of percentiles to be calculated. Each percentil is a number between 0 and 100.
Raises : | TypeError if column type is string |
---|---|
Returns: | List of percentils in the same order as given in nths |
Function to plot values from your table in 1, 2 or 3 dimensions using Matplotlib
Parameters: |
|
---|---|
Returns: | the matplotlib.pyplot module |
Examples: simple plotting functions
tab=Table(['a','b','c','d'],'iffi', a=range(5,0,-1),
b=[x/2.0 for x in range(1,6)],
c=[math.cos(x) for x in range(0,5)],
d=range(3,8))
# one dimensional plot of column 'd' vs. index
plt=tab.Plot('d')
plt.show()
# two dimensional plot of 'a' vs. 'c'
plt=tab.Plot('a', y='c', style='o-')
plt.show()
# three dimensional plot of 'a' vs. 'c' with values 'b'
plt=tab.Plot('a', y='c', z='b')
# manually save plot to file
plt.savefig("plot.png")
Create a barplot of the data in cols. Every element of a column will be represented as a single bar. If there are several columns, each row will be grouped together.
Parameters: |
|
---|---|
Title : | Title |
Plot an enrichment curve using matplotlib of column score_col classified according to class_col.
For more information about parameters of the enrichment, see compute_enrichment(), and for plotting see Plot().
Warning : | The function depends on matplotlib |
---|
Create a heatplot of the data in col x vs the data in col y using matplotlib
Parameters: |
|
---|
Create a histogram of the data in col for the range x_range, split into num_bins bins and plot it using Matplotlib.
Parameters: |
|
---|
Examples: simple plotting functions
tab=Table(['a'],'f', a=[math.cos(x*0.01) for x in range(100)])
# one dimensional plot of column 'd' vs. index
plt=tab.plot_histogram('a')
plt.show()
Plot an ROC curve using matplotlib.
For more information about parameters of the ROC, see compute_roc(), and for plotting see Plot().
Warning : | The function depends on matplotlib |
---|
Remove column with the given name from the table
Parameters: | col (str) – name of column to remove |
---|
Rename column old_name to new_name.
Parameters: |
|
---|---|
Raises : | ValueError when old_name is not a valid column |
Adds a new column of type ‘float’ with a specified name (mean_col_name), containing the mean of all specified columns for each row.
Cols are specified by their names and must be of numeric column type (‘float’, ‘int’) or boolean column type. Cells with None are ignored. Adds None if the row doesn’t contain any values.
Parameters: |
|
---|---|
Raises : | TypeError if column type of columns in col is string |
== Example ==
Staring with the following table:
x | y | u |
---|---|---|
1 | 10 | 100 |
2 | 15 | None |
3 | 20 | 400 |
the code here adds a column with the name ‘mean’ to yield the table below:
x | y | u | mean |
---|---|---|---|
1 | 10 | 100 | 50.5 |
2 | 15 | None | 2 |
3 | 20 | 400 | 201.5 |
save the table to stream or filename. The following three file formats are supported (for more information on file formats, see load()):
ost | ost-specific format (human readable) |
csv | comma separated values (human readable) |
pickle | pickled byte stream (binary) |
html | HTML table |
context | ConTeXt table |
Parameters: |
|
---|---|
Raises : | ValueError if format is unknown |
Returns a list of column names matching the regex
Parameters: | regex (str) – regex pattern |
---|---|
Returns: | list of column names (str) |
Set name of the table :param name: name :type name: str
Performs an in-place sort of the table, based on column by.
Parameters: |
|
---|
Calculate the Spearman correlation coefficient between col1 and col2, only taking rows into account where both of the values are not equal to None. If there are not enough data points to calculate a correlation coefficient, None is returned.
Warning : | The function depends on the following module: scipy.stats.mstats |
---|---|
Parameters: |
|
Returns the standard deviation of the given column. Cells with None are ignored. Returns None, if the column doesn’t contain any elements. Col must be of numeric column type (‘float’, ‘int’) or boolean column type.
Parameters: | col (str) – column name |
---|---|
Raises : | TypeError if column type is string |
Returns the sum of the given column. Cells with None are ignored. Returns 0.0, if the column doesn’t contain any elements. Col must be of numeric column type (‘float’, ‘int’) or boolean column type.
Parameters: | col (str) – column name |
---|---|
Raises : | TypeError if column type is string |
Convert the table into a string representation.
The output format can be modified for int and float type columns by specifying a formatting string for the parameters ‘float_format’ and ‘int_format’.
The option ‘rows’ specify the range of rows to be printed. The parameter must be a type that supports indexing (e.g. a list) containing the start and end row index, e.g. [start_row_idx, end_row_idx].
Parameters: |
|
---|
Allows to conveniently iterate over a selection of columns, e.g.
tab=Tab.load('...')
for col1, col2 in tab.zip('col1', 'col2'):
print col1, col2
is a shortcut for
tab=Tab.load('...')
for col1, col2 in zip(tab['col1'], tab['col2']):
print col1, col2
Returns a new table containing the data from both tables. The rows are combined based on the common values in the column(s) by. The option ‘by’ can be a list of column names. When this is the case, merging is based on multiple columns. For example, the two tables below
x | y |
---|---|
1 | 10 |
2 | 15 |
3 | 20 |
x | u |
---|---|
1 | 100 |
3 | 200 |
4 | 400 |
when merged by column x, it produces the following output:
x | y | u |
---|---|---|
1 | 10 | 100 |
2 | 15 | None |
3 | 20 | 200 |
4 | None | 400 |