ten_years_of_daily_data_for_3k_instruments.ipynb

{
 "cells": [
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "First few cells are just starting a local mongo instance\n",
    "==="
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 1,
   "metadata": {
    "collapsed": true
   },
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "db version v3.4.0\r\n",
      "git version: f4240c60f005be757399042dc12f6addbc3170c1\r\n",
      "OpenSSL version: OpenSSL 1.0.2l  25 May 2017\r\n",
      "allocator: system\r\n",
      "modules: none\r\n",
      "build environment:\r\n",
      "    distarch: x86_64\r\n",
      "    target_arch: x86_64\r\n"
     ]
    }
   ],
   "source": [
    "!mongod --version"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 2,
   "metadata": {
    "collapsed": true
   },
   "outputs": [],
   "source": [
    "# kill mongo if it's running (makes this notebook easily re-runnable)\n",
    "# however beware if you are running mongo for other reasons!\n",
    "!ps x | grep mongod | grep -v grep | awk '{print $1}' | xargs kill"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 3,
   "metadata": {
    "collapsed": true
   },
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "#!/usr/bin/env bash\r\n",
      "\r\n",
      "rm -rf data\r\n",
      "mkdir -p data/db\r\n",
      "mongod --dbpath data/db --logpath data/mongo.log &\r\n",
      "sleep 5\r\n"
     ]
    }
   ],
   "source": [
    "!cat ./mongo.sh"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 4,
   "metadata": {
    "collapsed": true
   },
   "outputs": [],
   "source": [
    "!./mongo.sh"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 5,
   "metadata": {
    "collapsed": true
   },
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "2017-09-19T21:41:42.505-0400 I CONTROL  [initandlisten] \r\n",
      "2017-09-19T21:41:42.505-0400 I CONTROL  [initandlisten] ** WARNING: Access control is not enabled for the database.\r\n",
      "2017-09-19T21:41:42.506-0400 I CONTROL  [initandlisten] **          Read and write access to data and configuration is unrestricted.\r\n",
      "2017-09-19T21:41:42.506-0400 I CONTROL  [initandlisten] \r\n",
      "2017-09-19T21:41:42.573-0400 I FTDC     [initandlisten] Initializing full-time diagnostic data capture with directory 'data/db/diagnostic.data'\r\n",
      "2017-09-19T21:41:42.600-0400 I INDEX    [initandlisten] build index on: admin.system.version properties: { v: 2, key: { version: 1 }, name: \"incompatible_with_version_32\", ns: \"admin.system.version\" }\r\n",
      "2017-09-19T21:41:42.600-0400 I INDEX    [initandlisten] \t building index using bulk method; build may temporarily use up to 500 megabytes of RAM\r\n",
      "2017-09-19T21:41:42.611-0400 I INDEX    [initandlisten] build index done.  scanned 0 total records. 0 secs\r\n",
      "2017-09-19T21:41:42.611-0400 I COMMAND  [initandlisten] setting featureCompatibilityVersion to 3.4\r\n",
      "2017-09-19T21:41:42.611-0400 I NETWORK  [thread1] waiting for connections on port 27017\r\n"
     ]
    }
   ],
   "source": [
    "!tail -10 data/mongo.log"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 6,
   "metadata": {
    "collapsed": true
   },
   "outputs": [],
   "source": [
    "import time\n",
    "\n",
    "import arctic\n",
    "import numpy as np\n",
    "import pandas as pd"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Create an example dataframe\n",
    "==="
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 7,
   "metadata": {
    "collapsed": true
   },
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style>\n",
       "    .dataframe thead tr:only-child th {\n",
       "        text-align: right;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: left;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>security_1</th>\n",
       "      <th>security_2</th>\n",
       "      <th>security_3</th>\n",
       "      <th>security_4</th>\n",
       "      <th>security_5</th>\n",
       "      <th>security_6</th>\n",
       "      <th>security_7</th>\n",
       "      <th>security_8</th>\n",
       "      <th>security_9</th>\n",
       "      <th>security_10</th>\n",
       "      <th>...</th>\n",
       "      <th>security_2991</th>\n",
       "      <th>security_2992</th>\n",
       "      <th>security_2993</th>\n",
       "      <th>security_2994</th>\n",
       "      <th>security_2995</th>\n",
       "      <th>security_2996</th>\n",
       "      <th>security_2997</th>\n",
       "      <th>security_2998</th>\n",
       "      <th>security_2999</th>\n",
       "      <th>security_3000</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>2000-01-01</th>\n",
       "      <td>0.360503</td>\n",
       "      <td>0.540044</td>\n",
       "      <td>0.175147</td>\n",
       "      <td>0.667505</td>\n",
       "      <td>0.052440</td>\n",
       "      <td>0.320114</td>\n",
       "      <td>0.479942</td>\n",
       "      <td>0.976082</td>\n",
       "      <td>0.452705</td>\n",
       "      <td>0.633895</td>\n",
       "      <td>...</td>\n",
       "      <td>0.522094</td>\n",
       "      <td>0.934895</td>\n",
       "      <td>0.853929</td>\n",
       "      <td>0.766299</td>\n",
       "      <td>0.278149</td>\n",
       "      <td>0.435728</td>\n",
       "      <td>0.415771</td>\n",
       "      <td>0.189010</td>\n",
       "      <td>0.939602</td>\n",
       "      <td>0.086047</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2000-01-02</th>\n",
       "      <td>0.116827</td>\n",
       "      <td>0.442737</td>\n",
       "      <td>0.213746</td>\n",
       "      <td>0.512537</td>\n",
       "      <td>0.023199</td>\n",
       "      <td>0.589539</td>\n",
       "      <td>0.825675</td>\n",
       "      <td>0.990114</td>\n",
       "      <td>0.937921</td>\n",
       "      <td>0.541192</td>\n",
       "      <td>...</td>\n",
       "      <td>0.501659</td>\n",
       "      <td>0.993732</td>\n",
       "      <td>0.957384</td>\n",
       "      <td>0.383764</td>\n",
       "      <td>0.800484</td>\n",
       "      <td>0.583847</td>\n",
       "      <td>0.835349</td>\n",
       "      <td>0.596753</td>\n",
       "      <td>0.251528</td>\n",
       "      <td>0.107287</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2000-01-03</th>\n",
       "      <td>0.775830</td>\n",
       "      <td>0.290438</td>\n",
       "      <td>0.821741</td>\n",
       "      <td>0.175384</td>\n",
       "      <td>0.065735</td>\n",
       "      <td>0.591823</td>\n",
       "      <td>0.224915</td>\n",
       "      <td>0.689926</td>\n",
       "      <td>0.529868</td>\n",
       "      <td>0.529271</td>\n",
       "      <td>...</td>\n",
       "      <td>0.091219</td>\n",
       "      <td>0.887558</td>\n",
       "      <td>0.211488</td>\n",
       "      <td>0.139867</td>\n",
       "      <td>0.129930</td>\n",
       "      <td>0.650067</td>\n",
       "      <td>0.537680</td>\n",
       "      <td>0.021839</td>\n",
       "      <td>0.368950</td>\n",
       "      <td>0.289270</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2000-01-04</th>\n",
       "      <td>0.800512</td>\n",
       "      <td>0.252834</td>\n",
       "      <td>0.024264</td>\n",
       "      <td>0.742498</td>\n",
       "      <td>0.455012</td>\n",
       "      <td>0.157255</td>\n",
       "      <td>0.010719</td>\n",
       "      <td>0.469180</td>\n",
       "      <td>0.811157</td>\n",
       "      <td>0.613518</td>\n",
       "      <td>...</td>\n",
       "      <td>0.608599</td>\n",
       "      <td>0.027016</td>\n",
       "      <td>0.513667</td>\n",
       "      <td>0.033696</td>\n",
       "      <td>0.262220</td>\n",
       "      <td>0.637498</td>\n",
       "      <td>0.121702</td>\n",
       "      <td>0.443866</td>\n",
       "      <td>0.405910</td>\n",
       "      <td>0.688135</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2000-01-05</th>\n",
       "      <td>0.295997</td>\n",
       "      <td>0.981768</td>\n",
       "      <td>0.989000</td>\n",
       "      <td>0.393383</td>\n",
       "      <td>0.871876</td>\n",
       "      <td>0.518018</td>\n",
       "      <td>0.553047</td>\n",
       "      <td>0.667828</td>\n",
       "      <td>0.295350</td>\n",
       "      <td>0.528792</td>\n",
       "      <td>...</td>\n",
       "      <td>0.485422</td>\n",
       "      <td>0.895180</td>\n",
       "      <td>0.388003</td>\n",
       "      <td>0.862983</td>\n",
       "      <td>0.673129</td>\n",
       "      <td>0.805076</td>\n",
       "      <td>0.198353</td>\n",
       "      <td>0.593167</td>\n",
       "      <td>0.398647</td>\n",
       "      <td>0.621736</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "<p>5 rows × 3000 columns</p>\n",
       "</div>"
      ],
      "text/plain": [
       "            security_1  security_2  security_3  security_4  security_5  \\\n",
       "2000-01-01    0.360503    0.540044    0.175147    0.667505    0.052440   \n",
       "2000-01-02    0.116827    0.442737    0.213746    0.512537    0.023199   \n",
       "2000-01-03    0.775830    0.290438    0.821741    0.175384    0.065735   \n",
       "2000-01-04    0.800512    0.252834    0.024264    0.742498    0.455012   \n",
       "2000-01-05    0.295997    0.981768    0.989000    0.393383    0.871876   \n",
       "\n",
       "            security_6  security_7  security_8  security_9  security_10  \\\n",
       "2000-01-01    0.320114    0.479942    0.976082    0.452705     0.633895   \n",
       "2000-01-02    0.589539    0.825675    0.990114    0.937921     0.541192   \n",
       "2000-01-03    0.591823    0.224915    0.689926    0.529868     0.529271   \n",
       "2000-01-04    0.157255    0.010719    0.469180    0.811157     0.613518   \n",
       "2000-01-05    0.518018    0.553047    0.667828    0.295350     0.528792   \n",
       "\n",
       "                ...        security_2991  security_2992  security_2993  \\\n",
       "2000-01-01      ...             0.522094       0.934895       0.853929   \n",
       "2000-01-02      ...             0.501659       0.993732       0.957384   \n",
       "2000-01-03      ...             0.091219       0.887558       0.211488   \n",
       "2000-01-04      ...             0.608599       0.027016       0.513667   \n",
       "2000-01-05      ...             0.485422       0.895180       0.388003   \n",
       "\n",
       "            security_2994  security_2995  security_2996  security_2997  \\\n",
       "2000-01-01       0.766299       0.278149       0.435728       0.415771   \n",
       "2000-01-02       0.383764       0.800484       0.583847       0.835349   \n",
       "2000-01-03       0.139867       0.129930       0.650067       0.537680   \n",
       "2000-01-04       0.033696       0.262220       0.637498       0.121702   \n",
       "2000-01-05       0.862983       0.673129       0.805076       0.198353   \n",
       "\n",
       "            security_2998  security_2999  security_3000  \n",
       "2000-01-01       0.189010       0.939602       0.086047  \n",
       "2000-01-02       0.596753       0.251528       0.107287  \n",
       "2000-01-03       0.021839       0.368950       0.289270  \n",
       "2000-01-04       0.443866       0.405910       0.688135  \n",
       "2000-01-05       0.593167       0.398647       0.621736  \n",
       "\n",
       "[5 rows x 3000 columns]"
      ]
     },
     "execution_count": 7,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "days = 365*10\n",
    "n_securities = 3000\n",
    "df_wide = pd.DataFrame(data=np.random.rand(days, n_securities), index=pd.date_range('2000', periods=days))\n",
    "df_wide.columns = ['security_{}'.format(i) for i in range(1, n_securities+1)]\n",
    "df_wide.head()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Using VersionStore to read/write wide data\n",
    "==="
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 8,
   "metadata": {
    "collapsed": true
   },
   "outputs": [
    {
     "name": "stderr",
     "output_type": "stream",
     "text": [
      "Library created, but couldn't enable sharding: no such command: 'enablesharding', bad cmd: '{ enablesharding: \"arctic\" }'. This is OK if you're not 'admin'\n"
     ]
    },
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "Libraries: []\n",
      "Symbols in libvs1: []\n"
     ]
    }
   ],
   "source": [
    "db = arctic.Arctic(\"localhost\")\n",
    "print(\"Libraries: {}\".format(db.list_libraries()))\n",
    "db.initialize_library('libvs1', lib_type='VersionStore')\n",
    "libvs1 = db['libvs1']\n",
    "print(\"Symbols in {}: {}\".format('libvs1', libvs1.list_symbols()))"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 9,
   "metadata": {
    "collapsed": true
   },
   "outputs": [],
   "source": [
    "def get_size(lib):\n",
    "    ''' helper to get size of an arctic library in mongo '''\n",
    "    byts = 0.\n",
    "    for c in lib._arctic_lib._library_coll.database.collection_names():\n",
    "        if lib._collection.name in c:\n",
    "            byts += lib._arctic_lib._library_coll.database.command('collstats', c)['storageSize']\n",
    "    return '{} megabytes'.format(byts / 1e6)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 10,
   "metadata": {
    "collapsed": true
   },
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "CPU times: user 687 ms, sys: 271 ms, total: 959 ms\n",
      "Wall time: 1.41 s\n",
      "\n",
      "Size before: 0.024576 megabytes\n",
      "Size after: 0.024576 megabytes\n",
      "Symbols in libvs1: ['wide_item1']\n"
     ]
    }
   ],
   "source": [
    "size_before = get_size(libvs1)\n",
    "%time libvs1.write('wide_item1', df_wide)\n",
    "size_after = get_size(libvs1)\n",
    "\n",
    "print('')\n",
    "print('Size before: {}'.format(size_before))\n",
    "print('Size after: {}'.format(size_after))\n",
    "print('Symbols in {}: {}'.format('libvs1', libvs1.list_symbols()))"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "note: that's way more space efficient than I'd expect.  I will dig a bit deeper when I have a moment... possibly I messed up the calc"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 11,
   "metadata": {
    "collapsed": true
   },
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "CPU times: user 558 ms, sys: 682 ms, total: 1.24 s\n",
      "Wall time: 1.39 s\n"
     ]
    }
   ],
   "source": [
    "#note: versionstore wraps results in a class, .data gets access to the object we want (DataFrame in this case)\n",
    "%time rb_wide = libvs1.read('wide_item1').data"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 12,
   "metadata": {
    "collapsed": true
   },
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style>\n",
       "    .dataframe thead tr:only-child th {\n",
       "        text-align: right;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: left;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>security_1</th>\n",
       "      <th>security_2</th>\n",
       "      <th>security_3</th>\n",
       "      <th>security_4</th>\n",
       "      <th>security_5</th>\n",
       "      <th>security_6</th>\n",
       "      <th>security_7</th>\n",
       "      <th>security_8</th>\n",
       "      <th>security_9</th>\n",
       "      <th>security_10</th>\n",
       "      <th>...</th>\n",
       "      <th>security_2991</th>\n",
       "      <th>security_2992</th>\n",
       "      <th>security_2993</th>\n",
       "      <th>security_2994</th>\n",
       "      <th>security_2995</th>\n",
       "      <th>security_2996</th>\n",
       "      <th>security_2997</th>\n",
       "      <th>security_2998</th>\n",
       "      <th>security_2999</th>\n",
       "      <th>security_3000</th>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>index</th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>2000-01-01</th>\n",
       "      <td>0.360503</td>\n",
       "      <td>0.540044</td>\n",
       "      <td>0.175147</td>\n",
       "      <td>0.667505</td>\n",
       "      <td>0.052440</td>\n",
       "      <td>0.320114</td>\n",
       "      <td>0.479942</td>\n",
       "      <td>0.976082</td>\n",
       "      <td>0.452705</td>\n",
       "      <td>0.633895</td>\n",
       "      <td>...</td>\n",
       "      <td>0.522094</td>\n",
       "      <td>0.934895</td>\n",
       "      <td>0.853929</td>\n",
       "      <td>0.766299</td>\n",
       "      <td>0.278149</td>\n",
       "      <td>0.435728</td>\n",
       "      <td>0.415771</td>\n",
       "      <td>0.189010</td>\n",
       "      <td>0.939602</td>\n",
       "      <td>0.086047</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2000-01-02</th>\n",
       "      <td>0.116827</td>\n",
       "      <td>0.442737</td>\n",
       "      <td>0.213746</td>\n",
       "      <td>0.512537</td>\n",
       "      <td>0.023199</td>\n",
       "      <td>0.589539</td>\n",
       "      <td>0.825675</td>\n",
       "      <td>0.990114</td>\n",
       "      <td>0.937921</td>\n",
       "      <td>0.541192</td>\n",
       "      <td>...</td>\n",
       "      <td>0.501659</td>\n",
       "      <td>0.993732</td>\n",
       "      <td>0.957384</td>\n",
       "      <td>0.383764</td>\n",
       "      <td>0.800484</td>\n",
       "      <td>0.583847</td>\n",
       "      <td>0.835349</td>\n",
       "      <td>0.596753</td>\n",
       "      <td>0.251528</td>\n",
       "      <td>0.107287</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2000-01-03</th>\n",
       "      <td>0.775830</td>\n",
       "      <td>0.290438</td>\n",
       "      <td>0.821741</td>\n",
       "      <td>0.175384</td>\n",
       "      <td>0.065735</td>\n",
       "      <td>0.591823</td>\n",
       "      <td>0.224915</td>\n",
       "      <td>0.689926</td>\n",
       "      <td>0.529868</td>\n",
       "      <td>0.529271</td>\n",
       "      <td>...</td>\n",
       "      <td>0.091219</td>\n",
       "      <td>0.887558</td>\n",
       "      <td>0.211488</td>\n",
       "      <td>0.139867</td>\n",
       "      <td>0.129930</td>\n",
       "      <td>0.650067</td>\n",
       "      <td>0.537680</td>\n",
       "      <td>0.021839</td>\n",
       "      <td>0.368950</td>\n",
       "      <td>0.289270</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2000-01-04</th>\n",
       "      <td>0.800512</td>\n",
       "      <td>0.252834</td>\n",
       "      <td>0.024264</td>\n",
       "      <td>0.742498</td>\n",
       "      <td>0.455012</td>\n",
       "      <td>0.157255</td>\n",
       "      <td>0.010719</td>\n",
       "      <td>0.469180</td>\n",
       "      <td>0.811157</td>\n",
       "      <td>0.613518</td>\n",
       "      <td>...</td>\n",
       "      <td>0.608599</td>\n",
       "      <td>0.027016</td>\n",
       "      <td>0.513667</td>\n",
       "      <td>0.033696</td>\n",
       "      <td>0.262220</td>\n",
       "      <td>0.637498</td>\n",
       "      <td>0.121702</td>\n",
       "      <td>0.443866</td>\n",
       "      <td>0.405910</td>\n",
       "      <td>0.688135</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2000-01-05</th>\n",
       "      <td>0.295997</td>\n",
       "      <td>0.981768</td>\n",
       "      <td>0.989000</td>\n",
       "      <td>0.393383</td>\n",
       "      <td>0.871876</td>\n",
       "      <td>0.518018</td>\n",
       "      <td>0.553047</td>\n",
       "      <td>0.667828</td>\n",
       "      <td>0.295350</td>\n",
       "      <td>0.528792</td>\n",
       "      <td>...</td>\n",
       "      <td>0.485422</td>\n",
       "      <td>0.895180</td>\n",
       "      <td>0.388003</td>\n",
       "      <td>0.862983</td>\n",
       "      <td>0.673129</td>\n",
       "      <td>0.805076</td>\n",
       "      <td>0.198353</td>\n",
       "      <td>0.593167</td>\n",
       "      <td>0.398647</td>\n",
       "      <td>0.621736</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "<p>5 rows × 3000 columns</p>\n",
       "</div>"
      ],
      "text/plain": [
       "            security_1  security_2  security_3  security_4  security_5  \\\n",
       "index                                                                    \n",
       "2000-01-01    0.360503    0.540044    0.175147    0.667505    0.052440   \n",
       "2000-01-02    0.116827    0.442737    0.213746    0.512537    0.023199   \n",
       "2000-01-03    0.775830    0.290438    0.821741    0.175384    0.065735   \n",
       "2000-01-04    0.800512    0.252834    0.024264    0.742498    0.455012   \n",
       "2000-01-05    0.295997    0.981768    0.989000    0.393383    0.871876   \n",
       "\n",
       "            security_6  security_7  security_8  security_9  security_10  \\\n",
       "index                                                                     \n",
       "2000-01-01    0.320114    0.479942    0.976082    0.452705     0.633895   \n",
       "2000-01-02    0.589539    0.825675    0.990114    0.937921     0.541192   \n",
       "2000-01-03    0.591823    0.224915    0.689926    0.529868     0.529271   \n",
       "2000-01-04    0.157255    0.010719    0.469180    0.811157     0.613518   \n",
       "2000-01-05    0.518018    0.553047    0.667828    0.295350     0.528792   \n",
       "\n",
       "                ...        security_2991  security_2992  security_2993  \\\n",
       "index           ...                                                      \n",
       "2000-01-01      ...             0.522094       0.934895       0.853929   \n",
       "2000-01-02      ...             0.501659       0.993732       0.957384   \n",
       "2000-01-03      ...             0.091219       0.887558       0.211488   \n",
       "2000-01-04      ...             0.608599       0.027016       0.513667   \n",
       "2000-01-05      ...             0.485422       0.895180       0.388003   \n",
       "\n",
       "            security_2994  security_2995  security_2996  security_2997  \\\n",
       "index                                                                    \n",
       "2000-01-01       0.766299       0.278149       0.435728       0.415771   \n",
       "2000-01-02       0.383764       0.800484       0.583847       0.835349   \n",
       "2000-01-03       0.139867       0.129930       0.650067       0.537680   \n",
       "2000-01-04       0.033696       0.262220       0.637498       0.121702   \n",
       "2000-01-05       0.862983       0.673129       0.805076       0.198353   \n",
       "\n",
       "            security_2998  security_2999  security_3000  \n",
       "index                                                    \n",
       "2000-01-01       0.189010       0.939602       0.086047  \n",
       "2000-01-02       0.596753       0.251528       0.107287  \n",
       "2000-01-03       0.021839       0.368950       0.289270  \n",
       "2000-01-04       0.443866       0.405910       0.688135  \n",
       "2000-01-05       0.593167       0.398647       0.621736  \n",
       "\n",
       "[5 rows x 3000 columns]"
      ]
     },
     "execution_count": 12,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "rb_wide.head()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 13,
   "metadata": {
    "collapsed": true
   },
   "outputs": [
    {
     "data": {
      "text/plain": [
       "True"
      ]
     },
     "execution_count": 13,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "np.all(rb_wide == df_wide)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 14,
   "metadata": {
    "collapsed": true
   },
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "<class 'pandas.core.frame.DataFrame'>\n",
      "DatetimeIndex: 3650 entries, 2000-01-01 to 2009-12-28\n",
      "Freq: D\n",
      "Columns: 3000 entries, security_1 to security_3000\n",
      "dtypes: float64(3000)\n",
      "memory usage: 83.6 MB\n"
     ]
    }
   ],
   "source": [
    "df_wide.info()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 15,
   "metadata": {
    "collapsed": true
   },
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "<class 'pandas.core.frame.DataFrame'>\n",
      "DatetimeIndex: 3650 entries, 2000-01-01 to 2009-12-28\n",
      "Columns: 3000 entries, security_1 to security_3000\n",
      "dtypes: float64(3000)\n",
      "memory usage: 83.6 MB\n"
     ]
    }
   ],
   "source": [
    "rb_wide.info()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 16,
   "metadata": {
    "collapsed": true
   },
   "outputs": [
    {
     "data": {
      "text/plain": [
       "'D'"
      ]
     },
     "execution_count": 16,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# note: rb_wide's index has lost metadata about it's frequency\n",
    "# but in this instance it can be inferred. would still\n",
    "# be good to change arctic to keep this..\n",
    "rb_wide.index.inferred_freq"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Using ChunkStore to read/write tall data\n",
    "==="
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 17,
   "metadata": {
    "collapsed": true
   },
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "CPU times: user 639 ms, sys: 430 ms, total: 1.07 s\n",
      "Wall time: 1.07 s\n"
     ]
    },
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style>\n",
       "    .dataframe thead tr:only-child th {\n",
       "        text-align: right;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: left;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>date</th>\n",
       "      <th>security_id</th>\n",
       "      <th>vals</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>2000-01-01</td>\n",
       "      <td>security_1</td>\n",
       "      <td>0.360503</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>2000-01-01</td>\n",
       "      <td>security_2</td>\n",
       "      <td>0.540044</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>2000-01-01</td>\n",
       "      <td>security_3</td>\n",
       "      <td>0.175147</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>2000-01-01</td>\n",
       "      <td>security_4</td>\n",
       "      <td>0.667505</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>2000-01-01</td>\n",
       "      <td>security_5</td>\n",
       "      <td>0.052440</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "        date security_id      vals\n",
       "0 2000-01-01  security_1  0.360503\n",
       "1 2000-01-01  security_2  0.540044\n",
       "2 2000-01-01  security_3  0.175147\n",
       "3 2000-01-01  security_4  0.667505\n",
       "4 2000-01-01  security_5  0.052440"
      ]
     },
     "execution_count": 17,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# reshape wide to tall\n",
    "%time df_tall = df_wide.stack().reset_index().rename(columns={'level_0': 'date', 'level_1': 'security_id', 0: 'vals'})\n",
    "\n",
    "df_tall.head()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 18,
   "metadata": {
    "collapsed": true
   },
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "Symbols in libcs1: []\n"
     ]
    }
   ],
   "source": [
    "db.initialize_library('libcs1', lib_type='ChunkStoreV1')\n",
    "libcs = db['libcs1']\n",
    "print('Symbols in {}: {}'.format('libcs1', libcs.list_symbols()))"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 19,
   "metadata": {
    "collapsed": true
   },
   "outputs": [
    {
     "name": "stderr",
     "output_type": "stream",
     "text": [
      "/Users/justin/miniconda2/envs/arctic-perf/lib/python3.6/site-packages/arctic/serialization/numpy_arrays.py:60: FutureWarning: pandas.lib is deprecated and will be removed in a future version.\n",
      "You can access infer_dtype as pandas.api.types.infer_dtype\n",
      "  if pd.lib.infer_dtype(a) == 'mixed':\n"
     ]
    },
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "CPU times: user 2.96 s, sys: 1.07 s, total: 4.03 s\n",
      "Wall time: 5.62 s\n",
      "\n",
      "Size before: 0.016384 megabytes\n",
      "Size after: 104.968192 megabytes\n",
      "Symbols in libcs1: ['tall_item1']\n"
     ]
    }
   ],
   "source": [
    "size_before = get_size(libcs)\n",
    "%time libcs.write('tall_item1', df_tall, chunk_size='A')\n",
    "size_after = get_size(libcs)\n",
    "\n",
    "print('')\n",
    "print('Size before: {}'.format(size_before))\n",
    "print('Size after: {}'.format(size_after))\n",
    "print('Symbols in {}: {}'.format('libcs1', libcs.list_symbols()))"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 20,
   "metadata": {
    "collapsed": true
   },
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "CPU times: user 2.54 s, sys: 2.19 s, total: 4.73 s\n",
      "Wall time: 5.08 s\n"
     ]
    },
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style>\n",
       "    .dataframe thead tr:only-child th {\n",
       "        text-align: right;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: left;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>date</th>\n",
       "      <th>security_id</th>\n",
       "      <th>vals</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>2000-01-01</td>\n",
       "      <td>security_1</td>\n",
       "      <td>0.360503</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>2000-01-01</td>\n",
       "      <td>security_2</td>\n",
       "      <td>0.540044</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>2000-01-01</td>\n",
       "      <td>security_3</td>\n",
       "      <td>0.175147</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>2000-01-01</td>\n",
       "      <td>security_4</td>\n",
       "      <td>0.667505</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>2000-01-01</td>\n",
       "      <td>security_5</td>\n",
       "      <td>0.052440</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "        date security_id      vals\n",
       "0 2000-01-01  security_1  0.360503\n",
       "1 2000-01-01  security_2  0.540044\n",
       "2 2000-01-01  security_3  0.175147\n",
       "3 2000-01-01  security_4  0.667505\n",
       "4 2000-01-01  security_5  0.052440"
      ]
     },
     "execution_count": 20,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "%time rb_tall = libcs.read('tall_item1')\n",
    "\n",
    "rb_tall.head()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 21,
   "metadata": {
    "collapsed": true
   },
   "outputs": [
    {
     "data": {
      "text/plain": [
       "True"
      ]
     },
     "execution_count": 21,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "np.all(df_tall == rb_tall)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 22,
   "metadata": {
    "collapsed": true
   },
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "<class 'pandas.core.frame.DataFrame'>\n",
      "RangeIndex: 10950000 entries, 0 to 10949999\n",
      "Data columns (total 3 columns):\n",
      "date           datetime64[ns]\n",
      "security_id    object\n",
      "vals           float64\n",
      "dtypes: datetime64[ns](1), float64(1), object(1)\n",
      "memory usage: 250.6+ MB\n"
     ]
    }
   ],
   "source": [
    "df_tall.info()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 23,
   "metadata": {
    "collapsed": true
   },
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "<class 'pandas.core.frame.DataFrame'>\n",
      "RangeIndex: 10950000 entries, 0 to 10949999\n",
      "Data columns (total 3 columns):\n",
      "date           datetime64[ns]\n",
      "security_id    object\n",
      "vals           float64\n",
      "dtypes: datetime64[ns](1), float64(1), object(1)\n",
      "memory usage: 250.6+ MB\n"
     ]
    }
   ],
   "source": [
    "rb_tall.info()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Writing tall data to VersionStore (perf ok, tall is less space efficient than wide format for VersionStore)\n",
    "==="
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 24,
   "metadata": {
    "collapsed": true
   },
   "outputs": [
    {
     "data": {
      "text/plain": [
       "'0.024576 megabytes'"
      ]
     },
     "execution_count": 24,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "get_size(libvs1)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 25,
   "metadata": {
    "collapsed": true
   },
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "CPU times: user 5.83 s, sys: 2.16 s, total: 7.99 s\n",
      "Wall time: 7.99 s\n"
     ]
    },
    {
     "data": {
      "text/plain": [
       "VersionedItem(symbol=tall_item1,library=arctic.libvs1,data=<class 'NoneType'>,version=1,metadata=None"
      ]
     },
     "execution_count": 25,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "%time libvs1.write('tall_item1', df_tall)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 26,
   "metadata": {
    "collapsed": true
   },
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "CPU times: user 2.78 s, sys: 2.6 s, total: 5.38 s\n",
      "Wall time: 5.99 s\n"
     ]
    }
   ],
   "source": [
    "%time rb_vs_tall = libvs1.read('tall_item1')"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 27,
   "metadata": {
    "collapsed": true
   },
   "outputs": [
    {
     "data": {
      "text/plain": [
       "True"
      ]
     },
     "execution_count": 27,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "np.all(df_tall == rb_vs_tall.data)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 28,
   "metadata": {
    "collapsed": true
   },
   "outputs": [
    {
     "data": {
      "text/plain": [
       "'0.024576 megabytes'"
      ]
     },
     "execution_count": 28,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "get_size(libvs1)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Writing wide data to ChunkStore (perf bad, not great on space either)\n",
    "==="
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 29,
   "metadata": {
    "collapsed": true
   },
   "outputs": [
    {
     "data": {
      "text/plain": [
       "'113.000448 megabytes'"
      ]
     },
     "execution_count": 29,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "get_size(libcs)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 30,
   "metadata": {
    "collapsed": true
   },
   "outputs": [],
   "source": [
    "df_wide.index.name = 'date'  # chunkstore's date chunker is picky about having an index or column called 'date'"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 31,
   "metadata": {
    "collapsed": true
   },
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "CPU times: user 39.5 s, sys: 1min 2s, total: 1min 41s\n",
      "Wall time: 1min 42s\n"
     ]
    }
   ],
   "source": [
    "%time libcs.write('wide_item1', df_wide, chunk_size='A')"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 32,
   "metadata": {
    "collapsed": true
   },
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "CPU times: user 778 ms, sys: 468 ms, total: 1.25 s\n",
      "Wall time: 1.39 s\n"
     ]
    }
   ],
   "source": [
    "%time rb_cs_wide = libcs.read('wide_item1')"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 33,
   "metadata": {
    "collapsed": true
   },
   "outputs": [
    {
     "data": {
      "text/plain": [
       "True"
      ]
     },
     "execution_count": 33,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "np.all(df_wide == rb_cs_wide)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 34,
   "metadata": {
    "collapsed": true
   },
   "outputs": [
    {
     "data": {
      "text/plain": [
       "'209.985536 megabytes'"
      ]
     },
     "execution_count": 34,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "get_size(libcs)"
   ]
  }
 ],
 "metadata": {
  "kernelspec": {
   "display_name": "Python 3",
   "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.6.0"
  }
 },
 "nbformat": 4,
 "nbformat_minor": 2
}