docs/examples/interface-overview.ipynb

{
 "cells": [
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "# PyKX introduction notebook\n",
    "\n",
    "_The purpose of this notebook is to introduce you to PyKX capabilities and functionality._\n",
    "\n",
    "For the best experience, visit [what is PyKX](../getting-started/what_is_pykx.html) and the [quickstart guide](../getting-started/quickstart.html) first.\n",
    "\n",
    "To follow along, we recommend to <a href=\"./interface-overview.ipynb\" download> download the notebook. </a>\n",
    "\n",
    "Now let's go through the following sections:\n",
    "\n",
    "1. [Import PyKX](#1-import-pykx)\n",
    "1. [Basic PyKX data structures](#2-basic-pykx-data-structures)\n",
    "1. [Access and create PyKX objects](#3-access-and-create-pykx-objects)\n",
    "1. [Run analytics on PyKX objects](#4-run-analytics-on-pykx-objects)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## 1. Import PyKX\n",
    "\n",
    "To access PyKX and its functions, import it in your Python code as follows:"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "tags": [
     "hide_code"
    ]
   },
   "outputs": [],
   "source": [
    "import os\n",
    "os.environ['PYKX_IGNORE_QHOME'] = '1' # Ignore symlinking PyKX q libraries to QHOME\n",
    "os.environ['PYKX_Q_LOADED_MARKER'] = '' # Only used here for running Notebook under mkdocs-jupyter during document generation."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "import pykx as kx\n",
    "kx.q.system.console_size = [10, 80]"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Tip: We recommend to always use `import pykx as kx`. The shortened import name `kx` makes the code more readabile and is standard for the PyKX library. \n",
    "\n",
    "Below we load additional libraries used through this notebook:"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "import numpy as np\n",
    "import pandas as pd"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## 2. Basic PyKX data structures\n",
    "\n",
    "Central to your interaction with PyKX are the data types supported by the library. PyKX is built atop the `q` programming language. This provides small footprint data structures for analytic calculations and the creation of highly-performant databases. The types we show below are generated from Python-equivalent types.\n",
    "\n",
    "This section describes the basic elements in the PyKX library and explains why/how they are different:\n",
    "\n",
    "- 2.1 [Atom](#21-atom)\n",
    "- 2.2 [Vector](#22-vector)\n",
    "- 2.3 [List](#23-list)\n",
    "- 2.4 [Dictionary](#24-dictionary)\n",
    "- 2.5 [Table](#25-table)\n",
    "- 2.6 [Other data types](#26-other-data-types)\n",
    "\n",
    "\n",
    "### 2.1 Atom\n",
    "\n",
    "In PyKX, an `atom` is a single irreducible value of a specific type. For example, you may come across `pykx.FloatAtom` or `pykx.DateAtom` objects which may have been generated as follows, from an equivalent Pythonic representation. "
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "kx.FloatAtom(1.0)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "from datetime import date\n",
    "kx.DateAtom(date(2020, 1, 1))"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### 2.2 Vector\n",
    "\n",
    "Like PyKX atoms, PyKX `Vectors` are a data structure with multiple elements of a single type. These objects in PyKX, along with lists described below, form the basis for most of the other important data structures that you will encounter including dictionaries and tables.\n",
    "\n",
    "Vector objects provide significant benefits when applying analytics over Python lists. Like Numpy, PyKX gains from the underlying speed of its analytic engine when operating on these strictly typed objects.\n",
    "\n",
    "Vector type objects are always 1-D and are/can be indexed along a single axis.\n",
    "\n",
    "In the following example, we create PyKX vectors from common Python equivalent `numpy` and `pandas` objects:"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "kx.IntVector(np.array([1, 2, 3, 4], dtype=np.int32))"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "kx.toq(pd.Series([1, 2, 3, 4]))"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### 2.3 List\n",
    "\n",
    "A PyKX `List` is an untyped vector object. Unlike vectors which are optimised for the performance of analytics, lists are mostly used for storing reference information or matrix data.\n",
    "\n",
    "Unlike vector objects which are 1-D in shape, lists can be ragged N-Dimensional objects. This makes them useful for storing complex data structures, but limits their performance when dealing with data-access/data modification tasks."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "kx.List([[1, 2, 3], [1.0, 1.1, 1.2], ['a', 'b', 'c']])"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### 2.4 Dictionary\n",
    "\n",
    "A PyKX `Dictionary` is a mapping between a direct key-value association. The list of keys and values to which they are associated must have the same count. While it can be considered as a key-value pair, it's physically stored as a pair of lists."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "kx.Dictionary({'x': [1, 2, 3], 'x1': np.array([1, 2, 3])})"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### 2.5 Table\n",
    "\n",
    "PyKX `Tables` are a first-class typed entity which lives in memory. They're a collection of named columns implemented as a dictionary. This mapping construct means that PyKX tables are column oriented. This makes analytic operations on columns much faster than for a relational database equivalent.\n",
    "\n",
    "PyKX Tables come in many forms, but the key table types are as follows:\n",
    "\n",
    " - `pykx.Table` \n",
    " - `pykx.KeyedTable`\n",
    " - `pykx.SplayedTable`\n",
    " - `pykx.PartitionedTable`\n",
    "\n",
    "In this section we exemplify the first two, which are the in-memory data table types.\n",
    "\n",
    "#### pykx.Table"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "print(kx.Table([[1, 2, 'a'], [2, 3, 'b'], [3, 4, 'c']], columns = ['col1', 'col2', 'col3']))"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "print(kx.Table(data = {'col1': [1, 2, 3], 'col2': [2 , 3, 4], 'col3': ['a', 'b', 'c']}))"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "#### pykx.KeyedTable"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "kx.Table([[1, 2, 'a'], [2, 3, 'b'], [3, 4, 'c']],\n",
    "         columns = ['col1', 'col2', 'col3'])"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "kx.Table(data = {\n",
    "         'col1': [1, 2, 3],\n",
    "         'col2': [2 , 3, 4],\n",
    "         'col3': ['a', 'b', 'c']})"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "##### `pykx.KeyedTable`\n",
    "\n",
    "[pykx.KeyedTable](../api/pykx-q-data/wrappers.html#pykx.wrappers.KeyedTable)\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "kx.Table(data = {'x': [1, 2, 3], 'x1': [2, 3, 4], 'x2': ['a', 'b', 'c']}\n",
    "         ).set_index(['x'])"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### 2.6 Other data types\n",
    "\n",
    "Below we outlined some of the important PyKX data type structures that you will run into through the rest of this notebook.\n",
    "\n",
    "#### pykx.Lambda\n",
    "\n",
    "A `pykx.Lambda` is the most basic kind of function within PyKX. They take between 0 and 8 parameters and are the building blocks for most analytics written by users when interacting with data from PyKX."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "pykx_lambda = kx.q('{x+y}')\n",
    "type(pykx_lambda)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "pykx_lambda(1, 2)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "#### pykx.Projection\n",
    "\n",
    "Like [functools.partial](https://docs.python.org/3/library/functools.html#functools.partial), functions in PyKX can have some of their parameters set in advance, resulting in a new function, which is called a projection. When you call this projection, the set parameters are no longer required and cannot be provided.\n",
    "\n",
    "If the original function had `n` total parameters and `m` provided, the result would be a function (projection) that requires the user to input `n-m` parameters."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "projection = kx.q('{x+y}')(1)\n",
    "projection"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "projection(2)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## 3. Access and create PyKX objects\n",
    "\n",
    "Now that you're familiar with the PyKX object types, let's see how they work in real-world scenarios, such as:\n",
    "\n",
    "- 3.1 [Create PyKX objects from Pythonic data types](#31-create-pykx-objects-from-pythonic-data-types)\n",
    "- 3.2 [Random data generation](#32-random-data-generation)\n",
    "- 3.3 [Run q code to generate data](#33-run-q-code-to-generate-data)\n",
    "- 3.4 [Read data from a CSV file](#34-read-data-from-a-csv-file)\n",
    "- 3.5 [Query external processes via IPC](#35-query-external-processes-via-ipc)\n",
    "\n",
    "### 3.1 Create PyKX objects from Pythonic data types\n",
    "\n",
    "One of the most common ways to generate PyKX data is by converting equivalent Pythonic data types. PyKX natively supports conversions to and from the following common Python data formats:\n",
    "\n",
    "- Python\n",
    "- Numpy\n",
    "- Pandas\n",
    "- PyArrow\n",
    "\n",
    "You can generate PyKX objects by using the `kx.toq` PyKX function:"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "pydict = {'a': [1, 2, 3], 'b': ['a', 'b', 'c'], 'c': 2}\n",
    "kx.toq(pydict)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "nparray = np.array([1, 2, 3, 4], dtype = np.int32)\n",
    "kx.toq(nparray)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "pdframe = pd.DataFrame(data = {'a':[1, 2, 3], 'b': ['a', 'b', 'c']})\n",
    "kx.toq(pdframe)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### 3.2 Random data generation\n",
    "\n",
    "PyKX provides a module to create random data of user-specified PyKX types or their equivalent Python types. The creation of random data helps in prototyping analytics.\n",
    "\n",
    "As a first example, generate a list of 1,000,000 random floating-point values between 0 and 1 as follows:"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "kx.random.random(1000000, 1.0)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "If you wish to choose values randomly from a list, use the list as the second argument to your function:"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "kx.random.random(5, [kx.LongAtom(1), ['a', 'b', 'c'], np.array([1.1, 1.2, 1.3])])"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Random data does not only come in 1-Dimensional forms. To create multi-Dimensional PyKX Lists, turn the first argument into a list. The following examples include a PyKX trick that uses nulls/infinities to generate random data across the full allowable range:"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "kx.random.random([2, 5], kx.GUIDAtom.null)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "kx.random.random([2, 3, 4], kx.IntAtom.inf)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Finally, to have consistency over the generated objects, set the seed for the random data generation explicitly. You can complete this globally or for individual function calls:"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "kx.random.seed(10)\n",
    "kx.random.random(10, 2.0)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "kx.random.random(10, 2.0, seed = 10)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### 3.3 Run q code to generate data\n",
    "\n",
    "PyKX is an entry point to the vector programming language q. This means that PyKX users can execute q code directly via PyKX within a Python session, by calling `kx.q`.\n",
    "\n",
    "For example, to create q data, run the following command:"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "kx.q('0 1 2 3 4')"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "kx.q('([idx:desc til 5]col1:til 5;col2:5?1f;col3:5?`2)')"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Next, apply arguments to a user-specified function `x+y`:"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "kx.q('{x+y}', kx.LongAtom(1), kx.LongAtom(2))"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### 3.4 Read data from a CSV file\n",
    "\n",
    "A lot of data that you run into for data analysis tasks comes in the form of CSV files. PyKX, like Pandas, provides a CSV reader called via `kx.q.read.csv`. In the next cell we create a CSV that can be read in PyKX:"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "import csv\n",
    "\n",
    "with open('pykx.csv', 'w', newline='') as file:\n",
    "    writer = csv.writer(file)\n",
    "    field = [\"name\", \"age\", \"height\", \"country\"]\n",
    "    \n",
    "    writer.writerow(field)\n",
    "    writer.writerow([\"Oladele Damilola\", \"40\", \"180.0\", \"Nigeria\"])\n",
    "    writer.writerow([\"Alina Hricko\", \"23\", \"179.2\", \"Ukraine\"])\n",
    "    writer.writerow([\"Isabel Walter\", \"50\", \"179.5\", \"United Kingdom\"])"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "kx.q.read.csv('pykx.csv', types = {'age': kx.LongAtom, 'country': kx.SymbolAtom})"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "import os\n",
    "os.remove('pykx.csv')"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### 3.5 Query external processes via IPC\n",
    "\n",
    "One of the most common usage patterns in organizations with access to data in kdb+/q is to query data from an external server process infrastructure. For the example below you need to [install q](https://kx.com/kdb-insights-personal-edition-license-download/).\n",
    "\n",
    "First, set up a q/kdb+ server. Set it on port 5050 and populate it with some data in the form of a table `tab`:"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "import subprocess\n",
    "import time\n",
    "\n",
    "try:\n",
    "    with kx.PyKXReimport():\n",
    "        proc = subprocess.Popen(\n",
    "            ('q', '-p', '5000')\n",
    "        )\n",
    "    time.sleep(2)\n",
    "except:\n",
    "    raise kx.QError('Unable to create q process on port 5000')"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Once a q process is available, connect to it for synchronous query execution:"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "conn = kx.SyncQConnection(port = 5000)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "You can now run q commands against the q server:"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "conn('tab:([]col1:100?`a`b`c;col2:100?1f;col3:100?0Ng)')\n",
    "conn('select from tab where col1=`a')"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Alternatively, use the PyKX query API:"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "conn.qsql.select('tab', where=['col1=`a', 'col2<0.3'])"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Or use PyKX's context interface to run SQL server side if you have access to it:"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "conn('\\l s.k_')\n",
    "conn.sql('SELECT * FROM tab where col2>=0.5')"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Finally, shut down the q server used for this demonstration:"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "proc.kill()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "---"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## 4. Run analytics on PyKX objects\n",
    "\n",
    "Like many Python libraries (including Numpy and Pandas), PyKX provides many ways to use its data with analytics that you generated and defined within the library. Let's explore the following:\n",
    "\n",
    "- 4.1 [Use in-built methods on PyKX Vectors](#41-use-in-built-methods-on-pykx-vectors)\n",
    "- 4.2 [Use in-built methods on PyKX Tables](#42-use-in-built-methods-on-pykx-tables)\n",
    "- 4.3 [Use PyKX/q native functions](#43-use-pykxq-native-functions)\n",
    "\n",
    "\n",
    "### 4.1 Use in-built methods on PyKX Vectors\n",
    "\n",
    "When you interact with PyKX Vectors, you may wish to gain insights into these objects through the application of basic analytics such as calculation of the `mean`/`median`/`mode` of the vector:"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "q_vector = kx.random.random(1000, 10.0)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "q_vector.mean()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "q_vector.max()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "The above is useful for basic analysis. For bespoke analytics on these vectors, use the `apply` method:"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "def bespoke_function(x, y):\n",
    "    return x*y\n",
    "\n",
    "q_vector.apply(bespoke_function, 5)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### 4.2 Use in-built methods on PyKX Tables\n",
    "\n",
    "In addition to the vector processing capabilities of PyKX, it's important to have the ability to manage tables. Highlighted in depth within the Pandas-Like API documentation [here](../user-guide/advanced/Pandas_API.ipynb), these methods allow you to apply functions and gain insights into your data in a familiar way.\n",
    "\n",
    "The example below uses combinations of the most used elements of this Table API operating on the following table:"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "N = 1000000\n",
    "example_table = kx.Table(data = {\n",
    "    'sym' : kx.random.random(N, ['a', 'b', 'c']),\n",
    "    'col1' : kx.random.random(N, 10.0),\n",
    "    'col2' : kx.random.random(N, 20)\n",
    "    }\n",
    ")\n",
    "example_table"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "You can search for and filter data within your tables using `loc` similarly to how this is achieved by Pandas:"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "example_table.loc[example_table['sym'] == 'a']"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "This also happens when retrieving data from a table through the `__get__` method:"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "example_table[example_table['sym'] == 'b']"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Next, you can set the index columns of a table. In PyKX, this means converting the table from a `pykx.Table` object to a `pykx.KeyedTable` object:"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "example_table.set_index('sym')"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Or you can apply basic data manipulation operations such as `mean` and `median`:"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "print('mean:')\n",
    "display(example_table.mean(numeric_only = True))\n",
    "\n",
    "print('median:')\n",
    "display(example_table.median(numeric_only = True))"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Next, use the `groupby` method to group PyKX tabular data so you can use it for analytic purposes.\n",
    "\n",
    "In the first example, let's start by grouping the dataset based on the `sym` column and calculate the `mean` for each column based on their `sym`:"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "example_table.groupby('sym').mean()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "To extend the above `groupby`, consider a more complex example which uses `numpy` to run calculations on the PyKX data. You will notice later that you can simplify this specific use-case further."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "def apply_func(x):\n",
    "    nparray = x.np()\n",
    "    return np.sqrt(nparray).mean()\n",
    "\n",
    "example_table.groupby('sym').apply(apply_func)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "For time-series specific joining of data, use `merge_asof` joins. In this example, you have several tables with temporal information namely a `trades` and `quotes` table:"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "trades = kx.Table(data={\n",
    "    \"time\": [\n",
    "        pd.Timestamp(\"2016-05-25 13:30:00.023\"),\n",
    "        pd.Timestamp(\"2016-05-25 13:30:00.023\"),\n",
    "        pd.Timestamp(\"2016-05-25 13:30:00.030\"),\n",
    "        pd.Timestamp(\"2016-05-25 13:30:00.041\"),\n",
    "        pd.Timestamp(\"2016-05-25 13:30:00.048\"),\n",
    "        pd.Timestamp(\"2016-05-25 13:30:00.049\"),\n",
    "        pd.Timestamp(\"2016-05-25 13:30:00.072\"),\n",
    "        pd.Timestamp(\"2016-05-25 13:30:00.075\")\n",
    "    ],\n",
    "    \"ticker\": [\n",
    "       \"GOOG\",\n",
    "       \"MSFT\",\n",
    "       \"MSFT\",\n",
    "       \"MSFT\",\n",
    "       \"GOOG\",\n",
    "       \"AAPL\",\n",
    "       \"GOOG\",\n",
    "       \"MSFT\"\n",
    "   ],\n",
    "   \"bid\": [720.50, 51.95, 51.97, 51.99, 720.50, 97.99, 720.50, 52.01],\n",
    "   \"ask\": [720.93, 51.96, 51.98, 52.00, 720.93, 98.01, 720.88, 52.03]\n",
    "})\n",
    "quotes = kx.Table(data={\n",
    "   \"time\": [\n",
    "       pd.Timestamp(\"2016-05-25 13:30:00.023\"),\n",
    "       pd.Timestamp(\"2016-05-25 13:30:00.038\"),\n",
    "       pd.Timestamp(\"2016-05-25 13:30:00.048\"),\n",
    "       pd.Timestamp(\"2016-05-25 13:30:00.048\"),\n",
    "       pd.Timestamp(\"2016-05-25 13:30:00.048\")\n",
    "   ],\n",
    "   \"ticker\": [\"MSFT\", \"MSFT\", \"GOOG\", \"GOOG\", \"AAPL\"],\n",
    "   \"price\": [51.95, 51.95, 720.77, 720.92, 98.0],\n",
    "   \"quantity\": [75, 155, 100, 100, 100]\n",
    "})\n",
    "\n",
    "print('trades:')\n",
    "display(trades)\n",
    "print('quotes:')\n",
    "display(quotes)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "When applying the `asof` join, you can additionally use named arguments to make a distinction between the tables that the columns originate from. In this case, suffix with `_trades` and `_quotes`:"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "trades.merge_asof(quotes, on='time', suffixes=('_trades', '_quotes'))"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### 4.3 Use PyKX/q native functions\n",
    "\n",
    "While the Pandas-like API and methods provided off PyKX Vectors provides an effective method of applying analytics on PyKX data, the most efficient and performant way to run analytics on your data is by using PyKX/q primitives available through the `kx.q` module.\n",
    "\n",
    "These include functionality for calculating moving averages, asof/window joins, column reversal etc. Now let's see a few examples with how you can use these functions, grouped into the following sections:\n",
    "\n",
    "- 4.3.1 [Mathematical functions](#431-mathematical-functions)\n",
    "- 4.3.2 [Iteration functions](#432-iteration-functions)\n",
    "- 4.3.3 [Table functions](#433-table-functions)\n",
    "\n",
    "#### 4.3.1 Mathematical functions\n",
    "\n",
    "##### mavg\n",
    "\n",
    "Calculate a series of average values across a list using a rolling window:"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "kx.q.mavg(10, kx.random.random(10000, 2.0))"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "##### cor\n",
    "\n",
    "Calculate the correlation between two lists:"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "kx.q.cor([1, 2, 3], [2, 3, 4])"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "kx.q.cor(kx.random.random(100, 1.0), kx.random.random(100, 1.0))"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "##### prds\n",
    "\n",
    "Calculate the cumulative product across a supplied list:"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "kx.q.prds([1, 2, 3, 4, 5])"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "#### 4.3.2 Iteration functions\n",
    "\n",
    "##### each\n",
    "\n",
    "Supplied both as a standalone primitive and as a method for PyKX Lambdas `each` allows you to pass individual elements of a PyKX object to a function:"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "kx.q.each(kx.q('{prd x}'), kx.random.random([5, 5], 10.0, seed=10))"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "kx.q('{prd x}').each(kx.random.random([5, 5], 10.0, seed=10))"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "#### 4.3.3 Table functions\n",
    "\n",
    "##### meta\n",
    "\n",
    "Retrieve metadata information about a table:"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "qtab = kx.Table(data = {\n",
    "    'x' : kx.random.random(1000, ['a', 'b', 'c']).grouped(),\n",
    "    'y' : kx.random.random(1000, 1.0),\n",
    "    'z' : kx.random.random(1000, kx.TimestampAtom.inf)\n",
    "})"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "kx.q.meta(qtab)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "##### xasc\n",
    "\n",
    "Sort the contents of a specified column in ascending order:"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "kx.q.xasc('z', qtab)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "You can find the full list of the functions and some examples of their usage [here](../api/pykx-execution/q.md).\n",
    "\n"
   ]
  }
 ],
 "metadata": {
  "file_extension": ".py()",
  "kernelspec": {
   "display_name": "Python 3 (ipykernel)",
   "language": "python",
   "name": "python3"
  },
  "language_info": {
   "codemirror_mode": {
    "name": "ipython",
    "version": 3
   },
   "file_extension": ".py",
   "mimetype": "text/x-python",
   "name": "python",
   "nbconvert_exporter": "python",
   "pygments_lexer": "ipython3",
   "version": "3.10.12"
  },
  "mimetype": "text/x-python",
  "name": "python",
  "npconvert_exporter": "python",
  "pygments_lexer": "ipython3",
  "version": 3
 },
 "nbformat": 4,
 "nbformat_minor": 4
}