Pandas API¶

The purpose of this notebook is to provide a demonstration of the capabilities of the pandas like API for PyKX Table objects.

To follow along please download this notebook using the following link.

This demonstration will outline the following

1. Constructing Tables
2. Metadata
3. Querying and Data Interrogation
4. Data Joins/Merging
5. Analytic Functionality
6. Data Preprocessing

In [2]:

import pykx as kx
import numpy as np
import pandas as pd
kx.q.system.console_size = [10, 80]

import pykx as kx import numpy as np import pandas as pd kx.q.system.console_size = [10, 80]

Constructing Tables¶

Table¶

Create a table from a list of rows or by converting a Python dictionary object

Parameters:

Name	Type	Description	Default
x	Union[list, array]	An array like object containing the contents of each row of the table.	None
data	dict	A dictionary to be converted into a Table object.	None
columns	list[str]	A list of column names to use when constructing from an array of rows.	None

Returns:

Type	Description
Table	The newly constructed table object.

Examples:

Create a table from a dictionary object.

In [3]:

kx.Table(data={'x': list(range(10)), 'y': [10 - x for x in range(10)]})

kx.Table(data={'x': list(range(10)), 'y': [10 - x for x in range(10)]})

Out[3]:

	x	y
0	0	10
1	1	9
2	2	8
3	3	7
4	4	6
5	5	5
6	6	4
7	7	3
8	8	2

Create a Table from an array like object.

In [4]:

kx.Table([[0, 1], [2, 3], [4, 5], [6, 7], [8, 9]])

kx.Table([[0, 1], [2, 3], [4, 5], [6, 7], [8, 9]])

Out[4]:

	x	x1
0	0	1
1	2	3
2	4	5
3	6	7
4	8	9

Create a Table from an array like object and provide names for the columns to use.

In [5]:

kx.Table([[0, 1, 2], [3, 4, 5], [6, 7, 8], [9, 10, 11]], columns=['x', 'y', 'z'])

kx.Table([[0, 1, 2], [3, 4, 5], [6, 7, 8], [9, 10, 11]], columns=['x', 'y', 'z'])

Out[5]:

	x	y	z
0	0	1	2
1	3	4	5
2	6	7	8
3	9	10	11

Keyed Table¶

Create a keyed table from a list of rows or by converting a Python dictionary object

Parameters:

Name	Type	Description	Default
x	Union[list, array]	An array like object containing the contents of each row of the table.	None
data	dict	A dictionary to be converted into a Table object.	None
columns	list[str]	A list of column names to use when constructing from an array of rows.	None
index	list[Any]	An array like object to use as the index column of the table.	None

Returns:

Type	Description
KeyedTable	The newly constructed keyed table object.

Examples:

Create a keyed table from a dictionary object.

In [6]:

kx.KeyedTable(data={'x': list(range(10)), 'y': list(10 - x for x in range(10))})

kx.KeyedTable(data={'x': list(range(10)), 'y': list(10 - x for x in range(10))})

Out[6]:

	x	y
idx
0	0	10
1	1	9
2	2	8
3	3	7
4	4	6
5	5	5
6	6	4
7	7	3
8	8	2

Create a keyed table from a list of rows.

In [7]:

kx.KeyedTable([[0, 1], [2, 3], [4, 5], [6, 7], [8, 9]])

kx.KeyedTable([[0, 1], [2, 3], [4, 5], [6, 7], [8, 9]])

Out[7]:

	x	x1
idx
0	0	1
1	2	3
2	4	5
3	6	7
4	8	9

Create a keyed table from a list of rows and provide names for the resulting columns.

In [8]:

kx.KeyedTable([[0, 1, 2], [3, 4, 5], [6, 7, 8], [9, 10, 11]], columns=['x', 'y', 'z'])

kx.KeyedTable([[0, 1, 2], [3, 4, 5], [6, 7, 8], [9, 10, 11]], columns=['x', 'y', 'z'])

Out[8]:

	x	y	z
idx
0	0	1	2
1	3	4	5
2	6	7	8
3	9	10	11

Create a keyed table with a specified index column.

In [9]:

kx.KeyedTable(data={'x': list(range(10)), 'y': list(10 - x for x in range(10))}, index=[2 * x for x in range(10)])

kx.KeyedTable(data={'x': list(range(10)), 'y': list(10 - x for x in range(10))}, index=[2 * x for x in range(10)])

Out[9]:

	x	y
idx
0	0	10
2	1	9
4	2	8
6	3	7
8	4	6
10	5	5
12	6	4
14	7	3
16	8	2

Metadata¶

In [10]:

N = 1000
tab = kx.Table(data = {
    'x': kx.q.til(N),
    'y': kx.random.random(N, ['AAPL', 'GOOG', 'MSFT']),
    'z': kx.random.random(N, 500.0),
    'w': kx.random.random(N, 1000),
    'v': kx.random.random(N, [kx.LongAtom.null, 0, 50, 100, 200, 250])})
tab

N = 1000 tab = kx.Table(data = { 'x': kx.q.til(N), 'y': kx.random.random(N, ['AAPL', 'GOOG', 'MSFT']), 'z': kx.random.random(N, 500.0), 'w': kx.random.random(N, 1000), 'v': kx.random.random(N, [kx.LongAtom.null, 0, 50, 100, 200, 250])}) tab

Out[10]:

	x	y	z	w	v
0	0	AAPL	454.7063	766	100
1	1	AAPL	149.4238	916	0
2	2	MSFT	227.0315	469	0
3	3	GOOG	78.47098	280	250
4	4	MSFT	23.49633	369	100
5	5	GOOG	479.5628	254	200
6	6	MSFT	398.3865	388	200
7	7	MSFT	132.0956	318	250
...	...	...	...	...	...
999	999	GOOG	3.79704	246	0

1,000 rows × 5 columns

Table.columns¶

Get the name of each column in the table

In [11]:

tab.columns

tab.columns

Out[11]:

pykx.SymbolVector(pykx.q('`x`y`z`w`v'))

Table.dtypes¶

Get the datatypes of the table columns

In [12]:

tab.dtypes

tab.dtypes

Out[12]:

	columns	datatypes	type
0	x	"kx.LongAtom"	"kx.LongAtom"
1	y	"kx.SymbolAtom"	"kx.SymbolAtom"
2	z	"kx.FloatAtom"	"kx.FloatAtom"
3	w	"kx.LongAtom"	"kx.LongAtom"
4	v	"kx.LongAtom"	"kx.LongAtom"

Table.empty¶

Returns True if the table is empty otherwise returns False.

In [13]:

tab.empty

tab.empty

Out[13]:

pykx.BooleanAtom(pykx.q('0b'))

Table.ndim¶

Get the nuber of columns within the table.

In [14]:

tab.ndim

tab.ndim

Out[14]:

pykx.LongAtom(pykx.q('2'))

Table.shape¶

Get the shape of the table as a tuple (number of rows, number of columns).

In [15]:

tab.shape

tab.shape

Out[15]:

(pykx.LongAtom(pykx.q('1000')), pykx.LongAtom(pykx.q('5')))

Table.size¶

Get the number of values in the table (rows * cols).

In [16]:

tab.size

tab.size

Out[16]:

pykx.LongAtom(pykx.q('5000'))

Querying and Data Interrogation¶

In [17]:

# The examples in this section will use this example table filled with random data
N = 1000
tab = kx.Table(data = {
    'x': kx.q.til(N),
    'y': kx.random.random(N, ['AAPL', 'GOOG', 'MSFT']),
    'z': kx.random.random(N, 500.0),
    'w': kx.random.random(N, 1000),
    'v': kx.random.random(N, [kx.LongAtom.null, 0, 50, 100, 200, 250])})
tab

# The examples in this section will use this example table filled with random data N = 1000 tab = kx.Table(data = { 'x': kx.q.til(N), 'y': kx.random.random(N, ['AAPL', 'GOOG', 'MSFT']), 'z': kx.random.random(N, 500.0), 'w': kx.random.random(N, 1000), 'v': kx.random.random(N, [kx.LongAtom.null, 0, 50, 100, 200, 250])}) tab

Out[17]:

	x	y	z	w	v
0	0	GOOG	326.1157	458	0
1	1	MSFT	4.083758	511	250
2	2	GOOG	81.29854	96	0
3	3	AAPL	256.6323	998	50
4	4	MSFT	398.2529	103	0
5	5	GOOG	429.4278	214	100
6	6	AAPL	470.0497	807	100
7	7	GOOG	409.6725	727	200
...	...	...	...	...	...
999	999	AAPL	142.8215	874	0

1,000 rows × 5 columns

Table.all()¶

Table.all(axis=0, bool_only=False, skipna=True)

Returns whether or not all values across the given axis have a truthy value.

Parameters:

Name	Type	Description	Default
axis	int	The axis to calculate all across 0 is columns, 1 is rows.	0
bool_only	bool	Only use columns of the table that are boolean types.	False
skipna	bool	Ignore any null values along the axis.	True

Returns:

Type	Description
Dictionary	A dictionary where the key represents the column name / row number and the values are the result of calling all on that column / row.

In [18]:

tab.all()

tab.all()

Out[18]:

x	0b
y	1b
z	1b
w	1b
v	0b

Table.any()¶

Table.any(axis=0, bool_only=False, skipna=True)

Returns whether or not any values across the given axis have a truthy value.

Parameters:

Name	Type	Description	Default
axis	int	The axis to calculate any across 0 is columns, 1 is rows.	0
bool_only	bool	Only use columns of the table that are boolean types.	False
skipna	bool	Ignore any null values along the axis.	True

Returns:

Type	Description
Dictionary	A dictionary where the key represents the column name / row number and the values are the result of calling any on that column / row.

In [19]:

tab.any()

tab.any()

Out[19]:

x	1b
y	1b
z	1b
w	1b
v	1b

Table.at[]¶

Table.at[row, col]

Access a single value for a row / column pair.

Similar to loc[], in that both provide label-based lookups. Use at if you only need to get or set a single value.

The at property can be used for both assignment and retrieval of values at a given row and column.

Examples:

Get the value of the z column in the 997th row.

In [20]:

tab.at[997, 'z']

tab.at[997, 'z']

Out[20]:

pykx.FloatAtom(pykx.q('20.61333'))

Reassign the value of the z column in the 997th row to 3.14159.

In [21]:

tab.at[997, 'z'] = 3.14159
tab.at[997, 'z']

tab.at[997, 'z'] = 3.14159 tab.at[997, 'z']

Out[21]:

pykx.FloatAtom(pykx.q('3.14159'))

Table.get()¶

Table.get(key, default=None)

Get a column or columns from a table by key, if the key does not exist return the default value.

Parameters:

Name	Type	Description	Default
key	Union[str, list[str]]	The column name or list of names to get from the table.	required
default	int	The default value if the key is not found.	None

Returns:

Type	Description
Union[Table, Any]	A table containing only the columns requested or the default value.

Examples:

Get the y column from the table.

In [22]:

tab.get('y')

tab.get('y')

/usr/local/lib/python3.10/site-packages/pykx/pandas_api/pandas_indexing.py:40: FutureWarning: 
	Single column retrieval using 'get' method will return a vector/list object in release 3.0+
	To access the vector/list directly use table['column_name']
  warnings.warn("\n\tSingle column retrieval using 'get' method will return a vector/list "

Out[22]:

	y
0	GOOG
1	MSFT
2	GOOG
3	AAPL
4	MSFT
5	GOOG
6	AAPL
7	GOOG
...	...
999	AAPL

1,000 rows × 1 columns

Get the y and z columns from the table.

In [23]:

tab.get(['y', 'z'])

tab.get(['y', 'z'])

Out[23]:

	y	z
0	GOOG	326.1157
1	MSFT	4.083758
2	GOOG	81.29854
3	AAPL	256.6323
4	MSFT	398.2529
5	GOOG	429.4278
6	AAPL	470.0497
7	GOOG	409.6725
...	...	...
999	AAPL	142.8215

1,000 rows × 2 columns

Attempt to get the q column from the table and receive none as that column does not exist.

In [24]:

print(tab.get('q'))

print(tab.get('q'))

None

/usr/local/lib/python3.10/site-packages/pykx/pandas_api/pandas_indexing.py:40: FutureWarning: 
	Single column retrieval using 'get' method will return a vector/list object in release 3.0+
	To access the vector/list directly use table['column_name']
  warnings.warn("\n\tSingle column retrieval using 'get' method will return a vector/list "

Attempt to get the q column from the table and receive the default value not found as that column does not exist.

In [25]:

tab.get('q', 'not found')

tab.get('q', 'not found')

Out[25]:

'not found'

Table.head()¶

Table.head(n=5)

Get the first n rows from a table.

Parameters:

Name	Type	Description	Default
n	int	The number of rows to return.	5

Returns:

Type	Description
Table	The first n rows of the table.

Examples:

Return the first 5 rows of the table.

In [26]:

tab.head()

tab.head()

Out[26]:

	x	y	z	w	v
0	0	GOOG	326.1157	458	0
1	1	MSFT	4.083758	511	250
2	2	GOOG	81.29854	96	0
3	3	AAPL	256.6323	998	50
4	4	MSFT	398.2529	103	0

Return the first 10 rows of the table.

In [27]:

tab.head(10)

tab.head(10)

Out[27]:

	x	y	z	w	v
0	0	GOOG	326.1157	458	0
1	1	MSFT	4.083758	511	250
2	2	GOOG	81.29854	96	0
3	3	AAPL	256.6323	998	50
4	4	MSFT	398.2529	103	0
5	5	GOOG	429.4278	214	100
6	6	AAPL	470.0497	807	100
7	7	GOOG	409.6725	727	200
8	8	AAPL	280.2929	230	250

Table.isna()¶

Table.isna()

Detects null values in a Table object.

Parameters:

Returns:

Type	Description
Table	A Table with the same shape as the original but containing boolean values. 1b represents a null value present in a cell, 0b represents the opposite.

In [28]:

tabDemo = kx.Table(data= {
    'a': [1, 0, float('nan')],
    'b': [1, 0, float('nan')],
    'c': [float('nan'), 4, 0]
    })

tabDemo = kx.Table(data= { 'a': [1, 0, float('nan')], 'b': [1, 0, float('nan')], 'c': [float('nan'), 4, 0] })

In [29]:

tabDemo.isna()

tabDemo.isna()

Out[29]:

	a	b	c
0	0b	0b	1b
1	0b	0b	0b
2	1b	1b	0b

Table.isnull()¶

Table.isnull()

Alias of Table.isna().

Detects null values in a Table object.

Parameters:

Returns:

Type	Description
Table	A Table with the same shape as the original but containing boolean values. 1b represents a null value present in a cell, 0b represents the opposite.

In [30]:

tabDemo.isnull()

tabDemo.isnull()

Out[30]:

	a	b	c
0	0b	0b	1b
1	0b	0b	0b
2	1b	1b	0b

Table.notna()¶

Table.notna()

Boolean inverse of Table.isna().

Detects non-null values on a Table object.

Parameters:

Returns:

Type	Description
Table	A Table with the same shape as the original but containing boolean values. 0b represents a null value present in a cell, 1b represents the opposite.

In [31]:

tabDemo.notna()

tabDemo.notna()

Out[31]:

	a	b	c
0	1b	1b	0b
1	1b	1b	1b
2	0b	0b	1b

Table.notnull()¶

Table.notna()

Boolean inverse of Table.isnull(). Alias of Table.isna()

Detects non-null values on a Table object.

Parameters:

Returns:

Type	Description
Table	A Table with the same shape as the original but containing boolean values. 0b represents a null value present in a cell, 1b represents the opposite.

In [32]:

tabDemo.notnull()

tabDemo.notnull()

Out[32]:

	a	b	c
0	1b	1b	0b
1	1b	1b	1b
2	0b	0b	1b

Table.iloc[]¶

Table.iloc[:, :]

Purely integer-location based indexing for selection by position.

iloc is primarily integer position based (from 0 to length-1 of the axis), but may also be used with a BooleanVector.

Allowed inputs are:

• An integer, e.g. 5.
• A list or array of integers, e.g. [4, 3, 0].
• A slice object with ints, e.g. 1:7.
• A BooleanVector.
• A callable function with one argument (the calling Series or DataFrame) and that returns valid output for indexing (one of the above). This is useful in method chains, when you don’t have a reference to the calling object, but would like to base your selection on some value.
• A tuple of row and column indexes. The tuple elements consist of one of the above inputs, e.g. (0, 1).

Returns:

Type	Description
Table	A table containing only the columns / rows requested.

Examples:

Get the second row from a table.

In [33]:

tab.iloc[1]

tab.iloc[1]

Out[33]:

	x	y	z	w	v
0	1	MSFT	4.083758	511	250

Get the first 5 rows from a table.

In [34]:

tab.iloc[:5]

tab.iloc[:5]

Out[34]:

	x	y	z	w	v
0	0	GOOG	326.1157	458	0
1	1	MSFT	4.083758	511	250
2	2	GOOG	81.29854	96	0
3	3	AAPL	256.6323	998	50
4	4	MSFT	398.2529	103	0

Get all rows of the table where the y column is equal to AAPL.

In [35]:

tab.iloc[tab['y'] == 'AAPL']

tab.iloc[tab['y'] == 'AAPL']

Out[35]:

	x	y	z	w	v
0	3	AAPL	256.6323	998	50
1	6	AAPL	470.0497	807	100
2	8	AAPL	280.2929	230	250
3	9	AAPL	223.9135	153	100
4	10	AAPL	293.3982	354	0
5	15	AAPL	444.315	286	50
6	17	AAPL	147.3942	895	50
7	18	AAPL	460f	471	50
...	...	...	...	...	...
341	999	AAPL	142.8215	874	0

342 rows × 5 columns

Get all rows of the table where the y column is equal to AAPL, and only return the y, z and w columns.

In [36]:

tab.iloc[tab['y'] == 'AAPL', ['y', 'z', 'w']]

tab.iloc[tab['y'] == 'AAPL', ['y', 'z', 'w']]

Out[36]:

	y	z	w
0	AAPL	256.6323	998
1	AAPL	470.0497	807
2	AAPL	280.2929	230
3	AAPL	223.9135	153
4	AAPL	293.3982	354
5	AAPL	444.315	286
6	AAPL	147.3942	895
7	AAPL	460f	471
...	...	...	...
341	AAPL	142.8215	874

342 rows × 3 columns

Replace all null values in the column v with the value -100.

In [37]:

tab.iloc[tab['v'] == kx.q('0N'), 'v'] = -100
tab

tab.iloc[tab['v'] == kx.q('0N'), 'v'] = -100 tab

Out[37]:

	x	y	z	w	v
0	0	GOOG	326.1157	458	0
1	1	MSFT	4.083758	511	250
2	2	GOOG	81.29854	96	0
3	3	AAPL	256.6323	998	50
4	4	MSFT	398.2529	103	0
5	5	GOOG	429.4278	214	100
6	6	AAPL	470.0497	807	100
7	7	GOOG	409.6725	727	200
...	...	...	...	...	...
999	999	AAPL	142.8215	874	0

1,000 rows × 5 columns

Table.loc[]¶

Table.loc[:, :]

Access a group of rows and columns by label or by BooleanVector.

loc is a label based form of indexing, but may also be used with a boolean array.

Allowed inputs are:

• A single label, e.g. 5 or 'a', (note that 5 is interpreted as a label of the index, and never as an integer position along the index)
• A list or array of labels, e.g. ['a', 'b', 'c']
• A slice object with labels, e.g. 'a':'f'
  • Warning contrary to usual python slices, both the start and the stop are included
• A BooleanVector of the same length as the axis being sliced
• An alignable BooleanVector. The index of the key will be aligned before masking
• An alignable Index. The Index of the returned selection will be the input
• A callable function with one argument (the calling Table like object) and that returns valid output for indexing (e.g. one of the above)

Note: When the Pandas API is enabled, using [] to index into a table will use Table.loc[]

Returns:

Type	Description
Table	A table containing only the columns / rows requested.

Examples:

Get every row in the y column.

In [38]:

tab[:, 'y']

tab[:, 'y']

Out[38]:

	y
0	GOOG
1	MSFT
2	GOOG
3	AAPL
4	MSFT
5	GOOG
6	AAPL
7	GOOG
...	...
999	AAPL

1,000 rows × 1 columns

Get all rows of the table where the value in the z column is greater than 250.0

In [39]:

tab[tab['z'] > 250.0]

tab[tab['z'] > 250.0]

Out[39]:

	x	y	z	w	v
0	0	GOOG	326.1157	458	0
1	3	AAPL	256.6323	998	50
2	4	MSFT	398.2529	103	0
3	5	GOOG	429.4278	214	100
4	6	AAPL	470.0497	807	100
5	7	GOOG	409.6725	727	200
6	8	AAPL	280.2929	230	250
7	10	AAPL	293.3982	354	0
...	...	...	...	...	...
517	998	AAPL	383.3942	13	0

518 rows × 5 columns

Replace all null values in the column v with the value -100.

In [40]:

tab.loc[tab['v'] == kx.LongAtom.null, 'v'] = -100
tab

tab.loc[tab['v'] == kx.LongAtom.null, 'v'] = -100 tab

Out[40]:

	x	y	z	w	v
0	0	GOOG	326.1157	458	0
1	1	MSFT	4.083758	511	250
2	2	GOOG	81.29854	96	0
3	3	AAPL	256.6323	998	50
4	4	MSFT	398.2529	103	0
5	5	GOOG	429.4278	214	100
6	6	AAPL	470.0497	807	100
7	7	GOOG	409.6725	727	200
...	...	...	...	...	...
999	999	AAPL	142.8215	874	0

1,000 rows × 5 columns

Replace all locations in column v where the value is -100 with a null.

In [41]:

tab[tab['v'] == -100, 'v'] = kx.LongAtom.null
tab

tab[tab['v'] == -100, 'v'] = kx.LongAtom.null tab

Out[41]:

	x	y	z	w	v
0	0	GOOG	326.1157	458	0
1	1	MSFT	4.083758	511	250
2	2	GOOG	81.29854	96	0
3	3	AAPL	256.6323	998	50
4	4	MSFT	398.2529	103	0
5	5	GOOG	429.4278	214	100
6	6	AAPL	470.0497	807	100
7	7	GOOG	409.6725	727	200
...	...	...	...	...	...
999	999	AAPL	142.8215	874	0

1,000 rows × 5 columns

Usage of the loc functionality under the hood additionally allows users to set columns within a table for single or multiple columns. Data passed for this can be q/Python.

In [42]:

tab['new_col'] = kx.random.random(1000, 1.0)

tab['new_col'] = kx.random.random(1000, 1.0)

In [43]:

tab[['new_col1', 'new_col2']] = [20, kx.random.random(1000, kx.GUIDAtom.null)]

tab[['new_col1', 'new_col2']] = [20, kx.random.random(1000, kx.GUIDAtom.null)]

Table.sample()¶

Table.sample(n, frac, replace, weights, random_state, axis, ignore_index)

Sample random data from the table.

Parameters:

Name	Type	Description	Default
n	int	Number of rows to return. Cannot be used with frac. Default is 1 if frac is None.	None
frac	float	Fraction of the rows to return. Cannot be used with n.	None
replace	bool	Whether or not it should be possible to sample the same row twice.	False
weights	None	Not yet implemented.	None
random_state	None	Not yet implemented.	None
axis	None	Not yet implemented.	None
ignore_index	bool	Not yet implemented.	False

Returns:

Type	Description
Table	A table with the given column(s) renamed.

In [44]:

# The examples in this section will use this example table filled with random data
N = 1000
tab = kx.Table(data = {
    'x': kx.q.til(N),
    'y': kx.random.random(N, ['AAPL', 'GOOG', 'MSFT']),
    'z': kx.random.random(N, 500.0),
    'w': kx.random.random(N, 1000),
    'v': kx.random.random(N, [kx.LongAtom.null, 0, 50, 100, 200, 250])})
tab.head()

# The examples in this section will use this example table filled with random data N = 1000 tab = kx.Table(data = { 'x': kx.q.til(N), 'y': kx.random.random(N, ['AAPL', 'GOOG', 'MSFT']), 'z': kx.random.random(N, 500.0), 'w': kx.random.random(N, 1000), 'v': kx.random.random(N, [kx.LongAtom.null, 0, 50, 100, 200, 250])}) tab.head()

Out[44]:

	x	y	z	w	v
0	0	AAPL	51.56208	1	200
1	1	GOOG	192.8475	238	200
2	2	GOOG	152.3489	774	0N
3	3	GOOG	104.8755	913	0
4	4	GOOG	160.1738	506	100

Examples:

Sample 10 Rows.

In [45]:

tab.sample(n=10)

tab.sample(n=10)

Out[45]:

	x	y	z	w	v
0	604	AAPL	276.1397	785	0N
1	893	MSFT	353.1517	659	200
2	590	AAPL	30.3018	634	200
3	881	MSFT	384.4023	461	0N
4	224	MSFT	64.81406	455	250
5	740	GOOG	184.8131	730	100
6	172	MSFT	460.7157	329	0
7	738	MSFT	425.1625	556	50
8	943	AAPL	254.8109	792	100

Sample 10% of the rows.

In [46]:

tab.sample(frac=0.1)

tab.sample(frac=0.1)

Out[46]:

	x	y	z	w	v
0	580	AAPL	218.6735	743	0N
1	653	MSFT	458.9859	2	250
2	875	MSFT	30.793	273	0N
3	641	GOOG	41.78246	973	50
4	436	AAPL	345.3793	103	250
5	261	MSFT	2.576664	990	100
6	780	GOOG	25.01553	7	0N
7	851	GOOG	492.5621	688	0N
...	...	...	...	...	...
99	107	AAPL	189.0718	667	0N

100 rows × 5 columns

Sample 10% of the rows and allow the same row to be sampled twice.

In [47]:

tab.sample(frac=0.1, replace=True)

tab.sample(frac=0.1, replace=True)

Out[47]:

	x	y	z	w	v
0	576	MSFT	32.12644	902	0
1	298	MSFT	203.5688	36	50
2	48	MSFT	477.9462	345	0N
3	172	MSFT	460.7157	329	0
4	144	MSFT	149.4715	441	250
5	65	AAPL	347.9628	848	50
6	336	GOOG	31.34298	618	0
7	137	GOOG	476.6447	676	0N
...	...	...	...	...	...
99	217	GOOG	358.6286	781	250

100 rows × 5 columns

Table.select_dtypes()¶

Table.select_dtypes(include=None, exclude=None)

Return a subset of the DataFrame’s columns based on the column dtypes.

Allowed inputs for include/exclude are:

• A single dtype or string.
• A list of dtypes or strings.
• Inputs given for include and exclude cannot overlap.

The dtype kx.CharVector will return an error. Use kx.CharAtom for a column of single chars. Both kx.*Atom and kx.*Vector will be taken to mean a column containing a single item per row of type *. kx.List will include/exclude any columns containing mixed list data (including string columns).

Parameters:

Name	Type	Description	Default
include	Union[List, str]	A selection of dtypes or strings to be included.	None
exclude	Union[List, str]	A selection of dtypes or strings to be excluded.	None

At least one of these parameters must be supplied.

Returns:

Type	Description
Dataframe	The subset of the frame including the dtypes in include and excluding the dtypes in exclude.

Examples:

The examples in the section will use the example table.

In [48]:

df = kx.Table(data = {
  'c1': kx.SymbolVector(['a', 'b', 'c']),
  'c2': kx.ShortVector([1, 2, 3]),
  'c3': kx.LongVector([1, 2, 3]),
  'c4': kx.IntVector([1, 2, 3])
  })

df = kx.Table(data = { 'c1': kx.SymbolVector(['a', 'b', 'c']), 'c2': kx.ShortVector([1, 2, 3]), 'c3': kx.LongVector([1, 2, 3]), 'c4': kx.IntVector([1, 2, 3]) })

Exclude columns containing symbols

In [49]:

df.select_dtypes(exclude = kx.SymbolVector)

df.select_dtypes(exclude = kx.SymbolVector)

Out[49]:

	c2	c3	c4
0	1h	1	1i
1	2h	2	2i
2	3h	3	3i

Include a list of column types

In [50]:

df.select_dtypes(include = [kx.ShortVector, kx.LongVector])

df.select_dtypes(include = [kx.ShortVector, kx.LongVector])

Out[50]:

	c2	c3
0	1h	1
1	2h	2
2	3h	3

Table.tail()¶

Table.tail(n=5)

Get the last n rows from a table.

Parameters:

Name	Type	Description	Default
n	int	The number of rows to return.	5

Returns:

Type	Description
Table	The last n rows of the table.

Examples:

Return the last 5 rows of the table.

In [51]:

tab.tail()

tab.tail()

Out[51]:

	x	y	z	w	v
0	995	MSFT	30.33	974	100
1	996	GOOG	417.5746	591	0
2	997	GOOG	237.1697	119	250
3	998	MSFT	75.62617	784	50
4	999	AAPL	324.1628	231	200

Return the last 10 rows of the table.

In [52]:

tab.tail(10)

tab.tail(10)

Out[52]:

	x	y	z	w	v
0	990	MSFT	251.8079	223	0N
1	991	MSFT	316.3818	523	0N
2	992	GOOG	236.4901	855	0N
3	993	MSFT	415.9962	542	100
4	994	GOOG	468.8945	175	100
5	995	MSFT	30.33	974	100
6	996	GOOG	417.5746	591	0
7	997	GOOG	237.1697	119	250
8	998	MSFT	75.62617	784	50

Sorting¶

Table.sort_values()¶

Table.sort_values(by, ascending=True)

Sort Table objects based on the value of a selected column.

Parameters:

Name	Type	Description	Default
by	str or list of str	The name of the column to sort by.	required
ascending	bool	The order in which to sort the values, ascending is True and descending is False.	True

Returns:

Type	Description
Table	The resulting table after the sort has been perfomed

Examples:

In [53]:

tab = kx.Table(data={
    'column_a': [20, 3, 100],
    'column_b': [56, 15, 42],
    'column_c': [45, 80, 8]})
tab

tab = kx.Table(data={ 'column_a': [20, 3, 100], 'column_b': [56, 15, 42], 'column_c': [45, 80, 8]}) tab

Out[53]:

	column_a	column_b	column_c
0	20	56	45
1	3	15	80
2	100	42	8

Sort a Table by the second column

In [54]:

tab.sort_values(by='column_b')

tab.sort_values(by='column_b')

Out[54]:

	column_a	column_b	column_c
0	3	15	80
1	100	42	8
2	20	56	45

Sort a Table by the third column in descending order

In [55]:

tab.sort_values(by='column_c', ascending=False)

tab.sort_values(by='column_c', ascending=False)

Out[55]:

	column_a	column_b	column_c
0	3	15	80
1	20	56	45
2	100	42	8

Table.nsmallest()¶

Table.nsmallest(
    n,
    columns,
    keep='first'
)

Return the first n rows of a Table ordered by columns in ascending order

Parameters:

Name	Type	Description	Default
n	int	The number of rows to return	required
columns	str or list of str	Column labels to order by	required
keep	str	Can be 'first', 'last' or 'all'. Used in case of duplicate values	'first'

Returns

Type	Description
Table	The first n rows ordered by the given columns in ascending order

Examples:

Sample table

In [56]:

tab = kx.Table(data={
    'column_a': [2, 3, 2, 2, 1],
    'column_b': [56, 15, 42, 102, 32],
    'column_c': [45, 80, 8, 61, 87]})
tab

tab = kx.Table(data={ 'column_a': [2, 3, 2, 2, 1], 'column_b': [56, 15, 42, 102, 32], 'column_c': [45, 80, 8, 61, 87]}) tab

Out[56]:

	column_a	column_b	column_c
0	2	56	45
1	3	15	80
2	2	42	8
3	2	102	61
4	1	32	87

Get the row where the first column is the smallest

In [57]:

tab.nsmallest(n=1, columns='column_a')

tab.nsmallest(n=1, columns='column_a')

Out[57]:

	column_a	column_b	column_c
0	1	32	87

Get the 4 rows where the first column is the smallest, then any equal values are sorted based on the second column

In [58]:

tab.nsmallest(n=4,columns=['column_a', 'column_b'])

tab.nsmallest(n=4,columns=['column_a', 'column_b'])

Out[58]:

	column_a	column_b	column_c
0	1	32	87
1	2	42	8
2	2	56	45
3	2	102	61

Get the 2 rows with the smallest values for the first column and in case of duplicates, take the last entry in the table

In [59]:

tab.nsmallest(n=2, columns=['column_a'], keep='last')

tab.nsmallest(n=2, columns=['column_a'], keep='last')

Out[59]:

	column_a	column_b	column_c
0	1	32	87
1	2	102	61

Table.nlargest()¶

Table.nlargest(
    n,
    columns,
    keep='first'
)

Return the first n rows of a Table ordered by columns in descending order

Parameters:

Name	Type	Description	Default
n	int	The number of rows to return	required
columns	str or list of str	Column labels to order by	required
keep	str	Can be 'first', 'last' or 'all'. Used in case of duplicate values	'first'

Returns

Type	Description
Table	The first n rows ordered by the given columns in descending order

Examples:

Sample table

In [60]:

tab = kx.Table(data={
    'column_a': [2, 3, 2, 2, 1],
    'column_b': [102, 15, 42, 56, 32],
    'column_c': [45, 80, 8, 61, 87]})
tab

tab = kx.Table(data={ 'column_a': [2, 3, 2, 2, 1], 'column_b': [102, 15, 42, 56, 32], 'column_c': [45, 80, 8, 61, 87]}) tab

Out[60]:

	column_a	column_b	column_c
0	2	102	45
1	3	15	80
2	2	42	8
3	2	56	61
4	1	32	87

Get the row with the largest value for the first column

In [61]:

tab.nlargest(n=1, columns='column_a')

tab.nlargest(n=1, columns='column_a')

Out[61]:

	column_a	column_b	column_c
0	3	15	80

Get the 4 rows where the first column is the largest, then any equal values are sorted based on the third column

In [62]:

tab.nlargest(n=4,columns=['column_a', 'column_c'])

tab.nlargest(n=4,columns=['column_a', 'column_c'])

Out[62]:

	column_a	column_b	column_c
0	3	15	80
1	2	56	61
2	2	102	45
3	2	42	8

Get the 2 rows with the smallest values for the first column and in case of duplicates, take all rows of the same value for that column

In [63]:

tab.nsmallest(n=2, columns=['column_a'], keep='all')

tab.nsmallest(n=2, columns=['column_a'], keep='all')

Out[63]:

	column_a	column_b	column_c
0	1	32	87
1	2	102	45
2	2	42	8
3	2	56	61

Data Joins/Merging¶

Table.merge()¶

Table.merge(
    right,
    how='inner',
    on=None,
    left_on=None,
    right_on=None,
    left_index=False,
    right_index=False,
    sort=False,
    suffixes=('_x', '_y'),
    copy=True,
    validate=None,
    q_join=False
)

Merge Table or KeyedTable objects with a database-style join.

The join is done on columns or keys. If joining columns on columns, the Table key will be ignored. Otherwise if joining keys on keys or keys on a column or columns, the index will be passed on. When performing a cross merge, no column specifications to merge on are allowed.

Parameters:

Name	Type	Description	Default
right	Union[Table/KeyedTable]	The object to merge with.	required
how	str	The type of join to be used. One of {‘left’, ‘right’, ‘outer’, ‘inner’, ‘cross’}.	‘inner’
on	str	The column name to join on.	None
left_on	str	The column name in the left table to join on.	None
right_on	str	The column name in the right table to join on.	None
left_index	bool	Use the index of the left Table.	False
right_index	bool	Use the index of the right Table.	False
sort	bool	Sort the join keys of the resulting table.	False
suffixes	Tuple(str, str)	The number of rows to return.	('_x', '_y')
copy	bool	If False avoid copies and modify the input table.	None
validate	str	If specified checks if merge matches specified type. - “one_to_one” or “1:1”: check if merge keys are unique in both left and right datasets. - “one_to_many” or “1:m”: check if merge keys are unique in left dataset. - “many_to_one” or “m:1”: check if merge keys are unique in right dataset. - “many_to_many” or “m:m”: allowed, but does not result in checks.	None
q_join	bool	If True perform native q joins instead of the pandas SQL like joins. More documentation around these joins can be found here.	False

Returns:

Type	Description
Table / KeyedTable	The resulting table-like object after the join has been preformed.

Examples:

Merge tab1 and tab2 on the lkey and rkey columns. The value columns have the default suffixes, _x and _y, appended.

In [64]:

tab1 = kx.Table(data={'lkey': ['foo', 'bar', 'baz', 'foo'], 'value': [1, 2, 3, 5]})
tab2 = kx.Table(data={'rkey': ['foo', 'bar', 'baz', 'foo'], 'value': [5, 6, 7, 8]})
tab1.merge(tab2, left_on='lkey', right_on='rkey')

tab1 = kx.Table(data={'lkey': ['foo', 'bar', 'baz', 'foo'], 'value': [1, 2, 3, 5]}) tab2 = kx.Table(data={'rkey': ['foo', 'bar', 'baz', 'foo'], 'value': [5, 6, 7, 8]}) tab1.merge(tab2, left_on='lkey', right_on='rkey')

Out[64]:

	lkey	value_x	rkey	value_y
0	foo	1	foo	5
1	foo	1	foo	8
2	foo	5	foo	5
3	foo	5	foo	8
4	bar	2	bar	6
5	baz	3	baz	7

Merge tab1 and tab2 with specified left and right suffixes appended to any overlapping columns.

In [65]:

tab1.merge(tab2, left_on='lkey', right_on='rkey', suffixes=('_left', '_right'))

tab1.merge(tab2, left_on='lkey', right_on='rkey', suffixes=('_left', '_right'))

Out[65]:

	lkey	value_left	rkey	value_right
0	foo	1	foo	5
1	foo	1	foo	8
2	foo	5	foo	5
3	foo	5	foo	8
4	bar	2	bar	6
5	baz	3	baz	7

Merge tab1 and tab2 but raise an exception if the Tables have any overlapping columns.

In [66]:

try:
    tab1.merge(tab2, left_on='lkey', right_on='rkey', suffixes=(False, False))
except BaseException as e:
    print(f'Caught Error: {e}')

try: tab1.merge(tab2, left_on='lkey', right_on='rkey', suffixes=(False, False)) except BaseException as e: print(f'Caught Error: {e}')

Caught Error: Columns overlap but no suffix specified: ['value']

In [67]:

tab1 = kx.Table(data={'a': ['foo', 'bar'], 'b': [1, 2]})
tab2 = kx.Table(data={'a': ['foo', 'baz'], 'c': [3, 4]})

tab1 = kx.Table(data={'a': ['foo', 'bar'], 'b': [1, 2]}) tab2 = kx.Table(data={'a': ['foo', 'baz'], 'c': [3, 4]})

Merge tab1 and tab2 on the a column using an inner join.

In [68]:

tab1.merge(tab2, how='inner', on='a')

tab1.merge(tab2, how='inner', on='a')

Out[68]:

	a	b	c
0	foo	1	3

Merge tab1 and tab2 on the a column using a left join.

In [69]:

tab1.merge(tab2, how='left', on='a')

tab1.merge(tab2, how='left', on='a')

Out[69]:

	a	b	c
0	foo	1	3
1	bar	2	0N

Merge tab1 and tab2 using a cross join.

In [70]:

tab1 = kx.Table(data={'left': ['foo', 'bar']})
tab2 = kx.Table(data={'right': [7, 8]})
tab1.merge(tab2, how='cross')

tab1 = kx.Table(data={'left': ['foo', 'bar']}) tab2 = kx.Table(data={'right': [7, 8]}) tab1.merge(tab2, how='cross')

Out[70]:

	left	right
0	foo	7
1	foo	8
2	bar	7
3	bar	8

Merge tab1 and tab2_keyed using a left join with q_join set to True. Inputs/Outputs will match q lj behaviour.

In [71]:

tab1 = kx.Table(data={'a': ['foo', 'bar', 'baz'], 'b': [1, 2, 3]})
tab2 = kx.Table(data={'a': ['foo', 'baz', 'baz'], 'c': [3, 4, 5]})
tab2_keyed = tab2.set_index(1)
tab1.merge(tab2_keyed, how='left', q_join=True)

tab1 = kx.Table(data={'a': ['foo', 'bar', 'baz'], 'b': [1, 2, 3]}) tab2 = kx.Table(data={'a': ['foo', 'baz', 'baz'], 'c': [3, 4, 5]}) tab2_keyed = tab2.set_index(1) tab1.merge(tab2_keyed, how='left', q_join=True)

Out[71]:

	a	b	c
0	foo	1	3
1	baz	3	4

Inputs/Outputs will match q ij behaviour.

In [72]:

tab3 = kx.Table(data={'a': ['foo', 'bar'], 'd': [6, 7]})
tab3_keyed = tab3.set_index(1)
tab1.merge(tab3_keyed, how='inner', q_join=True)

tab3 = kx.Table(data={'a': ['foo', 'bar'], 'd': [6, 7]}) tab3_keyed = tab3.set_index(1) tab1.merge(tab3_keyed, how='inner', q_join=True)

Out[72]:

	a	b	d
0	foo	1	6
1	bar	2	7

Merge using q_join set to True, and how set to left, will fail when tab2 is not a keyed table.

In [73]:

#Will error as Left Join requires a keyed column for the right dataset.
try:
    tab1.merge(tab2, how='left', q_join=True)
except ValueError as e:
    print(f'Caught Error: {e}')

#Will error as Left Join requires a keyed column for the right dataset. try: tab1.merge(tab2, how='left', q_join=True) except ValueError as e: print(f'Caught Error: {e}')

Caught Error: Left Join requires a keyed table for the right dataset.

Table.merge_asof()¶

Table.merge_asof(
    right,
    on=None,
    left_on=None,
    right_on=None,
    left_index=False,
    right_index=False,
    by=None,
    left_by=None,
    right_by=None,
    suffixes=('_x', '_y'),
    tolerance=None,
    allow_exact_matches=True,
    direction='backward'

)

Merge Table or KeyedTable objects with a database-style join.

The join is done on columns or keys. If joining columns on columns, the Table key will be ignored. Otherwise if joining keys on keys or keys on a column or columns, the index will be passed on. When performing a cross merge, no column specifications to merge on are allowed.

Parameters:

Name	Type	Description	Default
right	Union[Table/KeyedTable]	The object to merge with.	required
how	str	The type of join to be used. One of {‘left’, ‘right’, ‘outer’, ‘inner’, ‘cross’}.	‘inner’
on	str	The column name to join on.	None
left_on	str	The column name in the left table to join on.	None
right_on	str	The column name in the right table to join on.	None
left_index	bool	Use the index of the left Table.	False
right_index	bool	Use the index of the right Table.	False
by	str	Not yet implemented.	None
left_by	str	Field names to match on in the left table.	None
right_by	str	Field names to match on in the right table.	None
suffixes	Tuple(str, str)	The number of rows to return.	('_x', '_y')
tolerance	Any	Not yet implemented.	None
allow_exact_matches	bool	Not yet implemented.	True
direction	str	Not yet implemented.	'backward'

Returns:

Type	Description
Table / KeyedTable	The resulting table like object after the join has been preformed.

Examples:

Perform a simple asof join on two tables.

In [74]:

left  = kx.Table(data={"a": [1, 5, 10], "left_val": ["a", "b", "c"]})
right = kx.Table(data={"a": [1, 2, 3, 6, 7], "right_val": [1, 2, 3, 6, 7]})
left

left = kx.Table(data={"a": [1, 5, 10], "left_val": ["a", "b", "c"]}) right = kx.Table(data={"a": [1, 2, 3, 6, 7], "right_val": [1, 2, 3, 6, 7]}) left

Out[74]:

	a	left_val
0	1	a
1	5	b
2	10	c

In [75]:

right

right

Out[75]:

	a	right_val
0	1	1
1	2	2
2	3	3
3	6	6
4	7	7

In [76]:

left.merge_asof(right)

left.merge_asof(right)

Out[76]:

	a	left_val	right_val
0	1	a	1
1	5	b	3
2	10	c	7

Perform a asof join on two tables but first merge them on the by column.

In [77]:

trades = kx.Table(data={
    "time": [
        pd.Timestamp("2016-05-25 13:30:00.023"),
        pd.Timestamp("2016-05-25 13:30:00.023"),
        pd.Timestamp("2016-05-25 13:30:00.030"),
        pd.Timestamp("2016-05-25 13:30:00.041"),
        pd.Timestamp("2016-05-25 13:30:00.048"),
        pd.Timestamp("2016-05-25 13:30:00.049"),
        pd.Timestamp("2016-05-25 13:30:00.072"),
        pd.Timestamp("2016-05-25 13:30:00.075")
    ],
    "ticker": [
       "GOOG",
       "MSFT",
       "MSFT",
       "MSFT",
       "GOOG",
       "AAPL",
       "GOOG",
       "MSFT"
   ],
   "bid": [720.50, 51.95, 51.97, 51.99, 720.50, 97.99, 720.50, 52.01],
   "ask": [720.93, 51.96, 51.98, 52.00, 720.93, 98.01, 720.88, 52.03]
})
quotes = kx.Table(data={
   "time": [
       pd.Timestamp("2016-05-25 13:30:00.023"),
       pd.Timestamp("2016-05-25 13:30:00.038"),
       pd.Timestamp("2016-05-25 13:30:00.048"),
       pd.Timestamp("2016-05-25 13:30:00.048"),
       pd.Timestamp("2016-05-25 13:30:00.048")
   ],
   "ticker": ["MSFT", "MSFT", "GOOG", "GOOG", "AAPL"],
   "price": [51.95, 51.95, 720.77, 720.92, 98.0],
   "quantity": [75, 155, 100, 100, 100]
})
trades

trades = kx.Table(data={ "time": [ pd.Timestamp("2016-05-25 13:30:00.023"), pd.Timestamp("2016-05-25 13:30:00.023"), pd.Timestamp("2016-05-25 13:30:00.030"), pd.Timestamp("2016-05-25 13:30:00.041"), pd.Timestamp("2016-05-25 13:30:00.048"), pd.Timestamp("2016-05-25 13:30:00.049"), pd.Timestamp("2016-05-25 13:30:00.072"), pd.Timestamp("2016-05-25 13:30:00.075") ], "ticker": [ "GOOG", "MSFT", "MSFT", "MSFT", "GOOG", "AAPL", "GOOG", "MSFT" ], "bid": [720.50, 51.95, 51.97, 51.99, 720.50, 97.99, 720.50, 52.01], "ask": [720.93, 51.96, 51.98, 52.00, 720.93, 98.01, 720.88, 52.03] }) quotes = kx.Table(data={ "time": [ pd.Timestamp("2016-05-25 13:30:00.023"), pd.Timestamp("2016-05-25 13:30:00.038"), pd.Timestamp("2016-05-25 13:30:00.048"), pd.Timestamp("2016-05-25 13:30:00.048"), pd.Timestamp("2016-05-25 13:30:00.048") ], "ticker": ["MSFT", "MSFT", "GOOG", "GOOG", "AAPL"], "price": [51.95, 51.95, 720.77, 720.92, 98.0], "quantity": [75, 155, 100, 100, 100] }) trades

Out[77]:

	time	ticker	bid	ask
0	2016.05.25D13:30:00.023000000	GOOG	720.5	720.93
1	2016.05.25D13:30:00.023000000	MSFT	51.95	51.96
2	2016.05.25D13:30:00.030000000	MSFT	51.97	51.98
3	2016.05.25D13:30:00.041000000	MSFT	51.99	52f
4	2016.05.25D13:30:00.048000000	GOOG	720.5	720.93
5	2016.05.25D13:30:00.049000000	AAPL	97.99	98.01
6	2016.05.25D13:30:00.072000000	GOOG	720.5	720.88
7	2016.05.25D13:30:00.075000000	MSFT	52.01	52.03

In [78]:

quotes

quotes

Out[78]:

	time	ticker	price	quantity
0	2016.05.25D13:30:00.023000000	MSFT	51.95	75
1	2016.05.25D13:30:00.038000000	MSFT	51.95	155
2	2016.05.25D13:30:00.048000000	GOOG	720.77	100
3	2016.05.25D13:30:00.048000000	GOOG	720.92	100
4	2016.05.25D13:30:00.048000000	AAPL	98f	100

In [79]:

trades.merge_asof(quotes, on="time")

trades.merge_asof(quotes, on="time")

Out[79]:

	time	ticker_x	bid	ask	ticker_y	price	quantity
0	2016.05.25D13:30:00.023000000	GOOG	720.5	720.93	MSFT	51.95	75
1	2016.05.25D13:30:00.023000000	MSFT	51.95	51.96	MSFT	51.95	75
2	2016.05.25D13:30:00.030000000	MSFT	51.97	51.98	MSFT	51.95	75
3	2016.05.25D13:30:00.041000000	MSFT	51.99	52f	MSFT	51.95	155
4	2016.05.25D13:30:00.048000000	GOOG	720.5	720.93	AAPL	98f	100
5	2016.05.25D13:30:00.049000000	AAPL	97.99	98.01	AAPL	98f	100
6	2016.05.25D13:30:00.072000000	GOOG	720.5	720.88	AAPL	98f	100
7	2016.05.25D13:30:00.075000000	MSFT	52.01	52.03	AAPL	98f	100

Analytic functionality¶

In [80]:

# All the examples in this section will use this example table.
N = 100
kx.Table(data={
    'sym': kx.random.random(N, ['AAPL', 'GOOG', 'MSFT']),
    'price': 250 + kx.random.random(N, 500.0),
    'traded': 100 - kx.random.random(N, 200),
    'hold': kx.random.random(N, False)
    })
tab

# All the examples in this section will use this example table. N = 100 kx.Table(data={ 'sym': kx.random.random(N, ['AAPL', 'GOOG', 'MSFT']), 'price': 250 + kx.random.random(N, 500.0), 'traded': 100 - kx.random.random(N, 200), 'hold': kx.random.random(N, False) }) tab

Out[80]:

	column_a	column_b	column_c
0	2	102	45
1	3	15	80
2	2	42	8
3	2	56	61
4	1	32	87

Table.abs()¶

Table.abs(numeric_only=False)

Take the absolute value of each element in the table. This will raise an error if there are columns that contain data that have no absolute value.

Parameters:

Name	Type	Description	Default
numeric_only	bool	Only use columns of the table that can be converted to an absolute value.	False

Returns:

Type	Description
Table / KeyedTable	The resulting table like object with only positive numerical values.

In [81]:

tab.abs(numeric_only=True)

tab.abs(numeric_only=True)

Out[81]:

	column_a	column_b	column_c
0	2	102	45
1	3	15	80
2	2	42	8
3	2	56	61
4	1	32	87

Table.count()¶

Table.count(axis=0, numeric_only=False)

Returns the count of non null values across the given axis.

Parameters:

Name	Type	Description	Default
axis	int	The axis to count elements across 1 is columns, 0 is rows.	0
numeric_only	bool	Only use columns of the table that are of a numeric data type.	False

Returns:

Type	Description
Dictionary	A dictionary where the key represent the column name / row number and the values are the result of calling count on that column / row.

In [82]:

tab.count()

tab.count()

Out[82]:

column_a	5
column_b	5
column_c	5

Table.max()¶

Table.max(axis=0, skipna=True, numeric_only=False)

Returns the maximum value across the given axis.

Parameters:

Name	Type	Description	Default
axis	int	The axis to calculate the maximum across 0 is columns, 1 is rows.	0
skipna	bool	Ignore any null values along the axis.	True
numeric_only	bool	Only use columns of the table that are of a numeric data type.	False

Returns:

Type	Description
Dictionary	A dictionary where the key represents the column name / row number and the values are the result of calling max on that column / row.

In [83]:

tab.max()

tab.max()

Out[83]:

column_a	3
column_b	102
column_c	87

Table.min()¶

Table.min(axis=0, skipna=True, numeric_only=False)

Returns the minimum value across the given axis.

Parameters:

Name	Type	Description	Default
axis	int	The axis to calculate the minimum across 0 is columns, 1 is rows.	0
skipna	bool	Ignore any null values along the axis.	True
numeric_only	bool	Only use columns of the table that are of a numeric data type.	False

Returns:

Type	Description
Dictionary	A dictionary where the key represents the column name / row number and the values are the result of calling min on that column / row.

In [84]:

tab.min()

tab.min()

Out[84]:

column_a	1
column_b	15
column_c	8

Table.idxmax()¶

Table.idxmax(axis=0, skipna=True, numeric_only=False)

Return index of first occurrence of maximum over requested axis.

Parameters:

Name	Type	Description	Default
axis	int	The axis to calculate the idxmax across. 0 is columns, 1 is rows.	0
skipna	bool	Ignore any null values along the axis.	True
numeric_only	bool	Only use columns of the table that are of a numeric data type.	False

Returns:

Type	Description
Dictionary	A dictionary where the key represents the column name / row number and the values are the result of calling idxmax on that column / row.

Examples:

Calculate the idxmax across the columns of a table

In [85]:

tab.idxmax()

tab.idxmax()

Out[85]:

column_a	1
column_b	0
column_c	4

Calculate the idxmax across the rows of a table using only columns thar are of a numeric data type

In [86]:

tab.idxmax(axis=1, numeric_only=True)

tab.idxmax(axis=1, numeric_only=True)

Out[86]:

0	column_b
1	column_c
2	column_b
3	column_c
4	column_c

Table.idxmin()¶

Table.idxmax(axis=0, skipna=True, numeric_only=False)

Return index of first occurrence of minimum over requested axis.

Parameters:

Name	Type	Description	Default
axis	int	The axis to calculate the idxmin across. 0 is columns, 1 is rows.	0
skipna	bool	Ignore any null values along the axis.	True
numeric_only	bool	Only use columns of the table that are of a numeric data type.	False

Returns:

Type	Description
Dictionary	A dictionary where the key represents the column name / row number and the values are the result of calling idxmin on that column / row.

Examples:

Calculate the idxmin across the columns of a table

In [87]:

tab.idxmin()

tab.idxmin()

Out[87]:

column_a	4
column_b	1
column_c	2

Calculate the idxmin across the rows of a table using only columns thar are of a numeric data type

In [88]:

tab.idxmin(axis=1, numeric_only=True)

tab.idxmin(axis=1, numeric_only=True)

Out[88]:

0	column_a
1	column_a
2	column_a
3	column_a
4	column_a

Table.sum()¶

Table.sum(axis=0, skipna=True, numeric_only=False, min_count=0)

Returns the sum of all values across the given axis.

Parameters:

Name	Type	Description	Default
axis	int	The axis to calculate the sum across 0 is columns, 1 is rows.	0
skipna	bool	Ignore any null values along the axis.	True
numeric_only	bool	Only use columns of the table that are of a numeric data type.	False
min_count	int	If not set to 0 if there are less then min_count values across the axis a null value will be returned	0

Returns:

Type	Description
Dictionary	A dictionary where the key represents the column name / row number and the values are the result of calling sum on that column / row.

In [89]:

tab.sum()

tab.sum()

Out[89]:

column_a	10
column_b	247
column_c	281

Table.mean()¶

Table.mean(axis=0, numeric_only=False)

Get the mean of values across the requested axis.

Parameters:

Name	Type	Description	Default
axis	int	The axis to calculate mean across 0 is columns, 1 is rows.	0
numeric_only	bool	Include only columns / rows with numeric data.	False

Returns:

Type	Description
Dictionary	The mean across each row / column with the key corresponding to the row number or column name.

Examples:

Calculate the mean across the columns of a table

In [90]:

tab = kx.Table(data=
    {
        'a': [1, 2, 2, 4],
        'b': [1, 2, 6, 7],
        'c': [7, 8, 9, 10],
        'd': [7, 11, 14, 14]
    }
)
tab

tab = kx.Table(data= { 'a': [1, 2, 2, 4], 'b': [1, 2, 6, 7], 'c': [7, 8, 9, 10], 'd': [7, 11, 14, 14] } ) tab

Out[90]:

	a	b	c	d
0	1	1	7	7
1	2	2	8	11
2	2	6	9	14
3	4	7	10	14

In [91]:

tab.mean()

tab.mean()

Out[91]:

a	2.25
b	4f
c	8.5
d	11.5

Calculate the mean across the rows of a table

In [92]:

tab.mean(axis=1)

tab.mean(axis=1)

Out[92]:

<

0	4f
1	5.75