# otp.Source.join_with_collection #### Source.join_with_collection(collection_name, query_func=None, how='outer', params=None, start=None, end=None, prefix=None, caching=None, keep_time=None, default_fields_for_outer_join=None) For each tick uses `query_func` to join ticks from `collection_name` tick collection (tick set, unordered tick set, tick list, or tick deque). * **Parameters:** * **collection_name** ([*str*](https://docs.pip.distribution.sol.onetick.com/api/operation/str/root.html.md#onetick.py.Operation.str)) -- Name of the collection state variable from which to join ticks. Collections are the following types: [`TickSet`](https://docs.pip.distribution.sol.onetick.com/api/state/tick_set.html.md#onetick.py.core._internal._state_objects.TickSet), [`TickSetUnordered`](https://docs.pip.distribution.sol.onetick.com/api/state/tick_set_unordered.html.md#onetick.py.core._internal._state_objects.TickSetUnordered), [`TickList`](https://docs.pip.distribution.sol.onetick.com/api/state/tick_list.html.md#onetick.py.core._internal._state_objects.TickList) and [`TickDeque`](https://docs.pip.distribution.sol.onetick.com/api/state/tick_deque.html.md#onetick.py.core._internal._state_objects.TickDeque). * **query_func** (*callable*) -- Callable `query_func` should return [`Source`](https://docs.pip.distribution.sol.onetick.com/api/source/root.html.md#onetick.py.Source). If passed, this query will be used on ticks from collection before joining them. In this case, `query_func` object will be evaluated by OneTick (not python) for every input tick. Note that python code will be executed only once, so all python's conditional expressions will be evaluated only once too. Callable should have `source` parameter. When callable is called, this parameter will have value of a [`Source`](https://docs.pip.distribution.sol.onetick.com/api/source/root.html.md#onetick.py.Source) object representing ticks loaded directly from the collection. Any operation applied to this source will be applied to ticks from the collection before joining them. Also, callable should have the parameters with names from `params` if they are specified in this method. If `query_func` is not passed, then all ticks from the collection will be joined. * **how** ( *'inner'* *,* *'outer'*) -- Type of join. If **inner**, then output tick is propagated only if some ticks from the collection were joined to the input tick. * **params** ([*dict*](https://docs.python.org/3/builtins/stdtypes.html#dict)) -- Mapping of the parameters' names and their values for the `query_func`. [`Columns`](https://docs.pip.distribution.sol.onetick.com/api/operation/root.html.md#onetick.py.Column) can be used as a value. * **start** ([`otp.datetime`](https://docs.pip.distribution.sol.onetick.com/api/datetime/dt.html.md#onetick.py.datetime), [`otp.Operation`](https://docs.pip.distribution.sol.onetick.com/api/operation/root.html.md#onetick.py.Operation)) -- Start time to select ticks from collection. If specified, only ticks in collection that have higher or equal timestamp will be processed. If not passed, then there will be no lower time bound for the collection ticks. This means that even ticks with TIMESTAMP lower than \_START_TIME of the main query will be joined. * **end** ([`otp.datetime`](https://docs.pip.distribution.sol.onetick.com/api/datetime/dt.html.md#onetick.py.datetime), [`otp.Operation`](https://docs.pip.distribution.sol.onetick.com/api/operation/root.html.md#onetick.py.Operation)) -- End time to select ticks from collection. If specified, only ticks in collection that have lower timestamp will be processed. If not passed, then there will be no upper time bound for the collection ticks. This means that even ticks with TIMESTAMP higher than \_END_TIME of the main query will be joined. * **prefix** ([*str*](https://docs.pip.distribution.sol.onetick.com/api/operation/str/root.html.md#onetick.py.Operation.str)) -- Prefix for the names of joined tick fields. * **caching** ([*str*](https://docs.pip.distribution.sol.onetick.com/api/operation/str/root.html.md#onetick.py.Operation.str)) -- If None caching is disabled. You can specify caching by using values: > * 'per_symbol': cache is different for each symbol. * **keep_time** ([*str*](https://docs.pip.distribution.sol.onetick.com/api/operation/str/root.html.md#onetick.py.Operation.str)) -- Name for the joined timestamp column. None means no timestamp column will be joined. * **default_fields_for_outer_join** ([*dict*](https://docs.python.org/3/builtins/stdtypes.html#dict)) -- When you use outer join, all output ticks will have fields from the schema of the joined source. If nothing was joined to a particular output tick, these fields will have default values for their type. This parameter allows to override the values that would be added to ticks for which nothing was joined. Dictionary keys should be field names, and dictionary values should be constants or [`Operation`](https://docs.pip.distribution.sol.onetick.com/api/operation/root.html.md#onetick.py.Operation) expressions * **self** ([*Source*](https://docs.pip.distribution.sol.onetick.com/api/source/root.html.md#onetick.py.Source)) * **Returns:** Source with joined ticks from `collection_name` * **Return type:** [`Source`](https://docs.pip.distribution.sol.onetick.com/api/source/root.html.md#onetick.py.Source) ### Examples ```pycon >>> data = otp.Tick(A=1) >>> data.state_vars['TICK_SET'] = otp.state.tick_set('LATEST_TICK', 'B', otp.Tick(B=1, C='STR')) >>> data = data.join_with_collection('TICK_SET') >>> otp.run(data) Time B C A 0 2003-12-01 1 STR 1 ``` ```pycon >>> data = otp.Ticks(A=[1, 2, 3, 4, 5], ... B=[2, 2, 3, 3, 3]) >>> data.state_vars['TICK_LIST'] = otp.state.tick_list() >>> def fun(tick): ... tick.state_vars['TICK_LIST'].push_back(tick) >>> data = data.script(fun) >>> def join_fun(source, param_b): ... source = source.agg({'VALUE': otp.agg.sum(source['A'])}) ... source['VALUE'] = source['VALUE'] + param_b ... return source >>> data = data.join_with_collection('TICK_LIST', join_fun, params={'param_b': data['B']}) >>> otp.run(data) Time VALUE A B 0 2003-12-01 00:00:00.000 3 1 2 1 2003-12-01 00:00:00.001 5 2 2 2 2003-12-01 00:00:00.002 9 3 3 3 2003-12-01 00:00:00.003 13 4 3 4 2003-12-01 00:00:00.004 18 5 3 ``` Join last standing quote from each exchange to trades: ```pycon >>> trd = otp.Ticks(offset=[1000, 2000, 3000, 4000, 5000], ... PRICE=[10.1, 10.2, 10.15, 10.23, 10.4], ... SIZE=[100, 50, 100, 60, 200]) >>> qte = otp.Ticks(offset=[500, 600, 1200, 2500, 3500, 3600, 4800], ... EXCHANGE=['N', 'C', 'Q', 'Q', 'C', 'N', 'C'], ... ASK_PRICE=[10.2, 10.18, 10.18, 10.15, 10.31, 10.32, 10.44], ... BID_PRICE=[10.1, 10.17, 10.17, 10.1, 10.23, 10.31, 10.4]) >>> trd['TICK_TYPE'] = 'TRD' >>> qte['TICK_TYPE'] = 'QTE' >>> data = otp.merge([trd, qte]) >>> data.state_vars['LAST_QUOTE_PER_EXCHANGE'] = otp.state.tick_set( ... 'LATEST', 'EXCHANGE', ... schema=['EXCHANGE', 'ASK_PRICE', 'BID_PRICE'] ... ) >>> data = data.state_vars['LAST_QUOTE_PER_EXCHANGE'].update(where=data['TICK_TYPE'] == 'QTE', ... value_fields=['ASK_PRICE', 'BID_PRICE']) >>> data = data.where(data['TICK_TYPE'] == 'TRD') >>> data = data.drop(['ASK_PRICE', 'BID_PRICE', 'EXCHANGE']) >>> data = data.join_with_collection('LAST_QUOTE_PER_EXCHANGE') >>> otp.run(data) Time EXCHANGE ASK_PRICE BID_PRICE PRICE SIZE TICK_TYPE 0 2003-12-01 00:00:01 N 10.20 10.10 10.10 100 TRD 1 2003-12-01 00:00:01 C 10.18 10.17 10.10 100 TRD 2 2003-12-01 00:00:02 N 10.20 10.10 10.20 50 TRD 3 2003-12-01 00:00:02 C 10.18 10.17 10.20 50 TRD 4 2003-12-01 00:00:02 Q 10.18 10.17 10.20 50 TRD 5 2003-12-01 00:00:03 N 10.20 10.10 10.15 100 TRD 6 2003-12-01 00:00:03 C 10.18 10.17 10.15 100 TRD 7 2003-12-01 00:00:03 Q 10.15 10.10 10.15 100 TRD 8 2003-12-01 00:00:04 N 10.32 10.31 10.23 60 TRD 9 2003-12-01 00:00:04 C 10.31 10.23 10.23 60 TRD 10 2003-12-01 00:00:04 Q 10.15 10.10 10.23 60 TRD 11 2003-12-01 00:00:05 N 10.32 10.31 10.40 200 TRD 12 2003-12-01 00:00:05 C 10.44 10.40 10.40 200 TRD 13 2003-12-01 00:00:05 Q 10.15 10.10 10.40 200 TRD ``` #### SEE ALSO **JOIN_WITH_COLLECTION_SUMMARY** OneTick event processor