Technical News from the Python Financial Analytics Front¶
Quick Overview¶
In this talk, I cover the following libraries:
- TsTables — High Performance Time Series Management
- bcolz — High Performance Columnar Data Store
- ibis — Efficient and Performant Handling of SQL Sources
- blaze — Data Blending and Analysis
TsTables – High Performance Times Series Management
TsTables is a Python library by Andy Fiedler built on top of the popular PyTables HDF5 database library. It is meant to handle large amounts of high frequency time series data in append once, retrieve many times scenarios (cf. Gihub page). The focus lies on retrieving chunks of data from large data sets as quickly as possible.
import os
import numpy as np
import pandas as pd
import tables as tb
import tstables as tstb
import random
from time import time
from datetime import datetime
import seaborn as sns; sns.set()
%matplotlib inline
Sample Time Series Data¶
Let us generate a decent amount of sample data points.
no = 30000000
co = 3
dt = 1. / (12 * 30 * 24 * 60)
vol = 0.2
We generate one second intervals of data.
dr = pd.date_range('2017-1-1', periods=no, freq='1s')
dr
In memory generation is quite quick.
%%time
da = 100 * np.exp(np.cumsum(-0.5 * vol ** 2 * dt +
vol * np.sqrt(dt) * np.random.standard_normal((no, co)), axis=0))
da[0] = 100
df = pd.DataFrame(da, index=dr, columns=['ts1', 'ts2', 'ts3'])
df.count()
The starting values of the three time series.
df.head()
And a plot of the time series data (every 100000th point).
df[::100000].plot(figsize=(10, 6));
Storage and Retrieval with TsTables¶
To store the time series data in a PyTables table we first define the table structure.
class TS(tb.IsDescription):
timestamp = tb.Int64Col(pos=0)
ts1 = tb.Float64Col(pos=1)
ts2 = tb.Float64Col(pos=1)
ts3 = tb.Float64Col(pos=1)
Second, open a database file and create the table object.
path = '/notebooks/fpq/yves/data/'
os.environ['path'] = path
h5 = tb.open_file(path + 'ts.h5','w')
TsTables adds a new function create_ts to PyTables.
ts = h5.create_ts('/','TS', TS)
Third, we append the time series data to the table object.
%time ts.append(df)
!ls -n $path/ts*
The approach of TsTables is to apply a highly structured storage hierarchy.
a = str(h5)
print(a[:508])
The strength of TsTables lies in retrieving chunks of time series data defined by a start date and an end date (which obviously is a typical case in finance, e.g. in backtesting strategies or risk management).
read_start_dt = datetime(2017, 2, 1, 0, 0)
read_end_dt = datetime(2017, 2, 2, 0, 0)
TsTables tries to make such an operation as fast as possible.
%time rows = ts.read_range(read_start_dt, read_end_dt)
Let us try it with random intervals.
t0 = time()
its = 100
for _ in range(its):
day = random.randint(1, 27)
read_start_dt = datetime(2017, 2, day, 0, 0)
read_end_dt = datetime(2017, 2, day + 1, 0, 0)
rows = ts.read_range(read_start_dt, read_end_dt)
t1 = time()
The results are convincing.
print("time for %d random accesses %5.3f seconds" %(its, t1 - t0))
print("average time for random access %5.3f seconds" %((t1 - t0) / its))
Conveniently, the returned object is a pandas DataFrame.
rows.count()
rows.head()
A look at a data sub-set.
rows[::500].plot(figsize=(10, 6));
h5.close()
!rm $path/ts.h5
bcolz – High Performance Columnar Data Store
bcolz is a columnar data store for fast data storage and retrieval with built-in high performance compression. It supports both in-memory and out-of-memory storage and operations. Cf. http://bcolz.blosc.org/.
import bcolz
ctable Example¶
The first example is based on the ctable class for data in table format. The example data set is 1 GB in size.
N = 100000 * 1000
print(N)
In-Memory Storage¶
We generate first an in-memory object using high compression. Since we work with integers, good compression ratios are to be expected. It takes about 44 sec to generate the ctable object from a generator via the fromiter method.
%%time
ct = bcolz.fromiter(((i, i ** 2) for i in range(N)),
dtype="i4, i8",
count=N,
cparams=bcolz.cparams(clevel=9))
The in-memory size is about 150 MB only, which translates in to a compression ratio of 7+.
ct
You can now implement fast numerical operations on this data object (note that the output is a carray object).
%time ct.eval('f0 ** 2 + sqrt(f1)')
Disk-Based Storage¶
The same tasks can be implemented with disk-based storage. To this end, only specify the rootdir parameter. With about 1 minute the generation takes a bit longer on disk. everything else (especially the object handling) remaining the same however.
%%time
ct = bcolz.fromiter(((i, i ** 2) for i in range(N)),
dtype="i4, i8",
count=N, rootdir=path + 'ct',
cparams=bcolz.cparams(clevel=9))
Everything else (especially the object handling) remains almost the same however.
ct
The numerical operations work in the same fashion and hardly take longer due to native multi threading and optimized caching.
%time ct.eval('f0 ** 2 + sqrt(f1)')
Let us finally verify system disk usage.
!ls $path/ct
!du -hs $path/ct
# system disk usage
!rm -r $path/ct
carray Example¶
This example is about mid data which does not fit (in general) into memory (without compression).
import numpy as np
We generte as basis a NumPy ndarray object of size 32 MB.
n = 2000
a = np.arange(n * n).reshape(n, n)
a.nbytes
In-Memory Storage¶
Let us first again work in-memory. Our carray object contains 4,000 versions of the ndarray object. The in-memory generation of the object takes about 25 sec.
%%time
it = 4000
ca = bcolz.carray(a, cparams=bcolz.cparams(clevel=9))
for i in range(it):
ca.append(a)
The carray object stores 120 GB worth of data in less than 1 GB of memory, for a compression ratio of more than 130.
ca
Let us implement the evaluation of a numerical expression on this data set. The syntax and handling are the same as with NumPy ndarray objects.
%time ca[:5000] ** 2 + np.sqrt(ca[10000:15000])
Another approach is to use the eval function of bcolz.
x = ca[:10000] # 10,000 rows as sub-set
%time bcolz.eval('x ** 2 + sqrt(x)', cparams=bcolz.cparams(clevel=9))
# output carray object compressed
Disk-Based Storage¶
Disk-based storage of multiple versions of the array object. We write the object 4,000 times to disk in a single carray object. It takes only about 1 min to compress and store 120 GB worth of data on disk.
%%time
it = 4000
ca = bcolz.carray(a, rootdir=path + 'ca',
cparams=bcolz.cparams(clevel=9))
for i in range(it):
ca.append(a)
The compression ratio in this case is again 130+.
ca
Simple numerical operations are easy to implement.
%time np.sum(ca[:1000] + ca[4000:5000])
Let us try the previous, mathematically more demanding operation – again with a sub-set of the data.
x = ca[:10000] # 10,000 rows as sub-set
First, with an in-memory carray results object.
%time bcolz.eval('x ** 2 + sqrt(x)', cparams=bcolz.cparams(9))
Second, with an on-disk results object. The time difference is not that huge.
%time bcolz.eval('x ** 2 + sqrt(x)', cparams=bcolz.cparams(9), rootdir=path + 'out')
Finally, we verify system disk usage.
# system disk usage data object
!du -hs $path/ca
# system disk usage results object
!du -hs $path/out
!rm -r $path/out
!rm -r $path/ca
ibis — Efficient and Performant Handling of SQL Sources
ibis is (one of) the new projects by Wes McKinney. The content that follows is from the tutorial as found under http://blog.ibis-project.org/sqlite-crunchbase-quickstart/.
Importing & Database Connection¶
First, let us establish a connection to a SQLite3 database.
import ibis
ibis.options.interactive = True
con = ibis.sqlite.connect('crunchbase.db')
Basic Operations and Lookups¶
Certain operations and lookups are easily accomplished without any SQL (1).
con.list_tables()
Certain operations and lookups are easily accomplished without any SQL (2).
rounds = con.table('rounds')
rounds.info()
Certain operations and lookups are easily accomplished without any SQL (3).
rounds.funding_round_type.value_counts()
Instead of writing SQL, you create Pythonic expressions with ibis.
acquisitions = con.table('acquisitions')
expr = (acquisitions.price_amount
.isnull()
.name('has_price')
.value_counts())
expr
Executing expressions gives back DataFrame objects by default for table like results.
df = expr.execute()
df
type(expr)
type(df)
Another case is a single value result.
companies = con.table('companies')
expr = companies.funding_total_usd.mean()
type(expr)
expr.execute()
Funding Metrics¶
Let us have a look at some funding metrics. The time stamping doesn't seem to be "too accurate".
funded_at = rounds.funded_at.cast('timestamp')
funded_at.year().value_counts()
Another look at different funding stages.
rounds.funding_round_code.value_counts()
Next, a somewhat more complex analysis ...
year = funded_at.year().name('year')
type(year)
year
... selecting venture fundings between 2000 and 2015 and grouping them by year and funding rounds. The syntax/API is similar to pandas.
expr = (rounds[(rounds.funding_round_type == 'venture') &
year.between(2000, 2015) &
rounds.funding_round_code.notnull()]
.group_by([year, 'funding_round_code'])
.size())
A selection of the results.
results = expr.execute()
type(results)
results[:15]
Pivot tables are quickly generated out of such results objects.
pivoted = (results.set_index(['year', 'funding_round_code'])
.unstack('funding_round_code')
.fillna(0))
pivoted
Now an analysis with regard to funding sum buckets.
funding_buckets = [0, 1000000, 10000000, 50000000, 100000000,
500000000, 1000000000]
bucket = (companies
.funding_total_usd
.bucket(funding_buckets, include_over=True))
bucket.value_counts()
Adding bucket names makes the results output more meaningful.
bucket_names = ['0 to 1m', '1m to 10m', '10m to 50m',
'50m to 100m', '100m to 500m',
'500m to 1b', 'Over 1b']
counts = bucket.name('bucket').value_counts()
labeled = counts.bucket.label(bucket_names)
with_names = counts.mutate(bucket_name=labeled)
with_names
Let us add another results column, about the total funding received for the bucket.
metrics = (companies.group_by(bucket.name('bucket'))
.aggregate(count=companies.count(),
total_funding=companies.funding_total_usd.sum())
.mutate(bucket_name=lambda x: x.bucket.label(bucket_names)))
metrics
Finally, a complex query using the buckets and differentiating across the stati of the company.
joined = (companies.mutate(bucket=bucket,
status=companies.status.fillna('Unknown'))
[(companies.founded_at > '2010-01-01') |
companies.founded_at.isnull()]
.group_by(['bucket', 'status'])
.size()
.mutate(bucket_name=lambda x: (x.bucket.label(bucket_names)
.fillna('Unknown'))))
table = joined.execute()
table.set_index(['status', 'bucket', 'bucket_name'])['count'].unstack('status')
Generated SQL Queries¶
A major benefit of working with ibis is the automatic generation of SQL queries out of Pythonic code/expressions (in the background).
print(ibis.impala.compile(joined))
blaze – Data Blending and Analysis
blaze allows Python users a familiar interface to query data living in diverse data storage systems.
Cf. http://blaze.pydata.org/.
import blaze as bz
Simple Example¶
The first example constructs a blaze.Data object from native Python objects.
t = bz.Data([('Henry', 'boy', 8),
('Lilli', 'girl', 14)],
fields=['name', 'gender', 'age'])
t
t[t.age > 10]
Data from NumPy Array¶
Let us read data from an in-memory NumPy ndarray object.
import numpy as np
a = np.random.standard_normal((1000000, 5))
# 1mn data rows, 5 columns
df = bz.DataFrame(a, columns=['f0', 'f1', 'f2', 'f3', 'f4'])
# blaze DataFrame constructor
A look at the data structure.
df.head()
Data itself is stored as NumPy ndarray object.
df.values
Data from CSV File¶
We generate first a CSV file using the random data from before.
%time df.to_csv(path + 'data.csv', index=False)
Let us read the data with blaze. Actually, we only generate a view.
%time csv = bz.CSV(path + 'data.csv')
%time t1 = bz.Data(csv)
Now, we can work with the data. Note, however, that iterating, slicing, etc. are not (yet) implemented.
%time t1.count()
The backend is a CSV object. And a look at the first 10 rows.
t1.data
t1
Data from SQL¶
We now generate a SQLite3 table with the dummy data from before.
import sqlite3 as sq3
con = sq3.connect(path + 'data.sql')
try:
con.execute('DROP TABLE numbers')
# delete in case it exists
except:
pass
We write the data into an appropriate table.
con.execute(
'CREATE TABLE numbers (f0 real, f1 real, f2 real, f3 real, f4 real)'
)
%time con.executemany('INSERT INTO numbers VALUES (?, ?, ?, ?, ?)', a)
con.commit()
con.close()
Now reading the data with blaze (i.e. just generating a view).
%time t2 = bz.Data('sqlite:///%sdata.sql::numbers' % path)
The SQL backend and first 10 rows again.
t2
Working with the blaze Objects¶
blaze provides an abstraction logic for computations/queries.
# generic table description -- independent of the target data structure
ts = bz.TableSymbol('ts',
'{f0: float64, f1: float64, f2: float64, f3: float64, f4: float64}')
# generic expression -- independent of the target data structure
expr = ts[ts['f0'] + ts['f3'] > 2.5]['f1']
The blaze compiler specializes the generic objects to different data structures.
%time np.array(bz.compute(expr, a)) # NumPy ndarray object
%time np.array(bz.compute(expr, df)) # DataFrame object
%time np.array(bz.compute(expr, csv)) # CSV file representation
In similar fashion, blaze allows unified expression evaluations for different backends (I).
%time t1[t1['f0'] + t1['f3'] > 2.5]['f1'].head()
# table representation 1
# from CSV
In similar fashion, blaze allows unified expression evaluations for different backends (II).
%time t2[t2['f0'] + t2['f3'] > 2.5]['f1'].head()
# table representation 2
# from SQL database
Typical aggregational operations work as well.
%time t1.f0.sum()
%time t2.f3.max()
Transforming Data Formats¶
If you work intensively with data sets, it might be beneficial to transform them once into highly performant binary data formats (eg bcolz, HDF5).
Using bcolz as Data Store¶
%time bz.into(path + 'data.bcolz', path + 'data.csv')
# natively done by blaze
# cparams=bcolz.cparams(9) could be added
# no effect here due to random floats
We can now connect to the bcolz disk-based ctable object.
import bcolz as bc
b = bc.ctable(rootdir=path + 'data.bcolz')
Now, the power of bcolz for numerical computations can be played out.
%time nex = b.eval('sqrt(abs(f0)) + log(abs(f1))')
nex
Using HDF5¶
Similarly, we can use PyTables and HDF5 as an efficient binary store.
import pandas as pd
%%time
con = sq3.connect(path + 'data.sql')
pd.HDFStore(path + 'data.h5')['sql'] = pd.read_sql('SELECT * FROM numbers', con)
# simultaneously reading whole SQL table and writing it to HDF5 store
con.close()
Now, data can be efficiently retrieved.
%%time
%matplotlib inline
pd.HDFStore(path + 'data.h5')['sql'][::1000].cumsum().plot(figsize=(10, 6))
# simultaneously reading data from HDF5 store and plotting it
Cleaning Up¶
!du -h $path
!ls -n $path/dat*.*
# cleaning up
!rm -r $path/dat*.*
Conclusion¶
High performance (hardware-bound) I/O operations and highly efficient data blending and analytics are among Python's key strengths.
http://tpq.io | yves@tpq.io | @dyjh | hilpisch.com
Quant Platform — quant-platform.com
Python for Finance — python-for-finance.com
Derivatives Analytics with Python — derivatives-analytics-with-python.com
Python for Finance Trainings — training.tpq.io