Merging and joining¶

Join two tables together. These methods are available on pykx.Table and pykx.KeyedTable objects.

merge ¶

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,
)

Join two tables with a database style join.

A cross join cannot be given any columns or indexes to join on.

Parameters:

Name	Type	Description	Default
`right`	`Table or KeyedTable`	The `Table` or `KeyedTable` to join with.	required
`how`	`str`	The type of join, one of `'inner'`, `'left'`, `'right'`, `'outer'` or `'cross'`.	`'inner'`
`on`	`str`	The name of the column to join on.	`None`
`left_on`	`str`	The name of the column in the left table to join on.	`None`
`right_on`	`str`	The name of the column in the right table to join on.	`None`
`left_index`	`bool`	Join on the index of the left table.	`False`
`right_index`	`bool`	Join on the index of the right table.	`False`
`sort`	`bool`	Sort the result on the join keys.	`False`
`suffixes`	`tuple`	The suffixes added to overlapping column names from the left and right tables.	`('_x', '_y')`
`copy`	`bool`	Not yet implemented.	`True`
`validate`	`str`	If given, check the join is of this type: `one_to_one` or `1:1`, `one_to_many` or `1:m`, `many_to_one` or `m:1`, or `many_to_many` or `m:m`.	`None`
`q_join`	`bool`	Use the native q joins rather than the pandas style joins.	`False`

Returns:

Type	Description
`Table or KeyedTable`	The joined table or keyed table.

Examples:

>>> left = kx.q('([] k:`a`b`c; v:1 2 3)')
>>> right = kx.q('([] k:`a`b`d; w:4 5 6)')
>>> left.merge(right, on='k')
pykx.Table(pykx.q('
k v w
-----
a 1 4
b 2 5
'))
>>> left.merge(right, on='k', how='outer')
pykx.Table(pykx.q('
k v w
-----
a 1 4
b 2 5
c 3
d   6
'))

merge_asof ¶

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",
)

Join two tables on the nearest earlier value of a key, as the q function aj does.

Parameters:

Name	Type	Description	Default
`right`	`Table or KeyedTable`	The `Table` or `KeyedTable` to join with.	required
`on`	`str`	The name of the column to join on, which must be sorted.	`None`
`left_on`	`str`	The name of the column in the left table to join on.	`None`
`right_on`	`str`	The name of the column in the right table to join on.	`None`
`left_index`	`bool`	Join on the index of the left table.	`False`
`right_index`	`bool`	Join on the index of the right table.	`False`
`by`	`str or list`	The columns to match exactly before the nearest value is found.	`None`
`left_by`	`str or list`	The names of the columns in the left table to match on.	`None`
`right_by`	`str or list`	The names of the columns in the right table to match on.	`None`
`suffixes`	`tuple`	The suffixes added to overlapping column names from the left and right tables.	`('_x', '_y')`
`tolerance`	`None`	Not yet implemented.	`None`
`allow_exact_matches`	`bool`	Not yet implemented.	`True`
`direction`	`str`	Only `'backward'` is implemented.	`'backward'`

Returns:

Type	Description
`Table or KeyedTable`	The joined table or keyed table.

Examples:

>>> trades = kx.q('([] time:10:00:01 10:00:03 10:00:05; sym:`a`a`a; px:1 2 3f)')
>>> quotes = kx.q('([] time:10:00:00 10:00:02 10:00:04; sym:`a`a`a; bid:9 8 7f)')
>>> trades.merge_asof(quotes, on='time', by='sym')
pykx.Table(pykx.q('
time     sym px bid
-------------------
10:00:01 a   1  9
10:00:03 a   2  8
10:00:05 a   3  7
'))