# Joins
This section contains examples for Joins using the `onetick.py`.
\\\\
Each example is a self-contained script that can be run against the OneTick Cloud sample databases.
```default
# onetick-py WebAPI configuration for OneTick Cloud
import os
os.environ['OTP_WEBAPI'] = '1'
os.environ['OTP_HTTP_ADDRESS'] = 'https://rest.cloud.onetick.com'
os.environ['OTP_ACCESS_TOKEN_URL'] = 'https://cloud-auth.parent.onetick.com/realms/OMD/protocol/openid-connect/token'
os.environ['OTP_CLIENT_ID'] = '__FILL_IN__'
os.environ['OTP_CLIENT_SECRET'] = '__FILL_IN__'
```
## Calculates Period Statistics and Join them to Trading Activity
Period statistics calculate the Volume Weighted Average Price (VWAP), High, Low and total Volume across the
whole period as a single tick (`bucket_interval=0`),
which is then joined to every trade with [`otp.join(on='all')`](https://docs.pip.distribution.sol.onetick.com/api/functions/join.html.md#onetick.py.join).
```ipython3
import onetick.py as otp
trd = otp.DataSource(db='US_COMP_SAMPLE', tick_type='TRD')
trd = trd[['PRICE', 'SIZE', 'EXCHANGE', 'COND']]
# Compute Aggregates across the whole Period as a single tick.
stats_src = otp.DataSource(db='US_COMP_SAMPLE', tick_type='TRD')
stats = stats_src.agg({
'VWAP_PRICE': otp.agg.vwap('PRICE', 'SIZE'),
'FIRST_PRICE': otp.agg.first('PRICE'),
'HIGH_PRICE': otp.agg.max('PRICE'),
'LOW_PRICE': otp.agg.min('PRICE'),
'LAST_PRICE': otp.agg.last('PRICE'),
'VOLUME': otp.agg.sum('SIZE'),
})
# Join every trade to the single period-statistics record.
data = otp.join(trd, stats, on='all')
data = data.limit(1000)
result = otp.run(data,
start=otp.dt(2024, 1, 3, 9, 30),
end=otp.dt(2024, 1, 3, 16, 0),
timezone='America/New_York',
symbols='CSCO')
result
```
```myst-ansi
Time PRICE SIZE EXCHANGE COND VWAP_PRICE \
0 2024-01-03 09:30:00.065443591 50.02 2 Z @ I 50.402342
1 2024-01-03 09:30:00.111130049 50.16 3 Z @ I 50.402342
2 2024-01-03 09:30:00.127459523 50.17 100 V @ 50.402342
3 2024-01-03 09:30:00.128498068 50.17 5 Z @F I 50.402342
4 2024-01-03 09:30:00.135190071 50.13 46 Q @FTI 50.402342
.. ... ... ... ... ... ...
995 2024-01-03 09:30:07.378682043 50.12 2 Q @ I 50.402342
996 2024-01-03 09:30:07.378683771 50.12 50 Q @ I 50.402342
997 2024-01-03 09:30:07.379088757 50.12 100 Z @F 50.402342
998 2024-01-03 09:30:07.390014965 50.12 100 P @ 50.402342
999 2024-01-03 09:30:07.396341167 50.09 1 D @4 I 50.402342
FIRST_PRICE HIGH_PRICE LOW_PRICE LAST_PRICE VOLUME
0 50.02 50.805 49.94 50.52 15008890
1 50.02 50.805 49.94 50.52 15008890
2 50.02 50.805 49.94 50.52 15008890
3 50.02 50.805 49.94 50.52 15008890
4 50.02 50.805 49.94 50.52 15008890
.. ... ... ... ... ...
995 50.02 50.805 49.94 50.52 15008890
996 50.02 50.805 49.94 50.52 15008890
997 50.02 50.805 49.94 50.52 15008890
998 50.02 50.805 49.94 50.52 15008890
999 50.02 50.805 49.94 50.52 15008890
[1000 rows x 11 columns]
```
## Join Trades to Multiple Tables
Returns trade price/size with prevailing bid/ask prices from both the `NBBO` and `NBBO_COMP` tables.
\\\\
Joins three tables together: `TRD`, `NBBO` and `NBBO_COMP`.
\\\\
`NBBO` provides the standard NBBO;
`NBBO_COMP` provides an improved NBBO that includes the Best Odd Lot order (BOLO).
\\\\
Each side's fields are renamed before the join, since `NBBO` and `NBBO_COMP` share column names.
```ipython3
import onetick.py as otp
trd = otp.DataSource(db='US_COMP', tick_type='TRD')
trd = trd[['PRICE', 'SIZE']]
nbbo = otp.DataSource(db='US_COMP', tick_type='NBBO')
nbbo['NBBO_TIME'] = nbbo['TIMESTAMP']
nbbo = nbbo[['NBBO_TIME', 'BID_PRICE', 'ASK_PRICE', 'BID_SIZE', 'ASK_SIZE']]
nbbo_comp = otp.DataSource(db='US_COMP', tick_type='NBBO_COMP')
nbbo_comp = nbbo_comp.add_suffix('_COMP')
nbbo_comp['NBBO_COMP_TIME'] = nbbo_comp['TIMESTAMP']
data = otp.join_by_time([trd, nbbo, nbbo_comp])
data = data.limit(10)
result = otp.run(data,
start=otp.dt(2026, 7, 23, 9, 30),
end=otp.dt(2026, 7, 23, 16, 0),
timezone='America/New_York',
symbols='CSCO')
result
```
```myst-ansi
Time PRICE SIZE NBBO_TIME \
0 2026-07-23 09:30:00.015928092 111.76 45 2026-07-23 09:30:00.005781958
1 2026-07-23 09:30:00.025618222 111.97 1 2026-07-23 09:30:00.005781958
2 2026-07-23 09:30:00.033583537 111.96 1 2026-07-23 09:30:00.005781958
3 2026-07-23 09:30:00.033671013 111.96 1 2026-07-23 09:30:00.005781958
4 2026-07-23 09:30:00.054061753 111.96 1 2026-07-23 09:30:00.005781958
5 2026-07-23 09:30:00.055756073 111.96 1 2026-07-23 09:30:00.005781958
6 2026-07-23 09:30:00.055851011 111.96 1 2026-07-23 09:30:00.005781958
7 2026-07-23 09:30:00.163603286 111.76 1 2026-07-23 09:30:00.005781958
8 2026-07-23 09:30:00.190544330 111.97 2 2026-07-23 09:30:00.189544648
9 2026-07-23 09:30:00.199389623 111.97 7 2026-07-23 09:30:00.189544648
BID_PRICE ASK_PRICE BID_SIZE ASK_SIZE ASK_EXCHANGE_COMP ASK_PRICE_COMP \
0 111.55 111.97 100 1000 Q 111.97
1 111.55 111.97 100 1000 Q 111.97
2 111.55 111.97 100 1000 Q 111.97
3 111.55 111.97 100 1000 Q 111.97
4 111.55 111.97 100 1000 Q 111.97
5 111.55 111.97 100 1000 Q 111.97
6 111.55 111.97 100 1000 Q 111.97
7 111.55 111.97 100 1000 Q 111.97
8 111.55 111.97 100 1000 Q 111.97
9 111.55 111.97 100 1000 Q 111.97
ASK_SIZE_COMP ASK_SIZE_TOTAL_COMP BID_EXCHANGE_COMP BID_PRICE_COMP \
0 1051 1051 P 111.60
1 1051 1051 K 111.60
2 1051 1051 Z 111.60
3 1051 1051 Z 111.60
4 1051 1051 Z 111.63
5 1051 1051 Z 111.63
6 1051 1051 Z 111.63
7 1051 1051 Q 111.81
8 1049 1049 Q 111.81
9 1042 1042 Q 111.81
BID_SIZE_COMP BID_SIZE_TOTAL_COMP IS_PRE_OPEN_COMP OMDSEQ_COMP \
0 2 2 0 3
1 10 12 0 2
2 93 105 0 2
3 93 105 0 2
4 49 60 0 5
5 49 60 0 5
6 49 60 0 5
7 20 20 0 5
8 20 20 0 2
9 20 20 0 2
NBBO_COMP_TIME
0 2026-07-23 09:30:00.005781958
1 2026-07-23 09:30:00.021822151
2 2026-07-23 09:30:00.026266020
3 2026-07-23 09:30:00.026266020
4 2026-07-23 09:30:00.043221664
5 2026-07-23 09:30:00.043221664
6 2026-07-23 09:30:00.043221664
7 2026-07-23 09:30:00.140853617
8 2026-07-23 09:30:00.190543288
9 2026-07-23 09:30:00.199388052
```
## Join Trades to Prevailing Quotes
Returns trade price/size with prevailing bid/ask prices at time of trade.
\\\\
Joins trades and quotes by symbol using [`otp.join_by_time`](https://docs.pip.distribution.sol.onetick.com/api/functions/join_by_time.html.md#onetick.py.join_by_time).
\\\\
As the US Composite includes trades and quotes across multiple exchanges, the `NBBO` tick type is used.
```ipython3
import onetick.py as otp
trd = otp.DataSource(db='US_COMP_SAMPLE', tick_type='TRD')
trd = trd[['PRICE', 'SIZE']]
nbbo = otp.DataSource(db='US_COMP_SAMPLE', tick_type='NBBO')
nbbo = nbbo[['BID_PRICE', 'ASK_PRICE']]
data = otp.join_by_time([trd, nbbo])
data = data.limit(100)
result = otp.run(data,
start=otp.dt(2024, 1, 3),
end=otp.dt(2024, 1, 4),
timezone='UTC',
symbols='CSCO')
result
```
```myst-ansi
Time PRICE SIZE BID_PRICE ASK_PRICE
0 2024-01-03 00:00:40.413537416 50.56 17 50.51 50.65
1 2024-01-03 00:00:40.413540299 50.57 33 50.51 50.65
2 2024-01-03 00:03:34.026455739 50.52 26 50.51 50.65
3 2024-01-03 00:03:34.026458843 50.51 123 50.47 50.49
4 2024-01-03 00:03:34.026459421 50.49 3 50.47 50.49
.. ... ... ... ... ...
95 2024-01-03 10:15:34.061235730 50.15 8 50.01 50.22
96 2024-01-03 10:15:34.061560226 50.20 10 50.01 50.22
97 2024-01-03 10:15:34.061575831 50.20 13 50.01 50.22
98 2024-01-03 10:17:30.746036211 50.19 10 50.01 50.22
99 2024-01-03 10:17:30.746039819 50.21 12 50.01 50.22
[100 rows x 5 columns]
```
### Join Trades to Prevailing Exchange Quotes
Returns trade price/size with prevailing bid/ask prices at time of trade,
matched to the quote from the same exchange as the trade.
\\\\
The `on` parameter of [`otp.join_by_time`](https://docs.pip.distribution.sol.onetick.com/api/functions/join_by_time.html.md#onetick.py.join_by_time) adds this extra match key alongside the
time-based "as of" join, so the trade is joined to the prevailing quote for its own exchange.
```ipython3
import onetick.py as otp
trd = otp.DataSource(db='US_COMP_SAMPLE', tick_type='TRD')
trd = trd[['PRICE', 'SIZE', 'EXCHANGE']]
qte = otp.DataSource(db='US_COMP_SAMPLE', tick_type='QTE')
qte = qte[['EXCHANGE', 'BID_PRICE', 'ASK_PRICE']]
# Match on EXCHANGE in addition to the time-based "as of" join.
data = otp.join_by_time([trd, qte], on=['EXCHANGE'])
data = data.limit(100)
result = otp.run(data,
start=otp.dt(2024, 1, 3, 9, 30),
end=otp.dt(2024, 1, 4, 16, 0),
timezone='America/New_York',
symbols='CSCO')
result
```
```myst-ansi
Time PRICE SIZE EXCHANGE BID_PRICE ASK_PRICE
0 2024-01-03 09:30:00.065443591 50.0200 2 Z 50.00 50.17
1 2024-01-03 09:30:00.111130049 50.1600 3 Z 50.00 50.17
2 2024-01-03 09:30:00.127459523 50.1700 100 V NaN NaN
3 2024-01-03 09:30:00.128498068 50.1700 5 Z 50.00 50.17
4 2024-01-03 09:30:00.135190071 50.1300 46 Q 50.00 50.20
.. ... ... ... ... ... ...
95 2024-01-03 09:30:00.897416820 50.0800 51 Q 50.05 50.10
96 2024-01-03 09:30:00.897444128 50.0800 29 Q 50.05 50.10
97 2024-01-03 09:30:00.897474806 50.0800 12 Q 50.05 50.10
98 2024-01-03 09:30:00.897476583 50.0800 2 Q 50.05 50.10
99 2024-01-03 09:30:00.897670983 50.0797 100 D NaN NaN
[100 rows x 6 columns]
```
## Join Trades to Static to Calc Futures Notional Value
Notional Traded Value is calculated from both the Trade and Static (reference) record.
\\\\
The static record for the futures contract is fetched once (`back_to_first_tick` plus [`last()`](https://docs.pip.distribution.sol.onetick.com/api/source/aggregations/last.html.md#onetick.py.Source.last)),
then joined to every trade with [`otp.join(on='all')`](https://docs.pip.distribution.sol.onetick.com/api/functions/join.html.md#onetick.py.join),
since the static side is a single constant tick.
```ipython3
import onetick.py as otp
trd = otp.DataSource(db='CME_SAMPLE', tick_type='TRD')
trd = trd[['PRICE', 'SIZE']]
stat = otp.DataSource(db='CME_SAMPLE', tick_type='STAT', back_to_first_tick=86400)
stat = stat[['TICK_VALUE', 'TICK_SIZE', 'NAME']]
# Ensure exactly one static record, the prevailing one for the contract.
stat = stat.last()
# Join every trade to the single static record.
data = otp.join(trd, stat, on='all')
data['TRADED_VALUE'] = data['PRICE'] * data['SIZE'] * data['TICK_VALUE'] / data['TICK_SIZE'].astype(float)
data = data[['PRICE', 'SIZE', 'TICK_VALUE', 'TICK_SIZE', 'TRADED_VALUE', 'NAME']]
data = data.limit(1000)
result = otp.run(data,
start=otp.dt(2024, 1, 7, 17),
end=otp.dt(2024, 1, 8, 17),
timezone='America/New_York',
symbols=r'ES\H24')
result
```
```myst-ansi
Time PRICE SIZE TICK_VALUE TICK_SIZE \
0 2024-01-07 18:00:00.000000000 4735.75 145 12.5 0.25
1 2024-01-07 18:00:00.035634379 4736.00 1 12.5 0.25
2 2024-01-07 18:00:00.041642883 4736.00 1 12.5 0.25
3 2024-01-07 18:00:00.044776817 4735.50 1 12.5 0.25
4 2024-01-07 18:00:00.046079459 4735.50 3 12.5 0.25
.. ... ... ... ... ...
995 2024-01-07 18:02:05.246920871 4736.00 1 12.5 0.25
996 2024-01-07 18:02:05.246920871 4736.25 4 12.5 0.25
997 2024-01-07 18:02:05.246920871 4736.25 2 12.5 0.25
998 2024-01-07 18:02:07.807154961 4736.00 2 12.5 0.25
999 2024-01-07 18:02:07.807154961 4736.00 1 12.5 0.25
TRADED_VALUE NAME
0 34334187.5 E-mini S&P 500 Mar24
1 236800.0 E-mini S&P 500 Mar24
2 236800.0 E-mini S&P 500 Mar24
3 236775.0 E-mini S&P 500 Mar24
4 710325.0 E-mini S&P 500 Mar24
.. ... ...
995 236800.0 E-mini S&P 500 Mar24
996 947250.0 E-mini S&P 500 Mar24
997 473625.0 E-mini S&P 500 Mar24
998 473600.0 E-mini S&P 500 Mar24
999 236800.0 E-mini S&P 500 Mar24
[1000 rows x 7 columns]
```
## Join Trades to Sub Query of Prevailing Mid
Returns trade price/size with prevailing mid-price at time of trade.
\\\\
Joins trades and quotes with valid size using [`otp.join_by_time`](https://docs.pip.distribution.sol.onetick.com/api/functions/join_by_time.html.md#onetick.py.join_by_time).
```ipython3
import onetick.py as otp
trd = otp.DataSource(db='US_COMP_SAMPLE', tick_type='TRD')
trd = trd.where(trd['SIZE'] > 0)
trd = trd[['PRICE', 'SIZE']]
qte = otp.DataSource(db='US_COMP_SAMPLE', tick_type='QTE')
qte = qte.where((qte['BID_SIZE'] > 0) & (qte['ASK_SIZE'] > 0))
qte['MID'] = (qte['ASK_PRICE'] + qte['BID_PRICE']) / 2
qte = qte[['MID']]
data = otp.join_by_time([trd, qte])
data = data.limit(1000)
result = otp.run(data,
start=otp.dt(2024, 1, 3),
end=otp.dt(2024, 1, 4),
timezone='America/New_York',
symbols='CSCO')
result
```
```myst-ansi
Time PRICE SIZE MID
0 2024-01-03 04:00:00.019128245 50.46 1 NaN
1 2024-01-03 04:00:00.905488831 50.05 5 50.210
2 2024-01-03 04:00:01.278537460 50.29 13 50.210
3 2024-01-03 04:00:01.278746794 50.29 13 50.210
4 2024-01-03 04:00:03.090763650 50.29 1 50.210
.. ... ... ... ...
995 2024-01-03 09:27:26.473753349 50.00 20 50.125
996 2024-01-03 09:27:26.474854815 50.00 1 50.145
997 2024-01-03 09:27:26.476174053 50.00 1 50.145
998 2024-01-03 09:27:26.477614441 50.00 1 50.145
999 2024-01-03 09:27:26.479953219 50.00 2 50.145
[1000 rows x 4 columns]
```
## Join Trades to US Composite Day Volume
Returns individual trade price/size with the daily total volume (all exchanges composite).
\\\\
The day's volume is fetched once
(`back_to_first_tick` plus [`last()`](https://docs.pip.distribution.sol.onetick.com/api/source/aggregations/last.html.md#onetick.py.Source.last), filtered to the composite `EXCHANGE`),
then joined to every trade with [`otp.join(on='all')`](https://docs.pip.distribution.sol.onetick.com/api/functions/join.html.md#onetick.py.join),
since the daily side is a single constant tick for the query day.
```ipython3
import onetick.py as otp
trd = otp.DataSource(db='US_COMP_SAMPLE', tick_type='TRD')
trd = trd[['PRICE', 'SIZE']]
day = otp.DataSource(db='US_COMP_SAMPLE', tick_type='DAY', back_to_first_tick=86400)
day = day.where(day['EXCHANGE'] == '')
day['VOL_TIME'] = day['TIMESTAMP']
day = day[['VOLUME', 'VOL_TIME']]
day = day.last()
# Join every trade to the single daily volume record.
data = otp.join(trd, day, on='all')
data = data.limit(1000)
result = otp.run(data,
start=otp.dt(2024, 1, 3),
end=otp.dt(2024, 1, 4),
timezone='America/New_York',
symbols='CSCO')
result
```
```myst-ansi
Time PRICE SIZE VOLUME VOL_TIME
0 2024-01-03 04:00:00.019128245 50.46 1 20303875 2024-01-03 20:15:00
1 2024-01-03 04:00:00.905488831 50.05 5 20303875 2024-01-03 20:15:00
2 2024-01-03 04:00:01.278537460 50.29 13 20303875 2024-01-03 20:15:00
3 2024-01-03 04:00:01.278746794 50.29 13 20303875 2024-01-03 20:15:00
4 2024-01-03 04:00:03.090763650 50.29 1 20303875 2024-01-03 20:15:00
.. ... ... ... ... ...
995 2024-01-03 09:27:26.473753349 50.00 20 20303875 2024-01-03 20:15:00
996 2024-01-03 09:27:26.474854815 50.00 1 20303875 2024-01-03 20:15:00
997 2024-01-03 09:27:26.476174053 50.00 1 20303875 2024-01-03 20:15:00
998 2024-01-03 09:27:26.477614441 50.00 1 20303875 2024-01-03 20:15:00
999 2024-01-03 09:27:26.479953219 50.00 2 20303875 2024-01-03 20:15:00
[1000 rows x 5 columns]
```