import os
os.environ['IGNORE_QHOME'] = '1' # Ignore symlinking PyKX q libraries to QHOME
os.environ['PYKX_Q_LOADED_MARKER'] = '' # Only used here for running Notebook under mkdocs-jupyter during document generation.
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.
kx.Table(data={'x': list(range(10)), 'y': [10 - x for x in range(10)]})
x | y | |
---|---|---|
0 | 0 | 10 |
1 | 1 | 9 |
2 | 2 | 8 |
3 | 3 | 7 |
4 | 4 | 6 |
5 | 5 | 5 |
6 | 6 | 4 |
7 | 7 | 3 |
8 | 8 | 2 |
Create a Table from an array like object.
kx.Table([[0, 1], [2, 3], [4, 5], [6, 7], [8, 9]])
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.
kx.Table([[0, 1, 2], [3, 4, 5], [6, 7, 8], [9, 10, 11]], columns=['x', 'y', 'z'])
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.
kx.KeyedTable(data={'x': list(range(10)), 'y': list(10 - x for x in range(10))})
x | y | |
---|---|---|
idx | ||
0 | 0 | 10 |
1 | 1 | 9 |
2 | 2 | 8 |
3 | 3 | 7 |
4 | 4 | 6 |
5 | 5 | 5 |
6 | 6 | 4 |
7 | 7 | 3 |
8 | 8 | 2 |
Create a keyed table from a list of rows.
kx.KeyedTable([[0, 1], [2, 3], [4, 5], [6, 7], [8, 9]])
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.
kx.KeyedTable([[0, 1, 2], [3, 4, 5], [6, 7, 8], [9, 10, 11]], columns=['x', 'y', 'z'])
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.
kx.KeyedTable(data={'x': list(range(10)), 'y': list(10 - x for x in range(10))}, index=[2 * x for x in range(10)])
x | y | |
---|---|---|
idx | ||
0 | 0 | 10 |
2 | 1 | 9 |
4 | 2 | 8 |
6 | 3 | 7 |
8 | 4 | 6 |
10 | 5 | 5 |
12 | 6 | 4 |
14 | 7 | 3 |
16 | 8 | 2 |
Metadata¶
kx.q('N: 1000')
tab = kx.q('([] x: til N; y: N?`AAPL`GOOG`MSFT; z: N?500f; w: N?1000; v: N?(0N 0 50 100 200 250))')
tab
x | y | z | w | v | |
---|---|---|---|---|---|
0 | 0 | AAPL | 280.0322 | 868 | 200 |
1 | 1 | MSFT | 48.24615 | 18 | 0N |
2 | 2 | AAPL | 66.99184 | 301 | 50 |
3 | 3 | MSFT | 435.7574 | 951 | 0 |
4 | 4 | AAPL | 385.586 | 365 | 50 |
5 | 5 | AAPL | 325.6626 | 386 | 0 |
6 | 6 | GOOG | 68.13659 | 844 | 50 |
7 | 7 | AAPL | 399.9134 | 972 | 100 |
... | ... | ... | ... | ... | ... |
999 | 999 | MSFT | 476.8311 | 988 | 0 |
1,000 rows × 5 columns
Table.columns¶
Get the name of each column in the table
tab.columns
pykx.SymbolVector(pykx.q('`x`y`z`w`v'))
Table.dtypes¶
Get the pandas dtype of each column
tab.dtypes
columns | type | |
---|---|---|
0 | x | "kx.LongAtom" |
1 | y | "kx.SymbolAtom" |
2 | z | "kx.FloatAtom" |
3 | w | "kx.LongAtom" |
4 | v | "kx.LongAtom" |
Table.empty¶
Returns True if the table is empty otherwise returns False.
tab.empty
pykx.BooleanAtom(pykx.q('0b'))
Table.ndim¶
Get the nuber of columns within the table.
tab.ndim
pykx.LongAtom(pykx.q('2'))
Table.shape¶
Get the shape of the table as a tuple (number of rows, number of columns).
tab.shape
(pykx.LongAtom(pykx.q('1000')), pykx.LongAtom(pykx.q('5')))
Table.size¶
Get the number of values in the table (rows * cols).
tab.size
pykx.LongAtom(pykx.q('5000'))
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
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
a | b | c | d | |
---|---|---|---|---|
0 | 1 | 1 | 7 | 7 |
1 | 2 | 2 | 8 | 11 |
2 | 2 | 6 | 9 | 14 |
3 | 4 | 7 | 10 | 14 |
tab.mean()
a | 2.25 |
---|---|
b | 4f |
c | 8.5 |
d | 11.5 |
Calculate the mean across the rows of a table
tab.mean(axis=1)
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
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
a | b | c | d | |
---|---|---|---|---|
0 | 1 | 1 | 7 | 7 |
1 | 2 | 2 | 8 | 11 |
2 | 2 | 6 | 9 | 14 |
3 | 4 | 7 | 10 | 14 |
tab.median()
a | 2f |
---|---|
b | 4f |
c | 8.5 |
d | 12.5 |
Calculate the median across the rows of a table
tab.median(axis=1)
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
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
a | b | c | d | |
---|---|---|---|---|
0 | 1 | 1 | 7 | 7 |
1 | 2 | 2 | 8 | 11 |
2 | 2 | 6 | 9 | 14 |
3 | 4 | 7 | 10 | 14 |
tab.mode()
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
tab.mode(axis=1)
idx | 0 | 1 | 2 | 3 | |
---|---|---|---|---|---|
0 | 0 | 1 | 7 | 0N | 0N |
1 | 1 | 2 | 0N | 0N | 0N |
2 | 2 | 2 | 6 | 9 | 14 |
3 | 3 | 4 | 7 | 10 | 14 |
Calculate the mode across columns and keep null values.
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
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 |
tab.mode(dropna=False)
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 |
Indexing¶
# The examples in this section will use this example table filled with random data
kx.q('N: 1000')
tab = kx.q('([] x: til N; y: N?`AAPL`GOOG`MSFT; z: N?500f; w: N?1000; v: N?(0N 0 50 100 200 250))')
tab
x | y | z | w | v | |
---|---|---|---|---|---|
0 | 0 | AAPL | 60.54614 | 184 | 0N |
1 | 1 | GOOG | 292.5522 | 299 | 100 |
2 | 2 | GOOG | 283.0627 | 411 | 250 |
3 | 3 | MSFT | 42.38194 | 713 | 0 |
4 | 4 | MSFT | 453.4634 | 69 | 100 |
5 | 5 | MSFT | 12.28187 | 127 | 50 |
6 | 6 | MSFT | 76.00103 | 898 | 200 |
7 | 7 | AAPL | 17.802 | 822 | 0 |
... | ... | ... | ... | ... | ... |
999 | 999 | MSFT | 409.2486 | 807 | 0N |
1,000 rows × 5 columns
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.
tab.head()
x | y | z | w | v | |
---|---|---|---|---|---|
0 | 0 | AAPL | 60.54614 | 184 | 0N |
1 | 1 | GOOG | 292.5522 | 299 | 100 |
2 | 2 | GOOG | 283.0627 | 411 | 250 |
3 | 3 | MSFT | 42.38194 | 713 | 0 |
4 | 4 | MSFT | 453.4634 | 69 | 100 |
Return the first 10 rows of the table.
tab.head(10)
x | y | z | w | v | |
---|---|---|---|---|---|
0 | 0 | AAPL | 60.54614 | 184 | 0N |
1 | 1 | GOOG | 292.5522 | 299 | 100 |
2 | 2 | GOOG | 283.0627 | 411 | 250 |
3 | 3 | MSFT | 42.38194 | 713 | 0 |
4 | 4 | MSFT | 453.4634 | 69 | 100 |
5 | 5 | MSFT | 12.28187 | 127 | 50 |
6 | 6 | MSFT | 76.00103 | 898 | 200 |
7 | 7 | AAPL | 17.802 | 822 | 0 |
8 | 8 | AAPL | 68.05945 | 197 | 0 |
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.
tab.tail()
x | y | z | w | v | |
---|---|---|---|---|---|
0 | 995 | AAPL | 54.79148 | 295 | 0N |
1 | 996 | AAPL | 448.0876 | 779 | 0N |
2 | 997 | GOOG | 173.26 | 896 | 0 |
3 | 998 | GOOG | 350.8906 | 200 | 50 |
4 | 999 | MSFT | 409.2486 | 807 | 0N |
Return the last 10 rows of the table.
tab.tail(10)
x | y | z | w | v | |
---|---|---|---|---|---|
0 | 990 | GOOG | 407.4229 | 546 | 100 |
1 | 991 | AAPL | 18.6764 | 228 | 50 |
2 | 992 | AAPL | 408.8369 | 665 | 200 |
3 | 993 | AAPL | 282.1039 | 166 | 0N |
4 | 994 | GOOG | 69.54563 | 273 | 200 |
5 | 995 | AAPL | 54.79148 | 295 | 0N |
6 | 996 | AAPL | 448.0876 | 779 | 0N |
7 | 997 | GOOG | 173.26 | 896 | 0 |
8 | 998 | GOOG | 350.8906 | 200 | 50 |
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 contatining only the columns requested or the default value. |
Examples:
Get the y
column from the table.
tab.get('y')
y | |
---|---|
0 | AAPL |
1 | GOOG |
2 | GOOG |
3 | MSFT |
4 | MSFT |
5 | MSFT |
6 | MSFT |
7 | AAPL |
... | ... |
999 | MSFT |
1,000 rows × 1 columns
Get the y
and z
columns from the table.
tab.get(['y', 'z'])
y | z | |
---|---|---|
0 | AAPL | 60.54614 |
1 | GOOG | 292.5522 |
2 | GOOG | 283.0627 |
3 | MSFT | 42.38194 |
4 | MSFT | 453.4634 |
5 | MSFT | 12.28187 |
6 | MSFT | 76.00103 |
7 | AAPL | 17.802 |
... | ... | ... |
999 | MSFT | 409.2486 |
1,000 rows × 2 columns
Attempt to get the q
column from the table and recieve none as that column does not exist.
print(tab.get('q'))
None
Attempt to get the q
column from the table and recieve the default value not found
as that column does not exist.
tab.get('q', 'not found')
'not found'
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.
tab.at[997, 'z']
pykx.FloatAtom(pykx.q('173.26'))
Reassign the value of the z
column in the 997th row to 3.14159
.
tab.at[997, 'z'] = 3.14159
tab.at[997, 'z']
pykx.FloatAtom(pykx.q('3.14159'))
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 contatining only the columns / rows requested. |
Examples:
Get every row in the y
column.
tab[:, 'y']
y | |
---|---|
0 | AAPL |
1 | GOOG |
2 | GOOG |
3 | MSFT |
4 | MSFT |
5 | MSFT |
6 | MSFT |
7 | AAPL |
... | ... |
999 | MSFT |
1,000 rows × 1 columns
Get all rows of the table where the value in the z
column is greater than 250.0
tab[tab['z'] > 250.0]
x | y | z | w | v | |
---|---|---|---|---|---|
0 | 1 | GOOG | 292.5522 | 299 | 100 |
1 | 2 | GOOG | 283.0627 | 411 | 250 |
2 | 4 | MSFT | 453.4634 | 69 | 100 |
3 | 9 | AAPL | 405.7366 | 56 | 0 |
4 | 15 | MSFT | 311.1167 | 705 | 100 |
5 | 22 | MSFT | 325.2313 | 425 | 100 |
6 | 23 | GOOG | 389.6867 | 143 | 200 |
7 | 26 | MSFT | 374.9333 | 225 | 0N |
... | ... | ... | ... | ... | ... |
496 | 999 | MSFT | 409.2486 | 807 | 0N |
497 rows × 5 columns
Replace all null values in the column v
with the value -100
.
tab.loc[tab['v'] == kx.q('0N'), 'v'] = -100
tab
x | y | z | w | v | |
---|---|---|---|---|---|
0 | 0 | AAPL | 60.54614 | 184 | -100 |
1 | 1 | GOOG | 292.5522 | 299 | 100 |
2 | 2 | GOOG | 283.0627 | 411 | 250 |
3 | 3 | MSFT | 42.38194 | 713 | 0 |
4 | 4 | MSFT | 453.4634 | 69 | 100 |
5 | 5 | MSFT | 12.28187 | 127 | 50 |
6 | 6 | MSFT | 76.00103 | 898 | 200 |
7 | 7 | AAPL | 17.802 | 822 | 0 |
... | ... | ... | ... | ... | ... |
999 | 999 | MSFT | 409.2486 | 807 | -100 |
1,000 rows × 5 columns
Replace all locations in column v
where the value is -100
with a null.
tab[tab['v'] == -100, 'v'] = kx.q('0N')
tab
x | y | z | w | v | |
---|---|---|---|---|---|
0 | 0 | AAPL | 60.54614 | 184 | 0N |
1 | 1 | GOOG | 292.5522 | 299 | 100 |
2 | 2 | GOOG | 283.0627 | 411 | 250 |
3 | 3 | MSFT | 42.38194 | 713 | 0 |
4 | 4 | MSFT | 453.4634 | 69 | 100 |
5 | 5 | MSFT | 12.28187 | 127 | 50 |
6 | 6 | MSFT | 76.00103 | 898 | 200 |
7 | 7 | AAPL | 17.802 | 822 | 0 |
... | ... | ... | ... | ... | ... |
999 | 999 | MSFT | 409.2486 | 807 | 0N |
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.
tab['new_col'] = kx.q('1000?1f')
tab[['new_col1', 'new_col2']] = [20, kx.q('1000?0Ng')]
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 contatining only the columns / rows requested. |
Examples:
Get the second row from a table.
tab.iloc[1]
x | y | z | w | v | new_col | new_col1 | new_col2 | |
---|---|---|---|---|---|---|---|---|
0 | 1 | GOOG | 292.5522 | 299 | 100 | 0.2627833 | 20 | f8695691-f901-256f-ebd0-29b8fa8b83d7 |
Get the first 5 rows from a table.
tab.iloc[:5]
x | y | z | w | v | new_col | new_col1 | new_col2 | |
---|---|---|---|---|---|---|---|---|
0 | 0 | AAPL | 60.54614 | 184 | 0N | 0.8227748 | 20 | 298f534d-d51c-5ef0-e267-2ca28b4aa3cb |
1 | 1 | GOOG | 292.5522 | 299 | 100 | 0.2627833 | 20 | f8695691-f901-256f-ebd0-29b8fa8b83d7 |
2 | 2 | GOOG | 283.0627 | 411 | 250 | 0.8676527 | 20 | 81539d7c-c284-f7e5-8f9f-9a88261be328 |
3 | 3 | MSFT | 42.38194 | 713 | 0 | 0.1259831 | 20 | b30b2ac2-65a1-4a5a-c610-e115348e38bc |
4 | 4 | MSFT | 453.4634 | 69 | 100 | 0.7169927 | 20 | dbac4fbf-7e0d-9351-dada-3691d4032a1d |
Get all rows of the table where the y
column is equal to AAPL
.
tab.iloc[tab['y'] == 'AAPL']
x | y | z | w | v | new_col | new_col1 | new_col2 | |
---|---|---|---|---|---|---|---|---|
0 | 0 | AAPL | 60.54614 | 184 | 0N | 0.8227748 | 20 | 298f534d-d51c-5ef0-e267-2ca28b4aa3cb |
1 | 7 | AAPL | 17.802 | 822 | 0 | 0.9142186 | 20 | 0328294d-c97d-b42a-cd4f-5bef9c206974 |
2 | 8 | AAPL | 68.05945 | 197 | 0 | 0.974406 | 20 | abee1656-6460-b1f0-cdb8-287989e82eb9 |
3 | 9 | AAPL | 405.7366 | 56 | 0 | 0.9522664 | 20 | 91533618-2e03-9cb6-54a9-a4b31fe34655 |
4 | 17 | AAPL | 70.85145 | 722 | 50 | 0.3274562 | 20 | 0b0e7d70-d397-3df0-cb27-7918231b26a1 |
5 | 18 | AAPL | 172.3351 | 410 | 100 | 0.8824775 | 20 | de176486-d430-ebb9-ce40-32bdc9a16222 |
6 | 19 | AAPL | 9.906452 | 757 | 200 | 0.4697741 | 20 | 9c96eca4-2fe5-3178-0da2-213902715642 |
7 | 20 | AAPL | 199.5309 | 790 | 0N | 0.5114003 | 20 | 220980b5-aa45-eb96-a243-c57cc281ef51 |
... | ... | ... | ... | ... | ... | ... | ... | ... |
341 | 996 | AAPL | 448.0876 | 779 | 0N | 0.889285 | 20 | ca774342-f9d4-8d0a-b6c4-36c95e7719ed |
342 rows × 8 columns
Get all rows of the table where the y
column is equal to AAPL
, and only return the y
, z
and w
columns.
tab.iloc[tab['y'] == 'AAPL', ['y', 'z', 'w']]
y | z | w | |
---|---|---|---|
0 | AAPL | 60.54614 | 184 |
1 | AAPL | 17.802 | 822 |
2 | AAPL | 68.05945 | 197 |
3 | AAPL | 405.7366 | 56 |
4 | AAPL | 70.85145 | 722 |
5 | AAPL | 172.3351 | 410 |
6 | AAPL | 9.906452 | 757 |
7 | AAPL | 199.5309 | 790 |
... | ... | ... | ... |
341 | AAPL | 448.0876 | 779 |
342 rows × 3 columns
Replace all null values in the column v
with the value -100
.
tab.iloc[tab['v'] == kx.q('0N'), 'v'] = -100
tab
x | y | z | w | v | new_col | new_col1 | new_col2 | |
---|---|---|---|---|---|---|---|---|
0 | 0 | AAPL | 60.54614 | 184 | -100 | 0.8227748 | 20 | 298f534d-d51c-5ef0-e267-2ca28b4aa3cb |
1 | 1 | GOOG | 292.5522 | 299 | 100 | 0.2627833 | 20 | f8695691-f901-256f-ebd0-29b8fa8b83d7 |
2 | 2 | GOOG | 283.0627 | 411 | 250 | 0.8676527 | 20 | 81539d7c-c284-f7e5-8f9f-9a88261be328 |
3 | 3 | MSFT | 42.38194 | 713 | 0 | 0.1259831 | 20 | b30b2ac2-65a1-4a5a-c610-e115348e38bc |
4 | 4 | MSFT | 453.4634 | 69 | 100 | 0.7169927 | 20 | dbac4fbf-7e0d-9351-dada-3691d4032a1d |
5 | 5 | MSFT | 12.28187 | 127 | 50 | 0.1039635 | 20 | 5c6624e8-cd25-3787-2dfa-82e8718698c9 |
6 | 6 | MSFT | 76.00103 | 898 | 200 | 0.6325627 | 20 | 062e80b2-cd2a-38a8-b47a-a9e3019c35cf |
7 | 7 | AAPL | 17.802 | 822 | 0 | 0.9142186 | 20 | 0328294d-c97d-b42a-cd4f-5bef9c206974 |
... | ... | ... | ... | ... | ... | ... | ... | ... |
999 | 999 | MSFT | 409.2486 | 807 | -100 | 0.9509369 | 20 | 298c22bd-1045-081a-7f1b-f997a864d073 |
1,000 rows × 8 columns
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 contatining only the columns removed from the input table. |
Examples:
Remove the v
column from the table and return it.
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 | new_col | new_col1 | new_col2 | |
---|---|---|---|---|---|---|---|---|
0 | 0 | AAPL | 60.54614 | 184 | -100 | 0.8227748 | 20 | 298f534d-d51c-5ef0-e267-2ca28b4aa3cb |
1 | 1 | GOOG | 292.5522 | 299 | 100 | 0.2627833 | 20 | f8695691-f901-256f-ebd0-29b8fa8b83d7 |
2 | 2 | GOOG | 283.0627 | 411 | 250 | 0.8676527 | 20 | 81539d7c-c284-f7e5-8f9f-9a88261be328 |
3 | 3 | MSFT | 42.38194 | 713 | 0 | 0.1259831 | 20 | b30b2ac2-65a1-4a5a-c610-e115348e38bc |
4 | 4 | MSFT | 453.4634 | 69 | 100 | 0.7169927 | 20 | dbac4fbf-7e0d-9351-dada-3691d4032a1d |
Pop the `v` column out of the table
v | |
---|---|
0 | -100 |
1 | 100 |
2 | 250 |
3 | 0 |
4 | 100 |
5 | 50 |
6 | 200 |
7 | 0 |
... | ... |
999 | -100 |
1,000 rows × 1 columns
Updated Table
x | y | z | w | new_col | new_col1 | new_col2 | |
---|---|---|---|---|---|---|---|
0 | 0 | AAPL | 60.54614 | 184 | 0.8227748 | 20 | 298f534d-d51c-5ef0-e267-2ca28b4aa3cb |
1 | 1 | GOOG | 292.5522 | 299 | 0.2627833 | 20 | f8695691-f901-256f-ebd0-29b8fa8b83d7 |
2 | 2 | GOOG | 283.0627 | 411 | 0.8676527 | 20 | 81539d7c-c284-f7e5-8f9f-9a88261be328 |
3 | 3 | MSFT | 42.38194 | 713 | 0.1259831 | 20 | b30b2ac2-65a1-4a5a-c610-e115348e38bc |
4 | 4 | MSFT | 453.4634 | 69 | 0.7169927 | 20 | dbac4fbf-7e0d-9351-dada-3691d4032a1d |
Remove the z
and w
columns from the table and return them.
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 | new_col | new_col1 | new_col2 | |
---|---|---|---|---|---|---|---|
0 | 0 | AAPL | 60.54614 | 184 | 0.8227748 | 20 | 298f534d-d51c-5ef0-e267-2ca28b4aa3cb |
1 | 1 | GOOG | 292.5522 | 299 | 0.2627833 | 20 | f8695691-f901-256f-ebd0-29b8fa8b83d7 |
2 | 2 | GOOG | 283.0627 | 411 | 0.8676527 | 20 | 81539d7c-c284-f7e5-8f9f-9a88261be328 |
3 | 3 | MSFT | 42.38194 | 713 | 0.1259831 | 20 | b30b2ac2-65a1-4a5a-c610-e115348e38bc |
4 | 4 | MSFT | 453.4634 | 69 | 0.7169927 | 20 | dbac4fbf-7e0d-9351-dada-3691d4032a1d |
Pop the `z` and `w` columns out of the table
z | w | |
---|---|---|
0 | 60.54614 | 184 |
1 | 292.5522 | 299 |
2 | 283.0627 | 411 |
3 | 42.38194 | 713 |
4 | 453.4634 | 69 |
Updated Table
x | y | new_col | new_col1 | new_col2 | |
---|---|---|---|---|---|
0 | 0 | AAPL | 0.8227748 | 20 | 298f534d-d51c-5ef0-e267-2ca28b4aa3cb |
1 | 1 | GOOG | 0.2627833 | 20 | f8695691-f901-256f-ebd0-29b8fa8b83d7 |
2 | 2 | GOOG | 0.8676527 | 20 | 81539d7c-c284-f7e5-8f9f-9a88261be328 |
3 | 3 | MSFT | 0.1259831 | 20 | b30b2ac2-65a1-4a5a-c610-e115348e38bc |
4 | 4 | MSFT | 0.7169927 | 20 | dbac4fbf-7e0d-9351-dada-3691d4032a1d |
Reindexing¶
# The examples in this section will use this example table filled with random data
kx.q('N: 1000')
tab = kx.q('([] x: til N; y: N?`AAPL`GOOG`MSFT; z: N?500f; w: N?1000; v: N?(0N 0 50 100 200 250))')
tab.head()
x | y | z | w | v | |
---|---|---|---|---|---|
0 | 0 | MSFT | 430.2492 | 949 | 250 |
1 | 1 | AAPL | 305.8041 | 558 | 250 |
2 | 2 | AAPL | 414.3448 | 674 | 250 |
3 | 3 | AAPL | 84.95412 | 710 | 0 |
4 | 4 | GOOG | 372.5827 | 348 | 0N |
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.
tab.drop([0, 2, 4, 6, 8, 10]).head()
x | y | z | w | v | |
---|---|---|---|---|---|
0 | 1 | AAPL | 305.8041 | 558 | 250 |
1 | 3 | AAPL | 84.95412 | 710 | 0 |
2 | 5 | GOOG | 12.72708 | 48 | 0 |
3 | 7 | AAPL | 2.78757 | 833 | 0 |
4 | 9 | MSFT | 167.0789 | 260 | 50 |
Drop columns from a table.
tab.drop('y', axis=1).head()
x | z | w | v | |
---|---|---|---|---|
0 | 0 | 430.2492 | 949 | 250 |
1 | 1 | 305.8041 | 558 | 250 |
2 | 2 | 414.3448 | 674 | 250 |
3 | 3 | 84.95412 | 710 | 0 |
4 | 4 | 372.5827 | 348 | 0N |
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
tab2 = kx.q('([] 100?`AAPL`GOOG`MSFT; 100?3)')
tab2
x | x1 | |
---|---|---|
0 | AAPL | 0 |
1 | AAPL | 1 |
2 | MSFT | 1 |
3 | AAPL | 0 |
4 | AAPL | 0 |
5 | GOOG | 2 |
6 | GOOG | 0 |
7 | MSFT | 2 |
... | ... | ... |
99 | AAPL | 0 |
100 rows × 2 columns
Drop all duplicate rows from the table.
tab2.drop_duplicates()
x | x1 | |
---|---|---|
0 | AAPL | 0 |
1 | AAPL | 1 |
2 | MSFT | 1 |
3 | GOOG | 2 |
4 | GOOG | 0 |
5 | MSFT | 2 |
6 | MSFT | 0 |
7 | AAPL | 2 |
8 | GOOG | 1 |
Table.rename()¶
Table.rename(columns)
Rename columns in a table and return the resulting Table object.
Parameters:
Name | Type | Description | Default |
---|---|---|---|
item | dict[str, str] | A dictonary of column name to new column name to use when renaming. | required |
Returns:
Type | Description |
---|---|
Table | A table with the given column(s) renamed. |
Examples:
The inital table we will be renaming columns on.
tab.head()
x | y | z | w | v | |
---|---|---|---|---|---|
0 | 0 | MSFT | 430.2492 | 949 | 250 |
1 | 1 | AAPL | 305.8041 | 558 | 250 |
2 | 2 | AAPL | 414.3448 | 674 | 250 |
3 | 3 | AAPL | 84.95412 | 710 | 0 |
4 | 4 | GOOG | 372.5827 | 348 | 0N |
Rename column y
to symbol
and z
to price
.
tab.rename(columns={'y': 'symbol', 'z': 'price'}).head()
x | symbol | price | w | v | |
---|---|---|---|---|---|
0 | 0 | MSFT | 430.2492 | 949 | 250 |
1 | 1 | AAPL | 305.8041 | 558 | 250 |
2 | 2 | AAPL | 414.3448 | 674 | 250 |
3 | 3 | AAPL | 84.95412 | 710 | 0 |
4 | 4 | GOOG | 372.5827 | 348 | 0N |
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. |
Examples:
Sample 10 Rows.
tab.sample(n=10)
x | y | z | w | v | |
---|---|---|---|---|---|
0 | 694 | MSFT | 176.7876 | 657 | 50 |
1 | 730 | AAPL | 139.1981 | 119 | 100 |
2 | 907 | AAPL | 475.5725 | 270 | 100 |
3 | 35 | MSFT | 364.8574 | 31 | 100 |
4 | 374 | AAPL | 290.6377 | 709 | 0 |
5 | 839 | GOOG | 70.42376 | 567 | 0 |
6 | 620 | GOOG | 457.2576 | 927 | 100 |
7 | 779 | GOOG | 384.5403 | 343 | 200 |
8 | 805 | AAPL | 474.8613 | 835 | 0 |
Sample 10% of the rows.
tab.sample(frac=0.1)
x | y | z | w | v | |
---|---|---|---|---|---|
0 | 51 | GOOG | 192.5514 | 768 | 100 |
1 | 959 | MSFT | 258.0994 | 8 | 250 |
2 | 273 | AAPL | 211.6923 | 890 | 50 |
3 | 996 | AAPL | 126.8514 | 647 | 250 |
4 | 893 | AAPL | 70.56788 | 471 | 250 |
5 | 747 | GOOG | 139.1643 | 843 | 200 |
6 | 750 | AAPL | 65.00057 | 842 | 100 |
7 | 821 | MSFT | 211.0226 | 785 | 0N |
... | ... | ... | ... | ... | ... |
99 | 294 | GOOG | 82.38811 | 148 | 250 |
100 rows × 5 columns
Sample 10% of the rows and allow the same row to be sampled twice.
tab.sample(frac=0.1, replace=True)
x | y | z | w | v | |
---|---|---|---|---|---|
0 | 847 | MSFT | 217.0418 | 762 | 250 |
1 | 113 | MSFT | 153.6541 | 927 | 0N |
2 | 568 | AAPL | 474.214 | 925 | 200 |
3 | 985 | MSFT | 276.398 | 536 | 50 |
4 | 562 | GOOG | 122.4301 | 218 | 0 |
5 | 824 | GOOG | 472.5462 | 639 | 250 |
6 | 459 | MSFT | 176.35 | 846 | 0 |
7 | 818 | GOOG | 217.7887 | 183 | 0N |
... | ... | ... | ... | ... | ... |
99 | 137 | GOOG | 467.0504 | 169 | 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.
Parameters:
Name | Type | Description | Default |
---|---|---|---|
include | Union[List, str] | A selection of dtypes or strings to be included. | None |
exclude | Union[List, str] | A selection of dtypes or strings to be excluded. | None |
At least one of these parameters must be supplied.
Returns:
Type | Description |
---|---|
Dataframe | The subset of the frame including the dtypes in include and excluding the dtypes in exclude . |
Examples:
The examples in the section will use the example table.
df = kx.q('([] c1:`a`b`c; c2:1 2 3h; c3:1 2 3j; c4:1 2 3i)')
Exclude columns contatining symbols
df.select_dtypes(exclude = kx.SymbolVector)
c2 | c3 | c4 | |
---|---|---|---|
0 | 1h | 1 | 1i |
1 | 2h | 2 | 2i |
2 | 3h | 3 | 3i |
Include a list of column types
df.select_dtypes(include = [kx.ShortVector, kx.LongVector])
c2 | c3 | |
---|---|---|
0 | 1h | 1 |
1 | 2h | 2 |
2 | 3h | 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.
df = kx.q('([] c1:1 2 3i; c2:1 2 3j; c3:1 2 3h; c4:1 2 3i)')
Cast all columns to dtype LongVector
df.astype(kx.LongVector)
c1 | c2 | c3 | c4 | |
---|---|---|---|---|
0 | 1 | 1 | 1 | 1 |
1 | 2 | 2 | 2 | 2 |
2 | 3 | 3 | 3 | 3 |
Casting as specified in the dcitionary supplied with given dtype per column
df.astype({'c1':kx.LongVector, 'c2':'kx.ShortVector'})
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
df = kx.q('([] c1:3#.z.p; c2:`abc`def`ghi; c3:1 2 3j; c4:("abc";"def";"ghi");c5:"abc";c6:(1 2 3;4 5 6;7 8 9))')
Casting char and string columns to symbol columns
df.astype({'c4':kx.SymbolVector, 'c5':kx.SymbolVector})
c1 | c2 | c3 | c4 | c5 | c6 | |
---|---|---|---|---|---|---|
0 | 2024.01.11D11:40:56.808930870 | abc | 1 | abc | a | 1 2 3 |
1 | 2024.01.11D11:40:56.808930870 | def | 2 | def | b | 4 5 6 |
2 | 2024.01.11D11:40:56.808930870 | ghi | 3 | ghi | c | 7 8 9 |
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 indexes. If joining columns on columns, the DataFrame indexes will be ignored. Otherwise if joining indexes on indexes or indexes 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.
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')
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 on the lkey and rkey columns using a native q inner join. The value columns have the default suffixes, _x and _y, appended.
tab1.merge(tab2, left_on='lkey', right_on='rkey', q_join=True)
lkey | value_x | rkey | value_y | |
---|---|---|---|---|
0 | foo | 1 | foo | 5 |
1 | bar | 2 | bar | 6 |
2 | baz | 3 | baz | 7 |
3 | foo | 5 | foo | 5 |
Merge tab1 and tab2 with specified left and right suffixes appended to any overlapping columns.
tab1.merge(tab2, left_on='lkey', right_on='rkey', suffixes=('_left', '_right'))
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.
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']
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.
tab1.merge(tab2, how='inner', on='a')
a | b | c | |
---|---|---|---|
0 | foo | 1 | 3 |
Merge tab1 and tab2 on the a
column using a left join.
tab1.merge(tab2, how='left', on='a')
a | b | c | |
---|---|---|---|
0 | foo | 1 | 3 |
1 | bar | 2 | 0N |
Merge tab1 and tab2 using a cross join.
tab1 = kx.Table(data={'left': ['foo', 'bar']})
tab2 = kx.Table(data={'right': [7, 8]})
tab1.merge(tab2, how='cross')
left | right | |
---|---|---|
0 | foo | 7 |
1 | foo | 8 |
2 | bar | 7 |
3 | bar | 8 |
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 indexes. If joining columns on columns, the DataFrame indexes will be ignored. Otherwise if joining indexes on indexes or indexes 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.
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
a | left_val | |
---|---|---|
0 | 1 | a |
1 | 5 | b |
2 | 10 | c |
right
a | right_val | |
---|---|---|
0 | 1 | 1 |
1 | 2 | 2 |
2 | 3 | 3 |
3 | 6 | 6 |
4 | 7 | 7 |
left.merge_asof(right)
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.
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
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 |
quotes
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 |
trades.merge_asof(quotes, on="time")
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 |
Computations¶
# All the examples in this section will use this example table.
kx.q('N: 100')
tab = kx.q('([] sym: N?`AAPL`GOOG`MSFT; price: 250f - N?500f; traded: 100 - N?200; hold: N?0b)')
tab
sym | price | traded | hold | |
---|---|---|---|---|
0 | AAPL | -199.1755 | -91 | 1b |
1 | GOOG | 213.3049 | -13 | 1b |
2 | MSFT | 112.9427 | 6 | 1b |
3 | AAPL | -36.42095 | -37 | 0b |
4 | MSFT | 192.8483 | 44 | 0b |
5 | MSFT | 25.78804 | 45 | 0b |
6 | AAPL | 82.48745 | 38 | 0b |
7 | AAPL | -90.13045 | -72 | 0b |
... | ... | ... | ... | ... |
99 | GOOG | 119.8828 | -69 | 1b |
100 rows × 4 columns
Table.abs()¶
Table.abs(numeric_only=False)
Take the absolute value of each element in the table. 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. |
tab.abs(numeric_only=True)
price | traded | |
---|---|---|
0 | 199.1755 | 91 |
1 | 213.3049 | 13 |
2 | 112.9427 | 6 |
3 | 36.42095 | 37 |
4 | 192.8483 | 44 |
5 | 25.78804 | 45 |
6 | 82.48745 | 38 |
7 | 90.13045 | 72 |
... | ... | ... |
99 | 119.8828 | 69 |
100 rows × 2 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 represent the column name / row number and the values are the result of calling all on that column / row. |
tab.all()
sym | 1b |
---|---|
price | 1b |
traded | 1b |
hold | 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 represent the column name / row number and the values are the result of calling any on that column / row. |
tab.any()
sym | 1b |
---|---|
price | 1b |
traded | 1b |
hold | 1b |
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 represent the column name / row number and the values are the result of calling max on that column / row. |
tab.max()
sym | `MSFT |
---|---|
price | 246.298 |
traded | 100 |
hold | 1b |
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 represent the column name / row number and the values are the result of calling min on that column / row. |
tab.min()
sym | `AAPL |
---|---|
price | -240.7506 |
traded | -97 |
hold | 0b |
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 represent the column name / row number and the values are the result of calling sum on that column / row. |
tab.sum()
sym | `AAPLGOOGMSFTAAPLMSFTMSFTAAPLAAPLGOOGGOOGGOOGAAPLMSFTMSFTMSFTGOOGGOOGGOOGMSFT.. |
---|---|
price | 1871.6 |
traded | 597 |
hold | 50i |
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 represent the column name / row number and the values are the result of calling prd on that column / row. |
# This example will use a smaller version of the above table
# as the result of calculating the product quickly goes over the integer limits.
kx.q('N: 10')
tab = kx.q('([] sym: N?`AAPL`GOOG`MSFT; price: 2.5f - N?5f; traded: 10 - N?20; hold: N?0b)')
tab[tab['traded'] == 0, 'traded'] = 1
tab[tab['price'] == 0, 'price'] = 1.0
tab
sym | price | traded | hold | |
---|---|---|---|---|
0 | AAPL | -2.043057 | 8 | 0b |
1 | MSFT | 0.8623372 | 10 | 1b |
2 | GOOG | -2.328425 | -4 | 1b |
3 | MSFT | -0.09490959 | 1 | 0b |
4 | AAPL | 1.381233 | -9 | 1b |
5 | MSFT | -1.114598 | -5 | 0b |
6 | GOOG | -0.4742994 | -4 | 1b |
7 | AAPL | 0.66548 | 1 | 1b |
8 | MSFT | 1.638484 | -3 | 1b |
tab.prod(numeric_only=True)
price | -0.6994716 |
---|---|
traded | 1209600 |
hold | 0i |
Setting Indexes¶
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:
kx.q('N: 10')
tab = kx.q('([] sym: N?`AAPL`GOOG`MSFT; price: 2.5f - N?5f; traded: N?0 1; hold: N?01b)')
#Setting a single index
tab.set_index('sym')
price | traded | hold | |
---|---|---|---|
sym | |||
AAPL | 2.221871 | 1 | 0b |
AAPL | -0.5340566 | 0 | 1b |
GOOG | -0.9124374 | 1 | 1b |
AAPL | -2.200924 | 1 | 1b |
GOOG | -0.2018351 | 1 | 1b |
AAPL | 2.096948 | 0 | 0b |
AAPL | -1.962582 | 1 | 1b |
AAPL | -2.228272 | 0 | 1b |
MSFT | 1.64242 | 0 | 1b |
#Setting multipe indexes
tab.set_index(['sym', 'traded'])
price | hold | ||
---|---|---|---|
sym | traded | ||
AAPL | 1 | 2.221871 | 0b |
0 | -0.5340566 | 1b | |
GOOG | 1 | -0.9124374 | 1b |
AAPL | 1 | -2.200924 | 1b |
GOOG | 1 | -0.2018351 | 1b |
AAPL | 0 | 2.096948 | 0b |
1 | -1.962582 | 1b | |
0 | -2.228272 | 1b | |
MSFT | 0 | 1.64242 | 1b |
#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)
sym | price | traded | hold | |
---|---|---|---|---|
movement | ||||
up | AAPL | 2.221871 | 1 | 0b |
down | AAPL | -0.5340566 | 0 | 1b |
down | GOOG | -0.9124374 | 1 | 1b |
up | AAPL | -2.200924 | 1 | 1b |
down | GOOG | -0.2018351 | 1 | 1b |
down | AAPL | 2.096948 | 0 | 0b |
down | AAPL | -1.962582 | 1 | 1b |
up | AAPL | -2.228272 | 0 | 1b |
up | MSFT | 1.64242 | 0 | 1b |
Appending:
#Default is false - previous index 'sym' deleted and replaced by 'hold'
tab.set_index('sym').set_index('hold')
price | traded | |
---|---|---|
hold | ||
0b | 2.221871 | 1 |
1b | -0.5340566 | 0 |
1b | -0.9124374 | 1 |
1b | -2.200924 | 1 |
1b | -0.2018351 | 1 |
0b | 2.096948 | 0 |
1b | -1.962582 | 1 |
1b | -2.228272 | 0 |
1b | 1.64242 | 0 |
#append= True will retain 'sym' index and add 'hold' as second index
tab.set_index('sym').set_index('hold', append= True)
price | traded | ||
---|---|---|---|
sym | hold | ||
AAPL | 0b | 2.221871 | 1 |
1b | -0.5340566 | 0 | |
GOOG | 1b | -0.9124374 | 1 |
AAPL | 1b | -2.200924 | 1 |
GOOG | 1b | -0.2018351 | 1 |
AAPL | 0b | 2.096948 | 0 |
1b | -1.962582 | 1 | |
1b | -2.228272 | 0 | |
MSFT | 1b | 1.64242 | 0 |
Verify Integrity:
#Will allow duplicates in index:
tab.set_index('sym')
price | traded | hold | |
---|---|---|---|
sym | |||
AAPL | 2.221871 | 1 | 0b |
AAPL | -0.5340566 | 0 | 1b |
GOOG | -0.9124374 | 1 | 1b |
AAPL | -2.200924 | 1 | 1b |
GOOG | -0.2018351 | 1 | 1b |
AAPL | 2.096948 | 0 | 0b |
AAPL | -1.962582 | 1 | 1b |
AAPL | -2.228272 | 0 | 1b |
MSFT | 1.64242 | 0 | 1b |
#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)
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.
tab = kx.Table(data={
'Animal': ['Falcon', 'Falcon', 'Parrot', 'Parrot'],
'Max Speed': [380., 370., 24., 26.],
'Max Altitude': [570., 555., 275., 300.]
})
tab
Animal | Max 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 Max Speed
and Max Altitude
columns.
tab.groupby(kx.SymbolVector(['Animal'])).mean()
Max Speed | Max Altitude | |
---|---|---|
Animal | ||
Falcon | 375f | 562.5 |
Parrot | 25f | 287.5 |
Example table with multiple columns to group on.
tab = kx.q('2!', kx.Table(
data={
'Animal': ['Falcon', 'Falcon', 'Parrot', 'Parrot', 'Parrot'],
'Type': ['Captive', 'Wild', 'Captive', 'Wild', 'Wild'],
'Max Speed': [390., 350., 30., 20., 25.]
}
))
tab
Max Speed | ||
---|---|---|
Animal | Type | |
Falcon | Captive | 390f |
Wild | 350f | |
Parrot | Captive | 30f |
Wild | 20f | |
Wild | 25f |
Group on multiple columns using thier indexes.
tab.groupby(level=[0, 1]).mean()
Max Speed | ||
---|---|---|
Animal | Type | |
Falcon | Captive | 390f |
Wild | 350f | |
Parrot | Captive | 30f |
Wild | 22.5 |
Example table with Nulls.
tab = kx.Table(
[
["a", 12, 12],
[kx.q('`'), 12.3, 33.],
["b", 12.3, 123],
["a", 1, 1]
],
columns=["a", "b", "c"]
)
tab
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.
tab.groupby('a', dropna=False).sum()
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.
tab.groupby('a', dropna=False, as_index=False).sum()
a | b | c | |
---|---|---|---|
idx | |||
0 | 12.3 | 33f | |
1 | a | 13 | 13 |
2 | b | 12.3 | 123 |
Apply¶
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.
tab = kx.Table([[4, 9]] * 3, columns=['A', 'B'])
tab
A | B | |
---|---|---|
0 | 4 | 9 |
1 | 4 | 9 |
2 | 4 | 9 |
Apply square root on each item within a column
tab.apply(kx.q.sqrt)
A | B | |
---|---|---|
0 | 2f | 3f |
1 | 2f | 3f |
2 | 2f | 3f |
Apply a reducing function sum on either axis
tab.apply(kx.q.sum)
A | 12 |
---|---|
B | 27 |
tab.apply(lambda x: sum(x), axis=1)
pykx.LongVector(pykx.q('13 13 13'))
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:
|
|
*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.