Pandas Like API for PyKX Tables¶

This page demonstrates the PyKX functionality that aligns with the Pandas API for DataFrame interactions.

Only operations on PyKX tables that adhere to Pandas API conventions are covered. The focus is on areas where PyKX/q can offer a performance advantage over Pandas, particularly in terms of memory footprint and execution time.

A full breakdown of the the available functionality and examples of its use can be found in the Pandas API section below.

Covered sections of the Pandas API¶

In this context, coverage refers to the functionality provided by the PyKX API for Tables, which matches the methods and attributes supported by the Pandas DataFrame API. It does not include functionality for interacting with Pandas Series objects or for reading/writing CSV/JSON files.

If there's any functionality you would like us to add to this library, please open an issue here or open a pull request here.

Property/metadata type information¶

DataFrame properties	PyKX supported?	PyKX API documentation link
columns	Yes	link
dtypes	Yes	link
empty	Yes	link
ndim	Yes	link
shape	Yes	link
size	Yes	link

Analytic functionality¶

DataFrame method	PyKX supported?	PyKX API documentation link
abs	Yes	link
agg	Yes	link
apply	Yes	link
applymap	Yes	link
groupby	Yes	link
idxmax	Yes	link
idxmin	Yes	link
kurt	Yes	link
max	Yes	link
map	Yes	link
mean	Yes	link
median	Yes	link
min	Yes	link
mode	Yes	link
sem	Yes	link
sum	Yes	link
skew	Yes	link
std	Yes	link
prod	Yes	link

Querying and data interrogation¶

DataFrame method	PyKX supported?	PyKX API documentation link
all	Yes	link
any	Yes	link
at	Yes	link
count	Yes	link
get	Yes	link
head	Yes	link
iloc	Yes	link
isna	Yes	link
isnull	Yes	link
loc	Yes	link
notna	Yes	link
notnull	Yes	link
sample	Yes	link
select_dtypes	Yes	link
tail	Yes	link

Data preprocessing¶

DataFrame method	PyKX supported?	PyKX API documentation link
add_prefix	Yes	link
add_suffix	Yes	link
astype	Yes	link
drop	Yes	link
drop_duplicates	Yes	link
pop	Yes	link
rename	Yes	link
reset_index	Yes	link
set_index	Yes	link

Data joins/merge¶

DataFrame method	PyKX supported?	PyKX API documentation link
merge	Yes	link
merge_asof	Yes	link

Data sorting¶

DataFrame method	PyKX supported?	PyKX API documentation link
sort_values	Yes	link
nlargest	Yes	link
nsmallest	Yes	link

Unsupported functionality¶

DataFrame methods	PyKX supported?	Additional information
*from*	No	Functionality for the creation of PyKX Tables from alternative data sources is not supported at this time.
*plot*	No	Functionality for the plotting of columns/tables is not supported at this time.
*sparse*	No	Sparse data like interactions presently not supported.
to_*	No	Functionality for the conversion/persistence of PyKX Tables to other formats is not supported at this time.

Pandas API¶

This notebook provides a demonstration of the capabilities of the pandas-like API for PyKX Table objects.

To follow along, download this notebook.

This demonstration outlines 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
9	9	1

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
9	9	1

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
18	9	1

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
8	8	GOOG	157.248	372	50
...	...	...	...	...	...
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]:

print(tab.dtypes)

print(tab.dtypes)

columns datatypes      
-----------------------
x       "kx.LongAtom"  
y       "kx.SymbolAtom"
z       "kx.FloatAtom" 
w       "kx.LongAtom"  
v       "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 number 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
8	8	AAPL	280.2929	230	250
...	...	...	...	...	...
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'])

Out[22]:

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

1,000 rows × 1 columns

Get the z column from the table as a vector.

In [23]:

tab.get('z')

tab.get('z')

Out[23]:

pykx.FloatVector(pykx.q('326.1157 4.083758 81.29854 256.6323 398.2529 429.4278 470.0497 409.6725 280.2..'))

Get the y and z columns from the table.

In [24]:

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

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

Out[24]:

	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
8	AAPL	280.2929
...	...	...
999	AAPL	142.8215

1,000 rows × 2 columns

Attempt to get the q column from the table and receive the default value not found as that column does not exist. Will error if default value is not set.

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
9	9	AAPL	223.9135	153	100

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
8	21	AAPL	315.1793	291	200
...	...	...	...	...	...
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
8	AAPL	315.1793	291
...	...	...	...
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
8	8	AAPL	280.2929	230	250
...	...	...	...	...	...
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
8	AAPL
...	...
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
8	14	GOOG	385.7119	424	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
8	8	AAPL	280.2929	230	250
...	...	...	...	...	...
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
8	8	AAPL	280.2929	230	250
...	...	...	...	...	...
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
9	400	MSFT	105.4923	628	250

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
8	285	MSFT	211.3792	982	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
8	985	AAPL	391.4535	71	200
...	...	...	...	...	...
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.
• If both include and exclude are passed then exclude is ignored.
• If no columns are to be returned then pykx.Identity(pykx.q('::')) will be returned.
• For a kx.KeyedTable the key columns are all always returned, filtering only applies to value columns.
• For a kx.KeyedTable if no value column is to be returned then pykx.Identity(pykx.q('::')) will be returned.

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 or 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

If no columns are to be returned then pykx.Identity(pykx.q('::')) is returned

In [51]:

df.select_dtypes(include = kx.FloatVector)

df.select_dtypes(include = kx.FloatVector)

Out[51]:

pykx.Identity(pykx.q('::'))

For a kx.KeyedTable the key columns are all always returned, filtering only applies to value columns.

In [52]:

dfk = df.set_index('c1')

dfk.select_dtypes(include = kx.ShortVector)

dfk = df.set_index('c1') dfk.select_dtypes(include = kx.ShortVector)

Out[52]:

	c2
c1
a	1h
b	2h
c	3h

For a kx.KeyedTable if no value column is to be returned then pykx.Identity(pykx.q('::')) will be returned.

In [53]:

dfk.select_dtypes(exclude=[kx.ShortAtom, kx.LongAtom, kx.IntAtom])

dfk.select_dtypes(exclude=[kx.ShortAtom, kx.LongAtom, kx.IntAtom])

Out[53]:

pykx.Identity(pykx.q('::'))

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 [54]:

tab.tail()

tab.tail()

Out[54]:

	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 [55]:

tab.tail(10)

tab.tail(10)

Out[55]:

	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
9	999	AAPL	324.1628	231	200

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 performed

Examples:

In [56]:

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[56]:

	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 [57]:

tab.sort_values(by='column_b')

tab.sort_values(by='column_b')

Out[57]:

	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 [58]:

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

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

Out[58]:

	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 [59]:

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[59]:

	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 [60]:

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

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

Out[60]:

	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 [61]:

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

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

Out[61]:

	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 [62]:

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

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

Out[62]:

	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 [63]:

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[63]:

	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 [64]:

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

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

Out[64]:

	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 [65]:

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

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

Out[65]:

	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 [66]:

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

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

Out[66]:

	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 [67]:

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[67]:

	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 [68]:

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[68]:

	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 [69]:

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 [70]:

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 [71]:

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

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

Out[71]:

	a	b	c
0	foo	1	3

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

In [72]:

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

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

Out[72]:

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

Merge tab1 and tab2 using a cross join.

In [73]:

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[73]:

	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 [74]:

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[74]:

	a	b	c
0	foo	1	3
1	baz	3	4

Inputs/Outputs will match q ij behaviour.

In [75]:

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[75]:

	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 [76]:

#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 [77]:

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[77]:

	a	left_val
0	1	a
1	5	b
2	10	c

In [78]:

right

right

Out[78]:

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

In [79]:

left.merge_asof(right)

left.merge_asof(right)

Out[79]:

	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 [80]:

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[80]:

	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 [81]:

quotes

quotes

Out[81]:

	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 [82]:

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

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

Out[82]:

	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 [83]:

# 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[83]:

	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 [84]:

tab.abs(numeric_only=True)

tab.abs(numeric_only=True)

Out[84]:

	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 [85]:

tab.count()

tab.count()

Out[85]:

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 [86]:

tab.max()

tab.max()

Out[86]:

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 [87]:

tab.min()

tab.min()

Out[87]:

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 [88]:

tab.idxmax()

tab.idxmax()

Out[88]:

column_a	1
column_b	0
column_c	4

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

In [89]:

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

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

Out[89]:

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 [90]:

tab.idxmin()

tab.idxmin()

Out[90]:

column_a	4
column_b	1
column_c	2

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

In [91]:

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

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

Out[91]:

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 [92]:

tab.sum()

tab.sum()

Out[92]:

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 [93]:

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[93]:

	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 [94]:

tab.mean()

tab.mean()

Out[94]:

a	2.25
b	4f
c	8.5
d	11.5

Calculate the mean across the rows of a table

In [95]:

tab.mean(axis=1)

tab.mean(axis=1)

Out[95]:

0	4f
1	5.75
2	7.75
3	8.75

Table.median()¶

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

Get the median of values across the requested axis.

Parameters:

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

Returns:

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

Examples:

Calculate the median across the columns of a table

In [96]:

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[96]:

	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 [97]:

tab.median()

tab.median()

Out[97]:

a	2f
b	4f
c	8.5
d	12.5

Calculate the median across the rows of a table

In [98]:

tab.median(axis=1)

tab.median(axis=1)

Out[98]:

0	4f
1	5f
2	7.5
3	8.5

Table.mode()¶

Table.mode(axis=0, numeric_only=False, dropna=True)

Get the mode of values across the requested axis.

Parameters:

Name	Type	Description	Default
axis	int	The axis to calculate mode across 0 is columns, 1 is rows.	0
numeric_only	bool	Include only columns / rows with numeric data.	False
dropna	bool	Remove null values from the data before calculating the mode.	True

Returns:

Type	Description
Table	The mode across each row / column with the column corresponding to the row number or column name.

Examples:

Calculate the mode across the columns of a table

In [99]:

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[99]:

	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 [100]:

tab.mode()

tab.mode()

Out[100]:

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

Calculate the median across the rows of a table

In [101]:

print(tab.mode(axis=1))

print(tab.mode(axis=1))

idx 0 1 2  3 
-------------
0   1 7      
1   2        
2   2 6 9  14
3   4 7 10 14

Calculate the mode across columns and keep null values.

In [102]:

tab = kx.Table(data=
    {
        'x': [0, 1, 2, 3, 4, 5, 6, 7, np.NaN, np.NaN],
        'y': [10, 11, 12, 13, 14, 15, 16, 17, 18, np.NaN],
        'z': ['a', 'b', 'c', 'd', 'd', 'e', 'e', 'f', 'g', 'h']
    }
)
tab

tab = kx.Table(data= { 'x': [0, 1, 2, 3, 4, 5, 6, 7, np.NaN, np.NaN], 'y': [10, 11, 12, 13, 14, 15, 16, 17, 18, np.NaN], 'z': ['a', 'b', 'c', 'd', 'd', 'e', 'e', 'f', 'g', 'h'] } ) tab

Out[102]:

	x	y	z
0	0	10	a
1	1	11	b
2	2	12	c
3	3	13	d
4	4	14	d
5	5	15	e
6	6	16	e
7	7	17	f
8	0n	18	g
9	0n	0n	h

In [103]:

tab.mode(dropna=False)

tab.mode(dropna=False)

Out[103]:

	x	y	z
0	0n	10	d
1	0n	11	e
2	0n	12
3	0n	13
4	0n	14
5	0n	15
6	0n	16
7	0n	17
8	0n	18
9	0n	0n

Table.prod()¶

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

Returns the product of all values across the given axis.

Parameters:

Name	Type	Description	Default
axis	int	The axis to calculate the product 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 prd on that column / row.

In [104]:

# This example will use a smaller version of the above table
# as the result of calculating the product quickly goes over the integer limits.
N = 10
tab = kx.Table(data={
    'sym': kx.random.random(N, ['AAPL', 'GOOG', 'MSFT']),
    'price': 2.5 - kx.random.random(N, 5.0),
    'traded': 10 - kx.random.random(N, 20),
    'hold': kx.random.random(N, False)
    })
tab[tab['traded'] == 0, 'traded'] = 1
tab[tab['price'] == 0, 'price'] = 1.0
tab

# This example will use a smaller version of the above table # as the result of calculating the product quickly goes over the integer limits. N = 10 tab = kx.Table(data={ 'sym': kx.random.random(N, ['AAPL', 'GOOG', 'MSFT']), 'price': 2.5 - kx.random.random(N, 5.0), 'traded': 10 - kx.random.random(N, 20), 'hold': kx.random.random(N, False) }) tab[tab['traded'] == 0, 'traded'] = 1 tab[tab['price'] == 0, 'price'] = 1.0 tab

Out[104]:

	sym	price	traded	hold
0	GOOG	0.5558637	-7	0b
1	MSFT	0.2195904	7	1b
2	AAPL	-0.8458965	-6	0b
3	MSFT	-2.290325	-8	0b
4	GOOG	-0.8030193	-4	0b
5	GOOG	1.420972	7	1b
6	GOOG	0.1515956	3	1b
7	MSFT	-1.253654	7	1b
8	MSFT	-0.1387201	7	0b
9	MSFT	0.1938591	-6	1b

In [105]:

tab.prod(numeric_only=True)

tab.prod(numeric_only=True)

Out[105]:

price	-0.001379105
traded	-58084992
hold	0i

Table.kurt()¶

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

Return unbiased kurtosis over requested axis. Kurtosis obtained using Fisher’s definition of kurtosis (kurtosis of normal == 0.0). Normalized by N-1.

Parameters:

Name	Type	Description	Default
axis	int	Axis for the function to be applied on. 0 is columns, 1 is rows.	0
skipna	bool	Not yet implemented	True
numeric_only	bool	Only use columns of the table that are of a numeric data type.	False

Returns:

Type	Description
Dictionary	Map of columns and their yielded kurtosis values

Examples:

Calculate the kurt across the columns of a table

In [106]:

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[106]:

	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 [107]:

tab.kurt()

tab.kurt()

Out[107]:

a	2.227147
b	-4.890533
c	-1.2
d	-0.04958678

Calculate the kurtosis across the rows of a table

In [108]:

tab.kurt(axis=1)

tab.kurt(axis=1)

Out[108]:

0	-6f
1	-3.901235
2	-0.1014759
3	-0.6838056

Table.sem()¶

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

Return unbiased standard error of the mean over requested axis. Normalized by N-1 by default. This can be changed using the ddof argument.

Parameters:

Name	Type	Description	Default
axis	int	The axis to calculate the sum across. 0 is columns, 1 is rows.	0
skipna	bool	not yet implemented	True
numeric_only	bool	Only use columns of the table that are of a numeric data type.	False
ddof	int	Delta Degrees of Freedom. The divisor used in calculations is N - ddof, where N represents the number of elements.	1

Returns:

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

Examples

Calculate the sem across the columns of a table

In [109]:

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[109]:

	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 [110]:

tab.sem()

tab.sem()

Out[110]:

a	0.6291529
b	1.47196
c	0.6454972
d	1.658312

Calculate the sem across the rows of a table

In [111]:

tab.sem(axis=1)

tab.sem(axis=1)

Out[111]:

0	1.732051
1	2.25
2	2.528998
3	2.136001

Calculate sem across columns with ddof=0:

In [112]:

tab.sem(ddof=0)

tab.sem(ddof=0)

Out[112]:

a	0.5448624
b	1.274755
c	0.559017
d	1.436141

Table.skew()¶

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

Returns the skewness of all values across the given axis.

Parameters:

Name	Type	Description	Default
axis	int	The axis to calculate the skewness 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 represent the column name / row number and the values are the result of calling skew on that column / row.

In [113]:

tab.skew(numeric_only=True)

tab.skew(numeric_only=True)

Out[113]:

a	1.129338
b	0f
c	0f
d	-1.096405

Table.std()¶

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

Return sample standard deviation over requested axis. Normalized by N-1 by default. This can be changed using the ddof argument.

Parameters:

Name	Type	Description	Default
axis	int	The axis to calculate the sum across 0 is columns, 1 is rows.	0
skipna	bool	not yet implemented	True
numeric_only	bool	Only use columns of the table that are of a numeric data type.	False
ddof	int	Delta Degrees of Freedom. The divisor used in calculations is N - ddof, where N represents the number of elements.	1

Returns:

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

Examples:

Calculate the std across the columns of a table

In [114]:

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[114]:

	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 [115]:

tab.std()

tab.std()

Out[115]:

a	1.258306
b	2.94392
c	1.290994
d	3.316625

Calculate the std across the rows of a table

In [116]:

tab.std(axis=1)

tab.std(axis=1)

Out[116]:

0	3.464102
1	4.5
2	5.057997
3	4.272002

Calculate std across columns with ddof=0:

In [117]:

tab.std(ddof=0)

tab.std(ddof=0)

Out[117]:

a	1.089725
b	2.54951
c	1.118034
d	2.872281

Group By¶

Table.groupby()¶

Table.groupby(
    by=None,
    axis=0,
    level=None,
    as_index=True,
    sort=True,
    group_keys=True,
    observed=False,
    dropna=True
)

Group data based on like values within columns to easily apply operations on groups.

Parameters:

Name	Type	Description	Default
by	Union[Symbol/SymbolVector/int/list]	The column name(s) or column index(es) to group the data on.	None
axis	int	Not Yet Implemented.	0
level	Union[Symbol/SymbolVector/int/list]	The column name(s) or column index(es) to group the data on.	None
as_index	bool	Return the table with groups as the key column.	True
sort	bool	Sort the resulting table based off the key.	True
group_keys	bool	Not Yet Implemented.	True
observed	bool	Not Yet Implemented.	False
dropna	bool	Drop groups where the group is null.	True

Either by or level can be used to specify the columns to group on, using both will raise an error.

Using and integer or list of integers is only possible when calling groupby on a KeyedTable object.

Returns:

Type	Description
GroupbyTable	The resulting table after the grouping is done.

Examples:

Example Table.

In [118]:

tab = kx.Table(data={
    'Animal': ['Falcon', 'Falcon', 'Parrot', 'Parrot'],
    'Speed': [380., 370., 24., 26.],
    'Max_Altitude': [570., 555., 275., 300.]
})

tab

tab = kx.Table(data={ 'Animal': ['Falcon', 'Falcon', 'Parrot', 'Parrot'], 'Speed': [380., 370., 24., 26.], 'Max_Altitude': [570., 555., 275., 300.] }) tab

Out[118]:

	Animal	Speed	Max_Altitude
0	Falcon	380f	570f
1	Falcon	370f	555f
2	Parrot	24f	275f
3	Parrot	26f	300f

Group on the Animal column and calculate the mean of the resulting Speed and Max_Altitude columns.

In [119]:

tab.groupby('Animal').mean()

tab.groupby('Animal').mean()

Out[119]:

	Speed	Max_Altitude
Animal
Falcon	375f	562.5
Parrot	25f	287.5

Group on the Animal column and calculate the maximum speed only

In [120]:

tab.groupby('Animal')['Speed'].max()

tab.groupby('Animal')['Speed'].max()

Out[120]:

	Speed
Animal
Falcon	380f
Parrot	26f

Example table with multiple columns to group on.

In [121]:

tab = kx.Table(
    data={
        'Animal': ['Falcon', 'Falcon', 'Parrot', 'Parrot', 'Parrot'],
        'Type': ['Captive', 'Wild', 'Captive', 'Wild', 'Wild'],
        'Speed': [390., 350., 30., 20., 25.]
    })
tab = tab.set_index(2)
tab

tab = kx.Table( data={ 'Animal': ['Falcon', 'Falcon', 'Parrot', 'Parrot', 'Parrot'], 'Type': ['Captive', 'Wild', 'Captive', 'Wild', 'Wild'], 'Speed': [390., 350., 30., 20., 25.] }) tab = tab.set_index(2) tab

Out[121]:

		Speed
Animal	Type
Falcon	Captive	390f
	Wild	350f
Parrot	Captive	30f
	Wild	20f
	Wild	25f

Group on multiple columns using their indexes.

In [122]:

tab.groupby(level=[0, 1]).mean()

tab.groupby(level=[0, 1]).mean()

Out[122]:

		Speed
Animal	Type
Falcon	Captive	390f
	Wild	350f
Parrot	Captive	30f
	Wild	22.5

Example table with Nulls.

In [123]:

tab = kx.Table(
    [
        ["a", 12, 12],
        [kx.SymbolAtom.null, 12.3, 33.],
        ["b", 12.3, 123],
        ["a", 1, 1]
    ],
    columns=["a", "b", "c"]
)
tab

tab = kx.Table( [ ["a", 12, 12], [kx.SymbolAtom.null, 12.3, 33.], ["b", 12.3, 123], ["a", 1, 1] ], columns=["a", "b", "c"] ) tab

Out[123]:

	a	b	c
0	a	12	12
1		12.3	33f
2	b	12.3	123
3	a	1	1

Group on column a and keep null groups.

In [124]:

tab.groupby('a', dropna=False).sum()

tab.groupby('a', dropna=False).sum()

Out[124]:

	b	c
a
	12.3	33f
a	13	13
b	12.3	123

Group on column a keeping null groups and not using the groups as an index column.

In [125]:

tab.groupby('a', dropna=False, as_index=False).sum()

tab.groupby('a', dropna=False, as_index=False).sum()

Out[125]:

	a	b	c
idx
0		12.3	33f
1	a	13	13
2	b	12.3	123

Function Application¶

Table.apply()¶

Table.apply(
    func,
    *args,
    axis=0,
    raw=None,
    result_type=None,
    **kwargs
)

Apply a function along an axis of the DataFrame.

Objects passed to a function are passed as kx list objects.

Parameters:

Name	Type	Description	Default
func	function	Function to apply to each column or row.
*args	any	Positional arguments to pass to func in addition to the kx list.
axis	int	The axis along which the function is applied, 0 applies function to each column, 1 applied function to each row.	0
raw	bool	Not yet implemented.	None
result_type	str	Not yet implemented.	None
**kwargs	dict	Additional keyword arguments to pass as keywords to func, this argument is not implemented in the case func is a kx callable function.	None

Returns:

Type	Description
List, Dictionary or Table	Result of applying func along the giveen axis of the kx.Table.

Examples:

Example Table.

In [126]:

tab = kx.Table([[4, 9]] * 3, columns=['A', 'B'])

tab

tab = kx.Table([[4, 9]] * 3, columns=['A', 'B']) tab

Out[126]:

	A	B
0	4	9
1	4	9
2	4	9

Apply square root on each item within a column

In [127]:

tab.apply(kx.q.sqrt)

tab.apply(kx.q.sqrt)

Out[127]:

	A	B
0	2f	3f
1	2f	3f
2	2f	3f

Apply a reducing function sum on either axis

In [128]:

tab.apply(kx.q.sum)

tab.apply(kx.q.sum)

Out[128]:

A	12
B	27

In [129]:

tab.apply(lambda x: sum(x), axis=1)

tab.apply(lambda x: sum(x), axis=1)

Out[129]:

pykx.LongVector(pykx.q('13 13 13'))

Table.map()¶

Table.map(
    func,
    na_action=None,
    *args,
    **kwargs
)

Apply a function to all elements of a table.

Parameters:

Name	Type	Description	Default
func	function	Function to apply to every element of a table.
na_action	str	if supplied 'ignore' then propagate null values without passing to the function	None
**kwargs	dict	Additional keyword arguments to pass as keywords to func, this argument is not implemented in the case func is a kx callable function.	None

Returns:

Type	Description
Table	The transformed kx.Table object.

A vectorized version of a function you are attempting to call often exists, using the vectorized operation will be much faster in most cases. For example multiplying all elements of a table by 2:

tab.map(lambda x: 2*x)

The following is a vectorized version of the previous example:

tab * 2

Examples:

Example Table.

In [130]:

tab = kx.Table(data={
    'x': [[1, 2, 3], 1, [1, 2]],
    'y': [kx.LongAtom.null, 1, 2]})

tab = kx.Table(data={ 'x': [[1, 2, 3], 1, [1, 2]], 'y': [kx.LongAtom.null, 1, 2]})

Apply Python lambda function returning the length of the string representation of all elements within a table

In [131]:

tab.map(lambda x:len(str(x)))

tab.map(lambda x:len(str(x)))

Out[131]:

	x	y
0	5	2
1	1	1
2	3	1

Apply a Python lambda function returning the length of the string representation of all elements within a table ignoring any null values

In [132]:

tab.map(lambda x:len(str(x)), na_action='ignore')

tab.map(lambda x:len(str(x)), na_action='ignore')

Out[132]:

	x	y
0	5	0N
1	1	1
2	3	1

Apply a Python function to all elements within a table to count the number of values in all cells. Including an example where multiple arguments are supplied

In [133]:

def _multi_arg_count(x, y=0):
    try:
        count = len(x)
    except TypeError as err:
        count = 1
    return count + y

def _multi_arg_count(x, y=0): try: count = len(x) except TypeError as err: count = 1 return count + y

In [134]:

tab.map(_multi_arg_count)

tab.map(_multi_arg_count)

Out[134]:

	x	y
0	3	1
1	1	1
2	2	1

In [135]:

tab.map(_multi_arg_count, y = 5)

tab.map(_multi_arg_count, y = 5)

Out[135]:

	x	y
0	8	6
1	6	6
2	7	6

Table.applymap()¶

Table.applymap(
    func,
    na_action=None,
    *args,
    **kwargs
)

Table.applymap and Table.map functions are aliases, both have the same underlying definition and are both supplied to allow user migration of code if using Pandas 2.1 (which uses map) or versions prior to this which make use of applymap

Apply a function to all elements of a table.

Parameters:

Name	Type	Description	Default
func	function	Function to apply to every element of a table.
na_action	str	if supplied 'ignore' then propagate null values without passing to the function	None
**kwargs	dict	Additional keyword arguments to pass as keywords to func, this argument is not implemented in the case func is a kx callable function.	None

Returns:

Type	Description
Table	The transformed kx.Table object.

A vectorized version of a function you are attempting to call often exists, using the vectorized operation will be much faster in most cases. For example multiplying all elements of a table by 2:

tab.applymap(lambda x: 2*x)

The following is a vectorized version of the previous example:

tab * 2

Examples:

Example Table.

In [136]:

tab = kx.Table(data={
    'x': [[1, 2, 3], 1, [1, 2]],
    'y': [kx.LongAtom.null, 1, 2]})

tab = kx.Table(data={ 'x': [[1, 2, 3], 1, [1, 2]], 'y': [kx.LongAtom.null, 1, 2]})

Apply Python lambda function returning the length of the string representation of all elements within a table

In [137]:

tab.applymap(lambda x:len(str(x)))

tab.applymap(lambda x:len(str(x)))

Out[137]:

	x	y
0	5	2
1	1	1
2	3	1

Apply a Python lambda function returning the length of the string representation of all elements within a table ignoring any null values

In [138]:

tab.applymap(lambda x:len(str(x)), na_action='ignore')

tab.applymap(lambda x:len(str(x)), na_action='ignore')

Out[138]:

	x	y
0	5	0N
1	1	1
2	3	1

Apply a Python function to all elements within a table to count the number of values in all cells. Including an example where multiple arguments are supplied

In [139]:

def _multi_arg_count(x, y=0):
    try:
        count = len(x)
    except TypeError as err:
        count = 1
    return count + y

def _multi_arg_count(x, y=0): try: count = len(x) except TypeError as err: count = 1 return count + y

In [140]:

tab.applymap(_multi_arg_count)

tab.applymap(_multi_arg_count)

Out[140]:

	x	y
0	3	1
1	1	1
2	2	1

In [141]:

tab.applymap(_multi_arg_count, y = 5)

tab.applymap(_multi_arg_count, y = 5)

Out[141]:

	x	y
0	8	6
1	6	6
2	7	6

Aggregate¶

Table.agg()¶

Table.agg(
    func,
    axis=0,
    *args,
    **kwargs
)

Aggregate data using one or more operations over a specified axis

Objects passed to a function are passed as kx vector/list objects.

Parameters:

Name	Type	Description	Default
func	function, str, list or dict	Function to use for aggregating the data. If a function this must either work when passed a Table or when passed to Table.apply Accepted combinations are: function string function name list of functions and/or function names, e.g. [kx.q.sum, 'mean'] dict of axis labels -> functions or function names
*args	any	Positional arguments to pass to func in addition to the kx list.
axis	int	The axis along which the function is applied, 0 applies function to each column, at present row based application is not supported.	0
**kwargs	dict	Additional keyword arguments to pass as keywords to func, this argument is not implemented in the case func is a kx callable function.	None

Returns:

Type	Description
List, Dictionary or Table	Result of applying func along the giveen axis of the kx.Table.

Examples:

Example Table.

In [142]:

tab = kx.Table([[1, 2, 3],
                   [4, 5, 6],
                   [7, 8, 9],
                   [kx.FloatAtom.null, kx.FloatAtom.null, kx.FloatAtom.null]],
                  columns=['A', 'B', 'C'])

tab

tab = kx.Table([[1, 2, 3], [4, 5, 6], [7, 8, 9], [kx.FloatAtom.null, kx.FloatAtom.null, kx.FloatAtom.null]], columns=['A', 'B', 'C']) tab

Out[142]:

	A	B	C
0	1	2	3
1	4	5	6
2	7	8	9
3	0n	0n	0n

Aggregate a list of functions over rows

In [143]:

tab.agg(['sum', 'min'])

tab.agg(['sum', 'min'])

Out[143]:

	A	B	C
function
sum	12	15	18
min	1	2	3

Perform an aggregation using a user specified function

In [144]:

import statistics
def mode(x):
    return statistics.mode(x)
tab.agg(mode)

import statistics def mode(x): return statistics.mode(x) tab.agg(mode)

Out[144]:

A	1
B	2
C	3

Apply an aggregation supplying column specification for supplied function

In [145]:

tab.agg({'A': 'max', 'B': mode})

tab.agg({'A': 'max', 'B': mode})

Out[145]:

	A	B	C
function
max	7	()	()
mode	0N	2	()

Data Preprocessing¶

Table.add_prefix()¶

Table.add_prefix(columns)

Rename columns adding a prefix in a table and return the resulting Table object.

Parameters:

Name	Type	Description	Default
prefix	str	The string that will be concatenated with the name of the columns	required
axis	int	Axis to add prefix on.	0

Returns:

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

Examples:

The initial table to which a prefix will be added to its columns

In [146]:

tab.head()

tab.head()

Out[146]:

	A	B	C
0	1	2	3
1	4	5	6
2	7	8	9
3	0n	0n	0n
4	1	2	3

Add "col_" to table columns:

In [147]:

tab.add_prefix(prefix="col_").head()

tab.add_prefix(prefix="col_").head()

Out[147]:

	col_A	col_B	col_C
0	1	2	3
1	4	5	6
2	7	8	9
3	0n	0n	0n
4	1	2	3

Table.add_suffix()¶

Table.add_suffix(columns)

Rename columns adding a suffix in a table and return the resulting Table object.

Parameters:

Name	Type	Description	Default
suffix	str	The string that will be concatenated with the name of the columns	required
axis	int	Axis to add suffix on.	0

Returns:

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

Examples:

The initial table to which a suffix will be added to its columns

In [148]:

tab.head()

tab.head()

Out[148]:

	A	B	C
0	1	2	3
1	4	5	6
2	7	8	9
3	0n	0n	0n
4	1	2	3

Add "_col" to table columns:

In [149]:

tab.add_suffix(suffix="_col").head()

tab.add_suffix(suffix="_col").head()

Out[149]:

	A_col	B_col	C_col
0	1	2	3
1	4	5	6
2	7	8	9
3	0n	0n	0n
4	1	2	3

Table.astype()¶

Table.astype(dtype, copy=True, errors='raise')

Cast a column/columns of the Dataframes object to a specified dtype.

Parameters:

Name	Type	Description	Default
dtype	data type, or dict of column name -> data type	Use a PyKx wrapper data type or Python type to cast all columns to the same type. Alternatively, use {col: dtype, …}, where col is a column label and dtype is PyKx wrapper data type to cast one or more of the DataFrame’s columns to column-specific types.
copy	Boolean	Default of True, False not implemented	True
errors	{‘raise’, ‘ignore’}	If passed anything other than 'raise', it will return the dataframe	'raise'

Returns:

Type	Description
Dataframe	The dataframe with columns casted according to passed dtypes

Examples:

The examples in the section will use the example table.

In [150]:

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

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

Cast all columns to dtype LongVector

In [151]:

df.astype(kx.LongVector)

df.astype(kx.LongVector)

Out[151]:

	c1	c2	c3	c4
0	1	1	1	1
1	2	2	2	2
2	3	3	3	3

Casting as specified in the dictionary supplied with given dtype per column

In [152]:

df.astype({'c1':kx.LongVector, 'c2':'kx.ShortVector'})

df.astype({'c1':kx.LongVector, 'c2':'kx.ShortVector'})

Out[152]:

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

The next example will use this table

In [153]:

df = kx.Table(data={
    'c1': kx.TimestampAtom('now'),
    'c2': ['abc', 'def', 'ghi'],
    'c3': [1, 2, 3],
    'c4': [b'abc', b'def', b'ghi'],
    'c5': b'abc',
    'c6': [[1, 2, 3], [4, 5, 6], [7, 8, 9]]
    })
df

df = kx.Table(data={ 'c1': kx.TimestampAtom('now'), 'c2': ['abc', 'def', 'ghi'], 'c3': [1, 2, 3], 'c4': [b'abc', b'def', b'ghi'], 'c5': b'abc', 'c6': [[1, 2, 3], [4, 5, 6], [7, 8, 9]] }) df

Out[153]:

	c1	c2	c3	c4	c5	c6
0	2025.02.14D12:04:23.305730442	abc	1	"abc"	"a"	1 2 3
1	2025.02.14D12:04:23.305730442	def	2	"def"	"b"	4 5 6
2	2025.02.14D12:04:23.305730442	ghi	3	"ghi"	"c"	7 8 9

Casting char and string columns to symbol columns

In [154]:

df.astype({'c4':kx.SymbolVector, 'c5':kx.SymbolVector})

df.astype({'c4':kx.SymbolVector, 'c5':kx.SymbolVector})

Out[154]:

	c1	c2	c3	c4	c5	c6
0	2025.02.14D12:04:23.305730442	abc	1	abc	a	1 2 3
1	2025.02.14D12:04:23.305730442	def	2	def	b	4 5 6
2	2025.02.14D12:04:23.305730442	ghi	3	ghi	c	7 8 9

Table.drop()¶

Table.drop(item, axis=0)

Remove either columns or rows from a table and return the resulting Table object.

Parameters:

Name	Type	Description	Default
item	Union[str, list[str]]	The column name(s) or row number(s) to drop from the table.	required
axis	int	The column name or list of names to pop from the table.	0

Returns:

Type	Description
Table	A table with the given column(s) / row(s) removed.

Examples:

Drop rows from a table.

In [155]:

# 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[155]:

	x	y	z	w	v
0	0	AAPL	199.1148	467	0
1	1	MSFT	123.2344	832	50
2	2	GOOG	301.4733	150	0
3	3	GOOG	36.14336	784	0N
4	4	MSFT	246.0589	802	100

In [156]:

tab.drop([0, 2, 4, 6, 8, 10]).head()

tab.drop([0, 2, 4, 6, 8, 10]).head()

Out[156]:

	x	y	z	w	v
0	1	MSFT	123.2344	832	50
1	3	GOOG	36.14336	784	0N
2	5	MSFT	449.9769	447	50
3	7	GOOG	478.7092	437	250
4	9	GOOG	182.2163	25	0N

Drop columns from a table.

In [157]:

tab.drop('y', axis=1).head()

tab.drop('y', axis=1).head()

Out[157]:

	x	z	w	v
0	0	199.1148	467	0
1	1	123.2344	832	50
2	2	301.4733	150	0
3	3	36.14336	784	0N
4	4	246.0589	802	100

Table.drop_duplicates()¶

Table.drop_duplicates()

Remove either columns or rows from a table and return the resulting Table object.

Returns:

Type	Description
Table	A table with all duplicate rows removed.

Examples:

Create a table with duplicates for the example

In [158]:

N = 100
tab2 = kx.Table(data ={
    'x': kx.random.random(N, ['AAPL', 'GOOG', 'MSFT']),
    'x1': kx.random.random(N, 3)
    })
tab2

N = 100 tab2 = kx.Table(data ={ 'x': kx.random.random(N, ['AAPL', 'GOOG', 'MSFT']), 'x1': kx.random.random(N, 3) }) tab2

Out[158]:

	x	x1
0	MSFT	0
1	AAPL	1
2	AAPL	2
3	MSFT	0
4	MSFT	1
5	GOOG	1
6	GOOG	2
7	MSFT	0
8	AAPL	0
...	...	...
99	AAPL	2

100 rows × 2 columns

Drop all duplicate rows from the table.

In [159]:

tab2.drop_duplicates()

tab2.drop_duplicates()

Out[159]:

	x	x1
0	MSFT	0
1	AAPL	1
2	AAPL	2
3	MSFT	1
4	GOOG	1
5	GOOG	2
6	AAPL	0
7	MSFT	2
8	GOOG	0

Table.pop()¶

Table.pop(item)

Remove a column or columns from a table by column name and return the column after it has been removed.

Parameters:

Name	Type	Description	Default
item	Union[str, list[str]]	The column name or list of names to pop from the table.	required

Returns:

Type	Description
Table	A table containing only the columns removed from the input table.

Examples:

Remove the v column from the table and return it.

In [160]:

display(tab.head())
print('\n\nPop the `v` column out of the table')
display(tab.pop("v"))
print('\n\nUpdated Table')
display(tab.head())

display(tab.head()) print('\n\nPop the `v` column out of the table') display(tab.pop("v")) print('\n\nUpdated Table') display(tab.head())

	x	y	z	w	v
0	0	AAPL	199.1148	467	0
1	1	MSFT	123.2344	832	50
2	2	GOOG	301.4733	150	0
3	3	GOOG	36.14336	784	0N
4	4	MSFT	246.0589	802	100

Pop the `v` column out of the table

	v
0	0
1	50
2	0
3	0N
4	100
5	50
6	50
7	250
8	250
...	...
999	0N

1,000 rows × 1 columns

Updated Table

	x	y	z	w
0	0	AAPL	199.1148	467
1	1	MSFT	123.2344	832
2	2	GOOG	301.4733	150
3	3	GOOG	36.14336	784
4	4	MSFT	246.0589	802

Remove the z and w columns from the table and return them.

In [161]:

display(tab.head())
print('\n\nPop the `z` and `w` columns out of the table')
display(tab.pop(["z", "w"]).head())
print('\n\nUpdated Table')
display(tab.head())

display(tab.head()) print('\n\nPop the `z` and `w` columns out of the table') display(tab.pop(["z", "w"]).head()) print('\n\nUpdated Table') display(tab.head())

	x	y	z	w
0	0	AAPL	199.1148	467
1	1	MSFT	123.2344	832
2	2	GOOG	301.4733	150
3	3	GOOG	36.14336	784
4	4	MSFT	246.0589	802

Pop the `z` and `w` columns out of the table

	z	w
0	199.1148	467
1	123.2344	832
2	301.4733	150
3	36.14336	784
4	246.0589	802

Updated Table

	x	y
0	0	AAPL
1	1	MSFT
2	2	GOOG
3	3	GOOG
4	4	MSFT

Table.rename()¶

Table.rename(mapper=None, index=None, columns=None, axis=None, copy=None, inplace=False, level=None, errors='ignore')

Rename columns in a table and return the resulting Table object.

Parameters:

Name	Type	Description	Default
mapper	dict	A dictionary of either new index or column names to new names to be used in conjunction with the axis parameter.	None
columns	dict	A dictionary of column name to new column name to use when renaming.	None
index	dict	A dictionary of index to new index name to use when renaming single key column keyed tables.	None
axis	{0 or 'index', 1 or 'columns'}	Designating the axis to be renamed by the mapper dictionary.	None
copy	None	Not yet implemented.	None
inplace	bool	Not yet implemented.	None
level	None	Not yet implemented.	None
errors	string	Not yet implemented.	None

Returns:

Type	Description
Table	A table with the given columns or indices renamed.

Examples:

The initial table we will be renaming columns on and a keyed table to rename the index on.

In [162]:

tab.head()
key_tab = kx.KeyedTable(data=tab)

tab.head() key_tab = kx.KeyedTable(data=tab)

Rename column x to index and y to symbol using the columns keyword.

In [163]:

tab.rename(columns={'x': 'index', 'y': 'symbol'}).head()

tab.rename(columns={'x': 'index', 'y': 'symbol'}).head()

Out[163]:

	index	symbol
0	0	AAPL
1	1	MSFT
2	2	GOOG
3	3	GOOG
4	4	MSFT

Rename column x to index and y to symbol by setting the axis keyword.

In [164]:

tab.rename({'x': 'index', 'y': 'symbol'}, axis = 1).head()

tab.rename({'x': 'index', 'y': 'symbol'}, axis = 1).head()

Out[164]:

	index	symbol
0	0	AAPL
1	1	MSFT
2	2	GOOG
3	3	GOOG
4	4	MSFT

Rename index of a keyed table by using literal index as the axis parameter.

In [165]:

key_tab.rename({0:"a", 1:"b"}, axis = 'index').head()

key_tab.rename({0:"a", 1:"b"}, axis = 'index').head()

Out[165]:

	x	y
idx
`a	0	AAPL
`b	1	MSFT
2	2	GOOG
3	3	GOOG
4	4	MSFT

Table.replace()¶

Table.replace(to_replace, value)

Replace all values in a table with another given value.

Parameters:

Name	Type	Description	Default
to_replace	any	Value of element in table you wish to replace.	None
value	any	New value to perform replace with.	None

Returns:

Type	Description
Table	A table with the given elements replaced with new value.

Examples

Create an unkeyed Table and a KeyedTable with elements to be replaced.

In [166]:

tab = kx.q('([] a:2 2 3; b:4 2 6; c:(1b;0b;1b); d:(`a;`b;`c); e:(1;2;`a))')
ktab = kx.q('([a:2 2 3]b:4 2 6; c:(1b;0b;1b); d:(`a;`b;`c); e:(1;2;`a))')
ktab

tab = kx.q('([] a:2 2 3; b:4 2 6; c:(1b;0b;1b); d:(`a;`b;`c); e:(1;2;`a))') ktab = kx.q('([a:2 2 3]b:4 2 6; c:(1b;0b;1b); d:(`a;`b;`c); e:(1;2;`a))') ktab

Out[166]:

	b	c	d	e
a
2	4	1b	a	1
2	2	0b	b	2
3	6	1b	c	`a

Replace all instances of 2 in the KeyedTable with 123. Note the key column remains unchanged.

In [167]:

ktab.replace(2,123)

ktab.replace(2,123)

Out[167]:

	b	c	d	e
a
2	4	1b	a	1
2	123	0b	b	123
3	6	1b	c	`a

Replace all True values with a list of strings.

In [168]:

tab.replace(True, (b"one", b"two", b"three"))

tab.replace(True, (b"one", b"two", b"three"))

Out[168]:

	a	b	c	d	e
0	2	4	("one";"two";"three")	a	1
1	2	2	0b	b	2
2	3	6	("one";"two";"three")	c	`a

Table.reset_index()¶

Table.reset_index(levels, *,
                  drop=False, inplace=False,
                  col_level=0, col_fill='',
                  allow_duplicates=False,
                  names=None)

Reset the keys/index of a keyed PyKX table. This can be used to remove/unset one or more keys within a table.

Parameters:

Name	Type	Description	Default
level	int, str or list	The name/indices of the keys to be reset within the table.	None
drop	Boolean	Should remaining key columns be removed from the table post index resetting.	False
inplace	Boolean	Not Yet Implemented	False
col_level	int or str	Not Yet Implemented	0
col_fill	object	Not Yet Implemented	''
allow_duplicates	Boolean	Can duplicate columns be created	False
names	str or list	Not Yet Implemented	None

Returns:

Type	Description
Dataframe	The dataframe with table updated following index reset request

Examples:

Generate data to be used for index resetting

In [169]:

N = 1000
qtab = kx.Table(data = {
    'x0': kx.random.random(N, ['a', 'b', 'c']),
    'x1': kx.random.random(N, ['d', 'e', 'f']),
    'x2': kx.random.random(N, ['g', 'h', 'i']),
    'y0': kx.random.random(N, 10.0),
    'y1': kx.random.random(N, 10.0),
    'y2': kx.random.random(N, kx.GUIDAtom.null)
    }).set_index(['x0', 'x1', 'x2'])
qtab

N = 1000 qtab = kx.Table(data = { 'x0': kx.random.random(N, ['a', 'b', 'c']), 'x1': kx.random.random(N, ['d', 'e', 'f']), 'x2': kx.random.random(N, ['g', 'h', 'i']), 'y0': kx.random.random(N, 10.0), 'y1': kx.random.random(N, 10.0), 'y2': kx.random.random(N, kx.GUIDAtom.null) }).set_index(['x0', 'x1', 'x2']) qtab

Out[169]:

			y0	y1	y2
x0	x1	x2
a	f	i	1.238681	6.299753	0248b2cb-974d-50d7-f79c-b7c27fac8446
	d	h	2.194869	8.953976	ccccfb99-20f7-ba7f-3f32-265a9031a3a1
		g	1.155368	8.708738	cdeab52e-ac7b-6b53-3dee-3c810743aaf0
c	d	i	9.413713	5.084035	9f1da09e-19ce-6297-f245-ad903b405afc
	f	h	2.205184	2.474564	a6478672-84d9-50e7-ed24-6a20ce93f66d
a	d	h	3.715352	3.317283	848e2f8c-d8c9-4e2d-a44a-12c4b062f0a4
b	d	g	8.84107	7.208391	800b0a15-1bb3-259e-985d-0bb9b100b86e
c	e	h	2.894724	3.737715	05b7da51-c08f-df70-449e-a3e9bb3486c3
	f	h	5.169422	0.8935537	e8c7c115-fbf6-75ea-5707-420606595435
...	...	...	...	...	...
a	d	g	6.613845	2.998951	cae053d3-6598-5ec7-4d7b-c31cebbf72ec

1,000 rows × 6 columns

Resetting the index of the table will result in original index columns being added to the table directly

In [170]:

qtab.reset_index()

qtab.reset_index()

Out[170]:

	x0	x1	x2	y0	y1	y2
0	a	f	i	1.238681	6.299753	0248b2cb-974d-50d7-f79c-b7c27fac8446
1	a	d	h	2.194869	8.953976	ccccfb99-20f7-ba7f-3f32-265a9031a3a1
2	a	d	g	1.155368	8.708738	cdeab52e-ac7b-6b53-3dee-3c810743aaf0
3	c	d	i	9.413713	5.084035	9f1da09e-19ce-6297-f245-ad903b405afc
4	c	f	h	2.205184	2.474564	a6478672-84d9-50e7-ed24-6a20ce93f66d
5	a	d	h	3.715352	3.317283	848e2f8c-d8c9-4e2d-a44a-12c4b062f0a4
6	b	d	g	8.84107	7.208391	800b0a15-1bb3-259e-985d-0bb9b100b86e
7	c	e	h	2.894724	3.737715	05b7da51-c08f-df70-449e-a3e9bb3486c3
8	c	f	h	5.169422	0.8935537	e8c7c115-fbf6-75ea-5707-420606595435
...	...	...	...	...	...	...
999	a	d	g	6.613845	2.998951	cae053d3-6598-5ec7-4d7b-c31cebbf72ec

1,000 rows × 6 columns

Reset the index adding a specified named column to the table

In [171]:

qtab.reset_index('x0')

qtab.reset_index('x0')

Out[171]:

		x0	y0	y1	y2
x1	x2
f	i	a	1.238681	6.299753	0248b2cb-974d-50d7-f79c-b7c27fac8446
d	h	a	2.194869	8.953976	ccccfb99-20f7-ba7f-3f32-265a9031a3a1
	g	a	1.155368	8.708738	cdeab52e-ac7b-6b53-3dee-3c810743aaf0
	i	c	9.413713	5.084035	9f1da09e-19ce-6297-f245-ad903b405afc
f	h	c	2.205184	2.474564	a6478672-84d9-50e7-ed24-6a20ce93f66d
d	h	a	3.715352	3.317283	848e2f8c-d8c9-4e2d-a44a-12c4b062f0a4
	g	b	8.84107	7.208391	800b0a15-1bb3-259e-985d-0bb9b100b86e
e	h	c	2.894724	3.737715	05b7da51-c08f-df70-449e-a3e9bb3486c3
f	h	c	5.169422	0.8935537	e8c7c115-fbf6-75ea-5707-420606595435
...	...	...	...	...	...
d	g	a	6.613845	2.998951	cae053d3-6598-5ec7-4d7b-c31cebbf72ec

1,000 rows × 6 columns

Reset the index using multiple named columns

In [172]:

qtab.reset_index(['x0', 'x1'])

qtab.reset_index(['x0', 'x1'])

Out[172]:

	x0	x1	y0	y1	y2
x2
i	a	f	1.238681	6.299753	0248b2cb-974d-50d7-f79c-b7c27fac8446
h	a	d	2.194869	8.953976	ccccfb99-20f7-ba7f-3f32-265a9031a3a1
g	a	d	1.155368	8.708738	cdeab52e-ac7b-6b53-3dee-3c810743aaf0
i	c	d	9.413713	5.084035	9f1da09e-19ce-6297-f245-ad903b405afc
h	c	f	2.205184	2.474564	a6478672-84d9-50e7-ed24-6a20ce93f66d
h	a	d	3.715352	3.317283	848e2f8c-d8c9-4e2d-a44a-12c4b062f0a4
g	b	d	8.84107	7.208391	800b0a15-1bb3-259e-985d-0bb9b100b86e
h	c	e	2.894724	3.737715	05b7da51-c08f-df70-449e-a3e9bb3486c3
h	c	f	5.169422	0.8935537	e8c7c115-fbf6-75ea-5707-420606595435
...	...	...	...	...	...
g	a	d	6.613845	2.998951	cae053d3-6598-5ec7-4d7b-c31cebbf72ec

1,000 rows × 6 columns

Reset the index specifying the column number which is to be added to the table

In [173]:

qtab.reset_index(0)

qtab.reset_index(0)

Out[173]:

		x0	y0	y1	y2
x1	x2
f	i	a	1.238681	6.299753	0248b2cb-974d-50d7-f79c-b7c27fac8446
d	h	a	2.194869	8.953976	ccccfb99-20f7-ba7f-3f32-265a9031a3a1
	g	a	1.155368	8.708738	cdeab52e-ac7b-6b53-3dee-3c810743aaf0
	i	c	9.413713	5.084035	9f1da09e-19ce-6297-f245-ad903b405afc
f	h	c	2.205184	2.474564	a6478672-84d9-50e7-ed24-6a20ce93f66d
d	h	a	3.715352	3.317283	848e2f8c-d8c9-4e2d-a44a-12c4b062f0a4
	g	b	8.84107	7.208391	800b0a15-1bb3-259e-985d-0bb9b100b86e
e	h	c	2.894724	3.737715	05b7da51-c08f-df70-449e-a3e9bb3486c3
f	h	c	5.169422	0.8935537	e8c7c115-fbf6-75ea-5707-420606595435
...	...	...	...	...	...
d	g	a	6.613845	2.998951	cae053d3-6598-5ec7-4d7b-c31cebbf72ec

1,000 rows × 6 columns

Reset the index specifying multiple numbered columns

In [174]:

qtab.reset_index([0, 2])

qtab.reset_index([0, 2])

Out[174]:

	x0	x2	y0	y1	y2
x1
f	a	i	1.238681	6.299753	0248b2cb-974d-50d7-f79c-b7c27fac8446
d	a	h	2.194869	8.953976	ccccfb99-20f7-ba7f-3f32-265a9031a3a1
d	a	g	1.155368	8.708738	cdeab52e-ac7b-6b53-3dee-3c810743aaf0
d	c	i	9.413713	5.084035	9f1da09e-19ce-6297-f245-ad903b405afc
f	c	h	2.205184	2.474564	a6478672-84d9-50e7-ed24-6a20ce93f66d
d	a	h	3.715352	3.317283	848e2f8c-d8c9-4e2d-a44a-12c4b062f0a4
d	b	g	8.84107	7.208391	800b0a15-1bb3-259e-985d-0bb9b100b86e
e	c	h	2.894724	3.737715	05b7da51-c08f-df70-449e-a3e9bb3486c3
f	c	h	5.169422	0.8935537	e8c7c115-fbf6-75ea-5707-420606595435
...	...	...	...	...	...
d	a	g	6.613845	2.998951	cae053d3-6598-5ec7-4d7b-c31cebbf72ec

1,000 rows × 6 columns

Drop index columns from table

In [175]:

qtab.reset_index(drop=True)

qtab.reset_index(drop=True)

Out[175]:

	y0	y1	y2
0	1.238681	6.299753	0248b2cb-974d-50d7-f79c-b7c27fac8446
1	2.194869	8.953976	ccccfb99-20f7-ba7f-3f32-265a9031a3a1
2	1.155368	8.708738	cdeab52e-ac7b-6b53-3dee-3c810743aaf0
3	9.413713	5.084035	9f1da09e-19ce-6297-f245-ad903b405afc
4	2.205184	2.474564	a6478672-84d9-50e7-ed24-6a20ce93f66d
5	3.715352	3.317283	848e2f8c-d8c9-4e2d-a44a-12c4b062f0a4
6	8.84107	7.208391	800b0a15-1bb3-259e-985d-0bb9b100b86e
7	2.894724	3.737715	05b7da51-c08f-df70-449e-a3e9bb3486c3
8	5.169422	0.8935537	e8c7c115-fbf6-75ea-5707-420606595435
...	...	...	...
999	6.613845	2.998951	cae053d3-6598-5ec7-4d7b-c31cebbf72ec

1,000 rows × 3 columns

Drop specified key columns from table

In [176]:

qtab.reset_index('x0', drop=True)

qtab.reset_index('x0', drop=True)

Out[176]:

		y0	y1	y2
x1	x2
f	i	1.238681	6.299753	0248b2cb-974d-50d7-f79c-b7c27fac8446
d	h	2.194869	8.953976	ccccfb99-20f7-ba7f-3f32-265a9031a3a1
	g	1.155368	8.708738	cdeab52e-ac7b-6b53-3dee-3c810743aaf0
	i	9.413713	5.084035	9f1da09e-19ce-6297-f245-ad903b405afc
f	h	2.205184	2.474564	a6478672-84d9-50e7-ed24-6a20ce93f66d
d	h	3.715352	3.317283	848e2f8c-d8c9-4e2d-a44a-12c4b062f0a4
	g	8.84107	7.208391	800b0a15-1bb3-259e-985d-0bb9b100b86e
e	h	2.894724	3.737715	05b7da51-c08f-df70-449e-a3e9bb3486c3
f	h	5.169422	0.8935537	e8c7c115-fbf6-75ea-5707-420606595435
...	...	...	...	...
d	g	6.613845	2.998951	cae053d3-6598-5ec7-4d7b-c31cebbf72ec

1,000 rows × 5 columns

Table.set_index()¶

Table.set_index(
    keys,
    drop=True,
    append=False,
    inplace=False,
    verify_integrity=False,
)

Add index/indexes to a Table/KeyedTable.

Parameters:

Name	Type	Description	Default
keys	Union[Symbol/SymbolVector/Table]	The key(s) or data to key on	required
drop	bool	Not Yet Implemented	True
append	bool	Whether to append columns to existing index.	False
inplace	bool	Not Yet Implemented	False
verify_integrity	bool	Check the new index for duplicates	False

Returns:

Type	Description
KeyedTable	The resulting table after the index is applied

Examples:

Adding indexes:

In [177]:

N = 10
tab = kx.Table(data={
    'sym': kx.random.random(N, ['AAPL', 'GOOG', 'MSFT']),
    'price': 2.5 - kx.random.random(N, 5.0),
    'traded': 10 - kx.random.random(N, 20),
    'hold': kx.random.random(N, False)
    })

N = 10 tab = kx.Table(data={ 'sym': kx.random.random(N, ['AAPL', 'GOOG', 'MSFT']), 'price': 2.5 - kx.random.random(N, 5.0), 'traded': 10 - kx.random.random(N, 20), 'hold': kx.random.random(N, False) })

In [178]:

#Setting a single index
tab.set_index('sym')

#Setting a single index tab.set_index('sym')

Out[178]:

	price	traded	hold
sym
GOOG	-0.04115503	0	0b
MSFT	2.057705	-7	1b
AAPL	0.5650117	9	0b
GOOG	-1.54609	-1	0b
GOOG	-0.9236442	7	1b
MSFT	-2.386905	-7	1b
GOOG	-1.61369	3	0b
GOOG	-1.561493	2	1b
MSFT	-1.895856	2	0b
MSFT	0.8344895	-7	0b

In [179]:

#Setting multiple indexes
tab.set_index(['sym', 'traded'])

#Setting multiple indexes tab.set_index(['sym', 'traded'])

Out[179]:

		price	hold
sym	traded
GOOG	0	-0.04115503	0b
MSFT	-7	2.057705	1b
AAPL	9	0.5650117	0b
GOOG	-1	-1.54609	0b
	7	-0.9236442	1b
MSFT	-7	-2.386905	1b
GOOG	3	-1.61369	0b
	2	-1.561493	1b
MSFT	2	-1.895856	0b
	-7	0.8344895	0b

In [180]:

#Pass a table as index (lengths must match)
status = kx.q('{select movement from ungroup select movement:`down`up 0<=deltas price by sym from x}',tab)
tab.set_index(status)

#Pass a table as index (lengths must match) status = kx.q('{select movement from ungroup select movement:`down`up 0<=deltas price by sym from x}',tab) tab.set_index(status)

Out[180]:

	sym	price	traded	hold
movement
up	GOOG	-0.04115503	0	0b
down	MSFT	2.057705	-7	1b
down	AAPL	0.5650117	9	0b
up	GOOG	-1.54609	-1	0b
down	GOOG	-0.9236442	7	1b
up	MSFT	-2.386905	-7	1b
up	GOOG	-1.61369	3	0b
down	GOOG	-1.561493	2	1b
up	MSFT	-1.895856	2	0b
up	MSFT	0.8344895	-7	0b

Appending:

In [181]:

#Default is false - previous index 'sym' deleted and replaced by 'hold'
tab.set_index('sym').set_index('hold')

#Default is false - previous index 'sym' deleted and replaced by 'hold' tab.set_index('sym').set_index('hold')

Out[181]:

	price	traded
hold
0b	-0.04115503	0
1b	2.057705	-7
0b	0.5650117	9
0b	-1.54609	-1
1b	-0.9236442	7
1b	-2.386905	-7
0b	-1.61369	3
1b	-1.561493	2
0b	-1.895856	2
0b	0.8344895	-7

In [182]:

#append= True will retain 'sym' index and add 'hold' as second index
tab.set_index('sym').set_index('hold', append= True)

#append= True will retain 'sym' index and add 'hold' as second index tab.set_index('sym').set_index('hold', append= True)

Out[182]:

		price	traded
sym	hold
GOOG	0b	-0.04115503	0
MSFT	1b	2.057705	-7
AAPL	0b	0.5650117	9
GOOG	0b	-1.54609	-1
	1b	-0.9236442	7
MSFT	1b	-2.386905	-7
GOOG	0b	-1.61369	3
	1b	-1.561493	2
MSFT	0b	-1.895856	2
	0b	0.8344895	-7

Verify Integrity:

In [183]:

#Will allow duplicates in index:
tab.set_index('sym')

#Will allow duplicates in index: tab.set_index('sym')

Out[183]:

	price	traded	hold
sym
GOOG	-0.04115503	0	0b
MSFT	2.057705	-7	1b
AAPL	0.5650117	9	0b
GOOG	-1.54609	-1	0b
GOOG	-0.9236442	7	1b
MSFT	-2.386905	-7	1b
GOOG	-1.61369	3	0b
GOOG	-1.561493	2	1b
MSFT	-1.895856	2	0b
MSFT	0.8344895	-7	0b

In [184]:

#Will error as 'sym' has duplicates
try:
    tab.set_index('sym', verify_integrity= True)
except kx.QError as e:
    print(f'Caught Error: {e}')

#Will error as 'sym' has duplicates try: tab.set_index('sym', verify_integrity= True) except kx.QError as e: print(f'Caught Error: {e}')

Caught Error: Index has duplicate key(s)