<jupyter_start><jupyter_text>Predicting Trading VolumesHow well do various model types do at predicting transaction volumes for equities?  Let's start by loading & parsing our dataset.  DB record their data as 1 csv per hour, 1 folder per day, so we write our ingestion code accordingly to load a day at once.<jupyter_code>import pandas as pd
import matplotlib
import numpy as np
import matplotlib.pyplot as plt
import matplotlib.ticker as ticker
import glob

%matplotlib inline


data_folder = "data/deutsche-boerse-xetra-pds/2018-06-21/"

def load_ts(full_path):
    df = pd.read_csv(full_path) 
    df["date-time"] = df[['Date', 'Time']].apply(lambda x: ' '.join(x), axis=1)
    df["date-time"] = pd.to_datetime(df["date-time"])
    df.set_index('date-time', inplace=True)
    return (df)


def load_day(target_path):
    dirlist = glob.glob(target_path + "*.csv")
    frames = []
    for path in dirlist:
        print("Loading " + path)
        frames.append(load_ts(path))

    return (pd.concat(frames))

df_train = load_day(data_folder)
print("Loaded " + str(df_train.shape[0]) + " rows")
df_train.head()<jupyter_output>Loading data/deutsche-boerse-xetra-pds/2018-06-21\2018-06-21_BINS_XETR07.csv
Loading data/deutsche-boerse-xetra-pds/2018-06-21\2018-06-21_BINS_XETR08.csv
Loading data/deutsche-boerse-xetra-pds/2018-06-21\2018-06-21_BINS_XETR09.csv
Loading data/deutsche-boerse-xetra-pds/2018-06-21\2018-06-21_BINS_XETR10.csv
Loading data/deutsche-boerse-xetra-pds/2018-06-21\2018-06-21_BINS_XETR11.csv
Loading data/deutsche-boerse-xetra-pds/2018-06-21\2018-06-21_BINS_XETR12.csv
Loading data/deutsche-boerse-xetra-pds/2018-06-21\2018-06-21_BINS_XETR13.csv
Loading data/deutsche-boerse-xetra-pds/2018-06-21\2018-06-21_BINS_XETR14.csv
Loading data/deutsche-boerse-xetra-pds/2018-06-21\2018-06-21_BINS_XETR15.csv
Loaded 65915 rows<jupyter_text>Let's find a popular security to do predictions on...<jupyter_code>popular = df_train.groupby(by=["Mnemonic"])["TradedVolume"].count()
popular = popular.sort_values(ascending=False).head(n=20)
print(popular)<jupyter_output>Mnemonic
BAS     510
SIE     507
BMW     507
DBK     507
BAYN    507
1COV    507
VOW3    507
DAI     506
ALV     506
SAP     506
CBK     505
EOAN    505
DPW     501
ADS     498
MUV2    493
FRE     491
IFX     491
BEI     489
WDI     488
RWE     487
Name: TradedVolume, dtype: int64<jupyter_text>All of the "top 10" equities are traded very frequently througout the day.  Let's plot trade volume for BASF.<jupyter_code>def plot_ts(my_ts, start_time = None, end_time = None):
    # Please specify dates as yyyy-mm-dd
    ax = my_ts.plot(figsize=(15,4))

    
    if (start_time is not None and end_time is not None):
        ax.set_xlim(pd.to_datetime(start_time), pd.to_datetime(end_time))
    
    ax.xaxis.set_major_locator(matplotlib.dates.HourLocator())
    ax.xaxis.set_major_formatter(matplotlib.dates.DateFormatter('%H'))

    plt.show()
    return (plt)

trade_vols = {"DBK":df_train[df_train.Mnemonic == "DBK"]["NumberOfTrades"], "BAS":df_train[df_train.Mnemonic == "BAS"]["NumberOfTrades"]}
x = plot_ts(pd.DataFrame(trade_vols))<jupyter_output><empty_output><jupyter_text>What about over shorter periods?<jupyter_code>plot_ts(df_train[df_train.Mnemonic == "BAS"]["TradedVolume"], start_time = "2018-06-21 14:00", end_time = "2018-06-21 15:00")<jupyter_output><empty_output><jupyter_text>We'll next resample the signal: even intervals are easier to work with, and with luck the smoothing will remove more noise than signal.  Pandas makes this so easy!  Rule of thumb: if it takes you more a than a few lines to do some dataframe manipulation, most likely there is a Pandas function that you should be using instead.<jupyter_code>interval = '2Min'
smooth_bas = df_train[df_train.Mnemonic == "BAS"]["TradedVolume"].resample(interval)
plot_ts(smooth_bas)
print("Data has " + str(smooth_bas.shape[0]) + " rows")<jupyter_output>C:\Users\edwarddi\AppData\Local\Continuum\Anaconda3\lib\site-packages\ipykernel_launcher.py:3: FutureWarning: 
.resample() is now a deferred operation
You called plot(...) on this deferred object which materialized it into a series
by implicitly taking the mean.  Use .resample(...).mean() instead
  This is separate from the ipykernel package so we can avoid doing imports until<jupyter_text>Preparing for Machine LearningNow that we've learned to turn our irregular training data into a tidy little signal sampled at regular intervals, we can turn it into a matrix of inputs X and target values Y.  We'll pick Y to be a few time intervals into the future; the idea here is that we want enough warning of heavy traffic to be able to spin up more VMs to meet it.As well as creating our orderly X (one little timeseries per row), we will also want to normalize our data.  This is a key step because some of the machine learning model types we want to try will work much better if the data has a mean of about zero and most values in the range -1 to +1.<jupyter_code>import numpy as np


def normalize_mat(mat):
    # Given a numpy matrix, does row-wise normalization
    
    #norm_mat = mat - mat.mean(axis=1).reshape(mat.shape[0],1)
    norm_mat = mat - mat.mean()
    #norm_mat = norm_mat / norm_mat.std(axis=1).reshape(norm_mat.shape[0],1)
    norm_mat = norm_mat / norm_mat.std()
        
    return (norm_mat)


def to_supervised(df_ts, num_points = 10, look_ahead = 5):
    # Turns a series into a matrix!
    # We'll use the Pandas .shift function to create a matrix with a mini-timeseries per row
    df_result = pd.DataFrame()
    for i in range(0, num_points):        
        # Move back one more timestep on each loop
        X_col = df_ts.shift(i)        
        X_colname = "X_" + str(i)
        
        df_result[X_colname] = X_col        
        
    
    Y = df_ts.shift(num_points + look_ahead - 1)
    df_result['Y'] = Y
    
    # Shifting each column has resulted in some missing values - need to get rid of those
    mat_ts = df_result.dropna().copy().values
    
    # Let's normalize our data.  Some model types - especially neural networks - really require this
    mat_ts = normalize_mat(mat_ts)
    
    # Now that they've been normalized along with the "predictor" values, we can separate out our target/label values
    Y = mat_ts[:,mat_ts.shape[1] - 1]
    
    return(mat_ts[:,:mat_ts.shape[1] - 1], Y)
    
    
ts_mat, Y = to_supervised(smooth_bas)
print(ts_mat[0:5,])
print(Y[0:5])
print("Dataset of " + str(Y.shape[0]) + " samples ready for training")<jupyter_output>C:\Users\edwarddi\AppData\Local\Continuum\Anaconda3\lib\site-packages\ipykernel_launcher.py:21: FutureWarning: 
.resample() is now a deferred operation
You called shift(...) on this deferred object which materialized it into a series
by implicitly taking the mean.  Use .resample(...).mean() instead<jupyter_text>Scale it up!  Build our model from the most-traded stocksUsed the sorted list we made earlier, and make provision for a test set<jupyter_code>import math 
from random import random

train_X = []
train_Y = []
test_X = []
test_Y = []

train_fraction = 0.8

for i in popular.index:
    print("Loading data on " + i)
    df_smooth = df_train[df_train.Mnemonic == i]["TradedVolume"].resample(interval)
    X,Y = to_supervised(df_smooth, 10, 1)
    
    if random() < train_fraction:
        train_X.append(X)
        train_Y.append(Y)    
    else:
        test_X.append(X)
        test_Y.append(Y)
    
    #split_row = math.floor(train_fraction * X.shape[0])
    #train_X.append(X[0:split_row,])
    #train_Y.append(Y[0:split_row])
    #test_X.append(X[split_row:X.shape[0],])
    #test_Y.append(Y[split_row:Y.shape[0]])

# Convert from list of matrices to single matrix
print("Loaded data on " + str(len(train_X)) + " stocks")
train_X = np.vstack(train_X)
train_Y = np.concatenate(train_Y)
test_X = np.vstack(test_X)
test_Y = np.concatenate(test_Y)


np.save("train_X.npy", train_X)
np.save("train_Y.npy", train_Y)

np.save("test_X.npy", test_X)
np.save("test_Y.npy", test_Y)


print("Loaded " + str(train_X.shape[0]) + " training samples and " + str(test_X.shape[0]) + " test samples.")<jupyter_output>C:\Users\edwarddi\AppData\Local\Continuum\Anaconda3\lib\site-packages\ipykernel_launcher.py:21: FutureWarning: 
.resample() is now a deferred operation
You called shift(...) on this deferred object which materialized it into a series
by implicitly taking the mean.  Use .resample(...).mean() instead
C:\Users\edwarddi\AppData\Local\Continuum\Anaconda3\lib\site-packages\ipykernel_launcher.py:27: FutureWarning: 
.resample() is now a deferred operation
You called shift(...) on this deferred object which materialized it into a series
by implicitly taking the mean.  Use .resample(...).mean() instead<jupyter_text>Training timeDoing Bayesian Linear Regressions lets us predict not just the most likely trading volumes, but more importantly the likely upper and lower bounds - so we can reliably support the number of trades that happen.<jupyter_code>from sklearn.linear_model import BayesianRidge,  LinearRegression
from matplotlib import pyplot
from numpy.random import random

stockX = np.load("train_X.npy")
stockY = np.load("train_Y.npy")
print("Loaded training data with dimensions " + str(stockX.shape))

test_stockX = np.load("test_X.npy")
test_stockY = np.load("test_Y.npy")
print("Loaded testing data with dimensions " + str(test_stockX.shape))

clf = BayesianRidge(fit_intercept=True)
#clf = LinearRegression()
clf.fit(stockX[0:1000,:] + random((1000,10)) * 0.001, stockY[0:1000])
#Y_mean, Y_std = clf.predict(stockX[1000:2000], return_std=True)
Y_mean, Y_std = clf.predict(stockX[1000:2000], return_std = True)

print(clf)<jupyter_output>Loaded training data with dimensions (4316, 10)
Loaded testing data with dimensions (478, 10)
[-0.4071184  -0.43726881 -0.47353216 -0.43974691 -0.45957485 -0.43811098
 -0.19960886 -0.1758345  -0.17093136 -0.41259066]
[ 0.88940937  0.88948564  0.88969958  0.8896865   0.8899625   0.89967542
  0.89967692  0.89993007  0.90040638  0.90071817]
[-0.48486598 -0.6056048  -0.73412317 -0.80071622 -0.5912904  -0.57075236
 -0.87726712 -0.61307318 -0.45343655 -0.40613681]
BayesianRidge(alpha_1=1e-06, alpha_2=1e-06, compute_score=False, copy_X=True,
       fit_intercept=True, lambda_1=1e-06, lambda_2=1e-06, n_iter=300,
       normalize=False, tol=0.001, verbose=False)<jupyter_text>Looks like we've got a model!  Now let's take a look at an example prediction.  We'll plot the actual signal in blue, the median prediction in orange and lightly shade the standard interval around that prediction.<jupyter_code>from matplotlib import pyplot

def plot_truth_median_std(features, target, median_prediction, std_interval, num_intervals=1):
    #plt.plot(np.append(test_stockX[i,:],Y_mean[i]), color='lime')
    plt.plot(np.append(features,median_prediction), color='orange')
    plt.plot(np.append(features,target), color='skyblue')
    
    above = np.append(features,median_prediction + num_intervals * std_interval)
    below = np.append(features,median_prediction - num_intervals * std_interval)

    plt.fill_between(range(len(features) + 1),below,above,alpha=.25, color='orange')
    
plot_truth_median_std(stockX[0,:], stockY[0], Y_mean[0], Y_std[0])<jupyter_output><empty_output><jupyter_text>Let's RecapLet's repeat the journey from raw signal to model predictions.  First, we'll take a little data on BAS and normalize it.<jupyter_code>#df_train[df_train.Mnemonic == "BAS"]["TradedVolume"].resample(interval)
all_bas = df_train[df_train.Mnemonic == "BAS"]["TradedVolume"][0:22]
normal_bas = normalize_mat(all_bas)
ts_sample = normal_bas[0:22]
plt.plot(ts_sample, color='skyblue')<jupyter_output><empty_output><jupyter_text>Next we'll resample the signal to have nice constant intervals. This will also smooth it and make it a little less noisy.<jupyter_code>ts_sample_smooth = ts_sample.resample(interval)
plt.plot(ts_sample_smooth, color='skyblue')<jupyter_output>C:\Users\edwarddi\AppData\Local\Continuum\Anaconda3\lib\site-packages\matplotlib\cbook.py:2680: FutureWarning: 
.resample() is now a deferred operation
You called name(...) on this deferred object which materialized it into a series
by implicitly taking the mean.  Use .resample(...).mean() instead
  return y.name
C:\Users\edwarddi\AppData\Local\Continuum\Anaconda3\lib\site-packages\matplotlib\cbook.py:2548: FutureWarning: 
.resample() is now a deferred operation
You called index(...) on this deferred object which materialized it into a series
by implicitly taking the mean.  Use .resample(...).mean() instead
  return y.index.values, y.values
C:\Users\edwarddi\AppData\Local\Continuum\Anaconda3\lib\site-packages\matplotlib\cbook.py:2548: FutureWarning: 
.resample() is now a deferred operation
You called values(...) on this deferred object which materialized it into a series
by implicitly taking the mean.  Use .resample(...).mean() instead
  return y.index.values, y.values<jupyter_text>Now we've got normalized data, sampled to even intervals, we can go ahead and try predictions.<jupyter_code>sample_features = np.reshape(ts_sample_smooth[0:10], (1,10))
sample_pred, sample_pred_std = clf.predict(sample_features, return_std=True)
plot_truth_median_std(sample_features[0], ts_sample_smooth[10], sample_pred[0], sample_pred_std[0])<jupyter_output>C:\Users\edwarddi\AppData\Local\Continuum\Anaconda3\lib\site-packages\ipykernel_launcher.py:1: FutureWarning: 
.resample() is now a deferred operation
You called __getitem__(...) on this deferred object which materialized it into a series
by implicitly taking the mean.  Use .resample(...).mean() instead
  """Entry point for launching an IPython kernel.
C:\Users\edwarddi\AppData\Local\Continuum\Anaconda3\lib\site-packages\numpy\core\fromnumeric.py:57: FutureWarning: reshape is deprecated and will raise in a subsequent release. Please use .values.reshape(...) instead
  return getattr(obj, method)(*args, **kwds)
C:\Users\edwarddi\AppData\Local\Continuum\Anaconda3\lib\site-packages\ipykernel_launcher.py:3: FutureWarning: 
.resample() is now a deferred operation
You called __getitem__(...) on this deferred object which materialized it into a series
by implicitly taking the mean.  Use .resample(...).mean() instead
  This is separate from the ipykernel package so we can avoid doing imports un[...]
{'fraction_non_alphanumeric': 0.11301489614658368, 'fraction_numerical': 0.04916427722218337, 'mean_word_length': 4.521235521235521, 'pattern_counts': {'":': 2, '<': 42, '<?xml version=': 0, '>': 41, 'https://': 0, 'lorem ipsum': 0, 'www.': 0, 'xml': 0}, 'pii_count': 0, 'substrings_counts': 0, 'word_list_counts': {'cursed_substrings.json': 0, 'profanity_word_list.json': 0, 'sexual_word_list.json': 0, 'zh_pornsignals.json': 0}}
{'chain_length': '12', 'dir': 'jupyter-structured-clean-dedup', 'id': '393851', 'license': 'Apache-2.0', 'n_tokens_mistral': 5453, 'n_tokens_neox': 4937, 'n_words': 1294, 'path': 'trading_volumes_prediction.ipynb', 'repo_name': 'EdwardDixon/fsi', 'size': '14299'}