learnkdb
Back to General

File Parsing · General · Practice Question

Find Exchanges Supporting a Trading Pair

Sign in to track your progress

Question

A reference data file contains exchange and trading-pair records, one record per row.

Each row is written in the format:

Exchange_Base-Quote

Example symex.txt:

Binance_BTC-USDT
Coinbase_ETH-USD
Kraken_BTC-USD
OKX_SOL-USDT
Bybit_BTC-USDC
Bitstamp_XRP-USD
Binance_ETH-USDT
Coinbase_BTC-USDC
Kraken_DOT-USD

Write a q expression to return all exchanges where the trading pair BTC-USDC is supported.

Solution

t:flip ([ex:;asset:]) . flip "_" vs/: read0 `:symex.txt

exec ex from t where asset like "BTC-USDC"

Result:

"Bybit"
"Coinbase"

Alternative Solution

a:flip "_" vs/: read0 `:symex.txt

a[0] where a[1] like "BTC-USDC"

Result:

"Bybit"
"Coinbase"

Explanation

First, read the file:

read0 `:symex.txt

This returns a list of strings:

"Binance_BTC-USDT"
"Coinbase_ETH-USD"
"Kraken_BTC-USD"
"OKX_SOL-USDT"
"Bybit_BTC-USDC"
"Bitstamp_XRP-USD"
"Binance_ETH-USDT"
"Coinbase_BTC-USDC"
"Kraken_DOT-USD"

Next, split each row on the underscore character:

"_" vs/: read0 `:symex.txt

Result:

"Binance"  "BTC-USDT"
"Coinbase" "ETH-USD" 
"Kraken"   "BTC-USD" 
"OKX"      "SOL-USDT"
"Bybit"    "BTC-USDC"
"Bitstamp" "XRP-USD" 
"Binance"  "ETH-USDT"
"Coinbase" "BTC-USDC"
"Kraken"   "DOT-USD"

The solution then transposes the nested list:

flip "_" vs/: read0 `:symex.txt

Result:

"Binance"  "Coinbase" "Kraken"  "OKX"      "Bybit"    "Bitstamp" "Binance"  "Coinbase" "Kraken" 
"BTC-USDT" "ETH-USD"  "BTC-USD" "SOL-USDT" "BTC-USDC" "XRP-USD"  "ETH-USDT" "BTC-USDC" "DOT-USD"

The first row now contains all exchanges and the second row contains all trading pairs.

The dictionary literal syntax introduced in kdb+ 4.1 allows us to define a dictionary projection:

([ex:;asset:])

This projection expects two values:

  • One for the ex key
  • One for the asset key

Applying the projection to the flipped data:

([ex:;asset:]) . flip "_" vs/: read0 `:symex.txt

creates a dictionary:

ex   | "Binance"  "Coinbase" "Kraken"  "OKX"      "Bybit"    "Bitstamp" "Binance"  "Coinbase" "Kraken" 
asset| "BTC-USDT" "ETH-USD"  "BTC-USD" "SOL-USDT" "BTC-USDC" "XRP-USD"  "ETH-USDT" "BTC-USDC" "DOT-USD"

This is known as a column dictionary because each key contains an entire column of values.

Finally, flip converts the column dictionary into a table:

flip ([ex:;asset:]) . flip "_" vs/: read0 `:symex.txt

Result:

ex         asset     
---------------------
"Binance"  "BTC-USDT"
"Coinbase" "ETH-USD" 
"Kraken"   "BTC-USD" 
"OKX"      "SOL-USDT"
"Bybit"    "BTC-USDC"
"Bitstamp" "XRP-USD" 
"Binance"  "ETH-USDT"
"Coinbase" "BTC-USDC"
"Kraken"   "DOT-USD"

The table is assigned to t:

t:flip ([ex:;asset:]) . flip "_" vs/: read0 `:symex.txt

Once the data is in tabular form, we can query it using standard q syntax:

exec ex from t where asset like "BTC-USDC"

This filters the rows where the asset column equals BTC-USDC and returns:

"Bybit"
"Coinbase"

Alternative Solution

The alternative solution avoids creating a table entirely:

a:flip "_" vs/: read0 `:symex.txt

After the flip:

a[0]

contains the exchanges:

"Binance"
"Coinbase"
"Kraken"
"OKX"
"Bybit"
"Bitstamp"
"Binance"
"Coinbase"
"Kraken"

and:

a[1]

contains the trading pairs:

"BTC-USDT"
"ETH-USD"
"BTC-USD"
"SOL-USDT"
"BTC-USDC"
"XRP-USD"
"ETH-USDT"
"BTC-USDC"
"DOT-USD"

The expression:

a[1] like "BTC-USDC"

returns a boolean vector identifying the matching rows (like takes the list of strings on the left and the pattern on the right). With no wildcards in the pattern, this is an exact match; a[1] ~\: "BTC-USDC" would give the same result.

Those positions are then used to select the corresponding exchanges:

a[0] where a[1] like "BTC-USDC"

Result:

"Bybit"
"Coinbase"

The main solution demonstrates how dictionary literal syntax can be used to quickly construct a table, while the alternative solution shows how the same task can be solved directly using list operations.